The following script creates the Journal entry from the Excel file stored in the File Cabinet with Error Handling. The Script validates the file name, Excel content formats and heading formats.
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
* @NModuleScope SameAccount
* @NAmdConfig ./JsLibraryConfig.json
*/
define(['N/record', 'N/search', 'N/file', 'N/format', 'N/email', 'xlsx'],
(record, search, file, format, email, XLSX) => {
"use strict";
const DEFAULT_COSTCENTER = 202; //Internal id ofCost Centre Missing.
// Error handling roles for each parent subsidiry folder ID.
const FOLDER_DATA = [
{ folderId: 9928, roles: [1357, 1408] },
{ folderId: 9929, roles: [1498, 1500] },
{ folderId: 9930, roles: [1416, 1418] },
{ folderId: 9931, roles: [1490, 1491] },
{ folderId: 9932, roles: [1486, 1384] },
{ folderId: 9933, roles: [1479, 1478] },
{ folderId: 9934, roles: [1461, 1352] }
];
const HEADING_LABEL = {
'Xero': {
startIndexLabel: "Account Code",
endIndexLabel: "Total",
accountCodeLabel: "Account Code",
debitLabel: "Debit - Year to date",
creditLabel: "Credit - Year to date",
departmentLabel: ["Cost Centre", "Cost Center"],
classLabel: "Class",
legacyAccountLabel: "Legacy Account"
},
'MYOB': {
startIndexLabel: "Account No.",
endIndexLabel: "Total:",
accountCodeLabel: "Account No.",
debitLabel: "YTD Debit",
creditLabel: "YTD Credit",
departmentLabel: ["Cost Centre", "Cost Center"],
classLabel: "Class",
legacyAccountLabel: "Legacy Account"
},
'ASC': {
startIndexLabel: "Account Code",
endIndexLabel: "TOTALS",
accountCodeLabel: "Account Code",
debitLabel: "Debit",
creditLabel: "Credit",
departmentLabel: ["Cost Centre", "Cost Center"],
classLabel: "Class",
legacyAccountLabel: "Legacy Account"
},
'Quickbooks': {
startIndexLabel: "Debit",
endIndexLabel: "TOTAL",
accountCodeLabel: null,
debitLabel: "Debit",
creditLabel: "Credit",
departmentLabel: ["Cost Centre", "Cost Center"],
classLabel: "Class",
legacyAccountLabel: "Legacy Account"
},
'QuickBooks': {
startIndexLabel: "Debit",
endIndexLabel: "TOTAL",
accountCodeLabel: null,
debitLabel: "Debit",
creditLabel: "Credit",
departmentLabel: ["Cost Centre", "Cost Center"],
classLabel: "Class",
legacyAccountLabel: "Legacy Account"
},
};
let errorArray = []; //Storing errors --> For error handling & debugging process.
const getInputData = (inputContext) => {
try {
let fileArray = exports.getFiles();
return fileArray;
} catch (e) {
log.error('error@getInputData', e);
}
}
const reduce = (reduceContext) => {
try {
let fileID = JSON.parse(reduceContext.values);
let fileRecordObj = file.load({ id: fileID });
let fileContents = fileRecordObj.getContents();
// Validate the file name and extract data from it.
let JEBodyData = exports.fileNameValidations(fileRecordObj.name);
let externalSystem = JEBodyData.externalSystem;
// Check if there's a error message in file format.
if (checkForParameter(JEBodyData.message)) {
// Create new file with error reason and send Error handling email.
exports.handleError('body', externalSystem, JEBodyData, fileRecordObj, fileContents);
} else {
// Check for duplicate entries based on Subsidiary and JE Date.
let duplicate = exports.checkDuplicateJE(JEBodyData);
if (duplicate.length > 0) {
JEBodyData.message = 'TB Journal already exists for Subsidiary & Date combination.';
exports.handleError('body', externalSystem, JEBodyData, fileRecordObj, fileContents);
} else {
// Get line datas from the Excel file.
let JElineData = exports.getFileLineContents(fileContents, JEBodyData);
if (JElineData.length > 0) {
// Process line validation and Create Journal entry.
let journalEntryReturn = exports.journalCreate(JEBodyData, JElineData);
// Check if the journal entry creation was successful
if (checkForParameter(journalEntryReturn.journalEntryId)) {
exports.folderChange(fileRecordObj, fileContents, 'Processed Files');
} else {
let errorType = checkForParameter(journalEntryReturn.message) ? 'body' : 'line';
exports.handleError(errorType, externalSystem, journalEntryReturn, fileRecordObj, fileContents);
}
} else {
JEBodyData.message = "Invalid File Format: Unable to Retrieve Data from the EXCEL File, or Required Columns Are Missing.";
exports.handleError('body', externalSystem, JEBodyData, fileRecordObj, fileContents);
}
}
}
} catch (e) {
log.error('error@reduce', e);
}
}
const summarize = (summaryContext) => {
// Summarize added to find the Map/Reduce script excution end time.
}
const exports = {
/**
* Fetches a list of files based on specified filters and columns.
* @returns {Array} An array of files matching the search criteria.
*/
getFiles() {
let folderIds = FOLDER_DATA.map((item) => item.folderId);
let fileSearchObj = search.create({
type: "file",
filters:
[
["folder", "anyof", folderIds],
"AND",
["filetype", "anyof", "EXCEL"],
],
columns:
[
search.createColumn({ name: "internalid", sort: search.Sort.ASC }),
search.createColumn({ name: "folder" })
]
});
let fileArray = [];
fileSearchObj.run().each(function (result) {
let folder = Number(result.getValue({ name: "folder" }));
let folderData = FOLDER_DATA.find((item) => item.folderId === folder);
if (folderData) {
fileArray.push(result.getValue({ name: "internalid" }));
}
return true;
});
return fileArray;
},
/**
* Validates the format of a given file name and extracts relevant data from it.
* @param {string} fileName - The name of the file to be validated.
* @returns {object} An object containing extracted data or an error message if validation fails.
*/
fileNameValidations(fileName) {
let fileNameData = {};
let fileNameArray = fileName.split('_');
// Check if the array length is not 4 or the file name is not starting with 'TB'
if (fileNameArray.length !== 4 || fileNameArray[0] !== 'TB') {
fileNameData.externalSystem = '';
fileNameData.message = 'File Name format was wrong';
return fileNameData;
}
// Extract accounting system from File Name
const EXTERNAL_SYSTEM = Object.keys(HEADING_LABEL);
let externalSystem = EXTERNAL_SYSTEM.find(item => item === fileNameArray[2]);
if (!checkForParameter(externalSystem)) {
fileNameData.externalSystem = '';
fileNameData.message = 'Accounting System Name was wrong on File Name';
return fileNameData;
}
fileNameData.externalSystem = externalSystem;
// Extract subsidiary from the File Name
let subsidiaryName = fileNameArray[1];
let subsidiaryArray = exports.subsidiarySearch(subsidiaryName);
let subsidiaryId = subsidiaryArray[0]?.internalid?.value;
if (!checkForParameter(subsidiaryId)) {
fileNameData.message = 'Subsidiary Name was wrong on File Name';
return fileNameData;
}
fileNameData.subsidiaryId = subsidiaryId;
fileNameData.defaultLocation = subsidiaryArray[0]?.custrecord_jcs_sub_location?.value;
// Extract Posting Period from the File Name
let postingPeriod;
let periodFlag = false;
let period = fileNameArray[3].split('.xlsx')[0]; // Period Name format will be 'April2023'
if (checkForParameter(period)) {
let pattern = /^(January|February|March|April|May|June|July|August|September|October|November|December)(\d{4})$/;
periodFlag = pattern.test(period);
if (periodFlag) {
//Change naming format
let periodArray = period.split(/(\d{4})/).filter(Boolean);
let abbreviatedMonth = periodArray[0].substr(0, 3);
let year = periodArray[1];
let periodName = `${abbreviatedMonth} ${year}`; // Period Name format wil be 'Apr 2023'
// Search for the posting period based on the formatted periodName
postingPeriod = exports.postingPeriodSearch(periodName);
if (checkForParameter(postingPeriod)) {
postingPeriod = postingPeriod[0];
}
}
}
if (!checkForParameter(period) || periodFlag === false) {
fileNameData.message = 'Accounting Period was wrong on File Name';
return fileNameData;
}
// Converting Date string to Object
let journalDateObj = format.parse({
value: postingPeriod.enddate.value,
type: format.Type.DATE,
timezone: format.Timezone.GMT
});
// calculate reversal date by adding 1 day for finding the first day of the next month.
journalDateObj.setDate(journalDateObj.getDate() + 1);
fileNameData.journalEntryDate = postingPeriod.enddate.value;
fileNameData.reversalDate = journalDateObj;
fileNameData.memo = period + ' TB Data';
return fileNameData;
},
/**
* Checks for duplicate journal entries based on provided JEBodyData.
* @param {object} JEBodyData - An object containing data related to the journal entry.
* @returns {array} An array of duplicate journal entries or an empty array if none are found.
*/
checkDuplicateJE(JEBodyData) {
let journalentrySearchObj = search.create({
type: "journalentry",
filters:
[
["type", "anyof", "Journal"],
"AND",
["subsidiary", "anyof", JEBodyData.subsidiaryId],
"AND",
["trandate", "on", JEBodyData.journalEntryDate],
"AND",
["custbody_cogfs_jetype", "is", "T"],
"AND",
["mainline", "is", "T"]
],
columns:
[
search.createColumn({ name: "internalid" }),
search.createColumn({ name: "tranid" })
]
});
return iterateSavedSearch(journalentrySearchObj, fetchSavedSearchColumn(journalentrySearchObj, 'name'));
},
/**
* Extracts line contents from a EXCEL file and formats them into an array of objects.
* @param {string} fileContents - The contents of the EXCEL file.
* @param {object} JEBodyData - An object containing data related to the journal entry.
* @returns {array} An array of formatted line objects.
*/
getFileLineContents(fileContents, JEBodyData) {
let externalSystem = JEBodyData.externalSystem;
let config = HEADING_LABEL[externalSystem];
if (!config) {
return [];
}
let excelData = exports.getDataFromEXCEL(fileContents);
let finalArray = [];
let startRow = -1;
let endRow = -1;
// Iterate through excelData to find start and end Row based on heading label.
for (let i = 0; i < excelData.length; i++) {
for (let j = 0; j < excelData[i].length; j++) {
if (startRow === -1 && config.startIndexLabel === excelData[i][j]) {
startRow = i;
}
if (endRow === -1 && config.endIndexLabel === excelData[i][j]) {
endRow = i;
break;
}
}
if (startRow !== -1 && endRow !== -1) {
break;
}
}
// Create a trimmedArray based on the start and end row
let trimmedArray = (startRow !== -1 && endRow !== -1) ? excelData.slice(startRow, endRow) : [];
// Filter out arrays with all empty strings
trimmedArray = trimmedArray.filter(array => array.some(item => item !== ""));
if (trimmedArray.length > 0) {
let accCodeIndex = config.accountCodeLabel ? trimmedArray[0].indexOf(config.accountCodeLabel) : -1;
let debitIndex = trimmedArray[0].indexOf(config.debitLabel);
let creditIndex = trimmedArray[0].indexOf(config.creditLabel);
let departmentIndex = trimmedArray[0].indexOf(config.departmentLabel[0]);
if (departmentIndex === -1) {
departmentIndex = trimmedArray[0].indexOf(config.departmentLabel[1]);
}
let classIndex = trimmedArray[0].indexOf(config.classLabel);
let legacyAccountIndex = trimmedArray[0].findIndex(element => element.startsWith(config.legacyAccountLabel));
if (externalSystem === 'Quickbooks' || externalSystem === 'QuickBooks') {
accCodeIndex = 0;
}
if (accCodeIndex !== -1 && debitIndex !== -1 && creditIndex !== -1 && classIndex !== -1) {
for (let k = 1; k < trimmedArray.length; k++) {
let finalObj = {
legacyAccountCode: trimmedArray[k][accCodeIndex],
debit: trimmedArray[k][debitIndex],
credit: trimmedArray[k][creditIndex],
department: departmentIndex !== -1 ? trimmedArray[k][departmentIndex] : '',
class: trimmedArray[k][classIndex],
legacyAccount: legacyAccountIndex !== -1 ? trimmedArray[k][legacyAccountIndex] : ''
};
finalArray.push(finalObj);
}
}
}
return finalArray;
},
/**
*
* @param {*} fileContents
* @returns
*/
getDataFromEXCEL(fileContents) {
let data = [];
let workbook = XLSX.read(fileContents, { type: 'base64' });
let sheetName = workbook.SheetNames[0]; // Assuming the first sheet
let sheet = workbook.Sheets[sheetName];
let range = XLSX.utils.decode_range(sheet['!ref']);
for (let rowIndex = range.s.r; rowIndex <= range.e.r; rowIndex++) {
let row = [];
for (let colIndex = range.s.c; colIndex <= range.e.c; colIndex++) {
let cellAddress = XLSX.utils.encode_cell({ r: rowIndex, c: colIndex });
let cellValue = sheet[cellAddress] ? sheet[cellAddress].v : '';
row.push(cellValue);
}
data.push(row);
}
data = exports.cleanArray(data); //To remove leading and trailing whitespace from array elements
return data;
},
/**
* Function to remove leading and trailing whitespace from array elements
* @param {*} arr
* @returns
*/
cleanArray(arr) {
for (var i = 0; i < arr.length; i++) {
for (var j = 0; j < arr[i].length; j++) {
if (typeof arr[i][j] === 'string') {
arr[i][j] = arr[i][j].trim();
}
}
}
return arr;
},
/**
* Creates a Journal Entry record in NetSuite based on provided data.
* @param {*} JEBodyData - An object containing data related to the journal entry header.
* @param {*} JElineData - An array of line items for the journal entry.
* @returns {object} An object with journal entry data or error messages.
*/
journalCreate(JEBodyData, JElineData) {
try {
let accountMappingArray = exports.accountMappingSearch(JEBodyData.subsidiaryId);
let departementArray = exports.departmentSearch(JEBodyData.subsidiaryId);
let classArray = exports.classSearch(JEBodyData.subsidiaryId);
let recordObj = record.create({
type: record.Type.JOURNAL_ENTRY,
isDynamic: true,
});
recordObj.setValue({ fieldId: 'subsidiary', value: JEBodyData.subsidiaryId });
recordObj.setValue({ fieldId: 'currency', value: '1' }); // Default currency 'AUD'
recordObj.setText({ fieldId: 'trandate', text: JEBodyData.journalEntryDate });
recordObj.setValue({ fieldId: 'reversaldate', value: JEBodyData.reversalDate });
recordObj.setValue({ fieldId: 'custbody_cogfs_jetype', value: true });
recordObj.setValue({ fieldId: 'memo', value: JEBodyData.memo });
for (let a = 0; a < JElineData.length; a++) {
let legacyAccountCode = typeof JElineData[a].legacyAccountCode === 'string' ? JElineData[a].legacyAccountCode : String(JElineData[a].legacyAccountCode);
let netSuiteAccount = accountMappingArray.find(item => item.legacyAccount.value === legacyAccountCode);
let netSuiteAccountID = netSuiteAccount?.NSAccount?.value;
let debit = exports.convertToNumber(JElineData[a].debit);
let credit = exports.convertToNumber(JElineData[a].credit);
if (checkForParameter(netSuiteAccountID)) {
if ((debit > 0 && credit === 0) || (debit === 0 && credit > 0)) {
let classDetails = classArray.find(item => item.namenohierarchy.value === JElineData[a].class);
let classId = classDetails?.internalid?.value;
if (checkForParameter(classId)) {
try {
let department = departementArray.find(item => item.namenohierarchy.value === JElineData[a].department);
let departmentId = department?.internalid?.value || DEFAULT_COSTCENTER;
recordObj.selectNewLine({ sublistId: 'line' });
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'account',
value: netSuiteAccountID,
});
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'debit',
value: debit,
});
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'credit',
value: credit,
});
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'class',
value: classId,
});
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'department',
value: departmentId,
});
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'custcol_jcs_cogf_legacy_account',
value: JElineData[a].legacyAccount,
});
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'memo',
value: JEBodyData.memo,
});
recordObj.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'location',
value: JEBodyData.defaultLocation,
});
recordObj.commitLine({ sublistId: 'line' });
} catch (err) {
JElineData[a].message = err.message;
}
} else {
JElineData[a].message = 'Class Value Missing or Incorrect';
}
} else if (debit === 0 && credit === 0) {
} else {
JElineData[a].message = 'Both Debit and Credit amounts are available';
}
} else {
JElineData[a].message = 'NetSuite Account Not Found in the Mapping Record';
}
}
let messagesArray = JElineData.map(item => item.message).filter(message => message !== undefined);
if (messagesArray.length > 0) {
return JElineData;
} else {
try {
JEBodyData.journalEntryId = recordObj.save({ ignoreMandatoryFields: true });
return JEBodyData;
} catch (e) {
JEBodyData.message = e.message;
return JEBodyData;
}
}
} catch (e) {
JEBodyData.message = e.message;
return JEBodyData;
}
},
/**
*
* @param {*} errorType
* @param {*} JEBodyData
* @param {*} fileRecordObj
* @param {*} fileContents
*/
handleError(errorType, externalSystem, JEBodyData, fileRecordObj, fileContents) {
let newErrorFileData = exports.createErrorFile(externalSystem, fileContents, errorType, JEBodyData);
let newErrorFileObj = exports.folderChange(fileRecordObj, newErrorFileData, 'Failed Files');
exports.errorEmail(errorType, newErrorFileObj, JEBodyData, fileRecordObj.folder);
},
/**
* Creates an error EXCEL string by appending error reasons to the original EXCEL data.
* @param {string} externalSystem - The external accounting system.
* @param {string} fileContents - The original EXCEL file contents as a string.
* @param {string} error - The type of error ('body' or 'line').
* @param {object} journalEntryReturn - An object containing error messages for body or line items.
* @returns {string} The modified EXCEL data with error reasons.
*/
createErrorFile(externalSystem, fileContents, errorType, journalEntryReturn) {
let excelData = exports.getDataFromEXCEL(fileContents);
let config = HEADING_LABEL[externalSystem];
if (!config) {
// Handle an invalid external system.
excelData[0].push('Error Reason');
excelData[0].push(journalEntryReturn.message);
} else {
let startRow = -1;
let endRow = -1;
// Iterate through excelData to find start and end rows
for (let i = 0; i < excelData.length; i++) {
for (let j = 0; j < excelData[i].length; j++) {
if (startRow === -1 && config.startIndexLabel === excelData[i][j]) {
startRow = i;
}
if (config.endIndexLabel === excelData[i][j]) {
endRow = i;
break;
}
}
}
if (startRow !== -1 && endRow !== -1) {
// Adding Error Reason Column after last column or Class Column.
let elementWithValue = config.classLabel;
for (let q = excelData[startRow].length - 1; q >= 0; q--) {
if (excelData[startRow][q] !== "") {
elementWithValue = excelData[startRow][q];
break;
}
}
let errorIndex = excelData[startRow].indexOf(elementWithValue) + 1;
excelData[startRow].splice(errorIndex, 0, "Error Reason");
if (errorType === 'body') {
excelData[startRow + 1][errorIndex] = journalEntryReturn.message;
} else if (errorType === 'line') {
let accCodeIndex = config.accountCodeLabel ? excelData[startRow].indexOf(config.accountCodeLabel) : -1;
let debitIndex = excelData[startRow].indexOf(config.debitLabel);
let creditIndex = excelData[startRow].indexOf(config.creditLabel);
let classIndex = excelData[startRow].indexOf(config.classLabel);
if (externalSystem === 'Quickbooks' || externalSystem === 'QuickBooks') {
accCodeIndex = 0;
}
for (let j = startRow + 1; j < endRow; j++) {
let matchingLine = journalEntryReturn.find(line =>
line.legacyAccountCode === excelData[j][accCodeIndex] &&
line.debit === excelData[j][debitIndex] &&
line.credit === excelData[j][creditIndex] &&
line.class === excelData[j][classIndex]
);
if (matchingLine) {
excelData[j][errorIndex] = matchingLine.message;
}
}
}
} else {
excelData[0].push('Error Reason');
excelData[0].push(journalEntryReturn.message);
}
}
let wb = XLSX.utils.book_new();
let ws = XLSX.utils.aoa_to_sheet(excelData);
XLSX.utils.book_append_sheet(wb, ws, "Trial Balance");
return XLSX.write(wb, { bookType: 'xlsx', type: 'base64' });
},
/**
*
* @param {*} value
* @returns
*/
convertToNumber(value) {
if (checkForParameter(value) && typeof value === 'string') {
// Remove currency symbols and commas, then convert to a float
let numericValue = parseFloat(value.replace(/[^0-9.-]/g, '')) || 0;
return isNaN(numericValue) ? null : numericValue;
} else if (!checkForParameter(value)) {
return 0;
}
return value;
},
/**
* Creates a new EXCEL file with modified data and moves it to a specified subfolder.
* Deletes the original file if the new file is created successfully.
* @param {object} fileObj - The original file object to be replaced.
* @param {string} newFileData - The modified EXCEL data as a string.
* @param {string} subfolder - The name of the subfolder to move the new file to.
* @returns {object} The new file object after moving it to the subfolder.
*/
folderChange(fileRecordObj, newFileData, subfolder) {
let folderID = exports.folderSearch(subfolder, fileRecordObj.folder);
if (subfolder === 'Processed Files') {
fileRecordObj.folder = folderID[0]?.folderID?.value;
fileRecordObj.save();
} else if (subfolder === 'Failed Files') {
let newFileObj = file.create({
name: fileRecordObj.name,
fileType: file.Type.EXCEL,
contents: newFileData,
folder: folderID[0]?.folderID?.value,
});
let newFileID = newFileObj.save();
log.debug('newFileID', newFileID);
if (checkForParameter(newFileID)) {
file.delete({
id: fileRecordObj.id
});
}
return newFileObj;
}
},
/**
* Sends an error email to specified recipients with error details and an attached file.
* @param {string} error - The type of error ('body' or 'line').
* @param {object} newFileObj - The new file object to be attached to the email.
* @param {object} journalEntryReturn - The journal entry return object containing error information.
* @param {Array} emailRoles
*/
errorEmail(error, newFileObj, journalEntryReturn, mainFolder) {
let emailRoles = exports.roleDefine(mainFolder);
// Search for employees with the specified role and extract their email addresses
let employee = exports.employeeSearch(emailRoles);
let emailArray = employee.map(item => item.Email.value);
let recipientsEmail = emailArray.slice(0, 10); // Limit recipients to the first 10 email addresses
if (recipientsEmail.length > 0) {
let body;
if (error === 'body') {
body = 'Hi All,<br><br> We encountered an error during the Trail Balance Journal Entry Migration, <br><br>Error: ' + journalEntryReturn.message + '<br><br>Kindly Check the attched file. <br><br>Thank you.';
} else if (error === 'line') {
body = 'Hi All,<br><br> We encountered an error during the Trail Balance Journal Entry Migration, <br><br> The error details was added in the corresponding line <br><br>Kindly Check the attched file for more details. <br><br>Thank you.';
}
email.send({
author: -5,
recipients: recipientsEmail,
subject: 'Error in TB Journal Migration',
body: body,
attachments: [newFileObj]
});
}
},
/**
*
* @param {*} folder
* @returns
*/
roleDefine(folder) {
let folderData = FOLDER_DATA.find((item) => item.folderId === folder);
return folderData ? folderData.roles : [];
},
/**
* Searches for an accounting period based on its name.
* @param {string} periodName - The name of the accounting period to search for.
* @returns {array} An array of accounting periods matching the provided name.
*/
postingPeriodSearch(periodName) {
let accountingperiodSearchObj = search.create({
type: "accountingperiod",
filters:
[
["isquarter", "is", "F"],
"AND",
["isyear", "is", "F"],
"AND",
["closed", "is", "F"],
"AND",
["periodname", "is", periodName]
],
columns:
[
search.createColumn({ name: "internalid" }),
search.createColumn({ name: "enddate" })
]
});
return iterateSavedSearch(accountingperiodSearchObj, fetchSavedSearchColumn(accountingperiodSearchObj, 'name'));
},
/**
* Searches for a subsidiary based on its name.
* @param {string} subsidiaryName - The name of the subsidiary to search for.
* @returns {array} An array of subsidiaries matching the provided name.
*/
subsidiarySearch(subsidiaryName) {
let subsidiarySearchObj = search.create({
type: "subsidiary",
filters:
[
["formulatext: {namenohierarchy}", "is", subsidiaryName]
],
columns:
[
search.createColumn({ name: "name", sort: search.Sort.ASC }),
search.createColumn({ name: "internalid" }),
search.createColumn({ name: "namenohierarchy" }),
search.createColumn({ name: "custrecord_jcs_sub_location" })
]
});
return iterateSavedSearch(subsidiarySearchObj, fetchSavedSearchColumn(subsidiarySearchObj, 'name'));
},
/**
* Searches for a NetSuite account mapping based on legacy account and subsidiary.
* @returns {array} An array of NetSuite account mappings matching the provided legacy account and subsidiary.
*/
accountMappingSearch(subsidiaryId) {
let customrecord_cogfs_tbuploadmappingsSearchObj = search.create({
type: "customrecord_cogfs_tbuploadmappings",
filters:
[
["custrecord_cogfs_tbupload_parent", "anyof", subsidiaryId],
"AND",
["isinactive", "is", "F"],
],
columns:
[
search.createColumn({ name: "custrecord_cogfs_tbupload_nsaccount", label: 'NSAccount' }),
search.createColumn({ name: "custrecord_cogfs_tbupload_legacy", label: 'legacyAccount' }),
search.createColumn({ name: "custrecord_cogfs_tbupload_parent", label: 'subsidiary' }),
search.createColumn({ name: "internalid", label: 'internalid' })
]
});
return iterateSavedSearch(customrecord_cogfs_tbuploadmappingsSearchObj, fetchSavedSearchColumn(customrecord_cogfs_tbuploadmappingsSearchObj, 'label'));
},
/**
* Search for a fetching department based on subsidiary.
* @returns {array} An array of of department for the corresponding subsidiary.
*/
departmentSearch(subsidiaryId) {
let departmentSearchObj = search.create({
type: "department",
filters:
[
["isinactive", "is", "F"],
"AND",
["subsidiary", "anyof", subsidiaryId]
],
columns:
[
search.createColumn({ name: "namenohierarchy" }),
search.createColumn({ name: "name" }),
search.createColumn({ name: "internalid" })
]
});
return iterateSavedSearch(departmentSearchObj, fetchSavedSearchColumn(departmentSearchObj, 'name'));
},
/**
* Search for a fetching Class based on subsidiary.
* @returns {array} An array of of Class for the corresponding subsidiary.
*/
classSearch(subsidiaryId) {
let classSearchObj = search.create({
type: "classification",
filters:
[
["isinactive", "is", "F"],
"AND",
["subsidiary", "anyof", subsidiaryId]
],
columns:
[
search.createColumn({ name: "namenohierarchy" }),
search.createColumn({ name: "name" }),
search.createColumn({ name: "internalid" })
]
});
return iterateSavedSearch(classSearchObj, fetchSavedSearchColumn(classSearchObj, 'name'));
},
/**
* Searches for a folder based on its name and parent folder.
* @param {string} name - The name of the folder to search for.
* @param {string} parent - The parent folder ID.
* @returns {array} An array of folders matching the provided name and parent folder.
*/
folderSearch(name, parent) {
let folderSearchObj = search.create({
type: "folder",
filters:
[
["parent", "anyof", parent],
"AND",
["name", "is", name]
],
columns:
[
search.createColumn({ name: "internalid", label: "folderID" })
]
});
return iterateSavedSearch(folderSearchObj, fetchSavedSearchColumn(folderSearchObj, 'label'));
},
/**
* Searches for employees with a specific role and non-empty email addresses.
* @param {number} role - The role ID to search for.
* @returns {array} An array of employees matching the provided role and having non-empty email addresses.
*/
employeeSearch(role) {
let employeeSearchObj = search.create({
type: "employee",
filters:
[
["role", "anyof", role],
"AND",
["isinactive", "is", "F"],
"AND",
["email", "isnotempty", ""]
],
columns:
[
search.createColumn({ name: "internalid", label: "empID" }),
search.createColumn({ name: "email", label: "Email" })
]
});
return iterateSavedSearch(employeeSearchObj, fetchSavedSearchColumn(employeeSearchObj, 'label'));
}
}
applyTryCatch(exports, "exports");
function applyTryCatch(DATA_OBJ, NAME) {
const tryCatch = function (myfunction, key) {
return function () {
try {
return myfunction.apply(this, arguments);
} catch (e) {
log.error("error in " + key, e);
errorArray.push(e.message ? e.message : e);
return false;
}
};
}
for (let key in DATA_OBJ) {
if (typeof DATA_OBJ[key] === "function") {
DATA_OBJ[key] = tryCatch(DATA_OBJ[key], NAME + "." + key);
}
}
}
function fetchSavedSearchColumn(savedSearchObj, priorityKey) {
try {
let columns = savedSearchObj.columns;
let columnsData = {},
columnName = '';
columns.forEach(function (result) {
columnName = '';
if (result[priorityKey]) {
columnName += result[priorityKey];
} else {
if (result.summary)
columnName += result.summary + '__';
if (result.formula)
columnName += result.formula + '__';
if (result.join)
columnName += result.join + '__';
columnName += result.name;
}
columnsData[columnName] = result;
});
return columnsData;
} catch (e) {
log.error({ title: 'error@fetchSavedSearchColumn', details: e });
}
}
function formatSingleSavedSearchResult(searchResult, columns) {
try {
let responseObj = {};
for (let column in columns) {
responseObj[column] = {
value: searchResult.getValue(columns[column]),
text: searchResult.getText(columns[column])
};
}
return responseObj;
} catch (e) {
log.error({ title: 'error@formatSingleSavedSearchResult', details: e });
}
}
function iterateSavedSearch(searchObj, columns) {
try {
if (!checkForParameter(searchObj)) {
return [];
}
if (!checkForParameter(columns)) {
columns = fetchSavedSearchColumn(searchObj);
}
let response = [];
let searchPageRanges;
try {
searchPageRanges = searchObj.runPaged({
pageSize: 1000
});
} catch (err) {
return [];
}
if (searchPageRanges.pageRanges.length < 1) {
return [];
}
let pageRangeLength = searchPageRanges.pageRanges.length;
for (let pageIndex = 0; pageIndex < pageRangeLength; pageIndex++) {
searchPageRanges.fetch({
index: pageIndex
}).data.forEach(function (result) {
response.push(formatSingleSavedSearchResult(result, columns));
});
}
return response;
} catch (e) {
log.error({ title: 'error@iterateSavedSearch', details: e });
}
}
function checkForParameter(parameter, parameterName) {
try {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false' && parameter !== '[]' && parameter !== '{}') {
return true;
} else {
if (parameterName) {
return false;
}
}
} catch (e) {
log.error({ title: "error@checkForParameter", details: e });
}
}
return { getInputData, reduce, summarize }
});