/** * 校验批量退款申请excel的合法性<br> * 如果合法,提交申请成功,生成批量退款号,申请状态为待审批 * * @param realPath * @param isApply true为申请,fasle为提交批量退款 * @param operationUser * @param request * @return */ private Result processRefundFastpayApplyExcel( String realPath, boolean isApply, String applyId, String operator, String operationRemark) { if (!isApply) { // 导入批量退款 realPath = refundFastpayService.getApplyDetailByBatchNo(applyId).get("filepath").toString(); } // TODO Auto-generated method stub Set<String> repetFilter = new HashSet(); // 过滤重复 Workbook book = null; StringBuffer msg = new StringBuffer(); Result result = new Result(); // 2011112421847473^0.01^NOT_THIS_PARTNERS_TRAD int batchNum = 0; StringBuffer batchData = new StringBuffer(); StringBuffer relation = new StringBuffer(); BigDecimal totalRefund = new BigDecimal(0); // 退款总金额 boolean hasError = false; try { book = Workbook.getWorkbook(new File(realPath)); Sheet sheet = book.getSheet(0); int count = sheet.getRows(); // 生成批量退款笔数及退款数据集 if (count <= 2) { // 没有数据 result.setMsg("没有数据!"); return result; } else if (count >= (Integer.valueOf(PropertiesUtil.getProperties("alipay.batchNumLimit", "1000")) .intValue() + 2)) { // 超出最大笔数 result.setMsg( "支付宝即时到账批量退款,最大支持" + PropertiesUtil.getProperties("alipay.batchNumLimit", "1000") + "笔!"); return result; } else { // 校验合法性 for (int i = 1; i < count - 1; i++) { // 最后一行是合计 Cell[] cells = sheet.getRow(i); String outTradeNo = cells[Integer.valueOf(PropertiesUtil.getProperties("outTradeNOIndex", "0"))] .getContents() .trim(); if (!repetFilter.contains(outTradeNo)) { repetFilter.add(outTradeNo); } else { msg.append( "EXCEL第" + i + "行(不包含表头部分)数据-单品订单号[" + outTradeNo + "]在导入文件的前面部分已经出现请合并为一条退款记录!<br/>"); hasError = true; } // 校验money String totalFee = cells[Integer.valueOf(PropertiesUtil.getProperties("totalFeeIndex", "0"))] .getContents() .trim(); totalRefund = totalRefund.add(new BigDecimal(totalFee)); String tradeNo = getTradeNoByOutTradeNo(outTradeNo, new BigDecimal(totalFee)); // if(true){//测试 // result.setSuccess(true); // // tradeNo="1111111111111111111111111111111111111111111111111222222222222222222222222222222222222221"; // } // 校验tradeNo逻辑 if (tradeNo == null) { msg.append("EXCEL第" + i + "行(不包含表头部分)数据-单品订单号[" + outTradeNo + "]获取支付宝交易号失败!<br/>"); hasError = true; } else if (tradeNo.indexOf( PropertiesUtil.getProperties( "alipay.interface.returncode.outoftotalfee", "OUTOFTOTALFEE")) == 0) { msg.append("EXCEL第" + i + "行(不包含表头部分)数据-单品订单号[" + outTradeNo + "]退款总金额大于当前可退款金额!<br/>"); hasError = true; } else { // 拼接relation (商家订单号1^支付宝交易号1#商家订单号2^支付宝交易号2) relation.append(outTradeNo).append("^").append(tradeNo); if (i < count - 2) { relation.append("#"); } } String remark = PropertiesUtil.getProperties("refundRemark", "上品折扣支付宝退款"); // 处理逻辑 batchNum++; batchData.append(tradeNo + "^" + totalFee + "^" + remark); if (i < count - 2) { batchData.append("#"); } } } if (hasError) { result.setMsg(msg.toString()); return result; } boolean validateResult = refundFastpayService.validateRefundFastpay( batchNum, batchData.toString(), relation.toString(), totalRefund); // validateResult = true; if (!validateResult) { result.setMsg("未通过系统验证,可能近一周时间内导入过相同的批量退款数据,请核实!"); result.setSuccess(false); return result; } if (isApply) { // 提交申请 result = refundFastpayService.applyRefundFastpay(realPath, operator, operationRemark); } else { // 审批通过,提交批量退款 result = refundFastpayService.processRefundFastpayExcel( batchNum, batchData.toString(), realPath, relation.toString(), totalRefund, applyId, operator, operationRemark); } } catch (BiffException e) { e.printStackTrace(); result.setMsg( (StringUtils.isNotEmpty(result.getMsg()) ? result.getMsg() : "") + "读取路径为:" + realPath + "的文件产生异常!<br>"); } catch (FileNotFoundException e) { e.printStackTrace(); result.setMsg( (StringUtils.isNotEmpty(result.getMsg()) ? result.getMsg() : "") + "路径为:" + realPath + "的文件不存在!<br>"); } catch (RuntimeException e) { e.printStackTrace(); result.setMsg( (StringUtils.isNotEmpty(result.getMsg()) ? result.getMsg() : "") + "服务端异常,导致导入文件读取失败,请联系开发人员!<br>" + e.getMessage()); } catch (Exception e) { e.printStackTrace(); result.setMsg( (StringUtils.isNotEmpty(result.getMsg()) ? result.getMsg() : "") + "服务端异常,导致导入文件读取失败,请联系开发人员!<br>" + e.getMessage()); } finally { try { book.close(); } catch (Exception e) { } } return result; }