package com.yeejoin.amos.boot.module.hygf.biz.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.map.MapBuilder;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.yeejoin.amos.boot.biz.common.utils.DateUtils;
import com.yeejoin.amos.boot.module.hygf.api.Enum.CommonEnum;
import com.yeejoin.amos.boot.module.hygf.api.Enum.StatisicsHomePageEnum;
import com.yeejoin.amos.boot.module.hygf.api.entity.PeasantHousehold;
import com.yeejoin.amos.boot.module.hygf.api.mapper.JpStationMapper;
import com.yeejoin.amos.boot.module.hygf.api.mapper.StatisticsHomepageMapper;
import org.apache.activemq.util.MapHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;

@Service
public class StatisticsHomepageServiceImpl {

    @Autowired
    StatisticsHomepageMapper statisticsHomepageMapper;
    @Autowired
    JpStationMapper jpStationMapper;


    public Map<String, Object> statisticsHomepage( String developerCode,String regionalCompaniesCode, String province, String city, String district){

        Map<String, Object> resultMap = MapBuilder.<String, Object>create().build();
        Map<String, Object> map = MapBuilder.<String, Object>create().build();
        List<Map<String, Object>> surveyMaps = statisticsHomepageMapper.surveyStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
       if (!CollectionUtil.isEmpty(surveyMaps)){
           surveyMaps.get(0).put("未勘察",statisticsHomepageMapper.selectWKC(developerCode, regionalCompaniesCode, province, city, district));
       }
        this.resuleMapBuild(map,resultMap,surveyMaps,"surey");
        List<Map<String, Object>> desionMaps = statisticsHomepageMapper.desionTotal(developerCode, regionalCompaniesCode, province, city, district);
        this.resuleMapBuild(map,resultMap,desionMaps,"desion");
        List<Map<String, Object>> contractMaps = statisticsHomepageMapper.contractStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
        this.resuleMapBuild(map,resultMap,contractMaps,"contract");
        List<Map<String, Object>> preparationMoneyMaps = statisticsHomepageMapper.preparationMoneyTotal(developerCode, regionalCompaniesCode, province, city, district);
        this.resuleMapBuild(map,resultMap,preparationMoneyMaps,"preparationMoney");
        List<Map<String, Object>> workOrderMaps = statisticsHomepageMapper.workOrderTotal(developerCode, regionalCompaniesCode, province, city, district);
        this.resuleMapBuild(map,resultMap,workOrderMaps,"workOrder");
        List<Map<String, Object>> gridMaps = statisticsHomepageMapper.gridStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
        this.resuleMapBuild(map,resultMap,gridMaps,"grid");
        List<Map<String, Object>> acceptanceMaps = statisticsHomepageMapper.acceptanceStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
        this.resuleMapBuild(map,resultMap,acceptanceMaps,"acceptance");
        List<Map<String, Object>> financingMaps = statisticsHomepageMapper.financingStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
        this.resuleMapBuild(map,resultMap,financingMaps,"financing");
        List<String> regionNodeByCode = new ArrayList<>();
        if (StringUtils.isNotEmpty(province)){
             regionNodeByCode = jpStationMapper.getRegionNodeByCode(province);
        }
        if (StringUtils.isNotEmpty(city)){
             regionNodeByCode = jpStationMapper.getRegionNodeByCode(province);
        }
        if (StringUtils.isNotEmpty(district)){
            regionNodeByCode = jpStationMapper.getRegionNodeByCode(province);
        }
        List<Map<String, Object>> inverterMaps = statisticsHomepageMapper.inverterTotal(developerCode, regionalCompaniesCode, province, city, district,regionNodeByCode);
        List<Map<String, Object>> inverterJpMaps = statisticsHomepageMapper.inverterTotalJp(developerCode, regionalCompaniesCode, province, city, district,regionNodeByCode);
        inverterMaps.addAll(inverterJpMaps);
        this.resuleMapBuild(map,resultMap,inverterMaps,"inverter");
        map.put("stationNum",statisticsHomepageMapper.stationNum(developerCode, regionalCompaniesCode, province, city, district).get("total"));
        resultMap.put("above",map);
        return resultMap;
    }
 
