金蝶BOS 报表插件

金蝶BOS 报表插件

提示:以下是本篇文章正文内容,下面案例可供参考

一、报表插件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using System.ComponentModel;
using Kingdee.BOS.ServiceHelper;

namespace Kingdee.GBKJ.HuiHang.MaterialSummaryReport
{
    [Description("物料库存汇总报表")]
    public class ReportData : SysReportBaseService
    {
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            this.GetOrgIdList(filter);
            /*过滤条件*/
            DynamicObject obj2 = filter.FilterParameter.CustomFilter;
            //物料,仓库,日期
            string wlIdList = "", ckIdList = "", rqStr = "";
            //参数 物料F_GBKJ_MulBase
            if (obj2["F_GBKJ_MulBase"] != null && obj2["F_GBKJ_MulBase"].ToString() != "")
            {
                DynamicObjectCollection wl_list = obj2["F_GBKJ_MulBase"] as DynamicObjectCollection;
                foreach (DynamicObject item in wl_list)
                {
                    DynamicObject data = item["F_GBKJ_MulBase"] as DynamicObject;
                    wlIdList += data["Id"].ToString() + ",";
                }
                if (wlIdList.Count()>1)
                {
                    wlIdList = wlIdList.Substring(0, wlIdList.Count() - 1);
                }
                
            }
            //参数 仓库F_GBKJ_MulBase1
           /*if (obj2["F_GBKJ_MulBase1"] != null && obj2["F_GBKJ_MulBase1"].ToString() != "")
            {
                DynamicObjectCollection ck_list = obj2["F_GBKJ_MulBase1"] as DynamicObjectCollection;
                foreach (DynamicObject item in ck_list)
                {
                    DynamicObject data = item["F_GBKJ_MulBase1"] as DynamicObject;
                    ckIdList += data["Id"].ToString() + ",";
                }
            }*/
            //参数 日期F_GBKJ_Date
            if (obj2["F_GBKJ_Date"] != null && obj2["F_GBKJ_Date"].ToString() != "")
            {
                rqStr = obj2["F_GBKJ_Date"].ToString();
                rqStr = rqStr.Substring(0, 10);
            }

