Create a CouchDB view that return key=datetime and value= multiple values and present the sum of motion in a tabulator(tabulator.info) in a node-red dashboard
Create a view in CouchDB
function(doc) {
if(doc.payload_fields.accMotion || doc.dev_id) {
emit(doc.metadata.time, [{“accMotion”: doc.payload_fields.accMotion}, {“app_id”: doc.app_id}, {“dev_id”: doc.dev_id}]);
}
}
Step 1 Parse the msg.payload from view from couchdb/cloudant node returning the data from the custom view with a change node and JSONata
payload.$.key@$L.{ ‘datetime’: key, ‘accMotion’: value.accMotion, ‘app_id’: value.app_id, ‘dev_id’: value.dev_id }
Step 2 Format the tabulator table with a change node
{
"tabulator": {
"columnResized": "function(column){ var newColumn = { field: column._column.field, visible: column._column.visible, width: column._column.width, widthFixed: column._column.widthFixed, widthStyled: column._column.widthStyled }; this.send({topic:this.config.topic,ui_control:{callback:'columnResized',columnWidths:newColumn}}); }",
"columnMoved": "function(column, columns){ var newColumns=[]; columns.forEach(function (column) { newColumns.push({'field': column._column.field}); }); this.send({topic:this.config.topic,ui_control:{callback:'columnMoved',columns:newColumns}}); }",
"groupHeader": "function (value, count, data, group) {return value + \"<span style='color:#d00; margin-left:10px;'>(\" + count + \" Termostat\"+((count>1) ? \"e\" : \"\") + \")</span>\";}",
"columns": [
{
"formatterParams": {
"outputFormat": "DD/MM/YY HH:mm",
"timezone": "Europe/Oslo"
},
"title": "DateTime",
"formatter": "datetime",
"field": "datetime",
"width": 144,
"frozen": true
},
{
"formatterParams": {
"target": "_blank"
},
"title": "Data",
"columns": [
{
"title": "Device",
"field": "dev_id",
"headerFilter": "input",
"width": 100
},
{
"formatterParams": {
"target": "_blank",
"min": -1,
"max": 600,
"color": [
"cyan",
"lightgreen",
"yellow",
"gold",
"orange",
"orangered",
"red"
],
"legend": "function (value) {return ' '+value;}",
"legendColor": "#101010",
"legendAlign": "left"
},
"title": "Motion",
"field": "accMotion",
"formatter": "progress",
"topCalc": "avg",
"width": 150,
"legendalign": "center",
"headerFilter": "number",
"headerFilterPlaceholder": "less than...",
"headerFilterFunc": "<"
},
{
"title": "Application",
"field": "app_id",
"formatter": "text",
"headerFilter": true,
"width": 100
}
]
}
],
"layout": "fitColumns",
"movableColumns": true,
"groupBy": ""
},
"customHeight": 120
}
Step 3 Add a “reload/refresh” data button
Add a http node, a button and change node to create your msg.url payload.
The flow
[{"id":"1e8415e.c1bdcea","type":"cloudantplus in","z":"94c4d41e.d7bca8","name":"","cloudant":"bfad9871.6b48d8","database":"elsysdb","service":"_ext_","search":"_view_","design":"demo","index":"v_demo3","x":600,"y":80,"wires":[["2eef63f6.798c1c"]]},{"id":"ebb8c163.f71a4","type":"inject","z":"94c4d41e.d7bca8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":80,"wires":[["a5b63d02.893af"]]},{"id":"bbcedca3.80843","type":"debug","z":"94c4d41e.d7bca8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1090,"y":20,"wires":[]},{"id":"a6ad7c74.ba5fd","type":"function","z":"94c4d41e.d7bca8","d":true,"name":"cloudant_view","func":"msg.payload = {view: \"demo\", index: \"v_demo2?descending=true\", limit:3};\n\nreturn msg;\n\n//v_demo2?descending=true&limit=3","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":40,"wires":[["1e8415e.c1bdcea"]]},{"id":"cf753344.14695","type":"debug","z":"94c4d41e.d7bca8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":620,"y":40,"wires":[]},{"id":"cf25f43d.4295e8","type":"ui_table","z":"94c4d41e.d7bca8","group":"151b608b.d5f8ff","name":"","order":1,"width":"10","height":"8","columns":[],"outputs":0,"cts":false,"x":1250,"y":80,"wires":[]},{"id":"a5b63d02.893af","type":"function","z":"94c4d41e.d7bca8","name":"setInterval","func":"\nlet d = new Date();\nlet endkey = new Date().toJSON(); // in UTC format\n//console.log('Today is: ' + d.toLocaleString());\n d.setMinutes(d.getMinutes() -1440); //use a flow value for interval_in_minutes\nlet startkey = d.toJSON(); // in UTC format\nmsg.payload = {view: \"demo\", index: \"v_demo3?descending=true\",startkey: startkey, endkey: endkey, limit:200};\n\n\nreturn msg;\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":370,"y":100,"wires":[["1e8415e.c1bdcea"]]},{"id":"2eef63f6.798c1c","type":"change","z":"94c4d41e.d7bca8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.$.key@$L.{\t 'datetime': key,\t 'accMotion': value.accMotion,\t 'app_id': value.app_id,\t 'dev_id': value.dev_id\t \t \t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":860,"y":80,"wires":[["bbcedca3.80843","2ef1aa05.9a5946","f71b466c.ab9448"]]},{"id":"2ef1aa05.9a5946","type":"change","z":"94c4d41e.d7bca8","name":"ui_control","rules":[{"t":"set","p":"ui_control","pt":"msg","to":"{\"tabulator\":{\"columnResized\":\"function(column){ var newColumn = { field: column._column.field, visible: column._column.visible, width: column._column.width, widthFixed: column._column.widthFixed, widthStyled: column._column.widthStyled }; this.send({topic:this.config.topic,ui_control:{callback:'columnResized',columnWidths:newColumn}}); }\",\"columnMoved\":\"function(column, columns){ var newColumns=[]; columns.forEach(function (column) { newColumns.push({'field': column._column.field}); }); this.send({topic:this.config.topic,ui_control:{callback:'columnMoved',columns:newColumns}}); }\",\"groupHeader\":\"function (value, count, data, group) {return value + \\\"<span style='color:#d00; margin-left:10px;'>(\\\" + count + \\\" Termostat\\\"+((count>1) ? \\\"e\\\" : \\\"\\\") + \\\")</span>\\\";}\",\"columns\":[{\"formatterParams\":{\"outputFormat\":\"DD/MM/YY HH:mm\",\"timezone\":\"Europe/Oslo\"},\"title\":\"DateTime\",\"formatter\":\"datetime\",\"field\":\"datetime\",\"width\":144,\"frozen\":true},{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"Data\",\"columns\":[{\"title\":\"Device\",\"field\":\"dev_id\",\"headerFilter\":\"input\",\"width\":100},{\"formatterParams\":{\"target\":\"_blank\",\"min\":-1,\"max\":600,\"color\":[\"cyan\",\"lightgreen\",\"yellow\",\"gold\",\"orange\",\"orangered\",\"red\"],\"legend\":\"function (value) {return ' '+value;}\",\"legendColor\":\"#101010\",\"legendAlign\":\"left\"},\"title\":\"Motion\",\"field\":\"accMotion\",\"formatter\":\"progress\",\"topCalc\":\"avg\",\"width\":150,\"legendalign\":\"center\",\"headerFilter\":\"number\",\"headerFilterPlaceholder\":\"less than...\",\"headerFilterFunc\":\"<\"},{\"title\":\"Application\",\"field\":\"app_id\",\"formatter\":\"text\",\"headerFilter\":true,\"width\":100}]}],\"layout\":\"fitColumns\",\"movableColumns\":true,\"groupBy\":\"\"},\"customHeight\":120}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":1080,"y":100,"wires":[["cf25f43d.4295e8"]]},{"id":"4eac44d4.86740c","type":"http in","z":"94c4d41e.d7bca8","name":"","url":"/int","method":"get","upload":false,"swaggerDoc":"","x":160,"y":140,"wires":[["a5b63d02.893af"]]},{"id":"f71b466c.ab9448","type":"http response","z":"94c4d41e.d7bca8","name":"","statusCode":"","headers":{},"x":1080,"y":200,"wires":[]},{"id":"bbd5fd59.7a0f6","type":"change","z":"94c4d41e.d7bca8","name":"http://localhost/int","rules":[{"t":"set","p":"url","pt":"msg","to":"http://localhost/int","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":220,"wires":[["a5b63d02.893af"]]},{"id":"a675ca77.8dfec8","type":"ui_button","z":"94c4d41e.d7bca8","name":"","group":"151b608b.d5f8ff","order":3,"width":"0","height":"0","passthru":false,"label":"Refresh data","tooltip":"Click her to update the dataset","color":"yellow","bgcolor":"","icon":"fa fa-history","payload":"","payloadType":"date","topic":"","topicType":"str","x":110,"y":240,"wires":[["bbd5fd59.7a0f6"]]},{"id":"bfad9871.6b48d8","type":"cloudantplus","host":"782ca24c-296b-4576-9fb7-d423e63cc8bf-bluemix.cloudant.com","name":"elsysdb"},{"id":"151b608b.d5f8ff","type":"ui_group","name":"Sensordata","tab":"c0831e25.7ef5f","order":1,"disp":true,"width":"10","collapse":true},{"id":"c0831e25.7ef5f","type":"ui_tab","name":"Tabledata","icon":"fa-spinner","order":5,"disabled":false,"hidden":false}]
Node-RED LoRaWAN CouchDB and more 
Leave a comment