package com.bcxin.ins.service.preservation.impl;

import com.alibaba.fastjson.JSONObject;
import com.bcxin.ins.service.order.PolicyService;
import com.bcxin.ins.dao.preservation.InsPreservationDetailAPIDao;
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.entity.policy_core.InsPreservationDetail;
import com.bcxin.ins.entity.policy_core.InsPreservationRecord;
import com.bcxin.ins.entity.policy_core.InsPreservationResultSet;
import com.bcxin.ins.util.*;
import com.bcxin.ins.vo.*;
import com.bcxin.ins.vo.ConstProp;
import com.bcxin.ins.vo.excel.RecordDetailExcelVo;
import com.bcxin.ins.vo.excel.RecordExcelVo;
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 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.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * <b>保单子表业务处理 </b>
 *
 * @author zxf
 * @date 2017年1月5日 上午10:28:33
 * @注意事项 </b>
 * <b>
 */
@Service
@Transactional
public class InsPreservationDetailAPIServiceImpl extends ServiceImpl<InsPreservationDetailAPIDao, InsPreservationDetail> implements InsPreservationDetailAPIService {

    private Logger log =  LoggerFactory.getLogger(InsPreservationDetailAPIServiceImpl.class);

    @Autowired
    private InsPreservationDetailAPIDao insPreservationDetailAPIDao;
    @Autowired
    private InsPreservationRecordAPIService insPreservationRecordAPIService;
    @Autowired
    private InsPreservationResultSetAPIService insPreservationResultSetAPIService;
    @Autowired
    private PolicyService policyService;

    /**
     * <b>根据保全申请单id及条件查询保全详情记录表并进行分页 </b>
     *
     * @param page 分页
     * @param preservationId 保全申请单id
     * @param keyword 关键字
     * @return InsPreservationDetail 返回的是实体
     * @author ZXF
     * @date 2017年8月3日 下午17:19:24
     * @注意事项 </b>
     * <b>
     */
    @Override
    public List<InsPreservationDetail> findInsPreservationDetailByKeyword(DwzPage page, Long preservationId, String keyword){
        List<InsPreservationDetail> list = null;
        if(page != null){
            Page<InsPreservationDetail> pageHelper = new Page(page.getPageNum(), page.getNumPerPage());
            list = insPreservationDetailAPIDao.findInsPreservationDetailByKeyword( pageHelper, preservationId, keyword);
            page.setTotalCount(new Long(pageHelper.getTotal()).intValue());
        }else{
            list = insPreservationDetailAPIDao.findInsPreservationDetailByKeyword( preservationId, keyword);
        }
        return list;
    }

    /**
     * <b>根据保全申请单id及条件查询保全详情记录表并进行分页 </b>
     *
     * @param page 分页
     * @param preservationId 保全申请单id
     * @param keyword 关键字
     * @return InsPreservationDetailVo 返回的是辅助类
     * @author ZXF
     * @date 2017年8月3日 下午17:19:24
     * @注意事项 </b>
     * <b>
     */
    @Override
    public List<InsPreservationDetailVo> findInsPreservationDetailVoByKeyword(DwzPage page, Long preservationId, String keyword){
        List<InsPreservationDetailVo> list = null;
        if(page != null){
            Page<InsPreservationDetailVo> pageHelper = new Page(page.getPageNum(), page.getNumPerPage());
            list = insPreservationDetailAPIDao.findInsPreservationDetailVoByKeyword( pageHelper, preservationId, keyword);
            page.setTotalCount(new Long(pageHelper.getTotal()).intValue());
        }else{
            list = insPreservationDetailAPIDao.findInsPreservationDetailVoByKeyword( preservationId, keyword);
        }
        return list;
    }

    /**
     * <b> 根据保全ID查询批改人员信息 </b>
     * @author ZXF
     * @create 2020/03/18 0018 17:29
     * @version
     * @注意事项 </b>
     */
    @Override
    public List<InsPreservationDetailVo> findInsPreservationDetailVoByPreservationId(String sign, Long preservationId){
        return insPreservationDetailAPIDao.findInsPreservationDetailVoByPreservationId(sign, preservationId);
    }

