修改查看时间和删除关键词

guyue

2025-08-12 ff1fec7eae681e89e607fd441d2597ab67b5ed2a

 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())) {
        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, 1);
            vo.setBeforeRecord(statics);
            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;
    }



}