java-insagent-pc/src/main/java/com/ydtech/modules/nai/controller/ExcelCeController.java

package com.ydtech.modules.nai.controller;

import com.ydtech.core.page.*;
import com.ydtech.modules.ins.service.InsCompanyFeeService;
import com.ydtech.modules.nai.httpvo.excel.InsCompanyFeeExcel;
import com.ydtech.modules.nai.service.ExcelService;
import com.ydtech.modules.nai.utils.ExcelUtil;
import com.ydtech.modules.nai.excelvo.OfflineExcelVO;
import com.ydtech.modules.nai.excelvo.SettlementVO;
import com.ydtech.modules.nai.utils.ExcelUtils;
import org.apache.commons.collections4.ListUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName ExcelCeController
 * @Description: TODO
 * @Author
 * @Date 2021/6/26
 **/
@RestController
@RequestMapping("excel")
public class ExcelCeController extends BaseController {

    Logger logger = LoggerFactory.getLogger(ExcelCeController.class);

    @Autowired
    InsCompanyFeeService insCompanyFeeService;
    @Autowired
    ExcelService excelService;

    /**
     * excel导出结算数据
     *
     * @param resp
     * @return
     */
    @PostMapping(value = "/v1/**")
    public HttpResult findSignDataExport(@RequestBody PageRequest pageRequest, HttpServletResponse resp) {
        int pageNum = pageRequest.getPageNum();
        int pageSize = pageRequest.getPageSize();
//增加保险公司 inscompany，车牌号 licenseNo，险种 riskCode，协议类型 agreetype，协议号 agreeid，日期筛选 startdate、enddate，工号 userid，姓名 username，机构 deptid
        String settlestatus = getColumnFilterValue(pageRequest, "settlestatus"); // 结算状态 0未结算 1已结算
        String agreetype = getColumnFilterValue(pageRequest, "agreetype"); //协议类型
        String startdate = getColumnFilterValue(pageRequest, "startdate"); // 开始时间
        String enddate = getColumnFilterValue(pageRequest, "enddate"); // 截止时间
        String frameNo = getColumnFilterValue(pageRequest, "frameNo"); // 车架号
        String licenseNo = getColumnFilterValue(pageRequest, "licenseNo"); // 车牌号
        String orderno = getColumnFilterValue(pageRequest, "orderno"); // 订单号
        String inscompany = getColumnFilterValue(pageRequest, "inscompany"); // 保险公司
        String settletype = getColumnFilterValue(pageRequest, "settletype"); // 结算类型  A保险公司 B业务员
        String userid = getColumnFilterValue(pageRequest, "userid"); // 业务员工号
        String username = getColumnFilterValue(pageRequest, "username"); // 业务员
        String deptid = getColumnFilterValue(pageRequest, "deptid"); // 机构
        String agreeid = getColumnFilterValue(pageRequest, "agreeid"); // 协议号
        String riskCode = getColumnFilterValue(pageRequest, "riskCode"); // 险种代码

        if (StringUtils.isEmpty(settletype)) {
            return HttpResult.error("结算类型 settletype不能为空");
        }

        StringBuffer whereStr = new StringBuffer(" where 1=1");
        ArrayList array = new ArrayList();
        if (!StringUtils.isEmpty(settlestatus)) {
            whereStr.append(" and settlestatus=?");
            array.add(settlestatus);
        }

        if (!StringUtils.isEmpty(settletype)) {
            whereStr.append(" and settletype=?");
            array.add(settletype);
        }

        if (!StringUtils.isEmpty(agreetype)) {
            whereStr.append(" and agreetype=?");
            array.add(agreetype);
        }

        if (!StringUtils.isEmpty(frameNo)) {
            whereStr.append(" and frameno=?");
            array.add(frameNo);
        }
        if (!StringUtils.isEmpty(licenseNo)) {
            whereStr.append(" and licenseno=?");
            array.add(licenseNo);
        }
        if (!StringUtils.isEmpty(orderno)) {
            whereStr.append(" and orderno=?");
            array.add(orderno);
        }
        if (!StringUtils.isEmpty(inscompany)) {
            whereStr.append(" and inscompany=?");
            array.add(inscompany);
        }
        if (!StringUtils.isEmpty(userid)) {
            whereStr.append(" and userid=?");
            array.add(userid);
        }
        if (!StringUtils.isEmpty(username)) {
            whereStr.append(" and username=?");
            array.add(username);
        }
        if (!StringUtils.isEmpty(deptid)) {
            whereStr.append(" and deptid like ?");
            array.add(deptid + "%");
        }
        if (!StringUtils.isEmpty(agreeid)) {
            whereStr.append(" and agreeid=?");
            array.add(agreeid);
        }
        if (!StringUtils.isEmpty(riskCode)) {
            whereStr.append(" and producname=?");
            array.add(riskCode);
        }
        if (!StringUtils.isEmpty(startdate) && !StringUtils.isEmpty(enddate)) {
            whereStr.append(" and exists(select 1 from ins_order a where a.orderno=t.orderno and (DATE_FORMAT(a.audittime, '%Y-%m-%d') between date ? and date ?))");
            array.add(startdate);
            array.add(enddate);
        }
        ExcelUtil<InsCompanyFeeExcel> excelUtil = new ExcelUtil<>(InsCompanyFeeExcel.class);
        List<InsCompanyFeeExcel> list = insCompanyFeeService.selectFeeExcel(whereStr.toString(), array.toArray());

        try {
            //获取待写入excel数据
            if (list.size() == 0) {
                return null;
            }
            //写入excel filename=是生成的文件名
            excelUtil.exportExcel(list, "userInformation.xls", resp);

            return null;
        } catch (Exception e) {
            logger.error("XXXX统计失败！", e);
            return null;
        }

    }

