USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONDONORCHALLENGECLAIMDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONDONORCHALLENGECLAIMDETAIL (
  @ID nvarchar(73)
  ,@DATALOADED bit = 0 output
  ,@DONORCLAIMID uniqueidentifier = null output
  ,@GIVENANONYMOUSLY bit = null output
  ,@TOTALDONORCLAIMAMOUNT money = null output
  ,@TOTALSPLITS int = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  ,@TRANSACTIONTYPE nvarchar(100) = null output
  ,@TOTALAPPLIEDTOTHISPURPOSE money = null output
  ,@THISPURPOSESPLITS xml = null output
  ,@TOTALAPPLIEDTOOTHERPURPOSES money = null output
  ,@OTHERPURPOSESPLITS xml = null output
  ,@PAYMENTSTOTAL money = null output
  ,@PAYMENTS xml = null output
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
  )
as
begin
  set nocount on;
  set @DATALOADED = 0;

  declare @PURPOSEID uniqueidentifier = null;

  select @DONORCLAIMID = cast(substring(@ID, 0, 37) as uniqueidentifier)

  select @PURPOSEID = cast(substring(@ID, 38, 36) as uniqueidentifier)

  select @DATALOADED = 1
    ,@TOTALDONORCLAIMAMOUNT = FT.TRANSACTIONAMOUNT
    ,@BASECURRENCYID = null
    ,@TRANSACTIONTYPE = FT.TYPE
    ,@GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY
    ,@TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID
  from dbo.FINANCIALTRANSACTION FT
  inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
  where FT.ID = @DONORCLAIMID

  declare @designationttable table (
    ID uniqueidentifier
    ,USERID nvarchar(512)
    ,AMOUNT money
    ,CURRENCYID uniqueidentifier
    ,THISPURPOSE bit
    );

  insert into @designationttable (
    ID
    ,USERID
    ,AMOUNT
    ,CURRENCYID
    ,THISPURPOSE
    )
  select [ID] = D.ID
    ,[USERID] = D.USERID
    ,[AMOUNT] = COMMITMENTLINEITEM.TRANSACTIONAMOUNT
    ,[CURRENCYID] = @TRANSACTIONCURRENCYID
    ,THISPURPOSE = case 
      when @PURPOSEID in (
          D.DESIGNATIONLEVEL1ID
          ,D.DESIGNATIONLEVEL2ID
          ,D.DESIGNATIONLEVEL3ID
          ,D.DESIGNATIONLEVEL4ID
          ,D.DESIGNATIONLEVEL5ID
          )
        then 1
      else 0
      end
  from dbo.FINANCIALTRANSACTION COMMITMENT with (nolock)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM with (nolock) on COMMITMENT.ID = COMMITMENTLINEITEM.FINANCIALTRANSACTIONID
    and COMMITMENTLINEITEM.DELETEDON is null
    and COMMITMENT.DELETEDON is null
  inner join dbo.REVENUESPLIT_EXT COMMITMENTLINEITEMAPPINFO with (nolock) on COMMITMENTLINEITEM.ID = COMMITMENTLINEITEMAPPINFO.ID
  inner join dbo.DESIGNATION D with (nolock) on COMMITMENTLINEITEMAPPINFO.DESIGNATIONID = D.ID
  where COMMITMENT.ID = @DONORCLAIMID

  select @TOTALSPLITS = COUNT(distinct ID)
    ,@TOTALAPPLIEDTOTHISPURPOSE = sum(case 
        when THISPURPOSE = 1
          then AMOUNT
        else 0
        end)
    ,@TOTALAPPLIEDTOOTHERPURPOSES = sum(case 
        when THISPURPOSE = 0
          then AMOUNT
        else 0
        end)
  from @designationttable

  set @THISPURPOSESPLITS = (
      select top 6 [ID] = D.ID
        ,[AMOUNT] = SUM(D.AMOUNT)
        ,[DESIGNATION] = D.USERID
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID
      from @designationttable D
      where THISPURPOSE = 1
      group by D.ID
        ,D.USERID
      order by [AMOUNT] desc
        ,D.USERID
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('THISPURPOSESPLITS')
        ,binary BASE64
      )
  set @THISPURPOSESPLITS = isnull(@THISPURPOSESPLITS, '<THISPURPOSESPLITS></THISPURPOSESPLITS>')
  set @OTHERPURPOSESPLITS = (
      select top 6 [ID] = D.ID
        ,[AMOUNT] = SUM(D.AMOUNT)
        ,[DESIGNATION] = D.USERID
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID
      from @designationttable D
      where THISPURPOSE = 0
      group by D.ID
        ,D.USERID
      order by [AMOUNT] desc
        ,D.USERID
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('OTHERPURPOSESPLITS')
        ,binary BASE64
      )
  set @OTHERPURPOSESPLITS = isnull(@OTHERPURPOSESPLITS, '<OTHERPURPOSESPLITS></OTHERPURPOSESPLITS>')

  select @PAYMENTSTOTAL = sum(ISP.AMOUNT)
  from dbo.FINANCIALTRANSACTION PAYMENT with (nolock)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLINEITEM with (nolock) on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
    and PAYMENT.TYPECODE = 0
    and PAYMENT.DELETEDON is null
  inner join (
    select ISP.PAYMENTID
      ,ISP.PLEDGEID
      ,SUM(ISP.AMOUNT) AMOUNT
    from dbo.INSTALLMENTSPLITPAYMENT ISP with (nolock)
    where ISP.PLEDGEID = @DONORCLAIMID
    group by ISP.PLEDGEID
      ,ISP.PAYMENTID
    ) ISP on PAYMENTLINEITEM.ID = ISP.PAYMENTID
    and PAYMENTLINEITEM.DELETEDON is null
  inner join dbo.REVENUE_EXT PAYMENT_EXT with (nolock) on PAYMENT.ID = PAYMENT_EXT.ID
  inner join dbo.REVENUESPLIT_EXT PAYMENTAPPINFO with (nolock) on PAYMENTLINEITEM.ID = PAYMENTAPPINFO.ID
  inner join dbo.DESIGNATION D with (nolock) on PAYMENTAPPINFO.DESIGNATIONID = D.ID
  inner join dbo.FINANCIALTRANSACTION COMMITMENT with (nolock) on ISP.PLEDGEID = COMMITMENT.ID
    and COMMITMENT.DELETEDON is null
  inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM with (nolock) on COMMITMENT.ID = COMMITMENTLINEITEM.FINANCIALTRANSACTIONID
    and COMMITMENTLINEITEM.DELETEDON is null
  inner join dbo.REVENUESPLIT_EXT COMMITMENTLINEITEMAPPINFO with (nolock) on COMMITMENTLINEITEM.ID = COMMITMENTLINEITEMAPPINFO.ID
    and (
      PAYMENTLINEITEM.SOURCELINEITEMID = COMMITMENTLINEITEMAPPINFO.ID
      or (
        COMMITMENTLINEITEMAPPINFO.DESIGNATIONID = D.ID
        and PAYMENTLINEITEM.SOURCELINEITEMID is null
        )
      )
  where COMMITMENT.ID = @DONORCLAIMID
    and (
      D.DESIGNATIONLEVEL1ID = @PURPOSEID
      or D.DESIGNATIONLEVEL2ID = @PURPOSEID
      or D.DESIGNATIONLEVEL3ID = @PURPOSEID
      or D.DESIGNATIONLEVEL4ID = @PURPOSEID
      or D.DESIGNATIONLEVEL5ID = @PURPOSEID
      );

  with PAYMENTS_CTE
  as (
    select ISP.PAYMENTID
      ,ISP.PLEDGEID
      ,SUM(ISP.AMOUNT) AMOUNT
    from dbo.INSTALLMENTSPLITPAYMENT ISP with (nolock)
    where ISP.PLEDGEID = @DONORCLAIMID
    group by ISP.PLEDGEID
      ,ISP.PAYMENTID
    )
  select @PAYMENTS = (
      select top 6 [PAYMENTID] = PAYMENT.ID
        ,[DATE] = cast(PAYMENT.[DATE] as datetime)
        ,[GIVENANONYMOUSLY] = PAYMENT_EXT.GIVENANONYMOUSLY
        ,[AMOUNT] = SUM(ISP.AMOUNT)
        ,[DESIGNATION] = dbo.UDA_BUILDLIST(distinct D.USERID)
        ,[BASECURRENCYID] = null
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID
      from dbo.FINANCIALTRANSACTION PAYMENT with (nolock)
      inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLINEITEM with (nolock) on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
        and PAYMENT.TYPECODE = 0
        and PAYMENT.DELETEDON is null
      inner join PAYMENTS_CTE ISP on PAYMENTLINEITEM.ID = ISP.PAYMENTID
        and PAYMENTLINEITEM.DELETEDON is null
      inner join dbo.REVENUE_EXT PAYMENT_EXT with (nolock) on PAYMENT.ID = PAYMENT_EXT.ID
      inner join dbo.REVENUESPLIT_EXT PAYMENTAPPINFO with (nolock) on PAYMENTLINEITEM.ID = PAYMENTAPPINFO.ID
      inner join dbo.DESIGNATION D with (nolock) on PAYMENTAPPINFO.DESIGNATIONID = D.ID
      inner join dbo.FINANCIALTRANSACTION COMMITMENT with (nolock) on ISP.PLEDGEID = COMMITMENT.ID
        and COMMITMENT.DELETEDON is null
      inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM with (nolock) on COMMITMENT.ID = COMMITMENTLINEITEM.FINANCIALTRANSACTIONID
        and COMMITMENTLINEITEM.DELETEDON is null
      inner join dbo.REVENUESPLIT_EXT COMMITMENTLINEITEMAPPINFO with (nolock) on COMMITMENTLINEITEM.ID = COMMITMENTLINEITEMAPPINFO.ID
        and (
          PAYMENTLINEITEM.SOURCELINEITEMID = COMMITMENTLINEITEMAPPINFO.ID
          or (
            COMMITMENTLINEITEMAPPINFO.DESIGNATIONID = D.ID
            and PAYMENTLINEITEM.SOURCELINEITEMID is null
            )
          )
      where COMMITMENT.ID = @DONORCLAIMID
        and (
          D.DESIGNATIONLEVEL1ID = @PURPOSEID
          or D.DESIGNATIONLEVEL2ID = @PURPOSEID
          or D.DESIGNATIONLEVEL3ID = @PURPOSEID
          or D.DESIGNATIONLEVEL4ID = @PURPOSEID
          or D.DESIGNATIONLEVEL5ID = @PURPOSEID
          )
      group by PAYMENT.ID
        ,cast(PAYMENT.[DATE] as datetime)
        ,PAYMENT_EXT.GIVENANONYMOUSLY
      order by [DATE] desc
        ,[AMOUNT] desc
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('PAYMENTS')
        ,binary BASE64
      )

  set @PAYMENTS = isnull(@PAYMENTS, '<PAYMENTS></PAYMENTS>')

  return 0;
end