def __get_and_verify(id, token) -> EmailSub:
sub = EmailSub.query.get_or_404(id)
if not sub.verify_token(token):
b = Forbidden()
b.description = 'invalid token'
raise b
return sub
def send_notifications():
if not request.headers.get('X-API-KEY') == os.getenv('API_KEY'):
f = Forbidden()
f.description = 'Invalid API key'
raise f
__delete_unverified_emails()
__rotate_tokens()
c = CaseDevelopments('cases_per_county_and_day_risklayer')
sevendaysago = (datetime.now(timezone.utc) - timedelta(days=8)).strftime('%Y-%m-%d')
tomorrow = (datetime.now(timezone.utc) + timedelta(days=1)).strftime('%Y-%m-%d')
de = c.getCountry(fromTime=sevendaysago, toTime=tomorrow, idCountry='de')
de_developments = de['properties']['developments']
de_today = de_developments[-1]
de_24h = de_developments[-2]
de_72h = de_developments[-4]
de_7d = de_developments[-8]
prognosis = RisklayerPrognosis.query.order_by(RisklayerPrognosis.datenbestand.desc()).limit(1)[0]
sql_result = db.engine.execute('''
SELECT id, email, token, lang, c.ags, MAX(c.updated_at),
(CASE WHEN c.updated_at::date = e.last_email_sent::date THEN true ELSE false END) AS second_email
FROM email_subs e
JOIN email_subs_counties esc on e.id = esc.sub_id
JOIN cases_lk_risklayer c ON esc.ags = c.ags
WHERE c.updated_today = true AND c.date = now()::date
AND c.updated_at > e.last_email_sent AND e.verified = true
GROUP BY id, email, token, lang, c.ags, c.updated_at::date, e.last_email_sent::date
''')
num_emails = 0
for row in sql_result:
lk = c.getCounty(fromTime=sevendaysago, toTime=tomorrow, idCounty=row['ags'])
lk_developments = lk['properties']['developments']
lk_today = lk_developments[-1]
lk_24h = lk_developments[-2]
lk_72h = lk_developments[-4]
lk_7d = lk_developments[-8]
desc = lk['properties']['description']
name = lk['properties']['name']
sub: EmailSub = EmailSub.query.get_or_404(row['id'])
sub.send_email(
subject=f'[CoronaVis] Neue Daten für {desc} {name}',
sender='*****@*****.**',
template='mail/county_notification',
sub_id=row['id'],
token=decrypt_message(row['token'].tobytes()),
second_email=row['second_email'],
county_desc=desc,
county_name=name,
county_population=round(lk_today['population']),
county_last_updated=lk_today['last_updated'],
county_cases_total=lk_today['cases'],
county_cases_total_100k=round(__100k_change(lk_today['cases'], 0, lk_today['population']), 2),
county_cases_24=__diff_str(lk_today['cases'] - lk_24h['cases']),
county_cases_24_prc=__diff_str(__prc_change(lk_today['cases'], lk_24h['cases'])),
county_cases_24_100k=__diff_str(__100k_change(lk_today['cases'], lk_24h['cases'], lk_today['population'])),
county_cases_72=__diff_str(lk_today['cases'] - lk_72h['cases']),
county_cases_72_prc=__diff_str(__prc_change(lk_today['cases'], lk_72h['cases'])),
county_cases_72_100k=__diff_str(__100k_change(lk_today['cases'], lk_72h['cases'], lk_today['population'])),
county_cases_7=__diff_str(lk_today['cases'] - lk_7d['cases']),
county_cases_7_prc=__diff_str(__prc_change(lk_today['cases'], lk_7d['cases'])),
county_cases_7_100k=__diff_str(__100k_change(lk_today['cases'], lk_7d['cases'], lk_today['population'])),
county_deaths_total=lk_today['deaths'],
county_deaths_total_100k=round(__100k_change(lk_today['deaths'], 0, lk_today['population']), 2),
county_deaths_24=__diff_str(lk_today['deaths'] - lk_24h['deaths']),
county_deaths_24_prc=__diff_str(__prc_change(lk_today['deaths'], lk_24h['deaths'])),
county_deaths_24_100k=__diff_str(
__100k_change(lk_today['deaths'], lk_24h['deaths'], lk_today['population'])),
county_deaths_72=__diff_str(lk_today['deaths'] - lk_72h['deaths']),
county_deaths_72_prc=__diff_str(__prc_change(lk_today['deaths'], lk_72h['deaths'])),
county_deaths_72_100k=__diff_str(
__100k_change(lk_today['deaths'], lk_72h['deaths'], lk_today['population'])),
county_deaths_7=__diff_str(lk_today['deaths'] - lk_7d['deaths']),
county_deaths_7_prc=__diff_str(__prc_change(lk_today['deaths'], lk_7d['deaths'])),
county_deaths_7_100k=__diff_str(__100k_change(lk_today['deaths'], lk_7d['deaths'], lk_today['population'])),
num_counties_reported=de_today['num_counties_reported'],
num_counties_total=de_today['num_counties_total'],
prognosis=round(prognosis.prognosis),
country_population=round(de_today['population']),
country_last_updated=de_today['last_updated'],
country_cases_total=de_today['cases'],
country_cases_total_100k=round(__100k_change(de_today['cases'], 0, de_today['population']), 2),
country_cases_24=__diff_str(de_today['cases'] - de_24h['cases']),
country_cases_24_prc=__diff_str(__prc_change(de_today['cases'], de_24h['cases'])),
country_cases_24_100k=__diff_str(__100k_change(de_today['cases'], de_24h['cases'], de_today['population'])),
country_cases_72=__diff_str(de_today['cases'] - de_72h['cases']),
country_cases_72_prc=__diff_str(__prc_change(de_today['cases'], de_72h['cases'])),
country_cases_72_100k=__diff_str(__100k_change(de_today['cases'], de_72h['cases'], de_today['population'])),
country_cases_7=__diff_str(de_today['cases'] - de_7d['cases']),
country_cases_7_prc=__diff_str(__prc_change(de_today['cases'], de_7d['cases'])),
country_cases_7_100k=__diff_str(__100k_change(de_today['cases'], de_7d['cases'], de_today['population'])),
country_deaths_total=de_today['deaths'],
country_deaths_total_100k=round(__100k_change(de_today['deaths'], 0, de_today['population']), 2),
country_deaths_24=__diff_str(de_today['deaths'] - de_24h['deaths']),
country_deaths_24_prc=__diff_str(__prc_change(de_today['deaths'], de_24h['deaths'])),
country_deaths_24_100k=__diff_str(
__100k_change(de_today['deaths'], de_24h['deaths'], de_today['population'])),
country_deaths_72=__diff_str(de_today['deaths'] - de_72h['deaths']),
country_deaths_72_prc=__diff_str(__prc_change(de_today['deaths'], de_72h['deaths'])),
country_deaths_72_100k=__diff_str(
__100k_change(de_today['deaths'], de_72h['deaths'], de_today['population'])),
country_deaths_7=__diff_str(de_today['deaths'] - de_7d['deaths']),
country_deaths_7_prc=__diff_str(__prc_change(de_today['deaths'], de_7d['deaths'])),
country_deaths_7_100k=__diff_str(__100k_change(de_today['deaths'], de_7d['deaths'], de_today['population']))
)
db.session.add(sub)
db.session.commit()
num_emails += 1
return f'emails sent {num_emails}', 200