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


import cn.com.countrygarden.bi.main.dao.excontract.ContractDictionaryMapper;
import cn.com.countrygarden.bi.main.dao.excontract.ContractEvaluateMapper;
import cn.com.countrygarden.bi.main.dao.excontract.ContractRecordMapper;
import cn.com.countrygarden.bi.main.dao.excontract.ContractTargetMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.TbHsprCommunityMapper;
import cn.com.countrygarden.bi.main.model.excontract.ContractDictionary;
import cn.com.countrygarden.bi.main.model.excontract.ContractEvaluate;
import cn.com.countrygarden.bi.main.model.excontract.ContractRecord;
import cn.com.countrygarden.bi.main.model.excontract.ContractTarget;
import cn.com.countrygarden.bi.main.model.excontract.dto.EnvironmentalClean;
import cn.com.countrygarden.bi.main.model.excontract.dto.GarbageCleaning;
import cn.com.countrygarden.bi.main.model.excontract.dto.GreeningMaintenance;
import cn.com.countrygarden.bi.main.model.twerp.ErpNcData;
import cn.com.countrygarden.bi.main.service.excontract.ExContractService;
import cn.com.countrygarden.bi.main.util.DateUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import tk.mybatis.mapper.entity.Condition;
import tk.mybatis.mapper.entity.Example;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: DPshow_depart
 * @description: 委外合同管理服务实现类
 * @author: SignV
 * @create: 2018-09-05 13:14
 **/
@Service("exContractService")
public class ExContractServiceImpl implements ExContractService {
    @Autowired
    private ContractDictionaryMapper dictionaryMapper;

    @Autowired
    private ContractEvaluateMapper evaluateMapper;

    @Autowired
    private ContractRecordMapper recordMapper;

    @Autowired
    private ContractTargetMapper targetMapper;

    @Autowired
    private TbHsprCommunityMapper tbHsprCommunityMapper;


    private static final Logger LOGGER = LoggerFactory.getLogger(ExContractServiceImpl.class);


    /*----------------------------------------供应商月度考核记录部分  start-----------------------------------------------*/

    /**
     * @Description: 供应商月度考核记录 数据获取
     * @Params: [page, pageSize, organCodeList]
     * @return: com.alibaba.fastjson.JSONObject
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public JSONObject getRecordList(int page, int pageSize, List<String> organCodeList) {
        //条件查询
        Condition condition = new Condition(ContractRecord.class);
        Condition.Criteria criteria = condition.createCriteria();
        //查询未删除的数据 isDelete = 0 未删除
        criteria.andEqualTo("isDelete", 0);
        //只能查询自己所拥有的区域
        criteria.andIn("organCode", organCodeList);
        //查询考核记录总条数
        int count = recordMapper.selectCountByCondition(condition);
        //分页的总页数
        int pageNum = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;

        JSONObject returnJson = new JSONObject();
        returnJson.put("pageNum", pageNum);

        //按考核年份 月份 创建时间 倒序
        condition.orderBy("year").desc();
        condition.orderBy("month").desc();
        condition.orderBy("createDate").desc();
        //分页查询
        PageHelper.startPage(page, pageSize);
        returnJson.put("recordList", recordMapper.selectByCondition(condition));
        return returnJson;
    }

    /**
     * @Description: 供应商月度考核记录 按条件 数据获取
     * @Params: [organCodeList, commname, supplierName, year, month, exCode, page, pageSize, isPage]
     * @return: com.alibaba.fastjson.JSONObject
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public JSONObject getRecordListByCondition(List<String> organCodeList, String commname, String supplierName,
                                               String year, String month, String exCode, int page, int pageSize, boolean isPage) {
        //模糊查询 以及 条件查询
        Condition condition = new Condition(ContractRecord.class);
        Example.Criteria criteria = condition.createCriteria();
        //项目名称模糊查询
        if (commname != null && !commname.equals("")) {
            criteria.andLike("commname", "%" + commname + "%");
        }
        //供应商名称模糊查询
        if (supplierName != null && !supplierName.equals("")) {
            criteria.andLike("supplierName", "%" + supplierName + "%");
        }
        //委外类别code
        if (exCode != null) {
            criteria.andEqualTo("exCode", exCode);
        }

        //年份月份
        criteria.andEqualTo("year", year);
        criteria.andEqualTo("month", month);
        //查询未删除的数据 isDelete = 0 未删除
        criteria.andEqualTo("isDelete", 0);

        //区域查询
        //只能查询自己所拥有的区域
        criteria.andIn("organCode", organCodeList);

        //按考核年份 月份 创建时间 倒序
        condition.orderBy("year").desc();
        condition.orderBy("month").desc();
        condition.orderBy("createDate").desc();

        JSONObject returnJson = new JSONObject();
        if (isPage) {                 //是否分页查询
            //按条件 查询考核记录总条数
            int count = recordMapper.selectCountByCondition(condition);
            //分页的总页数
            int pageNum = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;

            returnJson.put("pageNum", pageNum);
            //分页查询
            PageHelper.startPage(page, pageSize);
        }
        returnJson.put("recordList", recordMapper.selectByCondition(condition));
        return returnJson;
    }

    /**
     * @Description: 删除记录（逻辑删除） 按委外类别(公共环境保洁有三个特殊的三级指标)删除不同的 评定项 以及 指标项
     * @Params: [recordId, exCode, userCode]
     * @return: void
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public void deleteRecord(int recordId, String exCode, String userCode) {
        //逻辑删除 record表的数据
        ContractRecord record = new ContractRecord();
        record.setId(recordId);
        record.setIsDelete(1);
        record.setUpdateBy(userCode);
        record.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
        recordMapper.updateByPrimaryKeySelective(record);

        //获取该考核记录下的所有评定项
        Condition condition = new Condition(ContractEvaluate.class);
        Example.Criteria criteria = condition.createCriteria();
        criteria.andEqualTo("recordId", recordId);
        List<ContractEvaluate> evaluateList = evaluateMapper.selectByCondition(condition);
        //循环 逻辑删除
        evaluateList.forEach(evaluate -> {
            //逻辑删除 evaluate表的数据
            evaluate.setIsDelete(1);
            evaluate.setUpdateBy(userCode);
            evaluate.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
            evaluateMapper.updateByPrimaryKeySelective(evaluate);
            //获取该评定项下面的所有指标
            List<ContractTarget> targetList = targetMapper.selectByEvaluateId(evaluate.getId());
            //循环 逻辑删除
            targetList.forEach(target -> {
                //逻辑删除 target表的数据
                target.setIsDelete(1);
                target.setUpdateBy(userCode);
                target.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
                targetMapper.updateByPrimaryKeySelective(target);

                //公共环境保洁有三个特殊的三级指标 需要再一级逻辑删除 （三级指标已废弃）
//                if (exCode.equals("1")) {
//                    if (target.getCode().equals("10103") || target.getCode().equals("10104") || target.getCode().equals("10105")) {
//                        //10103月度巡检  10104区域或总公司检查  10105总公司飞行检查
//                        List<ContractTarget> tempTargetList = targetMapper.selectByParId(target.getId());
//                        tempTargetList.forEach(lowerTarget -> {
//                            //逻辑删除 更下一级target表的数据
//                            lowerTarget.setIsDelete(1);
//                            lowerTarget.setUpdateBy(userCode);
//                            lowerTarget.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
//                            targetMapper.updateByPrimaryKeySelective(lowerTarget);
//                        });
//                    }
//                }
            });
        });

    }

    /*----------------------------------------供应商月度考核记录部分  end-----------------------------------------------*/


    /*----------------------------------------考核详细部分  start-----------------------------------------------*/

    /**
     * @Description: 获取考核的详情 按委外类别(1、公共环境保洁 2、垃圾清运 3、公共绿化养护) 返回对应的DTO数据
     * @Params: [recordId, exCode]
     * @return: cn.com.countrygarden.bi.main.model.excontract.ContractRecord
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public ContractRecord getDataDetail(int recordId, String exCode) {
        switch (exCode) {
            case "1":               //公共环境保洁
                return getEnvironmental(recordId);
            case "2":               //垃圾清运
                return getGarbage(recordId);
            case "3":               //公共绿化养护
                return getGreening(recordId);
        }
        return null;
    }

    /**
     * 添加考核记录 按类别分别添加不同的考核记录（公共环境保洁、垃圾清运、公共绿化养护）
     * 同一供应商、同一合同、同一月，只能有一条考核记录；保存时，如已有记录，提示“已有重复考核记录”
     * <p>
     * 修改考核记录 按委外类别分别修改不同的考核记录（公共环境保洁、垃圾清运、公共绿化养护）
     * 显示当前记录的所有字段值，除“委外类别”这一项不可更改以外，其他项都可更改
     *
     * @param record    数据对象
     * @param exCode    数据类型：1保洁 2垃圾清运 3绿化养护
     * @param operaType 操作类型：1插入 2更新
     * @throws Exception 重复提示
     * @author Geoffrey
     */
    public void saveOrUpdateRecord(String record, String exCode, Integer operaType, String userCode) throws Exception {

        ContractRecord r = new ContractRecord();
        switch (exCode) {
            case "1": {          //保洁
                r = JSON.parseObject(record, EnvironmentalClean.class);
                break;
            }
            case "2": {         //垃圾清运
                r = JSON.parseObject(record, GarbageCleaning.class);
                break;
            }
            case "3": {         //绿化
                r = JSON.parseObject(record, GreeningMaintenance.class);
                break;
            }
            default: {
                break;
            }
        }

        if (operaType == 1) {
            if (checkIsExist(r) == 1) { //插入操作判断是否重复
                throw new Exception("已有重复考核记录");
            } else {
                saveRecord(r, userCode);
            }
        } else if (operaType == 2) {
            updateRecord(r, userCode);
        }
    }

    /**
     * @Description: 根据codeType的值获取 1、项目 或 2、组团 的名称以及编号
     * @Params: [code, codeType]
     * @return: java.util.List<com.alibaba.fastjson.JSONObject>
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public List<JSONObject> getAuthDetails(String code, int codeType) {
        List<ErpNcData> erpNcDataList;
        List<JSONObject> jsonList = new ArrayList<>();
        //codeType = 1 通过organCode 获取项目 的名称和编号
        if (codeType == 1) {
            erpNcDataList = tbHsprCommunityMapper.selectRegionByOrganCode(code);
            erpNcDataList.forEach(erpNcData -> {
                JSONObject authDetails = new JSONObject();
                authDetails.put("commId", erpNcData.getCommID());
                authDetails.put("commName", erpNcData.getPropertyProject());
                jsonList.add(authDetails);
            });
        }
        //codeType = 2 通过commID 获取组团 的名称和编号
        else if (codeType == 2) {
            erpNcDataList = tbHsprCommunityMapper.selectGroupsByCommId(code);
            erpNcDataList.forEach(erpNcData -> {
                JSONObject authDetails = new JSONObject();
                authDetails.put("regionId", erpNcData.getRegionID());
                authDetails.put("regionName", erpNcData.getRegionName());
                jsonList.add(authDetails);
            });
        }
        return jsonList;
    }


    /*----------------------------------------考核详细部分  end------------------------------------------------*/



    /*----------------------------------------公共环境保洁  start-----------------------------------------------*/

