SPTRANSACTIONS_GETALLUNPROCESSEDTRANSACTIONS_SIGNUPANDACQUISITION

CREATE procedure dbo.SPTRANSACTIONS_GETALLUNPROCESSEDTRANSACTIONS_SIGNUPANDACQUISITION
(
            @BeginDownload datetime output,
            @DownloadUserID int,
            @TransactionType nvarchar(100),
      @IncludeTransactiontypeCode int
)
as
begin
        SET @BeginDownload = getutcdate()
          Create table #UnprocessedSignUpAndAcquisitionTransactionIDs
          (
          SignupTransactionsID int,
          SimpleEmailSignUp bit
          )
          insert into #UnprocessedSignUpAndAcquisitionTransactionIDs
          SELECT SignupTransactionsID, SimpleEmailSignUp 
              FROM SignupTransactions 
              WHERE SimpleEmailSignUp = ( case when @IncludeTransactiontypeCode=1  then 0 when @IncludeTransactiontypeCode=2 then 1 else SimpleEmailSignUp end)
              AND processed_date is Null

            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 
                TransactionType = @TransactionType 
                AND
                ID not in (select BBNCTRANID from BBNCTRANSACTIONDOWNLOADEXCEPTION)
                AND
                ((BeginDownload IS NULL AND EndDownload IS NULL) 
                OR 
                (BeginDownload IS NOT NULL AND EndDownload IS NULL AND DownloadUserID > 0)) 
        AND
        TransactionID IN (SELECT SignupTransactionsID FROM #UnprocessedSignUpAndAcquisitionTransactionIDs)
            end
              else
            --This is a NetCommunity Service Request

            begin

                UPDATE dbo.Transactions 
                SET BeginDownload = @BeginDownload, DownloadUserID = @DownloadUserID
                WHERE 
                TransactionType = @TransactionType 
                AND
                ID not in (select BBNCTRANID from BBNCTRANSACTIONDOWNLOADEXCEPTION)
                AND
                ((BeginDownload IS NULL AND EndDownload IS NULL) 
                OR 
                (BeginDownload IS NOT NULL AND EndDownload IS NULL AND DownloadUserID = 0)) 
        AND
        TransactionID IN (SELECT SignupTransactionsID FROM #UnprocessedSignUpAndAcquisitionTransactionIDs)

            end


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

            if @TransactionType = '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}' /*Signups*/
            begin
                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
                WHERE 
                T.TransactionType = @TransactionType AND
                T.BeginDownload = @BeginDownload AND 
                T.EndDownload IS NULL AND 
                T.DownloadUserID = @DownloadUserID and
                ST.processed_date is null
            end
      else
            begin
                SELECT 
                ID, TransactionID, TransactionType, Data, BBNCUserID, BackOfficeID 
                FROM dbo.Transactions 
                WHERE 
                TransactionType = @TransactionType AND
                BeginDownload = @BeginDownload AND 
                EndDownload IS NULL AND 
                DownloadUserID = @DownloadUserID
            end
end