服务层获取报表数据并放到临时表中原创
金蝶云社区-weiweibrother
weiweibrother
25人赞赏了该文章 510次浏览 未经作者许可,禁止转载编辑于2024年01月19日 15:25:55

using Kingdee.BOS;

using Kingdee.BOS.App.Data;

using Kingdee.BOS.Core.Enums;

using Kingdee.BOS.Core.List;

using Kingdee.BOS.Core.Report;

using Kingdee.BOS.Orm.DataEntity;

using Kingdee.BOS.Resource;

using Kingdee.BOS.Util;

using Kingdee.K3.FIN.App.Core;

using Kingdee.K3.FIN.CB.App.Report;

using Kingdee.K3.FIN.Core;

using Kingdee.BOS.Model;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using Kingdee.BOS.Contracts;

using Kingdee.BOS.Contracts.Report;

using Kingdee.BOS.ServiceHelper;

using Kingdee.BOS.Model.ReportFilter;


namespace CBEXPAND

{

  public  class TestReport : SysReportBaseService

    {

        private string[] TempTableName;

        public override void Initialize()

        {   //初始化

            base.Initialize();

            // 简单账表类型:普通、树形、分页

            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;

            //通过插件创建

            //是否分组汇总

            this.ReportProperty.IsGroupSummary = true;

        }

        public override ReportTitles GetReportTitles(IRptParams filter)

        {

            ReportTitles reportTitles = new ReportTitles();

            DynamicObject customFilter = filter.FilterParameter.CustomFilter;

            if (customFilter != null)

            {

                string multiOrgnNameValues = customFilter["F_QLDP_Org"].ToString();

                reportTitles.AddTitle("F_QLDP_Org", multiOrgnNameValues);

            }

            return reportTitles;

        }


        public override ReportHeader GetReportHeaders(IRptParams filter)

