package cn.com.goldenwater.dcproj.service.impl.importex;

import cn.com.goldenwater.dcproj.dao.ImpPersInfoDao;
import cn.com.goldenwater.dcproj.dto.ExcelInspPersDto;
import cn.com.goldenwater.dcproj.model.*;
import cn.com.goldenwater.dcproj.param.*;
import cn.com.goldenwater.dcproj.service.*;
import cn.com.goldenwater.core.service.AbstractCrudService;
import cn.com.goldenwater.id.util.UuidUtil;
import com.github.pagehelper.PageHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author lune
 * @date 2019-7-23
 */
@Service
@Transactional
public class ImpPersInfoServiceImpl extends AbstractCrudService<ImpPersInfo, ImpPersInfoParam> implements ImpPersInfoService {

    @Autowired
    private ImpPersInfoDao impPersInfoDao;
    @Autowired
    private AttAdXBaseService attAdXBaseService;
    @Autowired
    private BisInspAllService inspAllService;
    @Autowired
    private BisInspAllRlationPersService bisInspAllRlationPersService;
    @Autowired
    private BisInspAllRlationService bisInspAllRlationService;
    @Autowired
    private BisInspSelAreaService bisInspSelAreaService;

    public ImpPersInfoServiceImpl(ImpPersInfoDao impPersInfoDao) {
        super(impPersInfoDao);
        this.impPersInfoDao = impPersInfoDao;
    }


    @Override
    public String insertList(List<ExcelInspPersDto> list, String mnth) {
        ImpPersInfo impPersInfo = new ImpPersInfo();
        int i = 1;
        for (ExcelInspPersDto inspPers : list) {
            ImpPersInfo persInfo = new ImpPersInfo();
            persInfo.setId(UuidUtil.uuid());
            persInfo.setNum(String.valueOf(i));
            persInfo.setOrgId(inspPers.getOrgId());
            persInfo.setOrgNm(inspPers.getOrgName());
            String adCd = getAdCd(inspPers.getGroName(), "", "");
            persInfo.setAdCode(adCd);
            persInfo.setAdName(inspPers.getGroName());
            Map<String, String> map = this.addPers(inspPers);
            String leadId = map.get("one");
            persInfo.setLeadId(leadId);
            persInfo.setLeadName(inspPers.getHeadman());
            persInfo.setLeadUnitJob(inspPers.getHeadNm());
            persInfo.setCrewId(map.get("two"));
            persInfo.setCrewName(inspPers.getMembers());
            persInfo.setCrewUnitJob(inspPers.getMemNm());
            persInfo.setLisonId(map.get("three"));
            persInfo.setLisonName(inspPers.getConPers());
            persInfo.setLisonUnitJob(inspPers.getConNm());
            persInfo.setMnth(mnth);
            if (StringUtils.isNotBlank(leadId)) {
                //查询是否能关联上现有的督查组，根据月份、督查区域、组长进行匹配
                Map<String, Object> param = new HashMap<>();
                param.put("persId", leadId);
                param.put("persType", "1");
                param.put("adCd", adCd.substring(0, 2));
                param.put("id", "001");
                param.put("mnth", mnth);
                List<Map<String, Object>> groups = impPersInfoDao.getGroupId(param);
                if (groups != null && groups.size() > 0) {
                    Map<String, Object> group = groups.get(0);
                    persInfo.setOldGroupId(group.get("ID").toString());
                } else {
                    param.put("persType", "");
                    groups = impPersInfoDao.getGroupId(param);
                    if (groups != null && groups.size() > 0) {
                        Map<String, Object> group = groups.get(0);
                        persInfo.setOldGroupId(group.get("ID").toString());
                        //如果不是组长，那么把本行数据中的组长设置成该督查组组长
                        BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
                        bisInspAllRlation.setId(group.get("ID").toString());
                        bisInspAllRlation.setPersid(leadId);
                        bisInspAllRlation.setPertype("1");
                        bisInspAllRlation.setImpData("2");
                        bisInspAllRlationService.update(bisInspAllRlation);
                    }
                }
            } else {
                persInfo.setOldGroupId(impPersInfo.getOldGroupId());
            }
            impPersInfo = persInfo;
            impPersInfoDao.insert(persInfo);
            i++;
        }
        return null;
    }

