/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define(['N/record', 'N/search', 'N/format', 'N/email', 'N/url', 'N/config'],
/**
* @param{record} record
* @param{search} search
* @param{format} format
* @param{email} email
* @param{url} url
* @param{config} config
*/
(record, search, format, email, url, config) => {
/**
* Defines the Sender employee
*/
let SENDER = '21065'
/**
* Function to process Date
* @param {*} dateValue
* @returns
*/
function processDate(dateValue) {
try {
let formatDate = format.format({
value: dateValue,
type: format.Type.DATETIME,
timezone: format.Timezone.GMT
});
let startDateText = formatDate.split(' ')
let processedStartDateText = startDateText[0];
return processedStartDateText;
}
catch (e) {
log.debug("error at ProcessDate Function", e)
return false
}
}
/**
* Function to get time sheets
* @returns {}
*/
function getTimeSheets() {
try {
let timesheetSearchObj = search.create({
type: "timesheet",
filters:
[
// ["timebill.date", "within", "thismonth"],
["timebill.date", "within", "lastmonth"],
"AND",
["timebill.duration", "greaterthan", "0"],
"AND",
["custrecord_jj_employee_type", "isnot", "Consultant"],
],
columns:
[
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "customer",
join: "timeBill",
summary: "GROUP",
label: "Donor"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{timebill.durationdecimal}",
label: "Total Time per month"
}),
search.createColumn({
name: "custcol_jj_regnl_off_ippf_2091",
join: "timeBill",
summary: "GROUP",
label: "Regional Office"
})
]
});
let searchResultCount = timesheetSearchObj.runPaged().count;
if (searchResultCount > 0) {
return timesheetSearchObj
}
else {
return {}
}
}
catch (err) {
log.error("error@getTimeSheets", err)
return {}
}
}
/**
* Function to search the Payroll details to get activity code
* @param {*} employee
* @param {*} project
* @param {*} month
* @param {*} year
* @returns {}
*/
function searchCustomRecordJJPayrollDetails(employee, project, month, year) {
try {
let customrecord_jj_payroll_detailsSearchObj = search.create({
type: "customrecord_jj_payroll_details",
filters:
[
["custrecord_jj_payroll_emp", "anyof", employee],
"AND",
["custrecord_jj_month", "anyof", month],
"AND",
["custrecord_jj_year", "is", year],
"AND",
["isinactive", "is", "F"],
"AND",
["custrecord_jj_project_period.custrecord_jj_project", "anyof", project],
"AND",
["custrecord_jj_project_period.isinactive", "is", "F"]
],
columns:
[
search.createColumn({
name: "custrecord_jj_pro_activity_code",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project Activity Code"
}),
search.createColumn({ name: "custrecord_jj_payroll_emp", label: "Employee" }),
]
});
let activityCodeObj = {};
let searchResultCount = customrecord_jj_payroll_detailsSearchObj.runPaged().count;
if (searchResultCount > 0) {
customrecord_jj_payroll_detailsSearchObj.run().each(function (result) {
let emp = result.getValue({
name: "custrecord_jj_payroll_emp", label: "Employee"
})
activityCodeObj[emp] = result.getValue({
name: "custrecord_jj_pro_activity_code",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project Activity Code"
});
return true
});
return activityCodeObj;
}
else {
return {}
}
}
catch (e) {
log.error("error at searchCustomRecordJJPayrollDetails", e)
return {}
}
}
/**
* Function to get total time tracked by the employee
* @param {*} employee
* @returns
*/
function totalTimeSearch(employee) {
try {
let timesheetSearchObj = search.create({
type: "timesheet",
filters:
[
["timebill.date", "within", "thismonth"],
"AND",
["timebill.duration", "greaterthan", "0"],
"AND",
["custrecord_jj_employee_type", "isnot", "Consultant"],
"AND",
["employee", "anyof", employee]
],
columns:
[
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{timebill.durationdecimal}",
label: "Total Time per month"
}),
]
});
let employeeObject = {};
let searchResultCount = timesheetSearchObj.runPaged().count;
if (searchResultCount > 0) {
timesheetSearchObj.run().each(function (result) {
let employee = result.getValue({
name: "employee",
summary: "GROUP",
label: "Employee"
})
let totalTime = result.getValue({
name: "formulanumeric",
summary: "SUM",
formula: "{timebill.durationdecimal}",
label: "Total Time per month"
})
if (!employeeObject.hasOwnProperty(employee)) {
employeeObject[employee] = Number(totalTime)
}
else {
let temp = Number(employeeObject[employee]) + Number(totalTime)
employeeObject[employee] = temp
}
return true;
});
return employeeObject;
}
else {
return {}
}
}
catch (err) {
log.error("error@getTimeSheets", err)
return {}
}
}
/**
* Function to check the Parent PM approval record is existing
* @param {*} project
* @param {*} month
* @param {*} year
* @returns
*/
function projectInternalId(project, month, year) {
try {
let parentRecordSearch = search.create({
type: "customrecord_jj_pm_approval",
filters:
[
["custrecord_jj_project_name", "is", project],
"AND",
["custrecord_jj_month_pm", "anyof", month],
"AND",
["custrecord_jj_year_pm", "is", year],
],
columns:
[
search.createColumn({
name: "internalid",
})
]
});
let parentRecordSearchResult;
let parentRecordSearchCount = parentRecordSearch.runPaged().count;
if (parentRecordSearchCount > 0) {
parentRecordSearch.run().each(function (result) {
parentRecordSearchResult = result.getValue({
name: "internalid"
})
});
return parentRecordSearchResult;
}
else {
return 0
}
}
catch (e) {
log.error("error at projectInternalId", e)
return 0
}
}
/**
* Function to create the PM Approval record
* @param {*} year
* @param {*} month
* @param {*} projects
* @returns
*/
function pmApprovalRecord(year, month, projects) {
try {
let parentRecord = record.create({
type: 'customrecord_jj_pm_approval'
});
parentRecord.setValue({
fieldId: 'custrecord_jj_year_pm',
value: year
});
parentRecord.setValue({
fieldId: 'custrecord_jj_month_pm',
value: month
});
parentRecord.setValue({
fieldId: 'custrecord_jj_project_name',
value: projects
})
// parentId = parentRecord.save();
let parentId = parentRecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
return parentId
}
catch (err) {
log.error("error@pmApprovalRecord", err)
return 0
}
}
/**
* Function to check the Child Employee monthly time is existing
* @param {*} parentRecordSearchResult
* @param {*} employName
* @param {*} project
* @returns
*/
function getChildRecordInternalId(parentRecordSearchResult, employName, project) {
try {
let customrecord_jj_emp_timeSearchObj = search.create({
type: "customrecord_jj_emp_time",
filters:
[
["custrecord_jj_parent_month", "anyof", parentRecordSearchResult],
"AND",
["custrecord_jj_emp_pm", "anyof", employName],
"AND",
["custrecord_jj_act_project", "anyof", project],
"AND",
["isinactive", "is", "F"]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "custrecord_jj_emp_pm", label: "Employee" })
]
});
let searchResultCount = customrecord_jj_emp_timeSearchObj.runPaged().count;
let childRecord = {};
if (searchResultCount > 0) {
customrecord_jj_emp_timeSearchObj.run().each(function (result) {
let emp = result.getValue({
name: "custrecord_jj_emp_pm", label: "Employee"
})
childRecord[emp] = result.getValue({
name: "internalid", label: "Internal ID"
})
return true;
});
return childRecord
}
else {
return {}
}
} catch (e) {
log.error("Error at getChildRecordInternalId", e);
return {}
}
}
/**
* Function to create the Employee Monthly time records
* @param {*} parentRecord
* @param {*} employees
* @param {*} projects
* @param {*} activityCode
* @param {*} totalTime
*/
function empMonthlyTimeRecord(parentRecord, employees, projects, activityCode, totalTime) {
try {
if (employees.length > 0) {
let approvedTime = approvedTimesPerProject(employees, projects)
let unApprovedTime = unApprovedTimesPerProject(employees, projects)
for (let i = 0; i < employees.length; i++) {
log.debug("activityCode[employees[i]]", activityCode[employees[i]])
let childRecord = record.create({
type: 'customrecord_jj_emp_time'
});
childRecord.setValue({
fieldId: 'custrecord_jj_parent_month',
value: parentRecord
});
childRecord.setValue({
fieldId: 'custrecord_jj_emp_pm',
value: employees[i]
});
childRecord.setValue({
fieldId: 'custrecord_jj_activity_code_pm',
value: activityCode[employees[i]]
});
childRecord.setValue({
fieldId: 'custrecord_jj_time_per_project',
value: totalTime[employees[i]]
});
childRecord.setValue({
fieldId: 'custrecord_jj_act_project',
value: projects
});
childRecord.setValue({
fieldId: 'custrecord_jj_approval_status',
value: 1
});
childRecord.setValue({
fieldId: 'custrecord_jj_total_time',
value: totalTime[employees[i]]
});
if (!approvedTime[employees[i]]) {
let time = 0
childRecord.setValue({
fieldId: 'custrecord_jj_approv_time_per_project',
value: time
});
}
childRecord.setValue({
fieldId: 'custrecord_jj_approv_time_per_project',
value: approvedTime[employees[i]] ? approvedTime[employees[i]] : 0
});
childRecord.setValue({
fieldId: 'custrecord_jj_unapprov_time_per_project',
value: unApprovedTime[employees[i]] ? unApprovedTime[employees[i]] : 0
});
let childId = childRecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
}
}
catch (err) {
log.error("error@empMonthlyTimeRecord", err)
}
}
/**
* Function to get the approved time
* @param {} employees
* @param {*} projects
* @returns
*/
function approvedTimesPerProject(employees, projects) {
try {
let timesheetSearchObj = search.create({
type: "timesheet",
filters:
[
["timebill.date", "within", "thismonth"],
"AND",
["timebill.duration", "greaterthan", "0"],
"AND",
["custrecord_jj_employee_type", "isnot", "Consultant"],
"AND",
["employee", "anyof", employees],
"AND",
["timebill.approvalstatus", "anyof", "3"],
"AND",
["timebill.customer", "anyof", projects],
],
columns:
[
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{timebill.durationdecimal}",
label: "Total Time per month"
}),
]
});
let employeeObject = {};
let searchResultCount = timesheetSearchObj.runPaged().count;
if (searchResultCount > 0) {
timesheetSearchObj.run().each(function (result) {
let employee = result.getValue({
name: "employee",
summary: "GROUP",
label: "Employee"
})
let totalTime = result.getValue({
name: "formulanumeric",
summary: "SUM",
formula: "{timebill.durationdecimal}",
label: "Total Time per month"
})
if (!employeeObject.hasOwnProperty(employee)) {
employeeObject[employee] = Number(totalTime)
}
else {
let temp = Number(employeeObject[employee]) + Number(totalTime)
employeeObject[employee] = temp
}
return true;
});
return employeeObject;
}
else {
return {}
}
}
catch (err) {
log.error("error@approvedTimesPerProject", err)
return {}
}
}
/**
* Function to get the unapproved time
* @param {} employees
* @param {*} projects
* @returns
*/
function unApprovedTimesPerProject(employees, projects) {
try {
let timesheetSearchObj = search.create({
type: "timesheet",
filters:
[
["timebill.date", "within", "thismonth"],
"AND",
["timebill.duration", "greaterthan", "0"],
"AND",
["custrecord_jj_employee_type", "isnot", "Consultant"],
"AND",
["employee", "anyof", employees],
"AND",
["timebill.approvalstatus", "noneof", "3"],
"AND",
["timebill.customer", "anyof", projects],
],
columns:
[
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{timebill.durationdecimal}",
label: "Total Time per month"
}),
]
});
let employeeObject = {};
let searchResultCount = timesheetSearchObj.runPaged().count;
if (searchResultCount > 0) {
timesheetSearchObj.run().each(function (result) {
let employee = result.getValue({
name: "employee",
summary: "GROUP",
label: "Employee"
})
let totalTime = result.getValue({
name: "formulanumeric",
summary: "SUM",
formula: "{timebill.durationdecimal}",
label: "Total Time per month"
})
if (!employeeObject.hasOwnProperty(employee)) {
employeeObject[employee] = Number(totalTime)
}
else {
let temp = Number(employeeObject[employee]) + Number(totalTime)
employeeObject[employee] = temp
}
return true;
});
return employeeObject;
}
else {
return {}
}
}
catch (err) {
log.error("error@approvedTimesPerProject", err)
return {}
}
}
/**
* Function to get the email from project record
* @param {*} project
* @returns
*/
function getPmEmail(project) {
try {
let jobSearchObj = search.create({
type: "job",
filters:
[
["internalid", "anyof", project],
"AND",
["isinactive", "is", "F"]
],
columns:
[
search.createColumn({
name: "altname",
join: "CUSTENTITY_BUDGET_HOLDER",
label: "Name"
}),
search.createColumn({
name: "email",
join: "CUSTENTITY_BUDGET_HOLDER",
label: "Email"
})
]
});
let searchResultCount = jobSearchObj.runPaged().count;
if (searchResultCount > 0) {
let dataObj = {};
jobSearchObj.run().each(function (result) {
dataObj.pmName = result.getValue({
name: "altname", join: "CUSTENTITY_BUDGET_HOLDER", label: "Name"
});
dataObj.pmEmail = result.getValue({
name: "email", join: "CUSTENTITY_BUDGET_HOLDER", label: "Email"
});
return true;
});
return dataObj;
}
else {
return {};
}
}
catch (e) {
log.debug("Error at getPmEmail", e);
return {};
}
}
/**
* Function to send email
*/
function sendEmail(parentRecord, dataObj, link) {
try {
// let link = 'https://4533524-sb1.app.netsuite.com/app/common/custom/custrecordentry.nl?rectype=534&id=9803&compid=4533524_SB1'
// let link = "https://4533524-sb1.app.netsuite.com/app/common/custom/custrecordentry.nl?rectype=534&id="+parentRecord;
let emailBody = `Hi ${dataObj.pmName},<br/><br/>Employee Monthly Time is waiting for your Approval.
Please click on the link below to approve the Employee Monthly Time.<br/><a href=`+ link + ` style="color: blue;"><b>View Record</b></a><br/><br/>`
emailBody += `Kindly check and take necessary action.<br/><br/>`;
emailBody += `Thank you`;
email.send({
author: SENDER,
recipients: dataObj.pmEmail,
subject: 'Employee Monthly Time is subject to your Approval',
body: emailBody
});
}
catch (err) {
log.error("error@SendEmail", err)
}
}
/**
* 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 {
let data = getTimeSheets()
if (Object.keys(data).length > 0) {
return data
}
else {
return {}
}
}
catch (err) {
log.error("error@GetInputData", err)
return {}
}
}
/**
* Defines the function that is executed when the map entry point is triggered. This entry point is triggered automatically
* when the associated getInputData stage is complete. This function is applied to each key-value pair in the provided
* context.
* @param {Object} mapContext - Data collection containing the key-value pairs to process in the map stage. This parameter
* is provided automatically based on the results of the getInputData stage.
* @param {Iterator} mapContext.errors - Serialized errors that were thrown during previous attempts to execute the map
* function on the current key-value pair
* @param {number} mapContext.executionNo - Number of times the map function has been executed on the current key-value
* pair
* @param {boolean} mapContext.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} mapContext.key - Key to be processed during the map stage
* @param {string} mapContext.value - Value to be processed during the map stage
* @since 2015.2
*/
const map = (mapContext) => {
try {
let result = JSON.parse(mapContext.value);
log.debug("result", result)
let employees = result.values["GROUP(employee)"].value
let projects = result.values["GROUP(customer.timeBill)"].value
let employeeObj = {}
let employeeArray = []
employeeObj.emp = employees
employeeArray.push(employeeObj)
mapContext.write({
key: projects,
value: employeeArray
});
}
catch (err) {
log.error("erroe@map", err)
}
}
/**
* 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 {
let projects = reduceContext.key;
log.debug("project", projects)
let reduceValues = reduceContext.values;
// log.debug("reduceValues..................", reduceValues)
let employeeArray = [];
if (reduceValues.length > 0) {
for (let i = 0; i < reduceValues.length; i++) {
let projectObject = {}
let dataValues = JSON.parse(reduceContext.values[i])
projectObject.employee = dataValues[0].emp
projectObject.time = dataValues[0].time
employeeArray.push(projectObject)
}
}
log.debug("employeeArray", employeeArray)
let employees = []
if (employeeArray.length > 0) {
for (let i = 0; i < employeeArray.length; i++) {
employees.push(employeeArray[i].employee)
}
}
let currentDate = new Date();
let finalDate = processDate(currentDate)
let dateArray = finalDate.split("/");
let day = dateArray[0];
let month = dateArray[1];
let year = dateArray[2];
let totalTime = totalTimeSearch(employees)
log.debug("totalTime", totalTime)
// code added for running october data by aswathy on 09/11/2023
month = 10
let activityCode = searchCustomRecordJJPayrollDetails(employees, projects, month, year);
// log.debug("activityCode", activityCode);
let parentRecordSearchResult = projectInternalId(projects, month, year);
log.debug("parentRecordSearchResult", parentRecordSearchResult)
let parentRecord, childRecord
if (parentRecordSearchResult) {
parentRecord = parentRecordSearchResult
}
else if (!parentRecordSearchResult) {
parentRecord = pmApprovalRecord(year, month, projects)
}
log.debug("parentRecord......", parentRecord)
let links = url.resolveRecord({
recordType: 'customrecord_jj_pm_approval',
recordId: parentRecord,
isEditMode: false
});
log.debug("links", links)
let configRecObj = config.load({
type: config.Type.COMPANY_INFORMATION
});
log.debug("configRecObj", configRecObj)
let netSuiteDomain = configRecObj.getValue({ fieldId: 'appurl' });
log.debug("netSuiteDomain", netSuiteDomain)
let newLink = netSuiteDomain + links
log.debug("newLink", newLink)
if (parentRecord) {
let childExist = getChildRecordInternalId(parentRecord, employees, projects)
log.debug("childExist...", childExist);
if (Object.keys(childExist).length < 1) {
log.debug("not existing child..............")
childRecord = empMonthlyTimeRecord(parentRecord, employees, projects, activityCode, totalTime);
let dataObj = getPmEmail(projects)
let emailSend = sendEmail(parentRecord, dataObj, newLink);
}
}
}
catch (err) {
log.error("error@Reduce", err)
}
}
return { getInputData, map, reduce }
});