cdf-gm/src/main/java/com/cdf/service/impl/ExcelService.java

package com.cdf.service.impl;


import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.amazonaws.services.ecs.model.Tmpfs;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.cdf.common.CacheUtil;
import com.cdf.common.ResultCode;
import com.cdf.entity.*;
import com.cdf.httpClient.client.CdfClient;
import com.cdf.httpClient.client.CdfHKClient;
import com.cdf.httpClient.response.cdf.CdfProduct;
import com.cdf.httpClient.response.cdf.CdfProductListByIdsRequest;
import com.cdf.httpClient.response.cdf.CdfProductListByIdsVo;
import com.cdf.request.UploadHotsParam;
import com.cdf.response.*;
import com.cdf.exception.BusinessException;
import com.cdf.service.*;
import com.cdf.util.ExcelUtil;
import com.cdf.util.UploadToCdfOssUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ExcelService {

    @Resource
    private UploadToCdfOssUtil uploadToCdfOssUtil;

    @Value("${upload.file-path}")
    private String filePath;
    @Value("${upload.query-path}")
    private String queryPath;
    @Autowired
    private FdkkSceneEditService fdkkSceneEditService;
    @Autowired
    CdfHKClient cdfHKClient;
    @Autowired
    IProductSourceService productSourceService;
    @Autowired
    private IHotOutlineService hotOutlineService;
    @Autowired
    private IBrandService brandService;
    @Autowired
    private IHotRelationService hotRelationService;
    @Autowired
    private IHotRecommendService hotRecommendService;
    @Autowired
    IFdkkUserService fdkkUserService;
    @Autowired
    INumRegionService numRegionService;


    // 0 外框设置 ，1 瀑布流， 2，精选推荐设置 ，3 品牌推荐设置
    public void  downTemplate(Integer type,String sceneNum,HttpServletRequest req,HttpServletResponse response)  {
        if(type == null ){
            throw new BusinessException(ResultCode.PARAM_MISS);
        }
        try {
            List<HotUploadTemplate> list = new ArrayList<>();
            if(type !=0 && StringUtils.isNotBlank(sceneNum)){
                list = getHotsList(sceneNum);
            }

            String fileName = "";
            switch (type){
                case 0 :
                    fileName ="导入外框模版";
                    List<HotOutline> list1 = hotOutlineService.list();
                    List<OutlineUploadTemplate> OutlineUploadTemplateList = new ArrayList<>();
                    for (HotOutline hotOutline : list1) {
                        OutlineUploadTemplate template = new OutlineUploadTemplate(hotOutline.getId().toString());
                        OutlineUploadTemplateList.add(template);
                    }

                    this.commonExport(req,response,fileName,OutlineUploadTemplateList, OutlineUploadTemplate.class);
                    break;
                case 1 :
                    HashMap<String,HotUploadTemplate> sidMap = new HashMap<>();
                    for (HotUploadTemplate hotUploadTemplate : list) {
                        sidMap.put(hotUploadTemplate.getSid(),hotUploadTemplate);
                    }
                    List<HotRelation> hotRelations = hotRelationService.getByNum(sceneNum);
                    List<String> sids = hotRelations.stream().filter(e -> e.getHotType() == 3).map(HotRelation::getHotId).collect(Collectors.toList());
                    List<ProductUploadTemplate> resultList = new ArrayList<>();
                    for (String sid : sids) {
                        HotUploadTemplate hotUploadTemplate = sidMap.get(sid);
                        if(hotUploadTemplate == null){
                            continue;
                        }
                        ProductUploadTemplate template = new ProductUploadTemplate();
                        template.setSceneNum(sceneNum);
                        template.setSid(sid);
                        template.setHotTitle(hotUploadTemplate.getHotTitle());
                        resultList.add(template);
                    }
                    fileName ="导入商品瀑布流模版";
                    this.commonExport(req,response,fileName,resultList, ProductUploadTemplate.class);
                    break;
                case 2 :
                    fileName ="导入精选推荐模版";
                    this.commonExport(req,response,fileName,list, HotUploadTemplate.class);
                    break;
                case 3 :
                    fileName ="导入品牌推荐模版";
                    this.commonExport(req,response,fileName,list, HotUploadTemplate.class);
                    break;
                default: throw new BusinessException(ResultCode.PARAM_MISS);
            }

        }catch (Exception e){
            log.info("导出热点列表出错",e);
        }

    }

    public void commonExport(HttpServletRequest request, HttpServletResponse response,String name,List<?> result,Class<?> clz) throws Exception {
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clz).build();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = name  + ".xlsx";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        WriteSheet writeSheet = EasyExcel.writerSheet(name).build();
        excelWriter.write(result, writeSheet);
        excelWriter.finish();
    }

    public List<String> checkFile(UploadHotsParam param) {
        if(StringUtils.isBlank(param.getFilePath()) || param.getType() == null){
            throw new BusinessException(ResultCode.PARAM_MISS);
        }
        String awsKey = param.getFilePath().replace(queryPath, "");
        if(!uploadToCdfOssUtil.existKey(awsKey)){
            throw new BusinessException(ResultCode.UPLOAD_FILE_NO_EXIST);
        }
        String localPath = String.format(CacheUtil.localFilePath, CacheUtil.activeYaml);
        String localFile = localPath + awsKey;
        File file = new File(localFile);
        if (! file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        log.info("checkFile:{}",localFile);
        uploadToCdfOssUtil.downFromS3(awsKey,localFile);
        if(!file.exists()){
            throw new BusinessException(ResultCode.UPLOAD_FILE_NO_EXIST);
        }

        List<String> errorList = new ArrayList<>();
        String resultError = null;
        switch (param.getType()){
            case 0:
                checkUploadOutline(file,errorList);
                break;
            case 1:
                checkUploadProduct(file,errorList);
                break;
            case 2 :case 3 :
                checkHot(file,errorList);
                break;
            default:  throw new BusinessException(ResultCode.PARAM_MISS);
        }

        return errorList;
    }



    private void checkUploadOutline(File file, List<String> errorList) {
        List<HashMap<Integer, String>> excelRowList = ExcelUtil.getExcelRowList(file);
        Integer colum = 1;
        List<String> outlineIds = new ArrayList<>();
        List<String> outlineIdsDb = new ArrayList<>();
        List<String> brandIds = new ArrayList<>();
        List<String> brandIdsDb = new ArrayList<>();
        for (HashMap<Integer, String> map : excelRowList) {
            String outlineId = map.get(0);
            String brandId = map.get(1);
            if(StringUtils.isNotBlank(outlineId)){
                outlineIds.add(outlineId);
            }
            if(StringUtils.isNotBlank(brandId)){
                brandIds.add(brandId);
            }
        }
        if(outlineIds.size() >0){
            List<HotOutline> hotOutlines = hotOutlineService.listByIds(outlineIds);
            for (HotOutline hotOutline : hotOutlines) {
                outlineIdsDb.add(hotOutline.getId().toString());
            }
        }
        if(brandIds.size() >0){
            List<Brand> brands = brandService.getByCdfBrandIds(brandIds);
            for (Brand brand : brands) {
                brandIdsDb.add(brand.getCdfBrandId().toString());
            }
        }

        for (HashMap<Integer, String> map : excelRowList) {
            colum ++;
            String outlineId = map.get(0);
            String brandId = map.get(1);
            if(StringUtils.isBlank(outlineId) || StringUtils.isBlank(brandId)){
                errorList.add(colum.toString());
                continue;
            }
            if(!outlineIdsDb.contains(outlineId)){
                errorList.add(colum.toString());
                continue;
            }
            if(!brandIdsDb.contains(brandId)){
                errorList.add(colum.toString());
            }

        }
    }

    private void checkUploadProduct(File file, List<String> errorList){
        List<HashMap<Integer, String>> excelRowList = ExcelUtil.getExcelRowList(file);
        Integer colum = 1;
        HashMap<String,ProductSource> productSourceHashMap = new HashMap<>();
        HashMap<String, List<String>> productSourceProductMap = new HashMap<>();
        HashMap<String, List<String>> resultPMap = new HashMap<>();
        HashMap<String, List<String>> numMap = new HashMap<>();

        for (HashMap<Integer, String> map : excelRowList) {
            String sourceId = map.get(3);
            if(StringUtils.isNotBlank(sourceId)){
                ProductSource byMchId = productSourceService.getByMchId(sourceId);
                productSourceHashMap.put(sourceId,byMchId);
            }
            String pid = map.get(4);
            if(StringUtils.isNotBlank(pid)){
                List<String> pidList = productSourceProductMap.computeIfAbsent(sourceId, k -> new ArrayList<>());
                pidList.add(pid);
            }
        }
        for (String mchId : productSourceProductMap.keySet()) {
            ProductSource productSource = productSourceHashMap.get(mchId);
            JSONArray ids = new JSONArray();
            ids.addAll(productSourceProductMap.get(mchId));
            CdfProductListByIdsRequest idsRequest = new CdfProductListByIdsRequest(ids);
            CdfProductListByIdsVo vos = cdfHKClient.getProductListByIds(productSource.getCdfHost(), productSource.getCdfMchId(), idsRequest);
            if(vos.getProductCardList()!=null && vos.getProductCardList().size() >0){
                for (CdfProduct cdfProduct : vos.getProductCardList()) {
                    List<String> pidList = resultPMap.computeIfAbsent(mchId, k -> new ArrayList<>());
                    pidList.add(cdfProduct.getId());
                }
            }
        }
        for (HashMap<Integer, String> map : excelRowList) {
            colum ++;
            String sceneNum = map.get(0);
            String sid = map.get(1);
            String title = map.get(2);
            String mchId = map.get(3);
            String pid = map.get(4);
            if(StringUtils.isBlank(sceneNum) || StringUtils.isBlank(sid) || StringUtils.isBlank(pid) || StringUtils.isBlank(mchId)){
                errorList.add(colum.toString());
                continue;
            }
            if(numMap.get(sceneNum) == null){
                List<HotRelation> hotRelations = hotRelationService.getByNum(sceneNum);
                List<String> sidList = hotRelations.stream().map(HotRelation::getHotId).collect(Collectors.toList());
                numMap.put(sceneNum,sidList);
            }
            if(!numMap.get(sceneNum).contains(sid)){
                errorList.add(colum.toString());
                continue;
            }
            if(!productSourceHashMap.containsKey(mchId)){
                errorList.add(colum.toString());
                continue;
            }
            List<String> cdfProductList = resultPMap.get(mchId);
            if(cdfProductList.isEmpty()){
                errorList.add(colum.toString());
                continue;
            }
            if(!cdfProductList.contains(pid)){
                errorList.add(colum.toString());
            }

        }
    }
    private void checkHot(File file, List<String> errorList) {
        List<HashMap<Integer, String>> excelRowList = ExcelUtil.getExcelRowList(file);
        Integer colum = 1;
        List<HotUploadTemplate> list = null;

        for (HashMap<Integer, String> map : excelRowList) {
            colum ++;
            String sceneNum = map.get(0);
            String sid = map.get(1);
            String title = map.get(2);
            if(StringUtils.isBlank(sceneNum) || StringUtils.isBlank(sid) ){
                errorList.add(colum.toString());
                continue;
            }
            if(list == null){
                list  = getHotsList(sceneNum);
            }
            if(list .isEmpty()){
                errorList.add(colum.toString());
                continue;
            }
            List<String> collect = list.stream().map(HotUploadTemplate::getSid).collect(Collectors.toList());
            if(!collect.contains(sid)){
                errorList.add(colum.toString());
            }

        }
    }

    private  List<HotUploadTemplate> getHotsList(String sceneNum){
        JSONArray hotJson = fdkkSceneEditService.getCdfHotJson(sceneNum);
        List<HotUploadTemplate> list = new ArrayList<>();
        for (Object obj : hotJson) {
            JSONObject tag = (JSONObject) obj;
            String sid = tag.getString("sid");
            String title = tag.getString("title");
            HotUploadTemplate hotExcelVo = new HotUploadTemplate(sceneNum,sid,title);
            list.add(hotExcelVo);
        }
        return list;
    }

    public void uploadExcel(UploadHotsParam param) {
        if(StringUtils.isBlank(param.getFilePath()) || param.getType()== null){
            throw new BusinessException(ResultCode.PARAM_MISS);
        }
        String awsKey = param.getFilePath().replace(queryPath, "");
        if(!uploadToCdfOssUtil.existKey(awsKey)){
            throw new BusinessException(ResultCode.UPLOAD_FILE_NO_EXIST);
        }
        String localPath = String.format(CacheUtil.localFilePath, CacheUtil.activeYaml);
        String localFile = localPath + awsKey;
        File file = new File(localFile);
        if(!file.exists()){
            throw new BusinessException(ResultCode.UPLOAD_EXCEL_CHECK_ERROR);
        }
        switch (param.getType()){
            case 0:
                uploadOutline(file);
                break;
            case 1:
                uploadProduct(file);
                break;
            case 2 :case 3 :
                uploadHot(file,param.getType());
                break;
            default:  throw new BusinessException(ResultCode.PARAM_MISS);
        }

    }

    private void uploadOutline(File file) {
        List<String> errorList = new ArrayList<>();
        this.checkUploadOutline(file,errorList);
        Integer colum = 1;
        List<HashMap<Integer, String>> excelRowList = ExcelUtil.getExcelRowList(file);
        for (HashMap<Integer, String> map : excelRowList) {
            colum ++;
            if(errorList.contains(colum.toString())){
                continue;
            }
            String outlineId = map.get(0);
            String brandId = map.get(1);
            if(StringUtils.isNotBlank(outlineId) && StringUtils.isNotBlank(brandId)){
                brandService.updateBrandOutline(brandId,outlineId);
            }
        }

    }

    private void uploadProduct(File file) {
        List<String> errorList = new ArrayList<>();
        this.checkUploadProduct(file,errorList);
        Integer colum = 1;
        List<HashMap<Integer, String>> excelRowList = ExcelUtil.getExcelRowList(file);
        HashMap<String,HashMap<String,HashSet<String>>> numMap = new HashMap<>();
        for (HashMap<Integer, String> map : excelRowList) {
            colum ++;
            if(errorList.contains(colum.toString())){
                continue;
            }
            String sceneNum = map.get(0);
            String sid = map.get(1);
            String title = map.get(2);
            String mchId = map.get(3);
            String pid = map.get(4);
            if(StringUtils.isBlank(mchId) || StringUtils.isBlank(sceneNum)  || StringUtils.isBlank(pid) || StringUtils.isBlank(sid)){
                continue;
            }
            numMap.computeIfAbsent(sceneNum, k -> new HashMap<>());
            numMap.get(sceneNum).computeIfAbsent(sid,k -> new HashSet<>());
            numMap.get(sceneNum).get(sid).add(pid);
        }
        List<HotRelation> updateList = new ArrayList<>();
        for (String num : numMap.keySet()) {
            HashMap<String, HashSet<String>> sidMap = numMap.get(num);
            for (String sid : sidMap.keySet()) {
                HotRelation hotRelation = hotRelationService.getById(sid);
                if(hotRelation == null){
                    continue;
                }
                HashSet<String> pidSet = sidMap.get(sid);
                hotRelation.setRelationIds(JSONArray.toJSONString(pidSet));
                updateList.add(hotRelation);
            }
        }
        if(!updateList.isEmpty()){
            hotRelationService.updateBatchById(updateList);
        }

        for (String num : numMap.keySet()) {
            NumRegion numRegion = fdkkUserService.getRegionByNum(num);
            fdkkSceneEditService.writeHotJson(num,numRegion.getRegion());
        }
    }

    private void uploadHot(File file,Integer type) {
        List<String> errorList = new ArrayList<>();
        this.checkHot(file,errorList);
        Integer colum = 1;
        List<HashMap<Integer, String>> excelRowList = ExcelUtil.getExcelRowList(file);
        HashMap<String,HashSet<String>> numMap = new HashMap<>();
        for (HashMap<Integer, String> map : excelRowList) {
            colum ++;
            if(errorList.contains(colum.toString())){
                continue;
            }
            String sceneNum = map.get(0);
            String sid = map.get(1);
            String title = map.get(2);
            if(StringUtils.isBlank(sid) || StringUtils.isBlank(sceneNum)){
                continue;
            }

            numMap.computeIfAbsent(sceneNum, k -> new HashSet<>());
            numMap.get(sceneNum).add(sid);
            HotRecommend recommend = hotRecommendService.getByNumAndSid(sceneNum,sid);
            if(recommend == null){
                recommend = new HotRecommend();
                recommend.setSceneNum(sceneNum);
                recommend.setSid(sid);
            }
            if(type == 2){
                recommend.setRecommendSelection(1);
            }
            if(type == 3){
                recommend.setRecommendBrand(1);
            }
            hotRecommendService.saveOrUpdate(recommend);
        }
        List<Integer> delIds = new ArrayList<>();
        for (String num : numMap.keySet()) {
            List<HotRecommend> list = hotRecommendService.getByNum(num);
            HashSet<String> strings = numMap.get(num);
            for (HotRecommend recommend : list) {
               if(!strings.contains(recommend.getSid())){
                   delIds.add(recommend.getId());
               }
            }
        }
        if(!delIds.isEmpty()){
            hotRecommendService.removeByIds(delIds);
        }
        for (String num : numMap.keySet()) {
            NumRegion numRegion = fdkkUserService.getRegionByNum(num);
            fdkkSceneEditService.writeHotJson(num,numRegion.getRegion());
        }
    }

    public void downBrandExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {
        LambdaQueryWrapper<Brand> wrapper = new LambdaQueryWrapper<>();
        wrapper.orderByDesc(Brand::getCdfBrandId);
        List<Brand> list = brandService.list(wrapper);
        List<BrandExcelVo> voList = new ArrayList<>();
        for (Brand brand : list) {
            BrandExcelVo vo = new BrandExcelVo();
            BeanUtil.copyProperties(brand,vo);
            voList.add(vo);
        }

        this.commonExport(request,response,"品牌信息",voList, BrandExcelVo.class);
    }
}