User event script for Export CSV files of Purchase Order from NetSuite to AWS SFTP server. The script fetch the details from the PO and converted it to CSV files. Then CSV file will be stored in the file cabinet folder. The data from the file cabinet will export to SFTP server.
UserEvent Script
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/file', 'N/record', 'N/search', 'N/sftp'],
(file, record, search, sftp) => {
/**
* Defines the function definition that is executed after record is submitted.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.type - Trigger type; use values from the context.UserEventType enum
* @since 2015.2
*/
const afterSubmit = (scriptContext) => {
log.debug("scriptContext.type", scriptContext.type);
var salesOrderId = scriptContext.newRecord.getValue('createdfrom');
log.debug("inside afterSubmit dropship", salesOrderId);
const poRecord = scriptContext.newRecord;
const poid = poRecord.id;
log.debug("poid", poid);
let vendor = poRecord.getValue({
fieldId: 'entity'
});
log.debug("vendor", vendor);
let soId = poRecord.getValue({
fieldId: 'createdfrom'
});
log.debug('soId',soId);
// let soNum = poRecord.getText({
// fieldId: 'createdfrom'
// });
// log.debug('soNum',soNum);
// let soArr=soNum.split('#')
// var soType=soArr[0].toString().trim();
// log.debug('soType',soType);
// var field= search.lookupFields({type:'purchaseorder',id:soId,columns:['createdfrom.type']});
// log.debug('field',field);
// if (((scriptContext.type == 'dropship' ||(scriptContext.type == 'create' && soId)) && vendor == '65249')) {//834
if (((scriptContext.type == 'edit'||(scriptContext.type == 'create' && soId)) && vendor == '834')) {
log.debug("inside condition", true);
let orderDetails = getOrderData(poid);
log.debug("orderDetails", orderDetails);
if (orderDetails) {
let file = createCSVFileRequest(poid,orderDetails);
log.debug("File", file);
file && uploadFileToServer(file.fileObj);
}
}
}
function getOrderData(poid) {
try{
var orderDetails = '';
var purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters:
[
["type","anyof","PurchOrd"],
"AND",
["item.isdropshipitem","is","T"],
"AND",
["internalid","anyof",poid]
],
columns:
[
search.createColumn({name: "tranid", label: "Document Number"}),
search.createColumn({
name: "account",
join: "createdFrom",
label: "Account"
}),
search.createColumn({name: "item", label: "Item"}),
search.createColumn({name: "quantity", label: "Quantity"}),
search.createColumn({
name: "entity",
join: "createdFrom",
label: "Name"
}),
search.createColumn({name: "shipaddress1", label: "Shipping Address 1"}),
search.createColumn({name: "shipaddress2", label: "Shipping Address 2"}),
search.createColumn({name: "shipaddress3", label: "Shipping Address 3"}),
search.createColumn({name: "shipcity", label: "Shipping City"}),
search.createColumn({name: "shipcountry", label: "Shipping Country"}),
search.createColumn({name: "shipzip", label: "Shipping Zip"}),
search.createColumn({name: "shipphone", label: "Shipping Phone"}),
search.createColumn({name: "datecreated", label: "Date Created"}),
search.createColumn({name: "duedate", label: "Due Date/Receive By"})
]
});
var searchResultCount = purchaseorderSearchObj.runPaged().count;
log.debug("purchaseorderSearchObj result count",searchResultCount);
return purchaseorderSearchObj;
}
catch(error){
log.error("Error @getOrderData",error);
return '';
}
}
function createCSVFileRequest(poid,results){
try{
// var myContent;
var columns = results.columns;
var content = new Array();
var headers = new Array();
var temp = new Array();
var x = 0
headers[0] = "PO Number ";
headers[1] = "Customer Acc No";
headers[2] = "Part Number";
headers[3] = "Quantity";
headers[4] = "Customer Name ";
headers[5] = "Delivery Address 1";
headers[6] = "Delivery Address 2";
headers[7] = "Delivery Address 3";
headers[8] = "Delivery Town";
headers[9] = "Delivery Country";
headers[10] = "Delivery Post Code";
headers[11] = "Telephone Number";
headers[12] = "Order Date";
headers[13] = "Promised Date";
content[x] = headers;
x = 1;
results.run().each(function (result) {
content[x] = "";
var document= result.getValue(result.columns[0]);
var account= result.getValue(result.columns[1]);
var item= result.getText(result.columns[2]);
var quantity= result.getValue(result.columns[3]);
var entity= result.getText(result.columns[4]);
var shipaddress1= result.getValue(result.columns[5]);
var shipaddress2= result.getValue(result.columns[6]);
var shipaddress3= result.getValue(result.columns[7]);
var shipcity= result.getValue(result.columns[8]);
var shipcountry= result.getText(result.columns[9]);
var shipzip= result.getValue(result.columns[10]);
var shipphone= result.getValue(result.columns[11]);
var orderDate= result.getValue(result.columns[12]);
var dueDate= result.getValue(result.columns[13]);
temp.push('"'+document+'"')
temp.push('"'+account+'"');
temp.push('"'+item+'"');
temp.push('"'+quantity+'"');
temp.push('"'+entity+'"');
temp.push('"'+shipaddress1+'"');
temp.push('"'+shipaddress2+'"');
temp.push('"'+shipaddress3+'"');
temp.push('"'+shipcity+'"');
temp.push('"'+shipcountry+'"');
temp.push('"'+shipzip+'"');
temp.push('"'+shipphone+'"');
temp.push('"'+orderDate+'"');
temp.push('"'+dueDate+'"');
content[x] += temp;
log.debug("temp", temp);
log.debug("content[x]", content[x]);
x++;
log.debug("x",x);
return true;
});
var contents = '';
for (var z = 0; z < content.length; z++) {
contents += content[z].toString() + '\n';
}
log.debug("contents",contents);
let fileObj = file.create({
name: 'Pacific-Order of ' + poid+'.csv',
fileType: file.Type.CSV,
contents: contents,
folder: '6061'//Pacific Lifestyle Dropship Orders
});
let fileId = fileObj.save();
log.debug('fileId',fileId)
return {fileId: fileId,fileObj: fileObj};
//}
}catch(error){
log.error("Error @createCSVFileRequest",error);
}
}
function uploadFileToServer(fileObj){
try{
const myPwdGuid = "479b5e747f324d3c9ac055a70199d3c2";
const myHostKey = "AAAAB3NzaC1yc2EAAAADAQABAAABAQDW5s75gHDWU9IGQTKpBe3XTEh6ISYz/8cRlPpoIR+3J7BoMMTInx9v2MAbERMV+gWSW8qWAQOoC3VnF3wnNQXvsJgeINsvnRjG50FpAbzq9FAUzPxyxNBgLGQuBs1YCb12u+vI77FpnXeI1RPvuz7cHq1MGoIz5YUYT9EIZBmkrVsrKL3sEW6sacNdjW7w+O/OpzBQpM87A+K6hgGcV5vJCWoQO4ghacO7LolEebDYMv3PnWK6bVgMcGYFCdN2haK0/8TwRl242eLMv59R1AWUmB7qSW4b/OlIv2UeTRsOQhZY4lNcz7KvfgxKtX+xd6IPufkRf+OXxMmpj8IpkebH";
log.debug('myPwdGuid',myPwdGuid)
// Establish a connection to a remote SFTP server
let connection = sftp.createConnection({
username: 'MazeUser',
passwordGuid: myPwdGuid,
url: 's-7e1df0f0dc9148c9a.server.transfer.eu-west-2.amazonaws.com',
hostKey: myHostKey,
directory:'/maze-hl-bucket/Pacific-Order Waiting to Export'
});
log.debug('connection',connection)
// Upload the file to the remote server
try{
fileObj && connection.upload({
// directory: '/maze-hl-bucket/Order Waiting to Import',
// filename: 'Maze-Stock of ' + internalID + '.csv',
file: fileObj,
replaceExisting: true
});
}catch(e){
log.debug("error @connection upload",e);
}
}catch(error){
log.error("Error @UploadFileToServer",error);
}
}
return {afterSubmit}
});
Suitelet
Suitelet script is used to generate the host key and PasswordGUID. Hostkey and PasswordGUID is needed to make connection between the Netsuite and SFTP server.
var HTTPSMODULE, SFTPMODULE, SERVERWIDGETMODULE;
var HOST_KEY_TOOL_URL = 'https://ursuscode.com/tools/sshkeyscan.php?url=';
/**
*@NApiVersion 2.x
*@NScriptType Suitelet
*@NModuleScope Public
*/
define(["N/https", "N/sftp", "N/ui/serverWidget"], runSuitelet);
//********************** MAIN FUNCTION **********************
function runSuitelet(https, sftp, serverwidget) {
HTTPSMODULE = https;
SERVERWIDGETMODULE = serverwidget;
SFTPMODULE = sftp;
var returnObj = {};
returnObj.onRequest = execute;
return returnObj;
}
function execute(context) {
var method = context.request.method;
var form = getFormTemplate(method);
if (method == 'GET') {
form = addSelectorFields(form);
}
if (method == 'POST') {
var selectaction = context.request.parameters.selectaction;
if (selectaction == 'getpasswordguid') {
form = addPasswordGUID1Fields(form);
}
else if (selectaction == 'gethostkey') {
form = addHostKeyFields(form);
}
else if (selectaction == 'downloadfile') {
form = addDownloadFileFields(form);
} else {
var password = context.request.parameters.password;
var username = context.request.parameters.username;
var passwordGuid = context.request.parameters.passwordguid;
var url = context.request.parameters.url;
var hostKey = context.request.parameters.hostkey;
var hostKeyType = context.request.parameters.hostkeytype;
var port = context.request.parameters.port;
var directory = context.request.parameters.directory;
var timeout = context.request.parameters.timeout;
var filename = context.request.parameters.filename;
var restricttoscriptids = context.request.parameters.restricttoscriptids;
var restricttodomains = context.request.parameters.restricttodomains;
if (restricttoscriptids && restricttodomains) {
form = addPasswordGUID2Fields(form, restricttoscriptids, restricttodomains);
}
if (password) {
form.addField({
id: 'passwordguidresponse',
type: SERVERWIDGETMODULE.FieldType.LONGTEXT,
label: 'PasswordGUID Response',
displayType: SERVERWIDGETMODULE.FieldDisplayType.INLINE
}).defaultValue = password;
}
if (url && passwordGuid && hostKey && filename) {
var sftpConnection = getSFTPConnection(username, passwordGuid, url, hostKey, hostKeyType, port, directory, timeout);
var downloadedFile = sftpConnection.download({
filename: filename
}).getContents();
form.addField({
id: 'filecontents',
type: SERVERWIDGETMODULE.FieldType.LONGTEXT,
label: 'File Contents',
displayType: SERVERWIDGETMODULE.FieldDisplayType.INLINE
}).defaultValue = downloadedFile;
} else if (url) {
var myUrl = HOST_KEY_TOOL_URL + url + "&port=" + port + "&type=" + hostKeyType;
var theResponse = HTTPSMODULE.get({ url: myUrl }).body;
form.addField({
id: 'hostkeyresponse',
type: SERVERWIDGETMODULE.FieldType.LONGTEXT,
label: 'Host Key Response',
displayType: SERVERWIDGETMODULE.FieldDisplayType.INLINE
}).defaultValue = theResponse;
}
}
}
context.response.writePage(form);
return;
}
function addSelectorFields(form) {
var select = form.addField({
id: 'selectaction',
type: SERVERWIDGETMODULE.FieldType.SELECT,
label: 'Select Action'
});
select.addSelectOption({
value: 'getpasswordguid',
text: 'Get Password GUID',
});
select.addSelectOption({
value: 'gethostkey',
text: 'Get Host Key'
});
select.addSelectOption({
value: 'downloadfile',
text: 'Download File'
});
return form;
}
function addPasswordGUID1Fields(form) {
form.addField({
id: 'restricttoscriptids',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'Restrict To Script Ids',
}).isMandatory = true;
form.addField({
id: 'restricttodomains',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'Restrict To Domains',
}).isMandatory = true;
return form;
}
function addPasswordGUID2Fields(form, restrictToScriptIds, restrictToDomains) {
form.addCredentialField({
id: 'password',
label: 'Password',
restrictToScriptIds: restrictToScriptIds.replace(' ', '').split(','),
restrictToDomains: restrictToDomains.replace(' ', '').split(','),
});
return form;
}
function addHostKeyFields(form) {
form.addField({
id: 'url',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'URL (Required)',
});
form.addField({
id: 'port',
type: SERVERWIDGETMODULE.FieldType.INTEGER,
label: 'Port (Optional)',
});
form.addField({
id: 'hostkeytype',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'Type (Optional)',
});
return form;
}
function addDownloadFileFields(form) {
form.addField({
id: 'url',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'URL (Required)',
});
form.addField({
id: 'username',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'Username',
});
form.addField({
id: 'passwordguid',
type: SERVERWIDGETMODULE.FieldType.LONGTEXT,
label: 'PasswordGuid (Required)',
});
form.addField({
id: 'hostkey',
type: SERVERWIDGETMODULE.FieldType.LONGTEXT,
label: 'Host Key (Required)',
});
form.addField({
id: 'hostkeytype',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'Host Key Type',
});
form.addField({
id: 'filename',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'File Name',
});
form.addField({
id: 'port',
type: SERVERWIDGETMODULE.FieldType.INTEGER,
label: 'Port',
});
form.addField({
id: 'directory',
type: SERVERWIDGETMODULE.FieldType.TEXT,
label: 'Directory',
});
form.addField({
id: 'timeout',
type: SERVERWIDGETMODULE.FieldType.INTEGER,
label: 'Timeout',
});
return form;
}
function getFormTemplate() {
var form = SERVERWIDGETMODULE.createForm({
title: 'SFTP Helper Tool'
});
form.addSubmitButton({
label: 'Submit'
});
return form;
}
function getSFTPConnection(username, passwordGuid, url, hostKey, hostKeyType, port, directory, timeout) {
var preConnectionObj = {};
preConnectionObj.passwordGuid = passwordGuid;
preConnectionObj.url = url;
preConnectionObj.hostKey = hostKey;
if (username) { preConnectionObj.username = username; }
if (hostKeyType) { preConnectionObj.hostKeyType = hostKeyType; }
if (port) { preConnectionObj.port = Number(port); }
if (directory) { preConnectionObj.directory = directory; }
if (timeout) { preConnectionObj.timeout = Number(timeout); }
var connectionObj = SFTPMODULE.createConnection(preConnectionObj);
return connectionObj;
}