    @Override
    public String handlePersData(String mnth, String orgId) {
        ImpPersInfoParam impPersInfoParam = new ImpPersInfoParam();
        impPersInfoParam.setMnth(mnth);
        impPersInfoParam.setOrgId(orgId);
        List<ImpPersInfo> persInfos = impPersInfoDao.findList(impPersInfoParam);
        String groupId = "";
        String groupNm = "";
        for (ImpPersInfo info:persInfos) {
            Map<String, String> map = addGroup(info,groupId,groupNm);
            groupId = map.get("groupId");
            groupNm = map.get("groupNm");
        }
        return null;
    }

    public String getAdCd(String sheng, String shi, String xian) {
        String adCd = "";
        String adCd2 = "";
        if(sheng.contains("/")){
            String sheng2 = sheng.substring(sheng.indexOf("/")+1);
            adCd2 = getAdCd(sheng2,"","");
            sheng = sheng.substring(0,sheng.indexOf("/"));
        }
        AttAdXBaseParam attAdXBaseParam = new AttAdXBaseParam();
        AttAdXBase attAdXBase;
        if (StringUtils.isNotBlank(sheng)) {
            attAdXBaseParam.setAdName(sheng);
            attAdXBaseParam.setAdGrad("2");
            attAdXBase = attAdXBaseService.getBy(attAdXBaseParam);
            if (attAdXBase != null) {
                String shengCode = attAdXBase.getAdCode();
                attAdXBaseParam.setAdFcode(shengCode);
                adCd = shengCode;
            } else {
                attAdXBaseParam.setAdName("");
                attAdXBaseParam.setAdFullName(sheng);
                attAdXBase = attAdXBaseService.getBy(attAdXBaseParam);
                if (attAdXBase != null) {
                    String shengCode = attAdXBase.getAdCode();
                    attAdXBaseParam.setAdFcode(shengCode);
                    adCd = shengCode;
                }
            }
        }
        if (StringUtils.isNotBlank(shi)) {
            attAdXBaseParam.setAdName(shi);
            attAdXBaseParam.setAdGrad("3");
            attAdXBase = attAdXBaseService.getBy(attAdXBaseParam);
            if (attAdXBase != null) {
                String shiCode = attAdXBase.getAdCode();
                attAdXBaseParam.setAdFcode(shiCode);
                adCd = shiCode;
            } else {
                attAdXBaseParam.setAdName("");
                attAdXBaseParam.setAdFullName(shi);
                attAdXBase = attAdXBaseService.getBy(attAdXBaseParam);
                if (attAdXBase != null) {
                    String shiCode = attAdXBase.getAdCode();
                    attAdXBaseParam.setAdFcode(shiCode);
                    adCd = shiCode;
                }
            }
        }
        if (StringUtils.isNotBlank(xian)) {
            attAdXBaseParam.setAdName(xian);
            attAdXBaseParam.setAdGrad("4");
            attAdXBase = attAdXBaseService.getBy(attAdXBaseParam);
            if (attAdXBase != null) {
                adCd = attAdXBase.getAdCode();
            } else {
                attAdXBaseParam.setAdName("");
                attAdXBaseParam.setAdFullName(xian);
                attAdXBase = attAdXBaseService.getBy(attAdXBaseParam);
                if (attAdXBase != null) {
                    adCd = attAdXBase.getAdCode();
                }
            }
        }
        if(StringUtils.isNotBlank(adCd2)){
            adCd = adCd + "/" + adCd2;
        }
        return adCd;
    }