            //查询系数
            String sql = @"/*dialect*/select  TOP 1 f_gbkj_integer  天数 ,f_gbkj_integer1 系数 from  GBKJ_DYH_t_Cust100002  order by  fid";
            sql = string.Format(sql);
            DynamicObjectCollection listData = DBServiceHelper.ExecuteDynamicObject(this.Context, sql);
            if (listData ==null || listData.Count() == 0)
            {
                throw new Exception("基础系数未设置!");
                
                
            }
            StringBuilder builder3 = new StringBuilder();
            //builder3.Append("/*dialect*/exec T_GBKJ_HH_MATSUMREPOT_SP '" + wlIdList + "','" + ckIdList + "','" + rqStr + "'");
            builder3.Append("/*dialect*/exec T_GBKJ_HH_MATSUMREPOT_SP '" + wlIdList + "','" + rqStr + "'");
            DBUtils.Execute(base.Context, builder3.ToString());
            StringBuilder builder4 = new StringBuilder();
            builder4.Append("select {0},Tname.* into {1} from (SELECT * FROM T_GBKJ_HH_MATSUMREPOT) Tname");
            DBUtils.Execute(base.Context, string.Format(builder4.ToString(), string.Format(base.KSQL_SEQ, "FID"), tableName));

        }
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            //29个字段
            ReportHeader header = new ReportHeader();
            var com1 = header.AddChild("F_com1", new LocaleValue("物料编码"));
            var com2 = header.AddChild("F_com2", new LocaleValue("名称"));
            var com3 = header.AddChild("F_com3", new LocaleValue("规格型号"));
            var com4 = header.AddChild("F_com4", new LocaleValue("车型"));
            var com5 = header.AddChild("F_com5", new LocaleValue("半成品保管部门"));
            var com6 = header.AddChild("F_com6", new LocaleValue("成品库归属"));
            var com7 = header.AddChild("F_com7", new LocaleValue("顾客简称"));
            var com8 = header.AddChild("F_com8", new LocaleValue("中转库"));
            var com9 = header.AddChild("F_com9", new LocaleValue("开票客户"));
            var com10 = header.AddChild("F_com10", new LocaleValue("销售计划"));
            var com11 = header.AddChild("F_com11", new LocaleValue("生产计划"));
            var com12 = header.AddChild("F_com12", new LocaleValue("成品出库"));
            var com13 = header.AddChild("F_com13", new LocaleValue("退货数量"));
            var com14 = header.AddChild("F_com14", new LocaleValue("交付率"));
            var com15 = header.AddChild("F_com15", new LocaleValue("完成情况"));
            var com16 = header.AddChild("F_com16", new LocaleValue("半成品"));
            var com17 = header.AddChild("F_com17", new LocaleValue("成品"));
            var com18 = header.AddChild("F_com18", new LocaleValue("中转库"));
            var com19 = header.AddChild("F_com19", new LocaleValue("即时库存"));
            var com20 = header.AddChild("F_com20", new LocaleValue("日发货数量"));
            var com21 = header.AddChild("F_com21", new LocaleValue("安全库存"));
            var com22 = header.AddChild("F_com22", new LocaleValue("半成品库存天数"));
            var com23 = header.AddChild("F_com23", new LocaleValue("成品库存天数"));
            var com24 = header.AddChild("F_com24", new LocaleValue("中转库存天数"));
            var com25 = header.AddChild("F_com25", new LocaleValue("合计总天数"));
           // var com26 = header.AddChild("F_com26", new LocaleValue("管理分类"));
            var com27 = header.AddChild("F_com27", new LocaleValue("归属车间"));
            var com28 = header.AddChild("F_com28", new LocaleValue("成品+中转"));
            return header;
        }
        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            ReportTitles titles = new ReportTitles();
            DynamicObject obj2 = filter.FilterParameter.CustomFilter;
            //参数 物料F_GBKJ_MulBase
            if (obj2["F_GBKJ_MulBase"] != null && obj2["F_GBKJ_MulBase"].ToString() != "")
            {
                DynamicObjectCollection wl_list = obj2["F_GBKJ_MulBase"] as DynamicObjectCollection;
                String wlNameList = "";
                 foreach (DynamicObject item in wl_list){
                     DynamicObject data = item["F_GBKJ_MulBase"] as DynamicObject;
                     wlNameList += data["Name"].ToString()+";";
                 }
                titles.AddTitle("F_GBKJ_MulBase", wlNameList);

            }
            /*if (obj2["F_GBKJ_MulBase1"] != null && obj2["F_GBKJ_MulBase1"].ToString() != "")
            {
                DynamicObjectCollection ck_list = obj2["F_GBKJ_MulBase1"] as DynamicObjectCollection;
                String ckNameList = "";
                foreach (DynamicObject item in ck_list)
                {
                    DynamicObject data = item["F_GBKJ_MulBase1"] as DynamicObject;
                    ckNameList += data["Name"].ToString() + ";";

                }
                titles.AddTitle("F_GBKJ_MulBase1", ckNameList);
            }*/
            if (obj2["F_GBKJ_Date"] != null && obj2["F_GBKJ_Date"].ToString() != "")
            {
                String rqStr = obj2["F_GBKJ_Date"].ToString();
                rqStr = rqStr.Substring(0, 10);
                titles.AddTitle("F_GBKJ_Date", rqStr);
            }
            return titles;
        }
    }
}

二、新建表

