Jira Code: Prof_24
Gross Profit script is a customization report, that shows the Gross Profit for every month. There is also a provision to calculate the commision percentage for the sales rep. Another feature for the script is that there is a provision to download the report in CSV.
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
define(['N/search', 'N/ui/serverWidget', 'N/url', 'N/runtime'],
function(search, serverWidget, url, runtime) {
/**
* 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) {
try {
var employeefilter = context.request.parameters.employeefilter;
form = serverWidget.createForm({
title: 'Gross Profit Report'
});
form.addFieldGroup({
id: 'fieldgroupfilters',
label: 'Filters'
});
var SELECT = form.addField({
id: 'employeefilter',
type: serverWidget.FieldType.SELECT,
label: 'Sales Rep Filter',
container: 'fieldgroupfilters'
});
SELECT.updateDisplaySize({
height: 60,
width: 220
});
SELECT = addemployeefilter(SELECT);
var data = form.addField({
id: 'csvextractfield',
type: serverWidget.FieldType.INLINEHTML,
label: 'Data',
container: 'fieldgroupfilters'
});
var extractcsv = form.addButton({
id: 'csvextract',
label: 'Download CSV',
functionName: 'extractcsv'
});
data.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
var newsub = form.addSublist({
id: 'sublistidprof',
type: serverWidget.SublistType.LIST,
label: 'Gross Profit Report'
});
newsub.addField({
id: 'recordurl',
type: serverWidget.FieldType.URL,
label: 'View'
}).linkText = "View";
newsub.addField({
id: 'recordurl1',
type: serverWidget.FieldType.TEXT,
label: 'Invoice'
});
newsub.addField({
id: 'customer',
type: serverWidget.FieldType.TEXT,
label: 'Customer'
});
newsub.addField({
id: 'salesrep',
type: serverWidget.FieldType.TEXT,
label: 'Sales Rep'
});
newsub.addField({
id: 'sonumber',
type: serverWidget.FieldType.TEXT,
label: 'SO Number'
});
newsub.addField({
id: 'trandate',
type: serverWidget.FieldType.TEXT,
label: 'Date'
});
newsub.addField({
id: 'statusref',
type: serverWidget.FieldType.TEXT,
label: 'Status'
});
newsub.addField({
id: 'paiddate',
type: serverWidget.FieldType.TEXT,
label: 'Paid Date'
});
newsub.addField({
id: 'amount',
type: serverWidget.FieldType.TEXT,
label: 'Amount'
});
newsub.addField({
id: 'cogs',
type: serverWidget.FieldType.TEXT,
label: 'COGS'
});
newsub.addField({
id: 'profit',
type: serverWidget.FieldType.TEXT,
label: 'Profit'
});
form.clientScriptFileId = '14700';
var userObj = runtime.getCurrentUser();
var role = userObj.role;
var empid = userObj.id;
var stockData = getRows(empid, role, employeefilter);
data.defaultValue = JSON.stringify(stockData);
// list.addRows({
// rows: getRows(empid, role)
// });
form.addPageLink({
title: 'Export as CSV',
type: serverWidget.FormPageLinkType.CROSSLINK,
url: downloadCSV(stockData)
});
var set = setsublist(stockData, newsub);
context.response.writePage(form);
} catch (e) {
log.debug({ details: e, title: "e i main " });
}
}
/*Add Employeess to filter dropdown list*/
function addemployeefilter(field) {
try {
var employeeSearchObj = search.create({
type: "employee",
filters: [
["salesrep", "is", "T"]
],
columns: [
search.createColumn({
name: "entityid",
sort: search.Sort.ASC
}),
"email",
"phone",
"internalid"
]
});
var employeesearch = {};
var searchResultCount = employeeSearchObj.runPaged().count;
field.addSelectOption({
value: " ",
text: "ALL"
});
employeeSearchObj.run().each(function(result) {
var entityid = result.getValue({
name: 'entityid'
});
var internalid = result.getValue({
name: 'internalid'
});
field.addSelectOption({
value: internalid,
text: entityid
});
return true;
});
return field;
} catch (e) {
log.debug({ title: "e", details: e });
}
}
/*Setting sublist on page init */
function setsublist(dataobj, records) {
try {
for (var i = 0; i < dataobj.length; i++) {
records.setSublistValue({
id: 'recordurl',
line: i,
value: createurl(dataobj[i].internalid)
});
records.setSublistValue({
id: 'recordurl1',
line: i,
value: checknull(dataobj[i].recordUrl)
});
records.setSublistValue({
id: 'customer',
line: i,
value: checknull(dataobj[i].customer)
});
records.setSublistValue({
id: 'salesrep',
line: i,
value: checknull(dataobj[i].salesrep)
});
records.setSublistValue({
id: 'sonumber',
line: i,
value: checknull(dataobj[i].sonumber)
});
records.setSublistValue({
id: 'trandate',
line: i,
value: checknull(dataobj[i].trandate)
});
records.setSublistValue({
id: 'statusref',
line: i,
value: checknull(dataobj[i].statusref)
});
records.setSublistValue({
id: 'paiddate',
line: i,
value: checknull(dataobj[i].paiddate)
});
records.setSublistValue({
id: 'amount',
line: i,
value: checknull(dataobj[i].amount)
});
records.setSublistValue({
id: 'cogs',
line: i,
value: checknull(dataobj[i].cogs)
});
records.setSublistValue({
id: 'profit',
line: i,
value: checknull(dataobj[i].profit)
});
}
} catch (e) {
log.debug({ details: e, title: "e in setsublist" })
}
}
function checknull(data) {
try {
if (data != null && data != "" && data != undefined) {
return data
} else {
return " "
}
} catch (e) {
log.debug({ details: e, title: "e in checknull" })
}
}
function createurl(id) {
try {
var outputurl = url.resolveRecord({
recordType: 'invoice',
recordId: id,
isEditMode: false
});
return outputurl
} catch (e) {
log.debug({ details: e, title: "e in createurl" })
}
}
function getBaseUrl() {
try {
return url.resolveRecord({
recordType: 'invoice'
});
} catch (e) {
log.debug({ details: e, title: "e in getBaseUrl" })
}
}
function getUrl() {
try {
var recordUrl = url.resolveRecord({
recordType: 'invoice',
recordId: 6,
isEditMode: false
});
var baseUrl = recordUrl.split('id');
var actualUrl = 'https://debugger.na2.netsuite.com' + baseUrl[0];
return actualUrl;
} catch (e) {
log.debug({ details: e, title: "e in getUrl" })
}
}
function getRows(empid, role, employeefilter) {
try {
var filter = [
["type", "anyof", "CustInvc", "SalesOrd", "ItemShip"],
"AND", ["mainline", "is", "T"],
// "AND", ["payingtransaction.type", "noneof", "CustCred"],
"AND", ["taxline", "is", "F"],
"AND", ["shipping", "is", "F"],
"AND", ["item.name", "isnot", "AR Opening Balance"],
"AND", ["createdfrom.type", "anyof", "SalesOrd"],
"AND", ["trandate", "before", "4/1/2018"],
"AND", ["payingTransaction.trandate", "within", "2/1/2018", "6/24/2018"],
];
if (employeefilter != "" && employeefilter != null && employeefilter != undefined) {
var salesrepfilter = ["salesrep", "anyof", employeefilter]
filter.push("AND", salesrepfilter);
}
if (empid == 528) {
var salesrepfilter = ["salesrep", "anyof", empid]
filter.push("AND", salesrepfilter);
} else if (empid == 8) {
var salesrepfilter = ["salesrep", "anyof", empid]
filter.push("AND", salesrepfilter);
}
log.debug("filter", filter);
var transactionSearchObj = search.create({
type: "transaction",
filters: filter,
columns: [
search.createColumn({
name: "type",
sort: search.Sort.ASC
}),
search.createColumn({
name: "altname",
join: "customer"
}),
"salesrep",
"tranid",
"trandate",
"statusref",
search.createColumn({
name: "trandate",
join: "payingTransaction"
}),
search.createColumn({
name: "formulanumeric",
formula: "{totalamount}-(NVL({taxtotal},0)+NVL({shippingamount},0))"
}),
"createdfrom",
"internalid"
]
});
var soArray = [];
var soObjArray = [];
var myPagedData = transactionSearchObj.runPaged();
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
/* var paidInvResults = transactionSearchObj.run().getRange({
start: 0,
end: 1000
});*/
// for (var i = 0; i < paidInvResults.length; i++) {
transactionSearchObj.run().each(function(paidInvResult) {
// .run().each has a limit of 4,000 results
// var paidInvResult = paidInvResults[i];
var oldIndex = soArray.indexOf(paidInvResult.getValue('createdfrom'));
if (oldIndex > -1) {
soObjArray[oldIndex].invoices = soObjArray[oldIndex].invoices + paidInvResult.getValue('tranid') + '-Date' + paidInvResult.getValue('trandate') + '-Paid Date' + paidInvResult.getValue({
name: "trandate",
join: "payingTransaction"
}) + '----';
} else {
soArray.push(paidInvResult.getValue('createdfrom'));
var soObj = {
recordUrl: paidInvResult.getValue('tranid'),
customer: paidInvResult.getValue({
name: "altname",
join: "customer"
}),
salesrep: paidInvResult.getText('salesrep'),
sonumber: paidInvResult.getText('createdfrom'),
trandate: paidInvResult.getValue('trandate'),
statusref: paidInvResult.getValue('statusref'),
paiddate: paidInvResult.getValue({
name: "trandate",
join: "payingTransaction"
}),
amount: paidInvResult.getValue({
name: "formulanumeric",
formula: "{totalamount}-(NVL({taxtotal},0)+NVL({shippingamount},0))"
}),
cogs: '',
profit: 0,
commisionpercent: 0,
commisionproduct: 0,
internalid: paidInvResult.id,
invoices: ""
};
soObjArray.push(soObj);
}
return true;
});
// }
var itemfulfillmentSearchObj = search.create({
type: "itemfulfillment",
filters: [
["type", "anyof", "ItemShip"],
"AND", ["createdfrom", "anyof", soArray],
"AND", ["accounttype", "anyof", "COGS"]
],
columns: [
search.createColumn({
name: "amount",
summary: "SUM"
}),
search.createColumn({
name: "createdfrom",
summary: "GROUP"
}),
search.createColumn({
name: "formulanumeric",
summary: "MIN",
formula: "{createdfrom.totalamount}-(NVL({createdfrom.taxtotal},0)+NVL({createdfrom.shippingamount},0))"
})
]
});
/* var ifInvResults = itemfulfillmentSearchObj.run().getRange({
start: 0,
end: 1000
});*/
itemfulfillmentSearchObj.run().each(function(ifInvResults) {
// .run().each has a limit of 4,000 results
// for (var j = 0; j < ifInvResults.length; j++) {
var soNumber = ifInvResults.getValue({
name: "createdfrom",
summary: "GROUP"
});
var cogsAmt = ifInvResults.getValue({
name: "amount",
summary: "SUM"
});
var totalAmt = ifInvResults.getValue({
name: "formulanumeric",
summary: "MIN",
formula: "{createdfrom.totalamount}-(NVL({createdfrom.taxtotal},0)+NVL({createdfrom.shippingamount},0))"
});
var indexOfSO = soArray.indexOf(soNumber);
if (soNumber == 6704) {
}
if (indexOfSO > -1) {
soObjArray[indexOfSO].cogs = cogsAmt;
soObjArray[indexOfSO].amount = totalAmt;
if (soObjArray[indexOfSO].amount == "" || soObjArray[indexOfSO].amount == null) {
soObjArray[indexOfSO].amount = 0;
}
if (cogsAmt == null || cogsAmt == '' || cogsAmt == undefined) {
soObjArray[indexOfSO].cogs = 0;
}
soObjArray[indexOfSO].profit = (parseFloat(soObjArray[indexOfSO].amount) - parseFloat(soObjArray[indexOfSO].cogs)).toFixed(2);
if (soObjArray[indexOfSO].salesrep == "Carol Brahney") {
soObjArray[indexOfSO].commisionpercent = 3.00 + "%";
soObjArray[indexOfSO].commisionproduct = " $" + (((parseFloat(soObjArray[indexOfSO].commisionpercent) / 100) * parseFloat(soObjArray[indexOfSO].profit).toFixed(2))).toFixed(2);
} else if (soObjArray[indexOfSO].salesrep == "Elena Aranda") {
soObjArray[indexOfSO].commisionpercent = 1.50 + "%";
soObjArray[indexOfSO].commisionproduct = " $" + (((parseFloat(soObjArray[indexOfSO].commisionpercent) / 100) * parseFloat(soObjArray[indexOfSO].profit).toFixed(2))).toFixed(2);
}
}
return true;
});
// }
return soObjArray;
} catch (e) {
log.debug({ title: "e in get rows", details: e })
}
}
function convertArrayOfObjectsToCSV(args) {
try {
var result, ctr, keys, columnDelimiter, lineDelimiter, data;
data = args.data || null;
if (data == null || !data.length) {
return null;
}
columnDelimiter = args.columnDelimiter || ',';
lineDelimiter = args.lineDelimiter || '\n';
keys = Object.keys(data[0]);
result = '';
result += keys.join(columnDelimiter);
result += lineDelimiter;
data.forEach(function(item) {
ctr = 0;
keys.forEach(function(key) {
if (ctr > 0) result += columnDelimiter;
result += item[key];
ctr++;
});
result += lineDelimiter;
});
return result;
} catch (e) {
log.debug({ details: e, title: "e in convertArrayOfObjectsToCSV" })
}
}
function downloadCSV(stockData) {
try {
log.debug({ details: stockData, title: "stockData" })
for (var i = 0; i < stockData.length; i++) {
delete stockData[i].commisionpercent;
delete stockData[i].commisionproduct;
}
var data, filename, link;
var csv = JSONToCSVConvertor(stockData, "Gross Profit Report", true);
// if (csv == null) return;
// filename = 'export.csv';
// if (!csv.match(/^data:text\/csv/i)) {
// csv = 'data:text/csv;charset=utf-8,' + csv;
// }
// data = encodeURI(csv);
return csv;
} catch (e) {
log.debug({ details: e, title: "e in downloadCSV" })
}
}
function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel) {
try {
//If JSONData is not an object then JSON.parse will parse the JSON string in an Object
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var CSV = '';
//Set Report title in first row or line
CSV += ReportTitle + '\r\n\n';
//This condition will generate the Label/Header
if (ShowLabel) {
var row = "";
//This loop will extract the label from 1st index of on array
for (var index in arrData[0]) {
//Now convert each value to string and comma-seprated
row += index + ',';
}
row = row.slice(0, -1);
//append Label row with line break
CSV += row + '\r\n';
}
//1st loop is to extract each row
for (var i = 0; i < arrData.length; i++) {
var row = "";
//2nd loop will extract each column and convert it in string comma-seprated
for (var index in arrData[i]) {
row += '"' + arrData[i][index] + '",';
}
row.slice(0, row.length - 1);
//add a line break after each row
CSV += row + '\r\n';
}
if (CSV == '') {
alert("Invalid data");
return;
}
//Generate a file name
var fileName = "MyReport_";
//this will remove the blank-spaces from the title and replace it with an underscore
fileName += ReportTitle.replace(/ /g, "_");
//Initialize file format you want csv or xls
var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);
return uri;
} catch (e) {
log.debug({ details: e, title: "e in JSONToCSVConvertor" })
}
}
return {
onRequest: onRequest
};
}); /**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(['N/http', 'N/https', 'N/record', 'N/search',
'N/url', 'N/runtime', 'N/format',
'N/ui/message', 'N/currentRecord'
],
function(http, https, record, search, url, runtime, format, message, currentRecord) {
/**
* Function to be executed after page is initialized.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.mode - The mode in which the record is being accessed (create, copy, or edit)
*
* @since 2015.2
*/
function pageInit(scriptContext) {
try {
var records = scriptContext.currentRecord;
var employeefilter = getParameterByName('employeefilter');
records.setValue({
fieldId: 'employeefilter',
value: employeefilter,
ignoreFieldChange: true
});
// jQuery("#fg_fieldgroupfilters").append('<span class="ns-icon ns-filters-onoff-button" tabindex="0" role="button" aria-expanded="false" aria-label="Expand/Collapse filters" aria-controls="uir_filters_body"></span>');
jQuery("#tr_fg_fieldgroupfilters").show();
jQuery("#fg_fieldgroupfilters").click(function() {
jQuery("#tr_fg_fieldgroupfilters").toggle();
});
} catch (e) {
console.log(e);
}
}
function fieldChanged(scriptContext) {
try {
var records = scriptContext.currentRecord;
if (scriptContext.fieldId == 'employeefilter') {
var value = records.getValue({
fieldId: 'employeefilter'
});
var urls = url.resolveScript({
scriptId: "customscript_prof_24_sl_gross_profit",
deploymentId: "customdeploy_prof_24_sl_gross_profit",
returnExternalUrl: false
});
window.location.href = urls + "&employeefilter=" + value;
}
} catch (e) {
console.log(e);
}
}
function extractcsv() {
var currentrecord = currentRecord.get();
var value = currentrecord.getValue({
fieldId: 'csvextractfield'
});
console.log(value);
JSONToCSVConvertor(value, "Gross Profit Report", true);
}
return {
pageInit: pageInit,
fieldChanged: fieldChanged,
extractcsv: extractcsv
};
function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel) {
//If JSONData is not an object then JSON.parse will parse the JSON string in an Object
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var CSV = '';
//Set Report title in first row or line
CSV += ReportTitle + '\r\n\n';
//This condition will generate the Label/Header
if (ShowLabel) {
var row = "";
//This loop will extract the label from 1st index of on array
for (var index in arrData[0]) {
//Now convert each value to string and comma-seprated
row += index + ',';
}
row = row.slice(0, -1);
//append Label row with line break
CSV += row + '\r\n';
}
//1st loop is to extract each row
for (var i = 0; i < arrData.length; i++) {
var row = "";
//2nd loop will extract each column and convert it in string comma-seprated
for (var index in arrData[i]) {
row += '"' + arrData[i][index] + '",';
}
row.slice(0, row.length - 1);
//add a line break after each row
CSV += row + '\r\n';
}
if (CSV == '') {
alert("Invalid data");
return;
}
//Generate a file name
var fileName = "MyReport_";
//this will remove the blank-spaces from the title and replace it with an underscore
fileName += ReportTitle.replace(/ /g, "_");
//Initialize file format you want csv or xls
var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);
console.log(uri)
// Now the little tricky part.
// you can use either>> window.open(uri);
// but this will not work in some browsers
// or you will not get the correct file extension
//this trick will generate a temp <a /> tag
var link = document.createElement("a");
link.href = uri;
//set the visibility hidden so it will not effect on your web-layout
link.style = "visibility:hidden";
link.download = fileName + ".csv";
//this part will append the anchor tag and remove it after automatic click
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
// for getting parameter by name .................
function getParameterByName(name, url) {
if (!url)
url = window.location.href;
name = name.replace(/[\[\]]/g, "\\$&");
var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
results = regex
.exec(url);
if (!results)
return null;
if (!results[2])
return ' ';
return decodeURIComponent(results[2].replace(/\+/g, " "));
}
});