简单账表
本帖将基于实际开发过程，逐步介绍如何开发出一张全新的简单账表 - 采购日报表
1.在BOS设计器中，新建一张空白简单账表
简单修改下报表的名称等属性之后，保存即可。
图一：新建简单账表
图二：简单账表设计器及其关键属性
2.开发报表服务端取数插件
简单账表服务端取数插件，必须从 Kingdee.BOS.Contracts.Report.SysReportBaseService 派生。
更详细的简单账表插件开发资料，可以从如下文档获取：
http://open.kingdee.com/K3Cloud/Wenku/DocumentView.aspx?docId=117280
账表服务取数插件示例代码
插件开发完毕后，编译成*.dll文件。
本帖最后会附上示例报表服务端插件的完整代码。
3.给简单账表，注册服务端插件
把刚刚编译通过的报表插件，注册到服务端插件列表
图三：注册服务器插件
4.新建报表过滤界面
点击新建菜单，继承 BOS - 应用框架 - 动态表单 - 公共过滤，创建一个新的过滤界面，
并在过滤界面上，绘制各个过滤条件字段。
绘制完毕，保存。
图四：新建过滤界面
5：配置简单账表的过滤界面
把新建的过滤界面的标识，复制粘贴到简单账表的"过滤窗口业务对象"属性上
6.修改报表服务端取数插件，根据过滤界面上的条件，据此过滤数据
在BuilderReportSqlAndTempTable事件中，通过读取filter.FilterParameter对象，获取到完整的过滤方案，据此拼装出过滤条件、排序字段等。
本帖的示例代码中，未读取过滤条件，请自行尝试。
7.发布主控台
把报表发布到主控台，为了快速看到效果，建议先取消报表的权限控制。
8.浏览报表效果
登录K/3 Cloud，打开报表，浏览报表的运行效果，调试与修正报表插件问题。
图五：本案例的报表效果
实例代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using Kingdee.BOS;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Core.Report.PlugIn;
using Kingdee.BOS.Core.Report.PlugIn.Args;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Orm.DataEntity;
namespace JDSample.ServicePlugIn.Report
{
/// <summary>
/// 自定义简单账表：服务端插件
/// </summary>
/// <remarks>
/// 开发目的：用以学习简单账表插件的各种事件、属性
///
/// 案例：
/// 按日期，搜索采购订单，
/// 输出：编号、状态、物料、数量、单位、单价、价税合计
/// 可以对物料汇总；
/// 默认按编号排序
/// 数量、价税合计需要合计
/// 数量、单价、价税合计，需控制精度
/// 相同的采购订单行，编号不重复显示
/// </remarks>
[Description("自定义简单账表")]
public class S151016MySysReport : SysReportBaseService
{
/***********事件执行顺序*************
2015/8/31 18:04:12 : Initialize
2015/8/31 18:04:12 : GetTableName
2015/8/31 18:04:15 : BuilderReportSqlAndTempTable
2015/8/31 18:04:15 : GetIdentityFieldIndexSQL
2015/8/31 18:04:15 : ExecuteBatch
2015/8/31 18:04:19 : GetReportHeaders
2015/8/31 18:04:19 : GetReportTitles
2015/8/31 18:04:27 : GetTableName
2015/8/31 18:04:27 : GetIdentityFieldIndexSQL
2015/8/31 18:04:28 : ExecuteBatch
2015/8/31 18:04:28 : AnalyzeDspCloumn
2015/8/31 18:04:28 : AfterCreateTempTable
2015/8/31 18:04:28 : GetSummaryColumnInfo
2015/8/31 18:04:28 : GetSummaryColumsSQL
2015/8/31 18:04:28 : GetTableName
2015/8/31 18:04:28 : GetTableName
2015/8/31 18:04:29 : ExecuteBatch
2015/8/31 18:04:29 : GetIdentityFieldIndexSQL
2015/8/31 18:04:29 : ExecuteBatch
2015/8/31 18:04:29 : CreateGroupSummaryData
2015/8/31 18:04:29 : GetListData
2015/8/31 18:04:30 : GetReportData
2015/8/31 18:04:30 : GetRowsCount
2015/8/31 18:04:30 : GetListData
*/
/// <summary>
/// 初始化事件：在此事件中，设置报表基本属性
/// </summary>
/// <remarks>
///
/// </remarks>
public override void Initialize()
{
base.Initialize();
// 简单账表类型：普通、树形、分页
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
// 报表名称
this.ReportProperty.ReportName = new LocaleValue("日采购报表", base.Context.UserLocale.LCID);
//
this.IsCreateTempTableByPlugin = true;
//
this.ReportProperty.IsUIDesignerColumns = false;
//
this.ReportProperty.IsGroupSummary = true;
//
this.ReportProperty.SimpleAllCols = false;
// 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
this.ReportProperty.PrimaryKeyFieldName = "FID";
//
this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
// 报表主键字段名：默认为FIDENTITYID，可以修改
//this.ReportProperty.IdentityFieldName = "FIDENTITYID";
//
// 设置精度控制
List<DecimalControlField> list = new List<DecimalControlField>();
// 数量
list.Add(new DecimalControlField
{
ByDecimalControlFieldName = "FQty",
DecimalControlFieldName = "FUnitPrecision"
});
// 单价
list.Add(new DecimalControlField
{
ByDecimalControlFieldName = "FTAXPRICE",
DecimalControlFieldName = "FPRICEDIGITS"
});
// 金额
list.Add(new DecimalControlField
{
ByDecimalControlFieldName = "FALLAMOUNT",
DecimalControlFieldName = "FAMOUNTDIGITS"
});
this.ReportProperty.DecimalControlFieldList = list;
}
public override string GetTableName()
{
var result = base.GetTableName();
return result;
}
/// <summary>
/// 向报表临时表，插入报表数据
/// </summary>
/// <param name="filter"></param>
/// <param name="tableName"></param>
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
// 拼接过滤条件 ： filter
// 略
// 默认排序字段：需要从filter中取用户设置的排序字段
base.KSQL_SEQ = string.Format(base.KSQL_SEQ, " FID ");
// 取数SQL
// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计
string sql = string.Format(@"/*dialect*/
select t0.FID, t1.FENTRYID
,t0.FBILLNO
,t0.FDOCUMENTSTATUS
,t2.FLOCALCURRID
,ISNULL(t20.FPRICEDIGITS,4) AS FPRICEDIGITS
,ISNULL(t20.FAMOUNTDIGITS,2) AS FAMOUNTDIGITS
,t1.FMATERIALID
,t1M_L.FNAME as FMaterialName
,t1.FQTY
,t1u.FPRECISION as FUnitPrecision
,t1U_L.FNAME as FUnitName
,t1f.FTAXPRICE
,t1f.FALLAMOUNT
,row_number() over (order by t0.FID) as FIDENTITYID
into {0}
from T_PUR_POORDER t0
inner join T_PUR_POORDERFIN t2 on (t0.FID = t2.FID)
left join T_BD_CURRENCY t20 on (t2.FLOCALCURRID = t20.FCURRENCYID)
inner join T_PUR_POORDERENTRY t1 on (t0.FID = t1.FID)
left join T_BD_MATERIAL_L t1M_L on (t1.FMATERIALID = t1m_l.FMATERIALID and t1M_L.FLOCALEID = 2052)
inner join T_PUR_POORDERENTRY_F t1F on (t1.FENTRYID = t1f.FENTRYID)
left join T_BD_UNIT t1U on (t1f.FPRICEUNITID = t1u.FUNITID)
left join T_BD_UNIT_L t1U_L on (t1U.FUNITID = t1U_L.FUNITID and t1U_L.FLOCALEID = 2052) ",
tableName);
DBUtils.ExecuteDynamicObject(this.Context, sql);
}
protected override string GetIdentityFieldIndexSQL(string tableName)
{
string result = base.GetIdentityFieldIndexSQL(tableName);
return result;
}
protected override void ExecuteBatch(List<string> listSql)
{
base.ExecuteBatch(listSql);
}
/// <summary>
/// 构建出报表列
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
/// <remarks>
/// // 如下代码，演示如何设置同一分组的分组头字段合并
/// // 需配合Initialize事件，设置分组依据字段(PrimaryKeyFieldName)
/// ReportHeader header = new ReportHeader();
/// header.Mergeable = true;
/// int width = 80;
/// ListHeader headChild1 = header.AddChild("FBILLNO", new LocaleValue("应付单号"));
/// headChild1.Width = width;
/// headChild1.Mergeable = true;
///
/// ListHeader headChild2 = header.AddChild("FPURMAN", new LocaleValue("采购员"));
/// headChild2.Width = width;
/// headChild2.Mergeable = true;
/// </remarks>
public override ReportHeader GetReportHeaders(IRptParams filter)
{
// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计
ReportHeader header = new ReportHeader();
// 编号
var status = header.AddChild("FDocumentStatus", new LocaleValue("状态"));
status.ColIndex = 0;
var billNo = header.AddChild("FBillNo", new LocaleValue("单据编号"));
billNo.ColIndex = 1;
billNo.IsHyperlink = true; // 支持超链接
var material = header.AddChild("FMaterialName", new LocaleValue("物料"));
material.ColIndex = 2;
var qty = header.AddChild("FQty", new LocaleValue("数量"), SqlStorageType.SqlDecimal);
qty.ColIndex = 3;
var unit = header.AddChild("FUnitName", new LocaleValue("单位"));
unit.ColIndex = 4;
var price = header.AddChild("FTAXPRICE", new LocaleValue("含税价"), SqlStorageType.SqlDecimal);
price.ColIndex = 5;
var amount = header.AddChild("FALLAMOUNT", new LocaleValue("价税合计"), SqlStorageType.SqlDecimal);
amount.ColIndex = 6;
return header;
}
public override ReportTitles GetReportTitles(IRptParams filter)
{
var result = base.GetReportTitles(filter);
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
if (dyFilter != null)
{
if (result == null)
{
result = new ReportTitles();
}
result.AddTitle("F_JD_Date", Convert.ToString(dyFilter["F_JD_Date"]));
}
return result;
}
protected override string AnalyzeDspCloumn(IRptParams filter, string tablename)
{
string result = base.AnalyzeDspCloumn(filter, tablename);
return result;
}
protected override void AfterCreateTempTable(string tablename)
{
base.AfterCreateTempTable(tablename);
}
/// <summary>
/// 设置报表合计列
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
{
var result = base.GetSummaryColumnInfo(filter);
result.Add(new SummaryField("FQty", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
result.Add(new SummaryField("FALLAMOUNT", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
return result;
}
protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
{
var result = base.GetSummaryColumsSQL(summaryFields);
return result;
}
protected override System.Data.DataTable GetListData(string sSQL)
{
var result = base.GetListData(sSQL);
return result;
}
protected override System.Data.DataTable GetReportData(IRptParams filter)
{
var result = base.GetReportData(filter);
return result;
}
protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
{
var result = base.GetReportData(tablename, filter);
return result;
}
public override int GetRowsCount(IRptParams filter)
{
var result = base.GetRowsCount(filter);
return result;
}
protected override string BuilderFromWhereSQL(IRptParams filter)
{
string result = base.BuilderFromWhereSQL(filter);
return result;
}
protected override string BuilderSelectFieldSQL(IRptParams filter)
{
string result = base.BuilderSelectFieldSQL(filter);
return result;
}
protected override string BuilderTempTableOrderBySQL(IRptParams filter)
{
string result = base.BuilderTempTableOrderBySQL(filter);
return result;
}
public override void CloseReport()
{
base.CloseReport();
}
protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
{
string result = base.CreateGroupSummaryData(filter, tablename);
return result;
}
protected override void CreateTempTable(string sSQL)
{
base.CreateTempTable(sSQL);
}
public override void DropTempTable()
{
base.DropTempTable();
}
public override System.Data.DataTable GetList(IRptParams filter)
{
var result = base.GetList(filter);
return result;
}
public override List<long> GetOrgIdList(IRptParams filter)
{
var result = base.GetOrgIdList(filter);
return result;
}
public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
{
var result = base.GetTreeNodes(filter);
return result;
}
}
}
