Incident ageing detail as mail notification.

[SAM321]

Hi , I have requirement where, email notification should be send to respective managers of group .The emails contains the details of incidents , scrum aging details. 
Let the group contains members A, B, C and the manager is X
 another group contain members D,E,F and the manager is Y.
now the mail notification should be send to the respective managers weekly.  The mail body will be like
To X manager it will be:
                    Incidents>5 days        Incidents>10 days          Incident>20 days         Incidents>60
A                               2                                 0                                      1                                     0

B                               4                                 1                                      6                                     1

C                               2                                3                                       2                                     3
The days will be based on assigned to date:
Similarly for manager Y with his group members details.

what is the best way to approach this requirement, Iam new to scripting, Can anyone guide me to achieve this.

[Mark Manders]

Something like this, but please check all the fields, because OOB there isn't an 'assigned to date' as example. You have some customization in there, that needs to be included. 
You can also run into some issues if it's not a date, but a date/time field. The basics should be in here.

// Function to get incidents aging
function getIncidentsAging(manager) {
    var members = getGroupMembers(manager);
    var agingData = {};
    
    members.forEach(function(member) {
        agingData[member.name] = {
            '5_days': 0,
            '10_days': 0,
            '20_days': 0,
            '60_days': 0
        };
        
        var gr = new GlideRecord('incident');
        gr.addQuery('assigned_to', member.sys_id);
        gr.query();
        
        while (gr.next()) {
            var assignedDate = gr.getValue('assigned_date');
            var daysOld = gs.daysAgo(assignedDate);
            
            if (daysOld > 60) {
                agingData[member.name]['60_days']++;
            } else if (daysOld > 20) {
                agingData[member.name]['20_days']++;
            } else if (daysOld > 10) {
                agingData[member.name]['10_days']++;
            } else if (daysOld > 5) {
                agingData[member.name]['5_days']++;
            }
        }
    });
    
    return agingData;
}

// Function to get group members
function getGroupMembers(manager) {
    var members = [];
    var gr = new GlideRecord('sys_user_grmember');
    gr.addQuery('manager', manager);
    gr.query();
    
    while (gr.next()) {
        members.push({
            'sys_id': gr.user.sys_id,
            'name': gr.user.name
        });
    }
    
    return members;
}

// Function to format the email content
function formatEmailContent(manager, agingData) {
    var content = 'Incidents Aging Report for Manager ' + manager + '\n\n';
    content += 'Name\tIncidents > 5 days\tIncidents > 10 days\tIncidents > 20 days\tIncidents > 60 days\n';
    
    for (var member in agingData) {
        var data = agingData[member];
        content += member + '\t' + data['5_days'] + '\t' + data['10_days'] + '\t' + data['20_days'] + '\t' + data['60_days'] + '\n';
    }
    
    return content;
}

// Main function to send the email
function sendIncidentsAgingReport() {
    var managers = ['X', 'Y']; // Replace with actual manager sys_ids or query them dynamically
    
    managers.forEach(function(manager) {
        var agingData = getIncidentsAging(manager);
        var emailContent = formatEmailContent(manager, agingData);
        
        // Sending email
        gs.email(manager, 'Weekly Incidents Aging Report', emailContent);
    });
}

// Schedule job script
sendIncidentsAgingReport();