User Tools

Site Tools


download-csv-js

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 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:

  1. Start time date picker
  2. End time date picker
  3. Button for invoking the JS script
  4. 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)

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  
download-csv-js.txt · Last modified: 2023/10/15 18:07 by emozolyak

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki