Reminder mail should trigger for pending Change task after 3 days of task creation

[surajbiswal]

Expected Result: I am trying to set up an email notification system that triggers an email to the task assignee and their manager if a change task is pending for more than 3 days. The email should contain all pending change task details in an Excel sheet. If there are multiple change tasks with different assignees, separate emails should be sent to each assignee with their respective pending task details.

Currently, one email is being sent to all assignees with all pending change task details which is not expected" how to achieve?

[Ankur Bawiskar]

@surajbiswal 

so Assignee1 is receiving email with list of all pending tasks, same for Assignee2

that's what you want

what's not working as expected?

how is the current setup configured?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[surajbiswal]

Hi @Ankur Bawiskar ,

Whatever the Change task is pending with Assignee 1 those details he should get as a attachment, not all the the pending task details which is pending with other assignee. 
I have configured one schedule job which will run daily to check if the the task is pedning more than 3days then it will trigger an event and through the event the email will trigger to the Assignee and Assignee to manager. Also created an script include for attachment generate.The notification is triggered with no attachment. Below are the script details:
Schedule job:

(function executeScheduledJob() {

    var gr = new GlideRecord('change_task');

    gr.addQuery("active", true);

    gr.addEncodedQuery("sys_created_on<javascript&colon;gs.daysAgoStart(3)");

    gr.query();

    var userTaskMap = {};

    while (gr.next()) {

        var userId = gr.assigned_to.sys_id.toString();

        if (!userTaskMap[userId]) {

            userTaskMap[userId] = {

                user: gr.assigned_to,

                manager: gr.assigned_to.manager,

                tasks: []

            };

        }

        userTaskMap[userId].tasks.push({

            number: gr.getValue('number'),

            short_description: gr.getValue('short_description'),

            type: gr.getValue('type'),

            state: gr.getValue('state'),

            planned_start_date: gr.getValue('planned_start_date'),

            planned_end_date: gr.getValue('planned_end_date'),

            assignment_group: gr.getDisplayValue('assignment_group'),

            sys_id: gr.getUniqueValue()

        });

    }

    var excelUtil = new GenerateExcelForTasks();

    for (var userId in userTaskMap) {

        var userInfo = userTaskMap[userId];

        var firstTaskId = userInfo.tasks[0].sys_id;

        var fileName = userInfo.user.name.replace(/\s/g, "_") + "_Pending_Tasks";

        // Generate Excel and attach to the first task

        var attachmentSysId = excelUtil.createExcel(userInfo.tasks, fileName, firstTaskId);

        var taskGR = new GlideRecord('change_task');

if (taskGR.get(firstTaskId)) {

    gs.log("Triggering event for user: " + userInfo.user.name);

    gs.log("Task number: " + taskGR.getValue('number'));

    gs.log("Assigned to: " + taskGR.assigned_to.email);

    gs.log("Manager: " + taskGR.assigned_to.manager.email);

    gs.eventQueue("pending.change.task.email", taskGR, "", "You have pending change tasks.");

} else {

    gs.log("Failed to load task record for user: " + userInfo.user.name);

}}

})();
Script include:

var GenerateExcelForTasks = Class.create();

GenerateExcelForTasks.prototype = {

    initialize: function() {},

    createExcel: function(tasks, fileName, taskSysId) {

        var excel = new GlideExcelWorkbook();

        var sheet = excel.createSheet("Pending Tasks");

        // Add headers

        sheet.setCell(1, 1, "Number");

        sheet.setCell(1, 2, "Short Description");

        sheet.setCell(1, 3, "Type");

        sheet.setCell(1, 4, "State");

        sheet.setCell(1, 5, "Planned Start Date");

        sheet.setCell(1, 6, "Planned End Date");

        sheet.setCell(1, 7, "Assignment Group");

        // Add task rows

        for (var i = 0; i < tasks.length; i++) {

            var row = i + 2;

            var task = tasks[i];

            sheet.setCell(row, 1, task.number);

            sheet.setCell(row, 2, task.short_description);

            sheet.setCell(row, 3, task.type);

            sheet.setCell(row, 4, task.state);

            sheet.setCell(row, 5, task.planned_start_date);

            sheet.setCell(row, 6, task.planned_end_date);

            sheet.setCell(row, 7, task.assignment_group);

        }

        // Attach to the actual task record

        var attachmentSysId = excel.saveAsAttachment("change_task", taskSysId, fileName + ".xlsx");

        return attachmentSysId;

    },

    type: 'GenerateExcelForTasks'

}; let me know if anything i missed.

[Ankur Bawiskar]

@surajbiswal 

did you test if excel is generated properly? also you need to attach that file to outbound email

check this link about excel generation

Exporto MRVS to Excel 

If my response helped please mark it correct and close the thread so that it benefits future readers.

[surajbiswal]

@Ankur Bawiskar 
Now I have changed the schedule job to below and created one schedule email of report, now i am getting the emails with the attached excel but all the pending task details sent to Assignee 1 . I want whatever the Change task is pending with Assignee 1 only those change task details he should get as a excel not all whatever pending with other assignee.

updateUsersListWithManager()

function updateUsersListWithManager(){

    var scheduleReport = new GlideRecord('sysauto_report');  // glide the Scheuled report

    scheduleReport.get("94e66b1e2b962214a225fe73fc91bf4f"); //Sys ID of your schedule Report

    var recipients = []; var recipientsManager = [];

    var tablename = scheduleReport.report.table;

    var query = scheduleReport.report.filter;

    var reportgr = new GlideRecord(tablename);

    reportgr.addEncodedQuery(query);

    reportgr.query();

    while (reportgr.next()) {

        recipients.push(reportgr.assigned_to.sys_id.toString());  // push the assigned _to users in recipients array

        recipientsManager.push(reportgr.assigned_to.manager.sys_id.toString());  // push the assigned _to managers in recipientsManager array

    }

    //  gs.log("recipients array: " + recipients.toString());

    //  gs.log("recipients manager array: " + recipientsManager.toString());

    var arrayUtil = new ArrayUtil();

    arrayUtil.concat(recipients, recipientsManager);

    var finalRecipients = arrayUtil.unique(recipients);   // unique elements

    //  gs.log("finalRecipients: " + finalRecipients);

    scheduleReport.user_list = finalRecipients.join(',');

    //  gs.log("User list: " + scheduleReport.user_list);

    scheduleReport.update();

    SncTriggerSynchronizer.executeNow(scheduleReport); //execute schedule report

}