USP_DATALIST_COMMITMENTSFORDONOR

CREATE procedure dbo.USP_DATALIST_COMMITMENTSFORDONOR
(
  @CONSTITUENTID uniqueidentifier,
  @EVENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE 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;

  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 @CONSTITUENTS as table(
    ID uniqueidentifier
  );

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

  declare @HOUSEHOLDSCANBEDONORS bit;
  set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();

  declare @HOUSEHOLDID uniqueidentifier;
  select top(1) @HOUSEHOLDID = GM.GROUPID
  from dbo.GROUPMEMBER as GM
    left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
  where GM.MEMBERID = @CONSTITUENTID
    and GD.GROUPTYPECODE = 0
    and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
    and @HOUSEHOLDSCANBEDONORS = 1;

  insert into @CONSTITUENTS(ID)
  select CONSTITUENTS.ID
  from (
    select @HOUSEHOLDID as ID

    union

    select
      case 
        when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1 then
          case 
            when @HOUSEHOLDSCANBEDONORS = 1 then @CONSTITUENTID
            else null
          end
        else @CONSTITUENTID
      end

    union

    select GM.MEMBERID
    from dbo.GROUPMEMBER GM
      left outer join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
    where(
        GM.GROUPID = @HOUSEHOLDID
        or GM.GROUPID = @CONSTITUENTID
      ) and (
        (GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE))
        or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE)
      )
    ) CONSTITUENTS
  where @ISADMIN = 1
    or ( --If form check by form

      @SECURITYFEATURETYPE = 1
      and dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYFEATUREID, CONSTITUENTS.ID) = 1
    )
    or ( --If datalist check by datalist

      @SECURITYFEATURETYPE = 2
      and dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYFEATUREID, CONSTITUENTS.ID) = 1
    )
    or ( --If otherwise check all roles

      @SECURITYFEATURETYPE not in(1, 2)
      and (
        @APPUSER_IN_NONRACROLE = 1 
        or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENTS.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
      )
    );

  --Pledge = 2

  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 @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REV.CONSTITUENTID
    inner join dbo.REVENUESCHEDULE (nolock)on REVENUESCHEDULE.ID = REV.ID
    left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RR.CONSTITUENTID) EM
    left join dbo.PDACCOUNTSYSTEMFORREVENUE (nolock) on REV.ID = PDACCOUNTSYSTEMFORREVENUE.ID 
    left join V_QUERY_REVENUE_PLEDGEBALANCE (nolock) VBAL on VBAL.ID = REV.ID
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REV.CONSTITUENTID) NF
    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)

  --Recurring Gift = 3

    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
    inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REV.CONSTITUENTID
    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
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RR.CONSTITUENTID) EM
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REV.CONSTITUENTID) NF
  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

  --Matching gift claim = 7

  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
    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
      from dbo.FINANCIALTRANSACTION (nolock)
        inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
        inner join dbo.REVENUEMATCHINGGIFT (nolock) on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
        inner join dbo.FINANCIALTRANSACTION fromR (nolock) on fromR.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(fromR.CONSTITUENTID) fromNF
      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 
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RR.CONSTITUENTID) EM
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REV.CONSTITUENTID) NF
    left join dbo.CURRENCY (nolock) on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
  where REVENUEWITHBALANCE.TYPECODE = 3
    and REVENUEWITHBALANCE.BALANCE > 0

  --Subsidiary Matching gift Claims = 7

  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
    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
      from dbo.FINANCIALTRANSACTION
        inner join dbo.ORGANIZATIONDATA (nolock) on ORGANIZATIONDATA.ID = FINANCIALTRANSACTION.CONSTITUENTID
        inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = ORGANIZATIONDATA.PARENTCORPID
        inner join dbo.REVENUEMATCHINGGIFT (nolock) on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
        inner join dbo.FINANCIALTRANSACTION fromR (nolock) on fromR.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(fromR.CONSTITUENTID) fromNF
      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 
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RR.CONSTITUENTID) EM
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REV.CONSTITUENTID) NF
    left join dbo.CURRENCY (nolock) on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
  where REVENUEWITHBALANCE.TYPECODE = 3
    and REVENUEWITHBALANCE.BALANCE > 0

  --Pending gift = 17

  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
    inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REV.CONSTITUENTID
    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
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REV.CONSTITUENTID) NF
    left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RR.CONSTITUENTID) EM
    left join V_QUERY_REVENUE_PLEDGEBALANCE VBAL on VBAL.ID = [REV].ID
    left join dbo.CURRENCY on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
  where [REV].TYPECODE = 9
    and VBAL.BALANCE > 0


  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