springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/questnaire/ExportAnswerResultVo.java
New file @@ -0,0 +1,11 @@ package com.panzhihua.common.model.vos.community.questnaire; import lombok.Data; /** * @Author: llming * @Description: 导出调查问卷答题结果记录 */ @Data public class ExportAnswerResultVo { } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommunityQuestnaireApi.java
@@ -1,21 +1,40 @@ 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.JSON; import com.panzhihua.common.controller.BaseController; import com.panzhihua.common.enums.PopulIsOkEnum; import com.panzhihua.common.enums.PopulPoliticalOutlookEnum; import com.panzhihua.common.enums.PopulSexEnum; import com.panzhihua.common.model.dtos.community.PageComCvtBusinessAppletsDTO; import com.panzhihua.common.model.dtos.community.PageQuestnaireDTO; import com.panzhihua.common.model.vos.LoginUserInfoVO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComCvtBusinessVO; import com.panzhihua.common.model.vos.community.ComMngPopulationExcelVo; import com.panzhihua.common.model.vos.community.ComMngPopulationVO; import com.panzhihua.common.model.vos.community.questnaire.EditComActQuestnaireVo; import com.panzhihua.common.model.vos.community.questnaire.QuestnaireVO; import com.panzhihua.common.service.community.CommunityService; import com.panzhihua.common.service.user.UserService; import com.panzhihua.common.utlis.SFTPUtil; import com.panzhihua.community_backstage.config.SFTPConfig; 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.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.util.ArrayList; import java.util.List; /** * @description: 社区通/调查问卷接口 @@ -30,6 +49,12 @@ @Resource private CommunityService communityService; @Resource private SFTPConfig sftpConfig; static { } @ApiOperation(value = "分页查询调查问卷", response = ComCvtBusinessVO.class) @PostMapping("/page") @@ -90,8 +115,71 @@ @ApiOperation(value = "导出数据", response = ComCvtBusinessVO.class) @GetMapping("/export") public R export(@RequestBody PageComCvtBusinessAppletsDTO comCvtBusinessAppletsDTO) { return null; public R export(@RequestParam("questId") Long questId) { //获取登陆用户 LoginUserInfoVO loginUserInfo = this.getLoginUserInfo(); //获取登陆用户绑定社区id Long communityId = loginUserInfo.getCommunityId(); //生成动态模板excel通过ftp工具上传到主节点，然后返回模板下载地址 String ftpUrl = "/mnt/data/web/excel/"; String name = "问卷调查答题情况.xlsx"; 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; // 这里 需要指定写用哪个class去写 // 这里 需要指定写用哪个class去写 ExcelWriter excelWriter = null; InputStream inputStream = null; try { List<ComMngPopulationVO> populList = null; populList = JSON.parseArray(JSON.toJSONString(communityService.getPopulationLists(questId).getData()), ComMngPopulationVO.class); List<ComMngPopulationExcelVo> populationExcelVoList = new ArrayList<>(); if (populList != null && populList.size() > 0) { for (ComMngPopulationVO popul : populList) { ComMngPopulationExcelVo populationExcelVo = new ComMngPopulationExcelVo(); BeanUtils.copyProperties(popul, populationExcelVo); //设置性别 populationExcelVo.setSex(PopulSexEnum.getCnDescByName(popul.getSex())); //设置政治面貌 populationExcelVo.setPoliticalOutlook(PopulPoliticalOutlookEnum.getCnDescByName(popul.getPoliticalOutlook())); //设置是否租住 populationExcelVo.setIsRent(PopulIsOkEnum.getCnDescByName(popul.getIsRent())); populationExcelVoList.add(populationExcelVo); } } excelWriter = EasyExcel.write(fileName, ComMngPopulationExcelVo.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomSheetWriteHandler()).build(); WriteSheet writeSheet = EasyExcel.writerSheet("问卷调查答题导出数据").build(); excelWriter.write(populationExcelVoList, 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(); } } } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/config/SFTPConfig.java
New file @@ -0,0 +1,30 @@ package com.panzhihua.community_backstage.config; import lombok.Data; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Configuration; /** * @Author: llming * @Description: */ @Configuration @Data public class SFTPConfig { // 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; } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComActQuestnaireService.java
@@ -62,4 +62,11 @@ * @param questId 问卷ID */ R answerInfoQuestnaire(Long userId, Long questId); /** * 问卷答题结果 * * @param questId 问卷ID */ R answerResultList(Long questId); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComActQuestnaireServiceImpl.java
@@ -176,7 +176,7 @@ } @Override public R statQuestnaire(Long questId){ public R statQuestnaire(Long questId) { ComActQuestnaireDO comActQuestnaireDO = questnaireDAO.selectById(questId); QuestnaireAnswerStatVO usersAnswerStatVO = new QuestnaireAnswerStatVO(); @@ -193,7 +193,7 @@ List<UsersSubAnswerStatVO> usersSubAnswerStatVOList = new ArrayList<>(); comActQuestnaireSubDOList.forEach(comActQuestnaireSubDO->{ comActQuestnaireSubDOList.forEach(comActQuestnaireSubDO -> { UsersSubAnswerStatVO usersSubAnswerStatVO = new UsersSubAnswerStatVO(); ComActQuestnaireSubVO comActQuestnaireSubVO = new ComActQuestnaireSubVO(); @@ -203,13 +203,13 @@ List<UserSubAnswerSelectionStatVO> userSubAnswerSelectionStatVOList = new ArrayList<>(); usersSubAnswerStatVO.setTotal(0); usearSubAnswerSelectionStatVOList.forEach(stat ->{ if(stat.getSubId()==comActQuestnaireSubDO.getId()){ usearSubAnswerSelectionStatVOList.forEach(stat -> { if (stat.getSubId() == comActQuestnaireSubDO.getId()) { userSubAnswerSelectionStatVOList.add(stat); } }); usersSubAnswerStatVO.setUsersAnswer(userSubAnswerSelectionStatVOList); Integer count = questnaireAnswerContentDAO.selectCount(new QueryWrapper<ComActQuestnaireAnswerContentDO>().select(" DISTINCT sub_id, user_id ").lambda().eq(ComActQuestnaireAnswerContentDO::getSubId,comActQuestnaireSubDO.getId())); Integer count = questnaireAnswerContentDAO.selectCount(new QueryWrapper<ComActQuestnaireAnswerContentDO>().select(" DISTINCT sub_id, user_id ").lambda().eq(ComActQuestnaireAnswerContentDO::getSubId, comActQuestnaireSubDO.getId())); usersSubAnswerStatVO.setTotal(count); usersSubAnswerStatVOList.add(usersSubAnswerStatVO); }); @@ -226,8 +226,8 @@ public R answerInfoQuestnaire(Long userId, Long questId) { ComActQuestnaireDO comActQuestnaireDO = questnaireDAO.selectById(questId); if(comActQuestnaireDO==null){ return R.fail("问卷不存在!"); if (comActQuestnaireDO == null) { return R.fail("问卷不存在!"); } UsersAnswerVO usersAnswerVO = new UsersAnswerVO(); @@ -239,7 +239,7 @@ list.sort(Comparator.comparing(ComActQuestnaireSubDO::getSort).reversed()); List<UsersSubAnswerVO> usersSubAnswerVOList = new ArrayList<>(); list.forEach(comActQuestnaireSubDO ->{ list.forEach(comActQuestnaireSubDO -> { UsersSubAnswerVO usersSubAnswerVO = new UsersSubAnswerVO(); //问卷调查题目 ComActQuestnaireSubVO comActQuestnaireSubVO = new ComActQuestnaireSubVO(); @@ -260,7 +260,7 @@ }); usersSubAnswerVO.setQuestSelectionList(comActQuestnaireSubSelectionVOs); //用户回答 if(userId!=null) { if (userId != null) { List<ComActQuestnaireAnswerContentDO> questnaireAnswerContentDOList = questnaireAnswerContentDAO.selectList(new QueryWrapper<ComActQuestnaireAnswerContentDO>().lambda().eq(ComActQuestnaireAnswerContentDO::getUserId, userId) .eq(ComActQuestnaireAnswerContentDO::getSubId, subId)); @@ -281,4 +281,13 @@ return R.ok(usersAnswerVO); } @Override public R answerResultList(Long questId) { LambdaQueryWrapper<ComActQuestnaireSubDO> subParam = new LambdaQueryWrapper<ComActQuestnaireSubDO>().eq(ComActQuestnaireSubDO::getQueId, questId); List<ComActQuestnaireSubDO> subDOS = questnaireSubDAO.selectList(subParam); new LambdaQueryWrapper<ComActQuestnaireAnswerContentDO>().eq(ComActQuestnaireAnswerContentDO::get) questnaireAnswerContentDAO.selectList() } }
