package com.artfess.cqxy.ledger.manager.impl;

import cn.hutool.json.JSONObject;
import com.artfess.base.query.*;
import com.artfess.base.util.BeanUtils;
import com.artfess.cqxy.contract.manager.AdministrativePaymentManager;
import com.artfess.cqxy.contract.manager.ContractManager;
import com.artfess.cqxy.contract.model.AdministrativePayment;
import com.artfess.cqxy.designEstimate.manager.EstimateManager;
import com.artfess.cqxy.designEstimate.model.Estimate;
import com.artfess.cqxy.feasiblePlan.manager.FeasibilityStudyReplyManager;
import com.artfess.cqxy.feasiblePlan.model.FeasibilityStudyReply;
import com.artfess.cqxy.ledger.manager.InvestmentLedgerManager;
import com.artfess.cqxy.ledger.vo.InvestmentLedgerVo;
import com.artfess.cqxy.ledger.vo.InvestmentMonthCountVo;
import com.artfess.cqxy.processManagermant.manager.ProgressManageManager;
import com.artfess.cqxy.processManagermant.manager.ProgressManageReportManager;
import com.artfess.cqxy.processManagermant.model.ProgressManage;
import com.artfess.cqxy.processManagermant.model.ProgressManageReport;
import com.artfess.cqxy.projectApproval.manager.ProjectEstablishmentManager;
import com.artfess.cqxy.projectManagement.manager.ProjectManagementManager;
import com.artfess.cqxy.projectManagement.model.ProjectManagement;
import com.artfess.cqxy.utils.BizUtils;
import com.artfess.poi.util.ExcelUtil;
import com.artfess.sysConfig.persistence.manager.SysDictionaryManager;
import com.artfess.sysConfig.persistence.param.DictModel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.*;

/**
 * @author Limuhua
 * @date 2022/3/30 14:26
 */
@Service
public class InvestmentLedgerManagerImpl implements InvestmentLedgerManager {

    @Autowired
    private SysDictionaryManager sdm;
    @Autowired
    private ProjectManagementManager pmm;
    @Autowired
    private EstimateManager em;
    @Autowired
    private ProgressManageReportManager pmrm;
    @Autowired
    private ProgressManageManager progressManager;
    @Autowired
    private ContractManager contractManager;
    @Autowired
    private ProjectEstablishmentManager pem;

    @Autowired
    private FeasibilityStudyReplyManager feasibilityStudyReplyManager;

    @Autowired
    private AdministrativePaymentManager administrativePaymentManager;

