Jira Code: WSDIS-2
The following is a
For every customer, there is a link in the saved search. On its click, a PDF opens up in the new tab and shows the customer purchase details of items in this year and last year. Script also compares the price levels of products, calculate the increase or decrease in the price.
WSDIS-2 JJ SL Customer Statement Suitelet
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/*******************************************************************************
* WSDisplay
* WSDIS-2
******************************************************************************
* Date:11-03-2019
* Author: Jobin & Jismi IT Services LLP
* Script Description:
* Date created : 11-03-2019
******************************************************************************/
define(['N/ui/serverWidget', 'N/search', 'N/file', 'N/render', 'N/record', 'N/config'],
function(serverWidget, search, file, render, record, config) {
/**
* 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) {
if (context.request.method == 'GET') {
try {
var customerID = context.request.parameters['IntenalId'];
if (customerID != '' && customerID != null && customerID != undefined) {
//******************************************************************************
//Search to find the top 10 products of 'this month this year'
var itemArr = [];
var mappedObj = {};
var items = [];
var searchThisMonthThisYearObj = search.create({
type: "invoice",
filters: [
["type", "anyof", "CustInvc"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["item.type", "anyof", "Assembly", "InvtPart"],
"AND",
["totalamount", "greaterthan", "0.00"],
"AND",
["trandate", "within", "thismonth"],
"AND",
["name", "anyof", customerID]
],
columns: [
search.createColumn({
name: "entity",
summary: "GROUP",
label: "Name"
}),
search.createColumn({
name: "item",
summary: "GROUP",
label: "Item"
}),
search.createColumn({
name: "amount",
summary: "SUM",
sort: search.Sort.DESC,
label: "Amount"
})
]
});
var searchThisMonthThisYear = searchThisMonthThisYearObj.run().getRange({
start: 0,
end: 10
});
for (var j = 0; j < searchThisMonthThisYear.length; j++) {
var item = searchThisMonthThisYear[j].getValue({
name: "item",
summary: "GROUP"
});
itemArr.push(item);
//ujna
var object = {};
object.product = searchThisMonthThisYear[j].getText({ name: "item", summary: "GROUP" });
object.thisyearamount = searchThisMonthThisYear[j].getValue({ name: "amount", summary: "SUM" })
items.push(object);
}
//******************************************************************************
//Search to find the top 10 products of 'this year till this date'
var searchYearToDateObj = search.create({
type: "invoice",
filters: [
["type", "anyof", "CustInvc"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["item.type", "anyof", "Assembly", "InvtPart"],
"AND",
["totalamount", "greaterthan", "0.00"],
"AND",
["trandate", "within", "thisyeartodate"],
"AND",
["name", "anyof", customerID],
],
columns: [
search.createColumn({
name: "entity",
summary: "GROUP",
label: "Name"
}),
search.createColumn({
name: "item",
summary: "GROUP",
label: "Item"
}),
search.createColumn({
name: "amount",
summary: "SUM",
sort: search.Sort.DESC,
label: "Amount"
})
]
});
var searchYearToDate = searchYearToDateObj.run().getRange({
start: 0,
end: 10
});
//otnas
for (var j = 0; j < searchYearToDate.length; j++) {
if (items.length == j) {
//push
var object = {};
object.ytdproduct = searchYearToDate[j].getText({ name: "item", summary: "GROUP" });
object.ytdamount = searchYearToDate[j].getValue({ name: "amount", summary: "SUM" });
items.push(object);
} else {
//merge
items[j].ytdproduct = searchYearToDate[j].getText({ name: "item", summary: "GROUP" });
items[j].ytdamount = searchYearToDate[j].getValue({ name: "amount", summary: "SUM" });
}
}
//******************************************************************************
//Search to find the amounts of the 'last year this month' for the top 10 products of 'this this year this month'
var searchfilter = [
["type", "anyof", "CustInvc"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["item.type", "anyof", "Assembly", "InvtPart"],
"AND",
["totalamount", "greaterthan", "0.00"],
"AND",
["trandate", "within", "samemonthlastfiscalyear"],
"AND",
["name", "anyof", customerID],
];
if (itemArr != '') {
searchfilter.push("AND",
["item", "anyof", itemArr]);
}
var searchThisMonthLastYearObj = search.create({
type: "invoice",
filters: searchfilter,
columns: [
search.createColumn({
name: "entity",
summary: "GROUP",
label: "Name"
}),
search.createColumn({
name: "item",
summary: "GROUP",
label: "Item"
}),
search.createColumn({
name: "amount",
summary: "SUM",
sort: search.Sort.DESC,
label: "Amount"
})
]
});
var searchThisMonthLastYear = searchThisMonthLastYearObj.run().getRange({
start: 0,
end: 10
});
for (var j = 0; j < searchThisMonthLastYear.length; j++) {
var item = searchThisMonthLastYear[j].getValue({
name: "item",
summary: "GROUP"
});
for (var k = 0; k < items.length; k++) {
if (items[k].product == searchThisMonthLastYear[j].getText({ name: "item", summary: "GROUP" })) {
items[k].lastyearamount = searchThisMonthLastYear[j].getValue({ name: "amount", summary: "SUM" });
items[k].decrease = items[k].thisyearamount - items[k].lastyearamount;
break;
}
}
}
//********************************************************************************
var custRec = record.load({
type: record.Type.CUSTOMER,
id: customerID
});
var customerName = custRec.getText({
fieldId: 'entityid'
})
var customerObject = { "customerName": customerName }
var myFile = render.create();
var template = 108;
myFile.setTemplateById(template);
//*********************************************************************
//loads a customer record and adds it into the xml
var companyInfo = config.load({
type: config.Type.COMPANY_INFORMATION
});
myFile.addRecord({
templateName: 'companyInformation',
record: companyInfo
});
myFile.addCustomDataSource({
format: render.DataSource.OBJECT,
alias: "customerObject",
data: customerObject
});
//*********************************************************************
//makes a object of sample data and pushes it into xml
myFile.addCustomDataSource({
format: render.DataSource.JSON,
alias: "items",
data: JSON.stringify({ items: items })
});
//************************************************************************
//render as PDF
var endUserForm = myFile.renderAsPdf();
context.response.writeFile(endUserForm, true);
}
} catch (er) {
log.debug('err@SL', er);
}
}
}
return {
onRequest: onRequest
};
});
WSDISPLAY XML Code
<?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>
<#assign aDateTime=.now>
<#assign aDate=aDateTime?date>
<#assign aTime=aDateTime?time>
<macrolist>
<macro id="nlheader">
<table class="header" style="width: 100%;">
<tr>
<td rowspan="2">
<img src="https://1030411-sb2.app.netsuite.com/core/media/media.nl?id=15&c=1030411_SB2&h=c4ee8e3c1cb926449849&expurl=T" /><span class="nameandaddress">${companyInformation.companyName}</span><br /><span class="nameandaddress">${companyInformation.addressText}</span>
</td>
<td align="right"><span class="title">Statement</span></td>
</tr>
<tr>
<td align="right">${aDate?iso_utc}</td>
</tr>
</table>
</macro>
<macro id="nlfooter">
<table class="footer" style="width: 100%;">
<tr>
<td align="right">
<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;
border-color: #FFF;
border-right: 2px;
}
td {
padding: 4px 6px;
}
td p {
align: left
}
b {
font-weight: bold;
color: #333333;
}
table.header td {
padding: 0;
font-size: 10pt;
}
table.footer td {
padding: 0;
font-size: 8pt;
}
table.itemtable th {
padding-bottom: 10px;
padding-top: 10px;
}
table.body td {
padding-top: 2px;
}
td.addressheader {
font-size: 8pt;
padding-top: 6px;
padding-bottom: 2px;
}
td.address {
padding-top: 0px;
}
span.title {
font-size: 28pt;
}
span.number {
font-size: 16pt;
}
hr {
border-top: 1px dashed #d3d3d3;
width: 100%;
color: #ffffff;
background-color: #ffffff;
height: 1px;
}
</style>
</head>
<body header="nlheader" header-height="10%" footer="nlfooter" footer-height="20pt" padding="0.5in 0.5in 0.5in 0.5in" size="Letter">
<table style="width: 100%;">
<tr>
<td align="right" class="addressheader" colspan="3"><b>Customer </b></td>
</tr>
<tr>
<td align="right" class="address" colspan="3">${customerObject.customerName}</td>
</tr>
</table>
<table border="0" style="width:100%; margin-top: 10px;">
<thead>
<tr>
<th align="right" scope="col">${customer.custentity_total_transactions@label}</th>
</tr>
</thead>
<tr>
<td align="right">${customer.custentity_total_transactions}</td>
</tr>
</table>
<table border="0" style="width:100%; margin-top: 10px;">
<thead>
<tr>
<th align="right" scope="col">${customer.custentity_total_sales@label}</th>
</tr>
</thead>
<tr>
<td align="right">${customer.custentity_total_sales}</td>
</tr>
</table>
<br /><strong>Selling Products/Sales Summary</strong>
<table font-size="8" border="" cellpadding="1" cellspacing="2" style="width:100%;">
<thead>
<tr>
<th colspan="8">
<p align="left">Last Month's Top 10 Selling Products</p>
</th>
<th colspan="4">Sales Amount</th>
<th colspan="8">2019 Top 10 Selling Products</th>
<th colspan="4">
<p align="left">2019 Sales Amount</p>
</th>
<th colspan="4">2018 Sales</th>
<th colspan="4">
<p align="left">Increase/Decrease</p>
</th>
</tr>
</thead>
<#list items.items as item>
<tr>
<td> </td>
</tr>
<tr>
<td colspan="8" style="padding-top:-10px;">${item.product}</td>
<td colspan="4" style="padding-top:-10px;" align="right">${item.thisyearamount}</td>
<td colspan="8" style="padding-top:-10px;">${item.ytdproduct}</td>
<td colspan="4" align="right" style="padding-top:-10px;">${item.ytdamount}</td>
<td colspan="4" align="right" style="padding-top:-10px;">${item.lastyearamount}</td>
<td colspan="4" align="right" style="padding-top:-10px;">${item.decrease}</td>
</tr>
<tr>
<td> </td>
</tr>
</#list>
</table>
<!--<table border="0" cellpadding="1" cellspacing="1" style="width:100%;">
<thead>
<tr>
<th scope="col">New Products</th>
</tr>
</thead>
<tr>
<td> </td>
</tr>
</table>
<table border="0" cellpadding="1" cellspacing="1" style="width:100%">
<thead>
<tr>
<th scope="col">Discontinued Products</th>
</tr>
</thead>
<tr>
<td> </td>
</tr>
</table>
<table class="body" style="width: 100%;">
<tr>
<th align="right">${customer.openingbalance@label}</th>
</tr>
<tr>
<td align="right">${customer.openingbalance@label}</td>
</tr>
</table>
<hr /><strong>Credit Line</strong>
<table style="width: 100%; margin-top: 10px;">
<tr>
<th>${record.aging1@label}</th>
<th>${record.aging2@label}</th>
<th>${record.aging3@label}</th>
<th>${record.aging4@label}</th>
<th>${record.aging5@label}</th>
<th>${record.agingbal@label}</th>
</tr>
<tr>
<td>${record.aging1}</td>
<td>${record.aging2}</td>
<td>${record.aging3}</td>
<td>${record.aging4}</td>
<td>${record.aging5}</td>
<td>${record.agingbal}</td>
</tr>
</table>
<br /><br /><strong>Survey</strong><br />
<table align="center" border="0" cellpadding="1" cellspacing="1" style="width:300px;">
<tr>
<td scope="col">Take January Survey</td>
</tr>
</table>-->
<!--<div style="text-align: center;"><img src="http://www.indiaspora.org/wp-content/uploads/2018/05/take-the-survey-button-1-230x108.png" style="width: 230px; height: 108px;" /></div>-->
</body>
</pdf>