package com.moral.api.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.moral.api.config.Interceptor.UserHelper;
import com.moral.api.entity.FileTable;
import com.moral.api.entity.YcGenerateReport;
import com.moral.api.exception.BusinessException;
import com.moral.api.pojo.enums.FileTableEnum;
import com.moral.api.pojo.enums.FileType;
import com.moral.api.pojo.ext.allocation.AllocationPageExt;
import com.moral.api.pojo.ext.ycgeneratereport.YcGenerateReportPageExt;
import com.moral.api.pojo.query.allocation.AllocationPageCond;
import com.moral.api.pojo.query.ycgeneratereport.YcGenerateReportPageCond;
import com.moral.api.pojo.query.ycgeneratereport.YcGenerateReportUploadCond;
import com.moral.api.pojo.vo.allocation.AllocationPageVo;
import com.moral.api.pojo.vo.user.QxUser;
import com.moral.api.pojo.vo.yc.AqiResultVo;
import com.moral.api.pojo.vo.yc.SixConcentrationsVo;
import com.moral.api.pojo.vo.ycgeneratereport.YcGenerateReportPageVo;
import com.moral.api.service.FileTableService;
import com.moral.api.service.ReptileResultService;
import com.moral.api.service.YcGenerateReportService;
import com.moral.api.service.impl.ReptileResultServiceImpl;
import com.moral.constant.PageResult;
import com.moral.constant.ResultMessage;
import com.moral.util.DateUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.Year;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @ClassName TestController
 * @Description TODO
 * @Author @cjl
 * @Date 2024-03-04 9:58
 * @Version 1.0
 */
@Slf4j
@Api(tags = {"盐城接口数据"})
@RestController
@RequestMapping("/cityReptiles")
public class YcReptilesController {

    @Autowired
    private ReptileResultService resultService;
    @Autowired
    private YcGenerateReportService ycGenerateReportService;
    @Autowired
    private FileTableService fileTableService;


    @Value("${file.path}")
    private String basePath;


    @GetMapping("/report")
    @ApiOperation("生成报告")
    public ResultMessage report(@RequestParam @ApiParam(value = "字典",name = "code") String code,
                                @RequestParam(required = false) @ApiParam(value = "时间",name = "time") String time){
        String filePath = getFilePath(FileTableEnum.YC_GENERATEEXCEL.value.toString());
        String targetFolder = basePath.replaceAll(com.moral.api.utils.StringUtils.BACKSLASH.concat(com.moral.api.utils.StringUtils.BACKSLASH), com.moral.api.utils.StringUtils.SLASH).concat(com.moral.api.utils.StringUtils.SLASH).concat(filePath);
        generateReport(time,code,targetFolder,filePath);
        return ResultMessage.ok();
    }



    @PostMapping("/page")
    @ApiOperation("分页")
    public ResultMessage<PageResult<YcGenerateReportPageVo>> page(@Valid @RequestBody YcGenerateReportPageCond ycGenerateReportPageCond) {
        Page<YcGenerateReportPageExt> page = ycGenerateReportService.extPage(ycGenerateReportPageCond);
        PageResult<YcGenerateReportPageVo> result = new PageResult<>(page);
        result.setList(YcGenerateReportPageVo.convert(page.getRecords()));
        return ResultMessage.ok(result);
    }

    @PostMapping("/fileUpload")
    @ApiOperation("重新上传")
    public ResultMessage fileUpload(@Valid @RequestBody YcGenerateReportUploadCond uploadCond) {
        ycGenerateReportService.fileUpload(uploadCond);
        return ResultMessage.ok();
    }


