用友NC65查询凭证明细方法

项目上对接的需求，对方系统需要查询NC65的凭证明细，研究了一番，NCC辅助核算关联还是比较复杂的，终于给解决了，贴代码：

查询凭证明细
package nc.impl.baseapp.sum.servicehandler;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nc.bs.framework.common.NCLocator;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.jdbc.framework.processor.MapProcessor;
import nc.vo.pub.BusinessException;

import com.alibaba.fastjson.JSONObject;

/**
 * 根据业务单据查询凭证
 * @author dingxm
 *
 */
public class QueryVoucherHandler {
	
	public Map<String,Object> execute(JSONObject param){
		Map<String,Object> map = new HashMap<>();
		String pk_bill = param.getString("pk_bill");//业务单据主键
		//1、凭证头查询语句
		StringBuffer sb = new StringBuffer("select a.pk_voucher,c.name orgname,d.name vouchertype,a.year,a.period,a.num,a.prepareddate,")
			.append(" e.user_name prepared,f.user_name casher,g.user_name checker,h.user_name manager")
			.append(" from gl_voucher a inner join fip_relation b on a.pk_voucher = b.des_relationid")
			.append(" left join org_orgs c on a.pk_org = c.pk_org")
			.append(" left join bd_vouchertype d on a.pk_vouchertype = d.pk_vouchertype")
			.append(" left join sm_user e on a.pk_prepared = e.cuserid")
			.append(" left join sm_user f on a.pk_casher = f.cuserid")
			.append(" left join sm_user g on a.pk_checked = g.cuserid")
			.append(" left join sm_user h on a.pk_manager = h.cuserid")
			.append(" where nvl(a.dr,0)=0 and nvl(b.dr,0)=0 and b.src_relationid = '" + pk_bill + "'");
		List<Map<String,Object>> list = queryList(sb);
		if(list==null || list.size()==0){
			map.put("code","100");
			map.put("msg","查询结果为空");
			map.put("result", "");
			return map;
		}
		Map<String,Object> resMap = list.get(0);
		String pk_voucher = resMap.get("pk_voucher").toString();
		//2、凭证分录查询明细
		StringBuffer detailSql = new StringBuffer("select a.assid,a.pk_accasoa,a.detailindex,a.explanation,b.dispname,a.debitamount,a.creditamount")
			.append(" from gl_detail a inner join bd_accasoa b on a.pk_accasoa = b.pk_accasoa")
			.append(" where nvl(a.dr,0)=0 and a.pk_voucher='" + pk_voucher + "'")
			.append(" order by a.detailindex asc");
		
		List<Map<String,Object>> detailList = queryList(detailSql);
		
		//3、查询该凭证分录所有的辅助核算项目及辅助项目对应的数据表
		IUAPQueryBS query = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		StringBuffer condition = new StringBuffer();
		StringBuffer assidCondition = new StringBuffer();
		for(int i=0;i<detailList.size();i++){
			Map<String,Object> tmpMap = detailList.get(i);
			String pk_accasoa = (String) tmpMap.get("pk_accasoa");
			String assid = (String) tmpMap.get("assid");
			if(i==0){
				condition.append("'" + pk_accasoa + "'");
				assidCondition.append("'" + assid + "'");
			}else{
				condition.append(",'" + pk_accasoa + "'");
				assidCondition.append(",'" + assid + "'");
			}
		}
		Map<String,String> freeTableMap = new HashMap<String,String>();
		Map<String,String> freeItemMap = new HashMap<String,String>();
		try {
			StringBuffer qrySql = new StringBuffer(" select d.defaulttablename,a.pk_entity,c.name from bd_accass a")
				.append(" inner join bd_accasoa b on a.pk_accasoa = b.pk_accasoa")
				.append(" inner join bd_accassitem c on a.pk_entity = c.pk_accassitem")
				.append(" left join md_class d on c.classid = d.id")
				.append(" where nvl(a.dr,0)=0 and nvl(b.dr,0)=0")
				.append(" and b.pk_accasoa in (" + condition + ")");
			List<Map<String,String>> tmpList = (List<Map<String, String>>) query.executeQuery(qrySql.toString(), new MapListProcessor());
			for(int i=0;i<tmpList.size();i++){
				freeTableMap.put(tmpList.get(i).get("pk_entity"), tmpList.get(i).get("defaulttablename"));
				freeItemMap.put(tmpList.get(i).get("pk_entity"), tmpList.get(i).get("name"));
			}
		} catch (BusinessException e) {
			e.printStackTrace();
		}
		//4、查询辅助核算的值
		Map<String,Map<String,String>> assValueMap = new HashMap<>();
		try{
			String assidSql = "select freevalueid,typevalue1,typevalue2,typevalue3,typevalue4,typevalue5,typevalue6,typevalue7,typevalue8,typevalue9 from gl_freevalue where freevalueid in (" + assidCondition + ")";
			List<Map<String,String>> assidList = (List<Map<String, String>>) query.executeQuery(assidSql, new MapListProcessor());
			for(int i=0;i<assidList.size();i++){
				Map<String,String> tmpMap = assidList.get(i);
				String assid = tmpMap.get("freevalueid");
				String freevalue1 = tmpMap.get("typevalue1").equals("NN/A")?"":tmpMap.get("typevalue1");
				String freevalue2 = tmpMap.get("typevalue2").equals("NN/A")?"":tmpMap.get("typevalue2");
				String freevalue3 = tmpMap.get("typevalue3").equals("NN/A")?"":tmpMap.get("typevalue3");
				String freevalue4 = tmpMap.get("typevalue4").equals("NN/A")?"":tmpMap.get("typevalue4");
				String freevalue5 = tmpMap.get("typevalue5").equals("NN/A")?"":tmpMap.get("typevalue5");
				String freevalue6 = tmpMap.get("typevalue6").equals("NN/A")?"":tmpMap.get("typevalue6");
				String freevalue7 = tmpMap.get("typevalue7").equals("NN/A")?"":tmpMap.get("typevalue7");
				String freevalue8 = tmpMap.get("typevalue8").equals("NN/A")?"":tmpMap.get("typevalue8");
				String freevalue9 = tmpMap.get("typevalue9").equals("NN/A")?"":tmpMap.get("typevalue9");
				
				Map<String,String> assitemMap = new HashMap<>();
				if(!"".equals(freevalue1)){
					String assItem = freevalue1.substring(0, 20);
					String pkValue = freevalue1.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue2)){
					String assItem = freevalue2.substring(0, 20);
					String pkValue = freevalue2.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue3)){
					String assItem = freevalue3.substring(0, 20);
					String pkValue = freevalue3.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue4)){
					String assItem = freevalue4.substring(0, 20);
					String pkValue = freevalue4.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue5)){
					String assItem = freevalue5.substring(0, 20);
					String pkValue = freevalue5.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue6)){
					String assItem = freevalue6.substring(0, 20);
					String pkValue = freevalue6.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue7)){
					String assItem = freevalue7.substring(0, 20);
					String pkValue = freevalue7.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue8)){
					String assItem = freevalue8.substring(0, 20);
					String pkValue = freevalue8.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				if(!"".equals(freevalue9)){
					String assItem = freevalue9.substring(0, 20);
					String pkValue = freevalue9.substring(20);
					if(!"~".equals(pkValue)){
						String code = getNameOrCode(freeTableMap.get(assItem),pkValue);
						assitemMap.put(assItem, code);
					}
				}
				assValueMap.put(assid, assitemMap);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		for(int i=0;i<detailList.size();i++){
			String assid = (String) detailList.get(i).get("assid");
			List<Map<String,Object>> freeList = new ArrayList<>();
			
			Map<String,String> assValue = assValueMap.get(assid);
			if(assValue==null){
				continue;
			}
			for(String key:assValue.keySet()){
				String name = freeItemMap.get(key);
				String value = assValue.get(key);
				Map<String,Object> freemap = new HashMap<>();
				freemap.put("checktype", name);
				freemap.put("checkvalue", value);
				freeList.add(freemap);
			}			
			detailList.get(i).put("assid", freeList);
		}
		
		resMap.put("detail", detailList);
		
		return resMap;

	}
	
	/**
	 * 查询辅助核算值
	 * @param tableName
	 * @param pkValue
	 * @return
	 */
	public String getNameOrCode(String tableName,String pkValue){
		IUAPQueryBS query = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		String valueField = "code";
		String pkField = "";
		if("bd_project".equals(tableName)){
			valueField = "project_code";
			pkField = "pk_project";
		}else if("org_dept".equals(tableName)){
			valueField = "code";
			pkField = "pk_dept";
		}else if("bd_defdoc".equals(tableName)){
			valueField = "code";
			pkField = "pk_defdoc";
		}else if("bd_customer".equals(tableName)){
			valueField = "code";
			pkField = "pk_customer";
		}else if("bd_psndoc".equals(tableName)){
			valueField = "code";
			pkField = "pk_psndoc";
		}else if("bd_bankdoc".equals(tableName)){
			valueField = "accnum";
			pkField = "pk_bankdoc";
		}else if("bd_cust_supplier".equals(tableName)){
			valueField = "code";
			pkField = "pk_cust_sup";
		}else if("bd_cashflow".equals(tableName)){
			valueField = "code";
			pkField = "pk_cashflow";
		}else if("bd_bankaccsub".equals(tableName)){
			valueField = "code";
			pkField = "pk_bankaccsub";
		}else if("bd_supplier".equals(tableName)){
			valueField = "code";
			pkField = "pk_supplier";
		}
		String querySql = "select " + valueField + " from " + tableName + " where " + pkField + "='" + pkValue + "'";
		try {
			String value = (String) query.executeQuery(querySql, new ColumnProcessor());
			return value;
		} catch (BusinessException e) {
			return "";
		}
	}
	public List<Map<String, Object>> queryList(StringBuffer sql) {

		IUAPQueryBS query = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		List<Map<String, Object>> list = null;
		try {
			list = (List<Map<String, Object>>) query.executeQuery(sql.toString(), new MapListProcessor());
		} catch (BusinessException e) {
			e.printStackTrace();
		}
		return list;
	}
}