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)
};
}());