    public void generateReport(String time,String code,String filePath,String path) {
        YcReptilesController testController = new YcReptilesController();
        String yc = "大丰区,阜宁县,盐都区,响水县,建湖县,东台市,亭湖区,射阳县,盐城市区,滨海县,盐南高新区,开发区";

        String gk = "大丰高级中学,开发区管委会,宝龙广场,盐城电厂,市监测站,月亮广场";

        String url = "http://116.147.41.178:8081/DataReport/AQIReport";
        String cookie = null;
        if(StringUtils.isEmpty(code)){
            cookie = "ASP.NET_SessionId=xo1nn2mvzmhaeb2csok130fd;CurrentAMenuID=2fa3c748-bf4e-4453-9544-e5fd860d74bf;";
        }else {
            cookie = code+"CurrentAMenuID=2fa3c748-bf4e-4453-9544-e5fd860d74bf;";
        }
        Date resultDate = null;
        if(StringUtils.isEmpty(time)){
            resultDate= new Date();
        }else {
            resultDate = DateUtils.convertDate(time);
        }

        ReptileResultService resultService = new ReptileResultServiceImpl();
        Date endDate = DateUtils.addDays(resultDate,-1);
        String endDateTime = DateUtils.dateToDateString(endDate,DateUtils.yyyy_MM_dd_EN);
        //Map<String,Object> map0 = resultService.getYcDaily(url,cookie,DateUtils.dateToDateString(DateUtils.getFirstDayOfLastYear(endDate),DateUtils.yyyy_MM_dd_EN),2);

        Map<String,Object> map = resultService.getYcDaily(url,cookie,endDateTime,2);
        Map<String,Object> map0 = resultService.getYcDaily(url,cookie,DateUtils.dateToDateString(DateUtils.getFirstDayOfLastYear(endDate),DateUtils.yyyy_MM_dd_EN),2);


        Date dateTime = DateUtils.getFirstDayOfMonth(resultDate);

        String sTime = DateUtils.dateToDateString(dateTime);
        String eTime = endDateTime+" 00:00:00";
        String url1 = "http://116.147.41.178:8081/AQA/YearCustom/Year_Custom1?sTime="+sTime+"&eTime="+eTime+"&type=1&viewtype=1&isDustDay=true&DecimalPlaces=1";
        Map<String,Object> map1 = resultService.getYcDaily(url1,cookie,null,3);
        String url2 = "http://116.147.41.178:8081/AQI/Statistics/YouLiangRateDataNewNewNew?sTime="+sTime+"&eTime="+eTime+"&DecimalPlaces=1&isDustDay=false&selec=0&selet=0&isDustDayXianShi=false";
        Map<String,Object> map3 = resultService.getYcDaily(url2,cookie,null,1);
        sTime = DateUtils.dateToDateString(DateUtils.getFirstDayOfLastYear(DateUtils.getFirstDayOfMonth(dateTime)));
        eTime = DateUtils.dateToDateString(DateUtils.getFirstDayOfLastYear(endDate));
        url1 = "http://116.147.41.178:8081/AQA/YearCustom/Year_Custom1?sTime="+sTime+"&eTime="+eTime+"&type=1&viewtype=1&isDustDay=true&DecimalPlaces=1";
        Map<String,Object> map2 = resultService.getYcDaily(url1,cookie,null,3);
        url2 = "http://116.147.41.178:8081/AQI/Statistics/YouLiangRateDataNewNewNew?sTime="+sTime+"&eTime="+eTime+"&DecimalPlaces=1&isDustDay=false&selec=0&selet=0&isDustDayXianShi=false";
        Map<String,Object> map4 = resultService.getYcDaily(url2,cookie,null,1);


        Date dateFirstTime = DateUtils.getFirstDayOfCurrYear();
        sTime = DateUtils.dateToDateString(dateFirstTime);
        eTime = endDateTime+" 00:00:00";
        url1 = "http://116.147.41.178:8081/AQA/YearCustom/Year_Custom1?sTime="+sTime+"&eTime="+eTime+"&type=1&viewtype=1&isDustDay=true&DecimalPlaces=1";
        Map<String,Object> map5 = resultService.getYcDaily(url1,cookie,null,3);
        url2 = "http://116.147.41.178:8081/AQI/Statistics/YouLiangRateDataNewNewNew?sTime="+sTime+"&eTime="+eTime+"&DecimalPlaces=1&isDustDay=false&selec=0&selet=0&isDustDayXianShi=false";
        Map<String,Object> map7 = resultService.getYcDaily(url2,cookie,null,1);
        // 污染天
        String url3 = "http://116.147.41.178:8081/AQI/Statistics/StandardRateData?&sTime="+sTime+"&eTime="+eTime;
        Map<String,Object> surplusMap = resultService.getYcDaily(url3,cookie,null,4);

        sTime = DateUtils.dateToDateString(DateUtils.getFirstDayOfLastYear(dateFirstTime));
        eTime = DateUtils.dateToDateString(DateUtils.getFirstDayOfLastYear(endDate));
        url1 = "http://116.147.41.178:8081/AQA/YearCustom/Year_Custom1?sTime="+sTime+"&eTime="+eTime+"&type=1&viewtype=1&isDustDay=true&DecimalPlaces=1";
        Map<String,Object> map6 = resultService.getYcDaily(url1,cookie,null,3);
        url2 = "http://116.147.41.178:8081/AQI/Statistics/YouLiangRateDataNewNewNew?sTime="+sTime+"&eTime="+eTime+"&DecimalPlaces=1&isDustDay=false&selec=0&selet=0&isDustDayXianShi=false";
        Map<String,Object> map8 = resultService.getYcDaily(url2,cookie,null,1);
        url3 = "http://116.147.41.178:8081/AQI/Statistics/StandardRateData?&sTime="+sTime+"&eTime="+eTime;
        Map<String,Object> surplusOldMap = resultService.getYcDaily(url3,cookie,null,4);

        //String templateFile = "E:/java/模板-盐城县市区统计001.xlsx";
        // 结果文件，省去了根据模板文件生成的步骤
       // String resultFile = "E:/java/模板-盐城县市区统计1.xlsx";

        String templateFile = basePath+"/001.xlsx";
        String resultFileName = endDateTime+"盐城县市区统计.xlsx";;
        String resultFile =UUID.randomUUID().toString()+".xlsx";;
        storageFile(filePath.concat(resultFile));

        // 根据模板文件生成目标文件
        ExcelWriter excelWriter = EasyExcel
                .write(filePath.concat(resultFile))
                .withTemplate(templateFile)
                // 单独设置单元格格式
//                .registerWriteHandler(new CellStyleHandler())
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet("日").build();
        WriteSheet writeSheet1 = EasyExcel.writerSheet("月").build();
        WriteSheet writeSheet2 = EasyExcel.writerSheet("年").build();
        // 每次都会重新生成新的一行，而不是使用下面的空行
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.FALSE).build();
        // 第一种占位符替换
       /*  Map<String, Object> map = new HashMap<>();
        List<TestVo> list = new ArrayList<>();
       TestVo testVo = TestVo.builder().sj("Sj11").sl("2234").build();
        TestVo testVo1 = TestVo.builder().sj("Sj1").sl("2214").build();
        list.add(testVo);
        list.add(testVo1);*/

//        map.put("time", "111113");
//        map.put("time1", "2222224");
       // map.put("dd", list);