    Map<String,Object> resuleMapBuild(Map<String, Object> li, Map<String, Object> resultMap,List<Map<String, Object>> maps,String type){
        long total= 0;
        long trtotal= 0;
        double trrealScale = 0.00;
        double realScale = 0.00;
        long sgtotal= 0;
        double sgrealScale = 0.00;
        long sureyNum = 0;
        long workNum = 0;
        long preparationNum = 0;
        long gridNum = 0;
        long acceptanceNum = 0;
        long financingNum = 0;
        double stationRealScale = 0.00;
        double disbursementMoney = 0.00;

        LambdaQueryWrapper<PeasantHousehold> wrapper = new LambdaQueryWrapper<>();



        List<String> surveKeys = new ArrayList<>(Arrays.asList("未通过","未勘察","已完成(法务审核)","设计待审核","投融待审核","法务待审核"));
        List<String> contractKeys = new ArrayList<>(Arrays.asList("双方已签署","农户已签署","未签署","已作废"));
        List<String> workOrder = new ArrayList<>(Arrays.asList("未通过","施工资料上传","完工登记","经销商工程待审核","片区运营待审核","工程待审核","设计待审核","已完成","整改待审核" ,"待整改"));
        List<String> preparationMoneyKeys = new ArrayList<>(Arrays.asList("待发货","已收货","待收货"));
        List<String> desionKeys = new ArrayList<>(Arrays.asList("施工图已上传","典设图已上传"));
        List<String> gridKeys = new ArrayList<>(Arrays.asList("待登记","经销商管理员待审核","片区运营待审核","设计待审核/工程待审核","设计待审核","工程待审核","已完成","未通过","整改待审核" ,"待整改"));
        List<String> acceptanceKeys = new ArrayList<>(Arrays.asList("待提交","经销商管理员待审核","片区运营待审核","投融待审核/法务待审核","投融待审核","法务待审核","资产待审核","已完成","整改待审核","待整改","未通过"));
        List<String> financingKeys = new ArrayList<>(Arrays.asList("未通过","已放款","放款完成","已推送","待推送","待放款"));
        List<Map<String, Object>> list = new ArrayList<>();
        switch(type){
            case "surey" :
                resultMap.put("sureyComplete",0);

                if (Objects.isNull(maps)|| Objects.isNull(maps.get(0))){
                     list =    this.nullDataBuildResult(list,surveKeys);
                }else {
                    Map<String, Object> surey = maps.get(0);
                    surveKeys.removeAll(surey.keySet());
                    for (String e : surey.keySet()) {
                        if (!e.equals("completeTotal") && !e.equals("completeTotalrl") ){
                            list.add( MapBuilder.<String, Object>create().put("statusText",e).put("total",surey.get(e)).build());
                        }
                        if (!e.equals("未勘察") && !e.equals("completeTotalrl") ){
                            sureyNum += new BigDecimal(surey.get(e).toString()).longValue();
                        }
                    }
                    list =    this.nullDataBuildResult(list,surveKeys);
                    resultMap.put("sureyComplete",surey.containsKey("completeTotal")?surey.get("completeTotal"):0);
                    resultMap.put("completeTotalrl",surey.containsKey("completeTotalrl")?surey.get("completeTotalrl"):0);
                }

                    li.put("surveNum",sureyNum);
                    resultMap.put("surey",list);

                break;
            case "desion":
                if (Objects.isNull(maps)|| Objects.isNull(maps.get(0))){
                    list =    this.nullDataBuildResult(list,desionKeys);
                }else {
                    Map<String, Object> desion = maps.get(0);
                    long surveNum = (long) li.get("surveNum");
                    desionKeys.removeAll(desion.keySet());
                    for (String e : desion.keySet()) {
                        list.add( MapBuilder.<String, Object>create().put("statusText",e).put("total",desion.get(e)).build());
                        surveNum += new BigDecimal(desion.get(e).toString()).longValue();
                    }
                    li.put("surveNum",surveNum);
                    list =    this.nullDataBuildResult(list,desionKeys);
                    if (resultMap.containsKey("sureyComplete")){
                        list.add( MapBuilder.<String, Object>create().put("statusText","审核完成").put("total",resultMap.get("sureyComplete")).put("realScale",resultMap.get("completeTotalrl")).build());
                        resultMap.remove("sureyComplete");
                        resultMap.remove("completeTotalrl");
                    }else {
                        list.add( MapBuilder.<String, Object>create().put("statusText","审核完成").put("total",0).build());
                    }
                }

                resultMap.put("desion",list);
                break;
            case "contract":
                if (Objects.isNull(maps) || Objects.isNull(maps.get(0)) ){
                    list =    this.nullDataBuildResult(list,contractKeys);
                }else {
                    Map<String, Object> contract = maps.get(0);
                    contractKeys.removeAll(contract.keySet());
                    for (String e : contract.keySet()) {
                        if (!e.equals("sfyqsrl") && !e.equals("双方已签署")){
                            list.add(MapBuilder.<String, Object>create().put("statusText", e).put("total", contract.get(e)).build());
                        }
                    }
                    list.add(MapBuilder.<String, Object>create().put("statusText","双方已签署" ).put("total", contract.get("双方已签署")).put("realScale",contract.get("sfyqsrl")).build());
                    list =    this.nullDataBuildResult(list,contractKeys);
                }
                resultMap.put("contract",list);
                break;
            case "preparationMoney":
                if (Objects.isNull(maps)|| Objects.isNull(maps.get(0))){
                    list =    this.nullDataBuildResult(list,preparationMoneyKeys);
                }else {
                    Map<String, Object> preparationMoney = maps.get(0);
                    preparationMoneyKeys.removeAll(preparationMoney.keySet());
                    for (String e : preparationMoney.keySet()) {
                        if (!e.equals("已收货") &&!e.equals("yshrl") && !e.equals("备货完成") &&!e.equals("bhwcrl")){
                            list.add(MapBuilder.<String, Object>create().put("statusText", e).put("total", preparationMoney.get(e)).build());
                            preparationNum += new BigDecimal(preparationMoney.get(e).toString()).longValue();
                        }
                        if (e.equals("已收货") || e.equals("备货完成")){
                            preparationNum += new BigDecimal(preparationMoney.get(e).toString()).longValue();
                        }
                    }
                    list.add(MapBuilder.<String, Object>create().put("statusText", "已收货").put("total", preparationMoney.get("已收货")).put("realScale", preparationMoney.get("yshrl")).build());
                    list.add(MapBuilder.<String, Object>create().put("statusText", "备货完成").put("total", preparationMoney.get("备货完成")).put("realScale", preparationMoney.get("bhwcrl")).build());
                    list =    this.nullDataBuildResult(list,preparationMoneyKeys);
                }
                resultMap.put("preparationMoney",list);
                li.put("preparationNum",preparationNum);
                break;
            case "workOrder":
                if (CollectionUtil.isEmpty(maps)|| Objects.isNull(maps.get(0))){
                    list =    this.nullDataBuildResult(list,workOrder);
                    resultMap.put("workOrder",list);
                }else {
                    Iterator<Map<String, Object>> iterator = maps.iterator();
                    while (iterator.hasNext()) {
                        Map<String, Object> map = iterator.next();
                        workNum += (long) map.get("total");
                        BigDecimal realScale1 = new BigDecimal(String.valueOf(map.get("realScale")));
                        stationRealScale += realScale1.doubleValue();
                        if ("施工资料上传".equals(map.get("statusText"))) {
                            sgtotal += (long) map.get("total");
                            sgrealScale +=  new BigDecimal(String.valueOf(map.get("realScale"))).doubleValue();
                            iterator.remove();
                        }
                    }
                    maps.add(MapBuilder.<String, Object>create().put("statusText","施工资料上传").put("total",sgtotal).put("realScale",new BigDecimal(sgrealScale).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()).build());
                    differenceValue(maps,workOrder);
                    resultMap.put("workOrder",maps);
                }
                li.put("workNum",workNum);
                li.put("stationRealScale",new BigDecimal(stationRealScale/1000).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());

                break;
            case "grid":
                if (CollectionUtil.isEmpty(maps)|| Objects.isNull(maps.get(0))){
                    list =    this.nullDataBuildResult(list,gridKeys);
                    resultMap.put("grid",list);
                }else {
                    Iterator<Map<String, Object>> iterator1 = maps.iterator();
                    while (iterator1.hasNext()) {
                        Map<String, Object> map = iterator1.next();
                        gridNum += (long) map.get("total");
                        if ("审核中".equals(map.get("statusText"))) {
                            total += (long) map.get("total");
                            realScale +=  new BigDecimal(String.valueOf(map.get("realScale"))).doubleValue();
                            iterator1.remove();
                        }
                    }
                   // maps.add(MapBuilder.<String, Object>create().put("statusText", "审核中").put("total", total).put("realScale",new BigDecimal(realScale).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()).build());
                    differenceValue(maps,gridKeys);
                    resultMap.put("grid",maps);
                }
                li.put("gridNum",gridNum);
                break;
            case "acceptance":
                if (CollectionUtil.isEmpty(maps)|| Objects.isNull(maps.get(0))){
                    list =    this.nullDataBuildResult(list,acceptanceKeys);
                    resultMap.put("acceptance",list);
                }else {
                    Iterator<Map<String, Object>> iterator2 = maps.iterator();
                    while (iterator2.hasNext()) {
                        Map<String, Object> map = iterator2.next();
                        acceptanceNum += (long) map.get("total");
                        if ("审核中".equals(map.get("statusText"))) {
                            total += (long) map.get("total");
                            realScale += new BigDecimal(String.valueOf(map.get("realScale"))).doubleValue();
                            iterator2.remove();
                        }
                    }
                    //maps.add(MapBuilder.<String, Object>create().put("statusText","审核中").put("total",total).put("realScale",new BigDecimal(realScale).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()).build());
                    differenceValue(maps,acceptanceKeys);
                    resultMap.put("acceptance",maps);
                }
                li.put("acceptanceNum",acceptanceNum);
                break;
            case "financing":

                if (CollectionUtil.isEmpty(maps)|| Objects.isNull(maps.get(0))){
                    list =    this.nullDataBuildResult(list,financingKeys);
                    resultMap.put("financing",list);
                }else {
                    Iterator<Map<String, Object>> iterator2 = maps.iterator();
                    while (iterator2.hasNext()) {
                        Map<String, Object> map = iterator2.next();
                        financingNum += (long) map.get("total");
                        if ("放款完成".equals(map.get("statusText"))) {
                            disbursementMoney +=  new BigDecimal(String.valueOf(map.get("disbursementMoney"))).doubleValue();
                        }
                        if ("未通过".equals(map.get("statusText"))) {
                            trtotal += (long) map.get("total");
                            trrealScale += new BigDecimal(String.valueOf(map.get("realScale"))).doubleValue();
                            iterator2.remove();
                        }
                    }
                    maps.add(MapBuilder.<String, Object>create().put("statusText","未通过").put("total",trtotal).put("realScale",new BigDecimal(trrealScale).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()).build());
                    differenceValue(maps,financingKeys);
                    resultMap.put("financing",maps);
                }
                li.put("financingNum",financingNum);
                li.put("disbursementMoney",new BigDecimal(disbursementMoney/10000).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
                break;
            default:
                resultMap.put("inverter",maps);
                break;
        }
        return resultMap;
    }

    List<Map<String, Object>>  nullDataBuildResult(List<Map<String, Object>> list,List<String> Keys){
        if (Keys.size()< 1){
            return list ;
        }
        for (String key : Keys) {
            if (key.equals("workOrder") ||key.equals("grid")||key.equals("acceptance")||key.equals("financing")||key.equals("inverter")){
                list.add( MapBuilder.<String, Object>create().put("statusText",key).put("total",0).put("realScale",0).build());
            }else {
                list.add( MapBuilder.<String, Object>create().put("statusText",key).put("total",0).build());
            }
        }
        return list;
    }

    List<Map<String, Object>>  differenceValue (List<Map<String, Object>> list,List<String> Keys) {
        List<String> objects = new ArrayList<>();
        list.forEach(e->{
            objects.add(e.get("statusText").toString());
        });
        Keys.removeAll(objects);
        this.nullDataBuildResult(list,Keys);
        return list;
    }

   public void homePageExport(String developerCode, String regionalCompaniesCode, String province, String city, String district, HttpServletResponse response) throws IOException {
        //获取电站数据
        List<Map<String, Object>> maps = statisticsHomepageMapper.homePageExport(developerCode, regionalCompaniesCode, province, city, district);
        //获取区域公司对应的经销商数量 用来合并表格
        List<Map<String, Object>> merCountMap = statisticsHomepageMapper.merCount(developerCode, regionalCompaniesCode, province, city, district);
        String templatePath = "templates/homePageExport.xlsx";
        Workbook workbook = null;
        try (  InputStream fis = new ClassPathResource(templatePath).getInputStream()) {
            workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            fillSheet(firstSheet, maps);
            int startNum = 4;
            int replenishStartNum = 4+maps.size();
            //根据区域公司的省市区进行筛选
            List<String> regionNodeByCode = new ArrayList<>();
            if (StringUtils.isNotEmpty(province)){
                regionNodeByCode = jpStationMapper.getRegionNodeByCode(province);
            }
            if (StringUtils.isNotEmpty(city)){
                regionNodeByCode = jpStationMapper.getRegionNodeByCode(province);
            }
            if (StringUtils.isNotEmpty(district)){
                regionNodeByCode = jpStationMapper.getRegionNodeByCode(province);
            }
            //查询区域公司的还款计划
            List<Map<String, Object>> rent = statisticsHomepageMapper.getRent(regionalCompaniesCode,regionNodeByCode);
            for (Map<String, Object> map : merCountMap) {
                //此处逻辑针对需要合并表格的数据
                if (!map.get("total").toString().equals("1")){
                    int total = Integer.valueOf(map.get("total").toString()) -1 ;
                    int endNum = startNum+total;
                    String regionName = statisticsHomepageMapper.selectProvince(map.get("regionSeq").toString());
                    if ( firstSheet.getRow(startNum) != null){
                        Cell cell = firstSheet.getRow(startNum).createCell(1);
                        cell.setCellValue(regionName);
                        applyBorder(cell);
                        boolean flag = true;
                        //判断是不是区域公司存在还款计划 若不存在则给0
                        for (Map<String, Object> stringObjectMap : rent) {
                            if (stringObjectMap.get("regionalCompaniesCode").equals(map.get("regionalCompaniesCode"))){
                                Cell cell3 = firstSheet.getRow(startNum).createCell(43);
                                cell3.setCellValue(stringObjectMap.get("yhk").toString());
                                applyBorder(cell3);
                                Cell cell1 = firstSheet.getRow(startNum).createCell(44);
                                cell1.setCellValue(stringObjectMap.get("jnwhk").toString());
                                applyBorder(cell1);
                                Cell cell2 = firstSheet.getRow(startNum).createCell(45);
                                cell2.setCellValue(stringObjectMap.get("sywhk").toString());
                                applyBorder(cell2);

                                flag = false;
                            }
                        }

                        if (flag){
                            Cell cell3 = firstSheet.getRow(startNum).createCell(43);
                            cell3.setCellValue(0);
                            applyBorder(cell3);
                            Cell cell1 = firstSheet.getRow(startNum).createCell(44);
                            cell1.setCellValue(0);
                            applyBorder(cell1);
                            Cell cell2 = firstSheet.getRow(startNum).createCell(45);
                            cell2.setCellValue(0);
                            applyBorder(cell2);
                        }
                        //合并表格及设置样式
                        CellRangeAddress cellRangeAddress2 = new CellRangeAddress(startNum, endNum, 1, 1);
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(startNum, endNum, 2, 2);
                        CellRangeAddress cellRangeAddress1 = new CellRangeAddress(startNum, endNum, 38, 38);
                        CellRangeAddress cellRangeAddress3 = new CellRangeAddress(startNum, endNum, 43, 43);
                        CellRangeAddress cellRangeAddress4 = new CellRangeAddress(startNum, endNum, 44, 44);
                        CellRangeAddress cellRangeAddress5 = new CellRangeAddress(startNum, endNum, 45, 45);
                        firstSheet.addMergedRegion(cellRangeAddress);
                        firstSheet.addMergedRegion(cellRangeAddress1);
                        firstSheet.addMergedRegion(cellRangeAddress2);
                        firstSheet.addMergedRegion(cellRangeAddress3);
                        firstSheet.addMergedRegion(cellRangeAddress4);
                        firstSheet.addMergedRegion(cellRangeAddress5);



                        applyBorderToMergedRegion(firstSheet,cellRangeAddress2);
                        applyBorderToMergedRegion(firstSheet,cellRangeAddress );
                        applyBorderToMergedRegion(firstSheet,cellRangeAddress1);
                        applyBorderToMergedRegion(firstSheet,cellRangeAddress3);
                        applyBorderToMergedRegion(firstSheet,cellRangeAddress4);
                        applyBorderToMergedRegion(firstSheet,cellRangeAddress5);


                        startNum = endNum +1;
                    }
                }else {
                    String regionName = statisticsHomepageMapper.selectProvince(map.get("regionSeq").toString());
                    if ( firstSheet.getRow(startNum) != null) {
                        Cell cell = firstSheet.getRow(startNum).createCell(1);
                        cell.setCellValue(regionName);
                        applyBorder(cell);
                        boolean flag = true;
                        //判断是不是区域公司存在还款计划 若不存在则给0
                        for (Map<String, Object> stringObjectMap : rent) {
                            if (stringObjectMap.get("regionalCompaniesCode").equals(map.get("regionalCompaniesCode"))){
                                Cell cell3 = firstSheet.getRow(startNum).createCell(43);
                                cell3.setCellValue(stringObjectMap.get("yhk").toString());
                                applyBorder(cell3);
                                Cell cell1 = firstSheet.getRow(startNum).createCell(44);
                                cell1.setCellValue(stringObjectMap.get("jnwhk").toString());
                                applyBorder(cell1);
                                Cell cell2 = firstSheet.getRow(startNum).createCell(45);
                                cell2.setCellValue(stringObjectMap.get("sywhk").toString());
                                applyBorder(cell2);
                                flag = false;
                            }
                        }

                        if (flag){
                            Cell cell3 = firstSheet.getRow(startNum).createCell(43);
                            cell3.setCellValue(0);
                            applyBorder(cell3);
                            Cell cell1 = firstSheet.getRow(startNum).createCell(44);
                            cell1.setCellValue(0);
                            applyBorder(cell1);
                            Cell cell2 = firstSheet.getRow(startNum).createCell(45);
                            cell2.setCellValue(0);
                            applyBorder(cell2);
                        }
                        startNum +=1;
                    }

                }
            }
            //导出主体是电站，但可能存在部分区域公司不存在电站但存在还款计划 需将此类数据补充进excel
            for (Map<String, Object> map : rent) {
                boolean exist = false;
                for (Map<String, Object> stringObjectMap : merCountMap) {
                    if (stringObjectMap.get("regionalCompaniesCode").equals(map.get("regionalCompaniesCode"))){
                        exist = true;
                        break;
                    }
                }
                if (!exist){
                    Row row = firstSheet.createRow(replenishStartNum);
                    Cell cell = row.createCell(0);
                    cell.setCellValue(maps.size()+1);
                    applyBorder(cell);
                    Cell cell1 = row.createCell(1);
                    String regionName = statisticsHomepageMapper.selectProvince(map.get("regionSeq").toString());
                    cell1.setCellValue(regionName);
                    applyBorder(cell1);
                    Cell cell2 = row.createCell(2);
                    cell2.setCellValue(map.get("regionalCompaniesName").toString());
                    applyBorder(cell2);
                    Cell cell40= row.createCell(43);
                    cell40.setCellValue(map.get("yhk").toString());
                    applyBorder(cell40);
                    Cell cell41 = row.createCell(44);
                    cell41.setCellValue(map.get("jnwhk").toString());
                    applyBorder(cell41);
                    Cell cell42 = row.createCell(45);
                    cell42.setCellValue(map.get("sywhk").toString());
                    applyBorder(cell42);

                    for (int i = 3; i <40; i++) {
                        Cell celli = row.createCell(i);
                        celli.setCellValue("");
                        applyBorder(celli);
                    }

                }
            }
          //  autoSizeColumns(firstSheet);
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("统计数据.xlsx", "UTF-8"));
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            workbook.close();
          }
        }

