USP_DATALIST_COMMITMENTSFOREVENT

CREATE procedure dbo.USP_DATALIST_COMMITMENTSFOREVENT
(
  @EVENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @KEYNAME nvarchar(200) = null,
  @FIRSTNAME nvarchar(200) = null,
  @DATEFROM date = null,
  @DATETO date = null,
  @TYPECODE tinyint = null
)
as
    set nocount on;

  declare @RETURNTABLE as table(
    ID uniqueidentifier,
    APPLICATIONTYPE nvarchar(30),
    DESCRIPTION nvarchar(700),
    RECOGNITION nvarchar(160),
    DATE datetime,
    AMOUNT money,
    APPLICATIONTYPECODE tinyint,
    BALANCE money,
    PAID money
  )

  declare @ISADMIN bit;
  declare @APPUSER_IN_NONRACROLE bit;
  declare @APPUSER_IN_NOSECGROUPROLE bit;
  declare @PDACCOUNTSYSTEMID uniqueidentifier = null;

  set @KEYNAME = coalesce(@KEYNAME, '') + '%'
  set @FIRSTNAME = coalesce(@FIRSTNAME, '') + '%'

  if dbo.UFN_VALID_BASICGL_INSTALLED() != 0
  begin
    declare    @NUMBEROFACCOUNTSYSTEMSFORUSER smallint
    set @NUMBEROFACCOUNTSYSTEMSFORUSER = dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID)
    if @NUMBEROFACCOUNTSYSTEMSFORUSER = 1
      select @PDACCOUNTSYSTEMID =  T1.ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) as T1
    else
      set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)
  end

  if @PDACCOUNTSYSTEMID is null or @PDACCOUNTSYSTEMID = '00000000-0000-0000-0000-000000000000'
    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
  set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
  set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

   declare @CURRENTDATE datetime;
  set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  --Pledge = 2

  if isnull(@TYPECODE,1) = 1
  begin
      insert into @RETURNTABLE (ID, APPLICATIONTYPE, DESCRIPTION, RECOGNITION, DATE, AMOUNT, APPLICATIONTYPECODE, BALANCE, PAID)
      select 
          REV.ID, REV.TYPE, 
          coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' pledge' 
          + case when VBAL.BALANCE <> REV.TRANSACTIONAMOUNT then ' (' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast((REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as nvarchar(20)) + ' received)' else '' end  + ' from ' + NF.NAME,
          EM.NAME, REV.DATE, REV.TRANSACTIONAMOUNT, 2, VBAL.BALANCE, (REV.TRANSACTIONAMOUNT - VBAL.BALANCE)
      from dbo.FINANCIALTRANSACTION (nolock) REV
        join dbo.REVENUE_EXT (nolock) REVX on REV.ID = REVX.ID 
        join dbo.EVENT (nolock) E on E.APPEALID = REVX.APPEALID and E.ID =  @EVENTID
        join dbo.FINANCIALTRANSACTIONLINEITEM (nolock) RS on RS.FINANCIALTRANSACTIONID = REV.ID
        inner join dbo.REVENUESCHEDULE (nolock)on REVENUESCHEDULE.ID = REV.ID
        left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
        left join dbo.CONSTITUENT EM (NOLOCK) ON EM.ID = RR.CONSTITUENTID
        left join dbo.PDACCOUNTSYSTEMFORREVENUE (nolock) on REV.ID = PDACCOUNTSYSTEMFORREVENUE.ID 
        left join V_QUERY_REVENUE_PLEDGEBALANCE (nolock) VBAL on VBAL.ID = REV.ID
        left join dbo.CONSTITUENT NF (NOLOCK) ON NF.ID = REV.CONSTITUENTID
        left join dbo.INSTALLMENT (nolock) NEXTINSTALLMENT on NEXTINSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REV.ID)
        left join dbo.CURRENCY (nolock) on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
      where REV.TYPECODE = 1
          and VBAL.BALANCE > 0
          and (PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID or dbo.UFN_VALID_BASICGL_INSTALLED() = 0)
          and (@DATEFROM is null or REV.[DATE] >= @DATEFROM )
          and (@DATETO is null or REV.[DATE] <= @DATETO)
          and (EM.FIRSTNAME like @FIRSTNAME or NF.FIRSTNAME like @FIRSTNAME) 
          and (EM.KEYNAME like @KEYNAME or NF.KEYNAME like @KEYNAME)
  end

  --Recurring Gift = 3

    if isnull(@TYPECODE,2) = 2
  begin
    insert into @RETURNTABLE (ID, APPLICATIONTYPE, DESCRIPTION, RECOGNITION, DATE, AMOUNT, APPLICATIONTYPECODE, BALANCE, PAID)
      select 
          REV.ID, REV.TYPE, 
          coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' recurring gift ' + ' from ' + NF.NAME,
          EM.NAME, REV.DATE, REV.TRANSACTIONAMOUNT, 3, REV.TRANSACTIONAMOUNT as BALANCE, 0 as PAID
      from dbo.FINANCIALTRANSACTION (nolock) REV
        join dbo.REVENUE_EXT (nolock) REVX on REV.ID = REVX.ID 
        join dbo.EVENT (nolock) E on E.APPEALID = REVX.APPEALID and E.ID =  @EVENTID
        join dbo.FINANCIALTRANSACTIONLINEITEM (nolock) RS on RS.FINANCIALTRANSACTIONID = REV.ID 
        inner join dbo.REVENUESCHEDULE (nolock) RSH on REV.ID = RSH.ID
        left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
        left join dbo.CURRENCY on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
        left join dbo.CONSTITUENT EM (NOLOCK) ON EM.ID = RR.CONSTITUENTID
        left join dbo.CONSTITUENT NF (NOLOCK) ON NF.ID = REV.CONSTITUENTID
      where REV.TYPECODE = 2
          and RSH.STATUSCODE = 0        --Active

          and RSH.ISPENDING = 0        --Isn't pending

          and REV.TRANSACTIONAMOUNT > 0                    --Has Value???

          and dbo.UFN_RECURRINGGIFT_ISMEMBERSHIP(REV.ID) = 0
          and (@DATEFROM is null or REV.[DATE] >= @DATEFROM )
          and (@DATETO is null or REV.[DATE] <= @DATETO)    
          and (EM.FIRSTNAME like @FIRSTNAME or NF.FIRSTNAME like @FIRSTNAME) 
          and (EM.KEYNAME like @KEYNAME or NF.KEYNAME like @KEYNAME)
  end

  --Matching gift claim = 7

  if isnull(@TYPECODE,3) = 3
  begin
      insert into @RETURNTABLE (ID, APPLICATIONTYPE, DESCRIPTION, RECOGNITION, DATE, AMOUNT, APPLICATIONTYPECODE, BALANCE, PAID)
      select 
        REV.ID, 
        REV.TYPE, 
        case when EM.NAME is null then
          coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(15)) + ' matching gift claim for '
           + case when BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast(PAID as nvarchar(15)) + ' received) ' else '' end 
           + NF.NAME + ' from ' + REVENUEWITHBALANCE.fromName
        else
          coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(15)) + ' matching gift claim for ' + 
          case when BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast(PAID as nvarchar(15)) + ' received) ' else '' end
           + NF.NAME + ' from ' + REVENUEWITHBALANCE.fromName + ' to ' + EM.NAME
        END,
        NF.NAME, REV.DATE, REV.TRANSACTIONAMOUNT, 7, BALANCE, (REV.TRANSACTIONAMOUNT - BALANCE)
      from
      (
          select ID, TYPECODE, PAID, cast(TRANSACTIONAMOUNT - coalesce(PAID, 0) as money) as BALANCE, fromName, FIRSTNAME, KEYNAME
          from
          (
            select
                FINANCIALTRANSACTION.ID,        
                FINANCIALTRANSACTION.TYPECODE,
                FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                (
                  select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                  from dbo.INSTALLMENTSPLITPAYMENT
                  where INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
                ) as PAID,
                fromNF.NAME as fromName,
                fromNF.KEYNAME,
                fromNF.FIRSTNAME
            from dbo.FINANCIALTRANSACTION (nolock)
              inner join dbo.REVENUEMATCHINGGIFT (nolock) on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
              inner join dbo.FINANCIALTRANSACTION fromR (nolock) on fromR.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
              join dbo.CONSTITUENT (nolock) fromNF on fromNF.ID = fromR.CONSTITUENTID
            where REVENUEMATCHINGGIFT.ISACTIVE = 1        --Check if status for Revenue matching gift is Active    

          ) as T
      ) as REVENUEWITHBALANCE
        inner join dbo.FINANCIALTRANSACTION REV (nolock) on REV.ID = REVENUEWITHBALANCE.ID
        inner join dbo.REVENUE_EXT REVX (nolock) on REVX.ID = REV.ID
        inner join dbo.EVENT E (nolock) on E.APPEALID = REVX.APPEALID and E.ID = @EVENTID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM RS (nolock) on RS.FINANCIALTRANSACTIONID = REV.ID
        left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
        left join dbo.CONSTITUENT EM (NOLOCK) ON EM.ID = RR.CONSTITUENTID
        left join dbo.CONSTITUENT NF (NOLOCK) ON NF.ID = REV.CONSTITUENTID
        left join dbo.CURRENCY (nolock) on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
      where REVENUEWITHBALANCE.TYPECODE = 3
        and REVENUEWITHBALANCE.BALANCE > 0      
        and (@DATEFROM is null or REV.[DATE] >= @DATEFROM )
        and (@DATETO is null or REV.[DATE] <= @DATETO)                
        and (EM.FIRSTNAME like @FIRSTNAME or NF.FIRSTNAME like @FIRSTNAME or REVENUEWITHBALANCE.FIRSTNAME like @FIRSTNAME)
        and (EM.KEYNAME like @KEYNAME or NF.KEYNAME like @KEYNAME or REVENUEWITHBALANCE.KEYNAME like @KEYNAME)

  end


  --Pending gift = 17

  if isnull(@TYPECODE,9) = 9
  begin
      insert into @RETURNTABLE (ID, APPLICATIONTYPE, DESCRIPTION, RECOGNITION, DATE, AMOUNT, APPLICATIONTYPECODE, BALANCE, PAID)
      select 
          REV.ID, 
          REV.TYPE, 
          case when RPM.PAYMENTMETHODCODE is null or RPM.PAYMENTMETHODCODE = 9 THEN
      coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' pending gift ' 
        + case when VBAL.BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast((REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as nvarchar(15)) + ' received) ' else '' end 
        + ' from ' + NF.NAME
    else
      case when len(coalesce(CPM.CHECKNUMBER, REVENUEOFFLINEDONATION.CHECKNUMBER, '')) > 0 then
        coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' pending gift ' 
    + case when VBAL.BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast((REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as nvarchar(15)) + ' received) ' else '' end 
       + ' from ' + NF.NAME + ' (' + RPM.PAYMENTMETHOD + ' #' + coalesce(CPM.CHECKNUMBER, REVENUEOFFLINEDONATION.CHECKNUMBER, '') + ')'
      else
        coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' pending gift ' 
    + case when VBAL.BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast((REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as nvarchar(15)) + ' received) ' else '' end 
       + ' from ' + NF.NAME + ' (' + RPM.PAYMENTMETHOD + ')'
      end
    end as DESCRIPTION, 
          EM.NAME, REV.DATE, REV.TRANSACTIONAMOUNT, 17, VBAL.BALANCE, (REV.TRANSACTIONAMOUNT - VBAL.BALANCE)
      from dbo.FINANCIALTRANSACTION REV (nolock) 
        inner join dbo.REVENUE_EXT REVX (nolock) on REVX.ID = REV.ID
        inner join dbo.EVENT E (nolock) on E.APPEALID = REVX.APPEALID and E.ID = @EVENTID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM RS (nolock) on RS.FINANCIALTRANSACTIONID = REV.ID
    left join dbo.REVENUEPAYMENTMETHOD RPM With (NOLOCK) on RPM.REVENUEID = REV.ID 
    left join dbo.CHECKPAYMENTMETHODDETAIL CPM with (nolock) on CPM.ID = RPM.ID
    left join dbo.REVENUEOFFLINEDONATION (nolock) on REVENUEOFFLINEDONATION.ID = REV.ID
        left join dbo.CONSTITUENT NF (NOLOCK) ON NF.ID = REV.CONSTITUENTID
        left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
        left join dbo.CONSTITUENT EM (NOLOCK) ON EM.ID = RR.CONSTITUENTID
        left join V_QUERY_REVENUE_PLEDGEBALANCE VBAL on VBAL.ID = [REV].ID
        left join dbo.CURRENCY (nolock) on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
      where [REV].TYPECODE = 9
          and VBAL.BALANCE > 0
          and (@DATEFROM is null or REV.[DATE] >= @DATEFROM )
          and (@DATETO is null or REV.[DATE] <= @DATETO)
          and (EM.FIRSTNAME like @FIRSTNAME or NF.FIRSTNAME like @FIRSTNAME)
          and (EM.KEYNAME like @KEYNAME or NF.KEYNAME like @KEYNAME)
  end 


  select ID, APPLICATIONTYPE, DESCRIPTION, RECOGNITION, DATE, AMOUNT, APPLICATIONTYPECODE, BALANCE, PAID
  from @RETURNTABLE
  where
    exists(--Check site security

      select 1
      from dbo.UFN_SITEID_MAPFROM_REVENUEID(ID) as SITE
      where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
    )
  order by RECOGNITION