Domoticz: LUA script to create SelfConsumption, SelfSufficiency, TotalUsage, TotalProduction devices

The script_time_selfconsumption.lua file, available at https://github.com/CreasolTech/domoticz_lua_scripts , permits to update 5 devices in Domoticz measuring the self-consumption and self-sufficiency of the building, when one or more power sources are available (photovoltaic on the roof, photovoltaic in the garden, wind turbine, ...)

The script will update the following utility meters:

• TotalProduced energy/power:      (the array POWERMETER_GENS in config_power.lua contains the list of meters measuring power for each plant)
• SelfConsumption power:          totalProduced-exportedPower
• TotalConsumption energy/power:   selfConsumption+importedPower
• SelfConsumption percentage:      selfConsumption/TotalProduced
• SelfSufficiency percentage:      selfConsumption/TotalConsumption

Script installation

Enter the GitHub project https://github.com/CreasolTech/domoticz_lua_scripts and download script_time_selfconsumption.lua config_power.lua, globalfunctions.lua and globalvariables.lua into the DOMOTICZ/scripts/lua directory.

The following variables should be defined in config_power.lua:

PowerMeterImport='PowerMeter Import'             -- Meter measuring import power
PowerMeterExport='PowerMeter Export'             -- Meter measuring export power
POWERMETER_GENS={'PV_PowerMeter', 'PV_Garden'}   -- list of devices measuring power from renewable plants (PV on the roof, PV on the garden, wind, ...)

-- The following 5 devices have to be created manually (Setup -> Hardware -> Create virtual device)
POWERMETER_USAGE='Power_Used'                    -- Electric+Counter virtual device (to be created manually)
POWERMETER_PROD='Power_Produced'                 -- Electric+Counter virtual device (to be created manually)
POWERMETER_SELF='Power_SelfConsumption'          -- Electric+Counter virtual device (to be created manually)
PERCENTAGE_SELF='Perc_SelfConsumption'           -- Percentage virtual device (to be created manually)
PERCENTAGE_SUFF='Perc_SelfSufficiency'           -- Percentage virtual device (to be created manually)

Check the domoticz log for any errors, and wait at least 5 minutes to see the new devices charts.

Updating the database domoticz.db

The following operations are NOT RECOMMENDED because CAN DAMAGE THE DATABASE.

If you have months or years of data, and you want to import all data in the new meters, you can get information from the database, save in a CSV file to elaborate with a spreedsheet program, then add them to the database. ALWAYS MAKE A BACKUP OF THE DATABASE before each operation!

In my case, I have two solar photovoltaic systems, a powerImport device and powerExport device measuring the power from grid and power to grid. Domoticz is running in Linux OS. In the following instructions, comments describing each action are rapresented with #text...

cd /home/pi/domoticz   #enter domoticz directory
cp domoticz.db /home/backup/domoticz_202407011753.db   #make a backup of database
sqlite3 domoticz.db   #run sqlite client program
sqlite> .output /tmp/file1.csv
sqlite> SELECT * FROM Meter_Calendar WHERE DeviceRowID=105 ORDER BY Date; #105=idx of first solar meter
sqlite> .output /tmp/file2.csv
sqlite> SELECT * FROM Meter_Calendar WHERE DeviceRowID=1942 ORDER BY Date; #192=idx of second solar meter

Open file1 and file2 with libreoffice or other spreedsheet program, then merge the column by using the same date in the same row, then add a column with the sum of the two energies in each row, and another column with the counter value, as shown in the following picture.

where 2104 is the idx of the new POWERMETER_PROD value (you can get it entering the Utility panel in Domoticz, and clicking on Edit button on that device).

Save the file in /tmp/file3.csv then

sqlite3> .mode csv
sqlite3> .import /tmp/file3.csv Meter_Calendar
sqlite3> UPDATE DeviceStatus SET sValue="0;35732997" WHERE ID=2104;

The second line imports the file data in the existing table with day-by-day data of every meter device, the third line update the meter status with the current counter value. Replace 35732997 with the last couter value in the file3.csv + today energy, in Wh.

Check the POWERMETER_PROD chart: you should see the total produced power in the last days, months or  years.

Now, get exported energy data from the PowerMeterExport device (idx=967 in my case):

sqlite> .output /tmp/fileExport.csv
sqlite> SELECT * FROM Meter_Calendar WHERE DeviceRowID=967 ORDER BY Date;

then open fileExport.csv and compute, for each date, SelfConsumption=ProductionEnergy-ExportedEnergy, and import data with the commands

sqlite> .import /tmp/selfConsumption.csv Meter_Calendar
sqlite> UPDATE DeviceStatus SET sValue="0;13673479" WHERE ID=2100;

where 2100 is the idx of the created POWERMETER_SELF device, and 13673479 is the last counter value + today SelfConsumption energy.

Calculate the selfConsumption_percentage=int(SelfConsumption*100/ProductionEnergy) and export a CSV file with the fields
idx,perc,perc,perc,date

then import in the database with the command

sqlite> .import /tmp/selfConsumption_perc.csv Percentage_Calendar

With percentage device you don't need to update the DeviceStatus table: that's needed only for counters.
Export in a spreedsheet the imported power (from grid), then calculate the TotalUsedEnergy=SelfConsumption+ImportedEnergy, and finally calculate the SelfSufficiency_percentage=int(selfConsumption*100/TotalUsedEnergy); import all data in the same way explained above.