        {

            ReportHeader header = new ReportHeader();

            ReportHeader headerch = new ReportHeader();

            // 编号

            header.AddChild("FPRODUCTID_FNUMBER", new LocaleValue("标题一&产品编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);

            header.AddChild("FPRODUCTID_FNAME", new LocaleValue("标题一&产品名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);

            header.AddChild("FSPECIFICATION", new LocaleValue("标题一&规格型号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);

            header.AddChild("FBASICUNITFIELD_FNAME", new LocaleValue("标题一&基本单位", this.Context.UserLocale.LCID), SqlStorageType.SqlDatetime);

            header.AddChild("FEXPTYPENAME", new LocaleValue("标题一&费用类型", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);

            header.AddChild("FCURRUSEDHOUR", new LocaleValue("标题二&单位耗用量/工时", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);

            header.AddChild("FDIFFPRICE", new LocaleValue("标题二&数量", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);

            header.AddChild("FDIFFCOSTAMOUNT", new LocaleValue("标题二&单位", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);

            header.AddChild("FDIFFCOMPLETEQTY", new LocaleValue("标题二&含税价", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);

            header.AddChild("FDIFFAMOUNT", new LocaleValue("标题二&价税合计", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);

            return header;

        }


        /// <summary>

        /// 精度控制

        /// </summary>

        //public override void GenerateDecimalControlField()

        //{

        //    base.GenerateDecimalControlField();

        //}


        /// <summary>

        /// 汇总行

        /// </summary>

        /// <param name="filter"></param>

        /// <returns></returns>

        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)

        {

            var result = base.GetSummaryColumnInfo(filter);

            result.Add(new SummaryField("FDIFFCOMPLETEQTY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));

            result.Add(new SummaryField("FDIFFAMOUNT", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));

            return result;

        }


        /// <summary>

        /// 数据源

        /// </summary>

        /// <param name="filter"></param>

        /// <param name="tableName"></param>

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)

        {


            IDBService dbservice = Kingdee.BOS.App.ServiceHelper.GetService<IDBService>();

            TempTableName = dbservice.CreateTemporaryTableName(this.Context, 1);

            string strTable = TempTableName[0];

            //base.BuilderReportSqlAndTempTable(filter, strTable);

            GetReportData(strTable, "CB_PRODUCTRESTORECOSTDIFFRPT", "CB_PRORESTORECOSTDIFFFILTER", "65a4fa38fe8c6e", this.Context);

            this.buildtable(strTable,tableName);

        }

        private void buildtable(string strTable, string tablename)

    {

        string sql = string.Format("/*dialect*/ select t1.*, 1 as Freturnmodata into {1}  from {0} t1", strTable, tablename);

        DBUtils.Execute(this.Context, sql);

        throw new KDException("临时表名为{0}", tablename);


    }

        private void GetReportData(string tablename, string Reportformid, string ReportFilter, string schemeid, Context ctx)

        {

            ISysReportService sysReporSservice = ServiceFactory.GetSysReportService(ctx);

            IPermissionService permissionService = ServiceFactory.GetPermissionService(ctx);

            var filterMetadata = FormMetaDataCache.GetCachedFilterMetaData(ctx);//加载字段比较条件元数据。

            var reportMetadata = FormMetaDataCache.GetCachedFormMetaData(ctx, Reportformid);//加载报表元数据。

            var reportFilterMetadata = FormMetaDataCache.GetCachedFormMetaData(ctx, ReportFilter);//加载过滤条件元数据。

            var reportFilterServiceProvider = reportFilterMetadata.BusinessInfo.GetForm().GetFormServiceProvider();

            var model = new SysReportFilterModel();

            model.SetContext(ctx, reportFilterMetadata.BusinessInfo, reportFilterServiceProvider);

            model.FormId = reportFilterMetadata.BusinessInfo.GetForm().Id;

            model.FilterObject.FilterMetaData = filterMetadata;

            model.InitFieldList(reportMetadata, reportFilterMetadata);

            model.GetSchemeList();

            var entity = model.Load(schemeid);//过滤方案的主键值,可通过该SQL语句查询得到:SELECT * FROM T_BAS_FILTERSCHEME

            var filter = model.GetFilterParameter();

            IRptParams p = new RptParams();

            p.FormId = reportFilterMetadata.BusinessInfo.GetForm().Id;

            p.StartRow = 1;

            p.EndRow = int.MaxValue;//StartRow和EndRow是报表数据分页的起始行数和截至行数,一般取所有数据,所以EndRow取int最大值。

            p.FilterParameter = filter;

            ///这里可以修改过滤条件

            filter.CustomFilter["Year"] = 2023;

            filter.CustomFilter["Period"] = 10;

            filter.CustomFilter["EndYear"] = 2023;

            filter.CustomFilter["EndPeriod"] = 10;

            p.FilterFieldInfo = model.FilterFieldInfo;

            p.BaseDataTempTable.AddRange(permissionService.GetBaseDataTempTable(ctx, reportMetadata.BusinessInfo.GetForm().Id));

            using (DataTable dt = sysReporSservice.GetData(ctx, reportMetadata.BusinessInfo, p))

            {

                //TMPAccountingItemsBalance 定义一个临时表 保存查询报表数据 用来存储获取查询报表数据

                //string sql = string.Format("/*dialect*/delete from {0}", tablename);

                //DBUtils.Execute(this.Context, sql);

                //dt.TableName = tablename;

                  var createTableSql = BuildCreateTableSql(dt, tablename);

                DBUtils.Execute(ctx, createTableSql);

                // 将账表数据存入临时表

                var dataSource = dt.Clone();

                dataSource.TableName = tablename;

                foreach (DataRow row in dt.Rows)

                {

                    dataSource.Rows.Add(row.ItemArray);

                }

                DBUtils.BulkInserts(ctx, dataSource);

            }

            ServiceFactory.CloseService(sysReporSservice);

            ServiceFactory.CloseService(permissionService);


        }

    public static string BuildCreateTableSql(DataTable dt, string tableName)

        {

            var columnInfos = GetColumnInfos(dt);

            var sql = string.Format(@"/*dialect*/

IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'{0}') AND type IN (N'U'))

DROP TABLE {0}

CREATE TABLE {0} ({1})", tableName, string.Join(",", columnInfos.Select(o => o.Item1 + " " + o.Item2)));

            return sql;

        }

        /// <summary>

        /// 根据DataTable生成创建列脚本

        /// </summary>

        /// <param name="dt"></param>

        /// <returns></returns>

        private static List<Tuple<string, string>> GetColumnInfos(DataTable dt)

        {

            var list = new List<Tuple<string, string>>();

            foreach (DataColumn column in dt.Columns)

            {

                list.Add(new Tuple<string, string>(column.ColumnName, GetDbType(column.DataType)));

            }

            return list;

        }

        /// <summary>

        /// 根据数据类型倒推字段的数据库类型

        /// </summary>

        /// <param name="type"></param>

        /// <returns></returns>

        private static string GetDbType(Type type)

        {

            if (type == typeof(int) || type == typeof(short))

            {

                return "INT";

            }

            if (type == typeof(long))

            {

                return "BIGINT";

            }

            if (type == typeof(decimal) || type == typeof(float) || type == typeof(double))

            {

                return "DECIMAL(23,10)";

            }

            if (type == typeof(bool))

            {

                return "BIT";

            }

            if (type == typeof(DateTime))

            {

                return "DATETIME";

            }

            if (type == typeof(byte[]))

            {

                return "IMAGE";

            }

            return "NVARCHAR(4000)";

        }

    }

    }


图标赞 25
25人点赞
还没有人点赞,快来当第一个点赞的人吧!
图标打赏
0人打赏
还没有人打赏,快来当第一个打赏的人吧!