Dutch local covid-19 figures

Moderator: leecollings

User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Dutch local covid-19 figures

Post by waaren »

snuiter wrote: Saturday 15 August 2020 22:45 On the RIVM site there is a JSON file with information on the covid per town. I have tried several thing to read the JSON but I am not able to read it via dzVents. My knowledge on coding is lacking here. Any help is appreciated.

My idea was to filter based on the Municipality code : GMxxxx
This is not working at all, so I guess am I not able to read the attributes right in the JSON data.
I did choose city name as filter. The standard dzVents openURL approach does not work here because the JSON contains too many records. I therefore filter outside domoticz using the jq program. Good part is that the control is still with dzVents.

virtual devices to define are one text sensor and 6 counters. The counters need to be set as type counter using the edit button on the switch tab after they have been defined (see picture) on the dummy Hardware.
counter type on switch tab.png
counter type on switch tab.png (31.82 KiB) Viewed 4889 times

dzVents script

Code: Select all

--[[

        Purpose of the script is to present the day- and overall number of reported, Hospital Admitted and deceased per date for the set city
        The script has to work in several steps because the retrieved JSON file has too many records for domoticz / Lua / dzVents to handle in one
        pass. Every step require a separate execution because we don't want to block the event system.
         You only have to set the time that you want the script to start. The script will trigger the subsequent steps itself.

        first execution (trigger is timer ör customEvent if it is a rerun because no today's data was available ) : use curl to get the data from rivm
        2nd execution = interval 60 seconds (trigger is customEvent send by emitEvent): Check if today's data is available
        3rd execution = interval 2 seconds (trigger is customEvent send by emitEvent): use fq to filter data on cityname and sum all cities
        4th execution = interval 7 seconds (trigger is customEvent send by emitEvent): use curl to send filtered data
        5th execution = interval < 1 seconds (trigger is curl to send data as customevent data): process filtered JSON / populate Counters)
        6th execcution = interval < 1 seconds (trigger is customEvent send by emitEvent): update textDevice

        This script require dzVents version 3.0.2 or newer and curl, jq and sed to be available on the OS level. Plus sqlite3 ( to populate history tables )
        If they are not yet installed you can do that with
            sudo apt install curl jq sed sqlite3

        Use the bash script (also in the top post) after first successful execution of the script to populate history table for reported, hospitalized and deceased people.
        btw: Graphs need 24 hours before daytotals do make sense

        Available cities in RIVM data (as seen on August 16th 2020)

        Aa en Hunze                     Deurne                          Hoeksche Waard                  Oegstgeest                      Tiel
        Aalsmeer                        Deventer                        Hof van Twente                  Oirschot                        Tilburg
        Aalten                          De Wolden                       Hollands Kroon                  Oisterwijk                      Tubbergen
        Achtkarspelen                   Diemen                          Hoogeveen                       Oldambt                         Twenterand
        Alblasserdam                    Dinkelland                      Hoorn                           Oldebroek                       Tynaarlo
        Albrandswaard                   Doesburg                        Horst aan de Maas               Oldenzaal                       Tytsjerksteradiel
        Alkmaar                         Doetinchem                      Houten                          Olst-Wijhe                      Uden
        Almelo                          Dongen                          Huizen                          Ommen                           Uitgeest
        Almere                          Dordrecht                       Hulst                           Oosterhout                      Uithoorn
        Alphen aan den Rijn             Drechterland                    IJsselstein                     Oost Gelre                      Urk
        Alphen-Chaam                    Drimmelen                       Kaag en Braassem                Ooststellingwerf                Utrecht
        Altena                          Dronten                         Kampen                          Oostzaan                        Utrechtse Heuvelrug
        Ameland                         Druten                          Kapelle                         Opmeer                          Vaals
        Amersfoort                      Duiven                          Katwijk                         Opsterland                      Valkenburg aan de Geul
        Amstelveen                      Echt-Susteren                   Kerkrade                        Oss                             Valkenswaard
        Amsterdam                       Edam-Volendam                   Koggenland                      Oude IJsselstreek               Veendam
        Apeldoorn                       Ede                             Krimpen aan den IJssel          Ouder-Amstel                    Veenendaal
        Appingedam                      Eemnes                          Krimpenerwaard                  Oudewater                       Veere
        Arnhem                          Eersel                          Laarbeek                        Overbetuwe                      Veldhoven
        Assen                           Eijsden-Margraten               Landerd                         Papendrecht                     Velsen
        Asten                           Eindhoven                       Landgraaf                       Peel en Maas                    Venlo
        Baarle-Nassau                   Elburg                          Landsmeer                       Pekela                          Venray
        Baarn                           Emmen                           Langedijk                       Pijnacker-Nootdorp              Vijfheerenlanden
        Barendrecht                     Enkhuizen                       Lansingerland                   Purmerend                       Vlaardingen
        Barneveld                       Enschede                        Laren                           Putten                          Vlieland
        Beek                            Epe                             Leeuwarden                      Raalte                          Vlissingen
        Beekdaelen                      Ermelo                          Leiden                          Reimerswaal                     Voerendaal
        Beemster                        Etten-Leur                      Leiderdorp                      Renkum                          Voorschoten
        Beesel                          Geertruidenberg                 Leidschendam-Voorburg           Renswoude                       Voorst
        Bergeijk                        Geldrop-Mierlo                  Lelystad                        Reusel-De Mierden               Vught
        Berg en Dal                     Gemert-Bakel                    Leudal                          Rheden                          Waadhoeke
        Bergen (L.)                     Gennep                          Leusden                         Rhenen                          Waalre
        Bergen (NH.)                    Gilze en Rijen                  Lingewaard                      Ridderkerk                      Waalwijk
        Bergen op Zoom                  Goeree-Overflakkee              Lisse                           Rijssen-Holten                  Waddinxveen
        Berkelland                      Goes                            Lochem                          Rijswijk                        Wageningen
        Bernheze                        Goirle                          Loon op Zand                    Roerdalen                       Wassenaar
        Best                            Gooise Meren                    Lopik                           Roermond                        Waterland
        Beuningen                       Gorinchem                       Loppersum                       Roosendaal                      Weert
        Beverwijk                       Gouda                           Losser                          Rotterdam                       Weesp
        Bladel                          Grave                           Maasdriel                       Rozendaal                       West Betuwe
        Blaricum                        Groningen                       Maasgouw                        Rucphen                         Westerkwartier
        Bloemendaal                     Gulpen-Wittem                   Maassluis                       Schagen                         Westerveld
        Bodegraven-Reeuwijk             Haaksbergen                     Maastricht                      Scherpenzeel                    Westervoort
        Boekel                          Haaren                          Medemblik                       Schiedam                        Westerwolde
        Borger-Odoorn                   Haarlem                         Meerssen                        Schiermonnikoog                 Westland
        Borne                           Haarlemmermeer                  Meierijstad                     Schouwen-Duiveland              West Maas en Waal
        Borsele                         Halderberge                     Meppel                          's-Gravenhage                   Weststellingwerf
        Boxmeer                         Hardenberg                      Middelburg                      's-Hertogenbosch                Westvoorne
        Boxtel                          Harderwijk                      Midden-Delfland                 Simpelveld                      Wierden
        Breda                           Hardinxveld-Giessendam          Midden-Drenthe                  Sint Anthonis                   Wijchen
        Brielle                         Harlingen                       Midden-Groningen                Sint-Michielsgestel             Wijdemeren
        Bronckhorst                     Hattem                          Mill en Sint Hubert             Sittard-Geleen                  Wijk bij Duurstede
        Brummen                         Heemskerk                       Moerdijk                        Sliedrecht                      Winterswijk
        Brunssum                        Heemstede                       Molenlanden                     Sluis                           Woensdrecht
        Bunnik                          Heerde                          Montferland                     Smallingerland                  Woerden
        Bunschoten                      Heerenveen                      Montfoort                       Soest                           Wormerland
        Buren                           Heerhugowaard                   Mook en Middelaar               Someren                         Woudenberg
        Capelle aan den IJssel          Heerlen                         Neder-Betuwe                    Son en Breugel                  Zaanstad
        Castricum                       Heeze-Leende                    Nederweert                      Stadskanaal                     Zaltbommel
        Coevorden                       Heiloo                          Nieuwegein                      Staphorst                       Zandvoort
        Cranendonck                     Hellendoorn                     Nieuwkoop                       Stede Broec                     Zeewolde
        Cuijk                           Hellevoetsluis                  Nijkerk                         Steenbergen                     Zeist
        Culemborg                       Helmond                         Nijmegen                        Steenwijkerland                 Zevenaar
        Dalfsen                         Hendrik-Ido-Ambacht             Nissewaard                      Stein                           Zoetermeer
        Dantumadiel                     Hengelo                         Noardeast-Fryslân               Stichtse Vecht                  Zoeterwoude
        De Bilt                         Het Hogeland                    Noord-Beveland                  Súdwest-Fryslân                 Zuidplas
        De Fryske Marren                Heumen                          Noordenveld                     Terneuzen                       Zundert
        Delft                           Heusden                         Noordoostpolder                 Terschelling                    Zutphen
        Delfzijl                        Hillegom                        Noordwijk                       Texel                           Zwartewaterland
        Den Helder                      Hilvarenbeek                    Nuenen, Gerwen en Nederwetten   Teylingen                       Zwijndrecht
        De Ronde Venen                  Hilversum                       Nunspeet                        Tholen                          Zwolle

        history:
        20200816 Start coding
        20200817 Initial release on forum
        20200818 Fixed jq output redirection from stdout to file for version < 1.6
        20200818 Only select last date from records
        20200818 Add total for the Netherlands (based on all Municipalities)
        20200819 Add description how to populate history table
        20200819 Fixed unwanted end of comment block
        20200820 Update text device in separate execution to collect latest counters
        20200820 Add lodash debug lines
        20200820 Fixed unexpected behavior with city names containing special chars
        20200820 Add latest report time to text sensor
        20200820 Make filterdelay configurable and fixed typo
        20200821 Use base64 string as data for dzVents < 3.0.9 (curl POST cannot be used to trigger customEvents in these versions)
        20200821 Use persistent data to parse filtered data between script executions
        20200822 Add total deceased and total hospitalized for the country (2 additional counterdevices)
        20200822 Use external bash script to populate history table for all counters (Only needed once)
        20200828 Prevent negative numbers in daytotals
        20201005 Add extra test for today's data
        20210301 Add code for optional text device to report country totals
--]]

