如何在金蝶K3/Cloud创建一个简单的查询报表

合集 - C#(2)
1.金蝶K3/Cloud中，干预生成的凭证
2.如何在金蝶K3/Cloud创建一个简单的查询报表
收起

一、编写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报表

image

三、完成相关配置，发布即可

posted @   moducoder  阅读(86)  评论(0)  编辑  收藏  举报
相关博文：
· 金蝶K3/Cloud中，干预生成的凭证
· 金蝶云星空——简单账表
· 金蝶云星空获取分页账表中的数据并关联生成简单账表
· 调用金蝶标准报表服务
· 金蝶K3 SQL报表系列-委外核销检查表
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示