NC65二开单据单据联查追溯功能开发

一、单据元数据字段增加字段

• 新增来源单据主键
• 新增来源单据类型
• 

二、设置单据按钮&&修改单据类型

• 单据增加按钮

<bean id="linkQueryAction" class="nc.ui.so.socontract.ace.action.common.UserDefLinkQueryAction">
	<property name="model"><ref bean="bmModel"/></property>
	<property name="billType" value="HD01"/>
	<property name="exceptionHandler"><ref bean="exceptionHandler" /></property>
</bean>

package nc.ui.so.socontract.ace.action.common;

import nc.ui.pubapp.uif2app.actions.LinkQueryAction;
import nc.ui.scmpub.action.SCMActionInitializer;

public class UserDefLinkQueryAction extends LinkQueryAction {

	public UserDefLinkQueryAction() {
		// TODO Auto-generated constructor stub
		setOpenMode(1);
		SCMActionInitializer.initializeAction(this, "BillLinkQuery");
	}
}

• 设置来源单据数据查询类
  

三、实现类代码:

package nc.vo.so.socontract.linkquery;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import nc.bs.logging.Logger;
import nc.bs.pf.pub.PfDataCache;
import nc.bs.trade.billsource.DefaultDataFinder;
import nc.jdbc.framework.JdbcSession;
import nc.jdbc.framework.PersistenceManager;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.exception.DbException;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.ResultSetProcessor;
import nc.uif.pub.exception.UifRuntimeException;
import nc.vo.pu.pub.util.ArrayUtil;
import nc.vo.pubapp.pattern.pub.SqlBuilder;
import nc.vo.trade.billsource.LightBillVO;


/**
 * 军品销售合同单据联查类
 * @author Think
 *
 */
public class ContractSrcFinder extends DefaultDataFinder {

	@Override
	public LightBillVO[] getForwardBills(String srcBillType,
			String curBillType, String... srcBillID) {
		// TODO Auto-generated method stub
		if(!"30".equals(srcBillType)){
			return super.getForwardBills(srcBillType, curBillType, srcBillID);
		}else{
			SqlBuilder sql = new SqlBuilder();
//			sql.append("SELECT distinct pk_head id,'"+curBillType+"' code FROM  so_socontract_b ");
//			sql.append(" where dr = 0 ");
//			// and csrcid = '"+srcBillID+"' and  csrctype='"+srcBillType+"'"
//			sql.append(" and  csrcid ",srcBillID);
//			//sql.append(" and csrctype ",srcBillType);
			String pk= PfDataCache.getBillTypeInfo(curBillType).getPk_billtypeid();
			sql.append("SELECT distinct t1.pk_head id,t1.vbillcode code,t1.pk_billtypecode type,'"+pk+"' transtypepk,t2.csrcid sourceID  FROM SO_SOCONTRACT_H t1 ");
			sql.append("LEFT JOIN SO_SOCONTRACT_B t2 on t1.PK_HEAD = t2.PK_HEAD");
			sql.append(" WHERE t1.dr = 0 And t2.dr =0 ");
			sql.append(" and t2.csrcid ",srcBillID);
			
			
			
		    PersistenceManager sessionManager = null;
		    try {
		      sessionManager = PersistenceManager.getInstance();
		      JdbcSession session = sessionManager.getJdbcSession();

		      ArrayList result =
		          (ArrayList) session.executeQuery(sql.toString(),
		              new BeanListProcessor(LightBillVO.class));
		      if (result.size() == 0) {
		        return null;
		      }
		      else {
		        return (LightBillVO[]) result.toArray(new LightBillVO[result.size()]);
		      }

		    }
		    catch (DbException e) {
		      Logger.error(e.getMessage(), e);
		      throw new UifRuntimeException("getForwardBills error");
		    }
		    finally {
		      sessionManager.release();
		    }
		}
		
	}
	
	public LightBillVO[] getSoContractSourceBills(String curBillType, String curBillID){
		SqlBuilder sqlbuilder = new SqlBuilder();
		sqlbuilder.append("select * from so_saleorder where csaleorderid in ( ");
		sqlbuilder.append(" select csrcid from so_socontract_b where dr = 0 and pk_head  =  ? ");
		sqlbuilder.append(" ) and dr = 0 ");
		PersistenceManager sessionManager = null;
		 try {
			 sessionManager = PersistenceManager.getInstance();
		      JdbcSession session = sessionManager.getJdbcSession();
		      SQLParameter para = new SQLParameter();
		      para.addParam(curBillID);
		      ResultSetProcessor p = new ResultSetProcessor(){

				@Override
				public Object handleResultSet(ResultSet rs)
						throws SQLException {
					// TODO Auto-generated method stub
					  ArrayList al = new ArrayList();
					  while(rs.next()){
						  String id = rs.getString("csaleorderid");
						  String code = rs.getString("vbillcode");
						  if (id != null && id.trim().length() > 0) {
							  LightBillVO svo = new LightBillVO();
							  svo.setType("30");
				              svo.setID(id);
				              svo.setCode(code);
				              al.add(svo);
						  }
					  }
					  return al;
				}
		    	  
		      };
		      ArrayList<LightBillVO> result =
		              (ArrayList<LightBillVO>) session.executeQuery(sqlbuilder.toString(),
		                  para, p);
		          if (result.size() == 0) {
		            return null;
		          }
		          // 增补上游单据号
		          for (LightBillVO vo : result) {
		            List<String> info = this.getBillCodeAndCorp(vo.getType(), vo.getID());
		            if (info != null) {
		              vo.setCode(info.get(0));
		              vo.setCorp(info.get(1));
		            }
		          }
		          return result.toArray(new nc.vo.trade.billsource.LightBillVO[result
		                                                                       .size()]);
		 }catch (DbException e) {
		      Logger.error(e.getMessage(), e);
		      throw new UifRuntimeException(e.getMessage());
		    }
		    finally {
		      sessionManager.release();
		    }
	}
	
	
	@Override
	public LightBillVO[] getSourceBills(String curBillType, String curBillID) {
		// TODO Auto-generated method stub
		//return super.getSourceBills(curBillType, curBillID);
		LightBillVO[] source = super.getSourceBills(curBillType, curBillID);
		LightBillVO[] sosource = this.getSoContractSourceBills(curBillType, curBillID);
		return ArrayUtil.combinArrays(source,sosource);
	}
}

package nc.bs.trade.billsource;

/**
 * 默认的单据来源查找算法。
 * 创建日期：(2004-6-14 13:10:40)
 * @author：樊冠军1
 */
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import nc.bs.logging.Logger;
import nc.bs.mw.sqltrans.TempTable;
import nc.bs.pf.pub.BillTypeCacheKey;
import nc.bs.pf.pub.PfDataCache;
import nc.jdbc.framework.JdbcSession;
import nc.jdbc.framework.PersistenceManager;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.exception.DbException;
import nc.jdbc.framework.processor.ArrayProcessor;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.BeanProcessor;
import nc.jdbc.framework.processor.ResultSetProcessor;
import nc.uif.pub.exception.UifRuntimeException;
import nc.vo.jcom.lang.StringUtil;
import nc.vo.pf.change.PfUtilBaseTools;
import nc.vo.pub.BusinessException;
import nc.vo.pub.billtype.BilltypeVO;
import nc.vo.pub.forwardbilltype.ForwardBillTypeVO;
import nc.vo.trade.billsource.LightBillRowVO;
import nc.vo.trade.billsource.LightBillVO;

public class DefaultDataFinder implements IBillDataFinder {

	private String tempTableName = "";
	
	/**
	 * 功能描述:创建查询语句;在该类型的单据中查找某种类型单据的来源单据: 
	 * 
	 * 需要本单据描述：子表表名、子表外键字段名、来源单据类型字段名、来源单据ID字段名
	 *
	 */
	
	private IBillFlowFactory billFlowFactory = null;
	
	public DefaultDataFinder() {
		this(new DefaultBillFlowFactory());
	}

	public DefaultDataFinder(IBillFlowFactory billFlowFactory) {
		super();
		this.billFlowFactory = billFlowFactory;
	}

	protected String createSQL(String billType) {
		
		IBillFlow billflow = getBillFlow(billType);
		if (billflow == null) {
			nc.bs.logging.Logger.error("单据类型:" + billType + "没有找到单据流程信息！");
			return null;
		}	

		if(billflow.getSubTableName() == null)
			return null;
		
		String bTable = billflow.getSubTableName();
		String bFkField = billflow.getSubTableForeignKeyFiled();
		String bTableSourceTypeField = billflow.getSourceTypeField();
		String bTableSourceIDField = billflow.getSourceIDField();
		
		if (bTableSourceTypeField==null || bTableSourceIDField==null)
			return null;
		
		StringBuffer sb = new StringBuffer("SELECT DISTINCT ");
		sb.append(bTableSourceTypeField).append(",")
				.append(bTableSourceIDField);
		sb.append(" FROM ").append(bTable);

		sb.append(" WHERE");
		sb.append(" ");
		sb.append(bFkField);

		sb.append("=?");
		sb.append(" and dr=0 ");

		return sb.toString();
	}

