def get_all_humans_for_telegram_notifications(hours_of_day: list) -> list:
if not isinstance(hours_of_day, list) or len(hours_of_day) == 0:
return []
for hour_of_day in hours_of_day:
if not isinstance(hour_of_day, int) or (hour_of_day < 0 or hour_of_day > 23):
return []
hours_of_day = list(set(hours_of_day))
rdb = RDBManager(True)
audience = []
try:
audience, err = rdb.execute(
sql_query="""
SELECT
`id`,
`telegram_human_id`
FROM `humans`
WHERE
HOUR(CONVERT_TZ(UTC_TIMESTAMP(),'UTC',`current_tz`)) IN ({})
AND
`telegram_human_id` is not null
AND
`unsubscribed` = 0
""".format(
", ".join([str(hour_of_day) for hour_of_day in hours_of_day])
)
)
except Exception as e:
logger.error(e)
return audience
def human():
rdb = RDBManager()
records, err = rdb.execute(
sql_query="""SELECT id FROM `humans` WHERE `fingerprint` is not null LIMIT 1""")
human_id = records[0][model.HumanProperties.ID.value]
human = model.Human(human_id=human_id)
return human
def verify_telegram_id_exists(telegram_human_id: int) -> (bool, str):
if not isinstance(telegram_human_id, int):
return TypeError('`telegram_human_id` of type {}. Expected [int]'.format(type(telegram_human_id)))
rdb = RDBManager(True)
err = None
try:
telegram_human_id_search_results, err = rdb.execute(
sql_query="""
SELECT `{}`
FROM `humans`
WHERE `{}` = {}
""".format(
HumanProperties.ID.value,
HumanProperties.TELEGRAM_HUMAN_ID.value,
telegram_human_id
)
)
except Exception as e:
logger.error(e)
if len(telegram_human_id_search_results) == 1:
return True, telegram_human_id_search_results[0][HumanProperties.ID.value]
else:
return False, None
def update_tz(self, lat: float, lng: float) -> bool:
rdb = RDBManager()
err = None
try:
_, err = rdb.execute(
sql_query="""
UPDATE `humans`
SET
`current_tz` = (
SELECT `timezone_name`
FROM `timezones`
WHERE mbrcontains(area, point({}, {}))
LIMIT 1
)
WHERE
`id` = {}
""".format(
mysql_db_format_value(value=lng),
mysql_db_format_value(value=lat),
mysql_db_format_value(value=self.id)
)
)
except Exception as e:
logger.error(e)
return err is None
def log_location(self, latitude: float, longitude: float, send_alert: bool = True) -> bool:
if not isinstance(latitude, float) or not isinstance(longitude, float):
return False
rdb = RDBManager()
err = None
try:
_, err = rdb.execute(
sql_query="""
INSERT IGNORE
INTO `geolocations`(
`human_id`, `created`, `modified`, `latitude`, `longitude`, `latitude_noise`, `longitude_noise`
)
VALUES ({}, UTC_TIMESTAMP(), UTC_TIMESTAMP(), {}, {}, ROUND(gauss(0,0.001), 10), ROUND(gauss(0,0.001), 10))
""".format(
mysql_db_format_value(value=self.id),
mysql_db_format_value(value=round(latitude, 10)),
mysql_db_format_value(value=round(longitude, 10))
)
)
except Exception as e:
logger.error(e)
if send_alert:
self.send_proximity_alert(lat=latitude, lng=longitude)
self.update_tz(lat=latitude, lng=longitude)
return err is None
def get_most_recent_location(self) -> (float, float):
rdb = RDBManager(True)
err = None
try:
most_recent_location, err = rdb.execute(
sql_query="""
SELECT
`latitude` AS `{}`,
`longitude` AS `{}`
FROM `geolocations`
WHERE
`human_id` = {}
AND
`latitude` IS NOT NULL
AND
`longitude` IS NOT NULL
ORDER BY `created` DESC
LIMIT 1
""".format(
CoordinateType.LATITUDE.value,
CoordinateType.LONGITUDE.value,
mysql_db_format_value(value=self.id)
)
)
except Exception as e:
logger.error(e)
if len(most_recent_location) == 1:
return float(most_recent_location[0][CoordinateType.LATITUDE.value]), \
float(most_recent_location[0][CoordinateType.LONGITUDE.value])
else:
return None, None
def subscribe_to_newsletter(phone: str = None,
email: str = None) -> (bool, str):
if phone is not None or email is not None:
rdb = RDBManager()
try:
existing_contact, err = rdb.execute(sql_query="""
SELECT `id`
FROM `contact`
WHERE
`phone_number` = {}
OR
`email` = {}
LIMIT 1
""".format(mysql_db_format_value(
value=phone), mysql_db_format_value(value=email)))
except Exception as e:
logger.error(e)
return False, "An error occurred while processing your registration. Please try again."
else:
err = None
if len(existing_contact) == 1 and 'id' in existing_contact[0]:
contact_id = existing_contact[0]['id']
try:
_, err = rdb.execute(sql_query="""
UPDATE `contact`
SET
`phone_number` = IF({} is not null, {}, `phone_number`),
`email` = IF({} is not null, {}, `email`)
WHERE
`id` = {}
""".format(mysql_db_format_value(value=phone), quote_wrap(phone),
mysql_db_format_value(
value=email), mysql_db_format_value(
value=email), mysql_db_format_value(
value=contact_id)))
except Exception as e:
logger.error(e)
else:
new_contact_id = str(uuid.uuid4())
try:
_, err = rdb.execute(sql_query="""
INSERT IGNORE INTO `contact` (`id`, `phone_number`, `email`)
VALUES
({}, {}, {})
""".format(mysql_db_format_value(value=new_contact_id),
quote_wrap(phone), mysql_db_format_value(value=email)))
except Exception as e:
logger.error(e)
return err is None, "Subscribed!"
else:
return False, "Seems like you didn't provide any contact information. Please try again."
def fetch_usa_rt_estimate(fips: str) -> RtEstimate:
rdb: RDBManager = RDBManager(reader=True)
rt_estimates_data, err = rdb.execute(sql_query="""
SELECT
`date`,
`rt_estimate`,
`rt_low`,
`rt_high`
FROM `rt_estimates`
WHERE
`country_code` = 'USA'
AND
`region_code` = '{}'
ORDER BY `date` ASC
""".format(fips))
if err is not None:
logger.error(err)
if len(rt_estimates_data) > 0:
rt_estimate = RtEstimate(
_date=[item['date'] for item in rt_estimates_data],
rt_estimate=[
float(item['rt_estimate']) for item in rt_estimates_data
],
rt_low=[float(item['rt_low']) for item in rt_estimates_data],
rt_high=[float(item['rt_high']) for item in rt_estimates_data])
return rt_estimate
return RtEstimate()
def get_human_attribute(self, attribute_name: str) -> str:
rdb = RDBManager(True)
try:
human_id_search_results, err = rdb.execute(
sql_query="""
SELECT *
FROM `humans`
WHERE `id` = '{}'
""".format(self._human_id)
)
except Exception as e:
logger.error(e)
if len(human_id_search_results) == 1:
if attribute_name in human_id_search_results[0]:
return human_id_search_results[0][attribute_name]
return None
def unsubscribe(self) -> bool:
rdb = RDBManager()
err = None
try:
_, err = rdb.execute(
sql_query="""
UPDATE `humans`
SET
`unsubscribed` = 1
WHERE
`id` = {}
""".format(
mysql_db_format_value(value=self.id)
)
)
except Exception as e:
logger.error(e)
return err is None
def get_human_from_fingerprint(fingerprint: str) -> Human:
rdb = RDBManager(True)
err = None
try:
records, err = rdb.execute(
sql_query="""
SELECT `{}`
FROM `humans`
WHERE `{}` = {}
""".format(
HumanProperties.ID.value,
HumanProperties.FINGERPRINT.value,
mysql_db_format_value(fingerprint)
)
)
except Exception as e:
logger.error(e)
return Human(human_id=records[0][HumanProperties.ID.value]) if len(records) == 1 else None
def log_symptom(self, symptom_name: str) -> bool:
if not Symptoms.has_value(symptom_name):
return False
rdb = RDBManager()
try:
_, err = rdb.execute(
sql_query="""
INSERT IGNORE
INTO `symptoms`(`human_id`, `created`, `modified`, `symptom`, `value`)
VALUES ({}, UTC_TIMESTAMP(), UTC_TIMESTAMP(), {}, 1.0)
""".format(
mysql_db_format_value(value=self.id),
mysql_db_format_value(value=symptom_name)
)
)
except Exception as e:
logger.error(e)
return False
return err is None
def verify_human_exists(human_id: str) -> (bool, dict):
if not verify_uuid_regex(s=human_id):
return False, None
rdb = RDBManager(True)
err = None
try:
human_id_search_results, err = rdb.execute(
sql_query="""
SELECT *
FROM `humans`
WHERE `{}` = '{}'
""".format(
HumanProperties.ID.value,
human_id
)
)
except Exception as e:
logger.error(e)
if len(human_id_search_results) == 1:
return True, human_id_search_results[0]
else:
return False, None
def create_human(telegram_human_id: int = None, fingerprint: str = None) -> Tuple[Human, str]:
if telegram_human_id is None and fingerprint is None:
return None
human_id: str = str(uuid.uuid4())
rdb: RDBManager = RDBManager(reader=False)
try:
_, db_err = rdb.execute(
sql_query="""
INSERT IGNORE INTO `humans`(
`{}`, `{}`, `{}`, `created`, `modified`
)
VALUES (
{}, {}, {}, UTC_TIMESTAMP(), UTC_TIMESTAMP()
)
""".format(
HumanProperties.ID.value,
HumanProperties.TELEGRAM_HUMAN_ID.value,
HumanProperties.FINGERPRINT.value,
mysql_db_format_value(value=human_id),
mysql_db_format_value(value=telegram_human_id),
mysql_db_format_value(value=fingerprint)
)
)
except Exception as query_creation_err:
logger.error(query_creation_err)
return None, query_creation_err
if db_err is not None:
return None, db_err
time.sleep(0.1)
try:
new_human: Human = Human(human_id=human_id)
except AssertionError as assert_err:
logger.error("Error while fetching human with id: ", human_id, assert_err)
return None, assert_err
return new_human, None
def get_risky_humans(lat: float, lng: float, days_window: int, km_radius: int) -> list:
rdb = RDBManager(True)
err = None
try:
risky_humans, err = rdb.execute(
sql_query="""
SELECT
agg.`latitude` AS {},
agg.`longitude` AS {},
CASE
WHEN agg.`risk_level` IN (4,5) THEN agg.`risk_level`
WHEN COUNT(DISTINCT(agg.`symptom`)) > 3 THEN 3
ELSE COUNT(DISTINCT(agg.`symptom`))
END AS 'mag'
FROM (
SELECT
geo.`human_id`,
DATE(sym.`created`) AS 'date',
geo.`latitude` + geo.`latitude_noise` AS 'latitude',
geo.`longitude` + geo.`longitude_noise` AS 'longitude',
sym.`symptom`,
round(( 6373 * acos( least(1.0,
cos( radians({}) )
* cos( radians(geo.`latitude`) )
* cos( radians(geo.`longitude`) - radians({}) )
+ sin( radians({}) )
* sin( radians(geo.`latitude`)
) ) )
), 1) as 'distance',
CASE
WHEN sym.`symptom` = 'verified_covid19' THEN 5
WHEN sym.`symptom` = 'confirmed_covid19' THEN 4
ELSE 1
END AS 'risk_level'
FROM `geolocations` as geo
LEFT JOIN `symptoms` as sym
ON
geo.`human_id` = sym.`human_id`
AND
DATE(sym.`created`) = DATE(geo.`created`)
WHERE
sym.`symptom` is not null
AND
geo.`latitude` is not null
AND
geo.`longitude` is not null
{}
AND
(sym.`value` is not null or sym.`value` > 0)
{}
) as agg
GROUP BY
agg.`human_id`,
agg.`date`,
agg.`latitude`,
agg.`longitude`,
agg.`risk_level`;
""".format(
mysql_db_format_value(CoordinateType.LATITUDE.value),
mysql_db_format_value(CoordinateType.LONGITUDE.value),
mysql_db_format_value(value=lat),
mysql_db_format_value(value=lng),
mysql_db_format_value(value=lat),
"AND sym.`created` >= DATE(NOW()) - INTERVAL {} DAY".format(days_window) if days_window is not None else "",
"HAVING distance <= {}".format(km_radius*5) if km_radius is not None else ""
)
)
except Exception as e:
logger.error(e)
return risky_humans, err