最近做项目中,(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();
}
}