Jira Code: PROT -110
Creating a report for unfulfilled sales order and when a user clicks the export button, CSV file will be generated. Suitelet is used to create the user interface. When the user clicks the export button a CSV file is generated from the saved search and get downloaded.
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
**/
/**
* Script Description
* This script will create a form for displaying the back ordered sales orders of the customer 10MAZ001.
* Button to change item status from quantity available to do not commit. print sales orders.
**/
/*******************************************************************************
* ProTec
* **************************************************************************
*
* Date: 26-05-2018
*
* Author: Jobin & Jismi IT Services LLP
*
*
* REVISION HISTORY
*
* Revision 1 $ 26-05-2018 rosemol : Created
*
*****************************************************************************
**/
define(['N/ui/serverWidget', 'N/record', 'N/file', 'N/encode', 'N/xml',
'N/render', 'N/search', 'N/redirect'
],
function(serverWidget, record, file, encode, xml, render, search, redirect) {
function onRequest(context) {
try {
var typefilter = context.request.parameters.datefiltertype;
var tofilter = context.request.parameters.todate;
var fromdate = context.request.parameters.fromdate;
log.debug("fromdate", fromdate);
log.debug("tofilter", tofilter);
log.debug("typefilter", typefilter);
var datefilter = createdatefilter(typefilter, tofilter, fromdate);
log.debug("datefilterout", datefilter);
var request = context.request;
var response = context.response;
var method = request.method;
if (method == 'GET') {
try {
var form;
form = serverWidget.createForm({
title: '10AMAZO01 : Backordered Items'
});
form.addSubmitButton({
label: 'Do Not Commit & Print'
});
form.addButton({
id: 'custpage_selectbutton',
label: 'Select All',
functionName: 'selectallitems'
});
var fieldgroupdatefilter = form.addFieldGroup({
id: 'fieldgroupdatefilter',
label: 'Filters'
});
var datefiltertype = form.addField({
id: 'datefiltertype',
type: serverWidget.FieldType.SELECT,
label: 'Date',
container: 'fieldgroupdatefilter'
});
addtype(datefiltertype);
datefiltertype.defaultValue = typefilter;
datefiltertype.updateDisplaySize({
height: 60,
width: 64.8
});
var todate = form.addField({
id: 'todate',
type: serverWidget.FieldType.DATE,
label: 'ON',
container: 'fieldgroupdatefilter'
});
// todate.defaultValue = tofilter;
todate.updateDisplaySize({
height: 60,
width: 64.8
});
var fromdate = form.addField({
id: 'fromdate',
type: serverWidget.FieldType.DATE,
label: 'From '
});
// fromdate.defaultValue = fromdate;
fromdate.updateBreakType({
breakType: serverWidget.FieldBreakType.STARTCOL
});
/* fromdate.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});*/
fromdate.updateDisplaySize({
height: 60,
width: 64.8
});
form.addButton({
id: 'datefilterout',
label: 'Aplly Filters',
functionName: 'applyfilters'
});
/*form.addButton({
id : 'custpage_commitbutton',
label : 'Do Not Commit',
functionName : 'changeCommit'
});*/
// to create the Sublist
var searchSublist = form.addSublist({
id: 'custpage_sublist',
type: serverWidget.SublistType.INLINEEDITOR,
label: 'Sales Order Backordered Items'
});
// to add fields for the sublist
var checkbox = searchSublist.addField({
id: 'custpage_checkbox',
label: 'SELECT',
type: serverWidget.FieldType.CHECKBOX
});
var orderdate = searchSublist.addField({
id: 'custpage_orderdate',
label: 'ORDER DATE',
type: serverWidget.FieldType.DATE
});
var orderNo = searchSublist.addField({
id: 'custpage_orderno',
label: 'ORDER NO.',
type: serverWidget.FieldType.TEXT
});
var customerpo = searchSublist.addField({
id: 'custpage_customerpo',
label: 'CUSTOMER PO#',
type: serverWidget.FieldType.TEXT
});
var customername = searchSublist.addField({
id: 'custpage_customername',
label: 'CUSTOMER',
type: serverWidget.FieldType.TEXT
});
var itemname = searchSublist.addField({
id: 'custpage_itemname',
label: 'ITEM',
type: serverWidget.FieldType.TEXT
});
var quantityordered = searchSublist.addField({
id: 'custpage_quantityordered',
label: 'QUANTITY ORDERED',
type: serverWidget.FieldType.TEXT
});
var quantityremaining = searchSublist.addField({
id: 'custpage_quantityremaining',
label: 'QUANTITY REMAINING',
type: serverWidget.FieldType.TEXT
});
var commit = searchSublist.addField({
id: 'custpage_commit',
label: 'COMMIT',
type: serverWidget.FieldType.TEXT
});
var result = runSearch(search, datefilter);
// to set to sublist
searchSublist = setValuesToSublist(search, searchSublist, result);
context.response.writePage(form);
form.clientScriptFileId = '487041';
} catch (e) {
logme('err@GET', getError(e));
}
} else {
var resultToExcel = runSearch(search, datefilter);
var XML_TO_PRINT = getXMLDataExcel(resultToExcel, file);
var strXmlEncoded = encode.convert({
string: XML_TO_PRINT,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
var pdfFile = file.create({
name: 'Report.xls',
fileType: file.Type.EXCEL,
contents: strXmlEncoded
});
response.writeFile(pdfFile, true);
var custpage_sublist = context.request.parameters.custpage_sublist;
var lines = context.request.getLineCount({ group: "custpage_sublist" });
for (var i = 0; i < lines; i++) {
try {
var chkboxvalue = context.request.getSublistValue({
group: 'custpage_sublist',
name: 'custpage_checkbox',
line: i
});
if (chkboxvalue == 'T') {
var sonumber = context.request.getSublistValue({
group: 'custpage_sublist',
name: 'custpage_orderno',
line: i
});
var item = context.request.getSublistValue({
group: 'custpage_sublist',
name: 'custpage_itemname',
line: i
});
var searchResult = search.create({
type: 'salesorder',
filters: [
['tranid', 'is', sonumber]
]
}).run().getRange({
start: 0,
end: 1
});
var sointernalid = searchResult[0].id;
var salesrecord = record.load({
type: 'salesorder',
id: sointernalid
});
var sonumLines = salesrecord.getLineCount({
sublistId: 'item'
});
for (var j = 0; j < sonumLines; j++) {
var itemname = salesrecord.getSublistText({
sublistId: 'item',
fieldId: 'item',
line: j
});
if (itemname == item) {
var commitinventory = salesrecord.getSublistValue({
sublistId: 'item',
fieldId: 'commitinventory',
line: j
});
salesrecord.setSublistValue({
sublistId: 'item',
fieldId: 'commitinventory',
line: j,
value: 3
});
var soRecordId = salesrecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
}
}
} catch (e) {
logme('err@gettingcheckbox', getError(e));
}
}
/*redirect.toSuitelet({
scriptId: 'customscript_prot108_jj_sl_backorderedso',
deploymentId: 'customdeploy_prot108_jj_sl_backorderedso'
});*/
}
} catch (e) {
logme('err@onRequest', getError(e));
}
}
/**
* Function to find the back ordered items from the saved search
**/
function runSearch(search, date) {
try {
resultArray = [];
var filterarray = [
["type", "anyof", "SalesOrd"],
"AND", ["name", "anyof", "46893"],
"AND", ["status", "anyof", "SalesOrd:D", "SalesOrd:E", "SalesOrd:B"],
"AND", ["mainline", "is", "F"],
"AND", ["commit", "anyof", "3"],
"AND", ["formulanumeric: CASE WHEN {quantity}-{quantitypicked}!=0 THEN 1 ELSE 0 END", "equalto", "1"]
]
if (date != false) {
filterarray.push("AND", date);
}
var salesorderSearchObj = search.create({
type: "salesorder",
filters: filterarray,
columns: [
search.createColumn({ name: "mainline", label: "*" }),
search.createColumn({
name: "trandate",
sort: search.Sort.DESC,
label: "Date"
}),
search.createColumn({ name: "tranid", label: "Document Number" }),
search.createColumn({ name: "custbody_cust_po_number", label: "PO Number NEW" }),
search.createColumn({ name: "entity", label: "Name" }),
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({ name: "quantity", label: "Quantity" }),
search.createColumn({
name: "formulanumeric",
formula: "{quantity}-NVL({quantitycommitted},0)",
label: "Quantity Remaining"
}),
search.createColumn({ name: "commit", label: "Commit" })
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
salesorderSearchObj.run().each(function(result) {
resultArray.push(result);
// .run().each has a limit of 4,000 results
return true;
});
if (resultArray.length > 0) {
//logme('resultArray',resultArray);
return resultArray;
} else {
alert("No results found");
}
} catch (e) {
logme("ERR in search", getError(e));
}
}
function setValuesToSublist(search, searchSublist, result) {
// to set the values to sublist
try {
for (var i = 0; i < result.length; i++) {
var singleResult = result[i];
var orderdate = singleResult.getValue({
name: "trandate",
sort: search.Sort.ASC
});
var orderno = singleResult.getValue({
name: "tranid"
});
var customerpo = singleResult.getValue({
name: "custbody_cust_po_number"
});
var customername = singleResult.getText({
name: "entity"
});
var itemname = singleResult.getText({
name: "item",
});
var quantityordered = singleResult.getValue({
name: "quantity"
});
var quantityremaining = singleResult.getValue({
name: "formulanumeric",
formula: "{quantity}-NVL({quantitycommitted},0)"
});
var commit = singleResult.getText({
name: "commit"
});
searchSublist.setSublistValue({
id: "custpage_orderdate",
line: i,
value: checkif(orderdate)
});
searchSublist.setSublistValue({
id: "custpage_orderno",
line: i,
value: checkif(orderno)
});
searchSublist.setSublistValue({
id: "custpage_customerpo",
line: i,
value: checkif(customerpo)
});
searchSublist.setSublistValue({
id: "custpage_customername",
line: i,
value: checkif(customername)
});
searchSublist.setSublistValue({
id: "custpage_itemname",
line: i,
value: checkif(itemname)
});
searchSublist.setSublistValue({
id: "custpage_quantityordered",
line: i,
value: checkif(quantityordered)
});
searchSublist.setSublistValue({
id: "custpage_quantityremaining",
line: i,
value: checkif(quantityremaining)
});
searchSublist.setSublistValue({
id: "custpage_commit",
line: i,
value: checkif(commit)
});
}
return searchSublist;
} catch (e) {
logme('E@SetSublistValuestoForm', getError(e));
}
}
/*******************************************************************************
* To download XML
*/
function getXMLDataExcel(searchResult, file) {
try {
var myXMLFile = file.load({
id: '487043'
});
var myXMLFile_value = myXMLFile.getContents();
if (searchResult.length > 0) {
var TABLE = "";
logme("searchResult.length", searchResult.length);
for (var i = 0; i < searchResult.length; i++) {
var single_result = searchResult[i];
// get values
var orderdate = single_result.getValue({
name: "trandate",
sort: search.Sort.ASC
});
var orderno = single_result.getValue({
name: "tranid"
});
var customerpo = single_result.getValue({
name: "custbody_cust_po_number"
});
var customername = single_result.getText({
name: "entity"
});
var itemname = single_result.getText({
name: "item",
});
var quantityordered = single_result.getValue({
name: "quantity"
});
var quantityremaining = single_result.getValue({
name: "formulanumeric",
formula: "{quantity}-NVL({quantitycommitted},0)"
});
orderdate = checkif(orderdate);
orderno = checkif(orderno);
customerpo = checkif(customerpo);
customername = checkif(customername);
itemname = checkif(itemname);
quantityordered = checkif(quantityordered);
quantityremaining = checkif(quantityremaining);
var strVar = "";
strVar += " <Row ss:AutoFitHeight=\"0\">";
strVar += " <Cell><Data ss:Type=\"String\">" + orderdate +
"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">" + orderno +
"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">" + customerpo +
"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">" +
customername + "<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">" + itemname +
"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"Number\">" + quantityordered +
"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"Number\">" + quantityremaining +
"<\/Data><\/Cell>";
strVar += " <\/Row>";
if (i < (searchResult.length - 1)) {
strVar = strVar + '\n';
}
TABLE = TABLE + strVar;
}
XML = myXMLFile_value.replace('<!-- REPLACEWITHTABLEBODY -->', TABLE);
}
return XML;
} catch (e) {
logme('E@getXMLDataExcel', getError(e));
}
}
return {
onRequest: onRequest,
runSearch: runSearch,
setValuesToSublist: setValuesToSublist,
checkif: checkif,
getXMLDataExcel: getXMLDataExcel
};
});
function createdatefilter(typefilter, tofilter, fromdate) {
try {
if (typefilter != null || typefilter != undefined) {
if (typefilter == "NOTWITHIN" || typefilter == "WITHIN") {
var filterarray = ["trandate", typefilter, tofilter, fromdate]
} else {
var filterarray = ["trandate", typefilter, tofilter]
}
return filterarray
}
return false
} catch (e) {
log.debug("e in date filter", e)
}
}
function addtype(field) {
var dateobj = [{ "value": "ON", "text": "on" }, { "value": "BEFORE", "text": "before" }, { "value": "AFTER", "text": "after" }, { "value": "ONORBEFORE", "text": "on or before" }, { "value": "ONORAFTER", "text": "on or after" }, { "value": "WITHIN", "text": "within" }, { "value": "NOTON", "text": "not on" }, { "value": "NOTBEFORE", "text": "not before" }, { "value": "NOTAFTER", "text": "not after" }, { "value": "NOTONORBEFORE", "text": "not on or before" }, { "value": "NOTONORAFTER", "text": "not on or after" }, { "value": "NOTWITHIN", "text": "not within" }];
for (var key in dateobj) {
field.addSelectOption({
value: dateobj[key]["value"],
text: dateobj[key]["text"]
});
}
return field;
}
function checkif(singleitem) {
if (singleitem == "" || singleitem == null || singleitem == undefined) {
return "-";
} else {
return singleitem;
}
}
/*******************************************************************************
* return error
*
* @param e
* @returns {String}
*/
function getError(e) {
var stErrMsg = '';
if (e.getDetails != undefined) {
stErrMsg = '_' + e.getCode() + '<br>' + e.getDetails() + '<br>' +
e.getStackTrace();
} else {
stErrMsg = '_' + e.toString();
}
return stErrMsg;
}
/*******************************************************************************
* Log these data
*/
function logme(title, details) {
log.debug({
title: title,
details: details
});
}/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/**
* Script Description
* This script select all the items in suitelet form.Change item status to Do not commit.
**/
/*******************************************************************************
* ProTec
* **************************************************************************
*
* Date: 26-05-2018
*
* Author: Jobin & Jismi IT Services LLP
*
*
* REVISION HISTORY
*
* Revision 1 $ 26-05-2018 rosemol : Created
*
*****************************************************************************
**/
define(['N/record', 'N/search', 'N/currentRecord', 'N/url'],
/**
* @param {record} record
* @param {search} search
*/
function(record, search, currentRecord, url) {
function pageInit(scriptContext) {
var filtertype = getParameterByName('filter');
var todate = getParameterByName('todate');
var fromdate = getParameterByName('fromdate');
jQuery("#todate").val(todate);
jQuery("#fromdate").val(fromdate);
if (fromdate == " ") {
jQuery("#fromdate_fs_lbl_uir_label").hide();
jQuery("#fromdate").hide()
} else {
jQuery("#fromdate_fs_lbl_uir_label").html("To");
jQuery("#todate_fs_lbl_uir_label").html("From");
jQuery("#fromdate_fs_lbl_uir_label").show();
jQuery("#fromdate").show()
}
}
// for getting parameter by name .................
function getParameterByName(name, url) {
if (!url)
url = window.location.href;
name = name.replace(/[\[\]]/g, "\\$&");
var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
results = regex
.exec(url);
if (!results)
return null;
if (!results[2])
return ' ';
return decodeURIComponent(results[2].replace(/\+/g, " "));
}
function fieldChanged(scriptContext) {
var records = scriptContext.currentRecord;
if (scriptContext.fieldId == "datefiltertype") {
var type = records.getValue({
fieldId: 'datefiltertype'
});
var aad = records.getValue({
fieldId: 'fromdate'
});
console.log(aad);
if (type == "NOTWITHIN" || type == "WITHIN") {
jQuery("#fromdate_fs_lbl_uir_label").html("To");
jQuery("#todate_fs_lbl_uir_label").html("From");
jQuery("#fromdate_fs_lbl_uir_label").show();
jQuery("#fromdate").show()
} else {
jQuery("#fromdate").val("");
jQuery("#fromdate_fs_lbl_uir_label").hide();
jQuery("#fromdate").hide();
}
}
}
function applyfilters() {
var records = currentRecord.get();
var type = records.getValue({
fieldId: 'datefiltertype'
});
var todate = records.getText({
fieldId: 'todate'
});
var fromdate = records.getText({
fieldId: 'fromdate'
});
var dashboard_url = url.resolveScript({
scriptId: "customscript_prot108_jj_sl_backorderedso",
deploymentId: "customdeploy_prot108_jj_sl_backorderedso",
returnExternalUrl: false
});
window.location = dashboard_url + "&todate=" + todate + "&fromdate=" + fromdate + "&datefiltertype=" + type;
}
/**
* Selects all line items in sublist
*/
function selectallitems() {
try {
var currRecord = currentRecord.get();
var numLines = currRecord.getLineCount({
sublistId: 'custpage_sublist'
});
for (var i = 0; i < numLines; i++) {
try {
var lineNum = currRecord.selectLine({
sublistId: 'custpage_sublist',
line: i
});
currRecord.setCurrentSublistValue({
sublistId: 'custpage_sublist',
fieldId: 'custpage_checkbox',
line: lineNum,
value: true
});
} catch (e) {
logme('err@settingcheckbox', getError(e));
}
}
} catch (e) {
logme('err@selectallitems', getError(e));
}
}
/**
* Function will change the selected items status from available quantity to do not commit
*/
function changeCommit() {
try {
var currRecord = currentRecord.get();
var numLines = currRecord.getLineCount({
sublistId: 'custpage_sublist'
});
for (var i = 0; i < numLines; i++) {
try {
var chkboxvalue = currRecord.getSublistValue({
sublistId: 'custpage_sublist',
fieldId: 'custpage_checkbox',
line: i
});
if (chkboxvalue == true) {
var sonumber = currRecord.getSublistValue({
sublistId: 'custpage_sublist',
fieldId: 'custpage_orderno',
line: i
});
var item = currRecord.getSublistValue({
sublistId: 'custpage_sublist',
fieldId: 'custpage_itemname',
line: i
});
var searchResult = search.create({
type: 'salesorder',
filters: [
['tranid', 'is', sonumber]
]
}).run().getRange({
start: 0,
end: 1
});
var sointernalid = searchResult[0].id;
var salesrecord = record.load({
type: 'salesorder',
id: sointernalid
});
var sonumLines = salesrecord.getLineCount({
sublistId: 'item'
});
for (var j = 0; j < sonumLines; j++) {
var itemname = salesrecord.getSublistText({
sublistId: 'item',
fieldId: 'item',
line: j
});
if (itemname == item) {
var commitinventory = salesrecord.getSublistValue({
sublistId: 'item',
fieldId: 'commitinventory',
line: j
});
salesrecord.setSublistValue({
sublistId: 'item',
fieldId: 'commitinventory',
line: j,
value: 3
});
var soRecordId = salesrecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
console.log('soRecordId', soRecordId);
}
}
}
} catch (e) {
logme('err@gettingcheckbox', getError(e));
}
}
//window.location.reload();
} catch (e) {
logme('err@changeCommit', getError(e));
}
}
/*******************************************************************************
* return error
*
* @param e
* @returns {String}
*/
function getError(e) {
var stErrMsg = '';
if (e.getDetails != undefined) {
stErrMsg = '_' + e.getCode() + '<br>' + e.getDetails() + '<br>' +
e.getStackTrace();
} else {
stErrMsg = '_' + e.toString();
}
return stErrMsg;
}
/*******************************************************************************
* Log these data
*/
function logme(title, details) {
log.debug({
title: title,
details: details
});
}
return {
pageInit: pageInit,
selectallitems: selectallitems,
changeCommit: changeCommit,
getError: getError,
logme: logme,
fieldChanged: fieldChanged,
applyfilters: applyfilters
};
});