USP_BBIS_GETUNPROCESSEDTRANSACTIONCOUNTS

CREATE procedure dbo.USP_BBIS_GETUNPROCESSEDTRANSACTIONCOUNTS
as begin
  set nocount on;

    declare @CLIENTID int = 1;

    with
    UNPROCESSEDTRANSACTIONS_CTE as (
        --Donation Transactions

        select
            '{5705B543-4033-4a3a-BBCD-A731403EE1E6}' as TRANSACTIONTYPE,
            DT.DonationTransactionsID as TRANSACTIONID,0 as ISACQUISTION
        from
            dbo.fnTransactions_GetUnprocessedGiftsForClient(@CLIENTID) as DT

        union all

        --Event Transactions

        select
            '{172A5D9A-5241-493C-A2C2-EAC164C095B0}' as TRANSACTIONTYPE,
            ET.EventTransactionsID as TRANSACTIONID,0 as ISACQUISTION
        from
            dbo.fnTransactions_GetUnprocessedEventRegsForClient(@CLIENTID) as ET

        union all

        --Membership Transactions

        select
            '{D7D6143F-823D-4c74-AC2F-947CC96B7008}' as TRANSACTIONTYPE,
            MT.ID,0 as ISACQUISTION
        from
            dbo.MembershipTransactions MT
        inner join
            dbo.ClientMemberships CM on MT.ClientMembershipsID = CM.ID
        inner join
            dbo.SiteContent SC on CM.SiteContentID = SC.ID
        inner join
            dbo.ClientSites CS on SC.ClientSitesID = CS.ID
        where
            MT.processed_date is null and
            MT.IsDeleted=0 and
            MT.Status = 1 and
            CS.ClientsID = @CLIENTID 

        union all

        --Signup Transactions

        select
            '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}' as TRANSACTIONTYPE,
            ST.SignupTransactionsID, ST.SimpleEmailSignUp  as ISACQUISTION
        from
            dbo.SignupTransactions ST
        where
            ST.processed_date is null and
            ST.ClientsID = @CLIENTID and
            ST.IsDeleted = 0 


        union all

        --Profile Update Transactions

        select
            '{DCEAFCC7-0290-488e-A194-953DE66D6AAB}' as TRANSACTIONTYPE,
            PUT.ProfileUpdateTransactionsID as TRANSACTIONID,0 as ISACQUISTION
        from
            dbo.ProfileUpdateTransactions PUT 
        where
            processed_date is null and
            ClientsID = @CLIENTID  and
            IsDeleted=0 And
            Not BackOfficeID is null and
            --filter out dupe updates from count so BBEC UI shows correct initial count

            not exists(
                SELECT
                    null
                from
                    dbo.ProfileUpdateTransactions PUT2
                where
                    PUT.BackOfficeID = PUT2.BackOfficeID and
                    PUT2.ProfileUpdateTransactionsID > PUT.ProfileUpdateTransactionsID
            ) and
             dbo.UFN_BBNC_GETPROFILEUPDATECOUNTBYBACKOFFICEID(PUT.BackOfficeID) = 0
    ),

    UNPROCESSEDTRANSACTIONCOUNTS_CTE as (
        --Except Signup and Acquisition Transactions

        select
            UPT.TRANSACTIONTYPE,
            count(T.ID) as TRANSACTIONCOUNT,
            UPT.ISACQUISTION
        from
            UNPROCESSEDTRANSACTIONS_CTE UPT
        inner join
            dbo.Transactions T on T.[TransactionType] = UPT.[TRANSACTIONTYPE] and T.[TransactionID] = UPT.[TRANSACTIONID]
        left join
            dbo.BBNCDOWNLOADEDTRANSACTION DT on DT.[ID] = T.[ID]
        where
            DT.[ID] is null and UPT.[TRANSACTIONTYPE] <> '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}'
        group by
            UPT.TRANSACTIONTYPE,UPT.ISACQUISTION

        union all
        --Signup and Acquisition Transactions

        select
            '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}' as TRANSACTIONTYPE,
            count(T.ID) as TRANSACTIONCOUNT,
            UPT.ISACQUISTION
        from
            UNPROCESSEDTRANSACTIONS_CTE UPT
        inner join
            dbo.Transactions T on T.[TransactionType] = UPT.[TRANSACTIONTYPE] and T.[TransactionID] = UPT.[TRANSACTIONID]
        left join
            dbo.BBNCDOWNLOADEDTRANSACTION DT on DT.[ID] = T.[ID]
        where
            DT.[ID] is null and UPT.[TRANSACTIONTYPE] = '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}'
        group by
            UPT.ISACQUISTION
    ),

    UNPROCESSEDTRANSACTIONCOUNTSDISPLAY_CTE as (

    select 
         distinct TP.[ID] as ID, 
        TP.[NAME] as NAME,
        coalesce(UPTC.TRANSACTIONCOUNT,0) as TRANSACTIONCOUNT
    from
        dbo.NETCOMMUNITYTRANSACTIONPROCESSOR TP
        left join UNPROCESSEDTRANSACTIONCOUNTS_CTE UPTC on UPTC.TRANSACTIONTYPE = TP.TRANSACTIONTYPE
        left join dbo.NETCOMMUNITYBATCHPROCESSORSPLITMAPPING as MAPPING on TP.ID = MAPPING.NETCOMMUNITYTRANSACTIONPROCESSORID
        where MAPPING.ID is null and tp.TRANSACTIONTYPE <> '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}'

        union all 

        select 
         distinct TP.[ID] as ID, 
         TP.[NAME] as NAME,
         coalesce(UPTC.TRANSACTIONCOUNT,0) as TRANSACTIONCOUNT
    from
        dbo.NETCOMMUNITYTRANSACTIONPROCESSOR TP
        inner join dbo.NETCOMMUNITYMAPPINGRULE as MAPPINGRULE on TP.id=MAPPINGRULE.NETCOMMUNITYTRANSACTIONPROCESSORID and MAPPINGRULE.MAPPINGTYPECODE = 0 
        left join UNPROCESSEDTRANSACTIONCOUNTS_CTE UPTC on UPTC.TRANSACTIONTYPE = TP.TRANSACTIONTYPE and uptc.ISACQUISTION=0
        left join dbo.NETCOMMUNITYBATCHPROCESSORSPLITMAPPING as MAPPING on TP.ID = MAPPING.NETCOMMUNITYTRANSACTIONPROCESSORID
        where MAPPING.ID is null and tp.TRANSACTIONTYPE = '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}'
        and (MAPPINGRULE.INCLUDETRANSACTIONTYPECODE =1)

        union all 

        select 
         distinct TP.[ID] as ID, 
        TP.[NAME] as NAME,
        coalesce(UPTC.TRANSACTIONCOUNT,0) as TRANSACTIONCOUNT
    from
        dbo.NETCOMMUNITYTRANSACTIONPROCESSOR TP
        inner join dbo.NETCOMMUNITYMAPPINGRULE as MAPPINGRULE on TP.id=MAPPINGRULE.NETCOMMUNITYTRANSACTIONPROCESSORID and MAPPINGRULE.MAPPINGTYPECODE = 0
        left join UNPROCESSEDTRANSACTIONCOUNTS_CTE UPTC on UPTC.TRANSACTIONTYPE = TP.TRANSACTIONTYPE and uptc.ISACQUISTION=1
        left join dbo.NETCOMMUNITYBATCHPROCESSORSPLITMAPPING as MAPPING on TP.ID = MAPPING.NETCOMMUNITYTRANSACTIONPROCESSORID
        where MAPPING.ID is null and tp.TRANSACTIONTYPE = '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}'
        and (MAPPINGRULE.INCLUDETRANSACTIONTYPECODE =2)

        union all 

        select 
         distinct TP.[ID] as ID, 
         TP.[NAME] as NAME,
         coalesce(sum(UPTC.TRANSACTIONCOUNT),0) as TRANSACTIONCOUNT
    from
        dbo.NETCOMMUNITYTRANSACTIONPROCESSOR TP
        inner join dbo.NETCOMMUNITYMAPPINGRULE as MAPPINGRULE on TP.id=MAPPINGRULE.NETCOMMUNITYTRANSACTIONPROCESSORID and MAPPINGRULE.MAPPINGTYPECODE = 0 
        left join UNPROCESSEDTRANSACTIONCOUNTS_CTE UPTC on UPTC.TRANSACTIONTYPE = TP.TRANSACTIONTYPE
        left join dbo.NETCOMMUNITYBATCHPROCESSORSPLITMAPPING as MAPPING on TP.ID = MAPPING.NETCOMMUNITYTRANSACTIONPROCESSORID
        where MAPPING.ID is null and tp.TRANSACTIONTYPE = '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}'
        and (MAPPINGRULE.INCLUDETRANSACTIONTYPECODE=3)
    group by TP.[ID],TP.[NAME]
        )
        select ID,NAME,Max(TRANSACTIONCOUNT) as TRANSACTIONCOUNT from UNPROCESSEDTRANSACTIONCOUNTSDISPLAY_CTE
        group by ID,NAME
        order by NAME
end