2024-08-02 朱宝民 优化获取全部取水口接口，包含经纬度及12小时之内的报警信息
| | |
|---|
| | | 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 |
|---|
| | | 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}]',--> |
|---|
| | | <!--'[{"rtuAddr":"37142501020100215","isOnLine":true},{"rtuAddr":"4000004","isOnLine":true},{"rtuAddr":"dy20240325","isOnLine":false}]',--> |
|---|
| | | #{onLineMap}, |
|---|
| | | '$[*]' COLUMNS( |
|---|
| | | rtuAddr VARCHAR(20) PATH '$.rtuAddr', |
|---|
