/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
var addLineCounterId = 0;
var objLine = {};
var CLIENT_SCRIPT_FILE_ID = 22087;
define(['N/file', 'N/https', 'N/log', 'N/ui/message', 'N/query', 'N/record', 'N/render', 'N/runtime', 'N/ui/serverWidget', 'N/search', 'N/format', 'N/url', 'N/redirect'], main);
function main(fileModule, httpsModule, logModule, messageModule, queryModule, recordModule, renderModule, runtimeModule, serverWidgetModule, searchModule, formatModule, urlModule, redirectModule) {
file = fileModule;
https = httpsModule;
log = logModule;
message = messageModule;
query = queryModule;
record = recordModule;
render = renderModule;
runtime = runtimeModule;
serverWidget = serverWidgetModule;
url = urlModule;
search = searchModule;
format = formatModule;
redirect = redirectModule;
return {
onRequest: function (context) {
try {
var domain = 'https://';
domain += url.resolveDomain({
hostType: url.HostType.FORM,
accountId: runtime.accountId
});
log.debug('domain', domain)
scriptURL = url.resolveScript({
scriptId: runtime.getCurrentScript().id,
deploymentId: runtime.getCurrentScript().deploymentId,
returnExternalUrl: false
}); //returnExternalUrl
log.debug('scriptURL', scriptURL);
var objScript = runtime.getCurrentScript();
backgroundImg = objScript.getParameter({
name: 'custscript_backgroundimg_p2p'
});
log.debug('backgroundImg', backgroundImg);
//scriptURL = 'https://5693072-sb2.extforms.netsuite.com/app/site/hosting/scriptlet.nl?script=2650&deploy=1&compid=5693072_SB2&h=c670f0d963a4fdf8638f'
if (context.request.method == 'POST') {
postRequestHandle(context);
} else {
getRequestHandle(context);
}
} catch (up) {
log.debug("Error Occurred:", up);
}
}
}
}
function getRequestHandle(context) {
var form = serverWidget.createForm({
title: 'Purchase Comparison 2023 Vs 2024 ',
hideNavBar: false
});
form.clientScriptFileId = CLIENT_SCRIPT_FILE_ID
var start_dt = context.request.parameters.Start_Date
var end_dt = context.request.parameters.End_Date
form.addSubmitButton({
label: "Download CSV"
})
var strt_field = form.addField({
id: 'custpage_strt_dt',
type: serverWidget.FieldType.DATE,
label: 'Start Date'
});
if (start_dt) {
strt_field.defaultValue = start_dt;
} else {
strt_field.defaultValue = "01/01/2024";
start_dt = "01/01/2024";
}
var end_field = form.addField({
id: 'custpage_end_dt',
type: serverWidget.FieldType.DATE,
label: 'End Date'
});
if (end_dt) {
end_field.defaultValue = end_dt;
} else {
end_field.defaultValue = new Date();
var date = new Date()
var day = date.getDate();
var month = date.getMonth() + 1; // Month starts from 0
var year = date.getFullYear();
// Pad day and month with leading zeros if needed
day = (day < 10) ? '0' + day : day;
month = (month < 10) ? '0' + month : month;
// Return the formatted date
end_dt = month + '/' + day + '/' + year;
}
strt_field.updateBreakType({
breakType: serverWidget.FieldBreakType.STARTCOL
});
var htmlField = form.addField({
id: 'custpage_field_html',
type: serverWidget.FieldType.INLINEHTML,
label: 'HTML'
});
htmlField.defaultValue = `
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
<!-- Select2 -->
<link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
<script src="/ui/jquery/jquery-3.5.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdf.js/1.10.100/pdf.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdf.js/2.6.347/pdf.worker.entry.min.js" ></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdf.js/1.10.100/pdf.worker.min.js"></script>
<script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>
<style type = "text/css">
input[type="text"], input[type="search"], textarea, button {
outline: none;
box-shadow:none !important;
border: 1px solid #ccc !important;
}
p, pre {
font-size: 10pt;
}
td, th {
font-size: 9pt;
border: 2px;
}
th {
font-weight: bold;
border: 1px ;
}
.loader {
border: 16px solid #f3f3f3;
border-radius: 50%;
border-top: 16px solid teal;
border-right: 16px solid white;
border-bottom: 16px solid teal;
width: 100px;
height: 100px;
margin-top:-50px;
-webkit-animation: spin 2s linear infinite;
animation: spin 2s linear infinite;
}
@-webkit-keyframes spin {
0% { -webkit-transform: rotate(0deg); }
100% { -webkit-transform: rotate(360deg); }
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}
.divScroll {
overflow:scroll;
height:100px;
width:200px;
}
body {margin:2em;}
.tableFixHead {
overflow-y: auto; /* make the table scrollable if height is more than 200 px */
height: 660px; /* gives an initial height of 200px to the table */
width: 1795px;
opacity: 1;
}
.tableFixHead th{
position: sticky; /* make the table heads sticky */
top: 0px; /* table head will be placed from the top of the table and sticks to it */
background: #ADD8E6
}
.table {
width: 100%;
border-collapse: collapse;
}
#wrapper {
min-height: 100%;
}
</style>
<div id='wrapper' class="tableFixHead">
<div id="divLoaderReq" class="w3-display-container" style="z-index:99999999;position:absolute;background-size:cover;background-color:#36202052;width:100%;height:480px;display:none;">
<div class="loader w3-display-middle" ></div>
</div>
<table>
<table id="backordersalesordertable" class="table table-striped table-bordered" cellspacing="0">
<thead>
<tr>
<th style="width:30%">Vendor NAME</th>
<th>Purchase 2023</th>
<th>Purchase 2024</th>
<th>Purchase 2023 VS 2024</th>
</tr>
</thead>
<tbody id="tbodyLineItemView" style="width:100%;overflow:auto;">
</tbody>
</table>
</div>
<script>
$(document).ready(function () {
var table = document.getElementById('backordersalesordertable');
var requestPayload = {
'function': 'GetPurchasecomparedata',
'startdate':'`+ start_dt + `',
'enddate':'`+ end_dt + `',
'type':'inline'
}
var xhr_P = new XMLHttpRequest();
xhr_P.open('POST', '${scriptURL}', true);
xhr_P.send(JSON.stringify(requestPayload));
xhr_P.onload = function () {
if (xhr_P.status === 200) {
var fileExistsResponsePayload_boSo = JSON.parse(xhr_P.response);
console.log("fileExistsResponsePayload_boSo", fileExistsResponsePayload_boSo);
var count = 1;
var i;
var data = fileExistsResponsePayload_boSo.purchasecomparedetails;
data.sort(function(a, b) {
var vendorA = a.vendor.trim().toUpperCase(); // ignore leading space and case
var vendorB = b.vendor.trim().toUpperCase(); // ignore leading space and case
if (vendorA < vendorB) {
return -1;
}
if (vendorA > vendorB) {
return 1;
}
return 0; // names must be equal
});
for (i in fileExistsResponsePayload_boSo.purchasecomparedetails) {
var newRow = table.insertRow(-1);
var newCell = newRow.insertCell(-1);
newCell.innerHTML = data[i].vendor
var newCell = newRow.insertCell(-1);
newCell.innerHTML = (Math.round(Number(data[i].prev_purchase)*100)/100).toFixed(2);
var newCell = newRow.insertCell(-1);
newCell.innerHTML = (Math.round(Number(data[i].curr_purchase)*100)/100).toFixed(2);
var newCell = newRow.insertCell(-1);
newCell.innerHTML = (Math.round((Number(data[i].curr_purchase) - Number(data[i].prev_purchase))*100)/100).toFixed(2);
count = count + 1;
}
}
}
})
</script>
`;
htmlField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.INLINE
});
context.response.writePage(form);
}
function postRequestHandle(context) {
log.debug("context.request", context.request.body);
var body = context.request.body;
// if (body.includes('submitter')) {
// log.debug("submitter")
// var End_date = context.request.parameters.custpage_enddate;
// var Start_date = context.request.parameters.custpage_startdate;
// log.debug('Start_date', Start_date)
// log.debug('End_date', End_date)
// var today = new Date(Start_date);
// log.debug("today", today)
// var yr = today.getFullYear();
// if (yr % 400 == 0 || (yr % 100 != 0 && yr % 4 == 0)) {
// if (today.getDate() == "29") {
// var prev_date = today.getDate() - 1;
// } else {
// var prev_date = today.getDate();
// }
// } else {
// var prev_date = today.getDate();
// }
// var prev_month = today.getMonth() + 1;
// var prev_year = today.getFullYear() - 1;
// if (prev_date < 10) prev_date = '0' + prev_date;
// if (prev_month < 10) prev_month = '0' + prev_month;
// var Start_Date = prev_month + '/' + prev_date + '/' + prev_year;
// var Enddate = new Date(End_date);
// var year = Enddate.getFullYear();
// if (year % 400 == 0 || (year % 100 != 0 && year % 4 == 0)) {
// if (Enddate.getDate() == "29") {
// var end_date = Enddate.getDate() - 1;
// } else {
// var end_date = Enddate.getDate();
// }
// } else {
// var end_date = Enddate.getDate();
// }
// // var end_date = Enddate.getDate();
// var end_month = Enddate.getMonth() + 1;
// var end_year = Enddate.getFullYear() - 1;
// if (end_date < 10) end_date = '0' + end_date;
// if (end_month < 10) end_month = '0' + end_month;
// var End_Date = end_month + '/' + end_date + '/' + end_year;
// var curr_End_date = new Date();
// var curr_End_date1 = curr_End_date.getDate();
// var curr_End_month = curr_End_date.getMonth() + 1;
// var curr_End_year = curr_End_date.getFullYear() - 1;
// var today_curr_year = curr_End_date.getFullYear();
// if (curr_End_date1 < 10) curr_End_date1 = '0' + curr_End_date1;
// if (curr_End_month < 10) curr_End_month = '0' + curr_End_month;
// var CURR_End_Date = curr_End_month + '/' + curr_End_date1 + '/' + curr_End_year;
// var Todays_date = curr_End_month + '/' + curr_End_date1 + '/' + today_curr_year;
// var resultSet = new Array();
// resultSet.push('Customer Name,Annual Sales Projection, SALES 2023, COST 2023 , PROFIT 2023 , SALES 2024 , COST 2024 , PROFIT 2024 ,SALES 2023 VS 2024 ,COST 2023 VS 2024,PROFIT 2023 VS 2024 ');
// // if (Start_date && End_date) {
// // var prev_sales = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_Date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_Date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {amount} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_Date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_Date + "', 'MM/DD/YYYY') THEN {creditamount} ELSE 0 END END";
// // var prev_cost = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_Date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_Date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {costestimate} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_Date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_Date + "', 'MM/DD/YYYY') THEN {debitamount} ELSE 0 END END";
// // var curr_sales = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {amount} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_date + "', 'MM/DD/YYYY') THEN {creditamount} ELSE 0 END END";
// // var curr_cost = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {costestimate} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_date + "', 'MM/DD/YYYY') THEN {debitamount} ELSE 0 END END";
// // } else {
// // var prev_sales = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2023', 'MM/DD/YYYY') AND TO_DATE('" + CURR_End_Date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {amount} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2023', 'MM/DD/YYYY') AND TO_DATE('" + CURR_End_Date + "', 'MM/DD/YYYY') THEN {creditamount} ELSE 0 END END";
// // var prev_cost = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2023', 'MM/DD/YYYY') AND TO_DATE('" + CURR_End_Date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {costestimate} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2023', 'MM/DD/YYYY') AND TO_DATE('" + CURR_End_Date + "', 'MM/DD/YYYY') THEN {debitamount} ELSE 0 END END";
// // var curr_sales = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2024', 'MM/DD/YYYY') AND TO_DATE('" + Todays_date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {amount} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2024', 'MM/DD/YYYY') AND TO_DATE('" + Todays_date + "', 'MM/DD/YYYY') THEN {creditamount} ELSE 0 END END";
// // var curr_cost = "CASE WHEN {type} = 'Invoice' THEN CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2024', 'MM/DD/YYYY') AND TO_DATE('" + Todays_date + "', 'MM/DD/YYYY') AND ({item.internalid} > 0 AND {amount} > 0) THEN {costestimate} ELSE 0 END ELSE CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2024', 'MM/DD/YYYY') AND TO_DATE('" + Todays_date + "', 'MM/DD/YYYY') THEN {debitamount} ELSE 0 END END";
// // }
// // log.debug("prev_sales", prev_sales)
// // log.debug("prev_cost", prev_cost)
// // log.debug("curr_sales", curr_sales)
// // log.debug("curr_cost", curr_cost)
// var transactionSearchObj = search.create({
// type: "transaction",
// filters:
// [
// ["type", "anyof", "Journal", "CustInvc"],
// "AND",
// ["formulatext: {name}", "isnotempty", "none"],
// "AND",
// ["mainline", "any", ""],
// "AND",
// ["formulatext: {customer.internalid}", "isnotempty", ""],
// "AND",
// ["formulatext: {name}", "isnot", "1 MISCELLANEOUS"]
// ],
// columns:
// [
// search.createColumn({
// name: "entity",
// summary: "GROUP",
// label: "Name"
// }),
// search.createColumn({
// name: "companyname",
// sort: search.Sort.ASC,
// join: "customer",
// summary: "GROUP",
// label: "Company Name"
// }),
// search.createColumn({
// name: "formulanumeric1",
// summary: "SUM",
// formula: prev_sales,
// label: "Formula (Numeric)"
// }),
// search.createColumn({
// name: "formulanumeric2",
// summary: "SUM",
// formula: prev_cost,
// label: "Formula (Numeric)"
// }),
// search.createColumn({
// name: "formulanumeric3",
// summary: "SUM",
// formula: curr_sales,
// label: "Formula (Numeric)"
// }),
// search.createColumn({
// name: "formulanumeric4",
// summary: "SUM",
// formula: curr_cost,
// label: "Formula (Numeric)"
// }),
// search.createColumn({
// name: "custentity_annualsalesprojection",
// join: "customer",
// summary: "GROUP",
// label: "Annual Sales Projection"
// })
// ]
// });
// var searchResultCount = transactionSearchObj.runPaged().count;
// log.debug("transactionSearchObj result count", searchResultCount);
// transactionSearchObj.run().each(function (result) {
// var resultArr = new Array();
// //resultArr.push('\n"' + result.getValue({ name: "internalid", label: "internalid" }) + '"');
// var cus_name = result.getValue({
// name: "companyname",
// sort: search.Sort.ASC,
// join: "customer",
// summary: "GROUP",
// label: "Company Name"
// });
// // cus_name = cus_name.substring(cus_name.indexOf(' ') + 1);
// resultArr.push('\n"' + cus_name + '"');
// resultArr.push('"' + result.getValue({
// name: "custentity_annualsalesprojection",
// join: "customer",
// summary: "GROUP",
// label: "Annual Sales Projection"
// }) + '"');
// resultArr.push('"' + result.getValue({
// name: "formulanumeric1",
// summary: "SUM",
// formula: prev_sales,
// label: "Formula (Numeric)"
// }) + '"');
// resultArr.push('"' + result.getValue({
// name: "formulanumeric2",
// summary: "SUM",
// formula: prev_cost,
// label: "Formula (Numeric)"
// }) + '"');
// var prev_prof_col = Number(Number(result.getValue({
// name: "formulanumeric1",
// summary: "SUM",
// formula: prev_sales,
// label: "Formula (Numeric)"
// })) - Number(result.getValue({
// name: "formulanumeric2",
// summary: "SUM",
// formula: prev_cost,
// label: "Formula (Numeric)"
// })))
// resultArr.push('"' + prev_prof_col + '"');
// resultArr.push('"' + result.getValue({
// name: "formulanumeric3",
// summary: "SUM",
// formula: curr_sales,
// label: "Formula (Numeric)"
// }) + '"');
// resultArr.push('"' + result.getValue({
// name: "formulanumeric4",
// summary: "SUM",
// formula: curr_cost,
// label: "Formula (Numeric)"
// }) + '"');
// ;
// var curr_profit_col = Number(Number(Number(result.getValue({
// name: "formulanumeric3",
// summary: "SUM",
// formula: curr_sales,
// label: "Formula (Numeric)"
// })) - Number(result.getValue({
// name: "formulanumeric4",
// summary: "SUM",
// formula: curr_cost,
// label: "Formula (Numeric)"
// }))))
// resultArr.push('"' + curr_profit_col + '"');
// var sales_compare = Number(result.getValue({
// name: "formulanumeric3",
// summary: "SUM",
// formula: curr_sales,
// label: "Formula (Numeric)"
// })) - Number(result.getValue({
// name: "formulanumeric1",
// summary: "SUM",
// formula: prev_sales,
// label: "Formula (Numeric)"
// }))
// var cost_compare = Number(result.getValue({
// name: "formulanumeric4",
// summary: "SUM",
// formula: curr_cost,
// label: "Formula (Numeric)"
// })) - Number(result.getValue({
// name: "formulanumeric2",
// summary: "SUM",
// formula: prev_cost,
// label: "Formula (Numeric)"
// }))
// var profit_compare = Number(curr_profit_col) - Number(prev_prof_col)
// resultArr.push('"' + sales_compare + '"');
// resultArr.push('"' + cost_compare + '"');
// resultArr.push('"' + profit_compare + '"');
// resultSet.push(resultArr);
// return true;
// })
// var fname = 'Sales Comparison Report.csv';
// // Write to a CSV file
// var fileObj = file.create({
// name: fname,
// fileType: file.Type.CSV,
// contents: resultSet.join('')
// });
// // Accounting > Closing Month > Invoice Corrections
// fileObj.folder = -15;
// var fileid = fileObj.save();
// log.debug("fileid", fileid)
// var filePath = file.load({
// id: fileid
// });
// // var url = filePath.url;
// // window.open(url, '_blank');
// context.response.writeFile(filePath)
// }
// else {
var requestPayload = JSON.parse(context.request.body);
log.debug("requestPayload", requestPayload)
context.response.setHeader('Content-Type', 'application/json');
switch (requestPayload['function']) {
case 'GetPurchasecomparedata':
return GetPurchasecomparedata(context, requestPayload);
break;
}
// }
}
function GetPurchasecomparedata(context, requestPayload) {
log.debug("inside func reqpayload", requestPayload)
var purchasecomparedetails = [];
/*
purchaseorderSearchObj.id="customsearch1664932756807";
purchaseorderSearchObj.title="Transaction Search (copy)";
var newSearchId = purchaseorderSearchObj.save();
*/
var Start_date = requestPayload.startdate
var End_date = requestPayload.enddate;
var today = new Date(Start_date);
log.debug("today", today)
var yr = today.getFullYear();
if (yr % 400 == 0 || (yr % 100 != 0 && yr % 4 == 0)) {
if (today.getDate() == "29") {
var prev_date = today.getDate() - 1;
} else {
var prev_date = today.getDate();
}
} else {
var prev_date = today.getDate();
}
var prev_month = today.getMonth() + 1;
var prev_year = today.getFullYear() - 1;
if (prev_date < 10) prev_date = '0' + prev_date;
if (prev_month < 10) prev_month = '0' + prev_month;
var Start_Date = prev_month + '/' + prev_date + '/' + prev_year;
var Enddate = new Date(End_date);
var year = Enddate.getFullYear();
if (year % 400 == 0 || (year % 100 != 0 && year % 4 == 0)) {
if (Enddate.getDate() == "29") {
var end_date = Enddate.getDate() - 1;
} else {
var end_date = Enddate.getDate();
}
} else {
var end_date = Enddate.getDate();
}
// var end_date = Enddate.getDate();
var end_month = Enddate.getMonth() + 1;
var end_year = Enddate.getFullYear() - 1;
if (end_date < 10) end_date = '0' + end_date;
if (end_month < 10) end_month = '0' + end_month;
var End_Date = end_month + '/' + end_date + '/' + end_year;
var curr_End_date = new Date();
var curr_End_date1 = curr_End_date.getDate();
var curr_End_month = curr_End_date.getMonth() + 1;
var curr_End_year = curr_End_date.getFullYear() - 1;
var today_curr_year = curr_End_date.getFullYear();
if (curr_End_date1 < 10) curr_End_date1 = '0' + curr_End_date1;
if (curr_End_month < 10) curr_End_month = '0' + curr_End_month;
var CURR_End_Date = curr_End_month + '/' + curr_End_date1 + '/' + curr_End_year;
var Todays_date = curr_End_month + '/' + curr_End_date1 + '/' + today_curr_year;
log.debug("Todays_date",Todays_date)
if (Start_date && End_date) {
log.debug("if condition")
var prev_cost = "CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_Date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_Date + "', 'MM/DD/YYYY') THEN {amount} ELSE 0 END"
var curr_cost = "CASE WHEN {trandate} BETWEEN TO_DATE('" + Start_date + "', 'MM/DD/YYYY') AND TO_DATE('" + End_date + "', 'MM/DD/YYYY') THEN {amount} ELSE 0 END "
} else {
log.debug("else condition")
var prev_cost = "CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2023', 'MM/DD/YYYY') AND TO_DATE('" + CURR_End_Date + "', 'MM/DD/YYYY') THEN {amount} ELSE 0 END"
var curr_cost = "CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2024', 'MM/DD/YYYY') AND TO_DATE('" + Todays_date + "', 'MM/DD/YYYY') THEN {amount} ELSE 0 END "
}
log.debug("prev_cost", prev_cost)
log.debug("curr_cost", curr_cost)
var transactionSearchObj = search.create({
type: "vendorbill",
settings: [{ "name": "consolidationtype", "value": "ACCTTYPE" }],
filters:
[
["type", "anyof", "VendBill"],
"AND",
["mainline", "any", ""],
"AND",
["formulatext: {name}", "isnotempty", ""]
],
columns:
[
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "{name}",
label: "Formula (Text)"
}),
search.createColumn({
name: "amount",
summary: "SUM",
label: "Amount"
}),
search.createColumn({
name: "formulanumeric1",
summary: "SUM",
formula: prev_cost,
label: "Formula (Numeric)"
}),
search.createColumn({
name: "formulanumeric2",
summary: "SUM",
formula: curr_cost,
label: "Formula (Numeric)"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
var start = 0;
var end = 1000;
//srch.run().each(function(result)
do {
// if (searchResultCount < end) {
// end = searchResultCount;
// }
log.debug({
title: 'end',
details: end
})
var result = transactionSearchObj.run().getRange({
start: start,
end: end
});
log.debug("result", result)
for (var i = 0; i < result.length; i++) {
var soSearchobj = new Object();
soSearchobj.vendor = ("" + result[i].getValue({
name: "formulatext",
summary: "GROUP",
formula: "{name}",
label: "Formula (Text)"
})).replace(/^\d+/, '');
soSearchobj.prev_purchase = result[i].getValue({
name: "formulanumeric1",
summary: "SUM",
formula: "CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2023', 'MM/DD/YYYY') AND TO_DATE('12/30/2023', 'MM/DD/YYYY') THEN {amount} ELSE 0 END ",
label: "Formula (Numeric)"
});
soSearchobj.curr_purchase = result[i].getValue({
name: "formulanumeric2",
summary: "SUM",
formula: "CASE WHEN {trandate} BETWEEN TO_DATE('01/01/2024', 'MM/DD/YYYY') AND TO_DATE('12/30/2024', 'MM/DD/YYYY') THEN {amount} ELSE 0 END ",
label: "Formula (Numeric)"
});
purchasecomparedetails.push(soSearchobj)
}
end += 1000;
start += 1000;
searchResultCount -= 1000;
} while (searchResultCount > 0);
log.debug("soSearchobj", purchasecomparedetails)
responsePayload = {
'purchasecomparedetails': purchasecomparedetails,
};
context.response.write(JSON.stringify(responsePayload, null, 5));
}