成品库存周转率报表（一）
一、每日库存结存到表单《历史库存信息》
生成历史库存
每日库存存储
二、报表逻辑
组织=供应链中心，仓库=成品仓，区间可选，可单独搜索某物料
（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
报表服务插件
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 <方法> } }
简单账表绑定数据源插件
绑定权限，发布，授权即可访问
