From a469e6f48a0312cb6c5a183a89fb79b9807ef13d Mon Sep 17 00:00:00 2001
From: tangxiaobao <303826152@qq.com>
Date: 星期一, 06 九月 2021 18:20:17 +0800
Subject: [PATCH] 办事指南分页查询按时间降序排列

---
 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java |   62 ++++++++++++++-----------------
 1 files changed, 28 insertions(+), 34 deletions(-)

diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
index abc5173..036dd0c 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
@@ -156,9 +156,9 @@
     IPage<InputUserInfoVO> specialInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO);
 
     @Select("<script> "
-        + "select id,tag_name,community_id,create_at,sys_flag from com_mng_user_tag where community_id = #{comMngUserTagDTO.communityId}"
+        + "select id,tag_name,community_id,create_at,sys_flag from com_mng_user_tag <where> "
         + "<if test='comMngUserTagDTO.tagName != null and comMngUserTagDTO.tagName != &quot;&quot;'>"
-        + " AND tag_name LIKE concat(#{comMngUserTagDTO.tagName},'%') " + " </if> " + " order by create_at desc "
+        + " AND tag_name LIKE concat(#{comMngUserTagDTO.tagName},'%') " + " </if> " + "</where>" + " order by create_at desc "
         + "</script>")
     IPage<ComMngTagVO> specialInputUserTags(Page page, @Param("comMngUserTagDTO") PageInputUserDTO comMngUserTagDTO);
 
@@ -182,7 +182,7 @@
     @Select("select count(id) as populationTotal "
         + ",(select count(cmpct.id) from com_mng_population_community_tags cmpct left join com_mng_population cmp on cmpct.population_id = cmp.id where cmpct.community_id = #{communityId} and cmp.out_or_local = 1) as localTotal "
         + ",(select count(cmpct.id) from com_mng_population_community_tags cmpct left join com_mng_population cmp on cmpct.population_id = cmp.id where cmpct.community_id = #{communityId} and cmp.out_or_local = 2) as outTotal "
-        + ",(select count(cmpct.id) from com_mng_population_community_tags cmpct left join com_mng_population cmp on cmpct.population_id = cmp.id where cmpct.community_id = #{communityId} and cmpct.label is not null) as specialTotal "
+        + ",(select count(cmpct.id) from com_mng_population_community_tags cmpct left join com_mng_population cmp on cmpct.population_id = cmp.id where cmpct.community_id = #{communityId} and IFNULL(cmpct.label,'') != '') as specialTotal "
         + ",(select count(village_id) from com_mng_village where community_id = #{communityId}) as villageTotal "
         + ",(select count(id) from com_mng_population_community_tags where community_id = #{communityId} and label LIKE CONCAT('%','吸毒人员','%')) as drugTotal "
         + ",(select count(id) from com_mng_population_community_tags where community_id = #{communityId} and label LIKE CONCAT('%','社区矫正','%')) as correctTotal "
@@ -196,18 +196,19 @@
         + " from com_mng_population_community_tags as cmpct where community_id = #{communityId}")
     ComMngPopulationTotalVO getPopulationTotalByAdmin(@Param("communityId") Long communityId);
 
-    @Select("select count(id) as populationTotal,"
-        + "IFNULL((select count(id) from com_mng_population_house where community_id = cmp.act_id),0) as houseTotal, "
-        + "IFNULL((select count(id) from com_mng_real_company where community_id = cmp.act_id),0) as companyTotal, "
-        + "IFNULL((select count(village_id) from com_mng_village where community_id = cmp.act_id),0) as villageTotal "
-        + " com_mng_population_community_tags as cmpct " + " where community_id = = #{communityId}")
+    @Select("select count(id) as populationTotal," +
+            "IFNULL((select count(id) from com_mng_population_house where community_id = cmpct.community_id),0) as houseTotal, " +
+            "IFNULL((select count(id) from com_mng_real_company where community_id = cmpct.community_id),0) as companyTotal, " +
+            "IFNULL((select count(village_id) from com_mng_village where community_id = cmpct.community_id),0) as villageTotal " +
+            " from com_mng_population_community_tags AS cmpct " +
+            " where cmpct.community_id = #{communityId}")
     IndexBasicsStatisticsVO getScreenIndexByBasics(@Param("communityId") Long communityId);
 
     @Select("SELECT " + " IFNULL( NULL, 1 ) AS type, " + " count( cmpct.id ) AS sum  " + "FROM "
         + " com_mng_population_community_tags as cmpct  "
         + " left join com_mng_population  as cmp on cmpct.population_id = cmp.id " + "WHERE " + " cmp.sex = 1  "
         + " AND cmpct.community_id = #{communityId} UNION ALL " + "SELECT " + " IFNULL( NULL, 2 ) AS type, "
-        + " count( id ) AS sum  " + "FROM " + " com_mng_population_community_tags as cmpct  "
+        + " count( cmpct.id ) AS sum  " + "FROM " + " com_mng_population_community_tags as cmpct  "
         + " left join com_mng_population  as cmp on cmpct.population_id = cmp.id " + "WHERE " + " cmp.sex = 2  "
         + " AND cmpct.community_id = #{communityId}")
     List<IndexPopulationSexStatisticsVO> getScreenIndexByPopulationSex(@Param("communityId") Long communityId);
