Suitelet – show form for report
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
**/
define(['N/record', "N/search", "N/ui/serverWidget","SuiteScripts/Jobin and Jismi IT services LLP/NMS-182/lib/exchangeRate.js","SuiteScripts/Jobin and Jismi IT services LLP/NMS-182/lib/moment.js","N/render","N/file","N/encode","SuiteScripts/Jobin and Jismi IT services LLP/Maint File V4/lib/verticalList.js"],function (record, search, serverWidget,exchangeRate,moment,render,file,encode,verticalList) {
var REVENUE_START_DATE="(CASE WHEN (({status} NOT IN ('Closed - Won','Closed - Lost')) AND ({custcol_swe_contract_start_date} < {today}) AND ({expectedclosedate} < {today})) THEN TO_DATE(TO_CHAR({today},'mm/dd/yyyy'),'mm/dd/yyyy') ELSE (CASE WHEN (({status} NOT IN ('Closed - Won','Closed - Lost')) AND ({custcol_swe_contract_start_date} < {today}) AND ({expectedclosedate} >= {today})) THEN {expectedclosedate} ELSE {custcol_swe_contract_start_date} END) END)";
var DATE_DIFF=REVENUE_START_DATE+"-{custcol_swe_contract_start_date}";
var S = {
type: "opportunity",
filters: [["lineitem","noneof","@NONE@"]],
columns: [
search.createColumn({name: "formulatext",formula: "{custbody_jj_vertical}",label: "Vertical"}),
search.createColumn({name: "transactionnumber", label: "Opportunity Number"}),
search.createColumn({name: "internalid", label: "Opportunity ID"}),
search.createColumn({name: "entityid",join: "customer",label: "Customer Number"}),
search.createColumn({name: "item", label: "Line Item"}),
search.createColumn({name: "internalid",join: "customer",label: "Customer ID"}),
search.createColumn({name: "companyname",join: "customer",label: "Customer Name"}),
search.createColumn({name: "companyname",join: "CUSTBODY_END_USER",label: "End User Name"}),
search.createColumn({name: "formulatext",formula: "{item.name}",label: "Item"}),
search.createColumn({name: "formulatext",formula: "CASE WHEN {custcol_item_category} NOT IN ('License - Perpetual','Services - Perpetual', 'Other', 'Hardware', 'Training') THEN 'Renewing' ELSE 'Non-Renewing' END",label: "Item Type"}),
search.createColumn({name: "formulatext",formula: "{custcol_item_category}",label: "Item Category"}),
search.createColumn({name: "formulatext",formula: "{item.classnohierarchy}",label: "Class"}),
search.createColumn({name: "formulatext",formula: "{custbody_order_type}",label: "Order Type"}),
search.createColumn({name: "formulatext",formula: "{status}",label: "Status"}),
search.createColumn({name: "formulatext",formula: "{entitystatus}",label: "Quote/Opportunity Status"}),
search.createColumn({name: "currency", label: "CurrencyID"}),
search.createColumn({name: "projectedamount",label: "Opportunity Projected Total"}),
search.createColumn({name: "formulacurrency",formula: "{projectedamount}*{probability}",label: "Opportunity Weighted Total"}),
search.createColumn({name: "formulatext",formula: "{currency}",label: "Currency"}),
search.createColumn({name: "custbody_esw_oppo_base_amount", label: "Base Amount"}),
search.createColumn({name: "formulapercent",formula: "CASE WHEN ({custcol_item_category} NOT IN ('License - Perpetual','Services - Perpetual', 'Other', 'Hardware', 'Training') AND (NULLIF({custbody_esw_oppo_base_amount},0)!=0))THEN {custcol_list_rate}* 12 * {quantity} / NULLIF ({custbody_esw_oppo_base_amount},0) ELSE NULL END",label: "Item increase %"}),
search.createColumn({name: "formulacurrency",formula: "CASE WHEN {custcol_item_category} NOT IN ('License - Perpetual','Services - Perpetual', 'Other', 'Hardware', 'Training') THEN {custcol_list_rate}* 12 * {quantity} ELSE NULL END",label: "Item ARR"}),
search.createColumn({name: "formulacurrency",formula: "{rate} * {quantity}",label: "Total Item Amount"}),
search.createColumn({name: "probability", label: "Probability"}),
search.createColumn({name: "formulacurrency",formula: "CASE WHEN {custcol_item_category} NOT IN ('License - Perpetual','Services - Perpetual', 'Other', 'Hardware', 'Training') THEN {custcol_list_rate}* 12 * {probability} * {quantity} ELSE NULL END",label: "Item ARR Weighted Total"}),
search.createColumn({name: "custcol_swe_contract_start_date", label: "Contract Item Start Date"}),
search.createColumn({name: "formulanumeric",formula:DATE_DIFF,label: "dateDiff"}),
search.createColumn({name: "custcol_swe_contract_end_date", label: "Contract Item End Date"}),
search.createColumn({name: "custcol_swe_contract_item_term_months", label: "Contract Item Term"}),
search.createColumn({name: "formuladate",formula:REVENUE_START_DATE, label:"Revenue Date"}),
search.createColumn({name: "formulacurrency",formula: "CASE WHEN {custcol_item_category} NOT IN ('License - Perpetual','Services - Perpetual', 'Other', 'Hardware', 'Training') THEN {custcol_list_rate} * {quantity} * {probability} ELSE NULL END",label: "Monthly rate"})
]
};
var downloadMode;
var groupMembers=[];
var TODAY=moment();
var THIS_MONTH=TODAY.format("MMMM-YYYY");
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 style={
"Vertical" :"SHOW",
"Opportunity Number" :"SHOW",
"Customer Number" :"SHOW",
"Customer Name" :"SHOW",
"Customer ID" :"HIDDEN",
"End User Name" :"SHOW",
"Item" :"SHOW",
"Item Type" :"SHOW",
"Item Category" :"SHOW",
"Class" :"SHOW",
"Order Type" :"SHOW",
"Status" :"SHOW",
"Quote/Opportunity Status" :"SHOW",
"CurrencyID" :"HIDDEN",
"Currency" :"SHOW",
"Base Amount" :currency,
"Item increase %" :decimal,
"Item ARR" :currency,
"Total Item Amount" :currency,
"Probability" :"SHOW",
"Item ARR Weighted Total" :currency,
"Opportunity Projected Total" :currency,
"Opportunity Weighted Total" :currency,
"Contract Item Start Date" :"SHOW",
"Contract Item End Date" :"SHOW",
"Contract Item Term" :"SHOW",
"Opportunity ID" :"HIDDEN",
"Monthly rate" :"HIDDEN",
"Revenue Date" :"HIDDEN",
"dateDiff" :"HIDDEN"
};
var NUMBER_DEC_2=function(r){return r?'<Cell ss:StyleID="decim_2"><Data ss:Type="Number">' + parseFloat(r).toFixed(2) + '</Data></Cell>':'<Cell><Data ss:Type="String"></Data></Cell>';
}
var NUMBER = function(r) {
return r?'<Cell><Data ss:Type="Number">' + parseFloat(r).toFixed(0) + '</Data></Cell>':'<Cell><Data ss:Type="String"></Data></Cell>';
};
var PERCENT = function(r) {
return r?'<Cell ss:StyleID="percent"><Data ss:Type="Number">'+r+'</Data></Cell>':'<Cell><Data ss:Type="String">N/A</Data></Cell>';
};
var STRING = function(r){
return (r||r==0)?'<Cell><Data ss:Type="String">' + r + '</Data></Cell>':'<Cell><Data ss:Type="String"></Data></Cell>';
};
var DATETIME = function(r) {
return r?'<Cell ss:StyleID="s__DATE" ><Data ss:Type="DateTime">' + new Date(r).toISOString() + '</Data></Cell>':'<Cell><Data ss:Type="String"></Data></Cell>';
};
var doNothing=function(r){return "";}
var formatting={
"Vertical" :STRING,
"Opportunity Number" :STRING,
"Customer Number" :STRING,
"Customer Name" :STRING,
"End User Name" :STRING,
"Item" :STRING,
"Item Type" :STRING,
"Item Category" :STRING,
"Class" :STRING,
"Order Type" :STRING,
"Status" :STRING,
"Quote/Opportunity Status" :STRING,
"CurrencyID" :doNothing,
"Currency" :STRING,
"Base Amount" :NUMBER,
"Item increase %" :PERCENT,
"Item ARR" :NUMBER,
"Total Item Amount" :NUMBER,
"Probability" :PERCENT,
"Item ARR Weighted Total" :NUMBER,
"Opportunity Projected Total" :NUMBER,
"Opportunity Weighted Total" :NUMBER,
"Contract Item Start Date" :DATETIME,
"Contract Item End Date" :DATETIME,
"Contract Item Term" :NUMBER,
"Monthly rate" :doNothing,
"Revenue Date" :doNothing
};
var filterStartDate,filterEndDate;
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){
var exch=exchangeRate.exchangeRates[obj["CurrencyID"]];
var amounts=["Total Item Amount","Item ARR","Opportunity Weighted Total","Opportunity Projected Total","Item ARR Weighted Total","Base Amount"];
for(var i=0;i<amounts.length;i++)obj[amounts[i]]=(obj[amounts[i]]*exch).toFixed(0);
obj["Opportunity Number"]=(obj["Opportunity Number"]).replace("OPPRTNTY","");
if(!downloadMode){
obj["Customer Number"]="<a href='/app/common/entity/custjob.nl?id="+obj["Customer ID"]+"'>"+obj["Customer Number"]+"</a>";
obj["Opportunity Number"]="<a href='/app/accounting/transactions/opprtnty.nl?id="+obj["Opportunity ID"]+"'>"+obj["Opportunity Number"]+"</a>";
}
else{
obj["Customer Number"]="#"+obj["Customer Number"];
obj["Opportunity Number"]="#"+obj["Opportunity Number"];
obj["Item increase %"]=obj["Item increase %"]?(parseFloat(obj["Item increase %"].split("%")[0])/100):obj["Item increase %"];
obj["Probability"]=obj["Probability"]?(parseFloat((obj["Probability"].split("%")[0])/100)):obj["Probability"];
}
delete obj["Customer ID"];
delete obj["Opportunity ID"];
delete obj["CurrencyID"];
//obj["diff_StartExpectedDate"]=parseFloat(obj["Monthly rate"]*obj["diff_StartExpectedDate"]*(12/365)).toFixed(0);
var daysPassed=0
var currentEndDate=moment(obj["Contract Item End Date"],"MM/DD/YYYY");
var currentStartDate=moment(obj["Contract Item Start Date"],"MM/DD/YYYY");
var revenueStartDate=moment(obj["Revenue Date"],"MM/DD/YYYY");
if(revenueStartDate.isBetween(filterStartDate,filterEndDate)&&obj["Item Type"]!="Non-Renewing")
{
currentStartDate = moment(revenueStartDate.format("MM/DD/YYYY"),"MM/DD/YYYY");
daysPassed=obj["dateDiff"]?obj["dateDiff"]:0;
}
obj["dateDiff"]=daysPassed;
delete obj["Revenue Date"];
delete obj["dateDiff"];
if(filterStartDate.isAfter(currentStartDate))currentStartDate=moment(filterStartDate.format("MM/DD/YYYY"),"MM/DD/YYYY");
if(currentEndDate.isAfter(filterEndDate))currentEndDate=moment(filterEndDate.format("MM/DD/YYYY"),"MM/DD/YYYY");
if(obj["Item Type"]!="Non-Renewing")
{
while(currentStartDate.isBefore(currentEndDate)){
var lastDayInThisMonth=new Date(currentStartDate.format("YYYY"),currentStartDate.format("MM"),0).getDate();
var lastDateOfThisMonth_MM_DD_YYYY=currentStartDate.format("MM")+"/"+lastDayInThisMonth+"/"+currentStartDate.format("YYYY");
var thisMonthStartDate_MM_DD_YYYY=currentStartDate.format("MM")+"/01/"+currentStartDate.format("YYYY");
var thisMonthStart=moment(thisMonthStartDate_MM_DD_YYYY,"MM/DD/YYYY")
var thisMonthEnd=moment(lastDateOfThisMonth_MM_DD_YYYY,"MM/DD/YYYY");
var daysInCurrentMonth;
if(thisMonthEnd.isAfter(currentEndDate))thisMonthEnd=currentEndDate;
if(thisMonthStart.isBefore(currentStartDate))thisMonthStart=currentStartDate;
var daysInCurrentMonth= thisMonthEnd.diff(thisMonthStart,'days')+1;
var Month=currentStartDate.format("MMMM-YYYY");
obj[Month]=obj["Monthly rate"]?parseFloat(obj["Monthly rate"]*(daysInCurrentMonth+parseFloat(daysPassed))*(12/365)).toFixed(2):"-";
daysPassed=0;
currentStartDate=thisMonthStart.add(daysInCurrentMonth,'days');
}
return obj;
}else{
var dateInstance=currentStartDate;
var Month=dateInstance.format("MMMM-YYYY");
obj[Month]=obj["Total Item Amount"]?obj["Total Item Amount"]:"-";
obj["Contract Item Term"]="1";
return obj;
}
}
function createForm(params){
try {
var form = serverWidget.createForm({
title: 'FP&A - Opportunity & invoice report'
});
form.clientScriptFileId = "SuiteScripts/Jobin and Jismi IT services LLP/NMS-182/CL/SNI-182 CL FP&A - Opportunity & invoice report.js";
form.addButton({
id: 'filter',
label: 'Filter',
functionName: 'runFilter',
align : serverWidget.LayoutJustification.RIGHT
});
var vertical = form.addField({
id: 'vertical',
type: serverWidget.FieldType.MULTISELECT,
label: 'Vertical',
source: "customlist_gs_vertical",
});
var groupFilter = form.addField({
id: 'vertical_group',
type: serverWidget.FieldType.SELECT,
label: 'Group',
source:"customrecord_esw_verical_groups",
// container:"_groupfilter"
});
if(params.vertical_group){
groupFilter.defaultValue=params.vertical_group;
}
else if(params.vertical)vertical.defaultValue=params.vertical
var startMonth = form.addField({
id: 'start',
type: serverWidget.FieldType.SELECT,
label: 'Start Month',
});
var endMonth = form.addField({
id: 'end',
type: serverWidget.FieldType.SELECT,
label: 'End Month'
});
MONTHS=["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
var today=new Date();
var currentYear=today.getFullYear();
var currentMonth=MONTHS[today.getMonth()];
for(var j=currentYear-10;j<currentYear+10;j++)
for(var i=0;i<MONTHS.length;i++)
{
var monthCol=MONTHS[i]+"-"+j;
var option={text:monthCol,value:monthCol};
startMonth.addSelectOption(option);
endMonth.addSelectOption(option);
}
startMonth.defaultValue=currentMonth+"-"+currentYear;
endMonth.defaultValue=currentMonth+"-"+(currentYear+1);
if(params.start&¶ms.end){
startMonth.defaultValue=params.start;
endMonth.defaultValue=params.end;
var monthCol=moment(filterStartDate.format("MM/DD/YYYY"),"MM/DD/YYYY");
while(monthCol.isBefore(filterEndDate)){
style[monthCol.format("MMMM-YYYY")]=currency;
monthCol=monthCol.add(1,"months");
}
}
var sublist = form.addSublist({
id: 'results',
type: serverWidget.SublistType.LIST,
label: 'Results'
});
if((params.start==undefined||params.end==undefined)||params.vertical==undefined)return form;
else form.addButton({
id: 'download',
label: 'Download',
functionName: 'download',
align : serverWidget.LayoutJustification.RIGHT
});
var R=getResults();
for(var col in style) if(style[col]=="HIDDEN")delete style[col];
var i=0;
for(var col in style)
{
sublist.addField({
id: "custpage_"+i,
type: serverWidget.FieldType.TEXTAREA,
label: '<p align="center">'+col+'</p>',
align: serverWidget.LayoutJustification.CENTER
});
i++;
}
for(var j=0;j<R.length;j++){
i=0;
for(var col in style) {
sublist.setSublistValue({
id: "custpage_"+i,
line: j,
value:"<p align='center'>"+(R[j][col]?(style[col]=="SHOW"?R[j][col]:style[col](R[j][col])):"-")+"<p>"
});
i++;
}
}
return form;
}catch(err) {
log.debug("Error@createForm",err);
}
}
function createExcelFile(params)
{
var monthCol=moment(filterStartDate.format("MM/DD/YYYY"),"MM/DD/YYYY");
while(monthCol.isBefore(filterEndDate)){
formatting[monthCol.format("MMMM-YYYY")]=NUMBER_DEC_2;
monthCol=monthCol.add(1,"months");
}
var renderer = render.create();
var pages="";
var XML=file.load({ id: "SuiteScripts/Jobin and Jismi IT services LLP/NMS-182/template/templateExcel.xml"}).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: "excel.xls",
fileType: file.Type.EXCEL,
contents: contents
});
}
function onRequest(context) {
try {
filterStartDate=moment(context.request.parameters.start,'MMMM-YYYY');
filterEndDate=moment(context.request.parameters.end,'MMMM-YYYY').add(1,'months').add(-1,"days");
var startDate=filterStartDate.format("MM/DD/YYYY");
var endDate=filterEndDate.format("MM/DD/YYYY");
var timeFilter=[[[["custcol_swe_contract_end_date","within",startDate,endDate]],
"OR",["custcol_swe_contract_start_date","within",startDate,endDate]],"OR",
[["custcol_swe_contract_start_date","onorbefore",startDate],"AND",["custcol_swe_contract_end_date","onorafter",endDate]]];
S.filters.push("AND",timeFilter);
if(context.request.parameters.vertical_group){
var group=context.request.parameters.vertical_group?parseInt(context.request.parameters.vertical_group):"";
if(!isNaN(group)&&group)
{
var members=search.lookupFields({
type:"customrecord_esw_verical_groups",
id:group,
columns:["custrecord_sub_verticals"]
}).custrecord_sub_verticals;
// log.debug("members.value",members.value);
//if(members.value)groupMembers=groupMembers.push((""+members.value).spilt(","));
if(members.value)groupMembers=JSON.parse("["+members.value+"]");
else
for(var i=0;i<members.length;i++)
{
groupMembers.push(members[i].value)
}
}
}
if(groupMembers.length>0)
S.filters.push("AND", ["custbody_jj_vertical","anyof",verticalList.getVerticalIds(groupMembers)]);
if(context.request.parameters.vertical)S.filters.push("AND", ["custbody_jj_vertical","anyof",context.request.parameters.vertical.split(",")]);
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
}
});
Scheduled
/**
* @NApiVersion 2.x
@NScriptType ScheduledScript
@NModuleScope SameAccount
**/
define(["SuiteScripts/Jobin and Jismi IT services LLP/NMS-182/lib/exchangeRate.js"],function(exchangeRate){
return{
execute:exchangeRate.updateRates
}
})EXCEL SHEET TEMPLATE – render the excel file on the 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>
<Style ss:ID="decim_2">
<NumberFormat ss:Format="Standard"/>
</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>
Client script – client actions in report page
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(["N/record","N/search","N/url",'N/currentRecord',"SuiteScripts/Jobin and Jismi IT services LLP/NMS-182/lib/moment.js"],function(record,search,url,currentRecord,moment){
MONTHS=["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
function runFilter()
{
var c = currentRecord.get();
var start=c.getValue({fieldId:"start"});
var end=c.getValue({fieldId:"end"});
var vertical=c.getValue({fieldId:"vertical"});
var group=c.getValue({fieldId:"vertical_group"});
if(start){
var split=start.split("-");
var startDate=moment('01/'+(MONTHS.indexOf(split[0])+1)+'/'+split[1], 'DD/M/YYYY');
}else{
alert("Please fill Start date");
return;
}
if(end){
var split=end.split("-");
var endDate=moment('01/'+(MONTHS.indexOf(split[0])+1)+'/'+split[1], 'DD/M/YYYY').add(1, 'months');
}else{
alert("Please fill End date");
return;
}
if(endDate.isBefore(startDate)){
alert("End date is earlier than Start date");
return;
}
if(vertical=="" && group=="")
{
alert("Please select any Vertical/Group");
return;
}
var fields=["vertical","start","end","vertical_group"];
var params="";
for(var i=0;i<fields.length;i++)
params+="&"+fields[i]+"="+decodeURIComponent(c.getValue({fieldId:fields[i]}));
window.onbeforeunload="";
window.location.href=url.resolveScript({
scriptId: 'customscript_sni_182_fp_a_opp_inv_report',
deploymentId: 'customdeploy_sni_182_fp_a_opp_inv_report',
returnExternalUrl:window.location.href.indexOf("forms")>-1
})+params;
}
var LOCK_CHANGE=false;
return{
fieldChanged:function(context){
if(context.fieldId=="vertical_group"&&!LOCK_CHANGE)
{ LOCK_CHANGE=true;
context.currentRecord.setValue({fieldId:"vertical",value:""});
LOCK_CHANGE=false;
}
else if(context.fieldId=="vertical"&&!LOCK_CHANGE){
LOCK_CHANGE=true;
context.currentRecord.setValue({fieldId:"vertical_group",value:""});
LOCK_CHANGE=false;
}
},
runFilter:runFilter,
download:function(){
window.open(window.location.href+"&download=T");
}
}
})Custom Module for exchange rates – store the exchange rate for each currencies
define(['N/currency',"N/search","N/file"],function(currency,search,file){
var tempfileUrl="SuiteScripts/Jobin and Jismi IT services LLP/NMS-182/lib/exchangeRate.js";
var exchangeRates=/*s_e_p_a_r_a_t_o_r*/{"15":0.272242,"18":0.0280647,"6":0.72299,"2":1.30583,"19":0.268356,"3":0.761876,"12":0.152905,"4":1.1406,"10":0.127682,"22":0.270878,"14":0.0137014,"5":0.00883455,"24":0.0441478,"23":0.239866,"11":0.66718,"13":0.119564,"21":0.264606,"16":0.244612,"20":0.727617,"8":0.0702144,"9":0.110333,"7":0.995322,"17":0.18663,"1":1}/*s_e_p_a_r_a_t_o_r*/;
var S={
type:"currency",
columns:["internalid","symbol"]
};
function getMap(){
var map={};
var pageData = search.create(S).runPaged({ pageSize: 1000 });
var page = pageData.pageRanges;
var date=new Date();
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];
map[R.getValue(S.columns[0])]=currency.exchangeRate({
source: R.getValue(S.columns[1]),
target: 'USD',
date: date
});
}}
return map;
}
function updateRates()
{
var separator="/*s_e"+"_p_a_"+"r_a_"+"t_o_r*/";
var tempFile=file.load({id:tempfileUrl})
var contents=tempFile.getContents();
var splits=contents.split(separator);
var newContents=splits[0]+separator;
newContents+=JSON.stringify(getMap());
newContents+=separator+splits[2];
file.create({name:tempFile.name,contents:newContents,folder:tempFile.folder,fileType:tempFile.fileType}).save();
}
return {
get:getMap,
exchangeRates:exchangeRates,
updateRates:updateRates
}
})