Beginners Guide to Automation Episode 18 Flow

[{"id":"b5cba1dd.2346f","type":"tab","label":"DB Example","disabled":false,"info":""},{"id":"7a228d08.e7bd04","type":"mqtt in","z":"b5cba1dd.2346f","name":"","topic":"hydro1/tempExt","qos":"2","datatype":"auto","broker":"7dc82cc4.908a44","x":240,"y":260,"wires":[["938971c1.e751e","a8cc233e.50e07"]]},{"id":"5b2d49ef.d018c8","type":"mqtt in","z":"b5cba1dd.2346f","name":"","topic":"hydro1/humExt","qos":"2","datatype":"auto","broker":"7dc82cc4.908a44","x":240,"y":440,"wires":[["28196c13.055754","3e9aa02e.beccc"]]},{"id":"28196c13.055754","type":"ui_gauge","z":"b5cba1dd.2346f","name":"","group":"118776f9.65ebf9","order":2,"width":6,"height":5,"gtype":"wave","title":"Humidity","label":"%","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":560,"y":440,"wires":[]},{"id":"938971c1.e751e","type":"ui_chart","z":"b5cba1dd.2346f","name":"","group":"118776f9.65ebf9","order":1,"width":17,"height":5,"label":"Temperature","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"bezier","nodata":"","dot":false,"ymin":"5","ymax":"45","removeOlder":"48","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#f68504","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":570,"y":260,"wires":[[]]},{"id":"f5dc93f1.77c07","type":"sqlite","z":"b5cba1dd.2346f","mydb":"e4f6f10c.ec565","sqlquery":"msg.topic","sql":"","name":"IOT Database","x":590,"y":160,"wires":[[]]},{"id":"a8cc233e.50e07","type":"function","z":"b5cba1dd.2346f","name":"","func":"var bufferLength = flow.get('bufferLength')||0; //how many entries before writing to DB\nvar theDevice = \"growtent1\";\nvar theSensor = \"t\";\nvar sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch\nvar count = context.get('count')||0;\ncount +=1;\nvar theSQL = context.get('theSQL') || \"\";\n\nif (count> bufferLength){\n    theSQL = \"INSERT INTO IOTSensors (timestamp, deviceName, sensor, reading) VALUES \" + theSQL;\n    theSQL = theSQL + \"('\" + sqliteTimeStamp + \"', '\" +  theDevice +\"', '\" + theSensor + \"', \" + msg.payload + \");\";\n    count = 0;\n    context.set('count',count);\n    msg = {topic: theSQL};\n    context.set('theSQL',\"\");\n    return msg;\n}else{\n    theSQL = theSQL + \"('\" + sqliteTimeStamp + \"', '\" +  theDevice +\"', '\" + theSensor + \"', \" + msg.payload + \"),\";\n    context.set('theSQL',theSQL);\n    context.set('count',count);\n}","outputs":1,"noerr":0,"x":400,"y":180,"wires":[["f5dc93f1.77c07","52deffc2.c54b7"]]},{"id":"3e9aa02e.beccc","type":"function","z":"b5cba1dd.2346f","name":"","func":"var bufferLength = flow.get('bufferLength')||0; //how many entries before writing to DB\nvar theDevice = \"growtent1\";\nvar theSensor = \"h\";\nvar sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch\nvar count = context.get('count')||0;\ncount +=1;\nvar theSQL = context.get('theSQL') || \"\";\n\nif (count> bufferLength){\n    theSQL = \"INSERT INTO IOTSensors (timestamp, deviceName, sensor, reading) VALUES \" + theSQL;\n    theSQL = theSQL + \"('\" + sqliteTimeStamp + \"', '\" +  theDevice +\"', '\" + theSensor + \"', \" + msg.payload + \");\";\n    count = 0;\n    context.set('count',count);\n    msg = {topic: theSQL};\n    context.set('theSQL',\"\");\n    return msg;\n}else{\n    theSQL = theSQL + \"('\" + sqliteTimeStamp + \"', '\" +  theDevice +\"', '\" + theSensor + \"', \" + msg.payload + \"),\";\n    context.set('theSQL',theSQL);\n    context.set('count',count);\n}","outputs":1,"noerr":0,"x":390,"y":360,"wires":[["e540aa4b.eb3398"]]},{"id":"e540aa4b.eb3398","type":"sqlite","z":"b5cba1dd.2346f","mydb":"e4f6f10c.ec565","sqlquery":"msg.topic","sql":"","name":"IOT Database","x":580,"y":340,"wires":[[]]},{"id":"7814dd1a.b4cd84","type":"inject","z":"b5cba1dd.2346f","name":"startupExec","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":"0.1","x":210,"y":60,"wires":[["aa9792a5.2cc73"]]},{"id":"aa9792a5.2cc73","type":"function","z":"b5cba1dd.2346f","name":"","func":"flow.set('bufferLength',60)","outputs":1,"noerr":0,"x":390,"y":60,"wires":[[]]},{"id":"3086e6eb.3449ea","type":"inject","z":"b5cba1dd.2346f","name":"Purge Data","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":100,"wires":[["5dbd73a5.181a4c"]]},{"id":"5dbd73a5.181a4c","type":"function","z":"b5cba1dd.2346f","name":"","func":"flow.set('bufferLength',1)\n","outputs":1,"noerr":0,"x":390,"y":100,"wires":[[]]},{"id":"52deffc2.c54b7","type":"debug","z":"b5cba1dd.2346f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","x":570,"y":100,"wires":[]},{"id":"7dc82cc4.908a44","type":"mqtt-broker","z":"","name":"MQTT BROKER","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"118776f9.65ebf9","type":"ui_group","z":"","name":"Sensors","tab":"8414e9bb.ceda38","order":1,"disp":true,"width":25,"collapse":false},{"id":"e4f6f10c.ec565","type":"sqlitedb","z":"","db":"/databases/iot.db","mode":"RWC"},{"id":"8414e9bb.ceda38","type":"ui_tab","z":"","name":"DB Example","icon":"dashboard","order":1,"disabled":false,"hidden":false}]