package com.bcxin.ins.service.preservation.impl;
import java.util.Date;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.bcxin.ins.dto.Result;
import com.bcxin.ins.service.order.ComTaskResidualAPIService;
import com.bcxin.ins.service.order.InsInsuranceSlipAPIService;
import com.bcxin.ins.service.order.InsRoleInpolicyAPIService;
import com.bcxin.ins.service.order.PolicyService;
import com.bcxin.ins.dao.preservation.InsPreservationResultSetAPIDao;
import com.bcxin.ins.service.preservation.InsPreservationDetailAPIService;
import com.bcxin.ins.service.preservation.InsPreservationRecordAPIService;
import com.bcxin.ins.service.preservation.InsPreservationResultSetAPIService;
import com.bcxin.ins.core.util.DOM;
import com.bcxin.ins.entity.policy_core.InsPreservationDetail;
import com.bcxin.ins.entity.policy_core.InsPreservationRecord;
import com.bcxin.ins.entity.policy_core.InsPreservationResultSet;
import com.bcxin.ins.entity.policy_core.InsRoleInpolicy;
import com.bcxin.ins.spring.util.JedisUtils;
import com.bcxin.ins.util.*;
import com.bcxin.ins.vo.*;
import com.bcxin.ins.vo.ConstProp;
import com.bcxin.ins.vo.excel.InsPerExcelVo;
import com.bcxin.ins.vo.excel.ResultSetExcelVo;
import com.bcxin.mybatisplus.plugins.Page;
import com.bcxin.mybatisplus.service.impl.ServiceImpl;
import com.bcxin.mybatisplus.toolkit.IdWorker;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.xiaoleilu.hutool.poi.excel.ExcelReader;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
@Transactional
public class InsPreservationResultSetAPIServiceImpl extends ServiceImpl<InsPreservationResultSetAPIDao, InsPreservationResultSet> implements InsPreservationResultSetAPIService {

    private Logger log =  LoggerFactory.getLogger(InsPreservationResultSetAPIServiceImpl.class);
    @Autowired
    private InsPreservationResultSetAPIDao dao;
    @Autowired
    private InsPreservationDetailAPIService insPreservationDetailAPIService;
    @Autowired
    private InsPreservationRecordAPIService insPreservationRecordAPIService;
    @Autowired
    private InsRoleInpolicyAPIService insRoleInpolicyAPIService;
    @Autowired
    private PolicyService policyService;
    @Autowired
    private ComTaskResidualAPIService comTaskResidualAPIService;
    @Autowired
    private InsInsuranceSlipAPIService insInsuranceSlipAPIService;

    /**
     * <b>根据保全申请单id查询有效被保险人数据集并根据关键字进行分页 </b>
     *
     * @param page 分页
     * @param preservationId 保全申请单id
     * @param keyword 关键字
     * @param status label选项卡的状态 0，有效人员；1，失效人员
     * @param start_time 批改时间开始
     * @param end_time 批改时间截止
     * @return
     * @author ZXF
     * @date 2017年8月3日 下午17:19:24
     * @注意事项 </b>
     * <b>
     */
    @Override
    public List<InsPreservationResultSet> findInsPreservationResultSetByKeyword(DwzPage page, Long preservationId, String keyword,String status, String start_time, String end_time){
        List<InsPreservationResultSet> list = null;
        if(page != null){
            Page<InsPreservationResultSet> pageHelper = new Page(page.getPageNum(), page.getNumPerPage());
            list = dao.findResultSetByKeyword( pageHelper, preservationId, keyword, status, start_time, end_time);
            page.setTotalCount(new Long(pageHelper.getTotal()).intValue());
        }else{
            list = dao.findResultSetByKeyword( preservationId, keyword, status, start_time, end_time);
        }
        return list;
    }

