def edit_values():
"""
Erlaubt die Manuell zu erfassenden WErte inzidenz07_loerrach, inzidenz07_haut_rhin und isoliert_aph_bs
zu erfassen und speichern.
"""
sql = qry['edit_values_view']
st.markdown('### Stand Datenbank')
df = db.get_recordset(conn,sql)
st.dataframe(df)
st.markdown('### Werte editieren')
#datum = st.date_input('Datum (JJJJ-MM-TT')
datum = st.text_input('Datum (JJJJ-MM-TT')
sql = qry['get_manual_edit_fields'].format(datum)
df = db.get_recordset(conn,sql)
if len(df)>0 :
val_loerrach = df.iloc[0]['inzidenz07_loerrach']
val_haut_rhin = df.iloc[0]['inzidenz07_haut_rhin']
val_aph = df.iloc[0]['isoliert_aph_bs']
else:
val_loerrach = None
val_haut_rhin = None
val_aph = None
inzidenz07_loerrach = st.text_input('7-Tage Inzidenz Landeskreis Lörrach', val_loerrach)
inzidenz07_haut_rhin = st.text_input('7-Tage Inzidenz Département Haut-Rhin', val_haut_rhin)
isoliert_aph_bs = st.text_input('Isolierte in APHs', val_aph)
if st.button('Speichern'):
inzidenz07_loerrach = db.get_db_value(inzidenz07_loerrach,3)
inzidenz07_haut_rhin = db.get_db_value(inzidenz07_haut_rhin,3)
isoliert_aph_bs = db.get_db_value(isoliert_aph_bs,2)
cmd = qry['manual_update'].format(inzidenz07_loerrach, inzidenz07_haut_rhin, isoliert_aph_bs, datum)
db.exec_non_query(conn,cmd)
st.info("Datensatz wurde gespeichert.")
def show_verteiler_verwalten():
sql = qry['person_all'].format(session_state.user_name)
df = db.get_recordset(conn, sql)
st.markdown("----")
st.markdown("**Liste der erfassten Personen**")
st.write(df)
sql = qry['person_list'].format(session_state.user_name)
person_df = db.get_recordset(conn, sql).set_index('id')['name']
person_dic = person_df.to_dict()
# Editieren/Inserten von Personen kann man wieder einfügen, wenn das nötig ist.
edit_person(person_dic)
show_upload_personen()
st.markdown('---')
st.markdown('**Verteiler**')
sql = sql = qry['lookup_code_list'].format(4)
verteiler_dic = db.get_recordset(conn, sql).set_index('id')['name'].to_dict()
verteiler = st.selectbox('Auswahl Verteiler',options=list(verteiler_dic.keys()),
format_func=lambda x: verteiler_dic[x])
sql = qry['verteiler_person_bez'].format(verteiler)
verteiler_pers_list = db.get_recordset(conn, sql)['person_id'].to_list()
verteiler_person = st.multiselect(f'Personen zuweisen zu {verteiler_dic[verteiler]} ({len(verteiler_pers_list)})',options=list(person_dic.keys()),
format_func=lambda x: person_dic[x], default=verteiler_pers_list)
if st.button('Speichern', key='speichern_verteiler'):
verteiler_speichern(verteiler, verteiler_person)
def export_ogd(config):
"""
todo: Aufruf exec [dbo].[sp04_export_csv_lagebericht] funktioniert nicht, vermutlich läuft etwas intern in DeprecationWarningexpoert csv routine falsch,
denn die erste Datei wird erzeugt aber leer
"""
st.info('Prozess OGD Export wurde gestartet')
try:
sql = qry['export_ogd_nach_test_datum']
df = db.get_recordset(conn, sql)
st.write(df)
filename = config['export_csv_path'] + 'faelle_nach_test_datum.csv'
df.to_csv(filename, sep=';', index=False)
sql = qry['export_ogd_nach_publikations_datum']
df = db.get_recordset(conn, sql)
st.write(df)
filename = config['export_csv_path'] + 'faelle_nach_publikations_datum.csv'
df.to_csv(filename, sep=';', index=False)
sql = qry['export_ogd_detail']
df = db.get_recordset(conn, sql)
st.write(df)
filename = config['export_csv_path'] + config['export_csv_filename_detail']
df.to_csv(filename, sep=';', index=False)
#sql = qry['export_ogd_nach_gemeinden']
#df = db.get_recordset(conn, sql)
#st.write(df)
#filename = config['export_csv_path'] + config['export_csv_filename_gemeinden']
#df.to_csv(filename, sep=';', index=False)
st.info('OGD Dateien wurden erstellt und gespeichert.')
except Exception as ex:
st.warning(f'Beim Export der OGD Dateien ist ein Problem aufgetreten: {repr(ex)}')
def show_summary():
"""
Startscreen, zeigt ein paar allgemeine Infos sowie den Log der letzten 2 Tage und den Stand der
zu publizierenden Zahlen.
"""
heute = datetime.strftime(datetime.now(), '%d.%m.%Y')
st.markdown(f'### Log Einträge')
sql = qry['log_today']
df = db.get_recordset(conn,sql).set_index('tag', 'zeit')
st.write(df)
st.markdown(f'### Werte ({heute})', unsafe_allow_html=True)
sql = qry['compare_values']
df = db.get_recordset(conn,sql).set_index('Feld')
st.dataframe(df,1000,1000)
def edit_person(person_dic: dict):
person_id = st.selectbox('Person auswählen:', options=list(person_dic.keys()),
format_func=lambda x: person_dic[x])
sql = qry['person_info'].format(person_id)
person = db.get_recordset(conn, sql).iloc[0]
person_str = f"""Nachname:\t{person['nachname']}\n
Vorname:\t{person['vorname']}\n
Email:\t{person['email']}\n
Mobile:\t{person['mobile']}
"""
print(person_str)
st.info(person_str)
#ist_benutzer = 1 if st.checkbox('Ist Operator/in', value = (person['ist_benutzer'] == 1)) else 0
#col1, col2, col3 = st.beta_columns([0.1, 0.1, 0.5])
#if col1.button('Speichern'):
# sql = qry['person_update'].format(nachname, vorname, email, mobile, ist_benutzer, person_id)
# if db.exec_non_query(conn, sql) == 1:
# st.info('Der Datensatz wurde erfolgreich gespeichert')
# st.experimental_rerun()
# else:
## st.warning(f'Der Datensatz konnte nicht gespeichert werden.')
#elif col2.button('Neu'):
# sql = qry['person_new']
# if db.exec_non_query(conn, sql) == 1:
# st.info('Es wurde eine neue Person angelegt')
# st.experimental_rerun()
# else:
# st.warning(f'Die Person konnte nicht angelegt werden.')
if st.button('Person Löschen'):
delete_person(person_id)
def get_config():
"""
Gibt die Konfiguration in Form eines Dataframes und eines dictionarys zurück.
Der Dataframe wird verwendet um die Gesamtkonfiguration anzuzeigen und zu editieren.
Das dictionary wird verwendet, um die records in der App zu verwenden, z.B.
als config['schluessel']
"""
sql = qry['get_konfig']
cfg_df = db.get_recordset(conn,sql).set_index('schluessel')
cfg_dic = cfg_df['wert'].to_dict()
return cfg_df, cfg_dic
def show_werte_ueberpruefen():
st.markdown(f"### Zeitreihe")
selected_fields = st.multiselect('Auswahl Felder', const.FELDER_ZEITREIHE, default = const.FELDER_ZEITREIHE[:3])
selected_fields_csv = ','.join(selected_fields)
sql = qry['check_values_table'].format(selected_fields_csv)
df = db.get_recordset(conn, sql)
st.dataframe(df)
df_melted = pd.melt(df,id_vars=['datum'], value_vars=selected_fields)
plot(df_melted)
st.markdown(f"### Verstorbene")
sql = qry['verstorbene']
df = db.get_recordset(conn, sql)
st.dataframe(df)
st.markdown(f"### Inzidenzen")
selected_fields = st.multiselect('Auswahl Inzidenz-Felder', const.FELDER_INZIDENZEN, default = const.FELDER_INZIDENZEN[:7])
selected_fields_csv = ','.join(selected_fields)
sql = qry['inzidenzen'].format(selected_fields_csv)
df = db.get_recordset(conn, sql)
st.dataframe(df)
df_melted = pd.melt(df,id_vars=['datum'], value_vars=selected_fields)
plot(df_melted)
def calculate_values():
"""
Aggregiert die Einzelwerte in die Zeitreihetabelle und berechnet die Inzidenzen.
"""
st.info('Inzidenzen und Differenzen zu Vortags Werten werden berechnet')
sql = qry['run_update']
if db.exec_non_query(conn,sql) == 1:
st.info('Berechnungen wurden durchgeführt, die wichtigsten Resultate sind in untenstehender Tabelle aufgelistet')
else:
st.info('Bei den Berechnungen ist ein Problem aufgetreten')
sql = qry['inzidenzen_qs']
df = db.get_recordset(conn,sql)
st.write(df)
def change_password(session_state):
sql = qry['user_info'].format(session_state.user_name)
df = db.get_recordset(conn, sql)
row = df.iloc[0]
st.markdown(f'Benutzer: {row.nachname} {row.vorname}')
pwd1 = st.text_input('Neues Passwort',type='password')
pwd2 = st.text_input('Passwort bestätigen',type='password')
if st.button('Passwort ändern'):
if pwd1 == pwd2 and len(pwd1) > 0:
context = get_crypt_context()
sql = qry['change_pwd'].format(context.hash(pwd1), row['benutzer'])
if db.exec_non_query(conn, sql) == 1:
st.info('Passwort wurde erfolgreich geändert')
else:
st.warning('Das Passwort konnte leider nicht geändert werden, kontaktieren sie den Systemadministrator')
else:
t.warning('Passwörter stimmen nicht überein, versuchen sie es nochmals')
def get_bl_cases(config):
url = config['url_covid_faelle_kantone'].format('BL')
s = requests.get(url, proxies=proxy_dict).text
records = json.loads(s)
records = records['records']
df_json = pd.DataFrame(records)
lst = df_json['fields']
st.info("Datei mit BL-Fällen wurde geladen")
for x in lst:
cmd = qry['update_faelle_bl'].format(x['ncumul_conf'],x['date'])
if db.exec_non_query(conn,cmd) != 1:
st.markdown(f'Befehl `{cmd}` konnte nicht ausgeführt werden.')
st.info("BL Fälle wurden in Datenbank gespeichert")
sql = qry['show_bl_cases']
df = db.get_recordset(conn,sql)
st.dataframe(df, 1000, 1000)
def import_file(config):
"""
Ruft die stored procedure auf dem db-server auf und startet den Import
der Einzeldaten
"""
filename = config['source_path'] + config['source_filename']
try:
st.info(f"{config['source_filename']} wird gelesen")
df = pd.read_excel(filename)
sql = qry['db_fields']
df_fields = db.get_recordset(conn, sql)
raw_fields = df_fields['field_name'].to_list()
db_fields = df_fields['db_field_name'].to_list()
st.info('Import in DB wird gestartet, dies kann einige Minuten dauern.')
db.exec_non_query(conn, qry['truncate_rohdaten'])
db.append_pd_table(df, 'lagebericht_roh', raw_fields)
st.info(f"{config['source_filename']} wurde in DB importiert: {len(df)} Zeilen")
# formatiert die list zu einer comma separierten Text im Format [feld 1], [feld 2]...
raw_fields_str = '[' + '], ['.join(raw_fields) + ']'
db_fields_str = '[' + '], ['.join(db_fields) + ']'
sql_cmd = qry['insert_lagebericht'].format(db_fields_str, raw_fields_str)
if db.exec_non_query(conn, sql_cmd) == 1:
st.info('Felder wurden in DB Tabelle formatiert')
else:
st.warning('Felder konnten in der DB Tabelle nicht formatiert werden')
archive_file = f"{config['source_path']}archiv\\{date.today().strftime('%Y-%m-%d')}_{config['source_filename']}"
sql = qry['rowcount_lagebericht_today']
rows = db.get_value(conn, sql)
st.info(f"QS: {rows} Zeilen mit Datum heute in Lagebericht-Tabelle")
shutil.copyfile(filename, archive_file)
os.remove(filename)
csv_file = f"imp_{date.today().strftime('%Y-%m-%d')}.csv"
archive_file = config['source_path'] + 'archiv\\' + csv_file
df.to_csv(archive_file, index=False)
st.info('Import Datei wurde gelöscht')
except Exception as ex:
st.warning(f'Die Datei konnte nicht importiert werden: {ex}')
def get_ch_cases(config):
"""
Speichert den BAG lagebericht lokal ab, öffnet die Excel Datei und speichert die CH-Fälle. Da pd.read_excel keinen proxys parameter kennt, muss die
Datei zuerst geladen und lokal im Verzeichnis data abgespeichert werden.
"""
url = config['url_bag_lagebericht']
r = requests.get(url,proxies=proxy_dict) # create HTTP response object
filename = config['bag_lagebericht_lokal']
with open(filename,'wb') as f:
f.write(r.content)
df = pd.read_excel(filename, sheet_name='COVID19 Zahlen',header=6)
st.info("Excel Datei wurde geladen")
for i in range(len(df)) :
datum = df.loc[i, "Datum"]
cmd = qry['update_faelle_ch'].format(df.loc[i, "Fallzahlen pro Tag, kumuliert"], datum)
if db.exec_non_query(conn,cmd) != 1:
st.markdown(f'Befehl `{cmd}` konnte nicht ausgeführt werden.')
st.info("CH Fälle wurden in Datenbank gespeichert")
sql = qry['show_ch_faelle']
df = db.get_recordset(conn,sql)
st.dataframe(df, 1000, 1000)
def show_login(session_state):
st.write('## Login-Informationen')
session_state.user_name = st.text_input('Benutzername', value = session_state.user_name)
session_state.pwd = st.text_input('Passwort', value=session_state.pwd, type='password')
col1, col2 = st.beta_columns((1,12))
if col1.button('Login'):
if is_valid_password(session_state.user_name, session_state.pwd):
st.info('Willkommen beim Covid-Mailgenerator')
session_state.logged_in = True
sql = qry['user_info'].format(session_state.user_name)
df = db.get_recordset(conn,sql)
session_state.email = df.iloc[0]['email']
session_state.user = df.iloc[0]['benutzer']
log_entry(const.PROCESS['login'], const.RESULT_SUCCESS, '')
st.experimental_rerun()
show_menu(session_state)
else:
warning = 'Benutzername oder Passwort stimmen nicht'
log_entry(const.PROCESS['login'], const.RESULT_ERROR, warning)
st.warning(warning)
if col2.button('Passwort zurücksetzen'):
reset_password(session_state)
def reset_password(session_state):
sql = qry['user_info'].format(session_state.user_name)
df = db.get_recordset(conn,sql)
if len(df) > 0:
mailadresse = df.iloc[0]['email']
begruessung = df.iloc[0]['begruessung']
subject = 'Das Passwort für die Covid-19 Lagebericht App wurde zurückgesetzt'
pwd_neu = '{:07d}'.format(random.randint(0,999999))
context = get_crypt_context()
sql = qry['change_pwd'].format(context.hash(pwd_neu), session_state.user_name)
if db.exec_non_query(conn, sql) == 1:
if db.exec_non_query(conn, sql):
text = f"""{begruessung},<br>dein Passwort wurde zurückgesetzt auf <br><b>{pwd_neu}</b><br>
Bitte verwende nach dem ersten Login die Option `Passwort zurücksetzen` und setze dein eigenes Passwort.
"""
sql = qry['send_reset_mail'].format(mailadresse,subject,text)
if db.exec_non_query(conn, sql) == 1:
text = f"""{begruessung},<br>dein neues Passwort wurde an deine Mailadresse ({mailadresse}) geschickt. Bitte verwende nach dem ersten Login die
Menuoption `Passwort ändern`."""
st.markdown(text, unsafe_allow_html=True)
else:
st.warning('Beim reset des Passwortes gab es Probleme, kontaktieren sie den Systemadministrator')
else:
st.warning('Achte darauf, dass im Feld Benutzername dein Benutzerkürzel steht, z.B. sghxyz')
def get_bs_cases(config):
url = config['url_bs_hospitalisierte']
s = requests.get(url, proxies=proxy_dict).text
records = json.loads(s)
records = records['records']
df_json = pd.DataFrame(records)
lst = df_json['fields']
st.info("Datei mit BS-Fällen wurde geladen")
for x in lst:
if 'current_hosp_resident' in x:
# st.write(x['current_hosp_resident'],x['current_hosp'],x['current_icu'],x['date'])
cmd = qry['update_faelle_bs'].format(x['current_hosp_resident'],
x['current_hosp'],
x['current_icu'],
(1 if x['data_from_all_hosp'] == 'True' else 0),
x['date'] )
if db.exec_non_query(conn,cmd) != 1:
st.markdown(f'Befehl `{cmd}` konnte nicht ausgeführt werden.')
st.info("BS Hospitalisierte wurden in Datenbank gespeichert")
sql = qry['show_bs_hospitalised']
df = db.get_recordset(conn,sql)
st.dataframe(df, 1000, 1000)
def make_plots(config):
"""
Erstellt die 2 Grafiken (attachment des Mailversands):
Fig 1. Neue Fälle als Barchart, 7 Tagesmittel als Linie
Fig2: Fälle Bs kumuliert.
warum muss ihc hier config übergeben,
"""
file = config['export_csv_path'] + config['export_csv_filename_datum']
try:
#df = pd.read_csv(file, sep=';')
sql = qry['plot_data']
df = db.get_recordset(conn,sql)
# filtert Nullwerte raus
# fetch & enable German format & timeFormat locales.
s = requests.get('https://raw.githubusercontent.com/d3/d3-format/master/locale/de-DE.json', proxies=proxy_dict).text
de_format = json.loads(s)
s = requests.get('https://raw.githubusercontent.com/d3/d3-time-format/master/locale/de-DE.json', proxies=proxy_dict).text
de_time_format = json.loads(s)
alt.renderers.set_embed_options(formatLocale=de_format, timeFormatLocale=de_time_format)
#locale.setlocale(locale.LC_ALL, "de_CH")
df['datum'] = df['datum'].apply(lambda x:
datetime.strptime(x,'%Y-%m-%d'))
df['7-Tage Schnitt'] = '7-Tage Mittel'
df['Neumeldungen'] = 'Neumeldungen'
min_date = '2020-02-29'
max_date = max(df['datum'].to_list())
day=calendar.monthrange(max_date.year, max_date.month)[1]
max_date = datetime(max_date.year,max_date.month,day) + timedelta(days=1)
domain_pd = pd.to_datetime([min_date, max_date]).astype(int) / 10 ** 6
today = datetime.now().strftime("%d. %b %Y")
bar = alt.Chart(data=df, title='Neumeldungen BS, Stand: ' + today).encode(
).mark_bar(width = 2).encode(
x=alt.X('datum:T', axis=alt.Axis(title="",format="%b %y"), scale = alt.Scale(domain=list(domain_pd) )),
y=alt.Y('faelle_bs:Q', axis=alt.Axis(title="Anzahl Fälle")),
tooltip=['faelle_bs', 'datum'],
color=alt.Color('Neumeldungen:N', legend=alt.Legend(title=""))
)
line = bar.mark_line().encode(
y='mittel_07_tage_bs:Q',
color=alt.Color('7-Tage Schnitt:N', legend=alt.Legend(title=""))
)
fig1 = (bar + line).properties(width=600)
kumuliert= alt.Chart(data=df, title='Meldungen kumulativ BS, Stand: ' + today).encode(
).mark_line(color='blue').encode(
x=alt.X('datum:T', axis=alt.Axis(title="", format="%b %y"), scale = alt.Scale(domain=list(domain_pd) )),
y=alt.Y('faelle_bs_kum:Q', axis=alt.Axis(title="Anzahl Fälle")), # scale = alt.Scale(domain=(0, 4500))
tooltip=['faelle_bs_kum', 'datum']
)
fig2 = kumuliert.properties(width=600)
st.write(fig1 & fig2)
save(fig1 & fig2, config['temp_figuren_lokal'])
st.info(f"Datei wurde gespeichert unter: {config['temp_figuren_lokal']}")
except Exception as ex:
st.error(ex)