        List<AqiResultVo>  list = (List<AqiResultVo>) map.get("table");
        List<AqiResultVo>  listG = list.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<AqiResultVo>  listG1 = list.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());


        excelWriter.fill(map, writeSheet);
        excelWriter.fill(new FillWrapper("data",listG),fillConfig, writeSheet);

        map1.put("title1",map2.get("title"));
        excelWriter.fill(map1, writeSheet1);
        List<SixConcentrationsVo>  list1 = (List<SixConcentrationsVo>) map1.get("table");
        testController.listReplaceName(list1);
        List<SixConcentrationsVo>  list1G = list1.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list1G1 = list1.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        List<SixConcentrationsVo>  list2 = (List<SixConcentrationsVo>) map2.get("table");
        testController.listReplaceName(list2);
        List<SixConcentrationsVo>  list2G = list2.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list2G1 = list2.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        List<SixConcentrationsVo>  list3 = (List<SixConcentrationsVo>) map3.get("table");
        testController.listReplaceName(list3);
        List<SixConcentrationsVo>  list3G = list3.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list3G1 = list3.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        List<SixConcentrationsVo>  list4 = (List<SixConcentrationsVo>) map4.get("table");
        testController.listReplaceName(list4);
        List<SixConcentrationsVo>  list4G = list4.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list4G1 = list4.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        excelWriter.fill(new FillWrapper("data1",list1G),fillConfig, writeSheet1);
        excelWriter.fill(new FillWrapper("data2",list2G),fillConfig, writeSheet1);
        excelWriter.fill(new FillWrapper("data3",list3G),fillConfig, writeSheet1);
        excelWriter.fill(new FillWrapper("data4",list4G),fillConfig, writeSheet1);

        map5.put("title3",map5.get("title"));
        map5.put("title4",map6.get("title"));
        excelWriter.fill(map5, writeSheet2);
        List<SixConcentrationsVo>  list5 = (List<SixConcentrationsVo>) map5.get("table");
        testController.listReplaceName(list5);
        List<SixConcentrationsVo>  list5G = list5.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list5G1 = list5.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        List<SixConcentrationsVo>  list6 = (List<SixConcentrationsVo>) map6.get("table");
        testController.listReplaceName(list6);
        List<SixConcentrationsVo>  list6G = list6.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list6G1 = list6.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        List<SixConcentrationsVo>  list7 = (List<SixConcentrationsVo>) map7.get("table");
        testController.listReplaceName(list7);
        List<SixConcentrationsVo>  list7G = list7.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list7G1 = list7.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        List<SixConcentrationsVo>  list8 = (List<SixConcentrationsVo>) map8.get("table");
        testController.listReplaceName(list8);
        List<SixConcentrationsVo>  list8G = list8.stream().filter(it->yc.contains(it.getName())).collect(Collectors.toList());
        List<SixConcentrationsVo>  list8G1 = list8.stream().filter(it->!yc.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());

        excelWriter.fill(new FillWrapper("data5",list5G),fillConfig, writeSheet2);
        excelWriter.fill(new FillWrapper("data6",list6G),fillConfig, writeSheet2);
        excelWriter.fill(new FillWrapper("data7",list7G),fillConfig, writeSheet2);
        excelWriter.fill(new FillWrapper("data8",list8G),fillConfig, writeSheet2);


        //excelWriter.fill(map, writeSheet1);
        //excelWriter.fill(new FillWrapper("time2",list),fillConfig, writeSheet1);
        excelWriter.finish();

        //String templateFile1 = "E:/java/模板-日报模板计算00.xlsx";
        // 结果文件，省去了根据模板文件生成的步骤
        //String resultFile1 = "E:/java/模板-日报模板计算1.xlsx";
        String templateFile1 = basePath+"/00.xlsx";
        String resultFileName1 = endDateTime+"日报统计.xlsx";
        String resultFile1 = UUID.randomUUID().toString()+".xlsx";;
        storageFile(filePath.concat(resultFile1));
        // 根据模板文件生成目标文件
        ExcelWriter excelWriter1 = EasyExcel
                .write(filePath.concat(resultFile1))
                .withTemplate(templateFile1)
                // 单独设置单元格格式
