Domoticz

Posted: **Thursday 18 January 2018 16:26**

To get a better overview of my energy consumption, I have created some hourly, daily, monthly and yearly energy graphs using the Plotly offline functions and python scripts. The graphs are accessible from domoticz using the custom page.

In these examples you can see the consumption of different energy consumers as heatpumps (VPn), heatfloors (VGolv_xx), hot water (VVB), and others as well as the total electrical consumption

In short it works as follows:
A backup of the database is made each hour.
For each graph a python script is executed from the crontab. The script reads data from the backup database via sql and by use of the plotly offline function creates a html-page containing the graph.
The html-page with the graph is made accessible from the Domoticz Custom page

My python script for the hour graph looks as follows. I am not a python expert and this is my first python code.

Code: Select all

#!/usr/bin/python2.7
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys
from time import gmtime, strftime
import datetime

from plotly.offline import download_plotlyjs, plot
from plotly.graph_objs import *


layout = Layout(
  barmode='stack',
  title= 'Energiforbrukning per timme i kWh',
  yaxis=dict(
    title='kWh'
    ),
  yaxis2=dict(
    title='Grader',
#    titlefont=dict(
#      color='rgb(148, 103, 189)'),
    overlaying='y',
    side='right'
    ),
)
totConsumpt   = [0] * 250

startTime = datetime.datetime.now()

con = lite.connect('/home/pi/domoticz/backups/backup-hour.db')

with con:
    
    def getHoursE (idx):
        cur = con.cursor()    

        timeSqlStart = startTime.strftime("%Y-%m-%d") + 'T' + startTime.strftime("%H:%M")

##        text = "SELECT * FROM `Meter` WHERE `DeviceRowID` LIKE  " + str(idx) + "  AND `Date` < '" + timeSqlStart + "'  AND `Date` > '" + timeEnd + "' AND `Date` LIKE '%00:__'  ORDER BY `date` DESC LIMIT 0, 40;"
        text = "SELECT * FROM `Meter` WHERE `DeviceRowID` LIKE  " + str(idx) + "  AND `Date` < '" + timeSqlStart + "'  AND `Date` LIKE '%00:__'  ORDER BY `date` DESC LIMIT 0, 40;"
#        print (text)

        cur.execute(text)
        xrows = cur.fetchall()
        return xrows
    
    
    def getEnergyConsumpt (idx):
        rows = getHoursE(idx) 
        noOfRows = len(rows)
#        print (noOfRows)

        xxx = [None] * noOfRows
        yyy = [None] * noOfRows        
        i = 0
        while (i < noOfRows-1):
            xxx[i] = rows[i+1][2][:13]    #retrieve only hours, not minutes and seconds
            yyy[i] = (rows[i][1] - rows[i+1][1])/1000.0
            totConsumpt[i] = totConsumpt[i] + yyy[i]
            i += 1
        return xxx, yyy

    def getOtherEnergyConsumpt (idx):
        rows = getHoursE(idx) 
        noOfRows = len(rows)
#        print (noOfRows)

        xxx = [None] * noOfRows
        yyy = [None] * noOfRows        
        ttt = [None] * noOfRows
        i = 0
        while (i < noOfRows-1):
            xxx[i] = rows[i+1][2][:13]    #retrieve only hours, not minutes and seconds
            y = (rows[i][1] - rows[i+1][1])/1000.0
            yyy[i] = y - totConsumpt[i]
            if (yyy[i] < 0 ):   # no negative values
                yyy[i] = 0
            ttt[i] = 'E_tot: ' + str(y)
            i += 1
        return xxx, yyy, ttt

    def getTemp (idx):

        cur = con.cursor()    
        timeSqlStart = startTime.strftime("%Y-%m-%d") + 'T' + startTime.strftime("%H:%M")
#       text = "SELECT * FROM `Meter` WHERE `DeviceRowID` LIKE  " + str(idx) + "  AND `Date` < '" + timeSqlStart + "'  AND `Date` LIKE '%00:__'  ORDER BY `date` DESC LIMIT 0, 40;"
        text = "SELECT `DeviceRowID`, `Date`, `Temperature` FROM `Temperature` WHERE `DeviceRowID` LIKE  " + str(idx) + "  AND `Date` < '" + timeSqlStart +  "' AND `Date` LIKE '%00:__'   ORDER BY `Date` DESC LIMIT 0, 40";
#        print (text)

        cur.execute(text)
        rows = cur.fetchall()

        noOfRows = len(rows)
#        print (noOfRows)

        xxx = [None] * noOfRows
        yyy = [None] * noOfRows        
        i = 0
        while (i < noOfRows-1):
            xxx[i] = rows[i+1][1][:13]    #retrieve only hours, not minutes and seconds
            yyy[i] = rows[i+1][2]
            i += 1
#       print (xxx)
#       print (yyy)
        return xxx, yyy



#VP1
xx, yy = getEnergyConsumpt(420)
trace1 = Bar(
    x=xx,
    y=yy,
    name='VP1'
)

#VP2
xx, yy = getEnergyConsumpt(422)
trace2 = Bar(
    x=xx,
    y=yy,
    name='Vp2'
)

#VVB
xx, yy = getEnergyConsumpt(479)
trace3 = Bar(
    x=xx,
    y=yy,
    name='VVB'
)

#Varmegolv Uppe
xx, yy = getEnergyConsumpt(262)
trace4 = Bar(
    x=xx,
    y=yy,
    name='VGolv_Uppe'
)

#Varmegolv Hall
xx, yy = getEnergyConsumpt(467)
trace5 = Bar(
    x=xx,
    y=yy,
    name='VGolv_Hall'
)

#Varmegolv SPA
xx, yy = getEnergyConsumpt(469)
trace6 = Bar(
    x=xx,
    y=yy,
    name='VGolv_SPA'
)

#Varmegolv UteRum
xx, yy = getEnergyConsumpt(468)
trace7 = Bar(
    x=xx,
    y=yy,
    name='VGolv_UteRum'
)

#Other consumpt (subtracted from E_tot) 
xx, yy, tt = getOtherEnergyConsumpt(461)
trace8 = Bar(
    x=xx,
    y=yy,
    text= tt,
    name='Hushallsel'
)


#Ute temp
xx, yy = getTemp(426)
trace9 = Scatter(
    x=xx,
    y=yy,
    name='Ute_Temp',
    yaxis = "y2" 
)


data = [trace1, trace2, trace3, trace4, trace5, trace6, trace7, trace8, trace9]
fig = Figure(data=data, layout=layout)

plot(fig, filename='stacked-bar-hours.html')

# copy file to domoticz
import shutil
shutil.copy2('/home/pi/stacked-bar-hours.html', '/home/pi/domoticz/www/bargraph/stacked-bar-hours.html')

Plotly has a lot of examples how to create different types of graphs https://plot.ly/python/graphing-multiple-chart-types/

Domoticz

Energy graphs using Plotly offline function

Energy graphs using Plotly offline function