Custom scheduled reports - pulling in data from different tables using HTML

(Courtesy of Mohammad Nassar)



scheduled report:



event:



example notification



email script:

(function runMailScript(current, template, email, email_action, event) { email.setSubject('IM Summary ' + gs.nowDateTime()); email.addAddress('cc', 'dl_imsummary_xxx@xxxx.net', 'dl_imsummary_xxx@xxxx.net'); var REPORT_EMAIL = new _Report_Emails(); var output = ''; var table_name, encoded_query, field_names; output += '<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">'; output += '<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>'; output += '<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>'; // Active Major Incidents (Non-Dom1) output += '\n<h4>Active Major Incidents (Non-Dom1):</h4>\n'; table_name = 'incident'; encoded_query = 'active=true^major_incident_state=accepted^service_offering.name!=Dom1'; field_names = 'number, priority, state, short_description, service_offering, reassignment_count, sys_updated_on, description, u_service_portfolio'; output += REPORT_EMAIL.queryDataAndPresentInHTML(table_name, encoded_query, field_names); // Active Major Incidents (Dom1) output += '\n\n<h4>Active Major Incidents (Dom1):</h4>\n'; table_name = 'incident'; encoded_query = 'active=true^major_incident_state=accepted^service_offering.name=Dom1'; field_names = 'number, priority, state, short_description, service_offering, reassignment_count, sys_updated_on, description, u_service_portfolio'; output += REPORT_EMAIL.queryDataAndPresentInHTML(table_name, encoded_query, field_names); output += '\n<h4>NOTE: See MIM Comms for latest updates.</h4>'; // Scheduled Ongoing Changes output += '\n\n<h4>Scheduled Ongoing Changes:</h4>'; var today = GlideDate(); var next_day = REPORT_EMAIL.getNextDayDate(); table_name = 'change_request'; encoded_query = 'state!=3^start_dateSTARTSWITH' + today + '^ORstart_dateSTARTSWITH' + next_day; field_names = 'number, priority, state, short_description, service_offering, reassignment_count, sys_updated_on, description, u_service_portfolio'; output += REPORT_EMAIL.queryDataAndPresentInHTML(table_name, encoded_query, field_names); // MIM OOH Rota Cover - 12AM to 8AM var users_covering_rota_at_morning = REPORT_EMAIL.getRotaCoverageUsers(new GlideDate() + ' 01:00:00'); output += '\n\n<h4>MIM OOH Rota Cover - 12AM to 8AM:</h4>'; output += REPORT_EMAIL.queryDataAndPresentInHTML('sys_user', 'sys_idIN' + users_covering_rota_at_morning, 'name, email'); // MIM OOH Rota Cover - 06PM to 12AM var users_covering_rota_at_evening = REPORT_EMAIL.getRotaCoverageUsers(new GlideDate() + ' 20:00:00'); output += '\n\n<h4>MIM OOH Rota Cover - 06PM to 12AM:</h4>'; output += REPORT_EMAIL.queryDataAndPresentInHTML('sys_user', 'sys_idIN' + users_covering_rota_at_evening, 'name, email'); template.print(output); })(current, template, email, email_action, event);






















script include:


var _Report_Emails = Class.create(); _Report_Emails.prototype = Object.extendsObject(AbstractAjaxProcessor, { sendReportEmailForMIM: function() { if (!this.isTodayWorkingDay()) return; gs.eventQueue('.mim.daily_mim_report_email', '', 'DTSIncidentManagement@.NET', 'DTSIncidentManagement@.NET'); }, sendReportEmailForSMGT: function() { if (!this.isTodayWorkingDay()) return; gs.eventQueue('.mim.daily_smgt_report_email', '', 'dl_smtg_eod_to@.net', 'dl_smtg_eod_to@.net'); }, getRotaCoverageUsers: function(date_time) { var group_sys_id = gs.getProperty('.ooh.incident.managers'); var rota_coverage_users = new OnCallRotationSNC().whoIsOnCall(group_sys_id, '', '', date_time); var rota_users = []; for (var i=0; i<rota_coverage_users.length; i++) rota_users.push(rota_coverage_users[i].userId); return rota_users; }, queryDataAndPresentInHTML: function(table_name, encoded_query, field_names) { var records = this.queryData(table_name, encoded_query); var presentable_data = this.representDataInHTML(records, field_names); return presentable_data; }, queryData: function(table_name, encoded_query) { var rec = new GlideRecord(table_name); rec.addEncodedQuery(encoded_query); rec.query(); return rec; }, representDataInHTML: function(rec, field_names) { var data = ''; field_names = field_names.split(','); if (rec.hasNext()) { data += '<style> table {width: 100%;} table, th, td {border: 1px solid lightgrey; border-collapse: collapse;} th, td {padding: 5px;} th {font-weight: bold;} </style>'; data += '<table class="table table-striped table-bordered table-hover">'; } var while_loop_accessed = false; while (rec.next() && field_names.length != 0) { if (while_loop_accessed == false) { while_loop_accessed = true; for (var i = 0; i < field_names.length; i++) { if (i == 0) data += '<thead><tr>'; var field_label = rec.getElement(field_names[i].trim()).getLabel(); data += '<th>' + field_label + '</th>'; if (i == field_names.length -1) data += '</tr></thead>'; } } for (var i = 0; i < field_names.length; i++) { if (i == 0) data += '<tbody><tr>'; var field_label = rec.getElement(field_names[i].trim()).getLabel(); var field_value = rec.getElement(field_names[i].trim()).getDisplayValue(); var style = this.getStyle(rec, field_label, field_value); if (field_label == 'Number') field_value = this.getURI(rec, field_value); data += '<td' + style + '>' + field_value + '</td>'; if (i == field_names.length -1) data += '</tr></tbody>'; } } if (data != '') data += '</table>'; else data = 'No data available'; return data; }, getURI: function(record, field_value) { var uri = 'https://' + gs.getProperty('instance_name') + '.service-now.com/' + record.getTableName() + '.do?sys_id=' + record.sys_id; return '<a href="' + uri + '">' + field_value + '</a>'; }, getStyle: function(record, field_label, field_value) { if (record == null || record == undefined) return ''; field_label = field_label.toLowerCase(); field_value = field_value.toLowerCase(); var table_name = record.getTableName(); var bg_colour = ' style="background-color: '; if (table_name == 'incident' && field_label == 'number') return bg_colour + this.getAlternativeColour(record.u_rag_status) + ';"'; if (table_name == 'change_request' && field_label == 'state') { if (field_value == 'new') // If state is 'New' return bg_colour + '#5e95ed' + ';"'; if (field_value == 'access') // If state is 'Assess' return bg_colour + 'yellow' + ';"'; if (field_value == 'implement') // If state is 'Implement' return bg_colour + '#24ba0e' + ';"'; } return ''; }, getAlternativeColour: function(colour) { colour = colour.toLowerCase(); if (colour == 'green') return 'LimeGreen'; if (colour == 'amber') return 'orange'; if (colour == 'red') return 'tomato'; return colour; }, getNextDayDate: function() { var next_day = GlideDate(); if (this.isTodayFriday()) next_day.addDays(3); else next_day.addDays(1); return next_day; }, isTodayFriday: function() { var today = GlideDate(); var week_day = today.getDayOfWeekLocalTime(); var next_day = GlideDate(); return (week_day == 5); }, isTodayWorkingDay: function() { var today = GlideDate(); var week_day = today.getDayOfWeekLocalTime(); var next_day = GlideDate(); return (week_day != 6 || week_day != 7); }, type: '_Report_Emails' });

Comments

Popular posts from this blog

GlideRecord setValue

variable advanced reference qualifier example

URL link in addInfoMessage