    public Map<String, String> addPers(ExcelInspPersDto persDto) {
        Map<String, String> map = new HashMap<>();
        String sheng = persDto.getGroName();
        String shengCode = getAdCd(sheng, "", "");
        String orgId = persDto.getOrgId();
        String persName = persDto.getHeadman();
        String persId = "";
        String headerNm = persDto.getHeadNm();
        String memeber = persDto.getMembers();
        String memNm = persDto.getMemNm();
        String comPer = persDto.getConPers();
        String comNm = persDto.getConNm();
        if (StringUtils.isNotBlank(persName)) {
            persId = this.addOnePers(orgId, persName, headerNm);
        } else {
            persId = "";
        }
        map.put("one", persId);
        if (StringUtils.isNotBlank(memeber)) {
            persId = this.addOnePers(orgId, memeber, memNm);
        } else {
            persId = "";
        }
        map.put("two", persId);
        if (StringUtils.isNotBlank(comPer)) {
            persId = this.addOnePers(orgId, comPer, comNm);
        } else {
            persId = "";
        }
        map.put("three", persId);
        return map;
    }

    public String addOnePers(String orgId, String persName, String dpp) {
        String persId = "";
        if (StringUtils.isNotEmpty(persName)) {
            persName = persName.replaceAll(" ", "");
        }
        BisInspAllRlationPersParam persParam = new BisInspAllRlationPersParam();
        persParam.setOrgId(orgId);
        persParam.setPersName(persName);
        List<BisInspAllRlationPers> rlationPers = bisInspAllRlationPersService.findList(persParam);
        if (rlationPers != null && rlationPers.size() > 0) {
            BisInspAllRlationPers rlationPer = rlationPers.get(0);
            persId = rlationPer.getGuid();
        } else {
            persId = UuidUtil.uuid();
            BisInspAllRlationPers rlationPer = new BisInspAllRlationPers();
            rlationPer.setGuid(persId);
            rlationPer.setOrgId(orgId);
            rlationPer.setPersName(persName);
            rlationPer.setPwd("JinShui@433");
            if (dpp.contains("/")) {
                String[] strs = dpp.split("/");
                for (int i = 0; i < strs.length; i++) {
                    if (i == 0) {
                        rlationPer.setDpnm(strs[0]);
                    } else if (i == 1) {
                        rlationPer.setDppost(strs[1]);
                    }
                }
            } else {
                rlationPer.setDppost(dpp);
            }
            persParam.setPersName("");
            int count = bisInspAllRlationPersService.selectCount(persParam);
            String phone = orgId + String.format("%08d", count + 1);//其中0表示补零而不是补空格，6表示至少6位
            rlationPer.setMobilenumb(phone);
            bisInspAllRlationPersService.insert(rlationPer);
        }
        return persId;
    }