@@ -270,8 +271,8 @@
     List<ComMngUserTagVO> getUserTagListByCommunityId(@Param("communityId") Long communityId);
 
     @Select("SELECT " + " count( id )  " + "FROM " + " com_mng_population_community_tags  " + "WHERE "
-        + " label like concat('%',#{label},'%')")
-    Integer getSpecialStatisticsByLabel(@Param("label") String label);
+        + " community_id = #{communityId} and label like concat('%',#{label},'%')")
+    Integer getSpecialStatisticsByLabel(@Param("label") String label,@Param("communityId") Long communityId);
 
     @Select("<script> " + "SELECT " + " count( egd.id ) AS gridTotal, "
         + " ( SELECT count( user_id ) FROM sys_user WHERE community_id = egd.grid_community_id AND type = 6 ) AS gridMemberTotal, "
@@ -403,7 +404,7 @@
         + " AND create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> "
         + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != &quot;&quot;'>"
         + " AND create_at <![CDATA[<=]]> #{screenEventDTO.endTime} " + " </if> " + "), 0 ) AS eventSSPTotal, "
-        + " IFNULL(( SELECT count( id ) FROM com_act_easy_photo WHERE del_tag = 0 AND handle_status = 2 AND community_id = #{screenEventDTO.communityId} "
+        + " IFNULL(( SELECT count( id ) FROM com_act_easy_photo WHERE del_tag = 0 AND status = 4 AND community_id = #{screenEventDTO.communityId} "
         + "<if test='screenEventDTO.startTime != null and screenEventDTO.startTime != &quot;&quot;'>"
         + " AND create_at <![CDATA[>=]]> #{screenEventDTO.startTime} " + " </if> "
         + "<if test='screenEventDTO.endTime != null and screenEventDTO.endTime != &quot;&quot;'>"
@@ -506,15 +507,15 @@
     @Select("SELECT " + "(SELECT COUNT(aa.age) FROM "
         + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) as aa where aa.age<= 16) as age16,"
         + "(SELECT COUNT(aa.age) FROM "
-        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 27) AS age27,"
+        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id =  #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 27) AS age27,"
         + "(SELECT COUNT(aa.age) FROM "
-        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id  WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 27 and aa.age<= 35) AS age35,"
+        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 27 and aa.age<= 35) AS age35,"
         + "(SELECT COUNT(aa.age) FROM "
-        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id  WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45,"
+        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45,"
         + "(SELECT COUNT(aa.age) FROM "
-        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id  WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55,"
+        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55,"
         + "(SELECT COUNT(aa.age) FROM "
-        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population as cmp left join com_mng_population_community_tags as cmpct on cmp.id = cmpct.population_id  WHERE cmpct.community_id = #{communityId}) AS aa WHERE aa.age > 55) AS age55over")
+        + "(SELECT (SELECT  TIMESTAMPDIFF(YEAR, cmp.birthday, CURDATE()) ) AS age FROM com_mng_population_community_tags as capct left join com_mng_population as cmp on cmp.id = capct.population_id and capct.community_id = #{communityId}) AS aa WHERE aa.age > 55) AS age55over")
     Map<String, Long> indexCountByAge(@Param("communityId") Long communityId);
 
     @Select("SELECT " + " id, " + " su.nick_name AS userName, " + " su.image_url, " + " caep.create_at, "
@@ -569,7 +570,7 @@
         + " id = #{populationId}")
     PopulationDetailVO getPopulationDetailApp(@Param("populationId") Long populationId);
 
