InsureContorll.java

package cn.timer.api.controller.insure;

import cn.timer.api.bean.insure.*;
import cn.timer.api.bean.kqmk.KqglAssoLeaveRules;
import cn.timer.api.bean.qyzx.QyzxEntInfoM;
import cn.timer.api.bean.yggl.YgglMainEmp;
import cn.timer.api.config.annotation.CurrentUser;
import cn.timer.api.config.annotation.UserBean;
import cn.timer.api.controller.insure.enums.PlanEnum;
import cn.timer.api.dao.insure.InsureLogMapper;
import cn.timer.api.dao.insure.InsurePolicyMapper;
import cn.timer.api.dao.insure.InsureUserMapper;
import cn.timer.api.dao.yggl.YgglMainEmpMapper;
import cn.timer.api.dto.insure.InsureDto;
import cn.timer.api.dto.insure.InsureUserDto;
import cn.timer.api.dto.insure.PlansDto;
import cn.timer.api.dto.insure.PolicyDto;
import cn.timer.api.utils.*;
import com.alibaba.druid.util.Base64;
import com.aliyun.oss.common.utils.StringUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.beust.jcommander.internal.Lists;
import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.google.gson.JsonObject;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.eclipse.jetty.util.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

import com.alibaba.fastjson.JSONObject;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * @Description TODO
 * @Author wgd
 * @Date 2022/3/1 11:34
 */
@Api(tags = "8.0保险列表")
@RestController
@Transactional
@RequestMapping(value = "/insure", produces = {"application/json"})
public class InsureContorll {

    @Value("${BASE_API_URL}")
    private String base_api_url;
    private static final Logger log = LoggerFactory.getLogger(InsureContorll.class);

    @Value("${insure.appid}")
    private String appid;
    @Value("${insure.secret}")
    private String secret;
    @Value("${insure.appidq}")
    private String appidq;
    @Value("${insure.secretq}")
    private String secretq;
    @Value("${insure.uploadUrl}")
    private String uploadUrl;
    @Value("${insure.insuredUrl}")
    private String insuredUrl;
    @Value("${insure.uploadUrlq}")
    private String uploadUrl2;
    @Value("${insure.batchUrl}")
    private String batchUrl;
    @Value("${insure.policyDetail}")
    private String policyDetailUrl;

    private static SimpleDateFormat dtf3 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Autowired
    private YgglMainEmpMapper ygglMainEmpMapper;

    @Autowired
    private InsureUserMapper insureUserMapper;

    @Autowired
    private InsurePolicyMapper insurePolicyMapper;

    @Autowired
    private InsureLogMapper insureLogMapper;


    /**
     * 设置请求参数
     *
     * @param sign
     * @return
     */
    private Map setParams(String sign, String appid, String secret) {
        /*当前时间戳*/
        long timestamp = System.currentTimeMillis() / 1000;
        log.info("时间戳" + timestamp);
        Map paramsMap = Maps.newHashMap();
        paramsMap.put("pid", appid);
        paramsMap.put("timestamp", String.valueOf(timestamp));
        paramsMap.put("trace_id", appid + timestamp + new Random().nextInt((9999 - 100) + 1) + 10);
//        paramsMap.put("sign",Md5.md5(appid+secret+timestamp+sign.trim()));

        String value = appid + secret + timestamp + sign;
        log.info("body参数======" + sign);
        paramsMap.put("sign", DigestUtils.md5Hex(value));
        log.info("params参数======" + JSONObject.toJSONString(paramsMap));
        return paramsMap;
    }