local scriptVar = 'covidTrigger'
local city = "Rotterdam"  -- Change to one of the cities from above overview

-- when city contains any special char like like a dot, comma, bracket or quote you should use a cityName without these special chars to prevent processing errors
-- if the city is without any of these special chars you can remove next line. EXAMPLE: if city is "'s-Gravenhage" then cityName can be something like "den Haag"
-- local cityName = "den Haag"  -- for devicename

return
{
    on =
    {
        timer =
        {
            'at 17:09', -- Once per day ( change to time of your liking but today's report is only available after around 15:00 )
        },

        customEvents =
        {
            scriptVar .. '*' ,
        }
    },

    data =
    {
        filtered =
        {
            initial = {},
        },
    },

    logging =
    {
        level = domoticz.LOG_ERROR, -- change to domoticz.LOG_ERROR when all ok.
        marker = scriptVar,
    },

    execute = function(dz, item)
        local debug = _G.logLevel == dz.LOG_DEBUG
        local _ = dz.utils._
        local cityName = cityName or city
        local filterDelay = 31 -- This is the delay in seconds between the start of the jq filter in the OS background and pulling the result into dzVents. YMMV

        -- devices
        local textSensor = dz.devices('covid-19 in ' .. cityName) -- define as virtual textSensor
        local reported = dz.devices('Reported covid-19 in ' .. cityName) -- define as virtual counter and change type to Counter (edit on switch tab)
        local deceased = dz.devices('Deceased covid-19 in ' .. cityName) -- define as virtual counter and change type to Counter (edit on switch tab)
        local hospitalized = dz.devices('Hospitalized covid-19 in ' .. cityName) -- define as virtual counter and change type to Counter (edit on switch tab)
        local totalReported = dz.devices('Reported covid-19 in Netherlands') -- define as virtual counter and change type to Counter (edit on switch tab)
        local totalDeceased = dz.devices('Deceased covid-19 in Netherlands') -- define as virtual counter and change type to Counter (edit on switch tab)
        local totalHospitalized = dz.devices('Hospitalized covid-19 in Netherlands') -- define as virtual counter and change type to Counter (edit on switch tab)
        local totalText = dz.devices('covid-19 in Netherlands') -- define as virtual textSensor

        -- temp file locations
        local rivmAll = '/tmp/rivmDataCumulative'
        local rivmFiltered = '/tmp/rivmFiltered'
        local rivmTotalReported = '/tmp/rivmTotalReported'
        local rivmTotalHospitalized = '/tmp/rivmTotalHospitalized'
        local rivmTotalDeceased = '/tmp/rivmTotalDeceased'

        -- customEvent triggers
        local checkDate = '_checkDate'
        local curlReady = '_curlReady'
        local jqReturn = '_jqReturn'
        local filterReady = '_jqFilterReady'
        local updateText = '_updateText'
        local again = '_again'

        -- url's / os commands
        local preProcessorCommand
        if city:find("'") then
            city = city:gsub("'","@@")
            preProcessorCommand = "sed -i \"s/'/@@/g\" " .. rivmAll -- single quote is not nice in commands so replace with @@
        end
        local checkDateCommand = "tail -c 500 " .. rivmAll .. " | grep -o -i "  .. dz.time.rawDate .. " | wc -l "
        local rivmURL = 'https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_cumulatief.json'
        local getURLCommand = 'curl -o ' .. rivmAll .. ' ' .. rivmURL .. ' &'
        local jqFilterCommand = "jq ' .[] | select (.Municipality_name == " .. '"' .. city .. '"' .. " )' " ..
                                rivmAll .. " | jq .[-1] -s > " .. rivmFiltered .. " &"
        local jqTotalReportedCommand =  "jq ' .[] | select ( .Date_of_report > " .. '"' .. dz.time.rawDate .. '"' ..
                                " and .Municipality_name != null ) ' /tmp/rivmDataCumulative | jq -s 'map(.Total_reported) | add ' > " .. rivmTotalReported .. " &"
        local jqTotalHospitalizedCommand =  "jq ' .[] | select ( .Date_of_report > " .. '"' .. dz.time.rawDate .. '"' ..
                                " and .Municipality_name != null ) ' /tmp/rivmDataCumulative | jq -s 'map(.Hospital_admission) | add ' > " .. rivmTotalHospitalized .. " &"
        local jqTotalDeceasedCommand =  "jq ' .[] | select ( .Date_of_report > " .. '"' .. dz.time.rawDate .. '"' ..
                                " and .Municipality_name != null ) ' /tmp/rivmDataCumulative | jq -s 'map(.Deceased) | add ' > " .. rivmTotalDeceased .. " &"
        local getFilteredDataCommand = "curl -d '@" .. rivmFiltered .. "' '" .. dz.settings['Domoticz url'] ..
                                        "/json.htm?type=command&param=customevent&event=" .. scriptVar .. filterReady .. "'"
        math.randomseed(os.time())

        local function osCommand(cmd)
            local file = io.popen(cmd)
            local output = file:read('*all')
            local rc = { file:close() }
            return output, rc[3]
        end

        local function intVersion(strVersion) -- Compare string versions
            local significance = 100000
            local version = 0
            for significant in strVersion:gmatch('%d+') do
                version = version + significant * significance
                significance = significance / 100
            end
            return math.floor(version)
        end

        local function getRandomNumber(min, max)
            local min = min or 0
            local max = max or 1
            return math.random(min, max )
        end

        local function updateTextDevices(t)
            dz.log('updateTextDevice: ' .. _.str(t),dz.LOG_DEBUG)
            if dz.utils.deviceExists(textSensor.name) then
                textSensor.updateText('latest report date/Time: ' .. t.Date_of_report:sub(1,16) .. '\n'
                .. 'Reported: ' .. t.Total_reported .. ' (' .. ( ( reported and reported.counterToday ) or '-' ) .. ')\n'
                .. 'Hospitalized: ' .. t.Hospital_admission .. ' (' .. ( ( hospitalized and hospitalized.counterToday ) or '-' ) .. ')\n'
                .. 'Deceased: ' .. t.Deceased .. ' (' .. ( ( deceased and deceased.counterToday ) or '-' )  .. ')' )
            end
            if dz.utils.deviceExists(totalText.name) then
                totalText.updateText( 'latest report date/Time: ' .. t.Date_of_report:sub(1,16) .. '\n'
                .. 'Reported: ' .. math.floor(totalReported.counter) 
                ..  '\n' .. 'Hospitalized: ' .. math.floor(totalHospitalized.counter)   -- remove this line if you don't have the total Hospitalized counter
                .. '\n' .. 'Deceased: ' .. math.floor(totalDeceased.counter) -- remove this line if you don't have the total Deceased counter
                )
            end
        end

        local function getTotals ()
            local total = {}
            for key, file in ipairs({rivmTotalReported, rivmTotalHospitalized, rivmTotalDeceased}) do
                local fd = io.open(file,'r')
                dz.log()
                total[key] = math.floor(fd:read('*a'))
                fd:close()
            end
            return total
        end

        local function updateCounterDevices(t)
            dz.log('updateCounterDevices: ' .. _.str(t),dz.LOG_DEBUG)
            if dz.utils.deviceExists(reported.name) and reported.counter <= t.Total_reported then
                reported.updateCounter(t.Total_reported)
            end
            if dz.utils.deviceExists(deceased.name) and deceased.counter <= t.Deceased then
                deceased.updateCounter(t.Deceased)
            end
            if dz.utils.deviceExists(hospitalized.name) and hospitalized.counter <= t.Hospital_admission then
                hospitalized.updateCounter(t.Hospital_admission)
            end

            local totals = getTotals()
            if dz.utils.deviceExists(totalReported.name) and totals and ( totals[1] >= totalReported.counter ) then
                totalReported.updateCounter(totals[1])
            end
            if dz.utils.deviceExists(totalHospitalized.name) and totals and ( totals[2] >= totalHospitalized.counter ) then
                totalHospitalized.updateCounter(totals[2])
            end
            if dz.utils.deviceExists(totalDeceased.name) and totals and ( totals[3] >= totalDeceased.counter ) then
                totalDeceased.updateCounter(totals[3])
            end
        end

        -- main
        if intVersion(dz.settings.dzVentsVersion) < intVersion('3.0.9')then
            dz.log('dzVents version is '.. dz.settings.dzVentsVersion ..'. Must use base64 encoding ',dz.LOG_DEBUG)
            jqFilterCommand = "jq ' .[] | select (.Municipality_name == " .. '"' .. city .. '"' .. " )' " ..
                                rivmAll .. " | jq  -s '.[-1] | @base64' > " .. rivmFiltered .. " &"
            getFilteredDataCommand = "curl " .. '"' .. dz.settings['Domoticz url'] .. '/json.htm?type=command&param=customevent&event='
                                      .. scriptVar .. filterReady .. "&data=$(cat " .. rivmFiltered .. ')"'
        elseif intVersion(dz.settings.dzVentsVersion) < intVersion('3.0.2') then
            dz.log('dzVents version is '.. dz.settings.dzVentsVersion ..'. Script uses functions not available in this version.',dz.LOG_ERROR)
            return
        end

        if item.isTimer or item.trigger:find(again) then
            os.execute(getURLCommand)
            dz.log(getURLCommand,dz.LOG_DEBUG)
            dz.emitEvent(scriptVar .. checkDate).afterSec(getRandomNumber(61,67))

        elseif item.trigger:find(checkDate) then
            local todayAvailable, rc = osCommand(checkDateCommand)
            if tonumber(rc) == 0 and tonumber(todayAvailable) ~= 0 then
                dz.emitEvent(scriptVar .. curlReady).afterSec(3)
                dz.log('Today is available (' .. todayAvailable .. ' records). Continue processing.. ' ,dz.LOG_DEBUG)
            elseif dz.time.hour < 23 then
                dz.log("Today's is not yet available. Continue processing.. in approximate 30 minutes" ,dz.LOG_DEBUG)
                dz.emitEvent(scriptVar .. again).afterSec(getRandomNumber(1750,1850))
            else
                dz.log("Today's is not available. Giving up for today." ,dz.LOG_DEBUG)
            end

        elseif item.trigger:find(curlReady) then
            os.execute(preProcessorCommand or '')
            os.execute(jqFilterCommand)
            os.execute(jqTotalReportedCommand)
            os.execute(jqTotalDeceasedCommand)
            os.execute(jqTotalHospitalizedCommand)
            dz.log('preProcessorCommand: '  .. (preProcessorCommand or 'not needed for ' .. city),dz.LOG_DEBUG)
            dz.log('jqFilterCommand: ' .. jqFilterCommand,dz.LOG_DEBUG)
            dz.log('jqTotalReportedCommand: ' .. jqTotalReportedCommand,dz.LOG_DEBUG)
            dz.emitEvent(scriptVar .. jqReturn).afterSec(filterDelay)

        elseif item.trigger:find(jqReturn) then
            os.execute(getFilteredDataCommand)
            dz.log('getFilteredDataCommand: ' ..getFilteredDataCommand, dz.LOG_DEBUG)

        elseif item.trigger:find(filterReady) then
            dz.data.filtered = item.json or dz.utils.fromJSON(dz.utils.fromBase64(item.data))
            updateCounterDevices(dz.data.filtered)
            dz.emitEvent(scriptVar .. updateText)

        elseif item.trigger:find(updateText) then
            updateTextDevices(dz.data.filtered)

        else
            dz.log('There was a problem handling the request', dz.LOG_ERROR)
            dz.log(item, dz.LOG_DEBUG)
        end
    end
}

Bash script to populate history table after first execution of dzVents script

Code: Select all

#!/usr/bin/bash
#
#        20201018 Fixed issue with city names containing spaces. Thx to JeroenG
# -------------------------------
# set location vars
 
domoticzHome=/opt/domoticz
tmpDir=/tmp
prefix=covid
sqliteInput=$tmpDir/"$prefix"_sqliteInput

# set domoticz idx for Nation wide counters
totalReported=486
totalHospitalized=493
totalDeceased=492

# set domoticz name /idx for city counters
city="Rotterdam" 
export deceased=1169 
export reported=1171 
export hospitalized=1170

# -----------------------------------------# No changes required below this line

echo Get data from RIVM
curl --silent -o $tmpDir/"$prefix"_Complete.json 'https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_cumulatief.json'

echo Create field names line for details
echo "cityCode","cityName","Province",Reported,Hospitalized,Deceased,"Date" > $tmpDir/"$prefix"_details.csv 

echo Append detail data to csv file
sudo cat $tmpDir/"$prefix"_Complete.json | jq -r '. | map([.Municipality_code,.Municipality_name,.Province,.Total_reported,.Hospital_admission,.Deceased, .Date_of_report[:10] ] | @csv)| join("\n")' >>  $tmpDir/"$prefix"_details.csv 

echo Create csv Totals data 
sudo cat $tmpDir/"$prefix"_Complete.json | jq -r '. | map([0,0,0,0,0,0, .Date_of_report[:10] ] | @csv)| join("\n")' > $tmpDir/"$prefix"_dates.csv 

echo Sort and remove duplicates
sort -o $tmpDir/"$prefix"_uDates.csv -u $tmpDir/"$prefix"_dates.csv 

echo Create field names line for totals
echo tReported,tHospitalized,tDeceased,Reported,Hospitalized,Deceased,"Date" > $tmpDir/"$prefix"_dates.csv 

echo Append data to csv file
cat $tmpDir/"$prefix"_uDates.csv >> $tmpDir/"$prefix"_dates.csv 

echo Remove covid database and create new one
cd $domoticzHome
sudo rm -f $domoticzHome/covid.db 

echo .mode csv > "$sqliteInput"_Create
echo .import $tmpDir/"$prefix"_details.csv Details >> "$sqliteInput"_Create
echo .import $tmpDir/"$prefix"_dates.csv Totals >> "$sqliteInput"_Create

echo "UPDATE Totals SET tHospitalized = ( SELECT sum(Hospitalized) FROM Details WHERE date = Totals.date and cityCode <> \"\");" >> "$sqliteInput"_Create
echo "UPDATE Totals SET tReported = ( SELECT sum(Reported) FROM Details WHERE date = Totals.date and cityCode <> \"\");" >> "$sqliteInput"_Create
echo "UPDATE Totals SET tDeceased = ( SELECT sum(Deceased) FROM Details WHERE date = Totals.date and cityCode <> \"\");" >> "$sqliteInput"_Create

echo "UPDATE Totals SET Hospitalized = ( SELECT Totals.tHospitalized - IFNULL(sum(t2.Hospitalized),0) FROM Totals t2  WHERE t2.Date < Totals.date );" >> "$sqliteInput"_Create
echo "UPDATE Totals SET Reported = ( SELECT Totals.tReported - IFNULL(sum(t2.Reported),0) FROM Totals t2  WHERE t2.Date < Totals.date );" >> "$sqliteInput"_Create
echo "UPDATE Totals SET Deceased = ( SELECT Totals.tDeceased - IFNULL(sum(t2.Deceased),0) FROM Totals t2  WHERE t2.Date < Totals.date );" >> "$sqliteInput"_Create
echo .quit >> "$sqliteInput"_Create 

echo "ATTACH DATABASE \"domoticz.db\" AS target;" > "$sqliteInput"_Fill
echo "DELETE FROM target.Meter_Calendar WHERE DeviceRowID IN ($totalReported, $totalHospitalized, $totalDeceased, $reported, $hospitalized, $deceased);" >> "$sqliteInput"_Fill

echo "INSERT INTO target.Meter_Calendar(DeviceRowID, Value, Counter, Date) SELECT $totalReported, Reported, tReported, Date FROM Totals;" >> "$sqliteInput"_Fill
echo "INSERT INTO target.Meter_Calendar(DeviceRowID, Value, Counter, Date) SELECT $totalDeceased, Deceased, tDeceased, Date FROM Totals;" >> "$sqliteInput"_Fill
echo "INSERT INTO target.Meter_Calendar(DeviceRowID, Value, Counter, Date) SELECT $totalHospitalized, Hospitalized, tHospitalized, Date FROM Totals;" >> "$sqliteInput"_Fill
echo .quit >> "$sqliteInput"_Fill

echo Creating covid database 
sudo sqlite3 covid.db < "$sqliteInput"_Create 

echo Make a safe copy of domoticz database
sudo cp domoticz.db domoticz.db_$(date '+%Y%m%d%H%M%S')

echo Clearing and filling domoticz history tables
sudo sqlite3 covid.db < "$sqliteInput"_Fill 

echo Creating city csv files
sudo jq " .[] | select (.Municipality_name == \"$city\" )" $tmpDir/"$prefix"_Complete.json | jq . -s > "$tmpDir"/"$prefix"_"$city.json"
sudo cat $tmpDir/"$prefix"_"$city.json" | jq -r '. | map([ env.deceased, 0, .Deceased, .Date_of_report[:10] ] | @csv)| join("\n")' > "$tmpDir"/"$prefix"_deceased.csv
sudo cat $tmpDir/"$prefix"_"$city.json" | jq -r '. | map([ env.reported, 0, .Total_reported, .Date_of_report[:10] ] | @csv)| join("\n")' > "$tmpDir"/"$prefix"_reported.csv
sudo cat $tmpDir/"$prefix"_"$city.json" | jq -r '. | map([ env.hospitalized, 0, .Hospital_admission, .Date_of_report[:10] ] | @csv)| join("\n")' > "$tmpDir"/"$prefix"_hospitalized.csv

echo Reading city data into history tables 
sudo sqlite3 domoticz.db --csv  ".import $tmpDir/"$prefix"_deceased.csv Meter_Calendar"
sudo sqlite3 domoticz.db --csv  ".import $tmpDir/"$prefix"_reported.csv Meter_Calendar"
sudo sqlite3 domoticz.db --csv  ".import $tmpDir/"$prefix"_hospitalized.csv Meter_Calendar"

echo Calculate delta\'s
sudo sqlite3 domoticz.db  "UPDATE Meter_Calendar SET value = ( SELECT Meter_Calendar.counter - IFNULL(max(t2.counter),0) FROM Meter_Calendar t2  WHERE t2.DeviceRowID =  $deceased and t2.date < Meter_Calendar.date order by t2.date) WHERE DeviceRowID = $deceased;"
sudo sqlite3 domoticz.db  "UPDATE Meter_Calendar SET value = ( SELECT Meter_Calendar.counter - IFNULL(max(t2.counter),0) FROM Meter_Calendar t2  WHERE t2.DeviceRowID = $reported and t2.date < Meter_Calendar.date order by t2.date) WHERE DeviceRowID = $reported;"
sudo sqlite3 domoticz.db  "UPDATE Meter_Calendar SET value = ( SELECT Meter_Calendar.counter - IFNULL(max(t2.counter),0) FROM Meter_Calendar t2  WHERE t2.DeviceRowID = $hospitalized and t2.date < Meter_Calendar.date order by t2.date) WHERE DeviceRowID = $hospitalized;"

echo set Today\'s values
sudo sqlite3 domoticz.db  "UPDATE Meter SET Value = (SELECT MAX(Counter) FROM Meter_Calendar WHERE DeviceRowID = $deceased AND Date < DATE('NOW')) WHERE DeviceRowID = $deceased AND Value = 0;"
sudo sqlite3 domoticz.db  "UPDATE Meter SET Value = (SELECT MAX(Counter) FROM Meter_Calendar WHERE DeviceRowID = $hospitalized AND Date < DATE('NOW')) WHERE DeviceRowID = $hospitalized AND Value = 0;"
sudo sqlite3 domoticz.db  "UPDATE Meter SET Value = (SELECT MAX(Counter) FROM Meter_Calendar WHERE DeviceRowID = $reported AND Date < DATE('NOW')) WHERE DeviceRowID = $reported AND Value = 0;"

sudo sqlite3 domoticz.db  "UPDATE Meter SET Value = (SELECT MAX(Counter) FROM Meter_Calendar WHERE DeviceRowID = $totalDeceased AND Date < DATE('NOW')) WHERE DeviceRowID = $totalDeceased AND Value = 0;"
sudo sqlite3 domoticz.db  "UPDATE Meter SET Value = (SELECT MAX(Counter) FROM Meter_Calendar WHERE DeviceRowID = $totalHospitalized AND Date < DATE('NOW')) WHERE DeviceRowID = $totalHospitalized AND Value = 0;"
sudo sqlite3 domoticz.db  "UPDATE Meter SET Value = (SELECT MAX(Counter) FROM Meter_Calendar WHERE DeviceRowID = $totalReported AND Date < DATE('NOW')) WHERE DeviceRowID = $totalReported AND Value = 0;"


echo Cleanup
sudo rm -f $tmpDir/"$prefix"_*

#eoj
covid.png
covid.png (70.75 KiB) Viewed 4518 times
after populating the history table
graph.png
graph.png (108.83 KiB) Viewed 4799 times
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

Updated first post with description how to populate history table with data from rivm using jq and sqlite3
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
EdwinK
Posts: 1820
Joined: Sunday 22 January 2017 21:46
Target OS: Raspberry Pi / ODroid
Domoticz version: BETA
Location: Rhoon
Contact:

Re: Dutch local covid-19 figures

Post by EdwinK »

Got an error on line 27:
2020-08-19 08:51:36.511 Error: dzVents: Error: (3.0.11) error loading module 'Covid' from file '/home/pi/domoticz/scripts/dzVents/generated_scripts/Covid.lua':
2020-08-19 08:51:36.511 .../pi/domoticz/scripts/dzVents/generated_scripts/Covid.lua:27: unexpected symbol near '|'
Running latest BETA on a Pi-3 | Toon® Thermostat (rooted) | Hue | Tuya | IKEA tradfri | Dashticz V3 on Lenovo Huawei Tablet | Conbee
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

EdwinK wrote: Wednesday 19 August 2020 8:53 Got an error on line 27:
2020-08-19 08:51:36.511 Error: dzVents: Error: (3.0.11) error loading module 'Covid' from file '/home/pi/domoticz/scripts/dzVents/generated_scripts/Covid.lua':
2020-08-19 08:51:36.511 .../pi/domoticz/scripts/dzVents/generated_scripts/Covid.lua:27: unexpected symbol near '|'
Strange :o line 27 is in the comment block.

[Edit] found it. The line contains ]] which ends the comment block. Changed it in my original posting. Please try again.
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
EdwinK
Posts: 1820
Joined: Sunday 22 January 2017 21:46
Target OS: Raspberry Pi / ODroid
Domoticz version: BETA
Location: Rhoon
Contact:

