USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONPLEDGEDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONPLEDGEDETAIL (
  @ID nvarchar(73)
  ,@DATALOADED bit = 0 output
  ,@PLEDGEID uniqueidentifier = null output
  ,@GIVENANONYMOUSLY bit = null output
  ,@TOTALPLEDGEAMOUNT money = null output
  ,@TOTALSPLITS int = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  ,@TRANSACTIONTYPE nvarchar(100) = null output
  ,@FREQUENCYCODE tinyint = null output
  ,@STARTON date = null output
  ,@ENDON date = null output
  ,@NUMBEROFINSTALLMENTS int = 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
  ,@WRITEOFFSTOTAL money = null output
  ,@WRITEOFFS xml = null output
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
  )
as
begin
  set nocount on;
  set @DATALOADED = 0;

  declare @PURPOSEID uniqueidentifier = null;

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

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

  select top 1 @DATALOADED = 1
    ,@TOTALPLEDGEAMOUNT = FT.TRANSACTIONAMOUNT
    ,@BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
    ,@FREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE
    ,@STARTON = REVENUESCHEDULE.STARTDATE
    ,@ENDON = REVENUESCHEDULE.ENDDATE
    ,@TRANSACTIONTYPE = FT.TYPE
    ,@GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY
    ,@NUMBEROFINSTALLMENTS = REVENUESCHEDULE.NUMBEROFINSTALLMENTS
    ,@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 = @PLEDGEID

  select @STARTON = MIN(INSTALLMENT.[DATE])
    ,@ENDON = MAX(INSTALLMENT.[DATE])
  from dbo.INSTALLMENT
  where INSTALLMENT.REVENUEID = @PLEDGEID;

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

  insert into @designationttable (
    ID
    ,USERID
    ,AMOUNT
    ,CURRENCYID
    ,THISPURPOSE
    )
  select SPLITAPPLICATIONINFO.DESIGNATIONID
    ,[USERID]=case when PLEDGE.TYPECODE=15 then isnull(D.USERID,'None (Earned income)') else D.USERID end 
    ,PLEDGELINEITEM.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 PLEDGE with (readuncommitted)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM PLEDGELINEITEM with (readuncommitted) on PLEDGE.ID = PLEDGELINEITEM.FINANCIALTRANSACTIONID
  inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO with (readuncommitted) on PLEDGELINEITEM.ID = SPLITAPPLICATIONINFO.ID
  left join dbo.DESIGNATION D on SPLITAPPLICATIONINFO.DESIGNATIONID = D.ID --left joining because membership installment plans don't necessarily have designations
  where PLEDGE.ID = @PLEDGEID
    and (
      PLEDGE.DELETEDON is null
      and PLEDGELINEITEM.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>');

  with ISP_CTE
  as (
    select ISP.PAYMENTID
      ,SUM(ISP.AMOUNT) AMOUNT
    from dbo.INSTALLMENTSPLITPAYMENT ISP
    where ISP.PLEDGEID = @PLEDGEID
    group by ISP.PAYMENTID
    )
  select @PAYMENTSTOTAL = sum(AMOUNT)
  from dbo.FINANCIALTRANSACTION PAYMENT with (readuncommitted)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT with (readuncommitted) on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
  inner join ISP_CTE ISP with (readuncommitted) on PAYMENTSPLIT.ID = ISP.PAYMENTID
  inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO with (readuncommitted) on PAYMENTSPLIT.ID = SPLITAPPLICATIONINFO.ID
  inner join dbo.DESIGNATION D on SPLITAPPLICATIONINFO.DESIGNATIONID = D.ID
  where (
      PAYMENT.DELETEDON is null
      and PAYMENTSPLIT.DELETEDON is null
      )
    and PAYMENT.TYPECODE = 0
    and (
      D.DESIGNATIONLEVEL1ID = @PURPOSEID
      or D.DESIGNATIONLEVEL2ID = @PURPOSEID
      or D.DESIGNATIONLEVEL3ID = @PURPOSEID
      or D.DESIGNATIONLEVEL4ID = @PURPOSEID
      or D.DESIGNATIONLEVEL5ID = @PURPOSEID
      );

  with ISP_CTE
  as (
    select ISP.PAYMENTID
      ,SUM(ISP.AMOUNT) AMOUNT
    from dbo.INSTALLMENTSPLITPAYMENT ISP
    where ISP.PLEDGEID = @PLEDGEID
    group by ISP.PAYMENTID
    )
  select @PAYMENTS = (
      select top 6 [PAYMENTID] = PAYMENT.ID
        ,[DATE] = cast(PAYMENT.date as datetime)
        ,[GIVENANONYMOUSLY] = REVENUE_EXT.GIVENANONYMOUSLY
        ,[AMOUNT] = SUM(ISP.AMOUNT) --SUM(PAYMENTSPLIT.TRANSACTIONAMOUNT)
        ,[DESIGNATION] = dbo.UDA_BUILDLIST(distinct D.USERID)
        ,[BASECURRENCYID] = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID --PAYMENT.TRANSACTIONCURRENCYID
      from dbo.FINANCIALTRANSACTION PAYMENT with (readuncommitted)
      inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT with (readuncommitted) on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
      inner join ISP_CTE ISP with (readuncommitted) on PAYMENTSPLIT.ID = ISP.PAYMENTID
      inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO with (readuncommitted) on PAYMENTSPLIT.ID = SPLITAPPLICATIONINFO.ID
      inner join dbo.DESIGNATION D on SPLITAPPLICATIONINFO.DESIGNATIONID = D.ID
      inner join dbo.REVENUE_EXT on PAYMENT.ID = REVENUE_EXT.ID
      left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = PAYMENT.PDACCOUNTSYSTEMID
      left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
      where (
          PAYMENT.DELETEDON is null
          and PAYMENTSPLIT.DELETEDON is null
          )
        and PAYMENT.TYPECODE = 0
        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)
        ,REVENUE_EXT.GIVENANONYMOUSLY
        ,isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
      --,PAYMENT.TRANSACTIONCURRENCYID
      order by [DATE] desc
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('PAYMENTS')
        ,binary BASE64
      )

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

  with ISCTE
  as (
    select ISPLIT.REVENUESPLITID
      ,ISWO.WRITEOFFID
      ,ISPLIT.DESIGNATIONID
      ,SUM(ISWO.TRANSACTIONAMOUNT) AMOUNT
    from dbo.INSTALLMENTSPLITWRITEOFF ISWO with (readuncommitted)
    inner join dbo.INSTALLMENTSPLIT ISPLIT with (readuncommitted) on ISWO.INSTALLMENTSPLITID = ISPLIT.ID
    inner join dbo.DESIGNATION D with (readuncommitted) on ISPLIT.DESIGNATIONID = D.ID
    where ISPLIT.PLEDGEID = @PLEDGEID
      and (
        D.DESIGNATIONLEVEL1ID = @PURPOSEID
        or D.DESIGNATIONLEVEL2ID = @PURPOSEID
        or D.DESIGNATIONLEVEL3ID = @PURPOSEID
        or D.DESIGNATIONLEVEL4ID = @PURPOSEID
        or D.DESIGNATIONLEVEL5ID = @PURPOSEID
        )
    group by ISPLIT.REVENUESPLITID
      ,ISWO.WRITEOFFID
      ,ISPLIT.DESIGNATIONID
    )
  select @WRITEOFFSTOTAL = sum(ISCTE.AMOUNT)
  from ISCTE with (readuncommitted)
  inner join dbo.FINANCIALTRANSACTION WRITEOFF with (readuncommitted) on ISCTE.WRITEOFFID = WRITEOFF.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM on ISCTE.REVENUESPLITID = COMMITMENTLINEITEM.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFLINEITEM with (readuncommitted) on WRITEOFF.ID = WRITEOFFLINEITEM.FINANCIALTRANSACTIONID
  inner join dbo.REVENUESPLIT_EXT WRITEOFFAPPINFO with (readuncommitted) on WRITEOFFLINEITEM.ID = WRITEOFFAPPINFO.ID
    and ISCTE.DESIGNATIONID = WRITEOFFAPPINFO.DESIGNATIONID
  where (
      WRITEOFF.DELETEDON is null
      and WRITEOFFLINEITEM.DELETEDON is null
      and COMMITMENTLINEITEM.DELETEDON is null
      )
    and WRITEOFF.TYPECODE = 20;

  with ISCTE
  as (
    select ISPLIT.REVENUESPLITID
      ,ISWO.WRITEOFFID
      ,D.USERID
      ,D.ID DESIGNATIONID
      ,SUM(ISWO.TRANSACTIONAMOUNT) AMOUNT
    from dbo.INSTALLMENTSPLITWRITEOFF ISWO with (readuncommitted)
    inner join dbo.INSTALLMENTSPLIT ISPLIT with (readuncommitted) on ISWO.INSTALLMENTSPLITID = ISPLIT.ID
    inner join dbo.DESIGNATION D with (readuncommitted) on ISPLIT.DESIGNATIONID = D.ID
    where ISPLIT.PLEDGEID = @PLEDGEID
      and (
        D.DESIGNATIONLEVEL1ID = @PURPOSEID
        or D.DESIGNATIONLEVEL2ID = @PURPOSEID
        or D.DESIGNATIONLEVEL3ID = @PURPOSEID
        or D.DESIGNATIONLEVEL4ID = @PURPOSEID
        or D.DESIGNATIONLEVEL5ID = @PURPOSEID
        )
    group by ISPLIT.REVENUESPLITID
      ,ISWO.WRITEOFFID
      ,D.USERID
      ,D.ID
    )
  select @WRITEOFFS = (
      select top 6 [ID] = WRITEOFF.ID
        ,[DATE] = cast(WRITEOFF.[DATE] as datetime)
        ,[AMOUNT] = sum(ISCTE.AMOUNT)
        ,[DESIGNATION] = dbo.UDA_BUILDLIST(ISCTE.USERID)
        ,[BASECURRENCYID] = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID
      from ISCTE with (readuncommitted)
      inner join dbo.FINANCIALTRANSACTION WRITEOFF with (readuncommitted) on ISCTE.WRITEOFFID = WRITEOFF.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM on ISCTE.REVENUESPLITID = COMMITMENTLINEITEM.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFLINEITEM with (readuncommitted) on WRITEOFF.ID = WRITEOFFLINEITEM.FINANCIALTRANSACTIONID
      inner join dbo.REVENUESPLIT_EXT WRITEOFFAPPINFO with (readuncommitted) on WRITEOFFLINEITEM.ID = WRITEOFFAPPINFO.ID
        and ISCTE.DESIGNATIONID = WRITEOFFAPPINFO.DESIGNATIONID
      left join dbo.REVENUE_EXT on WRITEOFF.ID = REVENUE_EXT.ID
      left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = WRITEOFF.PDACCOUNTSYSTEMID
      left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
      where (
          WRITEOFF.DELETEDON is null
          and WRITEOFFLINEITEM.DELETEDON is null
          and COMMITMENTLINEITEM.DELETEDON is null
          )
        and WRITEOFF.TYPECODE = 20
      group by WRITEOFF.ID
        ,cast(WRITEOFF.[DATE] as datetime)
        ,isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
      order by [DATE] desc
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('WRITEOFFS')
        ,binary BASE64
      )

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

  return 0;
end