    @Override
    public Map<String, Object> queryByPage(QueryFilter<ProjectManagement> queryFilter) {
        queryFilter.withSorter(new FieldSort("PROJECT_TYPE_",Direction.ASC));
        queryFilter.addFilter("PARENT_ID_","root", QueryOP.EQUAL);
        PageList<ProjectManagement> project = pmm.queryAllByPage(queryFilter);
        List<ProjectManagement> projectManagementList = null == project ? new ArrayList<>() : project.getRows();
        Map<String, Object> result = new HashMap<>();
        // 获取字典
        List<DictModel> pStatus = sdm.queryDictListItemsByCode("xmgl-xmzt");
        List<InvestmentLedgerVo> data = new ArrayList<>();
        List<AdministrativePayment> administrativePaymentList = administrativePaymentManager.list();
        for (ProjectManagement ele : projectManagementList) {
            InvestmentLedgerVo temp = new InvestmentLedgerVo();
            //项目ID
            temp.setId(ele.getId());
            // 项目名称
            temp.setProjectName(ele.getProjectName());
            // 项目形象进度
            temp.setProjectStatus(BizUtils.getDicValueByCode(pStatus, ele.getProjectStatus()));
            // 业主工程师
            temp.setProjectManager(ele.getProjectManager());
            // 项目类型
            temp.setProjectType(Integer.valueOf(ele.getProjectType()));
            // 土地费用
            temp.setLandPrice(ele.getLandTotalPrice() == null ? BigDecimal.ZERO.stripTrailingZeros() : ele.getLandTotalPrice().setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros());
            /*
            List<Contract> contract = contractManager.list(
                    new QueryWrapper<Contract>().eq("PROJECT_ID_", ele.getId()).eq("IS_DELE_", "0").eq("type_","G1"));
            BigDecimal engineeringCost = BigDecimal.ZERO;
            for(Contract c : contract){
                if(StringUtils.isNotBlank(c.getContractAmount())){
                    engineeringCost = engineeringCost.add(new BigDecimal(c.getContractAmount()));
                }
            }
            temp.setEngineeringCost(engineeringCost.stripTrailingZeros().toPlainString());
             */
            // 建安费用（及工程费用）,土地费用，可研中查询
            FeasibilityStudyReply feasibilityStudyReply = feasibilityStudyReplyManager.getByProjectId(ele.getId());
            // 建安费用（及工程费用）,土地费用，概算批复中查询
            Estimate estimate = em.getOne(new QueryWrapper<Estimate>().eq("PROJECT_ID_", ele.getId())
                    .eq("IS_DELE_", "0").last("limit 1"));

            // 土地费,先从概算批复中获取，如果没有则从可研中获取
            if(null != estimate && StringUtils.isNotBlank(estimate.getDesignSpeed())){
                if(null == temp.getLandPrice() || temp.getLandPrice().compareTo(BigDecimal.ZERO) < 1 ){
                    temp.setLandPrice(new BigDecimal(estimate.getDesignSpeed()).setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros());
                }
//                temp.setLandPrice(temp.getLandPrice().add(new BigDecimal(estimate.getDesignSpeed())));
            }else {
                if(null != feasibilityStudyReply && StringUtils.isNotBlank(feasibilityStudyReply.getLandCost())){
                    if(null == temp.getLandPrice() || temp.getLandPrice().compareTo(BigDecimal.ZERO) < 1 ){
                        temp.setLandPrice(new BigDecimal(feasibilityStudyReply.getLandCost()).setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros());
                    }
//                    temp.setLandPrice(temp.getLandPrice().add(new BigDecimal(feasibilityStudyReply.getLandCost())));
                }
            }
            /*
            // 建安费,首先从概算批复中获取，如果没有则从可研中获取
            if(null != estimate && StringUtils.isNotBlank(estimate.getEngineeringCost())){
                temp.setEngineeringCost(estimate.getEngineeringCost());
            }else {
                if(null != feasibilityStudyReply && StringUtils.isNotBlank(feasibilityStudyReply.getEngineeringCost())){
                    temp.setEngineeringCost(feasibilityStudyReply.getEngineeringCost());
                }
            }
             */
            // 建安费用改为统计工程款
//            BigDecimal engineeringCost = progressManager.geTamountAppropriatedTotalByProjectIdAndPurpose(ele.getId(),true, "1");

            // 建安费用改为项目所属的施工合同总金额
            BigDecimal engineeringCost = contractManager.queryConstructionAmount2(ele.getId());

            if (engineeringCost!=null && engineeringCost.compareTo(BigDecimal.ZERO) >0){
//                BigDecimal bigDecimal = engineeringCost.setScale(2, BigDecimal.ROUND_HALF_UP);
                temp.setEngineeringCost(engineeringCost.setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toPlainString());
            }else {
                temp.setEngineeringCost("0");
            }
            // 安全文明施工费用
            BigDecimal actualSettlementAmount = progressManager.geTamountAppropriatedTotalByProjectIdAndPurpose(ele.getId(),true, "2");
            if (actualSettlementAmount!=null && actualSettlementAmount.compareTo(BigDecimal.ZERO) >0){
                BigDecimal bigDecimal = actualSettlementAmount.divide(new BigDecimal(10000))
                        .stripTrailingZeros().setScale(2, BigDecimal.ROUND_HALF_DOWN);
                temp.setActualSettlementAmount(bigDecimal.toPlainString());
            }else {
                temp.setActualSettlementAmount("0");
            }

            //总投资额：默认是项目管理中的总投资金额，如果没有总投资，就是立项中总投资
            temp.setTotalEstimate(BeanUtils.isEmpty(ele.getProjectMoney()) ?"0":ele.getProjectMoney().setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toPlainString());
            //立项总投资
            String pemMoney = pem.getMoneyByPid(ele.getId());
            BigDecimal tempMoney = new BigDecimal(StringUtils.isNotBlank(pemMoney)?pemMoney:"0");
            if (StringUtils.isEmpty(temp.getTotalEstimate()) && StringUtils.isNotBlank(pemMoney)) {
                temp.setTotalEstimate(tempMoney.setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toPlainString());
            }

            Calendar now = Calendar.getInstance();
            int nowYear = now.get(Calendar.YEAR);
            //当年年度总投资
            BigDecimal estimate1 = em.getEstimate(ele.getId(), String.valueOf(nowYear));
            if(BigDecimal.ZERO.compareTo(estimate1)==0){
                temp.setCurrentEstimate("0");
            }else {
                temp.setCurrentEstimate(estimate1
                        .divide(new BigDecimal(10000),2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toPlainString());
            }
            //去年年度总投资
            BigDecimal estimate2 = em.getEstimate(ele.getId(), String.valueOf(nowYear-1));
            if(BigDecimal.ZERO.compareTo(estimate2)==0){
                temp.setBeforeEstimate("0");
            }else {
                temp.setBeforeEstimate(estimate2
                        .divide(new BigDecimal(10000),2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toPlainString());
            }
            //累计完成总投资
            BigDecimal estimate3 = em.getEstimate(ele.getId(),"");
            if(BigDecimal.ZERO.compareTo(estimate3)==0){
                temp.setCompletedEstimate(BigDecimal.ZERO.add(temp.getLandPrice()).stripTrailingZeros().toPlainString());
            }else {
                temp.setCompletedEstimate(estimate3
                        .divide(new BigDecimal(10000),2,BigDecimal.ROUND_HALF_UP).add(temp.getLandPrice()).stripTrailingZeros().toPlainString());
            }


            // 总投资额（单位：万元）
//            temp.setTotalEstimate(BeanUtils.isEmpty(estimate) ? "" : estimate.getTotalEstimate());
//            temp.setTotalEstimate(ele.getProjectMoney().stripTrailingZeros().toPlainString());
            // 总投资额（单位：万元）,设置为累计总投资金额
            // 从工程支付中查询累计总投资
//            BigDecimal projectOvervie = progressManager.geTamountAppropriatedTotalByProjectId(ele.getId());
//            if (projectOvervie!=null && projectOvervie.compareTo(BigDecimal.ZERO) >0){
//                BigDecimal bigDecimal = projectOvervie.divide(new BigDecimal(10000)).stripTrailingZeros().setScale(2, BigDecimal.ROUND_HALF_DOWN);
//                temp.setTotalEstimate(bigDecimal.add(temp.getLandPrice()).stripTrailingZeros().toPlainString());
//            }else {
//                temp.setTotalEstimate(temp.getLandPrice().toPlainString());
//            }
            //二类费用
            /*
            List<ProgressManage> progress = progressManager.list(new QueryWrapper<ProgressManage>().eq("PROJECT_ID_", ele.getId()).eq("IS_DELE_", "0"));
            BigDecimal twoTypePrice = BigDecimal.ZERO;
            if(null != progress && progress.size() > 0){
                for (ProgressManage p : progress){
                    boolean isTwoType = !"1".equals(p.getPurpose()) && !"2".equals(p.getPurpose());
                    if(null!=p.getPayeeAmountTotal() && isTwoType){
                        twoTypePrice = twoTypePrice.add(p.getPayeeAmountTotal());
                    }
                }
            }
            temp.setTwoTypePrice(twoTypePrice);
              */
            // 二类费用，改为sql查询,查询用途不包括工程费用和安全文明施工费的其他费用
            BigDecimal twoTypePrice = progressManager.getTwoPrice(ele.getId(),"1");
//            BigDecimal twoTypePrice = progressManager.geTamountAppropriatedTotalByProjectIdAndPurpose(ele.getId(),false, "1");
//            BigDecimal twoTypePrice = progressManager.geTamountAppropriatedTotalByProjectIdAndPurpose(ele.getId(),false, "1,2");
            if(null != twoTypePrice && twoTypePrice.compareTo(BigDecimal.ZERO) >0){
                // 转为万元
                BigDecimal bigDecimal = twoTypePrice.divide(new BigDecimal(10000),2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
                temp.setTwoTypePrice(bigDecimal);
            }else {
                temp.setTwoTypePrice(BigDecimal.ZERO);
            }

            String year = null;
            try {
                year = BizUtils.getYearByQueryFields(queryFilter.getQuerys());
            } catch (Exception e) {
                e.printStackTrace();
            }
            QueryWrapper<ProgressManageReport> manageReportQueryWrapper = new QueryWrapper<ProgressManageReport>();
            manageReportQueryWrapper.eq("PROJECT_ID_", ele.getId()).eq("IS_DELE_", "0");
            manageReportQueryWrapper.between(StringUtils.isNotBlank(year),"PORT_DATE_", year+"-01-01", year+"-12-30");
            List<ProgressManageReport> reportList = pmrm.getBaseMapper().selectList(manageReportQueryWrapper);
            List<ProgressManageReport> reports = null == reportList ? new ArrayList<>() : mergeList(reportList);
            BigDecimal currentTotal = new BigDecimal("0");
            List<InvestmentMonthCountVo> monthCountVos = new ArrayList<>();
            for (ProgressManageReport pmr : reports) {
                InvestmentMonthCountVo monthCountVo = new InvestmentMonthCountVo();
                monthCountVo.setMonth(pmr.getPortDate().getMonth());
                if(null != pmr.getProgressDescription() && pmr.getProgressDescription().compareTo(BigDecimal.ZERO) > 0){
                    BigDecimal bigDecimal = pmr.getProgressDescription().divide(new BigDecimal(10000))
                            .setScale(2, BigDecimal.ROUND_HALF_DOWN).stripTrailingZeros();
                    monthCountVo.setCurrentInvestment(bigDecimal);
                }else {
                    monthCountVo.setCurrentInvestment(BigDecimal.ZERO);
                }
                // 形象进度
                String imageProgress = pmr.getWeeklyMouthlyWork();
//                String imageProgress = pmr.getImageProgress();
//                if(StringUtils.isNotBlank(imageProgress) && imageProgress.indexOf("%") == -1){
//                     imageProgress = imageProgress + "%";
//                }
//                monthCountVo.setProgress(imageProgress);
                monthCountVo.setProgress(imageProgress);
//                currentTotal = currentTotal.add(null ==  pmr.getProgressDescription() ? new BigDecimal("0") : pmr.getProgressDescription());
//                monthCountVo.setTotalInvestment(currentTotal);
                // 累计总投资转为万元
                if(null != pmr.getProjectOvervie()){
                    BigDecimal bigDecimal = pmr.getProjectOvervie().divide(new BigDecimal(10000)).stripTrailingZeros()
                            .setScale(2, BigDecimal.ROUND_HALF_DOWN).stripTrailingZeros();
                    monthCountVo.setTotalInvestment(bigDecimal);
                }
                monthCountVos.add(monthCountVo);
            }
            temp.setMonthCount(monthCountVos);

            // 行政缴费总额
            BigDecimal payAnoumt = new BigDecimal(0);
            for (AdministrativePayment pay : administrativePaymentList) {
                if (ele.getId().equals(pay.getProjectId())){
                    payAnoumt = payAnoumt.add(pay.getPaysValue());
                }
            }
            temp.setAdminPaymentAmount(payAnoumt);

            data.add(temp);
        }
        // 封装结果
        result.put("data", data);
        result.put("total", project.getTotal());
        result.put("page", project.getPage());
        result.put("pageSize", project.getPageSize());
        return result;
    }

    /**
     * 合并集合中name重复的数据
     *
     * @param list list
     * @return list
     */
    private List<ProgressManageReport> mergeList(List<ProgressManageReport> list) {
        HashMap<String, ProgressManageReport> map = new HashMap<>();
        for (ProgressManageReport ele : list) {
            String key = String.valueOf(null==ele.getPortDate()?"":ele.getPortDate().getMonth());
            if(StringUtils.isEmpty(key)){
                continue;
            }
            if (map.containsKey(String.valueOf(ele.getPortDate().getMonth()))) {
                BigDecimal mpd = null == map.get(key).getProgressDescription() ? new BigDecimal("0") : map.get(key).getProgressDescription();
                BigDecimal epd = null == ele.getProgressDescription() ? new BigDecimal("0") : ele.getProgressDescription();
                ele.setProgressDescription(mpd.add(epd));

                Double mip = null == map.get(key).getImageProgress() ? 0.00 : Double.valueOf(map.get(key).getImageProgress().replace("%",""));
                Double eip = null == ele.getImageProgress() ? 0.00 : Double.valueOf(ele.getImageProgress().replace("%",""));
                ele.setImageProgress(String.valueOf(mip > eip ? mip : eip));
                ele.setPortDate(ele.getPortDate());
            }
            map.put(key, ele);
        }
        list.clear();
        list.addAll(map.values());
        return list;
    }


    @Override
    public void exportDataToExcel(QueryFilter<ProjectManagement> queryFilter, HttpServletResponse response) throws Exception {
        // 获取字典
        List<DictModel> pType = sdm.queryDictListItemsByCode("xmgl-xmlx");
        String type = "";

        List<QueryField> queryFieldList = queryFilter.getQuerys();
        // 获取导出年份拼接表头
        String year = BizUtils.getYearByQueryFields(queryFieldList);
        if(StringUtils.isEmpty(year)){
            year = String.valueOf(Calendar.YEAR);
        }
        // 拼接导出年份到文件名
        String fileName = StringUtils.isEmpty(year) ? year + "年度项目投资进度台账" : "项目投资进度台账";

        // 创建excel工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建excel表
        HSSFSheet sheet = workbook.createSheet(fileName);
        // 创建样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //设置字体
        HSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        cellStyle.setFont(font);

        // 创建数据样式
        HSSFCellStyle dataCellStyle = workbook.createCellStyle();


        // 处理表头
        // 第一行
        HSSFRow titleRow = sheet.createRow(0);
        handelCell(workbook, cellStyle, fileName, titleRow.createCell(0), fileName, true);
        // 标题行
        // 拼接标题行数据
        List<String> heards = new ArrayList<>(50);
        List<String> subHeards = new ArrayList<>(50);
        int index = 12;
        int endClo = 49;
        int dataCloIndex = 15;
        for (int i = 0; i < 12; i++) {
            if (i == 0) {
                heards.addAll(Arrays.asList(new String[]{"序号", "项目名称", "业主工程师", "形象进度", "总投资（万元）","累计完成总投资",(Integer.valueOf(year)-1)+"年投资",year+"年投资", "建安费（万元）","土地费用（万元）","地勘、设计费，监理、检测费及人防配套费等二类费用（万元）","行政缴费总额"}));
                subHeards.addAll(Arrays.asList(new String[]{" ", " ", " ", " ", " ", " ", " ", " "," "," "," "," "}));
                heards.addAll(Arrays.asList(new String[]{i + 1 + "月", " ", " "}));
            } else if (i == 11) {
                heards.addAll(Arrays.asList(new String[]{i + 1 + "月", " ", " ", "年度累计", " "}));
                subHeards.addAll(Arrays.asList(new String[]{"本月投资（万元）", "本月进度", "累计工程投资", "本月投资（万元）", "本月进度", "累计工程投资", "总投资额", "备注"}));
            } else {
                heards.addAll(Arrays.asList(new String[]{i + 1 + "月", " ", " "}));
                subHeards.addAll(Arrays.asList(new String[]{"本月投资（万元）", "本月进度", "累计工程投资"}));
            }
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index, index + 2));
            index += 3;
        }
        // 第二行
        HSSFRow titleRow1 = sheet.createRow(1);
        for (int i = 0; i <= endClo; i++) {
            //
            handelCell(workbook, dataCellStyle, fileName, titleRow1.createCell(i), " ");
            if (i == 0) {
                //
                handelCell(workbook, dataCellStyle, fileName, titleRow1.createCell(i), "填写部室：");
            }
        }
        // 创建标题行并写入数据
        HSSFRow titleRow2 = sheet.createRow(2);
        for (int i = 0; i < heards.size(); i++) {
            handelCell(workbook, cellStyle, fileName, titleRow2.createCell(i), heards.get(i));
        }
        HSSFRow titleRow3 = sheet.createRow(3);
        for (int i = 0; i < subHeards.size(); i++) {
            handelCell(workbook, cellStyle, fileName, titleRow3.createCell(i), subHeards.get(i));
        }
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, endClo));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, endClo - 1, endClo));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
        for (int i = 0; i < 11; i++) {
            sheet.addMergedRegion(new CellRangeAddress(2, 3, i, i));
        }

        // 获取数据
        queryFilter.withSorter(new FieldSort("PROJECT_TYPE_"));
        List<InvestmentLedgerVo> data = (List<InvestmentLedgerVo>) queryByPage(queryFilter).get("data");
        if(data.isEmpty()){
            throw new RuntimeException("没有可导出的数据！");
        }
        // 数据行,从4行开始,共data.size行
        int row = 4;
        // 当前类型标记
        int currentType = 0;
        // 计数器，当前分类下的序号标记
        int counter = 1;

        //设置金额显示格式
        NumberFormat nf = new DecimalFormat(",###.##");

        // 处理第一条数据就漏分类的情况，即不从一开始就会丢失一到第一条数据的类别统计
        for (int j = 1; j < data.get(0).getProjectType(); j++) {
            HSSFRow addRow = sheet.createRow(sheet.getLastRowNum() + 1);
            type = BizUtils.getDicValueByCode(pType, j);
            handleSubTitle(j, cellStyle, fileName, endClo, type, workbook, addRow);
            row++;
        }

        // 处理数据
        for (int i = 0; i < data.size(); i++) {
            HSSFRow listRow = sheet.createRow(row);// 创建行
            if (currentType != data.get(i).getProjectType()) {
                // 当前项目类型标记 不同于 循环中项目的项目类型时，需要为其分类
                // 更新当前项目类型标记
                currentType = data.get(i).getProjectType();
                // 处理子标题行
                type = BizUtils.getDicValueByCode(pType, data.get(i).getProjectType());
                handleSubTitle(currentType, cellStyle, fileName, endClo, type, workbook, listRow);
                // 重置子标题下的序号计数器
                counter = 1;
                // 撤销当前循环
                i--;
            } else if (data.get(i).getProjectType() - currentType > 1) {
                // 处理断层情况，当中间数据的项目类型丢失的情况，即上一类为1，当前类为3，那就要补上中间漏掉的类2
                for (int j = currentType++; j < data.get(i).getProjectType(); j = currentType++) {
                    HSSFRow addRow = sheet.createRow(sheet.getLastRowNum() + 1);
                    type = BizUtils.getDicValueByCode(pType, data.get(0).getProjectType());
                    handleSubTitle(j, cellStyle, fileName, endClo, type, workbook, addRow);
                }
            } else {
                //
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(0), String.valueOf(counter));
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(1), data.get(i).getProjectName());
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(2), data.get(i).getProjectManager());
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(3), data.get(i).getProjectStatus());
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(4), nf.format(new BigDecimal(data.get(i).getTotalEstimate())));

                //累计完成总投资
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(5), nf.format(new BigDecimal(data.get(i).getCompletedEstimate())));
                //去年投资
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(6), nf.format(new BigDecimal(data.get(i).getBeforeEstimate())));
                //今年投资
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(7), nf.format(new BigDecimal(data.get(i).getCurrentEstimate())));

                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(8), nf.format(new BigDecimal(data.get(i).getEngineeringCost())));
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(9), nf.format(data.get(i).getLandPrice()));
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(10), nf.format(data.get(i).getTwoTypePrice()));
                handelCell(workbook, dataCellStyle, fileName, listRow.createCell(11), nf.format(data.get(i).getAdminPaymentAmount()));

                //

                for (int t = 12; t <= endClo; t++) {
                    handelCell(workbook, cellStyle, fileName, listRow.createCell(t), "");
                }

                List<InvestmentMonthCountVo> monthCount = null == data.get(i).getMonthCount() ? new ArrayList<>() : data.get(i).getMonthCount();
                for (InvestmentMonthCountVo ele : monthCount) {
                    handelCell(workbook, dataCellStyle, fileName, listRow.createCell(dataCloIndex + ele.getMonth() * 3 - 3), nf.format(((BeanUtils.isNotEmpty(ele.getCurrentInvestment())?ele.getCurrentInvestment():BigDecimal.ZERO)).setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros()));
                    handelCell(workbook, dataCellStyle, fileName, listRow.createCell(dataCloIndex + ele.getMonth() * 3 - 2), (BeanUtils.isNotEmpty(ele.getProgress()))?ele.getProgress():"");
                    handelCell(workbook, dataCellStyle, fileName, listRow.createCell(dataCloIndex + ele.getMonth() * 3 - 1), nf.format(((BeanUtils.isNotEmpty(ele.getTotalInvestment()))?ele.getTotalInvestment():BigDecimal.ZERO).setScale(2,BigDecimal.ROUND_HALF_UP).stripTrailingZeros()));
                }
                counter++;
            }
            row++;
        }

        currentType = currentType+1;
        // 填充没有数据的分类
        // 最后一行
        int lastIndex = sheet.getLastRowNum() + 1;
        for (int i = currentType++; i < pType.size(); i = currentType++) {
            HSSFRow lastRow = sheet.createRow(lastIndex++);
            type = BizUtils.getDicValueByCode(pType,(i==12)?8:i);
            handleSubTitle(i, cellStyle, fileName, endClo, type, workbook, lastRow);
        }

        sheet.setColumnWidth(0,1000);
        sheet.setColumnWidth(1,5000);
        for(int i = 2;i<heards.size();i++){
            sheet.setColumnWidth(i,2300);
        }

        ExcelUtil.downloadExcel(workbook, fileName, response);
    }

    /**
     * 处理子标题行
     *
     * @param currentType 当前项目类型
     * @param fileName    文件名，即Sheet名，用于获取Sheet单元格
     * @param type        项目类型数据
     * @param workbook    workBook工作蒲
     * @param row         行
     */
    private void handleSubTitle(int currentType, HSSFCellStyle cellStyle, String fileName, int endClo, String type, HSSFWorkbook workbook, HSSFRow row) {
        for (int t = 0; t <= endClo; t++) {
            if (t == 0) {
                handelCell(workbook, cellStyle, fileName, row.createCell(t), BizUtils.converttoChinaNumber((currentType==12)?8:currentType)+"、"+type,true);
                workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, 1));
            }else {
                handelCell(workbook, cellStyle, fileName, row.createCell(t), " ");
            }
        }
    }


    private void handelCell(HSSFWorkbook workbook, HSSFCellStyle cellStyle, String sheetName, HSSFCell cell, String value,boolean... title) {
        //单元格设置值
        cell.setCellValue(StringUtils.isEmpty(value) ? " " : value);
        //设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        HSSFFont font = cellStyle.getFont(workbook);
        if(null == font){
            font = workbook.createFont();
        }
        font.setFontName("宋体");//设置字体名称
        font.setFontHeightInPoints((short) 12);//设置字号
        if (title.length == 1 && title[0]) {
            font.setFontHeightInPoints((short) 14);
            font.setBold(true);//设置是否加粗
        }
        font.setColor(IndexedColors.BLACK.index);//设置字体颜色
        cellStyle.setFont(font);
        //自动换行
        cellStyle.setWrapText(true);
        //渲染单元格
        cell.setCellStyle(cellStyle);
        //设置标题行
        if (title.length == 1 && title[0]) {
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
//            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 13);
            font.setColor(IndexedColors.BLACK.index);
            style.setFont(font);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            cell.setCellStyle(style);
        }
        //列宽自适应、设置行高
        HSSFSheet sheet = workbook.getSheet(sheetName);
        sheet.setColumnWidth(0, 6 * 256);
        // 解决自动设置列宽中文失效的问题
//        sheet.setColumnWidth(cell.getColumnIndex(), sheet.getColumnWidth(cell.getColumnIndex()) * 12 / 10);

        for (int i = 0; i < cell.getColumnIndex(); i++) {
//            int colWidth = (sheet.getColumnWidth(i)*2);
            if(sheet.getColumnWidth(i)<255*256){
                sheet.setColumnWidth(i, sheet.getColumnWidth(i)*2 < 3000 ? 3000 : sheet.getColumnWidth(i));
            }else{
                sheet.setColumnWidth(i,6000);
            }
        }

        sheet.autoSizeColumn(cell.getColumnIndex());
        sheet.getRow(cell.getRowIndex()).setHeightInPoints(30);
    }

    @Override
    public JSONObject yearTotalInvest(Integer year) {
        Assert.notNull(year, "统计年份不能为空!");
        JSONObject result = new JSONObject();
        /**
         * 年度总支付 (万元)年度总支付计算公式：根据时间条件查询支付表进行支付求和+根据时间条件查询行政缴费表中费用求和
         * 年度总投资计算公式：根据时间条件查询支付表中用途为“工程款”的总和X0.75+根据时间条件查询支付表中用途不为“工程款”的总和+根据时间条件查询行政缴费表中费用求和
         */
        // 拉取项目支付表和行政缴费表数据
        List<ProgressManage> progressList = progressManager.list(new QueryWrapper<ProgressManage>().eq("YEAR_", year));
        List<AdministrativePayment> paymentList = administrativePaymentManager.list(new QueryWrapper<AdministrativePayment>().eq("PAYS_YEAR_", year));

        BigDecimal payTotal = new BigDecimal(0);
        BigDecimal investTotal = new BigDecimal(0);

        for (ProgressManage pro : progressList) {
            payTotal = payTotal.add(pro.getPayeeAmountTotal());
            if ("1".equals(pro.getPurpose())){
                investTotal = investTotal.add(pro.getPayeeAmountTotal().multiply(new BigDecimal(0.75)));
            }else{
                investTotal = investTotal.add(pro.getPayeeAmountTotal());
            }
        }

        for (AdministrativePayment pay : paymentList) {
            payTotal.add(pay.getPaysValue());
            investTotal.add(pay.getPaysValue());
        }

        result.set("payTotal", payTotal.setScale(2, BigDecimal.ROUND_HALF_UP).stripTrailingZeros());
        result.set("investTotal", investTotal.setScale(2, BigDecimal.ROUND_HALF_UP).stripTrailingZeros());

        return result;
    }

    @Override
    public BigDecimal totalInvestByYear(Integer year) {
        Assert.notNull(year, "统计年份不能为空!");
        BigDecimal result = BigDecimal.ZERO;
        /**
         * 年度总支付 (万元)年度总支付计算公式：根据时间条件查询支付表进行支付求和+根据时间条件查询行政缴费表中费用求和
         * 年度总投资计算公式：根据时间条件查询支付表中用途为“工程款”的总和X0.75+根据时间条件查询支付表中用途不为“工程款”的总和+根据时间条件查询行政缴费表中费用求和
         */
        //根据时间条件查询支付表中用途为"工程款"的总和X0.75
        Map<String, Object> map2 = progressManager.getMap(new QueryWrapper<ProgressManage>()
                .select("SUM(PAYEE_AMOUNT_TOTAL_) as price").eq(year!=0,"YEAR_", year)
                .eq("PURPOSE_", "1").eq("IS_DELE_","0"));
        if(BeanUtils.isNotEmpty(map2)){
            Object price = map2.get("price");
            if(BeanUtils.isNotEmpty(price)){
                BigDecimal multiply = ((BigDecimal) price).multiply(new BigDecimal(0.75));
                result = result.add(multiply);
            }
        }
        //根据时间条件查询支付表中用途不为"工程款"的总和
        Map<String, Object> map1 = progressManager.getMap(new QueryWrapper<ProgressManage>()
                .select("SUM(PAYEE_AMOUNT_TOTAL_) as price").eq(year!=0,"YEAR_", year)
                .ne("PURPOSE_", "1").eq("IS_DELE_","0"));
        if(BeanUtils.isNotEmpty(map1)){
            Object price = map1.get("price");
            if(BeanUtils.isNotEmpty(price)){
                result = result.add((BigDecimal)price);
            }
        }
        //根据时间条件查询行政缴费表中费用求和
        Map<String, Object> map3 = administrativePaymentManager.getMap(new QueryWrapper<AdministrativePayment>()
                .select("SUM(PAYS_VALUE_) as price")
                .eq(year!=0,"PAYS_YEAR_", year).eq("IS_DELE_","0"));
        if(BeanUtils.isNotEmpty(map3)){
            Object price = map3.get("price");
            if(BeanUtils.isNotEmpty(price)){
                result = result.add((BigDecimal)price);
            }
        }

        return result.setScale(2, BigDecimal.ROUND_HALF_UP).stripTrailingZeros();
    }
}