    /**
     * <b>页面点击保全时调用，通过订单id及条件赛选出当前有效被保险人结果集，分2种情况筛选被保险人：1，初次保全（查InsRoleInpolicy），2多次保全（查最后保全结果集） </b>
     *
     * @param page 分页
     * @param policyId 订单id
     * @param keyword 关键字
     * @param status 是否有效（用于查询区别有效的人员跟失效的人员） 0，有效人员；1，失效人员
     * @param start_time 批改时间开始
     * @param end_time 批改时间截止
     * @return
     * @author ZXF
     * @date 2017年8月3日 下午17:19:24
     * @注意事项 </b>
     * <b>
     */
    @Override
    public List<InsPreservationResultSet> initFindInsPreservationResultSetByKeyword(DwzPage page, Long policyId, String keyword,String status, String start_time, String end_time){

        List<InsPreservationResultSet> list = null;
        if(page != null){
            Page<InsPreservationResultSet> pageHelper = new Page(page.getPageNum(), page.getNumPerPage());
            list = dao.findResultSetByKeyword( pageHelper, policyId, keyword, status, start_time, end_time);
            page.setTotalCount(new Long(pageHelper.getTotal()).intValue());
        }else{
            list = dao.findResultSetByKeyword( policyId, keyword, status, start_time, end_time);
        }
        return list;
        /*InsPreservationRecord insPreservationRecord = insPreservationRecordAPIService.findInsPreservationRecordByPolicyID(policyId);
        log.info("保全结果集："+insPreservationRecord);
        if(insPreservationRecord != null){//如果能找到最后版本的保全申请单，就直接拿结果集
            log.info("保全结果集(insPreservationRecord.id)："+insPreservationRecord.getIns_preservation_record_id());
            List<InsPreservationResultSet> iprsList = findInsPreservationResultSetByKeyword(page, insPreservationRecord.getIns_preservation_record_id(), keyword, status, start_time, end_time);
            log.info("保全结果集(List<InsPreservationResultSet>)："+iprsList);
            return iprsList;
        }else{//如果没找到，就找被保险人记录并转成InsPreservationResultSet，同时参与条件跟分页筛选
            List<InsPreservationResultSet> list = new ArrayList<InsPreservationResultSet>();
            if(ConstProp.DIGIT_ZERO.equals(status)){//初始时查找有效人员才去被保险人表查（初始时不存在失效人员）
                List<InsRoleInpolicy> roles = insRoleInpolicyAPIService.findInsRoleInpolicyByPolicyAndKeywordForPage(page,policyId,keyword);
                InsPreservationResultSet iprs = null;
                for(InsRoleInpolicy role : roles){
                    iprs = new InsPreservationResultSet();
                    iprs.setName(role.getName_cn());
                    iprs.setId_card(role.getOrganization_code());
                    iprs.setId_type(role.getId_type());
                    iprs.setBelong_to_id(role.getIns_role_inpolicy_id());
                    iprs.setBelong_to_type(ConstProp.ROLE);
                    iprs.setBirthday(role.getBirth_date());
                    iprs.setSex(role.getSex());
                    iprs.setTel(role.getMobile());
                    iprs.setCareer(role.getMajor_group());
                    list.add(iprs);
                    iprs = null;
                }
            }
            return list;
        }*/
    }

    /**
     * <b> 根据订单id查询同投保人的在保人员清单列表 </b>
     * @author ZXF
     * @create 2019/12/24 0024 14:45
     * @version
     * @注意事项 </b>
     */
    @Override
    public List<InsPreservationResultSet> findZBByPolicyId(Long policyId,String productCode){
        if(StringUtils.isNotEmpty(productCode)){
            productCode = productCode.split("-")[0];
        }
        return dao.findZBByPolicyId(policyId,productCode);
    }

    /**
     * <b> 根据身份证查询对应在保人员转InsPreservationDetailVo </b>
     * @author ZXF
     * @create 2019/12/26 0026 15:50
     * @version
     * @注意事项 </b>
     */
    @Override
    public List<InsPreservationDetailVo> findInsPreservationDetailVoByIdCards(Long policyId, String idCards){
        String[] arr = idCards.split(ConstProp.COMMA);
        return dao.findInsPreservationDetailVoByIdCards(policyId,arr);
    }

    @Override
    public List<InsPreservationDetailVo> findInsPreservationDetailVoByImportBatchId(Long policyId, String importBatchId){
        return dao.findInsPreservationDetailVoByImportBatchId(policyId,importBatchId);
    }

    /**
     * <b> 根据订单id查询在保人数 </b>
     * @author ZXF
     * @create 2019/12/24 0024 14:45
     * @version
     * @注意事项 </b>
     */
    @Override
    public Integer findZBNumber(int dqNum, Long policyId){
        return dao.findZBNumber(dqNum, policyId);
    }

    /**
     * 被保险人数据转结果集表数据
     * @param role
     * @param iprsVo
     */
    private void roleInPolicyToResultSet(InsRoleInpolicy role,InsPreservationResultSetVo iprsVo){
        iprsVo.setName(role.getName_cn());
        iprsVo.setId_card(role.getOrganization_code());
        iprsVo.setId_type(role.getId_type());
        iprsVo.setBelong_to_id(String.valueOf(role.getIns_role_inpolicy_id()));
        iprsVo.setBelong_to_type(ConstProp.ROLE);
        iprsVo.setBirthday(role.getBirth_date());
        iprsVo.setSex(role.getSex());
        iprsVo.setTel(role.getMobile());
        iprsVo.setCareer(role.getMajor_group());
    }

