NC参照SQL追踪

最近做项目中，（ps:项目是别人做了一大部分，我们过去收尾），发现某个参照点击后，速度特别慢，等了很长时间才显示出来，遂欲得到参照查询语句。

参照一般不怎么弄，就是直接设置类型为参照，然后关联某个档案，可是有时候需要按照指定的方式查询。

先说下跟踪的方法吧，反正挺折腾人的。

1，不要想着从入口类得到参照的方法，应该从单据模板初始化中查看该“书名”的属性：

可以发现：类型设置里的自定义参照是<nc.ui.hn.pub.ref.XhfxInvmandocDefaultRefModel2>

2.这就好办啦，找到XhfxInvmandocDefaultRefModel2这个类，看看里面有没有组织sql的语句

事实上，本例子中的这个类有sql组织的代码，但是并没有得到全部的，只是其中的一部分，，

3.要想得到全部，还得继续，，

XhfxInvmandocDefaultRefModel2 继承了AbstractRefGridTreeBigDataModel  

而AbstractRefGridTreeBigDataModel  继承了AbstractRefGridTreeModel

这两个都是被系统封装过的，不过好在咱们用友开发者还是能搞到部分源代码

在AbstractRefGridTreeModel 的180行有个方法：

protected String getSql(String strPatch, String[] strFieldCode,
String[] hiddenFields, String strTableName, String strWherePart,
String strOrderField) 

4.over 了，

附该自定义参照：

package nc.ui.hn.pub.ref;

import nc.bs.framework.common.NCLocator;
import nc.bs.logging.Logger;
import nc.itf.uap.IUAPQueryBS;
import nc.itf.xhfx.pub.ISearchAllService;
import nc.ui.bd.ref.AbstractRefGridTreeBigDataModel;
import nc.ui.bd.ref.RefPubUtil;
import nc.ui.pub.ClientEnvironment;
import nc.vo.hn.hnh506.ChannelVO;
import nc.vo.ml.NCLangRes4VoTransl;
import nc.vo.pub.BusinessException;

/**
 * 基层店请配单专用
 * 
 * @author Administrator
 */
public class XhfxInvmandocDefaultRefModel2 extends AbstractRefGridTreeBigDataModel {

	// 修改构造方法为无参的
	public XhfxInvmandocDefaultRefModel2() {
		setRefNodeName("存货档案");
	}