	/**
	 * 功能描述:创建查询语句;在该类型的单据中查找某种类型单据的后续单据 
	 * 
	 * 输入参数:String billType,单据类型.
	 * 
	 * 返回值: SQL语句
	 * 
	 * 主表表名、主表主键字段名、主表单据号字段名、子表表名、子表外键字段名、单据来源类型字段名、来源单据ID字段名
	 * @param srcBillID 
	 * 
	 */
	protected String createSQL1(String curBillType, String... srcBillID) {

		IBillFlow billflow = getBillFlow(curBillType);
		if (billflow == null) {
			nc.bs.logging.Logger.error("单据类型:" + curBillType + "没有找到单据流程信息！");
			return null;
		}	
		String hTable = billflow.getMainTableName();
		String hPkField = billflow.getMainTablePrimaryKeyFiled();
		String hPkCorp = billflow.getBillCorp();
		String hBillCodeField = billflow.getBillNOField();
		
		String hBillTypeField = billflow.getBillTypeField();
		String hTransTypeField = billflow.getTransTypeField();
		String hTransTypePkField = billflow.getTransTypePkField();
		
		String bTable = billflow.getSubTableName();
		String bFkField = billflow.getSubTableForeignKeyFiled();
//		String bTableSourceTypeField = billflow.getSourceTypeField();
		String bTableSourceIDField = billflow.getSourceIDField();

		// 如果单据没有来源单据类型字段,则返回空.即如果该类型单据没有标识来源单据
		// 类型,就无法定位它是否是后单据.
		// 通常该情况是:它是某种固定类型单据的后续单据.
		if (bTableSourceIDField == null)
			return null;

		// 在该类型的单据中查找某种类型单据的后续单据
		StringBuffer sb = new StringBuffer("SELECT DISTINCT ");
		sb.append(" ");
		sb.append(hTable + "." + hPkField);
		sb.append(" id, ");
		sb.append(hTable + "." + hPkCorp);
		sb.append(" corp, ");
		sb.append(hTable + "." + hBillCodeField);
		sb.append(" code ");
		
		if(!StringUtil.isEmptyWithTrim(hBillTypeField))
		{
			sb.append( "," + hTable + "." + hBillTypeField);
			sb.append(" type");
		}
		if(!StringUtil.isEmptyWithTrim(hTransTypeField))
		{
			sb.append(", ");
			sb.append(hTable + "." + hTransTypeField);
			sb.append(" transtype ");
		}
		if(!StringUtil.isEmptyWithTrim(hTransTypePkField))
		{
			sb.append(", ");
			sb.append(hTable + "." + hTransTypePkField);
			sb.append(" transtypepk ");
		}
		{
			sb.append(", ");
			if (hTable.equalsIgnoreCase(bTable)) {
				sb.append("B" + "." + bTableSourceIDField);
			} else {
				sb.append(bTable + "." + bTableSourceIDField);
			}
			sb.append(" sourceID ");
		}
		sb.append(" ");
		sb.append("FROM");
		sb.append(" ");
		sb.append(hTable);
		sb.append(", ");
		if (hTable.equalsIgnoreCase(bTable)) {
			sb.append(bTable + " B");
		} else {
			sb.append(bTable);
		}
		sb.append(" ");
		sb.append("WHERE");
		sb.append(" ");
		sb.append(hTable + "." + hPkField);
		sb.append("=");

		if (hTable.equalsIgnoreCase(bTable)) {
			sb.append("B" + "." + bFkField);
		} else {
			sb.append(bTable + "." + bFkField);
		}

		sb.append(" and ");
		if (hTable.equalsIgnoreCase(bTable)) {
			sb.append("B" + "." + bTableSourceIDField);
		} else {
			sb.append(bTable + "." + bTableSourceIDField);
		}
		sb.append(" in(");

	    // 构造来源单据主键参数
	    for (String id : srcBillID) {
	    	sb.append("'");
	    	sb.append(id);
	    	sb.append("'");
	    	sb.append(",");
	    }
	    sb.deleteCharAt(sb.length() - 1);
	    sb.append(")");

//		if (hTableBillTypeField != null) {
//			sb.append(" and ");
//			sb.append(hTable + "." + hTableBillTypeField);
//			sb.append("=?");
//		}

		sb.append(" and ");
		sb.append(hTable + ".dr =0");
		sb.append(" and ");

		if (hTable.equalsIgnoreCase(bTable)) {
			sb.append("B" + ".dr =0");
		} else {
			sb.append(bTable + ".dr =0");
		}

		return sb.toString();
	}

	/**
	 * 查找单据号和公司。 功能描述: 输入参数: 返回值: 异常处理: 日期:
	 * 
	 * @return java.lang.String
	 * @param billType
	 *            java.lang.String
	 */
	private String createSQL2(String billType) {

		IBillFlow billflow = getBillFlow(billType);
		if (billflow == null) {
			nc.bs.logging.Logger.error("单据类型:" + billType + "没有找到单据流程信息！");
			return null;
		}	

		String hTable = billflow.getMainTableName();
		String hPkField = billflow.getMainTablePrimaryKeyFiled();
		String hTableCodeField = billflow.getBillNOField();
		String hTableCorpField = billflow.getBillCorp();
		
		String hBillTypeField = billflow.getBillTypeField();
		String hTransTypeField = billflow.getTransTypeField();
		String hTransTypePkField = billflow.getTransTypePkField();

		StringBuffer sb = new StringBuffer("SELECT");
		sb.append(" ");
		sb.append(hTableCodeField + " code");
		sb.append(","); 
		sb.append(hTableCorpField + " corp");
		if(!StringUtil.isEmptyWithTrim(hBillTypeField))
		{
			sb.append(",");
			sb.append(hBillTypeField + " billtype");
		}
		if(!StringUtil.isEmptyWithTrim(hTransTypeField))
		{
			sb.append(",");
			sb.append(hTransTypeField + " transtype");
		}
		if(!StringUtil.isEmptyWithTrim(hTransTypePkField))
		{
			sb.append(",");
			sb.append(hTransTypePkField + " transtypepk");
		}
		sb.append(" ");
		sb.append("FROM");
		sb.append(" ");
		sb.append(hTable);
		sb.append(" ");
		sb.append("WHERE");
		sb.append(" ");
		sb.append(hPkField);
		sb.append("=?");

		return sb.toString();
	}
	
	protected String createSQLToFindRow(String billType, String[] rows) {
		
		IBillFlow billflow = getBillFlow(billType);
		if (billflow == null) {
			nc.bs.logging.Logger.error("单据类型:" + billType + "没有找到单据流程信息！");
			return null;
		}	

		if(billflow.getSubTableName() == null || billflow.getSubTableRowNoField() == null)
			return null;
		
		String bTable = billflow.getSubTableName();
		String rowNo = billflow.getSubTableRowNoField();
		String srcRowNo = billflow.getSourceRowField();
		String bFkField = billflow.getSubTableForeignKeyFiled();
		String bTableSourceTypeField = billflow.getSourceTypeField();
		String bTableSourceIDField = billflow.getSourceIDField();
		
		if (bTableSourceTypeField==null || bTableSourceIDField==null)
			return null;
		
		StringBuffer sb = new StringBuffer("SELECT DISTINCT ");
		sb.append(rowNo).append(" rowNo,").append(bTableSourceTypeField).append(" srcBillType,")
		   .append(bTableSourceIDField).append(" srcBillID,").append(srcRowNo).append(" srcRowNo");
		sb.append(" FROM ").append(bTable);

		sb.append(" WHERE");
		sb.append(" ");
		sb.append(bFkField);

		sb.append("=?  and ");
		sb.append(rowNo);
		sb.append(" is not null");
		sb.append(" and dr=0 ");

		if (rows != null) {
			sb.append(" and ");
			this.append(sb, rowNo, rows);
		}

		return sb.toString();
	}
	
	/**
	 * 对于String数组值构造in条件
	 * 
	 * @param name sql字段名
	 * @param values String数组值
	 */
	public void append(StringBuffer sb, String name, String[] values) {
		int length = values.length;
		if (length == 1) {
			sb.append(name);
			sb.append("='");
			sb.append(values[0]);
			sb.append("' ");
			return;
		}
		sb.append(name);
		sb.append(" in (");
		for (int i = 0; i < length; i++) {
			sb.append("'");
			sb.append(values[i]);
			sb.append("'");
			sb.append(",");
		}
		length = sb.length();
		sb.deleteCharAt(length - 1);
		sb.append(") ");
	}
	
