Notification Tech Info - Revision history

Henk at 20:41, 20 May 2011

2011-05-20T20:41:20Z

LibraSun at 21:43, 13 August 2009

2009-08-13T21:43:31Z

Micasaverde at 15:32, 15 September 2008

2008-09-15T15:32:52Z

Micasaverde at 15:31, 15 September 2008

2008-09-15T15:31:19Z

Micasaverde: New page: The following query gets the list of outstanding notifications from the cmh_hosted database: select * FROM Notification_User LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_N...

2008-09-15T15:29:38Z

New page: The following query gets the list of outstanding notifications from the cmh_hosted database: select * FROM Notification_User LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_N...

New page

The following query gets the list of outstanding notifications from the cmh_hosted database:

select * FROM Notification_User
LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_Notification_Attempt
JOIN Notification ON FK_Notification=PK_Notification
WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result>1);

← Older revision		Revision as of 20:41, 20 May 2011
Line 1:		Line 1:
		+	[[Category:User Instructions]]
		+
	The following query gets the list of outstanding notifications from the cmh_hosted database:		The following query gets the list of outstanding notifications from the cmh_hosted database:

@@ Line 1: / Line 1: @@
 The following query gets the list of outstanding notifications from the cmh_hosted database:
-select * FROM Notification_User
+'''select * FROM Notification_User LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_Notification_Attempt JOIN Notification ON FK_Notification=PK_Notification WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result&gt;1);'''
 LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_Notification_Attempt
 JOIN Notification ON FK_Notification=PK_Notification
-WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result>1);
-NotificationType is a single character: E, S, V for Email/SMS/Voice.  Destination is the corresponding destination (email/phone number).
+NotificationType is a single character: E, S, V for Email/SMS/Voice. Destination is the corresponding destination (email/phone number).
-The script: X does this query that will find a list of all records where we need to contact the user. It should go through the result set and for each row it needs to make adds a record in Notification_Attempt and updates FK_NotificationAttempt in Notification_Users, and set Result to 0 (in progress), and do the attempted contact (ie send an email, sms, etc.). If the attempt was successful, it sets Final=1 and Result=1. If the attempt wasn't successful it sets Result to an error code (starting with 2). And it does a count on all records in Notification_Attempt where FK_Notification_User=x. If the count is >5 (meaning 5 failed attempts), set Final to 1 (meaning don't retry). At the end of the script, the script calls itself again in a loop so it can retry any failures. The script exits only when there are no more records. Your php page that receives an alert starts the script. So the script needs to touch a file or do something to prevent more than 1 instance from running while preventing a race condition.
+The script: X does this query that will find a list of all records where we need to contact the user. It should go through the result set and, for each row it needs to make, adds a record in Notification_Attempt, updates FK_NotificationAttempt in Notification_Users, and sets Result to 0 ('in progress'), and attempts contact (i.e. sends an email, SMS, etc.). If the attempt was successful, it sets Final=1 and Result=1. If the attempt wasn't successful, it sets Result to an error code (starting with 2). And it does a count on all records in Notification_Attempt where FK_Notification_User=x. If the count is &gt;5 (meaning 5 failed attempts), it sets Final to 1 (meaning don't retry). At the end of the script, the script calls itself again in a loop so it can retry any failures. The script exits only when there are no more records. Your php page that receives an alert starts the script. So the script needs to touch a file or do something to prevent more than 1 instance from running while preventing a race condition.

← Older revision		Revision as of 15:32, 15 September 2008
Line 7:		Line 7:

	NotificationType is a single character: E, S, V for Email/SMS/Voice. Destination is the corresponding destination (email/phone number).		NotificationType is a single character: E, S, V for Email/SMS/Voice. Destination is the corresponding destination (email/phone number).
		+
		+	The script: X does this query that will find a list of all records where we need to contact the user. It should go through the result set and for each row it needs to make adds a record in Notification_Attempt and updates FK_NotificationAttempt in Notification_Users, and set Result to 0 (in progress), and do the attempted contact (ie send an email, sms, etc.). If the attempt was successful, it sets Final=1 and Result=1. If the attempt wasn't successful it sets Result to an error code (starting with 2). And it does a count on all records in Notification_Attempt where FK_Notification_User=x. If the count is >5 (meaning 5 failed attempts), set Final to 1 (meaning don't retry). At the end of the script, the script calls itself again in a loop so it can retry any failures. The script exits only when there are no more records. Your php page that receives an alert starts the script. So the script needs to touch a file or do something to prevent more than 1 instance from running while preventing a race condition.

← Older revision		Revision as of 15:31, 15 September 2008
Line 5:		Line 5:
	JOIN Notification ON FK_Notification=PK_Notification		JOIN Notification ON FK_Notification=PK_Notification
	WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result>1);		WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result>1);
		+
		+	NotificationType is a single character: E, S, V for Email/SMS/Voice. Destination is the corresponding destination (email/phone number).