服务层获取报表数据并放到临时表中
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)";
}
}
}
服务层获取报表数据并放到临时表中
本文2024-09-16 18:27:59发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-22325.html