    @PostMapping(value = "/insured_add")
    @ApiOperation(value = "1.投保申请", httpMethod = "POST", notes = "投保申请")
    public Result<Object> insured_add(@RequestBody InsureDto insureDto) throws ParseException {
        InsureProduct insureProduct = InsureProduct.builder().id(Integer.parseInt(insureDto.getProductId())).build().selectById();
        if (insureProduct == null) {
            return ResultUtil.error("产品不存在");
        }
        QyzxEntInfoM qyzxEntInfoM = QyzxEntInfoM.builder().id(Integer.parseInt(insureDto.getOrgCode())).build().selectById(); // 企业信息
        DateTimeFormatter dtf2 = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        LocalDateTime now = LocalDateTime.now();
        Date date = Date.from(now.atZone(ZoneId.systemDefault()).toInstant());
        Map bodyMap = Maps.newHashMap();
        ArrayList<Map> quotationsArry = new ArrayList<Map>();
        bodyMap.put("is_ss", 0);
        Map quotations = Maps.newHashMap();
        insureDto.getPlans().forEach(i -> {
            i.setPrice(getPrice(i.getBenefitBasicPlan(), i.getBenefitOccupationCategory()));
            insureDto.setTotalPrice(i.getPrice() * i.getUserIds().length + (insureDto.getTotalPrice() == null ? 0 : insureDto.getTotalPrice()));
        });
        quotations.put("trans_id", "MB_" + dtf2.format(now));/*报价请求流水号*/
        quotations.put("applicant_type", "2");/*投保人类型：2-企业*/
        quotations.put("quotation_type", 3);/*报价类型：3-投保申请*/
        quotations.put("currency", "5");/*币种：5人民币*/

        quotations.put("source_type", "");//*币种：5人民币*//*
        quotations.put("cps_email", "");//*币种：5人民币*//*
        quotations.put("cps_tel", "");//*币种：5人民币*//*
        quotations.put("last_serial_number", "");//*币种：5人民币*//*
        quotations.put("from_source", "");//*币种：5人民币*//*
        quotations.put("operator_name", "");//*币种：5人民币

        /*企业信息*/
        InsureApplicant insureApplicant = InsureApplicant.builder().id(1).build().selectById();
        if (insureApplicant == null) {
            return ResultUtil.error("未设置投保人");
        }
        Map companyApplicantMap = Maps.newHashMap();
        companyApplicantMap.put("applicant_e_name", insureApplicant.getApplicantEName());/*企业/网点名称*/
        companyApplicantMap.put("applicant_e_address", insureApplicant.getApplicantEAddress());/*企业/网点地址*/
        companyApplicantMap.put("applicant_e_contacts", insureApplicant.getApplicantEContacts());/*企业授权操作人姓名*/
        companyApplicantMap.put("applicant_e_phone", insureApplicant.getApplicantEPhone());/*操作人的联系电话*/
        companyApplicantMap.put("applicant_e_no_type", insureApplicant.getApplicantENoType());/*企业证件类型*/
        companyApplicantMap.put("applicant_e_no", insureApplicant.getApplicantENo());/*企业证件号码*/
        companyApplicantMap.put("applicant_e_mail", insureApplicant.getApplicantEMail());/*企业邮箱*/
        companyApplicantMap.put("applicant_province_city", "21024");/*省市区*/
        /*人员清单填被保人员*/
        String url = creaXSSFWorkbook(insureDto.getPlans(), insureDto.getPolicyDateStart());
        if (!StringUtil.isNotBlank(url)) {
            return ResultUtil.error("文件错误");
        }
        companyApplicantMap.put("applicant_employee_list", url);
        quotations.put("company_applicant", companyApplicantMap);


        Map basicMap = Maps.newHashMap();
        basicMap.put("partner_pid", appid);/*分销商pid*/
        basicMap.put("plan_code_id", insureProduct.getPlanCodeId());/*计划代码,选项值:61b85207b73d2ab09fb2959 (生产环境) 61b85207b73d2ab09fb2959 (沙箱环境)*/
        basicMap.put("product_code_id", insureProduct.getProductCodeId());/*产品代码,选项值:61b85207a9f87da19fb5986 (生产环境) 61b85207a9f87da19fb5986 (沙箱环境)*/
        basicMap.put("policy_date_start", insureDto.getPolicyDateStart());
        basicMap.put("policy_date_end", insureDto.getPolicyDateEnd());
        basicMap.put("channel", "");
        basicMap.put("fee", "");
        basicMap.put("partner_user_rate", "");
        basicMap.put("promotion_fee", "");
        basicMap.put("agent_id", "");
        basicMap.put("policy_insure_notify", "");
        basicMap.put("promotion_id", 0);
        basicMap.put("policy_number_last_year", "");
        quotations.put("basic", basicMap);
        quotations.put("individual_applicant", Maps.newHashMap());




        /*被保人员 填被保企业信息*/
        ArrayList<Map> insuredArray = new ArrayList<>();
        insureDto.getPlans().forEach(p -> {
            Map insured = Maps.newHashMap();
            insured.put("trans_id", "PI_" + qyzxEntInfoM.getId() + "_" + dtf2.format(now) + "_" + UUID.randomUUID().toString().replace("-", ""));
            insured.put("price", p.getUserIds().length * p.getPrice());/*根据基础计划生成*/
            insured.put("insured_applicant_relations", "36960");
            insured.put("insured_mobile", insureApplicant.getApplicantEPhone());
            insured.put("insured_e_contact", insureApplicant.getApplicantEContacts());
            insured.put("insured_name", qyzxEntInfoM.getName());
            insured.put("insured_no_type", "3306");
            insured.put("insured_no", qyzxEntInfoM.getCreditCode());
            insured.put("insured_zm_openid", "");
            insured.put("benefit_basic_plan", p.getBenefitBasicPlan());/*基础计划写死*/
            insured.put("benefit_occupation_category", p.getBenefitOccupationCategory());
            insured.put("benefit_EL_employee_number_t", p.getUserIds().length);
            insuredArray.add(insured);
        });
        quotations.put("insured", insuredArray);
        quotations.put("file", Lists.newArrayList());
        quotations.put("specifically_agreed_file", Lists.newArrayList());
        quotations.put("policy_no", "");
        quotationsArry.add(quotations);
        bodyMap.put("quotations", quotationsArry);
        log.info(JSONObject.toJSONString(bodyMap));
        String data = HttpUtils.sendPost(insuredUrl, setParams(JSONObject.toJSONString(bodyMap), appid, secret), bodyMap);
        Map dataMap = JSONObject.parseObject(data, Map.class);
        if (dataMap != null) {
            if ((dataMap.get("errcode").toString().equals("suc")) || (dataMap.get("errcode").toString().equals("e25"))) {
                Map policyMap = Maps.newHashMap();
                List<Map> batchMap = Lists.newArrayList();
                if (dataMap.get("errcode").toString().equals("suc")) {
                    Object o = dataMap.get("data");
                    Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(o));
                    policyMap = JSONObject.parseObject(JSONObject.toJSONString(map.get("policy")), Map.class);
                    batchMap = JSONObject.parseObject(JSONObject.toJSONString(policyMap.get("batch")), List.class);
                }
                InsurePolicy insurePolicy = InsurePolicy.builder().build();
                insurePolicy.setPolicyDateStart(dtf3.parse(insureDto.getPolicyDateStart()));
                insurePolicy.setPolicyDateEnd(dtf3.parse(insureDto.getPolicyDateEnd()));
                if (dataMap.get("errcode").toString().equals("suc")) {
                    insurePolicy.setPolicyNo(policyMap.get("policy_no").toString());
                    insurePolicy.setPolicyFile(policyMap.get("policy_file").toString());
                    insurePolicy.setKitUrl(policyMap.get("kit_url").toString());
                }
                insurePolicy.setType(2);/*年单或者月单*/
                insurePolicy.setCurrency("5");
                if (dataMap.get("errcode").toString().equals("suc")) {
                    insurePolicy.setStatus("1");
                } else {
                    insurePolicy.setStatus("2");
                }
                insurePolicy.setOrgCode(qyzxEntInfoM.getId());
                insurePolicy.setCreateTime(date);
                insurePolicy.setUpdateTime(date);
                insurePolicy.setSchemeName(insureProduct.getName());/*先写死*/
                insurePolicy.setTotalPremium(String.valueOf(insureDto.getTotalPrice()));
                insurePolicy.setProductId(insureProduct.getId());
                insurePolicy.insert();
                InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(1)
                        .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(JSONObject.toJSONString(dataMap)).requestPath(insuredUrl)
                        .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).policyId(insurePolicy.getId()).fileUrl(url).build().insert();
                for (PlansDto p : insureDto.getPlans()) {
                    List<YgglMainEmp> ygglMainEmpList = ygglMainEmpMapper.selectListByIds(p.getUserIds());
                    InsureUser insureUser;
                    for (YgglMainEmp y : ygglMainEmpList) {
                        insureUser = InsureUser.builder().build();
                        insureUser.setUserId(y.getId());
                        insureUser.setInsuredName(qyzxEntInfoM.getName());
                        insureUser.setPrice(String.valueOf(p.getPrice()));/*前端获取的保费*/
                        insureUser.setTransId("P_" + qyzxEntInfoM.getId() + "_" + dtf2.format(now));
                        insureUser.setPremium(String.valueOf(insureDto.getTotalPrice()));
                        if (dataMap.get("errcode").toString().equals("suc")) {
                            insureUser.setBatchNo(batchMap.get(0).get("batch_no").toString());
                            insureUser.setPolicyNo(policyMap.get("policy_no").toString());
                        }
                        insureUser.setBenefitBasicPlan(p.getBenefitBasicPlan());
                        insureUser.setUserId(y.getId());
                        insureUser.setOrgCode(y.getOrgCode());
                        insureUser.setBenefitOccupationCategory(p.getBenefitOccupationCategory());
                        insureUser.setInsuredMobile(y.getPhone());
                        insureUser.setInsuredNo(y.getZjNum());
                        insureUser.setInsuredEContact(y.getName());
                        insureUser.setApplicantEmployeeList(url);
                        insureUser.setTricycleFrameNumber(p.getTricycleFrameNumber());
                        if (dataMap.get("errcode").toString().equals("suc")) {
                            insureUser.setInsureStatus(1);
                            insureUser.setStatus("1");
                        } else {
                            insureUser.setInsureStatus(3);
                            insureUser.setStatus("2");
                        }
                        insureUser.setPolicyDateStart(dtf3.parse(insureDto.getPolicyDateStart()));
                        insureUser.setPolicyDateEnd(dtf3.parse(insureDto.getPolicyDateEnd()));
                        insureUser.setApplyType(2);//投保类型
                        insureUser.setPolicyId(insurePolicy.getId());

                        insureUser.insert();
                        if (dataMap.get("errcode").toString().equals("suc")) {
                            y.setIsInsure(1);
                            y.insertOrUpdate();
                        }
                    }
                }
                return ResultUtil.success(dataMap.get("errmsg").toString());
            }
            return ResultUtil.error(dataMap.get("errmsg").toString());
        }
        return ResultUtil.error("网络异常");

    }

    /**
     * 生成投保文件
     *
     * @param ids
     * @return
     */
    public String creaXSSFWorkbook(List<PlansDto> plansList, String createDate) {
        if (plansList.size() <= 0) {
            return "error";
        }
        LocalDate localDate = LocalDate.now();
        Date date = new Date();
        List list = Lists.newArrayList();
        plansList.forEach(p -> {
            List<YgglMainEmp> ygglMainEmpList = ygglMainEmpMapper.selectListByIds(p.getUserIds());
            for (YgglMainEmp y : ygglMainEmpList) {
                String[] arr = new String[]{String.valueOf(y.getId()), y.getName(), "身份证", y.getZjNum(), PlanEnum.getEnum(p.getBenefitBasicPlan(), p.getBenefitOccupationCategory()).getName(), createDate, "", StringUtils.isNullOrEmpty(p.getTricycleFrameNumber()) ? "无" : p.getTricycleFrameNumber(), PlanEnum.getEnum(p.getBenefitBasicPlan(), p.getBenefitOccupationCategory()).getType()};
                list.add(arr);
            }
        });
        String[] rowName = new String[]{"ID", "name", "ID_type", "ID_number", "Scheme_name", "Date_start", "Branch", "Tricycle_frame_number", "benefit_occupation_category"};
        XSSFWorkbook xssfWorkbook = ExcelUtils.exportExcel(localDate + "_投保" + list.size() + "名人员清单.xlsx", rowName, list);

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        String url = "";
        try {
            xssfWorkbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            String fileBase64 = Base64.byteArrayToBase64(bytes);

            /*body参数 begin*/
            Map bodyMap = Maps.newHashMap();
            bodyMap.put("app", "mall");
            bodyMap.put("name", localDate + "_" + list.size() + "名人员清单.xlsx");
            bodyMap.put("file", fileBase64);
            /*end*/
            String data = HttpUtils.sendPost(uploadUrl, setParams(JSONObject.toJSONString(bodyMap), appid, secret), bodyMap);
            Map<String, Object> dataMap = JSONObject.parseObject(data);
            /*创建日志*/
            if (dataMap.get("errcode").toString().equals("suc")) {
                Object o = dataMap.get("data");
                Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(o));
                url = map.get("url").toString();
            }
            InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(4)
                    .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(data).requestPath(uploadUrl)
                    .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).fileUrl(url).build().insert();
            /*文件流base64*/
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                xssfWorkbook.close();
                byteArrayOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return url;

    }


    @PostMapping(value = "/policyListAdmin")
    @ApiOperation(value = "4.保单列表--运营后台端", httpMethod = "POST", notes = "保单列表--运营后台端")
    public Result<Object> policyListAdmin(@RequestBody PolicyDto policyDto) {
        Map map = Maps.newHashMap();
        List<PolicyDto> list = insurePolicyMapper.policyList(policyDto);
        map.put("list", list);
        map.put("total", insurePolicyMapper.totalPolicy(policyDto));
        return ResultUtil.data(map);
    }

    @GetMapping(value = "/policyDetail")
    @ApiOperation(value = "5.保单信息", httpMethod = "GET", notes = "保单信息")
    public Result<Object> policyDetail(@RequestParam("policyId") Integer policyId) {
//        Map map = Maps.newHashMap();
        PolicyDto policyDto = insurePolicyMapper.getPolicyDetail(policyId);
        /*获取可选计划*/
//        List<InsureUser> plansList = InsureUser.builder().build().selectList(new QueryWrapper<InsureUser>().lambda().eq(InsureUser::getApplyType,2).eq(InsureUser::getPolicyId,policyId).groupBy(InsureUser::getBenefitBasicPlan));
        /*获取可选类型*/
//        List<InsureUser> categoryList = InsureUser.builder().build().selectList(new QueryWrapper<InsureUser>().lambda().eq(InsureUser::getApplyType,2).eq(InsureUser::getPolicyId,policyId).groupBy(InsureUser::getBenefitOccupationCategory));
        if (policyDto == null) {
            return ResultUtil.error("保单不存在");
        }
//        map.put("policy",policyDto);
//        map.put("plans",plansList.stream().map(InsureUser::getBenefitBasicPlan).toArray());
//        map.put("category",categoryList.stream().map(InsureUser::getBenefitOccupationCategory).toArray());
        return ResultUtil.data(policyDto);
    }

    @PostMapping(value = "/joinUser")
    @ApiOperation(value = "2.增员", httpMethod = "POST", notes = "增员")
    public Result<Object> joinUser(@RequestBody InsureDto insureDto) throws ParseException {

        InsurePolicy insurePolicy = InsurePolicy.builder().id(Integer.parseInt(insureDto.getId())).build().selectById();
        if (insurePolicy == null) {
            return ResultUtil.error("保单不存在");
        }
        LocalDateTime now = LocalDateTime.now();
        String url = creaXSSFWorkbook1(insureDto);
        log.info("保全增员文件地址", url);
        DateTimeFormatter dtf2 = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        Map bodyMap = Maps.newHashMap();
        bodyMap.put("contract_no", insurePolicy.getPolicyNo());
        bodyMap.put("order_status", "1");
        bodyMap.put("callback_plan", "");
        bodyMap.put("file_url", url);
        bodyMap.put("from_source", "API");
        bodyMap.put("import_review_callback", "");
        bodyMap.put("import_callback", base_api_url + "/callBack/policy/CallBack");
        bodyMap.put("third_uuid", "IS_" + insurePolicy.getOrgCode() + "_" + dtf2.format(now));
        String responData = HttpUtils.sendPost(batchUrl, setParams(JSONObject.toJSONString(bodyMap), appidq, secretq), bodyMap);
        Map<String, Object> dataMap = JSONObject.parseObject(responData);
        if (dataMap.get("errcode").toString().equals("suc")) {
            Map<String, Object> data = JSONObject.parseObject(JSONObject.toJSONString(dataMap.get("data")));
            Map<String, Object> orderImportInfo = JSONObject.parseObject(JSONObject.toJSONString(data.get("order_import_info")));
            String status = orderImportInfo.get("status").toString();
            if (status.equals("5") || status.equals("6") || status.equals("7")) {
                InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(2)
                        .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(responData).requestPath(batchUrl)
                        .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).policyId(insurePolicy.getId()).transId("IS_" + insurePolicy.getOrgCode() + "_" + dtf2.format(now)).fileUrl(data.get("file_url").toString()).build().insert();
                return ResultUtil.error("信息填写错误，请查看日志");
            } else {
                List<YgglMainEmp> ygglMainEmpList = ygglMainEmpMapper.selectListByIds(insureDto.getUserIds());
                QyzxEntInfoM qyzxEntInfoM = QyzxEntInfoM.builder().id(Integer.parseInt(insureDto.getOrgCode())).build().selectById();
                ygglMainEmpList.forEach(y -> {
                    try {
                        InsureUser.builder()
                                .transId("IS_" + insurePolicy.getOrgCode() + "_" + dtf2.format(now))
                                .insuredName(qyzxEntInfoM.getName())
                                .price(String.valueOf(PlanEnum.getEnum(insureDto.getBenefitBasicPlan(), insureDto.getBenefitOccupationCategory()).getPrice()))/*前端获取的保费*/
                                .premium(String.valueOf(PlanEnum.getEnum(insureDto.getBenefitBasicPlan(), insureDto.getBenefitOccupationCategory()).getPrice() * insureDto.getUserIds().length))
                                .batchNo(data.get("import_uuid").toString())
                                .benefitBasicPlan(insureDto.getBenefitBasicPlan())
                                .policyNo(orderImportInfo.get("contract_no").toString())
                                .userId(y.getId())
                                .orgCode(y.getOrgCode())
                                .insuredMobile(y.getPhone())
                                .benefitOccupationCategory(insureDto.getBenefitOccupationCategory())
                                .insuredNo(y.getZjNum())
                                .tricycleFrameNumber(insureDto.getTricycleFrameNumber())
                                .insuredEContact(y.getName())
                                .applyType(1)
                                .applicantEmployeeList(data.get("file_url").toString())
                                .policyDateStart(dtf3.parse(insureDto.getPolicyDateStart()))
                                .policyDateEnd(dtf3.parse(insureDto.getPolicyDateEnd()))
                                .insureStatus(3).policyId(insurePolicy.getId()).build().insert();
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    y.setIsInsure(1);
                    y.insertOrUpdate();
                });
                InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(2)
                        .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(responData).requestPath(batchUrl)
                        .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).policyId(insurePolicy.getId()).fileUrl(url).build().insert();
                return ResultUtil.data("核保成功，等待系统更新");
            }
        }
        InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(2)
                .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(responData).requestPath(batchUrl)
                .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).policyId(insurePolicy.getId()).build().insert();
        return ResultUtil.error(dataMap.get("errmsg").toString());
    }

    /**
     * 生成保全增员文件
     *
     * @param ids
     * @return
     */
    public String creaXSSFWorkbook1(InsureDto insureDto) {
        if (insureDto.getUserIds().length <= 0) {
            return "error";
        }
        LocalDate localDate = LocalDate.now();
        List<YgglMainEmp> ygglMainEmpList = ygglMainEmpMapper.selectListByIds(insureDto.getUserIds());
        List list = Lists.newArrayList();
        for (YgglMainEmp y : ygglMainEmpList) {
            String[] arr = new String[]{String.valueOf(y.getId()), y.getName(), "身份证", y.getZjNum(), PlanEnum.getEnum(insureDto.getBenefitBasicPlan(), insureDto.getBenefitOccupationCategory()).getName(), insureDto.getPolicyDateStart(), "", StringUtils.isNullOrEmpty(insureDto.getTricycleFrameNumber()) ? "无" : insureDto.getTricycleFrameNumber(), PlanEnum.getEnum(insureDto.getBenefitBasicPlan(), insureDto.getBenefitOccupationCategory()).getType()};
            list.add(arr);
        }
        String[] rowName = new String[]{"ID", "name", "ID_type", "ID_number", "Scheme_name", "Date_start", "Branch", "Tricycle_frame_number", "benefit_occupation_category"};

        XSSFWorkbook xssfWorkbook = ExcelUtils.exportExcel(localDate + "_补充" + list.size() + "名人员清单.xlsx", rowName, list);

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        String url = "";
        try {
            xssfWorkbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            String fileBase64 = Base64.byteArrayToBase64(bytes);

            /*body参数 begin*/
            Map bodyMap = Maps.newHashMap();
            bodyMap.put("media", fileBase64);
            /*end*/
            String data = HttpUtils.sendPost(uploadUrl2, setParams(JSONObject.toJSONString(bodyMap), appidq, secretq), bodyMap);
            Map<String, Object> dataMap = JSONObject.parseObject(data);
            if (dataMap.get("errcode").toString().equals("suc")) {
                Object o = dataMap.get("data");
                Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(o));
                url = map.get("file_url").toString();
            }
            InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(5)
                    .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(data).requestPath(uploadUrl2)
                    .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).fileUrl(url).build().insert();
            /*文件流base64*/
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                xssfWorkbook.close();
                byteArrayOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return url;
    }

    @GetMapping(value = "/downPolicyFile")
    @ApiOperation(value = "7.下载电子保单", httpMethod = "GET", notes = "下载电子保单")
    public void downPolicyFile(@RequestParam("policyId") String policyId, HttpServletRequest request, HttpServletResponse response) {
        InsurePolicy insurePolicy = InsurePolicy.builder().id(Integer.parseInt(policyId)).build().selectById();
        if (insurePolicy == null || StringUtils.isNullOrEmpty(insurePolicy.getPolicyFile())) {
            return;
        }
        OutputStream sos = null;
        BufferedInputStream bis = null;
        try {
            response.setContentType("application/octet-stream");
            response.setHeader("content-disposition", "attachment; filename=" + new String((insurePolicy.getPolicyNo() + ".pdf").getBytes("UTF8"), "ISO-8859-1"));
            response.setCharacterEncoding("UTF-8");
            sos = response.getOutputStream();
            String destUrl = insurePolicy.getPolicyFile();
            URL url = new URL(destUrl);
            HttpURLConnection httpUrl = (HttpURLConnection) url.openConnection();
            //连接指定的网络资源
            httpUrl.connect();
            //获取网络输入流
            bis = new BufferedInputStream(httpUrl.getInputStream());
            int b;
            while ((b = bis.read()) != -1) {
                sos.write(b);
            }
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                sos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                bis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @PostMapping(value = "/replaceUser")
    @ApiOperation(value = "6.保全替换", httpMethod = "POST", notes = "保全替换")
    public Result<Object> replaceUser(@RequestBody InsureDto insureDto) {
        InsurePolicy insurePolicy = InsurePolicy.builder().id(Integer.parseInt(insureDto.getId())).build().selectById();
        if (insurePolicy == null) {
            return ResultUtil.error("保单不存在");
        }
        /*单个替换*/
        if(insureDto.getLimit()==1){
            if(!StringUtils.isNullOrEmpty(insureDto.getUserName())&&!StringUtils.isNullOrEmpty(insureDto.getIdCardNo())){
                YgglMainEmp y = YgglMainEmp.builder().build().selectOne(new QueryWrapper<YgglMainEmp>().lambda()
                        .eq(YgglMainEmp::getName,insureDto.getUserName()).eq(YgglMainEmp::getZjNum,insureDto.getIdCardNo()));
                if(y==null){
                    y=new YgglMainEmp();
                    y.setName(insureDto.getUserName());
                    y.setZjType(0);
                    y.setZjNum(insureDto.getIdCardNo());
                    y.setOrgCode(Integer.parseInt(insureDto.getOrgCode()));
                    y.insert();
                }
                insureDto.setUserIds(new String[]{String.valueOf(y.getId())});
            }
        }
        List<InsureUser> oldInsureUserList = insureUserMapper.selectListByIds(insureDto.getOldIds());
        String[] oldUser = new String[oldInsureUserList.size()];
        for (int i = 0; i < oldInsureUserList.size(); i++) {
            oldUser[i] = oldInsureUserList.get(i).getUserId().toString();
        }
        Integer price = getPrice(insureDto.getBenefitBasicPlan(), insureDto.getBenefitOccupationCategory());
        LocalDateTime now = LocalDateTime.now();
        String url = creaXSSFWorkbook2(insureDto.getUserIds(), insureDto.getPolicyDateStart(), oldUser, insureDto.getBenefitOccupationCategory(), insureDto.getTricycleFrameNumber());
        log.info("保全替换文件地址", url);
        DateTimeFormatter dtf2 = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        Map bodyMap = Maps.newHashMap();
        bodyMap.put("contract_no", insurePolicy.getPolicyNo());
        bodyMap.put("order_status", "3");
        bodyMap.put("callback_plan", "");
        bodyMap.put("file_url", url);
        bodyMap.put("from_source", "API");
        bodyMap.put("import_review_callback", "");
        bodyMap.put("import_callback", base_api_url + "/callBack/policy/CallBack");
        bodyMap.put("third_uuid", "RP_" + insurePolicy.getOrgCode() + "_" + dtf2.format(now));
        String responData = HttpUtils.sendPost(batchUrl, setParams(JSONObject.toJSONString(bodyMap), appidq, secretq), bodyMap);
        Map<String, Object> dataMap = JSONObject.parseObject(responData);
        InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(3)
                .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(responData).requestPath(batchUrl)
                .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).policyId(insurePolicy.getId()).fileUrl(url).build().insert();
        if (dataMap.get("errcode").toString().equals("suc")) {
            Map<String, Object> data = JSONObject.parseObject(JSONObject.toJSONString(dataMap.get("data")));
            Map<String, Object> orderImportInfo = JSONObject.parseObject(JSONObject.toJSONString(data.get("order_import_info")));
            String status = orderImportInfo.get("status").toString();
            if (status.equals("5") || status.equals("6") || status.equals("7")) {
                InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(2)
                        .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(responData).requestPath(batchUrl)
                        .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).policyId(insurePolicy.getId()).transId("RP_" + insurePolicy.getOrgCode() + "_" + dtf2.format(now)).fileUrl(data.get("file_url").toString()).build().insert();
                //TODO 写入日志
                return ResultUtil.error("信息填写错误，请查看日志");
            } else {
                List<YgglMainEmp> ygglMainEmpList = ygglMainEmpMapper.selectListByIds(insureDto.getUserIds());
                QyzxEntInfoM qyzxEntInfoM = QyzxEntInfoM.builder().id(Integer.parseInt(insureDto.getOrgCode())).build().selectById();
                ygglMainEmpList.forEach(y -> {
                    try {
                        InsureUser.builder()
                                .transId("RP_" + insurePolicy.getOrgCode() + "_" + dtf2.format(now))
                                .insuredName(qyzxEntInfoM.getName())
                                .price(price.toString())/*前端获取的保费*/
                                .premium(String.valueOf(price * ygglMainEmpList.size()))
                                .batchNo(data.get("import_uuid").toString())
                                .benefitBasicPlan(insureDto.getBenefitBasicPlan())
                                .policyNo(orderImportInfo.get("contract_no").toString())
                                .userId(y.getId())
                                .orgCode(y.getOrgCode())
                                .benefitOccupationCategory(insureDto.getBenefitOccupationCategory())
                                .insuredMobile(y.getPhone())
                                .benefitOccupationCategory(insureDto.getBenefitOccupationCategory())
                                .insuredNo(y.getZjNum())
                                .tricycleFrameNumber(insureDto.getTricycleFrameNumber())
                                .insuredEContact(y.getName())
                                .applicantEmployeeList(data.get("file_url").toString())
                                .policyDateStart(dtf3.parse(insureDto.getPolicyDateStart()))
                                .policyDateEnd(dtf3.parse(insureDto.getPolicyDateEnd()))
                                .applyType(3)
                                .status("1").insureStatus(3).policyId(insurePolicy.getId()).build().insert();
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    y.setIsInsure(1);
                    y.insertOrUpdate();
                });
                oldInsureUserList.forEach(o -> {
                    o.setReplaceTransId(data.get("import_uuid").toString());
                    o.updateById();
                });

                return ResultUtil.data("核保成功，等待系统更新");
            }
        }
        return ResultUtil.data("成功");
    }

    /**
     * 生成保全替换文件
     *
     * @param ids
     * @return
     */
    public String creaXSSFWorkbook2(String[] ids, String date, String[] oldIds, String type, String tricycleFrameNumber) {
        if (ids.length <= 0 || oldIds.length <= 0) {
            return "error";
        }
        LocalDate localDate = LocalDate.now();
        List<YgglMainEmp> newList = ygglMainEmpMapper.selectListByIds(ids);
        List<YgglMainEmp> oldList = ygglMainEmpMapper.selectListByIds(oldIds);
        if (newList.size() != oldList.size()) {
            return "error";
        }
        List list = Lists.newArrayList();
        for (int i = 0; i < newList.size(); i++) {
            String[] arr = new String[]{String.valueOf(oldList.get(i).getId()),
                    oldList.get(i).getName(),
                    "身份证",
                    oldList.get(i).getZjNum(),
                    date.substring(0, 10),
                    newList.get(i).getName(),
                    "身份证",
                    newList.get(i).getZjNum(),
                    "", "", "", "", "", "", StringUtils.isNullOrEmpty(tricycleFrameNumber) ? "无" : tricycleFrameNumber, "1", type.equals("63119") ? "A类" : type.equals("63120") ? "B类" : "C类"};
            list.add(arr);
        }
        String[] rowName = new String[]{"ID", "name", "ID_type", "ID_number", "Update_date", "replace_name", "replace_ID_type",
                "replace_ID_number", "Date_of_birth", "insured_type", "insured_relationship", "cert_type_related_insured", "insured_relationship_id", "Branch",
                "Tricycle_frame_number", "insured_number", "benefit_occupation_category"};

        XSSFWorkbook xssfWorkbook = ExcelUtils.exportExcel(localDate + "_替换" + list.size() + "名人员清单.xlsx", rowName, list);

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        String url = "";
        try {
            xssfWorkbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            String fileBase64 = Base64.byteArrayToBase64(bytes);

            /*body参数 begin*/
            Map bodyMap = Maps.newHashMap();
            bodyMap.put("media", fileBase64);
            /*end*/
            String data = HttpUtils.sendPost(uploadUrl2, setParams(JSONObject.toJSONString(bodyMap), appidq, secretq), bodyMap);
            Map<String, Object> dataMap = JSONObject.parseObject(data);

            if (dataMap.get("errcode").toString().equals("suc")) {
                Object o = dataMap.get("data");
                Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(o));
                url = map.get("file_url").toString();
            }
            InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(6)
                    .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(data).requestPath(uploadUrl2)
                    .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).fileUrl(url).build().insert();
            /*文件流base64*/
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                xssfWorkbook.close();
                byteArrayOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return url;
    }

    /*获取基础计划的价格*/
    private Integer getPrice(String plan, String type) {
       /*
       基础计划
       30万雇主/3万医疗   36968,
       50万雇主/5万医疗	36969,
       80万雇主/10万医疗	36970,
       80万雇主/20万医疗	36971,
       100万雇主/10万医疗 36972
       */
        /*行业类型
         * A类	63119
         * B类	63120
         * C类	63121\
         * */
        switch (plan) {
            case "36968":
                if (type.equals("63119")) {
                    return PlanEnum.A_30.getPrice();
                } else if (type.equals("63120")) {
                    return PlanEnum.B_30.getPrice();
                }
                break;
            case "36969":
                if (type.equals("63119")) {
                    return PlanEnum.A_30.getPrice();
                } else if (type.equals("63120")) {
                    return PlanEnum.B_50.getPrice();
                } else if (type.equals("63121")) {
                    return PlanEnum.C_50.getPrice();
                }
                break;
            case "36970":
                if (type.equals("63119")) {
                    return PlanEnum.A_80.getPrice();
                } else if (type.equals("63120")) {
                    return PlanEnum.B_80.getPrice();
                } else if (type.equals("63121")) {
                    return PlanEnum.C_80.getPrice();
                }
                break;
            case "36971":
                if (type.equals("63119")) {
                    return PlanEnum.A_80_20.getPrice();
                } else if (type.equals("63120")) {
                    return PlanEnum.B_80_20.getPrice();
                }
                break;
            case "36972":
                if (type.equals("63119")) {
                    return PlanEnum.A_100.getPrice();
                } else if (type.equals("63120")) {
                    return PlanEnum.B_100.getPrice();
                }
                break;
        }
        return 0;
    }

    @PostMapping(value = "/policyTotal")
    @ApiOperation(value = "11.保单统计列表", httpMethod = "POST", notes = "保单统计列表")
    public Result<Object> policyTotal() {
        List<PolicyDto> list = insurePolicyMapper.policyTotalList();
        return ResultUtil.data(list);
    }

    @GetMapping(value = "/isInsured")
    @ApiOperation(value = "10.企业是否已投保", httpMethod = "POST", notes = "企业是否已投保")
    public Result<Object> isInsured(@RequestParam("orgCode") String orgCode) {
        InsurePolicy insurePolicy = InsurePolicy.builder().build().selectOne(new QueryWrapper<InsurePolicy>().lambda().eq(InsurePolicy::getOrgCode, orgCode).eq(InsurePolicy::getStatus, 1));
        if (insurePolicy != null) {
            return ResultUtil.error("该企业已经投过保，保单号为" + insurePolicy.getPolicyNo());
        }
        return ResultUtil.success();
    }

    @GetMapping(value = "/verification")
    @ApiOperation(value = "一键校验", httpMethod = "POST", notes = "一键校验")
    public Result<Object> verification() {
        /*校验投保*/
        List<InsurePolicy> insurePolicies = InsurePolicy.builder().build().selectList(new QueryWrapper<InsurePolicy>().lambda().eq(InsurePolicy::getStatus, 2));
        insurePolicies.forEach(i -> {
            InsureLog insureLog = InsureLog.builder().build().selectOne(new QueryWrapper<InsureLog>().lambda().eq(InsureLog::getPolicyId, i.getId()).eq(InsureLog::getType, 1));
            Map bodyMap = JSONObject.parseObject(insureLog.getRequestData(), Map.class);
            String data = HttpUtils.sendPost(insuredUrl, setParams(JSONObject.toJSONString(bodyMap), appid, secret), bodyMap);
            log.info("校验结果：{}", data);
            Map dataMap = JSONObject.parseObject(data, Map.class);
            InsureLog.builder().requestParam(JSONObject.toJSONString(setParams(JSONObject.toJSONString(bodyMap), appid, secret))).type(9)
                    .requestData(JSONObject.toJSONString(bodyMap)).createTime(new Date()).requestType(1).returnBody(JSONObject.toJSONString(dataMap)).requestPath(insuredUrl)
                    .returnCode(dataMap.get("errcode").toString()).returnMsg(dataMap.get("errmsg").toString()).policyId(i.getId()).build().insert();
            if (dataMap != null) {
                if ((dataMap.get("errcode").toString().equals("suc"))) {
                    Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(dataMap.get("data")));
                    Map policyMap = JSONObject.parseObject(JSONObject.toJSONString(map.get("policy")), Map.class);
                    i.setPolicyNo(policyMap.get("policy_no").toString());
                    i.setPolicyFile(policyMap.get("policy_file").toString());
                    i.setKitUrl(policyMap.get("kit_url").toString());
                    i.setStatus("1");
                    i.updateById();
                    /*批次信息*/
                    List<Map> batchMap = JSONObject.parseObject(JSONObject.toJSONString(policyMap.get("batch")), List.class);
                    List<InsureUser> insureUserList = InsureUser.builder().build().selectList(new QueryWrapper<InsureUser>().lambda().eq(InsureUser::getPolicyId, i.getId())
                            .eq(InsureUser::getApplyType, 2));
                    insureUserList.forEach(user -> {
                        user.setBatchNo(batchMap.get(0).get("batch_no").toString());
                        user.setPolicyNo(policyMap.get("policy_no").toString());
                        user.setInsureStatus(1);
                        user.setStatus("1");
                        user.updateById();
                        YgglMainEmp.builder().isInsure(1).build().update(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getId, user.getUserId()));
                    });
                }

            }
        });
        /*校验人员*/

        return ResultUtil.success();
    }


    @PostMapping(value = "/policyTotalList")
    @ApiOperation(value = "11.保单统计列表 -- 8小时端", httpMethod = "POST", notes = "保单统计列表")
    public Result<Object> policyTotal(@CurrentUser UserBean userBean, @RequestBody Page page) {
        List<InsureUserDto> list = insurePolicyMapper.selectPlansListByorgCode(userBean.getOrgCode());
        return ResultUtil.data(list);
    }


    @PostMapping(value = "/importUser")
    @ApiOperation(value = "导入用户--运营后台", httpMethod = "POST", notes = "导入用户")
    public Result<Object> importUser(@RequestParam("orgCode") String orgCode, @RequestParam("file") MultipartFile file) {
        SimpleDateFormat dtf = new SimpleDateFormat("yyyyMMdd");
        QyzxEntInfoM company = QyzxEntInfoM.builder().id(Integer.valueOf(orgCode)).build().selectById();
        if (company == null) {
            return ResultUtil.error("该企业不存在");
        }
        if (file.getSize() <= 0) {
            return ResultUtil.error("请上传文件");
        }
        List<YgglMainEmp> yg = Lists.newArrayList();
        List<YgglMainEmp> selectUserList=Lists.newArrayList();
        List<YgglMainEmp> allList=Lists.newArrayList();
        try {
            XSSFWorkbook xw = new XSSFWorkbook(file.getInputStream());
            XSSFSheet sheetAt = xw.getSheetAt(0);
            //默认第一行为标题行，i = 0
            XSSFRow titleRow = sheetAt.getRow(0);
            YgglMainEmp y;
            // 循环获取每一行数据
            for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                y = YgglMainEmp.builder().build();
                y.setOrgCode(Integer.parseInt(orgCode));
                // 读取每一格内容
                for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                    XSSFCell titleCell = titleRow.getCell(index);
                    XSSFCell cell = row.getCell(index);
                    cell.setCellType(CellType.STRING);
                    if (cell.getStringCellValue().equals("")) {
                        continue;
                    }
                    switch (ExcelUtils.getString(titleCell)) {
                        case "姓名（必填）":
                            y.setName(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
                            break;
                        case "手机号":
                            y.setPhone(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
                            break;
//                        case "性别":
//                            y.setSex(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
//                            break;
//                        case "证件类型":
//                            y.setZjType(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
//                            break;
                        case "证件号（必填）":
                            String idNo = Optional.ofNullable(ExcelUtils.getString(cell)).orElse("");
                            if (Strings.isNullOrEmpty(idNo) && idNo.length() != 18) {
                                return ResultUtil.error(y.getName() + "身份证格式不正确");
                            }
                            if (!idNo.matches("^(\\d{6})(\\d{4})(\\d{2})(\\d{2})(\\d{3})([0-9]|X)$")) {
                                return ResultUtil.error(y.getName() + "身份证格式不正确");
                            }

                            try {
                                y.setBirthday(dtf.parse(idNo.substring(6, 14)));
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            y.setZjNum(idNo);
                            break;
//                        case "工作性质":
//                            y.setJobType(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
//                            break;
//                        case "试用期":
//                            y.setSyq(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
//                            break;
//                        case "入职日期":
//                            y.setRzTime(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
//                            break;
                        default:
                            break;
                    }
                }
                if (StringUtils.isNullOrEmpty(y.getName()) || StringUtils.isNullOrEmpty(y.getZjNum())) {
                    continue;
                }
                yg.add(y);
            }
            List<YgglMainEmp> userList = YgglMainEmp.builder().build().selectList(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getOrgCode, orgCode));
            List<String> idNumList = userList.stream().map(YgglMainEmp::getZjNum).collect(Collectors.toList());
            List<YgglMainEmp> newList = yg.stream().filter(v -> !idNumList.contains(v.getZjNum())).collect(Collectors.toList());
            if (newList.size() > 0) {
                newList.forEach(u -> u.insert());
            }
             allList=ygglMainEmpMapper.selectListByIZjNum(yg.stream().map(YgglMainEmp::getZjNum).toArray(String[]::new),company.getId());
             selectUserList=allList.stream().filter(s->s.getIsInsure()==0).collect(Collectors.toList());
            //假期规则初始化
            String current_time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
            List<KqglAssoLeaveRules> rul = KqglAssoLeaveRules.builder().build().selectList(new QueryWrapper<KqglAssoLeaveRules>().lambda().eq(KqglAssoLeaveRules::getOrgCode, orgCode));

        } catch (IOException e) {
            e.printStackTrace();
        }
        List<String> message =allList.stream().filter(s->s.getIsInsure()==1).map(YgglMainEmp::getName).collect(Collectors.toList());
        if(message.size()>0){
            return ResultUtil.data(selectUserList, JSONObject.toJSONString(message)+"已投过保");
        }
        return ResultUtil.data(selectUserList, "导入成功");
     }

    @PostMapping(value = "/importPolicy")
    @ApiOperation(value = "导入保单--运营后台", httpMethod = "POST", notes = "导入用户")
    public Result<Object> importPolicy(@RequestParam("file") MultipartFile file, @RequestParam("productId") String productId) {
        SimpleDateFormat dtf = new SimpleDateFormat("yyyyMMdd");

        if (file.getSize() <= 0) {
            return ResultUtil.error("请上传文件");
        }
        InsureProduct insureProduct = InsureProduct.builder().id(Integer.parseInt(productId)).build().selectById();
        if (insureProduct == null) {
            return ResultUtil.error("产品不存在");
        }
        try {
            XSSFWorkbook xw = new XSSFWorkbook(file.getInputStream());
            XSSFSheet sheetAt = xw.getSheetAt(0);
            //默认第一行为标题行，i = 0
            XSSFRow titleRow = sheetAt.getRow(0);
            InsurePolicy policy = InsurePolicy.builder().build();
            QyzxEntInfoM company = null;
            InsureUser user;
            List<InsureUser> userList = Lists.newArrayList();
            // 循环获取每一行数据
            for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                user = InsureUser.builder().build();
                // 读取每一格内容
                for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                    XSSFCell titleCell = titleRow.getCell(index);
                    XSSFCell cell = row.getCell(index);
                    cell.setCellType(CellType.STRING);
                    switch (ExcelUtils.getString(titleCell)) {
                        case "申报日期":
                            Date createTime = null;
                            try {
                                createTime = dtf3.parse(ExcelUtils.getString(cell));
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            policy.setCreateTime(createTime);
                            break;
                        case "保单号":
                            policy.setPolicyNo(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
                            user.setPolicyNo(Optional.ofNullable(ExcelUtils.getString(cell)).orElse(""));
                            break;
                        case "申报类型":
                            if (ExcelUtils.getString(cell).equals("新增")) {
                                user.setApplyType(1);
                            } else if (ExcelUtils.getString(cell).equals("批改")) {
                                user.setApplyType(3);
                            }
                            break;
                        case "方案名称":
                            PlanEnum plan = PlanEnum.getEnumOfName(ExcelUtils.getString(cell).trim());
                            user.setBenefitBasicPlan(plan.getPlan());
                            user.setBenefitOccupationCategory(plan.getCategory());
                            break;
                        case "有效期起":
                            Date start = null;
                            try {
                                start = dtf3.parse(ExcelUtils.getString(cell));
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            user.setPolicyDateStart(start);
                            policy.setPolicyDateStart(start);
                            break;
                        case "有效期止":
                            Date end = null;
                            try {
                                end = dtf3.parse(ExcelUtils.getString(cell));
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            user.setPolicyDateEnd(end);
                            policy.setPolicyDateEnd(end);
                            break;
                        case "被保险人姓名":
                            user.setInsuredEContact(ExcelUtils.getString(cell));
                            break;
                        case "证件类型":
                            if (ExcelUtils.getString(cell).equals("身份证")) {

                            }
                            break;
                        case "证件号码":
                            user.setInsuredNo(ExcelUtils.getString(cell));
                            break;
                        case "二、三轮车车架号":
                            user.setTricycleFrameNumber(!ExcelUtils.getString(cell).equals("无") ? ExcelUtils.getString(cell) : null);
                            break;
                        case "职业类别":
                            if (ExcelUtils.getString(cell).equals("A类")) {
                                user.setBenefitOccupationCategory("63119");
                            } else if (ExcelUtils.getString(cell).equals("B类")) {
                                user.setBenefitOccupationCategory("63120");
                            } else if (ExcelUtils.getString(cell).equals("C类")) {
                                user.setBenefitOccupationCategory("63121");
                            }
                            break;
                        case "企业全称":
                            company = QyzxEntInfoM.builder().build().selectOne(new QueryWrapper<QyzxEntInfoM>().lambda().eq(QyzxEntInfoM::getAttestName, ExcelUtils.getString(cell)));
                            if (company == null) {
                                return ResultUtil.error("企业不存在");
                            }
                            user.setOrgCode(company.getId());
                            policy.setOrgCode(company.getId());
                            break;
                        case "总保费":
                            user.setPrice(ExcelUtils.getString(cell));
                            break;
                    }
                }
                userList.add(user);
            }
            //TODO 验证保单的状态
            long timestamp = System.currentTimeMillis() / 1000;
            Map map = Maps.newHashMap();
            map.put("pid", appid);
            map.put("policy_no", policy.getPolicyNo());
            map.put("sign", DigestUtils.md5Hex(appid + secret + timestamp));
            map.put("timestamp", String.valueOf(timestamp));
            String data = HttpUtils.sendGet(policyDetailUrl, map);
            Map returnMap = JSONObject.parseObject(data, Map.class);
            if (returnMap.get("errcode").toString().equals("suc")) {
                Map dataMap = JSONObject.parseObject(JSONObject.toJSONString(returnMap.get("data")), Map.class);
                policy.setProductId(insureProduct.getId());
                policy.setPolicyFile(dataMap.get("policy_file").toString());
                policy.setStatus("1");
            } else {
                return ResultUtil.error("保单不存在，请检查保单号是否正确");
            }
            InsurePolicy oldPolicy = InsurePolicy.builder().build().selectOne(new QueryWrapper<InsurePolicy>().lambda().eq(InsurePolicy::getPolicyNo, policy.getPolicyNo()));

            if (oldPolicy == null) {
                policy.setSchemeName(insureProduct.getName());
                policy.setUpdateTime(new Date());
                policy.insert();
            } else {
                oldPolicy.setPolicyFile(policy.getPolicyFile());
                policy.setUpdateTime(new Date());
                policy = oldPolicy;
                policy.updateById();
            }
            if (userList.size() <= 0) {
                return ResultUtil.error("导入错误，保单已存在");
            }
            for (InsureUser insureUser : userList) {
                /*根据名字，身份，所属企业获取员工*/
                YgglMainEmp ygglMainEmp = YgglMainEmp.builder().build().selectOne(new QueryWrapper<YgglMainEmp>().lambda()
                        .eq(YgglMainEmp::getName, insureUser.getInsuredEContact())
                        .eq(YgglMainEmp::getZjNum, insureUser.getInsuredNo())
                        .eq(YgglMainEmp::getOrgCode, insureUser.getOrgCode()));
                if (ygglMainEmp != null) {
                    insureUser.setInsuredMobile(ygglMainEmp.getPhone());
                } else {
                    /*如果不存在员工就创建一名员工*/
                    ygglMainEmp = new YgglMainEmp();
                    ygglMainEmp.setName(insureUser.getInsuredEContact());
                    ygglMainEmp.setZjType(0);
                    ygglMainEmp.setZjNum(insureUser.getInsuredNo());
                    ygglMainEmp.setOrgCode(insureUser.getOrgCode());
                }
                ygglMainEmp.setIsInsure(1);
                ygglMainEmp.insertOrUpdate();
                insureUser.setInsuredName(company.getAttestName());
                insureUser.setPolicyId(policy.getId());
                insureUser.setInsureStatus(1);
                insureUser.setUserId(ygglMainEmp.getId());
                insureUser.setStatus("1");
                insureUser.insert();
            }
            InsureLog.builder().requestParam(JSONObject.toJSONString(map)).type(6).createTime(new Date()).requestType(2).returnBody(data).requestPath(policyDetailUrl)
                    .returnCode(returnMap.get("errcode").toString()).returnMsg(returnMap.get("errmsg").toString()).policyId(policy.getId()).build().insert();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return ResultUtil.data("成功导入");
    }

    @PostMapping(value = "/importUserPolicy")
    @ApiOperation(value = "导入被保人员--运营后台", httpMethod = "POST", notes = "导入用户")
    public Result<Object> importUserPolicy(@RequestParam("file") MultipartFile file,@RequestParam("orgCode") Integer orgCode) {
        if(file.getSize()<=0){
            return ResultUtil.error("未上传文件");
        }
        if(orgCode==null){
            return ResultUtil.error("企业不存在");
        }
        QyzxEntInfoM qyzxEntInfoM = QyzxEntInfoM.builder().id(orgCode).build().selectById();
        if(qyzxEntInfoM==null){
            return ResultUtil.error("企业不存在");
        }
        XSSFWorkbook xw = null;
        Map<String,String> map = Maps.newHashMap();
        try {
            xw = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheetAt = xw.getSheetAt(0);
            //默认第一行为标题行，i = 0
            XSSFRow titleRow = sheetAt.getRow(0);
            InsureUser user;
            List<InsureUser> userList = Lists.newArrayList();
            // 循环获取每一行数据
            for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                user = InsureUser.builder().build();
                // 读取每一格内容
                for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                    XSSFCell titleCell = titleRow.getCell(index);
                    XSSFCell cell = row.getCell(index);
                    cell.setCellType(CellType.STRING);
                    switch (ExcelUtils.getString(titleCell)) {
                        case "被保人姓名(必填)":
                            if(StringUtils.isNullOrEmpty(ExcelUtils.getString(cell))){
                                return ResultUtil.error("第"+i+"行第"+index+"列被保人姓名不能为空");
                            }
                            user.setInsuredEContact(ExcelUtils.getString(cell));
                            break;
                        case "被保人身份证(必填)":
                            if(StringUtils.isNullOrEmpty(ExcelUtils.getString(cell))){
                                return ResultUtil.error("第"+i+"行第"+index+"列被保人身份证不能为空");
                            }
                            user.setInsuredNo(ExcelUtils.getString(cell));
                            break;
                        case "基础计划":
                            if(ExcelUtils.getString(cell).equals("30万意外/3万医疗/扩展24小时")){
                                user.setBenefitBasicPlan("36968");
                            }else if (ExcelUtils.getString(cell).equals("50万意外/5万医疗/扩展24小时")) {
                                user.setBenefitBasicPlan("36969");
                            }else if (ExcelUtils.getString(cell).equals("80万意外/10万医疗/扩展24小时")) {
                                user.setBenefitBasicPlan("36970");
                            }else if (ExcelUtils.getString(cell).equals("80万雇主/20万医疗/扩展24小时")) {
                                user.setBenefitBasicPlan("36971");
                            }else if (ExcelUtils.getString(cell).equals("100万意外/10万医疗/扩展24小时")) {
                                user.setBenefitBasicPlan("36972");
                            }else{
                                return ResultUtil.error("第"+i+"行第"+index+"基础计划不能为空");
                            }
                            break;
                        case "职业类型":
                            if (ExcelUtils.getString(cell).equals("A类")) {
                                user.setBenefitOccupationCategory("63119");
                            } else if (ExcelUtils.getString(cell).equals("B类")) {
                                user.setBenefitOccupationCategory("63120");
                            } else if (ExcelUtils.getString(cell).equals("C类")) {
                                user.setBenefitOccupationCategory("63121");
                            }
                            break;
                        case "二、三轮车车架号":
                            user.setTricycleFrameNumber(StringUtils.isNullOrEmpty(ExcelUtils.getString(cell))==true?"无":ExcelUtils.getString(cell));
                            break;
                    }
                }
                userList.add(user);
            }
            YgglMainEmp y;
            for(InsureUser iu:userList){
                y = YgglMainEmp.builder().build().selectOne(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getZjNum,iu.getInsuredNo())
                        .eq(YgglMainEmp::getName,iu.getInsuredEContact()).eq(YgglMainEmp::getOrgCode,qyzxEntInfoM.getId()));
                if(y==null){
                    y = new YgglMainEmp();
                    y.setName(iu.getInsuredEContact());
                    y.setZjType(0);
                    y.setZjNum(iu.getInsuredNo());
                    y.setOrgCode(qyzxEntInfoM.getId());
                    y.setIsInsure(0);
                    y.insert();
                }else{
                    if(y.getIsInsure()==1){
                        return ResultUtil.error(y.getName()+"已经投保");
                    }
                }
                iu.setOrgCode(qyzxEntInfoM.getId());
                iu.setUserId(y.getId());
            }
            return ResultUtil.data(userList);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return ResultUtil.error("导入失败");
    }
}