static DateTime startDt = DateTime.Now.Date.AddDays(-10); //new DateTime(2020,09,04);
public static void ProcessExcelObsazenost(string fn, HlidacStatu.Api.V2.Dataset.Typed.Dataset <NemocniceData> ds)
{
Devmasters.Logging.Logger.Root.Info($"ProcessExcelObsazenost {fn} ");
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var p = new ExcelPackage(new System.IO.FileInfo(fn)))
{
foreach (var ws in p.Workbook.Worksheets)
{
//first date 2020-09-04
for (int row = 11; row < 100000; row++)
{
var dt = ws.Cells[row, 1].GetValue <DateTime?>();
if (dt.HasValue && dt.Value >= startDt)
{
string id = "id_" + dt.Value.ToString("yyyy-MM-dd");
Console.Write(dt.Value.ToString("yyyy-MM-dd "));
NemocniceData data = null;
try
{
data = ds.GetItem(id);
}
catch (Exception)
{
}
if (data == null)
{
continue;
}
var region = data.regions.FirstOrDefault(m => m.region == NemocniceData.ExcelWorkBookToRegion(ws.Name));
if (region != null)
{
var idx = data.regions.IndexOf(region);
data.regions[idx].Pacienti_bezpriznaku = ws.Cells[row, 7].GetValue <int>();
data.regions[idx].Pacienti_lehky = ws.Cells[row, 8].GetValue <int>();
data.regions[idx].Pacienti_stredni = ws.Cells[row, 9].GetValue <int>();
data.regions[idx].Pacienti_tezky = ws.Cells[row, 10].GetValue <int>();
data.regions[idx].Pacienti_zemreli = ws.Cells[row, 22].GetValue <int>() - ws.Cells[row - 1, 22].GetValue <int>();
Devmasters.Logging.Logger.Root.Info($"ProcessExcelObsazenost save {ws.Name} - {dt.Value.ToString("yyyy-MM-dd ")} - {region.region} ");
ds.AddOrUpdateItem(data, HlidacStatu.Api.V2.Dataset.Typed.ItemInsertMode.rewrite);
}
else
{
Console.WriteLine("not found region " + ws.Name);
}
}
}
}
}
}
public NemocniceData CelaCR()
{
NemocniceData cr = new NemocniceData();
List <Region> cdH = new List <Region>();
var fd = NemocniceData.Aggregate(this.regions); //fd.regionId = 0;
fd.name = "Celá ČR";
fd.region = "CR";
cdH.Add(fd);
cr.regions = cdH.ToList();
cr.lastUpdated = this.lastUpdated;
return(cr);
}
public NemocniceData PoKrajich()
{
NemocniceData krajF = new NemocniceData();
List <Region> krajFH = new List <Region>();
int krajId = 0;
List <string> kraje = new string[] { "PHA", "STC", "JHM", "MSK" }.Union(this.regions.Select(m => m.region).Distinct()).ToList();
foreach (var kraj in kraje.OrderBy(o => kraje.IndexOf(o)))
{
var hsF = this.regions.Where(m => m.region == kraj).ToArray();
var fd = NemocniceData.Aggregate(hsF); //fd.regionId = ++krajId;
fd.name = hsF.First().regionFull();
krajFH.Add(fd);
}
krajF.regions = krajFH.ToList(); krajF.lastUpdated = this.lastUpdated;
return(krajF);
}
private static void ProcessPacienti()
{
DateTime startDt = DateTime.Now.Date.AddDays(-30);
using (var reader = new StreamReader(new System.Net.Http.HttpClient().GetStreamAsync("https://onemocneni-aktualne.mzcr.cz/api/v2/covid-19/hospitalizace.csv").Result))
{
using (var csv = new CsvReader(reader, new CsvHelper.Configuration.CsvConfiguration(System.Globalization.CultureInfo.InvariantCulture)
{
Delimiter = ",",
IgnoreBlankLines = true,
HasHeaderRecord = true,
TrimOptions = CsvHelper.Configuration.TrimOptions.Trim
}))
{
//csv.Context.RegisterClassMap<NemocniceMap>();
//var records = csv.GetRecords<Nemocnice>().ToArray();
csv.Read();
csv.ReadHeader();
while (csv.Read())
{
DateTime?dt = Devmasters.DT.Util.ParseDateTime(csv["datum"], null);
if (dt.HasValue && dt.Value >= startDt)
{
string id = "id_" + dt.Value.ToString("yyyy-MM-dd");
Console.Write(dt.Value.ToString("yyyy-MM-dd "));
NemocniceData data = null;
try
{
data = ds.GetItem(id);
}
catch (Exception)
{
}
if (data == null)
{
continue;
}
}
}
}
}
}
public static NemocniceData Diff(NemocniceData f, NemocniceData l)
{
NemocniceData d = new NemocniceData();
d.lastUpdated = new DateTime((l.lastUpdated - f.lastUpdated).Ticks);
List <NemocniceData.Region> hs = new List <Region>();
foreach (var fh in f.regions)
{
Region h = new Region();
Region lh = l.regions.FirstOrDefault(m => m.region == fh.region);
if (lh != null)
{
hs.Add(Diff(fh, lh));
}
}
d.regions = hs.ToList();
return(d);
}
private static void GetExcelFromUzisZIP_Old()
{
string fn = GetExecutingDirectoryName() + $"\\dip-report-kraje-{DateTime.Now:yyyyMMdd-HHmmss}.xlsx";
string fnTemp = System.IO.Path.GetTempFileName();
//nejnovejsi ZIP
for (int i = 0; i < 7; i++)
{
DateTime dt = DateTime.Now.Date.AddDays(-1 * i);
string zipUrl = $"https://share.uzis.cz/s/fbCgFKagS6fCrzc/download?path=%2F{dt.Year}-{dt.ToString("MM")}%20({dt.ToString("MMMM", System.Globalization.CultureInfo.GetCultureInfo("cs"))}%20{dt.Year})&files={dt:yyyy-MM-dd}-dostupnost-kapacit.zip"; //$"https://share.uzis.cz/s/fbCgFKagS6fCrzc/download?path=%2F&files={dt:yyyy-MM-dd}-dostupnost-kapacit.zip";
Devmasters.Logging.Logger.Root.Info($"Getting ZIP url {zipUrl}");
using (Devmasters.Net.HttpClient.URLContent net = new Devmasters.Net.HttpClient.URLContent(zipUrl))
{
try
{
System.IO.File.WriteAllBytes(fnTemp, net.GetBinary().Binary);
break;
}
catch (Exception e)
{
}
}
}
try
{
Devmasters.Logging.Logger.Root.Info("Getting Excel from ZIP");
//get xlsx from ZIP
using (ZipArchive archive = ZipFile.OpenRead(fnTemp))
{
foreach (ZipArchiveEntry entry in archive.Entries)
{
if (entry.FullName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
{
entry.ExtractToFile(fn);
}
}
}
if (false) //download xls from web
{
//find xls url
string openDataPage = "https://onemocneni-aktualne.mzcr.cz/api/v2/covid-19";
Uri xlsUrl = null;
Devmasters.Logging.Logger.Root.Info("Getting URL of XLS from " + openDataPage);
using (Devmasters.Net.HttpClient.URLContent net = new Devmasters.Net.HttpClient.URLContent(openDataPage))
{
Devmasters.Logging.Logger.Root.Info("Getting Excel URL");
var html = net.GetContent().Text;
Devmasters.XPath xp = new Devmasters.XPath(html);
var node = xp.GetNode("//a[contains(@href,'dip-report-kraje.xlsx')]");
if (node != null)
{
xlsUrl = new Uri("https://onemocneni-aktualne.mzcr.cz" + node.Attributes["href"].Value);
}
}
if (xlsUrl == null)
{
Devmasters.Logging.Logger.Root.Fatal("No URL to download");
return;
}
using (Devmasters.Net.HttpClient.URLContent net = new Devmasters.Net.HttpClient.URLContent(xlsUrl.AbsoluteUri))
{
Devmasters.Logging.Logger.Root.Info("Getting Excel");
System.IO.File.WriteAllBytes(fn, net.GetBinary().Binary);
}
}
//debug
//fn = @"c:\!!\ONLINE_DISPECINK_IP_dostupne_kapacity_20201014_05-50.xlsx";
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var p = new ExcelPackage(new System.IO.FileInfo(fn)))
{
ExcelWorksheet ws = p.Workbook.Worksheets[1];
//find date
//Analýza provedena z exportu 01.10.2020
for (int row = 1; row < 100000; row++)
{
Console.Write(".");
var txt = ws.Cells[row, 1].GetValue <string>();
if (txt != null && txt.StartsWith("Stav k datu:"))
{
string head = txt.Replace("Stav k datu: ", "");
string sdate = Devmasters.RegexUtil.GetRegexGroupValue(head, @" \s* (?<dt>\d{1,2}\s*\.\s*\d{1,2}\s*\.\s*\d{4} )", "dt");
DateTime dt = Devmasters.DT.Util.ToDate(sdate).Value;
string id = "id_" + dt.ToString("yyyy-MM-dd");
NemocniceData nd = null;
try
{
nd = ds.GetItem(id); // new NemocniceData();
}
catch (Exception)
{
}
if (nd == null)
{
nd = new NemocniceData();
nd.regions = new List <NemocniceData.Region>();
}
nd.lastUpdated = dt;
nd.id = id;
Console.WriteLine(".");
Devmasters.Logging.Logger.Root.Info(nd.lastUpdated.ToString());
row = row + 4;
List <NemocniceData.Region> finalRegs = new List <NemocniceData.Region>();
for (int regs = 0; regs < 14; regs++)
{
string region = ws.Cells[row + regs, 1].GetValue <string>();
NemocniceData.Region r = nd.regions.FirstOrDefault(m => m.region == region); //new NemocniceData.Region();
if (r == null)
{
r = new NemocniceData.Region();
}
r.lastModified = nd.lastUpdated;
r.region = region;
r.UPV_celkem = ws.Cells[row + regs, 4].GetValue <int>();
r.UPV_volna = ws.Cells[row + regs, 5].GetValue <int>();
r.ECMO_celkem = ws.Cells[row + regs, 5].GetValue <int>();
r.ECMO_volna = ws.Cells[row + regs, 6].GetValue <int>();
r.CRRT_celkem = ws.Cells[row + regs, 8].GetValue <int>();
r.CRRT_volna = ws.Cells[row + regs, 9].GetValue <int>();
r.IHD_celkem = ws.Cells[row + regs, 11].GetValue <int>();
r.IHD_volna = ws.Cells[row + regs, 12].GetValue <int>();
r.AROJIP_luzka_celkem = ws.Cells[row + regs, 14].GetValue <int>();
r.AROJIP_luzka_covid = ws.Cells[row + regs, 15].GetValue <int>();
r.AROJIP_luzka_necovid = ws.Cells[row + regs, 16].GetValue <int>();
r.Standard_luzka_s_kyslikem_celkem = ws.Cells[row + regs, 18].GetValue <int>();
r.Standard_luzka_s_kyslikem_covid = ws.Cells[row + regs, 19].GetValue <int>();
r.Standard_luzka_s_kyslikem_necovid = ws.Cells[row + regs, 20].GetValue <int>();
r.Lekari_AROJIP_celkem = ws.Cells[row + regs, 22].GetValue <int>();
r.Lekari_AROJIP_dostupni = ws.Cells[row + regs, 23].GetValue <int>();
r.Sestry_AROJIP_celkem = ws.Cells[row + regs, 25].GetValue <int>();
r.Sestry_AROJIP_dostupni = ws.Cells[row + regs, 26].GetValue <int>();
r.Ventilatory_prenosne_celkem = ws.Cells[row + regs, 28].GetValue <int>();
r.Ventilatory_operacnisal_celkem = ws.Cells[row + regs, 29].GetValue <int>();
r.Standard_luzka_celkem = ws.Cells[row + regs, 30].GetValue <int>();
r.Standard_luzka_s_monitor_celkem = ws.Cells[row + regs, 31].GetValue <int>();
finalRegs.Add(r);
}
nd.regions = finalRegs;
row = row + 16;
Devmasters.Logging.Logger.Root.Info("Saving");
ds.AddOrUpdateItem(nd, HlidacStatu.Api.V2.Dataset.Typed.ItemInsertMode.rewrite);
}
}
}
}
catch (Exception e)
{
Devmasters.Logging.Logger.Root.Error("Processing ZIP XLS error", e);
SendMail("*****@*****.**", "Selhalo zpracovani dat z UZIS", e.ToString(), "");
}
}
private static void OpenDataDIP()
{
List <NemocniceData.Region> raw = new List <NemocniceData.Region>();
//process https://dip.mzcr.cz/api/v1/kapacity-intenzivni-pece-vlna-2.csv
DateTime mindate = DateTime.Now.Date.AddDays(-120);
using (var net = new System.Net.Http.HttpClient().GetStreamAsync("https://dip.mzcr.cz/api/v1/kapacity-intenzivni-pece-zdravotnicke-zarizeni-04-2021.csv"))
{
using (System.IO.StreamReader rr = new StreamReader(net.Result))
{
var csv = new CsvHelper.CsvReader(rr, new CsvHelper.Configuration.CsvConfiguration(System.Globalization.CultureInfo.GetCultureInfo("cs"))
{
HasHeaderRecord = true, Delimiter = ","
});
csv.Read(); csv.ReadHeader();
//csv.Read();//skip second line
while (csv.Read())
{
DateTime?date = Devmasters.DT.Util.ParseDateTime(csv.GetField <string>("datum")?.Trim(), null);
if (date == null)
{
continue;
}
DateTime dt = date.Value;
if (dt < mindate)
{
continue;
}
Console.WriteLine(".");
string kraj_nuts_kod = csv.GetField <string>("kraj_nuts_kod");
string region = Kraje[kraj_nuts_kod];
var r = new NemocniceData.Region();
r.lastModified = date.Value;
r.region = region;
r.name = csv.GetField <string>("zz_nazev");
r.UPV_celkem = 0;
r.UPV_volna = 0;
r.ECMO_celkem = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("ecmo_kapacita_celkem"), 0).Value;
r.ECMO_volna = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("ecmo_kapacita_volna"), 0).Value;
r.CRRT_celkem = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("cvvhd_kapacita_celkem"), 0).Value;
r.CRRT_volna = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("cvvhd_kapacita_volna"), 0).Value;
r.IHD_celkem = 0;
r.IHD_volna = 0;
r.AROJIP_luzka_celkem = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("luzka_upv_niv_kapacita_celkem"), 0).Value;
r.AROJIP_luzka_covid = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("luzka_upv_niv_kapacita_volna_covid_pozitivni"), 0).Value;
r.AROJIP_luzka_necovid = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("luzka_upv_niv_kapacita_volna_covid_negativni"), 0).Value;
r.Standard_luzka_s_kyslikem_celkem = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("luzka_standard_kyslik_kapacita_celkem"), 0).Value;
r.Standard_luzka_s_kyslikem_covid = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("luzka_standard_kyslik_kapacita_volna_covid_pozitivni"), 0).Value;
r.Standard_luzka_s_kyslikem_necovid = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("luzka_standard_kyslik_kapacita_volna_covid_negativni"), 0).Value;
//r.Lekari_AROJIP_celkem = 0;
//r.Lekari_AROJIP_dostupni = 0;
//r.Sestry_AROJIP_celkem = 0;
//r.Sestry_AROJIP_dostupni = 0;
//r.Standard_luzka_celkem = 0;
//r.Standard_luzka_s_monitor_celkem = 0;
r.Ventilatory_prenosne_celkem = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("ventilatory_prenosne_kapacita_celkem"), 0).Value;
r.Ventilatory_operacnisal_celkem = Devmasters.TextUtil.ConvertToInt(csv.GetField <string>("ventilatory_operacni_sal_kapacita_celkem"), 0).Value;
raw.Add(r);
}
}
}
Devmasters.Logging.Logger.Root.Info("Saving");
var dates = raw.Select(m => m.lastModified).Distinct();
var kraje = raw.Select(m => m.region).Distinct();
foreach (var dt in dates.Where(m => m > mindate))
{
Console.WriteLine(dt);
NemocniceData nem = null; //new NemocniceData();
var id = "id_" + dt.ToString("yyyy-MM-dd");
List <NemocniceData.Region> nr = new List <NemocniceData.Region>();
foreach (var kr in kraje)
{
var lines = raw.Where(m => m.lastModified == dt && m.region == kr).ToArray();
var r = NemocniceData.Aggregate(lines);
r.region = kr;
nr.Add(r);
}
try
{
nem = ds.GetItem(id); // new NemocniceData();
}
catch (Exception)
{
}
if (nem == null)
{
nem = new NemocniceData();
}
nem.id = id;
nem.regions = nr;
nem.lastUpdated = dt;
ds.AddOrUpdateItem(nem, HlidacStatu.Api.V2.Dataset.Typed.ItemInsertMode.rewrite);
}
//ds.AddOrUpdateItem(nd, HlidacStatu.Api.V2.Dataset.Typed.ItemInsertMode.rewrite);
}