Jira Code: PROT-97
This task to create a downloadable report. The format of downloading report should be excel. The data will be fetched from the sales order saved search. Generate a saved search based on all PENDING SALES ORDERS FOR 10MUSFR01 10MUSAR01 11GUICE01
Suitelet
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
define([ 'N/ui/serverWidget', 'N/record', 'N/file', 'N/encode', 'N/xml',
'N/render', 'N/search' ],
function(serverWidget, record, file, encode, xml, render, search) {
/**
* Definition of the Suitelet script trigger point.
*
* @param {Object}
* context
* @param {ServerRequest}
* context.request - Encapsulation of the incoming request
* @param {ServerResponse}
* context.response - Encapsulation of the Suitelet response
* @Since 2015.2
*/
function onRequest(context) {
// var user = runtime.getCurrentUser();
var request = context.request;
var response = context.response;
var method = request.method;
if (method == 'GET') {
try {
var form;
form = serverWidget.createForm({
title : 'Create Report:Guitar Center'
});
form.addSubmitButton({
label : 'Download Report'
});
// to create the Sublist
var searchSublist = form.addSublist({
id : 'custpage_sublist',
type : serverWidget.SublistType.INLINEEDITOR,
label : 'Guitar Center'
});
// to add fields for the sublist
var account = searchSublist.addField({
id : 'custpage_customer',
label : 'Account',
type : serverWidget.FieldType.TEXT
});
var so = searchSublist.addField({
id : 'custpage_so',
label : 'SO #',
type : serverWidget.FieldType.TEXT
});
var shipDate = searchSublist.addField({
id : 'custpage_shipdate',
label : 'Original Ship Date',
type : serverWidget.FieldType.DATE
});
var expectedShipDate = searchSublist.addField({
id : 'custpage_expected_date',
label : 'Expected Ship Date',
type : serverWidget.FieldType.TEXT
});
var amount = searchSublist.addField({
id : 'custpage_amount',
label : 'Amount Unbilled',
type : serverWidget.FieldType.CURRENCY
});
var sonotes = searchSublist.addField({
id : 'custpage_so_notes',
label : 'Internal Sales Notes',
type : serverWidget.FieldType.TEXT
});
} catch (e) {
logme("err@ sublist", getError(e));
}
var result = runSearch(search);
// to set to sublist
searchSublist = setValuesToSublist(search, searchSublist, result);
context.response.writePage(form);
}
/** **To download as excel ** */
else {
var resultToExcel = runSearch(search);
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);
}
}
function runSearch(search) {
try {
resultArray = [];
var salesorderSearchObj = search.create({
type : "salesorder",
filters : [
[ "type", "anyof", "SalesOrd" ],
"AND",
[ "status", "anyof", "SalesOrd:B" ],
"AND",
[ "mainline", "is", "T" ],
"AND",
[ [ "customer.entityid", "is", "10MUSFR01" ], "OR",
[ "customer.entityid", "is", "10MUSAR01" ],
"OR",
[ "customer.entityid", "is", "11GUICE01" ] ] ],
columns : [ search.createColumn({
name : "entityid",
join : "customer",
label : "Name"
}), search.createColumn({
name : "tranid",
label : "Document Number"
}), search.createColumn({
name : "shipdate",
label : "Ship Date"
}), search.createColumn({
name : "transhippeddate",
label : "Actual Ship Date"
}), search.createColumn({
name : "amountunbilled",
label : "Amount Unbilled"
}), search.createColumn({
name : "custbody17",
label : "Specific Internal SO Notes"
}) ]
});
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) {
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 account = singleResult.getValue({
name : "entityid",
join : "customer"
});
var soNum = singleResult.getValue({
name : "tranid"
});
var shipdate = singleResult.getValue({
name : "shipdate",
});
var expected = singleResult.getValue({
name : "transhippeddate",
});
var amount = singleResult.getValue({
name : "amountunbilled"
});
var soNotes = singleResult.getValue({
name : "custbody17"
});
searchSublist.setSublistValue({
id : "custpage_customer",
line : i,
value : checkif(account)
});
searchSublist.setSublistValue({
id : "custpage_so",
line : i,
value : checkif(soNum)
});
searchSublist.setSublistValue({
id : "custpage_shipdate",
line : i,
value : checkif(shipdate)
});
searchSublist.setSublistValue({
id : "custpage_expected_date",
line : i,
value : checkif(expected)
});
searchSublist.setSublistValue({
id : "custpage_amount",
line : i,
value : checkif(amount)
});
searchSublist.setSublistValue({
id : "custpage_so_notes",
line : i,
value : checkif(soNotes)
});
}
return searchSublist;
} catch (e) {
logme('E@SetSublistValuestoForm', getError(e));
}
}
return {
onRequest : onRequest,
runSearch : runSearch,
setValuesToSublist : setValuesToSublist,
checkif : checkif,
getXMLDataExcel : getXMLDataExcel
};
});
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
});
}
/*******************************************************************************
* To download XML
*/
function getXMLDataExcel(searchResult, file) {
try {
var XML = "";
var myXMLFile = file.load({
id : '475559'
});
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 account = single_result.getValue({
name : 'entityid',
join : "customer"
});
var documentno = single_result.getValue({
name : "tranid"
});
var shipDate = single_result.getValue({
name : "shipdate"
});
var expectedShipdate = single_result.getValue({
name : 'transhippeddate'
});
var amount = single_result.getValue({
name : "amountunbilled"
});
var soNotes = single_result.getValue({
name : "custbody17"
});
account = checkif(account);
documentno = checkif(documentno);
shipDate = checkif(shipDate);
expectedShipdate = checkif(expectedShipdate);
amount = checkif(amount);
soNotes = checkif(soNotes);
var strVar = "";
strVar += " <Row ss:AutoFitHeight=\"0\">";
strVar += " <Cell><Data ss:Type=\"String\">" + account
+ "<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">" + documentno
+ "<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">" + shipDate
+ "<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">"
+ expectedShipdate + "<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"Number\">" + amount
+ "<\/Data><\/Cell>";
strVar += " <Cell><Data ss:Type=\"String\">" + soNotes
+ "<\/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("err@getXMLData", getError(e));
}
}
XML Code
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Created>2006-09-16T00:00:00Z</Created>
<LastSaved>2006-09-16T00:00:00Z</LastSaved>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
<RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>8010</WindowHeight>
<WindowWidth>14805</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>105</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="1000000" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Column ss:Index="3" ss:AutoFitWidth="0" ss:Width="101.25"/>
<Column ss:AutoFitWidth="0" ss:Width="99"/>
<Column ss:AutoFitWidth="0" ss:Width="90.75"/>
<Column ss:AutoFitWidth="0" ss:Width="98.25"/>
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">Account</Data></Cell>
<Cell><Data ss:Type="String">SO #</Data></Cell>
<Cell><Data ss:Type="String">Original Ship Date</Data></Cell>
<Cell><Data ss:Type="String">Expected ShipDate</Data></Cell>
<Cell><Data ss:Type="String">Amount Unbilled</Data></Cell>
<Cell><Data ss:Type="String">Internal SO notes</Data></Cell>
</Row>
<!-- REPLACEWITHTABLEBODY -->
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Unsynced/>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>7</ActiveRow>
<ActiveCol>5</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:AutoFitHeight="0"/>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:AutoFitHeight="0"/>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>