	@Override
	public void setRefNodeName(String refNodeName) {
		m_strRefNodeName = refNodeName;
		setRootName(NCLangRes4VoTransl.getNCLangRes().getStrByID("common", "UC000-0001443"));
		setClassFieldCode(new String[] {
			"invclasscode",
			"invclassname",
			"invclasslev",
			"endflag",
			"pk_invcl" });
		setClassJoinField("pk_invcl");
		setClassTableName("bd_invcl");

		try {// ewei+ 查询模板数据权限后台调该处会抛空指针
			setCodingRule(RefPubUtil.getCodeRuleFromPara("BD101", getPk_corp()));
		} catch (Exception e) {
			Logger.error(e.getMessage(), e);
		}
		setClassDefaultFieldCount(2);
		setClassDataPower(true);
		setClassWherePart(" (pk_corp='"
				+ getPk_corp()
				+ "' or pk_corp= '"
				+ "0001"
				+ "') and sealdate is null ");

		// setConfig(refNodeName);
		// 存货参照右侧列表中增加ISBN号，定价，版别三列
		// setFieldCode(new String[] { "bd_invbasdoc.invcode", "bd_invbasdoc.invname",
		// "result.sumnum","bd_invbasdoc.invshortname", "bd_invbasdoc.visbn",
		// "bd_invbasdoc.nprice", "bd_invbasdoc.npurchdiscount", "bd_invbasdoc.pk_publishtype",
		// "bd_invbasdoc.dpublishingdate",
		// "bd_invbasdoc.invspec", "bd_invbasdoc.invtype", "bd_invbasdoc.invmnecode", "bd_invbasdoc.graphid",
		// "bd_invbasdoc.forinvname", });
		// setFieldName(new String[] { "存货编码", "存货名称", "可用量","简称", "ISBN号", "定价", "折扣", "版别", "出版年月", "规格", "型号", "助记码",
		// "图号", "外文名称", });
		setFieldCode(new String[] {
			"bd_invbasdoc.invcode",
			"bd_invbasdoc.invname",
			"result.sumnum",
			"result.NFSNUM",
			"result.NTHNUM",
			"result.NCANUSENUM",
			"bd_invbasdoc.invshortname",
			"bd_invbasdoc.visbn",
			"bd_invbasdoc.nprice",
			"bd_invbasdoc.npurchdiscount",
			"bd_invbasdoc.pk_publishtype",
			"bd_invbasdoc.dpublishingdate",
			"bd_invbasdoc.invspec",
			"bd_invbasdoc.invtype",
			"bd_invbasdoc.invmnecode",
			"bd_invbasdoc.graphid",
			"bd_invbasdoc.forinvname", });
		setFieldName(new String[] {
			"存货编码",
			"存货名称",
			"现存量",
			"分数占用量",
			"退货占用量",
			"可用量",
			"简称",
			"ISBN号",
			"定价",
			"折扣",
			"版别",
			"出版年月",
			"规格",
			"型号",
			"助记码",
			"图号",
			"外文名称", });
		setHiddenFieldCode(new String[] {
			"bd_invmandoc.pk_invbasdoc",
			"bd_invmandoc.pk_invmandoc",
			"bd_invbasdoc.pk_invcl" });
		setPkFieldCode("bd_invmandoc.pk_invmandoc");
		setTableName("bd_invmandoc inner join bd_invbasdoc on bd_invmandoc.pk_invbasdoc=bd_invbasdoc.pk_invbasdoc "
				+ getKYLSQL());
		if (isGroupAssignData()) {
			setWherePart(" bd_invmandoc.pk_corp='"
					+ getPk_corp()
					+ "' and bd_invmandoc.pk_invbasdoc in (select pk_invbasdoc from bd_invbasdoc where pk_corp='0001')");
		} else {
			setWherePart(" bd_invmandoc.pk_corp='"
					+ getPk_corp()
					+ "'");
		}
		// 设置默认列数为11
		setDefaultFieldCount(17);
		setDocJoinField("bd_invbasdoc.pk_invcl");
		setRefCodeField("bd_invbasdoc.invcode");
		setRefNameField("bd_invbasdoc.invname");
		// v56，助记码匹配不支持规格和型号了
		setMnecode(new String[] { "bd_invbasdoc.invmnecode" });

		setRefQueryDlgClaseName("nc.ui.bd.b16.QueryDlg");
		String strFomula = "getColValue(bd_measdoc, measname, pk_measdoc ,bd_invbasdoc.pk_measdoc)";
		// 添加版别公式和保留两位小数公式
		setFormulas(new String[][] {
			{
				"bd_invbasdoc.pk_measdoc",
				strFomula },
			{
				"bd_invbasdoc.pk_publishtype",
				"getColValue(bd_publishtype,vptname , pk_publishtype,bd_invbasdoc.pk_publishtype )" },
			{
				"bd_invbasdoc.nprice",
				"round( bd_invbasdoc.nprice, 2)" },
			{
				"bd_invbasdoc.npurchdiscount",
				"round( bd_invbasdoc.npurchdiscount, 2)" } });

		setSealedWherePart(" bd_invmandoc.sealflag='N' or bd_invmandoc.sealflag is null");
		resetFieldName();
		setCommonDataTableName("bd_invmandoc");
		setCommonDataBasDocPkField("bd_invmandoc.pk_invbasdoc");
		setCommonDataBasDocTableName("bd_invbasdoc");
		setLeafOfClassTreeFetchData(true);

	}

	@Override
	protected String getCommonDataCompositeTableName() {

		if (canUseDB()) {
			return getTableName();
		} else {
			return " bd_invmandoc_c inner join bd_invmandoc on bd_invmandoc_c.pk_doc = bd_invmandoc.pk_invmandoc inner join bd_invbasdoc on bd_invmandoc.pk_invbasdoc=bd_invbasdoc.pk_invbasdoc ";
		}

	}

