define(['N/ui/serverWidget', 'N/search'], function (serverWidget, search) {
const getVendorList = () => {
try {
const VENDORLIST = [];
let vendorId, vendorName
var itemSearchObj = search.create({
type: "vendor",
filters:
[
["isinactive", "is", "F"]
],
columns:
[
search.createColumn({ name: "entityid", sort: search.Sort.ASC, label: "Name" }),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var resultCount = itemSearchObj.runPaged().count;
log.debug("result count", resultCount);
itemSearchObj.run().each(function (result) {
vendorName = result.getValue({ name: "entityid", sort: search.Sort.ASC, label: "Name" });
vendorId = result.getValue({ name: "internalid", label: "Internal ID" });
let vendorInfo = {
vendorName: vendorName,
vendorId: vendorId
}
VENDORLIST.push(vendorInfo);
return true;
});
return VENDORLIST;
} catch (ex) {
log.error({
title: 'ERROR: getVendorList',
details: ex
})
}
}
const getSearchData = (vendorId) => {
try {
let itemId, itemName, displayCode, yearlyQtySold, monthlyAverage, qtyAvailable, qtyOnorder,qtyBackOrdered,safetyStockDays,preferredDays,purchaseLeadTime,preferredStockLevel;
const RESULTSET = [];
var itemSearchObj = search.create({
type: "item",
filters:
[
["isinactive", "is", "F"],
"AND",
["vendor", "anyof", vendorId],
"AND",
["transaction.type", "anyof", "CustInvc", "CashSale", "CustCred"],
"AND",
["transaction.trandate", "within", "lastrollingyear"],
"AND",
["isdropshipitem","is","F"],
"AND",
["isspecialorderitem","is","F"]
],
columns:
[
search.createColumn({ name: "internalid", summary: "GROUP", label: "Internal ID" }),
search.createColumn({ name: "itemid", summary: "GROUP", sort: search.Sort.ASC, label: "By Vendor" }),
search.createColumn({ name: "displayname", summary: "GROUP", label: "Display Name" }),
search.createColumn({ name: "quantity", join: "transaction", summary: "SUM", label: "Quantity" }),
search.createColumn({ name: "quantityavailable", summary: "AVG", label: "Current Quantity Available" }),
search.createColumn({ name: "quantityonorder", summary: "AVG", label: "Current Quantity On Order" }),
search.createColumn({ name: "quantitybackordered",summary: "AVG",label: "Quantity Back Ordered" }),
search.createColumn({ name: "safetystockleveldays",summary: "AVG",label: "Safety stock level Days"}),
search.createColumn({ name: "formulanumeric",summary: "AVG", formula: "{preferredstockleveldays}",
label: "Preferred stock level Days"}),
search.createColumn({ name: "leadtime", summary: "AVG", label: "Purchase Lead Time"}),
search.createColumn({ name: "preferredstocklevel", summary: "AVG", label: "Preferred Stock Level"})
]
});
var resultCount = itemSearchObj.runPaged().count;
log.debug("result count", resultCount);
itemSearchObj.run().each(function (result) {
itemId = result.getValue({ name: "internalid", summary: "GROUP", label: "Internal ID" });
itemName = result.getValue({ name: "itemid", summary: "GROUP", sort: search.Sort.ASC, label: "By Vendor" });
itemName = (itemName.indexOf(":") > -1 ? itemName.split(":")[1] : itemName);
displayCode = result.getValue({ name: "displayname", summary: "GROUP", label: "Display Name" });
yearlyQtySold = result.getValue({ name: "quantity", join: "transaction", summary: "SUM", label: "Quantity" });
log.debug("yearlyQtySold",yearlyQtySold);
monthlyAverage = (yearlyQtySold / 12);
qtyAvailable = result.getValue({ name: "quantityavailable", summary: "AVG", label: "Current Quantity Available" }) || 0;
qtyOnorder = result.getValue({ name: "quantityonorder", summary: "AVG", label: "Current Quantity On Order" }) || 0;
qtyBackOrdered = result.getValue({ name: "quantitybackordered",summary: "AVG",label: "Quantity Back Ordered" }) || 0;
safetyStockDays = result.getValue({ name: "safetystockleveldays",summary: "AVG",label: "Safety stock level Days" }) || 0;
preferredDays = result.getValue({ name: "formulanumeric",summary: "AVG", formula: "{preferredstockleveldays}",
label: "Preferred stock level Days"}) || 0;
purchaseLeadTime = result.getValue({ name: "leadtime", summary: "AVG", label: "Purchase Lead Time" }) || 0;
preferredStockLevel = result.getValue({ name: "preferredstocklevel", summary: "AVG", label: "Preferred Stock Level"}) || 0;
let items = {
itemId: itemId,
itemName: itemName,
displayCode: displayCode,
yearlyQtySold: yearlyQtySold,
monthlyAverage: monthlyAverage,
qtyAvailable: qtyAvailable,
qtyOnorder: qtyOnorder,
qtyBackOrdered:qtyBackOrdered,
safetyStockDays:safetyStockDays,
preferredDays:preferredDays,
purchaseLeadTime:purchaseLeadTime,
preferredStockLevel:preferredStockLevel
}
RESULTSET.push(items);
return true;
});
// Here we do some calculation
for (var i = 0; i < RESULTSET.length; i++) {
let dispalyname = RESULTSET[i].displayCode;
let monthlyAverage = RESULTSET[i].monthlyAverage;
let monthStock = (RESULTSET[i].qtyAvailable / monthlyAverage);
let monthOfStock=monthStock.toFixed(2);
let monthOfStockAvailable = Math.round(RESULTSET[i].qtyAvailable / monthlyAverage);
let monthsstock= (RESULTSET[i].qtyOnorder / monthlyAverage);
let monthofStock = monthsstock.toFixed(2);
let monthOfStockOnOrder = Math.round(RESULTSET[i].qtyOnorder / monthlyAverage);
monthlyAverage = Math.round(monthlyAverage);
let stockAvailablePlusStockOnOrder = (monthOfStockAvailable + monthOfStockOnOrder);
let tenMonthProductionTime = (stockAvailablePlusStockOnOrder - 10);
let monthToOrder = (6 - tenMonthProductionTime);
let bagsToOrder = (monthlyAverage * monthToOrder)
RESULTSET[i].dispalyname = dispalyname;
RESULTSET[i].monthOfStock = monthOfStock;
RESULTSET[i].monthofStock = monthofStock;
RESULTSET[i].stockAvailablePlusStockOnOrder = stockAvailablePlusStockOnOrder;
RESULTSET[i].tenMonthProductionTime = tenMonthProductionTime;
RESULTSET[i].monthToOrder = monthToOrder;
RESULTSET[i].bagsToOrder = bagsToOrder;
RESULTSET[i].monthlyAverage = monthlyAverage;
}
return RESULTSET;
} catch (ex) {
log.error({
title: 'ERROR :getSearchData',
details: ex
});
}
}
function onRequest(context) {
if (context.request.method === 'GET') {
try {
let vendorList = getVendorList();
log.debug("vendorList", vendorList);
var form = serverWidget.createForm({
title: 'Generate Analytic Report'
});
let vendors = form.addField({
id: 'custpage_vendorid',
type: serverWidget.FieldType.SELECT,
label: 'Select Vendor'
});
for (var i = 0; i < vendorList.length; i++) {
vendors.addSelectOption({
value: vendorList[i].vendorId,
text: vendorList[i].vendorName
});
}
vendors.isMandatory = true;
form.addSubmitButton({
label: 'Generate Report'
})
context.response.writePage(form);
} catch (ex) {
log.error("ERROR: GET", ex);
}
} else {
if (context.request.method === 'POST') {
try {
let vendorId = context.request.parameters.custpage_vendorid;
log.debug("vendorId", vendorId);
let EXPECTED_DATA = getSearchData(vendorId);
log.debug({
title: 'EXPECTED_DATA List',
details: EXPECTED_DATA
});
// create NS Page
var form = serverWidget.createForm({
title: "Analytic Report"
});
var sublist = form.addSublist({
id: 'analytic_report_results',
type: serverWidget.SublistType.LIST,
label: 'Analytics Report: Results'
});
sublist.addField({ id: 'sublist_itemname', type: serverWidget.FieldType.TEXT, label: 'By Vendor' });
sublist.addField({ id: 'sublist_displaycode', type: serverWidget.FieldType.TEXT, label: 'Display Name' });
sublist.addField({ id: 'sublist_yearqtysold', type: serverWidget.FieldType.FLOAT, label: 'QTY SOLD Rolling Year' });
sublist.addField({ id: 'sublist_monthlyaverage', type: serverWidget.FieldType.FLOAT, label: 'Average Monthly Sales' });
sublist.addField({ id: 'sublist_qtyavailable', type: serverWidget.FieldType.FLOAT, label: 'Current Quantity Available' });
sublist.addField({ id: 'sublist_qtyonorder', type: serverWidget.FieldType.FLOAT, label: 'Current Quantity On Order' });
sublist.addField({ id: 'sublist_monthofstock', type: serverWidget.FieldType.FLOAT, label: '# of Months of Stock' });
sublist.addField({ id: 'sublist_stockonorder', type: serverWidget.FieldType.FLOAT, label: '# of Months Stock On Order' });
sublist.addField({ id: 'sublist_bagtoorder', type: serverWidget.FieldType.FLOAT, label: 'Items To Order' });
sublist.addField({ id: 'sublist_backordered', type: serverWidget.FieldType.FLOAT, label: 'Quantity Back Ordered' });
sublist.addField({ id: 'sublist_safetystocklevel', type: serverWidget.FieldType.FLOAT, label: 'Safety Stock Level Days' });
sublist.addField({ id: 'sublist_preferedstocklevel', type: serverWidget.FieldType.FLOAT, label: 'Preferred Stock Level Days' });
sublist.addField({ id: 'sublist_purchaseleadtime', type: serverWidget.FieldType.FLOAT, label: 'Purchase Lead Time' });
sublist.addField({ id: 'sublist_preferredstocklevel', type: serverWidget.FieldType.FLOAT, label: 'Preferred Stock Level' });
for (var i = 0; i < EXPECTED_DATA.length; i++) {
let itemValue = '<a style="color:blue;" target="_blank" href="https://3630426.app.netsuite.com/app/common/item/item.nl?id=' + EXPECTED_DATA[i].itemId + '">' + EXPECTED_DATA[i].itemName + '</a>';
sublist.setSublistValue({ id: 'sublist_itemname', line: i, value: itemValue });
sublist.setSublistValue({ id: 'sublist_displaycode', line: i, value: EXPECTED_DATA[i].displayCode });
sublist.setSublistValue({ id: 'sublist_yearqtysold', line: i, value: EXPECTED_DATA[i].yearlyQtySold });
sublist.setSublistValue({ id: 'sublist_monthlyaverage', line: i, value: EXPECTED_DATA[i].monthlyAverage });
sublist.setSublistValue({ id: 'sublist_qtyavailable', line: i, value: EXPECTED_DATA[i].qtyAvailable });
sublist.setSublistValue({ id: 'sublist_qtyonorder', line: i, value: EXPECTED_DATA[i].qtyOnorder });
sublist.setSublistValue({ id: 'sublist_monthofstock', line: i, value: EXPECTED_DATA[i].monthOfStock });
sublist.setSublistValue({ id: 'sublist_stockonorder', line: i, value: EXPECTED_DATA[i].monthofStock });
sublist.setSublistValue({ id: 'sublist_bagtoorder', line: i, value: EXPECTED_DATA[i].bagsToOrder });
sublist.setSublistValue({ id: 'sublist_backordered', line: i, value: EXPECTED_DATA[i].qtyBackOrdered });
sublist.setSublistValue({ id: 'sublist_safetystocklevel', line: i, value: EXPECTED_DATA[i].safetyStockDays });
sublist.setSublistValue({ id: 'sublist_preferedstocklevel', line: i, value: EXPECTED_DATA[i].preferredDays });
sublist.setSublistValue({ id: 'sublist_purchaseleadtime', line: i, value: EXPECTED_DATA[i].purchaseLeadTime });
sublist.setSublistValue({ id: 'sublist_preferredstocklevel', line: i, value: EXPECTED_DATA[i].preferredStockLevel });
}
let countField = form.addField({
id: "result_count",
label: "Total Result Count",
type: serverWidget.FieldType.FLOAT
}).updateDisplayType({
displayType: serverWidget.FieldDisplayType.INLINE
});
form.addPageLink({
title: 'View Vendor',
type: serverWidget.FormPageLinkType.CROSSLINK,
url: 'https://3630426.app.netsuite.com/app/common/entity/vendor.nl?id=' + vendorId
});
countField.defaultValue = EXPECTED_DATA.length;
context.response.writePage(form);
} catch (ex) {
log.error("ERROR: GET", ex);
}
}
}
}
return {
onRequest: onRequest
}
});