package com.moral.api.service.impl;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.moral.api.entity.TbExcel;
import com.moral.api.mapper.ExcelMapper;
import com.moral.api.pojo.bo.ExcelBO;
import com.moral.api.pojo.vo.excel.HnExcelVo;
import com.moral.api.pojo.vo.excel.SyExcelVo;
import com.moral.api.service.HnExcelService;
import com.moral.api.utils.AqiUtils;
import com.moral.api.utils.ExcelUtils;
import com.moral.util.AQIUtils;
import com.moral.util.AmendUtils;
import com.moral.util.DateUtils;


@Service
public class HnExcelServiceImpl implements HnExcelService {

    @Autowired
    private ExcelMapper excelMapper;

    /**
     * 浑南区模板
     *
     * @param files
     */
    @Override
    public ExcelBO getExcelImport(List<MultipartFile> files, Map<String, Object> params) throws IOException, ParseException {
        String time = (String) params.get("time");
        String code = (String) params.get("code");
        String time1 = (String) params.get("date");
        Date date = DateUtils.getDate(time, "yyyy-MM-dd");
        Date date1 = DateUtils.getDate(time1, "yyyy-MM-dd");
        ExcelBO excelBO = new ExcelBO();
        QueryWrapper<TbExcel> wrapper = new QueryWrapper<>();
        wrapper.eq("time",date).eq("code",code);
        TbExcel excel = excelMapper.selectOne(wrapper);
        if (excel==null){
            TbExcel excel1 = new TbExcel();
            HashMap<String, Map<String, List<Integer>>> map = new HashMap<>();
            MultipartFile file1 = files.get(0);
            Map<String, List<Integer>> map1 = getMap1(file1, 0,time);
            map.put("浑南东路",map1);
            Map<String, List<Integer>> map2 = getMap1(file1, 1,time);
            map.put("新秀街",map2);
            Map<String, List<Integer>> map3 = getTo(file1,time);
            map.put("浑南区",map3);
            Map<String, List<Integer>> map4 = getSys(file1, 2, time);
            map.put("沈阳市",map4);
            String s = JSON.toJSONString(map);
            excel1.setValue(s);
            excel1.setTime(date);
            excel1.setCode(code);
            excel1.setDate(date1);
            excelMapper.insert(excel1);
            excelBO.setId(excel1.getId());
            excelBO.setTime(excel1.getTime());
            excelBO.setDate(excel1.getDate());
        }else {
            Date date2 = new Date();
            HashMap<String, Map<String, List<Integer>>> map = new HashMap<>();
            MultipartFile file1 = files.get(0);
            Map<String, List<Integer>> map1 = getMap1(file1, 0,time);
            map.put("浑南东路",map1);
            Map<String, List<Integer>> map2 = getMap1(file1, 1,time);
            map.put("新秀街",map2);
            Map<String, List<Integer>> map3 = getTo(file1,time);
            map.put("浑南区",map3);
            Map<String, List<Integer>> map4 = getSys(file1, 2, time);
            map.put("沈阳市",map4);
            String s = JSON.toJSONString(map);
            excel.setValue(s);
            excel.setDate(date2);
            excelMapper.updateById(excel);
            excelBO.setId(excel.getId());
            excelBO.setTime(excel.getTime());
            excelBO.setDate(date2);

        }
        return excelBO;


    }

    /**
     * 挂图作战导出
     * @param id
     * @return
     */
    @Override
    public SyExcelVo SyExport(Integer id) {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        HashMap<String, ArrayList<String>> rsMap = new HashMap<>();
        SyExcelVo syExcelVo = new SyExcelVo();
        TbExcel excel = excelMapper.selectById(id);
        String value = excel.getValue();
        Date time = excel.getTime();
        String format = sdf.format(time);
        String substring = format.substring(5, 7);
        int data= Integer.parseInt(substring);

        Map<String, Map<String, List<Integer>>> map = JSON.parseObject(value, Map.class);
        Set<String> strings = map.keySet();
        for (String string : strings) {
            if (string.equals("浑南东路")){
                Map<String, List<Integer>> stringListMap = map.get(string);
                Set<String> strings1 = stringListMap.keySet();
                for (String s : strings1) {
                    if (s.equals("count")){
                        ArrayList<String> list1 = getList(stringListMap, s);
                        rsMap.put("hnd1",list1);
                    }
                    if (s.equals("PM2_5")){
                        ArrayList<String> list1 = getArrayList(stringListMap, s);
                        rsMap.put("hnd2",list1);

                    }
                }
            }
            if (string.equals("新秀街")){
                Map<String, List<Integer>> stringListMap = map.get(string);
                Set<String> strings1 = stringListMap.keySet();
                for (String s : strings1) {
                    if (s.equals("count")){
                        ArrayList<String> list1 = getList(stringListMap, s);
                        rsMap.put("xxj1",list1);
                    }
                    if (s.equals("PM2_5")){
                        ArrayList<String> list1 = getArrayList(stringListMap, s);
                        rsMap.put("xxj2",list1);
                    }
                }
            }
            if (string.equals("沈阳市")){
                Map<String, List<Integer>> stringListMap = map.get(string);
                Set<String> strings1 = stringListMap.keySet();
                for (String s : strings1) {

                    if (s.equals("count")){
                        ArrayList<String> list1 = getList(stringListMap, s);
                        rsMap.put("sy1",list1);
                    }
                    if (s.equals("PM2_5")){
                        ArrayList<String> list1 = getArrayList(stringListMap, s);
                        rsMap.put("sy2",list1);
                    }
                }
            }
            if (string.equals("浑南区")){
                Map<String, List<Integer>> stringListMap = map.get(string);
                Set<String> strings1 = stringListMap.keySet();
                for (String s : strings1) {
                    if (s.equals("count")){
                        ArrayList<String> list1 = new ArrayList<>();
                        List<Integer> list = stringListMap.get(s);
                        String season;
                        Integer sum =0;
                        if (data>=1 && data<=3){
                            sum = list.get(0) + list.get(1) + list.get(2);
                            season = "一";
                        }else if (data>=4 &&data<=6){
                            sum = list.get(3) + list.get(4) + list.get(5);
                            season = "二";
                        }else if (data>=7 &&data<=9){
                            sum = list.get(6) + list.get(7) + list.get(8);
                            season = "三";
                        }else {
                            sum = list.get(9) + list.get(10) + list.get(11);
                            season = "四";
                        }
                        syExcelVo.setCode5(sum.toString());
                        syExcelVo.setSeason(season);
                        for (int i = 0; i < list.size()-1; i++) {
                            if (list.get(i)==0){
                                list1.add("");
                                continue;
                            }
                            list1.add(list.get(i).toString());
                        }
                        Integer integer = list.get(12);
                        syExcelVo.setCode1(integer.toString());
                        Integer integer1 = list.get(13);
                        int i = integer - integer1;
                        String code ="";
                        if (i<0){
                            code = "减少"+i+"天";
                        }else if (i>0){
                            code = "增加"+i+"天";
                        }else {
                            code = "持平";
                        }
                        list1.add(code);
                        syExcelVo.setCode2(code);
                        list1.add(0,"优良天数");
                        rsMap.put("hnq1",list1);
                    }


                    if (s.equals("季度")){
                        List<Integer> list = stringListMap.get(s);
                        if (list.size()>0){
                            syExcelVo.setCode6(list.get(0).toString());
                        }else {
                            syExcelVo.setCode6("");
                        }

                    }


                    if (s.equals("PM2_5")){
                        ArrayList<String> list1 = new ArrayList<>();
                        List<Integer> list = stringListMap.get(s);
                        for (int i = 0; i < list.size()-1; i++) {
                            if (list.get(i)==0){
                                list1.add("");
                                continue;
                            }
                            list1.add(list.get(i).toString());
                        }
                        Integer integer = list.get(12);
                        syExcelVo.setCode3(integer.toString());
                        Integer integer1 = list.get(13);
                        double i  = integer - integer1;

                        double i1 = AmendUtils.sciCal((Math.abs(i) / integer1)*100,1);
                        String code  = "";
                        if (i<0){
                            code = "改善"+i1+"%";
                        }else if (i>0){
                            code = "改善-"+i1+"%";
                        }else {
                            code ="持平";
                        }

                        list1.add(code);
                        syExcelVo.setCode4(code);
                        list1.add(0,"PM2.5浓度微克/立方");
                        rsMap.put("hnq2",list1);
                    }
                }
            }
        }
        if (map.size()==3){
            ArrayList<String> list1 = new ArrayList<>();
            ArrayList<String> list2 = new ArrayList<>();
            for (int i = 0; i <14 ; i++) {
                list1.add("");
                list2.add("");
            }
            list1.add(0,"优良天数");
            list2.add(0,"PM2.5浓度微克/立方");
            rsMap.put("sy1",list1);
            rsMap.put("sy2",list2);
        }
        syExcelVo.setList(rsMap);
        return syExcelVo;
    }