	protected String createSQLToFindRowByTempTable(String billType, String[] rows) {
		
		IBillFlow billflow = getBillFlow(billType);
		if (billflow == null) {
			nc.bs.logging.Logger.error("单据类型:" + billType + "没有找到单据流程信息！");
			return null;
		}	

		if(billflow.getSubTableName() == null || billflow.getSubTableRowNoField() == null)
			return null;
		
		String bTable = billflow.getSubTableName();
		String rowNo = billflow.getSubTableRowNoField();
		String srcRowNo = billflow.getSourceRowField();
		String bFkField = billflow.getSubTableForeignKeyFiled();
		String bTableSourceTypeField = billflow.getSourceTypeField();
		String bTableSourceIDField = billflow.getSourceIDField();
		
		if (bTableSourceTypeField==null || bTableSourceIDField==null)
			return null;
		
		StringBuffer sb = new StringBuffer("SELECT DISTINCT ");
		sb.append(rowNo).append(" rowNo,").append(bTableSourceTypeField).append(" srcBillType,")
		   .append(bTableSourceIDField).append(" srcBillID,").append(srcRowNo).append(" srcRowNo");
		sb.append(" FROM ").append(bTable);

		sb.append(" WHERE");
		sb.append(" ");
		sb.append(bFkField);

		sb.append("=?  and ");
		sb.append(rowNo);
		sb.append(" is not null");
		sb.append(" and dr=0 ");

		if (rows != null) {
			sb.append(" and ");
			this.appendByTempTable(sb, rowNo, rows);
		}

		return sb.toString();
	}
	
	/**
	 * 对于String数组值构造in条件
	 * 
	 * @param name sql字段名
	 * @param values String数组值
	 */
	public void appendByTempTable(StringBuffer sb, String name, String[] values) {
		int length = values.length;
		if (length == 1) {
			sb.append(name);
			sb.append("='");
			sb.append(values[0]);
			sb.append("' ");
			return;
		}
		sb.append(name);
		sb.append(" in (");
		sb.append(" select pk from " + tempTableName + " ");
//		for (int i = 0; i < length; i++) {
//			sb.append("'");
//			sb.append(values[i]);
//			sb.append("'");
//			sb.append(",");
//		}
//		length = sb.length();
//		sb.deleteCharAt(length - 1);
		sb.append(") ");
	}
	
	
	/**
	 * 进行查询单据编码和公司。
	 * 
	 * @return nc.vo.scm.sourcebill.LightBillVO[]
	 * @param curBillType
	 *            java.lang.String
	 * @param curBillID
	 *            java.lang.String
	 */
	protected BillTypeInfo getBillCodeAndCorp_V61(String curBillType, String curBillID) {
		
		
		String sql = createSQL2(curBillType);
		if (sql == null)
			return null;
		PersistenceManager sessionManager = null;
		try {
			sessionManager = PersistenceManager.getInstance();
			JdbcSession session = sessionManager.getJdbcSession();
			SQLParameter para = new SQLParameter();
			para.addParam(curBillID);
			
			BillTypeInfo r = (BillTypeInfo) session.executeQuery(sql,para, new BeanProcessor(BillTypeInfo.class));
			return r;
			
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
		} finally {
			sessionManager.release();
		}
		return null;
	}

	/**
	 * 进行查询单据编码和公司。
	 * 
	 * 
	 * 
	 * @return nc.vo.scm.sourcebill.LightBillVO[]
	 * @param curBillType
	 *            java.lang.String
	 * @param curBillID
	 *            java.lang.String
	 * 
	 */
	protected List<String> getBillCodeAndCorp(String curBillType, String curBillID) {
		
		
		String sql = createSQL2(curBillType);
		if (sql == null)
			return null;

		List<String> codeandcorp = null;
		PersistenceManager sessionManager = null;
		try {
			sessionManager = PersistenceManager.getInstance();
			JdbcSession session = sessionManager.getJdbcSession();
			SQLParameter para = new SQLParameter();
			para.addParam(curBillID);
			
			Object[] r = (Object[]) session.executeQuery(sql,para, new ArrayProcessor());
			if(r != null) {
				codeandcorp = new ArrayList<String>();
				codeandcorp.add((String)r[0]);
				codeandcorp.add((String)r[1]);
			}
			
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
		} finally {
			sessionManager.release();
		}
		return codeandcorp;
	}

	/*
	 * 功能:根据当前的单据ID,单据类型,获得指定类型的后续单据.
	 * 返回:LightBillVO[],后续单据VO数组,至少要填写LightBillVO的ID,TYPE,CODE三个属性.
	 * 其中TYPE属性就是forwardBillTYPE的参数值 参数: 1.String curBillType :当前单据类型 2.String
	 * curBillID:当前单据ID 3.String forwardBillType:后续单据的类型
	 * 
	 */
	@SuppressWarnings( { "unchecked", "serial" })
	public nc.vo.trade.billsource.LightBillVO[] getForwardBills(String srcBillType, String curBillType, String... srcBillID) {
		
		String sql = createSQL1(curBillType, srcBillID);
		if(sql == null)
			return null;
		
		PersistenceManager sessionManager = null;
		try {
			sessionManager = PersistenceManager.getInstance();
			JdbcSession session = sessionManager.getJdbcSession();
			
			ArrayList result = (ArrayList) session.executeQuery(sql, new BeanListProcessor(LightBillVO.class));
			if (result.size() == 0)
				return null;
			else
				return (LightBillVO[]) result.toArray(new LightBillVO[result.size()]);

		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
			throw new UifRuntimeException("getForwardBills error");
		} finally {
			sessionManager.release();
		}
	}
	
	/*
	 * 功能:根据当前的单据ID,单据类型,获得所有的来源单据
	 * 返回:LightBillVO[],来源单据VO数组,至少要填写LightBillVO的ID,TYPE,CODE三个属性. 参数: 1.String
	 * curBillType :当前单据类型 2.String curBillID:当前单据ID
	 * 
	 */
	@SuppressWarnings( { "serial", "unchecked" })
	public nc.vo.trade.billsource.LightBillVO[] getSourceBills(
			String curBillType, String curBillID) {
		String sql = createSQL(curBillType);
		if (sql == null)
			return null;
		PersistenceManager sessionManager = null;
		try {
			sessionManager = PersistenceManager.getInstance();
			JdbcSession session = sessionManager.getJdbcSession();
			SQLParameter para = new SQLParameter();
			para.addParam(curBillID);

			ResultSetProcessor p = new ResultSetProcessor() {
				@SuppressWarnings("unchecked")
				public Object handleResultSet(ResultSet rs) throws SQLException {
					ArrayList al = new ArrayList();
					while (rs.next()) {
						String type = rs.getString(1);
						String id = rs.getString(2);
						if (type != null && id != null
								&& type.trim().length() > 0
								&& id.trim().length() > 0) {
							LightBillVO svo = new LightBillVO();
							svo.setType(type);
							svo.setID(id);
							al.add(svo);
						}
					}
					return al;
				}
			};
			ArrayList<LightBillVO> result = (ArrayList<LightBillVO>) session
					.executeQuery(sql, para, p);
			if (result.size() == 0)
				return null;
			// 增补上游单据号
			for (LightBillVO vo : result) {
				BillTypeInfo info = getBillCodeAndCorp_V61(vo.getType(), vo.getID());
				if(info != null)
				{
					vo.setCode(info.getCode());
					vo.setCorp(info.getCorp());
					vo.setType(info.getBilltype());
					vo.setTranstype(info.getTranstype());
					vo.setTranstypepk(info.getTranstypepk());
				}
			}
			return (nc.vo.trade.billsource.LightBillVO[]) result
					.toArray(new nc.vo.trade.billsource.LightBillVO[result
							.size()]);
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
			throw new UifRuntimeException(e.getMessage());
		} finally {
			sessionManager.release();
		}

	}
	

	@Override
	public String[] getForwardBillTypes(String billType) throws BusinessException {
		LightBillVO vo = new LightBillVO();
		vo.setType(billType);
		return getForwardBillTypes(vo);
	}

	public String[] getForwardBillTypes(LightBillVO vo)	throws BusinessException {
		BilltypeVO billtypevo = PfDataCache.getBillTypeInfo(new BillTypeCacheKey().buildBilltype(vo.getType()));
		if(billtypevo == null)
			return null;
		ForwardBillTypeVO[] forwardBilltypes = billtypevo.getForwardBillTypeVOs();
		HashSet<String> set = new HashSet<String>();
		if(forwardBilltypes!= null) {
			for (ForwardBillTypeVO forwardBillTypeVO : forwardBilltypes) {
				set.add(forwardBillTypeVO.getPk_fwdbilltype());
			}
		}
		return set.toArray(new String[set.size()]);
	}
	
	public 	IBillFlow getBillFlow(String billType) {
		billType = PfUtilBaseTools.getRealBilltype(billType);
		return billFlowFactory.getInstance(billType);
	}
	
