本文主要是介绍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("存货档案");}@Overridepublic 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()+ "'");}// 设置默认列数为11setDefaultFieldCount(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);}@Overrideprotected 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();}}
这篇关于NC参照SQL追踪的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
