Scenario:
We can add HTML content in a Email Body and we can use basic operations like looping, if conditions, arithmetic and logical operations in this section. Here Pending Time sheets details are sending to corresponding project managers. The email body contains Time sheet link, and other details including its link to the corresponding time sheets.
Here, after completing each scheduling, a set of emails will send to the corresponding Project manager’s Email account.
//scheduleScript.js
/**
* @NApiVersion 2.1
* @NScriptType ScheduledScript
*/
define(['N/email', 'N/error', 'N/https', 'N/record', 'N/runtime', 'N/search', 'N/file', 'N/task'],
/**
* @param{email} email
* @param{error} error
* @param{https} https
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{file} file
* @param{task} task
*/
(email, error, https, record, runtime, search,file, task) => {
/**
* Function to check whether the field has an empty value or not.
* @param {parameter} parameter - fieldValue
* @returns {boolean} true - if the value is not empty
* @returns {boolean} false - if the value is empty
*/
function checkForParameter(parameter) {
try {
if (parameter != "" && parameter != null && parameter != undefined && parameter != "null" && parameter != "undefined" && parameter != " " && parameter != false) {
return true;
}
else {
return false;
}
}
catch (e) {
log.error({
title: 'Error @ empty check Function: ',
details: e.name + ' : ' + e.message
});
}
}
/**
* Function to create the search of Pending Timesheets.
* @returns {timesheetSearchObj} timesheetSearchObj - searchObject
*/
function timesheetSearch() {
try {
var timesheetSearchObj = search.create({
type: "timesheet",
filters:
[
["custrecord_jj_supervisor_approval_status","anyof","1"],
"AND",
["totalhours","greaterthan","0.0"],
"AND",
["timesheetdate","notbefore","21/2/2022","1/8/2021"]
columns:
[
search.createColumn({
name: "internalid",
summary: "GROUP",
sort: search.Sort.ASC,
label: "INTERNAL ID"
}),
search.createColumn({
name: "customer",
join: "timeBill",
summary: "COUNT",
label: "Client"
})
]
});
return timesheetSearchObj;
}
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"]
],
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 for rescheduling the task.
* @param {startRange} startRange - starting index
* @param {limit} limit - endRange
*/
function rescheduleScriptandReturn(startRange, limit) {
try {
var mrTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscript_jj_ss_scheduledemailforpm",
deploymentId: "customdeploy_jj_ss_scheduledemailforpm",
params: {
custscript_start_range: startRange,
custscript_end_range: limit
// custscript_tot_count: n
}
});
var scriptTaskId = mrTask.submit();
}
catch (err) {
log.error({
title: 'ERROR @ escheduleScriptandReturn: ',
details: err.name + " : " + err.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 {user} user - Name of the Project Manager
* @returns {emailBody} - Email Body Template as String
*/
function mailTemplate(tableContents,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 some 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.timesheetDate}</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.timesheetEmployeeTxt}</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
});
}
}
/**
* Function to process all the core process like scheduling, Iterating search results and Initialising Email sending process
*/
function getData() {
try {
var scriptObj = runtime.getCurrentScript()
var projManagerList = prjManagerSearch()
log.debug("prjManagerSearch: ",projManagerList)
log.debug("prjManagerSearch LENGTH: ",projManagerList.length)
var index;
var limit;
var remainingUsage;
var timesheetRes = []
var searchResult1 = timesheetSearch()
var totalCount1 = searchResult1.runPaged().count;
log.debug("TIMESHEET LENGTH: ",totalCount1)
index = scriptObj.getParameter({ name: "custscript_start_range" }) || 0
limit = scriptObj.getParameter({ name: 'custscript_end_range' })
if(checkForParameter(limit)==false){
if(totalCount1<100){
limit = totalCount1
}
else {
limit = 100
}
}
log.debug("Initial INDEX: ",index)
log.debug("Initial LIMIT: ",limit)
var searchRes1 = searchResult1.run().getRange({
start: index,
end: limit
});
var diff = Number(limit) - Number(index)
log.debug("DIFF: ",diff)
if(diff>0) {
for (var i = 0; i < diff; i++) {
log.debug("AA: ", i)
if (i < Number(diff)) {
let intId = searchRes1[i].getValue({
name: "internalid",
summary: "GROUP",
sort: search.Sort.ASC
})
log.debug("i: ", intId)
if (checkForParameter(intId) == true) {
var prjMngr = []
var timesheetRec = record.load({
type: 'timesheet',
id: intId,
isDynamic: true,
})
var timesheetDate = timesheetRec.getValue({
fieldId: 'trandate'
})
var timesheetEmployee = timesheetRec.getValue({
fieldId: 'employee'
})
var timesheetEmployeeTxt = timesheetRec.getText({
fieldId: 'employee'
})
var timeCount = timesheetRec.getLineCount({
sublistId: 'timeitem'
})
var clientArr = []
if (timeCount > 0) {
for (var j = 0; j < timeCount; j++) {
var clientId = timesheetRec.getSublistValue({
sublistId: 'timeitem',
fieldId: 'customer',
line: j
})
if (!clientArr.includes(clientId)) {
clientArr.push(clientId)
}
}
}
if (clientArr.length > 0) {
log.debug("clientArr of " + intId + " : ", clientArr)
log.debug("clientArr of " + intId + "'s Length : ", clientArr.length)
for (var k = 0; k < clientArr.length; k++) {
var clientRec = record.load({
type: "job",
id: clientArr[k],
isDynamic: true
})
var resourceCount = clientRec.getLineCount({
sublistId: 'jobresources'
})
if (resourceCount > 0) {
for (var l = 0; l < resourceCount; l++) {
var resourceId = clientRec.getSublistValue({
sublistId: 'jobresources',
fieldId: 'jobresource',
line: l
})
var role = clientRec.getSublistValue({
sublistId: 'jobresources',
fieldId: 'role',
line: l
})
if (role == -2) {
if (!prjMngr.includes(resourceId)) {
prjMngr.push(resourceId)
}
}
}
}
}
}
}
timesheetRes.push({
timesheetId: intId,
timesheetDate: timesheetDate,
timesheetEmployee: timesheetEmployee,
timesheetEmployeeTxt: timesheetEmployeeTxt,
prjMngr: prjMngr
})
remainingUsage = scriptObj.getRemainingUsage()
log.debug("REMAINING USAGE: ", remainingUsage)
if (Number(i) == (Number(diff) - Number(1))) {
index = Number(limit)
if ((Number(index) + Number(100)) > totalCount1) {
limit = Number(totalCount1)
} else {
limit = Number(index) + Number(100)
}
rescheduleScriptandReturn(index, limit);
}
}
}
}
if (projManagerList.length > 0) {
for (var i = 0; i < projManagerList.length; i++) {
var emailRes = timesheetRes.filter(res => res.prjMngr.includes(projManagerList[i].Employee))
var emailBody = mailTemplate(emailRes, projManagerList[i].EmployeeTxt)
if (emailRes.length > 0) {
log.debug("emailBody: ", emailBody)
log.debug("EMAIL RES of " + projManagerList[i].Employee + " : ", emailRes)
email.send({
author: 55445,
recipients: projManagerList[i].Employee,
subject: "PENDING TIMESHEET DETAILS",
body: emailBody
})
}
}
}
}
catch (e) {
log.error({
title: 'Error @ getData: ',
details: e.name + ' : ' + e.message
});
}
}
/**
* Defines the Scheduled script trigger point.
* @param {Object} scriptContext
* @param {string} scriptContext.type - Script execution context. Use values from the scriptContext.InvocationType enum.
* @since 2015.2
*/
const execute = (scriptContext) => {
try {
getData() // Main Function
}
catch (e) {
log.error({
title: 'Error @ EXECUTE: ',
details: e.name + ' : ' + e.message
});
}
}
return { execute }
});