    public Map<String, String> addGroup(ImpPersInfo info,String groupId, String groupNm) {
        Map<String, String> result = new HashMap<>();
        String oldGroupId = info.getOldGroupId();
        String leadId = info.getLeadId();
        String crewId = info.getCrewId();
        String lisonId = info.getLisonId();
        String mnth = info.getMnth();
        String orgId = info.getOrgId();
        String adCode = info.getAdCode();
        String adName = info.getAdName();
        //如果人员不能关联到该月批次下的组，则进行新增或者重新匹配
        if (StringUtils.isBlank(oldGroupId)) {
            //首先查询该月份是否有相应批次
            BisInspAllParam bisInspAllParam = new BisInspAllParam();
            BisInspAll bisInspAll;
            bisInspAllParam.setInspYear("2019");
            bisInspAllParam.setInspMnth(mnth);
            String pid = "001" + orgId;//6位
            bisInspAllParam.setId("");
            bisInspAllParam.setPid(pid);
            //查询该机构本月批次
            List<BisInspAll> bisInspAlls = inspAllService.findList(bisInspAllParam);
            if (bisInspAlls != null && bisInspAlls.size() > 0) {
                //查询该批次下是否有督查组能与本行数据中组长、行政区关联
                //首先判断本行数据中是否有组长
                if (StringUtils.isNotBlank(leadId)) {
                    Map<String, Object> param = new HashMap<>();
                    param.put("persId", leadId);
                    param.put("adCd", adCode.substring(0, 2));
                    param.put("id", pid);
                    param.put("mnth", mnth);
                    List<Map<String, Object>> groups = impPersInfoDao.getGroupId(param);
                    if (groups!=null&&groups.size()>0){
                        //对本行数据中的组长、组员、联系人进行判断，判断是否都在其督查组中，是否在督查组中担任的角色是准确的
                        this.checkGroupPers(info,groups);
                        //查询本行数据中行政区划是否在督查组
                        BisInspSelAreaParam bisInspSelAreaParam = new BisInspSelAreaParam();
                        bisInspSelAreaParam.setId(oldGroupId);
                        bisInspSelAreaParam.setAdCode(adCode);
                        BisInspSelArea selArea = bisInspSelAreaService.getBy(bisInspSelAreaParam);
                        if(selArea==null){
                            //本行数据中行政区划添加进督查组
                            selArea = new BisInspSelArea();
                            selArea.setAreaId(UuidUtil.uuid());
                            selArea.setId(oldGroupId);
                            selArea.setAdName(adName);
                            selArea.setAdCode(adCode);
                            selArea.setImpData("1");
                            bisInspSelAreaService.insert(selArea);
                        }
                    }else{
                        //如果没有查询到督查组，则在第一个批次下进行新增相应的督查组
                        //获取批次
                        bisInspAll = bisInspAlls.get(0);
                        pid = bisInspAll.getId();
                        //新增督查组，然后把本行数据中的组长、组员、联系人新增进该督查组
                        info = this.addBatchGroup(info,pid);
                    }
                } else {
                    //如果本行数据中没有组长，那么可以直接使用上一行的督查组信息
                    info.setOldGroupId(groupId);
                    info.setGroupId(groupId);
                    info.setGroupNm(groupNm);
                    impPersInfoDao.update(info);
                    Map<String, Object> param = new HashMap<>();
                    param.put("persId", crewId);
                    param.put("id", groupId);
                    param.put("mnth", mnth);
                    List<Map<String, Object>> groups = impPersInfoDao.getGroupId(param);
                    this.checkGroupPers(info, groups);
                }
            } else {
                //如果没有有对应批次则新建批次和督查组
                info = this.addBatchGroup(info, pid);
                pid = info.getGroupId();
                info = this.addBatchGroup(info, pid);
            }
        } else {
            //如果已经关联到已有督查组，则对本行数据中的组长、组员、联系人进行判断，判断是否都在其督查组中，是否在督查组中担任的角色是准确的
            Map<String, Object> param = new HashMap<>();
            List<Map<String, Object>> groups;
            param.put("adCd",info.getAdCode().substring(0,2));
            param.put("id",oldGroupId);
            param.put("mnth",mnth);
            String persIds = "";
            if (StringUtils.isNotBlank(leadId)) {
                persIds += leadId + ",";
            }
            if (StringUtils.isNotBlank(lisonId)) {
                persIds += lisonId + ",";
            }
            if (StringUtils.isNotBlank(crewId)) {
                persIds += crewId;
            }
            param.put("persIds", persIds);
            groups = impPersInfoDao.getGroupId(param);
            if (groups != null && groups.size() > 0) {
                //对本行数据中的组长、组员、联系人进行判断，判断是否都在其督查组中，是否在督查组中担任的角色是准确的
                this.checkGroupPers(info, groups);
            } else {
                //如果本组中没有人员，则把本行数据中的组长、组员、联系人新增进该督查组
                this.addGroupPers(info, oldGroupId);
            }
            //查询本行数据中行政区划是否在督查组
            BisInspSelAreaParam bisInspSelAreaParam = new BisInspSelAreaParam();
            bisInspSelAreaParam.setId(oldGroupId);
            bisInspSelAreaParam.setAdCode(adCode);
            BisInspSelArea selArea = bisInspSelAreaService.getBy(bisInspSelAreaParam);
            if(selArea==null){
                //本行数据中行政区划添加进督查组
                selArea = new BisInspSelArea();
                selArea.setAreaId(UuidUtil.uuid());
                selArea.setId(oldGroupId);
                selArea.setAdName(adName);
                selArea.setAdCode(adCode);
                selArea.setImpData("1");
                bisInspSelAreaService.insert(selArea);
            }
            BisInspAll bisInspAll = inspAllService.get(oldGroupId);
            info.setGroupId(oldGroupId);
            info.setGroupNm(bisInspAll.getPnm());
            impPersInfoDao.update(info);
        }
        result.put("groupId",info.getGroupId());
        result.put("groupNm",info.getGroupNm());
        return result;
    }

