Jira Code: OTG-58
The client will push CSV files to File cabinet from their SFTP server. Once files reach in NetSuite, the schedule script works every 15 minutes and process these CSV files. Either, CSV files are converted to sales orders and moved to PROCESSED folder or CSV files are pushed to TO BE REVIEWED orders.
OTG-58 JJ SS Download files and Create Order
/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
/**
/*******************************************************************************
* OX Tools Global | OTG-58 |
* **************************************************************************
* Date Created : 24 June 2019
*
* Author: Jobin & Jismi IT Services LLP
*
*******************************************************************************
* REVISION HISTORY
**/
define(['N/https', 'N/search', 'N/runtime', 'N/task', 'N/url', 'N/format', 'N/log', 'N/email', 'N/record', 'N/file'],
function (https, search, runtime, task, url, format, log, email, record, file) {
var CUSTOMER_OBJ = {};
var ITEM_OBJ = {};
var FILE_OBJ = {
'TO_BE_REVIEWED': '670955',
'PROCESSED': '670954',
'TO_BE_SEND': '670956',
'TO_BE_PROCESSED': '670953'
};
var reasonforError = '';
//Common Try-Catch function
function applyTryCatch(DATA_OBJ, NAME) {
function tryCatch(myfunction, key) {
return function () {
try {
return myfunction.apply(this, arguments);
} catch (e) {
log.error("error in " + key, e);
return false;
}
};
}
for (var key in DATA_OBJ) {
if (typeof DATA_OBJ[key] === "function") {
DATA_OBJ[key] = tryCatch(DATA_OBJ[key], NAME + "." + key);
}
}
}
var main = {
moveFile: function (fileObj, toFolder, fileId) {
log.debug('File pushed to ' + toFolder + ' folder')
if (!fileObj)
fileObj = file.load({ id: fileId });
if (toFolder == 'TO_BE_REVIEWED') {
email.send({
author: -5,
recipients: 'sales@oxtools.com',
subject: 'Error File Pushed to Folder',
body: 'A file is moved to "TO BE REVIEWED" folder<br>' + reasonforError,
// attachments: [fileObj],
});
}
fileObj.folder = FILE_OBJ[toFolder];
var newFileID = fileObj.save();
log.debug('NEW FILE ID', newFileID)
// return fileObj.save();
},
getCustomerID: function (REFERENCE_NUMBER) {
if (CUSTOMER_OBJ[REFERENCE_NUMBER])
return CUSTOMER_OBJ[REFERENCE_NUMBER];
var customerSearchObj = search.create({
type: "customer",
filters:
[
["custentity_gln", "startswith", REFERENCE_NUMBER] // starts with ???
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "custentity_gln", label: "EDI Reference Number" })
]
});
var custObj = {};
var searchResultCount = customerSearchObj.runPaged().count;
customerSearchObj.run().each(function (result) {
custObj.internalid = result.getValue(customerSearchObj.columns[0]);
custObj.referencenumber = result.getValue(customerSearchObj.columns[1]);
CUSTOMER_OBJ[REFERENCE_NUMBER] = custObj.internalid;
return false;
});
return CUSTOMER_OBJ[REFERENCE_NUMBER];
},
getItemId: function (UPC_CODE) {
if (!UPC_CODE)
return false;
if (ITEM_OBJ[UPC_CODE])
return ITEM_OBJ[UPC_CODE];
var itemSearchObj = search.create({
type: "item",
filters:
[
["upccode", "is", UPC_CODE],
"AND",
["subsidiary", "anyof", "1"] //OX Tools AU - items have same UPC code but would be in different subsidiary
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({
name: "itemid",
sort: search.Sort.ASC,
label: "Name"
})
]
});
itemSearchObj.run().each(function (result) {
ITEM_OBJ[UPC_CODE] = result.getValue(itemSearchObj.columns[0]);;
return false;
});
return ITEM_OBJ[UPC_CODE] ? ITEM_OBJ[UPC_CODE] : false;
},
parseItems: function (DATA_ARRAY) {
try {
var mappedItems = [];
// rest part - IMPORTANT
for (var j = 0, len = DATA_ARRAY.length; j < len; j++) {
if (DATA_ARRAY[j].length == 11) {
if (DATA_ARRAY[j][7]) {
var itemID = main.getItemId(DATA_ARRAY[j][7]);
if (itemID)
mappedItems.push({
item: itemID,
quantity: DATA_ARRAY[j][8],
price: DATA_ARRAY[j][9]
});
else {
log.debug('No barcorde match for any item')
reasonforError = 'Corresponding Item on NetSuite could not be identified. The item barcode in CSV file is ' + DATA_ARRAY[j][7] + ' .';
return [];
}
}
else {
reasonforError = 'No barcode could not be found for the item on CSV file. The line is ' + (j + 1) + ' .';
return [];
}
} else if (DATA_ARRAY[j].length == 1 && j == (len - 1)) { // if last line with no elements
} else {
log.debug('notify empty array');
reasonforError = 'CSV Column Mismatch .';
return [];
}
}
return mappedItems;
} catch (err) { }
reasonforError = 'Error while parsing Items on CSV .';
return [];
},
execute: function (scriptContext) {
try {
var searchFile = main.searchforFiles();
} catch (er) {
log.debug('err@execute', er.message);
}
},
searchforFiles: function () {
try {
var fileId = [];
var singleFileId;
var fileSearchObj = search.create({
type: "file",
filters: [
["folder", "anyof", "670953"] //extra filter
],
columns: [
search.createColumn({
name: "name",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({
name: "internalid",
label: "Internal ID"
})
]
});
fileSearchObj.run().each(function (result) {
var fileID = result.getValue(fileSearchObj.columns[1]);
var fileName = result.getValue(fileSearchObj.columns[0]);
main.processSalesOrder(fileID);
return true;
});
} catch (er) {
log.debug('err@searchforFiles', er);
}
},
searchforDuplicateFiles: function (CUSTOMER_ID, ORDER_NO) {
var salesorderSearchObj = search.create({
type: "salesorder",
filters:
[
["type", "anyof", "SalesOrd"],
"AND",
["custbody_cust_ref_no", "is", CUSTOMER_ID],
"AND",
["customer.internalid", "anyof", ORDER_NO]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "entity", label: "Name" })
]
});
var duplicateFilesNumber = salesorderSearchObj.runPaged().count;
log.debug("Number of Duplicate Files", duplicateFilesNumber);
return duplicateFilesNumber;
},
processSalesOrder: function (fileID) {
try {
// load the file from the folder 'To be processed'
// ID : internal id or relative path
var itemsArr = [];
var fileObj = file.load({
id: fileID
})
var fileData = fileObj.getContents();
var DATA_ARRAY = CSVToArray(fileData);
if (DATA_ARRAY.length != 0) {
var customerId = false;
if (DATA_ARRAY[0].length == 11) {
customerId = main.getCustomerID(DATA_ARRAY[0][1]);
var createdDate = DATA_ARRAY[0][2];
var customerOrderNo = DATA_ARRAY[0][3];
} else
reasonforError = 'CSV Column Mismatch .';
if (!!customerId && !!customerOrderNo) {
var duplicateFilesNumber = main.searchforDuplicateFiles(customerId, customerOrderNo);
if (duplicateFilesNumber == 0) {
itemsArr = main.parseItems(DATA_ARRAY);
if (itemsArr.length != 0) {
log.debug('itemsArr found')
var soRec = record.transform({
fromType: record.Type.CUSTOMER,
fromId: customerId,
toType: record.Type.SALES_ORDER,
isDynamic: true
});
var shipState = soRec.getValue({ fieldId: 'shipstate' });
if (shipState == 'Western Australia')
soRec.setValue({ fieldId: 'location', value: 45 });
else
soRec.setValue({ fieldId: 'location', value: 44 });
if (createdDate) {
createdDate = createdDate.toString();
var arr = [];
arr = createdDate.split('/');
var NSFileDate = arr[1] + '/' + arr[0] + '/' + arr[2];
var createdDate = new Date(NSFileDate);
soRec.setValue({ fieldId: 'trandate', value: createdDate });
}
soRec.setValue({ fieldId: 'memo', value: 'EDI' });
soRec.setValue({ fieldId: 'custbody11', value: 8 }); // ORDER RECEIVED BY
soRec.setValue({ fieldId: 'otherrefnum', value: customerOrderNo });
var lineCount = itemsArr.length;
for (var k = 0; k < lineCount; k++) {
var lineNum = soRec.selectNewLine({
sublistId: 'item'
});
soRec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
value: itemsArr[k].item
});
soRec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'quantity',
value: itemsArr[k].quantity
});
soRec.commitLine({
sublistId: 'item'
});
}
// check whether the populated price equals price in the csv
for (var m = 0; m < lineCount; m++) {
var lineNum = soRec.selectLine({
sublistId: 'item',
line: m
});
var autoRate = soRec.getCurrentSublistValue({
sublistId: 'item',
fieldId: 'rate'
});
// log.debug('PRICE MATCH', itemsArr[m].price + " and " + autoRate);
if (itemsArr[m].item) {
if (itemsArr[m].price != autoRate) {
// send email and move to 'To be Reviewed' folder
log.debug('Price mismatch');
reasonforError = 'Price on line item level in Netsuite Sales Order does not match with the price of the item on the CSV file .'
return main.moveFile(fileObj, 'TO_BE_REVIEWED', false);
}
}
}
var soRecID = soRec.save({
ignoreMandatoryFields: true
});
log.debug('soRecID', soRecID);
if (soRecID) {
// attach file to sales order
var attachFileID = record.attach({
record: {
type: 'file',
id: fileID
},
to: {
type: 'salesorder',
id: soRecID
}
});
}
return main.moveFile(fileObj, 'PROCESSED', false);
}
else
reasonforError += ' Error while proccessing items on CSV file .';
}
else {
log.debug('Duplicate Order');
reasonforError = 'Duplicate Order - Order already exists for the same customer with the exact order number .';
}
} else {
log.debug('Cannot Reference customer')
reasonforError = (!customerId) ? 'The EDI REFERENCE NUMBER in the csv file does not correspond to a CUSTOMER' : 'There is no Order Number in the CSV File .';
}
} else
reasonforError = 'Invalid CSV File .';
}
catch (er) {
log.debug('err@processSalesOrder', er.message);
reasonforError = 'Error while processing CSV and creating order .';
}
return main.moveFile(fileObj, 'TO_BE_REVIEWED', false);
}
};
applyTryCatch(main, 'main');
return main;
});
// ref: http://stackoverflow.com/a/1293163/2343
function CSVToArray(e, r) { r = r || ","; for (var n = new RegExp("(\\" + r + '|\\r?\\n|\\r|^)(?:"([^"]*(?:""[^"]*)*)"|([^"\\' + r + "\\r\\n]*))", "gi"), g = [[]], l = null; l = n.exec(e);) { var p, u = l[1]; u.length && u !== r && g.push([]), p = l[2] ? l[2].replace(new RegExp('""', "g"), '"') : l[3], g[g.length - 1].push(p) } return g }