<selectid="selectDeviceDataRecordByDeviceId"resultType="com.anbao.ambientMonitor.data.dto.device.DeviceDataValueDTO">
SELECT
pps.name AS propertyName,
ddr.property_value,
pps.unit,pps.params,pps.data_type
FROM
product_properties pps
LEFT JOIN device d ON d.product_id = pps.product_id
LEFT JOIN (
SELECT
a.product_property_id,
MAX(a.id) id
FROM
device_data_record a
WHERE
a.device_code = #{deviceCode}
GROUP BY
a.product_property_id
) temp ON pps.id = temp.product_property_id
LEFT JOIN device_data_record ddr ON ddr.id = temp.id
WHERE
pps.function_type = 1
AND pps.status = 1
AND pps.is_del = 0
AND d.device_code = #{deviceCode}
ORDER BY
pps.sort_value DESC
</select><selectid="selectDataListByDeviceCodeAndRegionId"resultType="com.anbao.ambientMonitor.data.dto.region.DeviceDataRecordDTO">
SELECT
ddr.product_property_id,
d.device_code,
ddr.create_time,
ddr.unit,
ddr.property_value,
pps.name AS propertyName,
pps.data_type,
pps.params,
pps.identifier
FROM
device_data_record ddr
RIGHT JOIN (
SELECT
a.product_property_id,
MAX(a.id) id
FROM
device_data_record a
RIGHT JOIN ( SELECT
pps.id
FROM
device d
LEFT JOIN product_properties pps ON pps.product_id = d.product_id
WHERE
d.region_id = #{regionId}
AND d.is_del = 0 ) pp_temp on pp_temp.id = a.product_property_id
WHERE
a.region_id = #{regionId}
GROUP BY
a.product_property_id
) temp ON ddr.id = temp.id
RIGHT JOIN product_properties pps ON pps.id = temp.product_property_id
RIGHT JOIN device d ON pps.product_id = d.product_id
WHERE
d.region_id = #{regionId}
AND d.is_del = 0
AND pps.important_flag = 1
AND pps.metering_properties_flag = 1
AND pps.status = 1
AND pps.is_del = 0
ORDER BY pps.sort_value DESC
</select><selectid="selectDataListByDeviceCode"resultType="com.anbao.ambientMonitor.data.dto.region.DeviceDataRecordDTO">
SELECT
ddr.product_property_id,
d.device_code,
ddr.create_time,
pps.unit,
ddr.property_value,
pps.name AS propertyName,
pps.data_type,
pps.params,
pps.identifier,
pps.rw_flag,
pps.identifier,
mrp.name AS positionName,
mr.region_name
FROM
device_data_record ddr
RIGHT JOIN (
SELECT
a.product_property_id,
MAX(a.id) id
FROM
device_data_record a
RIGHT JOIN ( SELECT
pps.id
FROM
device d
LEFT JOIN product_properties pps ON pps.product_id = d.product_id
WHERE
d.device_code = #{deviceCode}
AND d.is_del = 0 ) pp_temp on pp_temp.id = a.product_property_id
WHERE
a.device_code = #{deviceCode}
GROUP BY
a.product_property_id
) temp ON ddr.id = temp.id
RIGHT JOIN product_properties pps ON pps.id = temp.product_property_id
LEFT JOIN device d ON d.product_id = pps.product_id
Left JOIN region_position_device_mapping rpdm ON rpdm.device_id = d.id
Left JOIN merchant_region_position mrp ON mrp.id = rpdm.region_position_id
LEFT JOIN merchant_region mr ON mr.id = d.region_id
WHERE
d.device_code = #{deviceCode}
AND d.is_del = 0
AND pps.important_flag = 1
AND pps.metering_properties_flag = 1
AND pps.status = 1
AND pps.is_del = 0
ORDER BY pps.sort_value DESC
</select><selectid="slectRecordByPosition"resultType="com.anbao.ambientMonitor.data.dto.region.DeviceDataRecordDTO">
SELECT
ddr.property_name,
ddr.product_property_id,
ddr.create_time,
ddr.property_value,
ddr.region_id,
ddr.unit,
d.online_status,
ddr.device_code,
pp.identifier,pp.params,pp.data_type
FROM
device_data_record ddr
INNER JOIN device d ON ddr.device_code = d.device_code
INNER JOIN product_properties pp ON ddr.product_property_id = pp.id
RIGHT JOIN (
SELECT
MAX( a.id ) id,
a.device_code
FROM
device_data_record a
RIGHT JOIN (
SELECT
DISTINCT pps.id
FROM
device d
LEFT JOIN product_properties pps ON pps.product_id = d.product_id
WHERE
d.region_id = #{map.regionId}
AND d.is_del = 0
AND pps.identifier = #{map.identifier}
AND pps.status = 1
) pp_temp on pp_temp.id = a.product_property_id
WHERE
a.region_id = #{map.regionId}
AND a.create_time <= #{map.endTime}
AND a.create_time >= #{map.startTime}
GROUP BY
a.device_code
) temp ON ddr.id = temp.id
ORDER BY
pp.sort_value DESC
</select>
case"day" :
time="%H";break;case"week" :
time="%m-%d";break;case"month" :
time="%m-%d";break;// 查询时间和告警次数 <select id="countAlarmDataByTime" resultType="com.xxx.xxx.xxx.xxx.xxxx.AlarmStatisticsCountDTO">SELECT
temp1.timeAS name,SUM(temp1.count)AS count
FROM(SELECT
DATE_FORMAT(temp.time,#{map.time} ) AS time,temp.count
FROM(SELECT
DATE_FORMAT( aa.alarm_time,'%Y-%m-%d %H:00:00')AStime,COUNT( aa.id )AS count
FROM(SELECT ar.*FROM alarm_record ar
,(SELECT@ids :=#{map.regionId}) b,(SELECT@idsAS _ids,(SELECT@ids := GROUP_CONCAT(id)FROM merchant_region WHERE FIND_IN_SET(parent_id,@ids))AS cids FROM merchant_region WHERE@idsISNOTNULL) a
WHERE
ar.merchant_id =#{map.merchantId}AND FIND_IN_SET(ar.region_id, a._ids)) aa
GROUPBYtimeHAVINGtime>=#{map.startTime}ANDtime<#{map.endTime}ORDERBYtime)temp) temp1
GROUPBY
temp1.time</select>
<resultMap id="alarmStatisticsCountDTO"type="com.anbao.ambientMonitor.data.dto.monitor.AlarmDistributionDTO"><association property="alarmStatisticsCountDTO"
javaType="com.anbao.ambientMonitor.data.dto.monitor.AlarmStatisticsCountDTO"select="selectAlarmStatisticsCountDTO"column="{id=id,beginDate=beginDate,endDate=endDate}"></association></resultMap><select id="selectAlarmStatisticsCountDTO" resultType="com.anbao.ambientMonitor.data.dto.monitor.AlarmStatisticsCountDTO">SELECTcount(ar.id)AS count
FROM
alarm_record ar
LEFTJOIN merchant_region mr ON mr.id = ar.region_id
,(SELECT@ids :=#{id}) b,(SELECT@idsAS _ids,(SELECT@ids := GROUP_CONCAT(id)FROM merchant_region WHERE FIND_IN_SET(parent_id,@ids))AS cids FROM merchant_region WHERE@idsISNOTNULL) a
WHERE
FIND_IN_SET(mr.id, a._ids)AND ar.alarm_time >=#{beginDate}AND ar.alarm_time <#{endDate}</select><select id="alarmDistributionBySecondRegion" resultMap="alarmStatisticsCountDTO">SELECT
mr.id,mr.region_name,#{map.beginDate} AS beginDate,#{map.endDate} AS endDateFROM
merchant_region mr
WHERE
mr.parent_id =0AND mr.merchant_id =#{map.merchantId}AND mr.is_del =0</select>
<!-- 建立中间关系 --><select id="queryList" resultType="com.anbao.ambientMonitor.data.dto.device.DeviceInfoDTO">SELECT
a.*FROM(SELECT
d.id AS deviceId,
d.device_code,
d.device_model,
d.device_name,
d.online_status,
d.hardware_ver,
d.firmware_ver,
d.region_id,
d.last_communication_time,
d.bind_time
FROM
device d
LEFTJOIN merchant_region mr ON d.region_id = mr.id
LEFTJOIN merchant_user_region mur ON mr.id = mur.merchant_region_id
INNERJOIN(SELECT@ids := GROUP_CONCAT(mr.id) aaaa FROM merchant_region mr WHERE mr.merchant_id =#{params.merchantId}) aWHERE
d.merchant_id =#{params.merchantId}AND FIND_IN_SET(mur.merchant_region_id ,@ids)AND d.is_del =0AND mur.merchant_sub_user_id =#{params.merchantSubUserId}AND d.device_model =#{params.deviceModel}UNIONALLSELECT
d.id AS deviceId,
d.device_code,
d.device_model,
d.device_name,
d.online_status,
d.hardware_ver,
d.firmware_ver,
d.region_id,
d.last_communication_time,
d.bind_time
FROM
device d
WHERE
d.merchant_id =#{params.merchantId}AND d.is_del =0AND d.device_model =#{params.deviceModel}AND d.region_id ISNULL) a
ORDERBY
a.bind_time DESC</select>