Node Red ui til Homeseer

[Terje Klungervik]

Noen som har laget UI til homeseer med Node Red som er villige til å dele flowene? Har nettopp startet å se på Node Red og det ser ut til at en kan lage mye finere UI enn det HSTouch kan.

[SveinHa]

Begynte i går kveld å leke litt med node-red-contrib-tv men ikke hatt tid til å få gang på noe vettigt enda... Liker egentlig ikke designet så godt men det er nå en start...

[SveinHa]

Ga opp det forsøket mitt og startet med Node-Red Dashboard i stedet. Har bare snekret et par kladdebilder enda men ser en liten utfordring. Når bildet ikke har vært vist på en stund tar det evigheter (=opptil flere minutt) før trendobjektene oppdateres. Eksempel et halvminutt eller så etter siden vises:

En stund senere:

Beklager hvis jeg kupper tråden din @Terje Klungervik men jeg tenker å komme med noe mer matnyttig etterhvert...

 

[Terje Klungervik]

Det går helt greit. Hvis jeg forstår det riktig så kan vi designe dashboardet som vi vil hvis vi har kunnskapene. Har sett noen eksempler på veldig fine dashboards. Men finner lite templates. Må jobbe mer med forståelsen tror jeg.

[SveinHa]

Det ser for meg ut som et ganske strengt rutenett der hver enkelt objekt grupperes i en eller flere kolonner og rekkefølgen av objektene og rekkefølgen av kolonnene kan modifiseres i /View/Dashboard menyen. Hvert enkelt objekt kan endres i bredde men beholder høyde/bredde forholdet. Med dette som utgangspunkt så har en jo en del begrensninger men får også en veldig konsistent utseende. Fargepalette er Light, Dark og Custom så en kan jo trikse en god del på den siden og.

 

Mye roteprogrammering men her er flowen min for bildet over så langt, henter data fra både HS4 og MQTT:

