Script to parse Electricity from P1 Smart Meter reading [Solved]

[waaren]

For a week very big usage etc.. So perhaps something i can change???
I did not set anything else to other value, only update domoticz...

Because of the new way the internal updateDevice function is used in domoticz, a change has to be made in dzVents scripts using incremental counters.
I already posted an update for this functionality in this topic

[Derik]

@waaren thanks

[Hcroij]

Thx for the script, took me a while to implement it because I am not familiar with programming etc.
Finally found out I used the wrong kind of Virtual Censor.

But never to old to learn.

1 Question remains ... Now i got about 6 "Counters"low, high"(dag, Nacht) etc.
I really would like to add those 2 togheter something like :

Total Usage = Low Usage + High Usage
Total Delivery = Low Del. + High Del.
I am using the ScriptVersion = '0.1.8'

If this is basic knowledge... pls redirect me.

thx in advance.

[waaren]

I really would like to add those 2 togheter something like :

Total Usage = Low Usage + High Usage
Total Delivery = Low Del. + High Del.

There are quite a number of variations of this script flying around on this forum even with the same version number. So to ensure that a solution for this will work for your script, please share the script that you use now. Also helpful if you tell the device names / -types / -subtypes of the devices to receive the Total usage and Total delivery.

[Hcroij]

Thx for the fast response

As you can see i am not very familiar with the forum,

Hope this "knutsel"will do the trick for you

Knipsel.JPG (66.02 KiB) Viewed 1713 times

Code: Select all

--  dzVents script to Parse P1 Smart Meter Electricity value into separate Meter Readings.

local fetchIntervalMins = 5    -- (Integer) Minutes frequence of this script execution 1 = every minute, 10 = every 10 minutes, etc ) must be one of (1,2,3,4,5,6,10,12,15,20,30)
local ScriptVersion = '0.1.8'

return {

    on =      {
                        timer = { 'every ' .. fetchIntervalMins .. ' Minutes' }
              },
              
   logging = {
                        -- level = domoticz.LOG_DEBUG,    -- Uncomment this line to override the dzVents global logging setting
                        marker = 'SME '.. ScriptVersion
              },


    execute = function(dz, item)

    --  The following need updated for your environment get the 'Idx' or 'Name' of the Device tab.
        local P1data  = 211                                 -- Electra, P1 Smart Meter device
        local idxu1   = 214                                 -- Meter Usage low, Virtual device, counter incremental
        local idxu2   = 215                                 -- Meter Usage High, Virtual device, counter incremental
        local idxr1   = 216                                 -- Meter Return Low, Virtual device, counter incremental
        local idxr2   = 217                                 -- Meter Return High, Virtual device, counter incremental
        local idxcons = 218                                -- Meter Actual Usage, Virtual device, counter incremental
        local idxprod = 219                                -- Meter Actual Production, Virtual device, counter incremental
        
        -- Get values from device P1Data of the Smart Meter
        local SMdata = dz.devices(P1data)
  
        local function updateCounter(idx,value)   
            dz.devices(idx).updateCounter(value)
            dz.log("Set " .. dz.devices(idx).name .. " to: ",dz.LOG_DEBUG)
        end  

        -- Update the device and Debug meassages with the accessory values from table SMdata
        updateCounter(idxu1,SMdata.usage1)
        updateCounter(idxu2,SMdata.usage2)
        updateCounter(idxr1,SMdata.return1)
        updateCounter(idxr2,SMdata.return2)
        updateCounter(idxcons,SMdata.usage)
        updateCounter(idxprod,SMdata.usageDelivered)
    end 
}

thx again
Henk

[waaren]

As you can see i am not very familiar with the forum,

You can copy / paste text in your post. Select it and press the </> button at the top of the edit window. That will place the text / code in a code window.

dzVents script to Parse P1 Smart Meter Electricity value into separate Meter Readings.

You will have to add two virtual (incremental counter) devices like the ones you already added and replace the xxx in below script with the idx or 'names' of these new devices and copy / paste this script to replace your current script.
That should do it

Code: Select all

--  dzVents script to Parse P1 Smart Meter Electricity value into separate Meter Readings.

local fetchIntervalMins = 5    -- (Integer) Minutes frequence of this script execution 1 = every minute, 10 = every 10 minutes, etc ) must be one of (1,2,3,4,5,6,10,12,15,20,30)
local ScriptVersion = '0.1.9'

