一、编写SqlServer语句，并执行
alter procedure masterForecast
@periodParam varchar(20)
as
declare @curperoid datetime--需要查询的时间;
set @curperoid = @periodParam
declare @period varchar(50);
set @period = @curperoid;
create table #tempA(
materialGroup varchar(50),
forcastPeriod varchar(50),
Proportion decimal(30,8),
period datetime,
target decimal(30,8)
);
declare @tempFZ varchar(50);
declare @tempNF varchar(50);
set @tempNF = year(@period);
declare @LEtarget decimal(30,8);
declare @BPtarget decimal(30,8);
declare @value decimal(30,8);
create table #tempC(value decimal(30,2),num int);
if (month(@period) <10)
begin
set @period = concat(year(@period),'-0',month(@period),'-',day(@period));
end
else
begin
set @period = concat(year(@period),'-',month(@period),'-',day(@period));
end
declare FZ cursor local for
(select a.F_PATC_PL6CODE from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid
where a.FDOCUMENTSTATUS = 'C' and datediff(month,a.F_SHKD_PERIOD,@period) = 0 group by a.F_PATC_PL6CODE,a.F_SHKD_PERIOD );
open FZ
fetch next from FZ into @tempFZ
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
begin
--set @period = ( select F_SHKD_PERIOD from SHKD_MYZBWCZBBill where F_SHKD_WLFZ = @tempFZ group by F_SHKD_WLFZ,F_SHKD_PERIOD);--获得期间
--查询出当前的东东
--select * from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid where a.F_SHKD_WLFZ = @tempFZ and b.F_SHKD_YEAR = @tempNF;
--a1.Advanced Staining Instruments
--declare @period varchar(50);
--set @period = '2019-12-31';
--(select f_shkd_Jan from (select a.F_SHKD_WLFZ,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid
--where a.F_SHKD_WLFZ = 'a1.Advanced Staining Instruments' and F_SHKD_NF = year(@period))a);
set @LEtarget = (select top 1 F_PATC_LEAFTER from SHKD_WLFZYCSFBill where F_PATC_PL6CODE = @tempFZ and F_SHKD_YEAR = @tempNF and FDOCUMENTSTATUS = 'C')
set @BPtarget = (select top 1 F_PATC_BPAFTER from SHKD_WLFZYCSFBill where F_PATC_PL6CODE = @tempFZ and F_SHKD_YEAR = @tempNF and FDOCUMENTSTATUS = 'C')
delete from #tempC;
insert into #tempC values((select f_shkd_Jan from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),1); --1月
insert into #tempC values((select F_SHKD_FEB from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),2); --2月
insert into #tempC values((select F_SHKD_MAR from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),3); --3月
insert into #tempC values((select F_SHKD_APR from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),4); --4月
insert into #tempC values((select f_shkd_may from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),5); --5月
insert into #tempC values((select f_shkd_jun from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),6); --6月
insert into #tempC values((select f_shkd_jul from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),7); --7月
insert into #tempC values((select F_SHKD_AUG from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),8); --8月
insert into #tempC values((select F_SHKD_SEP from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),9); --9月
insert into #tempC values((select F_SHKD_OCT from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),10); --10月
insert into #tempC values((select F_SHKD_NOV from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),11); --11月
insert into #tempC values((select F_SHKD_DEC from (select c.fname,b.* from SHKD_MYZBWCZBBill a left join SHKD_MYZBWCZBEntry b on a.fid = b.fid left join T_SHKD_PLCGROUP_L c on a.F_PATC_PL6CODE = c.fid
where a.F_PATC_PL6CODE = @tempFZ and datediff(month,a.F_SHKD_PERIOD,@curperoid) = 0 and FDOCUMENTSTATUS = 'C')a),12); --12月
declare @i int
set @i=1
while(@i<=12)
begin
declare @monthNum varchar(5);
if(@i<10) begin set @monthNum = concat('0',@i) ; end;
else begin set @monthNum = @i ; end;
set @value = (select value from #tempC where num = @i);
--datediff(month,a.F_SHKD_PERIOD,@period) = 0
--@period <= concat(@tempNF,'-',@monthNum,'-',@monthNum)
--select datediff(month,'2020-12-01','2020-09-11')
declare @materialGroup varchar(100);
set @materialGroup = (select fname from T_SHKD_PLCGROUP_L where fid = @tempFZ);
if(@value!=0 and datediff(month,@period,concat(@tempNF,'-',@monthNum,'-01')) >=0)
begin
insert into #tempA values(@materialGroup,concat(@tempNF,'-',@monthNum),@value,@period,@LEtarget);
end --1月
insert into #tempA values(@materialGroup,concat(@tempNF+1,'-',@monthNum),@value,@period,@BPtarget);
set @i=@i+1
end
fetch next from FZ into @tempFZ --转到下一个游标，没有会死循环
end
close FZ; --关闭游标
deallocate FZ ; --释放游标
--查询avg
-- 三月的
declare @date datetime;
set @date = @curperoid;
declare @endTime datetime;
declare @beginTime datetime;
set @endTime = (Select DATEADD(mm, DATEDIFF(mm,0,@date), 0))
set @beginTime = (select dateadd(mm,-3,@endTime))
--if (@monthNum = 3)
-- begin
-- set @beginTime = (select dateadd(mm,-3,@endTime))
-- end
--else
-- begin
-- set @beginTime = (select dateadd(mm,-6,@endTime))
-- end
create table #tempAvg3Group(materialGroup varchar(20),amount decimal(30,6))
create index IX_SHKD_MID on #tempAvg3Group(materialGroup)
insert into #tempAvg3Group
select F_SHKD_PLCNEW,sum(d.FAMOUNT) amount from t_bd_material a
left join T_SAL_ORDERENTRY b on a.FMATERIALID = b.FMATERIALID
left join T_SAL_ORDER c on b.fid = c.fid
left join T_SAL_ORDERENTRY_F d on b.fentryid = d.fentryid
where c.FDOCUMENTSTATUS = 'C' and c.fdate between @beginTime and @endTime group by F_SHKD_PLCNEW
create table #tempAvg3Material(fmaterialid varchar(20),materialGroup varchar(20),amount decimal(30,6))
create index IX_SHKD_MID on #tempAvg3Material(fmaterialid,materialGroup)
insert into #tempAvg3Material
select a.FMATERIALID,a.F_SHKD_PLCNEW,sum(d.FAMOUNT) from t_bd_material a left join T_SAL_ORDERENTRY b on a.FMATERIALID = b.FMATERIALID
left join T_SAL_ORDER c on b.fid = c.fid
left join T_SAL_ORDERENTRY_F d on b.fentryid = d.fentryid
where c.FDOCUMENTSTATUS = 'C' and c.fdate between @begintime and @endTime group by a.FMATERIALID,a.F_SHKD_PLCNEW
create table #tempAvg3(fmaterialid varchar(20),avg3ratio decimal(30,10))
create index IX_SHKD_MID on #tempAvg3(fmaterialid)
insert into #tempAvg3
select a.fmaterialid,a.amount/b.amount from #tempAvg3Material a left join #tempAvg3Group b on a.materialGroup = b.materialGroup where b.amount!=0
-- 六月的
set @beginTime = (select dateadd(mm,-6,@endTime))
create table #tempAvg6Group(materialGroup varchar(20),amount decimal(30,6))
create index IX_SHKD_MID on #tempAvg6Group(materialGroup)
insert into #tempAvg6Group
select F_SHKD_PLCNEW,sum(d.FAMOUNT) amount from t_bd_material a
left join T_SAL_ORDERENTRY b on a.FMATERIALID = b.FMATERIALID
left join T_SAL_ORDER c on b.fid = c.fid
left join T_SAL_ORDERENTRY_F d on b.fentryid = d.fentryid
where c.FDOCUMENTSTATUS = 'C' and c.fdate between @beginTime and @endTime group by F_SHKD_PLCNEW
create table #tempAvg6Material(fmaterialid varchar(20),materialGroup varchar(20),amount decimal(30,6))
create index IX_SHKD_MID on #tempAvg6Material(fmaterialid,materialGroup)
insert into #tempAvg6Material
select a.FMATERIALID,a.F_SHKD_PLCNEW,sum(d.FAMOUNT) from t_bd_material a left join T_SAL_ORDERENTRY b on a.FMATERIALID = b.FMATERIALID
left join T_SAL_ORDER c on b.fid = c.fid
left join T_SAL_ORDERENTRY_F d on b.fentryid = d.fentryid
where c.FDOCUMENTSTATUS = 'C' and c.fdate between @begintime and @endTime group by a.FMATERIALID,a.F_SHKD_PLCNEW
create table #tempAvg6(fmaterialid varchar(20),avg6ratio decimal(30,10))
create index IX_SHKD_MID on #tempAvg6(fmaterialid)
insert into #tempAvg6
select a.fmaterialid,a.amount/b.amount from #tempAvg6Material a left join #tempAvg6Group b on a.materialGroup = b.materialGroup where b.amount!=0
create table #tempMain(ffid varchar(10),gfid varchar(10),fcsc decimal(30,10),period varchar(10),
fuserorgid varchar(10),fmaterialid varchar(20),materialno varchar(50),materialname varchar(100),special_case_comments varchar(50),
asp decimal(30,10),avg3ratio decimal(30,10),avg6ratio decimal(30,10),select_model varchar(20),pl6code varchar(10),plgroup_Name varchar(50))
create index IX_SHKD_MID on #tempMain(FMATERIALID)
insert into #tempMain
select f.fid as ffid,g.fid as gfid,g.F_SHKD_FCSC as FCSC,
concat(year(g.F_SHKD_PERIOD),'-',month(g.F_SHKD_PERIOD)) as [period] ,
a.fuseorgid,a.FMATERIALID,a.FNUMBER as materialNo,b.FNAME as materialName, '' as [Special_Case_Comments], round(c.FPRICE ,2) as [ASP],
avg3.avg3ratio as [Avg3Ratio],avg6.avg6ratio as [Avg6Ratio],
f.F_SHKD_SM as[select_model],tsp.FNUMBER as [PL6Code], plgroup.FName as plgroup_Name
from T_BD_material a
left join T_BD_MATERIAL_L b on a.FMATERIALID = b.fmaterialid
full join T_SAL_PRICELISTENTRY c on b.FMATERIALID = c.FMATERIALID
left join T_SAL_PRICELIST priceTable on c.fid = priceTable.fid
inner join SHKD_WLFZYCSFBill f on f.F_PATC_PL6CODE = a.F_SHKD_PLCNEW
inner join SHKD_MYZBWCZBBill g on g.F_PATC_PL6CODE = f.F_PATC_PL6CODE
left join T_SHKD_PLCGROUP_L plgroup on g.F_PATC_PL6CODE = plgroup.fid
left join T_SHKD_PLCGROUP tsp on plgroup.fid = tsp.FID
left join #tempAvg3 avg3 on a.fmaterialid = avg3.fmaterialid
left join #tempAvg6 avg6 on a.fmaterialid = avg6.fmaterialid
where a.FDOCUMENTSTATUS = 'C' and g.FDOCUMENTSTATUS = 'C' and priceTable.FDOCUMENTSTATUS = 'C'
and datediff(month,g.F_SHKD_PERIOD,@curperoid) = 0 and priceTable.FFORBIDSTATUS = 'A' and year(@curperoid) = f.F_SHKD_YEAR
and f.FDOCUMENTSTATUS = 'C' and F_PATC_PRICETYPE = 'ASP' and g.F_SHKD_NF = f.F_SHKD_YEAR
and @curperoid between priceTable.FEFFECTIVEDATE and priceTable.FEXPIRYDATE
order by fmaterialid
--查询语句
select a.period as [期间],PL6Code as 'PL6Code',a.materialNo 物料编码,a.materialName 物料名称,
a.[Special_Case_Comments],a.ASP,a.Avg3Ratio,a.Avg6Ratio,
a.[select_model],a.[预测期间],
cast(
(case [select_model]
when 'Avg3' then round( ((a.target*a.ZB*(a.FCSC))*a.Avg3Ratio/10000) ,2 )
when 'Avg6' then round( ((a.target*a.ZB*(a.FCSC))*a.Avg6Ratio/10000) ,2 ) end) as decimal(30,2)) as [金额],
cast(
round(
((case [select_model]
when 'Avg3' then (a.target*a.ZB*(a.FCSC))*a.Avg3Ratio/10000
when 'Avg6' then (a.target*a.ZB*(a.FCSC))*a.Avg6Ratio/10000 end)/a.ASP),0) as int) as 数量
from (
select a.*,h.forcastPeriod as [预测期间],h.Proportion as ZB,h.target as [target] from #tempMain a
inner join #tempA h on a.plgroup_Name = h.materialGroup
where datediff(month,@curperoid,concat(h.forcastPeriod,'-01'))>=0
and a.Avg6Ratio!=0
) a
left join SHKD_WLFZYCSFBill b on a.ffid = b.fid left join SHKD_MYZBWCZBBill c on a.gfid = c.fid
order by a.PL6Code,a.materialNo,a.[预测期间];
--select a.*,h.forcastPeriod as [预测期间],h.Proportion as ZB,h.target as [target] from #tempMain a
--inner join #tempA h on a.plgroup_Name = h.materialGroup
--where datediff(month,@curperoid,concat(h.forcastPeriod,'-01'))>=0
drop table #tempMain;
drop table #tempAvg3Group
drop table #tempAvg3Material
drop table #tempAvg3
drop table #tempAvg6Group
drop table #tempAvg6Material
drop table #tempAvg6
select FSOURCEFROMID,* from T_AP_PAYMatchLogENTRY entry left join T_AP_PAYMatchLog bill on entry.fid = bill.fid
where FSOURCEFROMID ='V_CN_PAYBILL'
select entry.fid,org_l.FNAME 店名,FSRCBILLNO 单据编号,CONVERT(VARCHAR(10),payble.FDATE,120) 业务日期,payble.FALLAMOUNTFOR 总金额,entry.FCURWRITTENOFFAMOUNTFOR 核销金额,
case entry.FCONTACTUNITTYPE
when 'BD_Customer' then cusl.fname
when 'FIN_OTHERS' then finl.FNAME
when 'BD_Supplier' then supl.FNAME
when 'BD_Empinfo' then empl.FNAME
end 客户,
payble.FREMARK 备注,
entry.FSOURCEFROMID 单据类型
from T_AP_PAYMatchLogENTRY entry
inner join T_AP_PAYMatchLog bill on bill.fid = entry.fid
inner join T_AP_PAYABLE payble on entry.FSRCBILLID = payble.FID
inner join T_ORG_Organizations org on entry.FBUSINESSORGID = org.FORGID
inner join T_ORG_ORGANIZATIONS_L org_l on org.FORGID = org_l.forgid
left join t_bd_customer cus on entry.FCONTACTUNIT = cus.FCUSTID
left join t_bd_customer_l cusl on cus.FCUSTID = cusl.FCUSTID
left join T_FIN_OTHERS fin on entry.FCONTACTUNIT =fin.fid
left join T_FIN_OTHERS_l finl on fin.fid = finl.fid
left join T_BD_SUPPLIER sup on entry.FCONTACTUNIT = sup.FSUPPLIERID
left join T_BD_SUPPLIER_L supl on sup.FSUPPLIERID = supl.FSUPPLIERID
left join T_HR_EMPINFO emp on entry.FCONTACTUNIT = emp.fid
left join T_HR_EMPINFO_L empl on emp.FID = empl.fid
where entry.FSOURCEFROMID = 'AP_Payable'
二、进行BOS页面，创建直接Sql报表
三、完成相关配置，发布即可