Re: Dutch local covid-19 figures

Post by EdwinK »

When I'm now give one of this commands:
sudo sqlite3 domoticz.db ".import --csv /tmp/deceased.csv meter_calendar"
sudo sqlite3 domoticz.db ".import --csv /tmp/reported.csv meter_calendar"
sudo sqlite3 domoticz.db ".import --csv /tmp/hospitalized.csv meter_calendar"
I get 'Usage: .import FILE TABLE'

and with:
sudo sqlite3 domoticz.db "update meter_calendar set value = ( select meter_calendar.counter - ifnull(max(t2.counter),0) from meter_calendar t2 where t2.devicerowid = $deceased and t2.date < meter_calendar.date order by t2.date) where devicerowid = $deceased;"
Error: near "and": syntax error



Error: near "and": syntax error
Running latest BETA on a Pi-3 | Toon® Thermostat (rooted) | Hue | Tuya | IKEA tradfri | Dashticz V3 on Lenovo Huawei Tablet | Conbee
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

EdwinK wrote: Wednesday 19 August 2020 17:21 When I'm now give one of this commands:

Code: Select all

sudo sqlite3 domoticz.db ".import --csv /tmp/deceased.csv meter_calendar"
sudo sqlite3 domoticz.db ".import --csv /tmp/reported.csv meter_calendar"
sudo sqlite3 domoticz.db ".import --csv /tmp/hospitalized.csv meter_calendar"
I get 'Usage: .import FILE TABLE'
You have to do all steps in sequence.
If you have done that please try again with