return {

    on =      {
                        timer = { 'every ' .. fetchIntervalMins .. ' Minutes' }
              },
              
   logging = {
                        level = domoticz.LOG_DEBUG,    -- Uncomment this line to override the dzVents global logging setting
                        marker = 'SME '.. ScriptVersion
              },


    execute = function(dz, item)

    --  The following need updated for your environment get the Idx or 'Name' of the Device tab.
        local P1data  = 211   -- Electra, P1 Smart Meter device
        local idxu1   = 214   -- Meter Usage low, Virtual device, counter incremental
        local idxu2   = 215   -- Meter Usage High, Virtual device, counter incremental
        local idxr1   = 216   -- Meter Return Low, Virtual device, counter incremental
        local idxr2   = 217   -- Meter Return High, Virtual device, counter incremental
        local idxcons = 218   -- Meter Actual Usage, Virtual device, counter incremental
        local idxprod = 219   -- Meter Actual Production, Virtual device, counter incremental
        
        local idxuTotal = xxx -- Meter Usage low, Virtual device, counter incremental
        local idxrTotal = xxx -- Meter returnlow, Virtual device, counter incremental

        
        -- Get values from device P1Data of the Smart Meter
        local SMdata = dz.devices(P1data)
  
        local function updateCounter(idx,value)   
            dz.devices(idx).updateCounter(value)
            dz.log("Set " .. dz.devices(idx).name .. " to: ",dz.LOG_DEBUG)
        end  

        -- Update the device and Debug meassages with the accessory values from table SMdata
        updateCounter(idxu1,SMdata.usage1)
        updateCounter(idxu2,SMdata.usage2)
        updateCounter(idxr1,SMdata.return1)
        updateCounter(idxr2,SMdata.return2)
        updateCounter(idxcons,SMdata.usage)
        updateCounter(idxprod,SMdata.usageDelivered)
        
        updateCounter(idxuTotal,( SMdata.usage1 + SMdata.usage2 ))
        updateCounter(idxrTotal,( SMdata.return1 + SMdata.return2 ))
        
        
    end 
}

[Hcroij]

Thank you for the fast response,
Working like a charm.

Groeten
Henk

[waaren]

I would like to read out the daily total values. Is this possible ??

daily total can be viewed in the log page. Or do you mean something else ?

[JuanUil]

Hi Waaren,

Nice script but I miss something:
I would like to know my net consumption ic.
when I have used 5 kWh and produced 2 kWh then the net consumption is right because the meter is given exactly what I have used.
When I have used 5 kWh and produced 10 kWh then the net consumption should be 5-10=-5 kWh

can we put this in a counter as well or do we need to measure this at the end of the day and store it in a textdevice or so?

thnx for any help

Jan

[waaren]

can we put this in a counter as well or do we need to measure this at the end of the day and store it in a textdevice or so?

Counters can be updated with positive and negative values but if the total becomes negative you will see strange big positive numbers because of the used number type in the underlying code.
If you are going to use the calculated net consumption (positive or negative) for display purposes only then I would use a custom-sensor or a text-sensor.
Happy to help if you let me know what your intended use is.

[JuanUil]

Hi Waaren,

Thnx for your reply.
My aim is to fill my excel sheet with the net usage, positive or negative.
I download the dbvalues with a script you made for me.
So if I have a sensor or textdevice which shows me te net consumpion on a day I will be verry happy.
If possible written in the dbase.

Thnx for your help

jan

[waaren]

My aim is to fill my excel sheet with the net usage, positive or negative.
I download the dbvalues with a script you made for me.

Understand.
May I suggest an approach where you use a customSensor for display purposes and your smartmeter device for exporting to the csv file? You can then easily do the calculation of net usage in Excel.
The issue with exporting history of a textdevice is that the history of text devices are not kept for a long time and the issue with exporting history of customDevices is that these only keep min, max and avg values over a day and not the values at the end of a day so you will not get the results that you are looking for.

The script to dump the domoticz values to a csv file can be extended quite quickly to include the values of your smartmeter.

[JuanUil]

Thnx Waaren,

I will work in excell for the time being and try to find another solution in the future.

[waaren]

I will work in excel for the time being and try to find another solution in the future.

Do you already dump the domoticz smartmeter data to the CSV file or do you need help with that?

[JuanUil]

No didn't do that yet, could use some help.
What if I update a counter at 23:55 u with the calculated value of usage minus deliverance if deliverance is higher than usage and with usage if deliverance is lower than usage?

Jan

[waaren]

No didn't do that yet, could use some help.
What if I update a counter at 23:55 u with the calculated value of usage minus deliverance if deliverance is higher than usage and with usage if deliverance is lower than usage?

Jan

Sorry but I miss your point here. I don't think this will show your net consumption.

What is the deviceID of your smart meter?
Can you share the script you now use for the creation of the csv file? I will add the part for the smart meter.

[JuanUil]

Hi Waaren,

idx = 918

