• SP--report存储过程


    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
    奋斗
  • 相关阅读:
    Ubuntu 图形界面和终端切换
    docker 启动失败
    Windows 压缩文件到 Linux中解压文件名乱码
    PHP注释标记整理
    docker常用命令
    caffe初体验
    css中的单位
    js动态添加div
    Pycharm 2019 添加 docker 解释器
    常用的方法整理
  • 原文地址:https://www.cnblogs.com/aimerh/p/8408834.html
Copyright © 2020-2023  润新知