DataCommand生成报表--- 多个临时表建立一个报表

 

DataCommand 创建报表时经常要使用到临时表，当业务复杂时会碰到多个临时报表合并的问题，现将此问题总结如下，已两个临时表合并为例：

1、建立临时表，建立临时表的方法有多种，此处以一种为例,分别建立两个临时表：

        /// <summary>

        /// 通过OQL创建一个临时的表

        /// </summary>

        private void MyCreateTempTableByOql()

        {

            StringBuilder sb = new StringBuilder();

            sb.Append("select SO.DOCNo as SO_DocNo, MO.DOCNO as MO_DocNo,MO.Department.Name as MO_Department, SO.SOLines.ItemInfo.ItemCode as ItemInfo_ItemCode, ");

            sb.Append("SO.SOLines.ItemInfo.ItemName as ItemInfo_ItemName,SO.SOLines.TU.Name as TU_Name, SO.SOLines.OrderByQtyTU as SOLines_OrderByQtyTU, ");

            sb.Append("SO.SOLines.SOShiplines.PlanDate as SOShiplines_PlanDate, fn_SO_GetIssuedQty(MO.DOCNO,SO.SOLines.OrderByQtyTU) as IssuedQty, MO.TotalCompleteQty as MO_TotalCompleteQty,  ");

            sb.Append("(SO.SOLines.OrderByQtyTU - MO.TotalCompleteQty) as WWC_Num, (MO.TotalCompleteQty / SO.SOLines.OrderByQtyTU) as CompletionRate, ");

            sb.Append(" MO.ActualCompleteDate as MO_ActualCompleteDate ,DATEDIFF( day, SO.SOLines.SOShiplines.PlanDate, MO.ActualCompleteDate) as YQ_Date ");

            sb.Append(" from UFIDA::U9::SM::SO::SO SO left join UFIDA::U9::MO::MO::MO MO ");

            sb.Append(" on SO.DOCNO = MO.SrcDoc.SrcDocNo where MO.SrcDoc.MOSrcDocType = 0 ");

            if (!string.IsNullOrEmpty(this.Parameters["MO_Date"].ToString()))

            {

                sb.Append(" and  SO.BusinessDate between '" + this.Parameters["MO_Date"].Values[0].ToString() + "' and '" + this.Parameters["MO_Date"].Values[1].ToString() +"'");

            }

            this.Oql = sb.ToString();

            TempTableUtil.CreateTempTableByOql("CustomerDailyTable", this.Oql, this.viewQuery);

        }

 

        /// <summary>

        ///  通过OQL创建第二个临时表

        /// </summary>

        private void ItemMasterTempTableByOql()

        {

            StringBuilder sb = new StringBuilder();

            //

            sb.Append("select  M.code ,fn_GetLevelItemCatName(dv.ID,2) as catName, dv.TreeDisplayName + '.' + M.Segment2 as SPEC ");

            sb.Append("from UFIDA::U9::CBO::SCM::Item::ItemMaster M ");

            sb.Append("inner join UFIDA::U9::Base::FlexField::ValueSet::DefineValue dv on M.Segment1=dv.Code  ");

            sb.Append("where  dv.Level>1 and (dv.ValueSetDef in ");

            sb.Append("(select V.ID from UFIDA::U9::Base::FlexField::ValueSet::ValueSetDef V  where V.Code='008')) ");

            this.Oql = sb.ToString();

            TempTableUtil.CreateTempTableByOql("ItemMasterTable", this.Oql, this.viewQuery);

        }

 

2、合并临时表,拼一个sql语句将要显示到报表上的所有列都写到新语句中，查询两个临时表并在后面加关联条件where a.ItemInfo_ItemCode = b.code就OK了

         /// <summary>

        /// 合并两个临时表

        /// </summary>

        private void GetData()

        {

            StringBuilder sb = new StringBuilder();

            sb.Append("select a.SO_DocNo,a.MO_DocNo,a.MO_Department,a.ItemInfo_ItemCode,a.ItemInfo_ItemName,a.TU_Name,a.SOLines_OrderByQtyTU, ");

            sb.Append("a.SOShiplines_PlanDate,a.IssuedQty,a.MO_TotalCompleteQty,a.WWC_Num,a.CompletionRate,a.MO_ActualCompleteDate ,a.YQ_Date, ");

            sb.Append("b.catName , b.SPEC ");

            sb.Append("from CustomerDailyTable a, ItemMasterTable b where a.ItemInfo_ItemCode = b.code ");

            TempTableUtil.CreateTempTableByOql("SaleOrgWorkDate",sb.ToString(), this.viewQuery);

        }

 

3、调用以上三个方法

        /// <summary>

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

        /// </summary>

        private void ProcessData()

        {            

            MyCreateTempTableByOql();

            ItemMasterTempTableByOql();

            GetData();     //注意调用顺序，合并临时表的方法最后

            SimpleOqlTool oqlTool = new SimpleOqlTool();

            foreach (string field in this.SelectFields)

            {

                oqlTool.AddSelect(field);

                oqlTool.AddSelect("Main." + field, field);

            }

            oqlTool.SetFromClause("SaleOrgWorkDate Main");

            this.ReportResultOqlString = oqlTool.GetOqlString();

        }

 

4、添加所有需要在报表上显示的列

          /// <summary>

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

          /// </summary>

          private void DefineResultTempTableSchema()

          {

         　     Column[] cols = new Column[]

             {

                 new Column("SO_DocNo", "nvarchar(50)"),

                 new Column("MO_DocNo","nvarchar(50)"),

                 new Column("MO_Department","nvarchar(255)"),

                 new Column("ItemInfo_ItemCode", "nvarchar(255)"),

                 new Column("ItemInfo_ItemName", "nvarchar(255)"),

                 new Column("SOLines_OrderByQtyTU", "decimal(24,9)"),

                 new Column("SOShiplines_PlanDate", "datetime"),

                 new Column("IssuedQty","decimal(24,9)"),   //已开票领料套数

                   new Column("MO_TotalCompleteQty","decimal(24,9)"),

                 new Column("WWC_Num","decimal(24,9)"),           //车间未完成数

                   new Column("CompletionRate","decimal(24,9)"),    //完成率

    new Column("MO_ActualCompleteDate", "datetime"),

                 new Column("YQ_Date","int"),    //延期天数

    new Column("TU_Code", "nvarchar(50)"),

                 new Column("TU_Name", "nvarchar(50)"),

                 new Column("catName","nvarchar(255)"),  //二级料品分类

    new Column("SPEC","nvarchar(255)"),   //规格

                 new Column("MO_Date", "datetime"),

             };

             viewQuery.DefineTempCollection(ResultTempTable, cols);                                

          }

 

到此为止多个临时表建立一个报表的工作已经全部完成。