def read_all_patients_for_assoc_vht(user: User, **kwargs) -> List[M]:
"""
Queries the database for all associated Patients
:param user: Current User
:param kwargs: limit, page, search, sortBy, sortDir
:return: A list patient_list that are associated to the VHT
"""
search_param = (None if kwargs.get("search", None) == "" else kwargs.get(
"search", None))
sql_str = SQL.get_sql_string(search_param, **kwargs)
vht_list = [{column: value
for column, value in row.items()}
for row in get_sql_vhts_for_cho_db(user.id)]
vht_list_id = [str(user.id)]
for vht in vht_list:
vht_list_id.append(str(vht["id"]))
sql_str_vht_ids = ",".join(vht_list_id)
sql_str_table = SQL.get_sql_table_operation_assoc_vht_list(
True, sql_str_vht_ids)
if search_param is not None:
return db_session.execute(sql_str_table + sql_str)
else:
return db_session.execute(sql_str_table + sql_str)
def read_all_admin_view(m: Type[M], **kwargs) -> List[M]:
"""
Queries the database for all Patients or Referrals
:param m: Type of the model to query for
:param kwargs: limit, page, search, sortBy, sortDir
:return: A list of models from the database
"""
search_param = (None if kwargs.get("search", None) == "" else kwargs.get(
"search", None))
sql_str = SQL.get_sql_string(search_param, **kwargs)
sql_str_table = SQL.get_sql_table_operations(m)
if m.schema() == Patient.schema():
if search_param is not None:
return db_session.execute(sql_str_table + sql_str)
else:
return db_session.execute(sql_str_table + sql_str)
if m.schema() == Referral.schema():
if search_param is not None:
return db_session.execute(sql_str_table + sql_str)
else:
return db_session.execute(sql_str_table + sql_str)
def get_days_with_readings(facility="%", user="******", filter={}):
"""Queries the database for number of days within specified timeframe
which have more than one reading
:return: number of days"""
query = """
SELECT COUNT(DISTINCT(FLOOR(R.dateTimeTaken / 86400)))
FROM reading R
JOIN user U ON U.id = R.userId
WHERE dateTimeTaken BETWEEN %s AND %s
AND (
(R.userId LIKE "%s" OR R.userId IS NULL)
AND (U.healthFacilityName LIKE "%s" OR U.healthFacilityName is NULL)
)
""" % (
filter.get("from"),
filter.get("to"),
str(user),
str(facility),
)
try:
result = db_session.execute(query)
return list(result)
except Exception as e:
print(e)
return None
def get_sent_referrals(facility="%", user="******", filter={}) -> List[M]:
"""Queries the database for total number of sent referrals
:return: Total number of sent referrals"""
query = """
SELECT COUNT(R.id) FROM referral R
JOIN user U ON U.id = R.userId
WHERE R.dateReferred BETWEEN %s and %s
AND (
(R.userId LIKE "%s" OR R.userId IS NULL)
AND (U.healthFacilityName LIKE "%s" OR U.healthFacilityName IS NULL)
)
""" % (
filter.get("from"),
filter.get("to"),
str(user),
str(facility),
)
try:
result = db_session.execute(query)
return list(result)
except Exception as e:
print(e)
return None
def get_total_color_readings(facility="%", user="******", filter={}) -> List[M]:
"""Queries the database for total number different coloured readings (red up, yellow down, etc)
filter: filter date range, otherwise uses max range
:return: Total number of respective coloured readings"""
query = """
SELECT R.trafficLightStatus, COUNT(R.trafficLightStatus)
FROM reading R
JOIN user U on U.id = R.userId
WHERE R.dateTimeTaken BETWEEN %s AND %s
AND (
(R.userId LIKE "%s" OR R.userId is NULL)
AND (U.healthFacilityName LIKE "%s" OR U.healthFacilityName is NULL)
)
GROUP BY R.trafficLightStatus
""" % (
filter.get("from"),
filter.get("to"),
str(user),
str(facility),
)
try:
result = db_session.execute(query)
return list(result)
except Exception as e:
print(e)
return None
def get_total_readings_completed(facility="%", user="******", filter={}) -> List[M]:
"""Queries the database for total number of readings completed
filter: filter date range, otherwise uses max range
:return: Number of total readings"""
query = """
SELECT COUNT(R.readingId)
FROM reading R
JOIN user U on U.id = R.userId
WHERE R.dateTimeTaken BETWEEN %s AND %s
AND (
(R.userId LIKE "%s" OR R.userId is NULL)
AND (U.healthFacilityName LIKE "%s" OR U.healthFacilityName is NULL)
)
""" % (
filter.get("from"),
filter.get("to"),
str(user),
str(facility),
)
try:
result = db_session.execute(query)
return list(result)
except Exception as e:
print(e)
return None
def get_unique_patients_with_readings(facility="%",
user="******",
filter={}) -> List[M]:
"""Queries the database for unique patients with more than one reading
:return: A number of unique patients"""
query = """ SELECT COUNT(pat.patientId) as patients
FROM (
SELECT DISTINCT(P.patientId)
FROM (SELECT R.patientId FROM reading R
JOIN user U ON R.userId = U.id
WHERE R.dateTimeTaken BETWEEN %s and %s
AND (
(userId LIKE "%s" OR userId is NULL)
AND (U.healthFacilityName LIKE "%s" or U.healthFacilityName is NULL)
)
) as P
JOIN reading R ON P.patientID = R.patientId
GROUP BY P.patientId
HAVING COUNT(R.readingId) > 0) as pat
""" % (
filter.get("from"),
filter.get("to"),
str(user),
str(facility),
)
try:
result = db_session.execute(query)
return list(result)
except Exception as e:
print(e)
return None
def read_all_readings_db(is_admin: bool, user_ids: str) -> List[M]:
"""
Queries the database for all Readings
:return: A dictionary of Readings
"""
# make DB call
get_sql_for_readings = SQL.get_sql_for_readings(user_ids, is_admin)
reading_and_referral = db_session.execute(get_sql_for_readings)
arr = []
# make list of readings
for reading_row in reading_and_referral:
creat_dict = {}
creat_dict = serialize.serialize_reading_sql_to_dict(
creat_dict, reading_row)
# make list of symptoms
if not creat_dict.get("symptoms"):
creat_dict["symptoms"] = []
else:
creat_dict["symptoms"] = creat_dict["symptoms"].split(",")
arr.append(creat_dict)
return arr
def read_all_referral_for_user(user: User, **kwargs) -> List[M]:
"""
Queries the database for all associated Patients
:param user: Current User
:param kwargs: limit, page, search, sortBy, sortDir
:return: A list referrals that are associated to the current user
"""
search_param = (None if kwargs.get("search", None) == "" else kwargs.get(
"search", None))
sql_str = SQL.get_sql_string(search_param, **kwargs)
sql_str_table = SQL.get_sql_table_operation_assoc(False, user)
if search_param is not None:
return db_session.execute(sql_str_table + sql_str)
else:
return db_session.execute(sql_str_table + sql_str)
def read_all_patients_db() -> List[M]:
"""
Queries the database for all Patients
:return: A dictionary of Patients
"""
# make DB call
patients = db_session.execute(
"SELECT * FROM patient ORDER BY patientId ASC")
arr = []
# make list of patients
for pat_row in patients:
creat_dict = {}
creat_dict = serialize.serialize_patient_sql_to_dict(
creat_dict, pat_row)
arr.append(creat_dict)
return arr
def read_all_assoc_patients_db(user_ids: str) -> List[M]:
"""
Queries the database for all Patients
:return: A dictionary of Patients
"""
# make DB call
patients = db_session.execute(
"SELECT * FROM patient p JOIN patient_associations pa "
"ON p.patientId = pa.patientId "
" AND pa.userId IN (" + user_ids + ") ORDER BY p.patientId ASC")
arr = []
# make list of patients
for pat_row in patients:
creat_dict = {}
creat_dict = serialize.serialize_patient_sql_to_dict(
creat_dict, pat_row)
arr.append(creat_dict)
return arr
def get_referred_patients(facility="%", filter={}) -> List[M]:
"""Queries the database for total number of patients that have referrals to specified facility
:return: Total number of referred patients"""
query = """
SELECT COUNT(DISTINCT(R.patientId))
FROM referral R
WHERE R.dateReferred BETWEEN %s AND %s
AND (R.referralHealthFacilityName LIKE "%s" OR R.referralHealthFacilityName IS NULL)
""" % (
filter.get("from"),
filter.get("to"),
str(facility),
)
try:
result = db_session.execute(query)
return list(result)
except Exception as e:
print(e)
return None
def get_sql_vhts_for_cho_db(cho_id: str) -> List[M]:
return db_session.execute("SELECT * from supervises s inner join "
"user u on s.vhtId = u.id "
"where choId = " + str(cho_id))
