package cn.com.countrygarden.bi.main.service.financial.Impl;

import cn.com.countrygarden.bi.main.dao.financial.*;
import cn.com.countrygarden.bi.main.dao.twerp.hm.TbSysOrganMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.ViewHsprFeesSerialStaticMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.TbHsprCommunityMapper;
import cn.com.countrygarden.bi.main.model.financial.*;
import cn.com.countrygarden.bi.main.model.financial.dto.*;
import cn.com.countrygarden.bi.main.model.twerp.ErpNcData;
import cn.com.countrygarden.bi.main.model.twerp.ViewHsprFeesSerialStatic;
import cn.com.countrygarden.bi.main.service.financial.FinancialService;
import cn.com.countrygarden.bi.main.util.FinancialUtil;
import cn.com.countrygarden.bi.main.util.MD5;
import cn.com.countrygarden.bi.main.util.NumberUtil;
import cn.com.countrygarden.bi.main.util.RedisUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;

import javax.servlet.http.HttpServletResponse;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class FinancialServiceImpl implements FinancialService {

    @Autowired
    private SubbalanceMapper subbalanceMapper;

    @Autowired
    private ViewHsprFeesSerialStaticMapper viewHsprFeesSerialStaticMapper;

    @Autowired
    private TbSysOrganMapper tbSysOrganMapper;

    @Autowired
    private TbHsprCommunityMapper tbHsprCommunityMapper;

    @Autowired
    private ErpToNcMapper erpToNcMapper;

    @Autowired
    private CommIdMatchCodoMapper commIdMatchCodoMapper;

    @Autowired
    private ReasonMapper reasonMapper;

    @Autowired
    private ProjectProfitMapper projectProfitMapper;

    @Override
    public List<PLByMonth> getPLByMonth(String organCode, String commId, String year, int startMonth, int endMonth, int userType, int buildingType) {

        if (userType == 1) {
            if (RedisUtils.hexits("bgyNCFinanicalPLByMonth", organCode + year + startMonth + endMonth + buildingType)) {
                return JSON.parseObject(RedisUtils.hget("bgyNCFinanicalPLByMonth", organCode + year + startMonth + endMonth + buildingType), new TypeReference<List<PLByMonth>>() {
                });
            }

        } else if (userType == 0) {
            if (RedisUtils.hexits("bgyNCFinanicalPLByMonth", year + startMonth + endMonth)) {
                return JSON.parseObject(RedisUtils.hget("bgyNCFinanicalPLByMonth", year + startMonth + endMonth), new TypeReference<List<PLByMonth>>() {
                });
            }
        }

        List<ErpNcData> erpNcDataList;

        if (userType == 0) {
            erpNcDataList = null;
        } else if (userType == 1) {
            erpNcDataList = tbHsprCommunityMapper.selectRegionByOrganCode(organCode);
        } else {
            erpNcDataList = tbHsprCommunityMapper.selectProjectByCommId(commId);
        }

        List<InCome> inComeList = getErpDataFromRedis(year, startMonth, endMonth, organCode, commId, userType, buildingType);


        List<OutCome> outComeList;
        if (buildingType == 1) {
            outComeList = dataHandlePLByMonth(erpNcDataList, userType, year, startMonth, endMonth);
        } else {
            outComeList = dataHandlePLByProjectRow(erpNcDataList, userType, year);
        }

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("incomes", inComeList);
        jsonObject.put("outcomes", outComeList);
        jsonObject.put("erpNcDataList", erpNcDataList);
        jsonObject.put("userType", userType);
        jsonObject.put("buildingType", buildingType);
        jsonObject.put("year", year);
        jsonObject.put("startMonth", startMonth);
        jsonObject.put("endMonth", endMonth);

        List<PLByMonth> returnList = buildingPLByMonth(jsonObject);
        //区域或者总部 返回数据时，Redis保存数据
        if (userType == 1) {
            RedisUtils.hset("bgyNCFinanicalPLByMonth", organCode + year + startMonth + endMonth + buildingType, JSONObject.toJSONString(returnList));
        } else if (userType == 0) {
            RedisUtils.hset("bgyNCFinanicalPLByMonth", year + startMonth + endMonth, JSONObject.toJSONString(returnList));
        }
        return returnList;
    }

    @Override
    public List<PLByProject> getPLByProject(String organCode, String commId, String year, int userType) {

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("userType", userType);
        jsonObject.put("year", year);

        List<ErpNcData> erpNcDataList = new ArrayList<>();

        Map<String, String> codeMap = new HashMap<>();

        Set<String> commIdList = new HashSet<>();
        if (userType == 1) {
            //先查询Redis中是否有数据 有则返回
            if (RedisUtils.hexits("bgyNCFinanicalPLByProject", organCode + year)) {
                return JSONObject.parseObject(RedisUtils.hget("bgyNCFinanicalPLByProject", organCode + year), new TypeReference<List<PLByProject>>() {
                });
            }
            erpNcDataList = tbHsprCommunityMapper.selectRegionByOrganCode(organCode);
        }
        if (userType == 2) {
            erpNcDataList = tbHsprCommunityMapper.selectProjectByCommId(commId);
        }

        for (ErpNcData erpNcData : erpNcDataList) {
            commIdList.add(erpNcData.getCommID());
            codeMap.put(erpNcData.getNCSystemNumber(), erpNcData.getOrganCode());
        }
        jsonObject.put("commIdList", commIdList);
        jsonObject.put("erpNcDataList", erpNcDataList);
        jsonObject.put("codeMap", codeMap);

        List<PLByProject> returnList = buildingPLByProject(jsonObject, organCode, commId);
        //区域返回数据时，Redis保存数据
        if (userType == 1) {
            RedisUtils.hset("bgyNCFinanicalPLByProject", organCode + year, JSONObject.toJSONString(returnList));
        }
        return returnList;
    }

    @Override
    public JSONObject getPLByProfit(String organCode, String year) {
        String item;        //分类
        String month;       //月份
        String organName = null;
        JSONObject returnJson = new JSONObject();
        //年度统计数据
        BigDecimal incomeWithOutTaxYear = new BigDecimal(0);        //年份统计不含税收入
        BigDecimal profitYear = new BigDecimal(0);                  //年份统计利润
        int projectCountYear = 0;
        List<Integer> tempYearList = Arrays.asList(0, 0, 0);
//        Integer routineCountYear = 0;                                       //年份统计纯常规
//        Integer caseCountYear = 0;                                          //年份统计纯案场
//        Integer routineCaseCountYear = 0;                                   //年份统计混合
        //年度Item List数据初始化
        ProjectProfitDetail detail0Year = new ProjectProfitDetail(organCode, year, "13", 0, 0, 0, 0,
                new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
        ProjectProfitDetail detail10Year = new ProjectProfitDetail(organCode, year, "13", 0, 0, 0, 0,
                new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
        ProjectProfitDetail detail15Year = new ProjectProfitDetail(organCode, year, "13", 0, 0, 0, 0,
                new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
        ProjectProfitDetail detail30Year = new ProjectProfitDetail(organCode, year, "13", 0, 0, 0, 0,
                new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
        ProjectProfitDetail detail50Year = new ProjectProfitDetail(organCode, year, "13", 0, 0, 0, 0,
                new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
        ProjectProfitDetail detail99Year = new ProjectProfitDetail(organCode, year, "13", 0, 0, 0, 0,
                new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));

        for (int i = 1; i < 13; i++) {
            BigDecimal incomeWithOutTax = new BigDecimal(0);        //月份统计不含税收入
            BigDecimal profit = new BigDecimal(0);                  //月份统计利润
            List<Integer> tempList = Arrays.asList(0, 0, 0);
//            Integer routineCount = 0;                                       //月份统计纯常规
//            Integer caseCount = 0;                                          //月份统计纯案场
//            Integer routineCaseCount = 0;                                   //月份统计混合
            month = String.valueOf(i);
            List<ProjectProfit> projectProfitList = projectProfitMapper.selectProfitByOrganYearMonth(organCode, year, month);
            organName = projectProfitList.get(0).getOrganName();
            //月份Item List数据初始化
            ProjectProfitDetail detail0 = new ProjectProfitDetail(organName, organCode, year, month, 0, 0, 0, 0,
                    new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
            ProjectProfitDetail detail10 = new ProjectProfitDetail(organName, organCode, year, month, 0, 0, 0, 0,
                    new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
            ProjectProfitDetail detail15 = new ProjectProfitDetail(organName, organCode, year, month, 0, 0, 0, 0,
                    new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
            ProjectProfitDetail detail30 = new ProjectProfitDetail(organName, organCode, year, month, 0, 0, 0, 0,
                    new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
            ProjectProfitDetail detail50 = new ProjectProfitDetail(organName, organCode, year, month, 0, 0, 0, 0,
                    new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
            ProjectProfitDetail detail99 = new ProjectProfitDetail(organName, organCode, year, month, 0, 0, 0, 0,
                    new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));

            for (ProjectProfit projectProfit : projectProfitList) {
                incomeWithOutTax = incomeWithOutTax.add(new BigDecimal(projectProfit.getIncomeWithOutTax()));
                profit = profit.add(new BigDecimal(projectProfit.getProfit()));
                item = projectProfit.getItem();
                switch (item) {
                    case "Negative": {          //Negative
                        FinancialUtil.profitDetialDataHandle(detail0, projectProfit, tempList);
                        FinancialUtil.profitDetialDataHandle(detail0Year, projectProfit, tempYearList);
                        break;
                    }
                    case "0~<10%": {       //0~<10%
                        FinancialUtil.profitDetialDataHandle(detail10, projectProfit, tempList);
                        FinancialUtil.profitDetialDataHandle(detail10Year, projectProfit, tempYearList);
                        break;
                    }
                    case "10%~<15%": {      //10%~<15%
                        FinancialUtil.profitDetialDataHandle(detail15, projectProfit, tempList);
                        FinancialUtil.profitDetialDataHandle(detail15Year, projectProfit, tempYearList);
                        break;
                    }
                    case "15%~<30%": {      //15%~<30%
                        FinancialUtil.profitDetialDataHandle(detail30, projectProfit, tempList);
                        FinancialUtil.profitDetialDataHandle(detail30Year, projectProfit, tempYearList);
                        break;
                    }
                    case "30%~50%": {      //30%~<50%
                        FinancialUtil.profitDetialDataHandle(detail50, projectProfit, tempList);
                        FinancialUtil.profitDetialDataHandle(detail50Year, projectProfit, tempYearList);
                        break;
                    }
                    case "50% or above": {         //50% or above
                        FinancialUtil.profitDetialDataHandle(detail99, projectProfit, tempList);
                        FinancialUtil.profitDetialDataHandle(detail99Year, projectProfit, tempYearList);
                        break;
                    }
                    default:
                        break;
                }
            }
            //月份数据利润率
            detail0.setProfitRate(NumberUtil.divide(detail0.getProfit(), detail0.getIncomeWithOutTax(), 4));
            detail10.setProfitRate(NumberUtil.divide(detail10.getProfit(), detail10.getIncomeWithOutTax(), 4));
            detail15.setProfitRate(NumberUtil.divide(detail15.getProfit(), detail15.getIncomeWithOutTax(), 4));
            detail30.setProfitRate(NumberUtil.divide(detail30.getProfit(), detail30.getIncomeWithOutTax(), 4));
            detail50.setProfitRate(NumberUtil.divide(detail50.getProfit(), detail50.getIncomeWithOutTax(), 4));
            detail99.setProfitRate(NumberUtil.divide(detail99.getProfit(), detail99.getIncomeWithOutTax(), 4));
            //添加月度详细数据
            List<ProjectProfitDetail> projectProfitDetailList = new ArrayList<>();
            projectProfitDetailList.add(detail0);
            projectProfitDetailList.add(detail10);
            projectProfitDetailList.add(detail15);
            projectProfitDetailList.add(detail30);
            projectProfitDetailList.add(detail50);
            projectProfitDetailList.add(detail99);

            JSONObject jsonObject = new JSONObject();
            jsonObject.put("organName", organName);
            jsonObject.put("organCode", organCode);
            jsonObject.put("year", year);
            jsonObject.put("month", month);

            jsonObject.put("routineCount", tempList.get(0));                                        //放入该月份纯常规总计
            jsonObject.put("caseCount", tempList.get(1));                                              //放入该月份纯案场总计
            jsonObject.put("routineCaseCount", tempList.get(2));                                //放入该月份混合总计

            jsonObject.put("projectCount", projectProfitList.size());                            //放入该月份项目统计
            jsonObject.put("incomeWithOutTax", incomeWithOutTax);                                //放入该月份不含税收入
            jsonObject.put("profit", profit);                                                    //放入该月份利润
            jsonObject.put("profitRate", NumberUtil.divide(profit, incomeWithOutTax, 4));     //放入该月份利润率

            jsonObject.put("projectProfitDetailList", projectProfitDetailList);                  //放入该月份Item List
            returnJson.put(month, jsonObject);
            projectCountYear = projectCountYear + projectProfitList.size();                     //计算年份项目数
        }
        //年份数据利润率
        detail0Year.setProfitRate(NumberUtil.divide(detail0Year.getProfit(), detail0Year.getIncomeWithOutTax(), 4));
        detail10Year.setProfitRate(NumberUtil.divide(detail10Year.getProfit(), detail10Year.getIncomeWithOutTax(), 4));
        detail15Year.setProfitRate(NumberUtil.divide(detail15Year.getProfit(), detail15Year.getIncomeWithOutTax(), 4));
        detail30Year.setProfitRate(NumberUtil.divide(detail30Year.getProfit(), detail30Year.getIncomeWithOutTax(), 4));
        detail50Year.setProfitRate(NumberUtil.divide(detail50Year.getProfit(), detail50Year.getIncomeWithOutTax(), 4));
        detail99Year.setProfitRate(NumberUtil.divide(detail99Year.getProfit(), detail99Year.getIncomeWithOutTax(), 4));

        detail0Year.setOrganName(organName);
        detail10Year.setOrganName(organName);
        detail15Year.setOrganName(organName);
        detail30Year.setOrganName(organName);
        detail50Year.setOrganName(organName);
        detail99Year.setOrganName(organName);
        //添加年度详细数据
        List<ProjectProfitDetail> projectProfitDetailListYear = new ArrayList<>();
        projectProfitDetailListYear.add(detail0Year);
        projectProfitDetailListYear.add(detail10Year);
        projectProfitDetailListYear.add(detail15Year);
        projectProfitDetailListYear.add(detail30Year);
        projectProfitDetailListYear.add(detail50Year);
        projectProfitDetailListYear.add(detail99Year);

        JSONObject yearJsonObject = new JSONObject();
        yearJsonObject.put("organName", organName);
        yearJsonObject.put("organCode", organCode);
        yearJsonObject.put("year", year);
        yearJsonObject.put("month", "13");

        yearJsonObject.put("routineCount", tempYearList.get(0));                                        //放入该年份纯常规总计
        yearJsonObject.put("caseCount", tempYearList.get(1));                                              //放入该年份纯案场总计
        yearJsonObject.put("routineCaseCount", tempYearList.get(2));                                //放入该年份混合总计

        yearJsonObject.put("projectCount", projectCountYear);                            //放入该年份项目统计
        yearJsonObject.put("incomeWithOutTax", incomeWithOutTaxYear);                                //放入该年份不含税收入
        yearJsonObject.put("profit", profitYear);                                                    //放入该年份利润
        yearJsonObject.put("profitRate", NumberUtil.divide(profitYear, incomeWithOutTaxYear, 4));     //放入该年份利润率

        yearJsonObject.put("projectProfitDetailList", projectProfitDetailListYear);                  //放入该年份Item List
        returnJson.put("13", yearJsonObject);
        return returnJson;
    }

    @Override
    public void exportPL(HttpServletResponse response, String organCode, String year, int startMonth, int endMonth, int userType, String commId, int exportType) {
        if (exportType == 1) {             //1 导出第一张表
            exportPLByMonth(response, organCode, commId, year, startMonth, endMonth, userType);
        } else if (exportType == 2) {      //2 导出第二张表
            exportPLByProjectRow(organCode, commId, year, response, userType);
        } else {                         //3 导出第三张表
            exportProjectProfitData(response, organCode, year);
        }
    }

    @Override
    public void saveReasonList(List<Reason> reasonList) {
        for (Reason reason : reasonList) {
            String reasonId = MD5.GetMD5Code(reason.getCommId() + reason.getYear() + reason.getMonth());
            reason.setReasonId(reasonId);
            reasonMapper.insertOrUpdateReason(reason);
        }
    }



    /*----------------------------------------处理NC 成本数据 start----------------------------------------------*/

    /**
     * PLByMonth 成本数据获取接口
     *
     * @param erpNcDataList
     * @param userType
     * @param year
     * @param startMonth
     * @param endMonth
     * @return
     */
    private List<OutCome> dataHandlePLByMonth(List<ErpNcData> erpNcDataList, int userType, String year, int startMonth, int endMonth) {
        List<OutCome> returnList = null;
        if (userType == 0) {
            System.out.println("---该账号为总部账号---");
            returnList = headquartersCostHandlePLByMonth(year, startMonth, endMonth);
        } else if (userType == 1) {
            System.out.println("---该账号为区域账号---");
            returnList = regionCostHandlePLByMonth(erpNcDataList, year, startMonth, endMonth);
        } else if (userType == 2) {
            System.out.println("---该账号为项目账号---");
            returnList = projectCostHandlePLByMonth(erpNcDataList, year, startMonth, endMonth);
        }
        return returnList;
    }

    /**
     * @Description: //P&LByMonth 项目成本数据获取
     * @Param: [erpNcDataList, year, startMonth, endMonth]
     * @return: java.util.List<cn.com.countrygarden.bi.main.model.financial.dto.OutCome>
     * @Author: SignV
     * @Date: 2018/8/23
     */
    private List<OutCome> projectCostHandlePLByMonth(List<ErpNcData> erpNcDataList, String year, int startMonth, int endMonth) {
        List<OutCome> returnList = new ArrayList<>();
        String codo;
        String commId;
        String lastYear;
        int monthSize = endMonth - startMonth;
        List<String> codoList = new ArrayList<>();
        List<String> commIdList = new ArrayList<>();
        //获取项目账套编码信息
        List<String> oneToManyCodos = tbHsprCommunityMapper.selectCodoOneToMany();
        for (ErpNcData erpNcData : erpNcDataList) {
            commId = erpNcData.getCommID();
            if (commIdList.contains(commId)) {
                //对erpNcDataList commId进行去重
                continue;
            }
            commIdList.add(commId);
        }
        for (ErpNcData erpNcData : erpNcDataList) {
            //获取项目所有成本数据
            commId = erpNcData.getCommID();
            codo = erpNcData.getNCSystemNumber() + "-0001";
            if (codoList.contains(codo)) {
                //对erpNcDataList账簿编号进行去重
                continue;
            }
            codoList.add(codo);
//            System.out.println(codoList.size() + "：去重后的commid -->" + commId + " || 去重后的codo -->" + codo);
            List<Subbalance> subbalanceList = subbalanceMapper.selectProjectCost(codo, year, true);               //获取该项目全年成本数据
            lastYear = String.valueOf(Integer.valueOf(year) - 1);
            List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectProjectCost(codo, lastYear, true);   //获取该项目去年成本数据
            System.out.println("last year:" + subbalanceLastYearList.size());
            //根据月份索引和月份长度获取区间月份
            List<List<Subbalance>> monthClassifyList = FinancialUtil.monthClassifyByMonthSize(subbalanceList, startMonth, monthSize);
            if (oneToManyCodos.contains(codo)) {
                List<CommIdMatchCodo> commIdMatchCodoList = commIdMatchCodoMapper.selectCommIdMatchCodoByCodo(codo);
                List<CommIdMatchCodo> commIdMatchCodos = new ArrayList<>();
                for (CommIdMatchCodo commIdMatchCodo : commIdMatchCodoList) {
                    if (commIdList.contains(commIdMatchCodo.getCommId())) {
                        commIdMatchCodos.add(commIdMatchCodo);
                    }
                }
                for (int i = 0; i <= monthSize; i++) {
                    //对区间每个月份的数据进行处理
                    FinancialUtil.financialDataHandle(monthClassifyList.get(i),
                            year, String.valueOf(startMonth + i), codo, returnList, 2, commIdMatchCodos, null);
                }
                //处理成本年度数据
                FinancialUtil.financialDataHandle(subbalanceList,
                        year, "13", codo, returnList, 2, commIdMatchCodos, null);
                //处理成本年度数据
                FinancialUtil.financialDataHandle(subbalanceLastYearList,
                        lastYear, "0", codo, returnList, 2, commIdMatchCodos, null);
            } else {//账簿编码一对一项目时
                for (int i = 0; i <= monthSize; i++) {
                    //对区间每个月份的数据进行处理
                    returnList.add(FinancialUtil.dataHandleForPLByMonth(monthClassifyList.get(i), year, String.valueOf(startMonth + i), commId, 2));
                }
                //处理成本年度数据
                List<Subbalance> filterList = subbalanceList.stream().filter(s -> Integer.parseInt(s.getPeriod()) <= endMonth && Integer.parseInt(s.getPeriod()) >= startMonth).collect(Collectors.toList());
                returnList.add(FinancialUtil.dataHandleForPLByMonth(filterList, year, "13", commId, 2));
                //处理成本年度数据
                returnList.add(FinancialUtil.dataHandleForPLByMonth(subbalanceLastYearList, lastYear, "0", commId, 2));
            }
        }
        return returnList;
    }

    /**
     * @Description: //P&LByMonth 区域成本数据获取
     * @Param: [erpNcDataList, year, startMonth, endMonth]
     * @return: java.util.List<cn.com.countrygarden.bi.main.model.financial.dto.OutCome>
     * @Author: SignV
     * @Date: 2018/8/23
     */
    private List<OutCome> regionCostHandlePLByMonth(List<ErpNcData> erpNcDataList, String year, int startMonth, int endMonth) {
        List<OutCome> returnList = new ArrayList<>();
        Map<String, List<String>> organMap = new HashMap<>();
        String codo;
        String organCode;
        int monthSize = endMonth - startMonth;
        String lastYear;
        List<String> codoList = new ArrayList<>();
        //获取区域账套编码信息
//        List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectRegionByUserCode(userCode);
        for (ErpNcData erpNcData : erpNcDataList) {
            codo = erpNcData.getNCSystemNumber() + "-0001";
            if (codoList.contains(codo)) {
                //对erpNcDataList账簿编号进行去重
                continue;
            }
            codoList.add(codo);
            organCode = erpNcData.getOrganCode();
            erpNcData.setNCSystemNumber(codo);
            if (!organMap.containsKey(organCode)) {
                List<String> codoOrganList = new ArrayList<>();
                codoOrganList.add(erpNcData.getNCSystemNumber());
                organMap.put(organCode, codoOrganList);
            } else {
                organMap.get(organCode).add(erpNcData.getNCSystemNumber());
            }
        }
        //获取每个区域的所有成本数据
        for (Map.Entry<String, List<String>> entry : organMap.entrySet()) {
            System.out.println(entry.getKey() + ":" + entry.getValue());
            List<Subbalance> subbalanceList = subbalanceMapper.selectRegionCost(entry.getValue(), year, true);               //获取该区域全年成本数据
            lastYear = String.valueOf(Integer.valueOf(year) - 1);
            List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectRegionCost(entry.getValue(), lastYear, true);   //获取该项目去年成本数据
            //根据月份索引和月份长度获取区间月份
            List<List<Subbalance>> monthClassifyList = FinancialUtil.monthClassifyByMonthSize(subbalanceList, startMonth, monthSize);
            for (int i = 0; i <= monthSize; i++) {
                //对区间每个月份的数据进行处理
                returnList.add(FinancialUtil.dataHandleForPLByMonth(
                        monthClassifyList.get(i), year, String.valueOf(startMonth + i), entry.getKey(), 1));
            }
            //处理成本年度数据
            List<Subbalance> filterList = subbalanceList.stream().filter(s -> Integer.parseInt(s.getPeriod()) <= endMonth && Integer.parseInt(s.getPeriod()) >= startMonth).collect(Collectors.toList());
            returnList.add(FinancialUtil.dataHandleForPLByMonth(filterList, year, "13", entry.getKey(), 1));
            //处理成本去年年度数据
            returnList.add(FinancialUtil.dataHandleForPLByMonth(subbalanceLastYearList, lastYear, "0", entry.getKey(), 1));
        }
        return returnList;
    }

    /**
     * @Description: //P&LByMonth 总部成本数据获取
     * @Param: [year, startMonth, endMonth]
     * @return: java.util.List<cn.com.countrygarden.bi.main.model.financial.dto.OutCome>
     * @Author: SignV
     * @Date: 2018/8/23
     */
    private List<OutCome> headquartersCostHandlePLByMonth(String year, int startMonth, int endMonth) {
        List<OutCome> returnList = new ArrayList<>();
        int monthSize = endMonth - startMonth;
        String lastYear;
        List<Subbalance> subbalanceList = subbalanceMapper.selectHeadquartersCost(year, true);                //获取总部全年成本数据
        lastYear = String.valueOf(Integer.valueOf(year) - 1);
        List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectHeadquartersCost(lastYear, true);   //获取总部去年成本数据
        //根据月份索引和月份长度获取区间月份
        List<List<Subbalance>> monthClassifyList = FinancialUtil.monthClassifyByMonthSize(subbalanceList, startMonth, monthSize);
        for (int i = 0; i <= monthSize; i++) {
            //对区间每个月份的数据进行处理
            returnList.add(FinancialUtil.dataHandleForPLByMonth(
                    monthClassifyList.get(i), year, String.valueOf(startMonth + i), null, 0));
        }
        //处理成本年度数据
        List<Subbalance> filterList = subbalanceList.stream().filter(s -> Integer.parseInt(s.getPeriod()) <= endMonth && Integer.parseInt(s.getPeriod()) >= startMonth).collect(Collectors.toList());
        returnList.add(FinancialUtil.dataHandleForPLByMonth(filterList, year, "13", null, 0));
        //处理成本去年年度数据
        returnList.add(FinancialUtil.dataHandleForPLByMonth(subbalanceLastYearList, lastYear, "0", null, 0));
        return returnList;
    }

    /**
     * @Description: //P&L 按项目横向 项目成本数据获取 (区域账号可复用此方法)
     * @Param: [codoCommIdMap, codoMap, commIdList, year]
     * @return: java.util.List<cn.com.countrygarden.bi.main.model.financial.dto.OutCome>
     * @Author: SignV
     * @Date: 2018/8/23
     */
    private List<OutCome> projectCostHandlePLByProjectRow(Map<String, String> codoCommIdMap, Map<String, String> codoMap, List<String> commIdList, String year) {
        List<OutCome> returnList = new ArrayList<>();
        String codo;
        String commId;
        String organCode;
        int startMonth = 1;
        int monthSize = 11;
        List<String> oneToManyCodos = tbHsprCommunityMapper.selectCodoOneToMany();
        for (Map.Entry<String, String> entry : codoCommIdMap.entrySet()) {
            codo = entry.getKey();
            commId = entry.getValue();
            organCode = codoMap.get(codo);
            List<Subbalance> subbalanceList = subbalanceMapper.selectProjectCost(codo, year, true); //获取该项目全年成本数据
            //根据月份索引和月份长度获取区间月份
            List<List<Subbalance>> monthClassifyList = FinancialUtil.monthClassifyByMonthSize(subbalanceList, startMonth, monthSize);
            if (oneToManyCodos.contains(codo)) {
                List<CommIdMatchCodo> commIdMatchCodoList = commIdMatchCodoMapper.selectCommIdMatchCodoByCodo(codo);
                List<CommIdMatchCodo> commIdMatchCodos = new ArrayList<>();
                for (CommIdMatchCodo commIdMatchCodo : commIdMatchCodoList) {
                    if (commIdList.contains(commIdMatchCodo.getCommId())) {
                        commIdMatchCodos.add(commIdMatchCodo);
                    }
                }
                for (int i = 0; i <= monthSize; i++) {
                    //对区间每个月份的数据进行处理
                    FinancialUtil.financialDataHandle(monthClassifyList.get(i),
                            year, String.valueOf(startMonth + i), codo, returnList, 2, commIdMatchCodos, organCode);
                }
                //处理成本年度数据
                FinancialUtil.financialDataHandle(subbalanceList,
                        year, "13", codo, returnList, 2, commIdMatchCodos, organCode);
            } else {//账簿编码一对一项目时
                for (int i = 0; i <= monthSize; i++) {
                    //对区间每个月份的数据进行处理
                    returnList.add(FinancialUtil.projectDataForProjectRow(monthClassifyList.get(i), year, String.valueOf(startMonth + i), commId, organCode));
                }
                //处理成本年度数据
                returnList.add(FinancialUtil.projectDataForProjectRow(subbalanceList, year, "13", commId, organCode));
            }
        }
        return returnList;
    }

    /**
     * @Description: //P&L 按项目横向 区域成本数据获取 (总部账号可复用此方法)
     * @Param: [erpNcDataList, year]
     * @return: java.util.List<cn.com.countrygarden.bi.main.model.financial.dto.OutCome>
     * @Author: SignV
     * @Date: 2018/8/23
     */
    private List<OutCome> regionCostHandlePLByProjectRow(List<ErpNcData> erpNcDataList, String year) {
        List<OutCome> returnList = new ArrayList<>();
        Map<String, Map<String, String>> organMap = new HashMap<>();
        Map<String, String> codoMap = new HashMap<>();
        String codo;
        String organCode;
        String commId;
        List<String> codoList = new ArrayList<>();
        //获取区域账套编码信息
//        List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectRegionByUserCode(userCode);
        List<String> commIdList = new ArrayList<>();
        for (ErpNcData erpNcData : erpNcDataList) {
            commId = erpNcData.getCommID();
            if (commIdList.contains(commId)) {
                //对erpNcDataList commId进行去重
                continue;
            }
            commIdList.add(commId);
        }
        for (ErpNcData erpNcData : erpNcDataList) {
            codo = erpNcData.getNCSystemNumber() + "-0001";
            commId = erpNcData.getCommID();
            if (codoList.contains(codo)) {
                //对erpNcDataList账簿编号进行去重
                continue;
            }
            codoList.add(codo);
            organCode = erpNcData.getOrganCode();
            codoMap.put(codo, organCode);
            if (!organMap.containsKey(organCode)) {
                Map<String, String> tempMap = new HashMap<>();
                tempMap.put(codo, commId);
                organMap.put(organCode, tempMap);
            } else {
                organMap.get(organCode).put(codo, commId);
            }
        }
        //获取每个区域的每个项目成本数据
        for (Map.Entry<String, Map<String, String>> entry : organMap.entrySet()) {
            returnList.addAll(projectCostHandlePLByProjectRow(entry.getValue(), codoMap, commIdList, year));
        }
        return returnList;
    }

    /**
     * PLByProject 成本获取
     *
     * @param erpNcDataList
     * @param userType
     * @param year
     * @return
     */
    private List<OutCome> dataHandlePLByProjectRow(List<ErpNcData> erpNcDataList, int userType, String year) {
        List<OutCome> returnList = null;
        if (userType == 0) {
            System.out.println("---该账号为总部账号---");
            returnList = regionCostHandlePLByProjectRow(erpNcDataList, year);    //总部账号复用区域数据获取方法
        } else if (userType == 1) {
            System.out.println("---该账号为区域账号---");
            returnList = regionCostHandlePLByProjectRow(erpNcDataList, year);    //获取区域下 所有项目的数据
        } else if (userType == 2) {
            //获取传入的 每个项目的数据
            String codo;
            String commId;
            Map<String, String> codoCommIdMap = new HashMap<>();
            Map<String, String> codoMap = new HashMap<>();
            List<String> commIdList = new ArrayList<>();
            //获取项目账套编码信息
            for (ErpNcData erpNcData : erpNcDataList) {
                commId = erpNcData.getCommID();
                if (commIdList.contains(commId)) {
                    //对erpNcDataList commId进行去重
                    continue;
                }
                commIdList.add(commId);
            }
            for (ErpNcData erpNcData : erpNcDataList) {
                //获取项目所有成本数据
                commId = erpNcData.getCommID();
                codo = erpNcData.getNCSystemNumber() + "-0001";
                if (codoCommIdMap.containsKey(codo)) {
                    //对erpNcDataList账簿编号进行去重 以及获取对应的OgranCode
                    continue;
                }
                codoMap.put(codo, erpNcData.getOrganCode());     //保存账套编号与organCode对应关系
                codoCommIdMap.put(codo, commId);                 //保存账套编号与commId对应关系
            }
            returnList = projectCostHandlePLByProjectRow(codoCommIdMap, codoMap, commIdList, year);
        }
        return returnList;
    }
    /*----------------------------------------处理NC 成本数据 end----------------------------------------------*/


    /*----------------------------------------ERP 收入部分  start-----------------------------------------------*/

    //ERP收入数据获取接口
    public List<InCome> getERPIncomeData(String year, Integer startMonth, Integer endMonth, List<ErpNcData> erpNcDataList, Integer userType, Integer buildingType) {
        List<InCome> incomeList = new ArrayList<>();

        List<Integer> commIdList = new ArrayList<>();

        Map<String, List<Integer>> organCodeCommIdMap = new HashMap<>();

        if (erpNcDataList != null) {
            for (ErpNcData erpNcData : erpNcDataList) {
                if (organCodeCommIdMap.keySet().contains(erpNcData.getOrganCode())) {
                    List<Integer> commIds = organCodeCommIdMap.get(erpNcData.getOrganCode());
                    commIds.add(Integer.valueOf(erpNcData.getCommID()));
                } else {
                    List<Integer> commIds = new ArrayList<>();
                    commIds.add(Integer.valueOf(erpNcData.getCommID()));
                    organCodeCommIdMap.put(erpNcData.getOrganCode(), commIds);
                }
                commIdList.add(Integer.valueOf(erpNcData.getCommID()));
            }
        } else {
            commIdList = null;
        }


        switch (userType) {
            case 0: {           //用户拥有总部权限，查询所有项目的数据
                incomeList = handleAllERPData(year, startMonth, endMonth);
                break;
            }
            case 1: {       //区域权限
                incomeList = handleRegionERPData(organCodeCommIdMap, year, startMonth, endMonth, buildingType);
                break;
            }
            case 2: {       //项目权限
                incomeList = handleProjectERPData(commIdList, year, startMonth, endMonth);
                break;
            }
            default:
                break;
        }
        return incomeList;
    }

    //总部
    private List<InCome> handleAllERPData(String year, Integer startMonth, Integer endMonth) {
        List<InCome> incomeList = new ArrayList<>();

        //物业管理 6001007
        //社区增值服务 6001008
        //协销服务 6001009
        //场地服务 6001010
        //其他业务收入 6501
        Map<Integer, Map<String, String>> estateManagement = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001007", null, 1).get(null);
        Map<Integer, Map<String, String>> communityService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001008", null, 1).get(null);
        Map<Integer, Map<String, String>> marketingService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001009", null, 1).get(null);
        Map<Integer, Map<String, String>> siteService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001010", null, 1).get(null);
        Map<Integer, Map<String, String>> other = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6501", null, 1).get(null);

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("estateManagement", estateManagement);
        jsonObject.put("communityService", communityService);
        jsonObject.put("marketingService", marketingService);
        jsonObject.put("siteService", siteService);
        jsonObject.put("other", other);
        jsonObject.put("year", year);
        jsonObject.put("startMonth", startMonth);
        jsonObject.put("endMonth", endMonth);

        incomeList = calculateTotal(jsonObject);
        return incomeList;
    }

    //区域
    private List<InCome> handleRegionERPData(Map<String, List<Integer>> organCodeCommIdMap, String year, Integer startMonth, Integer endMonth, Integer buildingType) {
        List<InCome> returnList = new ArrayList<>();

        for (String organCode : organCodeCommIdMap.keySet()) {
            List<Integer> commIdList = organCodeCommIdMap.get(organCode);
            Map<Integer, Map<Integer, Map<String, String>>> estateManagement = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001007", commIdList, buildingType);
            Map<Integer, Map<Integer, Map<String, String>>> communityService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001008", commIdList, buildingType);
            Map<Integer, Map<Integer, Map<String, String>>> marketingService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001009", commIdList, buildingType);
            Map<Integer, Map<Integer, Map<String, String>>> siteService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001010", commIdList, buildingType);
            Map<Integer, Map<Integer, Map<String, String>>> other = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6501", commIdList, buildingType);

            if (buildingType == 1) {
                commIdList.clear();
                commIdList.add(null);
            }
            for (Integer commId : commIdList) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("estateManagement", estateManagement.get(commId));
                jsonObject.put("communityService", communityService.get(commId));
                jsonObject.put("marketingService", marketingService.get(commId));
                jsonObject.put("siteService", siteService.get(commId));
                jsonObject.put("other", other.get(commId));
                jsonObject.put("year", year);
                jsonObject.put("startMonth", startMonth);
                jsonObject.put("endMonth", endMonth);
                jsonObject.put("commId", commId);
                jsonObject.put("organCode", organCode);
                List<InCome> incomeList = calculateTotal(jsonObject);
                returnList.addAll(incomeList);
            }
        }

        return returnList;
    }

    //项目
    private List<InCome> handleProjectERPData(List<Integer> commIDList, String year, Integer startMonth, Integer endMonth) {

        List<InCome> returnList = new ArrayList<>();
        Map<Integer, Map<Integer, Map<String, String>>> estateManagement = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001007", commIDList, 2);
        Map<Integer, Map<Integer, Map<String, String>>> communityService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001008", commIDList, 2);
        Map<Integer, Map<Integer, Map<String, String>>> marketingService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001009", commIDList, 2);
        Map<Integer, Map<Integer, Map<String, String>>> siteService = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6001010", commIDList, 2);
        Map<Integer, Map<Integer, Map<String, String>>> other = handleERPTONCData(Integer.parseInt(year), startMonth, endMonth, "6501", commIDList, 2);


        for (Integer commId : commIDList) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("estateManagement", estateManagement.get(commId));
            jsonObject.put("communityService", communityService.get(commId));
            jsonObject.put("marketingService", marketingService.get(commId));
            jsonObject.put("siteService", siteService.get(commId));
            jsonObject.put("other", other.get(commId));
            jsonObject.put("year", year);
            jsonObject.put("startMonth", startMonth);
            jsonObject.put("endMonth", endMonth);
            jsonObject.put("commId", commId);
            List<InCome> incomeList = calculateTotal(jsonObject);
            returnList.addAll(incomeList);
        }
        return returnList;
    }

    //erp原始数据处理  然后调用calculate方法计算汇总
    public Map<Integer, Map<Integer, Map<String, String>>> handleERPTONCData(Integer year, Integer startMonth, Integer endMonth, String code, List<Integer> commIDList, Integer buildingType) {

        Map<Integer, Map<Integer, Map<String, String>>> returnMap = new HashMap<>();   //项目编号：{1月：{xxx,xxx},2月：{xxx,xxx}}

        Map<String, List<Integer>> erpCodeMap = new HashMap<>();    // NC科目名称：[erp科目编号1、erp科目编号2...]

        Map<Integer, List<ViewHsprFeesSerialStatic>> commDataMap = new HashMap<>(); //项目编号：[erpData数据1、erpData数据2...]

        Map<String, List<ViewHsprFeesSerialStatic>> dataMap = new HashMap<>(); //NC科目名称：[erpData数据1、erpData数据2...]

        Set<Integer> corpCostIDList = new HashSet<>();  //所有的科目编号集合，一次查询数据库返回所有本科目下的数据

        List<ErpToNc> erpToNcList = erpToNcMapper.selectERPCostIdByNCCode(code, null);  //根据NC科目编码查询对应的ERP科目编码列表

        //科目ID分类处理
        for (ErpToNc erpToNc : erpToNcList) {
            if (erpCodeMap.keySet().contains(erpToNc.getThird())) {
                List<Integer> costIds = erpCodeMap.get(erpToNc.getThird());
                costIds.add(Integer.valueOf(erpToNc.getCostId()));
            } else {
                List<Integer> costIds = new ArrayList<>();
                costIds.add(Integer.valueOf(erpToNc.getCostId()));
                erpCodeMap.put(erpToNc.getThird(), costIds);
            }
            corpCostIDList.add(Integer.valueOf(erpToNc.getCostId()));
        }

        Set<String> erpNameList = erpCodeMap.keySet();

        String startDate = String.valueOf(year - 1) + "-01-01";      //2018年 2017-01-01
        String endDate = String.valueOf(year + 1) + "-01-01";       //2018年 2019-01-01

        List<ViewHsprFeesSerialStatic> viewHsprFeesSerialStatics = viewHsprFeesSerialStaticMapper.selectIncomeByCommIDCorpCostID(commIDList, corpCostIDList,
                startDate, endDate);


//        //物业费本月收上月，日期需要往后调整一个月，比如2018年1月的，要取2017年12月份的数据
//        if (code.equals("6001007")) {
//            for (ViewHsprFeesSerialStatic viewHsprFeesSerialStatic : viewHsprFeesSerialStatics) {
//                int m = viewHsprFeesSerialStatic.getMonth();
//                int y = viewHsprFeesSerialStatic.getYear();
//
//                viewHsprFeesSerialStatic.setYear(m == 12 ? y + 1 : y);
//                viewHsprFeesSerialStatic.setMonth(m == 12 ? 1 : m + 1);
//            }
//        }

        if (commIDList == null || commIDList.size() == 0 || buildingType == 1) {        //总部或者区域的时候 汇总处理
            commDataMap.put(null, viewHsprFeesSerialStatics);
        } else {
            for (Integer commID : commIDList) {
                List<ViewHsprFeesSerialStatic> dataList = new ArrayList<>();
                commDataMap.put(commID, dataList);
            }

            for (ViewHsprFeesSerialStatic ErpData : viewHsprFeesSerialStatics) {        //查询到的数据按项目分类
                if (commDataMap.keySet().contains(ErpData.getCommID())) {
                    List<ViewHsprFeesSerialStatic> dataList = commDataMap.get(ErpData.getCommID());
                    dataList.add(ErpData);
                } else {
                    List<ViewHsprFeesSerialStatic> dataList = new ArrayList<>();
                    dataList.add(ErpData);
                    commDataMap.put(ErpData.getCommID(), dataList);
                }
            }
        }

        for (Integer commId : commDataMap.keySet()) {
            if (!code.equals("6001007")) {
                for (String name : erpNameList) {                   //对ERP数据按科目分类
                    List<Integer> costIdList = erpCodeMap.get(name);
                    List<ViewHsprFeesSerialStatic> dataList = new ArrayList<>();

                    for (ViewHsprFeesSerialStatic ErpData : commDataMap.get(commId)) {
                        if (costIdList.contains(ErpData.getCorpCostID())) {
                            dataList.add(ErpData);
                        }
                    }
                    dataMap.put(name, dataList);
                }
            } else {            //物业费手动分类


                List<ViewHsprFeesSerialStatic> yz = new ArrayList<>();      // "业主"
                List<ViewHsprFeesSerialStatic> kzf = new ArrayList<>();     // "空置房物业服务费"
                List<ViewHsprFeesSerialStatic> fcdf = new ArrayList<>();    // "房产代付物业服务费"
                List<ViewHsprFeesSerialStatic> fcbt = new ArrayList<>();    // "房产补贴物业服务费"
                List<ViewHsprFeesSerialStatic> dt = new ArrayList<>();      // "电梯运行服务费"
                List<ViewHsprFeesSerialStatic> qt = new ArrayList<>();      // "其他"

                for (ViewHsprFeesSerialStatic ErpData : commDataMap.get(commId)) {
                    if (ErpData.getCorpCostID() == 100000489 || ErpData.getCorpCostID() == 100000410 || ErpData.getCorpCostID() == 100000509) {
                        kzf.add(ErpData);
                    } else if (ErpData.getCustTypeName() == null) {
                        qt.add(ErpData);
                    } else {
                        if ("开发商".equals(ErpData.getCustTypeName())) {    //空置房
                            kzf.add(ErpData);
                        } else if (ErpData.getCustTypeName().contains("业主") || ErpData.getCustTypeName().contains("租户")) {
                            if ("房产代付".equals(ErpData.getWaiversCategory())) {     //房产代付
                                fcdf.add(ErpData);
                            } else if (ErpData.getStanName() != null && ErpData.getStanName().contains("电梯运行费")) {
                                dt.add(ErpData);
                            } else if (ErpData.getStanName() != null && ErpData.getStanName().contains("房产补贴")) {
                                fcbt.add(ErpData);
                            } else {
                                yz.add(ErpData);
                            }
                        } else {
                            qt.add(ErpData);
                        }
                    }
                }
                dataMap.put("业主物业费", yz);
                dataMap.put("房产代付物业费", fcdf);
                dataMap.put("空置房物业服务费", kzf);
                dataMap.put("房产补贴物业费", fcbt);
                dataMap.put("电梯运行服务费", dt);
                dataMap.put("其他", qt);
                dataMap.put("fc_withTax", null);    //房产代付含税 计算签证率
                dataMap.put("kzf_withTax", null);   //空置房含税 计算签证率
                dataMap.put("其中：空置房折扣优惠额", null);
                erpNameList = dataMap.keySet();
//                erpNameList.add("fc_withTax");
//                erpNameList.add("kzf_withTax");
            }
            returnMap.put(commId, calculate(year, startMonth, endMonth, erpNameList, dataMap, code));
        }
        return returnMap;
    }

    /*----------------------------------------ERP 收入部分  end-----------------------------------------------*/


    /*----------------------------------------拼接计算  start-------------------------------------------------*/

    //收入分类汇总
    private Map<Integer, Map<String, String>> calculate(Integer year, Integer startMonth, Integer
            endMonth, Set<String> erpNameList, Map<String, List<ViewHsprFeesSerialStatic>> dataMap, String code) {

        Map<Integer, Map<String, String>> returnMap = new HashMap<>();  //{月份：{科目：金额},  月份：{科目：金额}}
        for (int i = startMonth; i <= endMonth; i++) {
            Map<String, String> map = new HashMap<>();
            map.put("total", "0");
            map.put("totalWithTax", "0");
            returnMap.put(i, map);
        }

        Map<String, String> lastYearMap = new HashMap<>();  //去年累计
        Map<String, String> yearMap = new HashMap<>();  //所选期间的本年累计

        lastYearMap.put("total", "0");
        lastYearMap.put("totalWithTax", "0");
        returnMap.put(0, lastYearMap);

        yearMap.put("total", "0");
        yearMap.put("totalWithTax", "0");
        returnMap.put(13, yearMap);


        for (String name : erpNameList) {                   //构造返回数据结构
            for (Map<String, String> tmp : returnMap.values()) {    //构造返回数据结构
                tmp.put(name, "0");
            }
        }

        //查询对应科目的金额
        for (String name : erpNameList) {

            if (name.equals("fc_withTax") || name.equals("kzf_withTax") || name.equals("其中：空置房折扣优惠额")) {  //计算签证率的和优惠额的跳过
                continue;
            }

            List<ViewHsprFeesSerialStatic> dataList = dataMap.get(name);
            //分类处理
            for (ViewHsprFeesSerialStatic data : dataList) {
                //去年汇总
                if (data.getYear() == year - 1) {
                    data.setYear(year);
                    data.setMonth(0);
                }

                if ((data.getMonth() != 0 && data.getMonth() < startMonth) || data.getMonth() > endMonth) {
                    continue;
                }

                BigDecimal notIncludeTax = Optional.ofNullable(data.getNotIncludeTax()).orElse(new BigDecimal(0));
                BigDecimal includeTax = Optional.ofNullable(data.getIncludeTax()).orElse(new BigDecimal(0));

                if (data.getPay().equals("0")) {
                    notIncludeTax = notIncludeTax.subtract(Optional.ofNullable(data.getOffsetAmount()).orElse(new BigDecimal(0)));
                }


                Map<String, String> monthMap = returnMap.get(data.getMonth());

                String amount = monthMap.get(name);
                String total = monthMap.get("total");
                String totalWithTax = monthMap.get("totalWithTax");

                monthMap.put(name, new BigDecimal(amount).add(notIncludeTax).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                monthMap.put("total", new BigDecimal(total).add(notIncludeTax).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                monthMap.put("totalWithTax", new BigDecimal(totalWithTax).add(includeTax).setScale(2, BigDecimal.ROUND_HALF_UP).toString());

                if (code.equals("6001007")) {
                    BigDecimal feesRate = Optional.ofNullable(data.getFeesRate()).orElse(new BigDecimal(0));    //比率
                    BigDecimal offset = includeTax.multiply(BigDecimal.valueOf(1).subtract(feesRate));    //空置房折扣优惠额
                    String offsetAmount = monthMap.get("其中：空置房折扣优惠额");
                    monthMap.put("其中：空置房折扣优惠额", new BigDecimal(offsetAmount).add(offset).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                }
                if (name.equals("房产代付物业费")) {
                    String amountWithTax = monthMap.get("fc_withTax");
                    monthMap.put("fc_withTax", new BigDecimal(amountWithTax).add(includeTax).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                }
                if (name.equals("空置房物业服务费")) {
                    String amountWithTax = monthMap.get("kzf_withTax");
                    monthMap.put("kzf_withTax", new BigDecimal(amountWithTax).add(includeTax).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                }

                returnMap.put(data.getMonth(), monthMap);
            }
        }

        returnMap.forEach((key, value) -> {
            if (key != 0 && key != 13) {
                for (String name : value.keySet()) {
                    BigDecimal amount = new BigDecimal(yearMap.get(name)).add(new BigDecimal(value.get(name)));
                    yearMap.put(name, amount.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
                }
            }
        });
        return returnMap;
    }

    //收入汇总
    private List<InCome> calculateTotal(JSONObject jsonObject) {
        List<InCome> inComeList = new ArrayList<>();

        JSONObject estateManagement = jsonObject.getJSONObject("estateManagement");
        JSONObject communityService = jsonObject.getJSONObject("communityService");
        JSONObject marketingService = jsonObject.getJSONObject("marketingService");
        JSONObject siteService = jsonObject.getJSONObject("siteService");
        JSONObject other = jsonObject.getJSONObject("other");

        String year = jsonObject.getString("year");
        Integer start = jsonObject.getInteger("startMonth");
        Integer end = jsonObject.getInteger("endMonth");
        String commId = jsonObject.getString("commId");
        String organCode = jsonObject.getString("organCode");
        JSONObject area;

        if (commId != null) {
            area = getArea(new ArrayList<String>() {{
                add(commId);
            }}, Integer.parseInt(year), start, end).getJSONObject(commId);
        } else {
            area = getArea(null, Integer.parseInt(year), start, end).getJSONObject(commId);
        }

        for (int i = 0; i <= 13; i++) {

            if ((i != 0 && i < start) || (i != 13 && i > end)) {
                continue;
            }

            InCome in = new InCome();
            in.setMonth(String.valueOf(i));
            in.setYear(year);
            in.setCommId(commId);
            in.setOrganCode(organCode);
            //分类统计 - 不含税
            String eTotal = estateManagement.getJSONObject(String.valueOf(i)).getString("total");
            String cTotal = communityService.getJSONObject(String.valueOf(i)).getString("total");
            String mTotal = marketingService.getJSONObject(String.valueOf(i)).getString("total");
            String sTotal = siteService.getJSONObject(String.valueOf(i)).getString("total");
            String oTotal = other.getJSONObject(String.valueOf(i)).getString("total");
            String total = new BigDecimal(eTotal).add(new BigDecimal(cTotal)).add(new BigDecimal(mTotal)).add(new BigDecimal(sTotal)).add(new BigDecimal(oTotal)).setScale(2, BigDecimal.ROUND_HALF_UP).toString();

            //含税
            String eTotalWithTax = estateManagement.getJSONObject(String.valueOf(i)).getString("totalWithTax");
            String cTotalWithTax = communityService.getJSONObject(String.valueOf(i)).getString("totalWithTax");
            String mTotalWithTax = marketingService.getJSONObject(String.valueOf(i)).getString("totalWithTax");
            String sTotalWithTax = siteService.getJSONObject(String.valueOf(i)).getString("totalWithTax");
            String oTotalWithTax = other.getJSONObject(String.valueOf(i)).getString("totalWithTax");
            String totalWithTax = new BigDecimal(eTotalWithTax).add(new BigDecimal(cTotalWithTax)).add(new BigDecimal(mTotalWithTax)).add(new BigDecimal(sTotalWithTax)).add(new BigDecimal(oTotalWithTax)).setScale(2, BigDecimal.ROUND_HALF_UP).toString();


            in.setEstateManagement(estateManagement.getJSONObject(String.valueOf(i)));
            in.setCommunityService(communityService.getJSONObject(String.valueOf(i)));
            in.setMarketingService(marketingService.getJSONObject(String.valueOf(i)));
            in.setSiteService(siteService.getJSONObject(String.valueOf(i)));
            in.setOther(oTotal);
            in.setTotalWithTax(totalWithTax);
            in.setTotal(total);

            in.setTakeOverArea(area.getJSONObject(String.valueOf(i)).getString("takeOverArea"));
            in.setPayBeginArea(area.getJSONObject(String.valueOf(i)).getString("payBeginArea"));
            in.setActualArea(area.getJSONObject(String.valueOf(i)).getString("actualArea"));
            inComeList.add(in);
        }

        return inComeList;
    }

    //利润及分析指标计算
    private List<PLByMonth> calculateProfitAndAnalysis(JSONObject jsonObject) {
        List<PLByMonth> plByMonths = jsonObject.getObject("data", new TypeReference<List<PLByMonth>>() {
        });
        String year = jsonObject.getString("year");
        Integer startMonth = jsonObject.getInteger("startMonth");
        Integer endMonth = jsonObject.getInteger("endMonth");
        List<ErpNcData> erpNcDataList = jsonObject.getObject("erpNcDataList", new TypeReference<List<ErpNcData>>() {
        });
        Integer userType = jsonObject.getInteger("userType");


        Set<String> commIdOrOrganCodeList = new HashSet<>();
        if (userType == 1) {
            for (ErpNcData erpNcData : erpNcDataList) {
                commIdOrOrganCodeList.add(erpNcData.getOrganCode());
            }
        } else if (userType == 2) {
            for (ErpNcData erpNcData : erpNcDataList) {
                commIdOrOrganCodeList.add(erpNcData.getCommID());
            }
        }

        JSONObject NCInCome = mainInComeHandle(new ArrayList<>(commIdOrOrganCodeList), year, startMonth, endMonth, userType);    //NC主营业务收入
        JSONObject marketingServiceArray = NCInCome.getJSONObject("marketingService");
        JSONObject communityServiceArray = NCInCome.getJSONObject("communityService");
        JSONObject managementServiceArray = NCInCome.getJSONObject("managementService");
        JSONObject siteServiceArray = NCInCome.getJSONObject("siteService");

        String incomeMarketing;     //协销服务
        String incomeCommunity;     //社区增值服务
        String incomeManagement;    //物业管理服务
        String incomeSite;          //场地服务

        String ncIncomeMarketing = "0";     //协销服务
        String ncIncomeCommunity = "0";     //社区增值服务
        String ncIncomeManagement = "0";    //物业管理服务
        String ncIncomeSite = "0";          //场地服务

        String outcomeMarketing;        //协销服务
        String outcomeCommunityAndSite; //社区增值&场地服务
        String outcomeManagement;       //物业管理服务

        String takeOverArea;        //接管面积
        String payBeginArea;        //收费面积
        String actualArea;          //收楼面积

        String repairAmount;         //设施维护费 主营业务成本 设施维护费+设施维护费（委外）
        String clearAmount;          //清洁费  主营业务成本  清洁费+清洁费(委外)
        String afforestAmount;       //绿化费  主营业务成本  绿化费+绿化费(委外)
        String waterAmount;          //水费
        String electricAmount;       //电费

        JSONArray jsonArray = regionTaxOutInComeHandle(new ArrayList<>(commIdOrOrganCodeList), year, startMonth, endMonth, userType); //税金及附加，营业外收入
        Map<String, Map<String, String>> allYearTaxMap = new HashMap<>();
        Map<String, Map<String, String>> allYearOutIncomeMap = new HashMap<>();
        Map<String, String> taxMap = new HashMap<>();
        Map<String, String> outIncomeMap = new HashMap<>();

        // 处理税金及附加&其他业务收入 [start]
        List<JSONObject> jsonObjects;
        if (userType == 0) {
            jsonObjects = jsonArray.getJSONObject(0).getJSONArray("data").toJavaList(JSONObject.class);
            for (JSONObject object : jsonObjects) {
                taxMap.put(object.getString("month"), object.getString("tax"));
                outIncomeMap.put(object.getString("month"), object.getString("outIncome"));
            }
            allYearTaxMap.put("0", taxMap);
            allYearOutIncomeMap.put("0", outIncomeMap);
        } else if (userType == 1) {
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsObject = jsonArray.getJSONObject(i);
                if (!allYearTaxMap.keySet().contains(jsObject.getString("organCode"))) {
                    jsonObjects = jsObject.getJSONArray("data").toJavaList(JSONObject.class);
                    for (JSONObject object : jsonObjects) {
                        taxMap.put(object.getString("month"), object.getString("tax"));
                        outIncomeMap.put(object.getString("month"), object.getString("outIncome"));
                    }
                }
                allYearTaxMap.put(jsObject.getString("organCode"), taxMap);
                allYearOutIncomeMap.put(jsObject.getString("organCode"), outIncomeMap);
            }
        } else if (userType == 2) {
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsObject = jsonArray.getJSONObject(i);
                if (!allYearTaxMap.keySet().contains(jsObject.getString("commId"))) {
                    jsonObjects = jsObject.getJSONArray("data").toJavaList(JSONObject.class);
                    for (JSONObject object : jsonObjects) {
                        taxMap.put(object.getString("month"), object.getString("tax"));
                        outIncomeMap.put(object.getString("month"), object.getString("outIncome"));
                    }
                }
                allYearTaxMap.put(jsObject.getString("commId"), taxMap);
                allYearOutIncomeMap.put(jsObject.getString("commId"), outIncomeMap);
            }
        }
        //处理税金及附加&其他业务收入[end]


        BigDecimal incomeWithTax;       //含税收入 = 取含税的
        BigDecimal incomeWithOutTax;    //不含税收入 = 收入合计
        BigDecimal outcomeWithOutTax;   //不含税成本 = 成本合计
        BigDecimal profitWithTax;       //不含税毛利 = 上面两个差
        BigDecimal profitWithOutTax;    //税后净利润 = 税前利润（含年终奖） - 所得税费用 - 少数股东本期损益

        BigDecimal tax;                 //税金及附加 （subBalance）6403%
        BigDecimal outIncome;           //营业外收入 （subBalance）6301% - 6711%， 小于0用短横杠 “-” 代替

        BigDecimal profitWithOutBonus;  //税前利润（不含年终奖）= 不含税毛利 - 税金及附加 - 间接费用合计 - 减值损失 + 营业外收入 + 年终奖
        BigDecimal bonus;               //年终奖 = 三个成本里面的年终奖
        BigDecimal profitWithBonus;     //税前利润（含年终奖） = 上面两个的差
        BigDecimal incomeTax;           //所得税费用  = 上面那个*25%


        for (PLByMonth plByMonth : plByMonths) {
            InCome inCome = plByMonth.getInCome();
            JSONObject outCome = JSON.parseObject(plByMonth.getOutcome().toString());

            incomeMarketing = inCome.getMarketingService() == null ? "0" : JSON.parseObject(JSON.toJSONString(inCome.getMarketingService())).getString("total");
            incomeCommunity = inCome.getCommunityService() == null ? "0" : JSON.parseObject(JSON.toJSONString(inCome.getCommunityService())).getString("total");
            incomeManagement = inCome.getEstateManagement() == null ? "0" : JSON.parseObject(JSON.toJSONString(inCome.getEstateManagement())).getString("total");
            incomeSite = inCome.getSiteService() == null ? "0" : JSON.parseObject(JSON.toJSONString(inCome.getSiteService())).getString("total");


            outcomeCommunityAndSite = outCome.getJSONObject("mainCost") == null ? "0" : outCome.getJSONObject("mainCost").getString("communityService&siteService");
            outcomeManagement = outCome.getJSONObject("mainCost") == null ? "0" : outCome.getJSONObject("mainCost").getString("estateManagement");
            outcomeMarketing = outCome.getJSONObject("mainCost") == null ? "0" : outCome.getJSONObject("mainCost").getString("marketingService");


            takeOverArea = inCome.getTakeOverArea();
            payBeginArea = inCome.getPayBeginArea();
            actualArea = inCome.getActualArea();

            if (userType == 0) {
                tax = new BigDecimal(allYearTaxMap.get("0").get(plByMonth.getMonth()));
                outIncome = new BigDecimal(allYearOutIncomeMap.get("0").get(plByMonth.getMonth()));
            } else if (userType == 1) {
                tax = new BigDecimal(allYearTaxMap.get(plByMonth.getOrganCode()).get(plByMonth.getMonth()));
                outIncome = new BigDecimal(allYearOutIncomeMap.get(plByMonth.getOrganCode()).get(plByMonth.getMonth()));
            } else {
                tax = new BigDecimal(allYearTaxMap.get(plByMonth.getCommId()).get(plByMonth.getMonth()));
                outIncome = new BigDecimal(allYearOutIncomeMap.get(plByMonth.getCommId()).get(plByMonth.getMonth()));
            }

            /*-------------------------------计算利润部分  start-------------------------------*/
            incomeWithTax = new BigDecimal(inCome.getTotalWithTax() == null ? "0" : inCome.getTotalWithTax());
            incomeWithOutTax = new BigDecimal(inCome.getTotal() == null ? "0" : inCome.getTotal());
            outcomeWithOutTax = new BigDecimal(outCome.getJSONObject("mainCost").getString("mainCostTotal"));
            profitWithTax = incomeWithOutTax.subtract(outcomeWithOutTax);

            try {
                String mainCostBonus = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("其中：年终奖");
                String managementCostBonus = outCome.getJSONObject("managementCost").getJSONObject("managementCostDetail").getString("其中：年终奖");
                String saleCostBonus = outCome.getJSONObject("saleCost").getJSONObject("saleCostDetail").getString("其中：年终奖");
                bonus = new BigDecimal(mainCostBonus).add(new BigDecimal(managementCostBonus)).add(new BigDecimal(saleCostBonus));
            } catch (NullPointerException e) {
                bonus = new BigDecimal(0);
            }

            String indirectCost = outCome.getJSONObject("indirectCost") == null ? "0" : outCome.getJSONObject("indirectCost").getString("indirectCostTotal"); //间接费用合计
            profitWithOutBonus = profitWithTax.subtract(tax).subtract(new BigDecimal(indirectCost)).add(outIncome).add(bonus);
            profitWithBonus = profitWithOutBonus.subtract(bonus);
            incomeTax = profitWithBonus.multiply(new BigDecimal(0.25F));
            profitWithOutTax = profitWithBonus.subtract(incomeTax);

            Profit profit = new Profit();
            profit.setIncomeWithTax(incomeWithTax);
            profit.setIncomeWithOutTax(incomeWithOutTax);
            profit.setOutcomeWithOutTax(outcomeWithOutTax);
            profit.setProfitWithTax(profitWithTax);
            profit.setProfitWithOutTax(profitWithOutTax);

            profit.setTax(tax);
            profit.setOutIncome(outIncome);
            profit.setProfitOrLoss("0");
            profit.setProfitWithOutBonus(profitWithOutBonus);
            profit.setBonus(bonus);
            profit.setProfitWithBonus(profitWithBonus);
            profit.setIncomeTax(incomeTax);
            profit.setOwnerProfit("0");

            plByMonth.setProfit(profit);
            /*-------------------------------计算利润部分  end------------------------------------*/


            /*-------------------------------计算分析指标部分  start-------------------------------*/

            String rate;

            //确认收入率
            Map<String, Object> ncRate = new HashMap<>();
            ncRate.put("物业费", "0");
            ncRate.put("社区增值服务", "0");
            ncRate.put("协销服务费", "0");
            ncRate.put("场地管理服务费", "0");

            if (null != managementServiceArray.get(plByMonth.getCommId())) {
                ncIncomeManagement = managementServiceArray.getJSONObject(plByMonth.getCommId()).getString(plByMonth.getMonth());
                rate = NumberUtil.divide(ncIncomeManagement, incomeManagement, 4);
                ncRate.put("物业费", rate);
            }

            if (null != communityServiceArray.get(plByMonth.getCommId())) {
                ncIncomeCommunity = communityServiceArray.getJSONObject(plByMonth.getCommId()).getString(plByMonth.getMonth());
                rate = NumberUtil.divide(ncIncomeCommunity, incomeCommunity, 4);
                ncRate.put("社区增值服务", rate);
            }

            if (null != marketingServiceArray.get(plByMonth.getCommId())) {
                ncIncomeMarketing = marketingServiceArray.getJSONObject(plByMonth.getCommId()).getString(plByMonth.getMonth());
                rate = NumberUtil.divide(ncIncomeMarketing, incomeMarketing, 4);
                ncRate.put("协销服务费", rate);
            }

            if (null != siteServiceArray.get(plByMonth.getCommId())) {
                ncIncomeSite = siteServiceArray.getJSONObject(plByMonth.getCommId()).getString(plByMonth.getMonth());
                rate = NumberUtil.divide(ncIncomeSite, incomeSite, 4);
                ncRate.put("场地管理服务费", rate);
            }

            //NC主营业务收入
            Map<String, String> ncIncomeMap = new HashMap<>();
            String ncIncomeTotal = new BigDecimal(ncIncomeSite).add(new BigDecimal(ncIncomeCommunity)).add(new BigDecimal(ncIncomeMarketing)).add(new BigDecimal(ncIncomeManagement)).toString();
            ncIncomeMap.put("ncIncomeSite", ncIncomeSite);
            ncIncomeMap.put("ncIncomeCommunity", ncIncomeCommunity);
            ncIncomeMap.put("ncIncomeMarketing", ncIncomeMarketing);
            ncIncomeMap.put("ncIncomeManagement", ncIncomeManagement);
            ncIncomeMap.put("ncIncomeTotal", ncIncomeTotal);
            inCome.setNcIncome(ncIncomeMap);

            //毛利率
            Map<String, String> profitRate = new HashMap<>();
            if (!incomeManagement.equals("0")) {
                rate = NumberUtil.divide(outcomeManagement, incomeManagement, 4);
                profitRate.put("物业服务", rate);
            } else {
                profitRate.put("物业服务", "0");
            }
            if (!incomeMarketing.equals("0")) {
                rate = NumberUtil.divide(outcomeMarketing, incomeMarketing, 4);
                profitRate.put("协销服务", rate);
            } else {
                profitRate.put("协销服务", "0");
            }
            if (!incomeCommunity.equals("0") && !incomeSite.equals("0")) {
                rate = NumberUtil.divide(outcomeCommunityAndSite, incomeCommunity, 4);
                profitRate.put("社区增值 & 场地服务", rate);
            } else {
                profitRate.put("社区增值 & 场地服务", "0");
            }

            //签证率
            Map<String, String> visaRate = new HashMap<>();

            String fc_withTax = JSON.parseObject(JSON.toJSONString(inCome.getEstateManagement())).getString("fc_withTax");
            String fc_notWithTax = JSON.parseObject(JSON.toJSONString(inCome.getEstateManagement())).getString("房产代付物业费");
            String kzf_withTax = JSON.parseObject(JSON.toJSONString(inCome.getEstateManagement())).getString("kzf_withTax");
            String kzf_notWithTax = JSON.parseObject(JSON.toJSONString(inCome.getEstateManagement())).getString("空置房物业服务费");
            String xx_withTax = JSON.parseObject(JSON.toJSONString(inCome.getMarketingService())).getString("totalWithTax");
            String xx_notWithTax = JSON.parseObject(JSON.toJSONString(inCome.getMarketingService())).getString("total");

            visaRate.put("房产代付", NumberUtil.divide(fc_notWithTax, fc_withTax, 2));
            visaRate.put("空置房", NumberUtil.divide(kzf_notWithTax, kzf_withTax, 2));
            visaRate.put("协销服务", NumberUtil.divide(xx_notWithTax, xx_withTax, 2));
            visaRate.put("合计", NumberUtil.divide(NumberUtil.add(fc_notWithTax, kzf_notWithTax, xx_notWithTax), NumberUtil.add(fc_withTax, kzf_withTax, xx_withTax), 2));
            //协销服务      暂时不处理
            Map<String, String> marketing = new HashMap<>();
            marketing.put("案场服务物业编制人数", "0");
            marketing.put("案场服务人均创收收入", "0");

            //收楼率
            Map<String, String> collectRate = new HashMap<>();
            collectRate.put("收楼率", NumberUtil.divide(actualArea, payBeginArea, 2));
            collectRate.put("收缴率(本月)", NumberUtil.divide(inCome.getTotal(), inCome.getTotalWithTax(), 2));
            collectRate.put("收缴率(本年)", NumberUtil.divide(inCome.getTotal(), inCome.getTotalWithTax(), 2));

            //人均
            String number = "0";   //暂无

            String rgcb = "0";
            String stsz = "0";
            String sssz = "0";
            try {
                rgcb = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("人工成本(含社保、公积金)");
                stsz = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("食堂收支");
                sssz = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("宿舍收支");
            } catch (NullPointerException e) {
                // TODO:
            }
            Map<String, String> AveragePerPerson = new HashMap<>();
            AveragePerPerson.put("平均总人数", number);
            AveragePerPerson.put("平均一线人数", number);
            AveragePerPerson.put("元均创收(总收入/人工成本)", NumberUtil.divide(profit.getIncomeWithOutTax().toString(), rgcb, 4));
            AveragePerPerson.put("人均接管收费面积", NumberUtil.divide(payBeginArea, number, 4));
            AveragePerPerson.put("人均创收收入", NumberUtil.divide(profit.getIncomeWithOutTax().toString(), number, 4));
            AveragePerPerson.put("人均人工成本", NumberUtil.divide(rgcb, number, 4));
            AveragePerPerson.put("人均食堂收支", NumberUtil.divide(stsz, number, 4));
            AveragePerPerson.put("人均宿舍收支", NumberUtil.divide(sssz, number, 4));
            AveragePerPerson.put("月平均单价", "0.42");
            AveragePerPerson.put("人均毛利利润", NumberUtil.divide(profitWithTax.toString(), number, 4));


            //每平方米成本
            Map<String, String> AverageOutcomePerSquare = new HashMap<>();
            repairAmount = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("设施维护费");
            clearAmount = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("清洁费");
            afforestAmount = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("绿化费");
            waterAmount = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("水费");
            electricAmount = outCome.getJSONObject("mainCost").getJSONObject("mainCostDetail").getString("电费");


            AverageOutcomePerSquare.put("已接管每平方米的设施维护费", NumberUtil.divide(repairAmount, payBeginArea, 2));
            AverageOutcomePerSquare.put("已接管每平方米的清洁费", NumberUtil.divide(clearAmount, payBeginArea, 2));
            AverageOutcomePerSquare.put("已接管每平方米的绿化费", NumberUtil.divide(afforestAmount, payBeginArea, 2));
            AverageOutcomePerSquare.put("已接管每平方米的水费", NumberUtil.divide(waterAmount, payBeginArea, 2));
            AverageOutcomePerSquare.put("已接管每平方米的电费", NumberUtil.divide(electricAmount, payBeginArea, 2));

            //每平方米收入
            Map<String, String> AverageIncomePerSquare = new HashMap<>();
            String management = NumberUtil.divide(incomeManagement, payBeginArea, 4);
            AverageIncomePerSquare.put("物业管理", management);
            AverageIncomePerSquare.put("开荒绿化养护", "0");

            Analysis analysis = new Analysis();
            analysis.setCollectRate(collectRate); // 暂不处理  todo
            analysis.setProfitRate(profitRate);
            analysis.setIncomeRate(ncRate);

            analysis.setVisaRate(visaRate);
            analysis.setAverageIncomePerSquare(AverageIncomePerSquare);
            analysis.setAverageOutcomePerSquare(AverageOutcomePerSquare);
            analysis.setAveragePerson(AveragePerPerson);   //没有总人数 暂时不处理
            analysis.setMarketing(marketing);
            plByMonth.setAnalysis(analysis);
            /*-------------------------------计算分析指标部分  end-------------------------------*/
        }

        return plByMonths;
    }

    //构建返回对象 PLByMonth
    private List<PLByMonth> buildingPLByMonth(JSONObject jsonObject) {
        List<InCome> inComeList = jsonObject.getObject("incomes", new TypeReference<List<InCome>>() {
        });
        List<OutCome> outComeList = jsonObject.getObject("outcomes", new TypeReference<List<OutCome>>() {
        });
        Integer userType = jsonObject.getInteger("userType");
        Integer buildingType = jsonObject.getInteger("buildingType");

        List<PLByMonth> tmpPLByMonthList = new ArrayList<>();
        List<PLByMonth> plByMonthList = new ArrayList<>();

        List<ErpNcData> erpNcDataList = jsonObject.getObject("erpNcDataList", new TypeReference<List<ErpNcData>>() {
        });
        switch (userType) {
            case 0: {           //总部
                for (InCome inCome : inComeList) {
                    PLByMonth pl = new PLByMonth();
                    for (OutCome outCome : outComeList) {
                        if (outCome.getMonth().equals(inCome.getMonth())) {
                            pl.setInCome(inCome);
                            pl.setOutcome(outCome);
                            pl.setYear(inCome.getYear());
                            pl.setMonth(inCome.getMonth());
                            pl.setProjectName("总部");

                            tmpPLByMonthList.add(pl);
                        }
                    }
                }
                break;
            }
            case 1: {        //区域
                for (InCome inCome : inComeList) {
                    PLByMonth pl = new PLByMonth();
                    for (OutCome outCome : outComeList) {
                        if (inCome.getOrganCode().equals(outCome.getOrganCode()) &&
                                (inCome.getCommId() == null ? inCome.getCommId() == outCome.getCommId() : inCome.getCommId().equals(outCome.getCommId()))
                                && inCome.getMonth().equals(outCome.getMonth())) {
                            pl.setInCome(inCome);
                            pl.setOutcome(outCome);
                            pl.setYear(inCome.getYear());
                            pl.setMonth(inCome.getMonth());
                            pl.setOrganCode(inCome.getOrganCode());
                            pl.setCommId(inCome.getCommId());
                            for (ErpNcData erpNcData : erpNcDataList) {
                                if (inCome.getOrganCode().equals(erpNcData.getOrganCode())) {
                                    pl.setProjectName(erpNcData.getOrganName());
                                }
                            }

                            tmpPLByMonthList.add(pl);
                        }
                    }
                }
                break;
            }
            case 2: {        //项目
                for (InCome inCome : inComeList) {
                    PLByMonth pl = new PLByMonth();
                    for (OutCome outCome : outComeList) {
                        if (inCome.getCommId().equals(outCome.getCommId()) && inCome.getMonth().equals(outCome.getMonth())) {
                            pl.setInCome(inCome);
                            pl.setOutcome(outCome);
                            pl.setYear(inCome.getYear());
                            pl.setMonth(inCome.getMonth());
                            pl.setCommId(inCome.getCommId());
                            pl.setOrganCode(inCome.getOrganCode());
                            for (ErpNcData erpNcData : erpNcDataList) {
                                if (inCome.getCommId().equals(erpNcData.getCommID())) {
                                    pl.setProjectName(erpNcData.getPropertyProject());
                                }
                            }

                            tmpPLByMonthList.add(pl);
                        }
                    }
                }
            }
            default:
                break;
        }

        if (tmpPLByMonthList.size() != 0) {
            jsonObject.remove("incomes");
            jsonObject.remove("outcomes");
            jsonObject.put("data", tmpPLByMonthList);
            plByMonthList = calculateProfitAndAnalysis(jsonObject);

        }
        return plByMonthList;
    }

    //构建返回对象 PLByProject
    private List<PLByProject> buildingPLByProject(JSONObject jsonObject, String organCodePara, String commId) {
        List<PLByProject> returnList = new ArrayList<>();
        String year = jsonObject.getString("year");
        Integer userType = jsonObject.getInteger("userType");
        List<PLByMonth> plByMonthList = getPLByMonth(organCodePara, commId, year, 1, 12, userType, 2);
        List<String> commIdList = jsonObject.getObject("commIdList", new TypeReference<List<String>>() {
        });
        Map<String, String> codeMap = jsonObject.getObject("codeMap", new TypeReference<Map<String, String>>() {
        });
        if (commIdList.size() == 0)
            commIdList = null;

        List<Project> projectList = tbHsprCommunityMapper.selectProjectBeanByCommIdList(commIdList);

        Map<String, List<OutCome>> regionCostList = regionDataForPLByProjectRow(codeMap, year);

        List<Reason> reasonList = reasonMapper.selectReasonsByYear(year, organCodePara);

        List<ProjectProfit> projectProfits = new ArrayList<>();
        for (PLByMonth plByMonth : plByMonthList) {

            PLByProject plByProject = new PLByProject();
            plByProject.setYear(plByMonth.getYear());
            plByProject.setMonth(plByMonth.getMonth());
            plByProject.setInCome(plByMonth.getInCome());
            plByProject.setOutcome(plByMonth.getOutcome());
            plByProject.setProfit(plByMonth.getProfit());
            plByProject.setAnalysis(plByMonth.getAnalysis());

            for (Project project : projectList) {
                if (plByMonth.getCommId().equals(project.getCommID())) {
                    plByProject.setProject(project);
                    plByProject.setOrganCode(project.getOrganCode());
                    plByProject.setCommId(project.getCommID());
                    plByProject.setProjectName(project.getCommName());
                }
            }

            if (plByProject.getProject() == null) {
                continue;
            }

            plByProject.setReason(new Reason());

            for (Reason reason : reasonList) {
                if (plByMonth.getCommId().equals(reason.getCommId()) && plByMonth.getYear().equals(reason.getYear()) && plByMonth.getMonth().equals(reason.getMonth())) {
                    plByProject.setReason(reason);
                    break;
                }
            }

            for (String organCode : regionCostList.keySet()) {
                if (plByProject.getProject().getOrganCode().equals(organCode)) {
                    for (OutCome outCome : regionCostList.get(organCode)) {
                        if (outCome.getMonth().equals(plByProject.getMonth())) {
                            plByProject.setAreaCost(outCome);
                        }
                    }
                }
            }

            OutCome areaCost = plByProject.getAreaCost();
            String inComeWithOutTax = plByProject.getProfit().getIncomeWithOutTax().toString();     //不含税收入
            String ncIncomeTotal = JSON.parseObject(JSON.toJSONString(plByProject.getInCome().getNcIncome())).getString("ncIncomeTotal"); //nc主营业务收入合计
            String ncOutcomeTotal = JSON.parseObject(JSON.toJSONString(plByProject.getOutcome().getMainCost())).getString("mainCostTotal");  //主营业务成本合计
            String saleCost = JSON.parseObject(JSON.toJSONString(plByProject.getOutcome().getSaleCost())).getString("saleCostTotal"); //销售费用
            String managementCost = JSON.parseObject(JSON.toJSONString(plByProject.getOutcome().getManagementCost())).getString("managementCost");    //管理费用
            String financialCost = JSON.parseObject(JSON.toJSONString(plByProject.getOutcome().getFinancialCost())).getString("financialCostTotal");    //财务费用
            String tax = plByProject.getProfit().getTax().toString();   //税金及附加
            String outIncome = plByProject.getProfit().getOutIncome().toString();    //营业外收入

            String areaCostTotal = areaCost == null ? "0" : JSON.parseObject(JSON.toJSONString(areaCost.getMainCost())).getString("mainCostTotal"); //区域成本合计
            String areaSaleCost = areaCost == null ? "0" : JSON.parseObject(JSON.toJSONString(areaCost.getSaleCost())).getString("saleCostTotal");   //区域销售费用
            String areaManagementCost = areaCost == null ? "0" : JSON.parseObject(JSON.toJSONString(areaCost.getManagementCost())).getString("managementCostTotal"); //区域管理费用

            String TTLGPAmount = NumberUtil.add(inComeWithOutTax, "-" + ncOutcomeTotal, "-" + areaCostTotal);

            String TTLGPPercent = NumberUtil.divide(TTLGPAmount, inComeWithOutTax, 4);

            String ContributionAmount = NumberUtil.add(TTLGPAmount, "-" + saleCost, "-" + managementCost, "-" + tax, "-" + financialCost, outIncome, "-" + areaManagementCost, "-" + areaSaleCost);

            Double ContributionPercent = new BigDecimal(NumberUtil.divide(ContributionAmount, inComeWithOutTax, 4)).doubleValue();

            String Analysis;

            if (ContributionPercent <= 0) {
                Analysis = "Negative";
            } else if (ContributionPercent < 0.10) {
                Analysis = "0~<10%";
            } else if (ContributionPercent < 0.15) {
                Analysis = "10%~<15%";
            } else if (ContributionPercent < 0.30) {
                Analysis = "15%~<30%";
            } else if (ContributionPercent < 0.50) {
                Analysis = "30%~<50%";
            } else {
                Analysis = "50% or above";
            }

            String inComeDiff = NumberUtil.add(inComeWithOutTax, "-" + ncIncomeTotal);

            String inComeDiffRate = NumberUtil.divide(inComeDiff, inComeWithOutTax, 4);

            plByProject.setTTLGPAmount(TTLGPAmount);
            plByProject.setTTLGPPercent(TTLGPPercent);
            plByProject.setContributionAmount(ContributionAmount);
            plByProject.setContributionAmountPercent(String.valueOf(ContributionPercent));
            plByProject.setAnalaysis(Analysis);
            plByProject.setInComeDiff(inComeDiff);
            plByProject.setInComeDiffRate(inComeDiffRate);


            ProjectProfit projectProfit = new ProjectProfit();
            projectProfit.setCommId(plByProject.getCommId());
            projectProfit.setOrganCode(plByProject.getOrganCode());
            projectProfit.setProjectName(plByProject.getProjectName());
            projectProfit.setOrganName(plByProject.getProject().getCommName());
            projectProfit.setYear(plByProject.getYear());
            projectProfit.setMonth(plByProject.getMonth());
            projectProfit.setItem(plByProject.getAnalaysis());
            projectProfit.setProjectStage(plByProject.getProject().getDevelopmentState());
            projectProfit.setIncomeWithOutTax(inComeWithOutTax);
            projectProfit.setProfit(ContributionAmount);
            projectProfit.setProfitRate(String.valueOf(ContributionPercent));

            projectProfits.add(projectProfit);

            returnList.add(plByProject);
        }
        saveProjectProfitList(projectProfits);      //保存
        return returnList;
    }

    /*----------------------------------------拼接计算  end-------------------------------------------------*/


    /*-------------------------------------------获取其他信息   start---------------------------------------------------*/
    //区域成本
    private Map<String, List<OutCome>> regionDataForPLByProjectRow(Map<String, String> codoMap, String year) {
        int startMonth = 1;
        int monthSize = 11;
        Map<String, List<String>> organMap = new HashMap<>();
        Map<String, List<OutCome>> returnMap = new HashMap<>();
        for (Map.Entry<String, String> entry : codoMap.entrySet()) {
            if (!organMap.containsKey(entry.getValue())) {
                List<String> tempList = new ArrayList<>();
                tempList.add(entry.getKey());
                organMap.put(entry.getValue(), tempList);
            } else
                organMap.get(entry.getValue()).add(entry.getKey());
        }
        //获取每个区域的成本数据（assValue包含 区域办公室）
        for (Map.Entry<String, List<String>> entry : organMap.entrySet()) {
            List<OutCome> tempList = new ArrayList<>();
            System.out.println(entry.getKey() + ":" + entry.getValue());
            List<Subbalance> subbalanceList = subbalanceMapper.selectRegionCostForProjectRow(entry.getValue(), year);               //获取该区域全年成本数据

            //根据月份索引和月份长度获取区间月份
            List<List<Subbalance>> monthClassifyList = FinancialUtil.monthClassifyByMonthSize(subbalanceList, startMonth, monthSize);
            for (int i = 0; i <= monthSize; i++) {
                //对区间每个月份的数据进行处理
                tempList.add(FinancialUtil.RegionDataForPLByProjectRow(
                        monthClassifyList.get(i), year, String.valueOf(startMonth + i), entry.getKey(), 1));
            }
            //处理成本年度数据
            tempList.add(FinancialUtil.RegionDataForPLByProjectRow(subbalanceList, year, "13", entry.getKey(), 1));
            //添加进入Map中
            returnMap.put(entry.getKey(), tempList);
        }
        return returnMap;
    }

    //NC主营业务收入 数据获取
    private JSONObject mainInComeHandle(List<String> commIdOrOrganCodeList, String year, int startMonth,
                                        int endMonth, int judgeIdOrCode) {
        String codo;
        String commId;
        int monthSize = endMonth - startMonth;
        JSONObject returnJsonObject = new JSONObject();        //返回数据
        returnJsonObject.put("managementService", new JSONObject());
        returnJsonObject.put("siteService", new JSONObject());
        returnJsonObject.put("marketingService", new JSONObject());
        returnJsonObject.put("communityService", new JSONObject());

        String lastYear = String.valueOf(Integer.valueOf(year) - 1);
        //judgeIdOrCode 0总部 1区域 2项目
        if (judgeIdOrCode == 1) {
            //分别获取各区域的数据
            for (String organCode : commIdOrOrganCodeList) {
                List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectRegionByOrganCode(organCode);
                List<String> codoList = new ArrayList<>();
                for (ErpNcData erpNcData : erpNcDataList) {
                    codo = erpNcData.getNCSystemNumber() + "-0001";
                    if (codoList.contains(codo)) {
                        //对erpNcDataList账簿编号进行去重
                        continue;
                    }
                    codoList.add(codo);
                }
                if (codoList.size() != 0) {
                    List<Subbalance> subbalanceList = subbalanceMapper.selectRegionInCome(codoList, year);
                    List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectRegionInCome(codoList, lastYear);
                    System.out.println(organCode);
                    FinancialUtil.mainInComeHandle(returnJsonObject, subbalanceList, subbalanceLastYearList, startMonth, monthSize, organCode);
                }
            }
        } else if (judgeIdOrCode == 2) {
            //分别获取各区域的数据
            List<String> oneToManyCodos = tbHsprCommunityMapper.selectCodoOneToMany();
            List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectProjectByCommIdList(commIdOrOrganCodeList);
            List<String> codoList = new ArrayList<>();
            for (ErpNcData erpNcData : erpNcDataList) {
                codo = erpNcData.getNCSystemNumber() + "-0001";
                if (codoList.contains(codo)) {
                    //对erpNcDataList账簿编号进行去重
                    continue;
                }
                codoList.add(codo);
                if (oneToManyCodos.contains(codo)) {
                    List<Subbalance> subbalanceList = subbalanceMapper.selectProjectInComeForMany(codo, year);
                    List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectProjectInComeForMany(codo, lastYear);
                    List<CommIdMatchCodo> commIdMatchCodoList = commIdMatchCodoMapper.selectCommIdMatchCodoByCodo(codo);
                    List<CommIdMatchCodo> commIdMatchCodos = new ArrayList<>();
                    for (CommIdMatchCodo commIdMatchCodo : commIdMatchCodoList) {
                        if (commIdOrOrganCodeList.contains(commIdMatchCodo.getCommId())) {
                            commIdMatchCodos.add(commIdMatchCodo);
                        }
                    }
                    List<Map<String, ArrayList<Subbalance>>> returnList = FinancialUtil.financialDataHandleForTaxOutInCome(subbalanceList, subbalanceLastYearList, commIdMatchCodos);
                    Map<String, ArrayList<Subbalance>> tempMap = returnList.get(0);    //键 commid
                    Map<String, ArrayList<Subbalance>> tempLastYearMap = returnList.get(1);    //键 commid
                    for (CommIdMatchCodo commIdMatchCodo : commIdMatchCodos) {
                        commId = commIdMatchCodo.getCommId();
                        FinancialUtil.mainInComeHandle(returnJsonObject, tempMap.get(commId), tempLastYearMap.get(commId), startMonth, monthSize, commId);
                    }
                } else {//账簿编码一对一项目时
                    List<Subbalance> subbalanceList = subbalanceMapper.selectProjectInComeForOne(codo, year);
                    List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectProjectInComeForOne(codo, lastYear);
                    FinancialUtil.mainInComeHandle(returnJsonObject, subbalanceList, subbalanceLastYearList, startMonth, monthSize, erpNcData.getCommID());
                }
            }
        } else {
            List<Subbalance> subbalanceList = subbalanceMapper.selectHeadquartersInCome(year);
            List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectHeadquartersInCome(lastYear);
            FinancialUtil.mainInComeHandle(returnJsonObject, subbalanceList, subbalanceLastYearList, startMonth, monthSize, null);
        }
        return returnJsonObject;
    }

    //税金及附加
    private JSONArray regionTaxOutInComeHandle(List<String> commIdOrOrganCodeList, String year, int startMonth,
                                               int endMonth, int judgeIdOrCode) {
        String codo;
        String commId;
        int monthSize = endMonth - startMonth;
        String lastYear = String.valueOf(Integer.valueOf(year) - 1);
        JSONArray returnArray = new JSONArray();        //返回数据
        //judgeIdOrCode 0总部 1区域 2项目
        if (judgeIdOrCode == 1) {
            //分别获取各区域的数据
            for (String organCode : commIdOrOrganCodeList) {
                List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectRegionByOrganCode(organCode);
                List<String> codoList = new ArrayList<>();
                for (ErpNcData erpNcData : erpNcDataList) {
                    codo = erpNcData.getNCSystemNumber() + "-0001";
                    if (codoList.contains(codo)) {
                        //对erpNcDataList账簿编号进行去重
                        continue;
                    }
                    codoList.add(codo);
                }
                if (codoList.size() != 0) {
                    List<Subbalance> subbalanceList = subbalanceMapper.selectRegionTaxOutInCome(codoList, year);
                    List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectRegionTaxOutInCome(codoList, lastYear);
                    System.out.println(organCode);
                    returnArray.add(FinancialUtil.financialDataHandleByOrganCode(subbalanceList, subbalanceLastYearList, year, startMonth, monthSize, organCode, judgeIdOrCode));
                }
            }
        } else if (judgeIdOrCode == 2) {
            //分别获取各区域的数据
            List<String> oneToManyCodos = tbHsprCommunityMapper.selectCodoOneToMany();
            List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectProjectByCommIdList(commIdOrOrganCodeList);
            List<String> codoList = new ArrayList<>();
            for (ErpNcData erpNcData : erpNcDataList) {
                codo = erpNcData.getNCSystemNumber() + "-0001";
                if (codoList.contains(codo)) {
                    //对erpNcDataList账簿编号进行去重
                    continue;
                }
                codoList.add(codo);
                List<Subbalance> subbalanceList = subbalanceMapper.selectProjectTaxOutInCome(codo, year);
                List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectProjectTaxOutInCome(codo, lastYear);
                if (oneToManyCodos.contains(codo)) {
                    List<CommIdMatchCodo> commIdMatchCodoList = commIdMatchCodoMapper.selectCommIdMatchCodoByCodo(codo);
                    List<CommIdMatchCodo> commIdMatchCodos = new ArrayList<>();
                    for (CommIdMatchCodo commIdMatchCodo : commIdMatchCodoList) {
                        if (commIdOrOrganCodeList.contains(commIdMatchCodo.getCommId())) {
                            commIdMatchCodos.add(commIdMatchCodo);
                        }
                    }
                    List<Map<String, ArrayList<Subbalance>>> returnList = FinancialUtil.financialDataHandleForTaxOutInCome(subbalanceList, subbalanceLastYearList, commIdMatchCodos);
                    Map<String, ArrayList<Subbalance>> tempMap = returnList.get(0);    //键 commid
                    Map<String, ArrayList<Subbalance>> tempLastYearMap = returnList.get(1);    //键 commid
                    for (CommIdMatchCodo commIdMatchCodo : commIdMatchCodos) {
                        commId = commIdMatchCodo.getCommId();
                        returnArray.add(FinancialUtil.financialDataHandleByOrganCode(tempMap.get(commId), tempLastYearMap.get(commId), year, startMonth, monthSize, commId, judgeIdOrCode));
                    }
                } else {//账簿编码一对一项目时
                    returnArray.add(FinancialUtil.financialDataHandleByOrganCode(subbalanceList, subbalanceLastYearList, year, startMonth, monthSize, erpNcData.getCommID(), judgeIdOrCode));
                }
            }
        } else {
            List<Subbalance> subbalanceList = subbalanceMapper.selectHeadquartersTaxOutInCome(year);
            List<Subbalance> subbalanceLastYearList = subbalanceMapper.selectHeadquartersTaxOutInCome(lastYear);
            returnArray.add(FinancialUtil.financialDataHandleByOrganCode(subbalanceList, subbalanceLastYearList, year, startMonth, monthSize, null, judgeIdOrCode));
        }
        return returnArray;
    }

    //面积
    public JSONObject getArea(List<String> commIdList, Integer year, Integer startMonth, Integer
            endMonth) {
        JSONObject returnObject = new JSONObject();
        Map<String, List<AreaData>> takeOverAreaMap = new HashMap<>();   //接管面积
        Map<String, List<AreaData>> payBeginAreaMap = new HashMap<>();   //收费
        Map<String, List<AreaData>> actualAreaMap = new HashMap<>(); //已收楼

        String startDate = "'" + String.valueOf(year - 1) + "-01-01" + "'"; //@Deprecated
        String endDate = "'" + String.valueOf(year + 1) + "-01-01" + "'";

        if (commIdList == null || commIdList.size() == 0) {
            commIdList = null;
        }

        //TakeOverDate 已接管，PayBeginDate 收费，ActualSubDate 已收楼
        List<AreaData> takeOverAreaList = viewHsprFeesSerialStaticMapper.selectArea(commIdList, startDate,
                endDate, "TakeOverDate", null, null);
        List<AreaData> payBeginAreaList = viewHsprFeesSerialStaticMapper.selectArea(commIdList, startDate,
                endDate, "PayBeginDate", null, null);
        List<AreaData> actualAreaList = viewHsprFeesSerialStaticMapper.selectArea(commIdList, startDate,
                endDate, "ActualSubDate", null, null);


        for (AreaData areaData : takeOverAreaList) {                      //以前的累计值
            if (areaData.getYear() <= (year - 1)) areaData.setMonth(0);
        }
        for (AreaData areaData : payBeginAreaList) {
            if (areaData.getYear() <= (year - 1)) areaData.setMonth(0);
        }
        for (AreaData areaData : actualAreaList) {
            if (areaData.getYear() <= (year - 1)) areaData.setMonth(0);
        }

        if (commIdList == null) {
            takeOverAreaMap.put(null, takeOverAreaList);
            payBeginAreaMap.put(null, payBeginAreaList);
            actualAreaMap.put(null, actualAreaList);
            commIdList = new ArrayList<>();
            commIdList.add(null);
        } else {
            takeOverAreaMap = takeOverAreaList.stream().collect(Collectors.groupingBy(AreaData::getCommID));
            payBeginAreaMap = payBeginAreaList.stream().collect(Collectors.groupingBy(AreaData::getCommID));
            actualAreaMap = actualAreaList.stream().collect(Collectors.groupingBy(AreaData::getCommID));

            for (String s : commIdList) {

                if (takeOverAreaMap.get(s) == null) {
                    takeOverAreaMap.put(s, new ArrayList<>());
                }
                if (payBeginAreaMap.get(s) == null) {
                    payBeginAreaMap.put(s, new ArrayList<>());
                }
                if (actualAreaMap.get(s) == null) {
                    actualAreaMap.put(s, new ArrayList<>());
                }
            }
        }


        for (String commId : commIdList) {

            Map<Integer, Map<String, String>> dataMap = new HashMap<>();    //1月：{takeOver:222,paybegin:222,actual:222},
            for (int i = 0; i < 14; i++) {
                Map<String, String> tmp = new HashMap<>();
                tmp.put("takeOverArea", "0");
                tmp.put("payBeginArea", "0");
                tmp.put("actualArea", "0");
                dataMap.put(i, tmp);
            }

            String takeOver = "0";
            String payBegin = "0";
            String actual = "0";


            Map<Integer, Double> takeOverValues = takeOverAreaMap.get(commId).stream().collect(Collectors.groupingBy(AreaData::getMonth, Collectors.summingDouble(x -> ((AreaData) x).getArea().doubleValue())));
            Map<Integer, Double> payBeginValues = payBeginAreaMap.get(commId).stream().collect(Collectors.groupingBy(AreaData::getMonth, Collectors.summingDouble(x -> ((AreaData) x).getArea().doubleValue())));
            Map<Integer, Double> actualValues = actualAreaMap.get(commId).stream().collect(Collectors.groupingBy(AreaData::getMonth, Collectors.summingDouble(x -> ((AreaData) x).getArea().doubleValue())));

            for (Map.Entry<Integer, Map<String, String>> e : dataMap.entrySet()) {
                takeOver = new BigDecimal(takeOver).add(new BigDecimal(Optional.ofNullable(takeOverValues.get(e.getKey())).orElse(0.0d))).setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString();
                payBegin = new BigDecimal(payBegin).add(new BigDecimal(Optional.ofNullable(payBeginValues.get(e.getKey())).orElse(0.0d))).setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString();
                actual = new BigDecimal(actual).add(new BigDecimal(Optional.ofNullable(actualValues.get(e.getKey())).orElse(0.0d))).setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString();

                e.getValue().put("takeOverArea", takeOver);
                e.getValue().put("payBeginArea", payBegin);
                e.getValue().put("actualArea", actual);
            }
            Map<String, String> totalMap = dataMap.get(12);
            dataMap.put(13, totalMap);
            returnObject.put(commId, dataMap);
        }
        return returnObject;
    }

    // 利润信息保存方法
    private void saveProjectProfitList(List<ProjectProfit> projectProfits) {
        for (ProjectProfit projectProfit : projectProfits) {
            String id = MD5.GetMD5Code(projectProfit.getCommId() + projectProfit.getYear() + projectProfit.getMonth());
            projectProfit.setProjectId(id);
            projectProfitMapper.insertOrUpdateProjectProfit(projectProfit);
        }
    }
    /*-------------------------------------------获取其他信息   end---------------------------------------------------*/


    /*-------------------------------------------excel导出方法   start---------------------------------------------------*/
    //P&L 按项目利润汇总 导出方法
    private void exportProjectProfitData(HttpServletResponse response, String organCode, String year) {
        JSONObject tempJson;
        InputStream inputstream;
        OutputStream output;
        Workbook workBook = null;
        Row tempRow;
        String organName;
        //获取工作簿模板

        File file = null;
        try {
            file = ResourceUtils.getFile("classpath:excel" + File.separator + "ncFinancial" + File.separator + "PLByProfit.xlsx");
            inputstream = new FileInputStream(file);
            // 获取模板
            workBook = new XSSFWorkbook(inputstream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取模板页面
        Sheet sheet = workBook.getSheetAt(0);
        //获取PL按项目利润汇总 区域数据
        JSONObject projectProfitJson = getPLByProfit(organCode, year);
        for (int i = 1; i < 14; i++) {      //按照每个月放入数据 第一个月为第7行 年度汇总为13
            tempJson = projectProfitJson.getJSONObject(String.valueOf(i));
            List<ProjectProfitDetail> projectProfitDetailList = (List<ProjectProfitDetail>) tempJson.get("projectProfitDetailList");
            int tempInt = 0;
            if(i == 13) {
                //获取区域名称
                organName = projectProfitDetailList.get(0).getOrganName();
                //区域名称对应行的单元格 放入区域名称
                sheet.getRow(1).getCell(1).setCellValue(organName);
            }
            //放入该月每个Item对应的数据
            for (ProjectProfitDetail projectProfitDetail : projectProfitDetailList) {
                tempRow = sheet.getRow(7 * i - 1 + tempInt);
                if (tempRow == null) continue;     //获取空白行则略过该行
                tempRow.getCell(3).setCellValue(projectProfitDetail.getRoutineCount());
                tempRow.getCell(4).setCellValue(projectProfitDetail.getCaseCount());
                tempRow.getCell(5).setCellValue(projectProfitDetail.getRoutineCaseCount());
                tempRow.getCell(6).setCellValue(projectProfitDetail.getProjectCount());
                if (projectProfitDetail.getIncomeWithOutTax() != null)
                    tempRow.getCell(7).setCellValue(projectProfitDetail.getIncomeWithOutTax().doubleValue());
                else tempRow.getCell(7).setCellValue(0d);
                if (projectProfitDetail.getProfit() != null)
                    tempRow.getCell(8).setCellValue(projectProfitDetail.getProfit().doubleValue());
                else tempRow.getCell(8).setCellValue(0d);
                tempInt++;
            }
        }
        //导出
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + new String("PLByProfit".getBytes("gbk"), "ISO-8859-1") + ".xlsx");
            response.setHeader("Pragma", "No-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expire", 0);
            response.setContentType("application/vnd.ms-excel");
            OutputStream outputStream = response.getOutputStream();
            workBook.write(outputStream);
            outputStream.flush();
            workBook.close();
//            output = new FileOutputStream("D:" + File.separator + "workbook.xlsx");
//            workBook.write(output);
//            workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //P&L 按项目横向 excel导出
    private void exportPLByProjectRow(String organCodePara, String commId, String year, HttpServletResponse response, int userType) {
        //--------------------调用接口方法获得List<PLByProject>--------------------------

        Set<String> organCodes = new HashSet<>();
        if (organCodePara == null) {
            organCodes = tbHsprCommunityMapper.selectProjectByUserCode("012413").stream().map(ErpNcData::getOrganCode).collect(Collectors.toSet());

        } else {
            organCodes.add(organCodePara);
        }
        //获得工作模板
        try {
            File file = ResourceUtils.getFile("classpath:excel" + File.separator + "ncFinancial" + File.separator + "PLByProjectRow.xlsx");
            FileInputStream fileInputStream = new FileInputStream(file);
            Workbook workBook = new XSSFWorkbook(fileInputStream);
            Sheet sheet = workBook.getSheetAt(0);//得到第一个工作薄
            int index = 7;//记行器，从第八行开始，每次遍历完行号+1
            /*
            思路：遍历拿到的plByProjectList，同时每次从sheet里得到一行，存入，sheet的行数+1递增
            同时取月份是1到13的，遇到0，跳过循环
             */
            for (String organCode : organCodes) {
                List<PLByProject> plByProjectList = getPLByProject(organCode, commId, year, userType);

                for (PLByProject plByProject : plByProjectList) {
                    //先获取所属期间，如果所属期间位0，则跳过本次循环操作
                    if (plByProject.getMonth().equals("0")) {
                        continue;
                    }
                    //获取当前行
                    Row row = Optional.ofNullable(sheet.getRow(index)).orElse(sheet.createRow(index));
                    row.setHeightInPoints(18f);
//
//                    CellStyle style = workBook.createCellStyle();
//                    style.setAlignment(HorizontalAlignment.CENTER);
//                    style.setVerticalAlignment(VerticalAlignment.CENTER);
//                    if (plByProject.getMonth().equals("13")) {
//                        Font font = workBook.getFontAt((short) 0);
//                        font.setBold(Boolean.TRUE);
//                        style.setFont(font);
//                        style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
//                        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//                    }

//                    headerStyle.setWrapText(true);
//                    headerStyle.setBorderBottom(BorderStyle.THIN); //下边框
//                    headerStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
//                    headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
//                    headerStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
//                    headerStyle.setBorderTop(BorderStyle.THIN);//上边框
//                    headerStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
//                    headerStyle.setBorderRight(BorderStyle.THIN);//右边框
//                    headerStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());


                    //方便下面的写法，先得分别拿到需要的对象
                    Project project = plByProject.getProject();
                    Profit profit = plByProject.getProfit();  //利润部分
                    InCome inCome = plByProject.getInCome();  //收入部分
                    OutCome outcome = plByProject.getOutcome(); //成本部分
                    Reason reason = plByProject.getReason(); //收入差异原因Remark
                    Analysis analysis = plByProject.getAnalysis();

                    //记录cell的位置，每次循环从0开始，为了避免模板更改之后大量修改列号
                    int tempCellIndex = 0;

                    //-------------将数据插入到excel中-----------------
                    //==========A--C==========
                    row.getCell(tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(project.getPropertyArea()).orElse(""));//区域
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(project.getNCSystemNumber()).orElse(""));//NC编码
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(project.getCommName()).orElse(""));//项目名称

                    //==========D--K==========
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(project.getDevelopmentState()).orElse(""));//项目阶段
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(project.getDevelopmentSubject()).orElse(""));//项目来源
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(project.getCityCounty()).orElse(""));//城市
                    ++tempCellIndex;//首次协销服务时间
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(project.getActualProperty()).orElse(""));//首次收楼时间
                    ++tempCellIndex;//最后一期收楼时间
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getMonth().equals("13") ? "本年累计" : plByProject.getMonth() + "月");//所属期间,模板已有，不必注入
                    ++tempCellIndex;//合同面积,不必注入,无数据

                    //==========L--R==========
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(inCome.getTakeOverArea()).orElse("0"));//已接管面积
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(inCome.getPayBeginArea()).orElse("0"));//计费面积

                    //物业费
                    JSONObject estateManagement = (JSONObject) inCome.getEstateManagement();
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(estateManagement.getString("total")).orElse("0"));
                    //协销服务费
                    JSONObject marketingService = (JSONObject) inCome.getMarketingService();
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(marketingService.getString("total")).orElse("0"));
                    //社区增值&场地服务
                    JSONObject siteService = (JSONObject) inCome.getSiteService();
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(siteService.getString("total")).orElse("0"));
                    //社区增值服务费
                    JSONObject communityService = (JSONObject) inCome.getCommunityService();
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(communityService.getString("total")).orElse("0"));

                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(inCome.getTotal()).doubleValue());//不含税收入

                    //==========S--U==========
                    JSONObject mainCost = JSON.parseObject(JSON.toJSONString(outcome.getMainCost()));

                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(mainCost.getString("estateManagement")).orElse("0"));//物业费
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(mainCost.getString("marketingService")).orElse("0"));//协销服务费
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(mainCost.getString("communityService&siteService")).orElse("0"));//社区增值&场地服务


                    //==========V--W==========
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(mainCost.getString("mainCostTotal")).orElse("0"));//TTL Cost $  拥有计算公式，跳过
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getTTLGPAmount());//TTL GP$ 拥有计算公式，跳过

