2024-08-02 朱宝民 优化获取全部取水口接口,包含经纬度及12小时之内的报警信息
| | |
| | | package com.dy.pipIrrGlobal.voPr; |
| | | |
| | | import com.alibaba.excel.annotation.write.style.ColumnWidth; |
| | | import com.dy.common.po.BaseEntity; |
| | | import lombok.Data; |
| | | |
| | |
| | | private String intakeNum; |
| | | |
| | | /** |
| | | * 取水口经度 |
| | | */ |
| | | private Double lng; |
| | | |
| | | /** |
| | | * 取水口纬度 |
| | | */ |
| | | private Double lat; |
| | | |
| | | /** |
| | | * 是否在线 |
| | | */ |
| | | private Boolean isOnLine; |
| | |
| | | * 是否绑定 |
| | | */ |
| | | private Boolean isBinded; |
| | | |
| | | |
| | | /** |
| | | * 报警信息Json数组 |
| | | */ |
| | | private String alarm; |
| | | } |
| | |
| | | /** |
| | | * 累计流量 |
| | | */ |
| | | private Float cumulativeFlow; |
| | | private Double cumulativeFlow; |
| | | |
| | | /** |
| | | * 数据获取日期 |
| | |
| | | <select id="getOnLineIntakesCount" resultType="java.lang.Long"> |
| | | SELECT |
| | | COUNT(*) AS recordCount |
| | | FROM pr_controller con |
| | | INNER JOIN pr_intake inta ON con.intakeId = inta.id |
| | | left JOIN JSON_TABLE( |
| | | FROM pr_intake inta |
| | | LEFT JOIN pr_controller con ON con.intakeId = inta.id |
| | | LEFT JOIN( |
| | | SELECT |
| | | intake_id AS intakeId, |
| | | CONCAT('[', |
| | | IF(alarm_loss = 1, IF(alarm_water_meter_fault = 1, '{"meter":"流量计故障"},', ''), IF(alarm_water_meter_fault = 1, '{"meter":"流量计故障"}', '')), |
| | | IF(alarm_battery_volt = 1, IF(alarm_loss = 1, '{"loss":"漏损(偷水)报警"},', ''), IF(alarm_loss = 1, '{"loss":"漏损(偷水)报警"}', '')), |
| | | IF(alarm_valve = 1, IF(alarm_battery_volt = 1, '{"batteryVolt":"蓄电池电压报警"},', ''), IF(alarm_battery_volt = 1, '{"batteryVolt":"蓄电池电压报警"}', '')), |
| | | IF(alarm_valve = 1, '{"valve":"阀门报警"}', ''), |
| | | ']') AS alarm |
| | | FROM rm_alarm_state_last |
| | | WHERE (alarm_water_meter_fault = 1 OR alarm_loss = 1 OR alarm_battery_volt = 1 OR alarm_valve = 1) |
| | | AND dt >= DATE_SUB(NOW(),INTERVAL 12 HOUR) |
| | | ) alarm ON alarm.intakeId = inta.id |
| | | LEFT JOIN JSON_TABLE( |
| | | <!--'[{"rtuAddr":"37142501020100215","isOnLine":true},{"rtuAddr":"4000004","isOnLine":true},{"rtuAddr":"dy20240325","isOnLine":false}]',--> |
| | | #{onLineMap}, |
| | | '$[*]' COLUMNS( |
| | |
| | | <if test="intakeNum != null and intakeNum != ''"> |
| | | AND inta.name = #{intakeNum} |
| | | </if> |
| | | <if test="isBinded == 0"> |
| | | AND inta.id NOT IN(SELECT intakeId FROM pr_controller where deleted = 0) |
| | | <if test="isBinded == false "> |
| | | AND con.rtuAddr IS NULL |
| | | </if> |
| | | <if test="isBinded == 1"> |
| | | AND inta.id IN(SELECT intakeId FROM pr_controller where deleted = 0) |
| | | <if test="isBinded == true "> |
| | | AND con.rtuAddr IS NOT NULL |
| | | </if> |
| | | </where> |
| | | </select> |
| | |
| | | <!--获取取水口列表(在线和不在先)--> |
| | | <select id="getOnLineIntakes" resultType="com.dy.pipIrrGlobal.voPr.VoOnLineIntake"> |
| | | SELECT |
| | | inta.id AS intakeId, |
| | | con.rtuAddr, |
| | | inta.name AS intakeNum, |
| | | (CASE |
| | | WHEN con.rtuAddr IS NULL THEN "false" |
| | | WHEN con.rtuAddr IS NOT NULL THEN "true" |
| | | END) AS isBinded, |
| | | rtus.isOnLine |
| | | inta.id AS intakeId, |
| | | con.rtuAddr, |
| | | inta.name AS intakeNum, |
| | | inta.lng, |
| | | inta.lat, |
| | | (CASE |
| | | WHEN con.rtuAddr IS NULL THEN "false" |
| | | WHEN con.rtuAddr IS NOT NULL THEN "true" |
| | | END) AS isBinded, |
| | | rtus.isOnLine, |
| | | alarm.alarm |
| | | FROM pr_intake inta |
| | | LEFT JOIN pr_controller con ON con.intakeId = inta.id |
| | | LEFT JOIN pr_controller con ON con.intakeId = inta.id |
| | | LEFT JOIN( |
| | | SELECT |
| | | intake_id AS intakeId, |
| | | CONCAT( |
| | | IF(alarm_loss = 1, IF(alarm_water_meter_fault = 1, '流量计故障,', ''), IF(alarm_water_meter_fault = 1, '流量计故障', '')), |
| | | IF(alarm_battery_volt = 1, IF(alarm_loss = 1, '漏损(偷水)报警,', ''), IF(alarm_loss = 1, '漏损(偷水)报警', '')), |
| | | IF(alarm_valve = 1, IF(alarm_battery_volt = 1, '蓄电池电压报警,', ''), IF(alarm_battery_volt = 1, '蓄电池电压报警', '')), |
| | | IF(alarm_valve = 1, '阀门报警', '') |
| | | ) AS alarm |
| | | FROM rm_alarm_state_last |
| | | WHERE (alarm_water_meter_fault = 1 OR alarm_loss = 1 OR alarm_battery_volt = 1 OR alarm_valve = 1) |
| | | AND dt >= DATE_SUB(NOW(),INTERVAL 12 HOUR) |
| | | ) alarm ON alarm.intakeId = inta.id |
| | | LEFT JOIN JSON_TABLE( |
| | | <!--'[{"rtuAddr":"37142501020100215","isOnLine":true},{"rtuAddr":"4000004","isOnLine":true},{"rtuAddr":"dy20240325","isOnLine":false}]',--> |
| | | #{onLineMap}, |