springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/grid/admin/excel/GridMemberWorkExportExcelVO.java
New file @@ -0,0 +1,71 @@ package com.panzhihua.common.model.vos.grid.admin.excel; import com.alibaba.excel.annotation.ExcelProperty; import com.panzhihua.common.model.helper.encrypt.EncryptDecryptClass; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.io.Serializable; /** * 网格员工作汇总Excel导出 */ @Data @ApiModel("网格员工作汇总Excel导出") public class GridMemberWorkExportExcelVO implements Serializable { @ExcelProperty(value = "网格员名称" ,index = 0) private String nickName; @ExcelProperty(value = "发布突发事件" ,index = 1) private Integer addTFTotal; @ExcelProperty(value = "处理突发事件" ,index = 2) private Integer handleTFTotal; @ExcelProperty(value = "发布矛盾纠纷" ,index = 3) private Integer addMDTotal; @ExcelProperty(value = "处理矛盾纠纷" ,index = 4) private Integer handleMDTotal; @ExcelProperty(value = "发布治安隐患" ,index = 5) private Integer addZATotal; @ExcelProperty(value = "小区名字" ,index = 6) private Integer handleZATotal; @ExcelProperty(value = "发布不稳定因素" ,index = 7) private Integer addBWDTotal; @ExcelProperty(value = "处理不稳定因素" ,index = 8) private Integer handleBWDTotal; @ExcelProperty(value = "发布公共服务" ,index = 9) private Integer addGGTotal; @ExcelProperty(value = "处理公共服务" ,index = 10) private Integer handleGGTotal; @ExcelProperty(value = "发布特殊人群" ,index = 11) private Integer addTSTotal; @ExcelProperty(value = "处理特殊人群" ,index = 12) private Integer handleTSTotal; @ExcelProperty(value = "完成走访任务" ,index = 13) private Integer handleZFTotal; @ExcelProperty(value = "处理随手拍" ,index = 14) private Integer easyPhotoTotal; @ExcelProperty(value = "发布宣传教育" ,index = 15) private Integer propagandaEducationTotal; @ExcelProperty(value = "新增居民数" ,index = 16) private Integer addResidentTotal = 0; @ExcelProperty(value = "新增实有房屋数" ,index = 17) private Integer addHouseTotal = 0; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/grid/GridService.java
@@ -956,7 +956,7 @@ * @return 网格统计数据 */ @PostMapping("/statistics/getGridEventStatistics") R getGridEventStatisticsByApp(@RequestParam("gridId") Long gridId); R getGridEventStatisticsByApp(@RequestParam("gridId") Long gridId,@RequestParam("userId") Long userId); /** * 查询网格事件统计数据 @@ -1257,4 +1257,12 @@ */ @GetMapping("/screen/village/statistics") R civilVillageStatistics(@RequestParam("villageId") Long villageId); /** * 查询所有网格员工作汇总数据 * @param statisticsAdminDTO 请求参数 * @return 网格员工作汇总数据 */ @PostMapping("/statistics/admin/gridMemberStatisticsAll") R gridMemberStatisticsAll(@RequestBody MemberStatisticsAdminDTO statisticsAdminDTO); } springcloud_k8s_panzhihuazhihuishequ/grid_app/src/main/java/com/panzhihua/grid_app/api/StatisticsApi.java
@@ -2,6 +2,7 @@ import com.panzhihua.common.controller.BaseController; import com.panzhihua.common.model.dtos.grid.GridEventStatisticsDTO; import com.panzhihua.common.model.vos.LoginUserInfoVO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.grid.ComMapGridDetailVO; import com.panzhihua.common.model.vos.grid.GridEventStatisticsDetailVO; @@ -24,10 +25,14 @@ @ApiOperation(value = "根据网格id查询统计详情-lyq",response = GridEventStatisticsVO.class) @PostMapping("event") public R getGridDetail(@RequestParam("gridId") Long gridId){ LoginUserInfoVO loginUserInfo = this.getLoginUserInfo(); if(loginUserInfo == null){ return R.fail("请先登录"); } if(gridId == null){ return R.fail("参数错误"); } return gridService.getGridEventStatisticsByApp(gridId); return gridService.getGridEventStatisticsByApp(gridId,loginUserInfo.getUserId()); } @ApiOperation(value = "网格id查询统计事件详情-lyq",response = GridEventStatisticsDetailVO.class) springcloud_k8s_panzhihuazhihuishequ/grid_backstage/src/main/java/com/panzhihua/grid_backstage/api/StatisticsApi.java
@@ -1,6 +1,12 @@ package com.panzhihua.grid_backstage.api; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import com.alibaba.fastjson.JSON; import com.panzhihua.common.controller.BaseController; import com.panzhihua.common.excel.CustomSheetWriteHandler; import com.panzhihua.common.model.dtos.grid.MemberStatisticsAdminDTO; import com.panzhihua.common.model.dtos.grid.PageEventManageDTO; import com.panzhihua.common.model.vos.LoginUserInfoVO; @@ -8,15 +14,25 @@ import com.panzhihua.common.model.vos.grid.EventStatisticsAllAdminVO; import com.panzhihua.common.model.vos.grid.EventStatisticsMemberAdminVO; import com.panzhihua.common.model.vos.grid.EventVO; import com.panzhihua.common.model.vos.grid.admin.excel.ComMngVillageExportExcelVO; import com.panzhihua.common.model.vos.grid.admin.excel.GridMemberWorkExportExcelVO; import com.panzhihua.common.service.grid.GridService; import com.panzhihua.common.utlis.ClazzUtils; import com.panzhihua.common.utlis.SFTPUtil; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Value; import org.springframework.validation.annotation.Validated; import org.springframework.web.bind.annotation.*; import javax.annotation.Resource; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List; /** * 统计模块 @@ -26,6 +42,21 @@ @RequestMapping("/statistics/") @Api(tags = {"统计模块 @lyq"}) public class StatisticsApi extends BaseController { // FTP 登录用户名 @Value("${ftp.username}") private String userName; // FTP 登录密码 @Value("${ftp.password}") private String password; // FTP 服务器地址IP地址 @Value("${ftp.host}") private String host; // FTP 端口 @Value("${ftp.port}") private int port; @Value("${excel.userurl}") private String excelUrl; @Resource private GridService gridService; @@ -53,4 +84,68 @@ return gridService.gridMemberStatistics(statisticsAdminDTO); } @ApiOperation(value = "网格员统计导出") @PostMapping("/gridMemberStatistics/export") public R gridMemberStatisticsExport(@RequestBody MemberStatisticsAdminDTO statisticsAdminDTO){ // LoginUserInfoVO loginUserInfoVO = this.getLoginUserInfo(); // if(loginUserInfoVO == null){ // return R.fail("请先登录"); // } // gridService.gridMemberStatistics(statisticsAdminDTO); //生成动态模板excel通过ftp工具上传到主节点，然后返回模板下载地址 String ftpUrl = "/mnt/data/web/excel/"; String name = "网格员工作情况汇总数据.xlsx"; try { SFTPUtil sftp = new SFTPUtil(userName, password, host, port); sftp.login(); boolean existDir = sftp.isExistDir(ftpUrl + name); if (!existDir) { String property = System.getProperty("user.dir"); String fileName = property + File.separator + name; // 这里 需要指定写用哪个class去写 ExcelWriter excelWriter = null; InputStream inputStream = null; try { List<EventStatisticsMemberAdminVO> populList = JSON.parseArray(JSON.toJSONString(gridService.gridMemberStatistics(statisticsAdminDTO).getData()), EventStatisticsMemberAdminVO.class); List<GridMemberWorkExportExcelVO> workExportExcelList = new ArrayList<>(); if(!populList.isEmpty()){ populList.forEach(popul -> { GridMemberWorkExportExcelVO workExportExcelVO = new GridMemberWorkExportExcelVO(); BeanUtils.copyProperties(popul,workExportExcelVO); workExportExcelList.add(workExportExcelVO); }); } excelWriter = EasyExcel.write(fileName, GridMemberWorkExportExcelVO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(new CustomSheetWriteHandler()).build(); WriteSheet writeSheet = EasyExcel.writerSheet("网格员工作情况汇总数据").build(); excelWriter.write(workExportExcelList, writeSheet); excelWriter.finish(); File file = new File(fileName); inputStream = new FileInputStream(file); sftp.uploadMore(ftpUrl, name, inputStream); sftp.logout(); inputStream.close(); String absolutePath = file.getAbsolutePath(); boolean delete = file.delete(); log.info("删除excel【{}】结果【{}】", absolutePath, delete); } finally { // 千万别忘记finish 会帮忙关闭流 if (inputStream != null) { inputStream.close(); } if (excelWriter != null) { excelWriter.finish(); } } } return R.ok(excelUrl + name); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/CommunityApi.java
@@ -388,9 +388,9 @@ } } if(StringUtils.isEmpty(comActDynVO.getCover())){ comActDynVO.setCover(comActDynDO.getCover()); } // if(StringUtils.isEmpty(comActDynVO.getCover())){ // comActDynVO.setCover(comActDynDO.getCover()); // } BeanUtils.copyProperties(comActDynVO, comActDynDO); boolean update = comActDynService.updateById(comActDynDO); springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComActMessageDAO.java
@@ -56,7 +56,7 @@ " com_act_message t LEFT JOIN sys_user u ON t.user_id = u.user_id " + " where t.community_id=#{comActMessageVO.communityId}\n" + "<if test='comActMessageVO.userAccount != null and comActMessageVO.userAccount.trim() != ""'>" + " and t.user_account=#{comActMessageVO.userAccount} \n" + " and u.phone=#{comActMessageVO.userAccount} \n" + " </if> " + "<if test='comActMessageVO.userName != null and comActMessageVO.userName.trim() != ""'>" + " and t.user_name=#{comActMessageVO.userName} \n" + springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngPopulationDAO.java
@@ -1044,6 +1044,7 @@ " cmp.card_no, " + " cmp.label, " + " cmp.address, " + " cmp.culture_level, " + " cmp.nation, " + " cmp.political_outlook, " + " cmp.sex, " + springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/api/StatisticsApi.java
@@ -43,8 +43,8 @@ * @return 网格统计数据 */ @PostMapping("/getGridEventStatistics") public R getGridEventStatisticsByApp(@RequestParam("gridId") Long gridId){ return eventService.getGridEventStatisticsByApp(gridId); public R getGridEventStatisticsByApp(@RequestParam("gridId") Long gridId,@RequestParam("userId") Long userId){ return eventService.getGridEventStatisticsByApp(gridId,userId); } /** @@ -56,4 +56,14 @@ public R getGridEventDetailStatisticsByApp(@RequestBody GridEventStatisticsDTO statisticsDTO){ return eventService.getGridEventDetailStatisticsByApp(statisticsDTO); } /** * 查询所有网格员工作汇总数据 * @param statisticsAdminDTO 请求参数 * @return 网格员工作汇总数据 */ @PostMapping("/admin/gridMemberStatisticsAll") public R gridMemberStatisticsAll(@RequestBody MemberStatisticsAdminDTO statisticsAdminDTO){ return eventService.gridMemberStatisticsAll(statisticsAdminDTO); } } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/dao/EventGridMemberRelationMapper.java
@@ -68,4 +68,6 @@ List<EventMapGridCascadeVO> getMapGridsListsByApp(@Param("userId") Long userId, @Param("type") Integer type, @Param("communityId") Long communityId); List<EventStatisticsMemberAdminVO> gridMemberStatisticsAll(@Param("statisticsAdminDTO") MemberStatisticsAdminDTO statisticsAdminDTO); } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/dao/EventMapper.java
@@ -137,4 +137,8 @@ List<CivilVillageStatisticsVO> getCivilScreenVillageList(@Param("communityId") Long communityId); ScreenDrawEventPopulationTotalVO getVillagePopulationTotal(@Param("villageId") Long villageId); List<Map<String,Object>> getMemberBuildName(@Param("userId") Long userId); Integer getPopulationByLabelCount(@Param("floor") String floor,@Param("villageId") Long villageId); } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/service/EventService.java
@@ -229,7 +229,7 @@ * @param gridId 网格id * @return 网格统计数据 */ R getGridEventStatisticsByApp(Long gridId); R getGridEventStatisticsByApp(Long gridId,Long userId); /** * 查询网格事件统计数据 @@ -290,4 +290,11 @@ * @return 小区统计数据 */ R civilVillageStatistics(Long villageId); /** * 查询所有网格员工作汇总数据 * @param statisticsAdminDTO 请求参数 * @return 网格员工作汇总数据 */ R gridMemberStatisticsAll(MemberStatisticsAdminDTO statisticsAdminDTO); } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/java/com/panzhihua/service_grid/service/impl/EventServiceImpl.java
@@ -1927,11 +1927,24 @@ * @return 网格统计数据 */ @Override public R getGridEventStatisticsByApp(Long gridId){ public R getGridEventStatisticsByApp(Long gridId,Long userId){ GridEventStatisticsVO result = this.baseMapper.getGridEventStatisticsByApp(gridId); if(result != null){ result.setTodayNum(result.getTodayNum() + result.getZfTodayNum()); result.setMonthNum(result.getMonthNum() + result.getZfMonthNum()); //查询当前网格员下管理的楼栋名称 List<Map<String,Object>> buildNameList = this.baseMapper.getMemberBuildName(userId); Integer specialTotal = 0; if(!buildNameList.isEmpty()){ for (Map<String,Object> buildMap:buildNameList) { String buildName = buildMap.get("name").toString(); Long villageId = Long.parseLong(buildMap.get("village_id").toString()); Integer num = this.baseMapper.getPopulationByLabelCount(buildName,villageId); specialTotal += num; } } result.setSpecialTotal(specialTotal); } return R.ok(result); } @@ -2551,4 +2564,14 @@ result.setVillageTotal(1); return R.ok(result); } /** * 查询所有网格员工作汇总数据 * @param statisticsAdminDTO 请求参数 * @return 网格员工作汇总数据 */ @Override public R gridMemberStatisticsAll(MemberStatisticsAdminDTO statisticsAdminDTO){ return R.ok(eventGridMemberRelationMapper.gridMemberStatisticsAll(statisticsAdminDTO)); } } springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/resources/mapper/EventGridMemberRelationMapper.xml
@@ -384,4 +384,250 @@ </if> </select> <select id="gridMemberStatisticsAll" resultType="com.panzhihua.common.model.vos.grid.EventStatisticsMemberAdminVO" parameterType="com.panzhihua.common.model.dtos.grid.MemberStatisticsAdminDTO"> SELECT DISTINCT su.nick_name,( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 5 AND event_status = 2 AND grid_member_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS addTFTotal, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 5 AND event_status = 2 AND event_process_status = 2 AND process_user_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS handleTFTotal,( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_status = 2 AND grid_member_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS addMDTotal, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_status = 2 AND event_process_status = 2 AND process_user_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS handleMDTotal,( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_status = 2 AND grid_member_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS addZATotal, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_status = 2 AND event_process_status = 2 AND process_user_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS handleZATotal,( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_status = 2 AND grid_member_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS addBWDTotal, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_status = 2 AND event_process_status = 2 AND process_user_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS handleBWDTotal,( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_status = 2 AND grid_member_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS addGGTotal, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_status = 2 AND event_process_status = 2 AND process_user_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS handleGGTotal,( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND grid_member_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS addTSTotal, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND event_process_status = 2 AND process_user_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS handleTSTotal, ( SELECT count( id ) FROM event_visiting_tasks WHERE grid_member = su.user_id AND event_status = 2 <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(submit_date,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(submit_date,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS handleZFTotal, ( SELECT count( id ) FROM com_act_easy_photo WHERE handle_status = 2 AND handler_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(feedback_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(feedback_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS easyPhotoTotal, ( SELECT count( id ) FROM `event` WHERE event_category = 2 AND event_status = 2 AND grid_member_id = su.user_id <if test="statisticsAdminDTO.startTime!=null and statisticsAdminDTO.startTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{statisticsAdminDTO.startTime} </if> <if test="statisticsAdminDTO.endTime!=null and statisticsAdminDTO.endTime != """> AND DATE_FORMAT(create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{statisticsAdminDTO.endTime} </if> ) AS propagandaEducationTotal FROM sys_user AS su LEFT JOIN `event` AS e ON su.user_id = e.grid_member_id WHERE su.type = 6 <if test="statisticsAdminDTO.communityId!=null"> AND su.community_id = #{statisticsAdminDTO.communityId} </if> <if test="statisticsAdminDTO.nickName!=null and statisticsAdminDTO.nickName != """> AND su.nick_name like concat (#{statisticsAdminDTO.nickName},'%') </if> ORDER BY su.create_at desc </select> </mapper> springcloud_k8s_panzhihuazhihuishequ/service_grid/src/main/resources/mapper/EventMapper.xml
@@ -604,26 +604,6 @@ </if>) AS educationNum, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND event_process_status = 2 AND process_date BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-%d %H:%i:%s' ) AND NOW() <if test="gridId!=null"> AND grid_id = #{gridId} </if> ) AS todaySpecialTotal, ( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND event_process_status = 2 <if test="gridId!=null"> AND grid_id = #{gridId} </if>) AS specialTotal, ( SELECT count( cmph.id ) FROM com_mng_population_house AS cmph @@ -1351,4 +1331,18 @@ village_id = #{villageId} </select> <select id="getMemberBuildName" resultType="Map"> SELECT cmb.`name`,egmbr.village_id FROM event_grid_member_building_relation AS egmbr LEFT JOIN com_mng_building AS cmb ON cmb.id = egmbr.building_id WHERE egmbr.grid_member_id = #{userId} </select> <select id="getPopulationByLabelCount" resultType="Integer"> select count(id) from com_mng_population where label is not null and village_id = #{villageId} and floor = #{floor} </select> </mapper> springcloud_k8s_panzhihuazhihuishequ/service_partybuilding/src/main/java/com/panzhihua/service_dangjian/service/impl/ComPbMemberServiceImpl.java
@@ -141,8 +141,7 @@ List<ComPbMemberDO> comPbMemberDOS=new ArrayList<>(); if (!ObjectUtils.isEmpty(list)) { List<PartyOrganizationVO> partyOrganizationVOArrayList=partyOrganizationVOS; list.forEach(partyBuildingMemberExcelVO -> { for (PartyBuildingMemberExcelVO partyBuildingMemberExcelVO:list) { ComPbMemberDO comPbMemberDO=new ComPbMemberDO(); Date date=new Date(); comPbMemberDO.setUpdateAt(date); @@ -161,7 +160,12 @@ .eq(ComPbMemberDO::getCommunityId,partyOrganizationVO.getCommunityId()) .in(ComPbMemberDO::getAuditResult,0,1)); if(!comPbMemberDOList.isEmpty()){ throw new PartyBuildingMemberException("导入失败，您导入的数据中有已存在的党员信息,已存在的数据:"+ partyBuildingMemberExcelVO.getEmploymentTime() + "，已存在的人名：" + partyBuildingMemberExcelVO.getName()); comPbMemberDOList.forEach(comPbMember -> { BeanUtils.copyProperties(partyBuildingMemberExcelVO,comPbMember); this.baseMapper.updateById(comPbMember); }); continue; // throw new PartyBuildingMemberException("导入失败，您导入的数据中有已存在的党员信息,已存在的数据:"+ partyBuildingMemberExcelVO.getEmploymentTime() + "，已存在的人名：" + partyBuildingMemberExcelVO.getName()); } } @@ -190,7 +194,7 @@ comPbMemberDO.setName(partyBuildingMemberExcelVO.getName()); comPbMemberDO.setIdCard(partyBuildingMemberExcelVO.getIdCard()); comPbMemberDOS.add(comPbMemberDO); }); } } this.saveBatch(comPbMemberDOS); return R.ok();
