USE [edison_prc] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_Load_alert_Rpt] (@loadtype varchar(50),@officecode char(10),@consigneestr varchar(300),@movement varchar(10),@containertypelist varchar(500),@day1 int,@day2 int) AS BEGIN declare @database_name varchar(100),@sql varchar(6000),@current_db varchar(100),@p int,@q int,@tm_len int,@etd_sql varchar(200), @movement_sql varchar(100),@rpt_sql varchar(2000),@cbm_sql varchar(1000),@min_cbm varchar(10),@max_kgs varchar(10),@typelist varchar(500) create table #t_Loadalert_report ([ID] [int] IDENTITY(1,1) NOT NULL,officecode varchar(10),sono varchar(20), so_seq int,po_no varchar(50),ref_no varchar(50),[Item Reference] varchar(100),shippername char(50), actshippername char(50),bookingdate varchar(19),cargoreadydate varchar(10),CY_ClosingDate varchar(10),lportname char(50), dportname char(50),Movement char(7),linercode char(10),Vesselname varchar(50),Voyage char(20),etd varchar(10), eta varchar(10),QTY int,unitcode char(2),CBM decimal(12,3),KGS decimal(18,2), [No.of 20GP] int,[No.of 20HC] int,[No.of 40GP] int,[No.of 40HC] int, CONSTRAINT [PK_t_Loadalert_report] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] create table #consignee(companycode varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS ) select @p=1,@q=1,@tm_len = len(ltrim(rtrim(@consigneestr))) while @p<=@tm_len begin if substring(@consigneestr,@p,1)='/' begin insert into #consignee values(substring(@consigneestr,@q,@p - @q)) select @q = @p + 1 end select @p = @p + 1 end if @tm_len>=6 begin insert into #consignee values(substring(@consigneestr,@q ,@p - @q)) end if(charindex('/',@containertypelist)=0) select @typelist='[NO.of '+@containertypelist+']' else begin select @typelist=',[NO.of '+substring(@containertypelist,1,charindex('/',@containertypelist)-1)+']' select @containertypelist=substring(@containertypelist,charindex('/',@containertypelist)+1,len(@containertypelist)-charindex('/',@containertypelist)) while charindex('/',@containertypelist)>0 begin select @typelist=@typelist+',[NO.of '+substring(@containertypelist,1,charindex('/',@containertypelist)-1)+']' select @containertypelist=substring(@containertypelist,charindex('/',@containertypelist)+1,len(@containertypelist)-charindex('/',@containertypelist)) end select @typelist=@typelist+(',[NO.of '+@containertypelist+']') select @typelist=substring(@typelist,2,len(@typelist)) end SET @rpt_sql = 'select [ID],officecode,sono,so_seq,po_no,ref_no,[Item Reference],shippername,actshippername, bookingdate,cargoreadydate,CY_ClosingDate,lportname,dportname,Movement,linercode,Vesselname,Voyage,etd,eta, QTY,unitcode,CBM,KGS,'+@typelist+' from #t_Loadalert_report' if @movement is null select @movement_sql='' else begin if @movement='CY/CY' select @movement_sql=' and a.movement=''CY/CY''' else if @movement='CFS/CY' select @movement_sql=' and a.movement=''CFS/CY''' else select @movement_sql=' ' end if @day1 is null and @day2 is null select @etd_sql=' ' else begin if @day1 is null select @etd_sql=' and a.createdate<=convert(char(10),getdate() + '+convert(varchar(3),@day2)+',121)' else if @day2 is null select @etd_sql=' and a.createdate>=convert(char(10),getdate() + '+convert(varchar(3),@day1)+',121)' else select @etd_sql=' and a.createdate>=convert(char(10),getdate() + '+convert(varchar(3),@day1)+',121) and a.createdate<=convert(char(10),getdate()+ '+convert(varchar(3),@day2)+',121)' end if @loadtype='MIN_CBM' begin if @movement='CY/CY' begin select @max_kgs='si' select @cbm_sql='join (select d.sono,d.so_seq,d.seq from (select s.sono,s.so_seq,min(si.itemseq) seq,sum(si.cbm) si_cbm from so_header s,so_item si where s.sono=si.sono and s.so_seq=si.so_seq and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) d, (select s.sono,s.so_seq,sum(bc.qty*cast(isnull(spec_char,''0'') as int)) bc_cbm from so_header s,bookingcontainer bc,edispecialchecking ec where s.sono=bc.bookingno and s.so_seq=bc.bookingseq and ec.checktype=''SO_CNTR_TYPE'' and ec.checkvalue=s.consigneecode and bc.containertype=ec.checkvalue_2 and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) b where d.sono=b.sono and d.so_seq=b.so_seq and d.si_cbm<b.bc_cbm) c on a.sono=c.sono and a.so_seq=c.so_seq' end else begin select @max_kgs='ld' select @cbm_sql='join lp_detail ld (nolock) on a.sono=ld.sono and a.so_seq=ld.so_seq join (select d.sono,d.so_seq,d.seq from (select s.sono,s.so_seq,min(itemseq) seq,sum(ld.cbm) ld_cbm from so_header s,lp_detail ld where s.sono=ld.sono and s.so_seq=ld.so_seq and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) d, (select lh.lp_no,bs.max_cbm bs_cbm from lp_header lh,base bs,edispecialchecking ec where lh.containertype=bs.basecode and ec.checktype=''SO_CNTR_TYPE'' and ec.checkvalue=lh.consigneecode and bs.basecode=ec.checkvalue_2 and lh.consigneecode in (select companycode from #consignee)) b where d.ld_cbm<b.bs_cbm) c on a.sono=c.sono and a.so_seq=c.so_seq' end end else if @loadtype='MAX_KGS' begin if @movement='CY/CY' begin select @max_kgs='si' select @cbm_sql='join (select d.sono,d.so_seq,d.seq from (select s.sono,s.so_seq,min(si.itemseq) seq,sum(si.kgs) si_kgs from so_header s,so_item si where s.sono=si.sono and s.so_seq=si.so_seq and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) d, (select s.sono,s.so_seq,sum(bc.qty*cast(isnull(spec_char2,''0'') as int)) bc_kgs from so_header s,bookingcontainer bc,edispecialchecking ec where s.sono=bc.bookingno and s.so_seq=bc.bookingseq and ec.checktype=''SO_CNTR_TYPE'' and ec.checkvalue=s.consigneecode and bc.containertype=ec.checkvalue_2 and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) b where d.sono=b.sono and d.so_seq=b.so_seq and d.si_kgs>b.bc_kgs) c on a.sono=c.sono and a.so_seq=c.so_seq' end else begin select @max_kgs='ld' select @cbm_sql='join lp_detail ld (nolock) on a.sono=ld.sono and a.so_seq=ld.so_seq join (select d.sono,d.so_seq,d.seq from (select s.sono,s.so_seq,min(itemseq) seq,sum(ld.kgs) ld_kgs from so_header s,lp_detail ld where s.sono=ld.sono and s.so_seq=ld.so_seq and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) d, (select lh.lp_no,bs.max_kgs bs_kgs from lp_header lh,base bs,edispecialchecking ec where lh.containertype=bs.basecode and ec.checktype=''SO_CNTR_TYPE'' and ec.checkvalue=lh.consigneecode and bs.basecode=ec.checkvalue_2 and lh.consigneecode in (select companycode from #consignee)) b where d.ld_kgs<b.bs_kgs) c on a.sono=c.sono and a.so_seq=c.so_seq' end end else begin select @max_kgs='si' select @cbm_sql='join (select d.sono,d.so_seq,d.seq from (select s.sono,s.so_seq,min(si.itemseq) seq,sum(si.cbm) si_cbm from so_header s,so_item si where s.sono=si.sono and s.so_seq=si.so_seq and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) d, (select s.sono,s.so_seq,sum(bc.qty*cast(isnull(spec_char,''0'') as int)) bc_cbm from so_header s,bookingcontainer bc,edispecialchecking ec where s.sono=bc.bookingno and s.so_seq=bc.bookingseq and ec.checktype=''SO_CNTR_TYPE_2'' and ec.checkvalue=s.consigneecode and bc.containertype=ec.checkvalue_2 and s.consigneecode in (select companycode from #consignee) group by s.sono,s.so_seq) b where d.sono=b.sono and d.so_seq=b.so_seq and d.si_cbm=b.bc_cbm) c on a.sono=c.sono and a.so_seq=c.so_seq' end set @current_db = db_name() if @officecode='' declare database_cur cursor for select db_name,officecode from systeminfo where linerflag<>'Y' and linercode='nvocc' and status='Y' and officecode not in ('ASH','SGP','STO') and db_name not like '%test%' and db_name not like '%imp%' else declare database_cur cursor for select db_name,officecode from systeminfo where linerflag<>'Y' and linercode='nvocc' and status='Y' and officecode=@officecode and db_name not like '%test%' and db_name not like '%imp%' --select db_name,officecode from systeminfo where db_name='edison_prc_hkg_nvo_test2' open database_cur fetch database_cur into @database_name,@officecode while @@fetch_status=0 begin set @sql='insert into #t_Loadalert_report' + ' select ''' + @officecode + ''',a.sono, a.so_seq, si.po_no, si.ref_no, (case when (select itemremarks from companyvalidpo (nolock) where si.po_no=companyvalidpo.pono and si.ref_no=companyvalidpo.itemno and si.shipmentno=companyvalidpo.shipmentno and a.consigneecode=companyvalidpo.companycode and si.destcode=companyvalidpo.destcode and companyvalidpo.companytype =''C'')='''' then '''' else (select itemremarks from companyvalidpo (nolock) where si.po_no=companyvalidpo.pono and si.ref_no=companyvalidpo.itemno and si.shipmentno=companyvalidpo.shipmentno and a.consigneecode=companyvalidpo.companycode and si.destcode=companyvalidpo.destcode and companyvalidpo.companytype =''C'') end) as [Item Reference], company.companyname, company_c.companyname, convert(varchar(19),a.bookingdate,120), convert(varchar(10),a.cargoreadydate,120), convert(varchar(10),vv.CY_ClosingDate,120), a.lportname, a.dportname, a.Movement, vv.linercode, ve.Vesselname, vv.Voyage, convert(varchar(10),vv.etd_date,120), convert(varchar(10),vv.eta_datedport,120), '+@max_kgs+'.QTY, '+@max_kgs+'.unitcode, '+@max_kgs+'.CBM, '+@max_kgs+'.KGS, (select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''20GP'') as [No.of20GP], (select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''20HC'') as [No.of20HC], (select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''40GP'') as [No.of40GP], (select qty from bookingcontainer where bookingno=a.sono and bookingseq=a.so_seq and '+@max_kgs+'.itemseq=c.seq and containertype=''40HC'') as [No.of40HC] from so_header a join so_item si (nolock) on a.sono=si.sono and a.so_seq=si.so_seq join vesvoyforwarder vv (nolock) on vv.vesvoyno=a.vesvoyno join vessel ve (nolock) on ve.vesselcode=vv.vesselcode join companyheader company (nolock) on a.shippercode=company.companycode join companyheader company_c (nolock) on a.act_shippercode=company_c.companycode '+@cbm_sql+' where a.consigneecode in (select companycode from #consignee) '+ @etd_sql + @movement_sql exec( 'USE ' + @database_name + ' ' + @sql) fetch database_cur into @database_name ,@officecode end close database_cur deallocate database_cur Exec(@rpt_sql) drop table #t_Loadalert_report drop table #consignee end GO
SP--report存储过程
奋斗