Jira Code: PROT 68
We would like to have a report generated to show ALL bin transfers, inventory transfers and inventory adjustments (together) using the parameters of date range & User ID.
Suitelet script
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/**
* Script Description
* This script will show All Inventory transfer, Inventory adjustments and Bin transfers.
*/
/*******************************************************************************
* PROTEC
* **************************************************************************
* Date: 21/04/2018
*
* Author: Jobin & Jismi IT Services LLP
*
*
* REVISION HISTORY :
*
* Revision 1.0 $ 21/04/2018 chinju : Created
* Revision 1.1 aj: Modified
*
******************************************************************************/
define(
[ 'N/ui/serverWidget', 'N/record', 'N/file', 'N/encode', 'N/xml',
'N/render', 'N/search' ],
function(serverWidget, record, file, encode, xml, render, search) {
function onRequest(context) {
try {
// var user = runtime.getCurrentUser();
var request = context.request;
var response = context.response;
var method = request.method;
if (method == 'GET') {
var form;
form = serverWidget
.createForm({
title : 'Create Report: Bin transfers, Inventory transfers and Inventory adjustments'
});
var datefromfield = form.addField({
id : 'custpage_datefrom',
type : serverWidget.FieldType.DATE,
label : 'DATE FROM'
// source: 'department'
});
datefromfield.isMandatory = true;
/*var userfield = form.addField({
id : 'custpage_userid',
type : serverWidget.FieldType.SELECT,
label : 'User',
source : 'employee'
});*/
//userfield.isMandatory = true;
var datetofield = form.addField({
id : 'custpage_dateto',
type : serverWidget.FieldType.DATE,
label : 'DATE TO'
// source: 'department'
});
datetofield.isMandatory = true;
/*hidden.updateDisplayType({
displayType : serverWidget.FieldDisplayType.HIDDEN
});*/
form.addSubmitButton({
label : 'Download Report'
});
context.response.writePage(form);
} else {
try {
var date = request.parameters.custpage_datefrom;
var dateto = request.parameters.custpage_dateto;
// logme('dateto', dateto);
var filters_all = [], filters_inventory=[];
filters_all.push([ "type", "anyof", "BinTrnfr",
"InvTrnfr" ]);
filters_all.push("AND");
filters_all.push([ "trandate","within",date,dateto]);
/*filters_all.push([ "trandate", "onorafter", "12/4/18" ]);
filters_all.push("AND");
filters_all.push([ "trandate", "before", "22/4/18" ]);*/
filters_all.push("AND");
filters_all.push([ "item.internalidnumber","greaterthan", "0" ]);
/*filters_all.push("AND");
filters_all.push([ "employee", "anyof",
"internalid" ]);*/
// to create the inventory adjustment search
filters_inventory.push(["type", "anyof","InvAdjst"]);
filters_inventory.push("AND");
filters_inventory.push([ "trandate","within",date,dateto]);
filters_inventory.push("AND");
filters_inventory.push([ "item.internalidnumber","greaterthan", "0" ]);
var inventorySearchObj = search
.create({
type : "transaction",
filters : filters_inventory,
columns : [
search.createColumn({
name: "type",
summary: "GROUP",
label: "Type"
}),
search.createColumn({
name: "tranid",
summary: "MAX",
sort: search.Sort.ASC,
label: "Document Number"
}),
search.createColumn({
name: "trandate",
summary: "GROUP",
label: "Date"
}),
search.createColumn({
name: "date",
join: "systemNotes",
summary: "MAX",
label: "Date"
}),
/* search.createColumn({
name: "name",
join: "systemNotes",
summary: "MIN",
label: "Set by"
}),*/
search.createColumn({
name: "createdby",
summary: "MIN",
label: "Created By"
}),
search.createColumn({
name: "item",
summary: "GROUP",
label: "Item"
}),
search.createColumn({
name: "salesdescription",
join: "item",
summary: "MIN",
label: "Description"
}),
search.createColumn({
name: "binnumber",
summary: "MAX",
label: "Transaction Bin Number"
}),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "case when nvl({quantity},0) < '0' then {binnumber} else null end",
label: "Formula (Text)"
}),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "case when nvl({quantity},0) > '0' then {binnumber} else null end",
label: "Formula (Text)"
}),
search.createColumn({
name: "binnumber",
join: "inventoryDetail",
summary: "COUNT",
label: "Count Bin Number"
}),
search.createColumn({
name: "quantity",
summary: "MAX",
label: "Quantity"
}),
search.createColumn({
name: "quantity",
join: "inventoryDetail",
summary: "MAX",
label: "Quantity"
})
]
});
var transactionSearchObj = search
.create({
type : "transaction",
filters : filters_all
/*
* [
* ["type","anyof","BinTrnfr","InvTrnfr","InvAdjst"],
* "AND", ["trandate","onorafter",date],
* "AND", ["trandate","before",dateto] ]
*/,
columns : [
search.createColumn({
name: "type",
summary: "GROUP",
label: "Type"
}),
search.createColumn({
name: "tranid",
summary: "MAX",
sort: search.Sort.ASC,
label: "Document Number"
}),
search.createColumn({
name: "trandate",
summary: "GROUP",
label: "Date"
}),
search.createColumn({
name: "date",
join: "systemNotes",
summary: "MAX",
label: "Date"
}),
/* search.createColumn({
name: "name",
join: "systemNotes",
summary: "MIN",
label: "Set by"
}),*/
search.createColumn({
name: "createdby",
summary: "MIN",
label: "Created By"
}),
search.createColumn({
name: "item",
summary: "GROUP",
label: "Item"
}),
search.createColumn({
name: "salesdescription",
join: "item",
summary: "MIN",
label: "Description"
}),
search.createColumn({
name: "formulatext",
summary: "MAX",
formula: "case when nvl({quantity},0) < '0' then {binnumber} else null end",
label: "Formula (Text)"
}),
search.createColumn({
name: "formulatext",
summary: "MAX",
formula: "case when nvl({quantity},0) = '1' then {binnumber} else null end",
label: "Formula (Text)"
}),
search.createColumn({
name: "formulatext",
summary: "MAX",
formula: "case when nvl({quantity},0) > '0' then {binnumber} else null end",
label: "Formula (Text)"
}),
search.createColumn({
name: "formulatext",
summary: "MAX",
formula: "case when nvl({quantity},0) < '0' then {binnumber} else null end",
label: "Formula (Text)"
}),
search.createColumn({
name: "quantity",
summary: "MAX",
label: "Quantity"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count",searchResultCount);
// to get search result more than 1000
var start = 0;
var end = 1000;
var resultarray = [];
var result;
var singleresult;
for (var i = 0; i < Math.ceil(searchResultCount / 1000); i++)
{
result = transactionSearchObj.run().getRange({
start: start,
end: end
});
// logme('result', result);
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
resultarray.push(singleresult);
}
start = end;
end = end + 1000;
}
var searchResultCountOfInventry = inventorySearchObj.runPaged().count;
log.debug("searchResultCountOfInventry = ",searchResultCountOfInventry);
// to get search result more than 1000
var start = 0;
var end = 1000;
for (var i = 0; i < Math.ceil(searchResultCountOfInventry / 1000); i++)
{
result = inventorySearchObj.run().getRange({
start: start,
end: end
});
// logme('result', result);
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
resultarray.push(singleresult);
}
start = end;
end = end + 1000;
}
// logme('resultarray', resultarray);
logme('resultarray.length', resultarray.length);
// to make error message when search result ==0
if(resultarray==0)
{
var form;
form = serverWidget
.createForm({
title : 'Create Report: Bin transfers, Inventory transfers and Inventory adjustments'
});
var hidden = form.addField({
id : 'custpage_hidden',
type : serverWidget.FieldType.INLINEHTML,
label : 'Hidden'
// source: 'department'
});
hidden.defaultValue = html;
logme('hidden', hidden);
var html = '<html> <body> <script> window.onload =alert("No results found")</script> </body> </html>'
//var html = ''<html> <body onload="myFunction()"> <script> function myFunction() { alert("No results found"); } </script> </body> </html>';
//var html = '<html> <body> <form action="/action_page.php"> <script> alert("No Results found") </script> </form> </body> </html>'
logme('hidden', hidden);
hidden.defaultValue = html;
context.response.writePage(form);
}
if(resultarray.length>=14000){
try{
var form;
form = serverWidget
.createForm({
title : 'Create Report: Bin transfers, Inventory transfers and Inventory adjustments'
});
var hidden = form.addField({
id : 'custpage_hidden',
type : serverWidget.FieldType.INLINEHTML,
label : 'Hidden'
// source: 'department'
});
hidden.defaultValue = html;
logme('hidden', hidden);
var html = '<html> <body> <script> window.onload =alert("File Exceeds 10.0 GB")</script> </body> </html>'
//var html = ''<html> <body onload="myFunction()"> <script> function myFunction() { alert("No results found"); } </script> </body> </html>';
//var html = '<html> <body> <form action="/action_page.php"> <script> alert("No Results found") </script> </form> </body> </html>'
logme('hidden', hidden);
hidden.defaultValue = html;
context.response.writePage(form);
}catch (e) {
logme("TRY1", getError(e));
}
}
// to convert to excel
// XML_TO_PRINT += getXMLDataExcel(result, file,transactionSearchObj);
var XML_TO_PRINT = getXMLDataExcel(resultarray, file,transactionSearchObj,inventorySearchObj);
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
});
} catch (e) {
logme("TRY1", getError(e));
}
response.writeFile(pdfFile, true);
}
// response.write(XML_TO_PRINT);
//response.writeFile(pdfFile, true);
} catch (e) {
logme("TRY1", getError(e));
}
}
return {
onRequest : onRequest
};
});
//var XML = "";
function getXMLDataExcel(searchResult, file,transactionSearchObj,inventorySearchObj) {
var XML = "";
var myXMLFile = file.load({
id : '463624'
});
var myXMLFile_value = myXMLFile.getContents();
if (searchResult.length > 0) {
var TABLE = "";
for (var i = 0; i < searchResult.length; i++) {
var single_result = searchResult[i];
// get values
var type = single_result.getText({
name : 'type',
summary : "GROUP"
});
var documentno = single_result.getValue({
name : "tranid",
summary : "MAX"
});
var user = single_result.getValue({
name : "createdby",
summary : "MIN"
});
//logme('user', user);
/*
* var id = single_result.getValue({ name : "internalidnumber" });
*/
var datetoprint = single_result.getValue({
name : 'trandate',
summary : "GROUP"
});
// logme('datetoprint', datetoprint);
var time = single_result.getValue({
name : "date",
join : "systemNotes",
summary : "MAX"
});
var item = single_result.getText({
name : "item",
summary : "GROUP"
});
var itemdescription = single_result.getValue({
name : "salesdescription",
join : "item",
summary : "MIN"
});
// logme('itemdescription', itemdescription);
qty = single_result.getValue({
name : "quantity",
summary : "MAX"
// join: "inventoryDetail"
});
logme('qty', qty);
var binfrominvet =single_result.getValue(transactionSearchObj.columns[7]);
//logme('binfrom', binfrom);
var bintoinvet = single_result.getValue(transactionSearchObj.columns[8]);
var binfrombin =single_result.getValue(transactionSearchObj.columns[9]);
var bintobin =single_result.getValue(transactionSearchObj.columns[10]);
var binnum=single_result.getValue(inventorySearchObj.columns[7]);
var binnumCount=single_result.getValue(inventorySearchObj.columns[10]);
logme('binnum',binnum);
logme('binnumCount',binnumCount);
/*var invtryAdjFrom =single_result.getValue({
name : "intryFrom",
summary : "GROUP"
});
logme('invtryAdjFrom',invtryAdjFrom);
var invtryAdjTo =single_result.getValue({
name : "intryTo",
summary : "GROUP"
});
logme('invtryAdjTo',invtryAdjTo);*/
//logme('binto', binto);
/*var fromlocation = single_result
.getValue({
name : "formulatext",
summary : "GROUP",
formula : "case when nvl({quantity},0) < '0' then {location} else null end"
});
var tolocation = single_result
.getValue({
name : "formulatext",
summary : "GROUP",
formula : " case when nvl({quantity},0) = '1' then {location} else null end"
});*/
// set values to table
//to take from bin and to bin for inventory transfer and bin transfer.
var qty;
var frombinforinventroy,tobinforinventroy;
if(type == 'Inventory Transfer')
{
frombinforinventroy = binfrominvet;
tobinforinventroy = bintoinvet;
}else if (type == 'Bin Transfer'){
frombinforinventroy = binfrombin;
tobinforinventroy = bintobin;
}
else{
if(qty<0)
{
tobinforinventroy=binnum;
frombinforinventroy='-None-';
}
else{
frombinforinventroy=binnum;
tobinforinventroy='-None-';
}
if(binnumCount>1)
{
/*qty = single_result.getValue({
name: "quantity",
join: "inventoryDetail",
summary: "MAX"
});*/
qty=single_result.getValue(inventorySearchObj.columns[12]);
}
else{
qty=single_result.getValue(inventorySearchObj.columns[11]);
}
}
/*if(type == 'Inventory Adjustment')
{
var frombinforinventroy = " ";
var tobinforinventroy = " ";
}*/
var strVar="";
strVar += " <Row ss:AutoFitHeight=\"0\">";
strVar += " <Cell><Data ss:Type=\"String\">"+type+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+documentno+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+datetoprint+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+time+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+user+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+item+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+itemdescription+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+frombinforinventroy+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+tobinforinventroy+"<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"+qty+"<\/Data><\/Cell>";
strVar += " <\/Row>";
if (i < (searchResult.length - 1)) {
strVar = strVar + '\n';
}
TABLE = TABLE + strVar;
}
XML = myXMLFile_value.replace('<!-- REPLACEWITHTABLEBODY -->', TABLE);
}
return XML;
}
/*******************************************************************************
* 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.error({
title : title,
details : details
});
}