    /**
     * 浑南区质量报告导出
     * @param id
     * @return
     */
    @Override
    public HnExcelVo hnExport(Integer id) throws ParseException {

        HashMap<String, Object> rsMap = new HashMap<>();

        HnExcelVo hnExcelVo = new HnExcelVo();

        ArrayList<String> syList = new ArrayList<>();
        TbExcel excel = excelMapper.selectById(id);
        //获取上传的时间
        Date time = excel.getTime();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String format = sdf.format(time);
        hnExcelVo.setTime(format.substring(5));
        //获取上传后一天的时间
        Calendar instance = Calendar.getInstance();
        instance.setTime(sdf.parse(format));
        instance.add(Calendar.DAY_OF_MONTH, 1);
        Date date = instance.getTime();

        hnExcelVo.setTime2(sdf.format(date));


        String value = excel.getValue();
        Map<String, Map<String, List<Integer>>> map = JSON.parseObject(value, Map.class);
        Set<String> strings = map.keySet();

        Map<String, List<Integer>> listMap = map.get("沈阳市");
        List<Integer> listSy = listMap.get("day");
        //pm25
        syList.add(listSy.get(0).toString());
        //pm10
        syList.add(listSy.get(1).toString());
        //so2
        syList.add(listSy.get(2).toString());
        //no2
        syList.add(listSy.get(3).toString());
        //O3
        syList.add(listSy.get(4).toString());
        //CO
        syList.add(listSy.get(5).toString()+"."+listSy.get(6));
        Map<String, Object> mapSy = getCityMap(listSy);
        rsMap.put("sy",mapSy);


        for (String string : strings) {
            if (string.equals("浑南东路")){
                Map<String, List<Integer>> stringListMap = map.get(string);
                List<Integer> list = stringListMap.get("day");
                Map<String, Object> cityMap = getCityMap(list);
                rsMap.put("hnd",cityMap);
                ArrayList<Double> list1 = new ArrayList<>();
                for (int i = 0; i < list.size()-2; i++) {
                    Integer code1 = list.get(i);
                    int code2 = Integer.parseInt(syList.get(i));
                    double data = getData(code1, code2);
                    list1.add(data);
                }
                //获取CO
                String s = list.get(5) + "." + list.get(6);
                double v = Double.parseDouble(s) - Double.parseDouble(syList.get(5));
                double i1 =0.0;
                if (Double.parseDouble(syList.get(5))!=0.0){
                    i1 = AmendUtils.sciCal((v / Double.parseDouble(syList.get(5)))*100,1);
                }
                hnExcelVo.setHnCO(i1);
                hnExcelVo.setHnPM25(list1.get(0));
                hnExcelVo.setHnPM10(list1.get(1));
                hnExcelVo.setHnSO2(list1.get(2));
                hnExcelVo.setHnNO2(list1.get(3));
                hnExcelVo.setHnO3(list1.get(4));



                HashMap<String, Map<String, Object>> years = getYears(stringListMap,"hnd");
                hnExcelVo.setHnd(years);
            }
            if (string.equals("新秀街")){
                Map<String, List<Integer>> stringListMap = map.get(string);
                HashMap<String, Map<String, Object>> years = getYears(stringListMap,"xxj");
                hnExcelVo.setXxj(years);
                List<Integer> list = stringListMap.get("day");
                Map<String, Object> map1 = getCityMap(list);
                rsMap.put("xxj",map1);

                ArrayList<Double> list2 = new ArrayList<>();
                for (int i = 0; i < list.size()-2; i++) {
                    Integer code1 = list.get(i);
                    int code2 = Integer.parseInt(syList.get(i));
                    double data = getData(code1, code2);
                    list2.add(data);
                }
                //获取CO
                String s = list.get(5) + "." + list.get(6);
                double v = Double.parseDouble(s) - Double.parseDouble(syList.get(5));
                double i1 =0.0;
                if (Double.parseDouble(syList.get(5))!=0.0){
                    i1 = AmendUtils.sciCal((v / Double.parseDouble(syList.get(5)))*100,1);
                }
                hnExcelVo.setXxjCO(i1);
                hnExcelVo.setXxjPM25(list2.get(0));
                hnExcelVo.setXxjPM10(list2.get(1));
                hnExcelVo.setXxjSO2(list2.get(2));
                hnExcelVo.setXxjNO2(list2.get(3));
                hnExcelVo.setXxjO3(list2.get(4));




            }
            if (string.equals("浑南区")){
                Map<String, List<Integer>> stringListMap = map.get(string);
                List<Integer> list = stringListMap.get("count");
                Integer integer = list.get(12);
                Integer integer1 = list.get(13);
                int i = integer - integer1;
                String code = "";
                if (i<0){
                    code = "减少"+i+"天";
                }else if (i>0){
                    code = "增加"+i+"天";
                }else {
                    code= "持平";
                }
                hnExcelVo.setCode1(integer.toString());
                hnExcelVo.setCode2(integer1.toString());
                hnExcelVo.setCode3(code);
                List<Integer> list1 = stringListMap.get("PM2_5");
                Integer integer2 = list1.get(12);
                Integer integer3 = list1.get(13);
                String code1 = getString(integer2, integer3);
                hnExcelVo.setCode4(integer2.toString());
                hnExcelVo.setCode5(code1);

                List<Integer> list2 = stringListMap.get("PM10");
                Integer integer4 = list2.get(0);
                Integer integer5 = list2.get(1);
                String code2 = getString(integer4, integer5);
                hnExcelVo.setCode6(integer4.toString());
                hnExcelVo.setCode7(code2);


                HashMap<String, Object> hnqMap = new HashMap<>();
                List<Integer> list3 = stringListMap.get("day");
                hnqMap.put("PM2_5",list3.get(0));
                hnqMap.put("PM10",list3.get(1));
                hnqMap.put("SO2",list3.get(2));
                hnqMap.put("NO2",list3.get(3));
                hnqMap.put("O3",list3.get(4));
                hnqMap.put("CO",list3.get(5)+"."+list3.get(6));
                Map<String, Object> aqi = AqiUtils.getAqi(hnqMap);
                String aqi1 = aqi.get("AQI").toString();
                String su = aqi.get("SU").toString();
                String s = AQIUtils.classOfPollutionByAqi(Integer.parseInt(aqi1));
                hnExcelVo.setData1(s);
                hnExcelVo.setData2(su);
                hnExcelVo.setData3(list3.get(1).toString());
                hnExcelVo.setData4(list3.get(0).toString());
                hnExcelVo.setData5(list3.get(2).toString());
                hnExcelVo.setData6(list3.get(3).toString());
                hnExcelVo.setData7(hnqMap.get("CO").toString());
                hnExcelVo.setData8(list3.get(4).toString());
            }

        }
        hnExcelVo.setList1(rsMap);
        return hnExcelVo;
    }

