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.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.
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.
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¶m=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¶m=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
}
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