    private void fillSheet(Sheet sheet, List<Map<String, Object>> data) {
        if (Objects.isNull(data)){
            return;
        }
        int startRow = 4;
        sheet.setDefaultColumnWidth(0);
        Map<String, List<Integer>> merList = new HashMap<>();
        for (int dataIndex = 0; dataIndex < data.size(); dataIndex++) {
            Row dataRow = sheet.createRow(startRow + dataIndex);
            Cell cell = dataRow.createCell(0);
            cell.setCellValue(dataIndex+1);
            applyBorder(cell);

            Map<String, Object> result = (Map<String, Object>) data.get(dataIndex);
            for (String key : result.keySet()) {
                Cell dataCell = dataRow.createCell(StatisicsHomePageEnum.getCodeByName(key));
                dataCell.setCellValue(result.get(key).toString());
                applyBorder(dataCell);
            }
        }
    }

    private void autoSizeColumns(Sheet sheet) {
        for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) {
            if (i<35){
                sheet.autoSizeColumn(i);
            }

        }
    }

    private void applyBorder(Cell cell) {
        Workbook workbook = cell.getSheet().getWorkbook();
        CellStyle style = cell.getCellStyle();
        if (style == null) {
            style = workbook.createCellStyle();
        }
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        cell.setCellStyle(style);
    }




    private void applyBorderToMergedRegion(Sheet sheet, CellRangeAddress mergedRegion) {
        RegionUtil.setBorderBottom(BorderStyle.THIN,mergedRegion,sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN,mergedRegion,sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN,mergedRegion,sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN,mergedRegion,sheet);
    }


    public Map<String, Object> statisticsTop(String type, String developerCode,String regionalCompaniesCode, String province, String city, String district){
        Map<String, Object> resultMap = MapBuilder.<String, Object>create().build();
        Map<String, Object> map = MapBuilder.<String, Object>create().build();
        Map<String, Object> map1 = MapBuilder.<String, Object>create().build();
        switch (type){
             case "survey":
                 Map<String, Object> surveyMaps = statisticsHomepageMapper.statisticsPowerStation(developerCode, regionalCompaniesCode, province, city, district);
                 for (String key : surveyMaps.keySet()) {
                     if (!key.contains("rl")){
                     resultMap.put(key,Arrays.asList(surveyMaps.get(key),"0"));
                     }
                 }
                 resultMap.put("kcywc",Arrays.asList(surveyMaps.get("kcywc"),new BigDecimal((double)surveyMaps.get("kcywcrl")).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()));
                 resultMap.put("sfyqs",Arrays.asList(surveyMaps.get("sfyqs"),new BigDecimal((double)surveyMaps.get("sfyqsrl")).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()));

                 break;
             case "preparationMoney":
                 Map<String, Object> preparationMoney = statisticsHomepageMapper.statisticsPreparationMoney(developerCode, regionalCompaniesCode, province, city, district);
                 for (String key : preparationMoney.keySet()) {
                     if (!key.contains("rl")){
                         resultMap.put(key,Arrays.asList(preparationMoney.get(key),"0"));
                     }
                 }
                 resultMap.put("ysh",Arrays.asList(preparationMoney.get("ysh"),new BigDecimal((double)preparationMoney.get("yshrl")).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()));
                 resultMap.put("bhwc",Arrays.asList(preparationMoney.get("bhwc"),new BigDecimal((double)preparationMoney.get("bhwcrl")).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue()));
                 break;
            case "workOrder":
                List<Map<String, Object>> workOrderMaps = statisticsHomepageMapper.workOrderTotal(developerCode, regionalCompaniesCode, province, city, district);
                this.resuleMapBuild(map,map1,workOrderMaps,"workOrder");
                List<Map<String, Object>> workOrder = (List<Map<String, Object>>) map1.get("workOrder");
                for (Map<String, Object> objectMap : workOrder) {
                    String key = objectMap.get("statusText").toString();
                    resultMap.put(key,Arrays.asList(objectMap.get("total"),objectMap.get("realScale")));
                }
                break;
            case "grid":
                List<Map<String, Object>> grid = statisticsHomepageMapper.gridStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
                this.resuleMapBuild(map,map1,grid,"grid");
                List<Map<String, Object>> gridMaps = (List<Map<String, Object>>) map1.get("grid");
                for (Map<String, Object> objectMap : gridMaps) {
                    String key = objectMap.get("statusText").toString();
                    resultMap.put(key,Arrays.asList(objectMap.get("total"),objectMap.get("realScale")));
                }
                break;
            case "acceptance":
                List<Map<String, Object>> acceptanceMaps = statisticsHomepageMapper.acceptanceStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
                this.resuleMapBuild(map,map1,acceptanceMaps,"acceptance");
                List<Map<String, Object>> acceptance = (List<Map<String, Object>>) map1.get("acceptance");
                for (Map<String, Object> objectMap : acceptance) {
                    String key = objectMap.get("statusText").toString();
                    resultMap.put(key,Arrays.asList(objectMap.get("total"),objectMap.get("realScale")));
                }
                break;
            case "financing":
                List<Map<String, Object>> financingMaps = statisticsHomepageMapper.financingStatusTotal(developerCode, regionalCompaniesCode, province, city, district);
                this.resuleMapBuild(map,map1,financingMaps,"financing");
                List<Map<String, Object>> financing = (List<Map<String, Object>>) map1.get("financing");
                for (Map<String, Object> objectMap : financing) {
                    String key = objectMap.get("statusText").toString();
                    resultMap.put(key,Arrays.asList(objectMap.get("total"),objectMap.get("realScale")));
                }
                break;


         }
         return resultMap;

    }

}
