摘要由AI智能服务提供本文介绍了在金蝶系统中开发一个简单账表插件的全过程。包括准备开发环境、创建空白账表并注册插件、插件开发的具体步骤（如初始化事件定义报表参数、组装拼接SQL语句等）、重新生成插件并重启IIS、发布账表到主控台菜单、进行权限授权、使用授权用户查看报表以及插件脚本的编写和注意事项。详细说明了如何设置报表属性、处理过滤条件、拼接SQL语句等关键技术点。
以上部分请参考第一部分、第二部分
一、准备插件开发环境
三、插件开发
3.1、 Initialize()初始化事件定义报表参数
3.2、BuilderReportSqlAndTempTable（）事件组装拼接SQL语句
首先接收过滤条件，再将过滤条件拼接到取数SQL语句中
3.3、重新生成插件，生成成功后，重启IIS。
3.4、发布账表到主控台菜单
3.5、使用管理员账户登陆主控台【全功能批量授权】对采购订单明细报表进行授权（案例只是为了实现快速授权，需要根据实际情况而定），如图 下图所示。
3.6、使用授权用户登陆主控台打开已发布的LDZ采购订单明细报表进行查看，如图所示。
3.7、插件脚本
将以下脚本复制粘贴到插件中，注意修改namespace和类名
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 Ldzprj2
{
[Kingdee.BOS.Util.HotUpdate]
[Description ("简单账表插件")]
public class LdzTest:SysReportBaseService
{
/// <summary>
/// 过滤SQL语句拼接
/// </summary>
//private string _projectManagerId;
private StringBuilder _filterSql;
/// <summary>
/// 开始日期
/// </summary>
private DateTime _startDate;
/// <summary>
/// 截止日期
/// </summary>
private DateTime _endDate;
/// <summary>
/// 物料ID
/// </summary>
private string _matId;
/// <summary>
/// 初始化事件：在此事件中，设置报表基本属性
/// </summary>
/// <remarks>
///
/// </remarks>
public override void Initialize()
{
base.Initialize();
// 简单账表类型：普通、树形、分页
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
this.ReportProperty.BillKeyFieldName = "FID";
this.ReportProperty.FormIdFieldName = "FOBJECTTYPEID";
// 报表名称
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
this.GetFilter(filter);
// 默认排序字段：需要从filter中取用户设置的排序字段
string seqFld = string.Format(base.KSQL_SEQ, " t0.FID ");
// 取数SQL
// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计
string sql = string.Format(@"/*dialect*/
select t0.FID, t1.FENTRYID
,t0.FBILLNO
,t0.FDate
,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,t0.FOBJECTTYPEID
,{0}
into {1}
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) where t0.FDOCUMENTSTATUS ='C' {2} ",
seqFld,
tableName, _filterSql);
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>
/// </remarks>
public override ReportHeader GetReportHeaders(IRptParams filter)
{
//return base.GetReportHeaders(filter);
// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计
ReportHeader header = new ReportHeader();
// 编号
var status = header.AddChild("FDate", new LocaleValue("单据日期"));
status.ColIndex = 0;
var billNo = header.AddChild("FBillNo", new LocaleValue("采购单号"));
billNo.ColIndex = 1;
billNo.IsHyperlink = true; // 支持超链接
var materialid = header.AddChild("FMATERIALID", new LocaleValue("物料编码"));
materialid.ColIndex = 2;
materialid.IsHyperlink = true;
var material = header.AddChild("FMaterialName", new LocaleValue("物料名称2"));
material.ColIndex = 3;
var qty = header.AddChild("FQty", new LocaleValue("数量"), SqlStorageType.SqlDecimal);
qty.ColIndex = 4;
var unit = header.AddChild("FUnitName", new LocaleValue("单位"));
unit.ColIndex = 5;
var price = header.AddChild("FTAXPRICE", new LocaleValue("含税价"), SqlStorageType.SqlDecimal);
price.ColIndex = 6;
var amount = header.AddChild("FALLAMOUNT", new LocaleValue("价税合计"), SqlStorageType.SqlDecimal);
amount.ColIndex = 7;
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_ora_SDate", Convert.ToString(dyFilter["F_ora_SDate"]));
result.AddTitle("F_ora_EDate", Convert.ToString(dyFilter["F_ora_EDate"]));
}
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;
}
private void GetFilter(IRptParams filter)
{
_filterSql = new StringBuilder();
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
this._startDate = Convert.ToDateTime(dyFilter["F_ora_SDate"]);
this._endDate = Convert.ToDateTime(dyFilter["F_ora_EDate"]);
//_endDate.ToShortDateString()
_filterSql.Append(string.Format(" and convert(varchar(10),t0.FDATE,120) BETWEEN '{0}' and '{1}' ", _startDate.ToString("yyyy-MM-dd"), _endDate.ToString("yyyy-MM-dd")));
this._matId = this.GetMoreBaseDataByKey(dyFilter, "F_ora_MulMat");
if (_matId != string.Empty)
{
_filterSql.Append(string.Format(" and t1.FMATERIALID in ({0})", _matId));
}
string filterstr = filter.FilterParameter.FilterString;
if (filterstr != string.Empty )
{
filterstr.Replace("FBillNo", "t0.FBILLNO");
_filterSql.Append(" and " + filterstr);
}
}
/// <summary>
/// 获得多选基础资料的ID
/// </summary>
/// <param name="doFilter"></param>
/// <param name="sKey"></param>
/// <returns></returns>
private string GetMoreBaseDataByKey(DynamicObject doFilter, string sKey)
{
string sReturnValue = string.Empty;
//StringBuilder str = new StringBuilder("'-1'");
StringBuilder str = new StringBuilder();
if (!doFilter.IsNullOrEmpty())
{
DynamicObjectCollection cols = doFilter[sKey] as DynamicObjectCollection;
foreach (DynamicObject col in cols)
{
if (str.ToString() == string.Empty)
{
str.Append("'").Append(col[sKey + "_Id"]).Append("'");
}
else
{
str.Append(",'").Append(col[sKey + "_Id"]).Append("'");
}
}
}
return str.ToString();
}
}
}
发布于 广分开发部技术交流圈子 社群
赞 20
打赏