Use of QueryManager inside a custom UI-Action of WebIQ

Hi there! We have a particular requirement of exporting trend data points as a .csv file. I already made an ui-action for a button that reads the current value of a set of variables, format and save the file. I based on a post here that used the readDirect() method of WebIQ Visuals' ItemManager.

This was cool, but I want to improve the code to retrieve the data points in a time interval that a specific event occurred (e.g. pressure raise, temperature outside limits, etc), so that a specialist can download and analyze offline. Then, the current values are not sufficient for me. I want the past values. I'm trying to use the queryDirect() method of QueryManager that I read in the documentation. However, I'm getting an error while trying to open the database (see the screenshot). I'm struggling to fix the code. I hope someone in the community can help me.

He is the code I'm testing. I started simple. I installed the SQLite Studio to see how the database was organized when I set up the Recorder Manager. Then I chose a specific interval between two timestamps of a variable (recorder_item_id = 1) that I knew that there are data. Just for start. In the future I want the user to choose the variable and the time interval.

/**
 * Custom UI-Action 'exportCSV'.
 *
 * Description:
 * Export variables in csv format
 */
(function() {
    const actions = shmi.pkg("visuals.session.userActions"); //get reference to userActions object

    function getTimestamp() {
        return (new Date(Date.now())).toISOString().split(".")[0].replace(/:/g, "-").split("T").join("_");
    } 

    function saveExport(exportData) {
        const anchor = document.createElement("a");

        anchor.href = `data:text/json;charset=utf-8 ${encodeURIComponent(exportData)}`;
        anchor.download = `export_${getTimestamp()}.txt`;
        anchor.click();
        shmi.notify(`File downloaded ${anchor.download}`);
    }   

    /**
     * UI-Action 'exportCSV' implementation
     *
     * @params {any[]} parameters  configured ui-action parameters
     * 
     */
    actions["exportCSV"] = function(parameters) {
        //Place your code here
        const delimiter = ";",
            lf = "\n",
            qm = shmi.visuals.session.QueryManager;
        let exportData = "";
        // Header line
        exportData += "Timestamp" + delimiter + "Value" + lf;
        const filter = [
            { column: "recorder_item_id", operator: "=", value: 1},
            { column: "timestamp", operator: ">=", value: 17478493429679309},
            { column: "timestamp", operator: "<=", value: 17478494434753332}
        ];
        const columns = ['timestamp','value'];
        const table = "_RecorderData";
        const dbPath = "teste.sqlite";

        qm.queryDirect((err, result) => {
            if (err) {
                shmi.notify("an error occurred!", err)
            } else {
                // Loop through the results and prepare the CSV data
                result.forEach((row) => {
                    exportData += row.timestamp + delimiter + row.value + lf;
                });
                saveExport(exportData);
                console.log(exportData);
            }
        },table,columns,filter,dbPath)       

    };
}());   
error_csv_function_db_open.png
145.49KB
4 replies