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

import cn.com.goldenwater.dcproj.constValue.BisInspEnum;
import cn.com.goldenwater.dcproj.dao.*;
import cn.com.goldenwater.dcproj.dto.ExcelInspLevelDto;
import cn.com.goldenwater.dcproj.dto.ExcelInspPblm;
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.BisInspKeyRegisterService;
import cn.com.goldenwater.dcproj.service.IndexService;
import cn.com.goldenwater.dcproj.utils.LoadExcel;
import cn.com.goldenwater.id.util.UuidUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;

/**
 * Created by jinshui on 2019/6/26.
 */
@Service
@Transactional
public class IndexServiceImpl implements IndexService {

    @Autowired
    private BisInspPblmDao bisInspPblmDao;
    @Autowired
    private ObjInspPblmsDao pblmsDao;
    @Autowired
    private ObjKeyPblmRelDao objKeyPblmRelDao;
    @Autowired
    private BisInspKeyRegisterService inspKeyRegisterService;
    @Autowired
    private BisInspKeyRegisterSectionDao keyRegisterSectionDao;
    @Autowired
    private BisInspKeyRegSecUnitDao keyRegSecUnitDao;
    @Autowired
    private BisInspAllRlationPersDao allRlationPersDao;
    @Autowired
    private BisInspRectFdbkDao inspRectFdbkDao;
    @Autowired
    private ObjKeyPblmsDao keyPblmsDao;

    @Autowired
    private AttMampuInfoDao attMampuInfoDao;
    @Autowired
    private AttWagaBaseDao attWagaBaseDao;
    @Autowired
    private AttWainBaseDao attWainBaseDao;
    @Autowired
    private AttSwhsBaseDao attSwhsBaseDao;
    @Autowired
    private BisInspAllRlationPersDao persDao;
    @Autowired
    private BisInspAllDao bisInspAllDao;
    @Autowired
    private BisInspAllRlationDao rlationDao;
    @Autowired
    private AttRsBaseDao rsBaseDao;

