From c6512655e722d9ca80dd8c34b79f6d3923ecf86c Mon Sep 17 00:00:00 2001
From: 罗元桥 <2376770955@qq.com>
Date: 星期二, 22 六月 2021 16:07:50 +0800
Subject: [PATCH] Merge branch 'test' into 'test_screen'
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java | 231 ++++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 209 insertions(+), 22 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 df824d0..9056f56 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
@@ -6,6 +6,7 @@
import com.panzhihua.common.model.dtos.community.ComMngPopulationDTO;
import com.panzhihua.common.model.dtos.user.PageInputUserDTO;
import com.panzhihua.common.model.vos.area.AreaAddressVO;
+import com.panzhihua.common.model.vos.community.ComActMessageVO;
import com.panzhihua.common.model.vos.community.ComMngPopulationTotalVO;
import com.panzhihua.common.model.vos.community.ComMngPopulationVO;
import com.panzhihua.common.model.vos.community.screen.civil.CivilPopulationStatisticsVO;
@@ -14,6 +15,11 @@
import com.panzhihua.common.model.vos.community.screen.index.*;
import com.panzhihua.common.model.vos.grid.EventGridDataVO;
import com.panzhihua.common.model.vos.user.*;
+import com.panzhihua.common.model.vos.community.PageComActMessageVO;
+import com.panzhihua.common.model.vos.user.ComHouseMemberVo;
+import com.panzhihua.common.model.vos.user.ComMngFamilyInfoVO;
+import com.panzhihua.common.model.vos.user.InputUserInfoVO;
+import com.panzhihua.common.model.vos.user.UserElectronicFileVO;
import com.panzhihua.service_community.model.dos.ComMngPopulationDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@@ -21,6 +27,7 @@
import java.util.Date;
import java.util.List;
+import java.util.Map;
/**
* 实有人口Dao
@@ -237,6 +244,7 @@
",(select count(id) from com_mng_population where act_id = #{communityId} and out_or_local = 1) as localTotal " +
",(select count(id) from com_mng_population where act_id = #{communityId} and out_or_local = 2) as outTotal " +
",(select count(id) from com_mng_population where act_id = #{communityId} and label is not null) as specialTotal " +
+ ",(select count(village_id) from com_mng_village where community_id = #{communityId}) as villageTotal " +
" from com_mng_population as cmp where act_id = #{communityId}")
ComMngPopulationTotalVO getPopulationTotalByAdmin(@Param("communityId") Long communityId);
@@ -267,7 +275,7 @@
List<IndexPopulationSexStatisticsVO> getScreenIndexByPopulationSex(@Param("communityId") Long communityId);
@Select("SELECT " +
- " e.create_at, " +
+ " e.happen_time as createAt, " +
" e.event_des, " +
" e.event_deal_status " +
"FROM " +
@@ -277,7 +285,7 @@
" egd.grid_community_id = #{communityId} " +
" AND e.event_deal_status IN ( 1, 4 ) " +
"ORDER BY " +
- " e.create_at DESC " +
+ " e.happen_time DESC " +
" LIMIT 8")
List<IndexEventListStatisticsVO> getScreenIndexByEventList(@Param("communityId") Long communityId);
@@ -298,18 +306,83 @@
@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( e1.id ) " +
+ " FROM " +
+ " `event` AS e1 " +
+ " LEFT JOIN event_grid_data AS egd1 ON egd1.id = e1.grid_id " +
+ " WHERE " +
+ " event_category = 1 " +
+ " AND event_type = 1 " +
+ " AND event_status = 2 " +
+ " AND egd1.grid_community_id = #{communityId} " +
+ " ), " +
+ " 0 " +
+ " ) AS eventZATotal, " +
+ " IFNULL(( " +
+ " SELECT " +
+ " count( e2.id ) " +
+ " FROM " +
+ " `event` AS e2 " +
+ " LEFT JOIN event_grid_data AS egd2 ON egd2.id = e2.grid_id " +
+ " WHERE " +
+ " event_category = 1 " +
+ " AND event_type = 3 " +
+ " AND event_status = 2 " +
+ " AND egd2.grid_community_id = #{communityId} " +
+ " ), " +
+ " 0 " +
+ " ) AS eventMDTotal, " +
+ " IFNULL(( " +
+ " SELECT " +
+ " count( e3.id ) " +
+ " FROM " +
+ " `event` AS e3 " +
+ " LEFT JOIN event_grid_data AS egd3 ON egd3.id = e3.grid_id " +
+ " WHERE " +
+ " event_category = 1 " +
+ " AND event_type = 6 " +
+ " AND event_status = 2 " +
+ " AND egd3.grid_community_id = #{communityId} " +
+ " ), " +
+ " 0 " +
+ " ) AS eventTSTotal, " +
+ " IFNULL(( " +
+ " SELECT " +
+ " count( e4.id ) " +
+ " FROM " +
+ " `event` AS e4 " +
+ " LEFT JOIN event_grid_data AS egd4 ON egd4.id = e4.grid_id " +
+ " WHERE " +
+ " event_category = 1 " +
+ " AND event_type = 4 " +
+ " AND event_status = 2 " +
+ " AND egd4.grid_community_id = #{communityId} " +
+ " ), " +
+ " 0 " +
+ " ) AS eventBWDTotal, " +
+ " IFNULL(( " +
+ " SELECT " +
+ " count( e5.id ) " +
+ " FROM " +
+ " `event` AS e5 " +
+ " LEFT JOIN event_grid_data AS egd5 ON egd5.id = e5.grid_id " +
+ " WHERE " +
+ " event_category = 1 " +
+ " AND event_type = 2 " +
+ " AND event_status = 2 " +
+ " AND egd5.grid_community_id = #{communityId} " +
+ " ), " +
+ " 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_status = 2 " +
" AND egd.grid_community_id = #{communityId}")
IndexGridEventStatisticsVO getGridEventStatisticsList(@Param("communityId") Long communityId);
@@ -318,17 +391,17 @@
@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 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 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 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 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 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_status = 2 " +
" AND e.grid_id = #{gridId}")
IndexGridEventStatisticsVO getGridEventByGirdId(@Param("gridId") Long gridId);
@@ -577,7 +650,7 @@
@Select("SELECT " +
" su.nick_name AS userName, " +
" su.image_url AS imageUrl, " +
- " e.create_at, " +
+ " e.happen_time as createAt, " +
" e.event_des, " +
" e.danger_level, " +
" e.urgent, " +
@@ -597,7 +670,7 @@
" AND e.event_deal_status = 1 " +
" AND e.event_category = 1 " +
"ORDER BY " +
- " e.create_at DESC " +
+ " e.happen_time DESC " +
" LIMIT 1")
EventNewStatisticsVO getEventScreenRightTop(@Param("communityId") Long communityId);
@@ -615,8 +688,9 @@
List<EventGridStatisticsVO> getEventScreenGridData(@Param("communityId") Long communityId);
@Select("SELECT " +
- " event_type as type, " +
- " happent_lat_lng as latLng " +
+ " event_type AS type, " +
+ " e.id AS eventId, " +
+ " happent_lat_lng AS latLng " +
"FROM " +
" `event` AS e " +
" LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id " +
@@ -624,18 +698,21 @@
" e.event_category = 1 " +
" 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, " +
+ " id AS eventId, " +
" lng_lat AS latLng " +
"FROM " +
" com_act_easy_photo " +
"WHERE " +
" 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 " +
@@ -660,5 +737,115 @@
" community_id = #{communityId}")
List<CivilVillageStatisticsVO> getCivilScreenVillageList(@Param("communityId") Long communityId);
+ @Select("SELECT " +
+ " count( id ) AS peopleNum, " +
+ " ( SELECT count( id ) FROM com_mng_population_house AS cmph WHERE village_id = #{villageId} ) AS houseNum, " +
+ " ( SELECT count( id ) FROM com_mng_population WHERE village_id = #{villageId} AND out_or_local = 1 ) AS registerNum, " +
+ " ( SELECT count( id ) FROM com_mng_population WHERE village_id = #{villageId} AND out_or_local = 2 ) AS flowNum " +
+ "FROM " +
+ " com_mng_population AS cmp " +
+ "WHERE " +
+ " village_id = #{villageId}")
+ CivilVillageStatisticsVO getCivilScreenVillageStatistics(@Param("villageId") Long villageId);
+
+
+ @Select("SELECT COUNT(id) AS man,(SELECT COUNT(id) FROM com_mng_population WHERE sex = 2 AND act_id = #{communityId}) AS woman FROM com_mng_population WHERE sex = 1 AND act_id = #{communityId}")
+ Map<String, Long> countBySex(@Param("communityId") Long communityId);
+
+ @Select(
+ "SELECT " +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) as aa where aa.age<= 16) as age16," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 25) AS age25," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 25 and aa.age<= 35) AS age35," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 55) AS age55over")
+ Map<String, Long> countByAge(@Param("communityId")Long communityId);
+
+ @Select("SELECT COUNT(id)AS xx," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 2 AND act_id = #{communityId}) as cz," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 3 AND act_id = #{communityId}) as gz," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 4 AND act_id = #{communityId}) as zz," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 5 AND act_id = #{communityId}) as dz," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 6 AND act_id = #{communityId}) as bk," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 7 AND act_id = #{communityId}) as ss," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 8 AND act_id = #{communityId}) as bs," +
+ "(SELECT COUNT(id) FROM com_mng_population WHERE culture_level = 9 AND act_id = #{communityId}) as qt" +
+ " FROM com_mng_population WHERE culture_level = 1 AND act_id = #{communityId}")
+ Map<String, Long> countByCulture(@Param("communityId")Long communityId);
+
+ @Select("select count(user_id) from sys_user where community_id = #{communityId} and type = 1")
+ Long countUsedCommunityPopulation(@Param("communityId")Long communityId);
+
+ @Select(
+ "SELECT " +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) as aa where aa.age<= 16) as age16," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 27) AS age27," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 27 and aa.age<= 35) AS age35," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55," +
+ "(SELECT COUNT(aa.age) FROM " +
+ "(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_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, " +
+ " caep.detail AS eventDes, " +
+ " caep.happen_addr AS happenAddress, " +
+ " caep.lng_lat AS happentLatLng, " +
+ " caep.photo_path_list, " +
+ " IFNULL( NULL, 7 ) AS eventType, " +
+ " caep.handle_status AS eventDealStatus " +
+ "FROM " +
+ " com_act_easy_photo AS caep " +
+ " LEFT JOIN sys_user AS su ON su.user_id = caep.sponsor_id " +
+ "WHERE " +
+ " id = #{eventId}")
+ EventNewStatisticsVO getEventScreenSSPDateil(@Param("eventId")Long eventId);
+
+ @Select("SELECT " +
+ " su.nick_name AS userName, " +
+ " su.image_url AS imageUrl, " +
+ " e.create_at as createAt, " +
+ " e.event_des, " +
+ " e.event_clazz, " +
+ " e.danger_level, " +
+ " e.urgent, " +
+ " e.major, " +
+ " e.happen_address, " +
+ " e.happent_lat_lng, " +
+ " e.event_type, " +
+ " e.event_category, " +
+ " e.id, " +
+ " egd.grid_name, " +
+ " e.event_deal_status " +
+ "FROM " +
+ " `event` AS e " +
+ " LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id " +
+ " LEFT JOIN sys_user AS su ON su.user_id = e.grid_member_id " +
+ "WHERE " +
+ " e.id = #{eventId}")
+ EventNewStatisticsVO getEventScreenEventDetail(@Param("eventId") Long eventId);
+
+ @Select("select process_date,process_result from event_transfer_record where event_id = #{eventId}")
+ List<EventTransferRecordVO> getEventScreenEventTransList(@Param("eventId") Long eventId);
+
+ @Select("select count(id) from com_elders_auth_elderly WHERE community_id = #{communityId}")
+ Integer getStatisticsCount(@Param("communityId") Long communityId);
}
--
Gitblit v1.7.1