comonway docs

# Influxdb CSV Report To FTP

Node-RED offer the desired flexibility for processing all kind of dataflux. The essence of Comonway.

Node Red Influxdb Csv Report

We focus in this note on InfluxDB datasource but we organize code for processing all kind of datasource type:

  1. Write your query to your datasource
  2. Format the response in a 2D objects (timestamp/objects) list for time report
  3. Create the report with the 2D objects
  4. Send it to a distant server

Example of 2D time objects:

{
  "2018-04-01T23:45:00.000Z":
  {
    "Accumulateurs":{"energie":null,"compteur_adresse":"22"},
    "Huile thermique":{"energie":null,"compteur_adresse":"21"},
    "Grille refroidissement":{"energie":null,"compteur_adresse":"22"},
    "Recuperateur de chaleur":{"energie":null,"compteur_adresse":"22"}  
  }
}

Requirements:

Node-RED nodes:

Note: the ftp-sftp node is not completed and must be updated in next version.

Node-RED JS libraries:

  • influxdb/influxdb-query-define-last-period.js
  • influxdb/influxdb-result-to-2d-objets.js
  • report/monthly-energy-index-csv-report.js
  • global/zero-padding.js

Note: These nodes are available in Comongate Node-RED.

# Nodes Configuration

  • Query: Define the period in msg.period = lastPeriod(new Date(msg.payload), "1M", 23, 45, 0, 0); and update query
  • InfluxDB: Define influxdb server and Retention Policy.
  • Result format: Define time and name index in msg.payload = resultOrderByDate(msg.payload, "time", "compteur_nom");
  • CSV report: Define report parameters like name, sitename and value to expose in the report
  • CSV node: Keep as default
  • Filename format: Define sitename, local and ftp folder path
  • FTP: Define FTP server, operation (to PUT) and Working Directory inside ftp server
  • Local file: Define Action as Overwrite file and check all checkbox

# Installation

Copy content below to clipboard and paste it in Node-RED.

