id_, product_id_, out_code_, code_, name_, full_name_, status_, ip_, area_id_, addvcd_, lgtd_, lttd_, address_, supplier_company_id_, memo_, meta_data_, sn_, is_dele_, create_by_, create_org_id_, create_time_, update_by_, update_time_, version_, last_time_ SELECT d.*,p.name_ as productName,c.COMPANY_NAME_ as companyName,p.icon_ FROM device_info d LEFT JOIN device_product_info p on p.id_ = d.product_id_ LEFT JOIN device_base_company c on c.ID_ = d.supplier_company_id_ WHERE d.is_dele_ = '0' and p.is_dele_='0' and d.code_ like concat('%',#{vo.code},'%') and d.product_id_ = #{vo.productId} and d.name_ like concat('%',#{vo.name},'%') and d.status_ = #{vo.status} and d.manager_user_id_ = #{vo.managerUserId} ORDER BY d.sn_ SELECT d.* FROM device_info d LEFT JOIN device_product_info p on p.id_ = d.product_id_ LEFT JOIN device_product_type t on t.id_ = p.type_id_ ${ew.customSqlSegment} WHERE AND d.IS_DELE_ = '0' and p.is_dele_='0' and t.is_dele_='0' SELECT name_ FROM portal_sys_areas WHERE code_ = #{addvcd} select count(DISTINCT id_) allNum, ifnull(COUNT(DISTINCT CASE WHEN status_ = 1 THEN id_ END), 0) normalNum, ifnull(COUNT(DISTINCT CASE WHEN status_ = 2 THEN id_ END), 0) failureNum, ifnull(COUNT(DISTINCT CASE WHEN status_ = 3 THEN id_ END), 0) warnNum, ifnull(COUNT(DISTINCT CASE WHEN status_ = 9 THEN id_ END), 0) otherNum, ifnull(COUNT(DISTINCT CASE WHEN status_ = -1 THEN id_ END), 0) scrapNum from device_info where is_dele_='0' and product_id_ = #{productId} SELECT d.* FROM device_info d LEFT JOIN device_product_info p ON p.id_ = d.product_id_ LEFT JOIN device_product_type t ON t.id_ = p.type_id_ WHERE d.is_dele_ = '0' and p.is_dele_='0' and t.is_dele_='0' and t.code_ = #{productType} SELECT l.*,d.name_ as deviceName FROM(SELECT device_code_, count(1) as count FROM device_status_log WHERE device_status_ = '2' GROUP BY device_code_) l LEFT JOIN device_info d on d.code_ = l.device_code_ ${ew.customSqlSegment} SELECT d.*,p.name_ as productName,c.COMPANY_NAME_ as companyName,u.name_ as managerUserName FROM device_info d LEFT JOIN device_product_info p on p.id_ = d.product_id_ LEFT JOIN device_base_company c on c.ID_ = d.supplier_company_id_ LEFT JOIN device_base_user u on u.ID_ = d.manager_user_id_ ${ew.customSqlSegment} WHERE AND d.IS_DELE_ = '0' and p.is_dele_='0' SELECT d.*,a.code_ as areaCode FROM device_info d LEFT JOIN device_product_info p on p.id_ = d.product_id_ LEFT JOIN device_product_type t on t.id_ = p.type_id_ LEFT JOIN device_base_area a on a.id_ = d.area_id_ and a.is_dele_='0' WHERE d.is_dele_='0' and p.is_dele_='0' and t.is_dele_='0' and d.flag_ ='1' and p.publish_status_ = '1' and d.code_ = #{deviceCode} SELECT di.name_, di.code_, di.ip_, di.address_, di.address_type_, di.lgtd_, di.lttd_, t.name_ productType FROM device_info di LEFT JOIN device_warn_info dwi ON dwi.device_code_ = di.CODE_ LEFT JOIN device_product_info p on p.id_ = di.product_id_ LEFT JOIN device_product_type t on t.id_ = p.type_id_ ${ew.customSqlSegment} UNION ALL SELECT di.name_, di.code_, di.ip_, di.address_, ex.address_type_, di.lgtd_, di.lttd_, '摄像头' productType FROM device_video_point di LEFT JOIN device_warn_info dwi ON dwi.DEVICE_CODE_ = di.code_ left JOIN device_video_point_extend ex on ex.code_ = di.code_ ${ew.customSqlSegment} SELECT di.STATUS_, dpt.code_, dpt.name_ FROM device_info di LEFT JOIN device_product_info dpi ON di.PRODUCT_ID_ = dpi.id_ LEFT JOIN device_product_type dpt ON dpt.id_ = dpi.type_id_ WHERE dpt.code_ IS NOT NULL UNION ALL SELECT STATUS_, 'video' code_, '摄像头' name_ FROM device_video_point SELECT di.NAME_, di.CODE_, di.ADDRESS_, dd.* FROM device_info di LEFT JOIN device_product_info dpi ON di.PRODUCT_ID_ = dpi.id_ LEFT JOIN device_product_type dpt ON dpi.type_id_ = dpt.id_ LEFT JOIN ${table} dd ON dd.DEVICE_CODE_ = di.CODE_ AND dd.COLL_TIME_ = ( SELECT max( COLL_TIME_ ) FROM device_data_signboaerd WHERE DEVICE_CODE_ = di.code_ LIMIT 1 ) WHERE dpt.code_ = #{code} SELECT di.STATUS_ status, DATE_FORMAT(dsl.START_TIME_, '%Y-%m-%d') time FROM device_info di left join device_status_log dsl on di.CODE_ = dsl.DEVICE_CODE_ LEFT JOIN device_product_info dpi ON di.PRODUCT_ID_ = dpi.id_ LEFT JOIN device_product_type dpt ON dpi.type_id_ = dpt.id_ where dsl.START_TIME_ >= DATE_SUB(now(),INTERVAL 1 MONTH) and dpt.CODE_ = #{code} SELECT dvp.STATUS_ status, DATE_FORMAT(dsl.START_TIME_, '%Y-%m-%d') time FROM device_video_point dvp left join device_status_log dsl on dvp.CODE_ = dsl.DEVICE_CODE_ where dsl.START_TIME_ >= DATE_SUB(now(),INTERVAL 1 MONTH)