USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONRECURRINGGIFTDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONRECURRINGGIFTDETAIL (
  @ID nvarchar(73)
  ,@DATALOADED bit = 0 output
  ,@RECURRINGGIFTID uniqueidentifier = null output
  ,@GIVENANONYMOUSLY bit = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  ,@TRANSACTIONTYPE nvarchar(100) = null output
  ,@TOTALSPLITS int = null output
  ,@TOTALAPPLIEDTOTHISPURPOSE money = null output
  ,@THISPURPOSESPLITS xml = null output
  ,@TOTALAPPLIEDTOOTHERPURPOSES money = null output
  ,@OTHERPURPOSESPLITS xml = null output
  ,@AMOUNT money = null output
  ,@FREQUENCYCODE tinyint = null output
  ,@STARTON date = null output
  ,@ENDON date = null output
  ,@PAYMENTSTOTAL money = null output
  ,@PAYMENTS xml = null output
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
  )
as
begin
  set nocount on;
  -- be sure to set this, in case the select returns no rows
  set @DATALOADED = 0;

  declare @PURPOSEID uniqueidentifier = null;

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

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

  select @DATALOADED = 1
    ,@BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
    ,@AMOUNT = FT.TRANSACTIONAMOUNT
    ,@FREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE
    ,@STARTON = REVENUESCHEDULE.STARTDATE
    ,@ENDON = REVENUESCHEDULE.ENDDATE
    ,@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
  inner join dbo.REVENUESCHEDULE on FT.ID = REVENUESCHEDULE.ID
  left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FT.PDACCOUNTSYSTEMID
  left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
  where FT.ID = @RECURRINGGIFTID

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

  insert into @designationttable (
    ID
    ,USERID
    ,AMOUNT
    ,CURRENCYID
    ,THISPURPOSE
    )
  select D.ID
    ,D.USERID
    ,FTLI.TRANSACTIONAMOUNT
    ,@TRANSACTIONCURRENCYID
    ,THISPURPOSE = case 
      when @PURPOSEID in (
          D.DESIGNATIONLEVEL1ID
          ,D.DESIGNATIONLEVEL2ID
          ,D.DESIGNATIONLEVEL3ID
          ,D.DESIGNATIONLEVEL4ID
          ,D.DESIGNATIONLEVEL5ID
          )
        then 1
      else 0
      end
  from dbo.FINANCIALTRANSACTION FT
  inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID
  inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = FTLI.ID
  inner join dbo.DESIGNATION D on D.ID = RSE.DESIGNATIONID
  inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
  left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FT.PDACCOUNTSYSTEMID
  left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
  where FT.ID = @RECURRINGGIFTID
    and FT.DELETEDON is null
    and FTLI.DELETEDON is null

  select @TOTALSPLITS = COUNT(distinct ID)
  from @designationttable

  select @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>')

  set @PAYMENTSTOTAL = (
      select sum(RGA.AMOUNT) AMOUNT
      from dbo.FINANCIALTRANSACTION FT
      inner join dbo.RECURRINGGIFTACTIVITY RGA on RGA.SOURCEREVENUEID = FT.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RGA.PAYMENTREVENUEID
      inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
      inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
      where FT.ID = @RECURRINGGIFTID
        and FT.DELETEDON is null
        and FTLI.DELETEDON is null
        and (
          DESIGNATION.DESIGNATIONLEVEL1ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL2ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL3ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL4ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL5ID = @PURPOSEID
          )
      )
  set @PAYMENTS = (
      select top 6 FTLI.FINANCIALTRANSACTIONID as PAYMENTID
        ,cast(PAYMENT.[DATE] as datetime) as date
        ,PAYMENT_EXT.GIVENANONYMOUSLY
        ,RGA.AMOUNT
        ,DESIGNATION.USERID DESIGNATION
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID
      from dbo.FINANCIALTRANSACTION FT
      inner join dbo.RECURRINGGIFTACTIVITY RGA on RGA.SOURCEREVENUEID = FT.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RGA.PAYMENTREVENUEID
      inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
      inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
      inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
      inner join dbo.REVENUE_EXT PAYMENT_EXT on FTLI.FINANCIALTRANSACTIONID = PAYMENT_EXT.ID
      inner join dbo.FINANCIALTRANSACTION PAYMENT on FTLI.FINANCIALTRANSACTIONID = PAYMENT.ID
      left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FT.PDACCOUNTSYSTEMID
      left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
      where FT.ID = @RECURRINGGIFTID
        and FT.DELETEDON is null
        and FTLI.DELETEDON is null
        and (
          DESIGNATION.DESIGNATIONLEVEL1ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL2ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL3ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL4ID = @PURPOSEID
          or DESIGNATION.DESIGNATIONLEVEL5ID = @PURPOSEID
          )
      order by PAYMENT.[DATE] desc
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('PAYMENTS')
        ,binary BASE64
      )
  set @PAYMENTS = isnull(@PAYMENTS, '<PAYMENTS></PAYMENTS>')

  return 0;
end