From 1061e15efcc66ba0ae5e95ae0af5c41b6104dea7 Mon Sep 17 00:00:00 2001
From: liurunyu <lry9898@163.com>
Date: 星期三, 23 十月 2024 20:37:03 +0800
Subject: [PATCH] 为”远程监测“功能查询在线取水口和不在线取水口SQL语句修改
---
pipIrr-platform/pipIrr-global/src/main/resources/mapper/PrIntakeMapper.xml | 26 ++++++++++++++++++++------
1 files changed, 20 insertions(+), 6 deletions(-)
diff --git a/pipIrr-platform/pipIrr-global/src/main/resources/mapper/PrIntakeMapper.xml b/pipIrr-platform/pipIrr-global/src/main/resources/mapper/PrIntakeMapper.xml
index 8709694..f17472d 100644
--- a/pipIrr-platform/pipIrr-global/src/main/resources/mapper/PrIntakeMapper.xml
+++ b/pipIrr-platform/pipIrr-global/src/main/resources/mapper/PrIntakeMapper.xml
@@ -532,7 +532,7 @@
SELECT COUNT(*) AS recordCount
FROM pr_intake inta
INNER JOIN pr_controller con ON con.intakeId = inta.id
- <if test="onLineMap != null and onLineMap !='' and isOnLine != null">
+ <if test="onLineMap != null and onLineMap !='' and onLineMap != '[]' and isOnLine != null">
LEFT JOIN JSON_TABLE(
<!--'[{"rtuAddr":"37142501020100215","isOnLine":true},{"rtuAddr":"4000004","isOnLine":true},{"rtuAddr":"dy20240325","isOnLine":false}]',-->
#{onLineMap},
@@ -543,8 +543,15 @@
) rtus ON con.rtuAddr = rtus.rtuAddr
</if>
WHERE con.intakeId is not null
- <if test="isOnLine != null">
- AND rtus.isOnLine = #{isOnLine}
+ <if test="isOnLine != null and isOnLine == false">
+ <if test="onLineMap != null and onLineMap !='' and onLineMap != '[]'">
+ AND rtus.isOnLine = false OR rtus.rtuAddr IS NULL
+ </if>
+ </if>
+ <if test="isOnLine != null and isOnLine == true">
+ <if test="onLineMap != null and onLineMap !='' and onLineMap != '[]'">
+ AND rtus.isOnLine = true
+ </if>
</if>
<if test="intakeNum != null and intakeNum != ''">
AND inta.name = #{intakeNum}
@@ -577,7 +584,7 @@
WHERE (alarm_loss = 1 OR alarm_battery_volt = 1 OR alarm_valve = 1 OR alarm_water_meter_fault = 1)
AND dt >= DATE_SUB(NOW(), INTERVAL 12 HOUR)
) alarm ON alarm.intakeId = inta.id
- <if test="onLineMap != null and onLineMap !='' and isOnLine != null">
+ <if test="onLineMap != null and onLineMap !='' and onLineMap != '[]' and isOnLine != null">
LEFT JOIN JSON_TABLE(
<!--'[{"rtuAddr":"37142501020100215","isOnLine":true},{"rtuAddr":"4000004","isOnLine":true},{"rtuAddr":"dy20240325","isOnLine":false}]',-->
#{onLineMap},
@@ -588,8 +595,15 @@
) rtus ON con.rtuAddr = rtus.rtuAddr
</if>
WHERE con.intakeId is not null
- <if test="isOnLine != null">
- AND rtus.isOnLine = #{isOnLine}
+ <if test="isOnLine != null and isOnLine == false">
+ <if test="onLineMap != null and onLineMap !='' and onLineMap != '[]'">
+ AND rtus.isOnLine = false OR rtus.rtuAddr IS NULL
+ </if>
+ </if>
+ <if test="isOnLine != null and isOnLine == true">
+ <if test="onLineMap != null and onLineMap !='' and onLineMap != '[]'">
+ AND rtus.isOnLine = true
+ </if>
</if>
<if test="intakeNum != null and intakeNum != ''">
AND inta.name = #{intakeNum}
--
Gitblit v1.8.0