springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/community/PageEldersAuthHistoryDTO.java
@@ -21,6 +21,9 @@ @ApiModelProperty(value = "月份") private Integer month; @ApiModelProperty(value = "年份") private Integer year; @ApiModelProperty(value = "分页-当前页数,默认1", example = "1") private Long pageNum = 1L;
New file @@ -0,0 +1,31 @@ package com.panzhihua.common.model.vos.partybuilding; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; import java.util.Date; /** * @description: 党员信息导出 * @author: lyq * @date: 2021/5/6 17:48 */ @Data public class ComEldersAuthHistoryExcelVO { @ExcelProperty(value = "姓名" ,index = 0) private String name; @ExcelProperty(value = "身份证号" ,index = 1) private String idCard; @ExcelProperty(value = "出生年月" ,index = 2) private String brithday; @ExcelProperty(value = "年龄" ,index = 3) private Date age; @ExcelProperty(value = "户籍地" ,index = 4) private Date domicile; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java
@@ -2853,4 +2853,10 @@ */ @PostMapping("/elders/setCommunityAuthType") R setCommunityAuthType(@RequestParam("communityId")Long communityId,@RequestParam("type") Integer type); @PostMapping("/elders/getAuthHistoryList") R getAuthHistoryList(@RequestBody PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO); @PostMapping("/elders/getAuthHistoryExport") R getAuthHistoryExport(@RequestBody PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO); } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/DateUtils.java
@@ -642,6 +642,48 @@ return cal.getTime(); } /** * 获取当前年月的第一天时间 * @param year 年份 * @param month 月份 * @return 时间 */ public static Date getYearMonthStart(Integer year,Integer month) { Calendar cal = Calendar.getInstance(); // 设置年份 cal.set(Calendar.YEAR, year); // 设置月份 cal.set(Calendar.MONTH, month - 1); // 获取某月最小天数 int firstDay = cal.getMinimum(Calendar.DATE); // 设置日历中月份的最小天数 cal.set(Calendar.DAY_OF_MONTH, firstDay); int actualMinimum = cal.getActualMinimum(Calendar.DAY_OF_MONTH); cal.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONDAY),actualMinimum,00,00,00); return cal.getTime(); } /** * 获取当前年月的最后一天时间 * @param year 年份 * @param month 月份 * @return 时间 */ public static Date getYearMonthEnd(Integer year,Integer month) { Calendar cal = Calendar.getInstance(); // 设置年份 cal.set(Calendar.YEAR, year); // 设置月份 cal.set(Calendar.MONTH, month - 1); // 获取某月最大天数 int lastDay = cal.getMaximum(Calendar.DATE); // 设置日历中月份的最大天数 cal.set(Calendar.DAY_OF_MONTH, lastDay); int actualMinimum = cal.getActualMinimum(Calendar.DAY_OF_MONTH); cal.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONDAY),actualMinimum,00,00,00); return cal.getTime(); } @@ -650,7 +692,7 @@ // Date date= new Date(); // Date after = new Date(); // System.out.println(calTimeDifference(date,after)); System.out.println(getFirstDayOfMonth()); System.out.println(getYearMonthStart(2021,2)); } } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/EldersAuthHistoryApi.java
@@ -1,14 +1,26 @@ package com.panzhihua.community_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.JSONArray; import com.panzhihua.common.controller.BaseController; import com.panzhihua.common.model.dtos.community.PageEldersAuthElderlyDTO; import com.panzhihua.common.model.dtos.community.PageEldersAuthHistoryDTO; import com.panzhihua.common.model.vos.LoginUserInfoVO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComEldersAuthElderlyVO; import com.panzhihua.common.model.vos.community.ComEldersAuthHistoryVO; import com.panzhihua.common.model.vos.partybuilding.ComEldersAuthHistoryExcelVO; import com.panzhihua.common.model.vos.partybuilding.ComPbMemberExcelVO; import com.panzhihua.common.service.community.CommunityService; import com.panzhihua.common.utlis.SFTPUtil; import com.panzhihua.community_backstage.excel.CustomSheetWriteHandler; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Value; import org.springframework.validation.annotation.Validated; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; @@ -16,30 +28,103 @@ import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.util.List; @Slf4j @RestController @RequestMapping("/elders/history") @Api(tags = {"高龄老人报表模块"}) public class EldersAuthHistoryApi { public class EldersAuthHistoryApi extends BaseController { @Resource private CommunityService communityService; // 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("${ftp.url}") private String url; @Value("${excel.userurl}") private String excelUrl; /** * 高领老人列表 * @param pageEldersAuthElderlyDTO * 分页查询老人报表记录 * @param pageEldersAuthElderlyDTO 请求参数 * @return */ @PostMapping("/page") @ApiOperation(value = "分页查询老人报表记录", response = ComEldersAuthHistoryVO.class) public R query(@Validated @RequestBody PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO){ LoginUserInfoVO loginUserInfo = this.getLoginUserInfo(); if(loginUserInfo == null){ return R.fail("请先登录"); } pageEldersAuthElderlyDTO.setCommunityId(loginUserInfo.getCommunityId()); return communityService.getAuthHistoryList(pageEldersAuthElderlyDTO); } // return communityService.pageQueryEldersAuthHistory(pageEldersAuthElderlyDTO); return null; @PostMapping("/export") @ApiOperation(value = "导出已认证老人记录") public R export(@Validated @RequestBody PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO){ String name = "已认证老人信息.xlsx"; String ftpUrl = "/mnt/data/web/excel/"; // 用户搜索了就下载搜索的用户否则下载所有用户 R r = communityService.getAuthHistoryExport(pageEldersAuthElderlyDTO); if (R.isOk(r)) { List<ComEldersAuthHistoryExcelVO> eexcelUserDTOS = JSONArray.parseArray(JSONArray.toJSONString(r.getData()), ComEldersAuthHistoryExcelVO.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, ComEldersAuthHistoryExcelVO.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(excelUrl + name); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } return R.fail("未查询到用户"); }
@@ -1,15 +1,12 @@ package com.panzhihua.service_community.api; import com.panzhihua.common.model.dtos.community.PageComOpsHouseDTO; import com.panzhihua.common.model.dtos.community.PageEldersAuthRecordDTO; import com.panzhihua.common.model.dtos.community.*; import com.panzhihua.common.model.dtos.elders.ComEldersAuthGetResultDTO; import com.panzhihua.common.model.dtos.elders.ComEldersAuthPageDTO; import com.panzhihua.common.model.dtos.elders.ComEldersAuthUserAddAppDTO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.elders.ComElderAuthUserDetailVO; import com.panzhihua.common.model.dtos.community.EldersAuthTypeQueryDTO; import com.panzhihua.common.model.vos.community.SysConfVO; import com.panzhihua.common.model.dtos.community.PageComEldersRecordsDTO; import com.panzhihua.service_community.model.dos.ComEldersAuthUserDO; import com.panzhihua.service_community.model.dos.EldersAuthDO; import com.panzhihua.common.model.vos.community.ComEldersAuthElderlyDetailsVO; @@ -135,5 +132,15 @@ return eldersAuthService.pageQueryEldersAuthRecord(pageEldersAuthElderlyDTO); } @PostMapping("/getAuthHistoryList") public R getAuthHistoryList(@RequestBody PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO){ return eldersAuthService.getAuthHistoryList(pageEldersAuthElderlyDTO); } @PostMapping("/getAuthHistoryExport") public R getAuthHistoryExport(@RequestBody PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO){ return eldersAuthService.getAuthHistoryExport(pageEldersAuthElderlyDTO); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComEldersAuthStatisticsMapper.java
@@ -4,6 +4,8 @@ import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.panzhihua.common.model.dtos.community.PageComEldersAuthStatisticsDTO; import com.panzhihua.common.model.dtos.community.PageEldersAuthHistoryDTO; import com.panzhihua.common.model.vos.community.ComEldersAuthHistoryVO; import com.panzhihua.service_community.model.dos.ComEldersAuthStatisticsDO; import com.panzhihua.common.model.vos.community.ComEldersAuthStatisticsVO; import org.apache.ibatis.annotations.Mapper; @@ -14,17 +16,18 @@ * * @author cedoo email:cedoo(a)qq.com * @version 1.0 * @since 1.0 * @date 2021-06-10 * */ * @since 1.0 */ @Mapper public interface ComEldersAuthStatisticsMapper extends BaseMapper<ComEldersAuthStatisticsDO> { /** * 分页查询 * @param pageComEldersAuthStatisticsDTO * * @param pageEldersAuthHistoryDTO * @return */ IPage<ComEldersAuthStatisticsVO> findByPage(Page page, @Param("pageComEldersAuthStatisticsDTO") PageComEldersAuthStatisticsDTO pageComEldersAuthStatisticsDTO); IPage<ComEldersAuthHistoryVO> findByPage(Page page, @Param("pageEldersAuthHistoryDTO") PageEldersAuthHistoryDTO pageEldersAuthHistoryDTO); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/EldersAuthElderlyDAO.java
New file @@ -0,0 +1,15 @@ package com.panzhihua.service_community.dao; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.panzhihua.service_community.model.dos.EldersAuthFeedbackDO; import org.apache.ibatis.annotations.Mapper; /** * @author xyh * @date 2021/6/9 14:34 */ @Mapper public interface EldersAuthElderlyDAO extends BaseMapper<EldersAuthFeedbackDO> { } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/model/dos/ComEldersAuthHistoryRecordDO.java
@@ -66,5 +66,9 @@ */ @TableField(fill = FieldFill.INSERT) private Date createAt; /** * 社区id 列：社区id */ private Long communityId; } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/model/dos/ComEldersAuthStatisticsDO.java
@@ -54,5 +54,9 @@ */ @TableField(fill = FieldFill.INSERT) private Date createAt; /** * 社区id 列: community_id */ private Long communityId; } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/EldersAuthService.java
@@ -142,4 +142,8 @@ */ R setCommunityAuthType(Long communityId, Integer type); R getAuthHistoryList(PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO); R getAuthHistoryExport(PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/EldersAuthServiceImpl.java
@@ -17,6 +17,7 @@ import com.panzhihua.common.model.vos.community.EldersAuthFeedbackVO; import com.panzhihua.common.model.vos.community.EldersAuthVO; import com.panzhihua.common.model.vos.elders.ComElderAuthUserAppVO; import com.panzhihua.common.model.vos.partybuilding.ComEldersAuthHistoryExcelVO; import com.panzhihua.common.model.vos.user.SysUserVO; import com.panzhihua.common.utlis.AgeUtils; import com.panzhihua.common.utlis.DateUtils; @@ -56,6 +57,8 @@ private SysConfMapper sysConfDao; @Resource private ComEldersAuthHistoryRecordMapper comEldersAuthHistoryRecordMapper; @Resource private ComEldersAuthStatisticsMapper comEldersAuthStatisticsMapper; /** * 新增高龄认证 @@ -417,4 +420,26 @@ } return R.fail(); } @Override public R getAuthHistoryList(PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO){ return R.ok(comEldersAuthStatisticsMapper.findByPage(new Page(pageEldersAuthElderlyDTO.getPageNum(),pageEldersAuthElderlyDTO.getPageSize()),pageEldersAuthElderlyDTO)); } public R getAuthHistoryExport(PageEldersAuthHistoryDTO pageEldersAuthElderlyDTO){ Date startTime = DateUtils.getYearMonthStart(pageEldersAuthElderlyDTO.getYear(),pageEldersAuthElderlyDTO.getMonth()); Date endTime = DateUtils.getYearMonthEnd(pageEldersAuthElderlyDTO.getYear(),pageEldersAuthElderlyDTO.getMonth()); List<ComEldersAuthHistoryRecordDO> userList = comEldersAuthHistoryRecordMapper.selectList(new QueryWrapper<ComEldersAuthHistoryRecordDO>().lambda() .eq(ComEldersAuthHistoryRecordDO::getCommunityId,pageEldersAuthElderlyDTO.getCommunityId()) .ge(ComEldersAuthHistoryRecordDO::getCreateAt,startTime).le(ComEldersAuthHistoryRecordDO::getCreateAt,endTime)); List<ComEldersAuthHistoryExcelVO> authHistoryExcelVOS = new ArrayList<>(); userList.forEach(user -> { ComEldersAuthHistoryExcelVO authHistoryExcelVO = new ComEldersAuthHistoryExcelVO(); BeanUtils.copyProperties(user,authHistoryExcelVO); authHistoryExcelVOS.add(authHistoryExcelVO); }); return R.ok(authHistoryExcelVOS); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/resources/mapper/ComEldersAuthStatisticsMapper.xml
@@ -12,48 +12,32 @@ <result property="authSum" column="auth_sum"/> <result property="noAuthSum" column="no_auth_sum"/> <result property="createAt" column="create_at"/> <result property="communityId" column="community_id"/> </resultMap> <sql id="columns"> <![CDATA[ id,year,month,sum,auth_sum,no_auth_sum,create_at id,year,month,sum,auth_sum,no_auth_sum,create_at,community_id ]]> </sql> <!-- 分页查询 --> <select id="findByPage" resultType="com.panzhihua.common.model.vos.community.ComEldersAuthStatisticsVO" parameterType="com.panzhihua.common.model.dtos.community.PageComEldersAuthStatisticsDTO"> <select id="findByPage" resultType="com.panzhihua.common.model.vos.community.ComEldersAuthHistoryVO" parameterType="com.panzhihua.common.model.dtos.community.PageEldersAuthHistoryDTO"> SELECT <include refid="columns" /> FROM com_elders_auth_statistics <where> <if test="pageComEldersAuthStatisticsDTO.id!=null"> AND id = #{pageComEldersAuthStatisticsDTO.id} <if test="pageEldersAuthHistoryDTO.month!=null"> AND month = #{pageEldersAuthHistoryDTO.month} </if> <if test="pageComEldersAuthStatisticsDTO.year!=null"> AND year = #{pageComEldersAuthStatisticsDTO.year} <if test="pageEldersAuthHistoryDTO.year!=null"> AND year = #{pageEldersAuthHistoryDTO.year} </if> <if test="pageComEldersAuthStatisticsDTO.month!=null"> AND month = #{pageComEldersAuthStatisticsDTO.month} </if> <if test="pageComEldersAuthStatisticsDTO.sum!=null"> AND sum = #{pageComEldersAuthStatisticsDTO.sum} </if> <if test="pageComEldersAuthStatisticsDTO.authSum!=null"> AND auth_sum = #{pageComEldersAuthStatisticsDTO.authSum} </if> <if test="pageComEldersAuthStatisticsDTO.noAuthSum!=null"> AND no_auth_sum = #{pageComEldersAuthStatisticsDTO.noAuthSum} </if> <if test="pageComEldersAuthStatisticsDTO.createAtBegin!=null"> AND create_at >= #{pageComEldersAuthStatisticsDTO.createAtBegin} </if> <if test="pageComEldersAuthStatisticsDTO.createAtEnd!=null"> AND create_at <= #{pageComEldersAuthStatisticsDTO.createAtEnd} <if test="pageEldersAuthHistoryDTO.communityId!=null"> AND community_id = #{pageEldersAuthHistoryDTO.communityId} </if> </where> <if test="pageComEldersAuthStatisticsDTO.sortColumns!=null"> ORDER BY ${pageComEldersAuthStatisticsDTO.sortColumns} ${pageComEldersAuthStatisticsDTO.sortType} </if> ORDER BY create_at desc </select> </mapper>