	public LightBillRowVO[] getRows(String curBillID, String curBillType, String[] rows){
		if (rows != null && rows.length > 100)
			return getRowsByTempTable(curBillID, curBillType, rows);
		
		String sql = createSQLToFindRow(curBillType, rows); 
		if (sql == null)
			return null;
		
		PersistenceManager sessionManager = null;
		try {
			sessionManager = PersistenceManager.getInstance();
			JdbcSession session = sessionManager.getJdbcSession();
			
			SQLParameter para = new SQLParameter();
			para.addParam(curBillID);
			Object ret = session.executeQuery(sql,para, new BeanListProcessor(LightBillRowVO.class));
			List<LightBillRowVO> rowList = (List<LightBillRowVO>) ret;
			
			return rowList == null ? null : rowList.toArray(new LightBillRowVO[0]);
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
		} finally {
			sessionManager.release();
		}
		return null;
	}
	
	private LightBillRowVO[] getRowsByTempTable(String curBillID,
			String curBillType, String[] rows) {
		String sql = createSQLToFindRowByTempTable(curBillType, rows); 
		if (sql == null)
			return null;
		
		PersistenceManager sessionManager = null;
		try {
			sessionManager = PersistenceManager.getInstance();
			JdbcSession session = sessionManager.getJdbcSession();
			
			//新建临时表
			tempTableName = createTempTable(session, "tempTableName");
			Logger.debug("生成临时表用于主键关联查询,表名->" + tempTableName);
			
			//清空临时表旧数据
			deleteTermpTablePKs(session, tempTableName);
			
			//往临时表插入当前查询需关联的
			insertTempTablePKs(session, tempTableName, rows);
			
			SQLParameter para = new SQLParameter();
			para.addParam(curBillID);
			Object ret = session.executeQuery(sql,para, new BeanListProcessor(LightBillRowVO.class));
			List<LightBillRowVO> rowList = (List<LightBillRowVO>) ret;
			
			return rowList == null ? null : rowList.toArray(new LightBillRowVO[0]);
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
		} catch (BusinessException e) {
					Logger.error(e.getMessage(), e);
		} finally {
			sessionManager.release();
		}
		return null;
	}

	private String createTempTable(JdbcSession session, String tempTableName)
			throws BusinessException, DbException {
		// 构造临时表
		String columnNms = "pk varchar(20)";
	    try {
			tempTableName = new TempTable().createTempTable(session.getConnection(),
			            		  tempTableName, columnNms, "pk");
		} catch (SQLException e) {
			Logger.error(e.getMessage(), e);
			throw new BusinessException(e.getMessage());
		}
		return tempTableName;
	}
	
	private void deleteTermpTablePKs(JdbcSession session, String tempTableName) throws BusinessException, DbException {
		// 将前次数据清空重新插入数据
		String deleteSql = "delete from " + tempTableName;
		session.executeUpdate(deleteSql);
	}
	
	private void insertTempTablePKs(JdbcSession session, String tempTableName, String... srcBillID) throws BusinessException, DbException {
		// 利用静态绑定变量方法执行临时表插入
		String insertSql = "insert into " + tempTableName + "(pk) values(?)";
		List<SQLParameter> params = new ArrayList<SQLParameter>();
		for (String id : srcBillID) {
		    SQLParameter param = new SQLParameter();
		    param.addParam(id);
		    params.add(param);
		}
		execBatchSql2(session, insertSql, params);
	}
	
	private int execBatchSql2(JdbcSession session, String sql, List<SQLParameter> list) throws BusinessException {
		try {
			session.setAddTimeStamp(false);
			session.addBatch(sql, list.toArray(new SQLParameter[list.size()]));
			return session.executeBatch();
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
			throw new BusinessException(e.getMessage());
		}
	}
	
	public static final class BillTypeInfo{
		
		private String code;
		private String corp;
		private String billtype;
		private String transtype;
		private String transtypepk;
		
		public String getCorp() {
			return corp;
		}
		public void setCorp(String corp) {
			this.corp = corp;
		}
		
		public String getCode() {
			return code;
		}
		public void setCode(String code) {
			this.code = code;
		}
		public String getBilltype() {
			return billtype;
		}
		public void setBilltype(String billtype) {
			this.billtype = billtype;
		}
		public String getTranstype() {
			return transtype;
		}
		public void setTranstype(String transtype) {
			this.transtype = transtype;
		}
		public String getTranstypepk() {
			return transtypepk;
		}
		public void setTranstypepk(String transtypepk) {
			this.transtypepk = transtypepk;
		}
	}

}

四、效果：

转载于：https://blog.csdn.net/kissi1990/article/details/103897108

有些联查类会继承 nc.impl.pubapp.linkquery.MultiSrcBillFinder 类，如采购订单的：

package nc.vo.pu.linkquery;

import java.util.Collection;

import nc.bs.pf.pub.PfDataCache;
import nc.bs.trade.billsource.IBillFlow;
import nc.impl.pubapp.linkquery.MultiSrcBillFinder;
import nc.impl.pubapp.linkquery.SingleSuportBillFlow;
import nc.jdbc.framework.DataSourceCenter;
import nc.jdbc.framework.util.DBConsts;
import nc.vo.pub.billtype.BilltypeVO;
import nc.vo.trade.billsource.LightBillVO;

import org.apache.commons.lang.StringUtils;

/**
 * 联查下游有修订情况时，过滤历史版本，只能联查出最新版本
 * 
 * @since 6.0
 * @version 2012-7-30 上午08:59:12
 * @author lixyp
 */
public class PUMultiSrcBillFinder extends MultiSrcBillFinder {

  private String srcBillType;

  @Override
  public LightBillVO[] getForwardBills(String srcBillType1, String curBillType,
      String... srcBillID) {
    this.srcBillType = srcBillType1;
    return super.getForwardBills(srcBillType1, curBillType, srcBillID);
  }

  @Override
  protected String createQueryFwdSql(IBillFlow billflow, String... srcBillID) {
    // 2013-5-27，平台修改方法，加了参数srcBillID
    String sql = super.createQueryFwdSql(billflow, srcBillID);

    if (billflow instanceof SingleSuportBillFlow
        && DBConsts.DB2 == DataSourceCenter.getInstance().getDatabaseType()) {
      String sourceTypeFromBeanid = billflow.getSourceTypeField();
      Collection<BilltypeVO> collbilltypes =
          PfDataCache.getBilltypes().values();
      BilltypeVO billTypeVO = null;
      for (BilltypeVO vo : collbilltypes) {
        if (vo != null
            && StringUtils.equalsIgnoreCase(vo.getPrimaryKey(),
                this.srcBillType)) {
          billTypeVO = vo;
          break;
        }
      }

      if (billTypeVO != null && !this.srcBillType.equals(sourceTypeFromBeanid)
          && !sourceTypeFromBeanid.equals(billTypeVO.getPk_billtypecode())) {
        sql = null;
      }
      else {
        billTypeVO = PfDataCache.getBillType(this.srcBillType);
        if (!this.srcBillType.equals(sourceTypeFromBeanid)
            && !sourceTypeFromBeanid.equals(billTypeVO.getPk_billtypeid())) {
          sql = null;
        }
      }
    }

    if (null != sql && sql.length() > 0) {
      // 增加过滤条件
      String hTbl = billflow.getMainTableName();
      sql += " and " + hTbl + ".bislatest='Y'";
    }
    return sql;
  }

}

package nc.impl.pubapp.linkquery;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nc.bs.dao.BaseDAO;
import nc.bs.dao.DAOException;
import nc.bs.framework.common.InvocationInfoProxy;
import nc.bs.logging.Logger;
import nc.bs.mw.sqltrans.TempTable;
import nc.bs.pf.pub.BillTypeCacheKey;
import nc.bs.pf.pub.PfDataCache;
import nc.bs.trade.billsource.DefaultBillFlowFactory;
import nc.bs.trade.billsource.IBillDataFinder;
import nc.bs.trade.billsource.IBillFlow;
import nc.impl.pubapp.pattern.database.IDQueryBuilder;
import nc.jdbc.framework.JdbcSession;
import nc.jdbc.framework.PersistenceManager;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.exception.DbException;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.vo.jcom.lang.StringUtil;
import nc.vo.pf.change.PfUtilBaseTools;
import nc.vo.pub.BusinessException;
import nc.vo.pub.billtype.BilltypeVO;
import nc.vo.pubapp.billtype.BillTypeQueryTools;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nc.vo.pubapp.pattern.exception.TransferException;
import nc.vo.pubapp.pattern.exception.TransferSqlException;
import nc.vo.trade.billsource.LightBillRowVO;
import nc.vo.trade.billsource.LightBillVO;

import org.apache.commons.lang.StringUtils;

/**
 * 综合数据查找器，依赖于{@link IMutiBillFlowFactory}， 会根据多个BillFlow查找多次
 * 
 * @since 6.0
 * @version 2011-03-21
 * @author 任志东
 * 
 */
public class MultiSrcBillFinder implements IBillDataFinder {

	private IMutiBillFlowFactory billFlowFactory;

	private boolean isSource = true;
	
	private String tempTableName = "";

	public boolean isSource() {
		return isSource;
	}

	public void setSource(boolean isSource) {
		this.isSource = isSource;
	}

