====== Example of graph data download with JS for dashboard ======
The standard method of exporting data from graphs to XLS, HTML, CSV files has the resolution of the selected view on the respective graph, not all data - due to performance issues.
Please refer to the [[access_via_api|API article]] on how to retrieve data with REST requests.
In the following example, dashboards' JS scripting option is presented.
===== Dashboard layout =====
There are 4 control elements on the dashboard you must provide:
{{ ::api:js-csv-dash.png?direct |}}
-Start time date picker
-End time date picker
-Button for invoking the JS script
-Dictionary element for selecting one of the many graphs you may have. E.g. if you have graphs with IDs of 1, 2, 3... you have to create a dictionary of 1, 2, 3.... and bind it with the register that will be used for the element on the dashboard.
===== Script =====
Put the following script into the 'Script' tab of the dashboard properties (w/o comments)
{{ :api:js-csv-dash-script-tab.png?direct&800 |}}
==== Script text ====
const DATA = {
TIME: {START: 2, END: 3}, // start and end time register ids
DOWNLOAD_BTN: 4, // register to detect if the button was pressed
GRAPH_ID: 1, // placeholder for the graph id that
GRAPH_SELECTION_REG: 7 // will be selected by selection register
};
const MAX_SLICES = 2999, // max number of the slices in the graph data api call
TIMEOUT = 15e3,
START_GRAPH_ID = 1, // start number of the range for graphs ids
END_GRAPH_ID = 6; // end number
let _i;
var preventMultiply = false,
dataToSend = {},
promises = [];
App.on('register:newValue', triggerUponRegChange);
function triggerUponRegChange(newValuesArray) {
let ifButtonPressed = (newValuesArray.length > 0)
&& newValuesArray.filter(readVal => parseInt(readVal.regId) === DATA.DOWNLOAD_BTN
&& parseInt(readVal.value) > 0)
.length > 0;
if (ifButtonPressed && !preventMultiply) {
preventMultiply = true;
console.log('my button is pressed!')
let selectedGraph = new Register(DATA.GRAPH_SELECTION_REG).getValue();
selectedGraph = parseInt(selectedGraph.replace(/^.*\((\d)\).*$/, (match, p1) => p1));
DATA.GRAPH_ID = (selectedGraph >= START_GRAPH_ID && selectedGraph <= END_GRAPH_ID) ? selectedGraph : 1;
Promise.all(makeApiCall())
.then(exportDataAsCSVToBrowser)
.catch(e => console.log(e))
.finally(() => {preventMultiply = false ; console.log('preventMultiply was reset!')})
}
}
function makeApiCall() {
let slicesParams = getSlicesFromTS(); // preventMultiply = true;
dataToSend = {};
promises = [];
slicesParams.forEach(function(paramsSet, i) {
let promise = new Promise(function(resolve, reject) {
$.ajax({
url: "/api/graph-data/" + DATA.GRAPH_ID,
method: "GET",
headers: {
"X-WH-START": paramsSet.start,
"X-WH-END": paramsSet.end,
"X-WH-SLICES": paramsSet.slices,
},
dataType: 'json',
context: this,
success: function (response) {
dataToSend[i] = response;
resolve();
},
error: function (result, text, error) {
reject(text);
}
});
});
promises.push(promise);
});
return promises;
}
function exportDataAsCSVToBrowser() {
let response = [];
Object.values(dataToSend).forEach(dataSet => {response = response.concat(dataSet)});
if (response.length > 0) {
let rep = [];
let header = ['Time'];
let regs = WebHMI.graphs[DATA.GRAPH_ID].regs;
regs.forEach(function(reg) {
let vv = reg.title.replace("\"", "\"\"");
header.push('"' + vv + " - Min" + '"');
header.push('"' + vv + " - Avg" + '"');
header.push('"' + vv + " - Max" + '"');
});
rep.push(header);
response.forEach(function(dataSet) {
let row = [moment(dataSet.x).format("YYYY-MM-DD HH:mm:ss")];
regs.forEach(reg => dataSet[reg.id].split(';').forEach(value => {row.push(value)}));
rep.push(row);
});
if (window.navigator.msSaveBlob) { // IE
let blobObject = new Blob([rep.join("\r\n")]);
window.navigator.msSaveBlob(blobObject, WebHMI.graphs[DATA.GRAPH_ID].title + '.csv');
} else {
let a = document.createElement('a');
a.href = 'data:attachment/csv,' + encodeURIComponent(rep.join("\r\n"));
a.target = '_blank';
a.download = WebHMI.graphs[DATA.GRAPH_ID].title + '.csv';
a.style.visibility = "hidden";
document.body.appendChild(a);
a.click();
}
}
}
function getSlicesFromTS() {
let paramsSet = [];
calcSlices(parseInt(new Register(DATA.TIME.START).getValue()),
parseInt(new Register(DATA.TIME.END).getValue()));
function calcSlices(s, e) {
if (s > e) {
console.log('startTS >= endTS') ; return ;
}
if (e - s <= MAX_SLICES)
paramsSet.push({start:s, end: e, slices: e - s + 1});
else {
let newEnd = s + MAX_SLICES - 1;
paramsSet.push({start: s, end: newEnd, slices: MAX_SLICES});
calcSlices(newEnd + 1, e);
}
}
return paramsSet;
}
==== Button release script ====
Because the dashboard expects the register change be greater than 0, we have to reset this register after each use.
The following script can do that:
BUTTON_REG_ID = 9
function main (userId)
local cur_value = R(BUTTON_REG_ID)
if (not resetDemand and cur_value > 0) then
resetDemand = true -- postpone reset for one scan for JS to detect change
return
end
if (resetDemand) then
reset(BUTTON_REG_ID) -- now real reset
resetDemand = false
end
end
function reset(reg)
local cur_value = R(reg)
if cur_value and cur_value ~= 0 then
W(reg, 0)
end
end