Script to generate a monthly sales report for sales representatives, including customer details and sales order information, and sends it via email. If there is no assigned sales representative, the report is sent to the administrator for further action.
getInputData Function:
- The getInputData function creates a search to retrieve sales orders created within the last month.
- It specifies the filters and columns for the search.
map Function:
- The map function processes each result during the Map phase.
- It parses the JSON result from the search into a structured object (searchResult).
- Extracts relevant information and writes it to the context for the Reduce phase.
reduce Function:
- The reduce function processes and aggregates data during the Reduce phase.
- Retrieves the sales representative’s name using another search.
- Parses and extracts sales order details from the Reduce values.
- Builds CSV content with sales order details.
- Creates a CSV file and saves it to a specified folder.
- Composes email subject and body.
- Sends an email to the sales representative (or administrator) with the attached CSV file.
Map/Reduce Script
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define([‘N/record’, ‘N/email’, ‘N/log’, ‘N/file’, ‘N/search’, ‘N/https’],
function(record, email, log, file, search, https) {
function getInputData() {
try{
return search.create({
type: record.Type.SALES_ORDER,
filters: [
[‘mainline’, ‘is’, ‘T’],
‘AND’,
[‘datecreated’, ‘within’, ‘lastmonth’]
],
columns: [‘entity’, ’email’, ‘tranid’, ‘amount’, ‘salesrep’]
});
} catch (e) {
log.error({
title: ‘Error Processing Request’,
details: e.message,
});
}
}
function map(scriptContext) {
try{
let searchResult = JSON.parse(scriptContext.value);
let salesRepId = searchResult.values[‘salesrep’].value;
const salesDetails = {
customerName: searchResult.values[‘entity’].text,
customerEmail: searchResult.values[’email’],
documentNumber: searchResult.values[‘tranid’],
salesAmount: parseFloat(searchResult.values[‘amount’]).toFixed(2)
};
scriptContext.write({
key: salesRepId || ‘admin’,
value: salesDetails
});
} catch (e) {
log.error({
title: ‘Error Processing Request’,
details: e.message,
});
}
}
function reduce(reduce) {
try{
const salesRepId = reduce.key;
let salesRepName;
const employeeSearch = search.create({
type: search.Type.EMPLOYEE,
filters: [[‘internalid’, ‘anyof’, salesRepId]],
columns: [‘firstname’, ‘lastname’]
});
const employeeResults = employeeSearch.run().getRange({
start: 0,
end: 1
});
if (employeeResults && employeeResults.length > 0) {
const firstName = employeeResults[0].getValue({ name: ‘firstname’ });
const lastName = employeeResults[0].getValue({ name: ‘lastname’ });
salesRepName = firstName + ” ” + lastName;
}
const salesOrders = reduce.values.map(JSON.parse);
let csvContent = ‘Customer Name,email,Sales Order Document Number,amountn’;
for (let j = 0; j < salesOrders.length; j++) {
const orderDetails = salesOrders[j];
let customer = orderDetails.customerName;
let emailId = orderDetails.customerEmail;
let tranid = orderDetails.documentNumber;
let amount = orderDetails.salesAmount;
csvContent += ‘”‘ + customer + ‘”,”‘ + emailId + ‘”,”‘ + tranid + ‘”,”‘ + amount + ‘”n’;
}
let csvFile = file.create({
name: ‘Last Month Sales Report’ + salesRepId + ‘.csv’,
fileType: file.Type.CSV,
contents: csvContent,
folder: ‘986’
});
csvFile.save();
let subject = ‘Last Month Sales Report for ‘ + (salesRepId === ‘admin’ ? ‘Kathryn Glass’ : salesRepName);
let body = ‘Dear ‘ + (salesRepId === ‘admin’ ? ‘Kathryn Glass’ : salesRepName) +
‘,nnWe hope this message finds you well. We would like to provide you with a summary of sales orders processed during the previous month.Your dedication and hard work have contributed significantly to the success of our sales efforts. Please review the attached detailed report for more information.n’ +
‘ nYour commitment to providing excellent service to our customers is highly appreciated.nnThank you for your ongoing dedication to our success.’;
if (salesRepId && salesRepId !== ‘admin’) {
body += ‘nnWith regards,nDreamLand.’;
email.send({
author: -5,
recipients: salesRepId,
subject: subject,
body: body,
attachments: [csvFile]
});
} else {
body += “nnAssign a sales representative to these customers.nnWith regards,nDreamLand”;
let recipientEmail = -5;
email.send({
author: -5,
recipients: recipientEmail,
subject: subject,
body: body,
attachments: [csvFile]
});
}
return true;
} catch (e) {
log.error({
title: ‘Error Processing Request’,
details: e.message,
});
}
}
return {
getInputData: getInputData,
map: map,
reduce: reduce
};
});