AQ-276
The script is to create a custom suitelet page on the employee page similar to the Approve time page and in this we can list out the expense report to be approved for the project manager. But for viewing the expense report, we can show them the print out of the expense report which is shown for approval. The user has to click it and view it before approving it. The user can also do bulk approval and rejection from this custom page.
Filters are added in this page :
Filters:
- Employee : Select the employee using the employee filter that you want to approve.
- Date : For date there are 3 filters available,
– on or before
– within
– on or after - When you select ‘on or before’ or ‘on or after’ then a new date field will appear . You can enter a value in it.
- When you select ‘within’ then two date fields will appear which are ‘from’ and ‘to’. You can enter values in those fields to get the expected result.
/**
*@NApiVersion 2.x
*@NScriptType Suitelet
*/
/****************************************************************************
* AqualisBraemar |AQ-276 | Custom page in employee centre
* **************************************************************************
*
* Date: 23/04/2020
*
* Author: Jobin & Jismi IT Services LLP
*
*
* Description:
* Custom suitlet page on the employee center similar to the Approve time page
* for expense report.
*
* Revision 1.0 ${23-04-2020} Gloria : created
* Revision 1.1 ${13-05-2020} Gloria : Updated -- > reject button
* Revision 1.2 ${14-05-2020} Gloria : Updated -- > 2nd level approval when PM and supervisor are same person
******************************************************************************/
define(['N/ui/serverWidget', 'N/url', 'N/runtime', 'N/record', 'N/search', 'N/format','N/email'],
function (serverWidget, url, runtime, record, search, format,email) {
var dateFilterValue = ['onorafter', 'within', 'onorbefore'];
/*****************************************************************************************
Apply try and catch
*****************************************************************************************/
function applyTryCatch(DATA_OBJ, NAME) {
function tryCatch(myfunction, key) {
return function () {
try {
return myfunction.apply(this, arguments);
} catch (e) {
log.error("error in " + key, e);
return false;
}
};
}
for (var key in DATA_OBJ) {
if (typeof DATA_OBJ[key] === "function") {
DATA_OBJ[key] = tryCatch(DATA_OBJ[key], NAME + "." + key);
}
}
}
/*****************************************************************************************
To check whether a value exists in parameter
*****************************************************************************************/
function checkForParameter(parameter, parameterName) {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
} else {
if (parameterName)
log.debug('Empty Value found', 'Empty Value for parameter ' + parameterName);
return false;
}
}
/*****************************************************************************************
To assign a default value if the it is empty
*****************************************************************************************/
function assignDefaultValue(value, defaultValue) {
if (checkForParameter(value))
return value;
else
return defaultValue;
}
var formOperations = {// format the date
formatingDate: function (d) {
if (d) {
var date = format.format({
value: d,
type: format.Type.DATE
});
return date;
} else return false;
},
//set search filter according to the suitelet page filters
SearchFilter: function (context, employee_filter, date_filter, date_dateon, date_datefrom, date_dateto) {
var employeeFilter = context.request.parameters.employee ? context.request.parameters.employee : employee_filter;
var dateFilter = context.request.parameters.datefilter ? context.request.parameters.datefilter : date_filter;
var dateOn = context.request.parameters.dateon ? context.request.parameters.dateon : formOperations.formatingDate(date_dateon);
var datefrom = context.request.parameters.datefrom ? context.request.parameters.datefrom : formOperations.formatingDate(date_datefrom);
var dateto = context.request.parameters.dateto ? context.request.parameters.dateto : formOperations.formatingDate(date_dateto);
var filter = [
["type", "anyof", "ExpRept"],
"AND",
["custbody_status", "anyof", "1"],
"AND",
["custbody_normal_approve", "is", "F"],
"AND",
["mainline", "is", "T"]
];
if (employeeFilter && dateFilter) {
if ((dateFilter == 1 || dateFilter == 3) && dateOn && dateOn != 'undefined') {
var addedfilter = [
"AND",
["trandate", dateFilterValue[Number(dateFilter) - 1], dateOn],
"AND",
["employee", "anyof", employeeFilter]
];
filter = filter.concat(addedfilter);
log.debug("filter_de_on", filter);
return filter;
} else if (dateFilter == 2 && datefrom && datefrom != 'undefined' && dateto && dateto != 'undefined') {
var addedfilter = [
"AND",
["trandate", dateFilterValue[Number(dateFilter) - 1], datefrom, dateto],
"AND",
["employee", "anyof", employeeFilter]
];
filter = filter.concat(addedfilter);
log.debug("filter_de_ft", filter);
return filter;
} else {
var addedfilter = [
"AND",
["employee", "anyof", employeeFilter]
];
filter = filter.concat(addedfilter);
log.debug("filter_de_e", filter);
return filter;
}
} else if (employeeFilter) {
filter = [
["type", "anyof", "ExpRept"],
"AND",
["custbody_status", "anyof", "1"],
"AND",
["custbody_normal_approve", "is", "F"],
"AND",
["mainline", "is", "T"],
"AND",
["employee", "anyof", employeeFilter]
];
//log.debug("filter_e",filter.splice(2, 7));
log.debug("filter_e", filter);
return filter;
} else if (dateFilter) {
if ((dateFilter == 1 || dateFilter == 3) && dateOn && dateOn != 'undefined') {
var addedfilter = [
"AND",
["trandate", dateFilterValue[Number(dateFilter) - 1], dateOn],
];
filter = filter.concat(addedfilter);
log.debug("filter_d_on", filter);
return filter;
} else if (dateFilter == 2 && datefrom && datefrom != 'undefined' && dateto && dateto != 'undefined') {
var addedfilter = [
"AND",
["trandate", dateFilterValue[Number(dateFilter) - 1], datefrom, dateto],
];
filter = filter.concat(addedfilter);
log.debug("filter_d_ft", filter);
return filter;
} else {
filter = filter;
log.debug("filter_d", filter);
return filter;
}
} else {
return filter;//.splice(2, 5);
}
},
createForm: function (context, employee_filter, date_filter, date_dateon, date_datefrom, date_dateto) {// to ceate form with search result
var employeeFilter = context.request.parameters.employee ? context.request.parameters.employee : employee_filter;
var dateFilter = context.request.parameters.datefilter ? context.request.parameters.datefilter : date_filter;
var dateOn = context.request.parameters.dateon ? context.request.parameters.dateon : date_dateon;
var datefrom = context.request.parameters.datefrom ? context.request.parameters.datefrom : date_datefrom;
var dateto = context.request.parameters.dateto ? context.request.parameters.dateto : date_dateto;
var form = serverWidget.createForm({ title: "Expense Report Project Manager Approval" });
form.clientScriptFileId = 32249;
var filterGrid = form.addFieldGroup({ id: '_filter', label: 'Filters' });
var Employee_Filter = form.addField({
id: 'custpage_employee',
type: serverWidget.FieldType.SELECT,
source: 'employee',
label: 'Employee',
container: '_filter'
});
var Date_Filter = form.addField({
id: 'custpage_date_filter',
type: serverWidget.FieldType.SELECT,
label: 'Date Filter',
container: '_filter'
});
Date_Filter.addSelectOption({ value: '', text: '' });
Date_Filter.addSelectOption({ value: '1', text: 'on or after' });
Date_Filter.addSelectOption({ value: '2', text: 'within' });
Date_Filter.addSelectOption({ value: '3', text: 'on or before' });
if (dateFilter == 1 || dateFilter == 3) {
var Date_on = form.addField({
id: 'custpage_date',
type: serverWidget.FieldType.DATE,
label: 'Date',
container: '_filter'
});
form.updateDefaultValues({
custpage_date_filter: dateFilter
});
if (dateOn && dateOn != 'undefined') {
log.debug("dateOn", dateOn);
form.updateDefaultValues({
custpage_date: formOperations.formatingDate(dateOn)
});
}
} else if (dateFilter == 2) {
var Date_from = form.addField({
id: 'custpage_from',
type: serverWidget.FieldType.DATE,
label: 'From',
container: '_filter'
});
var Date_to = form.addField({
id: 'custpage_to',
type: serverWidget.FieldType.DATE,
label: 'To',
container: '_filter'
});
form.updateDefaultValues({
custpage_date_filter: dateFilter
});
if (datefrom && datefrom != 'undefined') {
form.updateDefaultValues({
custpage_from: formOperations.formatingDate(datefrom)
});
}
if (dateto && dateto != 'undefined') {
form.updateDefaultValues({
custpage_to: formOperations.formatingDate(dateto)
});
}
}
if (employeeFilter) {
form.updateDefaultValues({
custpage_employee: employeeFilter
});
}
var sublist = form.addSublist({ id: 'custpage_results', type: serverWidget.SublistType.LIST, label: 'Project manager approval' });
sublist.addMarkAllButtons();
var checkbox = sublist.addField({ id: 'custpage_select', label: 'Select', type: serverWidget.FieldType.CHECKBOX }); //Check box
var date = sublist.addField({ id: 'custpage_sub_date', label: 'Date', type: serverWidget.FieldType.TEXT }); // sublist DATE
var DocumentNO = sublist.addField({ id: 'custpage_docno', label: 'Document Number', type: serverWidget.FieldType.TEXT });
var employee = sublist.addField({ id: 'custpage_employ', label: 'Employee', type: serverWidget.FieldType.TEXT });
var memo = sublist.addField({ id: 'custpage_memo', label: 'Memo', type: serverWidget.FieldType.TEXT });
var totAmount = sublist.addField({ id: 'custpage_amount', label: 'Amount', type: serverWidget.FieldType.TEXT });
var project = sublist.addField({ id: 'custpage_project', label: 'Project', type: serverWidget.FieldType.TEXT });
var subsidiary = sublist.addField({ id: 'custpage_subsidiary', label: 'Subsidiary', type: serverWidget.FieldType.TEXT });
var link = sublist.addField({ id: 'custpage_internalid', label: 'Expense Report', type: serverWidget.FieldType.TEXTAREA });
var file = sublist.addField({ id: 'custpage_file', label: 'Attachments', type: serverWidget.FieldType.TEXTAREA });//ield.linkText = ‘NetSuite’;
var internalID = sublist.addField({ id: 'custpage_id', label: 'ID', type: serverWidget.FieldType.TEXT });
var user = sublist.addField({ id: 'custpage_user', label: 'user', type: serverWidget.FieldType.TEXT });
var empId = sublist.addField({ id: 'custpage_empid', label: 'Employee ID', type: serverWidget.FieldType.TEXT });
var memoField = sublist.addField({
id: 'custpage_addmemo',
type: serverWidget.FieldType.TEXT,
label: 'Add Memo'
});///app/site/hosting/scriptlet.nl?script=473&deploy=1
internalID.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
user.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
empId.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
var expenseReportSearch = dataSet.iterateSearchResult(formOperations.SearchFilter(context, employee_filter, date_filter, date_dateon, date_datefrom, date_dateto));
if (expenseReportSearch && Array.isArray(expenseReportSearch) && expenseReportSearch.length) {
log.debug("expenseReportSearch", expenseReportSearch);
var expenseReportObj = dataSet.groupResults(expenseReportSearch);
log.debug('expenseReportObj', expenseReportObj);
//return true;
log.debug('expenseReportObj keys count', Object.keys(expenseReportObj).length);
if (Object.keys(expenseReportObj).length) {
var n = 0;
for (var key_out in expenseReportObj) {
for (var key_in in expenseReportObj[key_out]) {
var eachResult = expenseReportObj[key_out]
if (key_in == 'custpage_internalid') {
var output = url.resolveScript({
scriptId: 'customscript_jj_sl_exp_rep_print_aq_276',
deploymentId: 'customdeploy_jj_sl_exp_rep_print_aq_276',
params: { internalid: eachResult[key_in] },
returnExternalUrl: false
});// rel="noreferrer"
//var htmllink = '<a href="' + output + ' target="_blank">' + eachResult[key_in] + '</a>';
var htmllink = '<a href="' + output + '" target="_blank" onClick="window.open(' + output + ',"pagename","resizable,height=600,width=400"); return false;">Preview</a>';/*eachResult[key_in]*/
sublist.setSublistValue({ id: key_in, line: n, value: htmllink });
sublist.setSublistValue({ id: 'custpage_id', line: n, value: eachResult[key_in] });
var outputurl="/app/site/hosting/scriptlet.nl?script=451&deploy=1&recordID="+eachResult[key_in];
var memoLink='<a href="' + outputurl + '" target="popup" onclick="window.open(' + outputurl + ',"","height=600,width=400"); return false;">Add memo</a>';
sublist.setSublistValue({ id: 'custpage_addmemo', line: n, value: memoLink });
} else if (key_in == 'custpage_file') {
var fileArr = eachResult[key_in];
if (fileArr.length) {
var html = '';
for (var i = 0; i < fileArr.length; i++) {
html += '<a href="' + fileArr[i].url + '">' + fileArr[i].name + '</a><br><br>'
}
//html='<a href="/app/site/hosting/scriptlet.nl?script=519&deploy=1 "> name1 </a><br><a href=" www.google.com "> name2 </a><br>'
log.debug("html", html);
sublist.setSublistValue({ id: key_in, line: n, value: assignDefaultValue(html, '-') });
}
} else {
sublist.setSublistValue({ id: key_in, line: n, value: assignDefaultValue(eachResult[key_in], '-') });
}
}
n++;
}
} else {
checkbox.updateDisplayType({ displayType: serverWidget.FieldDisplayType.DISABLED });
}
}
form.addSubmitButton({ label: 'Approve' });
form.addButton({
id: 'custpage_reject',
label: 'Reject',
functionName: 'reject'
});
form.addButton({
id: 'custpage_rejectwithnote',
label: 'Reject With Note',
functionName: 'rejectWithNote'
});
context.response.writePage(form);
}
}
applyTryCatch(formOperations, "formOperations");
var dataSet = {
groupResults: function (searchResults) {// to convert the search result into object format
return searchResults.reduce(function (accumulator, currentElement) {
var internalid = currentElement.getValue({ name: "internalid", label: "Internal ID" });
var ProjectManager = currentElement.getValue({ name: "custbody_project_manager", label: "Project Manager" });
var Fin_Admin = currentElement.getValue({ name: "custbody_cc_level1", label: "Financial Administrators Level 1" });
var line = currentElement.getValue({ name: "line", sort: search.Sort.ASC, label: "Line ID" });
var ProjectManagers = ProjectManager.split(",");
log.debug("ProjectManagers", ProjectManagers);
var Fin_Admins = Fin_Admin.split(",");
var userObj = runtime.getCurrentUser();
var center = userObj.roleCenter;
log.debug("center", center);
log.debug("supervisor_if",supervisor);
//to only fetch the expense report search result in which project manager is current user
//if ((role.indexOf('Employee Centre') > -1 && ProjectManagers.indexOf(String(userObj.id)) > -1) || Fin_Admins.indexOf(String(userObj.id)) > -1 || userObj.role == 3) {
if (center == 'EMPLOYEE' && ProjectManagers.indexOf(String(userObj.id)) > -1) {
if (!accumulator[internalid] && line == 0) {
var fieldLookUp = search.lookupFields({
type: search.Type.EMPLOYEE,
id: currentElement.getValue({ name: "internalid", join: "employee", label: "Internal ID" }),
columns: ['supervisor']
});
var supervisor = '';
if (fieldLookUp.supervisor.length) {
supervisor = fieldLookUp.supervisor[0].value;
}
log.debug("supervisor_if",supervisor);
accumulator[internalid] = {
custpage_sub_date: currentElement.getValue({ name: "trandate", label: "Date" }),
custpage_docno: currentElement.getValue({ name: "tranid", label: "Document Number" }),
custpage_employ: currentElement.getValue({ name: "entityid", join: "employee", label: "Name" }),
custpage_internalid: currentElement.getValue({ name: "internalid", sort: search.Sort.ASC, label: "Internal ID" }),
custpage_memo: currentElement.getValue({ name: "memomain", label: "Memo (Main)" }),
custpage_amount: currentElement.getValue({ name: "fxamount", label: "Amount (Foreign Currency)" }),
custpage_project: currentElement.getValue({ name: "entityid", join: "CUSTBODY_JJ_AQ81_PROJECT", label: "Name" }),
custpage_subsidiary: currentElement.getText({ name: "subsidiary", label: "Subsidiary" }),
custpage_user: '',
custpage_empid:currentElement.getValue({ name: "internalid", join: "employee", label: "Internal ID" }),
custpage_file: []
}
if (Number(userObj.id) == Number(supervisor)) {
log.debug("supervisor_in",supervisor);
accumulator[internalid].custpage_user = userObj.id;
}
}
var file = currentElement.getValue({ name: "isavailable", join: "file", label: "Available" });
log.debug("file", file);
if (file == true) {
accumulator[internalid].custpage_file.push({
name: currentElement.getValue({ name: "name", join: "file", label: "Name" }),
internalid: currentElement.getValue({ name: "internalid", join: "file", label: "Internal ID" }),
url: currentElement.getValue({ name: "url", join: "file", label: "URL" })
});
}
}
return accumulator;
}, {});
},
// search for the expense report and iterate the search result
iterateSearchResult: function (filter) {
var expensereportSearchObj = search.create({
type: "expensereport",
filters: filter,
columns:
[
search.createColumn({ name: "trandate", label: "Date" }),
search.createColumn({ name: "tranid", label: "Document Number" }),
search.createColumn({
name: "entityid",
join: "employee",
label: "Name"
}),
search.createColumn({ name: "memomain", label: "Memo (Main)" }),
search.createColumn({ name: "fxamount", label: "Amount (Foreign Currency)" }),
search.createColumn({
name: "internalid",
sort: search.Sort.ASC,
label: "Internal ID"
}),
search.createColumn({
name: "internalid",
join: "employee",
label: "Internal ID"
}),
// search.createColumn({
// name: "formulatext",
// formula: "{linefile.name}||'+'||{linefile.internalid}||'+'||{linefile.url}",
// label: "file"
// }),
search.createColumn({
name: "name",
join: "file",
label: "Name"
}),
search.createColumn({
name: "internalid",
join: "file",
label: "Internal ID"
}),
search.createColumn({
name: "url",
join: "file",
label: "URL"
}),
search.createColumn({
name: "isavailable",
join: "file",
label: "Available"
}),
search.createColumn({
name: "line",
sort: search.Sort.ASC,
label: "Line ID"
}),
search.createColumn({ name: "custbody_project_manager", label: "Project Manager" }),
search.createColumn({ name: "custbody_cc_level1", label: "Financial Administrators Level 1" }),
search.createColumn({
name: "entityid",
join: "CUSTBODY_JJ_AQ81_PROJECT",
label: "Name"
}),
search.createColumn({ name: "subsidiary", label: "Subsidiary" })
]
});
var searchResultCount = expensereportSearchObj.runPaged().count;
log.debug("expensereportSearchObj result count", searchResultCount);
var response = [];
expensereportSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
response.push(result);
return true;
});
return response;
}
}
applyTryCatch(dataSet, "dataSet");
var main = {
onRequest: function (context) {// entry point
//GET
if (context.request.method == 'GET') {
//Create the form
formOperations.createForm(context);
}
//POST
else {
var type=context.request.parameters.type
if (type == 'reject') {
var idObj = JSON.parse(context.request.body);
var idArray=Object.keys(idObj);
log.debug("idArray", Object.keys(idObj));
for (var j = 0; j < idArray.length; j++) {
var recordId = record.submitFields({
type: record.Type.EXPENSE_REPORT,
id: Number(idArray[j]),
values: {
custbody_status: 4,
complete : 'F'
},
options: {
enableSourcing: true,
ignoreMandatoryFields: true
}
});
log.debug("recordId_reject", recordId);
if(type == 'reject'){
var erArray=idObj[idArray[j]];
var url="/app/accounting/transactions/exprept.nl?id="+idArray[j];
email.send({
author: runtime.getCurrentUser().id,
recipients: Number(erArray[0]),
subject: 'Rejected Expense report- '+erArray[1],
body: "Hi"+erArray[0]+",<br>"+erArray[1]+" is rejected, please contact your project manager or account administrator for more information.<br><br>Thank you!<br>"+"<a href='"+url+"'><b>View Record</b></a>"
});
}
}
context.response.write("true");
} else {
main.approve(context);
}
}
},
approve: function (context) {
var employee = context.request.parameters.custpage_employee;
var datefilter = context.request.parameters.custpage_date_filter;
var dtaeon = context.request.parameters.custpage_date;
var datefrom = context.request.parameters.custpage_from;
var dateto = context.request.parameters.custpage_to;
var line = context.request.getLineCount('custpage_results');
log.debug("line", line);
//to iterate through each sublist line
for (var i = 0; i < Number(line); i++) {
var select = context.request.getSublistValue('custpage_results', 'custpage_select', i);
log.debug("select", select);
if (select == 'T') {
var id = context.request.getSublistValue('custpage_results', 'custpage_id', i);
var supervisor = context.request.getSublistValue('custpage_results', 'custpage_user', i);
//to approve ER selected
//set second level approved if supervisor and PM are same person
if (supervisor && supervisor != '-') {
log.debug("supervisor",supervisor);
var recordId = record.submitFields({
type: record.Type.EXPENSE_REPORT,
id: Number(id),
values: {
custbody_status: 3,
supervisorapproval:'T'
},
options: {
enableSourcing: true,
ignoreMandatoryFields: true
}
});
log.debug("recordId", recordId);
} else {
log.debug("supervisor_ord",supervisor);
//set first level approved for ordinary cases
var recordId = record.submitFields({
type: record.Type.EXPENSE_REPORT,
id: Number(id),
values: {
custbody_status: 2
},
options: {
enableSourcing: true,
ignoreMandatoryFields: true
}
});
log.debug("recordId", recordId);
}
}
}
//create the form by again running the search
formOperations.createForm(context, employee, datefilter, dtaeon, datefrom, dateto);
}
}
applyTryCatch(main, "main");
return main;
});