Jira Code: PROT – 147
Description
Creates a UI for filtering the outstanding invoices and sends an email alert for the customers.
Suitescript
@NApiVersion 2.x
@NScriptType Suitelet
@NModuleScope SameAccount
**/
define(['N/task', "N/search", "N/ui/serverWidget","SuiteScripts/Jobin and Jismi/PROT-147/main.js","N/render","N/file","N/encode"],function (task, search, serverWidget,main,render,file,encode) {
var S = {
title:"Account Reminder",
type: "transaction",
filters: [
["mainline", "is", "T"],
"AND",["type", "anyof", "CustInvc"],
"AND",["shipping", "is", "F"],
"AND",["taxline", "is", "F"],
"AND",["status","anyof","CustInvc:A"],
"AND",["department","anyof",[1,2]]
// , "AND",["customer.email","isnotempty",""]
],
columns: [
search.createColumn({name: "daysoverdue", label: "Days Past due"}),
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "transactionnumber", label: "Invoice #"}),
search.createColumn({name: "amount", label:"Amount"}),
search.createColumn({name: "formulatext",formula:"{customer.internalid}",label: "Customer internalid"}),
search.createColumn({name: "formulatext",formula:"{customer.entityid}",label: "Customer ID"}),
search.createColumn({name: "formulatext",formula:"{customer.companyname}",label: "Customer"}),
search.createColumn({name: "department",label: "Department ID"}),
search.createColumn({name: "formulatext,",label: "Department",formula:"{department}"}),
search.createColumn({name: "formulatext,",label: "E-MAIL",formula:"{customer.email}"})
]
};
var downloadMode;
var fixAmount = function (r) {return parseFloat(r).toFixed(0);};
var currency = function (r) {
return r.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
};
var decimal=function(r){return r?parseFloat(r.split("%")[0]).toFixed(1)+"%":undefined;}
var alignCenter=function(r){
return ""+(r?r:"-")+"";
}
var noEmail=function(r){
return r?alignCenter(r):"-NONE-"
} var style={
"Internal ID" :"HIDDEN",
"Days Past due" :alignCenter,
"Invoice #" :alignCenter,
"Amount" :alignCenter,
"Customer ID" :alignCenter,
"Customer" :alignCenter,
"Department" :alignCenter,
"E-MAIL" :noEmail,
"Customer internalid" :"HIDDEN"
}; var NUMBER = function(r) {
return r?'' + parseFloat(r).toFixed(0) + '':'';
};
var PERCENT = function(r) {
return r?''+r+'':'N/A';
};
var STRING = function(r){
return (r||r==0)?'' + r + '':'';
};
var DATETIME = function(r) {
return r?'' + new Date(r).toISOString() + '':'';
}; var doNothing=function(r){return "";} var formatting={ "Days Past due" :STRING, "Internal ID" :STRING, "Invoice #" :STRING, "Amount" :STRING, "Customer internalid" :STRING, "Customer ID" :STRING, "Customer" :STRING, "Department" :STRING, "E-MAIL" :STRING }; function getResults() {var results = []; var pageData = search.create(S).runPaged({pageSize:1000}); var page = pageData.pageRanges; for (var k = 0; k < page.length; k++) { var data = pageData.fetch({index:k}).data; for (var j = 0; j < data.length; j++){ var R = data[j]; var obj = {}; for (var i = 0; i < S.columns.length; i++) obj[S.columns[i].label] = R.getValue(S.columns[i]); results.push(createLine(obj)); } } return results;} function createLine(obj){ if(!downloadMode){ obj["Invoice #"]=obj["Invoice #"]?"<a href='/app/accounting/transactions/custinvc.nl?id="+obj["Internal ID"]+"&whence='>"+obj["Invoice #"]+"</a>":"-"; obj["Customer"]=obj["Invoice #"]?"<a href='/app/common/entity/custjob.nl?id="+obj["Customer internalid"]+"'>"+obj["Customer"]+"</a>":"-"; } return obj; } function createForm(params){ try { var form = serverWidget.createForm({ title: S.title }); form.clientScriptFileId = main.files.clReportPage; form.addButton({ id: 'filter', label: 'Search', functionName: 'runFilter', align : serverWidget.LayoutJustification.RIGHT }); form.addSubmitButton({id:'buttonid',label:'Send Reminder'}); var days = form.addField({ id: 'days', type: serverWidget.FieldType.INTEGER, label: 'Number of days past due' }); if(params.days)days.defaultValue=params.days; var sublist = form.addSublist({ id: 'results', type: serverWidget.SublistType.LIST, label: 'Open Invoices' }); var selections=sublist.addField({ id: "selections", type: serverWidget.FieldType.CHECKBOX, label: '<p align="center">Select</p>', align: serverWidget.LayoutJustification.CENTER }); sublist.addMarkAllButtons(); form.addButton({ id: 'download', label: 'Download', functionName: 'download', align : serverWidget.LayoutJustification.RIGHT }); var R=getResults(); var i=0; for(var col in style) { var sublistField=sublist.addField({ id: "custpage_"+i, type: serverWidget.FieldType.TEXTAREA, label: '<p align="center">'+col+'</p>', align: serverWidget.LayoutJustification.CENTER }); if(style[col]=="HIDDEN") sublistField.updateDisplayType({displayType : serverWidget.FieldDisplayType.HIDDEN}); i++; } // for(var col in style) if(style[col]=="HIDDEN")delete style[col]; for(var j=0;j<R.length;j++){ i=0; for(var col in style) { var value=style[col]!="HIDDEN"?style[col](R[j][col]):R[j][col] if(value) sublist.setSublistValue({ id: "custpage_"+i, line: j, value:value }); i++; } } return form; }catch(err) { log.debug("Error@createForm",err); }} function createExcelFile(params) { var renderer = render.create(); var pages=""; var XML=file.load({ id:main.files.template}).getContents(); var singleSheet =XML.split("<!--SINGLE_SHEET-->")[1]; var RES={}; RES["Sheet1"]=getResults(params); for(var col in formatting) if(formatting[col]==doNothing)delete formatting[col]; for(var sheet in RES) { var contents=singleSheet.replace("Sheet1",sheet); var TABLE = '<Table ss:ExpandedColumnCount="100" ss:ExpandedRowCount="1000000" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15"><Row ss:AutoFitHeight="0"><!--LABEL_ROW--></Row><!--DATA_ROW--></Table>'; //set Column count and row count TABLE.replace("<!--COLUMN_COUNT-->", 11); TABLE.replace("<!--RESULT_COUNT-->", RES[sheet].length + 1); //Create Headers var LABEL_CELL = '<Cell><Data ss:Type="String"><!--LABEL--></Data></Cell>'; var LABEL_ROW = ""; for (var column in formatting)if(formatting[column]!=doNothing)LABEL_ROW += LABEL_CELL.replace("<!--LABEL-->",column);TABLE = TABLE.replace("<!--LABEL_ROW-->", LABEL_ROW); var DATA_ROW = ""; //create data rows var DATA_CELL = ''; for(var k=0;k<RES[sheet].length;k++) { DATA_ROW += '<Row ss:AutoFitHeight="0">'; //create each cell for (var column in formatting) { if(formatting[column]!=doNothing) { var typeVal = formatting[column](RES[sheet][k][column]); DATA_ROW += typeVal; } } DATA_ROW += '</Row>'; } TABLE = TABLE.replace("<!--DATA_ROW-->", DATA_ROW); pages+=contents.replace("<!--TABLE-->",TABLE); } renderer.templateContent = XML.replace(singleSheet,pages); contents = encode.convert({ string: renderer.renderAsString(), inputEncoding: encode.Encoding.UTF_8, outputEncoding: encode.Encoding.BASE_64 }); return file.create({ name: S.title+".xls", fileType: file.Type.EXCEL, contents: contents }); } function onRequest(context) { try { if(context.request.method=="POST") { var lineCount=context.request.getLineCount({group: 'results'}); var recIds=[]; for(var i=0;i<lineCount;i++) { context.request.getSublistValue({ group: 'results', name: 'selections', line: i })=="T"? recIds.push(context.request.getSublistValue({ group: 'results', name: 'custpage_0', line: i })):""; } var arrayFile=file.load({id:main.files.invoiceIds}) var idArray=JSON.parse(arrayFile.getContents()); idArray=idArray.concat(recIds); file.create({ name: arrayFile.name, fileType:arrayFile.fileType, contents: JSON.stringify(idArray), folder:arrayFile.folder }).save(); try{var mrTask = task.create(main.tasks.sendMail).submit();</code></pre>}catch(err){log.debug("Already Running")} log.debug("mrTask",mrTask);context.response.write("<html><head><body><script>alert('Email Sending process started will be completed soon');window.location.href='"+main.scripts.reportPage+"';</script></body></head></html>"); return; } S.filters.push("AND",["daysoverdue","greaterthan",context.request.parameters.days?context.request.parameters.days:0]); downloadMode=(context.request.parameters.download=="T"); if(downloadMode)context.response.writeFile(createExcelFile(context.request.parameters)); else context.response.writePage(createForm(context.request.parameters)); } catch (err) { log.debug("Error@onRequest", err); }} return { onRequest: onRequest } });
ClientScript
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(['N/currentRecord',"SuiteScripts/Jobin and Jismi/PROT-147/main.js"],function(currentRecord,main){
function runFilter()
{
var c = currentRecord.get();
window.location.href=main.scripts.reportPage+"&days="+c.getValue("days");
}
function download()
{
window.open(window.location.href+"&download=T");
}
return{
runFilter: runFilter,
download:download,
saveRecord:function(c){
var lineNumber = c.currentRecord.findSublistLineWithValue({
sublistId: 'results',
fieldId: 'selections',
value: "T"
});
if(lineNumber>-1)return true;
else {alert("Choose an invoice"); return false;}
}
}
})
Map Reduce
/**
* @NApiVersion 2.x
* @NScriptType MapReduceScript
* @NModuleScope SameAccount
*
*/
define(['N/record',"N/file","N/render","N/email","SuiteScripts/Jobin and Jismi/PROT-147/main.js","N/search","N/runtime"],function(record,file,render,email,main,search,runtime){
function sendReminder(C)
{
try{
var invoiceId=C.key
var invoice=record.load({type:"invoice",id:invoiceId});
var customerId=invoice.getValue("entity");
var customer=record.load({type:"customer",id:customerId});
var emailId=search.lookupFields({
type: search.Type.CUSTOMER,
id: customerId,
columns: ['email']
}).email;
if(emailId){
var renderer=render.create();
renderer.templateContent =file.load({ id: main.files.emailTemplate}).getContents();
renderer.addRecord({
templateName: 'record',
record:invoice
});
renderer.addRecord({
templateName: 'customer',
record:customer
});
var body = renderer.renderAsString();
var transactionFile = render.transaction({
entityId: parseInt(invoiceId),
printMode: render.PrintMode.PDF,
inCustLocale: true
});
email.send({
author : -5,
recipients:[emailId] ,
subject : "Account Reminder",
body : body,
attachments: [transactionFile]
});
}
else log.debug("NO_EMAIL-ID","NO EMAIL ID FOR CUSTOMER :"+customerId+" INV :"+C.key);
}catch(err)
{
log.debug("err@sendReminder@"+C.key,err);
}
}
return {
reduce: sendReminder,
map:function(C){C.write({key:C.value,value:C.value})},
getInputData:function(){
try{
var arrayFile=file.load({id:main.files.invoiceIds})
var idArray=JSON.parse(arrayFile.getContents());
file.create({
name: arrayFile.name,
fileType:arrayFile.fileType,
contents: "[]",
folder: arrayFile.folder
}).save();
return idArray
}
catch(err)
{
log.debug("err@getInputData",err)
}
},
summarize:function(context){
try{
var arrayFile=file.load({id:main.files.invoiceIds})
var idArray=JSON.parse(arrayFile.getContents());
if(idArray.length>0){
try{
var mrTask = task.create(main.tasks.sendMail).submit();
}catch(err){
log.debug("Already Running");
}
}
else{
var user=runtime.getCurrentUser();
// email.send({
// author:-5,
// recipients:user.email,
// subject:"Account reminders emails have been send",
// body:'Hi '+user.name+",<br> Account reminders emails have been send<br><br><br>Regards<br>Netsuite Administrator"
// });
log.debug(user.name,user.email);
log.debug("Task Completed",new Date().getTime());
}
}
catch(err)
{
log.debug("err@summarize",err)
}
}}
});
Email Template
<#if record.department=’PT’>
|
1340 MANHATTAN AVE. FULLERTON, CA 92831 T:714.441.0114 F:714.459.7477 PROTECSTYLE.COM |
Dear ${customer.companyname},
Our records indicated the attached invoice is now Past Due.
We would appreciate it if you could advise when we can expect payment
so that we may keep your records up to date and better manage any
orders that may be on hold for your account. Please contact us if you
should have any questions on this invoice.
We appreciate your business and thank you for your prompt attention on
this matter.
Best regards,
Jennifer West
Protec Accounting Dept
<#else>
|
1340 MANHATTAN AVE. FULLERTON, CA 92831 T:714.441.0114 F:714.459.7477 ILOVEBLVD.COM |
Dear ${customer.companyname},
Our records indicated the attached invoice is now Past Due.
We would appreciate it if you could advise when we can expect
payment so that we may keep your records up to date and better
manage any orders that may be on hold for your account. Please
contact us if you should have any questions on this invoice.
We appreciate your business and thank you for your prompt attention on this matter.
Best regards,
Jennifer West
Boulevard Accounting Dept
Excel Template
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Created>2006-09-16T00:00:00Z</Created>
<LastSaved>2018-07-14T07:17:02Z</LastSaved>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
<RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>8010</WindowHeight>
<WindowWidth>14805</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>105</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat ss:Format="#,##0"/>
<Protection/>
</Style>
<Style ss:ID="s__DATE"><NumberFormat ss:Format="Short Date"/></Style>
<Style ss:ID="percent"><NumberFormat ss:Format="0.0%"/></Style>
</Styles>
<!--SINGLE_SHEET-->
<Worksheet ss:Name="Sheet1">
<!--TABLE-->
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
<ActiveCol>5</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<!--SINGLE_SHEET-->
</Workbook>