From 5d901b5e3f033e8b04e53420d68626cbd87431c8 Mon Sep 17 00:00:00 2001
From: 648540858 <648540858@qq.com>
Date: 星期五, 06 五月 2022 10:12:34 +0800
Subject: [PATCH] 使用阿里代码规范。规范代码写法

---
 src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java |  165 +++++++++++++++++++++++++++++++++++++++---------------
 1 files changed, 119 insertions(+), 46 deletions(-)

diff --git a/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java b/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java
index 28d63c3..312d3e0 100644
--- a/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java
+++ b/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java
@@ -1,6 +1,7 @@
 package com.genersoft.iot.vmp.storager.dao;
 
 import com.genersoft.iot.vmp.gb28181.bean.DeviceChannel;
+import com.genersoft.iot.vmp.gb28181.bean.DeviceChannelInPlatform;
 import com.genersoft.iot.vmp.vmanager.gb28181.platform.bean.ChannelReduce;
 import org.apache.ibatis.annotations.*;
 import org.springframework.stereotype.Repository;
@@ -54,22 +55,21 @@
     int update(DeviceChannel channel);
 
     @Select(value = {" <script>" +
-            "SELECT * FROM ( "+
-            " SELECT * , (SELECT count(0) FROM device_channel WHERE parentId=dc.channelId) as subCount FROM device_channel dc " +
-            " WHERE dc.deviceId=#{deviceId} " +
+            "SELECT " +
+            "dc.* " +
+            "from " +
+            "device_channel dc " +
+            "WHERE " +
+            "dc.deviceId = #{deviceId} " +
             " <if test='query != null'> AND (dc.channelId LIKE '%${query}%' OR dc.name LIKE '%${query}%' OR dc.name LIKE '%${query}%')</if> " +
             " <if test='parentChannelId != null'> AND dc.parentId=#{parentChannelId} </if> " +
             " <if test='online == true' > AND dc.status=1</if>" +
-            " <if test='online == false' > AND dc.status=0</if>) dcr" +
-            " WHERE 1=1 " +
-            " <if test='hasSubChannel == true' >  AND subCount >0</if>" +
-            " <if test='hasSubChannel == false' >  AND subCount=0</if>" +
-            " ORDER BY channelId ASC" +
+            " <if test='online == false' > AND dc.status=0</if>" +
+            " <if test='hasSubChannel == true' >  AND dc.subCount > 0 </if>" +
+            " <if test='hasSubChannel == false' >  AND dc.subCount = 0 </if>" +
+            "GROUP BY dc.channelId " +
             " </script>"})
     List<DeviceChannel> queryChannels(String deviceId, String parentChannelId, String query, Boolean hasSubChannel, Boolean online);
-
-    @Select("SELECT * FROM device_channel WHERE deviceId=#{deviceId}")
-    List<DeviceChannel> queryChannelsByDeviceId(String deviceId);
 
     @Select("SELECT * FROM device_channel WHERE deviceId=#{deviceId} AND channelId=#{channelId}")
     DeviceChannel queryChannel(String deviceId, String channelId);
@@ -86,28 +86,44 @@
     @Update(value = {"UPDATE device_channel SET streamId=#{streamId} WHERE deviceId=#{deviceId} AND channelId=#{channelId}"})
     void startPlay(String deviceId, String channelId, String streamId);
 