    private String getString(Integer integer2, Integer integer3) {
        double i2  = integer2 - integer3;
        double i1 = 0.0;
        if (integer3 !=0){
            i1 = AmendUtils.sciCal((Math.abs(i2) / integer3)*100,1);
        }
        String code1  = "";
        if (i2==0){
            code1 = "持平";
        }else if (i2>0){
            code1 = "改善-"+i1+"%";
        }else {
            code1 = "改善"+i1+"%";
        }
        return code1;
    }

    //计算百分比
    private double getData(int code1,int code2) {

        double i = code1 - code2;
        double i1 =0.0;
        if (code2!=0){
            i1 = AmendUtils.sciCal((i / code2)*100,1);
        }
        return i1;
    }

    //获取aqi和首污
    private Map<String, Object> getCityMap(List<Integer> list) {
        HashMap<String, Object> map1 = new HashMap<>();
        map1.put("PM2_5", list.get(0));
        map1.put("PM10", list.get(1));
        map1.put("SO2", list.get(2));
        map1.put("NO2", list.get(3));
        map1.put("O3", list.get(4));
        map1.put("CO", list.get(5)+"."+ list.get(6));
        map1.put("ZH","");
        map1.put("PaiM","");
        Map<String, Object> aqi = AqiUtils.getAqi(map1);
        map1.put("AQI",aqi.get("AQI"));
        return  map1;
    }

    /**
     * 计算幅度变化
     * @param stringListMap
     * @return
     */
    private HashMap<String, Map<String, Object>> getYears(Map<String, List<Integer>> stringListMap,String data) {
        HashMap<String, Map<String, Object>> map = new HashMap<>();
        List<Integer> list1 = stringListMap.get("今年");
        HashMap<String, Object> hudMap1 = new HashMap<>();
        HashMap<String, Object> hudMap2 = new HashMap<>();
        HashMap<String, Object> hudMap3 = new HashMap<>();
        hudMap1.put("nd",list1.get(7));
        hudMap1.put("yl",list1.get(0));
        hudMap1.put("excellent",list1.get(1));
        hudMap1.put("good",list1.get(2));
        hudMap1.put("slightPol",list1.get(3));
        hudMap1.put("moderatePol",list1.get(4));
        hudMap1.put("heavyPol",list1.get(5));
        hudMap1.put("seriousPol",list1.get(6));
        map.put(data+"1",hudMap1);
        List<Integer> list2 = stringListMap.get("去年");
        hudMap2.put("nd",list2.get(7));
        hudMap2.put("yl",list2.get(0));
        hudMap2.put("excellent",list2.get(1));
        hudMap2.put("good",list2.get(2));
        hudMap2.put("slightPol",list2.get(3));
        hudMap2.put("moderatePol",list2.get(4));
        hudMap2.put("heavyPol",list2.get(5));
        hudMap2.put("seriousPol",list2.get(6));

        hudMap3.put("nd","变化幅度");
        hudMap3.put("yl",list1.get(0)-list2.get(0));
        hudMap3.put("excellent",list1.get(1)-list2.get(1));
        hudMap3.put("good",list1.get(2)-list2.get(2));
        hudMap3.put("slightPol",list1.get(3)-list2.get(3));
        hudMap3.put("moderatePol",list1.get(4)-list2.get(4));
        hudMap3.put("heavyPol",list1.get(5)-list2.get(5));
        hudMap3.put("seriousPol",list1.get(6)-list2.get(6));

        map.put(data+2,hudMap2);
        map.put(data+3,hudMap3);

        return map;
    }

