springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/reserve/HomeQuarantineRegisterExportVO.java
New file @@ -0,0 +1,27 @@ package com.panzhihua.common.model.vos.community.reserve; import java.util.List; import com.panzhihua.common.model.vos.community.ComActQuestnaireSubVO; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; /** * @title: HomeQuarantineRegisterExportVO * @projectName: 成都呐喊信息技术有限公司-智慧社区项目 * @description: 居家隔离统计导出信息 * @author: hans * @date: 2021/11/18 14:54 */ @Data @ApiModel("居家隔离统计导出信息") public class HomeQuarantineRegisterExportVO { @ApiModelProperty("题目列表") private List<ComActQuestnaireSubVO> subs; @ApiModelProperty("数据集") private List<List<Object>> dataList; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/reserve/HomeQuarantineRegisterStatisticsVO.java
@@ -25,16 +25,22 @@ @ApiModelProperty("用户id") private Long userId; @ApiModelProperty("提交时间") @ApiModelProperty("开始登记时间") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") private Date createAt; @ApiModelProperty("用户昵称") private String nickName; @ApiModelProperty("题目") private String reserveSubContent; @ApiModelProperty("用户昵称") @ApiModelProperty("答案") private String answerContent; @ApiModelProperty("题目id") private Long reserveSubId; @ApiModelProperty("选项类型（0 单选 1 多选 2 输入框 3 手机号 4 身份证 5 文字描述 6 日期选择 7 时间选择 8 可换行的输入框 9姓名输入框 10图片选择 11文件上传 12签名）") private Integer optionType; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java
@@ -7275,4 +7275,12 @@ */ @GetMapping("/property/publicity/incr-view") R incrPropertyPublicityView(@RequestParam("id") Long id); /** * 居家隔离导出 * @param pageReserveRegisterDetailedAdminDTO * @return */ @PostMapping("/reserve/admin/homeQuarantine/export") R exportHomeQuarantine(@RequestBody PageReserveRegisterDetailedAdminDTO pageReserveRegisterDetailedAdminDTO); } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/ComActReserveApi.java
@@ -8,6 +8,7 @@ import javax.annotation.Resource; import com.panzhihua.common.model.vos.community.reserve.HomeQuarantineRegisterExportVO; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.springframework.beans.BeanUtils; @@ -621,9 +622,9 @@ * 组合导出数据 * 用户问卷答案 */ R questnaireAnswersDTOR = communityService.exportRegisterAdmin(pageReserveRegisterDetailedAdminDTO); QuestnaireAnswersDTO questnaireAnswersDTOS = JSON.parseObject(JSON.toJSONString(questnaireAnswersDTOR.getData()), QuestnaireAnswersDTO.class); List<ComActQuestnaireSubVO> subVOList = questnaireAnswersDTOS.getSubs(); R exportResultR = communityService.exportHomeQuarantine(pageReserveRegisterDetailedAdminDTO); HomeQuarantineRegisterExportVO exportVO = JSON.parseObject(JSON.toJSONString(exportResultR.getData()), HomeQuarantineRegisterExportVO.class); List<ComActQuestnaireSubVO> subVOList = exportVO.getSubs(); //构造表头 List<List<String>> headList = new ArrayList<List<String>>(); String firstRowContent = "重点地区或高风险地区来攀返攀人员信息统计表（居家隔离表）"; @@ -655,7 +656,7 @@ head2.add("隔离体温情况"); headList.add(head2); //数据转换 datalist = HomeQuarantineUtil.ReserveData(questnaireAnswersDTOS.getAnswers()); datalist = exportVO.getDataList(); WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/ComActReserveApi.java
@@ -332,4 +332,14 @@ public R pageRegisterHomeQuarantine(@RequestBody PageReserveRegisterDetailedAdminDTO detailedAdminDTO) { return comActReserveAnswerContentService.pageRegisterHomeQuarantine(detailedAdminDTO); } /** * 居家隔离导出 * @param detailedAdminDTO * @return */ @PostMapping("/admin/homeQuarantine/export") public R exportHomeQuarantine(@RequestBody PageReserveRegisterDetailedAdminDTO detailedAdminDTO) { return comActReserveAnswerContentService.exportHomeQuarantine(detailedAdminDTO); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComActReserveAnswerContentMapper.java
@@ -54,4 +54,11 @@ IPage<String> pageRegisterHomeQuarantine(@Param("page") Page page, @Param("detailedAdminDTO") PageReserveRegisterDetailedAdminDTO detailedAdminDTO); List<HomeQuarantineRegisterStatisticsVO> selectStatistics(@Param("reserveId") Long reserveId, @Param("record") String record); /** * 查询居家隔离统计身份证号码 * @param detailedAdminDTO * @return */ List<String> exportHomeQuarantine(@Param("detailedAdminDTO") PageReserveRegisterDetailedAdminDTO detailedAdminDTO); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComActReserveAnswerContentService.java
@@ -32,4 +32,11 @@ * @return */ R pageRegisterHomeQuarantine(PageReserveRegisterDetailedAdminDTO detailedAdminDTO); /** * 居家隔离导出 * @param detailedAdminDTO * @return */ R exportHomeQuarantine(PageReserveRegisterDetailedAdminDTO detailedAdminDTO); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComActReserveAnswerContentServiceImpl.java
@@ -1,12 +1,15 @@ package com.panzhihua.service_community.service.impl; import java.net.URL; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import javax.annotation.Resource; import javax.xml.bind.DatatypeConverter; import com.panzhihua.common.model.vos.community.reserve.HomeQuarantineRegisterExportVO; import org.springframework.beans.BeanUtils; import org.springframework.stereotype.Service; @@ -52,19 +55,7 @@ @Override public R exportRegisterAdmin(PageReserveRegisterDetailedAdminDTO pageReserveRegisterDetailedAdminDTO){ QuestnaireAnswersDTO result = new QuestnaireAnswersDTO(); //查询题目 List<ComActReserveSubDO> list = comActReserveSubMapper.selectList(new QueryWrapper<ComActReserveSubDO>().lambda() .eq(ComActReserveSubDO::getReserveId, pageReserveRegisterDetailedAdminDTO.getReserveId()).orderByAsc(ComActReserveSubDO::getCreateAt).orderByAsc(ComActReserveSubDO::getId)); List<ComActQuestnaireSubVO> listSubVo = new ArrayList<>(); list.forEach(subDo -> { if(!subDo.getType().equals(5)){ ComActQuestnaireSubVO comActQuestnaireSubVO = new ComActQuestnaireSubVO(); BeanUtils.copyProperties(subDo, comActQuestnaireSubVO); listSubVo.add(comActQuestnaireSubVO); } }); result.setSubs(listSubVo); result.setSubs(retrieveQuestnaireSub(pageReserveRegisterDetailedAdminDTO)); //查询用户回答 List<ComActQuestnaireAnswerContentVO> vos = this.baseMapper.export(pageReserveRegisterDetailedAdminDTO); @@ -157,23 +148,35 @@ map.put("nub", nub + ""); List<HomeQuarantineRegisterStatisticsVO> statisticsList = this.baseMapper.selectStatistics(detailedAdminDTO.getReserveId(), record); StringBuffer conditions = new StringBuffer(); //渲染首次登记时间 map.put("time", DateUtils.format(statisticsList.get(0).getCreateAt(), DateUtils.ymdhms_format)); statisticsList.forEach(e -> { //渲染登记时间 map.put("time", DateUtils.format(e.getCreateAt(), DateUtils.ymdhms_format)); String reserveSubContent = e.getReserveSubContent(); String dateString = dateFormat.format(e.getCreateAt()); if (reserveSubContent.contains("上午体温")) { if (conditions.indexOf(dateString + " " + "上午体温") == -1) { int index = conditions.indexOf(dateString + " " + "上午体温"); if (index == -1) { conditions.append(dateString + " " + "上午体温：" + e.getAnswerContent() + "℃" + "\n"); } else { conditions.replace(index, conditions.indexOf("℃", index), dateString + " " + "上午体温：" + e.getAnswerContent()); } } else if (reserveSubContent.contains("下午体温")) { if (conditions.indexOf(dateString + " " + "下午体温") == -1) { int index = conditions.indexOf(dateString + " " + "下午体温"); if (index == -1) { conditions.append(dateString + " " + "下午体温：" + e.getAnswerContent() + "℃" + "\n"); } else { conditions.replace(index, conditions.indexOf("℃", index), dateString + " " + "下午体温：" + e.getAnswerContent()); } } else if (reserveSubContent.contains("隔离情况")) { if (conditions.indexOf(dateString + " " + "当日隔离情况") == -1) { int index = conditions.indexOf(dateString + " " + "当日隔离情况"); if (index == -1) { conditions.append(dateString + " " + "当日隔离情况：" + e.getAnswerContent() + "\n"); conditions.append("————————" + "\n"); } else { conditions.replace(index, conditions.indexOf("\n", index), dateString + " " + "当日隔离情况：" + e.getAnswerContent()); } } else { map.put(e.getReserveSubId() + "", e.getAnswerContent()); @@ -187,4 +190,105 @@ resultPage.setRecords(resultMapList); return R.ok(resultPage); } /** * 居家隔离导出 * @param detailedAdminDTO * @return */ @Override public R exportHomeQuarantine(PageReserveRegisterDetailedAdminDTO detailedAdminDTO) { HomeQuarantineRegisterExportVO exportVO = new HomeQuarantineRegisterExportVO(); //题目 exportVO.setSubs(retrieveQuestnaireSub(detailedAdminDTO)); //结果数据集合 List<List<Object>> resultList = new ArrayList<>(); List<String> idCards = this.baseMapper.exportHomeQuarantine(detailedAdminDTO); if (!idCards.isEmpty()) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); for (String idCard : idCards) { //构建单个用户数据 List<Object> userData = new ArrayList<>(); List<HomeQuarantineRegisterStatisticsVO> statisticsList = this.baseMapper.selectStatistics(detailedAdminDTO.getReserveId(), idCard); HomeQuarantineRegisterStatisticsVO firstStatisticsVO = statisticsList.get(0); userData.add(firstStatisticsVO.getNickName()); userData.add(DateUtils.format(firstStatisticsVO.getCreateAt(), DateUtils.ymdhms_format)); int size = statisticsList.size(); List<HomeQuarantineRegisterStatisticsVO> lastStatisticsVOList = statisticsList.subList(size - 13, size); for (HomeQuarantineRegisterStatisticsVO statisticsVO : lastStatisticsVOList) { //判断当前的组件类型是否是图片上传，导出时需要导出图片 if (statisticsVO.getOptionType().equals(11)) { try { userData.add(new URL(statisticsVO.getAnswerContent())); } catch (Exception e) { userData.add(" "); log.error("导出转换图片失败！"); } continue; } //判断当前组件类型是否签名 if (statisticsVO.getOptionType().equals(12)) { try { userData.add(DatatypeConverter.parseBase64Binary(statisticsVO.getAnswerContent().substring(statisticsVO.getAnswerContent().indexOf(",") + 1))); } catch (Exception e) { userData.add(" "); log.error("导出转换图片失败！"); } continue; } userData.add(statisticsVO.getAnswerContent()); } StringBuffer conditions = new StringBuffer(); statisticsList.forEach(e -> { String reserveSubContent = e.getReserveSubContent(); String dateString = dateFormat.format(e.getCreateAt()); if (reserveSubContent.contains("上午体温")) { int index = conditions.indexOf(dateString + " " + "上午体温"); if (index == -1) { conditions.append(dateString + " " + "上午体温：" + e.getAnswerContent() + "℃" + "\n"); } else { conditions.replace(index, conditions.indexOf("℃", index), dateString + " " + "上午体温：" + e.getAnswerContent()); } } else if (reserveSubContent.contains("下午体温")) { int index = conditions.indexOf(dateString + " " + "下午体温"); if (index == -1) { conditions.append(dateString + " " + "下午体温：" + e.getAnswerContent() + "℃" + "\n"); } else { conditions.replace(index, conditions.indexOf("℃", index), dateString + " " + "下午体温：" + e.getAnswerContent()); } } else if (reserveSubContent.contains("隔离情况")) { int index = conditions.indexOf(dateString + " " + "当日隔离情况"); if (index == -1) { conditions.append(dateString + " " + "当日隔离情况：" + e.getAnswerContent() + "\n"); conditions.append("————————" + "\n"); } else { conditions.replace(index, conditions.indexOf("\n", index), dateString + " " + "当日隔离情况：" + e.getAnswerContent()); } } }); userData.add(conditions.toString()); resultList.add(userData); } } exportVO.setDataList(resultList); return R.ok(exportVO); } private List<ComActQuestnaireSubVO> retrieveQuestnaireSub(PageReserveRegisterDetailedAdminDTO detailedAdminDTO) { //查询题目 List<ComActReserveSubDO> list = comActReserveSubMapper.selectList(new QueryWrapper<ComActReserveSubDO>().lambda() .eq(ComActReserveSubDO::getReserveId, detailedAdminDTO.getReserveId()).orderByAsc(ComActReserveSubDO::getCreateAt).orderByAsc(ComActReserveSubDO::getId)); List<ComActQuestnaireSubVO> listSubVo = new ArrayList<>(); list.forEach(subDo -> { if(!subDo.getType().equals(5)){ ComActQuestnaireSubVO comActQuestnaireSubVO = new ComActQuestnaireSubVO(); BeanUtils.copyProperties(subDo, comActQuestnaireSubVO); listSubVo.add(comActQuestnaireSubVO); } }); return listSubVo; } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/resources/mapper/ComActReserveAnswerContentMapper.xml
@@ -145,29 +145,51 @@ LEFT JOIN com_act_reserve_answer_content carac ON carr.id=carac.reserve_record_id LEFT JOIN com_act_reserve_sub t ON carac.reserve_sub_id = t.id WHERE carr.reserve_id = #{detailedAdminDTO.reserveId} AND t.content LIKE '身份证%' <if test='detailedAdminDTO.startTime != null and detailedAdminDTO.startTime != ""'> AND carr.create_at <![CDATA[ >= ]]> #{detailedAdminDTO.startTime} </if> <if test='detailedAdminDTO.endTime != null and detailedAdminDTO.endTime != ""'> AND carr.create_at <![CDATA[ <= ]]> #{detailedAdminDTO.endTime} </if> <if test="detailedAdminDTO.keyword!=null and detailedAdminDTO.keyword != """> AND carac.answer_content LIKE CONCAT (#{detailedAdminDTO.keyword},'%') </if> ORDER BY carr.id DESC LIMIT 9999999 ) temp GROUP BY answer_content ORDER BY id DESC ORDER BY carr.id ASC LIMIT 9999999) temp WHERE 1=1 <if test='detailedAdminDTO.startTime != null and detailedAdminDTO.startTime != ""'> AND temp.create_at <![CDATA[ >= ]]> #{detailedAdminDTO.startTime} </if> <if test='detailedAdminDTO.endTime != null and detailedAdminDTO.endTime != ""'> AND temp.create_at <![CDATA[ <= ]]> #{detailedAdminDTO.endTime} </if> GROUP BY answer_content ORDER BY id DESC </select> <select id="selectStatistics" resultType="com.panzhihua.common.model.vos.community.reserve.HomeQuarantineRegisterStatisticsVO"> SELECT carr.id, carr.create_at, carac.reserve_record_id, car.content AS reserveSubContent, carac.answer_content AS answerContent, carac.reserve_sub_id AS reserveSubId, car.`type` SELECT carr.id, carr.create_at, su.nick_name, carac.reserve_record_id, car.content AS reserveSubContent, carac.answer_content AS answerContent, carac.reserve_sub_id AS reserveSubId, car.`type` AS optionType FROM com_act_reserve_record AS carr LEFT JOIN com_act_reserve_answer_content carac ON carr.id=carac.reserve_record_id LEFT JOIN com_act_reserve_sub AS car ON carac.reserve_sub_id = car.id LEFT JOIN sys_user su ON carr.user_id = su.user_id WHERE carr.id IN ( SELECT t1.reserve_record_id FROM com_act_reserve_answer_content t1 LEFT JOIN com_act_reserve_record t2 ON t1.reserve_record_id = t2.id WHERE t2.reserve_id = #{reserveId} AND t1.answer_content = #{record} ) ORDER BY carr.id ASC </select> <select id="exportHomeQuarantine" resultType="java.lang.String"> SELECT answer_content FROM ( SELECT carr.id, carac.answer_content FROM com_act_reserve_record AS carr LEFT JOIN com_act_reserve_answer_content carac ON carr.id=carac.reserve_record_id LEFT JOIN com_act_reserve_sub t ON carac.reserve_sub_id = t.id WHERE carr.reserve_id = #{detailedAdminDTO.reserveId} AND t.content LIKE '身份证%' <if test="detailedAdminDTO.keyword!=null and detailedAdminDTO.keyword != """> AND carac.answer_content LIKE CONCAT (#{detailedAdminDTO.keyword},'%') </if> ORDER BY carr.id ASC LIMIT 9999999) temp WHERE 1=1 <if test='detailedAdminDTO.startTime != null and detailedAdminDTO.startTime != ""'> AND temp.create_at <![CDATA[ >= ]]> #{detailedAdminDTO.startTime} </if> <if test='detailedAdminDTO.endTime != null and detailedAdminDTO.endTime != ""'> AND temp.create_at <![CDATA[ <= ]]> #{detailedAdminDTO.endTime} </if> GROUP BY answer_content ORDER BY id DESC </select> </mapper>