//                .registerWriteHandler(new CellStyleHandler())
                .build();
        WriteSheet writeSheet4 = EasyExcel.writerSheet("日排名").build();
        WriteSheet writeSheet5 = EasyExcel.writerSheet("月排名").build();
        WriteSheet writeSheet6 = EasyExcel.writerSheet("年排名").build();
        map.put("title1",map0.get("title"));
        excelWriter1.fill(map, writeSheet4);
        List<AqiResultVo>  list0 = (List<AqiResultVo>) map0.get("table");
        excelWriter1.fill(new FillWrapper("data",listG1),fillConfig, writeSheet4);
        excelWriter1.fill(new FillWrapper("data0",list0),fillConfig, writeSheet4);
        //map1.put("title1",map2.get("title"));
        excelWriter1.fill(map1, writeSheet5);
        excelWriter1.fill(new FillWrapper("data1",list1G1),fillConfig, writeSheet5);
        excelWriter1.fill(new FillWrapper("data2",list2G1),fillConfig, writeSheet5);
        excelWriter1.fill(new FillWrapper("data3",list3G1),fillConfig, writeSheet5);
        excelWriter1.fill(new FillWrapper("data4",list4G1),fillConfig, writeSheet5);

        excelWriter1.fill(map5, writeSheet6);
        excelWriter1.fill(new FillWrapper("data5",list5G1),fillConfig, writeSheet6);
        excelWriter1.fill(new FillWrapper("data6",list6G1),fillConfig, writeSheet6);
        excelWriter1.fill(new FillWrapper("data7",list7G1),fillConfig, writeSheet6);
        excelWriter1.fill(new FillWrapper("data8",list8G1),fillConfig, writeSheet6);
        excelWriter1.finish();
        log.info("开始生成简报");
        List<AqiResultVo> Yc = listG.stream().filter(it->it.getName().equals("盐城市区")).collect(Collectors.toList());
        // 月-优良
        SixConcentrationsVo ycMon = testController.sixResult(list1G,"盐城市区");
        SixConcentrationsVo ycMonOld = testController.sixResult(list2G,"盐城市区");
        SixConcentrationsVo ycYMon = testController.sixResult(list3G,"盐城市区");
        SixConcentrationsVo ycYMonOld = testController.sixResult(list4G,"盐城市区");
        // 年-优良
        SixConcentrationsVo ycYear = testController.sixResult(list5G,"盐城市区");
        SixConcentrationsVo ycYearOld = testController.sixResult(list6G,"盐城市区");
        SixConcentrationsVo ycYYear = testController.sixResult(list7G,"盐城市区");
        SixConcentrationsVo ycYYearOld = testController.sixResult(list8G,"盐城市区");
        // 超标天
        int surplusDay = (int) surplusMap.get("surplusDay");
        int surplusOldDay = (int) surplusOldMap.get("surplusDay");


        StringBuilder broadcast = new StringBuilder();
        String timeDay = map.get("title").toString().substring(3,map.get("title").toString().length());
        String timeMon = map1.get("title").toString().substring(3,map1.get("title").toString().length());
        String timeYear = map5.get("title").toString().substring(3,map5.get("title").toString().length());
        broadcast.append("各位领导、同事，").append(timeDay).append("空气质量分析简报，请查阅。").append("\n");

        broadcast.append("【盐城市空气质量情况】").append("\n");
        broadcast.append(timeDay+"，盐城市环境空气质量为"+Yc.get(0).getCategory()+"，其中PM2.5浓度为"+
                Yc.get(0).getGranule25()+"微克/立方米、O3浓度为"+Yc.get(0).getOO()+"微克/立方米、优良率为").
                append(Yc.get(0).getCategory().equals("良")||Yc.get(0).getCategory().equals("优")?"100%；":"0%；").append("\n");
        broadcast.append(timeMon+"，盐城市PM2.5浓度均值为"+ycMon.getPM25()+"微克/立方米，同比"+testController.compareYear(ycMon.getPM25(),ycMonOld.getPM25(),2)+"；O3浓度均值为");
        broadcast.append((int)ycMon.getOOAvg()+"微克/立方米，同比"+testController.compareYear(ycMon.getOOAvg(),ycMonOld.getOOAvg(),2)+"。优良率为"+ycYMon.getExcellentRateRatio()+"%，同比"+testController.compareYear(ycYMon.getExcellentRateRatio(),ycYMonOld.getExcellentRateRatio(),1)+"。").append("\n");
        broadcast.append("截至"+timeDay+",盐城市PM2.5浓度均值为"+ycYear.getPM25()+"微克/立方米，同比"+testController.compareYear(ycYear.getPM25(),ycYearOld.getPM25(),2)+"。O3浓度均值为");
        broadcast.append((int)ycYear.getOOAvg()+"微克/立方米，同比"+testController.compareYear(ycYear.getOOAvg(),ycYearOld.getOOAvg(),2)+"。优良率为"+ycYYear.getExcellentRateRatio()+"%，同比"+testController.compareYear(ycYYear.getExcellentRateRatio(),ycYYearOld.getExcellentRateRatio(),1)+"。").append("\n");
        broadcast.append("\n");

        broadcast.append("【盐城市空气质量达标情况分析】").append("\n");
        broadcast.append("截至"+timeDay+"，盐城市 PM2.5累积均值为"+ycYear.getPM25()+"微克/立方米（去年同期"+ycYearOld.getPM25()+"微克/立方米），同比"+testController.compareYear(ycYear.getPM25(),ycYearOld.getPM25(),2)+"，若要PM2.5年均浓度达到目标值27.0微克/立方米，剩余");
        broadcast.append(testController.annualTarget(ycYear.getPM25(),27.0,resultDate)+"优良天数比率为"+ycYYear.getExcellentRateRatio()+"%（去年同期"+ycYYearOld.getExcellentRateRatio()+"%），同比"+testController.compareYear(ycYYear.getExcellentRateRatio(),ycYYearOld.getExcellentRateRatio(),1)+"；当前已超标"+surplusDay+"天，超标天数已超过同期污染天数"+(surplusDay-surplusOldDay)+"天。").append("\n");
        broadcast.append("\n");

        broadcast.append("【国控站点空气质量情况】").append("\n");
        broadcast.append("截至"+timeDay+"，各国控站空气质量排名情况如下：").append("\n");;
        List<SixConcentrationsVo>  list5G2 = list5G1.stream().filter(it->gk.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());
        List<SixConcentrationsVo>  list6G2 = list6G1.stream().filter(it->gk.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());
        List<SixConcentrationsVo> list5G3  = testController.resultList(list5G2,list6G2,2);
        broadcast.append("PM2.5排名情况：绝对值排名后三名分别为："+testController.rankingList(list5G2,1)).append("改善排名后三名分别为："+testController.rankingList(list5G3,2)).append("\n");
        broadcast.append("O3排名情况：绝对值排名后三名分别为："+testController.rankingList(list5G2,3)).append("改善排名后三名分别为："+testController.rankingList(list5G3,4)).append("\n");
        List<SixConcentrationsVo>  list7G2 = list7G1.stream().filter(it->gk.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());
        List<SixConcentrationsVo>  list8G2 = list8G1.stream().filter(it->gk.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());
        List<SixConcentrationsVo> list7G3  = testController.resultList(list7G2,list8G2,1);
        broadcast.append("优良率排名情况：绝对值排名后三名分别为："+testController.rankingList(list7G2,5)).append("改善排名后三名分别为："+testController.rankingList(list7G3,6)).append("\n");;
        broadcast.append("\n");

        broadcast.append("【省控站点空气质量情况】").append("\n");
        broadcast.append("截至"+timeDay+"，各省控站空气质量排名情况如下：").append("\n");;
        List<SixConcentrationsVo>  list5G4 = list5G1.stream().filter(it->!gk.contains(it.getName())&&!it.getName().equals("总计")&&!it.getName().equals("亭湖区政府(自建站)")).collect(Collectors.toList());
        List<SixConcentrationsVo>  list6G4 = list6G1.stream().filter(it->!gk.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());
        List<SixConcentrationsVo> list5G5  = testController.resultList(list5G4,list6G4,2);
        broadcast.append("PM2.5排名情况：绝对值排名后三名分别为："+testController.rankingList(list5G4,1)).append("改善排名后三名分别为："+testController.rankingList(list5G5,2)).append("\n");
        broadcast.append("O3排名情况：绝对值排名后三名分别为："+testController.rankingList(list5G4,3)).append("改善排名后三名分别为："+testController.rankingList(list5G5,4)).append("\n");
        List<SixConcentrationsVo>  list7G4 = list7G1.stream().filter(it->!gk.contains(it.getName())&&!it.getName().equals("总计")&&!it.getName().equals("亭湖区政府(自建站)")).collect(Collectors.toList());
        List<SixConcentrationsVo>  list8G4= list8G1.stream().filter(it->!gk.contains(it.getName())&&!it.getName().equals("总计")).collect(Collectors.toList());
        List<SixConcentrationsVo> list7G5  = testController.resultList(list7G4,list8G4,1);
        broadcast.append("优良率排名情况：绝对值排名后三名分别为："+testController.rankingList(list7G4,5)).append("改善排名后三名分别为："+testController.rankingList(list7G5,6)).append("\n");
        broadcast.append("\n");

        broadcast.append("【11个县（市、区）空气质量情况】").append("\n");
        broadcast.append(timeMon+"，各县（市、区）空气质量排名情况如下：").append("\n");
        List<SixConcentrationsVo> list1G0 = list1G.stream().filter(it->!it.getName().equals("盐城市区")).collect(Collectors.toList());
        List<SixConcentrationsVo> list1G3  = testController.resultList(list1G0,list2G,2);
        broadcast.append("PM2.5排名情况：绝对值排名后三名分别为："+testController.rankingList(list1G0,1)).append("改善排名后三名分别为："+testController.rankingList(list1G3,2)).append("\n");
        broadcast.append("O3排名情况：绝对值排名后三名分别为："+testController.rankingList(list1G0,3)).append("改善排名后三名分别为："+testController.rankingList(list1G3,4)).append("\n");
        List<SixConcentrationsVo> list3G0 = list3G.stream().filter(it->!it.getName().equals("盐城市区")).collect(Collectors.toList());
        List<SixConcentrationsVo> list3G3  = testController.resultList(list3G0,list4G,1);
        broadcast.append("优良率排名情况：绝对值排名后三名分别为："+testController.rankingList(list3G0,5)).append("改善排名后三名分别为："+testController.rankingList(list3G3,6)).append("\n");

        broadcast.append("截至"+timeDay+"，各县（市、区）空气质量排名情况如下：").append("\n");
        List<SixConcentrationsVo> list5G00 = list5G.stream().filter(it->!it.getName().equals("盐城市区")).collect(Collectors.toList());
        List<SixConcentrationsVo> list5G0  = testController.resultList(list5G00,list6G,2);
        broadcast.append("PM2.5排名情况：绝对值排名后三名分别为："+testController.rankingList(list5G00,1)).append("改善排名后三名分别为："+testController.rankingList(list5G0,2)).append("\n");
        broadcast.append("O3排名情况：绝对值排名后三名分别为："+testController.rankingList(list5G00,3)).append("改善排名后三名分别为："+testController.rankingList(list5G0,4)).append("\n");
        List<SixConcentrationsVo> list7G00 = list7G.stream().filter(it->!it.getName().equals("盐城市区")).collect(Collectors.toList());
        List<SixConcentrationsVo> list7G0  = testController.resultList(list7G00,list8G,1);
        broadcast.append("优良率排名情况：绝对值排名后三名分别为："+testController.rankingList(list7G00,5)).append("改善排名后三名分别为："+testController.rankingList(list7G0,6)).append("\n");
        broadcast.append("\n");

        log.info(broadcast.toString());
        log.info("生成结束");

        // 插入附件 表格
        LambdaQueryChainWrapper<YcGenerateReport> wrapper = ycGenerateReportService.lambdaQuery();
        wrapper.eq(YcGenerateReport::getReportTime,DateUtils.convertDate(endDateTime));
        YcGenerateReport ycGenerateReport = wrapper.one();
        if(ycGenerateReport == null){
            ycGenerateReport = new YcGenerateReport();
            ycGenerateReport.setReportTime(DateUtils.convertDate(endDateTime));
            ycGenerateReport.setReportContent(broadcast.toString()).setOriginalContent(broadcast.toString());
            ycGenerateReportService.save(ycGenerateReport);
        }else {
            ycGenerateReport.setReportContent(broadcast.toString()).setOriginalContent(broadcast.toString());
            ycGenerateReportService.updateById(ycGenerateReport);
        }

        Integer id = ycGenerateReport.getId();
        LambdaQueryChainWrapper<FileTable> wrapperFile = fileTableService.lambdaQuery();
        wrapperFile.eq(FileTable::getFileModule,FileTableEnum.YC_GENERATEEXCEL.getValue());
        wrapperFile.eq(FileTable::getRelationId,id);
        List<FileTable> listFileTable = wrapperFile.list();
        if(CollectionUtils.isNotEmpty(listFileTable)){
            listFileTable.forEach(it->it.setIsDel(1));
            fileTableService.updateBatchById(listFileTable);
        }
        // 封装文件信息
        QxUser user = UserHelper.getCurrentUser();
        FileTable fileTable = new FileTable();
        fileTable.setFileModule(FileTableEnum.YC_GENERATEEXCEL.getValue()).setRelationId(id)
                .setFileName(resultFileName).setFileAddress(path.concat(resultFile)).setFileType(FileType.EXCEL.getValue());
        if (Objects.nonNull(user)) {
            fileTable.setCreateId(user.getUserId()).setCreateName(user.getUserName()).setCreateTime(new Date());
        }
        fileTableService.save(fileTable);
        fileTable.setFileName(resultFileName1).setFileAddress(path.concat(resultFile1));
        fileTableService.save(fileTable);

    }
    public static void main(String[] args) {
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sdf.setTimeZone(TimeZone.getTimeZone("GMT+8")); // 设置时区为东八区
        String dateString = sdf.format(date);
        System.out.println(dateString);
    }

    public String compareYear(Double d1,Double d2,int type){
        BigDecimal r = BigDecimal.ZERO;
        BigDecimal decimal = d2==null?BigDecimal.ZERO:new BigDecimal(d2);
        if(type == 1){
            r = (new BigDecimal(d1).subtract(decimal)).setScale(1, BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
        }else {
            if(d2==null){
                r = new BigDecimal(100).setScale(1, BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
            }else {
                r = (new BigDecimal(d1).subtract(decimal)).divide(decimal,3, BigDecimal.ROUND_HALF_UP).multiply(BigDecimal.valueOf(100d)).stripTrailingZeros();
            }
        }
        if(1 == r.compareTo(BigDecimal.ZERO)){
            return 1==type?"上升"+r.toPlainString()+"个百分点" :"上升"+r.toPlainString()+"%";
        }else if(0 == r.compareTo(BigDecimal.ZERO)){
            return "持平";
        }else {
            r = r.abs().setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
            return 1==type?"下降"+r.toPlainString()+"个百分点" :"下降"+r.toPlainString()+"%";
        }
    }

    public List<Object> compareYearList(Double d1,Double d2,int type){
        BigDecimal decimal = d2==null?BigDecimal.ZERO:new BigDecimal(d2);
        List<Object> list = new ArrayList<>();
        BigDecimal r = BigDecimal.ZERO;
        if(type == 1){
            r = (new BigDecimal(d1).subtract(decimal)).setScale(1, BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
        }else {
            if(d2 == null){
                r = new BigDecimal(100).setScale(1, BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
            }else {
                r = (new BigDecimal(d1).subtract(decimal)).divide(new BigDecimal(d2),3, BigDecimal.ROUND_HALF_UP).multiply(BigDecimal.valueOf(100d)).stripTrailingZeros();

            }
           }
        list.add(r);
        if(1 == r.compareTo(BigDecimal.ZERO)){
             if(1==type){
                 list.add("上升"+r.toPlainString()+"个百分点");
             }else {
                 list.add("上升"+r.toPlainString()+"%");
             }
        }else if(0 == r.compareTo(BigDecimal.ZERO)){
            list.add("持平");
        }else {
            r = r.abs().setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
            if(1==type){
                list.add("下降"+r.toPlainString()+"个百分点");
            }else {
                list.add("下降"+r.toPlainString()+"%");
            }
        }
        return list;
    }

    public SixConcentrationsVo sixResult(List<SixConcentrationsVo> list,String name){
        SixConcentrationsVo sixConcentrationsVo = new SixConcentrationsVo();
        if(CollectionUtils.isNotEmpty(list)){
            sixConcentrationsVo = list.stream().filter(it->it.getName().equals(name)).collect(Collectors.toList()).get(0);
        }
        return sixConcentrationsVo;
    }
    public String annualTarget(Double d,Double target,Date date){
        int year = DateUtils.getYear(date);
        int totalDays = Year.of(year).length();
        LocalDate todayLast = LocalDate.now().minusDays(1);
        LocalDate yearEnd = LocalDate.of(year, 12, 31);
        long daysLeft = yearEnd.toEpochDay() - todayLast.toEpochDay();
        BigDecimal r = new BigDecimal(target).multiply(new BigDecimal(totalDays));
        BigDecimal r1 = new BigDecimal(d).multiply(new BigDecimal(totalDays-daysLeft));
        BigDecimal r3 = (r.subtract(r1)).divide(new BigDecimal(daysLeft),1,BigDecimal.ROUND_HALF_UP);
        return daysLeft+"天PM2.5日均浓度应低于"+r3+"微克/立方米，";
    }

    public String rankingList(List<SixConcentrationsVo>  list,int type){
        List<SixConcentrationsVo> resultList = new ArrayList<>();
        String resultStr = null;
        if(1 == type){
            resultList = list.stream().sorted(Comparator.comparing(SixConcentrationsVo::getPM25,Comparator.reverseOrder())).collect(Collectors.toList());

        }else if(2 == type){
            resultList = list.stream().sorted(Comparator.comparing(SixConcentrationsVo::getYoYPM25,Comparator.reverseOrder())).collect(Collectors.toList());

        }else if(3 == type){
            resultList = list.stream().sorted(Comparator.comparing(SixConcentrationsVo::getOOAvg,Comparator.reverseOrder())).collect(Collectors.toList());

        }else if(4 == type){
            resultList = list.stream().sorted(Comparator.comparing(SixConcentrationsVo::getYoYO3,Comparator.reverseOrder())).collect(Collectors.toList());
        }else if(5 == type){
            resultList = list.stream().sorted(Comparator.comparing(SixConcentrationsVo::getExcellentRateRatio)).collect(Collectors.toList());
        }else if(6 == type){
            resultList = list.stream().sorted(Comparator.comparing(SixConcentrationsVo::getExcellentRateRatioYoY)).collect(Collectors.toList());
        }

        resultStr =  improveList(resultList,type,1);
        return resultStr;
    }

    public List<SixConcentrationsVo> resultList(List<SixConcentrationsVo> list1,List<SixConcentrationsVo> list2,int type){
        for(SixConcentrationsVo x : list1){
            for(SixConcentrationsVo x1 : list2){
                if(x.getName().equals(x1.getName())){
                    if(type == 1){
                        List<Object> rateRatioList = compareYearList(x.getExcellentRateRatio(),x1.getExcellentRateRatio(),1);
                        BigDecimal excellentRateRatioYoY = (BigDecimal) rateRatioList.get(0);
                        x.setExcellentRateRatioYoY(excellentRateRatioYoY.doubleValue());
                        x.setExcellentRateRatioYoYStr(rateRatioList.get(1).toString());
                    }else {
                        List<Object> pm25List = compareYearList(x.getPM25(),x1.getPM25(),2);
                        x.setYoYPM25((BigDecimal) pm25List.get(0));
                        x.setYoYPM25Str(pm25List.get(1).toString());
                        List<Object> o3List = compareYearList(x.getOOAvg(),x1.getOOAvg(),2);
                        x.setYoYO3((BigDecimal) o3List.get(0));
                        x.setYoYO3Str(o3List.get(1).toString());
                    }
                }
            }
        }
        return list1;
    }

    private String improveList(List<SixConcentrationsVo> resultList,int type,int lastType){
        //resultList.stream().sorted(Comparator.comparing(SixConcentrationsVo::getPM25,Comparator.reverseOrder())).collect(Collectors.toList());
        int num = 1;
        int numLength = 1;
        Map<String,List<SixConcentrationsVo>> map = new LinkedHashMap<>();
        for(int i = 0;i<resultList.size();i++){
            SixConcentrationsVo concentrationsVo = resultList.get(i);
            if(StringUtils.isEmpty(concentrationsVo.getName())&&!StringUtils.isEmpty(concentrationsVo.getExcellentRateName())){
                concentrationsVo.setName(concentrationsVo.getExcellentRateName());
            }
            String result = null;
            boolean numType =false;
            if(num>3){
                numType =true;
            }
            //pm2.5，2.5改善，O3,O3改善
            if(1 == type){
                result = Double.toString(concentrationsVo.getPM25());
            }else if(2 == type){
                result = concentrationsVo.getYoYPM25Str();
            }else if(3 == type){
                result = String.valueOf((int)(concentrationsVo.getOOAvg()));
            }else if(4 == type){
                result = concentrationsVo.getYoYO3Str();
            }else if(5 == type){
                result = Double.toString(concentrationsVo.getExcellentRateRatio());
            }else if(6 == type){
                result = concentrationsVo.getExcellentRateRatioYoYStr();
            }

            if(map.containsKey(result)){
                numType =false;
            }
            if(numType){
                break;
            }
            if(!map.containsKey(result)){
                if(numLength >3){
                    break;
                }
                List<SixConcentrationsVo> list = new ArrayList<>();
                list.add(concentrationsVo);
                map.put(result,list);
                num++;
            }else {
                List<SixConcentrationsVo> list = map.get(result);
                list.add(concentrationsVo);
                map.put(result ,list);
            }
            numLength++;
        }
        StringBuilder topThree = new StringBuilder();
        StringBuilder topThreeNum = new StringBuilder();

        num = 1;
        int mapNum = map.size();
        String unitName = null;
        if(1 == type||3 == type){
            unitName = "微克/立方米";
        }else if(5 == type){
            unitName = "%";
        }else{
            unitName = "";
        }
        for(Map.Entry entry : map.entrySet()){
            String mapKey =  entry.getKey().toString();
            List<SixConcentrationsVo> mapValue = (List<SixConcentrationsVo>)entry.getValue();
            if(1 == mapValue.size()){
                topThree.append(mapValue.get(0).getName());
                topThreeNum.append(mapKey).append(unitName);
            }else{
                topThree.append(mapValue.get(0).getName()).append("（");
              /*  if(5 == type){
                    topThreeNum.append(mapKey+"%").append(unitName).append("（");
                }else {

                }*/
                topThreeNum.append(mapKey).append(unitName).append("（");
                for(int i = 1 ;i<mapValue.size();i++){
                    if(i == mapValue.size()-1){
                        topThreeNum.append(mapKey).append(unitName).append("）");
                        topThree.append(mapValue.get(i).getName()).append("并列）");
                    }else {
                        topThreeNum.append(mapKey).append(unitName).append("、");
                        topThree.append(mapValue.get(i).getName()).append("、");
                    }

                }
            }

            if(num<mapNum){
                topThree.append("、");
                topThreeNum.append("、");
            }else {
                topThree.append("，");
                if(6 == type){
                    topThreeNum.append("。");
                }else {
                    topThreeNum.append(";");
                }

            }
            num++;
        }
        String resultTopThree = null;
        if(1 == type || 3 == type){
            resultTopThree = topThree.toString()+"浓度分别为"+topThreeNum.toString();
        }else if(2 == type || 4 == type || 6 == type){
            resultTopThree = topThree.toString()+"同比分别为"+topThreeNum.toString();
        }else if(5 == type){
            resultTopThree = topThree.toString()+"比例分别为"+topThreeNum.toString();
        }

        return resultTopThree;
    }

    public void listReplaceName(List<SixConcentrationsVo> list){
        for(SixConcentrationsVo l : list){
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(l.getName())&&l.getName().equals("大丰高级中学(国)")){
                l.setName("大丰高级中学");
            }
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(l.getName())&&l.getName().equals("盐都区(市)")){
                l.setName("盐都区");
            }
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(l.getExcellentRateName())&&l.getExcellentRateName().equals("大丰高级中学(国)")){
                l.setExcellentRateName("大丰高级中学");
            }
            if(org.apache.commons.lang3.StringUtils.isNotEmpty(l.getExcellentRateName())&&l.getExcellentRateName().equals("盐都区(市)")){
                l.setExcellentRateName("盐都区");
            }

        }


    }
    private String getFilePath(String sysCode) {
        return sysCode.concat(com.moral.api.utils.StringUtils.SLASH).concat(DateUtils.dateToDateString(new Date(),DateUtils.yyyyMMdd_EN)).concat(com.moral.api.utils.StringUtils.SLASH);
    }

    private void storageFile(String filePath){
        File f = new File(filePath);
        try {
            // 保存文件
            if (!f.getParentFile().exists()) {
                boolean success = f.mkdir();
                if (!f.getParentFile().mkdirs()) {
                    log.error("error:创建文件失败! dir={}", f.getAbsolutePath());
                }
            }
        } catch (Exception e) {
            log.error("error:文件上传失败!!!e={}", e.getMessage());
            throw new BusinessException("文件上传失败，请重试!");
        }
    }
}