    //获取公共环境保洁 DTO数据
    private EnvironmentalClean getEnvironmental(int recordId) {
        Condition condition;
        Example.Criteria criteria;
        //获取考核记录信息
        EnvironmentalClean enviro = recordMapper.selectEnviroByPrimaryKey(recordId);

        //获取该考核记录下的所有评定项 公共环境保洁 有8项
        condition = new Condition(ContractEvaluate.class);
        criteria = condition.createCriteria();
        criteria.andEqualTo("recordId", recordId);
        List<ContractEvaluate> evaluateList = evaluateMapper.selectByCondition(condition);

        for (ContractEvaluate contractEvaluate : evaluateList) {
            List<ContractTarget> targetList = targetMapper.selectByEvaluateId(contractEvaluate.getId());
            switch (contractEvaluate.getCode()) {
                case "101":         //缺勤
                {
                    //获取 缺勤 评定项下面的各类指标
                    for (ContractTarget contractTarget : targetList) {
                        switch (contractTarget.getCode()) {
                            case "10101":
                                //本月应出勤人数 数据获取
                                enviro.setTotalNum(contractTarget.getOccurNum());
                                break;
                            case "10102":
                                //本月实际出勤人数 数据获取
                                enviro.setTotalActualNum(contractTarget.getOccurNum());
                                break;
                            case "10103":
                                //获取 本月加班总时数 下面的所有子指标
                                enviro.setTotalOvertime(contractTarget.getOccurNum());
                                break;
                            case "10104":
                                //获取 折算为加班人数 下面的所有子指标
                                enviro.setOvertimeNum(contractTarget.getOccurNum());
                                break;
                            case "10105":
                                //获取 缺勤人数 下面的所有子指标
                                enviro.setVacancyNum(contractTarget.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    }
                    break;
                }
                case "102":         //出勤率
                {
                    enviro.setVacancyMoney(contractEvaluate.getTotalMoney());            //出勤率 扣款

                    //获取 出勤率 评定项下面的各类指标
                    for (ContractTarget contractTarget : targetList) {
                        switch (contractTarget.getCode()) {
                            case "10201":
                                //出勤率 数据获取
                                enviro.setVacancyRate(contractTarget.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    }
                    break;
                }
                case "103":         //日常巡查
                {
                    enviro.setInspectionScore(contractEvaluate.getTotalScore());        //日常巡查月度得分
                    //获取 日常巡查 评定项下面的各类指标 （该评定项下无指标 所以先注释下面这段代码）
//                    for (ContractTarget contractTarget : targetList) {
//                        switch (contractTarget.getCode()) {
//                            case "10301":
//                                //日常巡查扣分 数据获取
//                                enviro.setInspectionScore(contractTarget.getDeductScore());            //日常巡查得分
//                                break;
//                            default:
//                                break;
//                        }
//                    }
                    break;
                }
                case "104":         //投诉
                {
                    enviro.setComplaintScore(contractEvaluate.getTotalScore());          //投诉 扣分
                    enviro.setComplaintMoney(contractEvaluate.getTotalMoney());          //投诉 扣款

                    //获取 投诉 评定项下面的各类指标
                    for (ContractTarget contractTarget : targetList) {
                        switch (contractTarget.getCode()) {
                            case "10401":
                                //一般投诉项 数据获取
                                enviro.setCommonComplaint(contractTarget.getOccurNum());
                                break;
                            case "10402":
                                //严重投诉项 数据获取
                                enviro.setSeriousComplaint(contractTarget.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    }
                    break;
                }
                case "105":         //满意度
                {
                    enviro.setSatisfactionScore(contractEvaluate.getTotalScore());          //满意度 扣分
                    enviro.setSatisfactionMoney(contractEvaluate.getTotalMoney());          //满意度 扣款

                    //获取 满意度 评定项下面的各类指标
                    for (ContractTarget contractTarget : targetList) {
                        switch (contractTarget.getCode()) {
                            case "10501":
                                //不满意问题项 数据获取
                                enviro.setSatisfactionNum(contractTarget.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    }
                    break;
                }
                case "106":         //区域监评
                {
                    enviro.setCheckScore(contractEvaluate.getTotalScore());          //区域监评 扣分
                    enviro.setCheckMoney(contractEvaluate.getTotalMoney());          //区域监评 扣款

                    //获取 区域监评 评定项下面的各类指标
                    for (ContractTarget contractTarget : targetList) {
                        switch (contractTarget.getCode()) {
                            case "10601":
                                //严重不合格 数据获取
                                enviro.setCheckSerious(contractTarget.getOccurNum());
                                break;
                            case "10602":
                                //黑色预警 数据获取
                                enviro.setCheckBlack(contractTarget.getOccurNum());
                                break;
                            case "10603":
                                //黄牌 数据获取
                                enviro.setCheckYellow(contractTarget.getOccurNum());
                                break;
                            case "10604":
                                //红牌 数据获取
                                enviro.setCheckRed(contractTarget.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    }
                    break;
                }
                case "107":         //飞行检查
                {
                    enviro.setFlightScore(contractEvaluate.getTotalScore());          //飞行检查 扣分
                    enviro.setFlightMoney(contractEvaluate.getTotalMoney());          //飞行检查 扣款

                    //获取 飞行检查 评定项下面的各类指标
                    for (ContractTarget contractTarget : targetList) {
                        switch (contractTarget.getCode()) {
                            case "10701":
                                //黑色预警 数据获取
                                enviro.setFlightBlack(contractTarget.getOccurNum());
                                break;
                            case "10702":
                                //黄牌 数据获取
                                enviro.setFlightYellow(contractTarget.getOccurNum());
                                break;
                            case "10703":
                                //红牌 数据获取
                                enviro.setFlightRed(contractTarget.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    }
                    break;
                }
                case "108":         //第三方调查
                {
                    enviro.setThirdScore(contractEvaluate.getTotalScore());          //第三方调查 扣分
                    enviro.setThirdMoney(contractEvaluate.getTotalMoney());          //第三方调查 扣款

                    //获取 第三方调查 评定项下面的各类指标
                    for (ContractTarget contractTarget : targetList) {
                        switch (contractTarget.getCode()) {
                            case "10801":
                                //红牌数 数据获取
                                enviro.setThirdRed(contractTarget.getOccurNum());
                                break;
                            case "10802":
                                //黄牌数 数据获取
                                enviro.setThirdYellow(contractTarget.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    }
                    break;
                }
                default:
                    break;
            }
        }
        return enviro;
    }

    /*----------------------------------------公共环境保洁  end-----------------------------------------------*/


    /*----------------------------------------垃圾清运  start-----------------------------------------------*/

    //获取垃圾清运 DTO数据
    private GarbageCleaning getGarbage(int recordId) {
        Condition condition;
        Example.Criteria criteria;
        //获取考核记录信息
        GarbageCleaning garbage = recordMapper.selectGarbageByPrimaryKey(recordId);

        //获取该考核记录下的所有评定项 垃圾清运 有11项
        condition = new Condition(ContractEvaluate.class);
        criteria = condition.createCriteria();
        criteria.andEqualTo("recordId", recordId);
        List<ContractEvaluate> evaluateList = evaluateMapper.selectByCondition(condition);

        for (ContractEvaluate contractEvaluate : evaluateList) {
            List<ContractTarget> targetList = targetMapper.selectByEvaluateId(contractEvaluate.getId());
            switch (contractEvaluate.getCode()) {
                case "201":         //时间要求
                    garbage.setTimeNum(targetList.get(0).getOccurNum());
                    garbage.setTimeScore(contractEvaluate.getTotalScore());
                    break;
                case "202":         //清运标准及时性
                    garbage.setStandardNum(targetList.get(0).getOccurNum());
                    garbage.setStandardScore(contractEvaluate.getTotalScore());
                    break;
                case "203":         //垃圾袋供给
                    garbage.setGarbageNum(targetList.get(0).getOccurNum());
                    garbage.setGarbageScore(contractEvaluate.getTotalScore());
                    break;
                case "204":         //散落现象
                    garbage.setScatterNum(targetList.get(0).getOccurNum());
                    garbage.setScatterScore(contractEvaluate.getTotalScore());
                    break;
                case "205":         //清理现场
                    garbage.setCleanNum(targetList.get(0).getOccurNum());
                    garbage.setCleanScore(contractEvaluate.getTotalScore());
                    break;
                case "206":         //车辆管理
                    garbage.setCarScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "20601": {
                                //车辆外观、车况，证照不符合政府部门的有关规定
                                garbage.setCarNum(t.getOccurNum());
                                break;
                            }
                            case "20602": {
                                //发现随意停放而影响业主或车辆通行次数
                                garbage.setOwnerNum(t.getOccurNum());
                                break;
                            }
                            default:
                                break;
                        }
                    });
                    break;
                case "207":         //工作配合
                    garbage.setWorkScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "20701": {
                                //出现故意拖延或拒不配合的情况
                                garbage.setWillfullyNum(t.getOccurNum());
                                break;
                            }
                            case "20702": {
                                //接到整改通知未按要求进行整改
                                garbage.setRequireNum(t.getOccurNum());
                                break;
                            }
                            case "20703": {
                                //不配合监督检查与甲方管理人员或员工发生口角及纠纷
                                garbage.setMouthNum(t.getOccurNum());
                                break;
                            }
                            case "20704": {
                                //不配合门岗安全检查
                                garbage.setSecurityNum(t.getOccurNum());
                                break;
                            }
                            default:
                                break;
                        }
                    });
                    break;
                case "208":         //垃圾处理
                    garbage.setManageScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "20801": {
                                //发现乱倒垃圾情况
                                garbage.setDisorderlyNum(t.getOccurNum());
                                break;
                            }
                            case "20802": {
                                //发现中转站场管理杂乱
                                garbage.setManageNum(t.getOccurNum());
                                break;
                            }
                            default:
                                break;
                        }
                    });
                    break;
                case "209":         //业户投诉
                    garbage.setComplaintNum(targetList.get(0).getOccurNum());
                    garbage.setComplaintScore(contractEvaluate.getTotalScore());
                    break;
                case "210":         //员工和现场管理
                    garbage.setStaffScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "21001": {
                                //不遵守分公司制定的各项物业管理服务制度
                                garbage.setPropertyNum(t.getOccurNum());
                                break;
                            }
                            case "21002": {
                                //员工违反现场管理制度情况
                                garbage.setSceneNum(t.getOccurNum());
                                break;
                            }
                            case "21003": {
                                //员工用工纠纷
                                garbage.setDisputeNum(t.getOccurNum());
                                break;
                            }
                            case "21004": {
                                //收捡垃圾、违规存放、对方纸皮等杂物
                                garbage.setCollectNum(t.getOccurNum());
                                break;
                            }
                            default:
                                break;
                        }
                    });
                    break;
                case "211":         //安全管理
                    garbage.setSaveScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "21101": {
                                //损坏公共设备设施或造成业主人身财产损失
                                garbage.setDamageNum(t.getOccurNum());
                                break;
                            }
                            case "21102": {
                                //未严格执行消防和安全生产管理制度
                                garbage.setExecuteNum(t.getOccurNum());
                                break;
                            }
                            default:
                                break;
                        }
                    });
                    break;
                default:
                    break;
            }
        }
        return garbage;
    }

    /*----------------------------------------垃圾清运  end-----------------------------------------------*/


    /*----------------------------------------公共绿化养护  start-----------------------------------------------*/

    //获取公共绿化养护 DTO数据
    private GreeningMaintenance getGreening(int recordId) {
        Condition condition;
        Example.Criteria criteria;
        //获取考核记录信息
        GreeningMaintenance green = recordMapper.selectGreenByPrimaryKey(recordId);

        //获取该考核记录下的所有评定项 垃圾清运 有11项
        condition = new Condition(ContractEvaluate.class);
        criteria = condition.createCriteria();
        criteria.andEqualTo("recordId", recordId);
        List<ContractEvaluate> evaluateList = evaluateMapper.selectByCondition(condition);

        for (ContractEvaluate contractEvaluate : evaluateList) {
            List<ContractTarget> targetList = targetMapper.selectByEvaluateId(contractEvaluate.getId());
            switch (contractEvaluate.getCode()) {
                case "301": {            //甲方监督员巡查评定
                    green.setMasterScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "30101":
                                //未整改或整改效果达不到甲方要求(个)
                                green.setMasterNum(t.getOccurNum());
                                break;
                            case "30102":
                                //反复未整改(个)
                                green.setMasterNotNum(t.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    });
                    break;
                }
                case "302": {            //甲方监督部门与乙方负责人联合检查评定
                    green.setAbNotNumScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "30201":
                                //一般不合格(项)
                                green.setAbCommonNum(t.getOccurNum());
                                break;
                            case "30202":
                                //严重不合格(项)
                                green.setAbSeriousNum(t.getOccurNum());
                                break;
                            case "30203":
                                //未整改或整改效果达不到合同要求(个)
                                green.setAbNum(t.getOccurNum());
                                break;
                            case "30204":
                                //反复未整改(个)
                                green.setAbNotNum(t.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    });
                    break;
                }
                case "303": {            //甲方区域物业品质检查
                    green.setRegionScore(contractEvaluate.getTotalScore());
                    targetList.forEach(t -> {
                        switch (t.getCode()) {
                            case "30301":
                                //一般不合格(项)
                                green.setRegionCommonNum(t.getOccurNum());
                                break;
                            case "30302":
                                //严重不合格(项)
                                green.setRegionSeriousNum(t.getOccurNum());
                                break;
                            case "30303":
                                //反复未整改(个)
                                green.setRegionNotNum(t.getOccurNum());
                                break;
                            case "30304":
                                //未整改或整改效果达不到合同要求(个)
                                green.setRegionNum(t.getOccurNum());
                                break;
                            default:
                                break;
                        }
                    });
                    break;
                }
                case "304": {            //甲方总公司品质管理中心品质抽查评定
                    green.setHeadquartersSeriousNum(targetList.get(0).getOccurNum());
                    green.setHeadquartersSocre(contractEvaluate.getTotalScore());
                    break;
                }
                default:
                    break;
            }
        }
        return green;
    }

    /*----------------------------------------公共绿化养护  end-----------------------------------------------*/


    /*----------------------------------------Excel导入导出部分  start-----------------------------------------------*/

    /**
     * @Description: 导出委外Excel     根据委外类别code获取不同的模板
     * @Params: [response, organCodeList, commname, supplierName, year, month, exCode]
     * @return: void
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public void exportExContractExcel(HttpServletResponse response, List<String> organCodeList, String commname, String supplierName,
                                      String year, String month, String exCode) {
        Workbook workBook1;
        Workbook workBook2;
        Workbook workBook3;
        Sheet sheet;
        Row tempRow;
        String fileName1;
        String fileName2;
        String fileName3;
        List<Integer> recordIdList = new ArrayList<>();

        //获取查询出来的 考核数据列表
        JSONObject json = getRecordListByCondition(organCodeList, commname, supplierName, year, month, exCode, 0, 0, false);
        List<ContractRecord> recordList = (List<ContractRecord>) json.get("recordList");
        for (ContractRecord record : recordList) {
            recordIdList.add(record.getId());
        }
        //对列表数据 按委外类别 进行分类(废弃)
//        Map<String,List<Integer>> recordIdMap = new HashMap<>();
//        recordIdMap.put("1",new ArrayList<>());
//        recordIdMap.put("2",new ArrayList<>());
//        recordIdMap.put("3",new ArrayList<>());
//        recordList.forEach(r -> {
//            switch (r.getExCode()){
//                case "1":{
//                    recordIdMap.get("1").add(r.getId());
//                    break;
//                }
//                case "2":{
//                    recordIdMap.get("2").add(r.getId());
//                    break;
//                }
//                case "3":{
//                    recordIdMap.get("3").add(r.getId());
//                    break;
//                }
//                default:
//                    break;
//            }
//        });

        //根据委外类别code 获取工作簿模板 并分类导出
//        for (String code : recordIdMap.keySet()) {
        switch (exCode) {
            case "1": {
                //若是无该类别的数据 则跳出 不需要导出
//                    recordIdList = recordIdMap.get(code);
//                    if(recordIdList.size() == 0) break;

                //公共环境保洁
                fileName1 = "委外供应商月度考核表(公共环境保洁).xlsx";
                workBook1 = getWorkbook(fileName1);
                // 获取对应委外类别 模板sheet页
                sheet = workBook1.getSheetAt(0);
                // 获取对应委外类别 详细数据
                List<EnvironmentalClean> enviroList = new ArrayList<>();
                recordIdList.forEach(recordId -> {
                    enviroList.add(getEnvironmental(recordId));
                });
                //循环写入 公共环境保洁 数据
                CellStyle cellStyle = workBook1.createCellStyle();
                cellStyle.setBorderTop(BorderStyle.THIN);           // 上部边框
                cellStyle.setBorderBottom(BorderStyle.THIN);        // 底部边框
                cellStyle.setBorderLeft(BorderStyle.THIN);          // 左部边框
                cellStyle.setBorderRight(BorderStyle.THIN);         // 右部边框

                int i = 1;
                for (EnvironmentalClean enviro : enviroList) {
                    //获取该数据行
                    tempRow = sheet.createRow(1 + i);
                    //设置该数据行的每个单元格的样式
                    for (int j = 0; j <= 50; j++) {
                        tempRow.createCell(j).setCellStyle(cellStyle);
                    }
                    // ------------        写入考核记录 基础数据        ------------ //
                    //复用写入考核记录部分基础数据
                    writeExcel(tempRow, enviro);
                    // ------ 写入 委外类别
                    tempRow.getCell(3).setCellValue("公共环境保洁");

                    //其他违约
                    // ------ 写入 违约事项
                    tempRow.getCell(43).setCellValue(enviro.getOtherDescribe());
                    // ------ 写入 扣分
                    tempRow.getCell(44).setCellValue(enviro.getOtherScore());
                    // ------ 写入 扣款
                    tempRow.getCell(45).setCellValue(enviro.getOtherMoney().doubleValue());

                    // ------ 写入 本月得分扣款率扣款
                    tempRow.getCell(46).setCellValue(enviro.getBreakMoney().doubleValue());
                    // ------ 写入 合计扣分
                    tempRow.getCell(47).setCellValue(enviro.getTotalScore());
                    // ------ 写入 合计扣款
                    tempRow.getCell(48).setCellValue(enviro.getTotalMoney().doubleValue());

                    //奖励
                    // ------ 写入 奖励事由
                    tempRow.getCell(49).setCellValue(enviro.getRewardDescribe());
                    // ------ 写入 奖励金额
                    tempRow.getCell(50).setCellValue(enviro.getRewardMoney().doubleValue());

                    // ------------        写入公共环境保洁 详细数据        ------------ //

                    //缺勤
                    // ------ 写入 本月应出勤人数
                    tempRow.getCell(13).setCellValue(enviro.getTotalNum());
                    // ------ 写入 本月实际出勤人数
                    tempRow.getCell(14).setCellValue(enviro.getTotalActualNum());
                    // ------ 写入 本月加班总时数
                    tempRow.getCell(15).setCellValue(enviro.getTotalOvertime());
                    // ------ 写入 折算为加班人数
                    tempRow.getCell(16).setCellValue(enviro.getOvertimeNum());
                    // ------ 写入 缺勤人数
                    tempRow.getCell(17).setCellValue(enviro.getVacancyNum());

                    //出勤率
                    // ------ 写入 出勤率(%）
                    tempRow.getCell(18).setCellValue(enviro.getVacancyRate());
                    // ------ 写入 扣款
                    tempRow.getCell(19).setCellValue(enviro.getVacancyMoney() == null ? 0 : enviro.getVacancyMoney().doubleValue());

                    //日常巡查
                    // ------ 写入 日常巡查月度得分
                    tempRow.getCell(20).setCellValue(enviro.getInspectionScore());

                    //投诉
                    // ------ 写入 一般投诉项
                    tempRow.getCell(21).setCellValue(enviro.getCommonComplaint());
                    // ------ 写入 严重投诉项
                    tempRow.getCell(22).setCellValue(enviro.getSeriousComplaint());
                    // ------ 写入 扣分
                    tempRow.getCell(23).setCellValue(enviro.getComplaintScore());
                    // ------ 写入 扣款
                    tempRow.getCell(24).setCellValue(enviro.getComplaintMoney() == null ? 0 : enviro.getComplaintMoney().doubleValue());

                    //满意度
                    // ------ 写入 不满意问题项
                    tempRow.getCell(25).setCellValue(enviro.getSatisfactionNum());
                    // ------ 写入 扣分
                    tempRow.getCell(26).setCellValue(enviro.getSatisfactionScore());
                    // ------ 写入 扣款
                    tempRow.getCell(27).setCellValue(enviro.getSatisfactionMoney() == null ? 0 : enviro.getSatisfactionMoney().doubleValue());

                    //区域监评
                    // ------ 写入 严重不合格
                    tempRow.getCell(28).setCellValue(enviro.getCheckSerious());
                    // ------ 写入 黑色预警
                    tempRow.getCell(29).setCellValue(enviro.getCheckBlack());
                    // ------ 写入 黄牌
                    tempRow.getCell(30).setCellValue(enviro.getCheckYellow());
                    // ------ 写入 红牌
                    tempRow.getCell(31).setCellValue(enviro.getCheckRed());
                    // ------ 写入 扣分
                    tempRow.getCell(32).setCellValue(enviro.getCheckScore());
                    // ------ 写入 扣款
                    tempRow.getCell(33).setCellValue(enviro.getCheckMoney() == null ? 0 : enviro.getCheckMoney().doubleValue());

                    //飞行检查
                    // ------ 写入 黑色预警
                    tempRow.getCell(34).setCellValue(enviro.getFlightBlack());
                    // ------ 写入 黄牌
                    tempRow.getCell(35).setCellValue(enviro.getFlightYellow());
                    // ------ 写入 红牌
                    tempRow.getCell(36).setCellValue(enviro.getFlightRed());
                    // ------ 写入 扣分
                    tempRow.getCell(37).setCellValue(enviro.getFlightScore());
                    // ------ 写入 扣款
                    tempRow.getCell(38).setCellValue(enviro.getFlightMoney() == null ? 0 : enviro.getFlightMoney().doubleValue());

                    //第三方调查
                    // ------ 写入 红牌
                    tempRow.getCell(39).setCellValue(enviro.getThirdRed());
                    // ------ 写入 黄牌
                    tempRow.getCell(40).setCellValue(enviro.getThirdYellow());
                    // ------ 写入 扣分
                    tempRow.getCell(41).setCellValue(enviro.getThirdScore());
                    // ------ 写入 扣款
                    tempRow.getCell(42).setCellValue(enviro.getThirdMoney() == null ? 0 : enviro.getThirdMoney().doubleValue());

                    //循环数+1
                    i++;
                }

                //导出
                exportExcel(response, workBook1, fileName1);
                break;
            }
            case "2": {
                //若是无该类别的数据 则跳出 不需要导出
//                    recordIdList = recordIdMap.get(code);
//                    if(recordIdList.size() == 0) break;

                //垃圾清运
                fileName2 = "委外供应商月度考核表(垃圾清运).xlsx";
                workBook2 = getWorkbook(fileName2);
                // 获取对应委外类别 模板sheet页
                sheet = workBook2.getSheetAt(0);
                // 获取对应委外类别 详细数据
                List<GarbageCleaning> garbageList = new ArrayList<>();
                recordIdList.forEach(recordId -> {
                    garbageList.add(getGarbage(recordId));
                });
                //循环写入 垃圾清运 数据
                CellStyle cellStyle = workBook2.createCellStyle();
                cellStyle.setBorderTop(BorderStyle.THIN);           // 上部边框
                cellStyle.setBorderBottom(BorderStyle.THIN);        // 底部边框
                cellStyle.setBorderLeft(BorderStyle.THIN);          // 左部边框
                cellStyle.setBorderRight(BorderStyle.THIN);         // 右部边框

                int i = 1;
                for (GarbageCleaning garbag : garbageList) {
                    //获取该数据行
                    tempRow = sheet.createRow(1 + i);
                    //设置该数据行的每个单元格的样式
                    for (int j = 0; j <= 49; j++) {
                        tempRow.createCell(j).setCellStyle(cellStyle);
                    }
                    // ------------        写入考核记录 基础数据        ------------ //
                    //复用写入考核记录部分基础数据
                    writeExcel(tempRow, garbag);
                    // ------ 写入 委外类别
                    tempRow.getCell(3).setCellValue("垃圾清运");

                    //其他违约
                    // ------ 写入 违约事项
                    tempRow.getCell(44).setCellValue(garbag.getOtherDescribe());
                    // ------ 写入 扣分
                    tempRow.getCell(45).setCellValue(garbag.getOtherScore());
                    // ------ 写入 扣款
                    tempRow.getCell(46).setCellValue(garbag.getOtherMoney().doubleValue());

                    // ------ 写入 合计扣分
                    tempRow.getCell(47).setCellValue(garbag.getTotalScore());
                    // ------ 写入 本月得分扣款率扣款
                    tempRow.getCell(48).setCellValue(garbag.getBreakMoney().doubleValue());
                    // ------ 写入 合计扣款
                    tempRow.getCell(49).setCellValue(garbag.getTotalMoney().doubleValue());

                    // ------------        写入公共环境保洁 详细数据        ------------ //

                    //时间要求
                    // ------ 写入 未在规定时间内清运垃圾
                    tempRow.getCell(13).setCellValue(garbag.getTimeNum());
                    // ------ 写入 扣分
                    tempRow.getCell(14).setCellValue(garbag.getTimeScore());

                    //清运标准及时性
                    // ------ 写入 有垃圾未在当天完成清运
                    tempRow.getCell(15).setCellValue(garbag.getStandardNum());
                    // ------ 写入 扣分
                    tempRow.getCell(16).setCellValue(garbag.getStandardScore());

                    //垃圾袋供给
                    // ------ 写入 清运垃圾未实行袋装打包
                    tempRow.getCell(17).setCellValue(garbag.getGarbageNum());
                    // ------ 写入 扣分
                    tempRow.getCell(18).setCellValue(garbag.getGarbageScore());

                    //散落现象
                    // ------ 写入 发现次数
                    tempRow.getCell(19).setCellValue(garbag.getScatterNum());
                    // ------ 写入 扣分
                    tempRow.getCell(20).setCellValue(garbag.getScatterScore());

                    //清理现场
                    // ------ 写入 未及时清扫现场及整理、清理垃圾桶
                    tempRow.getCell(21).setCellValue(garbag.getCleanNum());
                    // ------ 写入 扣分
                    tempRow.getCell(22).setCellValue(garbag.getCleanScore());

                    //车辆管理
                    // ------ 写入 车辆外观、车况，证照不符合政府部门的有关规定
                    tempRow.getCell(23).setCellValue(garbag.getCarNum());
                    // ------ 写入 发现随意停放而影响业主或车辆通行次数
                    tempRow.getCell(24).setCellValue(garbag.getOwnerNum());
                    // ------ 写入 扣分
                    tempRow.getCell(25).setCellValue(garbag.getCarScore());

                    //工作配合
                    // ------ 写入 出现故意拖延或拒不配合的情况
                    tempRow.getCell(26).setCellValue(garbag.getWillfullyNum());
                    // ------ 写入 接到整改通知未按要求进行整改
                    tempRow.getCell(27).setCellValue(garbag.getRequireNum());
                    // ------ 写入 不配合监督检查与甲方管理人员或员工发生口角及纠纷
                    tempRow.getCell(28).setCellValue(garbag.getMouthNum());
                    // ------ 写入 不配合门岗安全检查
                    tempRow.getCell(29).setCellValue(garbag.getSecurityNum());
                    // ------ 写入 扣分
                    tempRow.getCell(30).setCellValue(garbag.getWorkScore());

                    //垃圾处理
                    // ------ 写入 发现乱倒垃圾情况
                    tempRow.getCell(31).setCellValue(garbag.getDisorderlyNum());
                    // ------ 写入 发现中转站场管理杂乱
                    tempRow.getCell(32).setCellValue(garbag.getManageNum());
                    // ------ 写入 扣分
                    tempRow.getCell(33).setCellValue(garbag.getManageScore());

                    //业户投诉
                    // ------ 写入 有效投诉
                    tempRow.getCell(34).setCellValue(garbag.getComplaintNum());
                    // ------ 写入 扣分
                    tempRow.getCell(35).setCellValue(garbag.getComplaintScore());

                    //员工和现场管理
                    // ------ 写入 不遵守分公司制定的各项物业管理服务制度
                    tempRow.getCell(36).setCellValue(garbag.getPropertyNum());
                    // ------ 写入 员工违反现场管理制度情况
                    tempRow.getCell(37).setCellValue(garbag.getSceneNum());
                    // ------ 写入 员工用工纠纷
                    tempRow.getCell(38).setCellValue(garbag.getDisputeNum());
                    // ------ 写入 收捡垃圾、违规存放、对方纸皮等杂物
                    tempRow.getCell(39).setCellValue(garbag.getCollectNum());
                    // ------ 写入 扣分
                    tempRow.getCell(40).setCellValue(garbag.getStaffScore());

                    //安全管理
                    // ------ 写入 损坏公共设备设施或造成业主人身财产损失
                    tempRow.getCell(41).setCellValue(garbag.getDamageNum());
                    // ------ 写入 未严格执行消防和安全生产管理制度
                    tempRow.getCell(42).setCellValue(garbag.getExecuteNum());
                    // ------ 写入 扣分
                    tempRow.getCell(43).setCellValue(garbag.getSaveScore());

                    //循环数+1
                    i++;
                }

                //导出
                exportExcel(response, workBook2, fileName2);
                break;
            }
            case "3": {
                //若是无该类别的数据 则跳出 不需要导出
//                    recordIdList = recordIdMap.get(code);
//                    if(recordIdList.size() == 0) break;

                //公共绿化养护
                fileName3 = "委外供应商月度考核表(公共绿化养护).xlsx";
                workBook3 = getWorkbook(fileName3);
                // 获取对应委外类别 模板sheet页
                sheet = workBook3.getSheetAt(0);
                // 获取对应委外类别 详细数据
                List<GreeningMaintenance> greenList = new ArrayList<>();
                recordIdList.forEach(recordId -> greenList.add(getGreening(recordId)));
                //循环写入 公共绿化养护 数据
                CellStyle cellStyle = workBook3.createCellStyle();
                cellStyle.setBorderTop(BorderStyle.THIN);           // 上部边框
                cellStyle.setBorderBottom(BorderStyle.THIN);        // 底部边框
                cellStyle.setBorderLeft(BorderStyle.THIN);          // 左部边框
                cellStyle.setBorderRight(BorderStyle.THIN);         // 右部边框

                for (int i = 0; i < greenList.size(); i++) {
                    GreeningMaintenance greening = greenList.get(i);

                    //获取该数据行
                    tempRow = sheet.createRow(2 + i);
                    //设置该数据行的每个单元格的样式
                    for (int j = 0; j <= 35; j++) {
                        tempRow.createCell(j).setCellStyle(cellStyle);
                    }
                    // ------------        写入考核记录 基础数据        ------------ //
                    //复用写入考核记录部分基础数据
                    writeExcel(tempRow, greening);
                    // ------ 写入 委外类别
                    tempRow.getCell(3).setCellValue("公共绿化养护");

                    //监督员巡查评定
                    tempRow.getCell(13).setCellValue(greening.getMasterNum());
                    tempRow.getCell(14).setCellValue(greening.getMasterNotNum());
                    tempRow.getCell(15).setCellValue(greening.getMasterScore());

                    //监督部门与乙方负责人联合检查评定
                    tempRow.getCell(16).setCellValue(greening.getAbCommonNum());
                    tempRow.getCell(17).setCellValue(greening.getAbSeriousNum());
                    tempRow.getCell(18).setCellValue(greening.getAbNum());
                    tempRow.getCell(19).setCellValue(greening.getAbNotNum());
                    tempRow.getCell(20).setCellValue(greening.getAbNotNumScore());

                    //区域物业品质检查评定
                    tempRow.getCell(21).setCellValue(greening.getRegionCommonNum());
                    tempRow.getCell(22).setCellValue(greening.getRegionSeriousNum());
                    tempRow.getCell(23).setCellValue(greening.getRegionNum());
                    tempRow.getCell(24).setCellValue(greening.getRegionNotNum());
                    tempRow.getCell(25).setCellValue(greening.getRegionScore());

                    //品质管理中心品质抽查评定
                    tempRow.getCell(26).setCellValue(greening.getHeadquartersSeriousNum());
                    tempRow.getCell(27).setCellValue(greening.getHeadquartersSocre());

                    //其他违约
                    tempRow.getCell(28).setCellValue(greening.getOtherDescribe());
                    tempRow.getCell(29).setCellValue(greening.getOtherScore());
                    tempRow.getCell(30).setCellValue(greening.getOtherMoney().doubleValue());

                    //奖励
                    tempRow.getCell(31).setCellValue(greening.getRewardDescribe());
                    tempRow.getCell(32).setCellValue(greening.getRewardScore());

                    //合计扣分
                    tempRow.getCell(33).setCellValue(greening.getTotalScore());

                    //本月得分扣款率扣款
                    tempRow.getCell(34).setCellValue(greening.getBreakMoney().doubleValue());

                    //合计扣款
                    tempRow.getCell(35).setCellValue(greening.getTotalMoney().doubleValue());

                }
                exportExcel(response, workBook3, fileName3);
                break;
            }
            default:
                break;
        }
//        }
    }

    /**
     * @Description: 导入委外Excel
     * @Params: [multipartFile, organMap, exCode, userCode]
     * @return: void
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public void importExContractExcel(MultipartFile multipartFile, Map<String, String> organMap, String exCode, String userCode) throws Exception {
        Workbook workbook;
        Row tempRow;
        ContractRecord tempRecord;

        Cell cell5;
        Cell cell7;
        Cell cell9;

        try {
            String fileName = multipartFile.getOriginalFilename();
            BufferedInputStream bf = new BufferedInputStream(multipartFile.getInputStream());
            if (fileName.endsWith("xls")) {                 //  .xls Excel 格式
                workbook = new HSSFWorkbook(bf);
            } else {                                        //  .xlsx Excel 格式
                workbook = new XSSFWorkbook(bf);
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new Exception("表格格式不对！");
        }
        Sheet sheet = workbook.getSheetAt(0);
        //获取表格最大物理行数
        int maxRow = sheet.getPhysicalNumberOfRows();
        //获取导入EXCEL实际有数据的行数
        int j = 2;
        for (int i = 2; i < maxRow; i++) {
            tempRow = sheet.getRow(i);
            //通过表格 区域名称列 来判断实际有数据行数 若有一列为空 则直接
            if (tempRow.getCell(0) != null && tempRow.getCell(0).getStringCellValue().length() > 0) {
                j++;
            } else break;
        }
        maxRow = j;
        //表格第1、2行为表头 第三行开始为数据
        if (maxRow < 3) {
            throw new Exception("表格数据为空！");
        } else {
            //权限判断 以及 重复考核记录判断
            for (int i = 2; i < maxRow; i++) {
                tempRow = sheet.getRow(i);

                //  获取区域名称 判断是否拥有该区域的权限
                if (tempRow.getCell(0) == null || !organMap.containsKey(tempRow.getCell(0).getStringCellValue())) {
                    throw new Exception("表格第" + (i + 1) + "行数据，没有该区域的权限！");
                }

                //  获取项目名称 以及 组团名称（组团改为非必填） 判断是否为空
                if (tempRow.getCell(1) == null || tempRow.getCell(1).getStringCellValue().trim().length() == 0) {
                    throw new Exception("表格第" + (i + 1) + "行数据，项目名称为空！");
                }

                //  获取该考核记录判断数据
                tempRecord = new ContractRecord();
                cell5 = tempRow.getCell(5);
                cell7 = tempRow.getCell(7);
                cell9 = tempRow.getCell(9);
                //  供应商编号
                if (cell5 == null)
                    throw new Exception("表格第" + (i + 1) + "行数据供应商编号为空！");
                else {
                    cell5.setCellType(CellType.STRING);
                    tempRecord.setSupplierCode(cell5.getStringCellValue());
                }
                //  合同编号
                if (cell7 == null)
                    throw new Exception("表格第" + (i + 1) + "行数据合同编号为空！");
                else {
                    cell7.setCellType(CellType.STRING);
                    tempRecord.setContractCode(cell7.getStringCellValue());
                }
                //  考核 年份、月份
                if (cell9 == null)
                    throw new Exception("表格第" + (i + 1) + "行数据考核月份为空！");
                else {
                    int index = cell9.getStringCellValue().indexOf("-");
                    if (index == -1) {
                        throw new Exception("表格第" + (i + 1) + "行数据，请填写正确的考核月份数据（如:2018-09）！");
                    }
                    tempRecord.setYear(cell9.getStringCellValue().substring(0, index));           //考核年份
                    tempRecord.setMonth(cell9.getStringCellValue().substring(index + 1));          //考核月份
                }

                if (checkIsExist(tempRecord) == 1) { //插入操作判断是否重复
                    throw new Exception("表格第" + (i + 1) + "行数据已有重复考核记录！");
                }
            }
        }
        //通过权限判断以及重复判断 根据委外类别code 分别放入数据 执行添加操作
        switch (exCode) {
            case "1": {
                List<EnvironmentalClean> enviroList = new ArrayList<>();
                for (int i = 2; i < maxRow; i++) {
                    EnvironmentalClean enviro = new EnvironmentalClean();
                    tempRow = sheet.getRow(i);
                    // ------ 写入 区域名称 区域编码 项目名称 组团名称
                    enviro.setOrganName(tempRow.getCell(0).getStringCellValue());
                    enviro.setOrganCode(organMap.get(tempRow.getCell(0).getStringCellValue()));
                    enviro.setCommname(tempRow.getCell(1).getStringCellValue());
                    enviro.setGroupName(tempRow.getCell(2) == null ? "" : tempRow.getCell(2).getStringCellValue());

                    // ------ 写入 委外类别code
                    enviro.setExCode(exCode);
                    // ------ 写入 委外内容
                    enviro.setExContent(tempRow.getCell(4) == null ? "" : tempRow.getCell(4).getStringCellValue());
                    // ------ 写入 供应商编号
                    enviro.setSupplierCode(tempRow.getCell(5).getStringCellValue());
                    // ------ 写入 供应商名称
                    enviro.setSupplierName(tempRow.getCell(6) == null ? "" : tempRow.getCell(6).getStringCellValue());
                    // ------ 写入 合同编号
                    enviro.setContractCode(tempRow.getCell(7).getStringCellValue());
                    // ------ 写入 合同名称
                    enviro.setContractName(tempRow.getCell(8) == null ? "" : tempRow.getCell(8).getStringCellValue());

                    try {
                        // ------ 写入 考核年份 月份
                        cell9 = tempRow.getCell(9);
                        int index = cell9.getStringCellValue().indexOf("-");
                        enviro.setYear(cell9.getStringCellValue().substring(0, index));           //考核年份
                        enviro.setMonth(cell9.getStringCellValue().substring(index + 1));          //考核月份
                        // ------ 写入 月度实际评分
                        enviro.setTotalGrade(tempRow.getCell(10) == null ? 0 : tempRow.getCell(10) == null ? 0 : tempRow.getCell(10).getNumericCellValue());
                        // ------ 写入 每月承包费总额
                        enviro.setMonthContractMoney(new BigDecimal(tempRow.getCell(11) == null ? 0 : tempRow.getCell(11).getNumericCellValue()));
                        // ------ 写入 实际支付承包费
                        enviro.setActualContractMoney(new BigDecimal(tempRow.getCell(12) == null ? 0 : tempRow.getCell(12).getNumericCellValue()));

                        //其他违约
                        // ------ 写入 违约事项
                        enviro.setOtherDescribe(tempRow.getCell(43) == null ? "" : tempRow.getCell(43).getStringCellValue());
                        // ------ 写入 扣分
                        enviro.setOtherScore(tempRow.getCell(44) == null ? 0 : tempRow.getCell(44).getNumericCellValue());
                        // ------ 写入 扣款
                        enviro.setOtherMoney(new BigDecimal(tempRow.getCell(45) == null ? 0 : tempRow.getCell(45).getNumericCellValue()));

                        // ------ 写入 本月得分扣款率扣款
                        enviro.setBreakMoney(new BigDecimal(tempRow.getCell(46) == null ? 0 : tempRow.getCell(46).getNumericCellValue()));
                        // ------ 写入 合计扣分
                        enviro.setTotalScore(tempRow.getCell(47) == null ? 0 : tempRow.getCell(47).getNumericCellValue());
                        // ------ 写入 合计扣款
                        enviro.setTotalMoney(new BigDecimal(tempRow.getCell(48) == null ? 0 : tempRow.getCell(48).getNumericCellValue()));

                        //奖励
                        // ------ 写入 奖励事由
                        enviro.setRewardDescribe(tempRow.getCell(49) == null ? "" : tempRow.getCell(49).getStringCellValue());
                        // ------ 写入 奖励金额
                        enviro.setRewardMoney(new BigDecimal(tempRow.getCell(50) == null ? 0 : tempRow.getCell(50).getNumericCellValue()));

                        // ------------        写入公共环境保洁 详细数据        ------------ //

                        //缺勤
                        // ------ 写入 本月应出勤人数
                        enviro.setTotalNum(tempRow.getCell(13) == null ? 0 : (int) tempRow.getCell(13).getNumericCellValue());
                        // ------ 写入 本月实际出勤人数
                        enviro.setTotalActualNum(tempRow.getCell(14) == null ? 0 : (int) tempRow.getCell(14).getNumericCellValue());
                        // ------ 写入 本月加班总时数
                        enviro.setTotalOvertime(tempRow.getCell(15) == null ? 0 : (int) tempRow.getCell(15).getNumericCellValue());
                        // ------ 写入 折算为加班人数
                        enviro.setOvertimeNum(tempRow.getCell(16) == null ? 0 : (int) tempRow.getCell(16).getNumericCellValue());
                        // ------ 写入 缺勤人数
                        enviro.setVacancyNum(tempRow.getCell(17) == null ? 0 : (int) tempRow.getCell(17).getNumericCellValue());

                        //出勤率
                        // ------ 写入 出勤率(%）
                        enviro.setVacancyRate(tempRow.getCell(18) == null ? 0 : (int) tempRow.getCell(18).getNumericCellValue());
                        // ------ 写入 扣款
                        enviro.setVacancyMoney(new BigDecimal(tempRow.getCell(19) == null ? 0 : tempRow.getCell(19).getNumericCellValue()));

                        //日常巡查
                        // ------ 写入 日常巡查月度得分
                        enviro.setInspectionScore(tempRow.getCell(20) == null ? 0 : tempRow.getCell(20).getNumericCellValue());

                        //投诉
                        // ------ 写入 一般投诉项
                        enviro.setCommonComplaint(tempRow.getCell(21) == null ? 0 : (int) tempRow.getCell(21).getNumericCellValue());
                        // ------ 写入 严重投诉项
                        enviro.setSeriousComplaint(tempRow.getCell(22) == null ? 0 : (int) tempRow.getCell(22).getNumericCellValue());
                        // ------ 写入 扣分
                        enviro.setComplaintScore(tempRow.getCell(23) == null ? 0 : tempRow.getCell(23).getNumericCellValue());
                        // ------ 写入 扣款
                        enviro.setComplaintMoney(new BigDecimal(tempRow.getCell(24) == null ? 0 : tempRow.getCell(24).getNumericCellValue()));

                        //满意度
                        // ------ 写入 不满意问题项
                        enviro.setSatisfactionNum(tempRow.getCell(25) == null ? 0 : (int) tempRow.getCell(25).getNumericCellValue());
                        // ------ 写入 扣分
                        enviro.setSatisfactionScore(tempRow.getCell(26) == null ? 0 : tempRow.getCell(26).getNumericCellValue());
                        // ------ 写入 扣款
                        enviro.setSatisfactionMoney(new BigDecimal(tempRow.getCell(27) == null ? 0 : tempRow.getCell(27).getNumericCellValue()));

                        //区域监评
                        // ------ 写入 严重不合格
                        enviro.setCheckSerious(tempRow.getCell(28) == null ? 0 : (int) tempRow.getCell(28).getNumericCellValue());
                        // ------ 写入 黑色预警
                        enviro.setCheckBlack(tempRow.getCell(29) == null ? 0 : (int) tempRow.getCell(29).getNumericCellValue());
                        // ------ 写入 黄牌
                        enviro.setCheckYellow(tempRow.getCell(30) == null ? 0 : (int) tempRow.getCell(30).getNumericCellValue());
                        // ------ 写入 红牌
                        enviro.setCheckRed(tempRow.getCell(31) == null ? 0 : (int) tempRow.getCell(31).getNumericCellValue());
                        // ------ 写入 扣分
                        enviro.setCheckScore(tempRow.getCell(32) == null ? 0 : tempRow.getCell(32).getNumericCellValue());
                        // ------ 写入 扣款
                        enviro.setCheckMoney(new BigDecimal(tempRow.getCell(33) == null ? 0 : tempRow.getCell(33).getNumericCellValue()));

                        //飞行检查
                        // ------ 写入 黑色预警
                        enviro.setFlightBlack(tempRow.getCell(34) == null ? 0 : (int) tempRow.getCell(34).getNumericCellValue());
                        // ------ 写入 黄牌
                        enviro.setFlightYellow(tempRow.getCell(35) == null ? 0 : (int) tempRow.getCell(35).getNumericCellValue());
                        // ------ 写入 红牌
                        enviro.setFlightRed(tempRow.getCell(36) == null ? 0 : (int) tempRow.getCell(36).getNumericCellValue());
                        // ------ 写入 扣分
                        enviro.setFlightScore(tempRow.getCell(37) == null ? 0 : tempRow.getCell(37).getNumericCellValue());
                        // ------ 写入 扣款
                        enviro.setFlightMoney(new BigDecimal(tempRow.getCell(38) == null ? 0 : tempRow.getCell(38).getNumericCellValue()));

                        //第三方调查
                        // ------ 写入 红牌
                        enviro.setThirdRed(tempRow.getCell(39) == null ? 0 : (int) tempRow.getCell(39).getNumericCellValue());
                        // ------ 写入 黄牌
                        enviro.setThirdYellow(tempRow.getCell(40) == null ? 0 : (int) tempRow.getCell(40).getNumericCellValue());
                        // ------ 写入 扣分
                        enviro.setThirdScore(tempRow.getCell(41) == null ? 0 : tempRow.getCell(41).getNumericCellValue());
                        // ------ 写入 扣款
                        enviro.setThirdMoney(new BigDecimal(tempRow.getCell(42) == null ? 0 : tempRow.getCell(42).getNumericCellValue()));

                    } catch (Exception e) {
                        //捕获异常 并且打印异常信息 返回错误信息
                        e.printStackTrace();
                        throw new Exception("表格第" + (i + 1) + "行数据格式有误，请检查后再导入！");
                    }
                    enviroList.add(enviro);
                }
                //循环添加新数据
                enviroList.forEach(e -> {
                    saveRecord(e, userCode);
                });
                break;
            }
            case "2": {
                List<GarbageCleaning> garbagList = new ArrayList<>();
                for (int i = 2; i < maxRow; i++) {
                    GarbageCleaning garbag = new GarbageCleaning();
                    tempRow = sheet.getRow(i);
                    // ------ 写入 区域名称 区域编码 项目名称 组团名称
                    garbag.setOrganName(tempRow.getCell(0).getStringCellValue());
                    garbag.setOrganCode(organMap.get(tempRow.getCell(0).getStringCellValue()));
                    garbag.setCommname(tempRow.getCell(1).getStringCellValue());
                    garbag.setGroupName(tempRow.getCell(2) == null ? "" : tempRow.getCell(2).getStringCellValue());

                    // ------ 写入 委外类别code
                    garbag.setExCode(exCode);
                    // ------ 写入 委外内容
                    garbag.setExContent(tempRow.getCell(4) == null ? "" : tempRow.getCell(4).getStringCellValue());
                    // ------ 写入 供应商编号
                    garbag.setSupplierCode(tempRow.getCell(5).getStringCellValue());
                    // ------ 写入 供应商名称
                    garbag.setSupplierName(tempRow.getCell(6) == null ? "" : tempRow.getCell(6).getStringCellValue());
                    // ------ 写入 合同编号
                    garbag.setContractCode(tempRow.getCell(7).getStringCellValue());
                    // ------ 写入 合同名称
                    garbag.setContractName(tempRow.getCell(8) == null ? "" : tempRow.getCell(8).getStringCellValue());

                    try {
                        // ------ 写入 考核年份 月份
                        cell9 = tempRow.getCell(9);
                        int index = cell9.getStringCellValue().indexOf("-");
                        garbag.setYear(cell9.getStringCellValue().substring(0, index));           //考核年份
                        garbag.setMonth(cell9.getStringCellValue().substring(index + 1));          //考核月份
                        // ------ 写入 月度实际评分
                        garbag.setTotalGrade(tempRow.getCell(10) == null ? 0 : tempRow.getCell(10) == null ? 0 : tempRow.getCell(10).getNumericCellValue());
                        // ------ 写入 每月承包费总额
                        garbag.setMonthContractMoney(new BigDecimal(tempRow.getCell(11) == null ? 0 : tempRow.getCell(11).getNumericCellValue()));
                        // ------ 写入 实际支付承包费
                        garbag.setActualContractMoney(new BigDecimal(tempRow.getCell(12) == null ? 0 : tempRow.getCell(12).getNumericCellValue()));

                        //其他违约
                        // ------ 写入 违约事项
                        garbag.setOtherDescribe(tempRow.getCell(44) == null ? "" : tempRow.getCell(44).getStringCellValue());
                        // ------ 写入 扣分
                        garbag.setOtherScore(tempRow.getCell(45) == null ? 0 : tempRow.getCell(45).getNumericCellValue());
                        // ------ 写入 扣款
                        garbag.setOtherMoney(new BigDecimal(tempRow.getCell(46) == null ? 0 : tempRow.getCell(46).getNumericCellValue()));

                        // ------ 写入 合计扣分
                        garbag.setTotalScore(tempRow.getCell(47) == null ? 0 : tempRow.getCell(47).getNumericCellValue());
                        // ------ 写入 本月得分扣款率扣款
                        garbag.setBreakMoney(new BigDecimal(tempRow.getCell(48) == null ? 0 : tempRow.getCell(48).getNumericCellValue()));
                        // ------ 写入 合计扣款
                        garbag.setTotalMoney(new BigDecimal(tempRow.getCell(49) == null ? 0 : tempRow.getCell(49).getNumericCellValue()));

                        // ------------        写入垃圾清运 详细数据        ------------ //

                        //时间要求
                        // ------ 写入 未在规定时间内清运垃圾
                        garbag.setTimeNum(tempRow.getCell(13) == null ? 0 : (int) tempRow.getCell(13).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setTimeScore(tempRow.getCell(14) == null ? 0 : tempRow.getCell(14).getNumericCellValue());

                        //清运标准及时性
                        // ------ 写入 有垃圾未在当天完成清运
                        garbag.setStandardNum(tempRow.getCell(15) == null ? 0 : (int) tempRow.getCell(15).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setStandardScore(tempRow.getCell(16) == null ? 0 : tempRow.getCell(16).getNumericCellValue());

                        //垃圾袋供给
                        // ------ 写入 清运垃圾未实行袋装打包
                        garbag.setGarbageNum(tempRow.getCell(17) == null ? 0 : (int) tempRow.getCell(17).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setGarbageScore(tempRow.getCell(18) == null ? 0 : tempRow.getCell(18).getNumericCellValue());

                        //散落现象
                        // ------ 写入 发现次数
                        garbag.setScatterNum(tempRow.getCell(19) == null ? 0 : (int) tempRow.getCell(19).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setScatterScore(tempRow.getCell(20) == null ? 0 : tempRow.getCell(20).getNumericCellValue());

                        //清理现场
                        // ------ 写入 未及时清扫现场及整理、清理垃圾桶
                        garbag.setCleanNum(tempRow.getCell(21) == null ? 0 : (int) tempRow.getCell(21).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setCleanScore(tempRow.getCell(22) == null ? 0 : tempRow.getCell(22).getNumericCellValue());

                        //车辆管理
                        // ------ 写入 车辆外观、车况，证照不符合政府部门的有关规定
                        garbag.setCarNum(tempRow.getCell(23) == null ? 0 : (int) tempRow.getCell(23).getNumericCellValue());
                        // ------ 写入 发现随意停放而影响业主或车辆通行次数
                        garbag.setOwnerNum(tempRow.getCell(24) == null ? 0 : (int) tempRow.getCell(24).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setCarScore(tempRow.getCell(25) == null ? 0 : tempRow.getCell(25).getNumericCellValue());

                        //工作配合
                        // ------ 写入 出现故意拖延或拒不配合的情况
                        garbag.setWillfullyNum(tempRow.getCell(26) == null ? 0 : (int) tempRow.getCell(26).getNumericCellValue());
                        // ------ 写入 接到整改通知未按要求进行整改
                        garbag.setRequireNum(tempRow.getCell(27) == null ? 0 : (int) tempRow.getCell(27).getNumericCellValue());
                        // ------ 写入 不配合监督检查与甲方管理人员或员工发生口角及纠纷
                        garbag.setMouthNum(tempRow.getCell(28) == null ? 0 : (int) tempRow.getCell(28).getNumericCellValue());
                        // ------ 写入 不配合门岗安全检查
                        garbag.setSecurityNum(tempRow.getCell(29) == null ? 0 : (int) tempRow.getCell(29).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setWorkScore(tempRow.getCell(30) == null ? 0 : tempRow.getCell(30).getNumericCellValue());

                        //垃圾处理
                        // ------ 写入 发现乱倒垃圾情况
                        garbag.setDisorderlyNum(tempRow.getCell(31) == null ? 0 : (int) tempRow.getCell(31).getNumericCellValue());
                        // ------ 写入 发现中转站场管理杂乱
                        garbag.setManageNum(tempRow.getCell(32) == null ? 0 : (int) tempRow.getCell(32).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setManageScore(tempRow.getCell(33) == null ? 0 : tempRow.getCell(33).getNumericCellValue());

                        //业户投诉
                        // ------ 写入 有效投诉
                        garbag.setComplaintNum(tempRow.getCell(34) == null ? 0 : (int) tempRow.getCell(34).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setComplaintScore(tempRow.getCell(35) == null ? 0 : tempRow.getCell(35).getNumericCellValue());

                        //员工和现场管理
                        // ------ 写入 不遵守分公司制定的各项物业管理服务制度
                        garbag.setPropertyNum(tempRow.getCell(36) == null ? 0 : (int) tempRow.getCell(36).getNumericCellValue());
                        // ------ 写入 员工违反现场管理制度情况
                        garbag.setSceneNum(tempRow.getCell(37) == null ? 0 : (int) tempRow.getCell(37).getNumericCellValue());
                        // ------ 写入 员工用工纠纷
                        garbag.setDisputeNum(tempRow.getCell(38) == null ? 0 : (int) tempRow.getCell(38).getNumericCellValue());
                        // ------ 写入 收捡垃圾、违规存放、对方纸皮等杂物
                        garbag.setCollectNum(tempRow.getCell(39) == null ? 0 : (int) tempRow.getCell(39).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setStaffScore(tempRow.getCell(40) == null ? 0 : tempRow.getCell(40).getNumericCellValue());

                        //安全管理
                        // ------ 写入 损坏公共设备设施或造成业主人身财产损失
                        garbag.setDamageNum(tempRow.getCell(41) == null ? 0 : (int) tempRow.getCell(41).getNumericCellValue());
                        // ------ 写入 未严格执行消防和安全生产管理制度
                        garbag.setExecuteNum(tempRow.getCell(42) == null ? 0 : (int) tempRow.getCell(42).getNumericCellValue());
                        // ------ 写入 扣分
                        garbag.setSaveScore(tempRow.getCell(43) == null ? 0 : tempRow.getCell(43).getNumericCellValue());

                    } catch (Exception e) {
                        //捕获异常 并且打印异常信息 返回错误信息
                        e.printStackTrace();
                        throw new Exception("表格第" + (i + 1) + "行数据格式有误，请检查后再导入！");
                    }
                    garbagList.add(garbag);
                }
                //循环添加新数据
                garbagList.forEach(g -> {
                    saveRecord(g, userCode);
                });
                break;
            }
            case "3": {
                List<GreeningMaintenance> greeningMaintenanceList = new ArrayList<>();
                for (int i = 2; i < maxRow; i++) {
                    GreeningMaintenance g = new GreeningMaintenance();
                    tempRow = sheet.getRow(i);
                    g.setOrganName(tempRow.getCell(0).getStringCellValue());
                    g.setOrganCode(organMap.get(tempRow.getCell(0).getStringCellValue()));
                    g.setCommname(tempRow.getCell(1).getStringCellValue());
                    g.setGroupName(tempRow.getCell(2) == null ? "" : tempRow.getCell(2).getStringCellValue());

                    // ------ 写入 委外类别code
                    g.setExCode(exCode);
                    // ------ 写入 委外内容
                    g.setExContent(tempRow.getCell(4) == null ? "" : tempRow.getCell(4).getStringCellValue());
                    // ------ 写入 供应商编号
                    g.setSupplierCode(tempRow.getCell(5).getStringCellValue());
                    // ------ 写入 供应商名称
                    g.setSupplierName(tempRow.getCell(6) == null ? "" : tempRow.getCell(6).getStringCellValue());
                    // ------ 写入 合同编号
                    g.setContractCode(tempRow.getCell(7).getStringCellValue());
                    // ------ 写入 合同名称
                    g.setContractName(tempRow.getCell(8) == null ? "" : tempRow.getCell(8).getStringCellValue());

                    try {
                        // ------ 写入 考核年份 月份
                        cell9 = tempRow.getCell(9);
                        int index = cell9.getStringCellValue().indexOf("-");
                        g.setYear(cell9.getStringCellValue().substring(0, index));           //考核年份
                        g.setMonth(cell9.getStringCellValue().substring(index + 1));          //考核月份

                        // ------ 写入 月度实际评分
                        g.setTotalGrade(tempRow.getCell(10) == null ? 0 : tempRow.getCell(10) == null ? 0 : tempRow.getCell(10).getNumericCellValue());
                        // ------ 写入 每月承包费总额
                        g.setMonthContractMoney(new BigDecimal(tempRow.getCell(11) == null ? 0 : tempRow.getCell(11).getNumericCellValue()));
                        // ------ 写入 实际支付承包费
                        g.setActualContractMoney(new BigDecimal(tempRow.getCell(12) == null ? 0 : tempRow.getCell(12).getNumericCellValue()));

                        g.setMasterNum(tempRow.getCell(13) == null ? 0 : (int) tempRow.getCell(13).getNumericCellValue());
                        g.setMasterNotNum(tempRow.getCell(14) == null ? 0 : (int) tempRow.getCell(14).getNumericCellValue());
                        g.setMasterScore(tempRow.getCell(15) == null ? 0 : tempRow.getCell(15).getNumericCellValue());


                        g.setAbCommonNum(tempRow.getCell(16) == null ? 0 : (int) tempRow.getCell(16).getNumericCellValue());
                        g.setAbSeriousNum(tempRow.getCell(17) == null ? 0 : (int) tempRow.getCell(17).getNumericCellValue());
                        g.setAbNum(tempRow.getCell(18) == null ? 0 : (int) tempRow.getCell(18).getNumericCellValue());
                        g.setAbNotNum(tempRow.getCell(19) == null ? 0 : (int) tempRow.getCell(19).getNumericCellValue());
                        g.setAbNotNumScore(tempRow.getCell(20) == null ? 0 : tempRow.getCell(20).getNumericCellValue());

                        g.setRegionCommonNum(tempRow.getCell(21) == null ? 0 : (int) tempRow.getCell(21).getNumericCellValue());
                        g.setRegionSeriousNum(tempRow.getCell(22) == null ? 0 : (int) tempRow.getCell(22).getNumericCellValue());
                        g.setRegionNum(tempRow.getCell(23) == null ? 0 : (int) tempRow.getCell(23).getNumericCellValue());
                        g.setRegionNotNum(tempRow.getCell(24) == null ? 0 : (int) tempRow.getCell(24).getNumericCellValue());
                        g.setRegionScore(tempRow.getCell(25) == null ? 0 : tempRow.getCell(25).getNumericCellValue());

                        g.setHeadquartersSeriousNum(tempRow.getCell(26) == null ? 0 : (int) tempRow.getCell(26).getNumericCellValue());
                        g.setHeadquartersSocre(tempRow.getCell(27) == null ? 0 : tempRow.getCell(27).getNumericCellValue());

                        g.setOtherDescribe(tempRow.getCell(28) == null ? "" : tempRow.getCell(28).getStringCellValue());
                        g.setOtherScore(tempRow.getCell(29) == null ? 0 : tempRow.getCell(29).getNumericCellValue());
                        g.setOtherMoney(BigDecimal.valueOf(tempRow.getCell(30) == null ? 0 : tempRow.getCell(30).getNumericCellValue()));

                        g.setRewardDescribe(tempRow.getCell(31) == null ? "" : tempRow.getCell(31).getStringCellValue());
                        g.setRewardScore(tempRow.getCell(32) == null ? 0 : tempRow.getCell(32).getNumericCellValue());

                        g.setTotalScore(tempRow.getCell(33) == null ? 0 : tempRow.getCell(33).getNumericCellValue());
                        g.setBreakMoney(BigDecimal.valueOf(tempRow.getCell(34) == null ? 0 : tempRow.getCell(34).getNumericCellValue()));
                        g.setTotalMoney(BigDecimal.valueOf(tempRow.getCell(35) == null ? 0 : tempRow.getCell(35).getNumericCellValue()));
                    } catch (Exception e) {
                        //捕获异常 并且打印异常信息 返回错误信息
                        e.printStackTrace();
                        throw new Exception("表格第" + (i + 1) + "行数据格式有误，请检查后再导入！");
                    }
                    greeningMaintenanceList.add(g);
                }

                greeningMaintenanceList.forEach(g -> saveRecord(g, userCode));

                break;
            }
            default:
                break;
        }
    }

    /**
     * @Description: 下载委外Excel模板   根据委外类别code获取不同的模板
     * @Params: [response, exCode]
     * @return: void
     * @Author: SignV
     * @Date: 2018/10/8
     */
    public void downloadExContractExcel(HttpServletResponse response, String exCode) {
        OutputStream output;
        Workbook workBook = null;
        Sheet sheet;
        String fileName = null;
        //根据委外类别code获取不同的工作簿模板
        switch (exCode) {
            case "1": {
                //公共环境保洁
                fileName = "import" + File.separator + "委外供应商月度考核表(公共环境保洁).xlsx";
                workBook = getWorkbook(fileName);
                break;
            }
            case "2": {
                //垃圾清运
                fileName = "import" + File.separator + "委外供应商月度考核表(垃圾清运).xlsx";
                workBook = getWorkbook(fileName);
                break;
            }
            case "3": {
                //公共绿化养护
                fileName = "import" + File.separator + "委外供应商月度考核表(公共绿化养护).xlsx";
                workBook = getWorkbook(fileName);
                break;
            }
            default:
                break;
        }

        //导出
        exportExcel(response, workBook, fileName.substring(7));
    }

    /*----------------------------------------Excel导入导出部分  end-----------------------------------------------*/


    //插入记录
    private <T extends ContractRecord> void saveRecord(T record, String userCode) {
        //添加数据 设置ID为NULL 让其自增
        record.setId(null);

        Map<Integer, Map<String, String>> dataMap = parse2Map(record);

        record.setCreateBy(userCode);
        record.setCreateDate(DateUtil.simpleFormatAccurate(new Date()));
        record.setUpdateBy(userCode);
        record.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
        record.setIsDelete(0);
        recordMapper.insertSelective(record);       // 插入record  得分最后统计

        /*-------------------------------------------------------------------------------------------------*/
        Condition condition = new Condition(ContractDictionary.class);
        condition.createCriteria().andEqualTo("precode", record.getExCode());
        List<ContractDictionary> contractDictionaries = dictionaryMapper.selectByCondition(condition);
        List<ContractEvaluate> evaluates = new ArrayList<>();
        for (ContractDictionary contractDictionary : contractDictionaries) {
            ContractEvaluate evaluate = new ContractEvaluate();
            evaluate.setRecordId(record.getId());
            evaluate.setCode(String.valueOf(contractDictionary.getCode()));
            evaluate.setEvaluateName(contractDictionary.getKinddetail());
            evaluate.setCreateBy(userCode);
            evaluate.setCreateDate(DateUtil.simpleFormatAccurate(new Date()));
            evaluate.setUpdateBy(userCode);
            evaluate.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
            evaluate.setIsDelete(0);

            evaluateMapper.insertSelective(evaluate);   //插入评定项
            evaluates.add(evaluate);
        }

        /*-------------------------------------------------------------------------------------------------*/
        List<ContractTarget> targets = new ArrayList<>();
//        Map<String, Integer> targetIdMap = new HashMap<>();
        for (ContractEvaluate evaluate : evaluates) {
            condition = new Condition(ContractDictionary.class);
            condition.createCriteria().andEqualTo("precode", evaluate.getCode());
            List<ContractDictionary> dictionaries = dictionaryMapper.selectByCondition(condition);

            for (ContractDictionary dictionary : dictionaries) {
                Map<String, String> map = dataMap.get(dictionary.getCode());         //存放值

                ContractTarget target = new ContractTarget();
                target.setEvaluateId(evaluate.getId());
                target.setCode(String.valueOf(dictionary.getCode()));
                target.setTargetName(dictionary.getKinddetail());
                target.setOccurNum(Integer.parseInt(map.get("times")));
                target.setDeductMoney(new BigDecimal(map.get("money")));
                target.setDeductScore(Double.parseDouble(map.get("score")));
                target.setCreateBy(userCode);
                target.setCreateDate(DateUtil.simpleFormatAccurate(new Date()));
                target.setUpdateBy(userCode);
                target.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
                target.setIsDelete(0);
                //设置parent_target_id
                //没有三级指标 暂时废弃

//                switch (dictionary.getCode()) {
//                    case 1010301: {
//                        target.setParentTargetId(targetIdMap.get("10103"));
//                        target.setEvaluateId(null);
//                        break;
//                    }
//                    case 1010302: {
//                        target.setParentTargetId(targetIdMap.get("10103"));
//                        target.setEvaluateId(null);
//                        break;
//                    }
//                    case 1010401: {
//                        target.setParentTargetId(targetIdMap.get("10104"));
//                        target.setEvaluateId(null);
//                        break;
//                    }
//                    case 1010402: {
//                        target.setParentTargetId(targetIdMap.get("10104"));
//                        target.setEvaluateId(null);
//                        break;
//                    }
//                    case 1010501: {
//                        target.setParentTargetId(targetIdMap.get("10105"));
//                        target.setEvaluateId(null);
//                        break;
//                    }
//                    case 1010502: {
//                        target.setParentTargetId(targetIdMap.get("10105"));
//                        target.setEvaluateId(null);
//                        break;
//                    }
//                    default: {
//                        break;
//                    }
//                }
                targetMapper.insertSelective(target);
                targets.add(target);
//                targetIdMap.put(target.getCode(), target.getId());
            }
        }


        Map<Integer, List<ContractTarget>> targetMap = targets.stream().collect(Collectors.groupingBy(ContractTarget::getEvaluateId));
        for (ContractEvaluate evaluate : evaluates) {
            List<ContractTarget> targetList = targetMap.get(evaluate.getId());
            double totalScore = targetList.stream().mapToDouble(ContractTarget::getDeductScore).reduce(0, (s1, s2) -> s1 + s2);
            BigDecimal totalMoney = targetList.stream().map(ContractTarget::getDeductMoney).reduce(new BigDecimal(BigInteger.ZERO), (s1, s2) -> (s1.add(s2)));
            evaluate.setTotalScore(totalScore);
            evaluate.setTotalMoney(totalMoney);
            evaluateMapper.updateByPrimaryKeySelective(evaluate);
        }


        //计算指标总扣分和指标总扣款
        //103为月度巡检得分，这个得分值最后作为分母来计算，不能累加到合计扣分里面
        double totalScore = evaluates.stream().filter(e -> !e.getCode().equals("103")).mapToDouble(ContractEvaluate::getTotalScore).reduce(0, (s1, s2) -> s1 + s2);
        BigDecimal totalMoney = evaluates.stream().map(ContractEvaluate::getTotalMoney).reduce(new BigDecimal(BigInteger.ZERO), (s1, s2) -> (s1.add(s2)));

        record.setTotalScore(totalScore);
        record.setTotalMoney(totalMoney);
        //计算得分
        calculateTotal(record, dataMap);

        recordMapper.updateByPrimaryKeySelective(record);
    }


    //更新记录
    private <T extends ContractRecord> void updateRecord(T record, String userCode) {
        Map<Integer, Map<String, String>> dataMap = parse2Map(record);

        Condition condition = new Condition(ContractEvaluate.class);
        condition.createCriteria().andEqualTo("recordId", record.getId());
        List<ContractEvaluate> evaluates = evaluateMapper.selectByCondition(condition);

        for (ContractEvaluate evaluate : evaluates) {
            condition = new Condition(ContractTarget.class);
            condition.createCriteria().andEqualTo("evaluateId", evaluate.getId());
            List<ContractTarget> targets = targetMapper.selectByCondition(condition);

            for (ContractTarget target : targets) {
                Map<String, String> map = dataMap.get(Integer.parseInt(target.getCode()));         //存放值
                target.setOccurNum(Integer.parseInt(map.get("times")));
                target.setDeductMoney(new BigDecimal(map.get("money")));
                target.setDeductScore(Double.parseDouble(map.get("score")));
                target.setUpdateBy(userCode);
                target.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));

                targetMapper.updateByPrimaryKeySelective(target);
            }

            double totalScore = targets.stream().mapToDouble(ContractTarget::getDeductScore).reduce(0, (s1, s2) -> s1 + s2);
            BigDecimal totalMoney = targets.stream().map(ContractTarget::getDeductMoney).reduce(new BigDecimal(BigInteger.ZERO), (s1, s2) -> (s1.add(s2)));

            evaluate.setUpdateBy(userCode);
            evaluate.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));
            evaluate.setTotalScore(totalScore);
            evaluate.setTotalMoney(totalMoney);
            evaluateMapper.updateByPrimaryKeySelective(evaluate);
        }


        double totalScore = evaluates.stream().filter(e -> !e.getCode().equals("103")).mapToDouble(ContractEvaluate::getTotalScore).reduce(0, (s1, s2) -> s1 + s2);
        BigDecimal totalMoney = evaluates.stream().map(ContractEvaluate::getTotalMoney).reduce(new BigDecimal(BigInteger.ZERO), (s1, s2) -> (s1.add(s2)));


        record.setTotalScore(totalScore);
        record.setTotalMoney(totalMoney);
        calculateTotal(record, dataMap);

        record.setCreateBy(null);
        record.setCreateDate(null);     //执行更新使用selective方法，set null值强制不更新此字段
        record.setExCode(null);
        record.setUpdateBy(userCode);
        record.setUpdateDate(DateUtil.simpleFormatAccurate(new Date()));

        recordMapper.updateByPrimaryKeySelective(record);

    }


    //计算得分、扣分、扣款等
    private <T extends ContractRecord> void calculateTotal(T record, Map<Integer, Map<String, String>> dataMap) {


        double totalScore = record.getTotalScore();
        BigDecimal totalMoney = record.getTotalMoney();
        //合计扣分 = 考核项扣分之和+其他违约扣分
        totalScore += record.getOtherScore();


        //月度实际评分= 保洁：日常巡查月度得分-合计扣分； 垃圾清运：100 - 合计扣分；绿化：100 - 合计扣分 + 奖励分
        //奖励分类型为double，没有的时候默认0值
        double totalGrade = Double.parseDouble(Optional.ofNullable(dataMap.get(10301)).orElse(new HashMap<String, String>() {{
            put("score", "100");
        }}).get("score")) - totalScore + record.getRewardScore();

        //本月得分扣款率扣款：按计算公式
        BigDecimal breakMoney = BigDecimal.valueOf(0);
        switch (record.getExCode()) {
            case "1": {
//                94<=月度实际评分<95		每月承包费总额*0.005
//                93<=月度实际评分<94		每月承包费总额*0.01
//                92<=月度实际评分<93		每月承包费总额*0.015
//                91<=月度实际评分<92		每月承包费总额*0.02
//                90<=月度实际评分<91		每月承包费总额*0.025
//                89<=月度实际评分<90		每月承包费总额*0.03
//                88<=月度实际评分<89		每月承包费总额*0.035
//                87<=月度实际评分<88		每月承包费总额*0.04
//                86<=月度实际评分<87		每月承包费总额*0.045
//                85<=月度实际评分<86		每月承包费总额*0.05
//                84<=月度实际评分<85		每月承包费总额*0.055
//                83<=月度实际评分<84		每月承包费总额*0.06
//                82<=月度实际评分<83		每月承包费总额*0.065
//                81<=月度实际评分<82		每月承包费总额*0.07
//                80<=月度实际评分<81		每月承包费总额*0.075
//                79<=月度实际评分<80		每月承包费总额*0.08
//                78<=月度实际评分<79		每月承包费总额*0.085
//                77<=月度实际评分<78		每月承包费总额*0.09
//                76<=月度实际评分<77		每月承包费总额*0.095
//                75<=月度实际评分<76		每月承包费总额*0.10
//                月度实际评分<75		每月承包费总额*0.15

                if (totalGrade < 75) {
                    breakMoney = record.getMonthContractMoney().multiply(BigDecimal.valueOf(0.15));
                } else {
                    int diff = BigDecimal.valueOf(95).subtract(BigDecimal.valueOf(totalGrade)).setScale(0, BigDecimal.ROUND_UP).intValue();
                    diff = diff < 0 ? 0 : diff;
                    breakMoney = BigDecimal.valueOf(diff).multiply(BigDecimal.valueOf(0.005)).multiply(record.getMonthContractMoney());
                }
                break;
            }
            case "2": {
                if (totalGrade < 90) {
                    breakMoney = BigDecimal.valueOf(90 - totalGrade).multiply(BigDecimal.valueOf(0.01)).multiply(record.getMonthContractMoney());
                }
                break;
            }
            case "3": {
//                95<月度实际评分		0
//                94<=月度实际评分<=95		每月承包费总额*0.1%

//                93<=月度实际评分<=94		每月承包费总额*0.2%
//                92<=月度实际评分<=93		每月承包费总额*0.4%
//                91<=月度实际评分<=92		每月承包费总额*0.6%
//                90<=月度实际评分<=91		每月承包费总额*0.8%
//                89<=月度实际评分<=90		每月承包费总额*1%

//                88<=月度实际评分<=89		每月承包费总额*2%
//                87<=月度实际评分<=88		每月承包费总额*4%
//                86<=月度实际评分<=87		每月承包费总额*6%
//                85<=月度实际评分<=86		每月承包费总额*8%
//                84<=月度实际评分<=85		每月承包费总额*10%
//                实际评分<84	 	每月承包费总额*10%

                if (totalGrade < 84) {
                    breakMoney = record.getMonthContractMoney().multiply(BigDecimal.valueOf(0.1));
                } else {
                    int diff = BigDecimal.valueOf(95).subtract(BigDecimal.valueOf(totalGrade)).setScale(0, BigDecimal.ROUND_UP).intValue();
                    diff = diff < 0 ? 0 : diff;
                    double factor;
                    if (diff == 1) {            //94-95
                        factor = 0.001;
                    } else if (diff <= 6) {     //89-94   公差0.002
                        diff -= 1;
                        factor = 0.002;
                    } else {                    //84-88   公差 0.02
                        diff -= 6;
                        factor = 0.02;
                    }
                    breakMoney = BigDecimal.valueOf(diff).multiply(BigDecimal.valueOf(factor)).multiply(record.getMonthContractMoney());
                }
                break;
            }
            default:
                break;
        }


        //合计扣款=考核项扣款之和+其他违扣款+考核评分扣款
        totalMoney = totalMoney.add(record.getOtherMoney()).add(breakMoney);
        //实际支付费用=每月承包费总额-应扣除款合计+奖励金
        BigDecimal actualContractMoney = record.getMonthContractMoney().subtract(totalMoney).add(record.getRewardMoney());


        record.setTotalScore(totalScore);   //合计扣分
        record.setTotalGrade(totalGrade);   //月度实际得分
        record.setBreakMoney(breakMoney);   //本月得分扣款率扣款
        record.setTotalMoney(totalMoney);   //合计扣款
        record.setActualContractMoney(actualContractMoney);     //实际支付承包费
    }

    //查询数据库里面是否有存在的记录
    //0不存在 1存在
    private int checkIsExist(ContractRecord record) {

        String supplierCode = record.getSupplierCode();
        String contractCode = record.getContractCode();
        String month = record.getMonth();
        String year = record.getYear();

        Condition condition = new Condition(ContractRecord.class);
        condition.createCriteria().andEqualTo("supplierCode", supplierCode).andEqualTo("contractCode", contractCode).andEqualTo("month", month).andEqualTo("year", year);
        List<ContractRecord> contractRecords = recordMapper.selectByCondition(condition);

        if (contractRecords != null && contractRecords.size() > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    //把对象的属性抽取出来
    private <T extends ContractRecord> Map<Integer, Map<String, String>> parse2Map(T t) {
        Map<Integer, Map<String, String>> returnMap = new HashMap<>();

        Condition condition = new Condition(ContractDictionary.class);
        condition.createCriteria().andEqualTo("precode", t.getExCode());
        List<ContractDictionary> contractDictionaries = dictionaryMapper.selectByCondition(condition);

        condition = new Condition(ContractDictionary.class);
        condition.createCriteria().andIn("precode", contractDictionaries.stream().map(ContractDictionary::getCode).collect(Collectors.toList()));
        List<ContractDictionary> dictionaries = dictionaryMapper.selectByCondition(condition);

        for (ContractDictionary dictionary : dictionaries) {
            Map<String, String> map = new HashMap<>();
            map.put("times", "0");
            map.put("score", "0");
            map.put("money", "0");
            map.put("minusScore", String.valueOf(Optional.ofNullable(dictionary.getMinusscore()).orElse(1.0d)));
            map.put("minusMoney", String.valueOf(Optional.ofNullable(dictionary.getMinusmoney()).orElse(1.0d)));
            returnMap.put(dictionary.getCode(), map);
        }


        if (t instanceof EnvironmentalClean) {
            returnMap = parseEnvironmentalClean2Map((EnvironmentalClean) t, returnMap);
        }
        if (t instanceof GarbageCleaning) {
            returnMap = parseGarbageCleaning2Map((GarbageCleaning) t, returnMap);
        }
        if (t instanceof GreeningMaintenance) {
            returnMap = parseGreeningMaintenance2Map((GreeningMaintenance) t, returnMap);
        }

        return returnMap;
    }

    private Map<Integer, Map<String, String>> parseEnvironmentalClean2Map(EnvironmentalClean e, Map<Integer, Map<String, String>> map) {


        Map<Integer, Integer> timesMap = new HashMap<>();
        /*--------------缺勤----------------*/
        timesMap.put(10101, e.getTotalNum());          //本月应出勤人数
        timesMap.put(10102, e.getTotalActualNum());    //本月实际出勤人数
        timesMap.put(10103, e.getTotalOvertime());     //本月加班总时数
        //折算为加班人数
        timesMap.put(10104, BigDecimal.valueOf(e.getTotalOvertime()).divide(BigDecimal.valueOf(8), 0, BigDecimal.ROUND_HALF_UP).intValue());
        //缺勤人数 = 应出勤人数-实际出勤人数 - 折算加班人数
        timesMap.put(10105, timesMap.get(10101) - timesMap.get(10102) - timesMap.get(10104));
        //如果缺勤人数 算出来的数<0,就默认为0
        if(timesMap.get(10105) < 0) timesMap.put(10105,0);

        /*--------------出勤----------------*/
        //出勤率% = （实际出勤数 + 折算加班人数）/ 应出勤人数  * 100
        timesMap.put(10201, BigDecimal.valueOf(timesMap.get(10102)).add(BigDecimal.valueOf(timesMap.get(10104))).multiply(BigDecimal.valueOf(100)).divide(BigDecimal.valueOf(timesMap.get(10101)), 0, BigDecimal.ROUND_HALF_UP).intValue());

        /*--------------日常巡查----------------*/
//        timesMap.put(10301, e.getInspectionScore());                //日常巡查得分

        /*--------------投诉----------------*/
        timesMap.put(10401, e.getCommonComplaint());       //一般投诉项
        timesMap.put(10402, e.getSeriousComplaint());      //严重投诉项

        /*--------------满意度----------------*/
        timesMap.put(10501, e.getSatisfactionNum());       //不满意问题项

        /*--------------监评----------------*/
        timesMap.put(10601, e.getCheckSerious());          //区域监评严重不合格
        timesMap.put(10602, e.getCheckBlack());            //区域监评黑色预警
        timesMap.put(10603, e.getCheckYellow());           //区域监评黄牌
        timesMap.put(10604, e.getCheckRed());              //区域监评红牌

        /*--------------飞行----------------*/
        timesMap.put(10701, e.getFlightBlack());           //飞行检查黑色预警
        timesMap.put(10702, e.getFlightYellow());          //飞行检查黄牌
        timesMap.put(10703, e.getFlightRed());             //飞行检查红牌

        /*--------------三方----------------*/
        timesMap.put(10801, e.getThirdRed());              //第三方调查红牌
        timesMap.put(10802, e.getThirdYellow());           //第三方调查黄牌


        //针对手动输入的扣分或者扣款，如果只有一个子指标，那么就存入到对应的score或money字段；如果有多个子指标，就随便存到一个子指标里面
        Map<Integer, Double> scoreMap = new HashMap<>();
        scoreMap.put(10301, e.getInspectionScore());
        scoreMap.put(10401, e.getComplaintScore());
        scoreMap.put(10501, e.getSatisfactionScore());
        scoreMap.put(10601, e.getCheckScore());
        scoreMap.put(10701, e.getFlightScore());
        scoreMap.put(10801, e.getThirdScore());


        Map<Integer, Double> moneyMap = new HashMap<>();
        moneyMap.put(10201, Optional.ofNullable(e.getVacancyMoney()).orElse(BigDecimal.valueOf(0)).doubleValue());
        moneyMap.put(10401, Optional.ofNullable(e.getComplaintMoney()).orElse(BigDecimal.valueOf(0)).doubleValue());
        moneyMap.put(10501, Optional.ofNullable(e.getSatisfactionMoney()).orElse(BigDecimal.valueOf(0)).doubleValue());
        moneyMap.put(10601, Optional.ofNullable(e.getCheckMoney()).orElse(BigDecimal.valueOf(0)).doubleValue());
        moneyMap.put(10701, Optional.ofNullable(e.getFlightMoney()).orElse(BigDecimal.valueOf(0)).doubleValue());
        moneyMap.put(10801, Optional.ofNullable(e.getThirdMoney()).orElse(BigDecimal.valueOf(0)).doubleValue());


        map.forEach((k, v) -> {
            v.put("times", String.valueOf(Optional.ofNullable(timesMap.get(k)).orElse(0)));
            v.put("score", String.valueOf(Optional.ofNullable(scoreMap.get(k)).orElse(0.0d)));
            v.put("money", String.valueOf(Optional.ofNullable(moneyMap.get(k)).orElse(0.0d)));
        });

        return map;
    }

    private Map<Integer, Map<String, String>> parseGarbageCleaning2Map(GarbageCleaning garbageCleaning, Map<Integer, Map<String, String>> map) {
        Map<Integer, Integer> timesMap = new HashMap<>();
        timesMap.put(20101, garbageCleaning.getTimeNum());
        timesMap.put(20201, garbageCleaning.getStandardNum());
        timesMap.put(20301, garbageCleaning.getGarbageNum());
        timesMap.put(20401, garbageCleaning.getScatterNum());
        timesMap.put(20501, garbageCleaning.getCleanNum());
        timesMap.put(20601, garbageCleaning.getCarNum());
        timesMap.put(20602, garbageCleaning.getOwnerNum());
        timesMap.put(20701, garbageCleaning.getWillfullyNum());
        timesMap.put(20702, garbageCleaning.getRequireNum());
        timesMap.put(20703, garbageCleaning.getMouthNum());
        timesMap.put(20704, garbageCleaning.getSecurityNum());
        timesMap.put(20801, garbageCleaning.getDisorderlyNum());
        timesMap.put(20802, garbageCleaning.getManageNum());
        timesMap.put(20901, garbageCleaning.getComplaintNum());
        timesMap.put(21001, garbageCleaning.getPropertyNum());
        timesMap.put(21002, garbageCleaning.getSceneNum());
        timesMap.put(21003, garbageCleaning.getDisputeNum());
        timesMap.put(21004, garbageCleaning.getCollectNum());
        timesMap.put(21101, garbageCleaning.getDamageNum());
        timesMap.put(21102, garbageCleaning.getExecuteNum());


        Map<Integer, Double> scoreMap = new HashMap<>();
        scoreMap.put(20101, garbageCleaning.getTimeScore());
        scoreMap.put(20201, garbageCleaning.getStandardScore());
        scoreMap.put(20301, garbageCleaning.getGarbageScore());
        scoreMap.put(20401, garbageCleaning.getScatterScore());
        scoreMap.put(20501, garbageCleaning.getCleanScore());
        scoreMap.put(20601, garbageCleaning.getCarScore());
        scoreMap.put(20701, garbageCleaning.getWorkScore());
        scoreMap.put(20801, garbageCleaning.getManageScore());
        scoreMap.put(20901, garbageCleaning.getComplaintScore());
        scoreMap.put(21001, garbageCleaning.getStaffScore());
        scoreMap.put(21101, garbageCleaning.getSaveScore());

        map.forEach((k, v) -> {
            v.put("times", String.valueOf(Optional.ofNullable(timesMap.get(k)).orElse(0)));
            v.put("score", String.valueOf(Optional.ofNullable(scoreMap.get(k)).orElse(0.0d)));
            v.put("money", "0");
        });
        return map;
    }

    private Map<Integer, Map<String, String>> parseGreeningMaintenance2Map(GreeningMaintenance greeningMaintenance, Map<Integer, Map<String, String>> map) {
        Map<Integer, Integer> timesMap = new HashMap<>();
        timesMap.put(30101, greeningMaintenance.getMasterNum());
        timesMap.put(30102, greeningMaintenance.getMasterNotNum());
        timesMap.put(30201, greeningMaintenance.getAbCommonNum());
        timesMap.put(30202, greeningMaintenance.getAbSeriousNum());
        timesMap.put(30203, greeningMaintenance.getAbNum());
        timesMap.put(30204, greeningMaintenance.getAbNotNum());
        timesMap.put(30301, greeningMaintenance.getRegionCommonNum());
        timesMap.put(30302, greeningMaintenance.getRegionSeriousNum());
        timesMap.put(30303, greeningMaintenance.getRegionNotNum());
        timesMap.put(30304, greeningMaintenance.getRegionNum());
        timesMap.put(30401, greeningMaintenance.getHeadquartersSeriousNum());


        Map<Integer, Double> scoreMap = new HashMap<>();
        scoreMap.put(30101, greeningMaintenance.getMasterScore());
        scoreMap.put(30201, greeningMaintenance.getAbNotNumScore());
        scoreMap.put(30301, greeningMaintenance.getRegionScore());
        scoreMap.put(30401, greeningMaintenance.getHeadquartersSocre());

        map.forEach((k, v) -> {
            v.put("times", String.valueOf(Optional.ofNullable(timesMap.get(k)).orElse(0)));
            v.put("score", String.valueOf(Optional.ofNullable(scoreMap.get(k)).orElse(0.0d)));
            v.put("money", "0");
        });
        return map;
    }

    //EXCLE导入导出 方法

    /**
     * @Description: 获取工作簿
     * @Params: [fileName]
     * @return: org.apache.poi.ss.usermodel.Workbook
     * @Author: SignV
     * @Date: 2018/9/25
     */
    private Workbook getWorkbook(String fileName) {
        try {
            File file = ResourceUtils.getFile("classpath:excel" + File.separator + "exContract" + File.separator + fileName);
            InputStream inputStream = new FileInputStream(file);
            // 获取模板
            return new XSSFWorkbook(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @Description: 导出EXCEL
     * @Params: [response, workBook, fileName]
     * @return: void
     * @Author: SignV
     * @Date: 2018/9/25
     */
    private void exportExcel(HttpServletResponse response, Workbook workBook, String fileName) {
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("gbk"), "ISO-8859-1"));
            response.setHeader("Pragma", "No-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expire", 0);
            response.setContentType("application/vnd.ms-excel");
            OutputStream output = response.getOutputStream();
            workBook.write(output);
            output.flush();
            workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @Description: 写入考核记录 基础数据 可复用
     * @Params: [tempRow, t]
     * @return: void
     * @Author: SignV
     * @Date: 2018/9/28
     */
    private <T extends ContractRecord> void writeExcel(Row tempRow, T t) {
        // ------------        写入考核记录 基础数据        ------------ //
        // ------ 写入 区域名称
        tempRow.getCell(0).setCellValue(t.getOrganName());
        // ------ 写入 项目名称
        tempRow.getCell(1).setCellValue(t.getCommname());
        // ------ 写入 组团名称
        tempRow.getCell(2).setCellValue(t.getGroupName());
        // ------ 写入 委外类别
//        tempRow.getCell(3).setCellValue("公共环境保洁");
        // ------ 写入 委外内容
        tempRow.getCell(4).setCellValue(t.getExContent());
        // ------ 写入 供应商编号
        tempRow.getCell(5).setCellValue(t.getSupplierCode());
        // ------ 写入 供应商名称
        tempRow.getCell(6).setCellValue(t.getSupplierName());
        // ------ 写入 合同编号
        tempRow.getCell(7).setCellValue(t.getContractCode());
        // ------ 写入 合同名称
        tempRow.getCell(8).setCellValue(t.getContractName());
        // ------ 写入 考核月份
        tempRow.getCell(9).setCellValue(t.getYear() + "-" + t.getMonth());
        // ------ 写入 月度实际评分
        tempRow.getCell(10).setCellValue(t.getTotalGrade());
        // ------ 写入 每月承包费总额
        tempRow.getCell(11).setCellValue(t.getMonthContractMoney().doubleValue());
        // ------ 写入 实际支付承包费
        tempRow.getCell(12).setCellValue(t.getActualContractMoney().doubleValue());
    }
}