November 14, 2014 at 2:47 pm
DECLARE expiredRegs CURSOR STATIC FOR
SELECT DISTINCT register_ID, sessionID, event_ID
FROM registrations
WHERE sessionID IS NOT NULL
AND sessionID NOT IN ( SELECT ID FROM #Sessions )
AND (statusId <> @PaymentPendingStatusId)
AND not exists (
select RegisterID
from transactions
where registerID = register_id
)
FOR READ ONLY
OPEN expiredRegs
FETCH NEXT FROM expiredRegs INTO @regID, @sessionID, @eventID
WHILE @@FETCH_STATUS = 0 BEGIN
-- Get the 2 responder records to see if we need to send out
SELECT
@adminResponderID = Id,
@notifyAdminOnIncompletes = SendNotification,
@IsDailySummary = NotificationAsSummary
FROM RegMailResponder
WHERE RegMailTriggerId=5
and RegMailTypeId=1
and EventId=@eventID
and RegTypeID=0
SELECT
@userResponderID = Id,
@notifyUserOnIncompletes = SendConfirmation
FROM RegMailResponder
WHERE RegMailTriggerId=5
and RegMailTypeId=2
and EventId=@eventID
and RegTypeID=0
SELECT
@oldRegID = r.oldRegisterID,
@isAdminReg = r.internalReg,
@IsTestRegistration = r.Test
FROM registrations r
WHERE r.sessionID = @sessionID
andr.register_ID = @regID
SET @DoNotSend = 0
IF ( @notifyUserOnIncompletes = 1 AND @oldRegID IS NULL AND @isAdminReg = 0 )
BEGIN
SELECT
@firstName = a.first_Name,
@lastName = a.last_Name,
@emailAddress = a.email_Address
FROM Attendees a
INNER JOIN Registrations r ON a.Id = r.Attendee_Id
WHERE r.groupId = @regID
ORDER BY r.register_ID
IF @@ROWCOUNT > 0
BEGIN
if exists ( select r.register_id
from Registrations r
inner join Attendees a on r.Attendee_Id = a.Id
where r.Event_Id = @eventID
and a.First_Name = @firstName
and a.Last_Name = @lastName
and a.Email_Address = @emailAddress
and r.SessionId is null)
BEGIN
set @DoNotSend = 1
END
ELSE
BEGIN
-- Add the item to the queue
INSERT INTO RegMailQueue
(
RegisterID,
RegMailTypeID,
RegMailTriggerID,
EmailID,
IsTestRegistration,
AddDate
)
VALUES
(@regID,
2,
5,
@userResponderID,
@IsTestRegistration,
GETDATE()
)
END --@DoNotSend
END
END --if @notifyUserOnIncompletes = 1
--modified 3/2/2006 to eliminate updated registrations from admin incomplete notifications
IF ( @notifyAdminOnIncompletes = 1 AND @oldRegID IS NULL)
BEGIN
SELECT
@firstName = a.first_Name,
@lastName = a.last_Name,
@emailAddress = a.email_Address,
@attendeeID= a.Id
FROM Attendees a
INNER JOIN Registrations r ON a.Id = r.Attendee_Id
WHERE r.groupId = @regID
ORDER BY r.register_ID
IF @@ROWCOUNT > 0
BEGIN
if @DoNotSend = 0 and exists ( select r.register_id
from Registrations r
inner join Attendees a on r.Attendee_Id = a.Id
where r.Event_Id = @eventID
and a.First_Name = @firstName
and a.Last_Name = @lastName
and a.Email_Address = @emailAddress
and r.SessionId is null)
BEGIN
set @DoNotSend = 1
END
IF @DoNotSend = 0
BEGIN
-- If this is a daily summary for incompletes, we need to save the attendee
-- id instead of the register id
IF @IsDailySummary = 1
BEGIN
SET @id = @attendeeID
END
ELSE
BEGIN
SET @id = @regID
END
-- Add the item to the queue
INSERT INTO RegMailQueue
(
RegisterID,
RegMailTypeID,
RegMailTriggerID,
EmailID,
IsTestRegistration,
AddDate,
IsDailySummary
)
VALUES
(@id,
1,
5,
@adminResponderID,
@IsTestRegistration,
GETDATE(),
@IsDailySummary
)
END
END
END
--remove temporary registration (also alerts Attila & Chad if a registration is stuck in the incomplete loop)
EXEC RemoveTempReg @sessionID,@attendeeID
FETCH NEXT FROM expiredRegs INTO @regID, @sessionID, @eventID
END --fetch expired regs
CLOSE expiredRegs
November 14, 2014 at 4:49 pm
This is really complex and might take a while to transform into set based code. I wouldn't expect much help and even less without DDL and sample data.
Basically, you're inserting in a table based on different conditions. Using the WHERE clause you could get it done without a cursor. There's a Stored Procedure that expects scalar values, you need to change that code as well or you'll end up with a forced loop to execute it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply