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 * @description 针对表【keyword】的数据库操作Service实现 * @createDate 2025-07-04 20:17:33 */ @Service public class KeywordServiceImpl extends ServiceImpl implements KeywordService { @Autowired private KeywordMapper keywordMapper; @Autowired private ReferenceService referenceService; @Autowired private ReferenceMapper referenceMapper; @Autowired private PlatformService platformService; @Autowired private KeywordTaskService keywordTaskService; @Override public ResponseResult statics(Integer keywordId, Integer questionId) { KeywordStaticsListVO vo = new KeywordStaticsListVO(); // 1.关键词是否存在 Keyword keyword = this.getById(keywordId); if (keyword == null) { return ResponseResult.error("关键词不存在"); } if (!FinalStatus.COMPLETED.getValue().equals(keyword.getStatus())) { return ResponseResult.success(); } List statics = this.getBaseMapper().statics(keywordId, questionId, keyword.getNum()); List top30 = statics.subList(0, Math.min(statics.size(), 30)); vo.setNowRecord(top30); if (keyword.getNum() > 1) { statics = this.getBaseMapper().statics(keywordId, questionId, 1); top30 = statics.subList(0, Math.min(statics.size(), 30)); vo.setBeforeRecord(top30); } return ResponseResult.success(vo); } @Override public ResponseResult> getResultByTypeId(Integer keywordId, Integer questionId, Integer typeId) { // 1.关键词是否存在 Keyword keyword = this.getById(keywordId); if (keyword == null) { return ResponseResult.error("关键词不存在"); } if (!FinalStatus.COMPLETED.getValue().equals(keyword.getStatus())) { return ResponseResult.error("关键词采集未完成"); } List result = this.getBaseMapper().getResultByTypeId(keywordId, questionId, keyword.getNum(), typeId); return ResponseResult.success(result); } @Override public ResponseResult> getResultByPlatformId(Integer keywordId, Integer questionId, Integer platformId) { // 1.关键词是否存在 Keyword keyword = this.getById(keywordId); if (keyword == null) { return ResponseResult.error("关键词不存在"); } if (!FinalStatus.COMPLETED.getValue().equals(keyword.getStatus())) { return ResponseResult.error("关键词采集未完成"); } List result = this.getBaseMapper().getResultByPlatformId(keywordId, questionId, keyword.getNum(), platformId); return ResponseResult.success(result); } @Override public List getKeywordsByOrderId(String orderId) { LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(Keyword::getOrder_id, orderId); return keywordMapper.selectList(queryWrapper); } @Override public ResponseResult getTimeVOResponseResult(Integer keywordId, Integer questionId) { // GetTimeVO vo = new GetTimeVO(); Keyword keyword = this.getById(keywordId); LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(KeywordTask::getKeyword_id, keywordId); queryWrapper.eq(KeywordTask::getNum,keyword.getNum()); List list = keywordTaskService.list(queryWrapper); if (null != list && !list.isEmpty()) { vo.setNow(list.get(0).getCreate_time()); } //首次 LambdaQueryWrapper queryWrapper2 = new LambdaQueryWrapper<>(); queryWrapper2.eq(KeywordTask::getKeyword_id, keywordId); queryWrapper2.eq(KeywordTask::getNum,1); List list2 = keywordTaskService.list(queryWrapper2); if (null != list2 && !list2.isEmpty()) { vo.setFirst(list2.get(0).getCreate_time()); } return ResponseResult.success(vo); } @Override public ResponseEntity exportEChart(ExportStaticsDTO exportStaticsDTO) { Keyword keyword = keywordMapper.selectById(exportStaticsDTO.getKeywordId()); List voList=new ArrayList<>(); if (exportStaticsDTO.getIsFirst()==0){ voList = keywordMapper.statics(exportStaticsDTO.getKeywordId(), exportStaticsDTO.getQuestionId(), keyword.getNum()); }else { 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> getByTypeId(Integer keywordId, Integer questionId, Integer typeId, Integer isNow) { Keyword keyword = this.getById(keywordId); if (isNow == 0) { List 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 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 exportResult(ExportGetResultDTO dto) { Keyword keyword = this.getById(dto.getKeywordId()); List 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 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> getResultByPlatform(Integer keywordId, Integer questionId, Integer platformId, Integer isNow) { Keyword keyword = this.getById(keywordId); if (isNow == 0) { List 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 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 exportResultByPlatform(ExportGetResultByPlatformIdDTO dto) { Keyword keyword = this.getById(dto.getKeywordId()); List 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 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 exportResultByPlatform2(ExportGetResultByPlatformIdDTO dto) { Keyword keyword = this.getById(dto.getKeywordId()); List 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 result2=new ArrayList<>(); for (ResultListVO vo : result) { ResultList2VO vo2 = new ResultList2VO(); BeanUtils.copyProperties(vo, vo2); // 复制同名同类型的字段 result2.add(vo2); } //查询所有平台名称 List 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> importFeedByKeyWord(Integer keywordId, MultipartFile file) { Keyword keyword = this.getById(keywordId); //查找出最近一次的结果 List references = referenceMapper.importTemplateList(keywordId,keyword.getNum()); //平台名称 Map nameMap = references.stream() .collect(Collectors.toMap( FeedExportExcel::getPlatform_name, // Key映射 excel -> excel, // Value映射 (oldValue, newValue) -> oldValue // 键冲突处理（保留旧值） )); //URL Map urlMap = references.stream() .collect(Collectors.toMap( FeedExportExcel::getUrl, // Key映射 excel -> excel, // Value映射 (oldValue, newValue) -> oldValue // 键冲突处理（保留旧值） )); //title Map titleMap = references.stream() .collect(Collectors.toMap( FeedExportExcel::getTitle, // Key映射 excel -> excel, // Value映射 (oldValue, newValue) -> oldValue // 键冲突处理（保留旧值） )); //time // 定义日期格式化器 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); Map 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 excelList = EasyExcel.read(file.getInputStream()) .head(ReferenceExcel.class) .sheet() .doReadSync(); // 数据转换与验证 List errorMessages = new ArrayList<>(); List 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 exportFeedResult(ExportFeedDTO dto) { // 3. 导出Excel ByteArrayOutputStream out = new ByteArrayOutputStream(); List 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 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 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 nameMap, Map urlMap, Map titleMap, Map 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; } }