19.3.2. Technical implementation
This is technical detail designed to help developers
understand how the feature works in case they need to make changes
or deal with issues of non-delivery
The system adds a new table to Kartris:
tblKartrisStockNotifications. When a customer signs
up to a stock notification, a record is added here with their email
address, the version ID, and some other details. We store the product
name and the link to this product's page with the notification, as
well as the language ID.
When we send a notification, we can use the language ID to pull the appropriate mail template and we can pull the product name and the link to the page directly from the notification record - we don't need to look these things up separately.
We also add a new field V_BulkUpdateTimeStamp to the tblKartrisVersions table. The sprocs that modify and update version records are modified so that this parameter can be passed in, but will default to NULL if not. If a NULL value occurs, the current time and date will be added by the sproc to that field. Versions that are updated through the Kartris back end (i.e. the BLL functions) will pass in 1900/1/1 as the date. In this way, versions that are updated by the Data Tool will end up with a recent date in the V_BulkUpdateTimeStamp field, while items updated via the Data Tool will end up with either a NULL value or 1900/1/1. Because the BLL version update functions will run stock notification checks when they update a version, we can use the V_BulkUpdateTimeStamp field to identify versions that have not had this stock notification check after being updated (i.e. those updated via the Data Tool).
If there are versions that were updated by the Data Tool and so have not had stock notification checks, a new item will appear in the task list showing the number of such versions. Clicking the 'Stock Notifications' link will go to a page which shows pending notification requests (any stock notification sign ups from users), as well as completed (sent) ones in a second list.
If there are versions to be checked, this page will also have a button to run those checks. Clicking it will loop through versions with a V_BulkUpdateTimeStamp date of after 1901. When a version is checked, it's V_BulkUpdateTimeStamp date is reset to 1900/1/1.
When a stock notification is sent, the stock notification record is updated so the SNR_DateSettled field has the date and time added to it, and the 'status' field changes from 'w' (waiting) to 's' (settled).
When we send a notification, we can use the language ID to pull the appropriate mail template and we can pull the product name and the link to the page directly from the notification record - we don't need to look these things up separately.
We also add a new field V_BulkUpdateTimeStamp to the tblKartrisVersions table. The sprocs that modify and update version records are modified so that this parameter can be passed in, but will default to NULL if not. If a NULL value occurs, the current time and date will be added by the sproc to that field. Versions that are updated through the Kartris back end (i.e. the BLL functions) will pass in 1900/1/1 as the date. In this way, versions that are updated by the Data Tool will end up with a recent date in the V_BulkUpdateTimeStamp field, while items updated via the Data Tool will end up with either a NULL value or 1900/1/1. Because the BLL version update functions will run stock notification checks when they update a version, we can use the V_BulkUpdateTimeStamp field to identify versions that have not had this stock notification check after being updated (i.e. those updated via the Data Tool).
If there are versions that were updated by the Data Tool and so have not had stock notification checks, a new item will appear in the task list showing the number of such versions. Clicking the 'Stock Notifications' link will go to a page which shows pending notification requests (any stock notification sign ups from users), as well as completed (sent) ones in a second list.
If there are versions to be checked, this page will also have a button to run those checks. Clicking it will loop through versions with a V_BulkUpdateTimeStamp date of after 1901. When a version is checked, it's V_BulkUpdateTimeStamp date is reset to 1900/1/1.
When a stock notification is sent, the stock notification record is updated so the SNR_DateSettled field has the date and time added to it, and the 'status' field changes from 'w' (waiting) to 's' (settled).