金蝶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