New dynamic SQL chart and trend tile
This flow in an enhanced version of the dynamic SQL chart flow I did for an earlier video (https://youtu.be/ccKspiI8FRw). This works from the same SQL database, and it is capable of dynamically generating charts in the dashboard with multiple data series. I have redesigned the user interface and how the report works to provide a better functionality and make it is easier to use. You can easily add data series to the chart, change the time frame and easily move back and forward in time by a day or week.
I also created some more UI elements (using dashboard ui-template node) to display data in context with previous/reference values.
There is a very long video explaining the details of the code in case you want to customize or adapt it for your DB structure: https://youtu.be/nkKf26oKzhQ
[{"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}]