Code: Select all

sudo sqlite3 domoticz.db --csv  ".import /tmp/deceased.csv meter_calendar"
sudo sqlite3 domoticz.db --csv  ".import /tmp/reported.csv meter_calendar"
sudo sqlite3 domoticz.db --csv  ".import /tmp/hospitalized.csv meter_calendar"
If you have done all previous steps and the update meter_calendar steps still fail you could try again after replacing $deceased with the actual deviceID's
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
Xenomes
Posts: 385
Joined: Tuesday 27 November 2018 19:05
Target OS: Linux
Domoticz version: 2024.7
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by Xenomes »

Got a error on line 195:

Code: Select all

 2020-08-19 21:22:13.076 Error: dzVents: Error: (3.0.2) covidTrigger: An error occurred when calling event handler covidTrigger
2020-08-19 21:22:13.076 Error: dzVents: Error: (3.0.2) covidTrigger: ...oticz/scripts/dzVents/generated_scripts/covidTrigger.lua:195: attempt to index a nil value (local 't') 
History imported.
Nice job creating the script!
HP T630 (32GB SSD/8GB Mem) - Ubuntu 22.04.4 LTS (64Bit) - Domoticz 2024.7 with Machinon theme - RFLink - KaKu - Sonoff - Tasmota - Shelly - MQTT2Zigbee - OpenTherm Gateway - Tinytuya - IR Blaster - P1 Smart Meter - NPN Watermeter - Google Assistant
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

