项目上对接的需求,对方系统需要查询NC65的凭证明细,研究了一番,NC65辅助核算关联还是比较复杂的,终于给解决了,贴代码:
查询凭证明细
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;
}
}