【场景】汇总数量翻倍问题，通常为单据头合并字段重复计算
【数据准备】
```csharp
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.GroupElement;
using Kingdee.BOS.Core.Report;
using System.Collections.Generic;
using System.Linq;
namespace DynamicFormPlugIn.Report
{
[Kingdee.BOS.Util.HotUpdate]
public class ReportServicePlugIn_AdjustSummary : SysReportBaseService
{
public override void Initialize()
{
this.ReportProperty.BillKeyFieldName = "fid";
this.ReportProperty.PrimaryKeyFieldName = "fid";
this.ReportProperty.ReportName = new Kingdee.BOS.LocaleValue("账表汇总重复过滤插件");
base.Initialize();
}
public override ReportHeader GetReportHeaders(IRptParams filter)
{
var ans = new ReportHeader();
var billhead = ans.AddChild("FBillno", new LocaleValue("单据编号"),0);
billhead.Mergeable = true;
var header = ans.AddChild("F_PAEZ_Qty1", new LocaleValue("单据头汇总数量"), SqlStorageType.SqlDecimal);
header.Mergeable = true;
header.ColIndex = 1;
ans.AddChild("Fnumber", new LocaleValue("物料编码"),2);
var header2 = ans.AddChild("FQTY", new LocaleValue("数量") , SqlStorageType.SqlDecimal);
header2.ColIndex = 3;
return ans;
}
public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
{
List<SummaryField> fields = new List<SummaryField>();
fields.Add(new SummaryField("F_PAEZ_Qty1", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
fields.Add(new SummaryField("FQTY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
return fields;
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
KSQL_SEQ = string.Format(KSQL_SEQ, "FBILLNO ASC");
string sql = string.Format(@"/*dialect*/ select top 20 bill.fid, bill.fbillno, bill.F_PAEZ_Qty1, entry.fseq, entry.FMaterialid, bd.fnumber, entry.fqty, {0} into {1}
from t_pur_poorder bill
left join t_pur_poorderentry entry on bill.fid = entry.fid
left join t_bd_material bd on entry.FMaterialid = bd.FMaterialid
where bill.fbillno <>'' and bill.F_PAEZ_Qty1 <>0 ", KSQL_SEQ, tableName);
DBUtils.Execute(this.Context, sql);
}
}
}
```
【问题原因】
BillKeyFieldName 仅是用作界面显示的时候，同一个单多行时显示空白。
而汇总是直接根据临时表汇总的，所以数量翻倍
【方案】
方案1，临时表的sql，针对分录内码非第一行时字段调整为0
方案2，插件调整汇总sql
【补充】完整代码
```csharp
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.GroupElement;
using Kingdee.BOS.Core.Report;
using System.Collections.Generic;
using System.Linq;
namespace DynamicFormPlugIn.Report
{
[Kingdee.BOS.Util.HotUpdate]
public class ReportServicePlugIn_AdjustSummary : SysReportBaseService
{
public override void Initialize()
{
this.ReportProperty.BillKeyFieldName = "fid";
this.ReportProperty.PrimaryKeyFieldName = "fid";
this.ReportProperty.ReportName = new Kingdee.BOS.LocaleValue("账表汇总重复过滤插件");
base.Initialize();
}
public override ReportHeader GetReportHeaders(IRptParams filter)
{
var ans = new ReportHeader();
var billhead = ans.AddChild("FBillno", new LocaleValue("单据编号"),0);
billhead.Mergeable = true;
var header = ans.AddChild("F_PAEZ_Qty1", new LocaleValue("单据头汇总数量"), SqlStorageType.SqlDecimal);
header.Mergeable = true;
header.ColIndex = 1;
ans.AddChild("Fnumber", new LocaleValue("物料编码"),2);
var header2 = ans.AddChild("FQTY", new LocaleValue("数量") , SqlStorageType.SqlDecimal);
header2.ColIndex = 3;
return ans;
}
public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
{
List<SummaryField> fields = new List<SummaryField>();
fields.Add(new SummaryField("F_PAEZ_Qty1", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
fields.Add(new SummaryField("FQTY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
return fields;
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
KSQL_SEQ = string.Format(KSQL_SEQ, "FBILLNO ASC");
string sql = string.Format(@"/*dialect*/ select top 20 bill.fid, bill.fbillno, bill.F_PAEZ_Qty1, entry.fseq, entry.FMaterialid, bd.fnumber, entry.fqty, {0} into {1}
from t_pur_poorder bill
left join t_pur_poorderentry entry on bill.fid = entry.fid
left join t_bd_material bd on entry.FMaterialid = bd.FMaterialid
where bill.fbillno <>'' and bill.F_PAEZ_Qty1 <>0 ", KSQL_SEQ, tableName);
DBUtils.Execute(this.Context, sql);
}
protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
{
var billHeadQty = summaryFields.FirstOrDefault(x => x.Key == "F_PAEZ_Qty1");
if(billHeadQty == null)
{
return base.GetSummaryColumsSQL(summaryFields);
}
summaryFields.Remove(billHeadQty);
var sql = base.GetSummaryColumsSQL(summaryFields);
summaryFields.Add(billHeadQty);
sql = sql + ", sum(CASE FSEQ WHEN 1 THEN F_PAEZ_Qty1 ELSE 0 END) as F_PAEZ_Qty1";
return sql;
}
}
}
```
