Jira Code: SC-19
Created a Suitelet form to list all the Items. When we give the date type as date and email id. By default, the email address will be the current user mail address.
When you check the list and click on the submit button the details of the checked items will be collect and Create an Excel report. And this report will send to the respective mail address.
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/**
* Script Description
* This suitelet to create a fullfillment item report.
*/
/*******************************************************************************
*
* SKN Complex
* SC-19 Ful Fill Order Page
*
* NetSuite Name :SC-19 SL Item Report
* Script ID :customscript158
*
* *****************************************************************************
*
*
* $Author: Jobin & Jismi IT Services LLP $
*
* DESCRIPTION
* FullFillment Item Report.
*
* Date Created :19/07/2018
*
* REVISION HISTORY Update:
*
*
*
******************************************************************************/
define(['N/file', 'N/search', 'N/runtime', 'N/record', 'N/https', 'N/encode', 'N/ui/serverWidget', 'N/email'],
function (file, search, runtime, record, https, encode, serverWidget, email) {
var main = {
//function for onRequest-We create the UI
onRequest: function (context) {
var useremail = runtime.getCurrentUser().email
log.debug("useremail", useremail)
log.debug("context.request.method", context.request.method)
if (context.request.method === 'GET') {
//Enter to GET
var response = context.response
// log.debug("scriptcontext",context.request.method);
var form = serverWidget.createForm({
title: "FulFill Item Report"
})
form.addButton({
id: 'Submit',
label: 'Apply Filter',
functionName: 'filter'
})
var emailaddress = form.addField({
id: 'emailaddress',
type: serverWidget.FieldType.EMAIL,
label: 'E Mail Address'
})
emailaddress.updateDisplaySize({
height: 60,
width: 40
});
emailaddress.isMandatory = true;
var hiddenfield = form.addField({
id: 'hiddenfield',
type: serverWidget.FieldType.INLINEHTML,
label: 'Hidden'
})
var fieldgroupdatefilter = form.addFieldGroup({
id: 'fieldgroupdatefilter',
label: 'Invoice Date Filters'
})
var datefiltertype = form.addField({
id: 'datefiltertype',
type: serverWidget.FieldType.SELECT,
label: 'Date filter type',
container: 'fieldgroupdatefilter'
})
datefiltertype.isMandatory = true;
datefiltertype.updateBreakType({
breakType: serverWidget.FieldBreakType.STARTROW
})
main.addtype(datefiltertype);
var todate = form.addField({
id: 'todate',
type: serverWidget.FieldType.DATE,
label: 'To date',
container: 'fieldgroupdatefilter'
})
todate.updateBreakType({
breakType: serverWidget.FieldBreakType.STARTROW
})
todate.updateDisplaySize({
height: 60,
width: 64.8
});
todate.isMandatory = true;
var fromdate = form.addField({
id: 'fromdate',
type: serverWidget.FieldType.DATE,
label: 'From date ',
container: 'fieldgroupdatefilter'
})
fromdate.updateBreakType({
breakType: serverWidget.FieldBreakType.STARTROW
})
fromdate.updateDisplaySize({
height: 60,
width: 64.8
});
var sublist = form.addSublist({
id: 'itemslist',
type: serverWidget.SublistType.LIST,
label: 'Item List'
})
sublist.addMarkAllButtons();
var checkbox = sublist.addField({
id: 'custpage_check',
label: '<p align="center">check</p>',
type: serverWidget.FieldType.CHECKBOX
});
checkbox.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
sublist.addField({
id: 'po',
type: serverWidget.FieldType.TEXT,
label: 'PO',
})
sublist.addField({
id: 'so',
type: serverWidget.FieldType.TEXT,
label: 'SO',
})
sublist.addField({
id: 'sku',
type: serverWidget.FieldType.TEXT,
label: 'SKU',
})
sublist.addField({
id: 'displayname',
type: serverWidget.FieldType.TEXT,
label: 'Display Name',
})
sublist.addField({
id: 'qty',
type: serverWidget.FieldType.CURRENCY,
label: 'QTY',
})
sublist.addField({
id: 'price',
type: serverWidget.FieldType.CURRENCY,
label: 'Price',
})
sublist.addField({
id: 'totallaneitem',
type: serverWidget.FieldType.CURRENCY,
label: 'Total Line Item ',
})
sublist.addField({
id: 'vendorcode',
type: serverWidget.FieldType.TEXT,
label: 'Vendor Code',
})
sublist.addField({
id: 'vendorcost',
type: serverWidget.FieldType.CURRENCY,
label: 'Vendor Price',
})
//Call for the Client Script and getting values from the Client Script.
form.clientScriptFileId = '110416';
var emailss = context.request.parameters.emailaddress
var datetype = context.request.parameters.datefiltertype
var datefrom = context.request.parameters.fromdate
var dateto = context.request.parameters.todate
//Setting Default Values
if ((emailss == null) || (emailss == "")) {
emailaddress.defaultValue = useremail
} else {
emailaddress.defaultValue = emailss;
}
if ((datetype == null) || (datetype == "")) {
datefiltertype.defaultValue = 'ON';
datetype = "ON"
} else {
datefiltertype.defaultValue = datetype;
}
if ((datefrom == null) || (datefrom == "")) {
} else {
fromdate.defaultValue = datefrom;
}
if ((dateto == null) || (dateto == "")) {
var date = new Date();
var month = date.getMonth() + 1;
date = date.getDate() + "/" + month + "/" + date.getFullYear();
todate.defaultValue = date;
dateto = date
} else {
todate.defaultValue = dateto;
}
var itemvalue = main.itemSearch(sublist, dateto, datefrom, datetype, response, hiddenfield)
form.addSubmitButton({
label: 'Send Mail'
});
context.response.writePage(form);
} else {
//POST Part
var lines = context.request.getLineCount({
group: "itemslist"
});
log.debug("Post", lines)
//Getting the values from the selected fields to the array
var poarray = [];
for (var i = 0; i < lines; i++) {
var chkboxvalue = context.request.getSublistValue({
group: 'itemslist',
name: 'custpage_check',
line: i
});
if (chkboxvalue == 'T') {
var poobj = {};
poobj.po = context.request.getSublistValue({
group: 'itemslist',
name: 'po',
line: i
});
poobj.so = context.request.getSublistValue({
group: 'itemslist',
name: 'so',
line: i
});
poobj.item = context.request.getSublistValue({
group: 'itemslist',
name: 'sku',
line: i
});
poobj.displayname = context.request.getSublistValue({
group: 'itemslist',
name: 'displayname',
line: i
});
poobj.quantity = context.request.getSublistValue({
group: 'itemslist',
name: 'qty',
line: i
});
poobj.rate = context.request.getSublistValue({
group: 'itemslist',
name: 'price',
line: i
});
poobj.amount = context.request.getSublistValue({
group: 'itemslist',
name: 'totallaneitem',
line: i
});
poobj.vendorcode = context.request.getSublistValue({
group: 'itemslist',
name: 'vendorcode',
line: i
});
poobj.vendorcost = context.request.getSublistValue({
group: 'itemslist',
name: 'vendorcost',
line: i
});
poarray.push(poobj)
}
}
//Creating the Excel File
var xml_to_print = main.getXMLDataExcel(poarray, lines)
var strXmlEncoded = encode.convert({
string: xml_to_print,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
var fileObj = file.create({
name: 'report.xls',
fileType: file.Type.EXCEL,
contents: strXmlEncoded
});
context.response.writeFile(fileObj)
//Sending the mail
var emailadddd = context.request.parameters.emailaddress
log.debug("emailadress", emailadddd)
email.send({
author: -5,
recipients: emailadddd,
subject: "FulFillment Report",
attachments: [fileObj],
body: "The excel file is attached."
});
log.debug("mail is sendend", "mail send")
}
},
// Date type drop list
addtype: function (field) {
var dateobj = [{
"value": "",
"text": ""
}, {
"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;
},
alignLeft: function (r) {
return '<p align="Left">' + r + '</p>';
},
alignCenter: function (r) {
return '<p align="Center">' + r + '</p>';
},
// Search for the items
itemSearch: function (sublist, dateto, datefrom, datetype, response, hiddenfield) {
log.debug(datetype, datefrom + " " + dateto)
var transactionSearchObj = search.create({
type: "invoice",
filters: [
["item.type", "noneof", "Discount", "@NONE@", "Subtotal", "Service", "Payment", "OthCharge", "Description"],
"AND",
["trandate", datetype, datefrom, dateto],
"AND",
["type", "anyof", "CustInvc"],
"AND",
["item.internalid", "noneof", "7"]
],
columns: [
search.createColumn({
name: "item",
label: "Item"
}),
search.createColumn({
name: "displayname",
join: "item",
label: "Display Name"
}),
search.createColumn({
name: "quantity",
label: "Quantity"
}),
search.createColumn({
name: "rate",
label: "Item Rate"
}),
search.createColumn({
name: "amount",
label: "Amount"
}),
search.createColumn({
name: "otherrefnum",
join: "createdFrom",
label: "PO/Cheque Number"
}),
search.createColumn({
name: "tranid",
join: "createdFrom",
label: "Document Number"
}),
search.createColumn({
name: "vendorcode",
join: "item",
label: "Vendor Code"
}),
search.createColumn({
name: "vendorcost",
join: "item",
label: "Vendor Price"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
var newobj = [];
var itemlength = searchResultCount
if (searchResultCount > 3999) {
hiddenfield.defaultValue = '<html> <body> <script> alert("Search Result is more than the Limit"); </script> </body> </html>'
return []
}
transactionSearchObj.run().each(function (result) {
var tempObj = {};
var so1 = main.checkifnull(result.getValue({
name: "tranid",
join: "createdFrom",
label: "SO"
}));
tempObj.so = so1;
var po1 = main.checkifnull(result.getValue({
name: "otherrefnum",
join: "createdFrom",
label: "PO"
}));
tempObj.po = po1;
var item = main.checkifnull(result.getText({
name: "item",
label: "Item"
}));
tempObj.item = item;
var displayname = main.checkifnull(result.getValue({
name: "displayname",
join: "item",
label: "Display Name"
}));
tempObj.displayname = displayname;
var quantity = main.checkifempty(result.getValue({
name: "quantity",
label: "Quantity"
}));
tempObj.quantity = quantity;
var amount = main.checkifempty(result.getValue({
name: "amount",
label: "Total Line Item"
}));
tempObj.amount = amount;
var vendorcode = main.checkifnull(result.getValue({
name: "vendorcode",
join: "item",
label: "Vendor Code"
}));
tempObj.vendorcode = vendorcode;
var rate = main.checkifempty(result.getText({
name: "rate",
label: "Price"
}));
tempObj.rate = rate;
var vendorcost = main.checkifempty(result.getValue({
name: "vendorcost",
join: "item",
label: "Vendor Price"
}));
tempObj.vendorcost = vendorcost;
newobj.push(tempObj);
return true;
});
main.setValue(newobj, sublist, itemlength, transactionSearchObj, response)
},
//Setting Values to the Sublist
setValue: function (itemvalue, sublist, j, transactionSearchObj, response) {
log.debug("itemvalue111", itemvalue)
for (i = 0; i < j; i++) {
sublist.setSublistValue({
id: 'custpage_check',
line: i,
value: 'T'
});
sublist.setSublistValue({
id: 'so',
line: i,
value: main.alignLeft(itemvalue[i].so)
});
sublist.setSublistValue({
id: 'po',
line: i,
value: main.alignLeft(itemvalue[i].po)
});
sublist.setSublistValue({
id: 'sku',
line: i,
value: main.alignLeft(itemvalue[i].item)
});
sublist.setSublistValue({
id: 'displayname',
line: i,
value: main.alignLeft(itemvalue[i].displayname)
});
sublist.setSublistValue({
id: 'qty',
line: i,
value: itemvalue[i].quantity
});
sublist.setSublistValue({
id: 'price',
line: i,
value: itemvalue[i].rate
});
sublist.setSublistValue({
id: 'totallaneitem',
line: i,
value: itemvalue[i].amount
});
sublist.setSublistValue({
id: 'vendorcode',
line: i,
value: main.alignCenter(itemvalue[i].vendorcode)
});
sublist.setSublistValue({
id: 'vendorcost',
join: "item",
line: i,
value: (itemvalue[i].vendorcost) * (itemvalue[i].quantity)
});
}
},
checkifnull: function (argument) {
if (argument != undefined && argument != null && argument != '')
return argument;
else
return "-";
},
checkifempty: function (argument) {
if (argument != undefined && argument != null && argument != '')
return argument;
else
return 0;
},
//Getting the excel file contents and Creating the Excel file
getXMLDataExcel: function (itemvalue, transactionSearchObj) {
log.debug("itemvaluetest", itemvalue)
var XML = "";
var myXMLFile = file.load({
id: '110417'
});
var myXMLFile_value = myXMLFile.getContents();
if (itemvalue.length > 0) {
var TABLE = "";
for (var i = 0; i < itemvalue.length; i++) {
var single_result = itemvalue[i];
var strVar = "";
strVar += "<Row ss:AutoFitHeight=\"0\">";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.po + " " + single_result.so + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.item + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.displayname + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.quantity + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.rate + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.amount + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.vendorcode + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + single_result.vendorcost + "<\/Data><\/Cell>";
strVar += "<\/Row>";
if (i < (itemvalue.length - 1)) {
strVar = strVar + '\n';
}
TABLE = TABLE + strVar;
log.debug("Table", TABLE)
}
XML = myXMLFile_value.replace('<!-- REPLACEWITHTABLEBODY -->', TABLE);
log.debug("XML", XML)
}
return XML;
}
}
for (var key in main) {
if (typeof main[key] === 'function') {
main[key] = trycatch(main[key], key);
}
}
function trycatch(myfunction, key) {
return function () {
try {
return myfunction.apply(this, arguments);
} catch (e) {
log.debug("e in " + key, e);
}
}
};
return main;
});/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/**
* Script Description
* This ClientScript for the filter action.
*/
/*******************************************************************************
*
* SKN Complex
* SC-19 Ful Fill Order Page
*
* NetSuite Name :SC-19 CS Fullfillment Page filter
*
* Dependent to :SC-19 SL Item Report
*
* *****************************************************************************
*
*
* $Author: Jobin & Jismi IT Services LLP $
*
* DESCRIPTION
* FullFillment Item Report.
*
* Date Created :19/07/2018
*
* REVISION HISTORY Update:
*
*
*
******************************************************************************/
define(['N/record', 'N/search', 'N/currentRecord','N/https','N/url'],
function(record, search, currentRecord,https,url) {
var main={
//Hiding the Field for the From date when page is loaded
pageInit :function(scriptContext) {
jQuery("#fromdate").hide()
jQuery("#fromdate_fs_lbl_uir_label").hide();
window.onbeforeunload = function() {
}
var records = scriptContext.currentRecord;
},
//To get the values.
fieldChanged: function(scriptContext) {
var records = scriptContext.currentRecord;
if (scriptContext.fieldId == "datefiltertype") {
var type = records.getValue({
fieldId: 'datefiltertype'
});
var fromdate = records.getValue({
fieldId: 'fromdate'
});
var todate = records.getValue({
fieldId: 'todate'
});
// console.log("datefiltertype",type);
// console.log("fromdate",fromdate);
// console.log("todate",todate);
if (type == "NOTWITHIN" || type == "WITHIN") {
jQuery("#todate").show();
jQuery("#fromdate").show();
jQuery("#fromdate_fs_lbl_uir_label").show();
} else {
jQuery("#todate").show();
jQuery("#fromdate").hide();
jQuery("#fromdate_fs_lbl_uir_label").hide();
}
}
},
//For the button action in Suitelet
filter:function()
{
//Passing the values to the Suitelet as parameters.
var records=currentRecord.get()
var type = records.getValue({
fieldId: 'datefiltertype'
});
var fromdate = records.getText({
fieldId: 'fromdate'
});
var todate = records.getText({
fieldId: 'todate'
});
var emailaddress=records.getText({
fieldId:'emailaddress'
})
console.log("type",type)
console.log("fromdate",fromdate)
console.log("todate",todate)
console.log("emailaddress",emailaddress)
console.log("typelength",type.length)
if( type==null || type=="" || type==undefined||todate==null || todate=="" || todate==undefined)
{
alert("Please fill the fields");
}else{
var dashboardurl = url.resolveScript({
scriptId: "customscript158",
deploymentId: "customdeploy1",
returnExternalUrl: false
});
window.location = dashboardurl + "&todate=" + todate + "&fromdate=" + fromdate + "&datefiltertype=" + type + "&emailaddress=" +emailaddress
console.log("newpage","newpage")
}
},
//To check checkbox value
saveRecord: function(scriptContext) {
var checkarray = jQuery('.checkbox')
var count = 0;
for (var i = 0; i < checkarray.length; i++) {
if (checkarray[i].checked) {
count++
}
}
if (count < 1) {
if (confirm("Please select the items.")) {
}else{
}
}
else{
return true;
}
}
};
for (var key in main) {
if (typeof main[key] === 'function') {
main[key] = trycatch(main[key], key);
}
}
function trycatch(myfunction, key) {
return function() {
try {
return myfunction.apply(this, arguments);
} catch (e) {
console.error('e', e);
}
}
};
return main
});
Xml Data
<?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>2018-07-13T11:14:12Z</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>7365</WindowHeight>
<WindowWidth>14505</WindowWidth>
<WindowTopX>390</WindowTopX>
<WindowTopY>600</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="100000" ss:ExpandedRowCount="100000" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String"> PO SO </Data></Cell>
<Cell><Data ss:Type="String">SKU</Data></Cell>
<Cell><Data ss:Type="String">Display Name</Data></Cell>
<Cell><Data ss:Type="String">Qty</Data></Cell>
<Cell><Data ss:Type="String">Price</Data></Cell>
<Cell><Data ss:Type="String">Total Line Item</Data></Cell>
<Cell><Data ss:Type="String">Vendor Code</Data></Cell>
<Cell><Data ss:Type="String">Vendor Price</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/>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveCol>6</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>