USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONAUCTIONDONATIONDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONAUCTIONDONATIONDETAIL
(
  @ID nvarchar(73)
  ,@DATALOADED bit = 0 output
  ,@AUCTIONID uniqueidentifier = null output
  ,@GIVENANONYMOUSLY bit = null output
  ,@TOTALAUCTIONAMOUNT 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
  ,@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 @AUCTIONID = cast(substring(@ID, 0, 37) as uniqueidentifier)

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

  select @DATALOADED = 1
    ,@TOTALAUCTIONAMOUNT = FT.TRANSACTIONAMOUNT
    ,@BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
    ,@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
  left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FT.PDACCOUNTSYSTEMID
  left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
  where FT.ID = @AUCTIONID

  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
  where FT.ID = @AUCTIONID
    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 @WRITEOFFSTOTAL = (
      select sum(FTLI.TRANSACTIONAMOUNT) AMOUNT
      from dbo.FINANCIALTRANSACTION FT
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID
      inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
      inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
      where FT.PARENTID = @AUCTIONID
        and FT.TYPECODE = 20 -- Write off
        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 @WRITEOFFS = (
      select top 6 FT.ID
        ,cast(FT.[DATE] as datetime) as date
        ,FTLI.TRANSACTIONAMOUNT AMOUNT
        ,DESIGNATION.USERID DESIGNATION
        ,isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) BASECURRENCYID
        ,@TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID
      from dbo.FINANCIALTRANSACTION FT
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID
      inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
      inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
      left 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.PARENTID = @AUCTIONID
        and FT.TYPECODE = 20 -- Write off
        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 FT.[DATE] desc
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('WRITEOFFS')
        ,binary BASE64
      )
  set @WRITEOFFS = isnull(@WRITEOFFS, '<WRITEOFFS></WRITEOFFS>')

  return 0;
end