	public MultiSrcBillFinder() {
		super();
		// 声明一个组合工厂
		CompositeBillFlowFactory factory = new CompositeBillFlowFactory();
		// 默认的支持元数据接口的工厂
		factory.addFactory(new DefaultBillFlowFactory());
		// 单字段的工厂
		factory.addFactory(new SingleSuportBillFlowFactory());
		// 两个字段工厂
		factory.addFactory(new SrcGroupSuportBillFlowFactory());
		// 设置工厂类
		this.setBillFlowFactory(factory);
	}

  /**
   * 查找下游单据信息
   * 
   * @param srcBillType 来源单据类型
   * @param srcBillID 来源单据id
   * @param curBillType 当前单据类型
   * @return 下游信息vo数组
   */
  @SuppressWarnings({"unchecked"})
  @Override
  public LightBillVO[] getForwardBills(String srcBillType, String curBillType, String... srcBillID) {
	  
    SQLParameter para = new SQLParameter();
    
    Collection<BilltypeVO> collbilltypes =PfDataCache.getBilltypes().values();
    BilltypeVO billTypeVO = null;
	for(BilltypeVO vo:collbilltypes){
		if(vo != null && StringUtils.equalsIgnoreCase(vo.getPrimaryKey(), srcBillType)){
			billTypeVO = vo;
			break;
		}
	}
	
	if (billTypeVO != null) {
		// srcBillType 是ID的情况
		para.addParam(billTypeVO.getPk_billtypecode());
	    para.addParam(srcBillType);
	}
	else {
		// srcBillType 是CODE的情况		
	    billTypeVO = PfDataCache.getBillType(srcBillType);
	    para.addParam(srcBillType);
	    para.addParam(billTypeVO.getPk_billtypeid());
	}
    
    List<LightBillVO> allSrc = new ArrayList<LightBillVO>();
    
    PersistenceManager sessionManager = null;
    String sql = "";
	try {
		 // String billType = PfUtilBaseTools.getRealBilltype(curBillType);
	    String billType = this.getRealBilltypeByCodeOrPk(curBillType);
	    
	    // 查找下游单据时合并SQL CHENYL
	    IBillFlow[] billFlows = this.billFlowFactory.createBillFlows(billType);
	    Map<String, IBillFlow> billFlowTempMap = new HashMap<String, IBillFlow>();
	    if (billFlows == null) {
	    	return null;
	    }
	    
	    String tempKey = null;
		for (IBillFlow billFlow : billFlows) {
			// Key = 主表名+子表名+来源单据单据类型字段+来源单据FK字段
			tempKey = billFlow.getMainTableName() + billFlow.getSubTableName() + billFlow.getSourceTypeField()
					+ billFlow.getSourceIDField();
			if (!billFlowTempMap.containsKey(tempKey)) {
				billFlowTempMap.put(tempKey, billFlow);
			}
		}
	    
		sessionManager = PersistenceManager.getInstance();
		JdbcSession session = sessionManager.getJdbcSession();
		
//		//新建临时表
//		tempTableName = createTempTable(session, "tempTableName");
//		Logger.debug("生成临时表用于主键关联查询,表名->" + tempTableName);
//		
//		//清空临时表旧数据
//		deleteTermpTablePKs(session, tempTableName);
//		
//		//往临时表插入当前查询需关联的
//		insertTempTablePKs(session, tempTableName, srcBillID);
		
	    for ( String key : billFlowTempMap.keySet()) {
	      IBillFlow billFlow = billFlowTempMap.get(key);
	      sql = this.createQueryFwdSql(billFlow, srcBillID);
	      if (StringUtil.isEmptyWithTrim(sql)) {
	        continue;
	      }
	      BeanListProcessor p = new BeanListProcessor(LightBillVO.class);
	      allSrc.addAll((List<LightBillVO>) session.executeQuery(sql, para, p));
	    }

	    // 设置单据类型
	    for (LightBillVO vo : allSrc) {
	    	if (StringUtils.isEmpty(vo.getType()) && StringUtils.isEmpty(vo.getTranstype())
	    			&& StringUtils.isEmpty(vo.getTranstypepk())) {
	    		vo.setType(billType);
	    	}
	    }
	    return allSrc.toArray(new LightBillVO[allSrc.size()]);
	} catch (DbException e) {
		String msg = getStrByResID("common","UPPcommon-000033")/*"查询下游单据发生异常，请将问题直接填给或转给单据类型【"*/
				+ curBillType
				+ ":"/* -=notranslate=- */
				+ PfDataCache.getBillType(curBillType).getBilltypename() 
				+ getStrByResID("common","UPPcommon-000034")	/*"】的负责人！当前单据类型："*/
				+ srcBillID 
				+ getStrByResID("common","UPPcommon-000035")/*"，当前单据ID："*/ 
				+ srcBillID.toString()
				+ getStrByResID("common","UPPcommon-000036")/*"，下游单据类型：" */+ curBillType 
				+ getStrByResID("common","UPPcommon-000037")/*"，查询sql："*/ + sql;
		TransferException te = new TransferException(e);
		te.addMessage(msg);
		ExceptionUtils.wrappException(te);
	} catch (Exception e) {
		ExceptionUtils.wrappException(e);
	} finally {
		sessionManager.release();
	}
	
	return null;
   
  }

	private String createTempTable(JdbcSession session, String tempTableName)
			throws BusinessException, DbException {
		// 构造临时表
		String columnNms = "pk varchar(20)";
	    try {
			tempTableName = new TempTable().createTempTable(session.getConnection(),
			            		  tempTableName, columnNms, "pk");
		} catch (SQLException e) {
			Logger.error(e.getMessage(), e);
			throw new BusinessException(e.getMessage());
		}
		return tempTableName;
	}
	
	private void deleteTermpTablePKs(JdbcSession session, String tempTableName) throws BusinessException, DbException {
		// 将前次数据清空重新插入数据
		String deleteSql = "delete from " + tempTableName;
		session.executeUpdate(deleteSql);
	}
	
	private void insertTempTablePKs(JdbcSession session, String tempTableName, String... srcBillID) throws BusinessException, DbException {
		// 利用静态绑定变量方法执行临时表插入
		String insertSql = "insert into " + tempTableName + "(pk) values(?)";
		List<SQLParameter> params = new ArrayList<SQLParameter>();
		for (String id : srcBillID) {
		    SQLParameter param = new SQLParameter();
		    param.addParam(id);
		    params.add(param);
		}
		execBatchSql2(session, insertSql, params);
	}

	/**
	 * 查找注册的下游单据类型
	 * 
	 * @param vo
	 *            单据信息vo
	 * @return 注册的下游单据类型数组
	 * @throws BusinessException
	 */
	@Override
	public String[] getForwardBillTypes(LightBillVO vo)
			throws BusinessException {
		BilltypeVO billtypeVO = PfDataCache.getBillTypeInfo(vo.getType());
		if (billtypeVO == null || billtypeVO.getForwardbilltype() == null) {
			return new String[0];
		}
		return billtypeVO.getForwardbilltype().split(",");

	}

	@Override
	public String[] getForwardBillTypes(String billType)
			throws BusinessException {
		LightBillVO vo = new LightBillVO();
		vo.setType(billType);
		return getForwardBillTypes(vo);
	}

