Jira Code: BTN-208
To send email report to customers with their open balance and send a report to Super Admin with consolidated information of every customer with open balance.
/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
/**
* Script Description
* To send email report to customers with their open balance and send a report to Super Admin with
* consolidated information of every customer with open balance.
* Created on 03-April-2018
*/
/*******************************************************************************
* * Balaji Trading Network | BTN-208 *
* **************************************************************************
*
*
* Author: Jobin & Jismi IT Services LLP
*
* REVISION HISTORY
*
*Ravin : 03/09/2018 - Added Ship to address in email and CSV
*
******************************************************************************/
/**
* SCRIPT ID : { Name : BTN-208 JJ SS Customer Open Balances , ID : customscript_btn_208_jj_ss_cust_balance}
*
* DEPLOYMENT : { Title : BTN-208 JJ SS Customer Open Balances , ID : customdeploy_btn_208_jj_ss_cust_balance}
*
*
*/
define(['N/email', 'N/file', 'N/http', 'N/https', 'N/record', 'N/runtime', 'N/search', 'N/task', 'N/xml', 'SuiteScripts/customModule/moment', 'N/render', 'N/format', 'N/encode'],
/**
* @param {email} email
* @param {file} file
* @param {http} http
* @param {https} https
* @param {record} record
* @param {runtime} runtime
* @param {search} search
* @param {task} task
* @param {xml} xml
* @param {moment} moment
* @param {render} render
* @param {encode} encode
*/
function(email, file, http, https, record, runtime, search, task, xml, moment, render, format, encode) {
/**
* Definition of the Scheduled script trigger point.
*
* @param {Object} scriptContext
* @param {string} scriptContext.type - The context in which the script is executed. It is one of the values from the scriptContext.InvocationType enum.
* @Since 2015.2
*/
function searchAllCustomer() {
try {
var customerObj = new Object();
var customerSearchObj = search.create({
type: "customer",
filters: [
["balance", "greaterthan", "0.00"],
"OR", ["overduebalance", "greaterthan", "0.00"]
],
columns: [
search.createColumn({
name: "internalid"
}),
search.createColumn({
name: "entityid"
}),
search.createColumn({
name: "balance"
}),
search.createColumn({
name: "overduebalance"
}),
search.createColumn({
name: "depositbalance"
}),
search.createColumn({
name: "internalid"
}),
search.createColumn({
name: "daysoverdue",
sort: search.Sort.DESC
})
]
}).run().each(function(result) {
customerObj[result.getValue({
name: "internalid"
})] = result;
return true;
});
return customerObj;
} catch (err) {
logme('E@searchAllCustomer', getError(err));
return false;
}
}
function createCustomerEmailObject(CUSTOMER_ID) {
try {
var invoiceArray = new Array();
var invoiceSearchObj = search.create({
type: "invoice",
filters: [
["type", "anyof", "CustInvc"],
"AND", ["mainline", "is", "T"],
"AND", ["customer.internalidnumber", "equalto", CUSTOMER_ID],
"AND", ["amountremainingisabovezero", "is", "T"],
"AND", ["custbody2", "anyof", "2"],
"AND", ["terms", "noneof", "8", "4", "7", "9", "10"]
],
columns: [
search.createColumn({
name: "entity"
}),
search.createColumn({
name: "trandate"
}),
search.createColumn({
name: "tranid"
}),
search.createColumn({
name: "shipaddress"
}),
search.createColumn({
name: "terms"
}),
search.createColumn({
name: "duedate"
}),
search.createColumn({
name: "daysoverdue",
sort: search.Sort.DESC,
}),
search.createColumn({
name: "total"
}),
search.createColumn({
name: "amountremaining"
}),
search.createColumn({
name: "otherrefnum"
}),
search.createColumn({
name: "handlingcost"
}),
search.createColumn({
name: "shippingcost"
}),
search.createColumn({
name: "taxamount"
}),
search.createColumn({
name: "discountamount"
}),
search.createColumn({
name: "custentity_bsp_mcc_emailordersto",
join: "customer"
})
]
}).run().each(function(result) {
invoiceArray.push(result);
return true;
})
return invoiceArray;
} catch (err) {
logme('E@createCustomerEmailObject', getError(err));
return false;
}
}
function createCustomerCreditMemo(CUSTOMER_ID) {
try {
var creditMemo = new Array();
var creditmemoSearchObj = search.create({
type: "creditmemo",
filters: [
["type", "anyof", "CustCred"],
"AND", ["mainline", "is", "T"],
"AND", ["customer.internalidnumber", "equalto", CUSTOMER_ID],
"AND", ["amountremainingisabovezero", "is", "T"],
"AND", ["custbody2", "anyof", "2"],
"AND", ["terms", "noneof", "10", "9", "7", "4", "8"]
],
columns: [
search.createColumn({
name: "trandate",
sort: search.Sort.ASC,
label: "Date"
}),
search.createColumn({
name: "tranid"
}),
search.createColumn({
name: "shipaddress"
}),
search.createColumn({
name: "total"
}),
search.createColumn({
name: "amountremaining"
})
]
}).run().each(function(result) {
creditMemo.push(result);
return true;
})
return creditMemo;
} catch (err) {
logme('E@createCustomerCreditMemo', getError(err));
return false;
}
}
function executeCustomerOpenBalance(scriptContext) {
var currentDate = new Date();
logme('EXECUTION STARTS', 'EXECUTION BEGINS AT ' + (currentDate.toUTCString()));
logme('runtime.getCurrentScript().getParameter()', runtime.getCurrentScript().getParameter("custscript_btn_208_jj_ss_arrayindex"));
try {
var remainingUsage = runtime.getCurrentScript().getRemainingUsage();
var arrayIndex = runtime.getCurrentScript().getParameter("custscript_btn_208_jj_ss_arrayindex");
arrayIndex = arrayIndex ? arrayIndex : 0;
var customerObj = searchAllCustomer();
if (!checkForParameter(arrayIndex)) {
//superadmin email
var sendAdminEmail = createAdminEmail(customerObj);
}
var RESCHEDULE_FLAG = false;
var eachCustomerInvoice = new Array();
var eachCustomerCreditMemo = new Array();
var SendEmail;
var KEY_INDEX;
logme('customerObj keys', Object.keys(customerObj));
for (var key in customerObj) {
remainingUsage = runtime.getCurrentScript().getRemainingUsage();
if (remainingUsage < 500) {
logme('RESCHEDULE key', key);
RESCHEDULE_FLAG = true;
KEY_INDEX = key;
break;
}
if (arrayIndex != 0 && arrayIndex != key) {
continue;
}
arrayIndex = 0;
eachCustomerInvoice = createCustomerEmailObject((customerObj[key]).getValue({ name: "internalid" }));
eachCustomerCreditMemo = createCustomerCreditMemo((customerObj[key]).getValue({ name: "internalid" }));
SendEmail = createEmailTemplate(eachCustomerInvoice, eachCustomerCreditMemo);
}
if (RESCHEDULE_FLAG) {
remainingUsage = runtime.getCurrentScript().getRemainingUsage();
logme('remainingUsage', remainingUsage);
currentDate = new Date();
logme('EXECUTION RESCHEDULED AT KEYINDEX ' + KEY_INDEX, 'EXECUTION RESCHEDULED ON ' + (currentDate.toUTCString()));
rescheduleScriptandReturn(KEY_INDEX);
}
currentDate = new Date();
logme('EXECUTION ENDS', 'EXECUTION ENDS AT ' + (currentDate.toUTCString()));
} catch (err) {
currentDate = new Date();
logme('EXECUTION FAILED', 'EXECUTION THROWS ERROR AT ' + (currentDate.toUTCString()));
handleTryCatchError(err, 'function executeCustomerOpenBalance()');
}
}
function createCustomerCSV(eachCustomerInvoice, eachCustomerCreditMemo) {
try {
var finalArray = new Array();
finalArray.push({
header_0: 'MyCoolCell Open Invoices Report'
});
finalArray.push({
header_0: 'Customer : ',
header_1: emptyString(eachCustomerInvoice[0].getText({
name: "entity"
})).replace(/,/g, ' | ').replace(/"/g, '%22').replace(/'/g, '%27').replace(/\r\n/g, ' | ').replace(/\n/g, ' | ')
});
finalArray.push({
header_0: "Date",
header_1: "Invoice #",
header_2: "Order Number",
header_3: "Due",
header_4: "Aging",
header_5: "Total",
header_6: "Balance",
header_7: "Location",
header_8: "Ship To Address"
});
var invoiceTotal = returnFloat(0.00, format);
var total;
var invoiceBalTotal = returnFloat(0.00, format);
var balance;
eachCustomerInvoice.forEach(function(singleResult) {
total = returnFloat(singleResult.getValue({
name: "total"
}), format);
balance = returnFloat(singleResult.getValue({
name: "amountremaining"
}), format);
invoiceBalTotal = fixFloat((parseFloat(balance) + parseFloat(invoiceBalTotal)), 2);
invoiceTotal = fixFloat((parseFloat(total) + parseFloat(invoiceTotal)), 2);
finalArray.push({
header_0: " " + singleResult.getValue({
name: "trandate"
}),
header_1: " " + singleResult.getValue({
name: "tranid"
}),
header_2: "#" + emptyString(singleResult.getValue({
name: "otherrefnum"
})),
header_3: " " + emptyString(singleResult.getValue({
name: "duedate"
})),
header_4: emptyString(singleResult.getValue({
name: "daysoverdue"
})),
header_5: total,
header_6: balance,
header_7: singleResult.getValue({
name: "shipaddress"
}).replace(/,/g, ' | ').replace(/"/g, '%22').replace(/'/g, '%27').replace(/\r\n/g, ' | ').replace(/\n/g, ' | '),
header_8: singleResult.getValue({
name: "shipaddress"
}).replace(/,/g, ' | ').replace(/"/g, '%22').replace(/'/g, '%27').replace(/\r\n/g, ' | ').replace(/\n/g, ' | ')
});
});
finalArray.push({
header_0: " ",
header_1: " ",
header_2: " ",
header_3: " ",
header_4: "TOTAL",
header_5: invoiceTotal,
header_6: invoiceBalTotal,
header_7: " ",
header_8: " "
});
finalArray.push({
header_0: ' '
});
if (eachCustomerCreditMemo.length > 0) {
finalArray.push({
header_0: 'Open Credits Report'
});
finalArray.push({
header_0: "Date",
header_1: "Credit #",
header_2: "Original Amount",
header_3: "Amount Remaining",
header_4: "Location"
});
var memoTotal = returnFloat(0.00, format);
var memoAmount;
eachCustomerCreditMemo.forEach(function(singleResult) {
memoAmount = returnFloat(singleResult.getValue({
name: "amountremaining"
}), format);
memoTotal = fixFloat((parseFloat(memoAmount) + parseFloat(memoTotal)), 2);
finalArray.push({
header_0: " " + emptyString(singleResult.getValue({
name: "trandate"
})),
header_1: " " + singleResult.getValue({
name: "tranid"
}),
header_2: returnFloat(singleResult.getValue({
name: "total"
}), format),
header_3: memoAmount,
header_4: emptyString(singleResult.getValue({
name: "shipaddress"
})).replace(/,/g, ' | ').replace(/"/g, '%22').replace(/'/g, '%27').replace(/\r\n/g, ' | ').replace(/\n/g, ' | ')
});
});
finalArray.push({
header_0: " ",
header_1: " ",
header_2: "Total",
header_3: memoTotal,
header_4: " "
});
}
return finalArray;
} catch (error) {
logme("Err@createCustomerCSV", error);
return false;
}
}
function createAdminCSV(customerObj) {
try {
var adminFinalArray = new Array();
adminFinalArray.push({
header_0: 'Monthly Report of Open Balance'
});
adminFinalArray.push({
header_0: "Customer",
header_1: "Balance",
header_2: "Overdue Balance",
header_3: "Deposit Balance",
header_4: "Aging"
});
var balTotal = returnFloat(0.00, format);
var dueTotal = returnFloat(0.00, format);
var Balance;
var Due;
var singleResult;
for (var key in customerObj) {
singleResult = customerObj[key];
Balance = returnFloat(singleResult.getValue({
name: "balance"
}), format);
Due = returnFloat(singleResult.getValue({
name: "overduebalance"
}), format);
balTotal = fixFloat((parseFloat(balTotal) + parseFloat(Balance)), 2);
dueTotal = fixFloat((parseFloat(dueTotal) + parseFloat(Due)), 2);
adminFinalArray.push({
header_0: singleResult.getValue({
name: "entityid"
}).replace(/,/g, ' | ').replace(/"/g, '%22').replace(/'/g, '%27').replace(/\r\n/g, ' | ').replace(/\n/g, ' | '),
header_1: Balance,
header_2: Due,
header_3: returnFloat(singleResult.getValue({
name: "depositbalance"
}), format),
header_4: singleResult.getValue({
name: "daysoverdue"
})
});
}
adminFinalArray.push({
header_0: "Total",
header_1: balTotal,
header_2: dueTotal,
header_3: " ",
header_4: " "
});
return adminFinalArray;
} catch (error) {
logme("Err@createAdminCSV", error);
return false;
}
}
/* convert json to csv*/
function convertToCSV(objArray) {
try {
var array = ((typeof(objArray) != 'object') ? (JSON.parse(objArray)) : (objArray));
var str = '';
for (var i = 0; i < array.length; i++) {
var line = '';
for (var index in array[i]) {
if (line != '') {
line += ',';
}
line += array[i][index];
}
str += line + '\r\n';
}
return str;
} catch (err) {
logme('E@convertToCSV', getError(err));
return false;
}
}
/* Save file in file cabinet as CSV*/
function exportCSVFile(items, fileTitle) {
try {
var jsonObject = JSON.stringify(items);
var csvFile = convertToCSV(jsonObject);
var exportedFilename = (checkForParameter(fileTitle, 'fileTitle') ? (fileTitle + '.csv') : ('export.csv'));
var fileObj = file.create({
name: exportedFilename,
fileType: file.Type.CSV,
contents: csvFile,
encoding: file.Encoding.UTF8,
isInactive: true,
folder: 9333
});
return fileObj;
} catch (err) {
logme('E@exportCSVFile', getError(err));
return false;
}
}
/*to create email template for customer*/
function createEmailTemplate(eachCustomerInvoice, eachCustomerCreditMemo) {
try {
if (eachCustomerInvoice.length == 0) {
return false;
}
var SUBJECT = 'MyCoolCell Terms Open Invoice Report';
var CustomerName = eachCustomerInvoice[0].getText({
name: "entity"
});
var Email_CC = eachCustomerInvoice[0].getValue({
name: "custentity_bsp_mcc_emailordersto",
join: "customer"
});
if (checkForParameter(Email_CC, 'Checking CC')) {
Email_CC = formatStringtoArray(Email_CC, ',');
} else {
Email_CC = undefined;
}
var tableBGFlag = true;
var waitForResponse;
var EMAIL_BODY = "";
EMAIL_BODY += "<br><p><div style='color:black;font-weight:normal;'>Dear " + CustomerName + ",</div></p>";
EMAIL_BODY += "<br><p><div style='color:black;font-weight:normal;'>This notice is to inform you of your open balance</div></p>";
EMAIL_BODY += "<p><br></p>";
EMAIL_BODY += "<div><div style=\"overflow-x: auto;\"><div>";
EMAIL_BODY += " <table style=\"font-family:arial, sans-serif;border-collapse:collapse;width:100%;\">";
EMAIL_BODY += " <thead>";
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Order Date<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Invoice#<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Order Number<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Due Date<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Aging<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Total<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Balance<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Location<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Ship To Address<\/th>";
EMAIL_BODY += " <\/tr>";
EMAIL_BODY += " <\/thead>";
EMAIL_BODY += " <tbody>";
var trans_total = returnFloat(0.00);
var bal_total = returnFloat(0.00);
var memo_total = returnFloat(0.00);
for (var i = 0; i < eachCustomerInvoice.length; i++) {
var singleResult = eachCustomerInvoice[i];
var CUSTOMER_INTERNAL_ID = emptyString(singleResult.getValue({
name: "entity"
}));
var date = singleResult.getValue({
name: "trandate"
});
var invoice = singleResult.getValue({
name: "tranid"
});
var location = singleResult.getValue({
name: "shipaddress"
});
location = emptyString(location).replace(/\r\n/g, ", ").replace(/\r/g, ", ").replace(/\n/g, ", ");
var due = singleResult.getValue({
name: "duedate"
});
due = emptyString(due);
var aging = singleResult.getValue({
name: "daysoverdue"
});
aging = emptyString(aging);
var total = singleResult.getValue({
name: "total"
});
var tot = returnFloat(total, format);
var balance = singleResult.getValue({
name: "amountremaining"
});
var bal = returnFloat(balance, format);
//Calculation of Grand Total
trans_total = fixFloat((parseFloat(trans_total) + parseFloat(tot)), 2);
bal_total = fixFloat((parseFloat(bal_total) + parseFloat(bal)), 2);
var po_no = singleResult.getValue({
name: "otherrefnum"
});
po_no = emptyString(po_no);
if (tableBGFlag) {
EMAIL_BODY += " <!--FIRST-->";
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + date + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + invoice + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + po_no + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + due + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + aging + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + tot + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + bal + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + location + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + location + "<\/td>";
EMAIL_BODY += " <\/tr>";
tableBGFlag = false;
} else {
EMAIL_BODY += " <!--SECOND-->";
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + date + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + invoice + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + po_no + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + due + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + aging + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + tot + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + bal + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + location + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + location + "<\/td>";
EMAIL_BODY += " <\/tr>";
tableBGFlag = true;
}
}
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td colspan='5' style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + "Grand Total : " + trans_total + "<\/td>";
EMAIL_BODY += " <td colspan='3' style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + "Balance Total : " + bal_total + "<\/td>";
EMAIL_BODY += " <\/tr>";
EMAIL_BODY += " <\/tbody>";
EMAIL_BODY += " <\/table>";
EMAIL_BODY += "<\/div><\/div><\/div>";
if (eachCustomerCreditMemo.length > 0) {
//Credit Memo
EMAIL_BODY += "<br><p><div style='color:black;font-weight:normal;'>Open Credits Report</div></p>";
EMAIL_BODY += "<div><div style=\"overflow-x: auto;\"><div>";
EMAIL_BODY += " <table style=\"font-family:arial, sans-serif;border-collapse:collapse;width:100%;\">";
EMAIL_BODY += " <thead>";
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Date<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Credit#<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Location<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Original Amount<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Amount Reamining<\/th>";
EMAIL_BODY += " <\/tr>";
EMAIL_BODY += " <\/thead>";
EMAIL_BODY += " <tbody>";
var bgcolor = 'dddddd';
for (j = 0; j < eachCustomerCreditMemo.length; j++) {
var Result = eachCustomerCreditMemo[j];
var memo_date = Result.getValue({
name: "trandate"
});
var memo_location = Result.getValue({
name: "shipaddress"
});
memo_location = emptyString(memo_location).replace(/\r\n/g, ", ").replace(/\r/g, ", ").replace(/\n/g, ", ");
var credit = Result.getValue({
name: "tranid"
});
credit = emptyString(credit);
var amount = Result.getValue({
name: "total"
});
amount = returnFloat(amount, format);
var amt_rem = Result.getValue({
name: "amountremaining"
});
amt_rem = returnFloat(amt_rem, format);
bgcolor = ((j % 2) ? 'dddddd' : 'efefef');
memo_total = fixFloat((parseFloat(memo_total) + parseFloat(amt_rem)), 2);
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#" + bgcolor + ";\">" + memo_date + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#" + bgcolor + ";\">" + credit + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#" + bgcolor + ";\">" + memo_location + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#" + bgcolor + ";\">" + amount + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#" + bgcolor + ";\">" + amt_rem + "<\/td>";
EMAIL_BODY += " <\/tr>";
}
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td colspan='4' style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#" + (bgcolor == 'dddddd' ? 'efefef' : 'dddddd') + ";\">" + "Amount Remaining" + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#" + (bgcolor == 'dddddd' ? 'efefef' : 'dddddd') + ";\">" + memo_total + "<\/td>";
EMAIL_BODY += " <\/tr>";
EMAIL_BODY += " <\/tbody>";
EMAIL_BODY += " <\/table>";
EMAIL_BODY += "<\/div><\/div><\/div>";
}
EMAIL_BODY += "<br><p><div style='color:black;font-weight:normal;'>All payments are due on their respective invoice due date. A 5 day grace period is given for terms account invoices.</div></p>";
EMAIL_BODY += "<p><div style='color:black;font-weight:normal;'>Please make all checks payable to MyCoolCell and mail all payments to:</div></p>";
EMAIL_BODY += "<br><p><div style='color:black;font-weight:normal;'>MyCoolCell, LLC<br>Accounts Receivable<br>9722 Topanga Canyon Blvd<br>Chatsworth, CA 91311<br></div></p><br>";
EMAIL_BODY += "<p><div style='color:black;font-weight:normal;'>Customers with open balances beyond our grace period are subject to account freeze at our discretion.</div></p><br>";
EMAIL_BODY += "<p><div style='color:black;font-weight:bold;'>We appreciate your business and look forward to servicing you in the future.<br>Thank you for choosing MyCoolCell!<br> <a href='http://www.mycoolcell.net/' style='color:blue;font-weight:bold;'>www.mycoolcell.net</a> </div></p><br>";
var fileObj = exportCSVFile(createCustomerCSV(eachCustomerInvoice, eachCustomerCreditMemo), CustomerName);
waitForResponse = sendCustomerEmailReport(CUSTOMER_INTERNAL_ID, SUBJECT, EMAIL_BODY, fileObj, Email_CC);
return waitForResponse;
} catch (err) {
logme('E@createEmailTemplate', getError(err));
return false;
}
}
function createAdminEmail(customerObj) {
try {
var SUBJECT = 'MyCoolCell Terms Open Invoice Report';
var tableBGFlag = true;
var Admins = new Array("albert@mycoolcell.net", "grace@mycoolcell.net", "christina@mycool.net", "vivian@mycoolcell.net");
var waitForResponse;
var EMAIL_BODY = "";
EMAIL_BODY += "<br><p><div style='color:black;font-weight:normal;'>Dear Admin" + ",</div></p>";
EMAIL_BODY += "<br><p><div style='color:black;font-weight:normal;'>This notice is to inform you Customer Aging Report for this Month. </div></p>";
EMAIL_BODY += "<div><div style=\"overflow-x: auto;\"><div>";
EMAIL_BODY += " <table style=\"font-family:arial, sans-serif;border-collapse:collapse;width:100%;\">";
EMAIL_BODY += " <thead>";
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Name<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Balance<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Overdue Balance<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Deposit Balance<\/th>";
EMAIL_BODY += " <th style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;\">Days Overdue<\/th>";
EMAIL_BODY += " <\/tr>";
EMAIL_BODY += " <\/thead>";
EMAIL_BODY += " <tbody>";
var balance_total = returnFloat(0.00);
var due_total = returnFloat(0.00);
for (var key in customerObj) {
var singleResult = customerObj[key];
var id = singleResult.getValue({
name: "entityid"
});
var internalid = singleResult.getValue({
name: "internalid"
});
var balance = singleResult.getValue({
name: "balance"
});
var bal = returnFloat(balance, format);
var overdue = singleResult.getValue({
name: "overduebalance"
});
var due = returnFloat(overdue, format);
var deposit = singleResult.getValue({
name: "depositbalance"
});
var dep = returnFloat(deposit, format);
var aging = singleResult.getValue({
name: "daysoverdue"
});
balance_total = fixFloat((parseFloat(balance_total) + parseFloat(bal)), 2);
due_total = fixFloat((parseFloat(due_total) + parseFloat(due)), 2);
if (tableBGFlag) {
EMAIL_BODY += " <!--FIRST-->";
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + id + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + bal + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + due + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + dep + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + aging + "<\/td>";
EMAIL_BODY += " <\/tr>";
tableBGFlag = false;
} else {
EMAIL_BODY += " <!--SECOND-->";
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + id + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + bal + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + due + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + dep + "<\/td>";
EMAIL_BODY += " <td style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#efefef;\">" + aging + "<\/td>";
EMAIL_BODY += " <\/tr>";
tableBGFlag = true;
}
}
EMAIL_BODY += " <tr>";
EMAIL_BODY += " <td colspan='2' style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + "Balance Total : " + balance_total + "<\/td>";
EMAIL_BODY += " <td colspan='3' style=\"border-width:1px;border-style:solid;border-color:#cecece;text-align:left;padding-top:8px;padding-bottom:8px;padding-right:8px;padding-left:8px;background-color:#dddddd;\">" + "Overdue Total : " + due_total + "<\/td>";
EMAIL_BODY += " <\/tr>";
EMAIL_BODY += " <\/tbody>";
EMAIL_BODY += " <\/table>";
EMAIL_BODY += "<\/div><\/div><\/div>";
var fileObj = exportCSVFile(createAdminCSV(customerObj), "SuperAdmin");
waitForResponse = sendCustomerEmailReport(Admins, SUBJECT, EMAIL_BODY, fileObj);
return waitForResponse;
} catch (err) {
logme('E@createAdminEmail', getError(err));
}
}
/*to send email to the customer*/
function sendCustomerEmailReport(CUSTOMER_INTERNAL_ID, SUBJECT, EMAIL_BODY, fileObj, CC_EMAIL) {
try {
var senderId = 7; // 7 :grace@mycoolcell.net
var recipientId = CUSTOMER_INTERNAL_ID;
if (checkForParameter(CC_EMAIL, 'CC_EMAIL')) {
email.send({
author: senderId,
recipients: recipientId,
subject: SUBJECT,
body: EMAIL_BODY,
attachments: [fileObj],
cc: CC_EMAIL,
relatedRecords: {
entityId: recipientId,
}
});
} else {
email.send({
author: senderId,
recipients: recipientId,
subject: SUBJECT,
body: EMAIL_BODY,
attachments: [fileObj],
relatedRecords: {
entityId: recipientId,
}
});
}
return true;
} catch (err) {
logme('E@sendCustomerEmailReport', getError(err));
return false;
}
}
/*Try to reschedule script*/
function rescheduleScriptandReturn(indexParam) {
try {
var mrTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscript_btn_208_jj_ss_cust_balance",
deploymentId: "customdeploy_btn_208_jj_ss_cust_balance",
params: {
custscript_btn_208_jj_ss_arrayindex: indexParam
}
});
var scriptTaskId = mrTask.submit();
logme("rescheduleScriptandReturn_scriptTaskId", scriptTaskId);
} catch (err) {
logme('E@rescheduleScriptandReturn', getError(err));
}
}
/*To get current Date for start of the week (Date of Monday)*/
function getCurrentDate() {
//Date format is MM/DD/YYYY
var currentDate = new Date();
var currentMonth = currentDate.getMonth() + 1;
var currentDay = currentDate.getDate();
var currentYear = currentDate.getFullYear();
return (currentMonth + '/' + currentDay + '/' + currentYear);
}
/*to check for a parameter*/
function checkForParameter(parameter, parameterName) {
if (parameter != "" && parameter != null && parameter != undefined && parameter != "undefined" && parameter != " ") {
return true;
} else {
// if (parameterName)
// logme('No ' + parameterName + ' found', 'No ' + parameterName + ' found');
return false;
}
}
/*to handle 'try' error in try-catch statement*/
function handleTryCatchError(error, errorLocation) {
logme('Error on ' + errorLocation, getError(error));
logme('Error on ' + errorLocation, error.message);
}
return {
execute: executeCustomerOpenBalance
};
/*to format string to array*/
function formatStringtoArray(dataStr, delimiter) {
var result = new Array();
if (dataStr.indexOf(delimiter) > 0) {
dataStr = dataStr.split(delimiter);
}
if (!Array.isArray(dataStr)) {
result.push(dataStr);
} else {
result = dataStr;
}
if (checkForParameter(result, 'formatStringtoArray result')) {
var index = 0;
while (index < result.length) {
result[index] = (((result[index]).toString()).trim());
if (!validateEmail(((checkForParameter(result[index], 'emailSTR')) ? (result[index]) : ('false')))) {
result.remove(index);
index--;
}
index++;
}
}
return result;
}
/*to format string to array*/
function validateEmail(emailStr) {
var reEmail = /^(?:[\w\!\#\$\%\&\'\*\+\-\/\=\?\^\`\{\|\}\~]+\.)*[\w\!\#\$\%\&\'\*\+\-\/\=\?\^\`\{\|\}\~]+@(?:(?:(?:[a-zA-Z0-9](?:[a-zA-Z0-9\-](?!\.)){0,61}[a-zA-Z0-9]?\.)+[a-zA-Z0-9](?:[a-zA-Z0-9\-](?!$)){0,61}[a-zA-Z0-9]?)|(?:\[(?:(?:[01]?\d{1,2}|2[0-4]\d|25[0-5])\.){3}(?:[01]?\d{1,2}|2[0-4]\d|25[0-5])\]))$/;
if (emailStr.match(reEmail)) {
return true;
} else {
return false;
}
}
// to delete an element from an Array
Array.prototype.remove = function(from, to) {
var rest = this.slice(parseInt(to || from) + 1 || this.length);
this.length = from < 0 ? this.length + from : from;
return this.push.apply(this, rest);
};
});
/*******************************************************************************
* return error
*
* @param e
* @returns
*
*/
function getError(e) {
var stErrMsg = '';
if (e.getDetails != undefined) {
stErrMsg = '_' + e.getCode() + '<br>' + e.getDetails() + '<br>' +
e.getStackTrace();
} else {
stErrMsg = '_' + e.toString();
}
return stErrMsg;
}
/*******************************************************************************
* Log these data
*
* @param title
* @param details
* @returns
*
*/
function logme(title, details) {
log.debug({
title: title,
details: details
});
}
/*To round a float number */
function roundFloat(value, decimals) {
return Number(Math.round(parseFloat(value) + 'e' + parseInt(decimals)) + 'e-' + parseInt(decimals));
}
/*To fix a float number to specified decimal parts */
function fixFloat(value, decimals) {
return roundFloat(parseFloat(value), parseInt(decimals)).toFixed(parseInt(decimals));
}
//To convert the values to Float numbers
function returnFloat(value, format) {
value = (value ? (format ? (fixFloat(format.parse({
value: value,
type: format.Type.FLOAT
}), 2)) : (fixFloat(parseFloat(value), 2))) : parseFloat(0.00));
return value;
}
/*******************************************************************************
* Check if string is empty
*
* @param e
* @returns Created By RJ on 28-Feb-2018 3:16:13 PM
*/
function emptyString(e) {
switch (e) {
case "":
case null:
case false:
case undefined:
case NaN:
case " ":
case typeof this == "undefined":
return '_';
break;
default:
return e;
break;
}
}