UBF DataCommand报表基本代码_ubf得到oql-优快云博客

本文链接：https://blog.youkuaiyun.com/hduzh/article/details/4882169

本文介绍U9报表开发中使用临时表的方法，包括创建临时表、定义表结构及通过不同方式拼接OQL查询语句。重点讲解了收货单与采购订单临时表的创建过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用DataCommand方式，主要要学会使用临时表

临时表是基于数据库连接的，详细可参考U9研发体系（自定义报表开发手册）第8章

   1:          #region 临时表名定义部分

   2:          private const string ResultTempTable = "ResultTempTable";

   3:          private const string RcvTempTable = "RcvTempTable";

   4:          private const string POTempTable = "POTempTable";

   5:          #endregion

6:

   7:          #region 业务逻辑处理部分

8:

   9:          /// <summary>

  10:          /// 报表业务处理过程,以下是默认代码，请根据具体业务逻辑修改

  11:          /// </summary>

  12:          private void ProcessData()

  13:          {

  14:              GetRcvTempTable();

  15:              GetPOTempTable();

  16:              StringBuilder sb = new StringBuilder();

  17:              sb.Append("select A.Supplier_Code,A.RcvDate,A.RcvAmount,A.RcvWay,B.PlanArriveDate ");

  18:              sb.Append("from RcvTempTable as A ");

  19:              sb.Append("left join POTempTable as B  ");

  20:              sb.Append("on A.SrcDocNo=B.DocNo");

  21:              SimpleOqlTool oql = new SimpleOqlTool();

  22:              this.ReportResultOqlString = sb.ToString();

  23:          }

24:

  25:          /// <summary>

  26:          /// 创建收货单的临时表

  27:          /// </summary>

  28:          private void GetRcvTempTable()

  29:          {

  30:              /* select Supplier.Code,BusinessDate,sum(RcvLines.ArriveTotalMnyAC) as Amount,"让步接收" as RcvWay

  31:               * from UFIDA::U9::PM::Rcv::Receivement

  32:               * where DocNo in (select  SrcDocNo from UFIDA::U9::QC::QCDocBE::QCDocLine  where QCDocResults.QCResult=6)

  33:               * group by Supplier.Code,BusinessDate

  34:               */

  35:              StringBuilder sb = new StringBuilder();

  36:              sb.Append("select ");

  37:              sb.Append("Supplier.Code as Supplier_Code,BusinessDate as RcvDate,");

  38:              sb.Append("sum(RcvLines.ArriveTotalMnyAC) as RcvAmount,'让步接收' as RcvWay,RcvLines.SrcDoc.SrcDocNo as SrcDocNo ");

  39:              sb.Append("from ");

  40:              sb.Append("UFIDA::U9::PM::Rcv::Receivement ");

  41:              sb.Append("where ");

  42:              sb.Append("DocNo in (select  SrcDocNo from UFIDA::U9::QC::QCDocBE::QCDocLine  where QCDocResults.QCResult=6) ");

  43:              if (this.Parameters["RcvDate"] != null)

  44:              {

  45:                  sb.Append(" and RcvDate between " +

  46:                      this.Parameters["RcvDate"].Values[0] +

  47:                      " and " + this.Parameters["RcvDate"].Values[1]);

  48:              }

  49:              sb.Append("group by ");

  50:              sb.Append("Supplier.Code,BusinessDate,RcvLines.SrcDoc.SrcDocNo");

  51:              TempTableUtil.CreateTempTableByOql(RcvTempTable, sb.ToString(), viewQuery);

  52:          }

53:

  54:          private void GetPOTempTable()

  55:          {

  56:              /* select DocNo,max(POLines.POShiplines.PlanArriveDate) as PlanArriveDate

  57:               * from UFIDA::U9::PM::PO::PurchaseOrder group by DocNo

  58:               */

  59:              SimpleOqlTool oql = new SimpleOqlTool();

  60:              oql.AddSelect("DocNo");

  61:              oql.AddSelect("max(POLines.POShiplines.PlanArriveDate) as PlanArriveDate");

  62:              oql.SetFromClause("UFIDA::U9::PM::PO::PurchaseOrder");

  63:              oql.AddGroup("DocNo");

  64:              TempTableUtil.CreateTempTableByOql(POTempTable, oql.GetOqlString(), viewQuery);

  65:          }

  66:          #endregion

67:

  68:          #region  临时表定义部分

  69:            /// <summary>

  70:            /// 定义结果临时表的结构 ResultTempTable

  71:            /// </summary>

  72:            private void DefineResultTempTableSchema()

  73:            {

  74:           　     Column[] cols = new Column[]

  75:               {

  76:                   new Column("Supplier_Code", "nvarchar(50)"),

  77:                   new Column("RcvDate", "datetime"),

  78:                   new Column("RcvAmount","decimal(24,9)"),

  79:                   new Column("RcvWay","nvarchar(50)"),

  80:                   new Column("PlanArriveDate","datetime"),

  81:               };

  82:               viewQuery.DefineTempCollection(ResultTempTable, cols);

  83:            }

  84:            #endregion

85:

86:

这里需要注意一点，拼Oql的方法有两种

（1）利用StringBuilder类直接来拼

（2）利用Oql的工具类SimpleOqlTool类来拼，详细可参考U9研发体系，报表开发手册