	/**
	 * 查询来源单据信息
	 * 
	 * @param curBillType
	 *            当前单据类型
	 * @param curBillID
	 *            当前单据id
	 * @return 来源单据信息VO数组
	 */
	@SuppressWarnings("unchecked")
	@Override
	public LightBillVO[] getSourceBills(String curBillType, String curBillID) {
		SQLParameter para = new SQLParameter();
		para.addParam(curBillID);
		Map<String, List<LightBillVO>> allSrc = new HashMap<String, List<LightBillVO>>();
		BaseDAO dao = new BaseDAO();
		// String billType = PfUtilBaseTools.getRealBilltype(curBillType);
		IBillFlow[] billFlows = this.billFlowFactory
				.createBillFlows(getRealBilltypeByCodeOrPk(curBillType));
		for (IBillFlow billFlow : billFlows) {
			String sql = this.createQuerySrcSql(billFlow);
			if (sql == null) {
				continue;
			}
			try {
				BeanListProcessor p = new BeanListProcessor(LightBillVO.class);
				List<LightBillVO> vos = (List<LightBillVO>) dao.executeQuery(
						sql, para, p);
				for (LightBillVO vo : vos) {
					if (!StringUtils.isBlank(vo.getID())) {
						// 有的单据存的可能只有单据类型/交易类型pk而没有code，所以需要在这进行适配
						if (!this.isExistsBilltype(vo.getType())) {
							BilltypeVO billtypeVO = BillTypeQueryTools
									.queryBillTypeByPK(vo.getType());
							vo.setType(billtypeVO.getPk_billtypecode());
						}

						if (StringUtils.isBlank(vo.getType())) {
							String msg = 
									getStrByResID("common","UPPcommon-000038")/*"错误的上游单据信息,请将问题填给或转给单据类型【"*/
									+ curBillType
									+ ":"
									+ PfDataCache.getBillType(curBillType).getBilltypename() 
									+ getStrByResID("common","UPPcommon-000039")/*"】的负责人!"*/
									+ getStrByResID("common","UPPcommon-000040")/*"上游单据类型为空!当前单据【id：" */
									+ curBillID 
									+ "】!"/* -=notranslate=- */
									+ getStrByResID("common","UPPcommon-000041")/*"上游单据类型字段："*/
									+ billFlow.getSourceTypeField()
									+ getStrByResID("common","UPPcommon-000042")/*"，上游单据ID字段："*/
									+ billFlow.getSourceIDField();
							TransferException te = new TransferException(
									new Exception(msg));
							te.addMessage(msg);
							ExceptionUtils.wrappException(te);
						}

						// 同一单据类型的来源放在一起
						String srcBillType = vo.getType();
						if (!allSrc.containsKey(srcBillType)) {
							allSrc.put(srcBillType,
									new ArrayList<LightBillVO>());
						}
						allSrc.get(srcBillType).add(vo);
					}
				}
			} catch (DAOException e) {
				ExceptionUtils.wrappException(new TransferSqlException(e, sql));
			}
		}

		List<LightBillVO> alllist = new ArrayList<LightBillVO>();

		// 增补上游单据号
		for (String srcType : allSrc.keySet()) {
			List<LightBillVO> list = allSrc.get(srcType);
			Map<String, SourceQueryResultVO> result = this.getBillCodeAndCorp(
					srcType, list);

			for (LightBillVO vo : list) {
				SourceQueryResultVO sourceInfoVo = result.get(vo.getID());
				if (sourceInfoVo != null) {
					vo.setCode(sourceInfoVo.getCode());
					vo.setCorp(sourceInfoVo.getCorp());
					vo.setType(sourceInfoVo.getBilltype());
					vo.setTranstype(sourceInfoVo.getTranstype());
					vo.setTranstypepk(sourceInfoVo.getTranstypepk());
				}
			}

			alllist.addAll(list);
		}
		return alllist.toArray(new LightBillVO[allSrc.size()]);
	}

	public void setBillFlowFactory(IMutiBillFlowFactory flowFactory) {
		this.billFlowFactory = flowFactory;
	}

	/**
	 * 查找单据号和公司
	 * 
	 * @param billflow
	 * @param srcBills
	 * @return
	 */
	private String createQueryCodeAndCorpSql(IBillFlow billflow,
			List<LightBillVO> srcBills) {
		String hTbl = billflow.getMainTableName();
		String hPkFld = billflow.getMainTablePrimaryKeyFiled();
		String hTblCodeFld = billflow.getBillNOField();
		String hTblCorpFld = billflow.getBillCorp();
		String hBillTypeField = billflow.getBillTypeField();
		String hTransTypeField = billflow.getTransTypeField();
		String hTransTypePkField = billflow.getTransTypePkField();

		StringBuffer sql = new StringBuffer();
		sql.append("select ").append(hTblCodeFld).append(" code,")
				.append(hTblCorpFld + " corp ");

		// 以下为新增
		if (StringUtils.isNotEmpty(hBillTypeField))
			sql.append("," + hBillTypeField + " billtype ");
		if (!StringUtil.isEmptyWithTrim(hTransTypeField))
			sql.append("," + hTransTypeField + " transtype ");
		if (!StringUtil.isEmptyWithTrim(hTransTypePkField))
			sql.append("," + hTransTypePkField + " transtypepk ");
		if (!StringUtil.isEmptyWithTrim(hPkFld))
			sql.append("," + hPkFld + " id ");

		sql.append("  from ").append(hTbl);
		sql.append(" where ").append(hPkFld).append(" in(");

		// 构造来源单据主键参数
		for (LightBillVO srcVo : srcBills) {
			sql.append("'");
			sql.append(srcVo.getID());
			sql.append("'");
			sql.append(",");
		}
		sql.deleteCharAt(sql.length() - 1);
		sql.append(")");
		sql.append(" and " + hTbl + ".dr = 0 " );
		
		return sql.toString();
	}

	private String createTempTable(String tableName, String columns,
			String indexs) throws BusinessException {

		String vtn = null;
		JdbcSession session = null;
		try {
			session = new JdbcSession();
			vtn = new TempTable().createTempTable(session.getConnection(),
					tableName, columns, indexs);
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
			throw new BusinessException(e.getMessage());
		} catch (SQLException e) {
			Logger.error(e.getMessage(), e);
			throw new BusinessException(e.getMessage());
		} finally {
			if (session != null) {
				session.closeAll();
			}
		}
		return vtn;
	}

	/**
	 * 执行批量SQL
	 * 
	 * @param sql
	 * @param list
	 * @throws BusinessException
	 */
	private void execBatchSql(String sql, List<SQLParameter> list)
			throws BusinessException {
		PersistenceManager manager = null;
		try {
			manager = PersistenceManager.getInstance();
			JdbcSession session = manager.getJdbcSession();
			session.setAddTimeStamp(false);
			session.addBatch(sql, list.toArray(new SQLParameter[list.size()]));
			int count = session.executeBatch();
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
			throw new BusinessException(e.getMessage());
		} finally {
			if (manager != null) {
				manager.release();
			}
		}
	}

