/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
/************************************************************************************************
* Hygiene Technologies NZ-NS
*
* HTNZ-155 : KPI Dashboard
*
* **********************************************************************************************
*
* Author: Jobin and Jismi IT Services
*
* Date Created : 24-August-2023
*
* Description : Suitelet Page For the Dynamic connection to Excel
*
* REVISION HISTORY
*
* @version 1.0 HTNZ-155 : 24-August-2023 : Created the Initial build
* @version 1.1 HTNZ-155 : 13-October-2023 : Added logic for displaying Daily Sales, Month to Date Sales, Year To Date Sales from WorkBook Pivot
*
***********************************************************************************************/
define([
'N/file',
'N/search',
'N/workbook'
], (file, search, workbook) => {
const SALES_BY_CUSTOMER_WORKBOOK_ID = "custworkbook_salesbycustomer_jj_htnz160"
const DAILY_SALES_PIVOT = "custpivot8_16970868316985108876";
const MONTH_TO_DATE_PIVOT = "custpivot9_16970868317942190252";
const YEAR_TO_DATE_PIVOT = "custpivot10_16970868318841329240";
const REPORT_COLUMN_LABELS = ["Daily Sales", "Month To Date Sales", 'Year To Date Sales'];
const REPORT_REC_ID = "9";
const REPORT_PASS_KEY = "98989";
/**
* Function for checking the value is not null
* @param {String} parameter
* @returns Boolean
*/
const checkForParameter = (parameter) => {
try {
if (parameter !== "" && parameter !== " " && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
}
return false;
}
catch (err) {
log.debug("error@checkParameter", err);
return false;
}
}
/**
* The function is to fetch the saved search ID
* @param {String} paramKeyPair
* @returns Search Id
*/
const getSearchId = (paramKeyPair) => {
let searchKeyPair = null;
let searchId = null;
try {
let custRecordSearch = search.create({
type: "customrecord_kpi_excel_credentials",
filters: [],
columns: [
search.createColumn({ name: "internalid", label: "ID" }),
search.createColumn({ name: "custrecord_jj_kpi_search_id", label: "Search ID" }),
search.createColumn({ name: "custrecord_jj_kpi_passkey", label: "Secret Key" }),
]
});
custRecordSearch.run().each(function (result) {
searchKeyPair = (result.getValue({ name: "internalid", label: "ID" }) + "_" + result.getValue({ name: "custrecord_jj_kpi_passkey", label: "Secret Key" }))
if (searchKeyPair == paramKeyPair) {
searchId = result.getValue({ name: "custrecord_jj_kpi_search_id", label: "Search ID" });
return false;
}
return true;
});
return searchId;
} catch (error) {
log.error({
title: "---ERROR--- @getCustomRecordInfo",
details: error
});
return searchId;
}
}
/**
* The function is to write the message
* @param {String} msg
* @param {Object} scriptContext
* @returns writeLine
*/
const returnResponse = (msg, scriptContext) => {
try {
return scriptContext.response.writeLine((msg))
}
catch (error) {
log.debug({
title: "---ERROR--- @returnResponse",
details: error
});
}
}
/**
* Returns the Grand total from the Workbook pivot
* @param {Object} workBookLoadobj
* @param {String} pivotId
* @returns amount
*/
const calculateTotalFromPivot = (workBookLoadobj, pivotId) => {
try {
let myResultSet = workBookLoadobj.runPivot({
id: pivotId
});
log.debug("results",myResultSet)
let grandTotal = myResultSet[0].measureValues[0].value["amount"] || 0.00;
log.debug("Grand total", grandTotal);
return grandTotal;
} catch(error) {
log.debug({
title: "---ERROR--- @calculateTotalFromPivot",
details: error
});
}
}
const onRequest = (scriptContext) => {
try {
let paramRecordId = scriptContext.request.parameters.recordId;
let paramPassKey = scriptContext.request.parameters.passkey;
log.debug("Parameter Values", {
recordId: paramRecordId,
passkey: paramPassKey,
});
let paramKeyPair = paramRecordId + "_" + paramPassKey;
let searchId = getSearchId(paramKeyPair);
if (paramRecordId && paramPassKey && checkForParameter(searchId) && paramRecordId != REPORT_REC_ID) {
log.debug("record info", searchId);
var savedSearch = search.load({ id: searchId });
var myPagedData = savedSearch.runPaged();
var csvContent = '';
var columnLabels = [];
// Get column labels
savedSearch.columns.forEach(function (column, index) {
log.debug('label ' + index, {
label: column.label,
column,
})
columnLabels.push(column.label);
});
csvContent += columnLabels.join(',') + 'n';
// Process search results and build CSV content
myPagedData.pageRanges.forEach(function(pageRange){
var myPage = myPagedData.fetch({index: pageRange.index});
myPage.data.forEach(function(result){
var rowData = [];
savedSearch.columns.forEach(function (column){
let resValue = checkForParameter(result.getText(column)) ? result.getText(column) : result.getValue(column)
rowData.push('"' + resValue + '"');
})
csvContent += rowData.join(',') + 'n';
})
})
// Creating the CSV file
let fileObj = file.create({
name: 'reports.csv',
fileType: file.Type.CSV,
contents: csvContent,
encoding: file.Encoding.UTF8,
});
log.debug("CSV Content", csvContent)
return scriptContext.response.writeFile(fileObj)
} else if (paramRecordId === REPORT_REC_ID && paramPassKey === REPORT_PASS_KEY) {
let salesByCustomerWorkbookLoadObj = workbook.load({
id: SALES_BY_CUSTOMER_WORKBOOK_ID
});
let totalAmountArray = [];
totalAmountArray.push(calculateTotalFromPivot(salesByCustomerWorkbookLoadObj, DAILY_SALES_PIVOT));
totalAmountArray.push(calculateTotalFromPivot(salesByCustomerWorkbookLoadObj, MONTH_TO_DATE_PIVOT));
totalAmountArray.push(calculateTotalFromPivot(salesByCustomerWorkbookLoadObj, YEAR_TO_DATE_PIVOT));
log.debug("Total Amount Obj", totalAmountArray );
var csvContent = "";
csvContent += REPORT_COLUMN_LABELS.join(',') + 'n';
csvContent += totalAmountArray.join(',') + 'n';
log.debug("CSV Content", csvContent)
// Creating the CSV file
let fileObj = file.create({
name: 'reports.csv',
fileType: file.Type.CSV,
contents: csvContent,
encoding: file.Encoding.UTF8,
});
return scriptContext.response.writeFile(fileObj)
} else {
let responseMessage = "Invalid parameters";
returnResponse(responseMessage, scriptContext)
}
} catch (error) {
log.error({
title: "---ERROR--- @onRequest",
details: error
});
}
}
return { onRequest }
});