    @Override
    public void inputExcel() {
        String filePath = "D:\\temp\\2019水利工程运行管理台帐0625.xlsx";
        Map<String, String> areaMap = new HashMap<>();
        areaMap.put("黄河流域", "001010");
        areaMap.put("长江流域", "001009");
        areaMap.put("珠江流域", "001013");
        areaMap.put("海河流域", "001012");
        areaMap.put("松花江流域", "001014");
        areaMap.put("淮河流域", "001011");
        areaMap.put("太湖流域", "001015");
        File file = new File(filePath);
        if (file.isFile()) {
            System.out.println("文件存在!!");
        }
        List<ExcelInspPblm> exinspPblmList = new ArrayList<>();
        try {
            exinspPblmList = LoadExcel.getExcelEmpwtrun(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<String> projectNames = new ArrayList<>();

        List<Map<String, String>> orgMap = new ArrayList<>();
        for (ExcelInspPblm inspPblm : exinspPblmList) {
            Map<String, String> maporg = new HashMap<>();

            maporg.put("projectNm", inspPblm.getProjectNm());
            maporg.put("firOrg", inspPblm.getFirOrg());
            maporg.put("secOrg", inspPblm.getSecOrg());
            maporg.put("thiOrg", inspPblm.getThiOrg());

            if (!projectNames.contains(inspPblm.getProjectNm())) {
                projectNames.add(inspPblm.getProjectNm());
                System.out.println("projectNm-->" + inspPblm.getProjectNm());
            }

            if (!orgMap.contains(maporg)) {
                orgMap.add(maporg);
            }
        }
        Map<String, AttMampuInfo> orgs = new HashMap<>();
        //添加单位
        for (String nm : projectNames) {
            BisInspKeyRegisterParam keyRegisterParam = new BisInspKeyRegisterParam();
            keyRegisterParam.setNm(nm);
            BisInspKeyRegister keyRegister = inspKeyRegisterService.getOne(keyRegisterParam);
            if (keyRegister != null) {
                for (Map<String, String> org : orgMap) {
                    String projectNm = org.get("projectNm");
                    if (!nm.equals(projectNm)) {
                        continue;
                    }

                    //添加一级单位
                    String firOrg = org.get("firOrg");
                    if (StringUtils.isNotBlank(firOrg) && !orgs.keySet().contains(nm + firOrg)) {
                        AttMampuInfoParam param = new AttMampuInfoParam();
                        param.setNm(firOrg);
                        param.setLev("1");
                        param.setRegId(keyRegister.getId());
                        AttMampuInfo mampuInfo = attMampuInfoDao.getBy(param);
                        if (mampuInfo == null) {
                            mampuInfo = new AttMampuInfo();
                            mampuInfo.setId(UuidUtil.uuid());
                            mampuInfo.setPid("0");
                            mampuInfo.setNm(firOrg);
                            mampuInfo.setIntm(new Date());
                            mampuInfo.setUptm(new Date());
                            mampuInfo.setPersId(keyRegister.getRecPersId());
                            mampuInfo.setRegId(keyRegister.getId());
                            mampuInfo.setLev("1");
                            mampuInfo.setLgtd(keyRegister.getLgtd());
                            mampuInfo.setLttd(keyRegister.getLttd());
                            mampuInfo.setLgtdPc(keyRegister.getLgtdPc());
                            mampuInfo.setLttdPc(keyRegister.getLttdPc());
                            //attMampuInfoDao.insert(mampuInfo);
                        }
                        orgs.put(nm + firOrg, mampuInfo);
                    }

                    String secOrg = org.get("secOrg");
                    if (StringUtils.isNotBlank(secOrg) && !orgs.keySet().contains(nm + secOrg)) {
                        AttMampuInfoParam param = new AttMampuInfoParam();
                        param.setNm(secOrg);
                        param.setLev("2");
                        param.setRegId(keyRegister.getId());
                        AttMampuInfo mampuInfo = attMampuInfoDao.getBy(param);
                        if (mampuInfo == null) {
                            mampuInfo = new AttMampuInfo();
                            mampuInfo.setId(UuidUtil.uuid());
                            mampuInfo.setNm(secOrg);
                            mampuInfo.setIntm(new Date());
                            mampuInfo.setUptm(new Date());
                            mampuInfo.setPersId(keyRegister.getRecPersId());
                            mampuInfo.setRegId(keyRegister.getId());
                            mampuInfo.setPid(orgs.get(nm + firOrg).getId());
                            mampuInfo.setLev("2");
                            mampuInfo.setLgtd(keyRegister.getLgtd());
                            mampuInfo.setLttd(keyRegister.getLttd());
                            mampuInfo.setLgtdPc(keyRegister.getLgtdPc());
                            mampuInfo.setLttdPc(keyRegister.getLttdPc());
                            //attMampuInfoDao.insert(mampuInfo);
                        }
                        orgs.put(nm + secOrg, mampuInfo);
                    }

                    String thiOrg = org.get("thiOrg");
                    if (StringUtils.isNotBlank(thiOrg) && !orgs.keySet().contains(nm + thiOrg)) {
                        AttMampuInfoParam param = new AttMampuInfoParam();
                        param.setNm(secOrg);
                        param.setLev("2");
                        param.setRegId(keyRegister.getId());
                        AttMampuInfo mampuInfo = attMampuInfoDao.getBy(param);
                        if (mampuInfo == null) {
                            mampuInfo = new AttMampuInfo();
                            mampuInfo.setId(UuidUtil.uuid());
                            mampuInfo.setPid(orgs.get(nm + secOrg).getId());
                            mampuInfo.setNm(thiOrg);
                            mampuInfo.setIntm(new Date());
                            mampuInfo.setUptm(new Date());
                            mampuInfo.setPersId(keyRegister.getRecPersId());
                            mampuInfo.setRegId(keyRegister.getId());
                            mampuInfo.setLev("3");
                            mampuInfo.setLgtd(keyRegister.getLgtd());
                            mampuInfo.setLttd(keyRegister.getLttd());
                            mampuInfo.setLgtdPc(keyRegister.getLgtdPc());
                            mampuInfo.setLttdPc(keyRegister.getLttdPc());
                            // attMampuInfoDao.insert(mampuInfo);
                        }
                        orgs.put(nm + thiOrg, mampuInfo);
                    }
                }
            }
        }


        //添加问题
        for (ExcelInspPblm inspPblm : exinspPblmList) {

            BisInspPblm bisInspPblm = new BisInspPblm();
            String pblmId = UuidUtil.uuid();
            bisInspPblm.setPblmId(pblmId);
            if (StringUtils.isNotBlank(inspPblm.getCheckLeader())) {
                bisInspPblm.setReviConc("2");
            } else {
                bisInspPblm.setReviConc("1");
            }
            bisInspPblm.setInspPblmCate(getCate(inspPblm.getInspCate()));
            bisInspPblm.setIfCasePblm("0");
            bisInspPblm.setInspPblmName(setPblmName(inspPblm));
            bisInspPblm.setInspPblmDesc(inspPblm.getInspPblmDesc());
            bisInspPblm.setNote(inspPblm.getNote());
            BisInspAllRlationPersParam allRlationPersParam = new BisInspAllRlationPersParam();
            allRlationPersParam.setPersName(inspPblm.getCheckLeader());
            BisInspAllRlationPers allRlationPers = allRlationPersDao.getBy(allRlationPersParam);
            bisInspPblm.setRecPers(allRlationPers.getGuid());
            bisInspPblm.setState("0");
            bisInspPblm.setHasVedio("0");
            bisInspPblm.setReviOpin(inspPblm.getRevicon());

            //水利工程运行附件
            if (StringUtils.isNotBlank(inspPblm.getQualityAttachNo())) {
                ObjKeyPblmsParam pblmsParam = new ObjKeyPblmsParam();
                pblmsParam.setPblmType(setPblmName(inspPblm));
                pblmsParam.setAttachType("水利工程运行");
                String quality = inspPblm.getQualityAttachNo().replace("附件", "");
                if (quality.indexOf("-") > 0) {
                    String[] noes = quality.split("-");
                    pblmsParam.setAttach(noes[0]);
                    pblmsParam.setSheet(noes[1]);
                } else {
                    pblmsParam.setAttach(quality);
                }
                if (inspPblm.getQualitySn().contains("、")) {
                    String sn = inspPblm.getQualitySn();
                    sn = sn.substring(0, sn.indexOf("、"));
                    pblmsParam.setSn(sn);
                } else {
                    pblmsParam.setSn(inspPblm.getQualitySn());
                }

                ObjKeyPblms keyPblms = keyPblmsDao.getBy(pblmsParam);
                if (keyPblms == null) {
                    pblmsParam.setPblmType("合同问题");
                    keyPblms = keyPblmsDao.getBy(pblmsParam);
                    bisInspPblm.setInspPblmName("合同问题");
                    bisInspPblm.setPblmsId(keyPblms.getId());
                } else {
                    if (StringUtils.isNotBlank(inspPblm.getExp3())) {
                        bisInspPblm.setQualityTypeId(keyPblms.getId());
                    } else {
                        bisInspPblm.setPblmsTypeId(keyPblms.getId());
                    }
                }
            }

            if (StringUtils.isNotBlank(inspPblm.getHetongNo())) {
                ObjKeyPblmsParam pblmsParam = new ObjKeyPblmsParam();
                pblmsParam.setAttachType("水利工程运行");
                pblmsParam.setPblmType("合同问题");
                String hetong = inspPblm.getHetongNo().replace("附件", "");
                if (hetong.indexOf("-") > 0) {
                    String[] noes = hetong.split("-");
                    pblmsParam.setAttach(noes[0]);
                    pblmsParam.setSheet(noes[1]);
                } else {
                    pblmsParam.setAttach(hetong);
                }

                if ("工程缺陷".equals(bisInspPblm.getInspPblmName())) {
                    pblmsParam.setInspPblmCate(getCate(inspPblm.getInspCate()));
                }
                if (inspPblm.getHetongSN().contains("、")) {
                    String sn = inspPblm.getHetongSN();
                    sn = sn.substring(0, sn.indexOf("、"));
                    pblmsParam.setSn(sn);
                } else {
                    pblmsParam.setSn(inspPblm.getHetongSN());
                }

                ObjKeyPblms keyPblms = keyPblmsDao.getBy(pblmsParam);
                if (keyPblms == null) {
                    pblmsParam.setPblmType("违规行为");
                    keyPblms = keyPblmsDao.getBy(pblmsParam);
                    bisInspPblm.setInspPblmName("违规行为");
                    bisInspPblm.setPblmsTypeId(keyPblms.getId());
                } else {
                    bisInspPblm.setPblmsId(keyPblms.getId());
                }
            }


            if (StringUtils.isNotBlank(inspPblm.getFirOrg())) {
                bisInspPblm.setRegid(orgs.get(inspPblm.getProjectNm() + inspPblm.getFirOrg()).getId());
                bisInspPblm.setPblmLong(orgs.get(inspPblm.getProjectNm() + inspPblm.getFirOrg()).getLgtd());
                bisInspPblm.setPblmLat(orgs.get(inspPblm.getProjectNm() + inspPblm.getFirOrg()).getLttd());

            }

            if (StringUtils.isNotBlank(inspPblm.getSecOrg())) {
                bisInspPblm.setRegid(orgs.get(inspPblm.getProjectNm() + inspPblm.getSecOrg()).getId());
                bisInspPblm.setPblmLong(orgs.get(inspPblm.getProjectNm() + inspPblm.getSecOrg()).getLgtd());
                bisInspPblm.setPblmLat(orgs.get(inspPblm.getProjectNm() + inspPblm.getSecOrg()).getLttd());

            }

            if (StringUtils.isNotBlank(inspPblm.getThiOrg())) {
                bisInspPblm.setRegid(orgs.get(inspPblm.getProjectNm() + inspPblm.getThiOrg()).getId());
                bisInspPblm.setPblmLong(orgs.get(inspPblm.getProjectNm() + inspPblm.getThiOrg()).getLgtd());
                bisInspPblm.setPblmLat(orgs.get(inspPblm.getProjectNm() + inspPblm.getThiOrg()).getLttd());

            }

            bisInspPblm.setVillType("9");//9
            bisInspPblm.setObjType("7");//7
            if (StringUtils.isNotBlank(inspPblm.getFaxianTime())) {
                Date date = null;
                try {
                    date = new SimpleDateFormat("yyyy年MM月dd日").parse(setTimeRex(inspPblm.getFaxianTime()));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                bisInspPblm.setCollTime(date);
            }

            if (StringUtils.isNotBlank(inspPblm.getCheckLeader())) {
                allRlationPersParam.setPersName(inspPblm.getCheckLeader());
                allRlationPers = allRlationPersDao.getBy(allRlationPersParam);
                BisInspRectFdbk inspRectFdbk = new BisInspRectFdbk();
                String uuid = UuidUtil.uuid();
                inspRectFdbk.setGuid(uuid);
                if (StringUtils.isNotBlank(inspPblm.getFaxianTime())) {
                    Date date = null;
                    try {
                        date = new SimpleDateFormat("yyyy年MM月dd日").parse(setTimeRex(inspPblm.getFaxianTime()));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    inspRectFdbk.setCollTime(date);
                }
                inspRectFdbk.setDataStat("0");
                inspRectFdbk.setRecPers(allRlationPers.getGuid());
                inspRectFdbk.setPblmId(pblmId);
                inspRectFdbk.setHasVed("0");
                if ("已经整改".equals(inspPblm.getRevicon())) {
                    inspRectFdbk.setRectConc("1");
                } else {
                    inspRectFdbk.setRectConc("0");
                }
                inspRectFdbk.setOrgGuid(bisInspPblm.getInspGroupId());
                inspRectFdbk.setNote(inspPblm.getCheckPerson());
                inspRectFdbk.setRectMeas(inspPblm.getReviDetail());
                // inspRectFdbkDao.insert(inspRectFdbk);
                bisInspPblm.setFdbkId(uuid);
            }

            if (StringUtils.isNotBlank(inspPblm.getRechLeader())) {
                allRlationPersParam.setPersName(inspPblm.getRechLeader());
                allRlationPers = allRlationPersDao.getBy(allRlationPersParam);
                BisInspRectFdbk inspRectFdbk = new BisInspRectFdbk();
                String uuid = UuidUtil.uuid();
                inspRectFdbk.setGuid(uuid);
                if (StringUtils.isNotBlank(inspPblm.getRechTime())) {
                    Date date = null;
                    try {
                        date = new SimpleDateFormat("yyyy年MM月dd日").parse(setTimeRex(inspPblm.getRechTime()));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    inspRectFdbk.setCollTime(date);
                }

                inspRectFdbk.setDataStat("0");
                inspRectFdbk.setRecPers(allRlationPers.getGuid());
                inspRectFdbk.setPblmId(pblmId);
                inspRectFdbk.setHasVed("0");
                if ("已经整改".equals(inspPblm.getRevicon())) {
                    inspRectFdbk.setRectConc("1");
                } else {
                    inspRectFdbk.setRectConc("0");
                }
                inspRectFdbk.setOrgGuid(bisInspPblm.getInspGroupId());
                inspRectFdbk.setNote(inspPblm.getRechPerson());
                inspRectFdbk.setRectMeas(inspPblm.getReviDetail());
                //inspRectFdbkDao.insert(inspRectFdbk);
                bisInspPblm.setFdbkId(uuid);
            }
            // bisInspPblmDao.insert(bisInspPblm);
        }

        //添加复查问题
        //  List<ExcelInspPblm> fxblmList= new ArrayList<>();
    }

    private String getCate(String inspCate) {
        if ("一般".equals(inspCate)) {
            return "0";
        }
        if ("较重".equals(inspCate)) {
            return "1";
        }
        if ("严重".equals(inspCate)) {
            return "2";
        }
        if ("特别严重".equals(inspCate)) {
            return "3";
        }
        return "";
    }

    private String setPblmName(ExcelInspPblm inspPblm) {
        if (StringUtils.isBlank(inspPblm.getQualityAttachNo()) && StringUtils.isNotBlank(inspPblm.getHetongNo())) {
            return "合同问题";
        }
        if (StringUtils.isNotBlank(inspPblm.getQualityAttachNo()) && StringUtils.isNotBlank(inspPblm.getExp3())) {
            return "工程缺陷";
        }
        return "违规行为";
    }


    private String setTimeRex(String faxianTime) {
        if (StringUtils.isBlank(faxianTime)) {
            return "";
        }
        System.out.println("faxianTime--->" + faxianTime);
        String year = faxianTime.substring(0, faxianTime.indexOf("年"));
        String month = faxianTime.substring(faxianTime.indexOf("年") + 1, faxianTime.indexOf("月"));
        if (month.length() < 2) {
            month = "0" + month;
        }
        String day = faxianTime.substring(faxianTime.indexOf("月") + 1, faxianTime.indexOf("日"));
        if (day.length() < 2) {
            day = "0" + day;
        }
        return year + "年" + month + "月" + day + "日";
    }


    @Override
    public void inputTById(String type, String filePath) {
        File file = new File(filePath);
        if (!file.isFile()) {
            return;
        }
        List<ExcelInspLevelDto> levels = new ArrayList<>();
        try {
            levels = LoadExcel.getExcelLevel(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (ExcelInspLevelDto lv : levels) {
            if (StringUtils.isBlank(lv.getId())) {
                System.out.println("当前id为空");
                continue;
            }
            if (BisInspEnum.WAGA.getKey().equals(type)) {
                AttWagaBase base = new AttWagaBase();
                base.setObjCode(lv.getId());
                base.setDisplayLevel(lv.getDisPlayLevel());
                attWagaBaseDao.update(base);
            } else if ("wain".equals(type)) {
                AttWainBase base = new AttWainBase();
                base.setObjCode(lv.getId());
                base.setDisplayLevel(lv.getDisPlayLevel());
                //attWainBaseDao.update(base);
            } else if ("suws".equals(type)) {
                AttSwhsBase base = new AttSwhsBase();
                base.setSwhsCode(lv.getId());
                base.setDisplayLevel(lv.getDisPlayLevel());
                attSwhsBaseDao.updateLevelByCode(base);
            } else if ("rsvr".equals(type)) {
                AttRsBase base = new AttRsBase();
                base.setRsCode(lv.getId());
                base.setDisplayLevel(lv.getDisPlayLevel());
                rsBaseDao.update(base);
            }
        }
        System.out.println("导入成功");
    }

    @Override
    public List<String> checkPers(String filePath) {
        File file = new File(filePath);
        if (!file.isFile()) {
            return null;
        }
        List<ExcelInspLevelDto> levels = new ArrayList<>();
        try {
            levels = LoadExcel.getExcelLevel(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<String> names = new ArrayList<>();
        for (ExcelInspLevelDto lv : levels) {
            BisInspAllRlationPersParam param = new BisInspAllRlationPersParam();
            param.setPersName(lv.getId().replaceAll(" ", ""));
            List<BisInspAllRlationPers> list = allRlationPersDao.findList(param);
            if (list == null || list.size() == 0) {
                if (!names.contains(lv.getId().replaceAll(" ", ""))) {
                    names.add(lv.getId().replaceAll(" ", ""));
                }
            }
        }
        return names;
    }

    @Override
    public List<ExcelInspPersDto> inputRla(String filePath, String year, String mon, String type) {
        List<ExcelInspPersDto> list = new ArrayList<>();
        File file = new File(filePath);
        if (!file.isFile()) {
            return null;
        }
        List<ExcelInspPersDto> persList = new ArrayList<>();
        try {
            persList = LoadExcel.getExcelPers(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (type.indexOf("0") < 0) {
            type = "00" + type;
        }
        if (mon.indexOf("0") < 0) {
            mon = "0" + mon;
        }
        BisInspAllParam param = new BisInspAllParam();
        param.setPid(type);
        List<BisInspAll> orgs = bisInspAllDao.findList(param);
        if (orgs == null || orgs.size() <= 0) {
            return list;
        }
        Map<String, BisInspAll> orgMaps = orgs.stream().collect(Collectors.toMap(BisInspAll::getPnm, BisInspAll -> BisInspAll));
        CopyOnWriteArrayList a = new CopyOnWriteArrayList();
        a.addAll(persList);
        Iterator<ExcelInspPersDto> it = a.iterator();
        while (it.hasNext()) {
            ExcelInspPersDto pers = it.next();
            //机构名称为空
            if (StringUtils.isBlank(pers.getOrgName())) {
                pers.setNote("机构名称为空");
                list.add(pers);
                continue;
            }
            BisInspAll ins = orgMaps.get(pers.getOrgName().trim());
            if (ins == null) {
                pers.setNote("机构不存在");
                list.add(pers);
                continue;
            }
            if (StringUtils.isBlank(pers.getGroName())) {
                pers.setNote("组名称为空");
                list.add(pers);
                continue;
            }
            String pid = ins.getId();
            BisInspAllParam param1 = new BisInspAllParam();
            param1.setPid(pid);
            param1.setInspYear(year);
            param1.setInspMnth(mon);
            List<BisInspAll> alls = bisInspAllDao.findList(param1);
            BisInspAll bisInspAll = null;
            if (alls == null || alls.size() <= 0) {
                pers.setNote("组不存在");
                //create a group
//                bisInspAll = new BisInspAll();
//                bisInspAll.setId(pid+"001");
//                bisInspAll.setPid(pid);
//                bisInspAll.setPnm(year + "年" + mon + "月督查工作");
//                bisInspAll.setInspMnth(mon);
//                bisInspAll.setInspYear(year);
//                bisInspAllDao.insert(bisInspAll);
                list.add(pers);
                continue;
            } else {
                bisInspAll = alls.get(0);
            }
            param1 = new BisInspAllParam();
            param1.setInspYear(year);
            param1.setInspMnth(mon);
            param1.setPid(bisInspAll.getId());
            // param1.setPnm(setName(pers.getGroName()));
            param1.setPnm(pers.getGroName() + "组");
            BisInspAll all = bisInspAllDao.getBy(param1);
            if (all == null) {
                param1.setPnm(setName(pers.getGroName()));
                all = bisInspAllDao.getBy(param1);
            }
            if (all == null) {
                pers.setNote(param1.getPnm() + "不存在");
                list.add(pers);
                continue;
            }
            //start to find rla
            String id = all.getId();
            BisInspAllRlationParam rlationParam = new BisInspAllRlationParam();
            rlationParam.setId(id);
            //1：组长；2：组员；3：联络员
            BisInspAllRlationPersParam persParam = new BisInspAllRlationPersParam();
            persParam.setOrgId(pid.substring(3, 6));
            if (StringUtils.isNotBlank(pers.getHeadman().replaceAll(" ", ""))) {
                persParam.setPersName(pers.getHeadman().replaceAll(" ", ""));
                addRla(list, pers, rlationParam, persParam, "1");
            }

            if (StringUtils.isNotBlank(pers.getMembers().replaceAll(" ", ""))) {
                String dpnm = pers.getMemNm().replaceAll(" ", "");
                if (dpnm.indexOf("/") > 0) {
                    persParam.setDpnm(dpnm.split("/")[0]);
                } else {
                    persParam.setDpnm(dpnm);
                }
                String persName = pers.getMembers().replaceAll(" ", "");
                if (persName.indexOf("/") > 0) {
                    String[] names = persName.split("/");
                    for (String name : names) {
                        persParam.setPersName(name);
                        addRla(list, pers, rlationParam, persParam, "2");
                    }
                } else {
                    persParam.setPersName(persName);
                    addRla(list, pers, rlationParam, persParam, "2");
                }

            }
            if (StringUtils.isNotBlank(pers.getConPers().replaceAll(" ", ""))) {
                String persName = pers.getConPers().replaceAll(" ", "");
                if (persName.indexOf("/") > 0) {
                    String[] names = persName.split("/");
                    for (String name : names) {
                        persParam.setPersName(name);
                        addRla(list, pers, rlationParam, persParam, "3");
                    }
                } else {
                    persParam.setPersName(persName);
                    addRla(list, pers, rlationParam, persParam, "3");
                }
            }
        }
        return list;
    }

    private void addRla(List<ExcelInspPersDto> list, ExcelInspPersDto pers, BisInspAllRlationParam rlationParam, BisInspAllRlationPersParam persParam, String ptype) {
        BisInspAllRlationPers per = allRlationPersDao.getBy(persParam);
        if (per == null && StringUtils.isNotBlank(persParam.getDpnm())) {
            persParam.setDpnm("");
            per = allRlationPersDao.getBy(persParam);
        }
        if (per == null) {
            pers.setNote("当前名称" + persParam.getPersName() + "不在" + pers.getOrgName() + "机构下");
            list.add(pers);
            return;
        }
        String persId = per.getGuid();
        rlationParam.setPersid(persId);
        //rlationParam.setPertype(ptype);
        BisInspAllRlation rla = rlationDao.getBy(rlationParam);
        if (rla == null) {
            rla = new BisInspAllRlation();
            rla.setId(rlationParam.getId());
            rla.setPersid(persId);
            rla.setPertype(ptype);
            rlationDao.insert(rla);
        } else {
            rla.setPertype(ptype);
            rlationDao.update(rla);
        }
    }

    @Deprecated
    private String setName(String groName) {
        if ("北京".equals(groName) || "上海".equals(groName) || "天津".equals(groName) || "重庆".equals(groName)) {
            return groName + "市组";
        }
        return groName + "省组";
    }
}