“Whether you want to uncover the secrets of the universe, or you just want to pursue a career in the 21st century, basic computer programming is an essential skill to learn.”
— Stephen Hawking
Last time, we got started on our script that will run the review process by building and testing the primary function that finds all of the configurations that will need to run. Today we need to continue on with the script, beginning with the process for a single configuration’s execution. Before we get started, though, we need to talk a little bit about making sure that the notice will go to somebody.
Both the configuration record and the system as a whole have a default recipient/email address. The system default email address is contained in a System Property and the configuration default recipient is on the configuration record. There is a field on the source record for the recipient, and if that recipient record is valid and active and contains an email address, then that will be the address that we will want to use. If not, then we will want to use the configuration fallback recipient, and if there is some issue in the notice process with that recipient’s email address, then we will end up using the system fallback address. This is to ensure that the notice will actually get to a real person who can take action on the review request.
There is also another optional System Property that can be used to limit notifications to a specific email domain. If that value is present, and the recipient email is not an email address of that domain, then the fallback recipient will be used as well. All of this should be documented in the notice item record. So the first thing that we will want to do will be to pull those System Properties.
// fetch system properties
var systemFallbackAddress = gs.getProperty('x_11556_periodic_r.fallback_email_address');
var systemEmailDomain = gs.getProperty('x_11556_periodic_r.email_domain');
The next thing that we will want to do will be to create a record in the Review Execution table to memorialize this execution.
// create execution record
var executionGR = new GlideRecord('x_11556_periodic_r_review_execution');
executionGR.configuration = configuration;
executionGR.run_date = new GlideDate();
executionGR.run_start = new GlideDateTime();
executionGR.state = 'Running';
executionGR.short_description = executionGR.getDisplayValue('run_date') + ' review notices';
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.completion_code = 0;
executionGR.insert();
This is standard GlideRecord stuff, so we don’t really need to go into a lot of detail here other than to say that we link it back to the configuration that is being executed and initialize all of the other values. Once we have our execution record created, we can pull the configuration record from the reference, and once we have it, we can set up the fallback recipient for this configuration.
var configurationGR = executionGR.configuration.getRefRecord();
var fallbackRecipient = configurationGR.getDisplayValue('fallback_recipient');
Now we need to go fetch all of the items that need to be reviewed this cycle, but because we want to consolidate all of the items for any given recipient into a single notice, we will first need to fetch them all, and then once we have the full list, group them by recipient. To facilitate that, we can create a temporary notice record and assign all of the items to that notice, and once we run through all of the query results, we can then fetch them back by recipient. So let’s create that temporary notice record now.
// create temporary notice record
var noticeGR = new GlideRecord('x_11556_periodic_r_review_notice');
noticeGR.review_execution = executionGR.getUniqueValue();
noticeGR.recipient = configurationGR.fallback_recipient;
noticeGR.short_description = 'Temporary notice record';
noticeGR.insert();
var tempNotice = noticeGR.getUniqueValue();
Once we have our notice record, we can use the table and filter from the configuration record to find all of the items to be reviewed.
// create notice item records from source table data
var noticeItemGR = new GlideRecord('x_11556_periodic_r_review_notice_item');
var itemCt = 0;
var sourceGR = new GlideRecord(table);
if (sourceGR.isValid()) {
if (filter) {
sourceGR.addEncodedQuery(filter);
}
sourceGR.orderBy(configurationGR.short_description_column);
sourceGR.query();
while (sourceGR.next()) {
...
}
...
} else {
...
}
Then, inside the query loop, we can build a record in the Review Notice Item table for each item.
noticeItemGR.initialize();
noticeItemGR.review_notice = tempNotice;
noticeItemGR.id = sourceGR.getUniqueValue();
noticeItemGR.short_description = sourceGR.getDisplayValue(configurationGR.short_description_column);
noticeItemGR.description = sourceGR.getDisplayValue(configurationGR.description_column);
noticeItemGR.recipient = sourceGR.getValue(configurationGR.recipient_column);
noticeItemGR.insert();
itemCt++;
Once the notice item record has been created, we can inspect the recipient and come up with a valid recipient to which we will send the notice.
var recipientGR = new GlideRecord('sys_user');
recipientGR.get(noticeItemGR.getValue('recipient'));
var notes = '';
if (recipientGR.isValid()) {
if (recipientGR.getValue('active')) {
var email = recipientGR.getDisplayValue('email');
if (email) {
if (systemEmailDomain && !email.endsWith(systemEmailDomain)) {
notes = 'Recipient email address is not an authoized email address; reverting to fallback recipient';
}
} else {
notes = 'Specified recipient has no email address; reverting to fallback recipient';
}
} else {
notes = 'Specified recipient is not active; reverting to fallback recipient';
}
} else {
notes = 'Recipient column empty on source record; reverting to fallback recipient';
}
if (notes) {
noticeItemGR.notes = notes;
noticeItemGR.recipient = fallbackRecipient;
noticeItemGR.update();
}
Once we complete the loop, we will want to see if the query returned any items, and either process the items or close out this execution.
if (itemCt > 0) {
executionGR.total_items = itemCt;
executionGR.update();
this.sendNotices(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice);
} else {
// finalize the execution
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Complete';
executionGR.completion_code = 0;
executionGR.description = 'No items matched the filter criteria during this run, so no notices were sent out.';
executionGR.update();
}
And once that is done, whether we had any items returned or not, we will want to delete that temporary notice record.
// delete the temporary notice
noticeGR.get(tempNotice);
noticeGR.deleteRecord();
One last thing: we need to do something in the event that we find ourselves in the else branch of the if (sourceGR.isValid()) { conditional. This basically means that the source table specified on the configuration record is not valid and so we cannot look for any items there. This is basically a failure of the execution, so we need to update the execution record to reflect that.
// finalize the execution
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Failed';
executionGR.completion_code = 1;
executionGR.description = 'The specified source table in the configuration record is not valid; execution cannot proceed; aborting execution.';
executionGR.update();
Failure is not an option that we originally set up for that field, so we will have to go back into the dictionary at some point and fix that to make that code work. Finally, regardless of how the execution turned out, we need to update the configuration record with the next run date, which is calculated in the function that we added for that purpose earlier.
// set the next run date
configurationGR.setValue('next_scheduled_date', this.calculateNextRunDate(configurationGR));
configurationGR.update();
With the addition of this completed function, our utility Script Include now looks like this.
var PeriodicReviewUtils = Class.create();
PeriodicReviewUtils.prototype = {
initialize: function() {
},
dailyProcess: function() {
var toRun = [];
var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
var today = new GlideDate();
configurationGR.addQuery('next_scheduled_date', today);
configurationGR.orderBy('number');
configurationGR.query();
while (configurationGR.next()) {
var execution = {};
execution.configuration = configurationGR.getUniqueValue();
execution.table = configurationGR.getDisplayValue('table');
execution.filter = configurationGR.getDisplayValue('filter');
toRun.push(execution);
}
if (toRun.length > 0) {
gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
for (var i in toRun) {
var thisRun = toRun[i];
this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
}
gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
} else {
gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
}
},
processExecution: function(configuration, table, filter) {
gs.info('PeriodicReviewUtils.dailyProcess: ' + configuration + '; ' + table + '; ' + filter);
// fetch system properties
var systemFallbackAddress = gs.getProperty('x_11556_periodic_r.fallback_email_address');
var systemEmailDomain = gs.getProperty('x_11556_periodic_r.email_domain');
// create execution record
var executionGR = new GlideRecord('x_11556_periodic_r_review_execution');
executionGR.configuration = configuration;
executionGR.run_date = new GlideDate();
executionGR.run_start = new GlideDateTime();
executionGR.state = 'Running';
executionGR.short_description = executionGR.getDisplayValue('run_date') + ' review notices';
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.completion_code = 0;
executionGR.insert();
var configurationGR = executionGR.configuration.getRefRecord();
var fallbackRecipient = configurationGR.getDisplayValue('fallback_recipient');
// create temporary notice record
var noticeGR = new GlideRecord('x_11556_periodic_r_review_notice');
noticeGR.review_execution = executionGR.getUniqueValue();
noticeGR.recipient = configurationGR.fallback_recipient;
noticeGR.short_description = 'Temporary notice record';
noticeGR.insert();
var tempNotice = noticeGR.getUniqueValue();
// create notice item records from source table data
var noticeItemGR = new GlideRecord('x_11556_periodic_r_review_notice_item');
var itemCt = 0;
var sourceGR = new GlideRecord(table);
if (sourceGR.isValid()) {
if (filter) {
sourceGR.addEncodedQuery(filter);
}
sourceGR.orderBy(configurationGR.short_description_column);
sourceGR.query();
while (sourceGR.next()) {
noticeItemGR.initialize();
noticeItemGR.review_notice = tempNotice;
noticeItemGR.id = sourceGR.getUniqueValue();
noticeItemGR.short_description = sourceGR.getDisplayValue(configurationGR.short_description_column);
noticeItemGR.description = sourceGR.getDisplayValue(configurationGR.description_column);
noticeItemGR.recipient = sourceGR.getValue(configurationGR.recipient_column);
noticeItemGR.insert();
itemCt++;
var recipientGR = new GlideRecord('sys_user');
recipientGR.get(noticeItemGR.getValue('recipient'));
var notes = '';
if (recipientGR.isValid()) {
if (recipientGR.getValue('active')) {
var email = recipientGR.getDisplayValue('email');
if (email) {
if (systemEmailDomain && !email.endsWith(systemEmailDomain)) {
notes = 'Recipient email address is not an authoized email address; reverting to fallback recipient';
}
} else {
notes = 'Specified recipient has no email address; reverting to fallback recipient';
}
} else {
notes = 'Specified recipient is not active; reverting to fallback recipient';
}
} else {
notes = 'Recipient column empty on source record; reverting to fallback recipient';
}
if (notes) {
noticeItemGR.notes = notes;
noticeItemGR.recipient = fallbackRecipient;
noticeItemGR.update();
}
}
if (itemCt > 0) {
executionGR.total_items = itemCt;
executionGR.update();
this.sendNotices(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice);
} else {
// finalize the execution
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Complete';
executionGR.completion_code = 0;
executionGR.description = 'No items matched the filter criteria during this run, so no notices were sent out.';
executionGR.update();
}
// delete the temporary notice
noticeGR.get(tempNotice);
noticeGR.deleteRecord();
} else {
// finalize the execution
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Failed';
executionGR.completion_code = 1;
executionGR.description = 'The specified source table in the configuration record is not valid; execution cannot proceed; aborting execution.';
executionGR.update();
}
// set the next run date
configurationGR.setValue('next_scheduled_date', this.calculateNextRunDate(configurationGR));
configurationGR.update();
},
calculateNextRunDate: function(configurationGR) {
var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));
var frequency = configurationGR.getValue('frequency');
var days = 0;
var months = 0;
if (frequency == 'daily') {
days = 1;
} else if (frequency == 'weekly') {
days = 7;
} else if (frequency == 'biweekly') {
days = 14;
} else if (frequency == 'monthly') {
months = 1;
} else if (frequency == 'bimonthly') {
months = 2;
} else if (frequency == 'quarterly') {
months = 3;
} else if (frequency == 'semiannually') {
months = 6;
} else if (frequency == 'annually') {
months = 12;
} else if (frequency == 'biannually') {
months = 24;
}
if (days > 0) {
runDate.setDate(runDate.getDate() + days);
} else {
runDate.setMonth(runDate.getMonth() + months);
}
return JSON.stringify(runDate).substring(1, 11);
},
type: 'PeriodicReviewUtils'
};
Next time, we will stub out that sendNotices function and give things another test or two, and then actually build out that function to group the items by recipient and send out the notices.
