package cn.com.countrygarden.bi.main.service.insuranceOrder.impl;

import cn.com.countrygarden.bi.main.core.Response;
import cn.com.countrygarden.bi.main.core.Result;
import cn.com.countrygarden.bi.main.dao.insuranceOrder.InsuranceOrderAuthorityMapper;
import cn.com.countrygarden.bi.main.dao.insuranceOrder.InsuranceOrderMapper;
import cn.com.countrygarden.bi.main.dao.insuranceOrder.InsuranceOrderTemporaryMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.ManagerMapForErpMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.TbSysOrganMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.TbSysUserMapper;
import cn.com.countrygarden.bi.main.dto.advise.ProjectListVO;
import cn.com.countrygarden.bi.main.dto.insuranceOrder.InsuranceOrderDTO;
import cn.com.countrygarden.bi.main.dto.tbsysuser.RtsUserQuery;
import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.AccessToken;
import cn.com.countrygarden.bi.main.model.PageData;
import cn.com.countrygarden.bi.main.model.dup.DupComm;
import cn.com.countrygarden.bi.main.model.dup.DupOrgan;
import cn.com.countrygarden.bi.main.model.insuranceOrder.InsuranceOrder;
import cn.com.countrygarden.bi.main.model.insuranceOrder.InsuranceOrderAuthority;
import cn.com.countrygarden.bi.main.model.insuranceOrder.InsuranceOrderAuthoritycheckedKeys;
import cn.com.countrygarden.bi.main.model.insuranceOrder.InsuranceOrderTemporary;
import cn.com.countrygarden.bi.main.model.twerp.hm.TbSysUser;
import cn.com.countrygarden.bi.main.service.insuranceOrder.InsuranceOrderService;
import cn.com.countrygarden.bi.main.util.ExcelCommon;
import cn.com.countrygarden.bi.main.util.erp.StringUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageRowBounds;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
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 javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URISyntaxException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 *   保险订单业务处理
 * </p>
 *
 * @author 吕伟峰
 * @date 2020-07-15
 * @version V1.0
 */
@Service
@Transactional
public class InsuranceOrderServiceImpl implements InsuranceOrderService {

    @Autowired
    private ManagerMapForErpMapper managerMapForErpMapper;

    @Autowired
    private InsuranceOrderMapper insuranceOrderMapper;

    @Autowired
    private InsuranceOrderAuthorityMapper insuranceOrderAuthorityMapper;

    @Autowired
    private TbSysOrganMapper tbSysOrganMapper;

    @Autowired
    private ExcelCommon excelCommon;

    @Autowired
    private TbSysUserMapper tbSysUserMapper;

    @Autowired
    private InsuranceOrderTemporaryMapper insuranceOrderTemporaryMapper;

    @Override
    public Result queryPageList(InsuranceOrderDTO insuranceOrder, int page, int size, AccessToken accessToken) {

        // 获取登录人的角色授权
        //Map<String, Object> rolesMap = insuraService.findRoleInfoMap(accessToken.getUserCode());
        List<String> organCodeString=new ArrayList<>();
        List<String> commIdString=new ArrayList<>();
        InsuranceOrderAuthority authority=new InsuranceOrderAuthority();
        authority.setUserCode(accessToken.getUserCode());
        authority.setEnable(1);
        List<InsuranceOrderAuthority> orAuthList=insuranceOrderAuthorityMapper.select(authority);
        if(CollectionUtils.isNotEmpty(orAuthList)){
            for (int i = 0; i <orAuthList.size() ; i++) {
                if (orAuthList.get(i).getOrganType().equals(1)){
                    organCodeString.add(orAuthList.get(i).getOrgCode());
                }else if (orAuthList.get(i).getOrganType().equals(0)){
                    commIdString.add(orAuthList.get(i).getCommCode());
                }
            }
        }else {
            return null;
        }
       // List<Integer> paixu=insuraService.roleInfoList(rolesMap);
/*        for (int i=0;i<paixu.size();i++) {
            if (paixu.get(i)==1){
                break;
            }else if (paixu.get(i)==2){
                break;
            }else if(paixu.get(i)==3){
                organCodeString=managerMapForErpMapper.organCodeString(accessToken.getUserCode(),null);
                if(organCodeString==null||organCodeString.size()==0){
                    return null;
                }
                break;
            }else if(paixu.get(i)==4){
                commIdString=managerMapForErpMapper.commIdString(accessToken.getUserCode());
                if(commIdString==null||commIdString.size()==0){
                    return null;
                }
                break;
            }
        }*/
        if(StringUtil.isNotBlank(insuranceOrder.getFirstPayDateList())){
            String[] firstPayDate=insuranceOrder.getFirstPayDateList().split(",");
            insuranceOrder.setFirstPayDate1(firstPayDate[0]);
            insuranceOrder.setFirstPayDate2(firstPayDate[1]);
        }

        if(StringUtil.isNotBlank(insuranceOrder.getTransactionDateList())){
            String[] transactionDate=insuranceOrder.getTransactionDateList().split(",");
            insuranceOrder.setTransactionDate1(transactionDate[0]);
            insuranceOrder.setTransactionDate2(transactionDate[1]);
        }
        PageRowBounds pageRowBounds = new PageRowBounds(page,size);
        if(CollectionUtils.isNotEmpty(organCodeString)&&CollectionUtils.isNotEmpty(commIdString)){
            List<InsuranceOrder> queryPageList=insuranceOrderMapper.queryPageList(insuranceOrder,pageRowBounds,organCodeString,commIdString);
            PageData pageData = new PageData(queryPageList,pageRowBounds);
            return Response.response(pageData);
        }else if(CollectionUtils.isNotEmpty(organCodeString)){
            List<InsuranceOrder> queryPageList=insuranceOrderMapper.queryPageListOrg(insuranceOrder,pageRowBounds,organCodeString);
            PageData pageData = new PageData(queryPageList,pageRowBounds);
            return Response.response(pageData);
        }else if(CollectionUtils.isNotEmpty(commIdString)){
            List<InsuranceOrder> queryPageList=insuranceOrderMapper.queryPageListComm(insuranceOrder,pageRowBounds,commIdString);
            PageData pageData = new PageData(queryPageList,pageRowBounds);
            return Response.response(pageData);
        }
        return null;
    }