New version posted in first post:

History:

20200819 Add description how to populate history table
20200819 Fixed unwanted end of comment block
20200820 Update text device in separate execution to collect latest counters
20200820 Add lodash debug lines
20200820 Fixed unexpected behavior with city names containing special chars
20200820 Add latest report time to text sensor
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
EdwinK
Posts: 1820
Joined: Sunday 22 January 2017 21:46
Target OS: Raspberry Pi / ODroid
Domoticz version: BETA
Location: Rhoon
Contact:

Re: Dutch local covid-19 figures

Post by EdwinK »

Works fine now.
Running latest BETA on a Pi-3 | Toon® Thermostat (rooted) | Hue | Tuya | IKEA tradfri | Dashticz V3 on Lenovo Huawei Tablet | Conbee
User avatar
Xenomes
Posts: 385
Joined: Tuesday 27 November 2018 19:05
Target OS: Linux
Domoticz version: 2024.7
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by Xenomes »

On the new version, line 215 error

Code: Select all

 2020-08-20 14:35:13.499 Error: dzVents: Error: (3.0.2) covidTrigger: An error occurred when calling event handler covidTrigger
2020-08-20 14:35:13.499 Error: dzVents: Error: (3.0.2) covidTrigger: ...oticz/scripts/dzVents/generated_scripts/covidTrigger.lua:215: attempt to index a nil value (local 't') 
I see that the script developer is running the beta version. Is this script for the beta only?
HP T630 (32GB SSD/8GB Mem) - Ubuntu 22.04.4 LTS (64Bit) - Domoticz 2024.7 with Machinon theme - RFLink - KaKu - Sonoff - Tasmota - Shelly - MQTT2Zigbee - OpenTherm Gateway - Tinytuya - IR Blaster - P1 Smart Meter - NPN Watermeter - Google Assistant
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

