简单账表有个分组功能，由于我报表的列标题是动态设置的，比如标题是16年，17年，18年，随着年份变，所以我用插件来实现。
（我另一张计算公式复杂的报表，用插件也方便判断）。
我想实现图3的分组行小计功能，虽然金蝶顾问让我用GetSummaryColumnInfo()，但这也只实现了最后一行合计。
我总得要有地方指定分列的列吧？
在插件要如何实现图3的效果呢？
using System;
using System.Collections.Generic;
using System.Collections;
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;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.K3.MFG.App;
namespace GE.FIN.BudgetManage
{
[Description("销售预算汇总")]
public class PABR_FIN_FBM_SaleSum : SysReportBaseService
{
string version = "", table_name = "";
///
/// 初始化事件：在此事件中，设置报表基本属性
///
///
///
///
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 = "FDept";
//
this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
// 报表主键字段名：默认为FIDENTITYID，可以修改
this.ReportProperty.IdentityFieldName = "AutoID";
}
///
/// 向报表临时表，插入报表数据
///
///
///
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
table_name = tableName;
base.BuilderReportSqlAndTempTable(filter, tableName);
// 拼接过滤条件 ： filter
// 略
// 默认排序字段：需要从filter中取用户设置的排序字段
base.KSQL_SEQ = string.Format(base.KSQL_SEQ, " AutoID ");
// 取数SQL
string sql = @"/*dialect*/
create table {0} as
select row_number() over (order by FDEPT) AutoID,
case when FDept='前装业务部' then '前装' else '后装' end XSLX,
FNWX,FDept,FCategory,0 FYJSJ_JN_1,0 FYJSJ_JN_2,0 FYJSJ_MN,0 FYJSJ_HN,
sum(FXLYC_1) FXLYC_1,sum(FXLYC_2) FXLYC_2,sum(FXLYC_3) FXLYC_3,sum(FXLYC_4) FXLYC_4,
sum(FXLYC_5) FXLYC_5,sum(FXLYC_6) FXLYC_6,sum(FXLYC_7) FXLYC_7,sum(FXLYC_8) FXLYC_8,
sum(FXLYC_9) FXLYC_9,sum(FXLYC_10) FXLYC_10,sum(FXLYC_11) FXLYC_11,sum(FXLYC_12) FXLYC_12,
sum(FXLYC_SUM) FXLYC_SUM,sum(FXLYC_MN) FXLYC_MN,sum(FXLYC_HN) FXLYC_HN,
sum(FSRYC_1) FSRYC_1,sum(FSRYC_2) FSRYC_2,sum(FSRYC_3) FSRYC_3,sum(FSRYC_4) FSRYC_4,
sum(FSRYC_5) FSRYC_5,sum(FSRYC_6) FSRYC_6,sum(FSRYC_7) FSRYC_7,sum(FSRYC_8) FSRYC_8,
sum(FSRYC_9) FSRYC_9,sum(FSRYC_10) FSRYC_10,sum(FSRYC_11) FSRYC_11,sum(FSRYC_12) FSRYC_12,
sum(FSRYC_SUM) FSRYC_SUM,sum(FSRYC_MN) FSRYC_MN,sum(FSRYC_HN) FSRYC_HN
from GE_T_FIN_FBM_Budgets d
left join GE_T_FIN_FBM_Budget h on d.fid=h.fid
where h.FBillNo='{1}'
{2}
group by FDept,FNWX,FCategory
order by FDEPT ";
//获取快捷页签的过滤字段数据
DynamicObject customFil = filter.FilterParameter.CustomFilter;
version = (((DynamicObject)customFil["FVersion"])["Number"]).ToString();
string sql_where = "";
sql = string.Format(sql, tableName, version, sql_where);
DBUtils.Execute(this.Context, sql);
//反算售价
sql = @"/*dialect*/
update {0} set
FYJSJ_JN_1=round((FSRYC_1+FSRYC_2+FSRYC_3+FSRYC_4+FSRYC_5+FSRYC_6)/(FXLYC_1+FXLYC_2+FXLYC_3+FXLYC_4+FXLYC_5+FXLYC_6),2)
where (FXLYC_1+FXLYC_2+FXLYC_3+FXLYC_4+FXLYC_5+FXLYC_6)<>0";
sql = string.Format(sql, tableName);
DBUtils.Execute(this.Context, sql);
sql = @"/*dialect*/
update {0} set
FYJSJ_JN_2=round((FSRYC_7+FSRYC_8+FSRYC_9+FSRYC_10+FSRYC_11+FSRYC_12)/(FXLYC_7+FXLYC_8+FXLYC_9+FXLYC_10+FXLYC_11+FXLYC_12),2)
where (FXLYC_7+FXLYC_8+FXLYC_9+FXLYC_10+FXLYC_11+FXLYC_12)<>0";
sql = string.Format(sql, tableName);
DBUtils.Execute(this.Context, sql);
sql = @"/*dialect*/
update {0} set
FYJSJ_MN=round(FSRYC_MN/FXLYC_MN,2)
where FXLYC_MN<>0";
sql = string.Format(sql, tableName);
DBUtils.Execute(this.Context, sql);
sql = @"/*dialect*/
update {0} set
FYJSJ_HN=round(FSRYC_HN/FXLYC_HN,2)
where FXLYC_HN<>0";
sql = string.Format(sql, tableName);
DBUtils.Execute(this.Context, sql);
}
public override void CloseReport()
{
//删除临时表
AppServiceContext.DBService.DeleteTemporaryTableName(base.Context, new string[] { table_name });
}
/*
protected override string GetIdentityFieldIndexSQL(string tableName)
{
string result = base.GetIdentityFieldIndexSQL(tableName);
return result;
}
protected override void ExecuteBatch(List
{
base.ExecuteBatch(listSql);
}*/
///
/// 构建出报表列
///
///
///
///
/// // 如下代码，演示如何设置同一分组的分组头字段合并
/// // 需配合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;
///
public override ReportHeader GetReportHeaders(IRptParams filter)
{
string y0 = "去", y = "今", y2 = "明", y3 = "后";
if (version != null && !version.Trim().Equals(""))
{
string sql = "select FCurYear from GE_T_FIN_FBM_Version where FNumber='" + version + "'";
string begin_year = DBUtils.ExecuteScalar<string>(this.Context, sql, null, null);
y = begin_year.Substring(2, 2);
y0 = (Convert.ToInt32(y) - 1).ToString();
y2 = (Convert.ToInt32(y) + 1).ToString();
y3 = (Convert.ToInt32(y) + 2).ToString();
}
// FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计
ReportHeader header = new ReportHeader();
// 编号
var AutoID = header.AddChild("AutoID", new LocaleValue("序号"));
AutoID.ColIndex = 0;
AutoID.Visible = false;
AutoID.Width = 35;
var XSLX = header.AddChild("XSLX", new LocaleValue("销售类型"));
XSLX.Mergeable = true;
var FNWX = header.AddChild("FNWX", new LocaleValue("销售区域"));
FNWX.Mergeable = true;
var FDept = header.AddChild("FDept", new LocaleValue("业务部门"));
FDept.Mergeable = true;
var FCategory = header.AddChild("FCategory", new LocaleValue("产品类别"));
var FYJSJ_JN_1 = header.AddChild("FYJSJ_JN_1", new LocaleValue("预计单价：" + y + "年上半年"));
FYJSJ_JN_1.Width = 140;
header.AddChild("FYJSJ_JN_2", new LocaleValue(y + "年下半年"));
header.AddChild("FYJSJ_MN", new LocaleValue(y2 + "年"));
header.AddChild("FYJSJ_HN", new LocaleValue(y3 + "年"));
var FXLYC_1 = header.AddChild("FXLYC_1", new LocaleValue(y + "年销量:1月"));
FXLYC_1.Width = 120;
header.AddChild("FXLYC_2", new LocaleValue("2月"));
header.AddChild("FXLYC_3", new LocaleValue("3月"));
header.AddChild("FXLYC_4", new LocaleValue("4月"));
header.AddChild("FXLYC_5", new LocaleValue("5月"));
header.AddChild("FXLYC_6", new LocaleValue("6月"));
header.AddChild("FXLYC_7", new LocaleValue("7月"));
header.AddChild("FXLYC_8", new LocaleValue("8月"));
header.AddChild("FXLYC_9", new LocaleValue("9月"));
header.AddChild("FXLYC_10", new LocaleValue("10月"));
header.AddChild("FXLYC_11", new LocaleValue("11月"));
header.AddChild("FXLYC_12", new LocaleValue("12月"));
header.AddChild("FXLYC_SUM", new LocaleValue(y + "年合计"));
header.AddChild("FXLYC_MN", new LocaleValue(y2 + "年销量"));
header.AddChild("FXLYC_HN", new LocaleValue(y3 + "年销量"));
var FSRYC_1 = header.AddChild("FSRYC_1", new LocaleValue(y + "年预计收入:1月"));
FSRYC_1.Width = 120;
header.AddChild("FSRYC_2", new LocaleValue("2月"));
header.AddChild("FSRYC_3", new LocaleValue("3月"));
header.AddChild("FSRYC_4", new LocaleValue("4月"));
header.AddChild("FSRYC_5", new LocaleValue("5月"));
header.AddChild("FSRYC_6", new LocaleValue("6月"));
header.AddChild("FSRYC_7", new LocaleValue("7月"));
header.AddChild("FSRYC_8", new LocaleValue("8月"));
header.AddChild("FSRYC_9", new LocaleValue("9月"));
header.AddChild("FSRYC_10", new LocaleValue("10月"));
header.AddChild("FSRYC_11", new LocaleValue("11月"));
header.AddChild("FSRYC_12", new LocaleValue("12月"));
header.AddChild("FSRYC_SUM", new LocaleValue(y + "年合计"));
header.AddChild("FSRYC_MN", new LocaleValue(y2 + "年收入"));
header.AddChild("FSRYC_HN", new LocaleValue(y3 + "年收入"));
return header;
}
///
/// 设置报表合计列
///
///
///
public override List
{
var result = base.GetSummaryColumnInfo(filter);
result.Add(AddSum("FXLYC_SUM"));
result.Add(AddSum("FXLYC_MN"));
result.Add(AddSum("FXLYC_HN"));
for (int i = 1; i <= 12; i++)
{
result.Add(AddSum("FXLYC_" + i));
result.Add(AddSum("FSRYC_" + i));
}
result.Add(AddSum("FSRYC_SUM"));
result.Add(AddSum("FSRYC_MN"));
result.Add(AddSum("FSRYC_HN"));
return result;
}
SummaryField AddSum(string field_name)
{
return new SummaryField(field_name, Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM);
}
}
}
推荐阅读