以上部分请参考第一部分、第二部分
一、准备插件开发环境
三、插件开发
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();
}
}
}
发布于 广分开发部技术交流圈子 社群
推荐阅读