UBF DataCommand报表基本代码

用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:          

.csharpcode, .csharpcode pre{font-size: small;color: black;font-family: consolas, "Courier New", courier, monospace;background-color: #ffffff;/*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt {background-color: #f4f4f4;width: 100%;margin: 0em;}.csharpcode .lnum { color: #606060; }

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

（1）利用StringBuilder类直接来拼

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