/**
* @NApiVersion 2.1
* @NScriptType Portlet
*/
define([‘N/search’, ‘N/ui/serverWidget’],
function (search, serverWidget) {
const render = (params) => {
var portlet = params.portlet;
portlet.title = ‘Individual Sales Rep Ranking’;
var transactionSearchObj = search.create({
type: “transaction”,
settings: [{“name”: “consolidationtype”, “value”: “ACCTTYPE”}],
filters: [
[“type”, “anyof”, “CashSale”, “CustInvc”],
“AND”,
[“trandate”, “within”, “thismonth”],
“AND”,
[“mainline”, “is”, “T”]
],
columns: [
search.createColumn({
name: “salesrep”,
summary: “GROUP”,
label: “Sales Rep”
}),
search.createColumn({
name: “amount”,
summary: “SUM”,
label: “Amount”,
sort: search.Sort.DESC // Sort by amount column in descending order
})
]
});
var searchResult = transactionSearchObj.run().getRange({start: 0, end: 10});
var salesRepIds = [];
searchResult.forEach(function (result, index) {
var salesRepId = result.getValue({name: ‘salesrep’, summary: ‘GROUP’});
salesRepIds.push(salesRepId);
});
var customerSearchObj = search.create({
type: “customer”,
filters:
[
[“salesrep”,”anyof”, salesRepIds],
“AND”,
[“datecreated”,”within”,”thismonth”]
],
columns:
[
search.createColumn({
name: “entityid”,
join: “salesRep”,
summary: “GROUP”,
label: “Sales Rep”
}),
search.createColumn({
name: “entityid”,
summary: “COUNT”,
label: “Accounts”
}),
search.createColumn({
name: “amount”,
join: “transaction”,
summary: “SUM”,
label: “Amount”
}),
search.createColumn({
name: “amount”,
join: “transaction”,
summary: “SUM”,
label: “Amount $” // New column for amount$
})
]
});
var searchResultCount = customerSearchObj.runPaged().count;
var html = ‘<table style=”width:100%; border-collapse: collapse; font-size: 14px;”><tr><th style=”border: 1px solid #dddddd; text-align: left; padding: 8px;”>Rank</th><th style=”border: 1px solid #dddddd; text-align: left; padding: 8px;”>Sales Rep</th><th style=”border: 1px solid #dddddd; text-align: left; padding: 8px;”>Amount</th><th style=”border: 1px solid #dddddd; text-align: left; padding: 8px;”>Accounts</th><th style=”border: 1px solid #dddddd; text-align: left; padding: 8px;”>Amount $</th></tr>’;
searchResult.forEach(function (result, index) {
var rank = index + 1;
var salesRep = result.getText({name: ‘salesrep’, summary: ‘GROUP’});
var amount = result.getValue({name: ‘amount’, summary: ‘SUM’});
var accountsCount = ”;
var amountUSD = ”; // Initialize amount$ value
var rankCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px;”>’ + rank + ‘</td>’;
var salesRepCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: black;”>’ + salesRep + ‘</td>’;
var amountCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: black;”>’ + amount + ‘</td>’;
customerSearchObj.run().each(function(accountResult){
var accountSalesRep = accountResult.getValue({name: ‘entityid’, summary: ‘GROUP’, join: ‘salesRep’});
if(accountSalesRep === salesRep){
accountsCount = accountResult.getValue({name: ‘entityid’, summary: ‘COUNT’});
amountUSD = accountResult.getValue({name: ‘amount’, join: ‘transaction’, summary: ‘SUM’}); // Retrieve amount$ value
return false;
}
return true;
});
var accountsCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: black;”>’ + accountsCount + ‘</td>’;
var amountUSDCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: black;”>’ + amountUSD + ‘</td>’;
if (amount > 1000000) {
rankCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: green;”>’ + rank + ‘</td>’;
salesRepCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: green;”>’ + salesRep + ‘</td>’;
amountCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: green;”>’ + amount + ‘</td>’;
} else {
rankCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: red;”>’ + rank + ‘</td>’;
salesRepCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: red;”>’ + salesRep + ‘</td>’;
amountCell = ‘<td style=”border: 1px solid #dddddd; text-align: left; padding: 8px; color: red;”>’ + amount + ‘</td>’;
}
html += ‘<tr>’ + rankCell + salesRepCell + amountCell + accountsCell + amountUSDCell + ‘</tr>’;
});
html += ‘</table>’;
var htmlField = portlet.addField({
id: ‘custpage_html_chart’,
type: serverWidget.FieldType.INLINEHTML,
label: ‘Individual Sales Rep Report’
});
htmlField.defaultValue = html;
};
return {render};
});