    public void addGroupPers(ImpPersInfo info, String groupId) {
        String leadId = info.getLeadId();
        String crewId = info.getCrewId();
        String lisonId = info.getLisonId();
        //本行数据中组长、组员、联系人添加进督查组
        BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
        if (StringUtils.isNotBlank(leadId)) {
            bisInspAllRlation.setId(groupId);
            bisInspAllRlation.setPersid(leadId);
            bisInspAllRlation.setPertype("1");
            bisInspAllRlation.setImpData("1");
            System.out.println(bisInspAllRlation.toString());
            BisInspAllRlationParam param = new BisInspAllRlationParam();
            param.setPersid(bisInspAllRlation.getPersid());
            param.setId(bisInspAllRlation.getId());
            BisInspAllRlation rlation = bisInspAllRlationService.getBy(param);
            if(rlation!=null){
                bisInspAllRlationService.update(bisInspAllRlation);
            }else{
                bisInspAllRlationService.insert(bisInspAllRlation);
            }
        }
        if (StringUtils.isNotBlank(crewId)) {
            bisInspAllRlation.setId(groupId);
            bisInspAllRlation.setPersid(crewId);
            bisInspAllRlation.setPertype("2");
            bisInspAllRlation.setImpData("1");
            System.out.println(bisInspAllRlation.toString());
            BisInspAllRlationParam param = new BisInspAllRlationParam();
            param.setPersid(bisInspAllRlation.getPersid());
            param.setId(bisInspAllRlation.getId());
            BisInspAllRlation rlation = bisInspAllRlationService.getBy(param);
            if(rlation!=null){
                bisInspAllRlationService.update(bisInspAllRlation);
            }else{
                bisInspAllRlationService.insert(bisInspAllRlation);
            }
        }
        if (StringUtils.isNotBlank(lisonId)) {
            bisInspAllRlation.setId(groupId);
            bisInspAllRlation.setPersid(lisonId);
            bisInspAllRlation.setPertype("3");
            bisInspAllRlation.setImpData("1");
            System.out.println(bisInspAllRlation.toString());
            BisInspAllRlationParam param = new BisInspAllRlationParam();
            param.setPersid(bisInspAllRlation.getPersid());
            param.setId(bisInspAllRlation.getId());
            BisInspAllRlation rlation = bisInspAllRlationService.getBy(param);
            if(rlation!=null){
                bisInspAllRlationService.update(bisInspAllRlation);
            }else{
                bisInspAllRlationService.insert(bisInspAllRlation);
            }
        }
    }