Code: Select all

        Dumps daily data as csv file 

        requires sqlite3 
            install command on linux:    sudo apt install sqlite3 
            install command on openwrt:  opkg install sqlite3-cli
            install command on synology: sudo /opt/bin/opkg install sqlite3-cli

            History:
            20191231 first public release  
            20200102 Add sudo chmod to make csvfile readable for all
]]--

return 
{
    on =
    {  
        timer =
        {
            "at 17:25",
        },
    },
    
    logging =   
    {  
        level = domoticz.LOG_DEBUG,
        marker    =   "sqliteDump" 
    },

    execute = function(dz)
    
    -- =======================  enter you settings below this line ==================
        
        local path = "/home/pi/domoticz/"     -- full qualified path to your domoticz directory
        local database = "domoticz.db"    -- database filename + extension
        local sqlite = "/usr/bin/sqlite3" -- location of your sqlite3 tool -- which sqlite3 
        local allTemperatures = "131" -- comma separated list of temperature devices to export
        local allUsages = "44" -- comma separated list 
        local allSolar = "48" -- comma separated list 
        local csvFile = "/home/pi/domoticz2excel.csv"
        
        -- =======================  No modification needed below this line ==================
        
        local copy = "safeCopy.db"  
  
        local baseCommand = "sudo " .. sqlite .. " -header -csv " .. copy 
        local closure     = " >> "  .. csvFile
        local collects = {} 

        collects.meters = 'select a.id as meter, a.name as naam, b.Value as value, b.counter as counter from devicestatus a , meter_calendar b where a.id = b.devicerowid and a.id in (' .. allUsages .. ',' .. allSolar ..');'
        collects.temperatures = 'select a.id as temperature, a.name as naam, b.temp_min as min, b.temp_max as max, b.temp_avg as avg, b.date as date from devicestatus a , temperature_calendar b where a.id = b.devicerowid and a.id in (' .. allTemperatures .. ') order by a.id;'

        local function osCommand(cmd)
            dz.log('Executing Command: ' .. cmd,dz.LOG_DEBUG)

            local fileHandle = assert(io.popen(cmd .. ' 2>&1 || echo ::ERROR::', 'r'))
            local commandOutput = assert(fileHandle:read('*a'))
            local returnTable = {fileHandle:close()}

            if commandOutput:find '::ERROR::' then     -- something went wrong
            dz.log('Error ==>> ' .. tostring(commandOutput:match('^(.*)%s+::ERROR::') or ' ... but no error message ' ) ,dz.LOG_DEBUG)
            else -- all is fine!!
                dz.log('ReturnCode: ' .. returnTable[3] .. '\ncommandOutput:\n' .. commandOutput, dz.LOG_DEBUG)
            end

            return commandOutput,returnTable[3] -- rc[3] contains returnCode

        end

        local function sqliteDump()
            local result, rc
            if dz.utils.fileExists(path .. database) then
                if dz.utils.fileExists(sqlite) then
                    result, rc  = osCommand('cp '  .. path .. database .. ' ' .. copy)
                    if rc ~= 0 then return rc, result end
                    result, rc  = osCommand('rm -f '  .. csvFile)
                    if rc ~= 0 then return rc, result end
                    for type, sql in pairs (collects) do
                        result, rc  = osCommand(baseCommand .. ' "' .. sql .. '" ' .. closure)
                        if rc ~= 0 then return rc, result end
                    end
                    result, rc  = osCommand('rm -f '  .. copy)
                    if rc ~= 0 then return rc, result end
                    result, rc  = osCommand('sudo chmod 777 '  .. csvFile)
                    if rc ~= 0 then return rc, result end
                else
                    return -1,"sqlite3 not installed / not found"
                end
            else
                return -1,"wrong path to database"
            end
            return 0, commandOutput
        end
    
        -- main program
        local rc, result = sqliteDump()
        if rc ~= 0 then dz.log(result,dz.LOG_ERROR) end
    end
}

What I ment was:
When we extract the total usage and total production from the smart meter, do the calculation of the net usage and then update a dummy counter with this value at 23:55 uur, would that work do you think?

[waaren]

When we extract the total usage and total production from the smart meter, do the calculation of the net usage and then update a dummy counter with this value at 23:55 uur, would that work do you think?

Only when you do this at 23:50 latest and only if you are 100% sure you have a positive result.

Can you try with amended script?

Code: Select all

--[[
        Dumps daily data as csv file

        requires sqlite3
            install command on linux:    sudo apt install sqlite3
            install command on openwrt:  opkg install sqlite3-cli
            install command on synology: sudo /opt/bin/opkg install sqlite3-cli

            History:
            20191231 first public release
            20200102 Add sudo chmod to make csvfile readable for all
            20200608 Add multimeters (for smartmeter)

]]--