    @Override
    public Result listUser(RtsUserQuery rtsUserQuery, int page, int size, AccessToken accessToken) {
        PageRowBounds pageRowBounds = new PageRowBounds(page,size);
        List<TbSysUser> list=tbSysUserMapper.findByNameAndCodeInsuranceOrder(pageRowBounds,rtsUserQuery);
        PageData pageData = new PageData(list,pageRowBounds);
        return Response.response(pageData);
    }

    @Override
    public List<DupOrgan> findDupCommByUserCode(String userCode, String organCode, boolean isManager) {

        // 获取登录人的角色授权
        //Map<String, Object> rolesMap = insuraService.findRoleInfoMap(userCode);
        List<String> organCodeString=new ArrayList<>();
        List<String> organCodeStringDTO=new ArrayList<>();
        List<String> commIdString=new ArrayList<>();
        InsuranceOrderAuthority authority=new InsuranceOrderAuthority();
        authority.setUserCode(userCode);
        authority.setEnable(1);
        List<InsuranceOrderAuthority> orAuthList=insuranceOrderAuthorityMapper.select(authority);
        if(CollectionUtils.isNotEmpty(orAuthList)){
            for (int i = 0; i <orAuthList.size() ; i++) {
                if (orAuthList.get(i).getOrganType().equals(1)){
                    organCodeString.add(orAuthList.get(i).getOrgCode());
                }else if (orAuthList.get(i).getOrganType().equals(0)){
                    organCodeStringDTO.add(orAuthList.get(i).getOrgCode());
                    commIdString.add(orAuthList.get(i).getCommCode());
                }
            }
        }else {
            return null;
        }
 /*       List<Integer> paixu=insuraService.roleInfoList(rolesMap);
        for (int i=0;i<paixu.size();i++) {
            if (paixu.get(i)==1){
                break;
            }else if (paixu.get(i)==2){
                break;
            }else if(paixu.get(i)==3){
                organCodeString=managerMapForErpMapper.organCodeString(userCode,null);
                if(organCodeString==null||organCodeString.size()==0){
                    return null;
                }
                break;
            }else if(paixu.get(i)==4){
                commIdString=managerMapForErpMapper.commIdString(userCode);
                if(commIdString==null||commIdString.size()==0){
                    return null;
                }
                organCodeString=managerMapForErpMapper.organCodeString(userCode,commIdString);
                if(organCodeString==null||organCodeString.size()==0){
                    return null;
                }
                break;
            }
        }*/
        List<DupOrgan> result=null;
        List<DupOrgan> resultDTO=null;
        if(CollectionUtils.isNotEmpty(organCodeString)&&CollectionUtils.isNotEmpty(commIdString)){
             result=managerMapForErpMapper.queryAllRegionRdInfo(null,organCodeString,null);
            for (DupOrgan organ : result) {
                //查项目
                List<DupComm> comms=managerMapForErpMapper.queryAllCommCaseDistrict(organ.getOrganCode(),null,null,null,null);
                organ.setComms(comms);
            }
            resultDTO=managerMapForErpMapper.queryAllRegionRdInfo(null,organCodeStringDTO,null);
            for (DupOrgan organ : resultDTO) {
                //查项目
                List<DupComm> comms=managerMapForErpMapper.queryAllCommCaseDistrict(organ.getOrganCode(),null,commIdString,null,null);
                organ.setComms(comms);
            }
            result.addAll(resultDTO);
        }else if(CollectionUtils.isNotEmpty(organCodeString)){
            result =managerMapForErpMapper.queryAllRegionRdInfo(null,organCodeString,null);
            for (DupOrgan organ : result) {
                //查项目
                List<DupComm> comms=managerMapForErpMapper.queryAllCommCaseDistrict(organ.getOrganCode(),null,commIdString,null,null);
                organ.setComms(comms);
            }
        }else if(CollectionUtils.isNotEmpty(commIdString)){
            List<DupComm> commsOrg=managerMapForErpMapper.queryAllCommCaseDistrict(null,null,commIdString,null,null);
            for (int i = 0; i <commsOrg.size() ; i++) {
                organCodeString.add(commsOrg.get(i).getOrganCode());
            }
            result =managerMapForErpMapper.queryAllRegionRdInfo(null,organCodeString,null);
            for (DupOrgan organ : result) {
                //查项目
                List<DupComm> comms=managerMapForErpMapper.queryAllCommCaseDistrict(organ.getOrganCode(),null,commIdString,null,null);
                organ.setComms(comms);
            }
        }
        return  result;
    }
    @Override
    public Result queryById(Integer id) {
        InsuranceOrder insuranceOrder=new InsuranceOrder();
        insuranceOrder.setId(id);
        InsuranceOrder insurance=insuranceOrderMapper.selectOne(insuranceOrder);
        return Response.response(insurance);
    }

