proc_imissue_alys.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

if object_id('dbo.Proc_ImIssue_Alys') is not null
drop procedure dbo.Proc_ImIssue_Alys
go
Create procedure Proc_ImIssue_Alys
@plant  char(4) = null,
@frm_dt datetime = null,
@end_dt datetime = null,
@field  int
as

begin
	create table #im_analysis
	(
	item int identity(1,1) not null,
	plant varchar(4) not null,
	im_dt datetime not null,
	vendor_nm varchar(120) null,
	issue_desc varchar(120) null,	
	buyer	  varchar(15) null,
	Amount	  numeric(18,2) null
	)
	
	if(isnull(@plant,'')<>'') and (@frm_dt<>'1990-01-01') 
	begin
		insert into #im_analysis(plant,im_dt,vendor_nm,issue_desc,buyer) 
		select 
a.Plant,
a.IM_DT as [IM. Date],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.Buyer
 from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type
and a.plant=@plant
		and a.im_dt between @frm_dt and @end_dt
	end
	else if(isnull(@plant,'')<>'') and (@frm_dt='1990-01-01') 
begin
		insert into #im_analysis(plant,im_dt,vendor_nm,issue_desc,buyer) 
		select 
a.Plant,
a.IM_DT as [IM. Date],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.Buyer
  from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type
and a.plant=@plant
	end
	else 
		if(isnull(@plant,'')='') and (@frm_dt<>'1990-01-01') 
	begin
		insert into #im_analysis(plant,im_dt,vendor_nm,issue_desc,buyer) 
		select 
a.Plant,
a.IM_DT as [IM. Date],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.Buyer
 from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type
and  a.im_dt between @frm_dt and @end_dt
	end				
	else
	  if(isnull(@plant,'')='') and (@frm_dt='1990-01-01') 
	begin
		insert into #im_analysis(plant,im_dt,vendor_nm,issue_desc,buyer) 
		select 
a.Plant,
a.IM_DT as [IM. Date],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.Buyer
 from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type

end
	else
	  begin
		insert into #im_analysis(plant,im_dt,vendor_nm,issue_desc,buyer) 
		select 
a.Plant,
a.IM_DT as [IM. Date],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.Buyer
 from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type


end


   update #im_analysis
	set Amount =(select isnull(count(*),0) from #im_analysis)
      where im_dt is not null
	


	declare
	@sqlstr varchar(800) 
	if(@field=0) 
	select @sqlstr ='select count(vendor_nm) as [Statistic],vendor_nm as [Vendor Name] ,
        convert(varchar(10),convert(numeric(18,2),(count(vendor_nm)/Amount)*100))+'+'''%'''+' as [Percentage]    from #im_analysis where 
       isnull(vendor_nm,'+''' '''+')<>'+''' '''+' group by vendor_nm,Amount order by count(vendor_nm) desc '
	else
	if(@field=1) 
	select @sqlstr ='select count(issue_desc) as [Statistic],issue_desc as [Im Issue],
             convert(varchar(10),convert(numeric(18,2),count(vendor_nm)/Amount*100))+'+'''%'''+' as [Percentage]
        from #im_analysis where isnull(issue_desc,'+''' '''+')<>'+''' '''+' group by issue_desc,Amount order by count(issue_desc)  desc '
	else
	if(@field=2) 
	select @sqlstr ='select count(buyer) as [Statistic],Buyer,
               convert(varchar(10),convert(numeric(18,2),(count(vendor_nm)/Amount))*100)+'+'''%'''+' as [Percentage]from #im_analysis 
       where isnull(buyer,'+''' '''+')<>'+''' '''+' group by buyer,Amount order by count(buyer)  desc '
	exec(@sqlstr)                      
	drop table #im_analysis

end

/*

exec   Proc_ImIssue_Alys null,'1990-01-01',null,0


*/