    public void checkGroupPers(ImpPersInfo info, List<Map<String, Object>> groups) {
        String oldGroupId = info.getOldGroupId();
        String leadId = info.getLeadId();
        String crewId = info.getCrewId();
        String lisonId = info.getLisonId();
        boolean hasLead = false;
        boolean isLead = false;
        boolean hasLison = false;
        boolean isLison = false;
        boolean hasCrew = false;
        boolean isCrew = false;
        for (Map<String, Object> group : groups) {
            String persId = String.valueOf(group.get("PERSID"));
            String persType = String.valueOf(group.get("PERTYPE"));
            if (persId.equals(leadId)) {
                hasLead = true;
                if ("1".equals(persType)) {
                    isLead = true;
                }
            }
            if (persId.equals(crewId)) {
                hasCrew = true;
                if ("2".equals(persType)) {
                    isCrew = true;
                }
            }
            if (persId.equals(lisonId)) {
                hasLison = true;
                if ("3".equals(persType)) {
                    isLison = true;
                }
            }
        }
        //如果本组中有人员与本行数据中的组长一致，则判断人员类型是否匹配
        if (hasLead) {
            //如果不是组长，那么把本行数据中的组长设置成该督查组组长
            if (!isLead) {
                BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
                bisInspAllRlation.setId(oldGroupId);
                bisInspAllRlation.setPersid(leadId);
                bisInspAllRlation.setPertype("1");
                bisInspAllRlation.setImpData("2");
                System.out.println(bisInspAllRlation.toString());
                bisInspAllRlationService.update(bisInspAllRlation);
            }
        }else{
            if(StringUtils.isNotBlank(leadId)){
                //如果本组中没有人员与本行数据中的组长一致，那么把本行数据中的组长设置成该督查组组长
                BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
                bisInspAllRlation.setId(oldGroupId);
                bisInspAllRlation.setPersid(leadId);
                bisInspAllRlation.setPertype("1");
                bisInspAllRlation.setImpData("1");
                System.out.println(bisInspAllRlation.toString());
                bisInspAllRlationService.insert(bisInspAllRlation);
            }
        }
        //如果本组中有人员与本行数据中的组员一致，则判断人员类型是否匹配
        if (hasCrew) {
            //如果不是组员，那么把本行数据中的组员设置成该督查组组员
            if (!isCrew) {
                BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
                bisInspAllRlation.setId(oldGroupId);
                bisInspAllRlation.setPersid(crewId);
                bisInspAllRlation.setPertype("2");
                bisInspAllRlation.setImpData("2");
                System.out.println(bisInspAllRlation.toString());
                bisInspAllRlationService.update(bisInspAllRlation);
            }
        }else{
            if(StringUtils.isNotBlank(crewId)){
                //如果本组中没有人员与本行数据中的组员一致，那么把本行数据中的组员设置成该督查组组员
                BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
                bisInspAllRlation.setId(oldGroupId);
                bisInspAllRlation.setPersid(crewId);
                bisInspAllRlation.setPertype("2");
                bisInspAllRlation.setImpData("1");
                System.out.println(bisInspAllRlation.toString());
                bisInspAllRlationService.insert(bisInspAllRlation);
            }
        }
        //如果本组中有人员与本行数据中的联系人一致，则判断人员类型是否匹配
        if (hasLison) {
            //如果不是联系人，那么把本行数据中的联系人设置成该督查组联系人
            if (!isLison) {
                BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
                bisInspAllRlation.setId(oldGroupId);
                bisInspAllRlation.setPersid(lisonId);
                bisInspAllRlation.setPertype("3");
                bisInspAllRlation.setImpData("2");
                System.out.println(bisInspAllRlation.toString());
                bisInspAllRlationService.update(bisInspAllRlation);
            }
        }else{
            if(StringUtils.isNotBlank(lisonId)){
                //如果本组中没有有人员与本行数据中的联系人一致，则把本行数据中的联系人设置成该督查组联系人
                BisInspAllRlation bisInspAllRlation = new BisInspAllRlation();
                bisInspAllRlation.setId(oldGroupId);
                bisInspAllRlation.setPersid(lisonId);
                bisInspAllRlation.setPertype("3");
                bisInspAllRlation.setImpData("1");
                System.out.println(bisInspAllRlation.toString());
                bisInspAllRlationService.insert(bisInspAllRlation);
            }
        }
    }

    public String getGroupNm(ImpPersInfo info) {
        String nm;
        int num = 0;
        String id = info.getId();
        String mnth = info.getMnth();
        String orgId = info.getOrgId();
        String adCode = info.getAdCode();
        String adName = info.getAdName();
        ImpPersInfoParam param = new ImpPersInfoParam();
        param.setMnth(mnth);
        param.setOrgId(orgId);
        param.setAdCode(adCode);
        param.setIsLead("1");
        List<ImpPersInfo> impPersInfos = impPersInfoDao.findList(param);
        for (int i = 0; i < impPersInfos.size(); i++) {
            ImpPersInfo impPersInfo = impPersInfos.get(i);
            if(id.equals(impPersInfo.getId())){
                num = i+1;
                break;
            }
        }
        if(impPersInfos.size()==1){
            nm = adName+"组";
        }else {
            nm = adName+num+"组";
        }
        return nm;
    }