return
{
    on =
    {
        timer =
        {
            "at 17:25",
        },
    },

    logging =
    {
        level = domoticz.LOG_DEBUG,
        marker    =   "sqliteDump"
    },

    execute = function(dz)

    -- =======================  enter you settings below this line ==================

        local path = "/home/pi/domoticz/"     -- full qualified path to your domoticz directory
        local database = "domoticz.db"    -- database filename + extension
        local sqlite = "/usr/bin/sqlite3" -- location of your sqlite3 tool -- which sqlite3
        local allTemperatures = "131" -- comma separated list of temperature devices to export
        local allUsages = "44" -- comma separated list
        local allSolar = "48" -- comma separated list
        local allMultiMeters = "918"
        local csvFile = "/home/pi/domoticz2excel.csv"

        -- =======================  No modification needed below this line ==================

        local copy = "safeCopy.db"

        local baseCommand = "sudo " .. sqlite .. " -header -csv " .. copy
        local closure     = " >> "  .. csvFile
        local collects = {}

        collects.meters = 'select a.id as meter, a.name as naam, b.Value as value, b.counter as counter, from devicestatus a , meter_calendar b where a.id = b.devicerowid and a.id in (' .. allUsages .. ',' .. allSolar ..');'
        collects.temperatures = 'select a.id as temperature, a.name as naam, b.temp_min as min, b.temp_max as max, b.temp_avg as avg, b.date as date from devicestatus a , temperature_calendar b where a.id = b.devicerowid and a.id in (' .. allTemperatures .. ') order by a.id;'
        collects.multiMeters = 'select a.id as smartMeter, a.name as naam, b.counter1 as consumedLow, b.counter2 as producedLow, b.counter3 as consumedHigh, b.counter2 as producedHigh,  b.date as date from devicestatus a , multimeter_calendar b where a.id = b.devicerowid and a.id in (' .. allMultiMeters .. ') order by b.date desc;'

        local function osCommand(cmd)
            dz.log('Executing Command: ' .. cmd,dz.LOG_DEBUG)

            local fileHandle = assert(io.popen(cmd .. ' 2>&1 || echo ::ERROR::', 'r'))
            local commandOutput = assert(fileHandle:read('*a'))
            local returnTable = {fileHandle:close()}

            if commandOutput:find '::ERROR::' then     -- something went wrong
            dz.log('Error ==>> ' .. tostring(commandOutput:match('^(.*)%s+::ERROR::') or ' ... but no error message ' ) ,dz.LOG_DEBUG)
            else -- all is fine!!
                dz.log('ReturnCode: ' .. returnTable[3] .. '\ncommandOutput:\n' .. commandOutput, dz.LOG_DEBUG)
            end

            return commandOutput,returnTable[3] -- rc[3] contains returnCode

        end

        local function sqliteDump()
            local result, rc
            if dz.utils.fileExists(path .. database) then
                if dz.utils.fileExists(sqlite) then
                    result, rc  = osCommand('cp '  .. path .. database .. ' ' .. copy)
                    if rc ~= 0 then return rc, result end
                    result, rc  = osCommand('rm -f '  .. csvFile)
                    if rc ~= 0 then return rc, result end
                    for type, sql in pairs (collects) do
                        result, rc  = osCommand(baseCommand .. ' "' .. sql .. '" ' .. closure)
                        if rc ~= 0 then return rc, result end
                    end
                    result, rc  = osCommand('rm -f '  .. copy)
                    if rc ~= 0 then return rc, result end
                    result, rc  = osCommand('sudo chmod 777 '  .. csvFile)
                    if rc ~= 0 then return rc, result end
                else
                    return -1,"sqlite3 not installed / not found"
                end
            else
                return -1,"wrong path to database"
            end
            return 0, commandOutput
        end

        -- main program
        local rc, result = sqliteDump()
        if rc ~= 0 then dz.log(result,dz.LOG_ERROR) end
    end
}

[JuanUil]

Hi Waaren,

sorry for my late response but I am on a businesstrip.
I will try your code this weekend.
Thnx again.

[JuanUil]

Hi Waaren,

Installed it today but I get this error.

Code: Select all

2020-06-14 11:29:08.634 Error: dzVents: Error: (3.0.9) error loading module 'Slimme meter ophalen' from file '/home/pi/domoticz/scripts/dzVents/generated_scripts/Slimme meter ophalen.lua':
2020-06-14 11:29:08.634 ...ripts/dzVents/generated_scripts/Slimme meter ophalen.lua:1: syntax error near 'daily'

Jan