Jira Code: TC-140
We have to display the open sales order back ordered items of customers using a suitelet page and added it to the center link. We have set an email option to send the result pdf to the corresponding customers and PDF download for the result.
Suitelet
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CLIENTNAME:Taipan Canada
* TC-140
* Open Order Report
* **************************************************************************
* Date : 10-06-2019
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : This script is display the custom suitelet page Open Orders Report
* Date created : 10-06-2019
*
* REVISION HISTORY
*
* Revision 1.0 ${10-06-2019} nd :marg created
*
*****************************************************************************/
define(['N/record', 'N/search', 'N/ui/serverWidget', 'N/render', 'N/email', 'N/file', 'N/encode', 'N/task'],
function(record, search, serverWidget, render, email, file, encode, task) {
function onRequest(context) {
try {
var customerid = context.request.parameters.customerid;
var custName = context.request.parameters.custName;
var download = context.request.parameters.download;
var filterValue = context.request.parameters.filterValue;
var send = context.request.parameters.send;
if (context.request.method == 'GET') {
//To create a form
var form = serverWidget.createForm({
title: 'Open Orders Report'
});
form.clientScriptFileId = 128724;
var customer_name = form.addField({
id: 'custpage_customer',
type: serverWidget.FieldType.SELECT,
source: 'customer',
label: 'Customer Name'
});
if (customerid || (download && customerid)) {
customer_name.defaultValue = customerid;
}
var order = form.addSublist({
id: 'custpage_order',
type: serverWidget.SublistType.LIST,
label: 'Sales Order Backordered Items Info'
});
order.addField({
id: 'custpage_itemname',
type: serverWidget.FieldType.TEXT,
label: 'Item name'
});
order.addField({
id: 'custpage_qty',
type: serverWidget.FieldType.TEXT,
label: 'Qty'
});
order.addField({
id: 'custpage_itemeta',
type: serverWidget.FieldType.TEXT,
label: 'ETA'
});
order.addField({
id: 'custpage_customername',
type: serverWidget.FieldType.TEXT,
label: 'Customer Name'
});
order.addField({
id: 'custpage_sonumber',
type: serverWidget.FieldType.TEXT,
label: 'SO#'
});
order.addField({
id: 'custpage_sostatus',
type: serverWidget.FieldType.TEXT,
label: 'Status'
});
order.addField({
id: 'custpage_poumber',
type: serverWidget.FieldType.TEXT,
label: 'PO#'
});
// to add buttons
var DownloadPdf = form.addButton({
id: 'custpage_downloafpdf',
label: 'Download PDF',
functionName: 'DownloadPdf'
});
var sendEmail = form.addButton({
id: 'custpage_sendemail',
label: 'Send Email',
functionName: 'sendEmail'
});
// var filters = form.addButton({
// id: 'custpage_filters',
// label: 'Apply Filters',
// functionName: 'Applyfilters'
// });
if(customerid){
var custRec = search.lookupFields({
type: search.Type.CUSTOMER,
id: customerid,
columns: ['email', 'custentity_website']
});
var emailAdd = custRec.email;
if(custRec.custentity_website[0])
var website = custRec.custentity_website[0].value;
log.debug('website', website)
}
if(website){
image = 'http://shopping.na3.netsuite.com/core/media/media.nl?id=43966&c=3889163&h=52afb369f69d5dfbe49c'
}else{
image = 'http://shopping.na3.netsuite.com/core/media/media.nl?id=124609&c=3889163&h=2d96e5fcae971d296ae0'
}
if (filterValue) {
var searchResult = runSearch(customerid);
setfieldValues(searchResult, order);
}
if (download) {
var searchResult = runSearch(customerid);
setfieldValues(searchResult, order);
var createtabele = createTableContent(searchResult);
custName = escape_for_xml(custName);
var downloadxml = xmlContents(createtabele, custName ,image);
var renderer = render.create();
renderer.templateContent = downloadxml
var pdfFile = renderer.renderAsPdf();
context.response.writeFile(pdfFile, true);
}
if (send) {
var searchResult = runSearch(customerid);
setfieldValues(searchResult, order);
var createtabele = createTableContent(searchResult);
log.debug("createtabele", createtabele);
custName = escape_for_xml(custName);
var emailid = emailAdd;
log.debug('emailid', emailid)
var downloadxml = xmlContents(createtabele, custName,image);
log.debug("downloadxml", downloadxml);
var renderer = render.create();
renderer.templateContent = downloadxml
var pdfFile = renderer.renderAsPdf();
recipientId = 'ralph@taipancanada.com'//'margrat@jobinandjismi.com'
email.send({
author: 6517,
recipients: recipientId,
subject: 'Backordered Items Info',
body: 'Please find the attched document for your backordered items Info',
attachments: [pdfFile]
});
}
context.response.writePage(form);
}
} catch (err)
{
log.debug('Error @ Function', err)
}
}
function runSearch(customerid) {
var arrayfilter = [
["type", "anyof", "SalesOrd"],
"AND", ["status", "anyof", "SalesOrd:D", "SalesOrd:A", "SalesOrd:F", "SalesOrd:E", "SalesOrd:B"],
"AND", ["formulanumeric: {quantity}-{quantitypicked}", "notequalto", "0"],
"AND", ["mainline", "is", "F"],
"AND", ["item.quantitybackordered", "greaterthan", "0"]
];
if (customerid) {
arrayfilter.push("AND", ["customer.internalid", "anyof", customerid]);
}
log.debug('arrayfilter', arrayfilter)
var openOrderReportSerach = search.create({
type: "salesorder",
filters: arrayfilter,
columns: [
search.createColumn({
name: "altname",
join: "customer",
label: "Name"
}),
search.createColumn({
name: "tranid",
label: "Document Number"
}),
search.createColumn({
name: "statusref",
label: "Status"
}),
search.createColumn({
name: "otherrefnum",
label: "PO/Cheque Number"
}),
search.createColumn({
name: "itemid",
join: "item",
label: "Name"
}),
search.createColumn({
name: "unpickedorderquantity",
label: "Unpicked Order Quantity"
}),
search.createColumn({
name: "custcol_eta_date",
label: "ETA Date"
}),
search.createColumn({
name: "internalid",
join: "item",
label: "Internal ID"
})
]
});
var searchResultCount = openOrderReportSerach.runPaged().count;
var dataArray = [];
openOrderReportSerach.run().each(function(result) {
var dataObj = {};
var customername = result.getValue({
name: "altname",
join: "customer",
label: "Name"
});
// log.debug("email",email)
var sonumber = result.getValue({
name: 'tranid'
});
var status = result.getValue({
name: 'statusref'
});
var ponumber = result.getValue({
name: 'otherrefnum'
});
var itemname = result.getValue({
name: "itemid",
join: "item",
label: "Name"
});
itemIntId = result.getValue({
name: "internalid",
join: "item",
label: "Internal ID"
});
var qtybackordered = result.getValue({
name: 'unpickedorderquantity'
});
var etadate = result.getValue({
name: 'custcol_eta_date'
});
dataObj.customername = customername;
dataObj.email = email;
dataObj.sonumber = sonumber;
dataObj.status = status;
dataObj.ponumber = ponumber;
dataObj.itemname = itemname;
dataObj.qtybackordered = qtybackordered;
dataObj.etadate = getEtaDate(itemIntId);
log.debug(" dataObj.etadate", dataObj.etadate)
dataArray.push(dataObj);
return true;
});
return dataArray;
}
function getEtaDate(itemid) {
var purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters: [
["type", "anyof", "PurchOrd"],
"AND", ["item", "anyof", itemid],
"AND", ["status", "anyof", "PurchOrd:B"]
],
columns: [
search.createColumn({
name: "item",
label: "Item"
}),
search.createColumn({
name: "duedate",
sort: search.Sort.DESC,
label: "Due Date/Receive By"
}),
search.createColumn({
name: "internalid",
label: "Internal ID"
})
]
});
var searchResultCount = purchaseorderSearchObj.runPaged().count;
var etaDate;
purchaseorderSearchObj.run().each(function(result) {
// .run().each has a limit of 4,000 results
etaDate = result.getValue({
name: "duedate",
sort: search.Sort.DESC,
label: "Due Date/Receive By"
})
return true;
});
return etaDate;
log.debug('etaDate', etaDate)
}
function setfieldValues(searchResult, order) {
for (var i = 0; i < searchResult.length; i++) {
order.setSublistValue({
id: 'custpage_itemname',
line: i,
value: checkif(searchResult[i].itemname)
});
order.setSublistValue({
id: 'custpage_qty',
line: i,
value: checkif(searchResult[i].qtybackordered)
});
order.setSublistValue({
id: 'custpage_itemeta',
line: i,
value: checkif(searchResult[i].etadate)
});
order.setSublistValue({
id: 'custpage_customername',
line: i,
value: checkif(searchResult[i].customername)
});
order.setSublistValue({
id: 'custpage_sonumber',
line: i,
value: checkif(searchResult[i].sonumber)
});
order.setSublistValue({
id: 'custpage_sostatus',
line: i,
value: checkif(searchResult[i].status)
});
order.setSublistValue({
id: 'custpage_poumber',
line: i,
value: checkif(searchResult[i].ponumber)
});
}
}
function createTableContent(searchResult) {
var tableContents = ""
for (var i = 0; i < searchResult.length; i++) {
tableContents += '<tr>' +
' <td>' + escape_for_xml(searchResult[i].itemname) + '</td>' +
' <td>' + escape_for_xml(searchResult[i].qtybackordered) + ' </td>' +
' <td>' + escape_for_xml(searchResult[i].sonumber) + '</td>' +
' <td>' + escape_for_xml(searchResult[i].etadate) + '</td>' +
' </tr>'
}
return tableContents;
}
return {
onRequest: onRequest
};
});
//nullcheck
function checkif(singleitem) {
if (singleitem == "" || singleitem == null || singleitem == undefined || singleitem == '- None -' || singleitem == " ") {
return "-";
} else {
return singleitem;
}
}
//Download PDF
function xmlContents(createtabele, custName,image) {
try {
var XML = '<?xml version="1.0"?><!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd">' +
'<pdf>' +
'<head>' +
' <link name="NotoSans" type="font" subtype="truetype" src="${nsfont.NotoSans_Regular}" src-bold="${nsfont.NotoSans_Bold}" src-italic="${nsfont.NotoSans_Italic}" src-bolditalic="${nsfont.NotoSans_BoldItalic}" bytes="2" />' +
' <#if .locale == "zh_CN">' +
' <link name="NotoSansCJKsc" type="font" subtype="opentype" src="${nsfont.NotoSansCJKsc_Regular}" src-bold="${nsfont.NotoSansCJKsc_Bold}" bytes="2" />' +
' <#elseif .locale == "zh_TW">' +
' <link name="NotoSansCJKtc" type="font" subtype="opentype" src="${nsfont.NotoSansCJKtc_Regular}" src-bold="${nsfont.NotoSansCJKtc_Bold}" bytes="2" />' +
' <#elseif .locale == "ja_JP">' +
' <link name="NotoSansCJKjp" type="font" subtype="opentype" src="${nsfont.NotoSansCJKjp_Regular}" src-bold="${nsfont.NotoSansCJKjp_Bold}" bytes="2" />' +
' <#elseif .locale == "ko_KR">' +
' <link name="NotoSansCJKkr" type="font" subtype="opentype" src="${nsfont.NotoSansCJKkr_Regular}" src-bold="${nsfont.NotoSansCJKkr_Bold}" bytes="2" />' +
' <#elseif .locale == "th_TH">' +
' <link name="NotoSansThai" type="font" subtype="opentype" src="${nsfont.NotoSansThai_Regular}" src-bold="${nsfont.NotoSansThai_Bold}" bytes="2" />' +
' </#if>' +
' <macrolist>' +
' <macro id="nlheader">' +
' <table width="100%"><tr><td></td><td align="right"><img src="'+image+'"/></td></tr></table>' +
' </macro>' +
' <macro id="nlfooter">' +
' <table style="width: 100%; font-size: 8pt;"><tr>' +
' <td align="right" style="padding: 0;"><pagenumber/> of <totalpages/></td>' +
' </tr></table>' +
' </macro>' +
' </macrolist>' +
' <style type="text/css">* {' +
' <#if .locale == "zh_CN">' +
' font-family: NotoSans, NotoSansCJKsc, sans-serif;' +
' <#elseif .locale == "zh_TW">' +
' font-family: NotoSans, NotoSansCJKtc, sans-serif;' +
' <#elseif .locale == "ja_JP">' +
' font-family: NotoSans, NotoSansCJKjp, sans-serif;' +
' <#elseif .locale == "ko_KR">' +
' font-family: NotoSans, NotoSansCJKkr, sans-serif;' +
' <#elseif .locale == "th_TH">' +
' font-family: NotoSans, NotoSansThai, sans-serif;' +
' <#else>' +
' font-family: NotoSans, sans-serif;' +
' </#if>' +
' }' +
' table {' +
' font-size: 9pt;' +
' table-layout: fixed;' +
' }' +
' th {' +
' font-weight: bold;' +
' font-size: 8pt;' +
' vertical-align: middle;' +
' padding: 5px 6px 3px;' +
' background-color: #e3e3e3;' +
' color: #333333;' +
' }' +
' td {' +
' padding: 4px 6px;' +
' }' +
' td p { align:left }' +
'</style>' +
'</head>' +
'<body header="nlheader" header-height="10%" footer="nlfooter" footer-height="20pt" padding="0.5in 0.5in 0.5in 0.5in" size="Letter">' +
' <p style="font-size: 10pt;"><i>Dear <b>' + custName + '</b>,</i></p>' +
' <p style="font-size: 10pt;"><i>Please find the details of your backordered items below</i> </p>' +
' <br/>' +
' <table width="100%"><tr>' +
' <th> Item Name</th>' +
' <th> QTY</th>' +
' <th>SO#</th>' +
' <th> ETA Date</th>' +
' </tr>' +
createtabele +
' </table>' +
'</body>' +
'</pdf>';
return XML;
} catch (err) {
log.debug('Error @ Function xmlContents', err)
}
}
//Fix text
function escape_for_xml(argument) {
try {
if (argument != "" && argument != null) {
argument = argument.replace(/&/g, '&');
argument = argument.replace(/</g, '<');
argument = argument.replace(/>/g, '>');
argument = argument.replace(/"/g, '"');
argument = argument.replace(/'/g, ''');
return argument;
} else {
return "";
}
} catch (e) {
log.debug({
title: e.name,
details: e.message
});
}
}Client Script
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CLIENTNAME:Taipan Canada
* TC-140
* Open Order Report
* **************************************************************************
* Date : 11-06-2019
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : This script is to define the button actions for the custom suitelet page Open Orders Report
* Date created : 11-06-2019
*
* REVISION HISTORY
*
* Revision 1.0 ${11-06-2019} nd :marg created
*
*****************************************************************************/
define(['N/record', 'N/search', 'N/ui/dialog', 'N/ui/message', 'N/url', 'N/https', 'N/currentRecord'],
function(record, search, dialog, message, url, https, currentRecord) {
function pageInit(scriptContext) {
}
function Applyfilters() {
try {
window.onbeforeunload = null;
var record = currentRecord.get();
var filterValue =true;
var customerid = record.getValue({
fieldId: 'custpage_customer'
});
if (!customerid) {
alert("please select a customer");
return;
}
get_url = url.resolveScript({
scriptId: "customscript_jj_tc_140_sl_open_order_rep",
deploymentId: "customdeploy_jj_tc_140_sl_open_order_rep",
params: {
customerid: customerid,
filterValue:filterValue
}
});
window.location.href = get_url;
} catch (er) {
console.log('error in validating the filters', er)
}
}
function DownloadPdf() {
try {
window.onbeforeunload = null;
var record = currentRecord.get();
var customerid = record.getValue({
fieldId: 'custpage_customer'
});
var custName = record.getText({
fieldId: 'custpage_customer'
});
if(!customerid){
alert("please select a customer");
return;
}else{
var download = true;
console.log('download', download)
get_url = url.resolveScript({
scriptId: "customscript_jj_tc_140_sl_open_order_rep",
deploymentId: "customdeploy_jj_tc_140_sl_open_order_rep",
params: {
download: download,
customerid:customerid,
custName:custName
}
});
window.open(get_url);
}
} catch (er) {
console.log('error in Download PDF Button action', er)
}
}
function sendEmail() {
try {
window.onbeforeunload = null;
var record = currentRecord.get();
var customerid = record.getValue({
fieldId: 'custpage_customer'
});
var custName = record.getText({
fieldId: 'custpage_customer'
});
if(!customerid){
alert("please select a customer");
return;
}else{
var send = true;
console.log('send', send)
get_url = url.resolveScript({
scriptId: "customscript_jj_tc_140_sl_open_order_rep",
deploymentId: "customdeploy_jj_tc_140_sl_open_order_rep",
params: {
send: send,
customerid:customerid,
custName:custName
}
});
window.location.href = get_url;
}
} catch (er) {
console.log('error in Send Email Button action', er)
}
}
return {
pageInit: pageInit,
fieldChanged: Applyfilters,
DownloadPdf: DownloadPdf,
sendEmail:sendEmail
};
});