//                ==========X--Z==========
//                ==========毛利率==========
                    JSONObject profitRate = JSON.parseObject(JSON.toJSONString(analysis.getProfitRate()));
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(profitRate.getString("物业服务")).orElse("0"));//物业服务  拥有计算公式，跳过
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(profitRate.getString("协销服务")).orElse("0"));//协销服务  拥有计算公式，跳过
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(profitRate.getString("社区增值&场地服务")).orElse("0"));//社区增值 & 场地服务  拥有计算公式，跳过

                    //==========AA==========
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getTTLGPPercent());//TTL GP%  拥有计算公式，跳过

                    //==========AB--AF==========
                    //销售费用

                    JSONObject saleCost = JSON.parseObject(JSON.toJSONString(outcome.getSaleCost()));
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(saleCost.getString("saleCostTotal")).orElse("0"));

                    //管理费用
                    JSONObject managementCost = JSON.parseObject(JSON.toJSONString(outcome.getManagementCost()));
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(managementCost.getString("managementCostTotal")).orElse("0"));

                    //税金及附加
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(profit.getTax().doubleValue());

                    //财务费用
                    JSONObject financialCost = JSON.parseObject(JSON.toJSONString(outcome.getFinancialCost()));
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(Optional.ofNullable(financialCost.getString("financialCostTotal")).orElse("0"));

                    //营业外收支
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(profit.getOutIncome().doubleValue());

                    //==========AG--AI==========
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getContributionAmount());//Contribution $  拥有计算公式，跳过
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getContributionAmountPercent());//Contribution %  拥有计算公式，跳过
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getAnalaysis());//Analaysis  拥有计算公式，跳过

                    //==========AJ--AO==========
                    OutCome areaCost = plByProject.getAreaCost();
                    if (areaCost != null) {
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(JSON.parseObject(JSON.toJSONString(areaCost.getMainCost())).getString("estateManagement")).doubleValue());//区域成本——物业费
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(JSON.parseObject(JSON.toJSONString(areaCost.getMainCost())).getString("marketingService")).doubleValue());//区域成本——协销服务费
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(JSON.parseObject(JSON.toJSONString(areaCost.getMainCost())).getString("communityService&siteService"));//区域成本——社区增值&场地服务
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(JSON.parseObject(JSON.toJSONString(areaCost.getMainCost())).getString("mainCostTotal"));//区域成本——合计 有公式跳过
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(JSON.parseObject(JSON.toJSONString(areaCost.getSaleCost())).getString("saleCostTotal")).doubleValue());//区域销售费用
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(JSON.parseObject(JSON.toJSONString(areaCost.getManagementCost())).getString("managementCostTotal")).doubleValue());//区域管理费用
                    } else {
                        ++tempCellIndex;//区域成本——物业费
                        ++tempCellIndex;//区域成本——协销服务费
                        ++tempCellIndex;//区域成本——社区增值&场地服务
                        ++tempCellIndex;//区域成本——合计 有公式跳过
                        ++tempCellIndex;//区域销售费用
                        ++tempCellIndex;//区域管理费用
                    }

                    //==========AP--AQ==========
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(JSON.parseObject(JSON.toJSONString(inCome.getNcIncome())).getString("ncIncomeTotal")).doubleValue());//NC主营业务收入
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getInComeDiff());//不含税收入 VS NC主营业务收入  有公式跳过

                    //==========AR--AW==========
                    //==========收入差异原因Remark==========
                    if (reason != null) {
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(reason.getHouseOnWay());//房产流程在途
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(reason.getHouseUnconfirmed());//房产协同未确认
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(reason.getNcIsWrong());//NC记账有误
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(reason.getErpIsWrong());//天问数据有误
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(reason.getNotInAmount());//16年按权责未入账金额
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(reason.getOther());//其他
                    } else {
                        tempCellIndex = tempCellIndex + 6;
                    }

                    //==========AX==========
                    ++tempCellIndex;//空格列 跳过

                    //==========AY--AZ==========
                    row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(plByProject.getInComeDiffRate());//账面确认收入率(NC收入VS天问）  有公式，跳过，不处理
                    Map<String, String> collectRate = (Map<String, String>) plByProject.getAnalysis().getCollectRate();
                    if (collectRate != null) {
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(collectRate.get("收楼率"));//收楼率
                    } else {
                        ++tempCellIndex;//收楼率
                    }

                    //==========BA--BD==========
                    //==========签证率==========
                    Map<String, String> visaRate = (Map<String, String>) plByProject.getAnalysis().getVisaRate();
                    if (visaRate != null) {
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(visaRate.get("房产代付"));//房产代付
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(visaRate.get("空置房"));//空置房
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(visaRate.get("协销服务"));//协销服务
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(visaRate.get("合计"));//合计签证率
                    } else {
                        ++tempCellIndex;//房产代付
                        ++tempCellIndex;//空置房
                        ++tempCellIndex;//协销服务
                        ++tempCellIndex;//合计签证率
                    }

                    //==========BE--BL==========
                    //==========人均指标_一线员工==========
                    Map<String, String> averagePerson = (Map<String, String>) plByProject.getAnalysis().getAveragePerson();
                    if (averagePerson != null) {
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("平均一线人数")).doubleValue());//一线平均人数
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("人均接管收费面积")).doubleValue());//人均接管收费面积
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("月平均单价")).doubleValue());//月平均单价
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("人均创收收入")).doubleValue());//人均创收收入
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("人均人工成本")).doubleValue());//人均人工成本
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("人均食堂收支")).doubleValue());//人均食堂收支
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("人均宿舍收支")).doubleValue());//人均宿舍收支
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averagePerson.get("人均毛利利润")).doubleValue());//人均毛利利润
                    } else {
                        ++tempCellIndex;//一线平均人数
                        ++tempCellIndex;//人均接管收费面积
                        ++tempCellIndex;//月平均单价
                        ++tempCellIndex;//人均创收收入
                        ++tempCellIndex;//人均人工成本
                        ++tempCellIndex;//人均食堂收支
                        ++tempCellIndex;//人均宿舍收支
                        ++tempCellIndex;//人均毛利利润
                    }


                    //==========BM--BP==========
                    //==========其中：协销服务==========
                    ++tempCellIndex;//案场服务物业编制人数，跳过，数据库没数据
                    ++tempCellIndex;//案场服务人均创收收入，跳过，数据库没数据
                    ++tempCellIndex;//开荒绿化护理面积，跳过，数据库没数据
                    ++tempCellIndex;//开荒绿化养护每平方收入，跳过，数据库没数据

                    //==========BQ--BU==========
                    //==========每平方成本==========
                    Map<String, String> averageOutcomePerSquare = (Map<String, String>) plByProject.getAnalysis().getAverageOutcomePerSquare();
                    if (averageOutcomePerSquare != null) {
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averageOutcomePerSquare.get("已接管每平方米的设施维护费")).doubleValue());//已接管每平方米的设施维护费
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averageOutcomePerSquare.get("已接管每平方米的清洁费")).doubleValue());//已接管每平方米的清洁费
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averageOutcomePerSquare.get("已接管每平方米的绿化费")).doubleValue());//已接管每平方米的绿化费
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averageOutcomePerSquare.get("已接管每平方米的水费")).doubleValue());//已接管每平方米的水费
                        row.getCell(++tempCellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(new BigDecimal(averageOutcomePerSquare.get("已接管每平方米的电费")).doubleValue());//已接管每平方米的电费
                    } else {
                        ++tempCellIndex;//已接管每平方米的设施维护费
                        ++tempCellIndex;//已接管每平方米的清洁费
                        ++tempCellIndex;//已接管每平方米的绿化费
                        ++tempCellIndex;//已接管每平方米的水费
                        ++tempCellIndex;//已接管每平方米的电费
                    }
                    //行号+1
                    ++index;
                }
            }


            //输出
            response.setHeader("Content-Disposition", "attachment; filename=" + new String("PLByProjectRow".getBytes("gbk"), "ISO-8859-1") + ".xlsx");
            response.setHeader("Pragma", "No-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expire", 0);
            response.setContentType("application/vnd.ms-excel");
            OutputStream outputStream = response.getOutputStream();
            workBook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //P&L By Month excel导出
    private void exportPLByMonth(HttpServletResponse response, String organCode, String commId, String year, int startMonth, int endMonth, int userType) {
        OutputStream output;
        Workbook workBook = null;
        Sheet sheet;
        InputStream inputstream;
        List<OutCome> returnList;
        Row tempRow;
        BigDecimal data = null;
        int monthIndex;
        //获取工作簿模板
        try {
            File file = ResourceUtils.getFile("classpath:excel" + File.separator + "ncFinancial" + File.separator + "P&LByMonth.xlsx");
            inputstream = new FileInputStream(file);
            // 获取模板
            workBook = new XSSFWorkbook(inputstream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 获取模板sheet页
        sheet = workBook.getSheetAt(0);
        //删除不需要的月份列
        monthIndex = 6;     //Excel中一月份开始的位置
        int startIndex = monthIndex + (startMonth - 1) * 3; //获取月份开始对应的列
        monthIndex = 6;     //Excel中一月份开始的位置
        int endIndex = monthIndex + (endMonth - 1) * 3 + 1; //获取月份结束对应的列
        for (int i = 6; i <= 40; i++) {     //从第7列 到 第41 是 1月 到 12月对应的列
            if (i < startIndex || i > endIndex) {
                //删除不需要的月份列
                sheet.setColumnWidth(i, 0);
            }
        }
        //数据获取
        List<PLByMonth> plByMonthList = getPLByMonth(organCode, commId, year, startMonth, endMonth, userType, 1);
        //表格第6行D列 为去年的列 修改为去年
        tempRow = sheet.getRow(5);
        String lastYear = String.valueOf(Integer.valueOf(year) - 1);
        tempRow.getCell(3).setCellValue(lastYear + "年");
        //根据月份放入数据
        for (PLByMonth plByMonth : plByMonthList) {
            monthIndex = 6;     //Excel中一月份开始的位置
            monthIndex = monthIndex + (Integer.valueOf(plByMonth.getMonth()) - 1) * 3; //获取月份对应的列

            OutCome outCome = plByMonth.getOutcome();                      //获取成本数据
            JSONObject mainMap = new JSONObject((Map<String, Object>) outCome.getMainCost()); //获取主营业务成本
            JSONObject detail = mainMap.getJSONObject("mainCostDetail");    //获取主营业务成本明细
            //获取管理费用明细
            JSONObject managementMap = new JSONObject((Map<String, Object>) outCome.getManagementCost());
            JSONObject managementDetail = managementMap.getJSONObject("managementCostDetail");
            //获取销售费用明细
            JSONObject saletMap = new JSONObject((Map<String, Object>) outCome.getSaleCost());
            JSONObject saleDetail = saletMap.getJSONObject("saleCostDetail");
            //获取财务费用
            JSONObject financialMap = new JSONObject((Map<String, Object>) outCome.getFinancialCost());
            BigDecimal financialCost = financialMap.getBigDecimal("financialCostTotal");

            InCome inCome = plByMonth.getInCome();                      //获取收入数据
            JSONObject estateManagement = new JSONObject((Map<String, Object>) inCome.getEstateManagement());   //获取物业管理服务
            JSONObject communityService = new JSONObject((Map<String, Object>) inCome.getCommunityService());   //获取社区增值服务
            JSONObject marketingService = new JSONObject((Map<String, Object>) inCome.getMarketingService());   //获取协销服务
            JSONObject siteService = new JSONObject((Map<String, Object>) inCome.getSiteService());             //获取场地服务
            String totalWithTax = inCome.getTotalWithTax();                                                     //获取含税收入
            String other = inCome.getOther();                                                                   //其他业务收入

            Profit profit = plByMonth.getProfit();                      //获取利润数据
            String tax = profit.getTax().toString();                               //税金及附加
            String profitOrLoss = profit.getProfitOrLoss();             //资产减值损失&投资损失
            String outIncome = profit.getOutIncome().toString();                   //营业外收入
            String bonus = profit.getBonus().toString();                           //年终奖
            String ownerProfit = profit.getOwnerProfit();               //少数股东本期损益

            Analysis analysis = plByMonth.getAnalysis();                      //获取分析指标数据
            JSONObject collectRate = new JSONObject((Map<String, Object>) analysis.getCollectRate());       //收楼率
            JSONObject incomeRate = new JSONObject((Map<String, Object>) analysis.getIncomeRate());         //确认收入率
            JSONObject visaRate = new JSONObject((Map<String, Object>) analysis.getVisaRate());             //签证率
            JSONObject averagePerson = new JSONObject((Map<String, Object>) analysis.getAveragePerson());   //人均指标
            JSONObject xxfw = new JSONObject((Map<String, Object>) analysis.getMarketing());                     //协销服务

            //收入部分
            //表格第9行为含税收入
            FinancialUtil.excelOneRawHandle(sheet, totalWithTax, 56, monthIndex);
            //表格内21行至31行为物业管理服务（收入）
            FinancialUtil.excelHandle(sheet, estateManagement, 20, 30, monthIndex);
            //表格内35行至40行为协销服务（收入）
            FinancialUtil.excelHandle(sheet, marketingService, 34, 39, monthIndex);
            //表格内43行至47行为社区增值服务（收入）
            FinancialUtil.excelHandle(sheet, communityService, 42, 46, monthIndex);
            //表格内50行至54行为场地服务（收入）
            FinancialUtil.excelHandle(sheet, siteService, 49, 53, monthIndex);
            //表格第57行为其他业务收入
            FinancialUtil.excelOneRawHandle(sheet, other, 56, monthIndex);

            //成本部分
            for (int i = 63; i <= 65; i++) {        //表格内64行至66行为主营业务成本 64物业管理 65协销服务 66社区增值服务&场地管理服务
                tempRow = sheet.getRow(i);
                if (tempRow == null) continue;     //获取空白列则略过该行
                String comm = tempRow.getCell(1).getStringCellValue();      //获取对应科目
                switch (comm) {      //根据科目获取数据
                    case "物业管理":
                        data = mainMap.getBigDecimal("estateManagement");
                        break;
                    case "协销服务":
                        data = mainMap.getBigDecimal("marketingService");
                        break;
                    case "社区增值服务&场地管理服务":
                        data = mainMap.getBigDecimal("communityService&siteService");
                        break;
                    default:
                        break;
                }
                //map的key和B列科目名称对应 无数据则设置为0
                if (data != null) tempRow.getCell(monthIndex).setCellValue(data.doubleValue());
                else tempRow.getCell(monthIndex).setCellValue(0d);
            }
            for (int i = 71; i <= 111; i++) {       //表格内72行至112行为主营业务成本明细
                tempRow = sheet.getRow(i);
                if (tempRow == null) continue;     //获取空白列则略过该行
                String comm = tempRow.getCell(1).getStringCellValue();      //获取对应科目
                data = detail.getBigDecimal(comm);
                //map的key和B列科目名称对应 无数据则设置为0
                if (data != null) tempRow.getCell(monthIndex).setCellValue(data.doubleValue());
                else tempRow.getCell(monthIndex).setCellValue(0d);
            }
            for (int i = 120; i <= 136; i++) {       //表格内121行至137行为销售费用明细
                tempRow = sheet.getRow(i);
                if (tempRow == null) continue;      //获取空白列则略过该行
                String comm = tempRow.getCell(1).getStringCellValue();      //获取对应科目
                if (i == 136) comm = "其他费用";          //修改其他费用对应Map中的键
                data = new BigDecimal(saleDetail.getString(comm));
                //map的key和B列科目名称对应 无数据则设置为0
                if (data != null) tempRow.getCell(monthIndex).setCellValue(data.doubleValue());
                else tempRow.getCell(monthIndex).setCellValue(0d);
            }
            for (int i = 140; i <= 156; i++) {       //表格内141行至157行为管理费用明细
                tempRow = sheet.getRow(i);
                if (tempRow == null) continue;      //获取空白列则略过该行
                String comm = tempRow.getCell(1).getStringCellValue();      //获取对应科目
                if (i == 156) comm = "其他费用";          //修改其他费用对应Map中的键
                data = new BigDecimal(managementDetail.getString(comm));
                //map的key和B列科目名称对应 无数据则设置为0
                if (data != null) tempRow.getCell(monthIndex).setCellValue(data.doubleValue());
                else tempRow.getCell(monthIndex).setCellValue(0d);
            }
            tempRow = sheet.getRow(159);           //表格160行为财务费用
            //map的key和B列科目名称对应 无数据则设置为0
            if (financialCost != null) tempRow.getCell(monthIndex).setCellValue(financialCost.doubleValue());
            else tempRow.getCell(monthIndex).setCellValue(0d);

            //利润部分
            //表格第115行为资产减值损失&投资损失
            FinancialUtil.excelOneRawHandle(sheet, tax, 114, monthIndex);
            //表格第164行为资产减值损失&投资损失
            FinancialUtil.excelOneRawHandle(sheet, profitOrLoss, 163, monthIndex);
            //表格第166行为营业外收入
            FinancialUtil.excelOneRawHandle(sheet, outIncome, 165, monthIndex);
            //表格第169行为年终奖
            FinancialUtil.excelOneRawHandle(sheet, bonus, 168, monthIndex);
            //表格第174行为少数股东本期损益（收益用“-”填列）
            FinancialUtil.excelOneRawHandle(sheet, ownerProfit, 173, monthIndex);

            //分析指标部分
            //表格内180行至182行为收楼率
            FinancialUtil.excelHandle(sheet, collectRate, 179, 181, monthIndex);
            //表格内190行至194行为账面确认收入率(NC收入VS天问）
            FinancialUtil.excelHandle(sheet, incomeRate, 189, 193, monthIndex);
            //表格内197行至200行为签证率
            FinancialUtil.excelHandle(sheet, visaRate, 196, 199, monthIndex);
            //表格内203行至204行为人均指标_一线员工
            FinancialUtil.excelHandle(sheet, averagePerson, 202, 203, monthIndex);
            //表格内214行为协销服务
            FinancialUtil.excelOneRawHandle(sheet, xxfw.getString("案场服务物业编制人数"), 213, monthIndex);
        }
        //导出
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + new String("PLByMonth".getBytes("gbk"), "ISO-8859-1") + ".xlsx");
            response.setHeader("Pragma", "No-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expire", 0);
            response.setContentType("application/vnd.ms-excel");
            OutputStream outputStream = response.getOutputStream();
            workBook.write(outputStream);
            outputStream.flush();
            workBook.close();
//            output = new FileOutputStream("D:" + File.separator + "workbook.xlsx");
//            workBook.write(output);
//            workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /*-------------------------------------------excel导出方法   end---------------------------------------------------*/

    public void saveErpDataToRedis(String year, String organCode) throws Exception {

        List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectProjectByUserCode("012413");

        if (organCode != null) {
            erpNcDataList = erpNcDataList.stream().filter(s -> s.getOrganCode().equals(organCode)).distinct().collect(Collectors.toList());
        }

        Map<String, List<ErpNcData>> organCodeDataListMap = new HashMap<>();

        if (erpNcDataList != null) {
//            for (ErpNcData erpNcData : erpNcDataList) {
//                if (organCodeDataListMap.keySet().contains(erpNcData.getOrganCode())) {
//                    List<ErpNcData> datas = organCodeDataListMap.get(erpNcData.getOrganCode());
//                    datas.add(erpNcData);
//                } else {
//                    List<ErpNcData> datas = new ArrayList<>();
//                    datas.add(erpNcData);
//                    organCodeDataListMap.put(erpNcData.getOrganCode(), datas);
//                }
//            }
            organCodeDataListMap = erpNcDataList.stream().collect(Collectors.groupingBy(ErpNcData::getOrganCode));
        } else {
            throw new Exception("查询区域数据为空,区域编号 " + organCode);
        }

        List<InCome> inComeList = new ArrayList<>();
        for (Map.Entry<String, List<ErpNcData>> stringListEntry : organCodeDataListMap.entrySet()) {
            inComeList = getERPIncomeData(year, 1, 12, stringListEntry.getValue(), 1, 2);
            RedisUtils.hset("NCFinancial-ERP-RegionData", stringListEntry.getKey() + year, JSON.toJSONString(inComeList));
            inComeList.clear();
        }
    }

    public void deleteErpDataFromRedis(String year, String organCode) {
        if (organCode == null) {
            RedisUtils.del("NCFinancial-ERP-RegionData");
        } else if (RedisUtils.hexits("NCFinancial-ERP-RegionData", organCode + year)) {
            RedisUtils.hdel("NCFinancial-ERP-RegionData", organCode + year);
        }
    }

    public List<InCome> getErpDataFromRedis(String year, int startMonth, int endMonth, String organCode, String commId, int userType, int buildingType) {

        if (userType == 0) {            //每个区域处理完成 清理一遍内存
            List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectProjectByUserCode("012413");
            Set<String> organCodeSet = erpNcDataList.stream().map(ErpNcData::getOrganCode).collect(Collectors.toSet());
            List<InCome> inComeList = new ArrayList<>();
            for (String s : organCodeSet) {
                if (RedisUtils.hget("NCFinancial-ERP-RegionData", s + year) == null) {
                    continue;
                }
                inComeList.addAll(sumInComeList(RedisUtils.hget("NCFinancial-ERP-RegionData", s + year), year, startMonth, endMonth, s, null));
            }
            return sumInComeList(JSON.toJSONString(inComeList), year, startMonth, endMonth, organCode, null);
        }
        if (userType == 1) {
            if (buildingType == 1) {
                return sumInComeList(RedisUtils.hget("NCFinancial-ERP-RegionData", organCode + year), year, startMonth, endMonth, organCode, null);
            } else {
                return JSON.parseObject(RedisUtils.hget("NCFinancial-ERP-RegionData", organCode + year), new TypeReference<List<InCome>>() {
                });
            }
        }

        if (userType == 2) {
            List<ErpNcData> erpNcDataList = tbHsprCommunityMapper.selectProjectByCommId(commId);
            organCode = erpNcDataList.get(0).getOrganCode();
            List<InCome> organIncomeList = JSON.parseObject(RedisUtils.hget("NCFinancial-ERP-RegionData", organCode + year), new TypeReference<List<InCome>>() {
            });
            List<InCome> commIncomeList = organIncomeList.stream().filter(s -> s.getCommId().equals(commId)).collect(Collectors.toList());
            if (buildingType == 2) {
                return commIncomeList;
            } else {
                return sumInComeList(JSON.toJSONString(commIncomeList), year, startMonth, endMonth, organCode, commId);
            }
        }
        return null;
    }

    private List<InCome> sumInComeList(String data, String year, int startMonth, int endMonth, String organCode, String commId) {
        List<InCome> inComeList = JSON.parseObject(data, new TypeReference<List<InCome>>() {
        });
        Map<String, List<InCome>> inComeMap = new HashMap<>();

        inComeMap.put("0", new ArrayList<>());

        for (int i = startMonth; i <= endMonth; i++) {
            inComeMap.put(String.valueOf(i), new ArrayList<>());
        }

        for (InCome inCome : inComeList) {
            if (inComeMap.keySet().contains(inCome.getMonth())) {
                inComeMap.get(inCome.getMonth()).add(inCome);
            }
        }

        List<String> fieldFilter = new ArrayList<String>() {{
            add("year");
            add("month");
            add("commId");
            add("organCode");
            add("ncIncome");
        }};


        List<JSONObject> tmpList = new ArrayList<>();

        for (Map.Entry<String, List<InCome>> stringListEntry : inComeMap.entrySet()) {
            JSONObject target = new JSONObject();
            for (InCome source : stringListEntry.getValue()) {
                target = calculate(fieldFilter, JSON.parseObject(source.toString()), target);
            }
            target.put("year", year);
            target.put("month", stringListEntry.getKey());
            target.put("organCode", organCode);
            target.put("commId", commId);
            tmpList.add(target);
        }

        //处理面积  面积是累计值 不能直接sum
        // 取最后一个月份的面积当作本年累计
        JSONObject lastOne = new JSONObject();
        for (JSONObject jsonObject : tmpList) {
            if (jsonObject.getString("month").equals(String.valueOf(endMonth))) {
                lastOne = jsonObject;
            }
        }
        String takeOver = lastOne.getString("takeOverArea");
        String payBegin = lastOne.getString("payBeginArea");
        String actual = lastOne.getString("actualArea");


        JSONObject target = new JSONObject();
        for (JSONObject source : tmpList) {
            if (source.getString("month").equals("0")) {
                continue;
            }
            target = calculate(fieldFilter, source, target);
        }
        target.put("year", year);
        target.put("month", "13");
        target.put("organCode", organCode);
        target.put("commId", commId);
        target.put("takeOverArea", takeOver);
        target.put("payBeginArea", payBegin);
        target.put("actualArea", actual);
        tmpList.add(target);

        tmpList = tmpList.stream().sorted(Comparator.comparing(x -> x.getInteger("month"))).collect(Collectors.toList());

        return JSON.parseObject(JSON.toJSONString(tmpList), new TypeReference<List<InCome>>() {
        });


    }

    private static JSONObject calculate(List<String> filter, JSONObject source, JSONObject target) {
        for (String field : source.keySet()) {
            if (filter.contains(field)) {
                target.put(field, "0");
                continue;
            }
            if (source.get(field) instanceof JSONObject) {
                target.put(field, calculate(filter, source.getJSONObject(field), target.getJSONObject(field) == null ? new JSONObject() : target.getJSONObject(field)));
            } else if (source.get(field) instanceof String) {
                target.put(field, NumberUtil.add(source.getString(field), target.getString(field) == null ? "0" : target.getString(field)));
            }
        }
        return target;
    }
}