    DecimalFormat format = new DecimalFormat("0.00");

    @Override
    public Result add(InsuranceOrder insuranceOrder, AccessToken accessToken) {

        InsuranceOrder insur=new InsuranceOrder();
        insur.setInsuranceNumber(insuranceOrder.getInsuranceNumber());
        insur.setEnable(1);
        List<InsuranceOrder> list=insuranceOrderMapper.select(insur);
        if (list!=null&&list.size()>0){
            return Response.responseFail("保险单号重复，请核对再添加");
        }
        String pre = format.format(new BigDecimal(insuranceOrder.getPremium()));
        insuranceOrder.setPremium(pre);
        insuranceOrder.setEnable(1);
        insuranceOrder.setCreateTime(new Date());
        insuranceOrder.setCreateBy(accessToken.getUserCode());
        insuranceOrder.setUpdateTime(new Date());
        insuranceOrder.setUpdateBy(accessToken.getUserCode());
        insuranceOrderMapper.insert(insuranceOrder);
        return Response.response("添加成功");
    }

    @Override
    public Result edit(InsuranceOrder insuranceOrder, AccessToken accessToken) {

        InsuranceOrder insur=new InsuranceOrder();
        insur.setId(insuranceOrder.getId());
        insur.setInsuranceNumber(insuranceOrder.getInsuranceNumber());
        List<InsuranceOrder> list=insuranceOrderMapper.selectEdit(insur);
        if (list!=null&&list.size()>0){
            return Response.responseFail("保险单号重复，请核对再添加");
        }

        String pre = format.format(new BigDecimal(insuranceOrder.getPremium()));
        insuranceOrder.setPremium(pre);
        insuranceOrder.setUpdateTime(new Date());
        insuranceOrder.setUpdateBy(accessToken.getUserCode());
        insuranceOrderMapper.update(insuranceOrder);
        return Response.response("修改成功");
    }

    @Override
    public Result del(Integer id, AccessToken accessToken) {
        InsuranceOrder insuranceOrder=new InsuranceOrder();
        insuranceOrder.setId(id);
        insuranceOrder.setUpdateTime(new Date());
        insuranceOrder.setUpdateBy(accessToken.getUserCode());
        insuranceOrderMapper.deleteupdate(insuranceOrder);
        return Response.response("删除成功");
    }

    @Override
    public Result delpiliang(String ids, AccessToken accessToken) {
        String[] idString=ids.split(",");
        List<String> rids =  Arrays.asList(idString);

        for (int i = 0; i <rids.size() ; i++) {
            InsuranceOrder insuranceOrder=new InsuranceOrder();
            insuranceOrder.setId(Integer.parseInt(rids.get(i)));
            insuranceOrder.setUpdateTime(new Date());
            insuranceOrder.setUpdateBy(accessToken.getUserCode());
            insuranceOrderMapper.deleteupdate(insuranceOrder);
        }
        return Response.response("删除成功");
    }

    @Override
    public void downloadTemplate(String errorList,HttpServletRequest request, AccessToken accessToken, HttpServletResponse response) throws IOException {

        // excel模板路径
        String path = "";
        try {
            path = this.getClass().getClassLoader().getResource("/").toURI().getPath() + "/excel/insuranceOrder/保险订单导入模板.xlsx";
        } catch (URISyntaxException e) {
            e.printStackTrace();
        }
        File fi = new File(path);

//        ClassPathResource cpr = new ClassPathResource("excel/insuranceOrder/"+ File.separator+"保险订单导入模板.xlsx");
//        InputStream in = cpr.getInputStream();

        InputStream in = new FileInputStream(fi);
        // 读取excel模板

        XSSFWorkbook wb = new XSSFWorkbook(in);
        // 读取excel模板

        XSSFSheet sheet1 = wb.getSheetAt(0);


       // List<ProjectListVO> projectList1 = tbSysOrganMapper.findProject(null, accessToken.getUserCode(),null);
        InsuranceOrderAuthority authority=new InsuranceOrderAuthority();
        authority.setUserCode(accessToken.getUserCode());
        authority.setEnable(1);
        List<InsuranceOrderAuthority> projectList=insuranceOrderAuthorityMapper.select(authority);
        if(CollectionUtils.isNotEmpty(projectList)){
            projectListinationVO(sheet1,projectList);
        }
        List<InsuranceOrder> orderList =new ArrayList<>();
        InsuranceOrderTemporary insTempor=null;
        insTempor=new InsuranceOrderTemporary();
        insTempor.setUserCode(accessToken.getUserCode());
        List<InsuranceOrderTemporary> errlist=insuranceOrderTemporaryMapper.select(insTempor);
        if(CollectionUtils.isNotEmpty(errlist)){
            JSONArray jsonArray= JSON.parseArray(errlist.get(0).getTemporary());
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject=jsonArray.getJSONObject(i);
                InsuranceOrder insu=new InsuranceOrder();
                insu.setRegionCode(jsonObject.getString("regionCode"));
                insu.setRegionName(jsonObject.getString("regionName"));
                insu.setProjectCode(jsonObject.getString("projectCode"));
                insu.setProjectName(jsonObject.getString("projectName"));
                insu.setMonthDate(jsonObject.getString("monthDate"));
                insu.setInsuranceNumber(jsonObject.getString("insuranceNumber"));
                insu.setProductName(jsonObject.getString("productName"));
                insu.setAgentName(jsonObject.getString("agentName"));
                insu.setPost(jsonObject.getString("post"));
                insu.setPolicyholder(jsonObject.getString("policyholder"));
                insu.setInsured(jsonObject.getString("insured"));
                insu.setPremium(jsonObject.getString("premium"));
                insu.setTransactionDate(jsonObject.getString("transactionDate"));
                insu.setBrokerageFees(jsonObject.getString("brokerageFees"));
                insu.setErrorMessage(jsonObject.getString("errorMessage"));
                orderList.add(insu);
            }
            XSSFSheet sheet2 = wb.getSheetAt(1);
            projectListErrorVO(sheet2,orderList);
            insTempor=new InsuranceOrderTemporary();
            insTempor.setUserCode(accessToken.getUserCode());
            insuranceOrderTemporaryMapper.delete(insTempor);
        }