Xenomes wrote: Thursday 20 August 2020 14:42 On the new version, line 215 error

Code: Select all

 2020-08-20 14:35:13.499 Error: dzVents: Error: (3.0.2) covidTrigger: An error occurred when calling event handler covidTrigger
2020-08-20 14:35:13.499 Error: dzVents: Error: (3.0.2) covidTrigger: ...oticz/scripts/dzVents/generated_scripts/covidTrigger.lua:215: attempt to index a nil value (local 't') 
I see that the script developer is running the beta version. Is this script for the beta only?
No, it should also work on the latest stable.
Can you show all the changes you made to the script (city , cityName, deviceNames) and the complete log from the first start of the script until the error ?
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
snuiter
Posts: 67
Joined: Saturday 17 June 2017 12:30
Target OS: Raspberry Pi / ODroid
Domoticz version: beta
Contact:

Re: Dutch local covid-19 figures

Post by snuiter »

Hi Waaren,
thanks for doing this, appreciate it.
Yesterday I also tried the new script and uploaded the history but it goes to -1 at some point, tried again with new devices and deleted the old ones. The csv files etc.. look good though.

I run the history upload from my commandline that is no problem right?

I will try the updated version later today.

Tx again.
User avatar
Xenomes
Posts: 385
Joined: Tuesday 27 November 2018 19:05
Target OS: Linux
Domoticz version: 2024.7
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by Xenomes »

waaren wrote: Thursday 20 August 2020 15:33 No, it should also work on the latest stable.
Can you show all the changes you made to the script (city , cityName, deviceNames) and the complete log from the first start of the script until the error ?
I create the 4 counters and the Text
2020-08-20 19_59_41-Window.png
2020-08-20 19_59_41-Window.png (29.29 KiB) Viewed 4638 times
Run imports with export deceased=639 reported=638 hospitalized=640
Changed local city = "Steenbergen" and time to run

Log:

Code: Select all