    /**
     * 保全人员数据转结果集表数据
     * @param ipd
     * @param iprsVo
     */
    private void preservationDetailToResultSet(InsPreservationDetail ipd, InsPreservationResultSetVo iprsVo){
        try {
            MyConverUtil.map2PO(MyConverUtil.PO2Map(ipd), iprsVo);
            iprsVo.setBelong_to_type(ConstProp.PRESERVE);
            iprsVo.setBelong_to_id(String.valueOf(ipd.getIns_preservation_detail_id()));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据id跟标识查询不同表获取数据再转成集合表数据
     * @param oid
     * @param sign
     */
    @Override
    public InsPreservationResultSetVo getInsPreservationResultSetVoByOidAndSign(String oid, String sign){
        InsPreservationResultSetVo iprsVo = new InsPreservationResultSetVo();
        if("ROLE".equals(sign)){//被保险人的数据
            iprsVo = insRoleInpolicyAPIService.setResultSetVo(Long.parseLong(oid));
        }else if("PRESERVE".equals(sign) || "THIS".equals(sign) || StringUtils.isEmpty(sign)){//保全的数据
            iprsVo = dao.setResultSetVo(Long.parseLong(oid));
        }
        return iprsVo;
    }

    /**
     * <b> 根据订单Id和身份证列查询对应在保人员 </b>
     * @author ZXF
     * @create 2020/06/04 0004 14:26
     * @version
     * @注意事项 </b>
     */
    @Override
    public List<InsPreservationResultSetVo> getInsPreservationResultSetVoByIdCardsAndOrderId(Long policyId,List<String> idCardList){
        return dao.setResultSetVoList(policyId,idCardList);
    }

    /**
     * 根据订单id和身份证查询有效在保人员信息
     * @param policyId 订单id
     * @param id_card 身份证
     * @return true 存在有效人员，false 不存在在保人员
     */
    @Override
    public boolean getEffectivePersonnelBySet(Long policyId,String id_card){
        if(policyId == null || StringUtils.isEmpty(id_card) || dao.getResultSetIdByRecord(policyId,id_card) == null){
            return false;
        }else {
            return true;
        }
    }

    /**
     * excel人员导入处理
     * @param mFile MultipartFile上传文件
     * @return
     * @throws Exception
     */
    @Override
    public JSONObject readIPRSExcel(MultipartFile mFile) throws Exception {
        JSONObject jo = new JSONObject();
        List<InsPreservationResultSetVo> list = null;
        String msg = "";
        int cum = 0;
        String cds = "";
        int career1 = 0,career2 = 0,career3 = 0,career4 = 0;
        String[] arrStr = {"name","id_type","id_card","birthday","sex","tel","career"};
        // Read the Sheet
        list = Lists.newArrayList();
        try {
            InputStream is = mFile.getInputStream();
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                InsPreservationResultSetVo iprsVo = null;
                // Read the Row
                for (int rowNum = 6; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    //excel的行号
                    int hh = rowNum+1;
                    if (hssfRow == null) {
                        continue;
                    }
                    //整条记录为空就跳过
                    if(StringUtils.isEmpty(String.valueOf(hssfRow.getCell(1)).trim())&&
                            StringUtils.isEmpty(String.valueOf(hssfRow.getCell(2)).trim())&&
                            StringUtils.isEmpty(String.valueOf(hssfRow.getCell(3)).trim())&&
                            StringUtils.isEmpty(String.valueOf(hssfRow.getCell(6)).trim())&&
                            StringUtils.isEmpty(String.valueOf(hssfRow.getCell(7)).trim())&&
                            StringUtils.isEmpty(String.valueOf(hssfRow.getCell(8)).trim())){
                        break;
                    }
                    HSSFCell cell = null;
                    iprsVo = new InsPreservationResultSetVo();
                    Map<String,String> map = Maps.newHashMap();
                    String cd = "";
                    for(int i=1;i<8;i++){
                        cell = hssfRow.getCell(i);
                        String _value = getValue(cell).trim();
                        if(StringUtils.isEmpty(_value)){
                            String n = i==1?"姓名"
                                    :i==2?"证件类型"
                                    :i==3?"证件号"
                                    :i==4?"出生日期"
                                    :i==5?"性别"
                                    :i==6?"手机"
                                    :"职业";
                            msg = msg+ "第 "+hh+" 行："+n+"不能为空;";
                            cum = 1;
                            continue;
                        }
                        if("name".equals(arrStr[i-1])){
                            if(StringUtils.isNotEmpty(_value)){
                                _value = _value.replace(" ","");
                            }
                        }else if("id_type".equals(arrStr[i-1])){
                            if("居民身份证".equals(_value)){
                                _value = "0";
                            }else if("护照".equals(_value)){
                                _value = "3";
                            }else if("台胞证".equals(_value)){
                                _value = "4";
                            }else if("回乡证".equals(_value)){
                                _value = "5";
                            }
                        }else if("sex".equals(arrStr[i-1])){
                            if("男".equals(_value)){
                                _value = "1";
                            }else if("女".equals(_value)){
                                _value = "2";
                            }
                        }else if("birthday".equals(arrStr[i-1])){
                            try {
                                if(_value.contains(ConstProp.MINUS)){
                                    DateUtil.parseDate(_value);
                                }else{
                                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                                        Date d = cell.getDateCellValue();
                                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                                        _value = formater.format(d);
                                    }
                                }
                            }catch (Exception e){
                                msg = msg+ "第 "+hh+" 行：出生日期有误;";
                                cum = 1;
                                break;
                            }
                            if(IdNumberValidator.getAgeFromBirthday(_value)>65||IdNumberValidator.getAgeFromBirthday(_value)<16){
                                msg = msg+ "第 "+hh+" 行：人员的年龄超出限制（区间：16-60周岁）;";
                                cum = 1;
                                break;
                            }
                        }else if("id_card".equals(arrStr[i-1])){
                            _value = _value.toUpperCase();
                            if(!IdCardVerify.isValidatedAllIdcard(_value)){
                                msg = msg+ "第 "+hh+" 行：证件号码不是有效证件，请联系客服;";
                                cum = 1;
                                break;
                            }
                            cd = _value;
                        }else if("career".equals(arrStr[i-1])){
                            _value = _value.replace(".0","");
                            if(!"1234".contains(_value)){
                                msg = msg+ "第 "+hh+" 行：职业编码无效;";
                                cum = 1;
                                break;
                            }
                            switch (_value) {
                                case "1":
                                    career1 = career1+1;
                                    break;
                                case "2":
                                    career2 = career2+1;
                                    break;
                                case "3":
                                    career3 = career3+1;
                                    break;
                                case "4":
                                    career4 = career4+1;
                                    break;
                                default:
                                    break;
                            }
                        }else if("tel".equals(arrStr[i-1])){
                            if(_value.contains("E")){
                                try {
                                    DecimalFormat df = new DecimalFormat(ConstProp.POUND_SIGN);
                                    _value = df.format(cell.getNumericCellValue());
                                }catch (Exception e){
                                    msg = msg+ "第 "+hh+" 行：手机号码格式有误;";
                                    cum = 1;
                                    break;
                                }
                            }
                            if(!ValidatorUtil.validatePhone(_value)){
                                msg = msg+ "第 "+hh+" 行：手机号码格式有误;";
                                cum = 1;
                                break;
                            }
                        }
                        map.put(arrStr[i-1],_value);
                    }
                    if(StringUtils.isNotEmpty(cd)&&cds.contains(cd)){
                        msg = msg+ "第 "+hh+" 行：该证件号已在本清单中，请勿重复录入;";
                        cum = 1;
                    }
                    cds = cds + cd + ConstProp.SEMICOLON;
                    if(iprsVo != null&&map.size()>0){
                        MyConverUtil.map2PO(map, iprsVo);
                        list.add(iprsVo);
                        iprsVo = null;
                    }
                }
            }
        } catch (Exception e){
            cum = 1;
            msg = "Excel清单模板文件数据解析失败，请使用标准的Excel清单模板上传！\n" +
                    "可能原因：\n1.模板文件头部内容被删除或整体样式被改变；\n2.直接它处复制的人员信息附带格式。";
        }
        jo.put("ret",cum==1?ConstProp.CODE_FAILURE:ConstProp.CODE_SUCCESS);
        jo.put("msg",msg);
        jo.put("list",list);
        jo.put("career1",career1);
        jo.put("career2",career2);
        jo.put("career3",career3);
        jo.put("career4",career4);
        return jo;
    }