--《物料库存汇总报表》
if(Exists(select * from sys.sysobjects where id=OBJECT_ID('T_GBKJ_HH_MATSUMREPOT'))) 
drop table T_GBKJ_HH_MATSUMREPOT
else
CREATE TABLE [dbo].[T_GBKJ_HH_MATSUMREPOT](
	[FID] [INT] IDENTITY(1,1) NOT NULL,
	[F_com1] [NVARCHAR](100) NULL,
	[F_com2] [NVARCHAR](100) NULL,
	[F_com3] [NVARCHAR](100) NULL,
	[F_com4] [NVARCHAR](100) NULL,
	[F_com5] [NVARCHAR](100) NULL,
	[F_com6] [NVARCHAR](100) NULL,
	[F_com7] [NVARCHAR](300) NULL,
	[F_com8] [NVARCHAR](100) NULL,
	[F_com9] [NVARCHAR](100) NULL,
	[F_com10] [INT] NULL,
	[F_com11] [INT] NULL,
	[F_com12] [INT] NULL,
	[F_com13] [INT] NULL,
	[F_com14] [NVARCHAR](100) NULL,
	[F_com15] [NVARCHAR](100) NULL,
	[F_com16] [INT] NULL,
	[F_com17] [INT] NULL,
	[F_com18] [INT] NULL,
	[F_com19] [INT] NULL,
	[F_com20] [INT] NULL,
	[F_com21] [INT] NULL,
	[F_com22] [INT] NULL,
	[F_com23] [INT] NULL,
	[F_com24] [INT] NULL,
	[F_com25] [INT] NULL,
	[F_com26] [NVARCHAR](100) NULL,
	[F_com27] [NVARCHAR](100) NULL,
	[F_com28] [INT] NULL,
	[F_com29] [INT] NULL,
 CONSTRAINT [PK_T_GBKJ_HH_MATSUMREPOT] PRIMARY KEY CLUSTERED 
(
	[FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

三、存储过程

/*

	《存储过程 - 物料库存汇总报表》

*/

if exists(select 1 from sys.procedures where name = 'T_GBKJ_HH_MATSUMREPOT_SP')
drop procedure T_GBKJ_HH_MATSUMREPOT_SP;
go
/*	
创建存储过程
exec T_GBKJ_HH_MATSUMREPOT_SP

赛选条件:物料、仓库、日期
*/
CREATE PROCEDURE [dbo].[T_GBKJ_HH_MATSUMREPOT_SP]
	 @F_WL_LIST  VARCHAR(MAX)='' -- 物料
	--,@F_CK_LIST VARCHAR(MAX)='' --仓库
	,@F_DATE VARCHAR(10)='' --日期

AS
BEGIN
begin tran;
    begin try
		--半成品 114019、成品 114017、中转库114016
		DECLARE @sqlInsert VARCHAR(MAX);
		DECLARE @sqlSelect VARCHAR(MAX);
		DECLARE @sqlwhere VARCHAR(MAX);
		DECLARE @bcpID VARCHAR(MAX);
		DECLARE @cpID VARCHAR(MAX);
		DECLARE @zzkGroupID VARCHAR(MAX);
		DECLARE @ts VARCHAR(10);
		DECLARE @xs VARCHAR(10);
		SET @sqlwhere = ' where 1=1 '
		SET @bcpID='114019';--半成品库id
		SET @cpID='114017';--成品库id
		SET @zzkGroupID='114016';--中转库分组id
		select top 1  @ts= f_gbkj_integer   from   GBKJ_DYH_t_Cust100002  order by  fid;
		select top 1  @xs= f_gbkj_integer1  from  GBKJ_DYH_t_Cust100002  order by  fid;
		print '天数'+@ts
		print '系数'+@xs
		print '开始组装数据'
		print '1.删除表中数据'
		delete from T_GBKJ_HH_MATSUMREPOT
		IF @F_WL_LIST!=''
		BEGIN
			SET @sqlwhere +=  ' and t.FMATERIALID in ('+@F_WL_LIST+') ';
		END

		print @sqlwhere
		print '2.查询数据'

		SET	@sqlInsert =' insert into T_GBKJ_HH_MATSUMREPOT(F_com1,F_com2,F_com3,F_com4,F_com5,F_com6,F_com7,F_com8,F_com9,F_com10,F_com11,F_com12,F_com13,F_com14,F_com15,F_com16,F_com17,F_com18,F_com19,F_com20,F_com21,F_com22,F_com23,F_com24,F_com25,F_com26,F_com27,F_com28) '
		print @sqlInsert
		SET	@sqlSelect=' select
					t.FNUMBER 编码,
					tl.FNAME 名称,
					tl.FSPECIFICATION 规格型号,
					TGL.FNAME 车型,
					'''' 保管部门,
					'''' 产品类型,
					isnull(XSDDKH.FNMAE,'''') 客户简称,
					isnull(jskczzk.FNMAE,'''') 中转库,
					'''' 开票客户,
					isnull(XSDDKH.FQTY,0)  销售计划,
					isnull(XSDDKH.FQTY,0)+isnull(aqkc.FPLANSAFEQTY,0)-isnull(jskc.FBASEQTY,0) 生产计划,
					isnull(qtck.FBASEQTY,0)+isnull(xsckd.FREALQTY,0) 成品出库,
					isnull(xsth.FREALQTY,0) 退货数量,
					convert(nvarchar(30),
					convert(decimal(16,1),
					case isnull(XSDDKH.FQTY,0)
					when 0 then 0
					else  (isnull(qtck.FBASEQTY,0)+isnull(xsckd.FREALQTY,0))/isnull(XSDDKH.FQTY,0)*100
					end ))+''%''  交付率,
					case 
					when isnull(XSDDKH.FQTY,0)=0 then ''未完成''
					when (isnull(qtck.FBASEQTY,0)+isnull(XSDDKH.FQTY,0))/isnull(XSDDKH.FQTY,0)*100 = 100 then ''完成''
					else ''未完成''
					end  完成情况,
					isnull(jskcbcp.FBASEQTY,0) 半成品,
					isnull(jskccp.FBASEQTY,0) 成品,
					isnull(jskczzk.FBASEQTY,0) 中转库,
					isnull(jskc.FBASEQTY,0) 即时库存,
					isnull(XSDDKH.FQTY,0)/'+@ts+' 日发货数量,
					isnull(XSDDKH.FQTY,0)/'+@ts+'*'+@xs+' 安全库存,
					case isnull(XSDDKH.FQTY,0)
					when 0 then 0
					else isnull(jskcbcp.FBASEQTY,0)/(isnull(XSDDKH.FQTY,0)/'+@ts+')
					end  半成品库存天数,
					case isnull(XSDDKH.FQTY,0)
					when 0 then 0
					else isnull(jskccp.FBASEQTY,0)/(isnull(XSDDKH.FQTY,0)/'+@ts+')
					end   成品库存天数,
					case isnull(XSDDKH.FQTY,0)
					when 0 then 0
					else isnull(jskczzk.FBASEQTY,0)/(isnull(XSDDKH.FQTY,0)/'+@ts+')
					end   中转库存天数,
					case isnull(XSDDKH.FQTY,0)
					when 0 then 0
					else isnull(jskcbcp.FBASEQTY,0)/(isnull(XSDDKH.FQTY,0)/'+@ts+')+isnull(jskccp.FBASEQTY,0)/(isnull(XSDDKH.FQTY,0)/'+@ts+')+isnull(jskczzk.FBASEQTY,0)/(isnull(XSDDKH.FQTY,0)/'+@ts+')
					end   合计总天数,
					'''' 管理分类,
					''''归属车间,
					isnull(jskccp.FBASEQTY,0) +isnull(jskczzk.FBASEQTY,0) ''成品+中转''
					from  T_BD_MATERIAL t 
					inner join T_BD_MATERIAL_L tl on t.FMATERIALID = tl.FMATERIALID  and  t.FDOCUMENTSTATUS = ''C'' and  tl.FLOCALEID=2052
					left join T_BD_MATERIALGROUP TG ON  TG.FID =t.FMATERIALGROUP
					left join T_BD_MATERIALGROUP_L TGL ON  TGL.FID =TG.FPARENTID and  TGL.FLOCALEID=2052
					LEFT JOIN (
								select XSDD.FMATERIALID,
								FNMAE = ( STUFF(( SELECT    '','' + A.FSHORTNAME
													FROM      (select XSM.FMATERIALID,xscl.FSHORTNAME FROM T_SAL_ORDER XS
													inner join T_SAL_ORDERENTRY  XSM on XSM.fid = XS.fid and XS.FDOCUMENTSTATUS = ''C''and  CONVERT(varchar(10),XS.FAPPROVEDATE,23)  = '''+@F_DATE+'''
													left join T_BD_CUSTOMER_L xscl on xscl.FCUSTID = XS.FCUSTID  and xscl.FLOCALEID=2052) A
														  WHERE     A.FMATERIALID = XSDD.FMATERIALID
														FOR
														  XML PATH('''')
														), 1, 1, '''') ),
								sum(XSDD.FQTY)  FQTY
								from   (select XSM.FMATERIALID,xscl.FSHORTNAME,XSM.FQTY FROM T_SAL_ORDER XS
								inner join T_SAL_ORDERENTRY  XSM on XSM.fid = XS.fid and XS.FDOCUMENTSTATUS = ''C'' and  CONVERT(varchar(10),XS.FAPPROVEDATE,23)  = '''+@F_DATE+'''
								left join T_BD_CUSTOMER_L xscl on xscl.FCUSTID = XS.FCUSTID  and xscl.FLOCALEID=2052) XSDD
								group by XSDD.FMATERIALID) XSDDKH ON XSDDKH.FMATERIALID = t.FMATERIALID
					left join  T_BD_MATERIALPLANENTRY aqkc on aqkc.FMATERIALID =   t.FMATERIALID 
					left join (select oy.FMATERIALID,sum(oy.FREALQTY) FREALQTY from  T_SAL_OUTSTOCK  o INNER JOIN   T_SAL_OUTSTOCKENTRY oy ON  o.fid = oy.fid  where o.FDOCUMENTSTATUS =''C''  and CONVERT(varchar(10),o.FAPPROVEDATE,23) ='''+@F_DATE+''' group by oy.FMATERIALID)xsckd on xsckd.FMATERIALID = t.FMATERIALID
					left join  ( select sum(aqt.FBASEQTY) FBASEQTY,aqt.FMATERIALID from T_STK_INVENTORY aqt ,T_BD_MATERIAL aq where aq.FMATERIALID = aqt.FMATERIALID and  aq.FDOCUMENTSTATUS=''C'' group by  aqt.FMATERIALID) jskc on  jskc.FMATERIALID =   t.FMATERIALID 
					left join (select sum(t1.FBASEQTY) FBASEQTY,t1.FMATERIALID from T_STK_MISDELIVERYENTRY t1 , T_STK_MISDELIVERY  t where t.FID = t1.FID and CONVERT(varchar(10),t.FAPPROVEDATE,23)  = '''+@F_DATE+'''  and   t.FDOCUMENTSTATUS=''C''  group by t1.FMATERIALID) qtck on  qtck.FMATERIALID = t.FMATERIALID 	 		
					left join (select  t1.FMATERIALID,sum(t1.FREALQTY) FREALQTY from   T_SAL_RETURNSTOCK t,T_SAL_RETURNSTOCKENTRY t1 where  t.FID = t1.FID and   t.FDOCUMENTSTATUS=''C''  and CONVERT(varchar(10),t.FAPPROVEDATE,23)  =  '''+@F_DATE+''' group  by t1.FMATERIALID ) xsth on xsth.FMATERIALID = t.FMATERIALID 	 		
					left join (select sum(FBASEQTY) FBASEQTY,FMATERIALID from  T_STK_INVENTORY tsi where tsi.FSTOCKID='+@bcpID+' group by FMATERIALID)jskcbcp  on jskcbcp.FMATERIALID = t.FMATERIALID 	
					left join (select sum(FBASEQTY) FBASEQTY,FMATERIALID from  T_STK_INVENTORY tsi where tsi.FSTOCKID='+@cpID+' group by FMATERIALID)jskccp  on jskccp.FMATERIALID = t.FMATERIALID 
					left join (select  zzk.FMATERIALID ,sum(zzk.FBASEQTY) FBASEQTY ,
									FNMAE = ( STUFF(( SELECT    '','' + A.fname
														FROM      (	select tsl.fname,tsi.FMATERIALID from   T_STK_INVENTORY   tsi  
																	inner join   t_BD_Stock ts on   tsi.FSTOCKID  = ts.FSTOCKID   and ts.FGROUP='+@zzkGroupID+' 
																	inner join   T_BD_STOCK_L tsl on   tsl.FSTOCKID  = tsi.FSTOCKID   and  tsl.FLOCALEID=2052  group by tsl.fname,tsi.FMATERIALID ) A
															  WHERE     A.FMATERIALID = zzk.FMATERIALID 
															FOR
															  XML PATH('''')
															), 1, 1, '''') )
						 from (
							select sum(tsi.FBASEQTY) FBASEQTY,tsl.fname,tsi.FMATERIALID from   T_STK_INVENTORY   tsi  
							inner join   t_BD_Stock ts on   tsi.FSTOCKID  = ts.FSTOCKID   and ts.FGROUP='+@zzkGroupID+' 
							inner join   T_BD_STOCK_L tsl on   tsl.FSTOCKID  = tsi.FSTOCKID   and  tsl.FLOCALEID=2052  
							group by tsl.fname,tsi.FMATERIALID
						)zzk group by zzk.FMATERIALID)jskczzk on jskczzk.FMATERIALID =t.FMATERIALID'
			print @sqlSelect
			EXEC(@sqlInsert+@sqlSelect+@sqlwhere);
			 
			select * from T_GBKJ_HH_MATSUMREPOT 

commit;
end try    
begin catch        
    rollback tran;
    print ('发生异常,事务进行回滚');
end catch  
END
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值