Map/Reduce script is designed to run monthly and send overdue invoice reminders to customers via email.
1. getInputData:
– Purpose: Generates input data for the map/reduce process.
– Retrieves overdue invoices from the previous month using a search.
– Filters include invoices with a due date before the start of the current month, with a status of ‘CustInvc:A’ (approved), and with active sales representatives and customers.
– Returns the search object.
const getInputData = (inputContext) => {
try {
//create search and fetch details
let prevMnthInvSearch = search.create({
type: search.Type.INVOICE,
filters: [
search.createFilter({
name: 'mainline',
operator: 'is',
values: 'T'
}),
search.createFilter({
name: 'duedate',
operator: search.Operator.BEFORE,
values: 'startofthismonth'
}),
search.createFilter({
name: 'status',
operator: 'anyof',
values: 'CustInvc:A'
}),
search.createFilter({
name: 'isinactive',
join: 'salesrep',
operator: 'is',
values: 'F'
}),
search.createFilter({
name: 'isinactive',
join: 'customer',
operator: 'is',
values: 'F'
})],
columns: [
'entity',
'tranid',
'total',
'salesrep',
'daysoverdue',
search.createColumn({
name: 'email',
join: 'customer'
})]
});
prevMnthInvSearch.run().each(function (result) {
return true;
})
return prevMnthInvSearch;
} catch (error) {
log.error({
title: 'Error',
details: error.message
});
}
}
2. map:
– Purpose: Processes each key-value pair generated by the getInputData stage.
– Extracts relevant information from the search results, such as customer ID, email, invoice details, and sales representative information.
– Writes the extracted details into key-value pairs for further processing.
const map = (mapContext) => {
try {
//get the returned value from getInputData
let prevSearch = JSON.parse(mapContext.value);
let customerId = prevSearch.values['entity'].value;
let custEmail = prevSearch.values['email.customer'];
let docNum = prevSearch.values['tranid'];
let amount = prevSearch.values['total'];
let salesrepId = prevSearch.values['salesrep'].value;
let overdueDays = prevSearch.values['daysoverdue'];
if (!salesrepId) {
salesrepId = 0;
}
const detailsInvCust = { customerEmail: custEmail, invNum: docNum, total: amount, salesrep: salesrepId, days: overdueDays };
mapContext.write({
key: customerId,
value: detailsInvCust
});
} catch (error) {
log.error('Map Error', error.message);
}
}
3. reduce:
– Purpose: Aggregates and organizes the key-value pairs from the map stage.
– Groups data by customer ID.
– Writes the grouped data, containing details of overdue invoices, for further processing.
const reduce = (reduceContext) => {
try {
const entity = reduceContext.key;
// log.debug('Reduce Entity', entity);
const detailsInv = reduceContext.values.map(JSON.parse);
// log.debug('Invoice reduce', detailsInv);
reduceContext.write(entity, detailsInv);
}
catch (e) {
log.error('Reduce Error', e.message);
}
}
4. summarize:
– Purpose: Executes after the reduce stage and handles the final processing and actions.
– Looks up the customer’s name based on the customer ID.
– Prepares a CSV file with overdue invoice details for each customer.
– Sends an email reminder to each customer with the CSV attachment.
– Checks if a sales representative is assigned and active; if not, sends the email from the admin account.
– Logs information about the script’s completion.
const summarize = (summaryContext) => {
try {
summaryContext.output.iterator().each(function (key, values) {
const entity = key;
let custName = search.lookupFields({
type: search.Type.CUSTOMER,
id: entity,
columns: 'companyname'
});
let customer = custName.companyname;
const invoiceDetail = values;
const invoiceOutput = JSON.parse(invoiceDetail);
let csvContent = 'Customer Name, Customer Email, Invoice Document Number, Invoice Amount, Days Overduen';
let salesrepId, cust_Email;
for (let i = 0; i < invoiceOutput.length; i++) {
let invoiceEach = invoiceOutput[i];
salesrepId = invoiceEach.salesrep;
cust_Email = invoiceEach.customerEmail;
let docNumber = invoiceEach.invNum;
let amount = invoiceEach.total;
let day = invoiceEach.days;
csvContent += '"' + customer + '","' + cust_Email + '","' + docNumber + '","' + amount + '","' + day + '"n';
}
let csvFile = file.create({
name: 'Monthly Overdue Invoice Reminder' + entity + '.csv',
fileType: file.Type.CSV,
contents: csvContent,
folder: '2390'
});
csvFile.save();
let emailBody = 'Dear ' + customer + ' ,n You have overdue invoices till previous month. Details are provided in the csv file.n Please take a note of this. nn Thank You.';
let emailSub = 'Monthly Overdue Reminder for Customer.';
// Check if the sales representative is active
let salesRepIsActive = search.lookupFields({
type: search.Type.EMPLOYEE,
id: salesrepId,
columns: 'isinactive'
}).isinactive;
if (salesrepId != 0) {
email.send({
author: salesrepId, //salesrep id associated with the transaction
recipients: entity, //email of the customer
subject: emailSub,
body: emailBody,
attachments: [csvFile]
});
}
else {
emailBody += 'n This email is send from admin as no salesrep is assigned to you. nn Thank You.'
email.send({
author: -5,
recipients: entity,
subject: emailSub,
body: emailBody,
attachments: [csvFile]
});
}
return true;
});
}
catch (e) {
log.error('Summarize Error', e.message);
}
}
The script is designed to be scheduled to run monthly, identifying overdue invoices from the previous month, summarizing the details, and sending reminder emails to customers.