    /**
     * 获取过滤字段的值
     *
     * @param filterName
     * @return
     */
    public String getColumnFilterValue(PageRequest pageRequest, String filterName) {
        String value = null;
        ColumnFilter columnFilter = pageRequest.getColumnFilter(filterName);
        if (columnFilter != null) {
            value = columnFilter.getValue();
        }
        return value;
    }

    /**
     * 导入excel
     */
    @RequestMapping(value = "/uploadExcel", method = RequestMethod.POST)
    public HttpResult uploadFile(@RequestParam MultipartFile file, String type, HttpServletRequest request) {
        if (request instanceof MultipartHttpServletRequest) {

            MultipartFile multipartFile = ((MultipartHttpServletRequest) request).getFile("file");
            StringBuffer whereStr = new StringBuffer("");
            ArrayList array = new ArrayList();
            List<SettlementVO> listdate = new ArrayList<>();
            List listrs = new ArrayList();
            try {
                if (type != null && type.equals("1")) {
                    List<SettlementVO> list = ExcelUtils.importExcel(multipartFile, SettlementVO.class);

                    List<SettlementVO> lists = excelService.select(whereStr.toString(), array.toArray());
                    logger.info("list大小" + list.size());
                    logger.info("lists" + lists.size());
                    List<SettlementVO> listd = ListUtils.removeAll(list, lists);
                    //  }

                    for (int i = 0; i < listd.size(); i++) {
                        excelService.insert(listd.get(i));
                    }
                } else {
                    List<SettlementVO> list = ExcelUtils.importExcel2(multipartFile, SettlementVO.class);

                    for (int i = 0; i < list.size(); i++) {
                        StringBuffer whereStrs = new StringBuffer("");
                        ArrayList arrays = new ArrayList();
                        whereStrs.append(" where 1=1 ");
                        SettlementVO settlementVO = list.get(i);
                        if (!StringUtils.isEmpty(settlementVO.getPolicyNo())) {
                            whereStrs.append(" and policyNo=?");
                            arrays.add(list.get(i).getPolicyNo());
                        }
                        if (!StringUtils.isEmpty(settlementVO.getLicenseNo())) {
                            whereStrs.append(" and licenseNo=?");
                            arrays.add(list.get(i).getLicenseNo());
                        }
                        if (!StringUtils.isEmpty(settlementVO.getBirthday())) {
                            whereStrs.append(" and birthday=?");
                            arrays.add(list.get(i).getBirthday());
                        }
                        listdate = excelService.select(whereStrs.toString(), arrays.toArray());
                        listrs.add(listdate);
                    }

                }
                return HttpResult.ok("200", listrs);

            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return HttpResult.ok(200);
    }


    @PostMapping(value = "/ClaimOrder")
    public HttpResult saveProdFee(@RequestBody List<SettlementVO> record) {
        String msg = "认领成功";
        Integer integer = null;
        for (int i = 0; i < record.size(); i++) {
            SettlementVO settlementVO = record.get(i);
            integer = excelService.updateSelective(settlementVO);
        }
        return HttpResult.ok(msg, integer);

    }

    /**
     * 首先上传文件模块
     */
    @RequestMapping(value = "/queryZMExcel", method = RequestMethod.POST)
    public HttpResult queryZMExcel(@RequestBody PageRequest pageRequest, HttpServletResponse resp) {

        int pageNum = pageRequest.getPageNum();
        int pageSize = pageRequest.getPageSize();
//增加保险公司 inscompany，车牌号 licenseNo，险种 riskCode，协议类型 agreetype，协议号 agreeid，日期筛选 startdate、enddate，工号 userid，姓名 username，机构 deptid
        String policyNo = getColumnFilterValue(pageRequest, "policyNo"); // 保单号
        String licenseNo = getColumnFilterValue(pageRequest, "licenseNo"); //签单日期
        String riskCode = getColumnFilterValue(pageRequest, "riskCode"); // 车牌号
        String billPrice = getColumnFilterValue(pageRequest, "billPrice"); // 险种
        String taxAmountPrice = getColumnFilterValue(pageRequest, "taxAmountPrice"); // 签单保费
        String rate = getColumnFilterValue(pageRequest, "rate"); // 车船税缴税金额
        String mount = getColumnFilterValue(pageRequest, "mount"); // 手续费比例
        String birthday = getColumnFilterValue(pageRequest, "birthday"); // 手续费金额
        String createTime = getColumnFilterValue(pageRequest, "createTime"); // 导入时间
        String isClaim = getColumnFilterValue(pageRequest, "isClaim"); // 是否领取
        String insuredage = getColumnFilterValue(pageRequest, "insuredage"); // 是否领取
        String approvaldate = getColumnFilterValue(pageRequest, "approvaldate"); // 是否领取

        String startdate = getColumnFilterValue(pageRequest, "startdate"); // 开始时间
        String enddate = getColumnFilterValue(pageRequest, "enddate"); // 截止时间

        StringBuffer whereStr = new StringBuffer(" where 1=1");
        ArrayList array = new ArrayList();
        if (!StringUtils.isEmpty(policyNo)) {
            whereStr.append(" and policyNo=?");
            array.add(policyNo);
        }

        if (!StringUtils.isEmpty(licenseNo)) {
            whereStr.append(" and licenseNo=?");
            array.add(licenseNo);
        }

        if (!StringUtils.isEmpty(riskCode)) {
            whereStr.append(" and riskCode=?");
            array.add(riskCode);
        }

        if (!StringUtils.isEmpty(billPrice)) {
            whereStr.append(" and billPrice=?");
            array.add(billPrice);
        }
        if (!StringUtils.isEmpty(licenseNo)) {
            whereStr.append(" and licenseno=?");
            array.add(licenseNo);
        }
        if (!StringUtils.isEmpty(taxAmountPrice)) {
            whereStr.append(" and taxAmountPrice=?");
            array.add(taxAmountPrice);
        }
        if (!StringUtils.isEmpty(rate)) {
            whereStr.append(" and rate=?");
            array.add(rate);
        }
        if (!StringUtils.isEmpty(approvaldate)) {
            whereStr.append(" and approvaldate=?");
            array.add(approvaldate);
        }
        if (!StringUtils.isEmpty(insuredage)) {
            whereStr.append(" and insuredage=?");
            array.add(insuredage);
        }

        if (!StringUtils.isEmpty(mount)) {
            whereStr.append(" and mount=?");
            array.add(mount);
        }

        if (!StringUtils.isEmpty(isClaim)) {
            whereStr.append(" and isClaim=?");
            array.add(isClaim);
        }
        if (!StringUtils.isEmpty(riskCode)) {
            whereStr.append(" and riskCode=?");
            array.add(riskCode);
        }
        if (!StringUtils.isEmpty(birthday) && !StringUtils.isEmpty(birthday)) {
            whereStr.append("and (DATE_FORMAT(a.birthday, '%Y-%m-%d') between birthday ? and birthday ?))");
            array.add(startdate);
            array.add(enddate);
        }
        String msg = "查询成功";
        PageResult pageResult = excelService.selectPage(pageNum, pageSize, whereStr.toString(), array.toArray());

        return HttpResult.ok(pageResult);

    }

    private HashMap<String, List<String>> checkExcel(List<OfflineExcelVO> checkList) {
        HashMap<String, List<String>> checkResult = new HashMap<String, List<String>>();
        List<String> dbExistList = new ArrayList<String>();
        List<String> excelExistList = new ArrayList<String>();
        StringBuffer strb = new StringBuffer("");
        StringBuffer whereStr = new StringBuffer(" where 1=1");
        ArrayList array = new ArrayList();
        List<OfflineExcelVO> selectResult = new ArrayList<OfflineExcelVO>();
        for (OfflineExcelVO excelVO : checkList) {
            //数据库存在判断
            whereStr = new StringBuffer(" where 1=1");
            whereStr.append(" and fieldName5=?");
            array.clear();
            array.add(excelVO.getFieldName5());
            selectResult = excelService.selectOffFeeExcel(whereStr.toString(), array.toArray());
            if (selectResult != null && selectResult.size() > 0) {
                dbExistList.add(excelVO.getFieldName5());
            }
            //excel存在判断
            if (strb.toString().contains(excelVO.getFieldName5())) {
                excelExistList.add(excelVO.getFieldName5());
            } else {
                strb.append(excelVO.getFieldName5() + ",");
            }
        }
        if (dbExistList.size() > 0 || excelExistList.size() > 0) {
            checkResult.put("db", dbExistList);
            checkResult.put("excel", excelExistList);
        }

        return checkResult;
    }

    /**
     * 导入excel
     * 根据保单号先删除后插入
     */
    @RequestMapping(value = "/uploadOfflineExcel", method = RequestMethod.POST)
    public HttpResult uploadOfflineFile(@RequestParam MultipartFile file, String type, HttpServletRequest request) {
        if (request instanceof MultipartHttpServletRequest) {

            MultipartFile multipartFile = ((MultipartHttpServletRequest) request).getFile("file");

            String str1 = "0";
            BigDecimal jnum = new BigDecimal(str1);
            BigDecimal snum = new BigDecimal(str1);
            BigDecimal fnum = new BigDecimal(str1);
            BigDecimal shnum = new BigDecimal(str1);
            String a = "0";

            try {
                List<OfflineExcelVO> list = ExcelUtils.importOfflineExcel(multipartFile, OfflineExcelVO.class);
                HashMap<String, List<String>> checkResult = checkExcel(list);
                if (!checkResult.isEmpty()) {
                    return HttpResult.error(checkResult);
                }
//                excelService.delete(list);
                for (int i = 0; i < list.size(); i++) {
                    double fieldName10 = list.get(i).getFieldName10();
                    double fieldName16 = list.get(i).getFieldName16();
                    double fieldName21 = list.get(i).getFieldName21();
                    double fieldName22 = list.get(i).getFieldName22();
                    BigDecimal num10 = new BigDecimal(fieldName10);
                    BigDecimal num16 = new BigDecimal(fieldName16);
                    BigDecimal num21 = new BigDecimal(fieldName21);
                    BigDecimal num22 = new BigDecimal(fieldName22);
                    snum = snum.add(num16);
                    jnum = jnum.add(num10);
                    fnum = fnum.add(num22);
                    shnum = shnum.add(num21);
                    excelService.insertOffline(list.get(i));
                }
                Map map = new HashMap<>();
                map.put("importCount", list.size());
                logger.info("list大小" + list.size());
                map.put("actualimportCount", list.size());//导入-数据库中数据
                map.put("snum", snum);//商业险
                map.put("jnum", jnum);//交强险
                map.put("fnum", fnum);//应付金额
                map.put("shnum", shnum);//应收金额

                return HttpResult.ok("200", map);

            } catch (Exception e) {
                e.printStackTrace();
                return HttpResult.error(e.getMessage());
            }

        }
        return HttpResult.ok(200);
    }

    /**
     * 批量查询上传
     */
    @RequestMapping(value = "/uploadOfflineFile_query", method = RequestMethod.POST)
    public HttpResult uploadOfflineFile_query(@RequestParam MultipartFile file, String type, HttpServletRequest request) {
        if (request instanceof MultipartHttpServletRequest) {

            MultipartFile multipartFile = ((MultipartHttpServletRequest) request).getFile("file");
            StringBuffer whereStr = new StringBuffer(" where 1=1");
            ArrayList array = new ArrayList();
            String str1 = "0";
            try {
                List<OfflineExcelVO> list = ExcelUtils.importOfflineExcel_query(multipartFile, OfflineExcelVO.class);
                StringBuffer whereStr_sub = new StringBuffer(" ");
                ArrayList array5 = new ArrayList();
                ArrayList array7 = new ArrayList();
                for (int i = 0; i < list.size(); i++) {
                    String fieldName5 = list.get(i).getFieldName5();
                    String fieldName7 = list.get(i).getFieldName7();
                    if (i == list.size() - 1) {
                        whereStr_sub.append("?");
                    } else {
                        whereStr_sub.append("?,");
                    }
                    array5.add(fieldName5);
                    array7.add(fieldName7);
                }
                whereStr.append(" and ( fieldName5 in (" + whereStr_sub + ") ");
                whereStr.append(" or fieldName7 in (" + whereStr_sub + ") ) ");
                array.addAll(array5);
                array.addAll(array7);
                int pageNum = 1;
                int pageSize = list.size();
                PageResult pageResult = excelService.selectPageOfflineExcel(pageNum, pageSize, whereStr.toString(), array.toArray());
                return HttpResult.ok(pageResult);

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return HttpResult.ok(200);
    }

    /**
     * 首先上传文件模块
     */
    @RequestMapping(value = "/queryOfflineExcel", method = RequestMethod.POST)
    public HttpResult queryOfflineExcel(@RequestBody PageRequest pageRequest, HttpServletResponse resp) {

        int pageNum = pageRequest.getPageNum();
        int pageSize = pageRequest.getPageSize();
//增加保险公司 inscompany，车牌号 licenseNo，险种 riskCode，协议类型 agreetype，协议号 agreeid，日期筛选 startdate、enddate，工号 userid，姓名 username，机构 deptid
        String fieldName1 = getColumnFilterValue(pageRequest, "fieldName1"); // 保单号
        String fieldName2 = getColumnFilterValue(pageRequest, "fieldName2"); //签单日期
        String fieldName3 = getColumnFilterValue(pageRequest, "fieldName3"); // 车牌号
        String fieldName4 = getColumnFilterValue(pageRequest, "fieldName4"); // 险种
        String fieldName5 = getColumnFilterValue(pageRequest, "fieldName5"); // 险种
        String fieldName6 = getColumnFilterValue(pageRequest, "fieldName6"); // 签单保费
        String fieldName7 = getColumnFilterValue(pageRequest, "fieldName7"); // 手续费比例
        String fieldName8 = getColumnFilterValue(pageRequest, "fieldName8"); // 导入时间
        String fieldName9 = getColumnFilterValue(pageRequest, "fieldName9"); // 是否领取

        String fieldName10 = getColumnFilterValue(pageRequest, "fieldName10"); // 开始时间
        String fieldName11 = getColumnFilterValue(pageRequest, "fieldName11"); // 截止时间
        String fieldName12 = getColumnFilterValue(pageRequest, "fieldName12");
        String fieldName13 = getColumnFilterValue(pageRequest, "fieldName13");
        String fieldName14 = getColumnFilterValue(pageRequest, "fieldName14");
        String fieldName15 = getColumnFilterValue(pageRequest, "fieldName15");
        String fieldName16 = getColumnFilterValue(pageRequest, "fieldName16");
        String fieldName17 = getColumnFilterValue(pageRequest, "fieldName17");
        String fieldName18 = getColumnFilterValue(pageRequest, "fieldName18");
        String fieldName19 = getColumnFilterValue(pageRequest, "fieldName19");
        String fieldName20 = getColumnFilterValue(pageRequest, "fieldName20");
        String fieldName21 = getColumnFilterValue(pageRequest, "fieldName21");
        String fieldName22 = getColumnFilterValue(pageRequest, "fieldName22");
        String fieldName23 = getColumnFilterValue(pageRequest, "fieldName23");
        String fieldName24 = getColumnFilterValue(pageRequest, "fieldName24");
        String fieldName25 = getColumnFilterValue(pageRequest, "fieldName25");
        String fieldName26 = getColumnFilterValue(pageRequest, "fieldName26");


        StringBuffer whereStr = new StringBuffer(" where 1=1");
        ArrayList array = new ArrayList();

        if (!StringUtils.isEmpty(fieldName2)) {
            whereStr.append(" and fieldName2=?");
            array.add(fieldName2);
        }

        if (!StringUtils.isEmpty(fieldName3)) {
            whereStr.append(" and fieldName3=?");
            array.add(fieldName3);
        }

        if (!StringUtils.isEmpty(fieldName4)) {
            whereStr.append(" and fieldName4=?");
            array.add(fieldName4);
        }

        if (!StringUtils.isEmpty(fieldName5)) {
            whereStr.append(" and fieldName5=?");
            array.add(fieldName5);
        }


        if (!StringUtils.isEmpty(fieldName6)) {
            whereStr.append(" and fieldName6=?");
            array.add(fieldName6);
        }
        if (!StringUtils.isEmpty(fieldName7)) {
            whereStr.append(" and fieldName7=?");
            array.add(fieldName7);
        }
        if (!StringUtils.isEmpty(fieldName8)) {
            whereStr.append(" and fieldName8=?");
            array.add(fieldName8);
        }
        if (!StringUtils.isEmpty(fieldName9)) {
            whereStr.append(" and fieldName9=?");
            array.add(fieldName9);
        }
        if (!StringUtils.isEmpty(fieldName10)) {
            whereStr.append(" and fieldName10=?");
            array.add(fieldName10);
        }
        if (!StringUtils.isEmpty(fieldName11)) {
            whereStr.append(" and fieldName11=?");
            array.add(fieldName11);
        }
        if (!StringUtils.isEmpty(fieldName12)) {
            whereStr.append(" and fieldName12=?");
            array.add(fieldName12);
        }
        if (!StringUtils.isEmpty(fieldName13)) {
            whereStr.append(" and fieldName13=?");
            array.add(fieldName13);
        }
        if (!StringUtils.isEmpty(fieldName14)) {
            whereStr.append(" and fieldName14=?");
            array.add(fieldName14);
        }
        if (!StringUtils.isEmpty(fieldName15)) {
            whereStr.append(" and fieldName15=?");
            array.add(fieldName15);
        }
        if (!StringUtils.isEmpty(fieldName16)) {
            whereStr.append(" and fieldName16=?");
            array.add(fieldName16);
        }
        if (!StringUtils.isEmpty(fieldName17)) {
            whereStr.append(" and fieldName17=?");
            array.add(fieldName17);
        }
        if (!StringUtils.isEmpty(fieldName18)) {
            whereStr.append(" and fieldName18=?");
            array.add(fieldName18);
        }
        if (!StringUtils.isEmpty(fieldName19)) {
            whereStr.append(" and fieldName19=?");
            array.add(fieldName19);
        }
        if (!StringUtils.isEmpty(fieldName20)) {
            whereStr.append(" and fieldName20=?");
            array.add(fieldName20);
        }
        if (!StringUtils.isEmpty(fieldName21)) {
            whereStr.append(" and fieldName21=?");
            array.add(fieldName21);
        }
        if (!StringUtils.isEmpty(fieldName22)) {
            whereStr.append(" and fieldName22=?");
            array.add(fieldName22);
        }
        if (!StringUtils.isEmpty(fieldName23)) {
            whereStr.append(" and fieldName23=?");
            array.add(fieldName23);
        }
        if (!StringUtils.isEmpty(fieldName24)) {
            whereStr.append(" and fieldName24 like ?");
            array.add("%" + fieldName24 + "%");
        }
        if (!StringUtils.isEmpty(fieldName25)) {
            whereStr.append(" and fieldName25=?");
            array.add(fieldName25);
        }
        if (!StringUtils.isEmpty(fieldName26)) {
            whereStr.append(" and fieldName26=?");
            array.add(fieldName26);
        }

        if (!StringUtils.isEmpty(fieldName1) && !StringUtils.isEmpty(fieldName1)) {
            whereStr.append(" and DATE_FORMAT(fieldName1, '%Y-%m-%d') >= date ? and DATE_FORMAT(fieldName1, '%Y-%m-%d') <= date ?");
            if (fieldName1.contains(",")) {
                array.add(fieldName1.substring(0, fieldName1.indexOf(",")));
                array.add(fieldName1.substring(fieldName1.indexOf(",") + 1));
            } else {
                array.add(fieldName1);
                array.add(fieldName1);
            }
        }
        String msg = "查询成功";
        PageResult pageResult = excelService.selectPageOfflineExcel(pageNum, pageSize, whereStr.toString(), array.toArray());
        return HttpResult.ok(pageResult);
        /*if (fieldName24!=null&&!fieldName24.equals("")) {
            if (fieldName24.equals("收")||fieldName24.equals("已收")||fieldName24.equals("未收")) {
                PageResult pageResult = excelService.selectPageOfflineExcel(pageNum, pageSize, whereStr.toString(), array.toArray());
                return HttpResult.ok(pageResult);

            }else if(fieldName24.equals("付")||fieldName24.equals("已付")||fieldName24.equals("未付")){
                PageResult pageResult = excelService.selectPageOfflineExcelCheck(pageNum, pageSize, whereStr.toString(), array.toArray());
                return HttpResult.ok(pageResult);
            }
        }*/

    }


    /**
     * excel导出结算数据
     *
     * @param resp
     * @return
     */
    @PostMapping(value = "/findOfflineExcel")
    public HttpResult findOfflineExcel(@RequestBody PageRequest pageRequest, HttpServletResponse resp) {

        int pageNum = pageRequest.getPageNum();
        int pageSize = pageRequest.getPageSize();
//增加保险公司 inscompany，车牌号 licenseNo，险种 riskCode，协议类型 agreetype，协议号 agreeid，日期筛选 startdate、enddate，工号 userid，姓名 username，机构 deptid
        String fieldName1 = getColumnFilterValue(pageRequest, "fieldName1"); // 保单号
        String fieldName2 = getColumnFilterValue(pageRequest, "fieldName2"); //签单日期
        String fieldName3 = getColumnFilterValue(pageRequest, "fieldName3"); // 车牌号
        String fieldName4 = getColumnFilterValue(pageRequest, "fieldName4"); // 险种
        String fieldName5 = getColumnFilterValue(pageRequest, "fieldName5"); // 险种
        String fieldName6 = getColumnFilterValue(pageRequest, "fieldName6"); // 签单保费
        String fieldName7 = getColumnFilterValue(pageRequest, "fieldName7"); // 手续费比例
        String fieldName8 = getColumnFilterValue(pageRequest, "fieldName8"); // 导入时间
        String fieldName9 = getColumnFilterValue(pageRequest, "fieldName9"); // 是否领取

        String fieldName10 = getColumnFilterValue(pageRequest, "fieldName10"); // 开始时间
        String fieldName11 = getColumnFilterValue(pageRequest, "fieldName11"); // 截止时间
        String fieldName12 = getColumnFilterValue(pageRequest, "fieldName12");
        String fieldName13 = getColumnFilterValue(pageRequest, "fieldName13");
        String fieldName14 = getColumnFilterValue(pageRequest, "fieldName14");
        String fieldName15 = getColumnFilterValue(pageRequest, "fieldName15");
        String fieldName16 = getColumnFilterValue(pageRequest, "fieldName16");
        String fieldName17 = getColumnFilterValue(pageRequest, "fieldName17");
        String fieldName18 = getColumnFilterValue(pageRequest, "fieldName18");
        String fieldName19 = getColumnFilterValue(pageRequest, "fieldName19");
        String fieldName20 = getColumnFilterValue(pageRequest, "fieldName20");
        String fieldName21 = getColumnFilterValue(pageRequest, "fieldName21");
        String fieldName22 = getColumnFilterValue(pageRequest, "fieldName22");
        String fieldName23 = getColumnFilterValue(pageRequest, "fieldName23");
        String fieldName24 = getColumnFilterValue(pageRequest, "fieldName24");
        String fieldName25 = getColumnFilterValue(pageRequest, "fieldName25");
        String fieldName26 = getColumnFilterValue(pageRequest, "fieldName26");

        String fieldName99 = getColumnFilterValue(pageRequest, "fieldName99");
        StringBuffer whereStr = new StringBuffer(" where 1=1");
        ArrayList array = new ArrayList();
        if (fieldName99 != null && !fieldName99.equals("")) {
            String[] list = fieldName99.split(",");
            whereStr.append(" and fieldName5 in (");
            for (String s : list) {
                whereStr.append("?,");
                array.add(s);
            }
            whereStr.deleteCharAt(whereStr.length() - 1);
            whereStr.append(")");
        }


        if (!StringUtils.isEmpty(fieldName2)) {
            whereStr.append(" and fieldName2=?");
            array.add(fieldName2);
        }

        if (!StringUtils.isEmpty(fieldName3)) {
            whereStr.append(" and fieldName3=?");
            array.add(fieldName3);
        }

        if (!StringUtils.isEmpty(fieldName4)) {
            whereStr.append(" and fieldName4=?");
            array.add(fieldName4);
        }

        if (!StringUtils.isEmpty(fieldName5)) {
            whereStr.append(" and fieldName5=?");
            array.add(fieldName5);
        }


        if (!StringUtils.isEmpty(fieldName6)) {
            whereStr.append(" and fieldName6=?");
            array.add(fieldName6);
        }
        if (!StringUtils.isEmpty(fieldName7)) {
            whereStr.append(" and fieldName7=?");
            array.add(fieldName7);
        }
        if (!StringUtils.isEmpty(fieldName8)) {
            whereStr.append(" and fieldName8=?");
            array.add(fieldName8);
        }
        if (!StringUtils.isEmpty(fieldName9)) {
            whereStr.append(" and fieldName9=?");
            array.add(fieldName9);
        }
        if (!StringUtils.isEmpty(fieldName10)) {
            whereStr.append(" and fieldName10=?");
            array.add(fieldName10);
        }
        if (!StringUtils.isEmpty(fieldName11)) {
            whereStr.append(" and fieldName11=?");
            array.add(fieldName11);
        }
        if (!StringUtils.isEmpty(fieldName12)) {
            whereStr.append(" and fieldName12=?");
            array.add(fieldName12);
        }
        if (!StringUtils.isEmpty(fieldName13)) {
            whereStr.append(" and fieldName13=?");
            array.add(fieldName13);
        }
        if (!StringUtils.isEmpty(fieldName14)) {
            whereStr.append(" and fieldName14=?");
            array.add(fieldName14);
        }
        if (!StringUtils.isEmpty(fieldName15)) {
            whereStr.append(" and fieldName15=?");
            array.add(fieldName15);
        }
        if (!StringUtils.isEmpty(fieldName16)) {
            whereStr.append(" and fieldName16=?");
            array.add(fieldName16);
        }
        if (!StringUtils.isEmpty(fieldName17)) {
            whereStr.append(" and fieldName17=?");
            array.add(fieldName17);
        }
        if (!StringUtils.isEmpty(fieldName18)) {
            whereStr.append(" and fieldName18=?");
            array.add(fieldName18);
        }
        if (!StringUtils.isEmpty(fieldName19)) {
            whereStr.append(" and fieldName19=?");
            array.add(fieldName19);
        }
        if (!StringUtils.isEmpty(fieldName20)) {
            whereStr.append(" and fieldName20=?");
            array.add(fieldName20);
        }
        if (!StringUtils.isEmpty(fieldName21)) {
            whereStr.append(" and fieldName21=?");
            array.add(fieldName21);
        }
        if (!StringUtils.isEmpty(fieldName22)) {
            whereStr.append(" and fieldName22=?");
            array.add(fieldName22);
        }
        if (!StringUtils.isEmpty(fieldName23)) {
            whereStr.append(" and fieldName23=?");
            array.add(fieldName23);
        }
        if (!StringUtils.isEmpty(fieldName24)) {
            whereStr.append(" and fieldName24 like ?");
            array.add("%" + fieldName24 + "%");
        }
        if (!StringUtils.isEmpty(fieldName25)) {
            whereStr.append(" and fieldName25=?");
            array.add(fieldName25);
        }
        if (!StringUtils.isEmpty(fieldName26)) {
            whereStr.append(" and fieldName26=?");
            array.add(fieldName26);
        }
        if (!StringUtils.isEmpty(fieldName1) && !StringUtils.isEmpty(fieldName1)) {
            whereStr.append(" and DATE_FORMAT(fieldName1, '%Y-%m-%d') >= date ? and DATE_FORMAT(fieldName1, '%Y-%m-%d') <= date ?");
            array.add(fieldName1);
            array.add(fieldName1);
        }
        String msg = "查询成功";
        List<OfflineExcelVO> list = null;

        /*if (fieldName24!=null&&!fieldName24.equals("")) {
            if (fieldName24.equals("收")||fieldName24.equals("已收")||fieldName24.equals("未收")) {
                list = excelService.selectOffFeeExcel(whereStr.toString(), array.toArray());

            }else if(fieldName24.equals("付")||fieldName24.equals("已付")||fieldName24.equals("未付")){
                list = excelService.selectOffFeeExcelCheck(whereStr.toString(), array.toArray());

            }
        }else{
            list = excelService.selectOffFeeExcel(whereStr.toString(), array.toArray());
            List<OfflineExcelVO>   lists = excelService.selectOffFeeExcelCheck(whereStr.toString(), array.toArray());
            lists.addAll(list);
        }*/
        list = excelService.selectOffFeeExcel(whereStr.toString(), array.toArray());
        try {
            //获取待写入excel数据
            if (list.size() == 0) {
                return null;
            }
            //写入excel filename=是生成的文件名
            ExcelUtil<OfflineExcelVO> excelUtil = new ExcelUtil<>(OfflineExcelVO.class);
            excelUtil.exportExcel(list, "exportExcel.xls", resp);

            return null;
        } catch (Exception e) {
            logger.error("XXXX统计失败！", e);
            return null;
        }

    }

    /**
     * 删除导入结算信息
     *
     * @param records
     * @return
     */
    @PostMapping(value = "/delete")
    public HttpResult delete(@RequestBody List<OfflineExcelVO> records) {


        return HttpResult.ok(excelService.delete(records));
        /*
        String fieldName24=null;
        for (int i = 0; i <records.size() ; i++) {
             fieldName24 = records.get(i).getFieldName24();
        }
        if (fieldName24.equals("收")||fieldName24.equals("已收")||fieldName24.equals("未收")) {

            return HttpResult.ok(excelService.delete(records));
        }else{
            return HttpResult.ok(excelService.deleteCheck(records));
        }*/
    }

    @PostMapping(value = "/ClaimOfflineExcel")
    public HttpResult ClaimOfflineExcel(@RequestBody List<OfflineExcelVO> record) {
        String msg = "认领成功";
        Integer integer = null;
        for (int i = 0; i < record.size(); i++) {
            OfflineExcelVO offlineExcelVO = record.get(i);
            integer = excelService.ClaimOfflineExcel(offlineExcelVO);
        }
        return HttpResult.ok(msg, integer);

    }

    /**
     * 修改结算信息
     *
     * @param records
     * @return
     */
    @PostMapping(value = "/update")
    public HttpResult update(@RequestBody OfflineExcelVO records) {
        String fieldName24 = records.getFieldName24();
        Integer result = 0;
        result = excelService.updateOff(records);
        /*if (fieldName24.equals("收")||fieldName24.equals("已收")||fieldName24.equals("未收")){
             result = excelService.updateOff(records);
        }else{
            result = excelService.updateOffCheck(records);
        }*/

        return HttpResult.ok("保存成功", result);
    }

    /**
     * 批量修改--应收改实收、应付改实付
     * policynos 保单号数组，flag；1更新实收fieldName24，2更新实付fieldName26
     *
     * @param records
     * @return
     */
    @PostMapping(value = "/batchUpdate")
    public HttpResult batchUpdate(@RequestBody String policynos, String flag) {
        String[] plys = policynos.split(",");
        try {
            excelService.batchUpdate(plys, flag);
        } catch (Exception e) {
            return HttpResult.error(e.getMessage());
        }

        return HttpResult.ok("结算成功");
    }

    /**
     * 批量修改--对接人、业务员
     * policynos 保单号数组，flag；1更新实收fieldName24，2更新实付fieldName26
     *
     * @param records
     * @return
     */
    @PostMapping(value = "/batchUpdate_other")
    public HttpResult batchUpdate_other(@RequestBody PageRequest pageRequest) {

        String policynos = getColumnFilterValue(pageRequest, "fieldName5");
        String fieldName3 = getColumnFilterValue(pageRequest, "fieldName2");
        String fieldName6 = getColumnFilterValue(pageRequest, "fieldName6");
        String fieldName11 = getColumnFilterValue(pageRequest, "fieldName11");
        String fieldName12 = getColumnFilterValue(pageRequest, "fieldName12");
        String fieldName17 = getColumnFilterValue(pageRequest, "fieldName17");
        String fieldName18 = getColumnFilterValue(pageRequest, "fieldName18");

        String[] plys = policynos.split(",");
        try {
            excelService.batchUpdate_other(plys, fieldName3, fieldName6, fieldName11, fieldName12, fieldName17, fieldName18);
        } catch (Exception e) {
            return HttpResult.error(e.getMessage());
        }

        return HttpResult.ok("结算成功");
    }

    /**
     * 首先上传文件模块
     */
    @RequestMapping(value = "/querysettlement", method = RequestMethod.POST)
    public HttpResult querysettlement(@RequestBody PageRequest pageRequest, HttpServletResponse resp) {

        int pageNum = pageRequest.getPageNum();
        int pageSize = pageRequest.getPageSize();
//增加保险公司 inscompany，车牌号 licenseNo，险种 riskCode，协议类型 agreetype，协议号 agreeid，日期筛选 startdate、enddate，工号 userid，姓名 username，机构 deptid
        String fieldName1 = getColumnFilterValue(pageRequest, "fieldName1"); // 保单号
        String fieldName2 = getColumnFilterValue(pageRequest, "fieldName2"); //签单日期
        String fieldName3 = getColumnFilterValue(pageRequest, "fieldName3"); // 车牌号
        String fieldName4 = getColumnFilterValue(pageRequest, "fieldName4"); // 险种
        String fieldName5 = getColumnFilterValue(pageRequest, "fieldName5"); // 险种
        String fieldName6 = getColumnFilterValue(pageRequest, "fieldName6"); // 签单保费
        String fieldName7 = getColumnFilterValue(pageRequest, "fieldName7"); // 手续费比例
        String fieldName8 = getColumnFilterValue(pageRequest, "fieldName8"); // 导入时间
        String fieldName9 = getColumnFilterValue(pageRequest, "fieldName9"); // 是否领取

        String fieldName10 = getColumnFilterValue(pageRequest, "fieldName10"); // 开始时间
        String fieldName11 = getColumnFilterValue(pageRequest, "fieldName11"); // 截止时间
        String fieldName12 = getColumnFilterValue(pageRequest, "fieldName12");
        String fieldName13 = getColumnFilterValue(pageRequest, "fieldName13");
        String fieldName14 = getColumnFilterValue(pageRequest, "fieldName14");
        String fieldName15 = getColumnFilterValue(pageRequest, "fieldName15");
        String fieldName16 = getColumnFilterValue(pageRequest, "fieldName16");
        String fieldName17 = getColumnFilterValue(pageRequest, "fieldName17");
        String fieldName18 = getColumnFilterValue(pageRequest, "fieldName18");
        String fieldName19 = getColumnFilterValue(pageRequest, "fieldName19");
        String fieldName20 = getColumnFilterValue(pageRequest, "fieldName20");
        String fieldName21 = getColumnFilterValue(pageRequest, "fieldName21");
        String fieldName22 = getColumnFilterValue(pageRequest, "fieldName22");
        String fieldName23 = getColumnFilterValue(pageRequest, "fieldName23");
        String fieldName24 = getColumnFilterValue(pageRequest, "fieldName24");
        String fieldName25 = getColumnFilterValue(pageRequest, "fieldName25");

        StringBuffer whereStr = new StringBuffer(" where 1=1");
        ArrayList array = new ArrayList();

        String msg = "查询成功";
        PageResult pageResult = excelService.selectPageOfflineExcel(pageNum, pageSize, whereStr.toString(), array.toArray());

        return HttpResult.ok(pageResult);

    }

    /**
     * 对账功能统计图表信息查询
     */
    @RequestMapping(value = "/tongJiOffline", method = RequestMethod.POST)
    public HttpResult tongJiOffline(@RequestBody PageRequest pageRequest, HttpServletResponse resp) {

        String fieldName1_st = getColumnFilterValue(pageRequest, "fieldName1_st"); // 日期
        String fieldName1_en = getColumnFilterValue(pageRequest, "fieldName1_en"); // 日期

        String fieldName2 = getColumnFilterValue(pageRequest, "fieldName2"); //保险公司


        StringBuffer whereStr = new StringBuffer(" where 1=1");
        ArrayList array = new ArrayList();
        if (StringUtils.isEmpty(fieldName1_st) || StringUtils.isEmpty(fieldName1_en)) {
            return HttpResult.error("fieldName1_st、fieldName1_en 不能为空！");
        }

        if (!StringUtils.isEmpty(fieldName1_st) && !StringUtils.isEmpty(fieldName1_en)) {
            whereStr.append(" and DATE_FORMAT(fieldName1, '%Y-%m-%d') >= date ? and DATE_FORMAT(fieldName1, '%Y-%m-%d') <= date ?");
            for (int i = 0; i < 4; i++) {
                array.add(fieldName1_st);
                array.add(fieldName1_en);
                if (!StringUtils.isEmpty(fieldName2)) {
                    whereStr.append(" and fieldName2=?");
                    array.add(fieldName2);
                }
            }
        }


        String msg = "查询成功";
        PageResult pageResult = excelService.tongJiOffline(whereStr.toString(), array.toArray());
        return HttpResult.ok(pageResult);

    }

}