    private static String getValue(HSSFCell hssfRow) {
        if (hssfRow.getCellType() == hssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfRow.getBooleanCellValue());
        } else if (hssfRow.getCellType() == hssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfRow.getNumericCellValue());
        } else {
            return String.valueOf(hssfRow.getStringCellValue());
        }
    }

    /**
     * <b>
     *     根据产品标识查询已承保但未有保全结果集记录的订单id(目前产品标识仅限团意险和雇主责任险需要做保全)
     *     遍历订单id,查询对应保险人设置到结果集中
     * </b>
     * @author ZXF
     * @create 2018/06/05 0005 13:43
     * @version
     * @注意事项 </b>
     */
    @Override
    public void initAllOrderToResultSet(String product_code){
        List<String> list = insInsuranceSlipAPIService.selectOrderIdByProductCode(product_code);
        if(list != null){
            for(String oid : list){
                initResultSetByOrderId(Long.parseLong(oid));
            }
        }
    }

    /**
     * <b> 针对订单初始化结果集（所有需要做保全人员）
     *      注1：原来结果集定义是每次保全有效后都相应生成一个版本的有效人员供下次保全做关联
     *      注2：现在改成结果集一个订单只有一份，不管做多少次保全都在这份结果集上面新增记录或变更状态，不对此做删除动作
     *      当前方法为【注2】实现的第一个步骤，每个有需要做保全的订单都需要在承保后将在保人员搬到结果集表生成最初的结果集，之后的所有
     * 保全操作都将依于这份结果集人员列表做增减替换操作
     * </b>
     * @param oid 订单id
     * @author ZXF
     * @create 2018/05/30 0030 13:50
     * @version
     * @注意事项 </b>
     */
    @Override
    public void initResultSetByOrderId(Long oid){
        OrderFormVo dto = policyService.accordingToOrderIDToGetPolicyDto(oid);
        if(StringUtils.isEmpty(dto.getProduct_code())){
            return;
        }
        List<InsPreservationResultSet> iprsList = new ArrayList<InsPreservationResultSet>();
        InsPreservationResultSet iprs = null;
        if(dto.getProduct_code().contains(ConstProp.TYX)){//搬团意险人员
            GMRPolicyVo vo = policyService.accordingToOrderIDToGetGMRPolicyVo(oid);
            for(RoleSubjectVo rvo : vo.getRoleSubjectList()){
                if(!ConstProp.DIGIT_ONE.equals(rvo.getKind())){
                    iprs = new InsPreservationResultSet();
                    setResultSetByRole(iprs, rvo, oid);
                    iprs.setIns_preservation_result_set_id(IdWorker.getId());
                    iprsList.add(iprs);
                    iprs = null;
                }
            }
        }else if(dto.getProduct_code().contains(ConstProp.GZZRX)){//搬雇主责任险人员
            SpecialHirelingVo vo = policyService.accordingToOrderIDToGetSpecialHirelingVo(oid);
            for(HirelingVo hvo : vo.getHirelingVoList()){
                iprs = new InsPreservationResultSet();
                setResultSetByHireling(iprs, hvo, oid);
                iprs.setIns_preservation_result_set_id(IdWorker.getId());
                iprsList.add(iprs);
                iprs = null;
            }
        }
        if(iprsList.size()>0){
            dao.batchInsert(iprsList);
        }
    }

    private void setResultSetByRole(InsPreservationResultSet iprs, RoleSubjectVo rvo, Long oid){
        iprs.setName(rvo.getName_cn());
        iprs.setId_card(rvo.getOrganization_code());
        iprs.setId_type(rvo.getId_type());
        iprs.setBirthday(rvo.getBirth_date());
        iprs.setSex(rvo.getSex());
        iprs.setTel(rvo.getMobile());
        iprs.setCareer(rvo.getMajor_group());
        iprs.setIns_insurance_slip_id(oid);
        iprs.setStatus(0);
        iprs.setBelong_to_type("ROLE");
        iprs.setRevise_time(new Date());
    }

    private void setResultSetByHireling(InsPreservationResultSet iprs, HirelingVo hvo, Long oid){
        iprs.setName(hvo.getName());
        iprs.setId_card(hvo.getId_card());
        iprs.setId_type(hvo.getId_type());
        iprs.setBirthday(hvo.getBirth_date());
        iprs.setSex(hvo.getSex());
        iprs.setTel(hvo.getMobile());
        iprs.setCareer(hvo.getCareer());
        iprs.setIns_insurance_slip_id(oid);
        iprs.setStatus(0);//有效
        iprs.setBelong_to_type("HIRE");//来源订单
        iprs.setRevise_time(new Date());
    }

    /**
     * <b>
     *     背景：前台，部分产品线上做保全所以直接前台更新结果集和保全缴费
     *     将保全人员数据添加到结果集，或插入数据或变更历史数据状态
     * </b>
     * @param record_id 保全单id
     * @param revise_type 保全类型
     * @author ZXF
     * @create 2018/05/31 0031 14:00
     * @version
     * @注意事项 </b>
     */
    @Override
    public boolean pushResultSetByRecord(Long record_id, String revise_type) {
        try {
            int status = 0;//0 有效人员 1  失效人员
            Date date = new Date();
            Map<Object, Object> params = Maps.newHashMap();
            params.put("id", record_id);
            List<Map<Object, Object>> l = insPreservationDetailAPIService.queryDetail(params);//查询出该保全的保全人员列表
            InsPreservationRecord record = insPreservationRecordAPIService.selectById(record_id);
            List<InsPreservationResultSet> l2 = Lists.newArrayList();//要新增的人员
            List<Long> b1 = Lists.newArrayList();//做减员的id
            if("1".equals(revise_type)){
                for (Map<Object, Object> m : l) {//新增的人员
                    InsPreservationResultSet iprs = (InsPreservationResultSet) DOM.mtd(m, new InsPreservationResultSet());
                    iprs.setRevise_time(date);
                    iprs.setIns_preservation_result_set_id(IdWorker.getId());
                    iprs.setStatus(status);
                    iprs.setIns_insurance_slip_id(record.getIns_insurance_slip_id());
                    iprs.setBelong_to_type(ConstProp.PRESERVE);//更新Belong类型
                    l2.add(iprs);
                }
            }else if("2".equals(revise_type)){//减员
                for (Map<Object, Object> m : l) {//减员的人员
                    Long belong_to_id = (Long)m.get("belong_to_id");
                    if(belong_to_id==null){
                        belong_to_id = dao.getResultSetIdByRecord(record.getIns_insurance_slip_id(),String.valueOf(m.get("id_card")));
                    }
                    if(belong_to_id==null){
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return false;
                    }
                    b1.add(belong_to_id);
                }
            }else if("3".equals(revise_type)){//替换
                for (Map<Object, Object> m : l) {//替换的人员
                    InsPreservationResultSet iprs = (InsPreservationResultSet) DOM.mtd(m, new InsPreservationResultSet());
                    iprs.setRevise_time(date);
                    iprs.setIns_preservation_result_set_id(IdWorker.getId());
                    iprs.setStatus(status);
                    iprs.setIns_insurance_slip_id(record.getIns_insurance_slip_id());
                    iprs.setBelong_to_type(ConstProp.PRESERVE);//更新Belong类型
                    iprs.setBelong_to_id(null);
                    l2.add(iprs);
                    b1.add((Long)m.get("belong_to_id"));
                }
            }

            if(l2.size()>0) {//增员人员批量插入
                dao.batchInsert(l2);
            }
            if(b1.size()>0) {//减员状态批量变更
                Long[] longs = new Long[b1.size()];
                Long[] _l = b1.toArray(longs);
                int count = dao.batchUpdateStatus(_l, 1,revise_type, DateUtil.getCurrentDate_1());
                if (count==0) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return false;
                }
            }
            record.setRelease_time(date);//设置版本时间
            insPreservationRecordAPIService.updateById(record);
            //更新release时间  逻辑先找结果集再更新
            return true;
        }catch (NullPointerException nup){
            log.error("更新结果集业务，事务发现异常，回滚数据", nup);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return false;
        } catch(Exception e) {
            log.error("更新结果集业务，事务发现异常，回滚数据", e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return false;
        }
    }

    /**
     * <b>
     *     帮助完成pushResultSetByRecord业务，如果该业务未成功就插入一条未完成任务
     * </b>
     * @author ZXF
     * @create 2018/05/31 0031 16:28
     * @version
     * @注意事项 </b>
     */
    @Override
    public void updateResultSet(Long record_id, String revise_type){
        if(!pushResultSetByRecord(record_id, revise_type)){
            //如果返回false就向ComTaskResidual插入一条任务
            comTaskResidualAPIService.saveComTaskResidual("pushResultSetByRecord","更新结果集业务","com.bcxin.ins.service.preservation.InsPreservationResultSetAPIService",2,"Long,String",record_id+","+revise_type);
        }
    }

    /**
     * <b> 根据订单id和身份证查询结果集 </b>
     * @author ZXF
     * @create 2019/09/12 0012 10:55
     * @version
     * @注意事项 </b>
     */
    @Override
    public InsPreservationResultSet getResultSetBypolicyIdAndCardNo(String id_card,Long order_id){
        if(StringUtils.isEmpty(id_card) || order_id == null){
            return null;
        }
        return dao.getResultSetBypolicyIdAndCardNo(order_id,id_card);
    }
    /**
     * <b> 根据订单id和身份证查询结果集id </b>
     * @author ZXF
     * @create 2019/09/12 0012 10:55
     * @version
     * @注意事项 </b>
     */
    @Override
    public Long getIdByIdCardAndOrderId(String id_card,Long order_id){
        if(StringUtils.isEmpty(id_card) || order_id == null){
            return null;
        }
        return dao.getResultSetIdByRecord(order_id,id_card);
    }
    /**
     * <b> 根据resultid查询投保生效日期 </b>
     * @author ZXF
     * @create 2019/09/12 0012 10:55
     * @version
     * @注意事项 </b>
     */
    @Override
    public String getBusinessInceptionDateByBusinessId(String businessId){
        if(StringUtils.isEmpty(businessId)){
            return "";
        }
        return dao.getBusinessInceptionDateByBusinessId(businessId);
    }

    /**
     * <b> 根据给定的身份证检索是否存在已投保，如果存在返回对应身份证 </b>
     * @param appName 投保人名称
     * @param code 产品标识 ：GZZRX
     * @author ZXF
     * @create 2019/09/26 0026 15:00
     * @version
     * @注意事项 </b>
     */
    @Override
    public String findDuplicateIdCardNo(String appName,String code,String[] list){
        //设置保单到期天数区间内不参与检索
        return dao.findDuplicateIdCardNo(GlobalResources.POLICY_END_DAY,appName,code,list);
    }

    /**
     * <b> 根据给定的身份证检索保单对应不在保的人员 </b>
     * @param orderId 订单Id
     * @author ZXF
     * @create 2019/09/26 0026 15:00
     * @version
     * @注意事项 </b>
     */
    @Override
    public String findNotUnderWarrantyIdCardNo(String orderId,String[] list){
        return dao.findNotUnderWarrantyIdCardNo(orderId,list);
    }

    /**
     * <b> saas身份证查询对应在保人员信息 </b>
     * @author ZXF
     * @create 2020/01/06 0006 14:29
     * @version
     * @注意事项 </b>
     */
    @Override
    public List<Map> sassFindPerNameByIdCard(String[] list){
        return dao.sassFindPerNameByIdCard(list);
    }

    /**
     * <b> 根据在保人员id统计职业数量 </b>
     * @author ZXF
     * @create 2020/02/06 0006 13:58
     * @version
     * @注意事项 </b>
     */
    @Override
    public Map<String,Integer> countCareer(String[] list){
        return dao.countCareer(list);
    }

    /**
     * <b> 获取保单相关费用等信息 </b>
     * @author ZXF
     * @create 2019/11/05 0005 10:52
     * @version
     * @注意事项 </b>
     */
    @Override
    public Map<String, String> getCostByOrderId(int lastDay, int recordCount,Long oid){
        return dao.getCostByOrderId(lastDay,recordCount,oid);
    }

    /**
     * <b> 在保人员导出 </b>
     * @author ZXF
     * @create 2020/01/19 0019 16:44
     * @version
     * @注意事项 </b>
     */
    @Override
    public void downResultSet(Long oid, HttpServletResponse response) {
        OrderFormVo orderVo = policyService.accordingToOrderIDToGetPolicyDto(oid);
        com.bcxin.ins.util.excel.ExcelUtil<ResultSetExcelVo> util = new com.bcxin.ins.util.excel.ExcelUtil(ResultSetExcelVo.class);
        response.setContentType("application/vnd.ms-excel");
        try {
            response.setHeader("Content-Disposition", "attachment;fileName=" + new String((orderVo.getExternal_reference()+"-在保人员.xls").getBytes("gb2312"), "ISO8859-1"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        try(OutputStream os = response.getOutputStream();) {

            List<ResultSetExcelVo> voList = dao.queryResultSetVo(oid);
            util.exportExcel(voList, orderVo.getExternal_reference()+"在保人员", 65535, os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * <b> 批量人员导入处理(减员专用) </b>
     * @param importBatchId 批次Id：将获取当前登录的perId当做批次Id,这样每次导入就可以将对应批次在临时表的数据删除掉
     * @author ZXF
     * @create 2020/01/15 0015 15:58
     * @version
     * @注意事项 </b>
     */
    @Override
    public Result minusBatchUploadInsPer(Long importBatchId, MultipartFile file) throws Exception {
        List<Map<String, Object>> excellist;
        try (InputStream in = file.getInputStream()){
            /*读取excel，支持*/
            ExcelReader reader = com.xiaoleilu.hutool.poi.excel.ExcelUtil.getReader(in);
            reader.isIgnoreEmptyRow();
            /*从第三行开始读取，因为第三行为标题行*/
            excellist = reader.read(2, 6, 2147483647);
        }catch (Exception e){
            return Result.fail("Excel模板文件异常！");
        }
        /* 得到有效数据行并完成key、value的转换,初步校验数据 */
        excellist = screenValidData(excellist);
        if(excellist.size()==0){
            return Result.fail("Excel文件无有效数据");
        }
        //清除批次id对应历史数据
        dao.deleteTempBatchInsPerImport(importBatchId);
        //存入临时表
        dao.saveTempBatchInsPerImport(excellist,importBatchId);
        //校验导入的人员是否存在人事的组织架构里
//		comOnlineInsPerMapper.checkIdCardNoInPerBaseInfo(importBatchId, comId);
        //校验数据（身份证重复）
        dao.checkIdCardNoRepeat(importBatchId);
        //校验数据（不在企业下的在保人员中）
        dao.checkIsZb(importBatchId);
        //查询信息错误条数
        Integer errCount = dao.countErrOrSuccessTempBatchInsPerImport("0",importBatchId);
        //查询信息正确条数
        Integer successCount = dao.countErrOrSuccessTempBatchInsPerImport("1",importBatchId);
        //根据批次Id和企业Id查询该批次人员对应的保单信息(对应批量导入减员功能返回的有效结果集)
        List<Map<String, String>> mapList = dao.findValidMinusInsByImportBatchId(importBatchId);
        Map<String,Object> param = Maps.newHashMap();
        param.put("importBatchId",String.valueOf(importBatchId));
        param.put("errCount",String.valueOf(errCount));
        param.put("successCount",String.valueOf(successCount));
        param.put("minusInsList",mapList);
        JedisUtils.setObject(ConstProp.REDIS_HEAD_POLICYS+importBatchId,mapList,ConstProp.ONE_HOURS_TIMEOUT_SECONDS);
        return Result.success(Result.SUCCESS_MSG,param);
    }

    /**
     * <b> 筛选组装有效数据 </b>
     * @author ZXF
     * @create 2020/01/15 0015 20:41
     * @version
     * @注意事项 </b>
     */
    private List<Map<String, Object>> screenValidData(List<Map<String, Object>> excellist){
        log.info("获取到的文件信息" + JSON.toJSON(excellist));
        String[] keys = {"name","idCardNo","birthday","idCardType","perType","sex","mobilePhone","number","isSuccess","errorMsg"};
        Map<String, Object> newMap = null;
        List<Map<String, Object>> mlist = Lists.newArrayList();
        int isNull;
        for(Map<String, Object> map : excellist){
            if(StringUtils.isEmpty(String.valueOf(map.get("姓名")))
                    &&StringUtils.isEmpty(String.valueOf(map.get("职业")))
                    &&StringUtils.isEmpty(String.valueOf(map.get("证件号码")))
                    &&StringUtils.isEmpty(String.valueOf(map.get("手机号")))
                    &&StringUtils.isEmpty(String.valueOf(map.get("出生日期")))
                    &&StringUtils.isEmpty(String.valueOf(map.get("证件类型")))
                    &&StringUtils.isEmpty(String.valueOf(map.get("性别")))){
                continue;
            }
            newMap = Maps.newHashMap();
            isNull = 0;
            for(Map.Entry<String, Object> entry : map.entrySet()){
                String val = String.valueOf(entry.getValue());
                switch (entry.getKey()) {
                    case "姓名":
                        newMap.put(keys[0],val);
                        break;
                    case "证件号码":
                        newMap.put(keys[1],val);
                        break;
                    case "出生日期":
                        if(StringUtils.isNotEmpty(val)){
                            val = val.replace(" 00:00:00","");
                        }
                        newMap.put(keys[2],val);
                        break;
                    case "证件类型":
                        if(StringUtils.isNotEmpty(val)){
                            val = "居民身份证".equals(val) ? "0":"护照".equals(val) ? "3":"台胞证".equals(val) ? "4":"回乡证".equals(val) ? "5":"";
                        }
                        newMap.put(keys[3],val);
                        break;
                    case "职业":
                        newMap.put(keys[4],val);
                        break;
                    case "性别":
                        if(StringUtils.isNotEmpty(val)){
                            val = "男".equals(val) ? "1":"女".equals(val) ? "2":"";
                        }
                        newMap.put(keys[5],val);
                        break;
                    case "手机号":
                        newMap.put(keys[6],val);
                        break;
                    case "序号":
                        newMap.put(keys[7],val);
                        break;
                    default:
                        break;
                }
                if(StringUtils.isEmpty(val)&&StringUtils.isNotEmpty(entry.getKey())){
                    isNull = 1;
                }
            }
            if(isNull == 1){
                newMap.put(keys[8],"0");
                newMap.put(keys[9],"存在空值或数据不合法");
            }else{
                String errMsg = "";
                //检查身份证
                if("0".equals(String.valueOf(newMap.get(keys[1])))){//如果是身份证，校验年龄
                    if (!IdCardVerify.isValidatedAllIdcard(String.valueOf(newMap.get(keys[1])))){
                        errMsg = "身份证号码错误；";
                    }
                }
                try {
                    int age = DateUtil.getAge(String.valueOf(newMap.get(keys[2])));
                    if (age<16||age>65){
                        errMsg = errMsg + "年龄不符合投保条件；";
                    }
                }catch (Exception e){
                    errMsg = errMsg + "出生日期格式不合法；";
                }
                //职业
                String car = String.valueOf(newMap.get(keys[4]));
                if(car.length()>1||!"1234".contains(car)){
                    errMsg = errMsg+"职业编码错误；";
                }
                //检查手机
                if(!validateMobile(String.valueOf(newMap.get(keys[6])))){
                    errMsg = errMsg+"手机号码错误；";
                }
                if(StringUtils.isNotEmpty(errMsg)){
                    newMap.put(keys[8],"0");
                    newMap.put(keys[9],errMsg);
                }else{
                    newMap.put(keys[8],"1");
                    newMap.put(keys[9],"");
                }
            }
            mlist.add(newMap);
        }
        return mlist;
    }

    /**
     * 手机号验证
     *
     * @param mobile
     * @return 验证通过返回true
     */
    public static boolean validateMobile(String mobile) {
        Pattern p = null;
        Matcher m = null;
        boolean b = false;
        p = Pattern.compile("^[1][3,4,5,6,7,8,9][0-9]{9}$"); // 验证手机号
        m = p.matcher(mobile);
        b = m.matches();
        return b;
    }

    /**
     * <b> 根据批次id导出该批次错误人员信息 </b>
     * @author ZXF
     * @create 2020/01/16 0016 14:05
     * @version
     * @注意事项 </b>
     */
    @Override
    public Result downErrInsPer(String importBatchId, HttpServletResponse response) {
        try {
            OutputStream os = null;
            com.bcxin.ins.util.excel.ExcelUtil<InsPerExcelVo> util = new com.bcxin.ins.util.excel.ExcelUtil(InsPerExcelVo.class);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;fileName=" + new String("错误人员信息.xls".getBytes("gb2312"), "ISO8859-1"));
            os = response.getOutputStream();
            List<InsPerExcelVo> list = dao.findErrTempBatchInsPerImport(Long.parseLong(importBatchId));
            util.exportExcel(list, "错误人员信息", 65535, os);
        } catch (Exception e) {
            return Result.fail("导出Excel错误人员失败！");
        }
        return Result.success("导出Excel错误人员成功！");
    }
}