	private int execBatchSql2(JdbcSession session, String sql, List<SQLParameter> list) throws BusinessException {
		try {
			session.setAddTimeStamp(false);
			session.addBatch(sql, list.toArray(new SQLParameter[list.size()]));
			return session.executeBatch();
		} catch (DbException e) {
			Logger.error(e.getMessage(), e);
			throw new BusinessException(e.getMessage());
		}
	}
	/**
	 * 查找单据号和公司
	 * 
	 * @param billflow
	 * @param srcBills
	 * @return
	 */
	private String createQueryCodeAndCorpTempSql(IBillFlow billflow,
			List<LightBillVO> srcBills) {
		// 构造临时表
		String columnNms = "varpk varchar(20)";
		try {
			String tempTableNm = createTempTable("tempbilltable", columnNms,
					"varpk");

			// 将前次数据清空重新插入数据
			String deleteSql = "delete from " + tempTableNm;
			BaseDAO dao = new BaseDAO();
			dao.executeUpdate(deleteSql);

			// 利用静态绑定变量方法执行临时表插入
			String insertSql = "insert into " + tempTableNm
					+ "(varpk) values(?)";
			List<SQLParameter> params = new ArrayList<SQLParameter>();
			for (LightBillVO vo : srcBills) {
				SQLParameter param = new SQLParameter();
				param.addParam(vo.getID());
				params.add(param);
			}
			execBatchSql(insertSql, params);

			String hTbl = billflow.getMainTableName();
			String hPkFld = billflow.getMainTablePrimaryKeyFiled();
			String hTblCodeFld = billflow.getBillNOField();
			String hTblCorpFld = billflow.getBillCorp();
			String hBillTypeField = billflow.getBillTypeField();
			String hTransTypeField = billflow.getTransTypeField();
			String hTransTypePkField = billflow.getTransTypePkField();

			StringBuffer sql = new StringBuffer();
			sql.append("select t.").append(hTblCodeFld).append(" code, t.")
					.append(hTblCorpFld + " corp ");

			// 以下为新增
			if (StringUtils.isNotEmpty(hBillTypeField))
				sql.append(", t." + hBillTypeField + " billtype ");
			if (!StringUtil.isEmptyWithTrim(hTransTypeField))
				sql.append(", t." + hTransTypeField + " transtype ");
			if (!StringUtil.isEmptyWithTrim(hTransTypePkField))
				sql.append(", t." + hTransTypePkField + " transtypepk ");
			if (!StringUtil.isEmptyWithTrim(hPkFld))
				sql.append(", t." + hPkFld + " id ");

			sql.append("  from ").append(tempTableNm)
					.append(" tempbilltable left join ").append(hTbl);
			sql.append(" t on (tempbilltable.varpk = t.").append(hPkFld)
					.append(")");
			return sql.toString();
		} catch (BusinessException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 创建查询下游单据信息的SQL
	 * 
	 * @param billflow
	 * @return 查询脚本
	 */
	protected String createQueryFwdSql(IBillFlow billflow, String... srcBillID) {
		String hTbl = billflow.getMainTableName();
		String hPkFld = hTbl + "." + billflow.getMainTablePrimaryKeyFiled();
		String hPkCorp = hTbl + "." + billflow.getBillCorp();
		String hBillCodeFld = hTbl + "." + billflow.getBillNOField();
		String bTbl = billflow.getSubTableName();
		String bTblSrcTypeFld = null;
		if (billflow instanceof SingleSuportBillFlow)
			bTblSrcTypeFld = billflow.getSourceTypeField();
		else
			bTblSrcTypeFld = bTbl + "." + billflow.getSourceTypeField();

		String bTblSrcPkFld = bTbl + "." + billflow.getSourceIDField();

		String hBillTypeField = billflow.getBillTypeField();
		String hTransTypeField = billflow.getTransTypeField();
		String hTransTypePkField = billflow.getTransTypePkField();

		if (StringUtils.isBlank(bTbl)) {
			return null;
		}
		// 在该类型的单据中查找某种类型单据的后续单据
		StringBuffer sb = new StringBuffer();

		if (hTbl.equalsIgnoreCase(bTbl)) { // 如果接口属性映射到表头字段上
			sb.append("select ");
			sb.append(hPkFld + " id," + hPkCorp + " corp," + hBillCodeFld
					+ " code");

			// 以下为新增lkp
			if (!StringUtil.isEmptyWithTrim(hBillTypeField))
				sb.append(", " + hTbl + "." + hBillTypeField + " type ");
			if (!StringUtil.isEmptyWithTrim(hTransTypeField))
				sb.append(", " + hTbl + "." + hTransTypeField + " transtype ");
			if (!StringUtil.isEmptyWithTrim(hTransTypePkField))
				sb.append(", " + hTbl + "." + hTransTypePkField
						+ " transtypepk ");

			sb.append(", " + bTblSrcPkFld + " sourceID ");

			// ------------------------------------------

			sb.append("  from " + hTbl);
			sb.append(" where ");
			sb.append(bTblSrcTypeFld + " in (?,?)");
			sb.append(" and ");
			sb.append(new IDQueryBuilder().buildSQL(bTblSrcPkFld, srcBillID));

			sb.append("   and " + hTbl + ".dr=0");
		} else {
			String bFkFld = bTbl + "." + billflow.getSubTableForeignKeyFiled();
			if (StringUtils.isBlank(bFkFld)) {
				return null;
			}
			sb.append("select distinct ");
			sb.append(hPkFld + " id," + hPkCorp + " corp," + hBillCodeFld
					+ " code");

			// 以下为新增lkp
			if (!StringUtil.isEmptyWithTrim(hBillTypeField))
				sb.append(", " + hTbl + "." + hBillTypeField + " type ");
			if (!StringUtil.isEmptyWithTrim(hTransTypeField))
				sb.append(", " + hTbl + "." + hTransTypeField + " transtype");
			if (!StringUtil.isEmptyWithTrim(hTransTypePkField))
				sb.append(", " + hTbl + "." + hTransTypePkField
						+ " transtypepk");
			sb.append(", " + bTblSrcPkFld + " sourceID ");
			// ------------------------------------------

			sb.append("  from " + hTbl + "," + bTbl);
			sb.append(" where " + hPkFld + "=" + bFkFld);
			sb.append("   and ");
			sb.append(bTblSrcTypeFld + " in (?,?)");
			sb.append("   and " );
			sb.append(new IDQueryBuilder().buildSQL(bTblSrcPkFld, srcBillID));
			sb.append("   and " + hTbl + ".dr=0");
			sb.append("   and " + bTbl + ".dr=0");
		}
		return sb.toString();
	}

	/**
	 * 创建查询上游单据类型和PK的SQL
	 * 
	 * @param billflow
	 * @return 查询脚本
	 */
	private String createQuerySrcSql(IBillFlow billflow) {
		String bTable = billflow.getSubTableName();
		String bFkField = billflow.getSubTableForeignKeyFiled();
		String srctype = billflow.getSourceTypeField();
		String bTblSrcIDFld = billflow.getSourceIDField();
		if (bTable == null || srctype == null || bTblSrcIDFld == null) {
			return null;
		}
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT DISTINCT ");
		sb.append(srctype).append(" as type, ").append(bTblSrcIDFld)
				.append(" id");
		sb.append("  FROM ").append(bTable);
		sb.append(" WHERE ").append(bFkField).append(" = ? ");
		sb.append("   and dr=0 ");

		return sb.toString();
	}

	/**
	 * 查询单据编码和公司。
	 * 
	 * @return
	 * @param curBillType
	 *            单据类型
	 * @param curBillID
	 *            单据id
	 */
	private Map<String, SourceQueryResultVO> getBillCodeAndCorp(
			String curBillType, List<LightBillVO> srcBills) {

		// String billType = PfUtilBaseTools.getRealBilltype(curBillType);
		String billType = this.getRealBilltypeByCodeOrPk(curBillType);
		IBillFlow billflow = this.billFlowFactory.createBillFlows(billType)[0];

		// 增加临时表的情况
		String sql;
		if (srcBills.size() < 1000) {
			sql = this.createQueryCodeAndCorpSql(billflow, srcBills);
		} else {
			sql = this.createQueryCodeAndCorpTempSql(billflow, srcBills);
		}

		BaseDAO dao = new BaseDAO();
		try {
			BeanListProcessor processor = new BeanListProcessor(
					SourceQueryResultVO.class);
			List<SourceQueryResultVO> sourceInfoVOList = (List<SourceQueryResultVO>) dao
					.executeQuery(sql, processor);

			Map<String, SourceQueryResultVO> retMap = new HashMap<String, SourceQueryResultVO>();
			// 补充来源单据类型信息
			for (SourceQueryResultVO v : sourceInfoVOList) {
				if (StringUtils.isEmpty(v.getBilltype())) {
					v.setBilltype(billType);
				}
				retMap.put(v.getId(), v);
			}

			return retMap;
		} catch (DAOException e) {
			ExceptionUtils.wrappException(new TransferSqlException(e, sql));
			return null;
		}
	}

	public static final class SourceQueryResultVO {

		private String code;
		private String corp;
		private String billtype;
		private String transtype;
		private String transtypepk;
		private String id;

		public String getId() {
			return id;
		}

		public void setId(String id) {
			this.id = id;
		}

		public String getCorp() {
			return corp;
		}

		public void setCorp(String corp) {
			this.corp = corp;
		}

		public String getCode() {
			return code;
		}

		public void setCode(String code) {
			this.code = code;
		}

		public String getBilltype() {
			return billtype;
		}

		public void setBilltype(String billtype) {
			this.billtype = billtype;
		}

		public String getTranstype() {
			return transtype;
		}

		public void setTranstype(String transtype) {
			this.transtype = transtype;
		}

		public String getTranstypepk() {
			return transtypepk;
		}

		public void setTranstypepk(String transtypepk) {
			this.transtypepk = transtypepk;
		}
	}

	// 由于某些原因，业务组在单据保存的可能是单据类型/交易类型的主键而不是编码，所以导致
	// 寻找单据类型时无法寻找到，而如果要求业务组修改的话代价很大，所以只能在这做个兼容
	// 先按编码查找，如果查找不到再根据pk查找
	private String getRealBilltypeByCodeOrPk(String curBillTypeCodeOrPk) {
		String billType = null;
		if (this.isExistsBilltype(curBillTypeCodeOrPk)) {
			billType = PfUtilBaseTools.getRealBilltype(curBillTypeCodeOrPk);
		} else {
			billType = PfUtilBaseTools.getRealBilltypeByPK(curBillTypeCodeOrPk);
		}
		// try {
		// billType = PfUtilBaseTools.getRealBilltype(curBillTypeCodeOrPk);
		// }
		// catch (PFRuntimeException e) {
		// billType = PfUtilBaseTools.getRealBilltypeByPK(curBillTypeCodeOrPk);
		// }
		return billType;
	}

	/**
	 * 
	 * 判断单据类型是否存在
	 * 
	 * @param strTypeCodestr
	 * @return
	 */
	private boolean isExistsBilltype(String strTypeCodestr) {
		BillTypeCacheKey strTypeCode = new BillTypeCacheKey().buildBilltype(
				strTypeCodestr).buildPkGroup(
				InvocationInfoProxy.getInstance().getGroupId());
		String billtype = strTypeCode.getBilltype().trim();
		strTypeCode.buildBilltype(billtype);
		// XXX::根据缓存获取单据类型
		BilltypeVO btVO = PfDataCache.getBillTypeInfo(strTypeCode);
		if (btVO == null) {
			return false;
		}
		return true;
	}

	@Override
	public LightBillRowVO[] getRows(String curBillID, String curBillType,
			String[] rows) {
		if (curBillType == null) {
			return null;
		}
		
		if(rows != null && rows.length > 100) {
			return getRowsByTempTable(curBillID, curBillType, rows);
		}
		IBillFlow[] billFlows = this.billFlowFactory
				.createBillFlows(curBillType);
		Map<String, IBillFlow> billFlowTempMap = new HashMap<String, IBillFlow>();
		if (billFlows == null) {
			return null;
		}

		String tempKey = null;
		for (IBillFlow billFlow : billFlows) {
			if (StringUtils.isEmpty(billFlow.getSubTableRowNoField())) {
				continue;
			}
			// Key = 主表名+子表名+来源单据单据类型字段+来源单据行号
			tempKey = billFlow.getMainTableName() + billFlow.getSubTableName() + billFlow.getSourceTypeField() 
					+ billFlow.getSourceRowField();
			if (!billFlowTempMap.containsKey(tempKey)) {
				billFlowTempMap.put(tempKey, billFlow);
			}
		}

		List<LightBillRowVO> allRows = new ArrayList<LightBillRowVO>();
		BaseDAO dao = new BaseDAO();

		SQLParameter para = new SQLParameter();
		para.addParam(curBillID);

		for (String key : billFlowTempMap.keySet()) {
			IBillFlow billFlow = billFlowTempMap.get(key);
			String sql = this.createQueryRowsSQL(billFlow, rows);
			if (null == sql) {
				continue;
			}
			try {
				BeanListProcessor p = new BeanListProcessor(
						LightBillRowVO.class);
				allRows.addAll((List<LightBillRowVO>) dao.executeQuery(sql,
						para, p));
			} catch (DAOException e) {
				String msg = 
						getStrByResID("common","UPPcommon-000043")/*"单据行发生异常，请将问题直接填给或转给单据类型【" */
						+ curBillType
						+ getStrByResID("common","UPPcommon-000044")/*"】的负责人！"*/
						+ getStrByResID("common","UPPcommon-000045")/*"，当前单据ID："*/
						+ curBillID 
						+ getStrByResID("common","UPPcommon-000046")/*"，查询sql："*/ 
						+ sql;
				TransferException te = new TransferException(e);
				te.addMessage(msg);
				ExceptionUtils.wrappException(te);
			}
		}

		return allRows.toArray(new LightBillRowVO[allRows.size()]);
	}

	protected String createQueryRowsSQL(IBillFlow billflow, String[] rows) {
		if (billflow == null) {
			return null;
		}

		if (billflow.getSubTableName() == null
				|| billflow.getSubTableRowNoField() == null)
			return null;

		String bTable = billflow.getSubTableName();
		String rowNo = billflow.getSubTableRowNoField();
		String srcRowNo = billflow.getSourceRowField();
		String bFkField = billflow.getSubTableForeignKeyFiled();
		String bTableSourceTypeField = billflow.getSourceTypeField();
		String bTableSourceIDField = billflow.getSourceIDField();

		if (bTableSourceTypeField == null || bTableSourceIDField == null)
			return null;

		StringBuffer sb = new StringBuffer("SELECT DISTINCT ");
		sb.append(rowNo).append(" rowNo,").append(bTableSourceTypeField)
				.append(" srcBillType,").append(bTableSourceIDField)
				.append(" srcBillID,").append(srcRowNo).append(" srcRowNo");
		sb.append(" FROM ").append(bTable);

		sb.append(" WHERE");
		sb.append(" ");
		sb.append(bFkField);

		sb.append("=?  and ");
		sb.append(rowNo);
		sb.append(" is not null");
		sb.append(" and dr=0 ");

		if (rows != null) {
			sb.append(" and ");
			this.append(sb, rowNo, rows);
		}

		return sb.toString();
	}

	/**
	 * 对于String数组值构造in条件
	 * 
	 * @param name
	 *            sql字段名
	 * @param values
	 *            String数组值
	 */
	public void append(StringBuffer sb, String name, String[] values) {
		int length = values.length;
		if (length == 1) {
			sb.append(name);
			sb.append("='");
			sb.append(values[0]);
			sb.append("' ");
			return;
		}
		sb.append(name);
		sb.append(" in (");
		for (int i = 0; i < length; i++) {
			sb.append("'");
			sb.append(values[i]);
			sb.append("'");
			sb.append(",");
		}
		length = sb.length();
		sb.deleteCharAt(length - 1);
		sb.append(") ");
	}
	
	public LightBillRowVO[] getRowsByTempTable(String curBillID, String curBillType,
			String[] rows) {
		if (curBillType == null) {
			return null;
		}
		
		IBillFlow[] billFlows = this.billFlowFactory
				.createBillFlows(curBillType);
		Map<String, IBillFlow> billFlowTempMap = new HashMap<String, IBillFlow>();
		if (billFlows == null) {
			return null;
		}

		String tempKey = null;
		for (IBillFlow billFlow : billFlows) {
			if (StringUtils.isEmpty(billFlow.getSubTableRowNoField())) {
				continue;
			}
			// Key = 主表名+子表名+来源单据单据类型字段+来源单据行号
			tempKey = billFlow.getMainTableName() + billFlow.getSubTableName() + billFlow.getSourceTypeField() 
					+ billFlow.getSourceRowField();
			if (!billFlowTempMap.containsKey(tempKey)) {
				billFlowTempMap.put(tempKey, billFlow);
			}
		}

		List<LightBillRowVO> allRows = new ArrayList<LightBillRowVO>();

		SQLParameter para = new SQLParameter();
		para.addParam(curBillID);

		for (String key : billFlowTempMap.keySet()) {
			PersistenceManager sessionManager = null;
			JdbcSession session = null;
			String sql = null;
			try {
				sessionManager = PersistenceManager.getInstance();
				session = sessionManager.getJdbcSession();
				
				//新建临时表
				tempTableName = createTempTable(session, "tempTableName");
				Logger.debug("生成临时表用于主键关联查询,表名->" + tempTableName);
				
				//清空临时表旧数据
				deleteTermpTablePKs(session, tempTableName);
				
				//往临时表插入当前查询需关联的
				insertTempTablePKs(session, tempTableName, rows);
			
				IBillFlow billFlow = billFlowTempMap.get(key);
				sql = this.createQueryRowsSQLByTempTable(tempTableName, billFlow, rows);
				if (null == sql) {
					continue;
				}
				
				BeanListProcessor p = new BeanListProcessor(
						LightBillRowVO.class);
				allRows.addAll((List<LightBillRowVO>) session.executeQuery(sql,
						para, p));
			} catch (DAOException e) {
				String msg = 
						getStrByResID("common","UPPcommon-000043")/*"单据行发生异常，请将问题直接填给或转给单据类型【" */
						+ curBillType
						+ getStrByResID("common","UPPcommon-000044")/*"】的负责人！"*/
						+ getStrByResID("common","UPPcommon-000045")/*"，当前单据ID："*/
						+ curBillID 
						+ getStrByResID("common","UPPcommon-000046")/*"，查询sql："*/ 
						+ sql;
				TransferException te = new TransferException(e);
				te.addMessage(msg);
				ExceptionUtils.wrappException(te);
			} catch (DbException e) {
				Logger.error(e.getMessage(), e);
				ExceptionUtils.wrappException(e);
			} catch (BusinessException e) {
				Logger.error(e.getMessage(), e);
				ExceptionUtils.wrappException(e);
			}
		}

		return allRows.toArray(new LightBillRowVO[allRows.size()]);
	}

	protected String createQueryRowsSQLByTempTable(String tempTableName, IBillFlow billflow, String[] rows) {
		if (billflow == null) {
			return null;
		}

		if (billflow.getSubTableName() == null
				|| billflow.getSubTableRowNoField() == null)
			return null;

		String bTable = billflow.getSubTableName();
		String rowNo = billflow.getSubTableRowNoField();
		String srcRowNo = billflow.getSourceRowField();
		String bFkField = billflow.getSubTableForeignKeyFiled();
		String bTableSourceTypeField = billflow.getSourceTypeField();
		String bTableSourceIDField = billflow.getSourceIDField();

		if (bTableSourceTypeField == null || bTableSourceIDField == null)
			return null;

		StringBuffer sb = new StringBuffer("SELECT DISTINCT ");
		sb.append(rowNo).append(" rowNo,").append(bTableSourceTypeField)
				.append(" srcBillType,").append(bTableSourceIDField)
				.append(" srcBillID,").append(srcRowNo).append(" srcRowNo");
		sb.append(" FROM ").append(bTable);

		sb.append(" WHERE");
		sb.append(" ");
		sb.append(bFkField);

		sb.append("=?  and ");
		sb.append(rowNo);
		sb.append(" is not null");
		sb.append(" and dr=0 ");

		if (rows != null) {
			sb.append(" and ");
			this.appendByTempTable(tempTableName, sb, rowNo, rows);
		}

		return sb.toString();
	}

	/**
	 * 对于String数组值构造in条件
	 * 
	 * @param name
	 *            sql字段名
	 * @param values
	 *            String数组值
	 */
	public void appendByTempTable(String tempTableName, StringBuffer sb, String name, String[] values) {
		int length = values.length;
		if (length == 1) {
			sb.append(name);
			sb.append("='");
			sb.append(values[0]);
			sb.append("' ");
			return;
		}
		sb.append(name);
		sb.append(" in (");
		sb.append(" select pk from " + tempTableName );
//		for (int i = 0; i < length; i++) {
//			sb.append("'");
//			sb.append(values[i]);
//			sb.append("'");
//			sb.append(",");
//		}
//		length = sb.length();
//		sb.deleteCharAt(length - 1);
		sb.append(") ");
	}
	
	
	private String getStrByResID(String productCode, String resId) {
		return nc.bs.ml.NCLangResOnserver.getInstance().getStrByID(productCode,
				resId);

	}
}