Domoticz

Code: Select all

--[[ -- sqliteDump.lua

        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
            20201023 Add option to use MultiMeter
            20201026 Added option to get daily data, and included yesterdays' date to csv filename
]]--

return
{
    on =
    {
        timer =
        {
            --'at 00:04',
            'at 17:34',
        },
        devices =
        {
            'sqlDump',   -- test trigger can be ignored
        },
    },

    logging =
    {
        level = domoticz.LOG_DEBUG, -- set to domoticz.LOG_ERROR when all is OK
        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; check with 'which sqlite3'
        local csvFile = "/media/pi/KINGSTON/Pyranometer/Sunpower_Berkhout" --full path to the csv filename for the dump, without the .csv extension (will be added by script, together with date)

        -- set to '' for the types you don't need

	local allPercentages = '337,589' -- comma separated list of Percentage sensors to export

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

        local csvFile = csvFile .. "_" .. os.date("%Y-%m-%d",os.time()-24*60*60) .. ".csv"
        local copy = 'safeCopy.db'
        local baseCommand = 'sudo ' .. sqlite .. ' -header -csv ' .. copy
        local closure     = ' >> '  .. csvFile
        local collects = {}


        --==Query for minute data
                                
        collects.Percentages =  'SELECT a.ID ID, a.Name Name, b.Percentage Percentage, b.Date Date '.. 
        			' FROM DeviceStatus a , Percentage b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allPercentages .. ')'        


        --==end query minute data

        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 _, 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
}

Code: Select all

return {
	on = {
		timer = {
			'every hour', 
			} 
	},

	execute = function(dz, timer)

 	    local datum         = tostring(os.date('%d-%m-%Y', os.time()))
	    local uur           = tostring(os.date('%H', os.time()))
	    -- round consuption to 2 decimals
	    local gas = string.format("%.2f", dz.devices('Gas').counter)
	    gas = string.gsub(gas, '%.', ',')
	    local elektriciteit = string.format("%.2f", dz.devices('Elektriciteit').counter)
	    elektriciteit = string.gsub(elektriciteit, '%.', ',')

	    local tekst = datum ..';' .. uur .. ';' .. gas .. ';' .. elektriciteit .. '\n'
            -- at 24:00/0:00 write data for 24:00 (the day before) and 0:00
            if uur == '00' then
            datum = tostring(os.date('%d-%m-%Y', os.time()-60*60)) 
	        tekst = datum ..';24;' .. gas .. ';' .. elektriciteit .. '\n' .. tekst
            end

            -- write data to csv-file
	    file = io.open("/mnt/documents/Domoticz/uurverbruik.csv", "a+")
	    file:write(tekst)
            file:close()
    end
}