    @Override
    public boolean deleteInsPreservationDetail(Long oid){
        boolean flag = true;
        InsPreservationDetail ipd = selectById(oid);
        if(ipd != null){
            ipd.setStatus(ConstProp.INT_NUMBER_TWO);//2,逻辑删除
            try {
                updateById(ipd);
            }catch (Exception e){//回滚
                log.error("保全人员删除过程事务发现异常，回滚数据",e);
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            }
        }else{
            flag = false;
        }
        return flag;
    }

    /**
     * 保存或更新保全人员信息
     * @param revise_type 保全类型
     * @param iprID 保全申请单表id
     * @param ipdVoList 页面接受的保全详情人员信息列表
     */
    @Override
    public boolean saveOrUpdateInsPreservationDetail(String revise_type, Long iprID, List<InsPreservationDetailVo> ipdVoList){
        try {
            if(ipdVoList==null || ipdVoList.isEmpty()){
                return true;
            }
            //新增
            List<InsPreservationDetail> apd = Lists.newArrayList();
            //修改
            List<InsPreservationDetail> upd = Lists.newArrayList();
            InsPreservationDetail ipd = null;
            InsPreservationRecord record = insPreservationRecordAPIService.selectById(iprID);
            for(InsPreservationDetailVo ipdVo : ipdVoList){
                ipd = new InsPreservationDetail();
                MyConverUtil.map2PO(MyConverUtil.PO2Map(ipdVo), ipd);
                ipd.setIns_preservation_record_id(iprID);
                if(StringUtils.isNotEmpty(ipdVo.getBusiness_id())){
                    ipd.setBusiness_id(Long.parseLong(ipdVo.getBusiness_id()));
                }else{
                    //如果是减员，必须有Business_id值
                    if(ConstProp.DIGIT_TWO.equals(revise_type)){
                        ipd.setBusiness_id(insPreservationResultSetAPIService.getIdByIdCardAndOrderId(ipdVo.getId_card(), record.getIns_insurance_slip_id()));
                        ipd.setBusiness_type(ConstProp.PRESERVE);
                    }
                }
                if(StringUtils.isNotEmpty(ipdVo.getPrev_record_id())){
                    ipd.setPrev_record_id(Long.parseLong(ipdVo.getPrev_record_id()));
                }
                ipd.setStatus(0);
                ipd.setRevise_time(new Date());
                if(StringUtils.isBlank(ipdVo.getOid())){
                    //不存在该记录
                    ipd.setIns_preservation_detail_id(IdWorker.getId());
                    apd.add(ipd);
                }else{
                    //已存在该id的记录
                    ipd.setIns_preservation_detail_id(Long.parseLong(ipdVo.getOid()));
                    ipd.setPrev_card(ipdVo.getPrev_card());
                    upd.add(ipd);
                }
            }
            if(apd.size()>0){
                insPreservationDetailAPIDao.batchInsert(apd);
            }
            if(upd.size()>0){
                insPreservationDetailAPIDao.batchUpdate(upd);
            }
            return true;
        }catch (Exception e){//回滚
            log.error("保全人员更新过程事务发现异常，回滚数据",e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            e.printStackTrace();
            return false;
        }
    }

    /**
     * <b> 过滤重复投保人员 </b>
     * @author ZXF
     * @create 2019/09/26 0026 17:03
     * @version
     * @注意事项 </b>
     */
    @Override
    public String isDuplicateIdCardNoToPreservationDetail(String order_id,List<InsPreservationDetailVo> list){
        String[] idcards  = new String[list.size()];
        int count = 0;
        for(InsPreservationDetailVo pvo : list){
            idcards[count] = pvo.getId_card();
            count ++;
        }
        OrderFormVo dto = policyService.accordingToOrderIDToGetPolicyDto(Long.parseLong(order_id));
        String str = insPreservationResultSetAPIService.findDuplicateIdCardNo(dto.getApplicant_name(),dto.getProduct_code().split(ConstProp.MINUS)[0],idcards);
        if(StringUtils.isNotEmpty(str)){
            return "300#部分证件号已在保，无法重复投保："+str;
        }
        return "200#该记录校验通过。";
    }

    /**
     * <b> 存在不在保的人员数据 </b>
     * @author ZXF
     * @create 2019/09/26 0026 17:03
     * @version
     * @注意事项 </b>
     */
    @Override
    public String notUnderWarrantyIdCardNo(String order_id,List<InsPreservationDetailVo> list){
        String[] idcards  = new String[list.size()];
        int count = 0;
        for(InsPreservationDetailVo pvo : list){
            idcards[count] = pvo.getId_card();
            count ++;
        }
        String str = insPreservationResultSetAPIService.findNotUnderWarrantyIdCardNo(order_id,idcards);
        if(StringUtils.isNotEmpty(str)){
            return "300#部分人员已不在保，不能重复减员操作："+str;
        }
        return "200#该记录校验通过。";
    }

    /**
     * 保全人员信息校验
     * @param vo
     * @return 200#成功，300#身份证xxxx人员信息有误
     */
    @Override
    public String personnelToCheck(InsPreservationDetailVo vo,String revise_type){
        if(StringUtils.isBlank(vo.getName())
                ||StringUtils.isBlank(vo.getBirthday())
                ||StringUtils.isBlank(vo.getCareer())
                ||StringUtils.isBlank(vo.getId_card())
                ||StringUtils.isBlank(vo.getId_type())
                ||StringUtils.isBlank(vo.getSex())){
            return "300#人员信息不能为空。";
        }
        /*if(!ValidatorUtil.matchRegular(vo.getName())){
            return "300#证件号码为："+vo.getId_card()+"人员的姓名不规范。";
        }else */if(ConstProp.DIGIT_ZERO.equals(vo.getId_type())){
            //如果是身份证
            if(!IdCardVerify.isValidatedAllIdcard(vo.getId_card())){
                return "300#证件号码为："+vo.getId_card()+"人员的证件号码不是有效证件，请联系客服。";
            }
            if(!IdNumberValidator.verifyB(vo.getId_card(), DateUtil.convertStringToDate(vo.getBirthday()))){
                return "300#证件号码为："+vo.getId_card()+"人员的出生日期不匹配。";
            }
            //减员排除人员年龄限制
            if(!(StringUtils.isNotEmpty(revise_type)&&ConstProp.DIGIT_TWO.equals(revise_type))
                    &&(IdNumberValidator.getAgeFromCard(vo.getId_card())>65||IdNumberValidator.getAgeFromCard(vo.getId_card())<16)){
                return "300#证件号码为："+vo.getId_card()+"人员的年龄超出限制（区间：16-60周岁）。";
            }
            if(!IdNumberValidator.verifyG(vo.getId_card(),vo.getSex())){
                return "300#证件号码为："+vo.getId_card()+"人员的性别不匹配。";
            }
        }else if(!ConstProp.DIGIT_ZERO.equals(vo.getId_type())){
            //如果不是身份证
            //减员排除人员年龄限制
            if(!(StringUtils.isNotEmpty(revise_type)&&ConstProp.DIGIT_TWO.equals(revise_type))
                    &&(IdNumberValidator.getAgeFromBirthday(vo.getBirthday())>65||IdNumberValidator.getAgeFromBirthday(vo.getBirthday())<16)){
                return "300#证件号码为："+vo.getId_card()+"人员的年龄超出限制（区间：16-60周岁）。";
            }
        }else if(!ValidatorUtil.validatePhone(vo.getTel())){
            return "300#证件号码为："+vo.getId_card()+"人员的手机号码格式有误。";
        }
        return "200#该记录校验通过。";
    }
    /**
     * 人员排序及校验
     * @param vo
     */
    @Override
    public String detailSortAndValidator(InsPreservationRecordVo vo){
        OrderFormVo order = policyService.accordingToOrderIDToGetPolicyDto(Long.parseLong(vo.getIns_insurance_slip_id()));
//        if(ConstProp.DIGIT_ONE.equals(vo.getRevise_type())||ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
//            if(StringUtils.isNotEmpty(order.getProduct_code())&&order.getProduct_code().contains(ConstProp.TYX_CA)){
//                return "300#暂未开放增、减员功能！";
//            }
//        }
        if(order.getProduct_code().contains(ConstProp.GZZRX_QH)&&ConstProp.DIGIT_TWO.equals(order.getInception_type())&&ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            return "300#前海雇主责任险（月度）产品不支持减员业务！";
        }
        if(order.getProduct_code().contains(ConstProp.GZZRX_CA_DQ)&&ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            return "300#长安（短期）雇主责任险产品不支持减员业务！";
        }
        if(DateUtil.getCurrentDate().equals(order.getPlanned_end_date())){
            return "300#保单即将到期不支持批改操作！";
        }
        if(DictConst.ORDER_STATUS_HZF.equals(order.getPolicy_status())){
            return "300#订单未完成支付！";
        }
        List<InsPreservationDetailVo> ipdVoList = vo.getInsPreservationDetailVoList();
        if(vo.getInsPreservationDetailVoList() == null){
            return "300#未获取到人员信息！";
        }
        Long endDate = DateUtil.parseDate(order.getPlanned_end_date()).getTime();
        Long startDate = DateUtil.parseDate(vo.getInception_date()).getTime();
        //检索生效时间是否是到期时间当天,如果count>0表示已经是最后一天不能做批单
//        int count = policyService.isDueToByPolicy(Long.parseLong(vo.getIns_insurance_slip_id()),vo.getInception_date());
        if((startDate-endDate)>0){
            return "300#保障生效时间不在有效期内！";
        }
        if(StringUtils.isNotEmpty(order.getProduct_code())&&order.getProduct_code().contains(ConstProp.GZZRX_CA)){
            boolean bool = insPreservationRecordAPIService.countDSHByPolicyNo(order.getExternal_reference());
            if(bool){
                return "300#存在未完成审批的批改记录，请等待审批通过后继续操作，如超1小时未完成审批请与客服联系！";
            }
        }
        //减员时，当在保人员人数为3时拦截
        if(ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            String res = empConfine(vo,ConstProp.INT_NUMBER_THREE);
            if(res.contains(ConstProp.CODE_FAILURE)){
                return res;
            }
        }
        //根据订单id查询同个投保人当前在待审核状态的保全单人员信息列表
        List<InsPreservationDetail> doList = findDetailByPolicyId(Long.parseLong(vo.getIns_insurance_slip_id()));
        //当前在保人员列表
        List<InsPreservationResultSet> rsaList = null;
        if(!ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            rsaList = insPreservationResultSetAPIService.findZBByPolicyId(Long.parseLong(vo.getIns_insurance_slip_id()),order.getProduct_code());
        }
        for(InsPreservationDetailVo ipdVo : ipdVoList){
            if(!ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
                String result = personnelToCheck(ipdVo,vo.getRevise_type());
                if(ConstProp.CODE_FAILURE.equals(result.split(ConstProp.POUND_SIGN)[0])){
                    return result;
                }
            }
            if(StringUtils.isEmpty(ipdVo.getOid())){
                ipdVo.setRevise_type(vo.getRevise_type());
            }
            if(rsaList != null && rsaList.size()>0){
                for(InsPreservationResultSet ipr : rsaList){
                    if(ipdVo.getId_card().equals(ipr.getId_card())){
                        return "300#证件号码为："+ipdVo.getId_card()+"人员已在保！";
                    }
                }
            }
            for(InsPreservationDetail ipd : doList){
                if(ipdVo.getId_card().equals(ipd.getId_card())){
                    return "300#证件号码为："+ipdVo.getId_card()+"人员已在待审核的保全单中，无法重复操作！";
                }
                if(ConstProp.DIGIT_THREE.equals(vo.getRevise_type())){
                    if(ipdVo.getBusiness_id().equals(String.valueOf(ipd.getBusiness_id()))){
                        return "300#证件号码为："+ipdVo.getId_card()+"人员所要替换的对象已在待审核的保全单中，无法重复操作！";
                    }
                }
                //当前在操作状态如果为减员
                if(!ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
                    continue;
                }
                //历史在待审核单中如果状态为替换
                if(ConstProp.DIGIT_THREE.equals(ipd.getRevise_type())){
                    if(String.valueOf(ipd.getBusiness_id()).equals(ipdVo.getBusiness_id())){
                        return "300#证件号码为："+ipdVo.getId_card()+"人员所要减员的对象已在待审核的保全单中，无法重复操作！";
                    }
                }
                //历史在待审核单中如果状态为替换
                if(ConstProp.DIGIT_TWO.equals(ipd.getRevise_type())){
                    if(StringUtils.isNotEmpty(ipdVo.getBusiness_id())&&ipdVo.getBusiness_id().equals(String.valueOf(ipd.getBusiness_id()))){
                        return "300#证件号码为："+ipdVo.getId_card()+"人员已提交减员保全申请单，无法重复操作！";
                    }
                }
            }
        }
        vo.setInsPreservationDetailVoList(ipdVoList);
        return "200#校验通过。";
    }

    /**
     * 批量人员排序及校验
     * @param vo
     */
    @Override
    public String batchDetailSortAndValidator(InsPreservationRecordVo vo){
        List<InsPreservationDetailVo> ipdVoList = vo.getInsPreservationDetailVoList();
        if(vo.getInsPreservationDetailVoList() == null){
            return "300#未获取到人员信息！";
        }
        OrderFormVo order = policyService.accordingToOrderIDToGetPolicyDto(Long.parseLong(vo.getIns_insurance_slip_id()));
//        if(ConstProp.DIGIT_ONE.equals(vo.getRevise_type())||ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
//            if(StringUtils.isNotEmpty(order.getProduct_code())&&order.getProduct_code().contains(ConstProp.TYX_CA)){
//                return "300#暂未开放增、减员功能！";
//            }
//        }
        if(order.getProduct_code().contains(ConstProp.GZZRX_QH)&&ConstProp.DIGIT_TWO.equals(order.getInception_type())&&ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            return "300#前海雇主责任险（月度）产品不支持减员业务！";
        }
        if(order.getProduct_code().contains(ConstProp.GZZRX_CA_DQ)&&ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            return "300#长安（短期）雇主责任险产品不支持减员业务！";
        }
        Long endDate = DateUtil.parseDate(order.getPlanned_end_date()).getTime();
        Long oStartDate = DateUtil.parseDate(order.getInception_date()).getTime();
        Long startDate = DateUtil.parseDate(vo.getInception_date()).getTime();
        //检索生效时间是否是到期时间当天,如果count>0表示已经是最后一天不能做批单
//        int count = policyService.isDueToByPolicy(Long.parseLong(vo.getIns_insurance_slip_id()),vo.getInception_date());
        if(oStartDate>startDate){
            return "300#批单生效日期不能早于保单起保日期！";
        }
        if((startDate-endDate)>0){
            return "300#保障生效时间不在有效期内！";
        }
        if(StringUtils.isNotEmpty(order.getProduct_code())&&order.getProduct_code().contains(ConstProp.GZZRX_CA)){
            boolean bool = insPreservationRecordAPIService.countDSHByPolicyNo(order.getExternal_reference());
            if(bool){
                return "300#存在未完成审批的批改记录，请等待审批通过后继续操作，如超1小时未完成审批请与客服联系！";
            }
        }
        //减员时，当在保人员人数为3时拦截
        if(ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            String res = empConfine(vo,ConstProp.INT_NUMBER_THREE);
            if(res.contains(ConstProp.CODE_FAILURE)){
                return res;
            }
        }
        //根据订单id查询同个投保人当前在待审核状态的保全单人员信息列表
        List<InsPreservationDetail> doList = findDetailByPolicyId(Long.parseLong(vo.getIns_insurance_slip_id()));
        //当前在保人员列表
        List<InsPreservationResultSet> rsaList = null;
        if(!ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
            rsaList = insPreservationResultSetAPIService.findZBByPolicyId(Long.parseLong(vo.getIns_insurance_slip_id()),order.getProduct_code());
        }
        String errMsg = "";
        for(InsPreservationDetailVo ipdVo : ipdVoList){
            if(!ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
                String result = personnelToCheck(ipdVo,vo.getRevise_type());
                if(ConstProp.CODE_FAILURE.equals(result.split(ConstProp.POUND_SIGN)[0])){
                    return result;
                }
            }
            if(StringUtils.isEmpty(ipdVo.getOid())){
                ipdVo.setRevise_type(vo.getRevise_type());
            }
            if(rsaList != null && rsaList.size()>0){
                int _sg_1 = 0;
                for(InsPreservationResultSet ipr : rsaList){
                    if(ipdVo.getId_card().equals(ipr.getId_card())){
                        errMsg = errMsg + "人员【"+ipdVo.getName()+" "+ipdVo.getId_card()+"】已在保；\n";
                        _sg_1 = 1;
                        break;
                    }
                }
                if(_sg_1 == 1){
                    continue;
                }
            }
            for(InsPreservationDetail ipd : doList){
                if(ipdVo.getId_card().equals(ipd.getId_card())){
                    errMsg = errMsg + "人员【"+ipdVo.getName()+" "+ipdVo.getId_card()+"】已在待审核的保全单中；\n";
                    break;
                }
                if(ConstProp.DIGIT_THREE.equals(vo.getRevise_type())){
                    if(ipdVo.getBusiness_id().equals(String.valueOf(ipd.getBusiness_id()))){
                        errMsg = errMsg + "人员【"+ipdVo.getName()+" "+ipdVo.getId_card()+"】所要替换的对象已在待审核的保全单中；\n";
                        break;
                    }
                }
                //当前在操作状态如果为减员
                if(!ConstProp.DIGIT_TWO.equals(vo.getRevise_type())){
                    continue;
                }
                //历史在待审核单中如果状态为替换
                if(ConstProp.DIGIT_THREE.equals(ipd.getRevise_type())){
                    if(String.valueOf(ipd.getBusiness_id()).equals(ipdVo.getBusiness_id())){
                        errMsg = errMsg + "人员【"+ipdVo.getName()+" "+ipdVo.getId_card()+"】所要减员的对象已在待审核的保全单中；\n";
                        break;
                    }
                }
                //历史在待审核单中如果状态为替换
                if(ConstProp.DIGIT_TWO.equals(ipd.getRevise_type())){
                    if(StringUtils.isNotEmpty(ipdVo.getBusiness_id())&&ipdVo.getBusiness_id().equals(String.valueOf(ipd.getBusiness_id()))){
                        errMsg = errMsg + "人员【"+ipdVo.getName()+" "+ipdVo.getId_card()+"】已提交减员保全申请单；\n";
                        break;
                    }
                }
            }
        }
        if(StringUtils.isNotEmpty(errMsg)){
            return "300#"+errMsg;
        }
        vo.setInsPreservationDetailVoList(ipdVoList);
        return "200#校验通过。";
    }

    /**
     * <b> 提交减员操作时校验该单人员是否少于3人 </b>
     * @author ZXF
     * @create 2018/01/17 0017 15:51
     * @version
     * @注意事项 </b>
     */
    @Override
    public String empConfine(InsPreservationRecordVo vo, int perNum){
        //在保人数-减员在审人数-当前操作人数
        int num = insPreservationResultSetAPIService.findZBNumber(vo.getInsPreservationDetailVoList().size(), Long.parseLong(vo.getIns_insurance_slip_id()));
        //如果在保人数减待审人数减刚提交审核人数小于三人
        if(num<perNum){
            return "300#团体意外险最少保障人员不得少于"+perNum+"人！";
        }
        return "200#校验通过。";
    }

    /**
     * 根据订单id及保全申请单状态查询所有的保全申请人员信息
     * @param policyId
     * @param status
     * @return
     */
    @Override
    public List<InsPreservationDetail> selectDetailByRecordIdList(Long policyId, String status, String resType){
        List<InsPreservationDetail> list = insPreservationDetailAPIDao.findInsPreservationDetail(policyId,status,resType);
        /*List<String> strList = insPreservationRecordAPIService.selectRecordIdByPolicyIdAndStatus(policyId,status,resType);
        List<InsPreservationDetail> list = new ArrayList<InsPreservationDetail>();
        for(String str : strList){
            list.addAll(insPreservationDetailAPIDao.findInsPreservationDetailByKeyword( Long.parseLong(str), ""));
        }*/
        return list;
    }

    /**
     * 根据订单id及保全申请单状态查询所有的待审核的保全申请人员信息
     * @param policyId
     * @return
     */
    private List<InsPreservationDetail> findDetailByPolicyId(Long policyId){
        return insPreservationDetailAPIDao.findDetailByPolicyId(policyId);
    }

    /**
     * 结果集数据转换成InsPreservationDetailVo
     * @param resultSet
     * @return
     */
    @Override
    public InsPreservationDetailVo detailConvertToResultSet(InsPreservationResultSetVo resultSet){
        InsPreservationDetailVo vo = new InsPreservationDetailVo();
        try {
            MyConverUtil.map2PO(MyConverUtil.PO2Map(resultSet), vo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return vo;
    }

    /**
     * excel人员导入处理
     * @param mFile MultipartFile上传文件
     * @return
     * @throws Exception
     */
    @Override
    public JSONObject readIPDExcel(MultipartFile mFile) throws Exception {
        JSONObject jo = new JSONObject();
        List<InsPreservationDetailVo> list = null;
        String msg = "";
        int cum = 0;
        String cds = "";
        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;
                }
                InsPreservationDetailVo ipdVo = 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;
                    ipdVo = new InsPreservationDetailVo();
                    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("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(ipdVo != null&&map.size()>0){
                        MyConverUtil.map2PO(map, ipdVo);
                        list.add(ipdVo);
                        ipdVo = 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);
        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查询保全人员信息 </b>
     * @author ZXF
     * @create 2018/05/31 0031 16:03
     * @version
     * @注意事项 </b>
     */
    @Override
    public List<Map<Object,Object>> queryDetail(Map<Object, Object> p){
        return insPreservationDetailAPIDao.queryDetail(p);
    }

    /**
     * <b> 导出批单人员 </b>
     * @author ZXF
     * @create 2020/01/19 0019 16:44
     * @version
     * @注意事项 </b>
     */
    @Override
    public void downPreservationDetail(Long preservationId, HttpServletResponse response){
        InsPreservationRecord record = insPreservationRecordAPIService.selectById(preservationId);
        String bm = StringUtils.isNotEmpty(record.getRevise_serial_num_ins())?record.getRevise_serial_num_ins():record.getRevise_serial_number();
        String fileName = bm+"-批改人员清单.xls";
        try {
            fileName = new String(fileName.getBytes("gb2312"), "ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        com.bcxin.ins.util.excel.ExcelUtil<ResultSetExcelVo> util = new com.bcxin.ins.util.excel.ExcelUtil(ResultSetExcelVo.class);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;fileName=" + fileName);
        try(OutputStream os = response.getOutputStream();) {
            List<ResultSetExcelVo> voList = listDetail(record);
            util.exportExcel(voList, bm+"批改人员清单", 65535, os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * <b> 根据用户id导出批单信息 </b>
     * @author ZXF
     * @create 2020/03/17 0017 17:19
     * @version
     * @注意事项 </b>
     */
    @Override
    public void downPreservationList(String userId, String keyword, String name, String start_time, String end_time, HttpServletResponse response) {
        String fileName = "批单信息.xls";
        try {
            fileName = new String(fileName.getBytes("gb2312"), "ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        com.bcxin.ins.util.excel.ExcelUtil<RecordDetailExcelVo> util = new com.bcxin.ins.util.excel.ExcelUtil(RecordDetailExcelVo.class);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;fileName=" + fileName);
        try(OutputStream os = response.getOutputStream();) {
            //DateUtil.thisMonday() 得到这个周的周一
            List<RecordDetailExcelVo> listMap = insPreservationDetailAPIDao.downPreservationList(userId, keyword, name, start_time, end_time);
            List<RecordDetailExcelVo> addMap = Lists.newArrayList();
            RecordDetailExcelVo addVo;
            for(RecordDetailExcelVo vo : listMap){
                if(StringUtils.isNotEmpty(vo.getTjData())){
                    addVo = (RecordDetailExcelVo)MyConverUtil.map2PO(MyConverUtil.PO2Map(vo), new RecordDetailExcelVo());
                    String[] arr = vo.getTjData().split(ConstProp.POUND_SIGN);
                    addVo.setName(arr[0]);
                    addVo.setId_card(arr[1]);
                    addVo.setTel(arr[2]);
                    addVo.setRevise_type("减员");
                    addMap.add(addVo);
                }
            }
            if(addMap.size()>0){
                listMap.addAll(addMap);
            }
            util.exportExcel(listMap, "批单信息", 65535, os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * <b> 组装批单人员信息 </b>
     * @author ZXF
     * @create 2020/01/19 0019 20:17
     * @version
     * @注意事项 </b>
     */
    private List<ResultSetExcelVo> listDetail(InsPreservationRecord record){
        List<ResultSetExcelVo> voList = Lists.newArrayList();
        List<InsPreservationDetailVo> ipdVolist = findInsPreservationDetailVoByKeyword(null,record.getIns_preservation_record_id(), ConstProp.BLANK_CHAR);
        InsPreservationResultSetVo resultSet;
        ResultSetExcelVo rsvo;
        InsPreservationDetailVo bvo;
        try {
            for(InsPreservationDetailVo ipdVo : ipdVolist){
                ipdVo.setRevise_type(ConstProp.DIGIT_ONE.equals(record.getRevise_type())?"增员"
                        :ConstProp.DIGIT_TWO.equals(record.getRevise_type())?"减员"
                        :"增员");
                ipdVo.setSex(ConstProp.DIGIT_ONE.equals(ipdVo.getSex())?"男"
                        :ConstProp.DIGIT_TWO.equals(ipdVo.getSex())?"女"
                        :"-");
                ipdVo.setCareer(ConstProp.DIGIT_ONE.equals(ipdVo.getCareer())?"内勤人员"
                        :ConstProp.DIGIT_TWO.equals(ipdVo.getCareer())?"保安人员"
                        :ConstProp.DIGIT_FOUR.equals(ipdVo.getCareer())?"保洁人员"
                        :"武装押运人员");
                rsvo = new ResultSetExcelVo();
                MyConverUtil.map2PO(MyConverUtil.PO2Map(ipdVo), rsvo);
                voList.add(rsvo);
                if(ConstProp.DIGIT_THREE.equals(record.getRevise_type())){
                    if(StringUtils.isNotBlank(ipdVo.getBusiness_id())&&StringUtils.isNotBlank(ipdVo.getBusiness_type())){
                        resultSet = insPreservationResultSetAPIService.getInsPreservationResultSetVoByOidAndSign(ipdVo.getBusiness_id(),"");
                        bvo = detailConvertToResultSet(resultSet);
                        bvo.setRevise_type("减员");
                        bvo.setSex(ConstProp.DIGIT_ONE.equals(bvo.getSex())?"男"
                                :ConstProp.DIGIT_TWO.equals(bvo.getSex())?"女"
                                :"-");
                        bvo.setCareer(ConstProp.DIGIT_ONE.equals(bvo.getCareer())?"内勤人员"
                                :ConstProp.DIGIT_TWO.equals(bvo.getCareer())?"保安人员"
                                :ConstProp.DIGIT_FOUR.equals(bvo.getCareer())?"保洁人员"
                                :"武装押运人员");
                        rsvo = new ResultSetExcelVo();
                        MyConverUtil.map2PO(MyConverUtil.PO2Map(bvo), rsvo);
                        voList.add(rsvo);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return voList;
    }
}