    /**
     * 计算pm2.5同比
     * @param stringListMap
     * @param s
     * @return
     */
    private ArrayList<String> getArrayList(Map<String, List<Integer>> stringListMap, String s) {
        ArrayList<String> list1 = new ArrayList<>();
        List<Integer> list = stringListMap.get(s);
        for (int i = 0; i < list.size()-1; i++) {
            if (list.get(i)==0){
                list1.add("");
                continue;
            }
            list1.add(list.get(i).toString());
        }
        Integer integer = list.get(12);
        Integer integer1 = list.get(13);
        double i1 = 0.0;
        double i  = integer - integer1;
        if (integer1!=0){

            i1 = AmendUtils.sciCal((Math.abs(i) / integer1)*100,1);
        }
        String code  = "";
        if (i<0){
            code = "改善"+i1+"%";
        }else if (i>0){
            code = "改善-"+i1+"%";
        }else {
            code = "持平";
        }
        list1.add(code);
        list1.add(0,"PM2.5浓度微克/立方");
        return list1;
    }

    /**
     * 计算优良天数
     * @param stringListMap
     * @param s
     * @return
     */
    private ArrayList<String> getList(Map<String, List<Integer>> stringListMap, String s) {
        ArrayList<String> list1 = new ArrayList<>();
        List<Integer> list = stringListMap.get(s);
        for (int i = 0; i < list.size()-1; i++) {
            if (list.get(i)==0){
                list1.add("");
                continue;
            }
            list1.add(list.get(i).toString());
        }
        Integer integer = list.get(12);
        Integer integer1 = list.get(13);
        int i = integer - integer1;
        String code = "";
        if (i<0){
            code = "减少"+i+"天";
        }else if (i>0){
            code = "增加"+i+"天";
        }else {
            code = "持平";
        }
        list1.add(code);
        list1.add(0,"优良天数");
        return list1;
    }


    private Map<String, List<Integer>> getSys(MultipartFile file, int code,String time) throws IOException, ParseException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());

        HashMap<String, Object> rsMap1 = new HashMap<>();
        HashMap<String, Object> rsMap2 = new HashMap<>();
        XSSFSheet sheetAt = workbook.getSheetAt(code);
        String sheetName = sheetAt.getSheetName();
        if (!sheetName.equals("沈阳市")){
            return null;
        }
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 2; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum<=0){
                continue;
            }

            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    Object value = ExcelUtils.getValue(cell);
                    objects[j] = value;
                }

            }

            HashMap<String, Object> map1 = new HashMap<>();
            HashMap<String, Object> map2 = new HashMap<>();
            if (objects.length == 8 || objects.length == 9) {
                if (objects[0]!=null){
                    if (!objects[1].toString().contains("H")
                            && !objects[2].toString().contains("H")
                            && !objects[3].toString().contains("H")
                            && !objects[4].toString().contains("H")
                            && !objects[5].toString().contains("H")
                            && !objects[6].toString().contains("H"))
                    {
                        map1.put("time", objects[0]);
                        map1.put("PM10", objects[1]);
                        map1.put("PM2_5", objects[2]);
                        map1.put("SO2", objects[3]);
                        map1.put("NO2", objects[4]);
                        map1.put("CO", objects[5]);
                        map1.put("O3", objects[6]);
                        map1.put("AQI", objects[7]);
                        rsMap1.put(objects[0].toString(), map1);
                    }
                }


            }
            if (objects.length == 17) {

                if (!objects[1].toString().contains("H")
                        && !objects[2].toString().contains("H")
                        && !objects[3].toString().contains("H")
                        && !objects[4].toString().contains("H")
                        && !objects[5].toString().contains("H")
                        && !objects[6].toString().contains("H"))
                {
                    map1.put("time", objects[0]);
                    map1.put("PM10", objects[1]);
                    map1.put("PM2_5", objects[2]);
                    map1.put("SO2", objects[3]);
                    map1.put("NO2", objects[4]);
                    map1.put("CO", objects[5]);
                    map1.put("O3", objects[6]);
                    map1.put("AQI", objects[7]);
                    rsMap1.put(objects[0].toString(), map1);
                }

                if (objects[9]!=null){
                    if (!objects[10].toString().contains("H")
                            && !objects[11].toString().contains("H")
                            && !objects[12].toString().contains("H")
                            && !objects[13].toString().contains("H")
                            && !objects[14].toString().contains("H")
                            && !objects[15].toString().contains("H"))
                    {
                        map2.put("time", objects[9]);
                        map2.put("PM10", objects[10]);
                        map2.put("PM2_5", objects[11]);
                        map2.put("SO2", objects[12]);
                        map2.put("NO2", objects[13]);
                        map2.put("CO", objects[14]);
                        map2.put("O3", objects[15]);
                        map2.put("AQI", objects[16]);
                        rsMap2.put(objects[9].toString(), map2);
                    }
                }

            }


        }
        //计算本年度pm2.5和优良天数
        Map<String, List<Integer>> map = getMap(rsMap2,time);
        //计算去年PM2.5和优良天数
        Map<String, List<Integer>> map2 = getResult(rsMap1, time);
        List<Integer> list = map.get("count");
        list.add(map2.get("count").get(0));
        List<Integer> list1 = map.get("PM2_5");
        list1.add(map2.get("PM2_5").get(0));
        return map;
    }

    private Map<String, List<Integer>> getMap1(MultipartFile file, int code,String time) throws IOException, ParseException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HashMap<String, Object> rsMap1 = new HashMap<>();
        HashMap<String, Object> rsMap2 = new HashMap<>();
        HashMap<String, Object> rsMap3 = new HashMap<>();
        XSSFSheet sheetAt = workbook.getSheetAt(code);
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 2; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 8) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    Object value = ExcelUtils.getValue(cell);
                    objects[j] = value;
                }

            }
            HashMap<String, Object> map = new HashMap<>();
            HashMap<String, Object> map1 = new HashMap<>();
            HashMap<String, Object> map2 = new HashMap<>();
