From ff1fec7eae681e89e607fd441d2597ab67b5ed2a Mon Sep 17 00:00:00 2001 From: guyue <1721849008@qq.com> Date: 星期二, 12 八月 2025 19:04:34 +0800 Subject: [PATCH] 修改查看时间和删除关键词 --- src/main/java/com/linghu/service/impl/KeywordServiceImpl.java | 534 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 1 files changed, 514 insertions(+), 20 deletions(-) diff --git a/src/main/java/com/linghu/service/impl/KeywordServiceImpl.java b/src/main/java/com/linghu/service/impl/KeywordServiceImpl.java index c8a3bd6..389b4c0 100644 --- a/src/main/java/com/linghu/service/impl/KeywordServiceImpl.java +++ b/src/main/java/com/linghu/service/impl/KeywordServiceImpl.java @@ -1,21 +1,57 @@ package com.linghu.service.impl; +import com.alibaba.excel.EasyExcel; +import com.alibaba.excel.ExcelWriter; +import com.alibaba.excel.write.handler.SheetWriteHandler; +import com.alibaba.excel.write.metadata.WriteSheet; +import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; +import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; +import com.linghu.config.FinalStatus; +import com.linghu.mapper.ReferenceMapper; import com.linghu.model.common.ResponseResult; +import com.linghu.model.dto.ExportFeedDTO; +import com.linghu.model.dto.ExportGetResultByPlatformIdDTO; +import com.linghu.model.dto.ExportGetResultDTO; +import com.linghu.model.dto.ExportStaticsDTO; import com.linghu.model.entity.Keyword; +import com.linghu.model.entity.KeywordTask; +import com.linghu.model.entity.Platform; +import com.linghu.model.entity.Reference; +import com.linghu.model.excel.FeedExportExcel; +import com.linghu.model.excel.ReferenceExcel; import com.linghu.model.vo.*; import com.linghu.model.vo.KeywordStaticsListVO; import com.linghu.service.KeywordService; import com.linghu.mapper.KeywordMapper; +import com.linghu.service.KeywordTaskService; +import com.linghu.service.PlatformService; +import com.linghu.service.ReferenceService; +import org.apache.poi.ss.usermodel.DataValidation; +import org.apache.poi.ss.usermodel.DataValidationConstraint; +import org.apache.poi.ss.usermodel.DataValidationHelper; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.util.CellRangeAddressList; +import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.http.HttpHeaders; +import org.springframework.http.MediaType; +import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Service; import org.springframework.util.StringUtils; +import org.springframework.web.multipart.MultipartFile; +import java.io.ByteArrayOutputStream; +import java.time.LocalDate; +import java.time.format.DateTimeFormatter; +import java.time.format.DateTimeParseException; import java.util.ArrayList; import java.util.List; import java.util.Map; +import java.util.Objects; +import java.util.stream.Collectors; /** * @author xy @@ -27,6 +63,15 @@ @Autowired private KeywordMapper keywordMapper; + @Autowired + private ReferenceService referenceService; + @Autowired + private ReferenceMapper referenceMapper; + @Autowired + private PlatformService platformService; + @Autowired + private KeywordTaskService keywordTaskService; + @Override public ResponseResult<KeywordStaticsListVO> statics(Integer keywordId, Integer questionId) { KeywordStaticsListVO vo = new KeywordStaticsListVO(); @@ -35,14 +80,16 @@ if (keyword == null) { return ResponseResult.error("关键词不存在"); } - if (!"completed".equals(keyword.getStatus())) { - return ResponseResult.error("关键词采集未完成"); + if (!FinalStatus.COMPLETED.getValue().equals(keyword.getStatus())) { + return ResponseResult.success(); } List<KeywordStaticsVO> statics = this.getBaseMapper().statics(keywordId, questionId, keyword.getNum()); - vo.setNowRecord(statics); + List<KeywordStaticsVO> top30 = statics.subList(0, Math.min(statics.size(), 30)); + vo.setNowRecord(top30); if (keyword.getNum() > 1) { - statics = this.getBaseMapper().statics(keywordId, questionId, keyword.getNum() - 1); - vo.setBeforeRecord(statics); + statics = this.getBaseMapper().statics(keywordId, questionId, 1); + top30 = statics.subList(0, Math.min(statics.size(), 30)); + vo.setBeforeRecord(top30); } return ResponseResult.success(vo); } @@ -55,7 +102,7 @@ if (keyword == null) { return ResponseResult.error("关键词不存在"); } - if (!"completed".equals(keyword.getStatus())) { + if (!FinalStatus.COMPLETED.getValue().equals(keyword.getStatus())) { return ResponseResult.error("关键词采集未完成"); } List<PlatformProportionVO> result = this.getBaseMapper().getResultByTypeId(keywordId, questionId, @@ -71,7 +118,7 @@ if (keyword == null) { return ResponseResult.error("关键词不存在"); } - if (!"completed".equals(keyword.getStatus())) { + if (!FinalStatus.COMPLETED.getValue().equals(keyword.getStatus())) { return ResponseResult.error("关键词采集未完成"); } @@ -87,22 +134,469 @@ return keywordMapper.selectList(queryWrapper); } + @Override - public Boolean saveKeywords(String keywords, String order_id) { - if (StringUtils.hasText(keywords)) { - List<Keyword> keywordList = new ArrayList<>(); - String[] keywordArray = keywords.split("\\n"); - for (String keyword : keywordArray) { - Keyword newKeyword = new Keyword(); - newKeyword.setKeyword_name(keyword); - newKeyword.setOrder_id(order_id); - newKeyword.setStatus("notSubmitted"); - keywordList.add(newKeyword); - } - return this.saveBatch(keywordList); + public ResponseResult<GetTimeVO> getTimeVOResponseResult(Integer keywordId, Integer questionId) { + // + GetTimeVO vo = new GetTimeVO(); + Keyword keyword = this.getById(keywordId); + + LambdaQueryWrapper<KeywordTask> queryWrapper = new LambdaQueryWrapper<>(); + queryWrapper.eq(KeywordTask::getKeyword_id, keywordId); + queryWrapper.eq(KeywordTask::getNum,keyword.getNum()); + + List<KeywordTask> list = keywordTaskService.list(queryWrapper); + if (null != list && !list.isEmpty()) { + vo.setNow(list.get(0).getCreate_time()); + } + //首次 + LambdaQueryWrapper<KeywordTask> queryWrapper2 = new LambdaQueryWrapper<>(); + queryWrapper2.eq(KeywordTask::getKeyword_id, keywordId); + queryWrapper2.eq(KeywordTask::getNum,1); + + List<KeywordTask> list2 = keywordTaskService.list(queryWrapper2); + if (null != list2 && !list2.isEmpty()) { + vo.setFirst(list2.get(0).getCreate_time()); + } + + return ResponseResult.success(vo); + } + + @Override + public ResponseEntity<byte[]> exportEChart(ExportStaticsDTO exportStaticsDTO) { + Keyword keyword = keywordMapper.selectById(exportStaticsDTO.getKeywordId()); + List<KeywordStaticsVO> voList=new ArrayList<>(); + if (exportStaticsDTO.getIsFirst()==0){ + voList = keywordMapper.statics(exportStaticsDTO.getKeywordId(), exportStaticsDTO.getQuestionId(), keyword.getNum()); + }else { - return false; + voList = keywordMapper.statics(exportStaticsDTO.getKeywordId(), exportStaticsDTO.getQuestionId(), 1); + + } + + // 3. 导出Excel + ByteArrayOutputStream out = new ByteArrayOutputStream(); + EasyExcel.write(out, KeywordStaticsVO.class) + .sheet("引用数据") + .doWrite(voList); + + // 4. 构建响应 + return ResponseEntity.ok() + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=references_export.xlsx") + .contentType(MediaType.APPLICATION_OCTET_STREAM) + .body(out.toByteArray()); + } + + @Override + public ResponseResult<List<PlatformProportionVO>> getByTypeId(Integer keywordId, Integer questionId, Integer typeId, Integer isNow) { + Keyword keyword = this.getById(keywordId); + + + if (isNow == 0) { + List<PlatformProportionVO> result = keywordMapper.getResultByTypeId(keywordId, questionId, + keyword.getNum() , typeId); + // 检查列表是否为空或只包含null元素 + boolean isValid = result != null && result.stream() + .anyMatch(Objects::nonNull); + + return isValid ? ResponseResult.success(result) + : ResponseResult.success(new ArrayList<>()); + }else { + List<PlatformProportionVO> result = keywordMapper.getResultByTypeId(keywordId, questionId, 1, typeId); + boolean isValid = result != null && result.stream() + .anyMatch(Objects::nonNull); + + return isValid ? ResponseResult.success(result) + : ResponseResult.success(new ArrayList<>()); + } } + @Override + public ResponseEntity<byte[]> exportResult(ExportGetResultDTO dto) { + Keyword keyword = this.getById(dto.getKeywordId()); + List<PlatformProportionVO> result =new ArrayList<>(); + + if (dto.getIsNow()==0){ + result = keywordMapper.getResultByTypeId(dto.getKeywordId(), dto.getQuestionId(), + keyword.getNum() , dto.getTypeId()); + }else { + result = keywordMapper.getResultByTypeId(dto.getKeywordId(), dto.getQuestionId(), + 1 , dto.getTypeId()); + } + + //查询所有平台名称 + List<String> typeNameList = result.stream() + .map(PlatformProportionVO::getType_name) + .filter(Objects::nonNull) + .distinct() + .collect(Collectors.toList()); + + // 3. 导出Excel + ByteArrayOutputStream out = new ByteArrayOutputStream(); + // 3. 使用自定义的SheetWriteHandler来添加数据验证 + ExcelWriter excelWriter = EasyExcel.write(out, PlatformProportionVO.class) + .registerWriteHandler(new SheetWriteHandler() { + @Override + public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, + WriteSheetHolder writeSheetHolder) { + Sheet sheet = writeSheetHolder.getSheet(); + + // 添加数据验证(下拉框) + DataValidationHelper helper = sheet.getDataValidationHelper(); + CellRangeAddressList rangeList = new CellRangeAddressList( + 1, 65535, 0, 0); // D列(第4列) + + DataValidationConstraint constraint = helper.createExplicitListConstraint( + typeNameList.toArray(new String[0])); + DataValidation validation = helper.createValidation(constraint, rangeList); + sheet.addValidationData(validation); + } + }) + .build(); + + + WriteSheet writeSheet = EasyExcel.writerSheet("平台分布占比").build(); + excelWriter.write(result, writeSheet); + excelWriter.finish(); + + // 4. 构建响应 + return ResponseEntity.ok() + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=references_export.xlsx") + .contentType(MediaType.APPLICATION_OCTET_STREAM) + .body(out.toByteArray()); + } + + @Override + public ResponseResult<List<ResultListVO>> getResultByPlatform(Integer keywordId, Integer questionId, Integer platformId, Integer isNow) { + Keyword keyword = this.getById(keywordId); + if (isNow == 0) { + List<ResultListVO> result = keywordMapper.getResultByPlatformId(keywordId, questionId, keyword.getNum(), + platformId); + boolean isValid = result != null && result.stream() + .anyMatch(Objects::nonNull); + + return isValid ? ResponseResult.success(result) + : ResponseResult.success(new ArrayList<>()); + } else { + List<ResultListVO> result = keywordMapper.getResultByPlatformId(keywordId, questionId, 1, + platformId); + boolean isValid = result != null && result.stream() + .anyMatch(Objects::nonNull); + + return isValid ? ResponseResult.success(result) + : ResponseResult.success(new ArrayList<>()); + } + } + + @Override + public ResponseEntity<byte[]> exportResultByPlatform(ExportGetResultByPlatformIdDTO dto) { + Keyword keyword = this.getById(dto.getKeywordId()); + List<ResultListVO> result=new ArrayList<>(); + if (dto.getIsNow()==0){ + result = keywordMapper.getResultByPlatformId(dto.getKeywordId(), dto.getQuestionId(), keyword.getNum(), + dto.getPlatformId()); + }else { + result = keywordMapper.getResultByPlatformId(dto.getKeywordId(), dto.getQuestionId(), 1, + dto.getPlatformId()); + } + //查询所有平台名称 + List<String> platfromNames = platformService.list().stream() + .map(Platform::getPlatform_name) + .filter(Objects::nonNull) + .collect(Collectors.toList()); + // 3. 导出Excel + ByteArrayOutputStream out = new ByteArrayOutputStream(); + + // 3. 使用自定义的SheetWriteHandler来添加数据验证 + ExcelWriter excelWriter = EasyExcel.write(out, ResultListVO.class) + .build(); + + + WriteSheet writeSheet = EasyExcel.writerSheet("平台分布占比").build(); + excelWriter.write(result, writeSheet); + excelWriter.finish(); + + // 4. 构建响应 + return ResponseEntity.ok() + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=references_export.xlsx") + .contentType(MediaType.APPLICATION_OCTET_STREAM) + .body(out.toByteArray()); + } + + @Override + public ResponseEntity<byte[]> exportResultByPlatform2(ExportGetResultByPlatformIdDTO dto) { + Keyword keyword = this.getById(dto.getKeywordId()); + List<ResultListVO> result=new ArrayList<>(); + if (dto.getIsNow()==0){ + result = keywordMapper.getResultByPlatformId(dto.getKeywordId(), dto.getQuestionId(), keyword.getNum(), + dto.getPlatformId()); + }else { + result = keywordMapper.getResultByPlatformId(dto.getKeywordId(), dto.getQuestionId(), 1, + dto.getPlatformId()); + } + List<ResultList2VO> result2=new ArrayList<>(); + for (ResultListVO vo : result) { + ResultList2VO vo2 = new ResultList2VO(); + BeanUtils.copyProperties(vo, vo2); // 复制同名同类型的字段 + result2.add(vo2); + } + //查询所有平台名称 + List<String> platfromNames = platformService.list().stream() + .map(Platform::getPlatform_name) + .filter(Objects::nonNull) + .collect(Collectors.toList()); + // 3. 导出Excel + ByteArrayOutputStream out = new ByteArrayOutputStream(); + + // 3. 使用自定义的SheetWriteHandler来添加数据验证 + ExcelWriter excelWriter = EasyExcel.write(out, ResultList2VO.class) + .registerWriteHandler(new SheetWriteHandler() { + @Override + public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, + WriteSheetHolder writeSheetHolder) { + Sheet sheet = writeSheetHolder.getSheet(); + + // 添加数据验证(下拉框) + DataValidationHelper helper = sheet.getDataValidationHelper(); + CellRangeAddressList rangeList = new CellRangeAddressList( + 1, 65535, 0, 0); // D列(第4列) + + DataValidationConstraint constraint = helper.createExplicitListConstraint( + platfromNames.toArray(new String[0])); + DataValidation validation = helper.createValidation(constraint, rangeList); + sheet.addValidationData(validation); + } + }) + .build(); + + + WriteSheet writeSheet = EasyExcel.writerSheet("平台分布占比").build(); + excelWriter.write(result2, writeSheet); + excelWriter.finish(); + + // 4. 构建响应 + return ResponseEntity.ok() + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=references_export.xlsx") + .contentType(MediaType.APPLICATION_OCTET_STREAM) + .body(out.toByteArray()); + } + + @Override + public ResponseResult<List<FeedExportExcel>> importFeedByKeyWord(Integer keywordId, MultipartFile file) { + Keyword keyword = this.getById(keywordId); + //查找出最近一次的结果 + List<FeedExportExcel> references = referenceMapper.importTemplateList(keywordId,keyword.getNum()); + //平台名称 + Map<String, FeedExportExcel> nameMap = references.stream() + .collect(Collectors.toMap( + FeedExportExcel::getPlatform_name, // Key映射 + excel -> excel, // Value映射 + (oldValue, newValue) -> oldValue // 键冲突处理(保留旧值) + )); + //URL + Map<String, FeedExportExcel> urlMap = references.stream() + .collect(Collectors.toMap( + FeedExportExcel::getUrl, // Key映射 + excel -> excel, // Value映射 + (oldValue, newValue) -> oldValue // 键冲突处理(保留旧值) + )); + //title + Map<String, FeedExportExcel> titleMap = references.stream() + .collect(Collectors.toMap( + FeedExportExcel::getTitle, // Key映射 + excel -> excel, // Value映射 + (oldValue, newValue) -> oldValue // 键冲突处理(保留旧值) + )); + + //time + // 定义日期格式化器 + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + Map<String, FeedExportExcel> timeMap = references.stream() + .filter(excel -> excel.getCreate_time() != null) + .collect(Collectors.toMap( + excel -> excel.getCreate_time().format(formatter), + excel -> excel, + (oldValue, newValue) -> oldValue + )); + + try { + // 检查文件是否为空 + if (file.isEmpty()) { + return ResponseResult.error("上传文件不能为空"); + } + + // 读取Excel数据 + List<ReferenceExcel> excelList = EasyExcel.read(file.getInputStream()) + .head(ReferenceExcel.class) + .sheet() + .doReadSync(); + + // 数据转换与验证 + List<String> errorMessages = new ArrayList<>(); + List<FeedExportExcel> result = new ArrayList<>(); + for (ReferenceExcel excel : excelList) { + // 检查必要字段 + if (!StringUtils.hasText(excel.getPlatform_name())) { + errorMessages.add("平台名称不能为空"); + continue; + } + if (!StringUtils.hasText(excel.getTitle())) { + errorMessages.add("标题不能为空"); + continue; + } + if (!StringUtils.hasText(String.valueOf(excel.getCreate_time()))) { + errorMessages.add("发布时间不能为空"); + continue; + } + + if (!StringUtils.hasText(excel.getUrl())) { + errorMessages.add("发布网址不能为空"); + continue; + } + FeedExportExcel feedExportExcel = getFeedExportExcel(excel, nameMap, urlMap, titleMap, timeMap); + result.add(feedExportExcel); + + } + // 处理错误 + if (!errorMessages.isEmpty()) { + return ResponseResult.error("数据验证失败: " + String.join("; ", errorMessages)); + } + + + // 返回信息 + return ResponseResult.success(result); + }catch (DateTimeParseException e) { + return ResponseResult.error("文件解析失败:发布时间格式输入错误"); + } + catch (Exception e) { + // 记录详细异常信息 + + return ResponseResult.error("文件解析失败:" + e.getMessage()); + } + } + + @Override + public ResponseEntity<byte[]> exportFeedResult(ExportFeedDTO dto) { + // 3. 导出Excel + ByteArrayOutputStream out = new ByteArrayOutputStream(); + List<String> statusList = new ArrayList<>(); + statusList.add("未收录"); + statusList.add("已收录"); + statusList.add("未选择"); + // 3. 使用自定义的SheetWriteHandler来添加数据验证 + ExcelWriter excelWriter = EasyExcel.write(out, FeedExportExcel.class) + .registerWriteHandler(new SheetWriteHandler() { + @Override + public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, + WriteSheetHolder writeSheetHolder) { + Sheet sheet = writeSheetHolder.getSheet(); + + // 添加数据验证(下拉框) + DataValidationHelper helper = sheet.getDataValidationHelper(); + CellRangeAddressList rangeList = new CellRangeAddressList( + 1, 65535, 2, 2); // D列(第4列) + + DataValidationConstraint constraint = helper.createExplicitListConstraint( + statusList.toArray(new String[0])); + DataValidation validation = helper.createValidation(constraint, rangeList); + sheet.addValidationData(validation); + } + }) + .build(); + + // 4. 写入空数据 + WriteSheet writeSheet = EasyExcel.writerSheet("账号模板").build(); + excelWriter.write(dto.getExcels(), writeSheet); + excelWriter.finish(); + + // 4. 构建响应 + return ResponseEntity.ok() + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=references_export.xlsx") + .contentType(MediaType.APPLICATION_OCTET_STREAM) + .body(out.toByteArray()); + } + + @Override + + public ResponseEntity<byte[]> downloadKeywrodFeed() { + ByteArrayOutputStream out = new ByteArrayOutputStream(); + // 创建示例数据(第一行) + ReferenceExcel exampleData = new ReferenceExcel(); + exampleData.setPlatform_name("示例平台"); + exampleData.setTitle("示例标题"); + exampleData.setCreate_time(LocalDate.now()); // 设置当前日期作为示例 + exampleData.setUrl("https://example.com"); + + List<ReferenceExcel> excelList=new ArrayList<>(); + excelList.add(exampleData); + + ExcelWriter excelWriter = EasyExcel.write(out, ReferenceExcel.class).build(); + WriteSheet writeSheet = EasyExcel.writerSheet("平台分布占比").build(); + excelWriter.write(excelList, writeSheet); + excelWriter.finish(); + + + return ResponseEntity.ok() + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=platform_template.xlsx") + .contentType(MediaType.APPLICATION_OCTET_STREAM) + .body(out.toByteArray()); + } + + + private FeedExportExcel getFeedExportExcel(ReferenceExcel excel, Map<String, FeedExportExcel> nameMap, Map<String, FeedExportExcel> urlMap, Map<String, FeedExportExcel> titleMap, Map<String, FeedExportExcel> timeMap) { + FeedExportExcel vo=new FeedExportExcel(); + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + excel.setTimeString(excel.getCreate_time().format(formatter)); + vo.setPlatform_name(excel.getPlatform_name()); + vo.setTitle(excel.getTitle()); + vo.setCreate_time(excel.getCreate_time()); + vo.setUrl(excel.getUrl()); + vo.setInclusionStatus("未选择"); + vo.setPlatformIs(0); + vo.setTitleIs(0); + vo.setUrlIs(0); + vo.setCreateIs(0); + //平台名称 + if (nameMap.containsKey(excel.getPlatform_name())) { + vo.setPlatformIs(1); + if (urlMap.containsKey(excel.getUrl())) { + vo.setUrlIs(1); + } + if (titleMap.containsKey(excel.getTitle())) { + vo.setTitleIs(1); + } + if (timeMap.containsKey(excel.getTimeString())) { + vo.setCreateIs(1); + } + return vo; + } + //网站链接 + if (urlMap.containsKey(excel.getUrl())) { + vo.setUrlIs(1); + if (titleMap.containsKey(excel.getTitle())) { + vo.setTitleIs(1); + } + if (timeMap.containsKey(excel.getTimeString())) { + vo.setCreateIs(1); + } + return vo; + } + //标题 + if (titleMap.containsKey(excel.getTitle())) { + vo.setTitleIs(1); + if (timeMap.containsKey(excel.getTimeString())) { + vo.setCreateIs(1); + } + } + //时间 + if (timeMap.containsKey(excel.getTimeString())) { + vo.setCreateIs(1); + return vo; + } + return vo; + } + + + } -- Gitblit v1.7.1