    public ImpPersInfo addBatchGroup(ImpPersInfo info, String pid) {
        String mnth = info.getMnth();
        String adCode = info.getAdCode();
        String adName = info.getAdName();
        String groupId = "";
        String groupNm = "";
        try {
            BisInspAll bisInspAll = new BisInspAll();
            Date date = new SimpleDateFormat("yyyy/MM/dd").parse("2019/" + mnth + "/30");
            bisInspAll.setEntm(date);
            date = new SimpleDateFormat("yyyy/MM/dd").parse("2019/" + mnth + "/01");
            bisInspAll.setSttm(date);
            BisInspAllParam newInspAllParam=new BisInspAllParam();
            newInspAllParam.setPid(pid);
            String maxId=inspAllService.selectMax(newInspAllParam);
            if(StringUtils.isBlank(maxId)) {
                maxId = pid + "001";
            }else {
                maxId="00"+(Integer.parseInt(maxId)+1);
            }
            if(pid.length()==6){
                groupId = maxId;
                groupNm = "2019年" + mnth + "月督查工作";
                //创建批次
                bisInspAll.setId(maxId);
                bisInspAll.setPid(pid);
                bisInspAll.setInspYear("2019");
                bisInspAll.setInspMnth(mnth);
                bisInspAll.setPnm("2019年" + mnth + "月督查工作");
                bisInspAll.setInspTask("导入新增批次");
                bisInspAll.setImpData("1");
                inspAllService.insert(bisInspAll);
            } else if (pid.length() == 9) {
                //创建督查组
                bisInspAll.setId(maxId);
                bisInspAll.setPid(pid);
                bisInspAll.setInspYear("2019");
                bisInspAll.setInspMnth(mnth);
                String pnm = getGroupNm(info);
                bisInspAll.setPnm(pnm);
                bisInspAll.setInspTask(adCode);
                bisInspAll.setImpData("1");
                inspAllService.insert(bisInspAll);
                System.out.println("新增bis_insp_all p&c" +pnm);
                groupId = maxId;
                groupNm = pnm;
                //本行数据中组长、组员、联系人添加进督查组
                this.addGroupPers(info,maxId);
                //本行数据中行政区划添加进督查组
                BisInspSelArea selArea = new BisInspSelArea();
                selArea.setAreaId(UuidUtil.uuid());
                selArea.setId(maxId);
                selArea.setAdName(adName);
                selArea.setAdCode(adCode);
                selArea.setImpData("1");
                bisInspSelAreaService.insert(selArea);
            }
            //更新本行数据中督查组信息
            info.setOldGroupId(groupId);
            info.setGroupId(groupId);
            info.setGroupNm(groupNm);
            impPersInfoDao.update(info);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return info;
    }


    private String getOrgId(String orgNm){
        String orgId = "041";
        if ("长江委".equals(orgNm)||"长委".equals(orgNm)) {
            orgId = "020";
        } else if ("黄委".equals(orgNm)) {
            orgId = "021";
        } else if ("淮委".equals(orgNm)) {
            orgId = "022";
        } else if ("海委".equals(orgNm)) {
            orgId = "023";
        } else if ("珠委".equals(orgNm)||"珠江委".equals(orgNm)) {
            orgId = "024";
        } else if ("松辽委".equals(orgNm)) {
            orgId = "025";
        } else if ("太湖局".equals(orgNm)) {
            orgId = "026";
        } else if ("部督查办".equals(orgNm)||"督查办".equals(orgNm)) {
            orgId = "027";
        } else {
            orgId = "";
        }
        return orgId;
    }
    private String getMnth(String cell){
        String mnth="";
        if("三月".equals(cell)){
            mnth = "03";
        }else if("四月".equals(cell)){
            mnth = "03";
        }else if("五月".equals(cell)){
            mnth = "03";
        }else if("六月".equals(cell)){
            mnth = "03";
        }else if("七月".equals(cell)){
            mnth = "03";
        }else if("八月".equals(cell)){
            mnth = "03";
        }else if("九月".equals(cell)){
            mnth = "03";
        }else if("十月".equals(cell)){
            mnth = "03";
        }
        return mnth;
    }

    @Override
    public String insertPersList(MultipartFile file) {
        try{
            InputStream is = file.getInputStream();
            // 构造 XSSFWorkbook 对象，strPath 传入文件路径
            XSSFWorkbook xwb = new XSSFWorkbook(is);
            XSSFSheet sheet = xwb.getSheetAt(0);
            ImpPersInfo impPersInfo = new ImpPersInfo();
            for (int i = sheet.getFirstRowNum() + 2; i < sheet.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheet.getRow(i);
                int total = row.getPhysicalNumberOfCells();
                String cell = null;
                ImpPersInfo persInfo = new ImpPersInfo();
                for (int j = row.getFirstCellNum(); j < total; j++) {
                    cell = String.valueOf(row.getCell(j));
                    cell = cell.replaceAll(" ","");
                    persInfo.setId(UuidUtil.uuid());
                    if(j==0){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setNum(impPersInfo.getNum());
                        }else{
                            persInfo.setNum(String.valueOf(cell));
                        }
                    }else if(j==1){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setOrgId(impPersInfo.getOrgId());
                            persInfo.setOrgNm(impPersInfo.getOrgNm());
                        }else {
                            String orgId = getOrgId(cell);
                            persInfo.setOrgId(orgId);
                            persInfo.setOrgNm(cell);
                        }
                    }else if(j==2){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setAdCode(impPersInfo.getAdCode());
                            persInfo.setAdName(impPersInfo.getAdName());
                        }else {
                            String adCd = getAdCd(cell, "", "");
                            persInfo.setAdCode(adCd);
                            persInfo.setAdName(cell);
                        }
                    }else if(j==3){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setLeadId(impPersInfo.getLeadId());
                            persInfo.setLeadName(impPersInfo.getLeadName());
                            persInfo.setLeadUnitJob(impPersInfo.getLeadUnitJob());
                        }else {
                            persInfo.setLeadName(cell);
                        }
                    }else if(j==4){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setLeadId(impPersInfo.getLeadId());
                            persInfo.setLeadName(impPersInfo.getLeadName());
                            persInfo.setLeadUnitJob(impPersInfo.getLeadUnitJob());
                        }else {
                            String persId = this.addOnePers(persInfo.getOrgId(), persInfo.getLeadName(), cell);
                            persInfo.setLeadId(persId);
                            persInfo.setLeadUnitJob(cell);
                        }
                    }else if(j==5){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setCrewId(impPersInfo.getCrewId());
                            persInfo.setCrewName(impPersInfo.getCrewName());
                            persInfo.setCrewUnitJob(impPersInfo.getCrewUnitJob());
                        }else {
                            persInfo.setCrewName(cell);
                        }
                    }else if(j==6){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setCrewId(impPersInfo.getCrewId());
                            persInfo.setCrewName(impPersInfo.getCrewName());
                            persInfo.setCrewUnitJob(impPersInfo.getCrewUnitJob());
                        }else {
                            String persId = this.addOnePers(persInfo.getOrgId(), persInfo.getCrewName(), cell);
                            persInfo.setCrewId(persId);
                            persInfo.setCrewUnitJob(cell);
                        }
                    }else if(j==7){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setLisonId(impPersInfo.getLisonId());
                            persInfo.setLisonName(impPersInfo.getLisonName());
                            persInfo.setLisonUnitJob(impPersInfo.getLisonUnitJob());
                        }else {
                            persInfo.setLisonName(cell);
                        }
                    }else if(j==8){
                        if (StringUtils.isEmpty(cell)) {
                            persInfo.setLisonId(impPersInfo.getLisonId());
                            persInfo.setLisonName(impPersInfo.getLisonName());
                            persInfo.setLisonUnitJob(impPersInfo.getLisonUnitJob());
                        }else {
                            String persId = this.addOnePers(persInfo.getOrgId(), persInfo.getLisonName(), cell);
                            persInfo.setLisonId(persId);
                            persInfo.setLisonUnitJob(cell);
                        }
                    }else if(j==9){
                        persInfo.setMnth(getMnth(cell));
                    }
                }
                impPersInfoDao.insert(persInfo);
                impPersInfo = persInfo;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }


}