//            if (objects.length == 9) {
//                map.put("time", objects[0]);
//                map.put("PM10", objects[1]);
//                map.put("PM2_5", objects[2]);
//                map.put("SO2", objects[3]);
//                map.put("NO2", objects[4]);
//                map.put("CO", objects[5]);
//                map.put("O3", objects[6]);
//                map.put("AQI", objects[7]);
//                rsMap1.put(objects[0].toString(), map);
//            }
            if (objects.length == 18) {
                map.put("time", objects[0]);
                map.put("PM10", objects[1]);
                map.put("PM2_5", objects[2]);
                map.put("SO2", objects[3]);
                map.put("NO2", objects[4]);
                map.put("CO", objects[5]);
                map.put("O3", objects[6]);
                map.put("AQI", objects[7]);
                rsMap1.put(objects[0].toString(), map);

                if (!objects[11].toString().contains("H")
                        && !objects[12].toString().contains("H")
                        && !objects[13].toString().contains("H")
                        && !objects[14].toString().contains("H")
                        && !objects[15].toString().contains("H")
                        && !objects[16].toString().contains("H"))
                {
                    map1.put("time", objects[10]);
                    map1.put("PM10", objects[11]);
                    map1.put("PM2_5", objects[12]);
                    map1.put("SO2", objects[13]);
                    map1.put("NO2", objects[14]);
                    map1.put("CO", objects[15]);
                    map1.put("O3", objects[16]);
                    map1.put("AQI", objects[17]);
                    rsMap2.put(objects[10].toString(), map1);
                }

            }

            if (objects.length == 27) {
                map.put("time", objects[0]);
                map.put("PM10", objects[1]);
                map.put("PM2_5", objects[2]);
                map.put("SO2", objects[3]);
                map.put("NO2", objects[4]);
                map.put("CO", objects[5]);
                map.put("O3", objects[6]);
                map.put("AQI", objects[7]);
                rsMap1.put(objects[0].toString(), map);
                if (   !objects[11].toString().contains("H")
                        && !objects[12].toString().contains("H")
                        && !objects[13].toString().contains("H")
                        && !objects[14].toString().contains("H")
                        && !objects[15].toString().contains("H")
                        && !objects[16].toString().contains("H")
                ) {

                    map1.put("time", objects[10]);
                    map1.put("PM10", objects[11]);
                    map1.put("PM2_5", objects[12]);
                    map1.put("SO2", objects[13]);
                    map1.put("NO2", objects[14]);
                    map1.put("CO", objects[15]);
                    map1.put("O3", objects[16]);
                    map1.put("AQI", objects[17]);
                    rsMap2.put(objects[10].toString(), map1);
                }
                if (objects[19]!=null){
                    if ( !objects[20].toString().contains("H")
                            && !objects[21].toString().contains("H")
                            && !objects[22].toString().contains("H")
                            && !objects[23].toString().contains("H")
                            && !objects[24].toString().contains("H")
                            && !objects[25].toString().contains("H")
                    ) {
                        map2.put("time", objects[19]);
                        map2.put("PM10", objects[20]);
                        map2.put("PM2_5", objects[21]);
                        map2.put("SO2", objects[22]);
                        map2.put("NO2", objects[23]);
                        map2.put("CO", objects[24]);
                        map2.put("O3", objects[25]);
                        map2.put("AQI", objects[26]);
                        rsMap3.put(objects[19].toString(), map2);
                    }
                }

            }

        }
        //计算本年度pm2.5和优良天数
        Map<String, List<Integer>> map = getMap(rsMap3,time);
        //计算去年PM2.5和优良天数
        Map<String, List<Integer>> map2 = getResult(rsMap2, time);
        List<Integer> list = map.get("count");
        list.add(map2.get("count").get(0));
        List<Integer> list1 = map.get("PM2_5");
        list1.add(map2.get("PM2_5").get(0));
        map.put("去年",map2.get("去年"));
        return map;
    }

    /**
     * 计算本年的PM2.5 和优良天数
     *
     * @param rsMap3
     */
    private Map<String, List<Integer>> getMap(HashMap<String, Object> rsMap3,String time) {
        HashMap<String, List<Integer>> listMap = new HashMap<>();
        ArrayList<Integer> dayList = new ArrayList<>();
        ArrayList<Double> list1 = new ArrayList<>();
        ArrayList<Double> list2 = new ArrayList<>();
        ArrayList<Double> list3 = new ArrayList<>();
        ArrayList<Double> list4 = new ArrayList<>();
        ArrayList<Double> list5 = new ArrayList<>();
        ArrayList<Double> list6 = new ArrayList<>();
        ArrayList<Double> list7 = new ArrayList<>();
        ArrayList<Double> list8 = new ArrayList<>();
        ArrayList<Double> list9 = new ArrayList<>();
        ArrayList<Double> list10 = new ArrayList<>();
        ArrayList<Double> list11 = new ArrayList<>();
        ArrayList<Double> list12 = new ArrayList<>();
        ArrayList<Double> resList = new ArrayList<>();
        ArrayList<Integer> list = new ArrayList<>();
        ArrayList<Integer> listPM = new ArrayList<>();
        ArrayList<Double> AQI = new ArrayList<>();
        ArrayList<Double> pm10List = new ArrayList<>();

        int count1 = 0;
        int count2 = 0;
        int count3 = 0;
        int count4 = 0;
        int count5 = 0;
        int count6 = 0;
        int count7 = 0;
        int count8 = 0;
        int count9 = 0;
        int count10 = 0;
        int count11 = 0;
        int count12 = 0;

        Set<String> strings = rsMap3.keySet();
        for (String string : strings) {
            if (string.equals(time)){
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                JSONObject jsonObject = JSON.parseObject(s);
                Object pm25 = jsonObject.get("PM2_5");
                String co = jsonObject.getString("CO");
                String[] split = co.split("\\.");
                int PM2_5 = (int) Double.parseDouble(pm25.toString());
                int PM10 = (int) Double.parseDouble(jsonObject.getString("PM10"));
                int SO2 = (int) Double.parseDouble(jsonObject.getString("SO2"));
                int NO2 = (int) Double.parseDouble(jsonObject.getString("NO2"));
                int O3 = (int) Double.parseDouble(jsonObject.getString("O3"));
                dayList.add(PM2_5);
                dayList.add(PM10);
                dayList.add(SO2);
                dayList.add(NO2);
                dayList.add(O3);
                dayList.add(Integer.parseInt(split[0]));
                dayList.add(Integer.parseInt(split[1]));
            }

            String substring = string.substring(5, 7);

            if (substring.equals("01")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list1.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count1++;
                }
            }
            if (substring.equals("02")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list2.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count2++;
                }
            }
            if (substring.equals("03")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list3.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count3++;
                }
            }
            if (substring.equals("04")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list4.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count4++;
                }
            }
            if (substring.equals("05")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list5.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count5++;
                }
            }
            if (substring.equals("06")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list6.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count6++;
                }
            }
            if (substring.equals("07")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list7.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count7++;
                }
            }
            if (substring.equals("08")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list8.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count8++;
                }
            }
            if (substring.equals("09")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list9.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count9++;
                }
            }
            if (substring.equals("10")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list10.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count10++;
                }
            }
            if (substring.equals("11")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list11.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count11++;
                }
            }
            if (substring.equals("12")) {
                Object o = rsMap3.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                pm10List.add(Double.parseDouble(pm10));
                list12.add(Double.parseDouble(pm2_5));
                resList.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count12++;
                }
            }

        }
        //获取季度pm2.5
        String ju = time.substring(5, 7);
        int data = Integer.parseInt(ju);
        ArrayList<Double> rsData = new ArrayList<>();
        if (data>=1 && data<=3){
            rsData.addAll(list1);
            rsData.addAll(list2);
            rsData.addAll(list3);
        }else if (data>=4 &&data<=6){
            rsData.addAll(list4);
            rsData.addAll(list5);
            rsData.addAll(list6);
        }else if (data>=7 &&data<=9){
            rsData.addAll(list7);
            rsData.addAll(list8);
            rsData.addAll(list9);
        }else {
            rsData.addAll(list10);
            rsData.addAll(list11);
            rsData.addAll(list12);
        }
        ArrayList<Integer> pmList = new ArrayList<>();
        if (rsData.size()>0){
            int pm = (int) AmendUtils.sciCal(rsData.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
            pmList.add(pm);
        }


        //获取污染状态
        ArrayList<Integer> integers = getCode(AQI);
        String rsTime = time.substring(0, 4);
        integers.add(Integer.parseInt(rsTime));


        list.add(count1);
        list.add(count2);
        list.add(count3);
        list.add(count4);
        list.add(count5);
        list.add(count6);
        list.add(count7);
        list.add(count8);
        list.add(count9);
        list.add(count10);
        list.add(count11);
        list.add(count12);

        Integer sum = list.stream().reduce(Integer::sum).orElse(0);

        list.add(sum);
        listMap.put("count", list);

        Integer collect1 = 0;
        if (list1.size() > 0) {

            collect1 = (int) AmendUtils.sciCal(list1.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect2 = 0;
        if (list2.size() > 0) {

            collect2= (int) AmendUtils.sciCal(list2.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect3 = 0;
        if (list3.size() > 0) {

            collect3 = (int) AmendUtils.sciCal(list3.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect4 = 0;
        if (list4.size() > 0) {

            collect4 = (int) AmendUtils.sciCal(list4.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect5 = 0;
        if (list5.size() > 0) {

            collect5 = (int) AmendUtils.sciCal(list5.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect6 = 0;
        if (list6.size() > 0) {

            collect6 = (int) AmendUtils.sciCal(list6.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }

        Integer collect7 = 0;
        if (list7.size() > 0) {

            collect7 = (int) AmendUtils.sciCal(list7.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect8 = 0;
        if (list8.size() > 0) {

            collect8 = (int) AmendUtils.sciCal(list8.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect9 = 0;
        if (list9.size() > 0) {

            collect9 = (int) AmendUtils.sciCal(list9.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect10 = 0;
        if (list10.size() > 0) {

            collect10 = (int) AmendUtils.sciCal(list10.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect11 = 0;
        if (list11.size() > 0) {

            collect11 = (int) AmendUtils.sciCal(list11.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        Integer collect12 = 0;
        if (list12.size() > 0) {

            collect12 = (int) AmendUtils.sciCal(list12.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        }
        //今年的pm2.5累计
        int resPM25 = (int) AmendUtils.sciCal(resList.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        ArrayList<Integer> list13 = new ArrayList<>();
        int rsPM10 = (int) AmendUtils.sciCal(pm10List.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        list13.add(rsPM10);
        listPM.add(collect1);
        listPM.add(collect2);
        listPM.add(collect3);
        listPM.add(collect4);
        listPM.add(collect5);
        listPM.add(collect6);
        listPM.add(collect7);
        listPM.add(collect8);
        listPM.add(collect9);
        listPM.add(collect10);
        listPM.add(collect11);
        listPM.add(collect12);
        listPM.add(resPM25);
        listMap.put("PM2_5",listPM);
        listMap.put("今年",integers);
        listMap.put("day",dayList);
        listMap.put("季度",pmList);
        listMap.put("PM10",list13);
        return listMap;
    }

    /**
     * 获取污染状态
     * @param AQI
     * @return
     */
    private ArrayList<Integer> getCode(ArrayList<Double> AQI) {
        ArrayList<Integer> integers = new ArrayList<>();
        int code1 =0;
        int code2 =0;
        int code3 =0;
        int code4 =0;
        int code5 =0;
        int code6 =0;
        int i;
        for (double aDouble : AQI) {
            i=(int)aDouble;
            String s = AQIUtils.classOfPollutionByAqi(i);
            if (s.equals("优")){
                code1++;
            }
            if (s.equals("良")){
                code2++;
            }
            if (s.equals("轻度污染")){
                code3++;
            }
            if (s.equals("中度污染")){
                code4++;
            }
            if (s.equals("重度污染")){
                code5++;
            }
            if (s.equals("严重污染")){
                code6++;
            }
        }
        integers.add(code1+code2);
        integers.add(code1);
        integers.add(code2);
        integers.add(code3);
        integers.add(code4);
        integers.add(code5);
        integers.add(code6);

        return integers;
    }

    /**
     * 计算去年PM2.5和优良天数
     *
     * @param rsMap2
     */
    private Map<String, List<Integer>> getResult(HashMap<String, Object> rsMap2, String time) throws ParseException {
        HashMap<String, List<Integer>> rsMap = new HashMap<>();
        ArrayList<Double> list = new ArrayList<>();
        ArrayList<Integer> list2 = new ArrayList<>();
        ArrayList<Integer> list3 = new ArrayList<>();
        ArrayList<Double> AQI = new ArrayList<>();
        ArrayList<Double> list4 = new ArrayList<>();
        ArrayList<Integer> pm10List = new ArrayList<>();
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
        Calendar instance = Calendar.getInstance();
        instance.setTime(sdf1.parse(time));
        instance.add(Calendar.YEAR, -1);
        Date date = instance.getTime();
        //获取年
        String format = sdf1.format(date);
        String rsTime = format.substring(0, 4);
        //时间戳
        long time1 = date.getTime();
        int count = 0;
        Set<String> strings = rsMap2.keySet();
        for (String string : strings) {
            Date parse = sdf1.parse(string);
            long time2 = parse.getTime();
            if (time2 <= time1) {
                Object o = rsMap2.get(string);
                String s = JSON.toJSONString(o);
                Map map = JSON.parseObject(s, Map.class);
                String aqi = map.get("AQI").toString();
                String pm2_5 = map.get("PM2_5").toString();
                String pm10 = map.get("PM10").toString();
                list4.add(Double.parseDouble(pm10));
                list.add(Double.parseDouble(pm2_5));
                AQI.add(Double.parseDouble(aqi));
                if (Double.parseDouble(aqi) <= 100) {
                    count++;
                }
            }
        }
        list2.add(count);
        ArrayList<Integer> rsAqi = getCode(AQI);
        rsAqi.add(Integer.parseInt(rsTime));

        //去年的pm2.5累积
        int PM2_5 = (int) AmendUtils.sciCal(list.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        int PM10 = (int) AmendUtils.sciCal(list4.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        list3.add(PM2_5);
        pm10List.add(PM10);
        rsMap.put("count", list2);
        rsMap.put("PM2_5", list3);
        rsMap.put("去年",rsAqi);
        rsMap.put("PM10",pm10List);

        return rsMap;
    }

    /**
     * 计算 浑南区的数据
     * @param file
     * @throws IOException
     */
    private Map<String, List<Integer>> getTo(MultipartFile file,String time) throws IOException, ParseException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HashMap<String, Object> rsMap1 = new HashMap<>();
        HashMap<String, Object> rsMap2 = new HashMap<>();
        HashMap<String, Object> rsMap3 = new HashMap<>();
        HashMap<String, Object> rsMap4 = new HashMap<>();
        //去年的浑南区
        HashMap<String, Object> hnMap1 = new HashMap<>();
        //今年的浑南区
        HashMap<String, Object> hnMap2 = new HashMap<>();

        XSSFSheet sheetAt = workbook.getSheetAt(0);

        //一共有多少行
        //浑南东路
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 2; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 8) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    Object value = ExcelUtils.getValue(cell);
                    objects[j] = value;
                }

            }
            HashMap<String, Object> map1 = new HashMap<>();
            HashMap<String, Object> map2 = new HashMap<>();
            if (objects.length == 18) {

                map1.put("PM10", objects[11]);
                map1.put("PM2_5", objects[12]);
                map1.put("SO2", objects[13]);
                map1.put("NO2", objects[14]);
                map1.put("CO", objects[15]);
                map1.put("O3", objects[16]);

                rsMap1.put(objects[10].toString(), map1);

            }

            if (objects.length == 27) {

                map1.put("PM10", objects[11]);
                map1.put("PM2_5", objects[12]);
                map1.put("SO2", objects[13]);
                map1.put("NO2", objects[14]);
                map1.put("CO", objects[15]);
                map1.put("O3", objects[16]);

                rsMap1.put(objects[10].toString(), map1);

                if (objects[19]!=null){
                    map2.put("PM10", objects[20]);
                    map2.put("PM2_5", objects[21]);
                    map2.put("SO2", objects[22]);
                    map2.put("NO2", objects[23]);
                    map2.put("CO", objects[24]);
                    map2.put("O3", objects[25]);

                    rsMap2.put(objects[19].toString(), map2);

                }

            }

        }

        //新秀街
        XSSFSheet sheetAt1 = workbook.getSheetAt(1);

        int lastRowNum1 = sheetAt1.getLastRowNum();
        for (int i = 2; i < lastRowNum1; i++) {
            XSSFRow row = sheetAt1.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 8) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    Object value = ExcelUtils.getValue(cell);
                    objects[j] = value;
                }

            }
            HashMap<String, Object> map1 = new HashMap<>();
            HashMap<String, Object> map2 = new HashMap<>();
            if (objects.length == 18) {

                map1.put("PM10", objects[11]);
                map1.put("PM2_5", objects[12]);
                map1.put("SO2", objects[13]);
                map1.put("NO2", objects[14]);
                map1.put("CO", objects[15]);
                map1.put("O3", objects[16]);

                rsMap3.put(objects[10].toString(), map1);


            }

            if (objects.length == 27) {

                map1.put("PM10", objects[11]);
                map1.put("PM2_5", objects[12]);
                map1.put("SO2", objects[13]);
                map1.put("NO2", objects[14]);
                map1.put("CO", objects[15]);
                map1.put("O3", objects[16]);
                rsMap3.put(objects[10].toString(), map1);

                if (objects[19]!=null){
                    map2.put("PM10", objects[20]);
                    map2.put("PM2_5", objects[21]);
                    map2.put("SO2", objects[22]);
                    map2.put("NO2", objects[23]);
                    map2.put("CO", objects[24]);
                    map2.put("O3", objects[25]);
                    rsMap4.put(objects[19].toString(), map2);
                }

            }

        }
        //获取去年的因子
        Set<String> strings = rsMap1.keySet();
        for (String string : strings) {
            Object o = rsMap1.get(string);
            String s = JSON.toJSONString(o);
            Map map = JSON.parseObject(s, Map.class);

            Object o1 = rsMap3.get(string);
            String s1 = JSON.toJSONString(o1);
            Map map1 = JSON.parseObject(s1, Map.class);
            Set sets = map1.keySet();
            HashMap<Object, Object> hs = new HashMap<>();
            for (Object set : sets) {
                if (set.toString().equals("CO")){
                    String s3 = map1.get(set).toString();
                    String s2 = map.get(set).toString();
                    if (s3.contains("H") && !s2.contains("H")){
                        hs.put(set,Double.parseDouble(s2));
                    }else if(!s3.contains("H") && s2.contains("H")){
                        hs.put(set,Double.parseDouble(s3));
                    }else if(s3.contains("H") && s2.contains("H")){
                        break;
                    }else {
                        hs.put(set, AmendUtils.sciCal((Double.parseDouble(s3)+Double.parseDouble(s2))/2,1));
                    }
                    continue;
                }
                String s3 = map1.get(set).toString();
                String s2 = map.get(set).toString();
                if (s3.contains("H") && !s2.contains("H")){
                    hs.put(set,Double.parseDouble(s2));
                }else if(!s3.contains("H") && s2.contains("H")){
                    hs.put(set,Double.parseDouble(s3));
                }else if(s3.contains("H") && s2.contains("H")){
                    break;
                }else {
                    hs.put(set, AmendUtils.sciCal((Double.parseDouble(s3)+Double.parseDouble(s2))/2,0));
                }
            }
            if (hs.size()==6){
                hnMap1.put(string,hs);
            }

        }

        //获取今年的因子
        Set<String> strings1 = rsMap2.keySet();
        for (String s : strings1) {
            Object o = rsMap2.get(s);
            String s1 = JSON.toJSONString(o);
            Map map = JSON.parseObject(s1, Map.class);
            Object o1 = rsMap4.get(s);
            String s2 = JSON.toJSONString(o1);
            Map map1 = JSON.parseObject(s2, Map.class);
            Set sets = map1.keySet();
            HashMap<Object, Object> hs = new HashMap<>();
            for (Object set : sets) {
                if (set.toString().equals("CO")){
                    String s3 = map1.get(set).toString();
                    String s4 = map.get(set).toString();
                    if (s3.contains("H") && !s4.contains("H")){
                        hs.put(set,Double.parseDouble(s4));
                    }else if(!s3.contains("H") && s4.contains("H")){
                        hs.put(set,Double.parseDouble(s3));
                    }else if(s3.contains("H") && s4.contains("H")){
                        break;
                    }else {
                        hs.put(set, AmendUtils.sciCal((Double.parseDouble(s3)+Double.parseDouble(s4))/2,1));
                    }
                    continue;
                }
                String s3 = map1.get(set).toString();
                String s4 = map.get(set).toString();
                if (s3.contains("H") && !s4.contains("H")){
                    hs.put(set,Double.parseDouble(s4));
                }else if(!s3.contains("H") && s4.contains("H")){
                    hs.put(set,Double.parseDouble(s3));
                }else if(s3.contains("H") && s4.contains("H")){
                    break;
                }else {
                    hs.put(set, AmendUtils.sciCal((Double.parseDouble(s3)+Double.parseDouble(s4))/2,0));
                }

            }
            if (hs.size()==6){
                hnMap2.put(s,hs);
            }

        }


        //计算今年的aqi
        getHnMap(hnMap2);
        //计算去年的aqi
        getHnMap(hnMap1);

        //计算本年的pm2.5
        Map<String, List<Integer>> map = getMap(hnMap2,time);
        //计算去年的pm2.5
        Map<String, List<Integer>> map2 = getResult(hnMap1, time);
        List<Integer> list = map.get("count");
        list.add(map2.get("count").get(0));
        List<Integer> list1 = map.get("PM2_5");
        list1.add(map2.get("PM2_5").get(0));
        List<Integer> list2 = map.get("PM10");
        list2.add(map2.get("PM10").get(0));


        //获取今年本月的污染天数，和PM2.5
        List<Integer> month = getMonth1(time, hnMap2);
        int month2 = getMonth2(time, hnMap1);
        month.add(month2);
        map.put("month",month);
        return map;

    }


    /**
     * 获取去年本月的因子均值
     * @param time
     * @param hnMap1
     * @return
     */
    private int getMonth2(String time, HashMap<String, Object> hnMap1) {
        String years = time.substring(0, 4);
        int s = Integer.parseInt(years) - 1;
        String month = time.substring(5, 7);
        String day = time.substring(8, 10);
        ArrayList<Double> list2 = new ArrayList<>();
        for (int i = 1; i <= Integer.parseInt(day); i++) {
            if (i < 10) {
                Object o = hnMap1.get(s + "-" + month + "-0" + i);
                String s1 = JSON.toJSONString(o);
                if (s1.equals("null")){
                    list2.add(0.0);
                    continue;
                }
                Map map1 = JSON.parseObject(s1, Map.class);
                String pm2_5 = map1.get("PM2_5").toString();
                list2.add(Double.parseDouble(pm2_5));
            } else {
                Object o = hnMap1.get(s + "-" + month + "-" + i);
                String s1 = JSON.toJSONString(o);
                if (s1.equals("null")){
                    list2.add(0.0);
                    continue;
                }
                Map map1 = JSON.parseObject(s1, Map.class);
                String pm2_5 = map1.get("PM2_5").toString();
                list2.add(Double.parseDouble(pm2_5));
            }
        }
        int rsMonth = (int) AmendUtils.sciCal(list2.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        return rsMonth;
    }

    /**
     * 获取本年本月的因子的平均值
     * @param time
     * @param hnMap2
     * @return
     */
    private List<Integer> getMonth1(String time, HashMap<String, Object> hnMap2) {
        String years = time.substring(0, 4);
        String month = time.substring(5, 7);
        String day = time.substring(8, 10);
        ArrayList<Double> list2 = new ArrayList<>();
        ArrayList<Double> aqiList = new ArrayList<>();
        for (int i = 1; i <=Integer.parseInt(day) ; i++) {
            if (i<10){
                Object o = hnMap2.get(years + "-" + month + "-0" + i);
                String s1 = JSON.toJSONString(o);
                Map map1 = JSON.parseObject(s1, Map.class);
                String pm2_5 = map1.get("PM2_5").toString();
                String aqi = map1.get("AQI").toString();
                aqiList.add(Double.parseDouble(aqi));
                list2.add(Double.parseDouble(pm2_5));
            }else {
                Object o = hnMap2.get(years + "-" + month + "-" + i);
                String s1 = JSON.toJSONString(o);
                Map map1 = JSON.parseObject(s1, Map.class);
                String pm2_5 = map1.get("PM2_5").toString();
                String aqi = map1.get("AQI").toString();
                aqiList.add(Double.parseDouble(aqi));
                list2.add(Double.parseDouble(pm2_5));
            }

        }
        int rsMonth = (int) AmendUtils.sciCal(list2.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 0);
        List<Integer> code = getCode(aqiList);
        code.add(rsMonth);
        return code;
    }

    private void getHnMap(HashMap<String, Object> hnMap2) {
        Set<String> strings4 = hnMap2.keySet();
        for (String s : strings4) {
            Object o = hnMap2.get(s);
            String s1 = JSON.toJSONString(o);
            Map rs = JSON.parseObject(s1, Map.class);
            Map aqi1 = AqiUtils.getAqi(rs);
            JSONObject jsonObject = JSON.parseObject(s1);
            jsonObject.put("AQI",aqi1.get("AQI"));
            String s2 = JSON.toJSONString(jsonObject);
            Map map = JSON.parseObject(s2, Map.class);
            hnMap2.put(s,map);
        }
    }


}