+    @Select(value = {" <script>" +
+            "SELECT " +
+            "    dc.id,\n" +
+            "    dc.channelId,\n" +
+            "    dc.deviceId,\n" +
+            "    dc.name,\n" +
+            "    de.manufacturer,\n" +
+            "    de.hostAddress,\n" +
+            "    dc.subCount,\n" +
+            "    pgc.platformId as platformId,\n" +
+            "    pgc.catalogId as catalogId " +
+            " FROM device_channel dc " +
+            " LEFT JOIN device de ON dc.deviceId = de.deviceId " +
+            " LEFT JOIN platform_gb_channel pgc on pgc.deviceChannelId = dc.id " +
+            " WHERE 1=1 " +
+            " <if test='query != null'> AND (dc.channelId LIKE '%${query}%' OR dc.name LIKE '%${query}%' OR dc.name LIKE '%${query}%')</if> " +
+            " <if test='online == true' > AND dc.status=1</if> " +
+            " <if test='online == false' > AND dc.status=0</if> " +
+            " <if test='hasSubChannel!= null and hasSubChannel == true' >  AND dc.subCount > 0</if> " +
+            " <if test='hasSubChannel!= null and hasSubChannel == false' >  AND dc.subCount = 0</if> " +
+            " <if test='catalogId == null ' >  AND dc.id not in (select deviceChannelId from platform_gb_channel where platformId=#{platformId} ) </if> " +
+            " <if test='catalogId != null ' >  AND pgc.platformId = #{platformId} and pgc.catalogId=#{catalogId} </if> " +
+            " ORDER BY dc.deviceId, dc.channelId ASC" +
+            " </script>"})
+    List<ChannelReduce> queryChannelListInAll(String query, Boolean online, Boolean hasSubChannel, String platformId, String catalogId);
 
     @Select(value = {" <script>" +
-            "SELECT * FROM ( "+
-                " SELECT dc.channelId, dc.deviceId, dc.name, de.manufacturer, de.hostAddress, " +
-                "(SELECT count(0) FROM device_channel WHERE parentId=dc.channelId) as subCount, " +
-                "(SELECT pc.platformId FROM platform_gb_channel pc WHERE pc.deviceId=dc.deviceId AND pc.channelId = dc.channelId ) as platformId " +
-                "FROM device_channel dc " +
-                "LEFT JOIN device de ON dc.deviceId = de.deviceId " +
-                " WHERE 1=1 " +
-                " <if test='query != null'> AND (dc.channelId LIKE '%${query}%' OR dc.name LIKE '%${query}%' OR dc.name LIKE '%${query}%')</if> " +
-                " <if test='online == true' > AND dc.status=1</if> " +
-                " <if test='online == false' > AND dc.status=0</if> " +
-            ") dcr" +
-            " WHERE 1=1 " +
-            " <if test='hasSubChannel!= null and hasSubChannel == true' >  AND subCount >0</if> " +
-            " <if test='hasSubChannel!= null and hasSubChannel == false' >  AND subCount=0</if> " +
-            " <if test='platformId != null and inPlatform == true ' >  AND platformId='${platformId}'</if> " +
-            " <if test='platformId != null and inPlatform == false ' >  AND (platformId != '${platformId}' OR platformId is NULL )  </if> " +
-            " ORDER BY deviceId, channelId ASC" +
+            "SELECT " +
+            "    dc.*,\n" +
+            "    pgc.platformId as platformId,\n" +
+            "    pgc.catalogId as catalogId " +
+            " FROM device_channel dc " +
+            " LEFT JOIN platform_gb_channel pgc on pgc.deviceChannelId = dc.id " +
+            " WHERE pgc.platformId = #{platformId} " +
+            " ORDER BY dc.deviceId, dc.channelId ASC" +
             " </script>"})
+    List<DeviceChannelInPlatform> queryChannelByPlatformId(String platformId);
 
-    List<ChannelReduce> queryChannelListInAll(String query, Boolean online, Boolean hasSubChannel, String platformId, Boolean inPlatform);
 
     @Select("SELECT * FROM device_channel WHERE channelId=#{channelId}")
     List<DeviceChannel> queryChannelByChannelId( String channelId);
@@ -120,20 +136,46 @@
 
     @Insert("<script> " +
             "insert into device_channel " +
-            "(channelId, deviceId, name, manufacture, model, owner, civilCode, block, " +
+            "(channelId, deviceId, name, manufacture, model, owner, civilCode, block, subCount, " +
             "  address, parental, parentId, safetyWay, registerWay, certNum, certifiable, errCode, secrecy, " +
             "  ipAddress, port, password, PTZType, status, streamId, longitude, latitude, createTime, updateTime) " +
             "values " +
             "<foreach collection='addChannels' index='index' item='item' separator=','> " +
             "('${item.channelId}', '${item.deviceId}', '${item.name}', '${item.manufacture}', '${item.model}', " +
-            "'${item.owner}', '${item.civilCode}', '${item.block}'," +
+            "'${item.owner}', '${item.civilCode}', '${item.block}',${item.subCount}," +
             "'${item.address}', ${item.parental}, '${item.parentId}', ${item.safetyWay}, ${item.registerWay}, " +
             "'${item.certNum}', ${item.certifiable}, ${item.errCode}, '${item.secrecy}', " +
             "'${item.ipAddress}', ${item.port}, '${item.password}', ${item.PTZType}, ${item.status}, " +
             "'${item.streamId}', ${item.longitude}, ${item.latitude},'${item.createTime}', '${item.updateTime}')" +
             "</foreach> " +
+            "ON DUPLICATE KEY UPDATE " +
+            "updateTime=VALUES(updateTime), " +
+            "name=VALUES(name), " +
+            "manufacture=VALUES(manufacture), " +
+            "model=VALUES(model), " +
+            "owner=VALUES(owner), " +
+            "civilCode=VALUES(civilCode), " +
+            "block=VALUES(block), " +
+            "subCount=VALUES(subCount), " +
+            "address=VALUES(address), " +
+            "parental=VALUES(parental), " +
+            "parentId=VALUES(parentId), " +
+            "safetyWay=VALUES(safetyWay), " +
+            "registerWay=VALUES(registerWay), " +
+            "certNum=VALUES(certNum), " +
+            "certifiable=VALUES(certifiable), " +
+            "errCode=VALUES(errCode), " +
+            "secrecy=VALUES(secrecy), " +
+            "ipAddress=VALUES(ipAddress), " +
+            "port=VALUES(port), " +
+            "password=VALUES(password), " +
+            "PTZType=VALUES(PTZType), " +
+            "status=VALUES(status), " +
+            "streamId=VALUES(streamId), " +
+            "longitude=VALUES(longitude), " +
+            "latitude=VALUES(latitude)" +
             "</script>")
