Jira Code : MICL-180
/**
* @NApiVersion 2.1
* @NScriptType Restlet
*/
/*************************************************************************************************************************
* CLIENTNAME: Madi International Co LLC-UAE-SCA
* MICL-192
* Restlet Script to fetch the Stock details from Item record based on Location
*********************************************************************************************************************
* Date : 14/02/2023
*
* Author: Jobin & Jismi IT Services
* Script Description :This Restlet script is used to fetch the Stock details of item from NS using GET API
* Date created : 14/02/2023
*
* REVISION HISTORY
*
* Revision 1.0 ${14/02/2023} Aranya created
* Revision 2.0 ${17/02/2023} MICL-211 Restlet Script to fetch the Stock details from Item record based on Item and Location - Update 1.0
* Revision 3.0 ${24/02/2023} MICL-243 Update the GET APIs with POST data to fetch the data from Salesforce
* Revision 4.0 ${30/03/2023} MICL-351 Update Item Stock API to accept array of item codes in request body : Update 2.0
**************************************************************************************************************************/
define(["N/https", "N/record", "N/runtime", "N/search", "N/url"],
/**
* @param{https} https
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{url} url
*/
(https,record, runtime, search, url) => {
/** dataSets from Saved Search and formating Saved Search results **/
const DATASETS = {
/**
* Function to format saved search column to key-value pair
* @param {object} savedSearchObj - Object of the Saved search
* @param {name} priorityKey - name of the priority key
* @returns {{}}
*/
fetchSavedSearchColumn: function (savedSearchObj, priorityKey) {
try {
let columns = savedSearchObj.columns;
let columnsData = {},
columnName = '';
columns.forEach(function (result, counter) {
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("Error@fetchSavedSearchColumn", e);
return {};
}
},
/**
* Function to fetch the search results and get each results value or text
* @param {object} searchResult - search result
* @param {object} columns - search column object
* @returns {{}}
*/
formatSingleSavedSearchResult: function (searchResult, columns) {
try {
let responseObj = {};
for (let column in columns) {
if (column == "Inventory Location" || column == "Type") {
responseObj[column] = searchResult.getText(columns[column]);
} else {
responseObj[column] = searchResult.getValue(columns[column]);
}
}
return responseObj;
}
catch (e) {
log.error("Error@formatSingleSavedSearchResult", e);
return {}
}
},
/**
* Function to iterate over and initiate format of each saved search result
* @param {object} searchObj - Saved search object
* @param {object} columns - Saved search column
* @param {number} PAGE_INDEX - Index of the Page
* @param {number} PAGE_SIZE - Size of the Page
* @returns {{pageInfo: {pageLength: number, pageIndex: number, isLastPage: (boolean)}, lines: *[]}|*[]|{pageInfo: {pageLength: number, pageIndex: number, isLastPage: boolean}, lines: *[]}|boolean}
*/
iterateSavedSearch: function(searchObj, columns, PAGE_INDEX, PAGE_SIZE) {
try {
if (!main.checkForParameter(searchObj)) {
return {
status : "FAILURE",
reason: "ITEMS_NOT_FOUND",
pageInfo: {
pageLength: 1,
pageIndex: 1,
isLastPage: true
},
lines: []
}
}
if (!Object.keys(columns).length) {
columns = DATASETS.fetchSavedSearchColumn(searchObj);
}
let response = [];
let searchPageRanges;
try {
searchPageRanges = searchObj.runPaged({
pageSize: Number.isInteger(Number(PAGE_SIZE)) ? parseInt(Number(PAGE_SIZE)) : 30 //Default Page Size
});
}
catch (err) {
return Number.isInteger(PAGE_INDEX) ? {
status : "FAILURE",
reason: err.message,
pageInfo: {
pageLength: 1,
pageIndex: 1,
isLastPage: true
},
lines: []
} : [];
}
if (searchPageRanges.pageRanges.length < 1) {
return Number.isInteger(PAGE_INDEX) ? {
status : "FAILURE",
reason: "ITEMS_NOT_FOUND",
pageInfo: {
pageLength: 1,
pageIndex: 1,
isLastPage: true
},
lines: []
} : [];
}
let pageRangeLength = searchPageRanges.pageRanges.length;
//To make sure the pageIndex has minimum value of one and maximum value of pageRangeLength
const PAGEINDEXRANGERECTIFIER = function (value, pageRange) {
if (!Number.isInteger(Number(value)))
return 1;
if ((Number(value) - 1) <= 0)
return 1;
if ((Number(value) - 1) >= Number(pageRange))
return Number(pageRange);
return Number(value);
};
if (Number.isInteger(PAGE_INDEX)) {
searchPageRanges.fetch({
index: PAGEINDEXRANGERECTIFIER(PAGE_INDEX, pageRangeLength) - 1
}).data.forEach(function (result) {
response.push(DATASETS.formatSingleSavedSearchResult(result, columns));
});
}
else {
for (let pageIndex = 0; pageIndex < pageRangeLength; pageIndex++) {
searchPageRanges.fetch({
index: pageIndex
}).data.forEach(function (result) {
response.push(DATASETS.formatSingleSavedSearchResult(result, columns));
});
}
}
return {
status : "SUCCESS",
reason: "",
pageInfo: {
pageLength: pageRangeLength,
pageIndex: Number(PAGEINDEXRANGERECTIFIER(PAGE_INDEX, pageRangeLength)),
isLastPage: Number(PAGEINDEXRANGERECTIFIER(PAGE_INDEX, pageRangeLength)) >= Number(pageRangeLength) ? true : false
},
lines: response
};
}
catch (e) {
log.error("Error@iterateSavedSearch", e);
return Number.isInteger(PAGE_INDEX) ? {status : "FAILURE",
reason: "ERROR",
pageInfo: {
pageLength: 1,
pageIndex: 1,
isLastPage: true
},
lines: []
} : [];
}
},
/**
* Function to check whether the passed currency is exists in NetSuite
* @param {name} location - Name of the location
* @return {boolean|{pageInfo: {pageLength: number, pageIndex: number, isLastPage: boolean}, lines: *[]}|*[]}
*/
fetchLocation: function(location) {
try
{
let locationSearchObj = search.create({
type: "location",
filters:
[
["formulatext: {namenohierarchy}","is",location],
"AND",
["isinactive","is","F"]
],
columns:
[
search.createColumn({ name: "internalid", label: "InternalID" })
]
});
let searchResultCount = locationSearchObj.runPaged().count;
if(searchResultCount > 0)
{
return DATASETS.iterateSavedSearch(locationSearchObj, DATASETS.fetchSavedSearchColumn(locationSearchObj, 'label'));
}
else
{
return false;
}
}
catch (e) {
log.error("Error@fetchLocation", e);
return false;
}
},
/**
* Function to check whether the item exists in NetSuite
* @param {array} itemNameArray - An array of item names
* @return {boolean|{pageInfo: {pageLength: number, pageIndex: number, isLastPage: boolean}, lines: *[]}|*[]}
*/
fetchItem : function(itemNameArray) {
try {
let itemName = [];
if ( typeof(itemNameArray) == "string")
{
itemName = `'${itemNameArray}'` //to put itemNameArray within single quotes
}
else if (itemNameArray.length > 0 && typeof(itemNameArray) == "object") {
for (let i = 0; i < itemNameArray.length; i++) {
if (i == 0) {
if (itemNameArray.length == 1) {
itemName += "'"
itemName += itemNameArray[i].trim();
itemName += "'"
}
else {
itemName += "'"
itemName += itemNameArray[i].trim();
itemName += "',"
}
}
else if (i == (itemNameArray.length - 1)) {
if (itemNameArray.length !== 1) {
itemName += "'"
itemName += itemNameArray[i].trim();
itemName += "'"
}
}
else {
itemName += "'"
itemName += itemNameArray[i].trim();
itemName += "',"
}
}
}
// Creating Item Search with multiple item name with a limit of 1000 item names can be passed to the search
let itemSearchObj = search.create({
type: "item",
filters:
[
["isinactive", "is", "F"],
"AND",
["formulanumeric: CASE WHEN {itemid} IN (" + itemName + ") THEN 1 ELSE 0 END", "equalto", "1"]
],
columns:
[
search.createColumn({ name: "internalid", label: "InternalID" }),
search.createColumn({ name: "itemid", sort: search.Sort.ASC, label: "Name" })
]
});
let searchResultCount = itemSearchObj.runPaged().count;
if (searchResultCount > 0) {
return DATASETS.iterateSavedSearch(itemSearchObj, DATASETS.fetchSavedSearchColumn(itemSearchObj, 'label'));
}
else {
return false;
}
}
catch (e) {
log.error("Error@fetchItem", e);
return false;
}
},
/**
* Function to fetch the stock details of item as a JSON
* @param {array} itemInternalIDArray - An array of item Internal Ids
* @param {number} locationID - location internal id
* @param {number} pageIndex - Index of the page
* @return {string|{pageInfo: {pageLength: number, pageIndex: number, isLastPage: boolean}, lines: *[]}|*[]|boolean|{reason, pageInfo: {pageLength: number, pageIndex: number, isLastPage: boolean}, lines: *[], status: string}}
*/
getItemRecords: function ( itemInternalIDArray, locationID , pageIndex ) {
try {
let searchFilters = [
["isinactive", "is", "F"],
"AND",
["inventorylocation.isinactive", "is", "F"],
];
if (main.checkForParameter(itemInternalIDArray))
searchFilters.push("AND", ["internalid", "anyof", itemInternalIDArray]);
if (main.checkForParameter(locationID))
searchFilters.push("AND", ["inventorylocation", "anyof", locationID]);
let itemSearchObj = search.create({
type: "item",
filters: searchFilters,
columns: [
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "itemid",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({
name: "type",
summary: "GROUP",
label: "Type"
}),
search.createColumn({
name: "inventorylocation",
summary: "GROUP",
label: "Inventory Location"
}),
search.createColumn({
name: "formulanumeric",
summary: "MAX",
formula: "NVL({locationquantityonhand},0)",
label: "Stock OnHand"
}),
search.createColumn({
name: "formulanumeric",
summary: "MAX",
formula: "NVL({locationquantitycommitted},0)",
label: "Stock Committed"
}),
search.createColumn({
name: "formulanumeric",
summary: "MAX",
formula: "NVL({locationquantityonhand},0)- NVL({locationquantitycommitted},0)",
label: "Stock Available"
})
],
});
let searchResultCount = itemSearchObj.runPaged().count;
if (searchResultCount > 0) {
return DATASETS.iterateSavedSearch(itemSearchObj, DATASETS.fetchSavedSearchColumn(itemSearchObj, 'label'), pageIndex, 30);
}
else {
return JSON.stringify({
summary: {
status: "FAILURE",
reason: "ITEM_NOT_FOUND"
},
result: {
pageInfo: {
pageLength: 0,
pageIndex: 0,
isLastPage: true
},
lines: []
}
});
}
}
catch (e) {
log.error("Error@getItemRecords", e.message)
return {
status : "FAILURE",
reason: e.message,
pageInfo: {
pageLength: 1,
pageIndex: 1,
isLastPage: true
},
lines: []
}
}
},
}
let main = {
/**
* Function to check whether a value exists in parameter
* @param parameter -passing parameter
* @param parameterName - passing parameter name
* @returns{Boolean}
*/
checkForParameter: function (parameter, parameterName) {
if (
parameter != "" &&
parameter != null &&
parameter != undefined &&
parameter != "null" &&
parameter != "undefined" &&
parameter != " " &&
parameter != false
)
{
return true;
}
else {
if (parameterName)
log.error("Empty Value found", "Empty Value for parameter " + parameterName );
return false;
}
},
/**
* Function to define the JSON response
* @param {string} jsonStatus - staus of the JSON response
* @param {string} jsonReason - Reason of the JSON response
* @returns {object} - Returns the JSON object
*/
jsonResponse: function (jsonStatus, jsonReason){
try{
return JSON.stringify({
summary: {
status: jsonStatus,
reason: jsonReason
},
result: {
pageInfo: {
pageLength: 0,
pageIndex: 0,
isLastPage: true
},
lines: []
}
});
}
catch(e){
log.error("Error@jsonResponse", e);
return false;
}
},
/**
* Function to enforce the item stock details to Salesforce from NetSuite
* @param {object} requestBody - request object to fetch the details
* @returns {string}
*/
post: function (requestBody) {
try {
// ========================= NO PARAMETERS (ITEM , LOCATION & PAGEINDEX ============================
if (main.checkForParameter(requestBody?.pageIndex) == false && main.checkForParameter(requestBody?.location) == false && main.checkForParameter(requestBody?.item) == false) {
try {
return main.jsonResponse("FAILURE","PARAMETER_IS_INVALID");
}catch(e) {
log.error("Error @ checking All parameter", e);
return main.jsonResponse("FAILURE","PARAMETER_IS_INVALID");
}
}
// ============================= Item & Location Validation Provided ==============================
let itemArray = requestBody.item;
let location = requestBody.location;
let locationID, itemInternalID, itemInternalIDArray = [], itemNameArray = [], itemsNotInNetSuite = [];
if (main.checkForParameter(itemArray) || main.checkForParameter(location)) {
if (main.checkForParameter(itemArray)) {
try {
// if the item is not exists in the NetSuite
itemInternalID = DATASETS.fetchItem(itemArray);
if (itemInternalID == false) {
return main.jsonResponse("FAILURE","ITEM_NOT_FOUND");
}
}
catch (e) {
log.error("Error@ item Format", e);
return main.jsonResponse("FAILURE","ITEM_NOT_FOUND");
}
}
if (main.checkForParameter(location)) {
try {
location = location.toString();
// if the currency is not exists in the NetSuite
locationID = DATASETS.fetchLocation(location);
if (locationID == false) {
return main.jsonResponse("FAILURE","LOCATION_NOT_FOUND");
}
}
catch (e) {
log.error("Error@ location Format", e);
return main.jsonResponse("FAILURE","LOCATION_NOT_FOUND");
}
}
}
// ========================= Item & Location Validation (EMPTY) ===================================
if (main.checkForParameter(itemArray) == false && main.checkForParameter(location) == false) {
try {
return main.jsonResponse("FAILURE","ITEM_AND_LOCATION_INVALID");
}
catch(e){
return main.jsonResponse("FAILURE","ITEM_AND_LOCATION_INVALID");
}
}
// ================================= pageIndex validation ==========================================
if ((main.checkForParameter(requestBody?.pageIndex) == false) || isNaN(Number(requestBody?.pageIndex))) {
return main.jsonResponse("FAILURE","PAGENUMBER_FORMAT_IS_INVALID");
}
requestBody.pageIndex = Number(requestBody.pageIndex) < 1 ? 1 : Number(requestBody.pageIndex);
if (main.checkForParameter(locationID))
{
locationID = locationID.lines[0].InternalID;
}
if (main.checkForParameter(itemInternalID))
{
for (let i = 0; i < itemInternalID.lines.length; i++){
itemInternalIDArray.push(itemInternalID.lines[i].InternalID);
itemNameArray.push(itemInternalID.lines[i].Name);
}
}
// When any of the item in itemArray is not in NetSuite then, display it in the Execution log with timestamp
for ( let i =0; i < itemArray.length; i++){
if (!itemNameArray.includes(itemArray[i])){
itemsNotInNetSuite.push(itemArray[i]);
}
}
if (itemsNotInNetSuite.length > 0 ){
log.error("These Items not found in NetSuite - "+ new Date().getTime(), itemsNotInNetSuite);
}
let itemRecords = DATASETS.getItemRecords(itemInternalIDArray, locationID, requestBody?.pageIndex);
if (itemRecords.status === "SUCCESS" ) {
return JSON.stringify({
summary: {
status: "SUCCESS",
reason: "ITEM_FOUND"
},
result: {
pageInfo: itemRecords.pageInfo,
lines: itemRecords.lines
}
});
}
else {
return main.jsonResponse("FAILURE","ITEM_DETAILS_NOT_FOUND");
}
}
catch (e) {
log.error("Error@post", e);
return main.jsonResponse("FAILURE","ERROR");
}
}
}
return main;
});