Client requirement:
The client would like to automatically generate commission reports for their sales reps and email them to respective sales reps once a month
- User Event script to display a button to review the commission report for the specified month:
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/error', 'N/search'], (error, search) => {
"use strict";
const CLIENT_SCRIPT_ID = "YOUR_CLIENT_SCRIPT_FILE_PATH"
/************ Before Load *************/
const beforeLoad = (scriptContext) => {
let newRecord = scriptContext.newRecord;
let newId = scriptContext.newRecord.id;
try {
let form = scriptContext.form;
form.clientScriptFileId = CLIENT_SCRIPT_ID;
if (scriptContext.type == ('view')) {
let statusField = search.lookupFields({
type: 'customrecord_jj_cr_commisn_rpt_cdus_3317',
id: newId,
columns: ['custrecord_jj_cr_currentstatus_cdus_3317']
}).custrecord_jj_cr_currentstatus_cdus_3317;
log.debug("status", statusField);
if (statusField == "Pending") {
form.addButton({
id: 'custpage_jj_cb_view_commission_report',
label: 'Review Commission Report',
functionName: 'prepareCommissionReport(' + newId + ')'
});
}
else {
form.removeButton("custpage_jj_cb_view_commission_report");
form.removeButton("edit");
}
}
else if (scriptContext.type == 'create') {
newRecord.setValue({
fieldId: "custrecord_jj_cr_currentstatus_cdus_3317",
value: "Pending"
});
}
else if (scriptContext.type == 'copy') {
newRecord.setValue({
fieldId: "custrecord_jj_cr_from_date_cdus_3317",
value: null
});
newRecord.setValue({
fieldId: "custrecord_jj_cr_to_date_cdus_3317",
value: null
});
newRecord.setValue({
fieldId: "custrecord_jj_cr_currentstatus_cdus_3317",
value: "Pending"
});
}
else if (scriptContext.type == 'edit') {
let statusField = search.lookupFields({
type: 'customrecord_jj_autoclose_to_swft430',
id: newId,
columns: ['custrecord_jj_status']
}).custrecord_jj_status;
log.debug("status", statusField);
if (statusField != "Pending") {
let customError = error.create({
name: 'EDIT_IS_NOT_POSSIBLE',
message: "Execution for this entry is currently processing. You cannot edit this entry anymore. To execute an automated commission report for another month, kindly create a new entry. Please note that a new 'Commission Report' request should not be submitted again for the month for which the current execution is ongoing until it is completed.",
notifyOff: false
});
throw customError;
}
}
}
catch (err) {
log.debug({
title: 'Error @beforeLoad',
details: err.message
});
}
};
return { beforeLoad }
});
2: Client script to perform the button action and to call the suitelet that displays the commission report
/**
* @NApiVersion 2.1
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(['N/url', 'N/search'], function (url, search) {
"use strict";
function pageInit(scriptContext) {
}
function saveRecord(scriptContext) {
let currentRecord = scriptContext.currentRecord;
let fromDate = currentRecord.getValue({
fieldId: 'custrecord_jj_cr_from_date_cdus_3317'
});
let toDate = currentRecord.getValue({
fieldId: 'custrecord_jj_cr_to_date_cdus_3317'
});
if (fromDate && toDate && (new Date(fromDate) > new Date(toDate))) {
alert("Record cannot be saved; 'To Date' should be greater than or equal to 'From Date'.");
return false; // prevent record save
}
return true; // allow record save
}
/**
* Function that performs the button action.
*/
function prepareCommissionReport(curId) {
try {
//check if the MR script is currently executing
let searchObj = search.create({
type: "customrecord_jj_cr_commisn_rpt_cdus_3317",
filters:
[
["custrecord_jj_cr_currentstatus_cdus_3317","is","Processing"]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
]
});
let searchResultCount = searchObj.runPaged().count;
if (searchResultCount > 0) {
alert ("The script for the automated commission report is currently in progress. Please attempt to initiate another one after some time.");
return false;
}
let suiteltUrl = url.resolveScript({
scriptId: "customscript_jj_sl_commission_report",
deploymentId: "customdeploy_jj_sl_commission_report",
params: { "commissionId": curId }
});
window.open(suiteltUrl, "_blank", "width=1000, height=800");
} catch (err) {
log.error('error @ProcessReturnRecords', err.message);
}
}
return {
pageInit: pageInit,
saveRecord: saveRecord,
prepareCommissionReport: prepareCommissionReport,
};
});
3: Suitelet to display the commission report
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
define(['N/task', 'N/error', 'N/record', 'N/ui/serverWidget', 'N/search', 'N/currentRecord', 'N/file'], (task, error, record, serverWidget, search, currentRecord, file) => {
"use strict";
const onRequest = (scriptContext) => {
try {
let resultCount = null;
let form = serverWidget.createForm({
title: 'Commission Report'
});
let commissionRecEntry = scriptContext.request.parameters.commissionId;
if (scriptContext.request.method === 'GET') {
let orderId = form.addField({
id: "_jj_orderid",
type: serverWidget.FieldType.TEXT,
label: "Order Id",
});
orderId.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
let sublist = form.addSublist({
id: '_jj_sublist',
type: serverWidget.SublistType.LIST,
label: 'View Invoices'
});
sublist.addField({
id: 'custrecord_jj_tick',
type: serverWidget.FieldType.CHECKBOX,
label: 'Select'
});
sublist.addField({
id: 'custrecord_jj_sales_rep_id',
type: serverWidget.FieldType.TEXT,
label: 'Sales Rep ID'
});
sublist.addField({
id: 'custrecord_jj_sales_rep',
type: serverWidget.FieldType.TEXT,
label: 'Sales Rep'
});
sublist.addField({
id: 'custrecord_jj_commission_amount',
type: serverWidget.FieldType.TEXT,
label: 'Commission Amount',
align: serverWidget.LayoutJustification.RIGHT
});
sublist.addMarkAllButtons();
form.addSubmitButton({
label: 'Execute Commission Report'
});
log.debug("commissionRecEntry", commissionRecEntry);
let dateFilter = search.lookupFields({
type: 'customrecord_jj_cr_commisn_rpt_cdus_3317',
id: commissionRecEntry,
columns: ['custrecord_jj_cr_from_date_cdus_3317', 'custrecord_jj_cr_to_date_cdus_3317']
})
let fromDate = dateFilter.custrecord_jj_cr_from_date_cdus_3317
let toDate = dateFilter.custrecord_jj_cr_to_date_cdus_3317
log.debug("fromDate", fromDate)
log.debug("toDate", toDate)
let invoiceSearchObj = search.create({
type: "invoice",
filters:
[
["type", "anyof", "CustInvc"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["cogs", "is", "F"],
"AND",
["item.type", "anyof", "InvtPart", "Kit"],
"AND",
["status", "anyof", "CustInvc:B"],
"AND",
["amount", "greaterthan", "0.00"],
"AND",
["custbody_jj_sent_commission_email", "is", "F"],
"AND",
["salesrep", "noneof", "@NONE@"],
"AND",
["custbody_jj_inv_closeddate", "within", fromDate, toDate]
],
columns:
[
search.createColumn({
name: "salesrep",
summary: "GROUP",
sort: search.Sort.DESC,
label: "Sales Rep"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "Case When FLOOR(TO_DATE({custbody_jj_inv_closeddate})-TO_DATE({datecreated}))>50 Then (NVL({amount},0)-NVL({discountamount},0))*0.05 Else (NVL({amount},0)-NVL({discountamount},0))*0.06 End",
label: "Total Commission"
})
]
});
resultCount = invoiceSearchObj.runPaged().count;
log.debug("invoiceSearchObj result count", resultCount);
if (resultCount > 0) {
let arr = [];
let obj;
let searchResult = invoiceSearchObj.run();
searchResult.each(function (result) {
obj = {};
obj.salesRep = result.getText({
name: "salesrep",
summary: "GROUP",
sort: search.Sort.DESC,
label: "Sales Rep"
});
obj.salesRepId = result.getValue({
name: "salesrep",
summary: "GROUP",
sort: search.Sort.DESC,
label: "Sales Rep"
});
obj.commissionAmount = result.getValue({
name: "formulacurrency",
summary: "SUM",
formula: "Case When FLOOR(TO_DATE({custbody_jj_inv_closeddate})-TO_DATE({datecreated}))>50 Then (NVL({amount},0)-NVL({discountamount},0))*0.05 Else (NVL({amount},0)-NVL({discountamount},0))*0.06 End",
label: "Total Commission"
});
arr.push(obj);
return true;
});
log.debug("invoiceSearchObj result ", arr);
orderId.defaultValue = commissionRecEntry;
//Rendering the array to sublist
for (let j = 0; j < arr.length; j++) {
sublist.setSublistValue({
id: 'custrecord_jj_sales_rep_id',
line: j,
value: arr[j].salesRepId
});
sublist.setSublistValue({
id: 'custrecord_jj_sales_rep',
line: j,
value: arr[j].salesRep
});
sublist.setSublistValue({
id: 'custrecord_jj_commission_amount',
line: j,
value: arr[j].commissionAmount
});
}
}
else {
log.debug("no commission");
}
scriptContext.response.writePage(form);
}
//Post action of Suitelet
else {
let commissionRecEntry = scriptContext.request.parameters._jj_orderid;
log.debug("auto-receiveid in post action", commissionRecEntry);
let lineCount = scriptContext.request.getLineCount('_jj_sublist');
log.debug("count", lineCount);
let contentArray = [];
if (lineCount > 0) {
let titleArray = ["Sales Representative", "Commission Amount"];
let csvFileData = titleArray.toString() + 'rn';
let ifChecked = false;
for (let i = 0; i < lineCount; i++) {
let tempObj = {};
let sublistFieldValue = scriptContext.request.getSublistValue({
group: '_jj_sublist',
name: 'custrecord_jj_tick',
line: i
});
log.debug("checkbox value", sublistFieldValue);
if (sublistFieldValue == "T") {
ifChecked = true;
tempObj.salesRep = scriptContext.request.getSublistValue({
group: '_jj_sublist',
name: 'custrecord_jj_sales_rep_id',
line: i
});
tempObj.commissionAmount = scriptContext.request.getSublistValue({
group: '_jj_sublist',
name: 'custrecord_jj_commission_amount',
line: i
});
contentArray.push(tempObj);
}
}
let content = JSON.stringify(contentArray);
log.debug("content", content);
if (ifChecked == true) {
let fileObj = file.create({
name: `commission_report_${commissionRecEntry}_${new Date().getTime}.txt`,
fileType: file.Type.PLAINTEXT,
contents: content,
folder: 'your_file_id"
});
let savedFile = fileObj.save();
//Invoking Map/Reduce Script to convert the text file of selected transfer orders into item Receipt.
let mapReduceScriptId = 'your_mr_script_script_id';
let mapReduceDeployId = 'your_mr_script_script_deployment_id';
let mrTask = task.create({
taskType: task.TaskType.MAP_REDUCE,
scriptId: mapReduceScriptId,
deploymentId: mapReduceDeployId,
params: {
custscript_jj_selected_commissions: savedFile,
custscript_jj_custom_record_id: commissionRecEntry
}
});
let mrTaskId = mrTask.submit()
let taskStatus = task.checkStatus(mrTaskId);
log.debug("Map Reduce Status", taskStatus.status);
record.submitFields({
type: 'customrecord_jj_cr_commisn_rpt_cdus_3317',
id: commissionRecEntry,
values: {
'custrecord_jj_cr_currentstatus_cdus_3317': "Processing"
},
options: {
enableSourcing: false,
ignoreMandatoryFields: true
}
});
}
}
else {
let htmlCode = `<html><body><script type="text/javascript">window.opener.location.reload(); window.close()</script></body></html>`;
scriptContext.response.write(htmlCode);
return false;
}
let htmlCode = `<html><body><script type="text/javascript">window.opener.location.reload(); window.close()</script></body></html>`;
scriptContext.response.write(htmlCode);
}
}
catch (e) {
log.error("error@request function", e);
}
}
return { onRequest }
});
4: Map/Reduce script to send automated commission report emails to sales reps:
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define(['N/email', 'N/file', 'N/record', 'N/runtime', 'N/search', 'N/task'],
/**
* @param{email} email
* @param{file} file
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{task} task
*/
(email, file, record, runtime, search, task) => {
let fileArray = [];
/**
* Function to wrap a value in double quotes
* @param {*} value
* @returns
*/
function wrapInDoubleQuotes(value) {
return `"${value.replace(/"/g, '""')}"`; // Double up any double quotes within the value
}
/**
* Defines the function to load invoice search based on sales rep and given date range
* @param {spiff} spiff persons internal id
* @param {salesrep} salesrep internal id
* @param {spiffAmnt} spiffAmnt spiff amount
* @return {Array} filteredArray
*/
function invoiceSearchUsingCommission(dataObj) {
try {
let titleArray = ["SALES REP", "INVOICE DATE", "INVOICE CLOSED DATE", "INVOICE NUMBER", "SALES ORDER #", "PO #", "COMMISSION AMOUNT OF ITEM", "ITEM AMOUNT", "ITEM"];
let csvFileData = titleArray.toString() + 'rn';
let customRecordId = runtime.getCurrentScript().getParameter({ name: 'custscript_jj_custom_record_id' });
let dateFilter = search.lookupFields({
type: 'customrecord_jj_cr_commisn_rpt_cdus_3317',
id: customRecordId,
columns: ['custrecord_jj_cr_from_date_cdus_3317', 'custrecord_jj_cr_to_date_cdus_3317']
});
let fromDate = dateFilter.custrecord_jj_cr_from_date_cdus_3317;
let toDate = dateFilter.custrecord_jj_cr_to_date_cdus_3317;
log.debug("Date Range", { fromdate: fromDate, toDate: toDate });
let invoiceSearchObj = search.create({
type: "invoice",
filters:
[
["type", "anyof", "CustInvc"],
"AND",
["status", "anyof", "CustInvc:B"],
"AND",
["totalamount", "notequalto", "0.00"],
"AND",
["item.type", "anyof", "InvtPart", "Kit"],
"AND",
["formulanumeric: Case When FLOOR(TO_DATE({custbody_jj_inv_closeddate})-TO_DATE({datecreated}))>50 Then (NVL({amount},0)-ABS(NVL({discountamount},0)))*0.05 Else (NVL({amount},0)-ABS(NVL({discountamount},0)))*0.06 End", "greaterthan", "0"],
"AND",
["cogs", "is", "F"],
"AND",
["custbody_jj_inv_closeddate", "within", fromDate, toDate],
"AND",
["custbody_jj_sent_commission_email", "is", "F"],
"AND",
["salesrep.internalid", "anyof", dataObj.salesRep],
],
columns:
[
search.createColumn({
name: "salesrep",
sort: search.Sort.DESC,
label: "Sales Rep"
}),
search.createColumn('internalid'),
search.createColumn({ name: "trandate", label: "Invoice Date" }),
search.createColumn({ name: "closedate", label: "Paid in Full Date" }),
search.createColumn({ name: "tranid", label: "Invoice Number" }),
search.createColumn({ name: "createdfrom", label: "Sales Order #" }),
search.createColumn({ name: "otherrefnum", label: "PO #" }),
search.createColumn({
name: "formulanumeric",
formula: "Case When FLOOR(TO_DATE({custbody_jj_inv_closeddate})-TO_DATE({datecreated}))>50 Then (NVL({amount},0)-ABS(NVL({discountamount},0)))*0.05 Else (NVL({amount},0)-ABS(NVL({discountamount},0)))*0.06 End",
label: "Commission"
}),
search.createColumn({ name: "amount", label: "Invoice Amount" }),
search.createColumn({ name: "item", label: "Item" })
]
});
let salesRep, date, invNumber, salesOrder, po, commissionAmntItem, invAmnt, item, internalid, closedDate;
let invoiceArr = [];
let searchResultCount = invoiceSearchObj.runPaged().count;
log.debug("invoiceSearchObj result count", searchResultCount);
if (searchResultCount > 0) {
invoiceSearchObj.run().each(function (result) {
//push invoice internal id to an array
internalid = result.getValue('internalid');
invoiceArr.push(internalid);
salesRep = result.getText({
name: "salesrep",
sort: search.Sort.DESC,
label: "Sales Rep"
});
date = result.getValue({ name: "trandate", label: "Invoice Date" });
closedDate = result.getValue({ name: "closedate", label: "Paid in Full Date" });
invNumber = result.getValue({ name: "tranid", label: "Invoice Number" });
salesOrder = result.getText({ name: "createdfrom", label: "Sales Order #" });
po = result.getValue({ name: "otherrefnum", label: "PO #" });
commissionAmntItem = result.getValue({
name: "formulanumeric",
formula: "Case When FLOOR(TO_DATE({custbody_jj_inv_closeddate})-TO_DATE({datecreated}))>50 Then (NVL({amount},0)-ABS(NVL({discountamount},0)))*0.05 Else (NVL({amount},0)-ABS(NVL({discountamount},0)))*0.06 End",
label: "Commission"
});
invAmnt = result.getValue({ name: "amount", label: "Invoice Amount" })
item = result.getText({ name: "item", label: "Item" })
//append to csv file
csvFileData += wrapInDoubleQuotes(salesRep) + ',' + wrapInDoubleQuotes(date) + ',' + wrapInDoubleQuotes(closedDate) + ',' + invNumber + ',' + salesOrder + ',' + wrapInDoubleQuotes(po) + ',' + commissionAmntItem + ',' + invAmnt + ',' + wrapInDoubleQuotes(item) + ',';
csvFileData += 'rn';
return true;
});
csvFileData += ',' + ',' + ',' + ',' + ',' + 'TOTAL COMMISSION AMOUNT' + ',' + Number(dataObj.commissionAmount).toFixed(2);
log.debug("csv file data", csvFileData)
//filename
let today = new Date();
let dd = String(today.getDate()).padStart(2, '0');
let mm = String(today.getMonth() + 1).padStart(2, '0');
let yyyy = today.getFullYear();
today = mm + '-' + dd + '-' + yyyy;
let fileObj = file.create({
name: 'Commission report:' + today + '.csv',
fileType: file.Type.CSV,
contents: csvFileData,
encoding: file.Encoding.UTF8,
});
let repEmail = search.lookupFields({
type: search.Type.EMPLOYEE,
id: dataObj.salesRep,
columns: ['email']
}).email;
let emailSent = false;
let salesrepEmail = [];
if (repEmail) {
//send email
email.send({
author: 2528,
recipients: dataObj.salesRep,
subject: 'Commission Corp Design',
body: 'We LOVE giving back to celebrate you and your success with Corp Design. Below is the Commission report representing you hard work.' + 'n' + 'Thank you for your support of Corp Design – it’s such an enjoyment to celebrate you!' + 'n' + 'Corp Design',
attachments: [fileObj]
});
log.debug("Email sent")
emailSent = true;
}
if (emailSent) {
//keep only unique id's in invoiceArr array
log.debug("invoiceArr", invoiceArr)
invoiceArr = invoiceArr.filter(function (item, index, inputArray) {
return inputArray.indexOf(item) == index;
});
log.debug("unique array", invoiceArr)
//load each invoice and check its sent spiff report email check box
for (let i = 0; i < invoiceArr.length; i++) {
try {
record.submitFields({
type: 'invoice',
id: invoiceArr[i],
values: {
'custbody_jj_sent_commission_email': true
},
options: {
enableSourcing: false,
ignoreMandatoryFields: true
}
});
} catch (e) {
log.debug("Error", e)
break;
}
}
}
}
} catch (e) {
log.debug("Error@invoiceSearchUsingSpiff", e)
}
}
/**
* 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 scriptObj = runtime.getCurrentScript();
let selectedCommissions = scriptObj.getParameter({ name: 'custscript_jj_selected_commissions' });
let toFile = file.load({ id: selectedCommissions });
let fileContent = toFile.getContents();
let contentArray = JSON.parse(fileContent);
return contentArray;
} catch (e) {
log.debug("Error@getInputData", e)
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) => {
try {
log.debug("reduceContext", JSON.parse(reduceContext.values))
let dataObj = JSON.parse(reduceContext.values);
//create the invoice search and send the email to sales reps
let loadInvoiceSearch = invoiceSearchUsingCommission(dataObj);
} catch (e) {
log.debug("Error@reduce", e)
}
}
return { getInputData, reduce}
});