2020-08-20 20:10:40.173 Status: EventSystem: reset all events...
2020-08-20 20:10:40.176 Status: dzVents: Write file: /home/pi/domoticz/scripts/dzVents/generated_scripts/covidTrigger.lua
2020-08-20 20:11:00.581 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger:, trigger: "at 20:11"
2020-08-20 20:11:00.600 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-20 20:11:00.602 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:11:00.603 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:11:00.605 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:11:00.606 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-20 20:11:00.627 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-20 20:12:06.081 Status: dzVents: Info: Handling Domoticz custom event for: "covidTrigger_curlReady"
2020-08-20 20:12:06.081 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger: Custom event: "covidTrigger_curlReady"
2020-08-20 20:12:06.083 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-20 20:12:06.085 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:06.086 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:06.088 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:06.089 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-20 20:12:06.160 Status: dzVents: Debug: covidTrigger: strpreProcessorCommand:
2020-08-20 20:12:06.160 Status: dzVents: Debug: covidTrigger: jqFilterCommand: jq ' .[] | select (.Municipality_name == "Steenbergen" )' /tmp/rivmDataCumulative | jq .[-1] -s > /tmp/rivmFitered &
2020-08-20 20:12:06.160 Status: dzVents: Debug: covidTrigger: jqTotalCommand: jq ' .[] | select ( .Date_of_report > "2020-08-20" and .Municipality_name != null ) ' /tmp/rivmDataCumulative | jq -s 'map(.Total_reported) | add ' > /tmp/rivmTotal &
2020-08-20 20:12:06.160 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-20 20:12:06.161 Status: EventSystem: Script event triggered: /home/pi/domoticz/dzVents/runtime/dzVents.lua
2020-08-20 20:12:13.333 Status: dzVents: Info: Handling Domoticz custom event for: "covidTrigger_jqReturn"
2020-08-20 20:12:13.333 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger: Custom event: "covidTrigger_jqReturn"
2020-08-20 20:12:13.353 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-20 20:12:13.354 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:13.356 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:13.357 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:13.358 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-20 20:12:13.417 Status: dzVents: Debug: covidTrigger: getFilteredDataCommand: curl -d '@/tmp/rivmFitered' 'http://127.0.0.1:8085/json.htm?type=command&param=customevent&event=covidTrigger_jqFilterReady'
2020-08-20 20:12:13.417 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-20 20:12:13.545 Status: dzVents: Info: Handling Domoticz custom event for: "covidTrigger_jqFilterReady"
2020-08-20 20:12:13.545 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger: Custom event: "covidTrigger_jqFilterReady"
2020-08-20 20:12:13.565 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-20 20:12:13.566 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:13.568 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:13.569 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-20 20:12:13.570 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-20 20:12:13.570 Status: dzVents: Debug: covidTrigger: updateCounterDevices: nil
2020-08-20 20:12:13.570 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-20 20:12:13.570 Error: dzVents: Error: (3.0.2) covidTrigger: An error occurred when calling event handler covidTrigger
2020-08-20 20:12:13.570 Error: dzVents: Error: (3.0.2) covidTrigger: ...oticz/scripts/dzVents/generated_scripts/covidTrigger.lua:215: attempt to index a nil value (local 't')
Here is the full script
Script.txt
(20.46 KiB) Downloaded 129 times
Last edited by Xenomes on Thursday 20 August 2020 20:36, edited 1 time in total.
HP T630 (32GB SSD/8GB Mem) - Ubuntu 22.04.4 LTS (64Bit) - Domoticz 2024.7 with Machinon theme - RFLink - KaKu - Sonoff - Tasmota - Shelly - MQTT2Zigbee - OpenTherm Gateway - Tinytuya - IR Blaster - P1 Smart Meter - NPN Watermeter - Google Assistant
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

Xenomes wrote: Thursday 20 August 2020 20:17 2020-08-20 20:12:13.417 Status: dzVents: Debug: covidTrigger: getFilteredDataCommand: curl -d '@/tmp/rivmFitered' 'http://127.0.0.1:8085/json.htm?type=com ... ilterReady'
What is the content of /tmp/rivmFiltered ?
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
Xenomes
Posts: 385
Joined: Tuesday 27 November 2018 19:05
Target OS: Linux
Domoticz version: 2024.7
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by Xenomes »

waaren wrote: Thursday 20 August 2020 20:33 What is the content of /tmp/rivmFiltered ?
The content of /tmp/rivmFitered (missing a 'l') is:

Code: Select all

  "Date_of_report": "2020-08-20 10:00:00",
  "Municipality_code": "GM0851",
  "Municipality_name": "Steenbergen",
  "Province": "Noord-Brabant",
  "Total_reported": 105,
  "Hospital_admission": 13,
  "Deceased": 13
}
HP T630 (32GB SSD/8GB Mem) - Ubuntu 22.04.4 LTS (64Bit) - Domoticz 2024.7 with Machinon theme - RFLink - KaKu - Sonoff - Tasmota - Shelly - MQTT2Zigbee - OpenTherm Gateway - Tinytuya - IR Blaster - P1 Smart Meter - NPN Watermeter - Google Assistant
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

Xenomes wrote: Thursday 20 August 2020 20:38 The content of /tmp/rivmFitered (missing a 'l') is:
Can you see when it was created / modified (maybe the 7 seconds is not enough for your system) ?
btw. Have changed /tmp/rivmFitered to /tmp/rivmFiltered now but that is unrelated to the issue.
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
Xenomes
Posts: 385
Joined: Tuesday 27 November 2018 19:05
Target OS: Linux
Domoticz version: 2024.7
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by Xenomes »

waaren wrote: Thursday 20 August 2020 20:54 Can you see when it was created (maybe the 7 seconds is not enough for your system) ?
btw. Have changed /tmp/rivmFitered to /tmp/rivmFiltered now but that is unrelated to the issue.
Change it to 30 sec, no deference.
HP T630 (32GB SSD/8GB Mem) - Ubuntu 22.04.4 LTS (64Bit) - Domoticz 2024.7 with Machinon theme - RFLink - KaKu - Sonoff - Tasmota - Shelly - MQTT2Zigbee - OpenTherm Gateway - Tinytuya - IR Blaster - P1 Smart Meter - NPN Watermeter - Google Assistant
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

waaren wrote: Thursday 20 August 2020 20:54
Xenomes wrote: Thursday 20 August 2020 20:38 The content of /tmp/rivmFitered (missing a 'l') is:
Can you see when it was created / modified (maybe the 7 seconds is not enough for your system) ?
btw. Have changed /tmp/rivmFitered to /tmp/rivmFiltered now but that is unrelated to the issue.
:!: Think I found a possible explanation!
curl POST is not yet supported in dzVents 3.0.2. Have to think how to use GET to post the data from the file.
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
waaren
Posts: 6028
Joined: Tuesday 03 January 2017 14:18
Target OS: Linux
Domoticz version: Beta
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by waaren »

waaren wrote: Thursday 20 August 2020 21:15 :!: Think I found a possible explanation!
curl POST is not yet supported in dzVents 3.0.2. Have to think how to use curl GET to post the data from the file.
New version posted with workaround for dzVents version 3.0.2

history:
20200816 Start coding
20200817 Initial release on forum
20200818 Fixed jq output redirection from stdout to file for version < 1.6
20200818 Only select last date from records
20200818 Add total for the Netherlands (based on all Municipalities)
20200819 Add description how to populate history table
20200819 Fixed unwanted end of comment block
20200820 Update text device in separate execution to collect latest counters
20200820 Add lodash debug lines
20200820 Fixed unexpected behavior with city names containing special chars
20200820 Add latest report time to text sensor
20200820 Make filterdelay configurable and corrected typo
20200821 Use base64 string as data for dzVents < 3.0.9 (curl POST cannot be used to trigger customEvents in these versions)
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
User avatar
Xenomes
Posts: 385
Joined: Tuesday 27 November 2018 19:05
Target OS: Linux
Domoticz version: 2024.7
Location: Netherlands
Contact:

Re: Dutch local covid-19 figures

Post by Xenomes »

waaren wrote: Friday 21 August 2020 1:49 New version posted with workaround for dzVents version 3.0.2
Hi Waaren,

