sl html
Wed Sep 18 2024 11:22:53 GMT+0000 (Coordinated Universal Time)
Saved by @mdfahadmalik27
/** * @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)); }
Comments