        excelCommon.downloadInsuranceOrderExcel(wb, "保险订单导入模板", response);
    }

    private void projectListinationVO(XSSFSheet sheet,  List<InsuranceOrderAuthority> projectList){
        int count = 1;
        int copyRow = 3;

        List<String> commids=new ArrayList<>();
        List<String> orgCos=new ArrayList<>();
        List<ProjectListVO> projtLi=new ArrayList<>();
        for (int i = 0; i <projectList.size() ; i++) {
             if (projectList.get(i).getOrganType().equals(0)){
                 commids.add(projectList.get(i).getCommCode());
             }else if(projectList.get(i).getOrganType().equals(1)){
                 orgCos.add(projectList.get(i).getOrgCode());
             }
        }

        if(CollectionUtils.isNotEmpty(orgCos)){
            projtLi.addAll(tbSysOrganMapper.findProject(null,null,null,null,orgCos));
        }
        if(CollectionUtils.isNotEmpty(commids)){
            projtLi.addAll(tbSysOrganMapper.findProject(null, null,null,commids,null));
        }
        for (ProjectListVO livo : projtLi) {
            excelCommon.copyRows(sheet, 3, 3, copyRow);

            copyRow++;
            count++;

            XSSFCell cell = null;
            cell = sheet.getRow(count).getCell(12);// 区域编号
            cell.setCellValue(livo.getOrganCode() == null ? "" : livo.getOrganCode());
            cell = sheet.getRow(count).getCell(13);// 区域名称
            cell.setCellValue(livo.getOrganName() == null ? "" : livo.getOrganName());
            cell = sheet.getRow(count).getCell(14);// 项目编号
            cell.setCellValue(livo.getCommId() == null ? "" : livo.getCommId());
            cell = sheet.getRow(count).getCell(15);// 项目名称
            cell.setCellValue(livo.getCommName() == null ? "" : livo.getCommName());
        }
    }

    @Override
    public Result excelToxls(HttpServletRequest request,HttpServletResponse response,MultipartFile multipartFile, String fileName,String usname, String userCode, AccessToken accessToken) throws IOException{

        BufferedInputStream bf = new BufferedInputStream(multipartFile.getInputStream());
        Workbook xwb = new XSSFWorkbook(bf);
        Cell cell1,cell2,cell3,cell4;
        Sheet sheet = xwb.getSheetAt(0);//获取第几个页面0开始
        int sheetRowCount=sheet.getPhysicalNumberOfRows();//获取行数
        List<ProjectListVO> projectList=new ArrayList<>();
        for (int i = 2; i <= sheetRowCount; i++) {
            ProjectListVO proVO=new ProjectListVO();
            Row row = sheet.getRow(i);
            if(row!=null){
                if(row.getCell(12)!=null){
                    cell1=row.getCell(12);
                    cell1.setCellType(CellType.STRING);
                    if(StringUtils.isNotBlank(cell1.getStringCellValue())){
                        proVO.setOrganCode(cell1.getStringCellValue());
                    }else {
                        break;
                    }

                }
                if(row.getCell(13)!=null){
                    cell2=row.getCell(13);
                    cell2.setCellType(CellType.STRING);
                    proVO.setOrganName(cell2.getStringCellValue());
                }

                if(row.getCell(14)!=null){
                    cell3=row.getCell(14);
                    cell3.setCellType(CellType.STRING);
                    proVO.setCommId(cell3.getStringCellValue());
                }
                if(row.getCell(15)!=null){
                    cell4=row.getCell(15);
                    cell4.setCellType(CellType.STRING);
                    proVO.setCommName(cell4.getStringCellValue());
                }
                if(proVO!=null){
                    projectList.add(proVO);
                }
            }
        }
        Sheet sheet2 = xwb.getSheetAt(1);//获取第几个页面1开始
        InsuranceOrderAuthority authority=new InsuranceOrderAuthority();
        authority.setUserCode(userCode);
        authority.setEnable(1);
        List<InsuranceOrderAuthority> authorityList=insuranceOrderAuthorityMapper.select(authority);
        if(CollectionUtils.isEmpty(authorityList)){
            throw new ServiceException("当前账号没有任何区域项目权限");
        }
        List<InsuranceOrder> index=importXlsx(request,response,projectList,sheet2,authorityList,accessToken,usname);
       if(CollectionUtils.isEmpty(index)){
           return Response.response("导入成功");
       }else {
           return Response.response(index);
       }

    }

    @Override
    public Result getAllRtsOrganTree(String userCode) {
        List<DupOrgan> result =managerMapForErpMapper.queryAllRegionRdInfo(null,null,null);
        for (DupOrgan organ : result) {
            organ.setType(1);
            //查项目
            List<DupComm> comms=managerMapForErpMapper.queryAllCommCaseDistrict(organ.getOrganCode(),null,null,null,null);
            for (int i = 0; i <comms.size() ; i++) {
                comms.get(i).setOrganName(comms.get(i).getCommName());
                comms.get(i).setOrganCode(comms.get(i).getCommId());
                comms.get(i).setOrgan(organ.getOrganCode());
                comms.get(i).setType(0);
            }
                organ.setComms(comms);
        }
        InsuranceOrderAuthoritycheckedKeys insuitycheckedKeys=new InsuranceOrderAuthoritycheckedKeys();
        insuitycheckedKeys.setResult(result);

        InsuranceOrderAuthority authority=new InsuranceOrderAuthority();
        authority.setUserCode(userCode);
        authority.setEnable(1);
        List<String> commKs=new ArrayList<>();
        List<InsuranceOrderAuthority> list=insuranceOrderAuthorityMapper.select(authority);
        if(CollectionUtils.isNotEmpty(list)){
            for (int i = 0; i <list.size() ; i++) {
                if(list.get(i).getOrganType().equals(0)){
                    commKs.add(list.get(i).getCommCode());
                }else if(list.get(i).getOrganType().equals(1)){
                    List<DupComm> comms=managerMapForErpMapper.queryAllCommCaseDistrict(list.get(i).getOrgCode(),null,null,null,null);
                    if (CollectionUtils.isNotEmpty(comms)){
                        for (int j = 0; j <comms.size() ; j++) {
                            commKs.add(comms.get(j).getCommId());
                            commKs.add(comms.get(j).getOrgan());
                        }
                    }else {
                        commKs.add(list.get(i).getOrgCode());
                    }

                }
            }
        }
        Set<String> set=new HashSet<>(commKs);
        List<String> commKs1=new ArrayList<>(set);
        insuitycheckedKeys.setCheckedKeys(commKs1);
        return Response.response(insuitycheckedKeys);
    }

    @Override
    public boolean editOrganAuth(String userCode, String regionsCodeStr, String projectsCodeStr, AccessToken accessToken) {
        InsuranceOrderAuthority orderAuthority=null;
        orderAuthority=new InsuranceOrderAuthority();
        orderAuthority.setUserCode(userCode);
        insuranceOrderAuthorityMapper.delete(orderAuthority);
        List<String> regi=null;
        if(StringUtils.isNotBlank(regionsCodeStr)){
            String[] reStr=regionsCodeStr.split(",");
            regi=Arrays.asList(reStr);
            List<InsuranceOrderAuthority> list=new ArrayList<>();
            for (int i = 0; i <regi.size() ; i++) {
                orderAuthority=new InsuranceOrderAuthority();
                orderAuthority.setOrgCode(regi.get(i));
                orderAuthority.setUserCode(userCode);
                orderAuthority.setEnable(1);
                orderAuthority.setOrganType(1);
                orderAuthority.setCreateBy(accessToken.getUserCode());
                orderAuthority.setCreateTime(new Date());
                list.add(orderAuthority);
            }
            insuranceOrderAuthorityMapper.insertList(list);
        }

        if(StringUtils.isNotBlank(projectsCodeStr)){
            JSONArray jsonArrayPro= JSON.parseArray(projectsCodeStr);
            List<InsuranceOrderAuthority> listpoj=new ArrayList<>();
                    for (int i = 0; i <jsonArrayPro.size() ; i++) {
                        orderAuthority=new InsuranceOrderAuthority();
                        JSONObject jsonObjectReg=jsonArrayPro.getJSONObject(i);
                            orderAuthority.setOrgCode(jsonObjectReg.getString("organ"));
                            orderAuthority.setCommCode(jsonObjectReg.getString("commId"));
                            orderAuthority.setOrganType(0);
                            orderAuthority.setEnable(1);
                            orderAuthority.setUserCode(userCode);
                            orderAuthority.setCreateBy(accessToken.getUserCode());
                            orderAuthority.setCreateTime(new Date());
                            listpoj.add(orderAuthority);

                    }
            if(CollectionUtils.isNotEmpty(listpoj)){
     /*           for (int i = 0; i <listpoj.size() ; i++) {
                    insuranceOrderAuthorityMapper.insert(listpoj.get(i));
                }*/
                insuranceOrderAuthorityMapper.insertList(listpoj);
            }
        }

        orderAuthority=new InsuranceOrderAuthority();
        orderAuthority.setOrganType(1);
        orderAuthority.setUserCode(userCode);
        List<InsuranceOrderAuthority> list1=insuranceOrderAuthorityMapper.select(orderAuthority);

        orderAuthority=new InsuranceOrderAuthority();
        orderAuthority.setOrganType(0);
        orderAuthority.setUserCode(userCode);
        List<InsuranceOrderAuthority> list2=insuranceOrderAuthorityMapper.select(orderAuthority);

        List<String> deId=new ArrayList<>();
        for (int i = 0; i <list1.size() ; i++) {
            for (int j = 0; j <list2.size() ; j++) {
                if(list1.get(i).getOrgCode().equals(list2.get(j).getOrgCode())){
                    deId.add(list2.get(j).getId().toString());
                    //insuranceOrderAuthorityMapper.deleteByIds(list2.get(j).getId().toString());
                }
            }
        }
        if(CollectionUtils.isNotEmpty(deId)){
            insuranceOrderAuthorityMapper.deleteAuthority(deId);
        }
        return true;
    }

    private List<InsuranceOrder> importXlsx(HttpServletRequest request,HttpServletResponse response,List<ProjectListVO> list,Sheet sheet,List<InsuranceOrderAuthority> authorityList,AccessToken accessToken,String usname) throws IOException {

        SimpleDateFormat dFormat = new SimpleDateFormat("yyyy-MM");

        SimpleDateFormat dFormat2 = new SimpleDateFormat("yyyy-MM-dd");
        Row row = null;
        Cell cell;

        List<InsuranceOrder> orderList=new ArrayList<>();
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                row = sheet.getRow(j+1);
                if(row==null) {
                    break;
                }
                for (int k = 0; k < sheet.getRow(j).getPhysicalNumberOfCells(); k++) {
                    cell = row.getCell(0);
                    if(cell==null) {
                        break;
                    }
                    cell.setCellType(CellType.STRING);
                    String regionCode=cell.getStringCellValue();
                    if(StringUtils.isBlank(regionCode)){
                        break;
                    }

                    cell = row.getCell(1);
                    cell.setCellType(CellType.STRING);
                    String regionName=cell.getStringCellValue();
                    if(StringUtils.isBlank(regionName)){
                        throw new ServiceException("第" + (j + 1) + "行" + "区域名称不能为空");
                    }

                    cell = row.getCell(2);
                    String projectCode="";
                    if(cell!=null){
                        cell.setCellType(CellType.STRING);
                         projectCode=cell.getStringCellValue();
                    }

/*                    if(StringUtils.isBlank(projectCode)){
                        throw new ServiceException("第" + (j + 1) + "行" + "项目编号不能为空");
                    }*/

                    cell = row.getCell(3);
                    String projectName="";
                    if(cell!=null){
                        cell.setCellType(CellType.STRING);
                         projectName=cell.getStringCellValue();
                    }

/*                    if(StringUtils.isBlank(projectName)){
                        throw new ServiceException("第" + (j + 1) + "行" + "项目名称不能为空");
                    }*/

                    cell = row.getCell(4);
                    cell.setCellType(CellType.STRING);
                    String monthDate=cell.getStringCellValue();
                    if(StringUtils.isBlank(monthDate)){
                        throw new ServiceException("第" + (j + 1) + "行" + "月份不能为空");
                    }
                    Date date=null;
                    try {
                         date= dFormat.parse(monthDate);
                        monthDate=dFormat.format(date);
                    } catch (ParseException e) {
                        throw new ServiceException("第" + (j + 1) + "行" + "月份请按照yyyy-MM格式");
                    }

                    cell = row.getCell(5);
                    cell.setCellType(CellType.STRING);
                    String insuranceNumber=cell.getStringCellValue();
                    if(StringUtils.isBlank(insuranceNumber)){
                        throw new ServiceException("第" + (j + 1) + "行" + "保险单号不能为空");
                    }

                    cell = row.getCell(6);
                    cell.setCellType(CellType.STRING);
                    String productName=cell.getStringCellValue();
                    if(StringUtils.isBlank(productName)){
                        throw new ServiceException("第" + (j + 1) + "行" + "产品名称不能为空");
                    }

                    cell = row.getCell(7);
                    cell.setCellType(CellType.STRING);
                    String agentName=cell.getStringCellValue();
                    if(StringUtils.isBlank(agentName)){
                        throw new ServiceException("第" + (j + 1) + "行" + "代理人姓名不能为空");
                    }

                    cell = row.getCell(8);
                    cell.setCellType(CellType.STRING);
                    String post=cell.getStringCellValue();
                    if(StringUtils.isBlank(post)){
                        throw new ServiceException("第" + (j + 1) + "行" + "业务人员岗位不能为空");
                    }

                    cell = row.getCell(9);
                    cell.setCellType(CellType.STRING);
                    String policyholder=cell.getStringCellValue();
                    if(StringUtils.isBlank(policyholder)){
                        throw new ServiceException("第" + (j + 1) + "行" + "投保人不能为空");
                    }

                    cell = row.getCell(10);
                    cell.setCellType(CellType.STRING);
                    String insured=cell.getStringCellValue();
                    if(StringUtils.isBlank(insured)){
                        throw new ServiceException("第" + (j + 1) + "行" + "被保人不能为空");
                    }

                    cell = row.getCell(11);
                    cell.setCellType(CellType.STRING);
                    String premium=cell.getStringCellValue();
                    if(StringUtils.isBlank(premium)){
                        throw new ServiceException("第" + (j + 1) + "行" + "保费不能为空");
                    }

                    cell = row.getCell(12);
                    cell.setCellType(CellType.STRING);
                    String transactionDate=cell.getStringCellValue();
                    if(StringUtils.isBlank(transactionDate)){
                        throw new ServiceException("第" + (j + 1) + "行" + "成交日期不能为空");
                    }

                    Date date1=null;
                    try {
                        date1= dFormat2.parse(transactionDate);
                        transactionDate=dFormat2.format(date1);
                    } catch (ParseException e) {
                        throw new ServiceException("第" + (j + 1) + "行" + "成交日期请按照yyyy-MM-dd格式");
                    }

                    cell = row.getCell(13);
                    cell.setCellType(CellType.STRING);
                    String brokerageFees=cell.getStringCellValue();
                    if(StringUtils.isBlank(brokerageFees)){
                        throw new ServiceException("第" + (j + 1) + "行" + "经纪费收入不能为空");
                    }
                    InsuranceOrder insu=new InsuranceOrder();
                    insu.setRegionCode(regionCode);
                    insu.setRegionName(regionName);
                    insu.setProjectCode(projectCode);
                    insu.setProjectName(projectName);
                    insu.setMonthDate(monthDate);
                    insu.setInsuranceNumber(insuranceNumber);
                    insu.setProductName(productName);
                    insu.setAgentName(agentName);
                    insu.setPost(post);
                    insu.setPolicyholder(policyholder);
                    insu.setInsured(insured);
                    insu.setPremium(premium);
                    insu.setTransactionDate(transactionDate);
                    insu.setBrokerageFees(brokerageFees);
                    insu.setCreateBy(accessToken.getUserCode());
                    insu.setCreateName(usname);
                    insu.setEnable(1);
                    orderList.add(insu);
                    k+=13;
                }
            }
            Set<InsuranceOrder> set=new LinkedHashSet<>(orderList);
            List<InsuranceOrder> orList=new ArrayList<>(set);

           List<InsuranceOrder> correctList=new ArrayList<>();
           List<InsuranceOrder> errorList=new ArrayList<>();
         InsuranceOrder ordeeor=null;

        List<String> commids=new ArrayList<>();
        List<String> orgCos=new ArrayList<>();
        for (int i = 0; i <authorityList.size() ; i++) {
            if(authorityList.get(i).getOrganType().equals(0)){
                commids.add(authorityList.get(i).getCommCode());
            }else if(authorityList.get(i).getOrganType().equals(1)){
                orgCos.add(authorityList.get(i).getOrgCode());
            }
        }

            for (int i = 0; i <orList.size() ; i++) {
                List<ProjectListVO> projtLi=null;
                if(CollectionUtils.isNotEmpty(orgCos)&&CollectionUtils.isNotEmpty(commids)){
                    projtLi=tbSysOrganMapper.findOrgCoProject(orList.get(i).getRegionCode(),null,orderList.get(i).getProjectCode(),commids,orgCos);
                }else if(CollectionUtils.isNotEmpty(orgCos)){
                    projtLi=tbSysOrganMapper.findOrgCot(orList.get(i).getRegionCode(),null,orderList.get(i).getProjectCode(),orgCos);
                }else if(CollectionUtils.isNotEmpty(commids)){
                    projtLi=tbSysOrganMapper.findCommPro(orList.get(i).getRegionCode(),null,orderList.get(i).getProjectCode(),commids);
                }
                if(CollectionUtils.isNotEmpty(projtLi)){
                    InsuranceOrder order=new InsuranceOrder();
                    order.setRegionCode(orList.get(i).getRegionCode());
                    order.setRegionName(projtLi.get(0).getOrganName());
                    order.setProjectCode(orList.get(i).getProjectCode());
                    order.setProjectName(orList.get(i).getProjectName());
                    order.setMonthDate(orList.get(i).getMonthDate());
                    order.setInsuranceNumber(orList.get(i).getInsuranceNumber());
                    order.setEnable(1);
                    if (CollectionUtils.isNotEmpty(insuranceOrderMapper.select(order))){
                    ordeeor=new InsuranceOrder();
                    ordeeor=orList.get(i);
                    ordeeor.setErrorMessage("区域，项目，年月，保险单号4个字段数据同时重复，无法导入");
                    errorList.add(ordeeor);

                    }else {
                    orList.get(i).setRegionName(projtLi.get(0).getOrganName());
                    orList.get(i).setProjectName(orList.get(i).getProjectName());
                    correctList.add(orList.get(i));
                   }
                }else {
                    ordeeor=new InsuranceOrder();
                    ordeeor=orList.get(i);
                    ordeeor.setErrorMessage("区域项目权限不足");
                    errorList.add(ordeeor);
                }
            }

            if(CollectionUtils.isNotEmpty(errorList)){
                Set<InsuranceOrder> set1=new HashSet<>(errorList);
                List<InsuranceOrder> errorList1=new ArrayList<>(set1);
                errorList1.addAll(correctList);
                System.out.println(errorList1.toString());
                JSONArray json = new JSONArray();
                for (InsuranceOrder inor:errorList1) {
                    JSONObject jo = new JSONObject();
                    jo.put("regionCode",inor.getRegionCode());
                    jo.put("regionName",inor.getRegionName());
                    jo.put("projectCode",inor.getProjectCode());
                    jo.put("projectName",inor.getProjectName());
                    jo.put("monthDate",inor.getMonthDate());
                    jo.put("insuranceNumber",inor.getInsuranceNumber());
                    jo.put("productName",inor.getProductName());
                    jo.put("agentName",inor.getAgentName());
                    jo.put("post",inor.getPost());
                    jo.put("policyholder",inor.getPolicyholder());
                    jo.put("insured",inor.getInsured());
                    jo.put("premium",inor.getPremium());
                    jo.put("transactionDate",inor.getTransactionDate());
                    jo.put("brokerageFees",inor.getBrokerageFees());
                    jo.put("errorMessage",inor.getErrorMessage());
                    json.add(jo);
                }
                InsuranceOrderTemporary insurTemporary=new InsuranceOrderTemporary();
                insurTemporary.setTemporary(json.toString());
                insurTemporary.setUserCode(accessToken.getUserCode());
                insuranceOrderTemporaryMapper.insert(insurTemporary);
                return errorList1;
            }else {
                for (int i = 0; i <correctList.size() ; i++) {
                    correctList.get(i).setCreateTime(new Date());
                }
                if(CollectionUtils.isNotEmpty(correctList)){
                    insuranceOrderMapper.insertList(correctList);
                    return null;
                }else {
                    throw new ServiceException("数据为空，导入失败");
                }

            }
    }
    private void projectListErrorVO(XSSFSheet sheet,  List<InsuranceOrder> errorList){
        int count = 0;
        int copyRow = 2;

        for (InsuranceOrder livo : errorList) {
            excelCommon.copyRows(sheet, 2, 2, copyRow);

            copyRow++;
            count++;

            XSSFCell cell = null;
            cell = sheet.getRow(count).getCell(0);
            cell.setCellValue(livo.getRegionCode() == null ? "" : livo.getRegionCode());
            cell = sheet.getRow(count).getCell(1);
            cell.setCellValue(livo.getRegionName() == null ? "" : livo.getRegionName());
            cell = sheet.getRow(count).getCell(2);
            cell.setCellValue(livo.getProjectCode() == null ? "" : livo.getProjectCode());
            cell = sheet.getRow(count).getCell(3);
            cell.setCellValue(livo.getProjectName() == null ? "" : livo.getProjectName());
            cell = sheet.getRow(count).getCell(4);
            cell.setCellValue(livo.getMonthDate() == null ? "" : livo.getMonthDate());
            cell = sheet.getRow(count).getCell(5);
            cell.setCellValue(livo.getInsuranceNumber() == null ? "" : livo.getInsuranceNumber());
            cell = sheet.getRow(count).getCell(6);
            cell.setCellValue(livo.getProductName() == null ? "" : livo.getProductName());
            cell = sheet.getRow(count).getCell(7);
            cell.setCellValue(livo.getAgentName() == null ? "" : livo.getAgentName());
            cell = sheet.getRow(count).getCell(8);
            cell.setCellValue(livo.getPost() == null ? "" : livo.getPost());
            cell = sheet.getRow(count).getCell(9);
            cell.setCellValue(livo.getPolicyholder() == null ? "" : livo.getPolicyholder());
            cell = sheet.getRow(count).getCell(10);
            cell.setCellValue(livo.getInsured() == null ? "" : livo.getInsured());
            cell = sheet.getRow(count).getCell(11);
            cell.setCellValue(livo.getPremium() == null ? "" : livo.getPremium());
            cell = sheet.getRow(count).getCell(12);
            cell.setCellValue(livo.getTransactionDate() == null ? "" : livo.getTransactionDate());
            cell = sheet.getRow(count).getCell(13);
            cell.setCellValue(livo.getBrokerageFees() == null ? "" : livo.getBrokerageFees());
            cell = sheet.getRow(count).getCell(14);
            cell.setCellValue(livo.getErrorMessage() == null ? "" : livo.getErrorMessage());
        }
    }


    @Override
    public void erpjiashuju() {
        List<TbSysUser> erpjiashuju=tbSysUserMapper.erpjiashuju();
        for (int i = 0; i <erpjiashuju.size() ; i++) {
            List<ProjectListVO>  findOrgCoProject=tbSysOrganMapper.findOrgCoProject(null,erpjiashuju.get(i).getUserCode(),null,null,null);
            for (int j = 0; j <findOrgCoProject.size() ; j++) {
                List<InsuranceOrderAuthority> list=new ArrayList<>();
                InsuranceOrderAuthority authority=new InsuranceOrderAuthority();
                authority.setOrganType(0);
                authority.setOrgCode(findOrgCoProject.get(j).getOrganCode());
                authority.setCommCode(findOrgCoProject.get(j).getCommId());
                authority.setUserCode(erpjiashuju.get(i).getUserCode());
                authority.setEnable(1);
                authority.setCreateTime(new Date());
                insuranceOrderAuthorityMapper.insertStall(authority);
            }
        }

    }

}
