Requirement:
Client requires a statement of account for each MA pertaining to the financial year within a specified date range. This statement or certificate is intended to display the payments made to each MA throughout the year. As the MAs have email accounts in NetSuite, client wishes to automate the process of generating these certificates/reports and sending them via email quarterly and at the end of the year. Currently, this task is performed manually outside of the system, resulting in a significant amount of time being consumed. Client aims to implement this automated process for all MAs for every year.
Objective:
The objective of this project is to develop a scheduled functionality to automatically generate and send the MA Grant Certificates to appropriate member associates on quarterly basis on April 20th , July 20th , October 20th and January 20th of every year.
Our Solution:
- We will develop a schedule-based functionality that will be executed once every quarter to automatically send the “Grant Certificates” to the appropriate member associates.
- The execution of our script will be specifically scheduled for the following dates: April 20th, July 20th, October 20th, and January 20th. So, on these dates, the MA Grant Certificate will be sent to corresponding MAs.
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/*******************************************************************************************************
* International Planned Parenthood Federation
*
* IPPF-1957 : MA Grant Certificate
*
* *****************************************************************************************************
*
* Author: Jobin and Jismi IT Services
*
* Date Created : 19-June-2023
*
*Description : This script is to automatically generate and send the Grant Certificates to appropriate
* MAs on quarterly basis.
*
* REVISION HISTORY
*
* @version 1.0 IPPF-1598 : 19-June-2023 : Created the initial build by JJ0152
* @version 2.0 IPPF-1600 : 22-June-2023 : Updated the script to generate the PDF by JJ0169
*
* Revision
*
******************************************************************************************************/
define(['N/email', 'N/file', 'N/search', 'N/render', 'N/format'],
/**
* @param{email} email
* @param{file} file
* @param{search} search
* @param{render} render
* @param{format} format
*/
(email, file, search, render, format) => {
/**
* A const object that stores the static values used in script
*/
const STATICDATA = {
'subsidiary': 2,
'account1': 922,
'account2': 1851,
'january': 1,
'april': 4,
'july': 7,
'october': 10
};
const MONTHDATA = {
1: 'January',
3: 'March',
4: 'April',
6: 'June',
7: 'July',
9: 'September',
10: 'October',
12: 'December'
}
/**
* A const function that lists the functions used in the script
*/
const DATAFUNCTIONS = {
/**
* Function that fetches the payment report for each MA by creating a saved search
* @returns {Object} transactionSearchObj
*/
getMaGrantCertificateData: function () {
try {
//get the date range
let dateRange = DATAFUNCTIONS.getCurrentExecutionMonthAndYear();
if (!dateRange.fromDate || !dateRange.toDate) {
return [];
}
let transactionSearchObj = search.create({
type: "transaction",
filters:
[
["mainline", "any", ""],
"AND",
["subsidiary", "anyof", STATICDATA.subsidiary],
"AND",
["account", "anyof", STATICDATA.account1, STATICDATA.account2],
"AND",
["type", "noneof", "FxReval", "ItemRcpt"],
"AND",
["posting", "is", "T"],
"AND",
[["formulatext: NVL({vendor.category},{vendtype})", "is", "Member Association"], "OR", ["formulatext: NVL({vendor.category},{vendtype})", "is", "Collaborative Partner"]],
"AND",
["formulatext: NVL({vendor.custentity4},{vendorline.custentity4})", "isnotempty", ""],
"AND",
["trandate", "within", dateRange.fromDate, dateRange.toDate]
//"AND",
//["max(formulatext: NVL({vendor.custentity4},{vendorline.custentity4})||' - '||\tNVL({mainname},{name}))", "is", "209 - SUP118 AFGA Afghanistan"] //to be removed
],
columns:
[
search.createColumn({
name: "formulatext1",
summary: "GROUP",
formula: "NVL({department.name},{department.custrecord139})||' - '||SUBSTR({department.custrecord139},0,6)",
label: "References"
}),
search.createColumn({
name: "formulatext2",
summary: "GROUP",
formula: "NVL({vendor.custentity4},{vendorline.custentity4})||' - '|| NVL({mainname},{name})",
label: "MA"
}),
search.createColumn({
name: "postingperiod",
summary: "GROUP",
label: "Period"
}),
search.createColumn({
name: "formulatext3",
summary: "GROUP",
formula: "'US Dollar'",
label: "Fixed Currency"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "CASE WHEN {currency} = 'US Dollar' THEN {fxamount} ELSE {amount} END",
label: "Amount"
}),
search.createColumn({
name: "custcol3",
summary: "GROUP",
label: "Description"
}),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "NVL({mainname},{name})",
label: "MA Name"
})
]
});
//run the search
let searchResultCount = transactionSearchObj.runPaged().count;
if (searchResultCount <= 0) {
return [];
}
let resultSet = transactionSearchObj.run();
let paymentDataArray = [];
let currentRange = resultSet.getRange({
start: 0,
end: 1000
});
let i = 0; // iterator for all search results
let j = 0; // iterator for current result range 0..999
while (j < currentRange.length) {
// take the result row
let result = currentRange[j];
let paymentData = {};
paymentData.reference = result.getValue({
name: "formulatext1",
summary: "GROUP",
formula: "NVL({department.name},{department.custrecord139})||' - '||SUBSTR({department.custrecord139},0,6)",
label: "References"
}).replace(/&/g, ' &');
paymentData.memberAssociate = result.getValue({
name: "formulatext2",
summary: "GROUP",
formula: "NVL({vendor.custentity4},{vendorline.custentity4})||' - '|| NVL({mainname},{name})",
label: "MA"
});
paymentData.period = result.getText({
name: "postingperiod",
summary: "GROUP",
label: "Period"
});
paymentData.currency = 'US Dollar';
paymentData.amount = result.getValue({
name: "formulacurrency",
summary: "SUM",
formula: "CASE WHEN {currency} = 'US Dollar' THEN {fxamount} ELSE {amount} END",
label: "Amount"
});
paymentData.description = result.getValue({
name: "custcol3",
summary: "GROUP",
label: "Description"
}).replace(/&/g, ' &');
paymentData.maName = result.getValue({
name: "formulatext",
summary: "GROUP",
formula: "NVL({mainname},{name})",
label: "MA Name"
}).replace(/&/g, ' &');;
paymentData.toDate = dateRange.toDate;
paymentDataArray.push(paymentData);
i++; j++;
if (j == 1000) { // check if it reaches 1000
j = 0; // reset j and reload the next portion
currentRange = resultSet.getRange({
start: i,
end: i + 1000
});
}
}
return paymentDataArray;
} catch (e) {
log.error("Error @ getMaGrantCertificateData", e.message);
return [];
}
},
/**
* Function to get the daterange using the current execution date. If current month is April, then the date range should be jan 1 of current year to March 31st
* If July, daterange: Jan1 - June30, October : Jan1 - Sept 30, January : Jan1 - Dec 31st of previous year
* @returns {Object}
*/
getCurrentExecutionMonthAndYear: function () {
try {
let fromDate, toDate, today, month, year;
today = new Date();
month = today.getMonth() + 1;
month = 1; //change*
year = today.getFullYear();
fromDate = "1/1/" + year; //the current account date format is dd/mm/yyy. We assume that IPPF will not change the date format as this will affect other deployed functionalities.
switch (month) {
case STATICDATA.april:
toDate = "31/3/" + year;
break;
case STATICDATA.july:
toDate = "30/6/" + year;
break;
case STATICDATA.october:
toDate = "30/9/" + year;
break;
case STATICDATA.january:
toDate = "31/12/" + Number(year - 1);
fromDate = "1/1/" + Number(year - 1);
break;
default:
toDate = '';
break;
}
return { 'fromDate': fromDate, 'toDate': toDate };
} catch (e) {
log.error("Error @ getCurrentExecutionMonth", e.message);
return { 'fromDate': '', 'toDate': '' };
}
},
/**
* Function to generate MA Grant Certificate in PDF format
* @param {Object} resultObj
* @returns {object}
*/
generateMaGrantCertificate: function (resultObj) {
try {
//loading the xml file and getting the contents
let dataHtml = file.load({ id: 415175 }).getContents();
let renderer = render.create();
renderer.templateContent = dataHtml;
// let maPaymentDetailsJson = JSON.stringify(maPaymentDetails);
let templateFile = renderer.addCustomDataSource({
format: render.DataSource.OBJECT,
alias: 'resultObj',
data: resultObj
});
let renderData = renderer.renderAsPdf();
renderData.name = "Grant_Certificate.pdf";
return { 'status': 'success', 'data': renderData };
// return renderData;
} catch (e) {
log.error("Error @ generateMaGrantCertificate", e.message);
return { 'status': 'failure', 'data': e.message };
}
},
/**
* Function to send the generated MA grant certificates in PDF format to respective MAs
* @param {Number} template
* @param {String} maName
* @param {String} date
* @returns {}
*/
sendMaGrantCertificate: function (template, maName, year) {
try {
// if MA name is empty
//identify the MA email id using MA name
let supplierEmail = this.getSupplierMaEmailAddress(maName);
if (supplierEmail.status === 'failure') {
return supplierEmail; //return the failure status and error message
}
else if (supplierEmail.status === 'success' && !supplierEmail.data.email) {
return { 'status': 'failure', 'data': 'No email id is available for the supplier ' + maName }
}
let emailSubject = 'Grant Certificate ' + year;
let emailBody = supplierEmail.data.name + '<br>For the urgent attention of the Executive Director.' +
'The ' + year + ' cash and commodity grant certificate for your organization is attached.' +
'<br>Please agree the information given by signing the document and returning by email to your Regional Office and copying IPPF London.' +
'<br>Your prompt action would be most appreciated. <br><br>Thank you.';
email.send({
author: 10308,
recipients: [supplierEmail.data.email, 'JVenn@ippf.org', 'sjain@ippf.org'],
subject: emailSubject,
body: emailBody,
attachments: [template],
relatedRecords: {
entityId: supplierEmail.data.internalId
}
});
return { 'status': 'success', 'data': '' };
} catch (e) {
log.error("Error @ sendMaGrantCertificate", e.message);
return { 'status': 'failure', 'data': e.message };
}
},
/**
* Function to get the suppliers emailid, name and internal id using its entity id
* @param {String} maName
* @returns {Object}
*/
getSupplierMaEmailAddress: function (maName) {
try {
let vendorSearchObj = search.create({
type: "vendor",
filters:
[
["entityid", "is", maName],
"AND",
["isinactive", "is", "F"]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "email", label: "Email" }),
search.createColumn({ name: "companyname", label: "Company Name" })
]
});
let searchResultCount = vendorSearchObj.runPaged().count;
let supplierDetailObj = {};
if (searchResultCount > 0) {
vendorSearchObj.run().each(function (result) {
supplierDetailObj.email = result.getValue({ name: "email", label: "Email" }) || false;
supplierDetailObj.name = result.getValue({ name: "companyname", label: "Company Name" }) || false;
supplierDetailObj.internalId = result.getValue({ name: "internalid", label: "Internal ID" });
return false;
});
return { 'status': 'success', 'data': supplierDetailObj };
} else {
return { status: 'failure', data: 'There is no supplier with the given supplier id: ' + maName };
}
} catch (e) {
log.error("Error @ getSupplierMaEmailAddress", e.message);
return { status: 'failure', data: e.message };
}
},
/**
* Function to group objects in a given array in based on a particular key in the objects
* @param {Array} mapArray
* @returns
*/
groupObjectsByReference: function (mapArray) {
try {
let groupedObjects = {};
mapArray.forEach(maObj => {
let reference = maObj.reference;
if (groupedObjects.hasOwnProperty(reference)) {
//calculate the sum of values of key 'amount' of all objects having similar value for key 'reference' to print the subtotal in pdf
groupedObjects[reference].amount = (Number(groupedObjects[reference].amount) + Number(maObj.amount)).toFixed(2);
groupedObjects[reference].objects.push(maObj);
} else {
groupedObjects[reference] = {
reference: reference,
amount: Number(maObj.amount).toFixed(2),
objects: [maObj]
};
}
});
return { 'status': 'success', 'data': Object.values(groupedObjects) };
} catch (e) {
log.error("Error @ groupObjectsByReference", e.message);
return { 'status': 'failure', 'data': e.message };
}
},
/**
* Function to get the required body data to set up the PDF
* @param {Array} maArray
* @returns {Array}
*/
getBodyDataForPdf: function (toDate, maName) {
try {
let today = new Date('1/20/2023'); //change*
let pdfBodyLevelData = {};
let dateSplit = toDate.split('/');
let monthName = MONTHDATA[dateSplit[1]];
pdfBodyLevelData.currentDate = this.getCurrentDate(today);
pdfBodyLevelData.certificateYear = ((today.getMonth() + 1) == 1) ? (today.getFullYear() - 1) : today.getFullYear();
pdfBodyLevelData.maName = maName;
pdfBodyLevelData.fromDateString = '1 January';
pdfBodyLevelData.toDateString = dateSplit[0] + ' ' + monthName + ' ' + dateSplit[2];
pdfBodyLevelData.monthAndYear = monthName + ' ' + dateSplit[2];
pdfBodyLevelData.paymentYear = today.getFullYear();
pdfBodyLevelData.dateAfterTenDays = this.calculateDateAfterTenDays(today)
log.debug('pdfBodyLevelData', pdfBodyLevelData)
return { 'status': 'success', 'data': pdfBodyLevelData };
} catch (e) {
log.error("Error @ getBodyDataForPdf", e.message);
return { 'status': 'failure', 'data': e.message };
}
},
/**
* Function to get the current date in DD/MM/YYYY format
* @param {String} today
* @returns {string}
*/
getCurrentDate: function (today) {
try {
let day = String(today.getDate()).padStart(2, '0');
let month = String(today.getMonth() + 1).padStart(2, '0');
let year = today.getFullYear();
return day + '/' + month + '/' + year;
} catch (e) {
log.error('Error @ getCurrentDate', e.message)
return false;
}
},
/**
* Function to calculate the date after 10 days of the given date
* @param {String} date
* @returns {string}
*/
calculateDateAfterTenDays(date) {
try {
let day = parseInt(date.getDate());
let month = parseInt(date.getMonth());
let year = parseInt(date.getFullYear());
let currentDate = new Date(year, month, day);
currentDate.setDate(currentDate.getDate() + 10);
let newDay = currentDate.getDate();
let newMonth = currentDate.getMonth() + 1; // Adding 1 to get the correct month
let newYear = currentDate.getFullYear();
// Formatting the date with leading zeros if needed
let formattedDate = newDay + ' ' + MONTHDATA[newMonth] + ' ' + newYear;
return formattedDate;
} catch (e) {
log.error('Error @ calculateDateAfterTenDays', e.message)
return false;
}
},
/**
* Function to create a CSV file for errors
* @param {Array} errorsArray
*/
createFileForErrors: function (errorsArray) {
try {
let titleArray = ["MA Supplier", "Reason"];
let csvFileData = titleArray.toString() + '\r\n';
for (let i in errorsArray) {
csvFileData += errorsArray[i].ma + ',' + errorsArray[i].reason;
csvFileData += '\r\n';
}
//filename
let today = new Date(), dd = String(today.getDate()).padStart(2, '0'), mm = String(today.getMonth() + 1).padStart(2, '0'), yyyy = today.getFullYear();
let todayDate = mm + '-' + dd + '-' + yyyy;
let createdFile = file.create({
name: 'error_' + todayDate + '.csv',
fileType: file.Type.CSV,
contents: csvFileData,
description: 'Error information',
folder: 169917,
encoding: file.Encoding.UTF8
});
createdFile.save();
} catch (e) {
log.error('Error @ createFileForErrors', 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 {
//The script should continue execution only if the current month is any of April, July, October or January
// let currentMonth = new Date().getMonth() + 1
// if (currentMonth !== 1 && currentMonth !== 4 && currentMonth !== 7 && currentMonth !== 10) {
// return false;
// }
return DATAFUNCTIONS.getMaGrantCertificateData(); // validation before return data // check condition type equalto array
} catch (e) {
log.error("Error @ getInputData", e.message);
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 dataResult = JSON.parse(mapContext.value).memberAssociate;
// after parse the result then get MA associaite separate the line.
//Emit the key-value pair to be passed to the reduce stage
mapContext.write({
key: dataResult,
value: mapContext.value
});
} catch (e) {
log.error("Error @ map", e.message);
return {};
}
}
/**
* 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) => {
let maName;
try {
let maPaymentDetails = [];
// convert each elemet in reduceContext.values to object
for (let key in reduceContext.values) {
maPaymentDetails.push(JSON.parse(reduceContext.values[key]));
}
//If maName is empty, then write an error message to summarize()
maName = maPaymentDetails[0].maName ? maPaymentDetails[0].maName : '';
if (!maName) {
reduceContext.write({
key: 'errors',
value: { 'ma': maName, 'reason': 'Member associate name is not available' }
});
return false;
}
//toDate cannot be empty as it's todays date
let toDate = maPaymentDetails[0].toDate;
//group the objects of array based on similar key 'reference' and also calculate the sum of key 'amount' for these objects
maPaymentDetails = DATAFUNCTIONS.groupObjectsByReference(maPaymentDetails);
if ((maPaymentDetails.status == 'failure')) {
reduceContext.write({
key: 'errors',
value: { 'ma': maName, 'reason': maPaymentDetails.data }
});
return false;
}
//set the data required for pdf body
let maPaymentBodyDetails = DATAFUNCTIONS.getBodyDataForPdf(toDate, maName);
if ((maPaymentBodyDetails.status == 'failure')) {
reduceContext.write({
key: 'errors',
value: { 'ma': maName, 'reason': maPaymentBodyDetails.data }
});
return false;
}
//pass the MA details to a function to create PDF using these data
let resultObj = {pdfBodyData: maPaymentBodyDetails.data, pdfLineData: maPaymentDetails.data};
let pdfTemplate = DATAFUNCTIONS.generateMaGrantCertificate(resultObj);
//send email to respective MAs if PDF template is generated
if (pdfTemplate.status == 'success') {
let sendEmail = DATAFUNCTIONS.sendMaGrantCertificate(pdfTemplate.data, maName, maPaymentBodyDetails.data.certificateYear)
if (sendEmail.status == 'failure') {
reduceContext.write({
key: 'errors',
value: { 'ma': maName, 'reason': sendEmail.data }
});
}
} else {
reduceContext.write({
key: 'errors',
value: { 'ma': maName, 'reason': pdfTemplate.data }
});
}
} catch (e) {
log.error("Error @ reduce", e.message);
reduceContext.write({
key: 'errors',
value: { 'ma': maName, 'reason': e.message }
});
}
}
/**
* Defines the function that is executed when the summarize entry point is triggered. This entry point is triggered
* automatically when the associated reduce stage is complete. This function is applied to the entire result set.
* @param {Object} summaryContext - Statistics about the execution of a map/reduce script
* @param {number} summaryContext.concurrency - Maximum concurrency number when executing parallel tasks for the map/reduce
* script
* @param {Date} summaryContext.dateCreated - The date and time when the map/reduce script began running
* @param {boolean} summaryContext.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 {Iterator} summaryContext.output - Serialized keys and values that were saved as output during the reduce stage
* @param {number} summaryContext.seconds - Total seconds elapsed when running the map/reduce script
* @param {number} summaryContext.usage - Total number of governance usage units consumed when running the map/reduce
* script
* @param {number} summaryContext.yields - Total number of yields when running the map/reduce script
* @param {Object} summaryContext.inputSummary - Statistics about the input stage
* @param {Object} summaryContext.mapSummary - Statistics about the map stage
* @param {Object} summaryContext.reduceSummary - Statistics about the reduce stage
* @since 2015.2
*/
const summarize = (summaryContext) => {
try {
let errorArray = [];
summaryContext.output.iterator().each(function (key, value) {
if ((key == "errors") && JSON.parse(value)) {
errorArray.push(JSON.parse(value));
}
return true;
});
//if there are errors, then store those errors in a CSV file and save in filecabinet
if (errorArray.length > 0) {
DATAFUNCTIONS.createFileForErrors(errorArray);
}
} catch (e) {
log.error('Error @ summarize', e.message);
}
}
return { getInputData, map, reduce, summarize }
});