springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/community/ExcelHouseDTO.java
New file @@ -0,0 +1,38 @@ package com.panzhihua.common.model.dtos.community; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; /** * @description: 房屋信息导出 * @author: Null * @date: 2021/3/16 09:36 */ @Data public class ExcelHouseDTO { @ExcelProperty("楼栋") private String build; @ExcelProperty("单元") private String unit; @ExcelProperty("楼层") private String floor; @ExcelProperty("房间") private String room; @ExcelProperty("面积") private String square; @ExcelProperty("房屋状态") private String state; @ExcelProperty("居住人") private String username; @ExcelProperty("联系电话") private String phone; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java
@@ -1097,4 +1097,13 @@ */ @PostMapping("/convenient/serve/import") R listSaveConvenientServeExcelVO(@RequestBody List<ComCvtServeExcelVO> list,@RequestParam("communityId") Long communityId); /** * 导出房屋信息 * @param areaId * @return */ @PostMapping("/house/export") R houseExport(@RequestParam("areaId") Long areaId); } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommunityManagerApi.java
@@ -9,6 +9,7 @@ import com.alibaba.fastjson.JSONObject; import com.panzhihua.common.constants.FtpConstants; import com.panzhihua.common.controller.BaseController; import com.panzhihua.common.model.dtos.community.ExcelHouseDTO; import com.panzhihua.common.model.dtos.community.ExportUserDTO; import com.panzhihua.common.model.dtos.user.EexcelUserDTO; import com.panzhihua.common.model.vos.LoginUserInfoVO; @@ -640,4 +641,69 @@ } return R.fail("未查询到用户"); } @ApiOperation(value = "导出小区房屋信息") @GetMapping("house/export") public R houseExport(@RequestParam("areaId") Long areaId){ String url=excelUrl; String uuid= UUID.randomUUID().toString().replace("-",""); //String name=uuid+".xlsx"; String ftpUrl="/mnt/data/web/excel/"; R<ComMngStructAreaVO> areaR = communityService.detailArea(areaId); if(!R.isOk(areaR)){ return areaR; } ComMngStructAreaVO areaVO = JSONObject.parseObject(JSONObject.toJSONString(areaR.getData()),ComMngStructAreaVO.class); Long communityId = areaVO.getCommunityId(); R<ComActVO> comActR = communityService.detailCommunity(communityId); if(!R.isOk(comActR)){ return comActR; } ComActVO comActVO = JSONObject.parseObject(JSONObject.toJSONString(comActR.getData()),ComActVO.class); R r=communityService.houseExport(areaId); if (R.isOk(r)) { List<ExcelHouseDTO> excelHouseDTOS = JSONArray.parseArray(JSONArray.toJSONString(r.getData()),ExcelHouseDTO.class); try { SFTPUtil sftp = new SFTPUtil(userName,password,host,port); sftp.login(); String name=comActVO.getName()+areaVO.getAreaName()+"房屋信息.xlsx"; 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, ExcelHouseDTO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomSheetWriteHandler()).build(); WriteSheet writeSheet = EasyExcel.writerSheet(comActVO.getName()+areaVO.getAreaName()).build(); excelWriter.write(excelHouseDTOS, 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+URLEncoder.encode(name,"UTF-8")); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } return R.fail("未查询到用户"); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/CommunityApi.java
@@ -1276,4 +1276,14 @@ public R detailMessage(@RequestParam("id") Long id) { return comActMessageService.detailMessage(id); } /** * 导出房屋信息 * @param areaId * @return */ @PostMapping("house/export") public R houseExport(@RequestParam("areaId") Long areaId) { return comMngStructHouseService.houseExport(areaId); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngStructHouseDAO.java
@@ -1,11 +1,14 @@ package com.panzhihua.service_community.dao; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.panzhihua.common.model.dtos.community.ExcelHouseDTO; import com.panzhihua.service_community.model.dos.ComMngStructHouseDO; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Options; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.mapping.StatementType; import java.util.List; /** * @program: springcloud_k8s_panzhihuazhihuishequ @@ -18,4 +21,37 @@ @Select("{call smart_life.removeByParentCode(#{areaCode,jdbcType=VARCHAR,mode=IN})}") @Options(statementType = StatementType.CALLABLE) int batchDeleteHouse(String areaCode); @Select("<script> " + " SELECT REPLACE " + " ( h3.house_name, h4.house_name, '' ) build, " + " REPLACE ( h2.house_name, h3.house_name, '' ) unit, " + " REPLACE ( h1.house_name, h2.house_name, '' ) floor, " + " REPLACE ( h.house_name, h1.house_name, '' ) room, " + " h.square, " + "CASE " + " h.state " + " WHEN 1 THEN " + " '自住' " + " WHEN 2 THEN " + " '租住' " + " WHEN 3 THEN " + " '商用' ELSE '未知' " + " END AS state, " + " su.NAME username, " + " su.phone " + "FROM " + " com_mng_struct_house h " + " LEFT JOIN com_mng_struct_house h1 ON h.parent_code = h1.house_code " + " LEFT JOIN com_mng_struct_house h2 ON h1.parent_code = h2.house_code " + " LEFT JOIN com_mng_struct_house h3 ON h2.parent_code = h3.house_code " + " LEFT JOIN com_mng_struct_house h4 ON h3.parent_code = h4.house_code " + " LEFT JOIN com_mng_struct_house_user hu ON h.house_code = hu.house_code " + " LEFT JOIN sys_user su ON hu.user_id = su.user_id " + "WHERE " + " h.house_code LIKE concat(#{houseCode},'%') " + " AND h.type = 5" + " ORDER BY h.house_code ASC " + "</script> ") List<ExcelHouseDTO> houseExport(String houseCode); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComMngStructHouseService.java
@@ -77,4 +77,13 @@ * @return 新增结果 */ R addHouses(ComMngStructHouseVO comMngStructHouseVO); /** * 导出小区房屋信息 * @param areaId * @return */ R houseExport(Long areaId); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComMngStructHouseServiceImpl.java
@@ -3,12 +3,14 @@ import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.panzhihua.common.model.dtos.community.ExcelHouseDTO; import com.panzhihua.common.model.vos.LoginUserInfoVO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.BatchhouseVO; import com.panzhihua.common.model.vos.community.ComMngStructAreaVO; import com.panzhihua.common.model.vos.community.ComMngStructHouseVO; import com.panzhihua.common.model.vos.user.CommunityUserInfoVO; import com.panzhihua.service_community.dao.ComMngStructAreaDAO; import com.panzhihua.service_community.dao.ComMngStructHouseDAO; import com.panzhihua.service_community.dao.ComMngStructHouseUserDAO; import com.panzhihua.service_community.model.dos.ComMngStructAreaDO; @@ -40,7 +42,8 @@ private ComMngStructHouseDAO comMngStructHouseDAO; @Resource private ComMngStructHouseUserDAO comMngStructHouseUserDAO; @Resource private ComMngStructAreaDAO comMngStructAreaDAO; /** * 房屋信息 * @@ -506,5 +509,15 @@ throw new RuntimeException("绑定房屋和人员关系失败"); } @Override public R houseExport(Long areaId) { ComMngStructAreaDO comMngStructAreaDO = comMngStructAreaDAO.selectById(areaId); if (ObjectUtils.isEmpty(comMngStructAreaDO)) { return R.fail("小区不存在"); } List<ExcelHouseDTO> houseDTOS = comMngStructHouseDAO.houseExport(comMngStructAreaDO.getAreaCode()); return R.ok(houseDTOS); } }
