|
[{"id":"5bcca26f.6bfcbc","type":"function","z":"15e1f6a1.aeab19","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar p_30d = 1000*60*60*24*30 ; //30 Days\nvar p_7d = 1000*60*60*24*7 ; //7 Days\nvar p_1d = 1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\nvar sourcelist = [];\nvar aggrlist = [];\nvar title = \"\";\n\n\n// Get the period and the list of data sources \n// also set some default values if one or the other does not exist yet\nsourcelist = context.get(\"sourcelist\");\nif (sourcelist===undefined) { // if running for the first time\n sourcelist = [];\n}\naggrlist = context.get(\"aggrlist\");\nif (aggrlist===undefined) { // if running for the first time\n aggrlist = [];\n}\nfromdate = context.get(\"fromdate\");\nif (fromdate===undefined) {\n // set the period to a default if it is not selected yet\n fromdate = current-p_1d;\n}\nenddate = context.get(\"enddate\");\nif (enddate===undefined) {\n // set the period to a default if it is not selected yet\n enddate = current;\n}\n\nswitch(msg.topic) {\n case \"period\":\n switch(msg.payload) {\n case \"today\":\n fromdate = today0h;\n enddate = today0h+p_1d;\n break;\n case \"yesterday\":\n fromdate = today0h-p_1d;\n enddate = today0h;\n break;\n case \"thisweek\":\n fromdate = monday0h;\n enddate = monday0h+p_7d;\n break;\n case \"lastweek\":\n fromdate = monday0h-p_7d;\n enddate = monday0h;\n break;\n case \"last24h\":\n fromdate = current-p_1d;\n enddate = current;\n break;\n case \"last7d\":\n fromdate = current-p_7d;\n enddate = current;\n break;\n case \"last30d\":\n fromdate = current-p_30d;\n enddate = current;\n break;\n }\n context.set(\"fromdate\",fromdate);\n context.set(\"enddate\",enddate);\n break;\n case \"datasource\":\n if (msg.payload===\"delete\") {\n // remove all previous data sources\n sourcelist = [];\n } else {\n sourcelist = context.get(\"sourcelist\");\n if (sourcelist===undefined) { // if running for the first time\n sourcelist = [];\n }\n sourcelist.push(msg.payload);\n }\n context.set(\"sourcelist\",sourcelist);\n break;\n case \"aggrsource\":\n if (msg.payload===\"delete\") {\n // remove all previous data sources\n aggrlist = [];\n } else {\n aggrlist = context.get(\"aggrlist\");\n if (aggrlist===undefined) { // if running for the first time\n aggrlist = [];\n }\n aggrlist.push(msg.payload);\n }\n context.set(\"aggrlist\",aggrlist);\n break;\n case \"minus1w\":\n fromdate = fromdate-p_7d;\n enddate = enddate-p_7d;\n context.set(\"fromdate\",fromdate);\n context.set(\"enddate\",enddate);\n break;\n case \"plus1w\":\n fromdate = fromdate+p_7d;\n enddate = enddate+p_7d;\n context.set(\"fromdate\",fromdate);\n context.set(\"enddate\",enddate);\n break;\n case \"minus1d\":\n fromdate = fromdate-p_1d;\n enddate = enddate-p_1d;\n context.set(\"fromdate\",fromdate);\n context.set(\"enddate\",enddate);\n break;\n case \"plus1d\":\n fromdate = fromdate+p_1d;\n enddate = enddate+p_1d;\n context.set(\"fromdate\",fromdate);\n context.set(\"enddate\",enddate);\n break;\n}\n\n\n// Regenerate the SQL statements\n// Run through the data source list an generate the SQL statements\nsql = [];\nif (sourcelist.length>0) {\n for (var i = 0; i < sourcelist.length; i++) {\n var parts = sourcelist[i].split(\"/\");\n sql.push({ topic: \"SELECT * FROM sensor_data WHERE device='\"+parts[0]+\"' AND sensor='\"+parts[1]+\"' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n }\n} \nif (aggrlist.length>0) {\n for (var i = 0; i < aggrlist.length; i++) {\n var parts = aggrlist[i].split(\"/\");\n sql.push({ topic: \"SELECT * FROM sensor_aggr WHERE device='\"+parts[0]+\"' AND sensor='\"+parts[1]+\"' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n }\n} \nif (sql.length===0) { \n // Dummy select that returns nothing to clear the chart\n sql.push({ topic: \"SELECT * FROM sensor_data WHERE device='xxxx'\" });\n}\n\n// set the completed flag for the join node later\nsql[sql.length-1].complete=true;\n// pass along the email flag to redirect the flow later\nif (msg.topic===\"email\") {\n sql[sql.length-1].email=true;\n}\n\n// Generate report title\nif (sourcelist.length===0 && aggrlist.length===0) {\n title = \"No data source\";\n} else {\n if (sourcelist.length!==0) {\n title = title + sourcelist.toString()+ \", \";\n }\n if (aggrlist.length!==0) {\n title = title + aggrlist.toString()+ \", \";\n }\n title = title.substring(0,title.length-2);\n title = title + \" | \";\n\n var d = new Date();\n d.setTime(fromdate);\n var yyyy = d.getFullYear();\n var mm = d.getMonth() < 9 ? \"0\" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based\n var dd = d.getDate() < 10 ? \"0\" + d.getDate() : d.getDate();\n var hh = d.getHours() < 10 ? \"0\" + d.getHours() : d.getHours();\n var mmm = d.getMinutes() < 10 ? \"0\" + d.getMinutes() : d.getMinutes();\n var ss = d.getSeconds() < 10 ? \"0\" + d.getSeconds() : d.getSeconds();\n title = title + dd + \".\" + mm + \".\" + yyyy;\n d.setTime(enddate);\n yyyy = d.getFullYear();\n mm = d.getMonth() < 9 ? \"0\" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based\n dd = d.getDate() < 10 ? \"0\" + d.getDate() : d.getDate();\n hh = d.getHours() < 10 ? \"0\" + d.getHours() : d.getHours();\n mmm = d.getMinutes() < 10 ? \"0\" + d.getMinutes() : d.getMinutes();\n ss = d.getSeconds() < 10 ? \"0\" + d.getSeconds() : d.getSeconds();\n title = title + \" - \" + dd + \".\" + mm + \".\" + yyyy;\n}\nsql[sql.length-1].title=title;\n\nreturn [ sql ];\n\n","outputs":1,"noerr":0,"x":363.75,"y":554.25,"wires":[["5e720daf.769fd4"]]},{"id":"5e720daf.769fd4","type":"sqlite","z":"15e1f6a1.aeab19","mydb":"1c25415d.b8427f","name":"DB","x":503.75,"y":554.25,"wires":[["c20b5c42.f420f"]]},{"id":"cb92ae57.09059","type":"ui_chart","z":"15e1f6a1.aeab19","name":"Chart","group":"31f14418.dc50dc","order":2,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":"dd HH:mm","interpolate":"linear","nodata":"","ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"604800","cutout":"","colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"x":999.4166946411133,"y":593.4999485015869,"wires":[[],[]]},{"id":"ec068ef5.79225","type":"function","z":"15e1f6a1.aeab19","name":"Chart Prep","func":"var msg2 = [];\n\nif (msg.payload[0].length>0) {\n // this is the logic when there are multiple data sets are received\n for (var i=0; i<msg.payload.length; i++) {\n var output = [];\n for (var j=0; j<msg.payload[i].length; j++) {\n output.push([msg.payload[i][j].epoch, msg.payload[i][j].value]);\n }\n msg2.push({ key: msg.payload[i][0].device+\"/\"+msg.payload[i][0].sensor, values : output});\n //msg2.push({ key: \"test\", values : output});\n }\n} \n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;","outputs":1,"noerr":0,"x":826.5834121704102,"y":593.8333225250244,"wires":[["cb92ae57.09059"]]},{"id":"9964baf6.331138","type":"inject","z":"15e1f6a1.aeab19","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":524.4999923706055,"y":651.2500095367432,"wires":[["af278fff.e1d3f"]]},{"id":"af278fff.e1d3f","type":"function","z":"15e1f6a1.aeab19","name":"Empty payload","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":772.4166641235352,"y":650.5833234786987,"wires":[["cb92ae57.09059"]]},{"id":"c20b5c42.f420f","type":"join","z":"15e1f6a1.aeab19","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":636.75,"y":554.25,"wires":[["a6b7ba9c.5094e8","ec068ef5.79225"]]},{"id":"81965321.61cc2","type":"ui_dropdown","z":"15e1f6a1.aeab19","name":"Data source","label":"","group":"9d5d0e0a.e0772","order":1,"width":"5","height":"1","passthru":false,"options":[{"label":"[Remove all]","value":"delete","type":"str"},{"label":"Miflora temperature","value":"miflora/temp","type":"str"},{"label":"Miflora battery level","value":"miflora/battery","type":"str"},{"label":"Miflora sunlight","value":"miflora/sunlight","type":"str"},{"label":"Miflora soil moisture","value":"miflora/moisture","type":"str"},{"label":"Miflora soil fertility","value":"miflora/fertility","type":"str"},{"label":"Solar power","value":"growatt/power","type":"str"},{"label":"Solar voltage","value":"growatt/voltage","type":"str"}],"payload":"","topic":"datasource","x":534,"y":367,"wires":[["5bcca26f.6bfcbc"]]},{"id":"37ad97f5.526bd8","type":"ui_dropdown","z":"15e1f6a1.aeab19","name":"Period","label":"","group":"9d5d0e0a.e0772","order":4,"width":"4","height":"1","passthru":false,"options":[{"label":"Today","value":"today","type":"str"},{"label":"Yesterday","value":"yesterday","type":"str"},{"label":"This week","value":"thisweek","type":"str"},{"label":"Last week","value":"lastweek","type":"str"},{"label":"Last 24 hours","value":"last24h","type":"str"},{"label":"Last 7 days","value":"last7d","type":"str"},{"label":"Last 30 days","value":"last30d","type":"str"}],"payload":"","topic":"period","x":513,"y":410,"wires":[["5bcca26f.6bfcbc"]]},{"id":"61b56f57.0a0f7","type":"inject","z":"15e1f6a1.aeab19","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":true,"x":325,"y":350,"wires":[["81965321.61cc2","37ad97f5.526bd8","443fe9e5.bfd078"]]},{"id":"7cd4216b.bbb1b","type":"ui_button","z":"15e1f6a1.aeab19","name":"","group":"9d5d0e0a.e0772","order":5,"width":"2","height":"1","label":"-1W","color":"","bgcolor":"","icon":"chevron_left","payload":"","payloadType":"str","topic":"minus1w","x":95,"y":343,"wires":[["385704d6.4dd9fc","5bcca26f.6bfcbc"]]},{"id":"c550a633.1ceea8","type":"ui_button","z":"15e1f6a1.aeab19","name":"","group":"9d5d0e0a.e0772","order":6,"width":"2","height":"1","label":"-1D","color":"","bgcolor":"","icon":"chevron_left","payload":"","payloadType":"str","topic":"minus1d","x":93,"y":380,"wires":[["385704d6.4dd9fc","5bcca26f.6bfcbc"]]},{"id":"653b1b47.607e54","type":"ui_button","z":"15e1f6a1.aeab19","name":"","group":"9d5d0e0a.e0772","order":8,"width":"2","height":"1","label":"+1W","color":"","bgcolor":"","icon":"chevron_right","payload":"","payloadType":"str","topic":"plus1w","x":96,"y":418,"wires":[["385704d6.4dd9fc","5bcca26f.6bfcbc"]]},{"id":"b49dcc31.1d03f","type":"ui_button","z":"15e1f6a1.aeab19","name":"","group":"9d5d0e0a.e0772","order":7,"width":"2","height":"1","label":"+1D","color":"","bgcolor":"","icon":"chevron_right","payload":"","payloadType":"str","topic":"plus1d","x":94,"y":455,"wires":[["385704d6.4dd9fc","5bcca26f.6bfcbc"]]},{"id":"385704d6.4dd9fc","type":"change","z":"15e1f6a1.aeab19","name":"Reset","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":323,"y":410,"wires":[["37ad97f5.526bd8"]]},{"id":"443fe9e5.bfd078","type":"ui_dropdown","z":"15e1f6a1.aeab19","name":"Aggregate source","label":"","group":"9d5d0e0a.e0772","order":2,"width":"5","height":"1","passthru":false,"options":[{"label":"[Remove all]","value":"delete","type":"str"},{"label":"Miflora temperature Max","value":"miflora/temp_max","type":"str"},{"label":"Miflora battery level Min","value":"miflora/battery_min","type":"str"},{"label":"Miflora soil moisture","value":"miflora/moisture","type":"str"},{"label":"Solar power Max","value":"growatt/power_max","type":"str"},{"label":"Solar daily generation","value":"growatt/today","type":"str"}],"payload":"","topic":"aggrsource","x":553,"y":325,"wires":[["5bcca26f.6bfcbc"]]},{"id":"a6b7ba9c.5094e8","type":"change","z":"15e1f6a1.aeab19","name":"Title","rules":[{"t":"set","p":"payload","pt":"msg","to":"title","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":843.2500152587891,"y":506.25000953674316,"wires":[["c0da0d06.fc4fe"]]},{"id":"c0da0d06.fc4fe","type":"ui_text","z":"15e1f6a1.aeab19","group":"31f14418.dc50dc","order":1,"width":"0","height":"0","name":"Chart title","label":"","format":"{{msg.payload}}","layout":"row-center","x":995.2500152587891,"y":506.25000953674316,"wires":[]},{"id":"cc92346b.705a48","type":"ui_template","z":"15e1f6a1.aeab19","group":"a7e2730.babc99","name":"Trend","order":3,"width":"4","height":"3","format":"<div layout=\"row\" layout-align=\"space-between\">\n <table width=\"100%\" height=\"100%\">\n <tr>\n <td colspan=\"2\">{{msg.title}}</td>\n </tr>\n <tr>\n <td rowspan=\"2\" style=\"text-align: center;\"><span style=\"font-size: 300%; font-weight: bold;\">{{msg.current}}</span><br/>{{msg.unit}}</td>\n <td style=\"text-align: center; font-size: 300%; font-weight: bold;\" ng-style=\"{color: msg.trend === 'up' ? 'green' : 'red'}\">{{(msg.trend === 'up') ? '⇧' : '⇩'}}</td>\n </tr>\n <tr>\n <td style=\"text-align: center; font-style: italic;\">{{msg.reference}}</td>\n </tr>\n </table>\n</div>","storeOutMessages":false,"fwdInMessages":false,"x":909.5,"y":815.2500152587891,"wires":[[]]},{"id":"20534bd3.4d4ce4","type":"inject","z":"15e1f6a1.aeab19","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":142.5,"y":816.2500152587891,"wires":[["a5e70941.a031e8"]]},{"id":"a5e70941.a031e8","type":"function","z":"15e1f6a1.aeab19","name":"SQL","func":"var p_30d = 1000*60*60*24*30 ; //30 Days\nvar p_7d = 1000*60*60*24*7 ; //7 Days\nvar p_1d = 1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n\nfromdate = today0h - p_30d;\nenddate = today0h;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\nfromdate = fromdate - p_30d;\nenddate = enddate - p_30d;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\n// set the completed flag for the join node later\nsql[sql.length-1].complete=true;\n\nreturn [ sql ];","outputs":1,"noerr":0,"x":308.5,"y":816.2500152587891,"wires":[["f09ccb4f.de86a8"]]},{"id":"f09ccb4f.de86a8","type":"sqlite","z":"15e1f6a1.aeab19","mydb":"1c25415d.b8427f","name":"DB","x":453.5,"y":815.2500152587891,"wires":[["96c84720.d7c3d8"]]},{"id":"96c84720.d7c3d8","type":"join","z":"15e1f6a1.aeab19","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":586.5,"y":815.2500152587891,"wires":[["45c2e2fd.ef494c"]]},{"id":"45c2e2fd.ef494c","type":"function","z":"15e1f6a1.aeab19","name":"Prep Data","func":"// Output format for the ui_template node\n// msg.current: current values displayed in large numbers\n// msg.unit: unit value shown under the current value\n// msg.trend = up|down: displays the up/down trend arrow\n// msg.reference: reference/past value displayed in smaller numbers\n// msg.title: KPI title shown on the top\n\nmsg.title = \"Solar generation in last 30 days\";\nmsg.current = Math.floor(msg.payload[0][0].value/1000);\nmsg.unit = \"kWh\";\nmsg.reference = Math.floor(msg.payload[1][0].value/1000);\nif (msg.payload[0][0].value>msg.payload[1][0].value) {\n msg.trend = \"up\";\n} else {\n msg.trend = \"down\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":748.5,"y":815.2500152587891,"wires":[["cc92346b.705a48"]]},{"id":"e4f4f09c.c649a","type":"ui_template","z":"15e1f6a1.aeab19","group":"68451cd2.bb5e44","name":"Trend","order":3,"width":"4","height":"3","format":"<div layout=\"row\" layout-align=\"space-between\">\n <table width=\"100%\" height=\"100%\">\n <tr>\n <td colspan=\"2\">{{msg.title}}</td>\n </tr>\n <tr>\n <td rowspan=\"2\" style=\"text-align: center;\"><span style=\"font-size: 300%; font-weight: bold;\">{{msg.current}}</span><br/>{{msg.unit}}</td>\n <td style=\"text-align: center; font-size: 300%; font-weight: bold;\" ng-style=\"{color: msg.trend === 'up' ? 'green' : 'red'}\">{{(msg.trend === 'up') ? '⇧' : '⇩'}}</td>\n </tr>\n <tr>\n <td style=\"text-align: center; font-style: italic;\">{{msg.reference}}</td>\n </tr>\n </table>\n</div>","storeOutMessages":false,"fwdInMessages":false,"x":907.5,"y":867.2500152587891,"wires":[[]]},{"id":"b92511c2.dcdb9","type":"inject","z":"15e1f6a1.aeab19","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":140.5,"y":868.2500152587891,"wires":[["c702b1ec.7630d"]]},{"id":"c702b1ec.7630d","type":"function","z":"15e1f6a1.aeab19","name":"SQL","func":"var p_30d = 1000*60*60*24*30 ; //30 Days\nvar p_7d = 1000*60*60*24*7 ; //7 Days\nvar p_1d = 1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n\nfromdate = today0h - p_7d;\nenddate = today0h;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\nfromdate = fromdate - p_7d;\nenddate = enddate - p_7d;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\n// set the completed flag for the join node later\nsql[sql.length-1].complete=true;\n\nreturn [ sql ];","outputs":1,"noerr":0,"x":306.5,"y":868.2500152587891,"wires":[["30ab7eb5.ac6d92"]]},{"id":"30ab7eb5.ac6d92","type":"sqlite","z":"15e1f6a1.aeab19","mydb":"1c25415d.b8427f","name":"DB","x":451.5,"y":867.2500152587891,"wires":[["135e1ba7.37e084"]]},{"id":"135e1ba7.37e084","type":"join","z":"15e1f6a1.aeab19","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":584.5,"y":867.2500152587891,"wires":[["39dbca89.dc7cf6"]]},{"id":"39dbca89.dc7cf6","type":"function","z":"15e1f6a1.aeab19","name":"Prep Data","func":"// Output format for the ui_template node\n// msg.current: current values displayed in large numbers\n// msg.unit: unit value shown under the current value\n// msg.trend = up|down: displays the up/down trend arrow\n// msg.reference: reference/past value displayed in smaller numbers\n// msg.title: KPI title shown on the top\n\nmsg.title = \"Solar generation in last 7 days\";\nmsg.current = parseFloat(msg.payload[0][0].value/1000).toFixed(1);\nmsg.unit = \"kWh\";\nmsg.reference = Math.abs(Math.floor((msg.payload[0][0].value-msg.payload[1][0].value)/msg.payload[0][0].value*100)) + \"%\";\nif (msg.payload[0][0].value>msg.payload[1][0].value) {\n msg.trend = \"up\";\n} else {\n msg.trend = \"down\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":746.5,"y":867.2500152587891,"wires":[["e4f4f09c.c649a"]]},{"id":"1eafe5de.0e410a","type":"ui_template","z":"15e1f6a1.aeab19","group":"3a578850.88b108","name":"Trend","order":3,"width":"4","height":"3","format":"<div layout=\"row\" layout-align=\"space-between\">\n <table width=\"100%\" height=\"100%\" style=\"border-collapse: collapse;\">\n <tr>\n <td colspan=\"2\">{{msg.title}}</td>\n </tr>\n <tr>\n <td rowspan=\"2\" style=\"text-align: center;\"><span style=\"font-size: 300%; font-weight: bold;\">{{msg.current}}</span><br/>{{msg.unit}}</td>\n <td style=\"text-align: center; font-size: 300%; font-weight: bold;\" ng-style=\"{color: msg.trend === 'up' ? 'green' : 'red'}\"> </td>\n </tr>\n <tr>\n <td style=\"text-align: center; font-style: italic;\">{{msg.reference}}</td>\n </tr>\n <tr style=\"background-color: lightgrey;\">\n <td width={{msg.reference}} style=\"border-bottom: 6px solid blue;\"></td><td width={{1-msg.reference}}></td>\n </tr>\n </table>\n</div>","storeOutMessages":false,"fwdInMessages":false,"x":777.5,"y":922.2500152587891,"wires":[[]]},{"id":"4170ba3b.43ec44","type":"inject","z":"15e1f6a1.aeab19","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":145.5,"y":923.2500152587891,"wires":[["ecf97f61.f592"]]},{"id":"ecf97f61.f592","type":"function","z":"15e1f6a1.aeab19","name":"SQL","func":"var p_30d = 1000*60*60*24*30 ; //30 Days\nvar p_7d = 1000*60*60*24*7 ; //7 Days\nvar p_1d = 1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n\nfromdate = today0h-p_1d;\nmsg.topic= \"SELECT * FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch = \" + fromdate + \";\";\n\nreturn msg;","outputs":1,"noerr":0,"x":311.5,"y":923.2500152587891,"wires":[["cdb78fba.a23c9"]]},{"id":"cdb78fba.a23c9","type":"sqlite","z":"15e1f6a1.aeab19","mydb":"1c25415d.b8427f","name":"DB","x":456.5,"y":922.2500152587891,"wires":[["403c4a4e.c920c4"]]},{"id":"403c4a4e.c920c4","type":"function","z":"15e1f6a1.aeab19","name":"Prep Data","func":"// Output format for the ui_template node\n// msg.current: current values displayed in large numbers\n// msg.unit: unit value shown under the current value\n// msg.trend = up|down: displays the up/down trend arrow\n// msg.reference: reference/past value displayed in smaller numbers\n// msg.title: KPI title shown on the top\n\nmsg.title = \"Solar generation yesterday vs. max\";\nmsg.current = parseFloat(msg.payload[0].value/1000).toFixed(2);\nmsg.unit = \"kWh\";\nmsg.reference = Math.abs(Math.floor(msg.payload[0].value/20000*100)) + \"%\";\n\nreturn msg;","outputs":1,"noerr":0,"x":616.5,"y":922.2500152587891,"wires":[["1eafe5de.0e410a"]]},{"id":"1c25415d.b8427f","type":"sqlitedb","z":"","db":"/home/pi/sqlite/nodered"},{"id":"31f14418.dc50dc","type":"ui_group","z":"","name":"Report","tab":"401d066a.62ae48","order":2,"disp":false,"width":"24"},{"id":"9d5d0e0a.e0772","type":"ui_group","z":"","name":"Filters","tab":"401d066a.62ae48","disp":true,"width":"24"},{"id":"a7e2730.babc99","type":"ui_group","z":"","name":"Solar 30 days","tab":"1bb3770b.b9a7c9","order":1,"disp":false,"width":"4"},{"id":"68451cd2.bb5e44","type":"ui_group","z":"","name":"Solar 7 days","tab":"1bb3770b.b9a7c9","order":2,"disp":false,"width":"4"},{"id":"3a578850.88b108","type":"ui_group","z":"","name":"Solar target","tab":"1bb3770b.b9a7c9","order":3,"disp":false,"width":"4"},{"id":"401d066a.62ae48","type":"ui_tab","z":"","name":"Report (New)","icon":"event_note","order":12},{"id":"1bb3770b.b9a7c9","type":"ui_tab","z":"","name":"KPI","icon":"trending_up","order":13}] |