一、每日库存结存到表单《历史库存信息》
生成历史库存
每日库存存储
二、报表逻辑
组织=供应链中心，仓库=成品仓，区间可选，可单独搜索某物料
（1）库存数取自《历史库存信息》
（2） 期初库存为起始日期的查询库存，期末库存为结束日期的查询库存
（3）平均库存数量=（期初库存数量+期末库存数量）/2
（4）生产入库数量为选定日期范围内生产入库单的数量（按审核日期）
（5）销售出库数量为选定日期范围内销售出库单数量（按审核日期）
（6）产品库存周转率=360/（结束日期-起始日期）*（销售出库数量/平均库存数量）
（7）产品库存周转天数=360/库存周转率
三、报表设计
过滤框
简单账表界面
存储过程，根据传入条件获取并计算返回数据
CREATE PROCEDURE XXXX_PR_STK_ITORPT(
@STOCKID INT,
@BEGINTIME VARCHAR(20),
@ENDTIME VARCHAR(20),
@t VARCHAR(100)
)
AS
BEGIN
--传入开始时间6.2 ，结束时间7.23
--DECLARE @BEGINTIME VARCHAR(20)
--SET @BEGINTIME='2024-06-02 00:00:00'
--DECLARE @ENDTIME VARCHAR(20)
--SET @ENDTIME='2024-07-23 00:00:00'
--DECLARE @STOCKID INT
--SET @STOCKID=493513
--SELECT TOP 1000 * FROM XXXX_T_STK_HisInventory
DECLARE @ENDTIME1 VARCHAR(10);
SET @ENDTIME1=(
SELECT
CONVERT(VARCHAR(10),DATEADD(D,1,TRY_CAST(@ENDTIME as datetime)),23)
)
--SELECT CONVERT(varchar(10), GETDATE(), 23)
--期初结存库存数
SELECT t0.F_XXXX_MATERIALID FMATERIALID
,t1.FNUMBER FMATERIALNUMBER
, t0.F_XXXX_StockQty FQTY
INTO #tab1
FROM XXXX_T_STK_HisInventory t0 JOIN T_BD_MATERIAL t1 ON t1.FMATERIALID=t0.F_XXXX_MATERIALID
WHERE CONVERT(varchar(10), t0.F_XXXX_DATE, 23)=@BEGINTIME
AND t0.F_XXXX_StockId=@STOCKID
AND t0.F_XXXX_StockOrgId=100006
--期末结存库存数
SELECT t0.F_XXXX_MATERIALID FMATERIALID
,t1.FNUMBER FMATERIALNUMBER
, t0.F_XXXX_StockQty FQTY
INTO #tab2
FROM XXXX_T_STK_HisInventory t0 LEFT JOIN T_BD_MATERIAL t1 ON t1.FMATERIALID=t0.F_XXXX_MATERIALID
WHERE CONVERT(varchar(10), t0.F_XXXX_DATE, 23)=@ENDTIME
AND t0.F_XXXX_StockId=@STOCKID
AND t0.F_XXXX_StockOrgId=100006
--期间销售出库数
SELECT
t2.FMATERIALID fmaterialid
--,t2.FSTOCKID
, SUM(t2.FBASEUNITQTY) fqty
INTO #tab3
FROM T_SAL_OUTSTOCK t0
LEFT OUTER JOIN T_SAL_OUTSTOCKFIN t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_SAL_OUTSTOCKENTRY t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st238 ON t2.FMATERIALID = st238.FMATERIALID
LEFT OUTER JOIN t_BD_MaterialBase st239 ON t2.FMATERIALID = st239.FMATERIALID
WHERE (((((((t0.FAPPROVEDATE >= @BEGINTIME) AND (t0.FAPPROVEDATE < @ENDTIME1))
AND t0.FSTOCKORGID = 100006) AND ((t1.FISGENFORIOS = '0' AND (st238.FERPCLSID <> '6'))
AND (st239.FSUITE <> '1'))) AND t0.FOBJECTTYPEID = 'SAL_OUTSTOCK') AND t0.FCANCELSTATUS = 'A')
AND t2.FSTOCKID=@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
) GROUP BY t2.FMATERIALID
--期间产成品入库
SELECT t1.FMATERIALID fmaterialid
--, t1.FSTOCKID fstockid
, SUM(t1.FBASEREALQTY) fqty
INTO #tab4
FROM T_PRD_INSTOCK t0
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID
LEFT JOIN T_PRD_MO t2 ON t2.fid=t1.FMoId
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L t3 ON t3.FENTRYID=t2.F_XXXX_WKTYPE
LEFT JOIN dbo.T_BAS_BILLTYPE_L t4 ON t4.FBILLTYPEID=t2.FBILLTYPE
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId
WHERE (((((((t0.FAPPROVEDATE >= @BEGINTIME) AND (t0.FAPPROVEDATE < @ENDTIME1))
AND t0.FSTOCKORGID = 100006) AND t0.FENTRUSTINSTOCKID = 0)
AND t0.FFORMID = 'PRD_INSTOCK') AND t0.FCANCELSTATUS = 'A')
AND t1.FSTOCKID =@STOCKID
AND t4.FNAME='汇报入库-普通生产'
AND t3.FDATAVALUE IN ('普通生产订单','研发试制订单','销售改机订单','PCBA加工订单')
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
) GROUP BY t1.FMATERIALID
--sqlserver 声明小数
DECLARE @daterate NUMERIC(10,2)
SET @daterate=(SELECT ROUND(isnull(360.0/DATEDIFF(DAY,@BEGINTIME,@ENDTIME),0),2))
SELECT ROW_NUMBER() OVER ( ORDER BY m2.fmaterialid ) FID
,493513 FSTOCKID
,'02' AS FSTOCKNUMBER
,'成品仓' AS FSTOCKNAME
, m2.*
,CASE WHEN m2.fito>0 THEN CAST(ROUND(360/m2.fito,0) AS INT) ELSE 0 END FITD
INTO #t_ylm
FROM (
SELECT m11.*
,CASE WHEN m11.FAVGQTY>0 THEN CAST(ROUND(@daterate*(m11.FOUTQTY/m11.FAVGQTY),2) AS DECIMAL(10,2)) ELSE 0 END FITO
FROM (
SELECT m1.*
,CAST(ROUND((m1.FQCQTY+m1.FQMQTY)/2.0,2) AS DECIMAL(10,2)) FAVGQTY
--,ROUND(@daterate*(m1.foutqty/ROUND((m1.fqcqty+m1.fqmqty)/2.0,2)),2) fito
FROM (
SELECT m.*
,CAST(ISNULL(a.fqty,0) AS INT) FQCQTY
,CAST(ISNULL(b.fqty,0) AS INT) FQMQTY
,CAST(ISNULL(c.fqty,0) AS INT) FOUTQTY
,CAST(ISNULL(d.fqty,0) AS INT) FINQTY
FROM (
SELECT a1.FMATERIALID
,a1.FNUMBER FMATERIALNUMBER
,f1.FNAME FMATERIALNAME
,f1.FSPECIFICATION
,a1.FUSEORGID
,d1.FBASEUNITID
,e1.FNAME FBASEUNITNAME
FROM T_BD_MATERIAL a1
JOIN t_BD_MaterialStock b1 ON a1.FMATERIALID=b1.FMATERIALID
JOIN T_BD_STOCK_L c1 ON c1.FSTOCKID=b1.FSTOCKID
JOIN t_BD_MaterialBase d1 ON d1.FMATERIALID=a1.FMATERIALID
LEFT JOIN T_BD_UNIT_L e1 ON e1.FUNITID=d1.FBASEUNITID
LEFT JOIN T_BD_MATERIAL_L f1 ON f1.FMATERIALID=a1.FMATERIALID
WHERE c1.FNAME='成品仓' AND a1.FUSEORGID=100006
) m
LEFT JOIN #tab1 a ON m.FMATERIALNUMBER=a.FMATERIALNUMBER
LEFT JOIN #tab2 b ON m.FMATERIALNUMBER=b.FMATERIALNUMBER
LEFT JOIN #tab3 c ON m.fmaterialid=c.fmaterialid
LEFT JOIN #tab4 d ON m.fmaterialid=d.fmaterialid
) m1
) m11
) m2
--最后输出到临时表
DECLARE @cmdtext VARCHAR(MAX);
BEGIN
SET
@cmdtext = 'SELECT * ' + ' into ' + @t + ' FROM #t_ylm';
END;
EXEC(@cmdtext);
END
GO
XXXX_PR_STK_ITORPT
报表服务插件
using Kingdee.BOS;
using Kingdee.BOS.App;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using Krystal.K3Cloud.Core.Const;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
namespace Krystal.K3.App.Report.SAL
{
/// <summary>
/// 功能描述 ：成品库存周转率查询-报表插件
/// 创 建 者 ：Administrator
/// 创建日期 ：2024/7/31 15:52:57
/// 最后修改者 ：Krystal
/// 最后修改日期：2024/7/31 15:52:57
/// </summary>
[Description("成品库存周转率查询-报表插件"), HotUpdate]
public class STK_ITORpt: SysReportBaseService
{
#region <变量>
/// <summary>
/// 汇总字段
/// </summary>
List<string> listSumColumn = new List<string>() { "FQCQTY", "FQMQTY", "FOUTQTY", "FINQTY", "FAVGQTY" };
/// <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>
protected string temp_detail;
#endregion <变量>
#region <属性>
#region 过滤参数 FilterArgs
internal class FilterArgs
{
private DateTime _beginTime;
private DateTime _endTime;
//物料
private string _FMaterial = string.Empty;
public DateTime BeginTime
{
get { return this._beginTime; }
set { this._beginTime = value; }
}
public DateTime EndTime
{
get { return this._endTime; }
set { this._endTime = value; }
}
public string FMaterial
{
get { return this._FMaterial; }
set { this._FMaterial = value; }
}
}
#endregion
#endregion <属性>
#region <构造方法和析构方法>
#endregion <构造方法和析构方法>
#region <方法>
//界面加载表头
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
header.AddChild("FSTOCKNUMBER", new LocaleValue("仓库编码", this.Context.DefaultLocale.LCID));
header.AddChild("FSTOCKNAME", 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("FBASEUNITNAME", new LocaleValue("基本单位", this.Context.DefaultLocale.LCID));
header.AddChild("FQCQTY", new LocaleValue("期初结存数量", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
header.AddChild("FQMQTY", new LocaleValue("期末结存数量", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
header.AddChild("FINQTY", new LocaleValue("生产入库数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
header.AddChild("FAVGQTY", new LocaleValue("平均库存数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlDecimal);
header.AddChild("FOUTQTY", new LocaleValue("销售出库数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
header.AddChild("FITO", new LocaleValue("库存周转率", this.Context.DefaultLocale.LCID), SqlStorageType.SqlDecimal);
header.AddChild("FITD", new LocaleValue("库存周转天数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
//header.AddChild("F_XXXX_OrgName", new LocaleValue("供应组织", this.Context.DefaultLocale.LCID));
return header;
}
/// <summary>
/// 获取过滤条件
/// </summary>
/// <param name="filter"></param>
private DynamicObject GetFilter(IRptParams filter)
{
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
_filterArgs.FMaterial = GetBaseDataByKey(dyFilter, "F_XXXX_MaterialId", "Number");
_filterArgs.BeginTime = GetDataByKey(dyFilter, "F_XXXX_BeginDate") == string.Empty ? DateTime.MinValue : Convert.ToDateTime(XXXXCommonUtil.GetDataByKey(dyFilter, "F_XXXX_BeginDate"));
_filterArgs.EndTime = GetDataByKey(dyFilter, "F_XXXX_EndDate") == string.Empty ? DateTime.MaxValue : Convert.ToDateTime(XXXXCommonUtil.GetDataByKey(dyFilter, "F_XXXX_EndDate"));
_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 sDateFrom = _filterArgs.BeginTime == null ? string.Empty : _filterArgs.BeginTime.ToShortDateString().ToString();
string sDateTo = _filterArgs.EndTime == null ? string.Empty : _filterArgs.EndTime.ToShortDateString().ToString();
string ds = string.Format("{0}{1}{2}", sDateFrom, Kingdee.BOS.Resource.ResManager.LoadKDString(" 至 ", "004102030003172", Kingdee.BOS.Resource.SubSystemType.SCM), sDateTo);
//reportTitle.AddTitle("F_XXXX_DateRangeTitle", ds);
string beginApplicationMonthStr = _filterArgs.BeginTime.ToString("yyyy/MM/dd");//2024/06/01 至 2024/06/30
string endApplicationMonthStr = _filterArgs.EndTime.ToString("yyyy/MM/dd");
reportTitle.AddTitle("F_XXXX_DateRangeTitle", string.Format("{0}{1}{2}"
, beginApplicationMonthStr
, Kingdee.BOS.Resource.ResManager.LoadKDString(" 至 ", "004102030003172", Kingdee.BOS.Resource.SubSystemType.SCM)
, endApplicationMonthStr));
return reportTitle;
}
public override ReportTitles GetReportTitles(IRptParams filter)
{
return BuildTitle(filter);
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
//base.BuilderReportSqlAndTempTable(filter, 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 {2} T99", filterColumn, this.KSQL_SEQ, this.temp_detail);
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);
string[] str = { this.temp_detail };
DeleteTemporaryTableNames(this.Context, str);
}
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); //物料
}
private void GetReport()
{
this.temp_detail = CreateTemporaryTableNames(base.Context, 1)[0];
List<SqlParam> para = new List<SqlParam>()
{
new SqlParam("@STOCKID", KDDbType.Int32,493513),
new SqlParam("@BEGINTIME", KDDbType.String,_filterArgs.BeginTime.ToString("yyyy-MM-dd")),
new SqlParam("@ENDTIME",KDDbType.String,_filterArgs.EndTime.ToString("yyyy-MM-dd")),
new SqlParam("@t",KDDbType.String,temp_detail)
};
DBUtils.ExecuteDataSet(this.Context, System.Data.CommandType.StoredProcedure, string.Format(@"{0}XXXX_PR_STK_ITORPT", OtherConst.DIALECT), para);//XXXX_PR_STK_ITORPT--获取历史库存信息 XXXX_PR_STK_ITO--查询日期库存
}
private static string[] CreateTemporaryTableNames(Context context, int count)
{
return ServiceHelper.GetService<IDBService>().CreateTemporaryTableName(context, count);
}
/// <summary>
/// 标识需要删除的临时表
/// </summary>
/// <param name="context"></param>
/// <param name="temptableNames"></param>
/// <returns></returns>
private static bool DeleteTemporaryTableNames(Context context, string[] temptableNames)
{
return ServiceHelper.GetService<IDBService>().DeleteTemporaryTableName(context, temptableNames);
}
/// <summary>
/// 获取DynamicObject数据包中指定key的值
/// </summary>
private static string GetDataByKey(DynamicObject doFilter, string sKey)
{
string sReturnValue = string.Empty;
if (doFilter != null && doFilter[sKey] != null && !string.IsNullOrWhiteSpace(Convert.ToString(doFilter[sKey])))
{
sReturnValue = Convert.ToString(doFilter[sKey]);
}
return sReturnValue;
}
/// <summary>
/// 获取DynamicObject数据包中指定key的基础资料的指定属性的值
/// </summary>
private static string GetBaseDataByKey(DynamicObject doFilter, string sKey, string sItem)
{
string sReturnValue = string.Empty;
if (doFilter != null && doFilter[sKey] != null && !string.IsNullOrWhiteSpace(Convert.ToString(((DynamicObject)doFilter[sKey])[sItem])))
{
DynamicObject doTemp = doFilter[sKey] as DynamicObject;
sReturnValue = Convert.ToString(doTemp[sItem]);
}
return sReturnValue;
}
#endregion <方法>
}
}
报表服务插件完整代码
简单账表绑定数据源插件
绑定权限，发布，授权即可访问
标签：XXXX,报表,--,t0,成品,Context,周转率,new,string From： https://www.cnblogs.com/lanrenka/p/18334534