ALTER PROCEDURE [dbo].[usp_rptDropboxBatchSummary1]

    @DataSource     varchar(10)='ALL',

    @BatchNum        varchar(8)='ALL',

    @CurrentProcess varchar(10)='ALL'

AS

BEGIN

    SET NOCOUNT ON;

    --select * from PVBatch

    --select * from pvitem

    --0.set the source table

    select IDENTITY(int,1,1) as id,DataSourceID,BatchNum,CurrentProcess,BatchStatus,TotalChequeCount,0 as RejectChequeCount,0 as AcceptChequeCount,

            0 as HostPostedCount,convert(varchar(10),'') as SplitBatchNum,Currencycode,0 as SplitBatchAcceptCount,convert(decimal(18,2), 0) as SplitBatchClearingChqAmt,Extracted

            into #temp_RptBatchs from PVBatch with(nolock) where 1=1

            --where (DataSourceID=@DataSource or @DataSource='ALL')

            --and (BatchNum=@BatchNum or @BatchNum='ALL')

            --and (CurrentProcess=@CurrentProcess or @CurrentProcess='ALL')

    --1.get the conditon data from PVItem

    select  i.BatchNum,i.CurrencyCode,ItemStatus,HostPostStatus,isnull(ClearingChqAmt,0) as ClearingChqAmt,b.Extracted into #temp_batchItems from PVItem i with(nolock)

        join  PVBatch b on i.batchnum=b.batchnum

        where ItemType='C' and i.batchnum in (select batchnum from #temp_RptBatchs)

    --2.get the RejectChequeCount and AcceptChequeCount and HostPostedCount

    --3.set the RejectChequeCount

    update a set a.RejectChequeCount=b.Rcount from #temp_RptBatchs a,

        (select count(*) as Rcount,batchnum  from #temp_batchItems where itemstatus='Reject' group by batchnum) b where a.batchnum=b.batchnum

    --4.get the AcceptChequeCount

    --5.set the AcceptChequeCount

    update a set a.AcceptChequeCount=b.Rcount from #temp_RptBatchs a,

    (select count(*) as Rcount,batchnum  from #temp_batchItems where itemstatus='Accept' group by batchnum) b where a.batchnum=b.batchnum

    --6.get the HostPostedCount

    --7.set the HostPostedCount

    update a set a.HostPostedCount=b.Rcount from #temp_RptBatchs a,

        (select count(*) as Rcount,batchnum from #temp_batchItems where HostPostStatus='P' group by batchnum)  b where a.batchnum=b.batchnum

    --8.to get the split batch table group by the currency code

    select batchNum,batchNum as SplitBatchNum,CurrencyCode,sum(ClearingChqAmt) as SplitClearingChqAmt,0 as SplitBatchAcceptCount,Extracted into #temp_SplitBatch

         from #temp_batchItems  group by CurrencyCode,batchNum,Extracted

    --9.set the SplitBatchAcceptCount

    update a set a.SplitBatchAcceptCount=b.AcceptCurrency from

    (select batchNum,count(itemstatus) as AcceptCurrency,CurrencyCode from #temp_batchItems where itemstatus='Accept' group by CurrencyCode,batchNum) as b,#temp_SplitBatch a

    where a.batchnum=b.batchnum and a.CurrencyCode=b.CurrencyCode

    --10.process the splitbatchnum(120001 --> 130001 (HKD)140001 (USD)150001 (CNY) update the back fifth)

    update #temp_SplitBatch set splitbatchnum=left(splitbatchnum,len(splitbatchnum)-5)+''+right(splitbatchnum,4) where currencycode='HKD' and (Extracted='Y' or Extracted='P')

    update #temp_SplitBatch set splitbatchnum=left(splitbatchnum,len(splitbatchnum)-5)+''+right(splitbatchnum,4) where currencycode='USD' and (Extracted='Y' or Extracted='P')

    update #temp_SplitBatch set splitbatchnum=left(splitbatchnum,len(splitbatchnum)-5)+''+right(splitbatchnum,4) where currencycode='CNY' and (Extracted='Y' or Extracted='P')

    --11.update and insert the split data into the Rpt table

    --select * from #temp_RptBatchs

    --select * from #temp_SplitBatch

    Select * from #temp_RptBatchs left join #temp_SplitBatch on #temp_RptBatchs.BatchNum = #temp_SplitBatch.BatchNum

    drop table #temp_RptBatchs

    drop table #temp_batchItems

    drop table #temp_SplitBatch

END

ALTER PROCEDURE [dbo].[usp_rptDropboxBatchSummary]

    @DataSource     varchar(10)='ALL',

    @BatchNum        varchar(8)='ALL',

    @CurrentProcess varchar(10)='ALL'

AS

BEGIN

    IF @DataSource='ALL'

    Begin

        Set @DataSource=''

    End

    ;With Batch as(

    select DataSourceID,PVBatch.BatchNum,CurrentProcess,BatchStatus,TotalChequeCount,Extracted,currencycode

            from PVBatch

            where (DataSourceID=@DataSource or @DataSource='')

            and (isnull(@BatchNum,'')='' or BatchNum=@BatchNum or @BatchNum='ALL')

            and (CurrentProcess=@CurrentProcess or @CurrentProcess='ALL')

    ),

     Item as(

    select BatchNum

            ,Sum(Case When itemstatus='Reject' Then 1 Else 0 End) as RejectCount

        ,Sum(Case When itemstatus='Accept' Then 1 Else 0 End) as AcceptCount

        ,Sum(Case When HostPostStatus='P' Then 1 Else 0 End) as HostPostCount

            from PVITem

        Group By BatchNum

    ),

    PItem as(

    select PVBatch.BatchNum ,PVITem.currencycode,PVBatch.Extracted,

            sum(ClearingChqAmt) as SplitClearingChqAmt

        , Case When PVITem.currencycode='HKD' and (PVBatch.Extracted='Y' or PVBatch.Extracted='P') Then left(PVBatch.BatchNum,len(PVBatch.BatchNum)-5)+''+right(PVBatch.BatchNum,4)

               When PVITem.currencycode='USD' and (PVBatch.Extracted='Y' or PVBatch.Extracted='P') Then left(PVBatch.BatchNum,len(PVBatch.BatchNum)-5)+''+right(PVBatch.BatchNum,4)

               When PVITem.currencycode='CNY' and (PVBatch.Extracted='Y' or PVBatch.Extracted='P') Then left(PVBatch.BatchNum,len(PVBatch.BatchNum)-5)+''+right(PVBatch.BatchNum,4)

          else PVBatch.BatchNum

          End as SplitBatchNum

          ,Sum(Case When itemstatus='Accept' then 1 else 0 end) as SplitAcceptCount

            from PVBatch Left join PVITem

            on PVBatch.BatchNum = PVItem.BatchNum

            where (PVBatch.DataSourceID=@DataSource or @DataSource='')

            and  (@BatchNum='ALL' or PVBatch.BatchNum=@BatchNum or isnull(@BatchNum,'')='')

            and (PVBatch.CurrentProcess=@CurrentProcess or @CurrentProcess='ALL')

        Group By PVBatch.BatchNum,PVITem.currencycode,PVBatch.Extracted

    )

    select

    Batch.*, IsNull(Item.RejectCount,0) RejectCount, IsNull(Item.AcceptCount,0) AcceptCount, IsNull(Item.HostPostCount,0) HostPostCount

    , Isnull(PITem.currencycode, Batch.currencycode) currencycode, Isnull(PITem.SplitClearingChqAmt,0) SplitClearingChqAmt,PITem.SplitAcceptCount, PItem.SplitBatchNum

    , Case When row_number() over( PARTITION BY Batch. BatchNum order by SplitBatchNum) = 1 then 0 else 1 end  groupflag

     from Batch Left join Item

    on Batch. BatchNum = ITem. BatchNum

    Left join PITem

    on Batch. BatchNum = PITem.BatchNum

    order by Batch. BatchNum,PItem.SplitBatchNum

    End