Jira Code: ZR 21
Customized CSV import system to create an invoice for the customers from CSV. The invoice can be created to the customer from account ID. CSV columns should be always in the same order.
Custom record is created to upload the CSV file which is used to create an invoice. While saving the custom record, a user event script gets triggered and schedule a script which processes the CSV file and creates invoice record.
ZR-21 UE IMPORT CSV TRIGGER IMPORT PROCESS
/**
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
*/
define(['N/record', 'N/task', "N/file"],
function(record, task, file) {
/**
* Function definition to be triggered before record is loaded.
*
* @param {Object}
* scriptContext
* @param {Record}
* scriptContext.newRecord - New record
* @param {Record}
* scriptContext.oldRecord - Old record
* @param {string}
* scriptContext.type - Trigger type
* @Since 2015.2
*/
function afterSubmit(scriptContext) {
try {
if (scriptContext.type == "create") {
var cofigRecordNew = scriptContext.newRecord;
var CSVfileIdNew = cofigRecordNew.getValue({ fieldId: "custrecord_jj_zr21_csv_import_file" });
var csvFile = file.load({ id: CSVfileIdNew });
var isCSV = (csvFile.fileType == file.Type.CSV);
var isHeaderMatches = true;
if (isCSV) {
var csvFileContents = csvFile.getContents();
var csvDetailsLines = csvFileContents.split("\r\n");
var header = (csvDetailsLines[0]).split(",");
log.debug({ title: "header", details: header });
var staticHeader = ["the_ID", "Group/Garage", "Name", "Quantity", "Memo", "Date", "PO"];
var isValidHeader = (JSON.stringify(header) == JSON.stringify(staticHeader));
}
var recId = cofigRecordNew.getValue({ fieldId: 'id' });
var status = "";
var logERROR = "";
if (CSVfileIdNew != "" && CSVfileIdNew != null && CSVfileIdNew != undefined && isCSV && isValidHeader) {
try {
//schedule Schedule script
scheduleScrptTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscriptzr_21_ss_import_csv_process",
deploymentId: 'customdeployzr_21_ss_import_csv_process',
params: {
custscriptcustscript_zr21_ss_recordid: recId,
custscriptcustscript_zr21_ss_start: 1,
custscript_zr21_csv_file: CSVfileIdNew
}
});
var scriptTaskId = scheduleScrptTask.submit();
//log.debug({title:"CSV IMPORT SCHEDULED",details:scriptTaskId});
status = "STARTED";
logERROR += "STARTS"
} catch (err) {
//status="FAILED";
log.debug({ title: "CANNOT TRIGGER", details: err });
logERROR += "CANNOT TRIGGER";
}
} else {
log.debug({ title: "ERROR", details: "CSV_IMPORT_FILE_ID is invalid" });
if (scriptContext.type == "create") status = "FAILED";
if (!isCSV) status = "INVALID FILE";
else if (!isValidHeader) status = "INVALID HEADERS";
logERROR += "INVALID FILE/CREATE NEW";
}
//SET STATUS
if (scriptContext.type == "create")
var recId = record.submitFields({
id: recId,
type: "customrecord_jj_zor21_csv_import_conf",
values: {
custrecord_jj_zr21_status: status,
},
options: {
enableSourcing: false,
ignoreMandatoryFields: true
}
});
}
} catch (err) {
log.debug({ title: "ERROR_CSV_IMPORT_NOT_SCHEDULED", details: err });
}
}
return {
afterSubmit: afterSubmit
};
});
ZR-21 SS IMPORT CSV PROCESS FILE
/**
* * @NApiVersion 2.x
* @NScriptType ScheduledScript
*/
define(
['N/search', 'N/record', 'N/task', 'N/runtime', "N/file", 'N/format'],
function(search, record, task, runtime, file, format) {
function execute(scriptContext) {
try {
var start = runtime.getCurrentScript().getParameter(
"custscriptcustscript_zr21_ss_start");
var recId = runtime.getCurrentScript().getParameter(
"custscriptcustscript_zr21_ss_recordid");
var fileId = runtime.getCurrentScript().getParameter(
"custscript_zr21_csv_file");
log.debug("recId", recId)
// load record
var configuration = record.load({
id: recId,
type: "customrecord_jj_zor21_csv_import_conf",
});
// GET CURREENT DETAILS
var logCompleted = configuration.getValue({
fieldId: "custrecord_jj_zr21_lines_imported"
});
var logUnprocessed = configuration.getValue({
fieldId: "custrecord_jj_zr21_lines_faile"
});
var logPROCESSED = configuration.getValue({
fieldId: "custrecord_jj_zr21_lines_processed"
});
var logfailed = configuration.getValue({
fieldId: "custrecord_jj_zr21_lines_failed"
});
log.debug("logfailed", logfailed);
try {
var failedarrays = logfailed.split(",");
log.debug("failedarrays", failedarrays);
} catch (e) {
log.debug("empty failed array")
var failedarrays = [];
}
var logERROR = "";
// load file
var csvFile = file.load({
id: fileId
});
// get contents in file
var csvFileContents = csvFile.getContents();
var csvDetailsLines = csvFileContents.split("\r\n");
for (var i = start; i < csvDetailsLines.length; i++) {
var currentLine = csvDetailsLines[i];
var currentLineContents = currentLine.split(",");
var customerId = "";
if (currentLineContents[0] != "") {
var customerdetails = lookUpDestintionRecord(currentLineContents[0]);
customerId = customerdetails.destinationId;
}
log.debug("customerId==" + customerId, "Account ID ==" + currentLineContents[0]);
if (customerId != "" && customerId != undefined) {
// found record
try {
var invoicerecord = record.transform({
fromType: record.Type.CUSTOMER,
fromId: customerId,
toType: record.Type.INVOICE,
isDynamic: true,
});
invoicerecord.setValue({
fieldId: 'location',
value: 12
});
invoicerecord.setValue({
fieldId: 'memo',
value: currentLineContents[4]
});
invoicerecord.setValue({
fieldId: 'otherrefnum',
value: currentLineContents[6]
});
var recievedhiredate = currentLineContents[5];
try {
log.debug("recievedhiredate", recievedhiredate);
log.debug("recievedhiredate.indexOf(-)", recievedhiredate.indexOf("-"));
if (recievedhiredate.indexOf("-") > -1) {
recievedhiredate.replace(/-/g, "/");
}
log.debug("correctedrecievedhiredate", recievedhiredate);
var dateformatted = format.parse({
value: recievedhiredate,
type: format.Type.DATE
});
log.debug("dateformatted", dateformatted);
try {
invoicerecord.setValue({
fieldId: 'trandate',
value: dateformatted
});
} catch (e) {
invoicerecord.setText({
fieldId: 'trandate',
text: dateformatted
});
}
var posting = getposting(recievedhiredate);
log.debug("posting", posting);
invoicerecord.setText({
fieldId: 'postingperiod',
text: posting
});
} catch (e) {
log.debug("e in date setting", e);
}
invoicerecord.selectNewLine({
sublistId: 'item'
});
if (customerdetails.plantype == 1) {
invoicerecord.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
value: 1202,
ignoreFieldChange: false
});
} else if (customerdetails.plantype == 2) {
invoicerecord.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
value: 1203,
ignoreFieldChange: false
});
} else if (customerdetails.plantype == 7 || customerdetails.plantype == 8) {
invoicerecord.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
value: 1250,
ignoreFieldChange: false
});
}
invoicerecord.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'quantity',
value: currentLineContents[3],
ignoreFieldChange: false
});
invoicerecord.commitLine({
sublistId: 'item'
});
var recordId = invoicerecord.save();
log.debug("record", recordId);
logPROCESSED++
logCompleted++;
} catch (err) {
failedarrays.push(currentLineContents[0]);
logERROR += "\nCANNOT CREATE INVOICE FOR " +
customerId + "@LINE-" + i;
log.debug({
title: "ERROR@submitFields",
details: err
});
logUnprocessed++;
logPROCESSED++
}
} else {
failedarrays.push(currentLineContents[0]);
// No record with Value
logERROR += "\nNOT FOUND CUSTOMER for " +
currentLineContents[0] + "@line" + i;
log.debug({
title: "NOT FOUND CUSTOMER",
details: currentLineContents[0] + "@line-" + i
});
logUnprocessed++;
logPROCESSED++
}
var scriptObj = runtime.getCurrentScript();
var remainingTime = scriptObj.getRemainingUsage();
var isEnd = true;
// Check the remaining usage is too low
if (remainingTime < 1000) {
// reschedule Schedule script
scheduleScrptTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscriptzr_21_ss_import_csv_process",
deploymentId: 'customdeployzr_21_ss_import_csv_process',
params: {
custscriptcustscript_zr21_ss_recordid: recId,
custscriptcustscript_zr21_ss_start: i + 1,
custscript_zr21_csv_file: fileId
}
});
// set flag not the end of data.
isEnd = false;
logERROR += "\nCASCADE NEXT SCHEDULE SCRIPT"
var scriptTaskId = scheduleScrptTask.submit();
// break the loop
break;
}
}
log.debug("failedarrays.toString()", failedarrays.toString());
// UPDATE PROCESSING DETAILS
configuration.setValue({
fieldId: "custrecord_jj_zr21_lines_imported",
value: logCompleted
});
configuration.setValue({
fieldId: "custrecord_jj_zr21_lines_faile",
value: logUnprocessed
});
configuration.setValue({
fieldId: "custrecord_jj_zr21_lines_processed",
value: logPROCESSED
});
configuration.setValue({
fieldId: "custrecord_jj_zr21_lines_failed",
value: failedarrays.toString()
});
// configuration.setValue({fieldId:"custrecord_jj_mr85_log_details",value:logERROR});
// SET STATUS FINISHED IF COMPLETED
if (isEnd)
configuration.setValue({
fieldId: "custrecord_jj_zr21_status",
value: "FINISHED"
});
// save current configuration record
configuration.save();
} catch (err) {
log.debug({
title: "ERROR_CSV_IMPORT_PROCESS",
details: err
});
}
}
function getposting(date) {
try {
log.debug("date", date.toString());
var date = date.toString()
var arry = date.split("/");
log.debug("arry", arry);
log.debug("year", arry[2]);
var months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
log.debug("parseInt(arry[1])", arry[1] - 1);
var month = months[arry[1] - 1]
log.debug("month", month);
return month + " " + arry[2]
} catch (e) {
log.debug("E", e);
}
}
/* for getting customer ids */
function lookUpDestintionRecord(searchParams) {
var destinationId = "";
var plantype = ""
try {
var customerSearchObj = search.create({
type: "customer",
filters: [
["custentity7", "equalto", searchParams]
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "custentity3", label: "Plan Type" })
]
});
var searchResultCount = customerSearchObj.runPaged().count;
log.debug("customerSearchObj result count", searchResultCount);
customerSearchObj.run().each(function(result) {
destinationId = result.getValue("internalid");
plantype = result.getValue("custentity3");
return true;
});
} catch (err) {
log.debug({
title: "ERROR@lookUpDestintionRecord",
details: err
});
}
return {
destinationId: destinationId,
plantype: plantype
};
}
return {
execute: execute
};
});