From 2bff00bae9369d70d29acde8fea1fd8192ada033 Mon Sep 17 00:00:00 2001
From: 罗元桥 <2376770955@qq.com>
Date: 星期三, 16 六月 2021 20:14:39 +0800
Subject: [PATCH] Merge branch 'test_screen' into 'master'
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java | 152 ++++++++++++++++++++++++++------------------------
1 files changed, 79 insertions(+), 73 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 5b4754e..05aadd9 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
@@ -36,29 +36,29 @@
public interface ComMngPopulationDAO extends BaseMapper<ComMngPopulationDO> {
@Select("<script> " +
- "SELECT\n" +
- "cmp.`name`,\n" +
- "cmp.id,\n" +
- "cmp.street_id,\n" +
- "cmp.act_id,\n" +
- "cmp.village_id,\n" +
- "cmp.sex,\n" +
- "cmp.age,\n" +
- "cmp.card_no,\n" +
- "cmp.road,\n" +
- "cmp.door_no,\n" +
- "cmp.floor,\n" +
- "cmp.unit_no,\n" +
- "cmp.house_no,\n" +
- "cmp.political_outlook,\n" +
- "cmp.work_company,\n" +
- "cmp.special_situation,\n" +
- "cmp.phone,\n" +
- "cmp.remark,\n" +
- "cmv.alley,\n" +
- "cmp.native_place,\n" +
- "cmp.nation \n" +
- "FROM \n" +
+ "SELECT " +
+ "cmp.`name`, " +
+ "cmp.id, " +
+ "cmp.street_id, " +
+ "cmp.act_id, " +
+ "cmp.village_id, " +
+ "cmp.sex, " +
+ "cmp.age, " +
+ "cmp.card_no, " +
+ "cmp.road, " +
+ "cmp.door_no, " +
+ "cmp.floor, " +
+ "cmp.unit_no, " +
+ "cmp.house_no, " +
+ "cmp.political_outlook, " +
+ "cmp.work_company, " +
+ "cmp.special_situation, " +
+ "cmp.phone, " +
+ "cmp.remark, " +
+ "cmv.alley, " +
+ "cmp.native_place, " +
+ "cmp.nation " +
+ "FROM " +
"com_mng_population AS cmp " +
"LEFT JOIN com_mng_village AS cmv ON cmv.village_id = cmp.village_id " +
"<where>" +
@@ -113,39 +113,39 @@
List<ComHouseMemberVo> listHouseMermberByUserId(@Param("houseId") Long houseId, @Param("populId") Long populId);
@Select("<script> " +
- "SELECT\n" +
- "cmp.`name`,\n" +
- "cmp.id,\n" +
- "cmp.street_id,\n" +
- "cmp.act_id,\n" +
- "cmp.village_id,\n" +
- "cmp.sex,\n" +
- "cmp.age,\n" +
- "cmp.card_no,\n" +
- "cmp.road,\n" +
- "cmp.door_no,\n" +
- "cmp.floor,\n" +
- "cmp.unit_no,\n" +
- "cmp.house_no,\n" +
- "cmp.political_outlook,\n" +
- "cmp.work_company,\n" +
- "cmp.special_situation,\n" +
- "cmp.phone,\n" +
- "cmp.remark,\n" +
- "cmv.alley,\n" +
- "cmp.native_place,\n" +
- "cmp.nation, \n" +
- "cmp.label, \n" +
- "cmp.relation, \n" +
- "cmp.marriage, \n" +
- "cmp.culture_level, \n" +
- "cmp.out_or_local, \n" +
- "cmp.census_register, \n" +
- "cmp.healthy, \n" +
- "cmp.birthday, \n" +
- "cmp.update_at, \n" +
- "cmp.is_rent \n" +
- "FROM \n" +
+ "SELECT " +
+ "cmp.`name`, " +
+ "cmp.id, " +
+ "cmp.street_id, " +
+ "cmp.act_id, " +
+ "cmp.village_id, " +
+ "cmp.sex, " +
+ "cmp.age, " +
+ "cmp.card_no, " +
+ "cmp.road, " +
+ "cmp.door_no, " +
+ "cmp.floor, " +
+ "cmp.unit_no, " +
+ "cmp.house_no, " +
+ "cmp.political_outlook, " +
+ "cmp.work_company, " +
+ "cmp.special_situation, " +
+ "cmp.phone, " +
+ "cmp.remark, " +
+ "cmv.alley, " +
+ "cmp.native_place, " +
+ "cmp.nation, " +
+ "cmp.label, " +
+ "cmp.relation, " +
+ "cmp.marriage, " +
+ "cmp.culture_level, " +
+ "cmp.out_or_local, " +
+ "cmp.census_register, " +
+ "cmp.healthy, " +
+ "cmp.birthday, " +
+ "cmp.update_at, " +
+ "cmp.is_rent " +
+ "FROM " +
"com_mng_population AS cmp " +
"LEFT JOIN com_mng_village AS cmv ON cmv.village_id = cmp.village_id " +
"<where>" +
@@ -305,18 +305,19 @@
@Select("SELECT " +
" count( e.id ) AS eventTFTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_deal_status = 4 AND grid_id = egd.id ),0) AS eventZATotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_deal_status = 4 AND grid_id = egd.id ),0) AS eventMDTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_deal_status = 4 AND grid_id = egd.id ),0) AS eventTSTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_deal_status = 4 AND grid_id = egd.id ),0) AS eventBWDTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_deal_status = 4 AND grid_id = egd.id ),0) AS eventGGTotal " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventZATotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventMDTotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventTSTotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventBWDTotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventGGTotal " +
"FROM " +
" `event` AS e " +
" LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id " +
"WHERE " +
" e.event_category = 1 " +
" AND e.event_type = 5 " +
- " AND e.event_deal_status = 4 " +
+ " AND e.event_deal_status in (1,2,3) " +
+ " AND e.event_status = 2 " +
" AND egd.grid_community_id = #{communityId}")
IndexGridEventStatisticsVO getGridEventStatisticsList(@Param("communityId") Long communityId);
@@ -325,17 +326,18 @@
@Select("SELECT " +
" count( e.id ) AS eventTFTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_deal_status = 4 AND grid_id = e.grid_id ),0) AS eventZATotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_deal_status = 4 AND grid_id = e.grid_id) ,0) AS eventMDTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_deal_status = 4 AND grid_id = e.grid_id ),0) AS eventTSTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_deal_status = 4 AND grid_id = e.grid_id ),0) AS eventBWDTotal, " +
- " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_deal_status = 4 AND grid_id = e.grid_id ),0) AS eventGGTotal " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventZATotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id) ,0) AS eventMDTotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventTSTotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventBWDTotal, " +
+ " IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventGGTotal " +
"FROM " +
" `event` AS e " +
"WHERE " +
" e.event_category = 1 " +
" AND e.event_type = 5 " +
- " AND e.event_deal_status = 4 " +
+ " AND e.event_deal_status in (1,2,3) " +
+ " AND e.event_status = 2 " +
" AND e.grid_id = #{gridId}")
IndexGridEventStatisticsVO getGridEventByGirdId(@Param("gridId") Long gridId);
@@ -622,8 +624,8 @@
List<EventGridStatisticsVO> getEventScreenGridData(@Param("communityId") Long communityId);
@Select("SELECT " +
- " event_type as type, " +
- " happent_lat_lng as latLng " +
+ " event_type AS type, " +
+ " happent_lat_lng AS latLng " +
"FROM " +
" `event` AS e " +
" LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id " +
@@ -632,7 +634,9 @@
" AND e.event_type IN ( 1, 2, 3, 4, 5, 6 ) " +
" AND e.event_status = 2 " +
" AND e.event_deal_status = 1 " +
- " AND egd.grid_community_id = #{communityId} UNION ALL " +
+ " AND egd.grid_community_id = #{communityId} " +
+ " AND e.happen_time BETWEEN date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ) " +
+ " AND date_add( curdate()- DAY ( curdate())+ 1, INTERVAL 1 MONTH ) UNION ALL " +
"SELECT " +
" IFNULL( NULL, 7 ) AS type, " +
" lng_lat AS latLng " +
@@ -642,7 +646,9 @@
" community_id = #{communityId} " +
" AND handle_status = 1 " +
" AND del_tag = 0 " +
- " AND lng_lat IS NOT NULL")
+ " AND lng_lat IS NOT NULL " +
+ " AND create_at BETWEEN date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ) " +
+ " AND date_add( curdate()- DAY ( curdate())+ 1, INTERVAL 1 MONTH )")
List<EventGridIncidentStatisticsVO> getEventScreenEventList(@Param("communityId") Long communityId);
@Select("SELECT " +
@@ -699,7 +705,7 @@
" FROM com_mng_population WHERE culture_level = 1 AND act_id = #{communityId}")
Map<String, Long> countByCulture(@Param("communityId")Long communityId);
- @Select("SELECT COUNT(p.id) FROM com_mng_population p INNER JOIN sys_user u ON p.card_no = u.id_card where p.act_id = #{communityId}")
+ @Select("select count(user_id) from sys_user where community_id = #{communityId} and type = 1")
Long countUsedCommunityPopulation(@Param("communityId")Long communityId);
}
--
Gitblit v1.7.1