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