[{"id":"979596a.ed29168","type":"comment","z":"1fa9fcf6.fefd53","name":"Query InfluxDB to generate CSV for sending to FTPserver","info":"","x":230,"y":580,"wires":[]},{"id":"ad56a928.b83b38","type":"csv","z":"1fa9fcf6.fefd53","name":"","sep":",","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"","skip":"0","x":730,"y":760,"wires":[["684d7416.8e084c","78489f03.ec55c"]]},{"id":"8448612c.c2ab","type":"influxdb in","z":"1fa9fcf6.fefd53","influxdb":"b1f268c4.211488","name":"","query":"","rawOutput":false,"precision":"","retentionPolicy":"autogen","x":720,"y":620,"wires":[["f1101bbf.6c3628","645e6420.c4d9cc"]]},{"id":"ce3776fb.6e6a08","type":"inject","z":"1fa9fcf6.fefd53","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 05 * * *","once":false,"onceDelay":0.1,"x":110,"y":620,"wires":[["456dd39d.f001dc"]]},{"id":"a0f4d72c.0c13a8","type":"function","z":"1fa9fcf6.fefd53","name":"Influxdb Result to 2D objects","func":"// Order key/value influxdb result by 2D object ISOdate,Name\nfunction resultOrderByDate(src, keytime, keyname) {\n    \n    var dest = {};\n    \n    src.forEach(element => {\n        if (element[keytime] && element[keyname]) {\n            var keyT = element[keytime].toISOString();\n            if (dest[keyT]  === undefined) {\n                dest[keyT] = {};\n            }\n        \n            var keyN = element[keyname];\n            if (dest[keyT][keyN]  === undefined) {\n                dest[keyT][keyN] = {};\n            }\n            \n            Object.keys(element).forEach(key => {\n                if(String(key) != String(keytime) && String(key) != String(keyname)) {\n                    dest[keyT][keyN][key] = element[key];\n                }\n            });\n        }\n    });\n    \n    return dest;\n}\nif (msg.payload.length > 0) {\n    msg.payload = resultOrderByDate(msg.payload, \"time\", \"compteur_nom\");\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":760,"wires":[["73859912.1088a8"]]},{"id":"e028d5f7.9810f8","type":"ftp in","z":"1fa9fcf6.fefd53","ftp":"7148dae7.303a84","operation":"put","filename":"","localFilename":"","workdir":"/data/Comptage/Cricad","savedir":"","name":"","x":450,"y":880,"wires":[["a0414ecd.286fe"]]},{"id":"a0414ecd.286fe","type":"debug","z":"1fa9fcf6.fefd53","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":880,"wires":[]},{"id":"9c11fdcc.eb3f3","type":"file","z":"1fa9fcf6.fefd53","name":"Local file backup","filename":"","appendNewline":true,"createDir":true,"overwriteFile":"true","x":490,"y":920,"wires":[]},{"id":"a33c912f.3533d","type":"function","z":"1fa9fcf6.fefd53","name":"Query formatting","func":"// lastPeriod return starttime and endtime of the last period formatted to ISO date\nfunction lastPeriod(moment, period, h, m, s, ms) {\n\n    var start, end;    \n    var actMonth = moment.getMonth();\n    var actYear = moment.getFullYear();\n    var actDay = moment.getDay();\n    var periodType = period.slice(-1);\n    var quantity = period.substring(0, period.length-1)\n    \n    switch (periodType) {\n        case \"M\":\n            \n            // Start of period\n            var startMonth = moment.getMonth()-quantity;\n            var startYear = moment.getFullYear();\n            if (startMonth == 11) {\n                startYear = moment.getFullYear()-quantity;\n            }\n            start = new Date (startYear, startMonth, 1)\n            start.setHours(h, m, s, ms);\n            start = start.toISOString();\n            \n            // End of period\n            var endMonth = moment.getMonth()-1;\n            var endYear = moment.getFullYear();\n            if (endMonth == 11) {\n                endYear = moment.getFullYear()-1;\n            }\n            var endDay = new Date(actYear, actMonth, 0).getDate();\n            end = new Date (endYear, endMonth, endDay)\n            end.setHours(h, m, s, ms);\n            end = end.toISOString();\n    }\n    \n    return {\"start\": start, \"end\": end};\n}\n\nmsg.period = lastPeriod(new Date(msg.payload), \"1M\", 23, 45, 0, 0);\n\n// Influxdb request builder\nrequest = `SELECT first(\"energie\") AS \"energie\" `;\nrequest += `FROM \"heat_energy_meter\" `;\nrequest += `WHERE time >= '`+ msg.period.start +`' AND time <= '`+ msg.period.end +`' `;\n//request += `AND '`+ msg.period.end +`' `;\nrequest += `GROUP BY time(1d,-15m), \"compteur_adresse\", \"compteur_nom\" `;\nrequest += `FILL(null)`;\n\nmsg.query = request;\n\nreturn msg","outputs":1,"noerr":0,"x":490,"y":620,"wires":[["8448612c.c2ab"]]},{"id":"f1101bbf.6c3628","type":"debug","z":"1fa9fcf6.fefd53","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":910,"y":660,"wires":[]},{"id":"51940568.4ac67c","type":"function","z":"1fa9fcf6.fefd53","name":"Filename formatting (1:Ftp, 2:Local)","func":"Number.prototype.pad = function(size) {\n  var s = String(this);\n  while (s.length < (size || 2)) {s = \"0\" + s;}\n  return s;\n}\n\n// Configuration\nvar site = \"Cricad_Energies\"\n\n// Folder\nvar locFolder = \"/data/projects/cricadenergies-comonway/ftp/\";\nvar ftpFolder = \"/data/Comptage/Cricad/\";\n\n// Filename definition\nvar date = new Date(msg.period.end);\nvar year = date.getFullYear();\nvar month = date.getMonth() + 1;\n\nlocFilename = locFolder + site + \"_\" + year + \"-\" + month.pad(2) + \".csv\";\nftpFilename = ftpFolder + site + \"_\" + year + \"-\" + month.pad(2) + \".csv\";\n\nlocal = {\n    filename: locFilename,\n    payload: msg.payload\n        \n};\nftp = {\n    payload: {\n        filename: ftpFilename,\n        filedata: msg.payload   \n    }\n};\nreturn [ftp, local];","outputs":2,"noerr":0,"x":220,"y":900,"wires":[["e028d5f7.9810f8"],["9c11fdcc.eb3f3"]]},{"id":"645e6420.c4d9cc","type":"link out","z":"1fa9fcf6.fefd53","name":"InfluxdbToCsv","links":["ab3c3904.2769b8"],"x":875,"y":620,"wires":[]},{"id":"ab3c3904.2769b8","type":"link in","z":"1fa9fcf6.fefd53","name":"","links":["645e6420.c4d9cc"],"x":35,"y":760,"wires":[["a0f4d72c.0c13a8"]]},{"id":"78489f03.ec55c","type":"link out","z":"1fa9fcf6.fefd53","name":"CSVtoFile","links":["c40d52d0.4548e"],"x":835,"y":760,"wires":[]},{"id":"c40d52d0.4548e","type":"link in","z":"1fa9fcf6.fefd53","name":"","links":["78489f03.ec55c"],"x":35,"y":900,"wires":[["51940568.4ac67c"]]},{"id":"5b5e0424.c27b8c","type":"comment","z":"1fa9fcf6.fefd53","name":"Convert influxdb result to csv file","info":"","x":150,"y":720,"wires":[]},{"id":"891c81ed.26b04","type":"comment","z":"1fa9fcf6.fefd53","name":"Prepare for saving file to local or ftp server","info":"","x":180,"y":860,"wires":[]},{"id":"684d7416.8e084c","type":"debug","z":"1fa9fcf6.fefd53","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":870,"y":800,"wires":[]},{"id":"73859912.1088a8","type":"function","z":"1fa9fcf6.fefd53","name":"Monthly energy index CSV report","func":"// Configuration\nvar reportName = \"Comonway Monthly Energy Index report\";\nvar reportSite = \"Cricad Energies\";\nvar reportDate = new Date();\nvar reportDev = \"Eyxance - Connecting Smart technologies to people\";\nvar reportValueName = \"energie\"\n\nvar csv = [];\ncsv.push([reportName]);\ncsv.push([reportSite]);\ncsv.push([reportDate]);\ncsv.push([\"Period: \" + msg.period.start + \" to \" + msg.period.end]);\ncsv.push([reportDev]);\ncsv.push([]);\n\nif (Object.keys(msg.payload).length > 0) {\n    // Define report header from 2D objects\n    reportHeader = [\"timestamp\"];\n    Object.keys(msg.payload[Object.keys(msg.payload)[0]]).forEach(key => {\n        reportHeader.push(key);\n    });\n    csv.push(reportHeader);\n\n    // Extract line to report\n    Object.keys(msg.payload).forEach(key => {\n        var line = [];\n        line.push(key);\n        Object.keys(msg.payload[key]).forEach(element => {\n            line.push(msg.payload[key][element][reportValueName]);    \n        });\n        csv.push(line);\n    });\n} else {\n    csv.push([\"No result.\"]);\n}\n\nmsg.payload = csv;\n\nreturn msg;","outputs":1,"noerr":0,"x":500,"y":760,"wires":[["ad56a928.b83b38"]]},{"id":"6b48608a.afc7","type":"comment","z":"1fa9fcf6.fefd53","name":"Query influxdb to create a CSV report and send to FTP server","info":"","x":240,"y":540,"wires":[]},{"id":"5ab90bfa.37a8b4","type":"inject","z":"1fa9fcf6.fefd53","name":"","topic":"","payload":"","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":660,"wires":[["a33c912f.3533d"]]},{"id":"456dd39d.f001dc","type":"function","z":"1fa9fcf6.fefd53","name":"Day of report","func":"/**/\nvar reportDay = 1;\n/**/\n\nvar date = new Date(msg.payload);\n\nif(date.getDate() == reportDay) {\n    return msg;\n}","outputs":1,"noerr":0,"x":290,"y":620,"wires":[["a33c912f.3533d"]]},{"id":"b1f268c4.211488","type":"influxdb","z":"","hostname":"influxdb","port":"8086","protocol":"http","database":"monitor","name":"","usetls":false,"tls":""},{"id":"7148dae7.303a84","type":"ftp","z":"","host":"93.88.240.196","port":"21","secureOptions":"","user":"gckx_new_serv_ftp","connTimeout":"","pasvTimeout":"","keepalive":"","password":"Ftp789456"}]