文章目录
- 业务背景
- 业务需求
- 方案设计
- 详细设计
- 测试
业务背景
系统现有功能不支持查询过去一年内所有物料的入库数,需要人工导出,然后再汇总。
业务需求
可以查询所有物料的入库数,多个物料,单个物料,多个组织,单个组织的入库数,以及支持查询入库数大于某个阈值。
方案设计
物料资料:未禁用
采购入库单:已审核,审核日期在过去一年内的入库数按物料编码汇总
详细设计
组织:F_XXXX_OrgNumberTitle
物料:F_XXXX_MaterialNumberTitle
创建报表服务器插件MaterialCompareRpt,继承SysReportBaseService
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XXXX.K3.Core.Const;
using XXXX.K3.Core.Util;
namespace XXXX.K3.App.Report.BD
{
/// <summary>
/// 功能描述 :物料通用性比较报表插件-服务端
/// 创 建 者 :Administrator
/// 创建日期 :2024/8/21 17:09:34
/// 最后修改者 :Krystal
/// 最后修改日期:2024/8/21 17:09:34
/// </summary>
[Description("物料通用性比较报表插件-服务端"), HotUpdate]
public class MaterialCompareRpt: SysReportBaseService
{
#region <变量>
/// <summary>
/// 汇总字段
/// </summary>
List<string> listSumColumn = new List<string>() { "FQTY" };
/// <summary>
/// 用于sql的取数
/// </summary>
protected List<string> lstSql = new List<string>();
/// <summary>
/// 高级过滤条件
/// </summary>
private string _sWhereFilter = string.Empty;
/// <summary>
/// 排序条件
/// </summary>
private string _sOrderBy = string.Empty;
/// <summary>
/// 分组条件
/// </summary>
private string _sGroupFilter = string.Empty;
/// <summary>
/// 页面过滤参数
/// </summary>
FilterArgs _filterArgs = new FilterArgs();
/// <summary>
/// 快捷过滤参数
/// </summary>
internal class FilterArgs
{
/// <summary>
/// 物料编码
/// </summary>
private string _FMaterial = string.Empty;
/// <summary>
/// 组织
/// </summary>
private string _FOrg = string.Empty;
/// <summary>
/// 组织名称
/// </summary>
private string _FOrgName = string.Empty;
public string FMaterial
{
get { return this._FMaterial; }
set { this._FMaterial = value; }
}
public string FOrg
{
get { return this._FOrg; }
set { this._FOrg = value; }
}
public string FOrgName
{
get { return this._FOrgName; }
set { this._FOrgName = value; }
}
}
#endregion <变量>
#region <方法>
/// <summary>
/// 获取过滤条件
/// </summary>
/// <param name="filter"></param>
private DynamicObject GetFilter(IRptParams filter)
{
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
_filterArgs.FMaterial = XXXXCommonUtil.GetBaseDataByKey(dyFilter, "F_XXXX_MaterialId", "Number");
_filterArgs.FOrg = XXXXCommonUtil.GetBaseDataByKey(dyFilter, "F_XXXX_OrgId", "Number");
_filterArgs.FOrgName = XXXXCommonUtil.GetBaseDataByKey(dyFilter, "F_XXXX_OrgId", "Name");
_sWhereFilter = filter.FilterParameter.FilterString;
_sGroupFilter = filter.FilterParameter.GroupbyString;
_sOrderBy = filter.FilterParameter.SortString;
return dyFilter;
}
/// <summary>
/// 构造报表表头标题
/// </summary>
/// <param name="filter">过滤条件对象</param>
/// <returns></returns>
private ReportTitles BuildTitle(IRptParams filter)
{
ReportTitles reportTitle = new ReportTitles();
//物料
string materNumber = string.IsNullOrWhiteSpace(this._filterArgs.FMaterial) ? "全部" : this._filterArgs.FMaterial;
reportTitle.AddTitle("F_XXXX_MaterialNumberTitle", materNumber);
//组织
string orgName = string.IsNullOrWhiteSpace(this._filterArgs.FOrgName) ? "全部" : this._filterArgs.FOrgName;
reportTitle.AddTitle("F_XXXX_OrgNumberTitle", orgName);
return reportTitle;
}
/// <summary>
/// 拼接过滤条件
/// </summary>
/// <param name="sbSql"></param>
private void ConstructFastFilter(ref string sbSql)
{
sbSql += " WHERE 1=1 " + ((this._sWhereFilter.Length > 0) ? " and " + this._sWhereFilter : "");
if (!string.IsNullOrWhiteSpace(this._filterArgs.FMaterial))
sbSql += string.Format(@" AND FMATERIALNUMBER='{0}'", this._filterArgs.FMaterial); //物料
if (!string.IsNullOrWhiteSpace(this._filterArgs.FOrg))
sbSql += string.Format(@" AND FORGNUMBER='{0}'", this._filterArgs.FOrg); //组织
}
/// <summary>
/// 设计报表列头
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
header.AddChild("FORGNAME", new LocaleValue("使用组织", this.Context.DefaultLocale.LCID));
header.AddChild("FMATERIALNUMBER", new LocaleValue("物料编码", this.Context.DefaultLocale.LCID));
header.AddChild("FMATERIALNAME", new LocaleValue("物料名称", this.Context.DefaultLocale.LCID));
header.AddChild("FSPECIFICATION", new LocaleValue("规格型号", this.Context.DefaultLocale.LCID));
header.AddChild("FQTY", new LocaleValue("年采购入库总数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
return header;
}
//查询条件标题
public override ReportTitles GetReportTitles(IRptParams filter)
{
return BuildTitle(filter);
}
/// <summary>
/// 创建报表临时表
/// </summary>
/// <param name="filter"></param>
/// <param name="tableName"></param>
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
//构造过滤条件
DynamicObject dyFilter = this.GetFilter(filter);
//GetReport();
if (filter.FilterParameter.SummaryRows.Count > 0)
{
List<string> outColumn = filter.FilterParameter.ColumnInfo.Select(s => s.FieldName).ToList();
List<string> summaryColumn = filter.FilterParameter.SummaryRows.Select(s => s.FieldName).ToList();
outColumn.RemoveAll(s => this.listSumColumn.Contains(s));
outColumn.RemoveAll(s => summaryColumn.Contains(s));
filter.FilterParameter.ColumnInfo.RemoveAll(s => outColumn.Contains(s.FieldName));
}
string sbSql = string.Empty;
this.KSQL_SEQ = string.Format(this.KSQL_SEQ, this._sGroupFilter.Length > 0 ? this._sGroupFilter : this._sOrderBy.Length > 0 ? this._sOrderBy : "FID");
//string filterColumn = string.Empty;
string filterColumn = string.Join(",", filter.FilterParameter.ColumnInfo.Select(s => (this.listSumColumn.Contains(s.FieldName) && this._sGroupFilter.Length > 0) ? "sum(" + s.FieldName + ") as " + s.FieldName : s.FieldName).ToList());
if (string.IsNullOrEmpty(filterColumn) || string.IsNullOrWhiteSpace(filterColumn))
{
filterColumn += "FID";
}
sbSql = string.Format(@"select {1},{0} from XXXX_V_BD_MaterialCompareRpt T99", filterColumn, this.KSQL_SEQ);
this.ConstructFastFilter(ref sbSql);
if (this._sGroupFilter.Length > 0) sbSql += " group by " + this._sGroupFilter;
sbSql = string.Format(@"{0}select * into {1} from ({2}) report", OtherConst.DIALECT, tableName, sbSql);
DBUtils.Execute(this.Context, sbSql);
}
/// <summary>
/// 构造汇总字段信息集合
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
{
List<SummaryField> list = new List<SummaryField>();
filter.FilterParameter.ColumnInfo.ForEach(s =>
{
if (listSumColumn.Contains(s.FieldName))
list.Add(new SummaryField(s.FieldName, Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
});
return list;
}
#endregion <方法>
}
}
视图脚本:
CREATE VIEW XXXX_V_BD_MaterialCompareRpt
AS
SELECT
ROW_NUMBER() OVER( ORDER BY a.FMATERIALID, a.FUSEORGID ) FID
,a.FMATERIALID
, a.FUSEORGID
,a.FNUMBER FMATERIALNUMBER
,b.FNAME FMATERIALNAME
,b.FSPECIFICATION
,ISNULL(c.FREALQTY,0) FQTY
,d.FNUMBER FORGNUMBER
,e.FNAME FORGNAME
FROM T_BD_MATERIAL a
LEFT JOIN dbo.T_BD_MATERIAL_L b ON a.FMATERIALID=b.FMATERIALID AND b.FLOCALEID=2052
LEFT JOIN
(
SELECT
t2.FNUMBER
,SUM(t1.FREALQTY) FREALQTY
FROM T_STK_INSTOCK t
LEFT JOIN T_STK_INSTOCKENTRY t1 ON t.FID=t1.FID
LEFT JOIN T_BD_MATERIAL t2 ON t2.FMATERIALID=t1.FMATERIALID
WHERE t.FDOCUMENTSTATUS='C'
AND t.FAPPROVEDATE>=CONVERT(VARCHAR(10),DATEADD(YEAR,-1,GETDATE()),120)
GROUP BY t2.FNUMBER
) c ON c.FNUMBER=a.FNUMBER
LEFT JOIN t_ORG_Organizations d ON d.FORGID=a.FUSEORGID
LEFT JOIN dbo.T_ORG_ORGANIZATIONS_L e ON e.FORGID=d.FORGID AND e.FLOCALEID=2052
WHERE a.FFORBIDSTATUS='A'
GO
挂载插件
创建过滤框
创建权限对象
绑定权限对象
发布菜单
测试
重启开发环境,授权给角色,用拥有该角色的用户登录
按物料编码查询,可以查询分配到多个组织的数据
按组织查询所有物料
增加条件