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
New file @@ -0,0 +1,46 @@ package com.panzhihua.common.model.vos.community.reserve; import java.util.Date; import com.fasterxml.jackson.annotation.JsonFormat; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; /** * @title: HomeQuarantineRegisterStatisticsVO * @projectName: 成都呐喊信息技术有限公司-智慧社区项目 * @description: 居家隔离统计信息 * @author: hans * @date: 2021/11/17 15:25 */ @Data @ApiModel("居家隔离统计信息") public class HomeQuarantineRegisterStatisticsVO { @ApiModelProperty("记录id") private Long id; @ApiModelProperty("用户id") private Long userId; @ApiModelProperty("开始登记时间") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") private Date createAt; @ApiModelProperty("用户昵称") private String nickName; @ApiModelProperty("题目") private String reserveSubContent; @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
@@ -4940,14 +4940,6 @@ R getWestCommunityLists(); /** * 综治app-小区列表 * @param villageListAppDTO 请求参数 * @return 小区列表 */ @PostMapping("/village/grid/list/app") R getGridVillageListApp(@RequestBody ComMngVillageListAppDTO villageListAppDTO); /** * 分页查询退役军人列表 * * @param comExServicemanDTO @@ -7012,22 +7004,6 @@ R applyDonates(@RequestBody ComActWarehouseApplyDTO comActWarehouseApplyDTO); /** * 生成二维码 * @param qrCodeVO * @return */ @PostMapping("/comActActivityCode/getQRCode") public R getQRCode(@RequestBody QRActivityCodeVO qrCodeVO); /** * 重置二维码 * @param qrCodeVO * @return */ @PostMapping("/comActActivityCode/resetQRCode") public R resetQRCode(@RequestBody QRActivityCodeVO qrCodeVO); /** * 实有房屋数据修复定时任务 * @return 实有房屋数据修复定时任务 */ @@ -7150,46 +7126,20 @@ */ @PostMapping("/elders/pensionAuthRecord/add") R addPensionAuthRecordVO(@RequestBody ComElderAuthRecordVO comElderAuthRecordVO); /** * 活动报名签到情况 * @param id 活动主键id * @return */ @GetMapping("/activity/sign-in/list") R listSignInRecord(@RequestParam("id") Long id); /** * 活动签到记录 * @param id 活动主键 * @param userId 用户id * 分页查询居家隔离统计 * @param detailedAdminDTO * @return */ @GetMapping("/activity/regist/list") R listRegistRecord(@RequestParam("id") Long id, @RequestParam("userId") Long userId); @PostMapping("/reserve/admin/register/homeQuarantine/page") R pageRegisterHomeQuarantine(PageReserveRegisterDetailedAdminDTO detailedAdminDTO); /** * 获取活动类型（目前只有志愿者活动需要获取） * @param communityId * @param type * 居家隔离导出 * @param pageReserveRegisterDetailedAdminDTO * @return */ @GetMapping("/activity/type/list") R listActivityType(@RequestParam("communityId") Long communityId, @RequestParam("type") Integer type); /** * 添加活动类型 * @param comActActivityTypeVO * @return */ @PostMapping("/activity/type/add") R addActivityType(@RequestBody ComActActivityTypeVO comActActivityTypeVO); /** * 我的评价 * @param userId * @param activityId * @return */ @GetMapping("/activity/my-evaluate") R listMyActivityEvaluate(@RequestParam("userId") Long userId, @RequestParam("activityId") Long activityId); @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
@@ -1,5 +1,24 @@ package com.panzhihua.community_backstage.api; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List; 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; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; @@ -12,38 +31,47 @@ import com.panzhihua.common.controller.BaseController; import com.panzhihua.common.enums.ReserveRecordStatusEnum; import com.panzhihua.common.model.dtos.community.QuestnaireAnswersDTO; import com.panzhihua.common.model.dtos.community.reserve.*; import com.panzhihua.common.model.dtos.community.reserve.AddReserveAdminDTO; import com.panzhihua.common.model.dtos.community.reserve.CancelReserveRecordDTO; import com.panzhihua.common.model.dtos.community.reserve.ComActReserveMakeStatisticsDTO; import com.panzhihua.common.model.dtos.community.reserve.ComActReserveRegisterStatisticsDTO; import com.panzhihua.common.model.dtos.community.reserve.EditComActReserveInfoDTO; import com.panzhihua.common.model.dtos.community.reserve.EditComActReserveStatusDTO; import com.panzhihua.common.model.dtos.community.reserve.EditReserveAdminDTO; import com.panzhihua.common.model.dtos.community.reserve.MakeHandleAdminDTO; import com.panzhihua.common.model.dtos.community.reserve.PageReserveAdminDTO; import com.panzhihua.common.model.dtos.community.reserve.PageReserveMakeAdminDTO; import com.panzhihua.common.model.dtos.community.reserve.PageReserveMakeHandleAdminDTO; import com.panzhihua.common.model.dtos.community.reserve.PageReserveRegisterDetailedAdminDTO; import com.panzhihua.common.model.vos.LoginUserInfoVO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComActQuestnaireAnswerContentVO; import com.panzhihua.common.model.vos.community.ComActQuestnaireSubVO; import com.panzhihua.common.model.vos.community.ComCvtBusinessVO; import com.panzhihua.common.model.vos.community.ComMngPopulationExcelVo; import com.panzhihua.common.model.vos.community.questnaire.QuestnaireStatisticsSummaryExcelAdminVO; import com.panzhihua.common.model.vos.community.reserve.*; import com.panzhihua.common.model.vos.community.reserve.ComActReserveListAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeDetailAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeDetailedExcelAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeHandleExcelAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeHandleListAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeListAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeRightExcelAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeRightStatisticsAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveMakeStatisticsAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRegisterDetailedVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRegisterExcelAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRegisterStatisticsAdminVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveSubListVO; import com.panzhihua.common.service.community.CommunityService; import com.panzhihua.common.service.user.UserService; import com.panzhihua.common.utlis.*; import com.panzhihua.common.utlis.AssemblyUtils; import com.panzhihua.common.utlis.HomeQuarantineUtil; import com.panzhihua.common.utlis.SFTPUtil; import com.panzhihua.community_backstage.config.SFTPConfig; import com.panzhihua.community_backstage.excel.CustemSecondhandler; import com.panzhihua.community_backstage.excel.Custemhandler; import com.panzhihua.community_backstage.excel.CustomSheetWriteHandler; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.springframework.beans.BeanUtils; import org.springframework.web.bind.annotation.*; import javax.annotation.Resource; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.net.URL; import java.text.SimpleDateFormat; import java.util.*; import java.util.stream.Collectors; /** * @description: 预约登记接口 @@ -467,15 +495,116 @@ @ApiOperation(value = "导出登记明细") @PostMapping("/register/list/export") public R exportRegister(@RequestBody PageReserveRegisterDetailedAdminDTO pageReserveRegisterDetailedAdminDTO) { String name=""; R r=communityService.detailReserveAdmin(pageReserveRegisterDetailedAdminDTO.getReserveId()); ComActReserveDetailAdminVO comActReserveDetailAdminVO=JSON.parseObject(JSON.toJSONString(r.getData()),ComActReserveDetailAdminVO.class); if(comActReserveDetailAdminVO.getTitle().contains("居家隔离")){ name = "居家隔离导出数据.xlsx"; String ftpUrl = "/mnt/data/web/excel/"; String name = "登记明细导出数据.xlsx"; String noHaed = "承诺"; try { SFTPUtil sftp = new SFTPUtil(sftpConfig.getUserName(), sftpConfig.getPassword(), sftpConfig.getHost(), sftpConfig.getPort()); sftp.login(); boolean existDir = sftp.isExistDir(ftpUrl + name); if (!existDir) { String property = System.getProperty("user.dir"); String fileName = property + File.separator + name; ExcelWriter excelWriter = null; InputStream inputStream = null; try { /** * 组合导出数据 * 用户问卷答案 */ R QuestnaireAnswersDTOR = communityService.exportRegisterAdmin(pageReserveRegisterDetailedAdminDTO); QuestnaireAnswersDTO questnaireAnswersDTOS = JSON.parseObject(JSON.toJSONString(QuestnaireAnswersDTOR.getData()), QuestnaireAnswersDTO.class); List<ComActQuestnaireSubVO> subVOList = questnaireAnswersDTOS.getSubs(); //构造表头 List<List<String>> headList = new ArrayList<List<String>>(); String firstRowContent = "登记明细"; List<String> head0 = new ArrayList<>(); head0.add(firstRowContent); head0.add("登记人昵称"); headList.add(head0); List<String> head1 = new ArrayList<>(); head1.add(firstRowContent); head1.add("登记时间"); headList.add(head1); subVOList.forEach(sub -> { if (sub.getType() != 5 && sub.getType() != 11) { List<String> headn = new ArrayList<>(); headn.add(firstRowContent); if(!sub.getContent().equals(noHaed)){ if(sub.getType() == 11){ headn.add(sub.getContent().substring(0, sub.getContent().indexOf("（"))); }else{ headn.add(sub.getContent()); } else { name = "登记明细导出数据.xlsx"; headList.add(headn); } } }); //数据转换 List<List<Object>> datalist = AssemblyUtils.ReserveData(questnaireAnswersDTOS.getAnswers()); WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置头字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)6); headWriteFont.setBold(true); headWriteCellStyle.setWriteFont(headWriteFont); WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); //设置表格内容字体 WriteFont writeFont = new WriteFont(); writeFont.setFontHeightInPoints((short)6); //设置 水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置 垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); contentWriteCellStyle.setWriteFont(writeFont); contentWriteCellStyle.setWrapped(true); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); excelWriter = EasyExcel.write(fileName) .registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new Custemhandler()) .registerWriteHandler(new CustomSheetWriteHandler()).build(); WriteSheet writeSheet = EasyExcel.writerSheet("登记明细导出数据").head(headList).build(); excelWriter.write(datalist, 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(sftpConfig.getExcelUrl() + name); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } @ApiOperation(value = "导出居家隔离统计") @PostMapping("/register/list/export/homeQuarantine") public R exportRegisterHomeQuarantineStatistics(@RequestBody PageReserveRegisterDetailedAdminDTO pageReserveRegisterDetailedAdminDTO) { String name= "居家隔离统计表.xlsx"; String ftpUrl = "/mnt/data/web/excel/"; String noHaed = "承诺"; List<List<Object>> datalist=new ArrayList<>(); @@ -493,12 +622,11 @@ * 组合导出数据 * 用户问卷答案 */ 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>>(); if(comActReserveDetailAdminVO.getTitle().contains("居家隔离")){ String firstRowContent = "重点地区或高风险地区来攀返攀人员信息统计表（居家隔离表）"; List<String> head0 = new ArrayList<>(); head0.add(firstRowContent); @@ -509,7 +637,8 @@ head1.add("登记时间"); headList.add(head1); subVOList.forEach(sub -> { if (sub.getType() != 5 && sub.getType() != 11&&!sub.getContent().contains("今日上午")&&!sub.getContent().contains("今日下午")&&!sub.getContent().contains("今日隔离情况")) { if (sub.getType() != 5 && sub.getType() != 11 && !sub.getContent().contains("今日上午") && !sub.getContent().contains("今日下午") && !sub.getContent().contains("今日隔离情况")) { List<String> headn = new ArrayList<>(); headn.add(firstRowContent); if(!sub.getContent().equals(noHaed)){ @@ -527,7 +656,7 @@ head2.add("隔离体温情况"); headList.add(head2); //数据转换 datalist = HomeQuarantineUtil.ReserveData(questnaireAnswersDTOS.getAnswers()); datalist = exportVO.getDataList(); WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); @@ -556,191 +685,7 @@ excelWriter = EasyExcel.write(fileName) .registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new CustemSecondhandler()) .build(); WriteSheet writeSheet = EasyExcel.writerSheet("登记明细导出数据").head(headList).build(); excelWriter.write(datalist, 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); } else { String firstRowContent = "登记明细"; List<String> head0 = new ArrayList<>(); head0.add(firstRowContent); head0.add("登记人昵称"); headList.add(head0); List<String> head1 = new ArrayList<>(); head1.add(firstRowContent); head1.add("登记时间"); headList.add(head1); subVOList.forEach(sub -> { if (sub.getType() != 5 && sub.getType() != 11) { List<String> headn = new ArrayList<>(); headn.add(firstRowContent); if(!sub.getContent().equals(noHaed)){ if(sub.getType() == 11){ headn.add(sub.getContent().substring(0, sub.getContent().indexOf("（"))); }else{ headn.add(sub.getContent()); } headList.add(headn); } } }); //数据转换 datalist = AssemblyUtils.ReserveData(questnaireAnswersDTOS.getAnswers()); WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置头字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)5); headWriteFont.setBold(true); headWriteCellStyle.setWriteFont(headWriteFont); WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); //设置表格内容字体 WriteFont writeFont = new WriteFont(); writeFont.setFontHeightInPoints((short)5); //设置 水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置 垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); contentWriteCellStyle.setWriteFont(writeFont); contentWriteCellStyle.setWrapped(true); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); excelWriter = EasyExcel.write(fileName) .registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new Custemhandler()) .build(); WriteSheet writeSheet = EasyExcel.writerSheet("登记明细导出数据").head(headList).build(); excelWriter.write(datalist, 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(sftpConfig.getExcelUrl() + name); } catch (Exception e) { e.printStackTrace(); log.error("文件传输失败【{}】", e.getMessage()); return R.fail(); } } @ApiOperation(value = "导出登记明细") @PostMapping("/register/list/export/homeQuarantine") public R exportRegisterHomeQuarantine(@RequestBody PageReserveRegisterDetailedAdminDTO pageReserveRegisterDetailedAdminDTO) { String ftpUrl = "/mnt/data/web/excel/"; String name = "登记明细导出数据.xlsx"; String noHaed = "承诺"; try { SFTPUtil sftp = new SFTPUtil(sftpConfig.getUserName(), sftpConfig.getPassword(), sftpConfig.getHost(), sftpConfig.getPort()); sftp.login(); boolean existDir = sftp.isExistDir(ftpUrl + name); if (!existDir) { String property = System.getProperty("user.dir"); String fileName = property + File.separator + name; ExcelWriter excelWriter = null; InputStream inputStream = null; try { /** * 组合导出数据 * 用户问卷答案 */ R QuestnaireAnswersDTOR = communityService.exportRegisterAdmin(pageReserveRegisterDetailedAdminDTO); QuestnaireAnswersDTO questnaireAnswersDTOS = JSON.parseObject(JSON.toJSONString(QuestnaireAnswersDTOR.getData()), QuestnaireAnswersDTO.class); List<ComActQuestnaireSubVO> subVOList = questnaireAnswersDTOS.getSubs(); //构造表头 List<List<String>> headList = new ArrayList<List<String>>(); String firstRowContent = "重点地区或高风险地区来攀返攀人员信息统计表（居家隔离表）"; List<String> head0 = new ArrayList<>(); head0.add(firstRowContent); head0.add("登记人昵称"); headList.add(head0); List<String> head1 = new ArrayList<>(); head1.add(firstRowContent); head1.add("登记时间"); headList.add(head1); subVOList.forEach(sub -> { if (sub.getType() != 5 && sub.getType() != 11&&!sub.getContent().contains("今日上午")&&!sub.getContent().contains("今日下午")&&!sub.getContent().contains("今日隔离情况")) { List<String> headn = new ArrayList<>(); headn.add(firstRowContent); if(!sub.getContent().equals(noHaed)){ if(sub.getType() == 11){ headn.add(sub.getContent().substring(0, sub.getContent().indexOf("（"))); }else{ headn.add(sub.getContent()); } headList.add(headn); } } }); List<String> head2 = new ArrayList<>(); head2.add(firstRowContent); head2.add("隔离体温情况"); headList.add(head2); //数据转换 List<List<Object>> datalist = HomeQuarantineUtil.ReserveData(questnaireAnswersDTOS.getAnswers()); WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置头字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)6); headWriteFont.setBold(true); headWriteCellStyle.setWriteFont(headWriteFont); WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); //设置表格内容字体 WriteFont writeFont = new WriteFont(); writeFont.setFontHeightInPoints((short)6); //设置 水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置 垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); contentWriteCellStyle.setWriteFont(writeFont); contentWriteCellStyle.setWrapped(true); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); excelWriter = EasyExcel.write(fileName) .registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new Custemhandler()) .build(); WriteSheet writeSheet = EasyExcel.writerSheet("登记明细导出数据").head(headList).build(); WriteSheet writeSheet = EasyExcel.writerSheet("居家隔离统计表").head(headList).build(); excelWriter.write(datalist, writeSheet); excelWriter.finish(); File file = new File(fileName); @@ -769,6 +714,12 @@ } } @ApiOperation(value = "分页查询居家隔离统计", response = ComActReserveRegisterDetailedVO.class) @PostMapping("/register/homeQuarantine/page") public R pageRegisterHomeQuarantine(@RequestBody PageReserveRegisterDetailedAdminDTO detailedAdminDTO) { return communityService.pageRegisterHomeQuarantine(detailedAdminDTO); } @ApiOperation(value = "查询题目列表", response = ComActReserveSubListVO.class) @GetMapping("/subject/list") public R subjectList(@RequestParam("reserveId") Long reserveId) { springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/ComActReserveApi.java
@@ -322,4 +322,24 @@ public R list(@RequestParam("communityId") Long communityId){ return comActReserveService.reserveListApplets(communityId); } /** * 分页查询居家隔离统计 * @param detailedAdminDTO * @return */ @PostMapping("/admin/register/homeQuarantine/page") 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
@@ -7,6 +7,7 @@ import com.panzhihua.common.model.vos.community.ComActQuestnaireAnswerContentVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRegisterDetailedAnswerVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRegisterDetailedVO; import com.panzhihua.common.model.vos.community.reserve.HomeQuarantineRegisterStatisticsVO; import com.panzhihua.service_community.model.dos.ComActReserveAnswerContentDO; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; @@ -50,4 +51,14 @@ ComActReserveRegisterDetailedVO getRegisterDetailedById(@Param("reserveRecordId") Long reserveRecordId); 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/dao/ConvenientMerchantDAO.java
@@ -51,6 +51,8 @@ */ ConvenientMerchantVO selectMerchantById(@Param("merchantId") Long merchantId); List<ConvenientMerchantVO> selectMerchantByName(@Param("communityId") Long communityId, @Param("currentMon") String currentMon); /** * 获取社区下热门商家 * springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComActReserveAnswerContentService.java
@@ -26,4 +26,17 @@ */ R registerDetailedListAdmin(PageReserveRegisterDetailedAdminDTO detailedAdminDTO); /** * 分页查询居家隔离统计 * @param detailedAdminDTO * @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,5 +1,18 @@ 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; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; @@ -9,25 +22,18 @@ import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComActQuestnaireAnswerContentVO; import com.panzhihua.common.model.vos.community.ComActQuestnaireSubVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRecordListVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRegisterDetailedAnswerVO; import com.panzhihua.common.model.vos.community.reserve.ComActReserveRegisterDetailedVO; import com.panzhihua.common.model.vos.community.reserve.HomeQuarantineRegisterStatisticsVO; import com.panzhihua.common.utlis.DateUtils; import com.panzhihua.common.utlis.StringUtils; import com.panzhihua.service_community.dao.ComActReserveAnswerContentMapper; import com.panzhihua.service_community.dao.ComActReserveSubMapper; import com.panzhihua.service_community.model.dos.ComActQuestnaireAnswerContentDO; import com.panzhihua.service_community.model.dos.ComActQuestnaireSubDO; import com.panzhihua.service_community.model.dos.ComActReserveAnswerContentDO; import com.panzhihua.service_community.model.dos.ComActReserveSubDO; import com.panzhihua.service_community.service.ComActReserveAnswerContentService; import com.panzhihua.service_community.service.ComActReserveSubService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeanUtils; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.*; import lombok.extern.slf4j.Slf4j; /** * @auther lyq @@ -49,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); @@ -125,4 +119,176 @@ resultPage.setRecords(resultMapList); return R.ok(resultPage); } /** * 分页查询居家隔离统计 * @param detailedAdminDTO * @return */ @Override public R pageRegisterHomeQuarantine(PageReserveRegisterDetailedAdminDTO detailedAdminDTO) { IPage<HashMap<String,String>> resultPage = new Page<>(); Page page = new Page(detailedAdminDTO.getPageNum(), detailedAdminDTO.getPageSize()); IPage<String> statisticsVOIPage = this.baseMapper.pageRegisterHomeQuarantine(page, detailedAdminDTO); resultPage.setCurrent(statisticsVOIPage.getCurrent()); resultPage.setTotal(statisticsVOIPage.getTotal()); resultPage.setSize(statisticsVOIPage.getSize()); resultPage.setPages(statisticsVOIPage.getPages()); List<String> records = statisticsVOIPage.getRecords(); List<HashMap<String,String>> resultMapList = new ArrayList<>(); if (!records.isEmpty()) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); int nub = 1; for (String record : records) { //渲染序号 HashMap<String,String> map = new HashMap<>(); 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 -> { 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()); } } else { map.put(e.getReserveSubId() + "", e.getAnswerContent()); } }); map.put("isolation", conditions.toString()); resultMapList.add(map); nub++; } } 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/java/com/panzhihua/service_community/service/impl/ConvenientMerchantServiceImpl.java
@@ -133,6 +133,9 @@ if(comActDO!=null){ convenientMerchantDO.setCommunityName(comActDO.getName()); } else { convenientMerchantDO.setCommunityName("西区社区"); } } String account = convenientMerchantDTO.getAccount(); if (isNotBlank(account)) { @@ -286,6 +289,15 @@ page.setSize(pagePopularMerchantDTO.getPageSize()); page.setCurrent(pagePopularMerchantDTO.getPageNum()); IPage<ConvenientMerchantVO> merchantVOList = this.baseMapper.getPopularMerchants(page, pagePopularMerchantDTO.getCommunityId(), currentMon); if(pagePopularMerchantDTO.getPageNum().equals(1L)){ //第一页默认把犇师傅维修中心加载到第一个 List<ConvenientMerchantVO> merchantList = this.baseMapper.selectMerchantByName(pagePopularMerchantDTO.getCommunityId(), currentMon); if(merchantList != null && merchantList.size() > 0){ merchantList.addAll(merchantVOList.getRecords()); merchantVOList.setRecords(merchantList); } } return R.ok(merchantVOList); }
@@ -138,4 +138,58 @@ GROUP BY ac.id ) ac left join sys_user t on ac.user_id = t.user_id order by ac.reserve_record_id desc,ac.id asc </select> <select id="pageRegisterHomeQuarantine" 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> <select id="selectStatistics" resultType="com.panzhihua.common.model.vos.community.reserve.HomeQuarantineRegisterStatisticsVO"> 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> springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/resources/mapper/ComElderAuthRecordsMapper.xml
@@ -79,6 +79,67 @@ <if test="pageElderAuthRecordsDTO.personnelCategory != null and pageElderAuthRecordsDTO.personnelCategory != 0"> and ceae.personnel_category = #{pageElderAuthRecordsDTO.personnelCategory} </if> <if test="pageElderAuthRecordsDTO.isAlive != null"> and ceae.is_alive = #{pageElderAuthRecordsDTO.isAlive} </if> <if test="pageElderAuthRecordsDTO.searchKey != null and pageElderAuthRecordsDTO.searchKey != ''"> and (ceae.`name` like concat ('%', #{pageElderAuthRecordsDTO.searchKey}, '%') or cear.mark like concat ('%', #{pageElderAuthRecordsDTO.searchKey}, '%') or ceae.id_card = #{pageElderAuthRecordsDTO.idCard}) </if> order by cear.create_at desc </select> <select id="export" resultType="com.panzhihua.common.model.vos.community.ComElderAuthRecordVO"> SELECT cear.id, su.`name` submitUserName, ceae.phone, ceae.`name`, ceae.id_card, ceae.sex, ceae.birthday, ceae.personnel_category, cear.address, ceae.is_alive, cear.auth_method, cear.auth_period, cear.auth_video, cear.auth_date, cear.mark, cear.approval_status, cear.approver_id, su1.`name` as approverName, cear.approval_date, cear.auth_status FROM com_elder_auth_records cear LEFT JOIN com_elder_auth_elderlies ceae ON cear.elderlies_id = ceae.id LEFT JOIN sys_user su ON cear.submit_user_id = su.user_id LEFT JOIN sys_user su1 ON cear.approver_id = su1.user_id WHERE ceae.community_id = #{pageElderAuthRecordsDTO.communityId} <if test="pageElderAuthRecordsDTO.authPeriod != null and pageElderAuthRecordsDTO.authPeriod !=''"> and cear.auth_period = #{pageElderAuthRecordsDTO.authPeriod} </if> <if test="pageElderAuthRecordsDTO.authMethod != null and pageElderAuthRecordsDTO.authMethod != 0"> and cear.auth_method = #{pageElderAuthRecordsDTO.authMethod} </if> <if test="pageElderAuthRecordsDTO.authDateBegin != null and pageElderAuthRecordsDTO.authDateBegin != ''"> and cear.auth_date <![CDATA[ >= ]]> #{pageElderAuthRecordsDTO.authDateBegin} </if> <if test="pageElderAuthRecordsDTO.authDateEnd != null and pageElderAuthRecordsDTO.authDateEnd != ''"> and cear.auth_date <![CDATA[ <= ]]> #{pageElderAuthRecordsDTO.authDateEnd} </if> <if test="pageElderAuthRecordsDTO.approvalStatus != null and pageElderAuthRecordsDTO.approvalStatus != 0"> and cear.approval_status = #{pageElderAuthRecordsDTO.approvalStatus} </if> <if test="pageElderAuthRecordsDTO.authStatus != null and pageElderAuthRecordsDTO.authStatus != 0"> and cear.auth_status = #{pageElderAuthRecordsDTO.authStatus} </if> <if test="pageElderAuthRecordsDTO.personnelCategory != null and pageElderAuthRecordsDTO.personnelCategory != 0"> and ceae.personnel_category = #{pageElderAuthRecordsDTO.personnelCategory} </if> <if test="pageElderAuthRecordsDTO.isAlive != null and pageElderAuthRecordsDTO.isAlive != 0"> and ceae.is_alive = #{pageElderAuthRecordsDTO.isAlive} </if> springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/resources/mapper/ConvenientMerchantMapper.xml
@@ -68,6 +68,46 @@ LEFT JOIN sys_user su ON ccm.user_id = su.user_id WHERE ccm.id = #{merchantId} </select> <select id="selectMerchantByName" resultType="com.panzhihua.common.model.vos.community.convenient.ConvenientMerchantVO"> SELECT temp.id, temp.NAME, temp.introduction, temp.logo, temp.serviceScope, IF ( SUM( temp.consultation_volume ) IS NULL, 0, SUM( temp.consultation_volume )) AS monthConsultationVolume FROM ( SELECT ccm.id, ccm.NAME, ccm.introduction, ccm.logo, cccs.consultation_volume, GROUP_CONCAT( ccss.service_name ) AS serviceScope FROM com_convenient_merchants ccm LEFT JOIN ( SELECT * FROM com_convenient_consultation_statistics WHERE statistic_date LIKE #{currentMon}) cccs ON ccm.id = cccs.merchant_id LEFT JOIN com_convenient_service_scope ccss ON ccm.id = ccss.merchant_id WHERE ( ccm.community_id = #{communityId} or ccm.community_id = 0) AND ccm.business_status = 1 and ccm.is_del = 0 and ccm.`name` = '犇师傅维修中心' GROUP BY cccs.id ) temp GROUP BY temp.id ORDER BY SUM( temp.consultation_volume ) DESC </select> <select id="getPopularMerchants" resultType="com.panzhihua.common.model.vos.community.convenient.ConvenientMerchantVO"> SELECT temp.id, temp.name, temp.introduction, temp.logo, @@ -79,7 +119,7 @@ FROM com_convenient_merchants ccm LEFT JOIN (SELECT * FROM com_convenient_consultation_statistics WHERE statistic_date LIKE #{currentMon}) cccs ON ccm.id = cccs.merchant_id LEFT JOIN com_convenient_service_scope ccss ON ccm.id = ccss.merchant_id WHERE ( ccm.community_id = #{communityId} or ccm.community_id = 0) AND ccm.business_status = 1 and ccm.is_del = 0 GROUP BY cccs.id WHERE ( ccm.community_id = #{communityId} or ccm.community_id = 0) AND ccm.business_status = 1 and ccm.is_del = 0 and ccm.`name` != '犇师傅维修中心' GROUP BY cccs.id ) temp GROUP BY temp.id ORDER BY SUM(temp.consultation_volume) DESC </select> <select id="getClassifyMerchants" resultType="com.panzhihua.common.model.vos.community.convenient.ConvenientMerchantVO">
