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 excelUtil = new ExcelUtil<>(InsCompanyFeeExcel.class); List 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 listdate = new ArrayList<>(); List listrs = new ArrayList(); try { if (type != null && type.equals("1")) { List list = ExcelUtils.importExcel(multipartFile, SettlementVO.class); List lists = excelService.select(whereStr.toString(), array.toArray()); logger.info("list大小" + list.size()); logger.info("lists" + lists.size()); List listd = ListUtils.removeAll(list, lists); // } for (int i = 0; i < listd.size(); i++) { excelService.insert(listd.get(i)); } } else { List 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 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> checkExcel(List checkList) { HashMap> checkResult = new HashMap>(); List dbExistList = new ArrayList(); List excelExistList = new ArrayList(); StringBuffer strb = new StringBuffer(""); StringBuffer whereStr = new StringBuffer(" where 1=1"); ArrayList array = new ArrayList(); List selectResult = new ArrayList(); 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 list = ExcelUtils.importOfflineExcel(multipartFile, OfflineExcelVO.class); HashMap> 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 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 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 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 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 records) { return HttpResult.ok(excelService.delete(records)); /* String fieldName24=null; for (int i = 0; i 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); } }