-    @Select("<script> " + "SELECT " + " *  " + "FROM " + " ( " + " SELECT " + " cmp.id, " + "  cmp.`name` AS userName, "
+    @Select("<script> " + " SELECT " + " cmp.id, " + "  cmp.`name` AS userName, "
         + "  cmpct.label, " + "  cmp.card_no, " + "  cmp.card_no_str, " + "  cmp.sex, " + "  cmp.address, "
         + "  cmp.political_outlook, " + "  cmp.census_register, " + "  cmp.house_id, " + "  cmp.phone, "
         + "  IFNULL(cmp.house_id,0) as isHouse, "
@@ -588,9 +589,7 @@
         + "AND cmp.house_id is null " + " </if> " + "<if test='populationListDTO.communityId != null'>"
         + "AND cmpct.community_id = #{populationListDTO.communityId} " + " </if> "
         + "<if test='populationListDTO.politicalOutlook != null'>"
-        + "AND cmp.political_outlook = #{populationListDTO.politicalOutlook} " + " </if> "
-        + " ) as population where 1=1 " + "<if test='populationListDTO.eventStatus != null'>"
-        + "AND population.eventStatus = #{populationListDTO.eventStatus} " + " </if> " + " </script>")
+        + "AND cmp.political_outlook = #{populationListDTO.politicalOutlook} " + " </if> " + " </script>")
     IPage<ComMngPopulationListVO> getGridPopulationAdminList(Page page,
         @Param("populationListDTO") ComMngPopulationListDTO populationListDTO);
 
@@ -616,18 +615,15 @@
         @Param("populationDTO") PageComMngPopulationDTO populationDTO);
 
     @Select("<script> " + "SELECT " + " count( id ) AS localNum, "
-        + " ( SELECT count( cmpct.id ) FROM com_mng_population_community_tags as cmpct left join com_mng_population as cmp on cmp.id = cmpct.population_id WHERE cmp.out_or_local = 2 "
-        + "<if test='communityId != null and communityId != 0'>" + " AND cmpct.community_id = #{communityId} "
-        + " </if> " + " ) AS flowNum, "
-        + " ( SELECT count( cmpct.id ) FROM com_mng_population_community_tags as cmpct left join com_mng_population as cmp on cmp.id = cmpct.population_id WHERE cmpct.label IS NOT NULL "
-        + "<if test='communityId != null and communityId != 0'>" + " AND cmpct.community_id = #{communityId} "
-        + " </if> " + " ) AS specialNum  " + "FROM "
-        + " com_mng_population_community_tags as cmpct left join com_mng_population as cmp on cmp.id = cmpct.population_id  "
-        + "WHERE " + " cmp.out_or_local = 1 " + "<if test='communityId != null and communityId != 0'>"
-        + " AND cmpct.community_id = #{communityId} " + " </if> " + " </script>")
+        + " ( SELECT count( cmp.id ) FROM com_mng_population as cmp WHERE cmp.out_or_local = 2 "
+        +  " ) AS flowNum, "
+        + " ( SELECT count( cmpct.id ) FROM com_mng_population as cmp left join com_mng_population_community_tags AS cmpct on cmp.id = cmpct.population_id WHERE cmpct.label IS NOT NULL "
+        + " ) AS specialNum  " + "FROM "
+        + " com_mng_population as cmp  "
+        + "WHERE " + " cmp.out_or_local = 1 " + " </script>")
     PopulationStatisticsVO getGridPopulationStatistics(@Param("communityId") Long communityId);
 
-    @Select("<script> " + "SELECT " + " *  " + "FROM " + " ( " + " SELECT " + " cmp.id, " + "  cmp.`name` AS userName, "
+    @Select("<script> " + " SELECT " + " cmp.id, " + "  cmp.`name` AS userName, "
         + "  cmpct.label, " + "  cmp.card_no, " + "  cmp.card_no_str, " + "  cmp.sex, " + "  cmp.address, "
         + "  cmp.political_outlook, " + "  cmp.census_register, " + "  cmp.house_id, " + "  cmp.phone, "
         + "  IFNULL(cmp.house_id,0) as isHouse, "
@@ -650,9 +646,7 @@
         + "AND cmp.house_id is null " + " </if> " + "<if test='populationExportDTO.communityId != null'>"
         + "AND cmpct.community_id = #{populationExportDTO.communityId} " + " </if> "
         + "<if test='populationExportDTO.politicalOutlook != null'>"
-        + "AND cmp.political_outlook = #{populationExportDTO.politicalOutlook} " + " </if> "
-        + " ) as population where 1=1 " + "<if test='populationExportDTO.eventStatus != null'>"
-        + "AND population.eventStatus = #{populationExportDTO.eventStatus} " + " </if> " + " </script>")
+        + "AND cmp.political_outlook = #{populationExportDTO.politicalOutlook} " + " </if> " + " </script>")
     List<ComMngPopulationListVO>
         getGridPopulationAdminLists(@Param("populationExportDTO") ComMngPopulationExportDTO populationExportDTO);
 

--
Gitblit v1.7.1