-    void batchAdd(List<DeviceChannel> addChannels);
+    int batchAdd(List<DeviceChannel> addChannels);
 
     @Update({"<script>" +
             "<foreach collection='updateChannels' item='item' separator=';'>" +
@@ -146,6 +188,7 @@
             "<if test='item.owner != null'>, owner='${item.owner}'</if>" +
             "<if test='item.civilCode != null'>, civilCode='${item.civilCode}'</if>" +
             "<if test='item.block != null'>, block='${item.block}'</if>" +
+            "<if test='item.subCount != null'>, block=${item.subCount}</if>" +
             "<if test='item.address != null'>, address='${item.address}'</if>" +
             "<if test='item.parental != null'>, parental=${item.parental}</if>" +
             "<if test='item.parentId != null'>, parentId='${item.parentId}'</if>" +
@@ -167,21 +210,51 @@
             "WHERE deviceId=#{item.deviceId} AND channelId=#{item.channelId}"+
             "</foreach>" +
             "</script>"})
-    void batchUpdate(List<DeviceChannel> updateChannels);
+    int batchUpdate(List<DeviceChannel> updateChannels);
+
 
     @Select(value = {" <script>" +
-            "SELECT * FROM ( "+
-            " SELECT * , (SELECT count(0) FROM device_channel WHERE parentId=dc.channelId) as subCount FROM device_channel dc " +
-            " WHERE dc.deviceId=#{deviceId} " +
-            " <if test='query != null'> AND (dc.channelId LIKE '%${query}%' OR dc.name LIKE '%${query}%' OR dc.name LIKE '%${query}%')</if> " +
-            " <if test='parentChannelId != null'> AND dc.parentId=#{parentChannelId} </if> " +
-            " <if test='online == true' > AND dc.status=1</if>" +
-            " <if test='online == false' > AND dc.status=0</if>) dcr" +
-            " WHERE 1=1 " +
-            " <if test='hasSubChannel == true' >  AND subCount >0</if>" +
-            " <if test='hasSubChannel == false' >  AND subCount=0</if>" +
-            " ORDER BY channelId ASC" +
-            " LIMIT #{limit} OFFSET #{start}" +
+            "SELECT " +
+            "dc1.* " +
+            "from " +
+            "device_channel dc1 " +
+            "WHERE " +
+            "dc1.deviceId = #{deviceId} " +
+            " <if test='query != null'> AND (dc1.channelId LIKE '%${query}%' OR dc1.name LIKE '%${query}%' OR dc1.name LIKE '%${query}%')</if> " +
+            " <if test='parentChannelId != null'> AND dc1.parentId=#{parentChannelId} </if> " +
+            " <if test='online == true' > AND dc1.status=1</if>" +
+            " <if test='online == false' > AND dc1.status=0</if>" +
+            " <if test='hasSubChannel == true' >  AND dc1.subCount >0</if>" +
+            " <if test='hasSubChannel == false' >  AND dc1.subCount=0</if>" +
+            "ORDER BY dc1.channelId ASC " +
+            "Limit #{limit} OFFSET #{start}" +
             " </script>"})
-    List<DeviceChannel> queryChannelsByDeviceIdWithStartAndLimit(String deviceId, String parentChannelId, String query, Boolean hasSubChannel, Boolean online, int start, int limit);
+    List<DeviceChannel> queryChannelsByDeviceIdWithStartAndLimit(String deviceId, String parentChannelId, String query,
+                                                                 Boolean hasSubChannel, Boolean online, int start, int limit);
+
+    @Select("SELECT * FROM device_channel WHERE deviceId=#{deviceId} AND status=1")
+    List<DeviceChannel> queryOnlineChannelsByDeviceId(String deviceId);
+
+    @Delete(value = {" <script>" +
+            "DELETE " +
+            "from " +
+            "device_channel " +
+            "WHERE " +
+            "deviceId = #{deviceId} " +
+            " AND channelId NOT IN " +
+            "<foreach collection='channels'  item='item'  open='(' separator=',' close=')' > #{item.channelId}</foreach>" +
+            " </script>"})
+    int cleanChannelsNotInList(String deviceId, List<DeviceChannel> channels);
+
+    @Update(" update device_channel" +
+            " set subCount = (select *" +
+            "                from (select count(0)" +
+            "                      from device_channel" +
+            "                      where deviceId = #{deviceId} and parentId = #{channelId}) as temp)" +
+            " where deviceId = #{deviceId} " +
+            " and channelId = #{channelId}")
+    int updateChannelSubCount(String deviceId, String channelId);
+
+    @Update(value = {"UPDATE device_channel SET latitude=${latitude}, longitude=${longitude} WHERE deviceId=#{deviceId} AND channelId=#{channelId}"})
+    void updatePotion(String deviceId, String channelId, double longitude, double latitude);
 }

--
Gitblit v1.8.0