Tested your new script. The same error exits :oops: but now some counters are populated.
2020-08-21 08:55:19.517 Error: dzVents: Error: (3.0.2) covidTrigger: An error occurred when calling event handler covidTrigger
2020-08-21 08:55:19.517 Error: dzVents: Error: (3.0.2) covidTrigger: ...oticz/scripts/dzVents/generated_scripts/covidTrigger.lua:214: attempt to index a nil value (local 't')
2020-08-21 09_01_33-Window.png
2020-08-21 09_01_33-Window.png (33.79 KiB) Viewed 4567 times
I added this to test if it could write to the text device.

Code: Select all

if dz.utils.deviceExists(textSensor.name) then
     textSensor.updateText('Test')
     textSensor.updateText('latest report date/Time: ' .. t.Date_of_report:sub(1,16) .. '\n'
     .. 'Reported: ' .. t.Total_reported .. ' (' .. ( ( reported and reported.counterToday ) or '-' ) .. ')\n'
     .. 'Hospitalized: ' .. t.Hospital_admission .. ' (' .. ( ( hospitalized and hospitalized.counterToday ) or '-' ) .. ')\n'
     .. 'Deceased: ' .. t.Deceased .. ' (' .. ( ( deceased and deceased.counterToday ) or '-' )  .. ')' )
end
Log:

Code: Select all

2020-08-21 08:53:17.447 Status: dzVents: Write file: /home/pi/domoticz/scripts/dzVents/generated_scripts/covidTrigger.lua
2020-08-21 08:54:00.242 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger:, trigger: "at 08:54"
2020-08-21 08:54:00.260 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-21 08:54:00.262 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:54:00.263 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:54:00.264 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:54:00.265 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-21 08:54:00.266 Status: dzVents: Debug: covidTrigger: dzVents version is 3.0.2. Must use base64 encoding
2020-08-21 08:54:00.289 Status: dzVents: Debug: covidTrigger: curl -o /tmp/rivmDataCumulative https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_cumulatief.json &
2020-08-21 08:54:00.289 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-21 08:55:05.683 Status: dzVents: Info: Handling Domoticz custom event for: "covidTrigger_curlReady"
2020-08-21 08:55:05.683 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger: Custom event: "covidTrigger_curlReady"
2020-08-21 08:55:05.684 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-21 08:55:05.685 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:05.687 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:05.688 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:05.689 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-21 08:55:05.689 Status: dzVents: Debug: covidTrigger: dzVents version is 3.0.2. Must use base64 encoding
2020-08-21 08:55:05.767 Status: dzVents: Debug: covidTrigger: preProcessorCommand: not needed for Steenbergen
2020-08-21 08:55:05.767 Status: dzVents: Debug: covidTrigger: jqFilterCommand: jq ' .[] | select (.Municipality_name == "Steenbergen" )' /tmp/rivmDataCumulative | jq -s '.[-1] | @base64' > /tmp/rivmFiltered &
2020-08-21 08:55:05.767 Status: dzVents: Debug: covidTrigger: jqTotalCommand: jq ' .[] | select ( .Date_of_report > "2020-08-21" and .Municipality_name != null ) ' /tmp/rivmDataCumulative | jq -s 'map(.Total_reported) | add ' > /tmp/rivmTotal &
2020-08-21 08:55:05.767 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-21 08:55:18.894 Status: dzVents: Info: Handling Domoticz custom event for: "covidTrigger_jqReturn"
2020-08-21 08:55:18.894 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger: Custom event: "covidTrigger_jqReturn"
2020-08-21 08:55:18.911 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-21 08:55:18.913 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:18.914 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:18.915 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:18.916 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-21 08:55:18.916 Status: dzVents: Debug: covidTrigger: dzVents version is 3.0.2. Must use base64 encoding
2020-08-21 08:55:18.979 Status: dzVents: Debug: covidTrigger: getFilteredDataCommand: curl "http://127.0.0.1:8085/json.htm?type=command&param=customevent&event=covidTrigger_jqFilterReady&data=$(cat /tmp/rivmFiltered)"
2020-08-21 08:55:18.980 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-21 08:55:19.102 Status: dzVents: Info: Handling Domoticz custom event for: "covidTrigger_jqFilterReady"
2020-08-21 08:55:19.102 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger: Custom event: "covidTrigger_jqFilterReady"
2020-08-21 08:55:19.120 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-21 08:55:19.121 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:19.123 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Deceased covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:19.124 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Hospitalized covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:19.125 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Netherlands: Counter device adapter
2020-08-21 08:55:19.125 Status: dzVents: Debug: covidTrigger: dzVents version is 3.0.2. Must use base64 encoding
2020-08-21 08:55:19.132 Status: dzVents: Debug: covidTrigger: updateCounterDevices: {["Date_of_report"]="2020-08-20 10:00:00", ["Province"]="Noord-Brabant", ["Total_reported"]=105, ["Hospital_admission"]=13, ["Deceased"]=13, ["Municipality_code"]="GM0851", ["Municipality_name"]="Steenbergen"}
2020-08-21 08:55:19.133 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-21 08:55:19.134 Status: EventSystem: Script event triggered: /home/pi/domoticz/dzVents/runtime/dzVents.lua
2020-08-21 08:55:19.510 Status: dzVents: Info: Handling Domoticz custom event for: "covidTrigger_updateText"
2020-08-21 08:55:19.510 Status: dzVents: Info: covidTrigger: ------ Start internal script: covidTrigger: Custom event: "covidTrigger_updateText"
2020-08-21 08:55:19.514 Status: dzVents: Debug: covidTrigger: Processing device-adapter for covid-19 in Steenbergen: Text device
2020-08-21 08:55:19.516 Status: dzVents: Debug: covidTrigger: Processing device-adapter for Reported covid-19 in Steenbergen: Counter device adapter
2020-08-21 08:55:19.516 Status: dzVents: Debug: covidTrigger: dzVents version is 3.0.2. Must use base64 encoding
2020-08-21 08:55:19.516 Status: dzVents: Debug: covidTrigger: updateTextDevice: nil
2020-08-21 08:55:19.517 Status: dzVents: Info: covidTrigger: ------ Finished covidTrigger
2020-08-21 08:55:19.517 Error: dzVents: Error: (3.0.2) covidTrigger: An error occurred when calling event handler covidTrigger
2020-08-21 08:55:19.517 Error: dzVents: Error: (3.0.2) covidTrigger: ...oticz/scripts/dzVents/generated_scripts/covidTrigger.lua:214: attempt to index a nil value (local 't')
HP T630 (32GB SSD/8GB Mem) - Ubuntu 22.04.4 LTS (64Bit) - Domoticz 2024.7 with Machinon theme - RFLink - KaKu - Sonoff - Tasmota - Shelly - MQTT2Zigbee - OpenTherm Gateway - Tinytuya - IR Blaster - P1 Smart Meter - NPN Watermeter - Google Assistant
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests