springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/community/ExportSpecialUserDTO.java
New file @@ -0,0 +1,24 @@ package com.panzhihua.common.model.dtos.community; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; /** * @program: springcloud_k8s_panzhihuazhihuishequ * @description: 特殊群体excel导出查询参数 * @author: huang.hongfa weixin hhf9596 qq 959656820 * @create: 2021-02-20 10:27 **/ @Data @ApiModel("特殊群体excel导出查询参数") public class ExportSpecialUserDTO { @ApiModelProperty(value = "姓名") private String name; @ApiModelProperty(value = "小区名字") private String areaName; @ApiModelProperty(value = "社区id",hidden = true) private Long communityId; @ApiModelProperty("标签") private String tags; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/user/EexcelUserDTO.java
@@ -18,6 +18,10 @@ */ @ExcelProperty("序号") private String order; @ExcelProperty("小区") private String areaName; /** * 门牌号 */ @@ -128,6 +132,10 @@ */ @ExcelProperty("重点人员") private String keyPersonnel; @ExcelProperty("标签") private String tags; /** * 房屋编码 */ springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/user/InputUserInfoVO.java
@@ -98,6 +98,9 @@ @ApiModelProperty("小区名称") private String areaName; @ApiModelProperty("详细地址") private String address; @ApiModelProperty("创建时间") @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8") private Date createAt; springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/user/UserService.java
@@ -1,6 +1,7 @@ package com.panzhihua.common.service.user; import com.panzhihua.common.model.dtos.PageDTO; import com.panzhihua.common.model.dtos.community.ExportSpecialUserDTO; import com.panzhihua.common.model.dtos.community.ExportUserDTO; import com.panzhihua.common.model.dtos.community.NoticeReadDTO; import com.panzhihua.common.model.dtos.user.*; @@ -613,4 +614,19 @@ @PostMapping("common/data/special/tags/save") R saveSpecialInputUserTags(@RequestBody ComMngTagVO comMngTagVO); /** * 删除特殊群体标签 * @param id * @return */ @DeleteMapping("common/data/special/tags/delete") R deleteSpecialInputUserTags(@RequestParam(value = "id") Long id); /** * 特殊群体导出 * @param exportSpecialUserDTO * @return */ @PostMapping("/common/data/special/export") R specialUserExport(@RequestBody ExportSpecialUserDTO exportSpecialUserDTO); } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommonDataApi.java
@@ -336,6 +336,60 @@ return userService.deleteSpecialInputUser(id); } @ApiOperation(value = "特殊群体 导出特殊群体Excel" ) @PostMapping("/special/export") public R exportSpecial(@RequestBody ExportSpecialUserDTO exportSpecialUserDTO){ exportSpecialUserDTO.setCommunityId(this.getCommunityId()); String url=excelUrl; String uuid= UUID.randomUUID().toString().replace("-",""); String name=uuid+".xlsx"; String ftpUrl="/mnt/data/web/excel/"; // 用户搜索了就下载搜索的用户否则下载所有用户 R r=userService.specialUserExport(exportSpecialUserDTO); if (R.isOk(r)) { List<EexcelUserDTO>eexcelUserDTOS= JSONArray.parseArray(JSONArray.toJSONString(r.getData()),EexcelUserDTO.class); 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 { excelWriter = EasyExcel.write(fileName, EexcelUserDTO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomSheetWriteHandler()).build(); WriteSheet writeSheet = EasyExcel.writerSheet("模板").build(); excelWriter.write(eexcelUserDTOS, 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(url+name); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } return R.fail("未查询到用户"); } @ApiOperation(value = "特殊群体/分页查询标签列表",response = ComMngTagVO.class ) @PostMapping("/special/tags/page") @@ -358,6 +412,16 @@ return userService.saveSpecialInputUserTags(comMngTagVO); } /** * 删除特殊群体标签 * @param id * @return */ @DeleteMapping("/special/tags/delete") R deleteSpecialInputUserTags(@RequestParam(value = "id") Long id) { return userService.deleteSpecialInputUserTags(id); } @ApiOperation(value = "分页查询单位管理列表",response = ComMngRealCompanyVO.class) @PostMapping("/company/page") public R pageQueryComMngRealCompany(@RequestBody PageComMngRealCompanyDTO pageComMngRealCompanyDTO) { springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/api/UserApi.java
@@ -1,6 +1,7 @@ package com.panzhihua.service_user.api; import com.panzhihua.common.model.dtos.PageDTO; import com.panzhihua.common.model.dtos.community.ExportSpecialUserDTO; import com.panzhihua.common.model.dtos.community.ExportUserDTO; import com.panzhihua.common.model.dtos.community.NoticeReadDTO; import com.panzhihua.common.model.dtos.user.*; @@ -605,7 +606,7 @@ * @param pageInputUserDTO 查询参数 * @return 分页数据 */ @PostMapping("/common/data/user/page") @PostMapping("common/data/user/page") public R pageInputUser(@RequestBody PageInputUserDTO pageInputUserDTO){ return sysUserInputService.pageInputUser(pageInputUserDTO); } @@ -615,7 +616,7 @@ * @param inputUserTagsDTO 标签信息 * @return 设置结果 */ @PostMapping("/common/data/user/tags") @PostMapping("common/data/user/tags") public R setInputUserTag(@RequestBody InputUserTagsDTO inputUserTagsDTO){ return sysUserInputService.putUserTag(inputUserTagsDTO); } @@ -626,7 +627,7 @@ * @param areaName 小区名字 * @return 导入结果 */ @PostMapping("/common/data/user/import") @PostMapping("common/data/user/import") public R batchSaveInputUser(@RequestBody List<EexcelUserDTO> list,@RequestParam("areaName")StringBuffer areaName){ R r = null; try { @@ -651,7 +652,7 @@ * @param id 人员id * @return CommunityUserInfoVO */ @PostMapping("/common/data/user/detail") @PostMapping("common/data/user/detail") public R inputUserDetail(@RequestParam("id")Long id){ return sysUserInputService.inputUserDetail(id); } @@ -661,7 +662,7 @@ * @param exportUserDTO 用户搜索内容 * @return List<EexcelUserDTO> excel内容 */ @PostMapping("/common/data/user/export") @PostMapping("common/data/user/export") public R inputUserExport(@RequestBody ExportUserDTO exportUserDTO){ return sysUserInputService.exportInputUser(exportUserDTO); } @@ -704,4 +705,24 @@ R saveSpecialInputUserTags(@RequestBody ComMngTagVO comMngTagVO) { return sysUserInputService.saveSpecialInputUserTags(comMngTagVO); } /** * 删除特殊群体标签 * @param id * @return */ @DeleteMapping("common/data/special/tags/delete") R deleteSpecialInputUserTags(@RequestParam(value = "id") Long id) { return sysUserInputService.deleteSpecialInputUserTags(id); } /** * 特殊群体导出 * @param exportSpecialUserDTO * @return */ @PostMapping("common/data/special/export") R specialUserExport(@RequestBody ExportSpecialUserDTO exportSpecialUserDTO) { return sysUserInputService.specialUserExport(exportSpecialUserDTO); } } springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/dao/SysUserInputDAO.java
@@ -3,6 +3,7 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.panzhihua.common.model.dtos.community.ExportSpecialUserDTO; import com.panzhihua.common.model.dtos.community.ExportUserDTO; import com.panzhihua.common.model.dtos.user.EexcelUserDTO; import com.panzhihua.common.model.dtos.user.PageInputUserDTO; @@ -63,6 +64,7 @@ @Select("<script> " + "SELECT " + " t.id, " + " t.`name`, " + "CASE " + " IF " + @@ -119,22 +121,10 @@ @Select("select a.id,a.community_id,a.area_name,address_detail from com_mng_struct_area a where a.community_id=#{communityId}") List<ComMngStructAreaVO> selectComMngStructArea(@Param("communityId") Long communityId); @Select("" + "SELECT " + " t1.house_code houseCode, " + " t1.house_name houseName, " + " t1.square, " + " t1.state " + "FROM " + " sys_user_input t " + " LEFT JOIN com_mng_struct_house t1 ON t.house_code = t1.house_code " + " WHERE" + " t.id_card = #{idCard}") List<ComMngStructHouseVO> selectUserHouseList(@Param("idCard") String idCard); @Select("<script> " + "SELECT * FROM ( "+ " SELECT " + " t.id, " + " t.`name`, " + "CASE " + " IF " + @@ -170,10 +160,12 @@ " ( t.key_personnel = 1, '重点人员,', '' ), " + " IFNULL(t.tags,'') " + " ) tags, " + " t2.house_name as address, " + " t.create_at as createAt " + "FROM " + " sys_user_input t " + " LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id " + " LEFT JOIN com_mng_struct_house t2 ON t.house_code = t2.house_code " + "WHERE " + " t.community_id = #{pageInputUserDTO.communityId}"+ " AND " + @@ -195,9 +187,23 @@ "</script>") IPage<InputUserInfoVO> specialInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO); @Select("" + "SELECT " + " t1.house_code houseCode, " + " t1.house_name houseName, " + " t1.square, " + " t1.state " + "FROM " + " sys_user_input t " + " LEFT JOIN com_mng_struct_house t1 ON t.house_code = t1.house_code " + " WHERE" + " t.id_card = #{idCard}") List<ComMngStructHouseVO> selectUserHouseList(@Param("idCard") String idCard); @Select("<script> " + "SELECT " + "u.id 'order', " + "c.`area_name` areaName, "+ "u.door_number doorNumber, " + "if(u.is_rent=1,'租住','自主')isRent, " + "u.name, " + @@ -220,9 +226,10 @@ "if(u.old_people=1,'是','否') oldPeople, " + "if(u.special_service_family=1,'是','否') specialServiceFamily, " + "if(u.key_personnel=1,'是','否') keyPersonnel " + "u.tags " + "FROM " + " sys_user_input u " + " LEFT JOIN sys_user su on su.id_card=u.id_card"+ " where u.community_id=#{exportUserDTO.communityId} " + "<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" + " JOIN com_mng_struct_area c ON u.area_id = c.id " + " </if> " + @@ -232,7 +239,6 @@ "<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" + "AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') " + " </if> " + " where u.community_id=#{exportUserDTO.communityId} " + "<if test='exportUserDTO.phone != null and exportUserDTO.phone.trim() != ""'>" + "AND u.phone like concat(#{exportUserDTO.phone},'%') " + " </if> " + @@ -241,4 +247,62 @@ " </if> " + "</script>") List<EexcelUserDTO> selectExport(@Param("exportUserDTO") ExportUserDTO exportUserDTO); @Select("<script> " + "SELECT " + "u.id 'order', " + "c.`area_name` areaName, "+ "u.door_number doorNumber, " + "if(u.is_rent=1,'租住','自主')isRent, " + "u.name, " + "u.nation, " + "case u.political_outlook when 1 then '党员' when 2 then '团员' else '群众' end politicalOutlook, " + "if(u.marital_status=1,'已婚','未婚')maritalStatus, " + "u.phone, " + "u.education, " + "u.id_card, " + "u.company, " + "u.residence, " + "if(u.is_panzhiHua=1,'是','否') isPanZhiHua, " + "u.situation, " + "if(is_contact=1,'是','否') isContact, " + "if(u.major=1,'是','否') major, " + "if(u.soldier=1,'是','否') soldier, " + "if(u.disability=1,'是','否') disability, " + "if(u.lowIncome_households=1,'是','否') lowIncomeHouseholds, " + "if(u.lowIncome_people=1,'是','否') lowIncomePeople, " + "if(u.old_people=1,'是','否') oldPeople, " + "if(u.special_service_family=1,'是','否') specialServiceFamily, " + "if(u.key_personnel=1,'是','否') keyPersonnel " + "u.tags " + "FROM " + " sys_user_input u " + " JOIN com_mng_struct_area c ON u.area_id = c.id " + " where u.community_id=#{exportSpecialUserDTO.communityId} " + "<if test='exportSpecialUserDTO.areaName != null and exportSpecialUserDTO.areaName.trim() != ""'>" + "AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') " + " </if> " + "<if test='exportSpecialUserDTO.name != null and exportSpecialUserDTO.name.trim() != ""'>" + " and u.`name` like concat(#{exportSpecialUserDTO.name},'%') " + " </if> " + "<if test='exportSpecialUserDTO.name != null and exportSpecialUserDTO.name.trim() != ""'>" + " and u.`name` like concat(#{exportSpecialUserDTO.name},'%') " + " </if> " + "<if test='exportSpecialUserDTO.tags != null and exportSpecialUserDTO.tags.trim() != ""'>" + " CONCAT( " + " IF " + " ( t.soldier = 1, '军人,', '' ), " + " IF " + " ( t.lowIncome_households = 1, '低保户,', '' ), " + " IF " + " ( t.lowIncome_people = 1, '低收入,', '' ), " + " IF " + " ( t.old_people = 1, '特服家庭,', '' ), " + " IF " + " ( t.key_personnel = 1, '重点人员,', '' ), " + " IFNULL(t.tags,'') " + " ) like concat(#{exportSpecialUserDTO.tags},'%')"+ " </if> " + "</script>") List<EexcelUserDTO> specialUserExport(@Param("exportSpecialUserDTO") ExportSpecialUserDTO exportSpecialUserDTO); } springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/service/SysUserInputService.java
@@ -1,6 +1,7 @@ package com.panzhihua.service_user.service; import com.baomidou.mybatisplus.extension.service.IService; import com.panzhihua.common.model.dtos.community.ExportSpecialUserDTO; import com.panzhihua.common.model.dtos.community.ExportUserDTO; import com.panzhihua.common.model.dtos.user.ComMngUserTagDTO; import com.panzhihua.common.model.dtos.user.EexcelUserDTO; @@ -86,9 +87,24 @@ R saveSpecialInputUserTags(ComMngTagVO comMngTagVO); /** * 删除特殊群体标签 * @param id * @return */ R deleteSpecialInputUserTags(Long id); /** * 删除特殊群体用户 * @param id * @return */ R deleteSpecialInputUser(Long id); /** * 导出特殊群体用户 * @param exportSpecialUserDTO * @return */ R specialUserExport(ExportSpecialUserDTO exportSpecialUserDTO); } springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/service/impl/SysUserInputServiceImpl.java
@@ -6,6 +6,7 @@ import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.panzhihua.common.exceptions.ServiceException; import com.panzhihua.common.model.dtos.community.ExportSpecialUserDTO; import com.panzhihua.common.model.dtos.community.ExportUserDTO; import com.panzhihua.common.model.dtos.user.ComMngUserTagDTO; import com.panzhihua.common.model.dtos.user.EexcelUserDTO; @@ -359,6 +360,22 @@ } @Override public R deleteSpecialInputUserTags(Long id) { ComMngUserTagDO comMngUserTagDO = comMngUserTagDAO.selectById(id); if(null==comMngUserTagDO){ return R.fail("标签不存在"); } if(comMngUserTagDO.getSysFlag()==1){ return R.fail("系统预置标签不可删除"); } int delete = comMngUserTagDAO.deleteById(id); if(delete>0){ return R.ok(); } return R.fail("删除失败"); } @Override public R deleteSpecialInputUser(Long id) { SysUserInputDO sysUserInputDO = sysUserInputDAO.selectById(id); if(null==sysUserInputDO){ @@ -381,4 +398,10 @@ } return R.fail(); } @Override public R specialUserExport(ExportSpecialUserDTO exportSpecialUserDTO) { List<EexcelUserDTO> eexcelUserDTOS=sysUserInputDAO.specialUserExport(exportSpecialUserDTO); return R.ok(eexcelUserDTOS); } }
