public void exportContractAccountList(HttpServletRequest request, HttpServletResponse response, String ids, Map<String, Object> map, UserInfo user,String conIds) throws Exception {
String userOrgIds = "";
//查询用户所属部门
List<Map<String, Object>> list = sysOrgFacade.getOrgListByUserId(user.getUserId(), user.getYear());
if (EduUtils.isNotEmpty(list)) {
for (Map<String, Object> listMap : list) {
userOrgIds = userOrgIds + listMap.get("ID") + ",";
}
userOrgIds = userOrgIds.substring(0, userOrgIds.length() - 1);
}
//获取明细
List<Map<String, Object>> projectList = tContractDao.findContractAccountList(ids, map, user, userOrgIds,conIds,null);
if(CommonUtil.isNotEmpty(projectList)){
//"SUMPAY", "YERASYSPAY", "PASSMOENY", "PAYFORMOENY"
int i = -1;
int indexNum = 0;
Map<String, Object> countMoney = new HashMap<>();
List<String> conNumList = new ArrayList<>();
BigDecimal jinT = BigDecimal.ZERO;
String tempOrderNo = "";
String contOrderNo = "";
BigDecimal prePayMoney = BigDecimal.ZERO;
for (Map<String, Object> objectMap : projectList) {
//判断是否为仙湖单位
String nodeId = "sid-B8E5A3A2-6180-4853-92FB-DEF2E9FC6F52";
if(CommonUtil.isNotEmpty(objectMap.get("DEPLOY_ID")) && "3700372".equals(objectMap.get("DEPLOY_ID"))){
nodeId = "sid-DF2DCC98-47C5-40C6-9C2E-76AF3C41CF12";
}
if ("1".equals(objectMap.get("FLOW_WHERE") + "")) { //是
nodeId = "usertask3,sid-1D678D99-4779-4763-B4DA-F7F2579A8373";
} else { //不是
nodeId = nodeId + ",usertask4";
}
String sqlTask = "SELECT END_TIME FROM WF_HIS_TASK where PROCESS_INSTANCE_ID = ? AND REVIEWED_RESULT NOT IN (3,4)" +
" AND NODE_ID IN ('"+nodeId.replaceAll(",", "','")+"') ORDER BY END_TIME DESC LIMIT 1";
Map<String, Object> nodeMap = this.findOneMapBySql(sqlTask, objectMap.get("INSTANCE_ID"));
if (CommonUtil.isEmpty(nodeMap) || CommonUtil.isEmpty(nodeMap.get("END_TIME"))) {
continue;
}
/**
* LIST_MONEY 付款金额
*/
objectMap.put("LIST_MONEY", objectMap.get("APPLY_TOTAL_AMOUNTS"));
}
for (Map<String, Object> objectMap : projectList) {
String sumpaySql = "select aai.APPLY_TOTAL_AMOUNTS,aai.PAID_AMOUNTS," +
"IFNULL(if((DATE_FORMAT( AAI.CREATE_TIME, '%Y' )) = (DATE_FORMAT( CURDATE( ) , '%Y')) , 1 , 0), 0) PAYMENT_DATE from acc_account_info aai" +
" LEFT JOIN ACC_ACCOUNT_STATE_INFO flow ON flow.ACCOUNT_ID = aai.ID" +
" LEFT JOIN ACC_ACCOUNT_EXPAND_INFO a on a.ACCOUNT_ID = aai.ID left join T_CG_CONTRACT t " +
" on t.ID = a.CGJ_CONTRACT_ID where t.APPLY_NO =? and aai.BILL_CODE = ? limit 0,1";
//判断合同编号与上一条一致
boolean b = false, applyNoB = false;
if(i < 0 ? true : ((objectMap.get("APPLY_NO") + "").equals(projectList.get(i).get("APPLY_NO") + "") &&
!(objectMap.get("BILL_CODE") + "").equals(projectList.get(i).get("BILL_CODE") + ""))){
b = true;
}
//判断上一条合同是否为同一个
if(i < 0 ? true : (objectMap.get("APPLY_NO") + "").equals(projectList.get(i).get("APPLY_NO") + "")){
applyNoB = true;
}
//获取每条报销单的报账申请金额、报账支付金额
if(CommonUtil.isNotEmpty(objectMap.get("BILL_CODE"))){
Map<String, Object> accountmap = this.findOneMapBySql(sumpaySql,objectMap.get("APPLY_NO"),objectMap.get("BILL_CODE"));
if (CommonUtil.isNotEmpty(accountmap)) {
/**
* BL 付款比例
* JD 付款进度
*/
BigDecimal apply = new BigDecimal(objectMap.get("APPLY_TOTAL_AMOUNTS").toString());
BigDecimal paid = new BigDecimal(objectMap.get("LIST_MONEY") == null ? 0+"" : String.valueOf(objectMap.get("LIST_MONEY") ));
BigDecimal contract = new BigDecimal(objectMap.get("CONTRACT_MONEY").toString());
objectMap.put("BL", paid.divide(contract, 4, BigDecimal.ROUND_HALF_UP).scaleByPowerOfTen(2));
BigDecimal sumMony = new BigDecimal(objectMap.get("LIST_MONEY") == null ? 0+"" : String.valueOf(objectMap.get("LIST_MONEY") ));
BigDecimal jindu = new BigDecimal("0");
if(conNumList.contains(objectMap.get("APPLY_NO")+"")){
jindu = sumMony.divide(contract, 4, BigDecimal.ROUND_HALF_UP).scaleByPowerOfTen(2);
}else {
if(CommonUtil.isNotEmpty(objectMap.get("PRE_PAY_MONEY")+ "") && !(objectMap.get("PRE_PAY_MONEY")+ "").equals("null")){
BigDecimal usePayMoney = new BigDecimal(objectMap.get("PRE_PAY_MONEY")+ "");
prePayMoney = sumMony.add(usePayMoney);
}else {
prePayMoney = sumMony;
}
jindu = prePayMoney.divide(contract, 4, BigDecimal.ROUND_HALF_UP).scaleByPowerOfTen(2);
conNumList.add(objectMap.get("APPLY_NO")+"");
jinT = BigDecimal.ZERO;
}
if(!tempOrderNo.equals(objectMap.get("BILL_CODE")+"")){
jinT = jinT.add(jindu);
tempOrderNo = objectMap.get("BILL_CODE")+"";
}
objectMap.put("JD",jinT);
//是否是第一条数据 ? 去当前数据 : 是否合同为同一个且报销单不同 ? 当前数据加上上一条数据 : 是否为同一个合同 ? 取上一条数据的值 : 取当前数据的值
BigDecimal SUMPAY = (i < 0 ? paid : b ? paid.add(new BigDecimal(projectList.get(i).get("SUMPAY") == null ? 0+"" : String.valueOf(projectList.get(i).get("SUMPAY") ))) :
applyNoB ? new BigDecimal(projectList.get(i).get("SUMPAY")== null ? 0+"" : String.valueOf(projectList.get(i).get("SUMPAY") )) : paid );
objectMap.put("SUMPAY", SUMPAY);
//判断是否是本年
if("1".equals(accountmap.get("PAYMENT_DATE") + "")){
BigDecimal PAYFORMOENY = (i < 0 ? paid : b ? paid.add(new BigDecimal(projectList.get(i).get("PAYFORMOENY") == null ? 0+"" : String.valueOf(projectList.get(i).get("PAYFORMOENY") ))) :
applyNoB ? new BigDecimal(projectList.get(i).get("PAYFORMOENY") == null ? 0+"" : String.valueOf(projectList.get(i).get("PAYFORMOENY") )) : paid);
BigDecimal YERASYSPAY = (i < 0 ? apply : b ? apply.add(new BigDecimal(projectList.get(i).get("YERASYSPAY") == null ? 0+"" : String.valueOf(projectList.get(i).get("YERASYSPAY") ))) :
applyNoB ? new BigDecimal(projectList.get(i).get("YERASYSPAY") == null ? 0+"" : String.valueOf(projectList.get(i).get("YERASYSPAY") )) : apply );
objectMap.put("YERASYSPAY", YERASYSPAY);
objectMap.put("PAYFORMOENY", PAYFORMOENY);
}else{
//判断是否为当前循环第一条数据 ? 为0 : 是否是同一个合同 ? 去上一条数据的值 : 为0
objectMap.put("YERASYSPAY", i < 0 ? 0 : applyNoB ? new BigDecimal(projectList.get(i).get("YERASYSPAY") == null ? 0+"" : String.valueOf(projectList.get(i).get("YERASYSPAY") )) : 0);
objectMap.put("PAYFORMOENY", i < 0 ? 0 : applyNoB ? new BigDecimal(projectList.get(i).get("PAYFORMOENY") == null ? 0+"" : String.valueOf(projectList.get(i).get("PAYFORMOENY") )) : 0);
}
//本年报账审核通过金额(元)
String sumpaySql2 = "select aai.APPLY_TOTAL_AMOUNTS,aai.PAID_AMOUNTS from acc_account_info aai" +
" LEFT JOIN ACC_ACCOUNT_STATE_INFO flow ON flow.ACCOUNT_ID = aai.ID" +
" LEFT JOIN ACC_ACCOUNT_EXPAND_INFO a on a.ACCOUNT_ID = aai.ID left join T_CG_CONTRACT t " +
" on t.ID = a.CGJ_CONTRACT_ID where t.APPLY_NO =? AND flow.IS_FLOW_END_STATE = 'YES' " +
"and IFNULL(if((DATE_FORMAT( AAI.CREATE_TIME, '%Y' )) = (DATE_FORMAT( CURDATE( ), '%Y')), 1, 0), 0) and aai.BILL_CODE = ? limit 0,1";
Map<String, Object> stringObjectMap = this.findOneMapBySql(sumpaySql2, objectMap.get("APPLY_NO"),objectMap.get("BILL_CODE"));
if (CommonUtil.isNotEmpty(stringObjectMap)) {
BigDecimal applyAmounts = new BigDecimal(stringObjectMap.get("APPLY_TOTAL_AMOUNTS").toString());
objectMap.put("PASSMOENY", (i < 0 ? applyAmounts : b ? applyAmounts.add(new BigDecimal(projectList.get(i).get("PASSMOENY")== null ? 0+"" : String.valueOf(projectList.get(i).get("PASSMOENY") ))) :
applyNoB ? new BigDecimal(projectList.get(i).get("PASSMOENY")== null ? 0+"" : String.valueOf(projectList.get(i).get("PASSMOENY") )) : applyAmounts));
} else {
objectMap.put("PASSMOENY", i < 0 ? 0 : applyNoB ? new BigDecimal(projectList.get(i).get("PASSMOENY")== null ? 0+"" : String.valueOf(projectList.get(i).get("PASSMOENY") )) : 0);
}
if (indexNum>0){
if((objectMap.get("APPLY_NO") + "").equals(projectList.get(indexNum).get("APPLY_NO") + "")){
countMoney.put(objectMap.get("APPLY_NO")+"-PASSMOENY", projectList.get(indexNum).get("PASSMOENY"));
countMoney.put(objectMap.get("APPLY_NO")+"-SUMPAY", projectList.get(indexNum).get("SUMPAY"));
countMoney.put(objectMap.get("APPLY_NO")+"-YERASYSPAY", projectList.get(indexNum).get("YERASYSPAY"));
countMoney.put(objectMap.get("APPLY_NO")+"-PAYFORMOENY", projectList.get(indexNum).get("PAYFORMOENY"));
}
}
}
}
i++;
indexNum++;
}
if(CommonUtil.isNotEmpty(countMoney) && countMoney.size()>0){
for (Map<String, Object> objectMap : projectList) {
String contractNo = objectMap.get("APPLY_NO")+"";
for (Map.Entry<String, Object> entry : countMoney.entrySet()) {
String contractKey = entry.getKey();
if(contractKey.contains(contractNo)){
if(contractKey.contains("PASSMOENY")){
objectMap.put("PASSMOENY", entry.getValue());
}
if(contractKey.contains("SUMPAY")){
objectMap.put("SUMPAY", entry.getValue());
}
if(contractKey.contains("YERASYSPAY")){
objectMap.put("YERASYSPAY", entry.getValue());
}
if(contractKey.contains("PAYFORMOENY")){
objectMap.put("PAYFORMOENY", entry.getValue());
}
}
}
if(objectMap.get("PRE_PAY_MONEY")!=null){
//初始已支付
if(objectMap.get("SUMPAY")==null){
objectMap.put("SUMPAY",0);
}
objectMap.put("SUMPAY", new BigDecimal(objectMap.get("SUMPAY")+"").add(new BigDecimal(objectMap.get("PRE_PAY_MONEY")+"")));
}
}
}
}
// Collections.reverse(projectList);
//执行导出操作
exportContractAccountExcel(projectList, request, response);
}
private void exportContractAccountExcel(List<Map<String, Object>> list, HttpServletRequest request, HttpServletResponse response) throws Exception {
int i = 0;
int j = 0;
Map<String, Integer> combineNo = new HashMap<>();
for (Map<String, Object> map : list) {
map.put("XH", ++i);
boolean b = EduUtils.isEmpty(map.get("PAYMENT_DATE"));
//格式化金额
map.put("CONTRACT_MONEY", EduUtils.isEmpty(map.get("CONTRACT_MONEY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("CONTRACT_MONEY") + "")));
map.put("SETTLE_MONEY", EduUtils.isEmpty(map.get("SETTLE_MONEY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("SETTLE_MONEY") + "")));
map.put("USE_PAY_MONEY", EduUtils.isEmpty(map.get("USE_PAY_MONEY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("USE_PAY_MONEY") + "")));
map.put("NO_PAY_MONEY", EduUtils.isEmpty(map.get("NO_PAY_MONEY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("NO_PAY_MONEY") + "")));
map.put("CONTRACT_MAIN_WRITE_MONEY", EduUtils.isEmpty(map.get("CONTRACT_MAIN_WRITE_MONEY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("CONTRACT_MAIN_WRITE_MONEY") + "")));
map.put("APPLY_TOTAL_AMOUNTS", EduUtils.isEmpty(map.get("APPLY_TOTAL_AMOUNTS")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("APPLY_TOTAL_AMOUNTS") + "")));
map.put("TOTAL_AMOUNTS", EduUtils.isEmpty(map.get("TOTAL_AMOUNTS")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("TOTAL_AMOUNTS") + "")));
map.put("LIST_MONEY", b ? "" : EduUtils.isEmpty(map.get("LIST_MONEY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("LIST_MONEY") + "")));
map.put("SUMPAY", EduUtils.isEmpty(map.get("SUMPAY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("SUMPAY") + "")));
map.put("YERASYSPAY", EduUtils.isEmpty(map.get("YERASYSPAY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("YERASYSPAY") + "")));
map.put("PASSMOENY", EduUtils.isEmpty(map.get("PASSMOENY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("PASSMOENY") + "")));
map.put("PAYFORMOENY", EduUtils.isEmpty(map.get("PAYFORMOENY")) ? "" : EduUtils.numFormat(new BigDecimal(map.get("PAYFORMOENY") + "")));
//类型转换
map.put("CONTRACT_CATEGORIES", changeType((map.get("CONTRACT_CATEGORIES") + ""), 5));
map.put("FILLING_TYPE", changeType((map.get("FILLING_TYPE") + ""), 7));
map.put("PROCUREMENT_METHODS", changeType((map.get("PROCUREMENT_METHODS") + ""), 2));
map.put("PROCUREMENT_TYPE", changeType((map.get("PROCUREMENT_TYPE") + ""), 1));
map.put("EVALUATION_STATUS", changeType((map.get("EVALUATION_STATUS") + ""), 8));
map.put("CONTRACT_STATUS", changeType((map.get("CONTRACT_STATUS") + ""), 3));
map.put("FILL_TYPE", changeType((map.get("FILL_TYPE") + ""), 4));
if (CommonUtil.isEmpty(map.get("BELONG_QUOTA_CODE")) || CommonUtil.isEmpty(map.get("BELONG_QUOTA_NAME"))) {
map.put("BELONG_QUOTA_NAME", map.get("BELONG_QUOTA_NAME"));
} else {
map.put("BELONG_QUOTA_NAME", map.get("BELONG_QUOTA_CODE") + "-" + map.get("BELONG_QUOTA_NAME"));
}
if (CommonUtil.isNotEmpty(map.get("BL"))) {
map.put("BL", b ? "": (map.get("BL") + "%"));
}
if (CommonUtil.isNotEmpty(map.get("JD"))) {
map.put("JD", b ? "": (map.get("JD") + "%"));
}
if (combineNo.containsKey(map.get("CONTRACT_NO"))) {
map.put("combine", combineNo.get(map.get("CONTRACT_NO")));
continue;
}
int combine = 0;
for (Map<String, Object> map2 : list) {
if (map.get("CONTRACT_NO").equals(map2.get("CONTRACT_NO"))) {
combineNo.put(map.get("CONTRACT_NO") + "", ++combine);
}
}
map.put("combine", combineNo.get(map.get("CONTRACT_NO")));
// if(map.get("APPLY_NO").equals(map.get("APPLY_NO"))) {
// if (j >=1) {
// map.put("combine", 2);
// } else {
// map.put("combine", 1);
// }
// j++;
// }
if("0.00".equals(map.get("SUMPAY"))){
map.put("SUMPAY","");
}
}
//执行导出
String[] title = {"合同申请单号", "合同编号", "合同名称", "项目名称", "部门名称", "经办人", "合同类别", "采购项目类别"
, "采购方式", "采购类型", "合同金额(元)", "工程结算价（元）", "已报账金额（元）", "未报账金额（元）"
, "已释放金额（元）", "甲方单位", "乙方单位", "企业类型", "履约评价", "业务节点", "填报类型"
, "合同签订日期", "合同开始日期", "合同结束日期", "单据号", "支付指标", "报账金额", "实际付款金额", "付款比例", "付款进度", "付款日期", "累计已支付金额", "本年报账申请金额", "本年报账审核通过金额(元)", "本年已支付金额"};
//字段名
String[] par = {"APPLY_NO", "CONTRACT_NO", "CONTRACT_NAME", "PROJECT_NAME", "APPLY_DEPT_NAME", "AGENT_NAME", "CONTRACT_CATEGORIES", "FILLING_TYPE"
, "PROCUREMENT_METHODS", "PROCUREMENT_TYPE", "CONTRACT_MONEY", "SETTLE_MONEY", "USE_PAY_MONEY", "NO_PAY_MONEY"
, "CONTRACT_MAIN_WRITE_MONEY", "PARTY_A_UNIT_NAME", "PARTY_B_UNIT_NAME", "PARTY_B_ENTERPRISE_TYPE", "EVALUATION_STATUS"
, "CONTRACT_STATUS", "FILL_TYPE", "SIGNING_DATA", "CONTRACT_START_TIME", "CONTRACT_END_TIME", "BILL_CODE", "BELONG_QUOTA_NAME", "TOTAL_AMOUNTS", "LIST_MONEY", "BL", "JD", "PAYMENT_DATE", "SUMPAY", "YERASYSPAY", "PASSMOENY", "PAYFORMOENY"};
Map<String, Object> formatMap = new HashMap<>();
List<String> objects = new ArrayList<>();
objects.add("APPLY_NO_COMBINE");
objects.add("CONTRACT_NO");
objects.add("CONTRACT_NAME");
objects.add("PROJECT_NAME");
objects.add("APPLY_DEPT_NAME");
objects.add("AGENT_NAME");
objects.add("CONTRACT_CATEGORIES");
objects.add("FILLING_TYPE");
objects.add("PROCUREMENT_METHODS");
objects.add("PROCUREMENT_TYPE");
objects.add("CONTRACT_MONEY");
objects.add("SETTLE_MONEY");
objects.add("USE_PAY_MONEY");
objects.add("NO_PAY_MONEY");
objects.add("CONTRACT_MAIN_WRITE_MONEY");
objects.add("PARTY_A_UNIT_NAME");
objects.add("PARTY_B_UNIT_NAME");
objects.add("PARTY_B_ENTERPRISE_TYPE");
objects.add("EVALUATION_STATUS");
objects.add("CONTRACT_STATUS");
objects.add("FILL_TYPE");
objects.add("SIGNING_DATA");
objects.add("CONTRACT_START_TIME");
objects.add("CONTRACT_END_TIME");
objects.add("SUMPAY");
objects.add("YERASYSPAY");
objects.add("PASSMOENY");
objects.add("PAYFORMOENY");
objects.add("BILL_CODE_COMBINE");
objects.add("APPLY_TOTAL_AMOUNTS_COMBINE");
objects.add("LIST_MONEY_COMBINE");
objects.add("BL_COMBINE");
objects.add("JD_COMBINE");
objects.add("PAYMENT_DATE_COMBINE");
ExcelExportUtil.autoCombineField(list,"BILL_CODE","CONTRACT_NO",2);
ExcelExportUtil.autoCombineField(list,"APPLY_NO","CONTRACT_NO",2);
// ExcelExportUtil.autoCombineField(list,"APPLY_TOTAL_AMOUNTS","BILL_CODE",2);
ExcelExportUtil.autoCombineField(list,"LIST_MONEY","BILL_CODE",2);
ExcelExportUtil.autoCombineField(list,"BL","BILL_CODE",2);
ExcelExportUtil.autoCombineField(list,"JD","BILL_CODE",2);
ExcelExportUtil.autoCombineField(list,"PAYMENT_DATE","BILL_CODE",2);
//居右样式字段
formatMap.put("ALIGNMENT_RIGHT", new String[]{"CONTRACT_MONEY", "SETTLE_MONEY", "USE_PAY_MONEY", "NO_PAY_MONEY", "CONTRACT_MAIN_WRITE_MONEY", "TOTAL_AMOUNTS", "LIST_MONEY","SUMPAY", "YERASYSPAY", "PASSMOENY", "PAYFORMOENY"});
//居左样式字段
formatMap.put("ALIGNMENT_LEFT", new String[]{"APPLY_NO", "CONTRACT_NO", "CONTRACT_NAME", "PROJECT_NAME"});
List<Map<String, Object>> headList = new ArrayList<>();
Map<String, Object> head1 = new HashMap<>();
head1.put("CONTENT", "合同台账查询列表");
headList.add(head1);
ExcelExportUtil.exportIo(headList, "", new ArrayList<>(), 35, null, title, par, list, DateUtils.getYear() + "城管局合同台账查询列表数据" + DateUtils.getCurDateYMDHMSS() + ".xls",
true, objects, formatMap, request, response);
}
浙公网安备 33010602011771号