Requirement:
We need to send pending timesheet details to the corresponding project manager by end of the Day.
We can use a scheduled script for the development and it will check the timesheet and send a list of pending timesheets to the corresponding project managers with necessary details such as Date,Timesheet details,Actual Hours,Employee name , timesheet link etc.
NOTE
- If a project contains multiple project managers, we need to send the timesheet details to all the project managers
- The summarised emails should contain a form of table.
- //JJMRScheduledEmailsforPM.js
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define(['N/email', 'N/error', 'N/record', 'N/runtime', 'N/search', 'N/task'],
/**
* @param{email} email
* @param{error} error
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{task} task
*/
(email, error, record, runtime, search, task) => {
/**
* Function to create the search of Pending Timesheets.
* @returns [{res}] res - searchResult of AO Obj
*/
function timesheetSearch(project_Search_Result) {
try{
var filterArr = [];
if(project_Search_Result.length>0){
for(var i=0;i<project_Search_Result.length;i++){
filterArr.push(project_Search_Result[i].prjId)
}
}
log.debug("filterArr: ",filterArr)
var timesheetSearchObj = search.create({
type: "timesheet",
filters:
[
["custrecord_jj_supervisor_approval_status","anyof","1"],
"AND",
["totalhours","greaterthan","0.0"],
"AND",
["timesheetdate","onorafter","1/1/2020"],
"AND",
["timebill.customer","anyof",filterArr]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "custrecord_jj_supervisor_approval_status",
summary: "GROUP",
label: "Supervisor Approval Status"
}),
search.createColumn({
name: "startdate",
summary: "GROUP",
label: "Start Date"
})
]
});
var res = []
timesheetSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
var timesheetId = result.getValue({
name: "internalid",
summary: "GROUP"
})
var employee = result.getValue({
name: "employee",
summary: "GROUP"
})
var employeeTxt = result.getText({
name: "employee",
summary: "GROUP"
})
var startDate = result.getValue({
name: "startdate",
summary: "GROUP"
})
var client = result.getValue({
name: "customer",
join: "timeBill",
summary: "GROUP"
})
res.push({
timesheetId: timesheetId,
employee: employee,
employeeTxt: employeeTxt,
startDate: startDate,
client: client
})
return true;
});
return res
}
catch (e) {
log.error({
title: "Error @ Timesheet Search: ",
details: e.name+" : "+e.message
})
}
}
function projectSearch(user) {
try {
var jobSearchObj = search.create({
type: "job",
filters:
[
["isinactive","is","F"],
"AND",
[[["jobresource","anyof",user],"AND",["jobresourcerole","anyof","-2"]],"OR",["projectmanager","anyof",user]]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({
name: "entityid",
sort: search.Sort.ASC,
label: "Name"
})
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID", sort: search.Sort.ASC,}),
search.createColumn({name: "entityid", label: "Name"})
]
});
var searchResultCount = jobSearchObj.runPaged().count;
var res = []
log.debug("jobSearchObj result count",searchResultCount);
jobSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
var prjId = result.getValue({name: "internalid"})
var prjName = result.getValue({name: "entityid"})
res.push({
prjId: prjId,
prjName: prjName
})
return true;
});
return res
}
catch (e) {
log.error({
title: 'Error @ timesheet SEARCH: ',
details: e.name + ' : ' + e.message
});
}
}
/**
* Function to create the search of Employees who is a Project resource.
* @returns [{res}] res - searchArray of object
*/
function prjManagerSearch(){
try{
var employeeSearchObj = search.create({
type: "employee",
filters:
[
["isinactive","is","F"],
"AND",
["isjobresource","is","T"],
"AND",
[["releasedate","isempty",""],"OR",["releasedate","notonorbefore","today"]]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({
name: "entityid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({name: "email", label: "Email"})
]
});
var searchResultCount = employeeSearchObj.runPaged().count;
var res = []
employeeSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
var emp = result.getValue({name: "internalid"})
var empTxt = result.getValue({name: "entityid"})
var empMail = result.getValue({name: "email"})
res.push({
Employee: emp,
EmployeeTxt: empTxt,
EmployeeMail: empMail
})
return true;
});
return res;
}
catch (e) {
log.error({
title: 'Error @ Prj Manager Search: ',
details: e.name + ' : ' + e.message
});
}
}
/**
* Function to create Mail Body template for adding as Mail Body in Sending Timesheet details to each project managers.
* @param {tableContents} tableContents - Timesheet details to be included in Mail body
* @param {length} length - total number of pending timesheets of a project manager
* @param {user} user - Name of the Project Manager
* @returns {emailBody} - Email Body Template as String
*/
function mailTemplate(tableContents,length ,user){
try{
const emailBody = [];
emailBody.push(`<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">`);
emailBody.push(`<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title></title>
</head>`);
emailBody.push(`<body>`);
emailBody.push(`Dear ${user},<br><p>You have ${length} Pending Timesheets to Approve. Please Approve these Timsheets.<p><br/>
<p>Details of timesheets are given beolow:</p><br/>`);
emailBody.push(
`<table border="0" cellpadding="0" cellspacing="0" height="100%" width="100%" id="bodyTable">`
);
emailBody.push(`<tr>`);
emailBody.push(`<td align="center" valign="top">`);
emailBody.push(`<table border="0" cellpadding="20" cellspacing="0" width="600" id="customers" style="font-family: Arial, Helvetica, sans-serif;border-collapse: collapse;width: 50%;">`);
emailBody.push(`<tr> </tr>`);
emailBody.push(`<tr>`);
emailBody.push(`<th style="border: 1px solid #ddd;padding: 8px;padding-top: 12px;padding-bottom: 12px;text-align: left;"> DATE</th>`);
emailBody.push(`<th style="border: 1px solid #ddd;padding: 8px;padding-top: 12px;padding-bottom: 12px;text-align: left;"> Timesheet</th>`);
emailBody.push(`<th style="border: 1px solid #ddd;padding: 8px;padding-top: 12px;padding-bottom: 12px;text-align: left;"> Employee</th>`);
emailBody.push(`<th style="border: 1px solid #ddd;padding: 8px;padding-top: 12px;padding-bottom: 12px;text-align: left;"> Link</th>`);
emailBody.push(`<tr/>`);
emailBody.push(
tableContents.map((el) => {
let temp = [];
temp.push(`<tr>`)
temp.push(` <td style="border: 1px solid #ddd;padding: 8px;">${el.startDate}</td>`);
temp.push(` <td style="border: 1px solid #ddd;padding: 8px;">${el.timesheetId}</td>`);
temp.push(` <td style="border: 1px solid #ddd;padding: 8px;">${el.employeeTxt}</td>`);
temp.push(` <td style="border: 1px solid #ddd;padding: 8px;"><a href=" ${'https://3689903-sb1.app.netsuite.com/app/accounting/transactions/time/weeklytimebill.nl?id='+el.timesheetId}" />TIMSHEET - ${el.timesheetId}</td>`);
temp.push(`</tr>`);
return temp.join("");
}).join("")
);
emailBody.push(`</table>`);
emailBody.push(`</td>`);
emailBody.push(`</tr>`);
emailBody.push(`</table>`);
emailBody.push(`<b>Thank You</b>`);
emailBody.push(`</body>`);
emailBody.push(`</html>`);
return emailBody.join("");
}
catch (e) {
log.error({
title: 'Error @ Template: ',
details: e.name + ' : ' + e.message
});
}
}
/**
* Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
* @param {Object} inputContext
* @param {boolean} inputContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Object} inputContext.ObjectRef - Object that references the input data
* @typedef {Object} ObjectRef
* @property {string|number} ObjectRef.id - Internal ID of the record instance that contains the input data
* @property {string} ObjectRef.type - Type of the record instance that contains the input data
* @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use in the map/reduce process
* @since 2015.2
*/
const getInputData = (inputContext) => {
try{
var projManagerList = prjManagerSearch()
log.debug("prjManagerSearch: ",projManagerList)
log.debug("prjManagerSearch LENGTH: ",projManagerList.length)
return projManagerList
}
catch (e) {
log.error({
title: "ERROR @ REDUCE",
details: e.name+" : "+e.message
})
}
}
/**
* Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
* automatically when the associated map stage is complete. This function is applied to each group in the provided context.
* @param {Object} reduceContext - Data collection containing the groups to process in the reduce stage. This parameter is
* provided automatically based on the results of the map stage.
* @param {Iterator} reduceContext.errors - Serialized errors that were thrown during previous attempts to execute the
* reduce function on the current group
* @param {number} reduceContext.executionNo - Number of times the reduce function has been executed on the current group
* @param {boolean} reduceContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} reduceContext.key - Key to be processed during the reduce stage
* @param {List<String>} reduceContext.values - All values associated with a unique key that was passed to the reduce stage
* for processing
* @since 2015.2
*/
const reduce = (reduceContext) => {
try{
var json_Result = JSON.parse(reduceContext.values)// Each Project Manger's Value
log.debug("json_Result: ",json_Result)
var project_Search_Result = projectSearch(json_Result.Employee)// List of Projects which has logged user as project Manager
log.debug("project_Search_Result LENGTH: ",project_Search_Result.length)
log.debug("project_Search_Result: ",project_Search_Result)
var timesheet_Id_Array = []
var final_Array = []
if(project_Search_Result.length>0){
var timesheet_Search_Result = timesheetSearch(project_Search_Result)
log.debug("timesheet_Search_Result LENGTH: ",timesheet_Search_Result.length)
if(timesheet_Search_Result.length>0){
log.debug("timesheet_Search_Length: ",timesheet_Search_Result.length)
log.debug("timesheet_Search_Result: ",timesheet_Search_Result)
for(var j=0;j<timesheet_Search_Result.length>0;j++){
if(!timesheet_Id_Array.includes(timesheet_Search_Result[j].timesheetId)) {
timesheet_Id_Array.push(timesheet_Search_Result[j].timesheetId)
final_Array.push({
timesheetId: timesheet_Search_Result[j].timesheetId,
employee: timesheet_Search_Result[j].employee,
employeeTxt: timesheet_Search_Result[j].employeeTxt,
startDate: timesheet_Search_Result[j].startDate
})
}
}
}
}
log.debug("timesheet_Id_Array: ",timesheet_Id_Array)
log.debug("timesheet_Id_Array_Length: ",timesheet_Id_Array.length)
log.debug("final_Array: ",final_Array)
log.debug("final_Array_Lenght: ",final_Array.length)
if(final_Array.length>0) {
var emailBody = mailTemplate(final_Array, final_Array.length, json_Result.EmployeeTxt)
log.debug("emailBody: ", emailBody)
email.send({
author: 55445,
recipients: json_Result.Employee,
subject: "PENDING TIMESHEET DETAILS",
body: emailBody
})
}
}
catch (e) {
log.error({
title: "Error @ Reduce: ",
details: e.name+" : "+e.message
})
}
}
return {getInputData, reduce}
});