spTransactions_GetAllUnprocessedTransactions

CREATE procedure dbo.spTransactions_GetAllUnprocessedTransactions
(
@BeginDownload datetime output,
@DownloadUserID int
)
as
begin
      SET @BeginDownload = getutcdate()

            if (@DownloadUserID > 0)
            --This is an RE User download Request

            --The reason we do > 0 and not DownloadUserID = @DownloadUserID

            --is because we want all users in RE to be able to grab trans

            --not just the user that began the process. But this will run 

            --separate from the NetCommunity Service which is userid = 0

            begin
                UPDATE dbo.Transactions 
                SET BeginDownload = @BeginDownload, DownloadUserID = @DownloadUserID 
                WHERE 
                (BeginDownload IS NULL AND EndDownload IS NULL) 
                OR 
                (BeginDownload IS NOT NULL AND EndDownload IS NULL AND DownloadUserID > 0) 
                AND
                ID not in (select BBNCTRANID from BBNCTRANSACTIONDOWNLOADEXCEPTION)
            end
              else
            --This is a NetCommunity Service Request

            begin

                UPDATE dbo.Transactions 
                SET BeginDownload = @BeginDownload, DownloadUserID = @DownloadUserID
                WHERE 
                (BeginDownload IS NULL AND EndDownload IS NULL) 
                OR 
                (BeginDownload IS NOT NULL AND EndDownload IS NULL AND DownloadUserID = 0) 
                AND
                ID not in (select BBNCTRANID from BBNCTRANSACTIONDOWNLOADEXCEPTION)
            end


      /*Hacky way to make sure we do not try to download already committed transactions*/

   SELECT
      T.ID, T.TransactionID, T.TransactionType, T.Data, T.BBNCUserID, T.BackOfficeID
     FROM dbo.Transactions T
       inner join dbo.SignupTransactions ST on T.TransactionID = ST.SignupTransactionsID and T.TransactionType = '{5F84002B-ABB1-4f50-A244-D4B14FBB1579}'
    WHERE
      T.BeginDownload = @BeginDownload AND
      T.EndDownload IS NULL AND
      T.DownloadUserID = @DownloadUserID and
      ST.processed_date is null

    UNION ALL

    SELECT
       T.ID, T.TransactionID, T.TransactionType, T.Data, T.BBNCUserID, T.BackOfficeID
      FROM dbo.Transactions T
        inner join dbo.ProfileUpdateTransactions PUT on T.TransactionID = PUT.ProfileUpdateTransactionsID and T.TransactionType = '{DCEAFCC7-0290-488E-A194-953DE66D6AAB}' 
     WHERE
       T.BeginDownload = @BeginDownload AND
       T.EndDownload IS NULL AND
       T.DownloadUserID = @DownloadUserID and
       PUT.processed_date is null

    UNION ALL

    SELECT
       T.ID, T.TransactionID, T.TransactionType, T.Data, T.BBNCUserID, T.BackOfficeID
      FROM dbo.Transactions T
        inner join dbo.EventTransactions ERT on T.TransactionID = ERT.EventTransactionsID and T.TransactionType = '{172A5D9A-5241-493C-A2C2-EAC164C095B0}'
     WHERE
       T.BeginDownload = @BeginDownload AND
       T.EndDownload IS NULL AND
       T.DownloadUserID = @DownloadUserID and
       ERT.processed_date is null

    UNION ALL

    SELECT
       T.ID, T.TransactionID, T.TransactionType, T.Data, T.BBNCUserID, T.BackOfficeID
      FROM dbo.Transactions T
        inner join dbo.DonationTransactions DT on T.TransactionID = DT.DonationTransactionsID and T.TransactionType = '{5705B543-4033-4a3a-BBCD-A731403EE1E6}'
     WHERE
       T.BeginDownload = @BeginDownload AND
       T.EndDownload IS NULL AND
       T.DownloadUserID = @DownloadUserID and
       DT.processed_date is null

    UNION ALL

    SELECT
       T.ID, T.TransactionID, T.TransactionType, T.Data, T.BBNCUserID, T.BackOfficeID
      FROM dbo.Transactions T
        inner join dbo.MembershipTransactions MT on T.TransactionID = MT.ID and T.TransactionType = '{D7D6143F-823D-4c74-AC2F-947CC96B7008}'
     WHERE
       T.BeginDownload = @BeginDownload AND
       T.EndDownload IS NULL AND
       T.DownloadUserID = @DownloadUserID and
       MT.processed_date is null

    UNION ALL

    SELECT
       T.ID, T.TransactionID, T.TransactionType, T.Data, T.BBNCUserID, T.BackOfficeID
      FROM dbo.Transactions T
        inner join dbo.VolunteerTransactions VT on T.TransactionID = VT.ID and T.TransactionType = '{02B000B6-179F-4c0f-A2DE-6856DCD5D964}'
     WHERE
       T.BeginDownload = @BeginDownload AND
       T.EndDownload IS NULL AND
       T.DownloadUserID = @DownloadUserID and
       VT.ProcessedDate is null


end