springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/screen/ComActPopulationScreenVO.java
@@ -1,5 +1,6 @@ package com.panzhihua.common.model.vos.screen; import com.panzhihua.common.model.vos.community.screen.civil.CivilVillageStatisticsVO; import com.panzhihua.common.model.vos.community.screen.event.EventGridStatisticsVO; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; @@ -51,4 +52,7 @@ @ApiModelProperty("大屏网格数据") private List<EventGridStatisticsVO> gridStatisticsList; @ApiModelProperty("小区列表数据") private List<CivilVillageStatisticsVO> villageStatisticsList; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java
@@ -3008,4 +3008,12 @@ */ @PostMapping("/disable_person/batch/save") R saveBatchDisabledPersons(@RequestBody List<DisabledPersonsDTO> list); /** * 获取社区网格 * @param communityId 社区id * @return 网格数据 */ @GetMapping("/screen/getScreenGirds") R getScreenGirds(@RequestParam("communityId")Long communityId); } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/BigScreenStatisticsApi.java
@@ -2,6 +2,7 @@ import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.screen.civil.CivilStatisticsVO; import com.panzhihua.common.model.vos.community.screen.event.EventGridStatisticsVO; import com.panzhihua.common.model.vos.community.screen.event.EventStatisticsVO; import com.panzhihua.common.model.vos.community.screen.index.IndexStatisticsVO; import com.panzhihua.common.service.community.CommunityService; @@ -56,4 +57,13 @@ } return communityService.getScreenCivil(communityId); } @ApiOperation(value = "获取社区网格接口@lyq",response = EventGridStatisticsVO.class) @GetMapping("/event/grids/noToken") public R grids(@RequestParam("communityId") Long communityId) { if(communityId == null){ return R.fail("参数错误"); } return communityService.getScreenGirds(communityId); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/BigScreenStatisticsApi.java
@@ -48,5 +48,10 @@ return R.ok(comMngPopulationService.getScreenCivil(communityId)); } @GetMapping("/getScreenGirds") public R grids(@RequestParam("communityId") Long communityId) { return R.ok(comMngPopulationService.getScreenGirds(communityId)); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/BigScreenDAO.java
@@ -36,7 +36,8 @@ "</if> " + " </where>" + " GROUP BY org.id " + " ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " + //" ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " + " ORDER BY total desc " + "</script>") IPage<BigScreenStatisticPartyOrg> partyOrgMembers(Page page, @Param("pageDTO") PageBigScreenStatisticPartyOrg pageBigScreenStatisticPartyOrg); @@ -92,75 +93,76 @@ BigScreenStatisticAgeGender gender(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO); @Select("<script> " + " SELECT \n" + " SUM(CASE TYPE WHEN 1 THEN total ELSE 0 END) as 'orgCount',\n" + " SUM(CASE TYPE WHEN 2 THEN total ELSE 0 END) as 'memberCount',\n" + " SUM(CASE TYPE WHEN 3 THEN total ELSE 0 END) as 'activityCount',\n" + " SUM(CASE TYPE WHEN 4 THEN total ELSE 0 END) as 'dynCount'\n" + " FROM (\n" + " SELECT 1 AS TYPE , COUNT(id) total\n" + " FROM com_pb_org org \n" + " WHERE org.community_id = #{communityId} AND STATUS=1\n" + " UNION ALL \n" + " SELECT 2 AS TYPE , COUNT(id) total\n" + " FROM com_pb_member \n" + " WHERE community_id = #{communityId} AND audit_result = 1\n" + " UNION ALL \n" + " SELECT 3 AS TYPE , COUNT(id) total\n" + " FROM com_pb_activity\n" + " WHERE community_id = #{communityId} AND STATUS IN (2,3,4,5)\n" + " UNION ALL \n" + " SELECT 4 AS TYPE , COUNT(id) total\n" + " FROM com_pb_dyn \n" + " WHERE community_id = #{communityId} AND STATUS = 2\n" + " SELECT " + " SUM(CASE TYPE WHEN 1 THEN total ELSE 0 END) as 'orgCount', " + " SUM(CASE TYPE WHEN 2 THEN total ELSE 0 END) as 'memberCount', " + " SUM(CASE TYPE WHEN 3 THEN total ELSE 0 END) as 'activityCount', " + " SUM(CASE TYPE WHEN 4 THEN total ELSE 0 END) as 'dynCount' " + " FROM ( " + " SELECT 1 AS TYPE , COUNT(id) total " + " FROM com_pb_org org " + " WHERE org.community_id = #{communityId} AND STATUS=1 " + " UNION ALL " + " SELECT 2 AS TYPE , COUNT(id) total " + " FROM com_pb_member " + " WHERE community_id = #{communityId} AND audit_result = 1 " + " UNION ALL " + " SELECT 3 AS TYPE , COUNT(id) total " + " FROM com_pb_activity " + " WHERE community_id = #{communityId} AND STATUS IN (2,3,4,5) " + " UNION ALL " + " SELECT 4 AS TYPE , COUNT(id) total " + " FROM com_pb_dyn " + " WHERE community_id = #{communityId} AND TYPE = 1 " + ") t" + "</script>") BigScreenStatisticPartyBuild partybuild(@Param("communityId") Long communityId); @Select("<script> " + " SELECT pm.id, pm.member_id, pm.user_id, pm.name, pm.photo_path, COUNT(pa.id) AS total \n" + " FROM com_pb_activity_member pm LEFT JOIN com_pb_activity pa ON pm.activity_id = pa.id \n" + " WHERE pa.community_id = #{communityId} \n" + " GROUP BY pm.member_id\n" + " ORDER BY total DESC\n" + " SELECT pm.id, pm.member_id, pm.user_id, pm.name, pm.photo_path, COUNT(pa.id) AS total " + " FROM com_pb_activity_member pm LEFT JOIN com_pb_activity pa ON pm.activity_id = pa.id " + " WHERE pa.community_id = #{communityId} " + " GROUP BY pm.member_id " + " ORDER BY total DESC " + " LIMIT 3 " + "</script>") List<PartyBuildingMemberVO> bigscreenPartyactivitytopuser(@Param("communityId")Long communityId); @Select("<script> " + " SELECT id AS member_id, user_id, NAME, photo_path, 0 AS total\n" + " FROM com_pb_member\n" + " WHERE community_id=#{communityId}\n" + " ORDER BY join_time DESC \n" + " SELECT id AS member_id, user_id, NAME, photo_path, 0 AS total " + " FROM com_pb_member " + " WHERE community_id=#{communityId} " + " ORDER BY join_time DESC " + " LIMIT 3 " + "</script>") List<PartyBuildingMemberVO> defaultPartyactivity(@Param("communityId")Long communityId); @Select("<script> " + " SELECT m.idxName, case when d.total IS NOT NULL then d.total ELSE 0 END AS total\n" + " FROM \n" + " (\n" + " SELECT\n" + " DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as idxName\n" + "FROM\n" + " ( \n" + " SELECT @cdate := date_add(now(), INTERVAL 1 MONTH )\n" + " FROM com_pb_activity LIMIT 12\n" + " )d\n" + " ORDER BY idxName\n" + ")m LEFT JOIN \n" + "(\n" + "SELECT \n" + " date_format(activity_time_begin, '%Y-%m') AS idxName,\n" + " COUNT(id) AS total\n" + " FROM com_pb_activity \n" + " WHERE \n" + " community_id=#{queryDTO.communityId} AND STATUS IN ( 3, 4, 5 ) AND \n" + " activity_time_begin < DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL 1 MONTH ), '%Y-%m-%d' ) AND\n" + " activity_time_begin > DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL - 12 MONTH ), '%Y-%m-%d') \n" + " GROUP BY idxName\n" + " ORDER BY idxName ASC \n" + " ) d ON m.idxName = d.idxName\n" + " SELECT m.idxName, case when d.total IS NOT NULL then d.total ELSE 0 END AS total " + " FROM " + " ( " + " SELECT " + " DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as idxName " + "FROM " + " ( " + " SELECT @cdate := date_add(now(), INTERVAL 1 MONTH ) " + " FROM com_pb_activity LIMIT 12 " + " )d " + " ORDER BY idxName " + ")m LEFT JOIN " + "( " + "SELECT " + " date_format(activity_time_begin, '%Y-%m') AS idxName, " + " COUNT(id) AS total " + " FROM com_pb_activity " + " WHERE " + " community_id=#{queryDTO.communityId} AND STATUS IN ( 3, 4, 5 ) AND " + " activity_time_begin < DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL 1 MONTH ), '%Y-%m-%d' ) AND " + " activity_time_begin > DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL - 12 MONTH ), '%Y-%m-%d') " + " GROUP BY idxName " + " ORDER BY idxName ASC " + " ) d ON m.idxName = d.idxName " + " WHERE m.idxName > '2020-12-31' " + " ORDER BY m.idxName " + "</script>") List<BigScreenStatisticPartyActivity> partyactivity(@Param("queryDTO")BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO); springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
@@ -284,7 +284,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); @@ -708,4 +708,20 @@ @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); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComMngPopulationService.java
@@ -203,4 +203,6 @@ * @return */ R screenStatistic(Long communityId); R getScreenGirds(Long communityId); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComMngPopulationServiceImpl.java
@@ -801,9 +801,7 @@ List<IndexPopulationSexStatisticsVO> sexPopulationList = comMngPopulationDAO.getScreenIndexByPopulationSex(communityId); populationStatisticsVO.setSexPopulationList(sexPopulationList); //查询实有人口年龄统计 List<ComMngPopulationDO> populationDOList = comMngPopulationDAO.selectList(new QueryWrapper<ComMngPopulationDO>().lambda() .eq(ComMngPopulationDO::getActId,communityId)); List<IndexPopulationAgeStatisticsVO> agePopulationList = statisticsAge(populationDOList); List<IndexPopulationAgeStatisticsVO> agePopulationList = statisticsAge(communityId); populationStatisticsVO.setAgePopulationList(agePopulationList); statisticsVO.setPopulationStatisticsVO(populationStatisticsVO); @@ -872,7 +870,8 @@ return R.ok(statisticsVO); } private List<IndexPopulationAgeStatisticsVO> statisticsAge(List<ComMngPopulationDO> populationDOList){ private List<IndexPopulationAgeStatisticsVO> statisticsAge(Long communityId){ Map<String,Long> ageMap = populationDAO.indexCountByAge(communityId); List<IndexPopulationAgeStatisticsVO> agePopulationList = new ArrayList<>(); IndexPopulationAgeStatisticsVO ageStatisticsVO1 = new IndexPopulationAgeStatisticsVO(); IndexPopulationAgeStatisticsVO ageStatisticsVO2 = new IndexPopulationAgeStatisticsVO(); @@ -887,7 +886,7 @@ ageStatisticsVO5.setType(5); ageStatisticsVO6.setType(6); if(populationDOList.isEmpty()){ if(ageMap.isEmpty()){ ageStatisticsVO1.setSum(0); ageStatisticsVO2.setSum(0); ageStatisticsVO3.setSum(0); @@ -895,23 +894,14 @@ ageStatisticsVO5.setSum(0); ageStatisticsVO6.setSum(0); }else{ populationDOList.forEach(population -> { Integer age = IdcardUtil.getAgeByIdCard(population.getCardNoStr()); if(age >= 0 && age < 16){ ageStatisticsVO1.setSum(ageStatisticsVO1.getSum() + 1); }else if(age >= 16 && age < 27){ ageStatisticsVO2.setSum(ageStatisticsVO2.getSum() + 1); }else if(age >= 27 && age < 35){ ageStatisticsVO3.setSum(ageStatisticsVO3.getSum() + 1); }else if(age >= 35 && age < 45){ ageStatisticsVO4.setSum(ageStatisticsVO4.getSum() + 1); }else if(age >= 45 && age < 55){ ageStatisticsVO5.setSum(ageStatisticsVO5.getSum() + 1); }else if(age >= 55){ ageStatisticsVO6.setSum(ageStatisticsVO6.getSum() + 1); } }); ageStatisticsVO1.setSum(ageMap.get("age16").intValue()); ageStatisticsVO2.setSum(ageMap.get("age27").intValue()); ageStatisticsVO3.setSum(ageMap.get("age35").intValue()); ageStatisticsVO4.setSum(ageMap.get("age45").intValue()); ageStatisticsVO5.setSum(ageMap.get("age55").intValue()); ageStatisticsVO6.setSum(ageMap.get("age55over").intValue()); } agePopulationList.add(ageStatisticsVO1); agePopulationList.add(ageStatisticsVO2); agePopulationList.add(ageStatisticsVO3); @@ -1060,9 +1050,6 @@ return R.ok(civilStatisticsVO); } @Override public R editTagPopulationByCardNo(ComMngPopulationTagCardNoDTO comMngPopulationTagCardNoDTO) { ComMngPopulationDO comMngPopulationDO = getPopulationByCardNo(comMngPopulationTagCardNoDTO.getCardNo()); @@ -1128,6 +1115,10 @@ //查询网格数据 List<EventGridStatisticsVO> gridStatisticsList = this.baseMapper.getEventScreenGridData(communityId); comActPopulationScreenVO.setGridStatisticsList(gridStatisticsList); //查询小区列表 List<CivilVillageStatisticsVO> villageStatisticsList = this.baseMapper.getCivilScreenVillageList(communityId); comActPopulationScreenVO.setVillageStatisticsList(villageStatisticsList); return R.ok(comActPopulationScreenVO); } @@ -1215,6 +1206,10 @@ comActPopulationScreenVO.setAgeGroup(ageList); } public R getScreenGirds(Long communityId){ return R.ok(this.baseMapper.getEventScreenGridData(communityId)); } public static void main(String[] args) { // List<ComMngPopulationHouseUserDO> distinctClass = populationHouseUserDOList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> o.getPopulId() + ";" + o.getHouseId() + ";" + o.getId() + ";" + o.getRelation()))), ArrayList::new)); } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/model/dos/EventDO.java
@@ -220,7 +220,7 @@ /** * 撤销人/社区ID 列: revoke_id */ private Long revokeID; private Long revokeId; /** * 修改时间 列: revoke_type */ springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/service/impl/EventServiceImpl.java
@@ -516,7 +516,7 @@ eventDO.setProcessType(1);//网格员处理 eventDO.setCommunityProcess(0);//非 社区处理 eventDO.setRevokeType(eventRevokeDTO.getRevokeType()); eventDO.setRevokeID(eventRevokeDTO.getRevokeId()); eventDO.setRevokeId(eventRevokeDTO.getRevokeId()); int updated = this.getBaseMapper().updateById(eventDO); if (updated == 1) { addTransferRecord(eventDO.getId(), eventRevokeDTO.getUserId(), eventRevokeDTO.getUserName(), @@ -1283,7 +1283,7 @@ eventDO.setEventStatus(4);//4、撤销 eventDO.setProcessType(1);//网格员处理 eventDO.setCommunityProcess(0);//非 社区处理 eventDO.setRevokeID(eventRevokeDTO.getRevokeId()); eventDO.setRevokeId(eventRevokeDTO.getRevokeId()); eventDO.setRevokeType(eventRevokeDTO.getRevokeType()); int updated = this.getBaseMapper().updateById(eventDO); if (updated == 1) {
