Gå til innhold
  • Bli medlem
Støtt hjemmeautomasjon.no!

Node Red ui til Homeseer


Anbefalte innlegg

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:

bilde.png.bb8d9295d376f743042d00f52ae711eb.png

En stund senere:

bilde.png.d808bd92384a4a73f7e2658477d44131.png

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

 

Lenke til kommentar
Del på andre sider

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}]

 

Lenke til kommentar
Del på andre sider

  • 2 måneder senere...

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:

image.thumb.png.fa256bf35ffaf2746d44846a39ec7741.png

 

Kun PID for litt god oversikt for tuning:

image.thumb.png.66d6de211e79f554b1c8f1e7affba2d9.png

 

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

image.thumb.png.7cbcfa821d75ef1add23c2b93205d131.png

Endret av SveinHa
  • Like 1
Lenke til kommentar
Del på andre sider

  • 2 uker senere...

Hele greiene ser slik ut:

bilde.thumb.png.c739d69deeb888a1e8a97f9e31ce4ddc.png

Ø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}]

 

Lenke til kommentar
Del på andre sider

Bli med i samtalen

Du kan publisere innhold nå og registrere deg senere. Hvis du har en konto, logg inn nå for å poste med kontoen din.

Gjest
Skriv svar til emnet...

×   Du har limt inn tekst med formatering.   Lim inn uten formatering i stedet

  Du kan kun bruke opp til 75 smilefjes.

×   Lenken din har blitt bygget inn på siden automatisk.   Vis som en ordinær lenke i stedet

×   Tidligere tekst har blitt gjenopprettet.   Tøm tekstverktøy

×   Du kan ikke lime inn bilder direkte. Last opp eller legg inn bilder fra URL.

×
×
  • Opprett ny...

Viktig informasjon

Vi har plassert informasjonskapsler/cookies på din enhet for å gjøre denne siden bedre. Du kan justere dine innstillinger for informasjonskapsler, ellers vil vi anta at dette er ok for deg.