[{"id":"bd6892582b896458","type":"tab","label":"HMI","disabled":false,"info":"Node Red UI"},{"id":"2b070f1c4f123439","type":"inject","z":"bd6892582b896458","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"10","crontab":"","once":false,"onceDelay":0.1,"topic":"sync","x":90,"y":480,"wires":[["68aeab8d8cc0a568","24c96b0a09e51a79","f4628910ddbd0842"]]},{"id":"b0bd0bee704ca2a2","type":"mqtt in","z":"bd6892582b896458","name":"","topic":"Pool/TempOutside","qos":"2","datatype":"auto","broker":"6db118ed1b0c56de","nl":false,"rap":true,"rh":0,"x":110,"y":740,"wires":[["8f6820eaab18ff56","50722e1696144c01"]]},{"id":"8f6820eaab18ff56","type":"ui_chart","z":"bd6892582b896458","name":"","group":"394dcffd2bbde9fc","order":2,"width":0,"height":0,"label":"TempOutside","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":890,"y":720,"wires":[[]]},{"id":"447d83713bc15202","type":"mqtt in","z":"bd6892582b896458","name":"","topic":"Pool/TempWater","qos":"2","datatype":"auto","broker":"6db118ed1b0c56de","nl":false,"rap":true,"rh":0,"x":100,"y":620,"wires":[["181b1ea5b01130aa"]]},{"id":"059b0a959ff9cc50","type":"mqtt in","z":"bd6892582b896458","name":"","topic":"Pool/TempDome","qos":"2","datatype":"auto","broker":"6db118ed1b0c56de","nl":false,"rap":true,"rh":0,"x":100,"y":680,"wires":[["f63728a2287667ec"]]},{"id":"f8b50f685fa55795","type":"ui_chart","z":"bd6892582b896458","name":"","group":"45fc21be8d63c5ff","order":3,"width":0,"height":0,"label":"TempOutside","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1100,"y":740,"wires":[[]]},{"id":"6a43f2652264b1ab","type":"ui_chart","z":"bd6892582b896458","name":"","group":"47a266d7202a6d2f","order":1,"width":0,"height":0,"label":"TempWater","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"25","ymax":"35","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1100,"y":660,"wires":[[]]},{"id":"7007c23f63671f43","type":"ui_chart","z":"bd6892582b896458","name":"","group":"45fc21be8d63c5ff","order":2,"width":0,"height":0,"label":"TempDome","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1100,"y":700,"wires":[[]]},{"id":"24c96b0a09e51a79","type":"hs-device","z":"bd6892582b896458","name":"Uteområde Pool Pool VP Aeon Labs Switch Pool VP W","device":"581","server":"8cea2f7d775df12f","feature":"583","reportonstartup":false,"x":560,"y":460,"wires":[["e27f2d3db5e9f6f9"]]},{"id":"873e9689c2ac0a83","type":"ui_gauge","z":"bd6892582b896458","name":"","group":"b31303479769b0ef","order":3,"width":0,"height":0,"gtype":"donut","title":"VP W","label":"W","format":"{{value}}","min":0,"max":"3500","colors":["#00b500","#e6e600","#ca3838"],"seg1":"2500","seg2":"3000","className":"","x":1250,"y":460,"wires":[]},{"id":"80cabf4d730c69e9","type":"hs-device","z":"bd6892582b896458","name":"Uteområde Pool Pool VP Aeon Labs Switch Pool VP Switch","device":"581","server":"8cea2f7d775df12f","feature":"587","reportonstartup":false,"x":300,"y":120,"wires":[["97a96b7eb024e484"]]},{"id":"2c12c58c597facb6","type":"ui_switch","z":"bd6892582b896458","name":"","label":"VP SW","tooltip":"","group":"b31303479769b0ef","order":3,"width":0,"height":0,"passthru":false,"decouple":"true","topic":"payload.control.status","topicType":"str","style":"","onvalue":"1","onvalueType":"num","onicon":"","oncolor":"","offvalue":"0","offvalueType":"num","officon":"","offcolor":"","animate":false,"className":"","x":780,"y":120,"wires":[["a0309c8e844301eb"]]},{"id":"26ea40c467b040f9","type":"ui_chart","z":"bd6892582b896458","name":"","group":"47a266d7202a6d2f","order":3,"width":0,"height":0,"label":"VP W","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"3500","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1080,"y":620,"wires":[[]]},{"id":"e27f2d3db5e9f6f9","type":"function","z":"bd6892582b896458","name":"Extract Value","func":"var svar = \"\";\nvar value=msg.payload.value;\nsvar = {payload:value.toFixed(0)};\nreturn svar;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":890,"y":460,"wires":[["0b0d4068d4db0acd","6e6db3683ec2140b"]]},{"id":"bcb299f99d8057c2","type":"ui_gauge","z":"bd6892582b896458","name":"","group":"b31303479769b0ef","order":3,"width":0,"height":0,"gtype":"donut","title":"Filtersirk","label":"W","format":"{{value}}","min":0,"max":"500","colors":["#ffff00","#73d216","#ca3838"],"seg1":"330","seg2":"400","className":"","x":1090,"y":520,"wires":[]},{"id":"a98903487fdc51b7","type":"function","z":"bd6892582b896458","name":"Extract Value","func":"var svar = \"\";\nvar value=msg.payload.value;\nsvar = {payload:value.toFixed(0)};\nreturn svar;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":890,"y":520,"wires":[["bcb299f99d8057c2","87e6ed5553c1cfea"]]},{"id":"68aeab8d8cc0a568","type":"hs-device","z":"bd6892582b896458","name":"Uteområde Pool Pool Filter Fibaro Switch Pool Filter Power","device":"1117","server":"8cea2f7d775df12f","feature":"1122","reportonstartup":true,"x":580,"y":520,"wires":[["a98903487fdc51b7"]]},{"id":"0b0d4068d4db0acd","type":"show-value","z":"bd6892582b896458","name":"","path":"","x":920,"y":620,"wires":[["26ea40c467b040f9"]]},{"id":"50722e1696144c01","type":"show-value","z":"bd6892582b896458","name":"","path":"","x":290,"y":740,"wires":[["f8b50f685fa55795","c26e44f1ef456505"]]},{"id":"6e6db3683ec2140b","type":"show-value","z":"bd6892582b896458","name":"","path":"","x":1090,"y":460,"wires":[["873e9689c2ac0a83"]]},{"id":"48c4e3aebcd831c6","type":"change","z":"bd6892582b896458","name":"Av","rules":[{"t":"set","p":"topic","pt":"msg","to":"control","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"{\"status\":\"OFF\"}","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1070,"y":140,"wires":[["64e07103c1553251"]]},{"id":"17e31f8813594c62","type":"change","z":"bd6892582b896458","name":"På","rules":[{"t":"set","p":"topic","pt":"msg","to":"control","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"{\"status\":\"ON\"}","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1070,"y":100,"wires":[["64e07103c1553251"]]},{"id":"64e07103c1553251","type":"json","z":"bd6892582b896458","name":"","property":"payload","action":"","pretty":false,"x":1210,"y":120,"wires":[["80cabf4d730c69e9"]]},{"id":"a0309c8e844301eb","type":"switch","z":"bd6892582b896458","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"true","repair":true,"outputs":2,"x":930,"y":120,"wires":[["17e31f8813594c62"],["48c4e3aebcd831c6"]]},{"id":"8152c53f411495af","type":"ui_chart","z":"bd6892582b896458","name":"","group":"47a266d7202a6d2f","order":3,"width":0,"height":0,"label":"Filter W","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"500","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1080,"y":560,"wires":[[]]},{"id":"87e6ed5553c1cfea","type":"show-value","z":"bd6892582b896458","name":"","path":"","x":910,"y":560,"wires":[["8152c53f411495af"]]},{"id":"f63728a2287667ec","type":"show-value","z":"bd6892582b896458","name":"","path":"","x":290,"y":680,"wires":[["7007c23f63671f43"]]},{"id":"181b1ea5b01130aa","type":"show-value","z":"bd6892582b896458","name":"","path":"","x":290,"y":620,"wires":[["6a43f2652264b1ab"]]},{"id":"eb838059898d3fdc","type":"hs-device","z":"bd6892582b896458","name":"Uteområde Pool Pool Filter Fibaro Switch Pool Filter Switch","device":"1117","server":"8cea2f7d775df12f","feature":"1123","reportonstartup":false,"x":300,"y":240,"wires":[["15b7f60e9e8f9a65"]]},{"id":"72e2b0155c15c2a7","type":"ui_switch","z":"bd6892582b896458","name":"","label":"FilterSW","tooltip":"","group":"b31303479769b0ef","order":3,"width":0,"height":0,"passthru":false,"decouple":"true","topic":"payload.control.status","topicType":"str","style":"","onvalue":"1","onvalueType":"num","onicon":"","oncolor":"","offvalue":"0","offvalueType":"num","officon":"","offcolor":"","animate":false,"className":"","x":760,"y":240,"wires":[["b774a2faac9275e6"]]},{"id":"aff52e931bc694c8","type":"change","z":"bd6892582b896458","name":"Av","rules":[{"t":"set","p":"topic","pt":"msg","to":"control","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"{\"status\":\"OFF\"}","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1050,"y":260,"wires":[["c234bf11af45b0a8"]]},{"id":"c234bf11af45b0a8","type":"json","z":"bd6892582b896458","name":"","property":"payload","action":"","pretty":false,"x":1190,"y":240,"wires":[["eb838059898d3fdc"]]},{"id":"e390ae5cb88c5ade","type":"change","z":"bd6892582b896458","name":"På","rules":[{"t":"set","p":"topic","pt":"msg","to":"control","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"{\"status\":\"ON\"}","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1050,"y":220,"wires":[["c234bf11af45b0a8"]]},{"id":"b774a2faac9275e6","type":"switch","z":"bd6892582b896458","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"true","repair":true,"outputs":2,"x":890,"y":240,"wires":[["e390ae5cb88c5ade"],["aff52e931bc694c8"]]},{"id":"f4628910ddbd0842","type":"hs-device","z":"bd6892582b896458","name":"Bolig Kjøkken Beveg Kjøk Aeon Labs Multilevel Sensor Beveg Kjøk Temperature","device":"83","server":"8cea2f7d775df12f","feature":"87","reportonstartup":true,"x":440,"y":340,"wires":[["33af4e4f3bf9e3b9"]]},{"id":"33af4e4f3bf9e3b9","type":"function","z":"bd6892582b896458","name":"Extract Value","func":"var svar = \"\";\nvar value=msg.payload.value;\nsvar = {payload:value.toFixed(1)};\nreturn svar;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":890,"y":340,"wires":[["9774175638722593"]]},{"id":"9774175638722593","type":"show-value","z":"bd6892582b896458","name":"","path":"","x":690,"y":380,"wires":[["d0dad447410a790e","b109a4a06c64234c"]]},{"id":"d0dad447410a790e","type":"ui_chart","z":"bd6892582b896458","name":"","group":"ab0b4b6271303101","order":2,"width":0,"height":0,"label":"Temp Kjøkken","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":880,"y":380,"wires":[[]]},{"id":"15b7f60e9e8f9a65","type":"function","z":"bd6892582b896458","name":"Extract Value","func":"var svar = \"\";\nvar value=msg.payload.status;\nif (value == \"On\") {\nsvar = {payload:1};\nreturn svar}\nif (value == \"Off\") {\nsvar = {payload:0};\nreturn svar}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":610,"y":240,"wires":[["72e2b0155c15c2a7"]]},{"id":"5e2be42900ee5080","type":"inject","z":"bd6892582b896458","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"10","crontab":"","once":false,"onceDelay":0.1,"topic":"sync","x":90,"y":180,"wires":[["80cabf4d730c69e9","eb838059898d3fdc"]]},{"id":"97a96b7eb024e484","type":"function","z":"bd6892582b896458","name":"Extract Value","func":"var svar = \"\";\nvar value=msg.payload.status;\nif (value == \"On\") {\nsvar = {payload:1};\nreturn svar}\nif (value == \"Off\") {\nsvar = {payload:0};\nreturn svar}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":630,"y":120,"wires":[["2c12c58c597facb6"]]},{"id":"b109a4a06c64234c","type":"ui_text","z":"bd6892582b896458","group":"ab0b4b6271303101","order":1,"width":0,"height":0,"name":"","label":"Temp kjøkken","format":"{{msg.payload}}","layout":"col-center","className":"","x":880,"y":420,"wires":[]},{"id":"c26e44f1ef456505","type":"ui_text","z":"bd6892582b896458","group":"394dcffd2bbde9fc","order":1,"width":0,"height":0,"name":"","label":"Utetemperatur","format":"{{msg.payload}}","layout":"col-center","className":"","x":900,"y":760,"wires":[]},{"id":"6db118ed1b0c56de","type":"mqtt-broker","name":"DaleMQTT","broker":"172.16.0.94","port":"1883","clientid":"","usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"394dcffd2bbde9fc","type":"ui_group","name":"Trends","tab":"e3ef94435f0945bf","order":2,"disp":false,"width":"6","collapse":false,"className":""},{"id":"45fc21be8d63c5ff","type":"ui_group","name":"PoolTrends","tab":"b91fa54f1d7b5859","order":3,"disp":false,"width":"5","collapse":false,"className":""},{"id":"47a266d7202a6d2f","type":"ui_group","name":"PoolTrend2","tab":"b91fa54f1d7b5859","order":2,"disp":false,"width":"5","collapse":false,"className":""},{"id":"8cea2f7d775df12f","type":"hs-server","name":"HS4","host":"172.16.0.102","port":"80"},{"id":"b31303479769b0ef","type":"ui_group","name":"Values","tab":"b91fa54f1d7b5859","order":1,"disp":false,"width":"4","collapse":false,"className":""},{"id":"ab0b4b6271303101","type":"ui_group","name":"Default","tab":"e3ef94435f0945bf","order":1,"disp":false,"width":"6","collapse":false,"className":""},{"id":"e3ef94435f0945bf","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false},{"id":"b91fa54f1d7b5859","type":"ui_tab","name":"Pool","icon":"dashboard","order":2,"disabled":false,"hidden":false}]

 

[SveinHa]

Har putlet litt mer med NodeRed Dashboard og laget meg 2-3 bilder som gir meg god oversikt over energiforbruket i huset. Først de største forbrukerene med PID regulator, krampesparing i dag med all-time-high strømpris så langt, blir verre i morgen:

 

Kun PID for litt god oversikt for tuning:

 

Og til sist: Henting og visning av loggede data fra SQL:

Endret 20. desember 2021 av SveinHa

[Kvamsdal]

Ser ut som du har taket på dette med node red 🙂

 

Hvilken node har du brukt mellom MQTT og CHART nodene i flowen din?

[SveinHa]

Hele greiene ser slik ut:

Øverst lagres timeverdier i SQL og lengre nede leses SQL og data presenteres i både chart og table.

 

[{"id":"43c98426301432b6","type":"tibber-data","z":"d49bfe3335c3f7c9","name":"TodaysEnergyPrice","active":true,"apiEndpointRef":"3235237809bb894f","queryName":"getCurrentEnergyPrice","homeId":"4ac606d5-70bb-4b43-9cf8-6f5f867c00a8","energyResolution":"DAILY","lastCount":10,"x":450,"y":1260,"wires":[["06cc550f61d82e1c"]]},{"id":"0b858dc8e357231d","type":"change","z":"d49bfe3335c3f7c9","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"kWh_prev_h","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":1100,"wires":[["5c11f9028d78108f"]],"info":"{\"a\":$timestamp,\"b\":$flow.kWh_prev_h}"},{"id":"6736cd76640d17bd","type":"cronplus","z":"d49bfe3335c3f7c9","name":"Hourly","outputField":"payload","timeZone":"","persistDynamic":false,"commandResponseMsgOutput":"output1","outputs":1,"options":[{"name":"schedule1","topic":"schedule1","payloadType":"date","payload":"","expressionType":"cron","expression":"0 0 * * * * *","location":"","offset":"0","solarType":"all","solarEvents":"sunrise,sunset"}],"x":50,"y":1100,"wires":[["533368a5de56eede"]]},{"id":"533368a5de56eede","type":"delay","z":"d49bfe3335c3f7c9","name":"","pauseType":"delay","timeout":"3","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":260,"y":1080,"wires":[["0b858dc8e357231d","6b8d58f5ed2d5568"]]},{"id":"ad8fc9fae833b54e","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":980,"y":1100,"wires":[[]]},{"id":"ec65187082b5f759","type":"function","z":"d49bfe3335c3f7c9","name":"Generate SQL","func":"var kWh_T =flow.get(\"kWh_accT\")|| 0;\nvar MP =flow.get(\"MaxPower\")|| 0;\nvar CP =flow.get(\"CurrentPrice\")|| 0;\nvar PL =flow.get(\"PriceLevel\")|| \"-\";\nvar SP =flow.get(\"PID_SP\")|| 0;\nvar verdi = msg.payload.toFixed(4);\nvar dato = Date.now();\n\nmsg.payload={}\nmsg.payload.dat=dato;\nmsg.payload.verd=verdi;\nmsg.payload.mp=MP;\nmsg.payload.cp=CP;\nmsg.payload.pl=PL;\nmsg.payload.sp=SP;\nmsg.payload.kwht=kWh_T;\n\n\n\nmsg.topic = \"INSERT INTO Energi (kWh_hour, MaxPower, CurrentPrice, kWh_Tibber, PriceLevel, PID_SP) \";\nmsg.topic += \"VALUES ( :verd, :mp, :cp, :kwht, :pl, :sp );\"\n//msg.payload = [dato, verdi];\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":1100,"wires":[["ad8fc9fae833b54e"]]},{"id":"5c11f9028d78108f","type":"json","z":"d49bfe3335c3f7c9","name":"","property":"payload","action":"obj","pretty":false,"x":610,"y":1100,"wires":[["ec65187082b5f759"]]},{"id":"1c92d25c775f08c8","type":"comment","z":"d49bfe3335c3f7c9","name":"kWh timeverdi til SQL og .log","info":"","x":160,"y":1040,"wires":[]},{"id":"49ad13fd1b34421d","type":"ui_table","z":"d49bfe3335c3f7c9","group":"188f564c824c87cd","name":"","order":3,"width":"10","height":"12","columns":[],"outputs":0,"cts":false,"x":970,"y":1480,"wires":[]},{"id":"858128e9e731109a","type":"function","z":"d49bfe3335c3f7c9","name":"kWh i dag","func":"var d = new Date();\nd.setDate(d.getDate() - 1);\nvar pd = new Date();\npd.setDate(pd.getDate() - 2);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i dag', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi WHERE Timestamp >= \" + dbts;\nmsg.topic = \"SELECT MID(Timestamp,9,4) AS 'I dag', PID_SP AS 'SP', ROUND(kWh_Tibber,2) AS kWh, ROUND(CurrentPrice,2) AS Pris, ROUND((CurrentPrice*kWh_Tibber), 2) AS 'Kr/h', PriceLevel AS 'Lvl' FROM Energi \";\nmsg.topic += \"WHERE Timestamp >= \" + dbts;\nmsg.topic += \" ORDER BY DatoTid ASC;\"\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.topic = \"SELECT DatoTid, kWh_hour FROM Energi WHERE Timestamp >= \" + dbts;\nmsg.topic += \" ORDER BY DatoTid ASC;\"\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":1480,"wires":[["39201e04cc076738"]]},{"id":"39201e04cc076738","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1480,"wires":[["49ad13fd1b34421d"]]},{"id":"c8ac7ae53d71c179","type":"inject","z":"d49bfe3335c3f7c9","name":"","props":[{"p":"payload"}],"repeat":"300","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":110,"y":1600,"wires":[["858128e9e731109a","fe19627f283b8bba","4d769e24aced283a","5573440bcd0ffaf6","debe875237227809","abaab949ce52216b","86ab4360.50c6c","43c98426301432b6","078aa7c160ce1cd4"]]},{"id":"25aca45ef9a7d242","type":"ui_table","z":"d49bfe3335c3f7c9","group":"188f564c824c87cd","name":"","order":4,"width":"10","height":"12","columns":[],"outputs":0,"cts":false,"x":970,"y":1520,"wires":[]},{"id":"fe19627f283b8bba","type":"function","z":"d49bfe3335c3f7c9","name":"kWh i går","func":"\nvar d = new Date();\nd.setDate(d.getDate() - 1);\nvar pd = new Date();\npd.setDate(pd.getDate() - 2);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i går', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,9,4) AS 'I går', PID_SP AS 'SP', ROUND(kWh_Tibber,2) AS kWh, ROUND(CurrentPrice,2) AS Pris, ROUND((CurrentPrice*kWh_Tibber), 2) AS 'Kr/h', PriceLevel AS 'Lvl' FROM Energi \";\nmsg.topic += \"WHERE Timestamp >= \" + pdbts + \" AND Timestamp < \" + dbts;\nmsg.topic += \" ORDER BY DatoTid ASC;\"\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":1520,"wires":[["94fc9523796f3ac4"]]},{"id":"94fc9523796f3ac4","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1520,"wires":[["25aca45ef9a7d242"]]},{"id":"d205c5ce.1feca8","type":"ui_chart","z":"d49bfe3335c3f7c9","name":"","group":"188f564c824c87cd","order":1,"width":"22","height":"6","label":"kWh/h + pris (31d)","chartType":"line","legend":"true","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"3","removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#3465a4","#cc0000","#cc0000","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":930,"y":1440,"wires":[[]]},{"id":"86ab4360.50c6c","type":"function","z":"d49bfe3335c3f7c9","name":"Siste 31d","func":"var timeE = msg.payload;\n//Restrict the query to pull the last 24hrs\n//of data instead of the whole db\nvar d=new Date();\nd.setDate(d.getDate() - 31);\nvar aar=d.getFullYear();\nvar mnd=d.getMonth()+1; //Month er 0-11\nvar dag=d.getDate();\nvar tim=d.getHours();\n\nts = (aar*10000000000)+(mnd*100000000)+(dag*1000000)+(tim*10000);\n//msg.payload = (timeE - (1000*60*60*24));\nmsg.payload = ts;\n    node.status({text:msg.payload});\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":1420,"wires":[["308dd6b1.2a193a"]]},{"id":"308dd6b1.2a193a","type":"template","z":"d49bfe3335c3f7c9","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT DatoTid, kWh_hour, CurrentPrice FROM Energi WHERE Timestamp > {{payload}}","output":"str","x":500,"y":1440,"wires":[["736965c2c176b373"]]},{"id":"a03b0066.3ff5a","type":"change","z":"d49bfe3335c3f7c9","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t  $series := [\t    { \"field\": \"kWh_hour\", \"label\": \"kWh\" },\t    { \"field\": \"CurrentPrice\", \"label\": \"Pris\" }\t  ];\t  $xaxis := \"DatoTid\";\t  [\t    {\t      \"series\": $series.label,\t      \"data\": $series.[\t        (\t          $yaxis := $.field;\t          $$.payload.{\t            \"x\": $lookup($, $xaxis),\t            \"y\": $lookup($, $yaxis)\t          }\t        )\t      ]\t    }\t  ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":790,"y":1400,"wires":[["d205c5ce.1feca8"]]},{"id":"3016d0bf3923098b","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":700,"y":1440,"wires":[["a03b0066.3ff5a"]]},{"id":"736965c2c176b373","type":"function","z":"d49bfe3335c3f7c9","name":"Add dummy payload","func":"msg.payload = {\"Whatever\":\"0\"};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":1400,"wires":[["3016d0bf3923098b"]]},{"id":"6b8d58f5ed2d5568","type":"delay","z":"d49bfe3335c3f7c9","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":120,"y":1520,"wires":[["858128e9e731109a","fe19627f283b8bba","86ab4360.50c6c","4d769e24aced283a","43c98426301432b6","5573440bcd0ffaf6","debe875237227809","abaab949ce52216b","078aa7c160ce1cd4","0aca93d88df1f214"]]},{"id":"06cc550f61d82e1c","type":"show-value","z":"d49bfe3335c3f7c9","name":"","path":"","x":630,"y":1260,"wires":[["aafe6aae4f3432b7","fb78792335b9cd22"]]},{"id":"aafe6aae4f3432b7","type":"function","z":"d49bfe3335c3f7c9","name":"Lagre CurrentPrice i flow","func":"var cp = msg.payload.total;\n//cp=109.2;\nflow.set(\"CurrentPrice\",cp);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":870,"y":1260,"wires":[[]]},{"id":"ae1e3d2e03e57cb0","type":"ui_table","z":"d49bfe3335c3f7c9","group":"188f564c824c87cd","name":"","order":6,"width":"10","height":"12","columns":[],"outputs":0,"cts":false,"x":970,"y":1560,"wires":[]},{"id":"4d769e24aced283a","type":"function","z":"d49bfe3335c3f7c9","name":"kWh i forgårs","func":"\nvar d = new Date();\nd.setDate(d.getDate() - 2);\nvar pd = new Date();\npd.setDate(pd.getDate() - 3);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\n\nmsg.payload={}\n\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,9,4) AS 'Forgårs', PID_SP AS 'SP', ROUND(kWh_Tibber,2) AS kWh, ROUND(CurrentPrice,2) AS Pris, ROUND((CurrentPrice*kWh_Tibber), 2) AS 'Kr/h', PriceLevel AS 'Lvl' FROM Energi \";\nmsg.topic += \"WHERE Timestamp >= \" + pdbts + \" AND Timestamp < \" + dbts;\nmsg.topic += \" ORDER BY DatoTid ASC;\"\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":1560,"wires":[["39dd34e67f532e2d"]]},{"id":"39dd34e67f532e2d","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1560,"wires":[["ae1e3d2e03e57cb0"]]},{"id":"35e1c998ea014c70","type":"ui_table","z":"d49bfe3335c3f7c9","group":"188f564c824c87cd","name":"Kost/mnd","order":2,"width":"10","height":"3","columns":[],"outputs":0,"cts":false,"x":960,"y":1680,"wires":[]},{"id":"12e2682ce6d2f9c9","type":"function","z":"d49bfe3335c3f7c9","name":"kWh/kost pr mnd","func":"var d = new Date();\nlet hours = d.getHours();\nlet minutes = d.getMinutes();\nlet seconds = d.getSeconds();\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate();\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);//+(hours*10000);\nlet dbtsy = dbts - 1000000;\nlet dbtsfy = dbtsy - 1000000;\n\nmsg.payload={}\n\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT (LEFT(Timestamp,6)/100) AS 'År/Mnd', ROUND(SUM(kWh_Tibber),2) AS kWh, ROUND(SUM(kWh_Tibber * Currentprice),2) AS Kost \";\nmsg.topic += \"FROM Energi \";\n//msg.topic += \"WHERE Timestamp >= \" + dbtsfy + \" AND Timestamp < \" + dbtsy;\nmsg.topic += \"GROUP BY MID(Timestamp,5,2)\";\nmsg.topic += \"ORDER BY Timestamp DESC\";\n//msg.topic += ')'\nreturn msg;\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":570,"y":1680,"wires":[["f842943e2e9e0ff8"]]},{"id":"f842943e2e9e0ff8","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1680,"wires":[["35e1c998ea014c70"]]},{"id":"5573440bcd0ffaf6","type":"delay","z":"d49bfe3335c3f7c9","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":340,"y":1680,"wires":[["12e2682ce6d2f9c9","ac1bed130080554f"]]},{"id":"633df0a5819fbe5b","type":"comment","z":"d49bfe3335c3f7c9","name":"Hent energidata fra SQL til Dashboard","info":"","x":170,"y":1240,"wires":[]},{"id":"65e7d0fdb19c8dea","type":"ui_table","z":"d49bfe3335c3f7c9","group":"188f564c824c87cd","name":"","order":7,"width":"10","height":"12","columns":[],"outputs":0,"cts":false,"x":970,"y":1600,"wires":[]},{"id":"debe875237227809","type":"function","z":"d49bfe3335c3f7c9","name":"kWh i -3","func":"var d = new Date();\nd.setDate(d.getDate() - 3);\nvar pd = new Date();\npd.setDate(pd.getDate() - 4);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,9,4) AS 'D-3', PID_SP AS 'SP', ROUND(kWh_Tibber,2) AS kWh, ROUND(CurrentPrice,2) AS Pris, ROUND((CurrentPrice*kWh_Tibber), 2) AS 'Kr/h', PriceLevel AS 'Lvl' FROM Energi \";\nmsg.topic += \"WHERE Timestamp >= \" + pdbts + \" AND Timestamp < \" + dbts;\nmsg.topic += \" ORDER BY DatoTid ASC;\"\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":1600,"wires":[["37297c6dd1887fc7"]]},{"id":"37297c6dd1887fc7","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1600,"wires":[["65e7d0fdb19c8dea"]]},{"id":"8974b41bb8d851a8","type":"ui_table","z":"d49bfe3335c3f7c9","group":"188f564c824c87cd","name":"","order":8,"width":"10","height":"12","columns":[],"outputs":0,"cts":false,"x":970,"y":1640,"wires":[]},{"id":"abaab949ce52216b","type":"function","z":"d49bfe3335c3f7c9","name":"kWh i -4","func":"var d = new Date();\nd.setDate(d.getDate() - 4);\nvar pd = new Date();\npd.setDate(pd.getDate() - 5);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,9,4) AS 'D-4', PID_SP AS 'SP', ROUND(kWh_Tibber,2) AS kWh, ROUND(CurrentPrice,2) AS Pris, ROUND((CurrentPrice*kWh_Tibber), 2) AS 'Kr/h', PriceLevel AS 'Lvl' FROM Energi \";\nmsg.topic += \"WHERE Timestamp >= \" + pdbts + \" AND Timestamp < \" + dbts;\nmsg.topic += \" ORDER BY DatoTid ASC;\"\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":1640,"wires":[["1e38006ef2584640"]]},{"id":"1e38006ef2584640","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1640,"wires":[["8974b41bb8d851a8"]]},{"id":"fb78792335b9cd22","type":"function","z":"d49bfe3335c3f7c9","name":"Lagre PriceLevel i flow","func":"var cp = msg.payload.level;\n//cp=109.2;\nflow.set(\"PriceLevel\",cp);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":860,"y":1220,"wires":[[]]},{"id":"d0a1b1516d131821","type":"inject","z":"d49bfe3335c3f7c9","name":"","props":[{"p":"payload"}],"repeat":"300","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":110,"y":1720,"wires":[["5573440bcd0ffaf6"]]},{"id":"12c5c8eb488c16af","type":"ui_table","z":"d49bfe3335c3f7c9","group":"188f564c824c87cd","name":"Spart/dag","order":5,"width":"10","height":"9","columns":[],"outputs":0,"cts":false,"x":960,"y":1720,"wires":[]},{"id":"3b3b4a0a5fc87059","type":"function","z":"d49bfe3335c3f7c9","name":"Spart/dag","func":"var d = new Date();\nlet hours = d.getHours();\nlet minutes = d.getMinutes();\nlet seconds = d.getSeconds();\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate();\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);//+(hours*10000);\nlet dbtsy = dbts - 1000000;\nlet dbtsfy = dbtsy - 1000000;\n\nmsg.payload={}\n\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\n//msg.topic = \"SELECT LEFT(Timestamp,8) AS 'Dato', (SUM(CurrentPrice)/COUNT(*) * SUM(kWh_Tibber)/COUNT(*)) * 24 AS 'Snittpris', (SUM(CurrentPrice * kWh_Tibber)/COUNT(*)) * 24 AS 'ReellPri'\";\nmsg.topic = \"SELECT LEFT(Timestamp,8) AS 'Dato',\"\nmsg.topic += \"CAST(SUM(kWh_Tibber) as decimal(6,1)) AS 'kWh',\";\nmsg.topic += \"CAST(SUM(CurrentPrice)/COUNT(*) * SUM(kWh_Tibber)/COUNT(*) * 24 as decimal(10,2)) AS 'Snittpris',\";\nmsg.topic += \"CAST(SUM(CurrentPrice * kWh_Tibber)/COUNT(*) * 24 as decimal(10,2)) AS 'ReellPris',\";\nmsg.topic += \"CAST((SUM(CurrentPrice)/COUNT(*) * SUM(kWh_Tibber)/COUNT(*) * 24) - (SUM(CurrentPrice * kWh_Tibber)/COUNT(*) * 24)  as decimal(10,2)) AS 'Spart'\"\n\nmsg.topic += \"FROM Energi \";\n//msg.topic += \"WHERE Timestamp >= \" + dbtsfy + \" AND Timestamp < \" + dbtsy;\nmsg.topic += \"GROUP BY LEFT(Timestamp,8)\";\nmsg.topic += \"ORDER BY Timestamp DESC\";\n//msg.topic += ')'\nreturn msg;\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":1720,"wires":[["bfad0741bbc43305"]]},{"id":"bfad0741bbc43305","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1720,"wires":[["12c5c8eb488c16af"]]},{"id":"ac1bed130080554f","type":"delay","z":"d49bfe3335c3f7c9","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":400,"y":1720,"wires":[["3b3b4a0a5fc87059"]]},{"id":"7b554e4669b367f9","type":"ui_chart","z":"d49bfe3335c3f7c9","name":"","group":"188f564c824c87cd","order":1,"width":"8","height":"6","label":"kWh/h + pris (2d)","chartType":"line","legend":"true","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"","removeOlder":"3","removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#3465a4","#cc0000","#73d216","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":930,"y":1360,"wires":[[]]},{"id":"078aa7c160ce1cd4","type":"function","z":"d49bfe3335c3f7c9","name":"Siste 2d","func":"var timeE = msg.payload;\n//Restrict the query to pull the last 24hrs\n//of data instead of the whole db\nvar d=new Date();\nd.setDate(d.getDate() - 2);\nvar aar=d.getFullYear();\nvar mnd=d.getMonth()+1; //Month er 0-11\nvar dag=d.getDate();\nvar tim=d.getHours();\n\nts = (aar*10000000000)+(mnd*100000000)+(dag*1000000)+(tim*10000);\n//msg.payload = (timeE - (1000*60*60*24));\nmsg.payload = ts;\n    node.status({text:msg.payload});\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":1340,"wires":[["38c38a3dabaa53dc"]]},{"id":"38c38a3dabaa53dc","type":"template","z":"d49bfe3335c3f7c9","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT DatoTid, kWh_hour, CurrentPrice, PID_SP FROM Energi WHERE Timestamp > {{payload}}","output":"str","x":500,"y":1360,"wires":[["0a3c6d550a427301"]]},{"id":"b6b43742bb23be7f","type":"change","z":"d49bfe3335c3f7c9","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t  $series := [\t    { \"field\": \"kWh_hour\", \"label\": \"kWh\" },\t    { \"field\": \"CurrentPrice\", \"label\": \"Pris\" },\t    { \"field\": \"PID_SP\", \"label\": \"SP\" }\t  ];\t  $xaxis := \"DatoTid\";\t  [\t    {\t      \"series\": $series.label,\t      \"data\": $series.[\t        (\t          $yaxis := $.field;\t          $$.payload.{\t            \"x\": $lookup($, $xaxis),\t            \"y\": $lookup($, $yaxis)\t          }\t        )\t      ]\t    }\t  ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":790,"y":1320,"wires":[["7b554e4669b367f9"]]},{"id":"324130bee0fc15b3","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":700,"y":1360,"wires":[["b6b43742bb23be7f"]]},{"id":"0a3c6d550a427301","type":"function","z":"d49bfe3335c3f7c9","name":"Add dummy payload","func":"msg.payload = {\"Whatever\":\"0\"};\nreturn msg;\n// Fjernet fra Format data\n//    { \"field\": \"DateTime\", \"label\": \"Tid\" },\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":1320,"wires":[["324130bee0fc15b3"]]},{"id":"0aca93d88df1f214","type":"hs-device","z":"d49bfe3335c3f7c9","name":"RedusertEffekt","device":"1762","server":"8cea2f7d775df12f","feature":"1763","reportonstartup":false,"x":440,"y":1060,"wires":[["7c1234ae0ca72d65","a2c5e93546b26393"]]},{"id":"7c1234ae0ca72d65","type":"change","z":"d49bfe3335c3f7c9","name":"Set global.reduserteffekt","rules":[{"t":"set","p":"reduserteffekt","pt":"global","to":"payload.status","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":750,"y":1020,"wires":[[]]},{"id":"337d0b425331896c","type":"comment","z":"d49bfe3335c3f7c9","name":"Set global.reduserteffekt","info":"","x":470,"y":1020,"wires":[]},{"id":"a2c5e93546b26393","type":"change","z":"d49bfe3335c3f7c9","name":"Set global.reduserteffekt_value","rules":[{"t":"set","p":"reduserteffekt_value","pt":"global","to":"payload.value","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":770,"y":1060,"wires":[[]]},{"id":"3235237809bb894f","type":"tibber-api-endpoint","feedUrl":"wss://api.tibber.com/v1-beta/gql/subscriptions","queryUrl":"https://api.tibber.com/v1-beta/gql","name":"Dale"},{"id":"c919f77e07994edc","type":"Stackhero-MySQL-Server","name":"","host":"172.16.0.98","port":"3306","tls":false,"database":"NodeRedEnergi"},{"id":"188f564c824c87cd","type":"ui_group","name":"Chart","tab":"bbe9ae4be8e5f235","order":4,"disp":false,"width":"30","collapse":false,"className":""},{"id":"8cea2f7d775df12f","type":"hs-server","name":"HS4","host":"172.16.0.102","port":"80"},{"id":"bbe9ae4be8e5f235","type":"ui_tab","name":"Energi","icon":"receipt","order":2,"disabled":false,"hidden":false}]