	@SuppressWarnings("unchecked")
	private String getKYLSQL() {
		StringBuffer buf = new StringBuffer();
		String pk_corp = ClientEnvironment.getInstance().getCorporation().getPrimaryKey();
		String userid = ClientEnvironment.getInstance().getUser().getPrimaryKey();
		ISearchAllService server = NCLocator.getInstance().lookup(ISearchAllService.class);
		IUAPQueryBS server1 = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		ChannelVO channelvo = null;
		ChannelVO channelvosd = null;
		ChannelVO channelvofinal = null;
		try {
			channelvo = server.getUserLinkChannelVO(userid);
			String channelsql = "ileveltype='2' and isnull(dr,0)=0 and isnull(sealflag,'N')='N' and iproducttype ='3'";
			ChannelVO[] channelSDVOlist =
					(ChannelVO[]) server1.retrieveByClause(ChannelVO.class, channelsql).toArray(new ChannelVO[0]);
			channelvosd = channelSDVOlist[0];
		} catch (BusinessException e) {
			e.printStackTrace();
		}
		if (channelvosd.getPk_correscorp().equals(pk_corp)) {// 如果等于，说明是在省店
			channelvofinal = channelvosd;
		} else {
			channelvofinal = channelvosd;
		}
		buf.append(" left join (    ");
		buf.append(" SELECT   ");
		buf.append(" 	    V_IC_ONHANDNUM4.CINVENTORYID                      PK_INVMANDOC,   ");
		buf.append(" 	    V_IC_ONHANDNUM4.CINVBASID                         PK_INVBASDOC,   ");
		buf.append(" 	    SUM(COALESCE(NINNUM, 0.0)-COALESCE(NOUTNUM, 0.0)) SUMNUM,   ");
		buf.append(" 	    SUM(COALESCE(V_IC_KYL1.NFSNUM,0.0))               NFSNUM,   ");
		buf.append(" 	    SUM(COALESCE(V_IC_KYL1.NTHNUM,0.0))               NTHNUM,   ");
		buf.append(" 	    SUM(COALESCE(NINNUM, 0.0)-COALESCE(NOUTNUM, 0.0))- SUM(COALESCE(V_IC_KYL1.NFSNUM,0.0))-SUM   ");
		buf.append(" 	    (COALESCE(V_IC_KYL1.NTHNUM,0.0)) NCANUSENUM   ");
		buf.append(" 	FROM   ");
		buf.append(" 	    V_IC_ONHANDNUM4   ");
		buf.append(" 	LEFT JOIN   ");
		buf.append(" 	    (   ");
		buf.append(" 	        SELECT   ");
		buf.append(" 	            PK_INVBASDOC,   ");
		buf.append(" 	            PK_INVMANDOC,   ");
		buf.append(" 	            PK_RCVSTORDOC,   ");
		buf.append(" 	            SUM(COALESCE(NFSNUM,0.0)) NFSNUM,   ");
		buf.append(" 	            SUM(COALESCE(NTHNUM,0.0)) NTHNUM   ");
		buf.append(" 	        FROM   ");
		buf.append(" 	            V_IC_KYL   ");
		buf.append(" 	        GROUP BY   ");
		buf.append(" 	            PK_INVBASDOC,   ");
		buf.append(" 	            PK_INVMANDOC,   ");
		buf.append(" 	            PK_RCVSTORDOC ) V_IC_KYL1   ");
		buf.append(" 	ON   ");
		buf.append(" 	    V_IC_ONHANDNUM4.CINVENTORYID = V_IC_KYL1.PK_INVMANDOC   ");
		buf.append(" 	AND V_IC_ONHANDNUM4.CINVBASID =V_IC_KYL1.PK_INVBASDOC   ");
		buf.append(" 	AND V_IC_ONHANDNUM4.CWAREHOUSEID = V_IC_KYL1.PK_RCVSTORDOC   ");
		buf.append(" 	WHERE   ");
		buf.append(" 	    PK_CORP = '1002'   ");
		buf.append(" 	AND V_IC_ONHANDNUM4.CWAREHOUSEID = '").append(channelvofinal.getPk_dftstordoc()).append("' ");
		buf.append("    AND V_IC_ONHANDNUM4.CCALBODYID = '").append(channelvofinal.getPk_corrcalbody()).append("' ");
		buf.append(" 	GROUP BY   ");
		buf.append(" 	    CINVENTORYID,   ");
		buf.append(" 	    CINVBASID   ");
		buf.append(" ) result on result.pk_invmandoc=  bd_invmandoc.pk_invmandoc ");
		return buf.toString();
	}

}