def get_all_logs(self):
"""
Returns all logs in a dataframe
"""
query = "SELECT * FROM {}".format(shift_log_DAO.table_name)
# Get connection
factory = connection_manager()
connection = factory.connection
return pd.read_sql_query(query, connection)
def authenticate(username, password):
'''
Returns None or User. Authenticates a username and password combination
User: Successfull authentication
None: Wrong username / password
Kyeword arguments:
username -- str
password -- str
'''
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
# Check if username exists
query = "SELECT {} FROM {} WHERE {} = '{}'" \
.format(User.encrypted_password_token_tname, user_DAO.table_name, User.username_tname, username)
cursor.execute(query)
result = cursor.fetchone()
if result is None: return None # No username found
# Get salt, Encrypt given password and authenticate
salt = result[User.encrypted_password_token_tname]
encrypted_password = (salt + password).encode('utf-8')
encrypted_password = hashlib.sha512(encrypted_password).hexdigest()
query = "SELECT * FROM {} WHERE {} = '{}' AND {} = '{}'" \
.format(user_DAO.table_name, User.username_tname, username, User.encrypted_password_tname,
encrypted_password)
cursor.execute(query)
result = cursor.fetchone()
if result is None:
return None # Auth failed
else:
username = result[User.username_tname]
name = result[User.name_tname]
email = result[User.email_tname]
last_sign_in = result[User.last_sign_in_tname]
staff_type = result[User.staff_type_tname]
return User(username=username,
name=name,
email=email,
last_sign_in=last_sign_in,
staff_type=staff_type)
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_enclosing_logs(start_dt, end_dt, target_dt):
'''
Given a target datetime, returns...
Last record before the target and first record after the target
Inputs:
start_dt (datetime)
end_dt (datetime)
target_dt (datetime)
Returns:
records as returned by pymysql
NOTE: Can have 0, 1, or 2 records. Sorted in ascending datetime
'''
feed_dict = [target_dt, start_dt, end_dt, target_dt, start_dt, end_dt]
query = f"""SELECT * FROM
(SELECT * FROM {sensor_log_DAO.table_name}
WHERE {Sensor_Log.recieved_timestamp_tname} < %s
AND {Sensor_Log.recieved_timestamp_tname} >= %s
AND {Sensor_Log.recieved_timestamp_tname} <= %s
ORDER BY {Sensor_Log.recieved_timestamp_tname} ASC
LIMIT 1) as a
union
(SELECT * FROM {sensor_log_DAO.table_name}
WHERE {Sensor_Log.recieved_timestamp_tname} > %s
AND {Sensor_Log.recieved_timestamp_tname} >= %s
AND {Sensor_Log.recieved_timestamp_tname} <= %s
ORDER BY {Sensor_Log.recieved_timestamp_tname} DESC
LIMIT 1)
ORDER BY {Sensor_Log.recieved_timestamp_tname} ASC
"""
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, feed_dict)
result = cursor.fetchall()
enclosing_logs = [None, None]
if result != None:
for r in result:
r_ts = r[Sensor_Log.recieved_timestamp_tname]
if r_ts < target_dt: enclosing_logs[0] = r_ts
else: enclosing_logs[1] = r_ts
return enclosing_logs
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_logs(uuid, start_datetime, end_datetime):
"""
Returns a list of logs found in the database according to the parameters given
Inputs:
uuid (str) -- Sensor identifier
start_datetime (datetime)
end_datetime (datetime)
"""
query = f"""
SELECT * FROM {sensor_log_DAO.table_name}
WHERE `{Sensor_Log.uuid_tname}` = %s
AND `{Sensor_Log.recieved_timestamp_tname}` > %s
AND `{ Sensor_Log.recieved_timestamp_tname}` < %s
ORDER BY `{Sensor_Log.recieved_timestamp_tname}`
DESC
"""
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
# cursor.execute(query, [uuid, start_datetime.strftime('%Y-%m-%d %H:%M:%S'),
# end_datetime.strftime('%Y-%m-%d %H:%M:%S')])
cursor.execute(query, [uuid, start_datetime, end_datetime])
result = cursor.fetchall()
logs = []
if result != None:
for d in result:
uuid = d[Sensor_Log.uuid_tname]
node_id = d[Sensor_Log.node_id_tname]
event = d[Sensor_Log.event_tname]
recieved_timestamp = d[Sensor_Log.recieved_timestamp_tname]
row_log_obj = Sensor_Log(
uuid=uuid,
node_id=node_id,
event=event,
recieved_timestamp=recieved_timestamp)
logs.append(row_log_obj)
return logs
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_sensors(type=None, location=None, facility=None, uuid=None):
"""
Get Sesnors by `type` andor `location` andor `uuid` or all
Inputs
type (str)
location (str)
uuid (str)
Returns
list of Entity.sensor
"""
query = f"SELECT * FROM {sensor_DAO.table_name}"
ps = [] # param_set
if type != None: ps.append((Sensor.type_tname, type))
if location != None: ps.append((Sensor.location_tname, location))
if facility != None: ps.append(Sensor.facility_tname, facility)
if uuid != None: ps.append((Sensor.uuid_tname, uuid))
for i in range(len(ps)):
if i == 0:
query += f" WHERE `{ps[i][0]}` = \"{ps[i][1]}\""
else:
query += f" AND `{ps[i][0]}` = \"{ps[i][1]}\""
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
result = cursor.fetchall()
sensors = []
if result != None:
for r in result:
sensors.append(Sensor(uuid=r[Sensor.uuid_tname], type=r[Sensor.type_tname], \
location=r[Sensor.location_tname], facility=r[Sensor.facility_tname], \
description=r[Sensor.description_tname],
juvo_target=r[Sensor.juvo_target_tname]))
return sensors
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_type_by_node_id(node_id):
query = f"SELECT type FROM {sensor_DAO.table_name} where uuid = %s"
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, (node_id, ))
results = cursor.fetchall()
if results: return results
else: return []
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_last_logs(uuid, limit=1):
"""
Returns a list of logs found in the database according to the parameters given
Inputs:
uuid (str) -- Sensor identifier
limit (int) -- default=1
"""
query = f"""
SELECT * FROM {sensor_log_DAO.table_name}
WHERE {Sensor_Log.uuid_tname} = "{uuid}"
ORDER BY `{Sensor_Log.recieved_timestamp_tname}`
DESC LIMIT {limit}
"""
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
result = cursor.fetchall()
logs = []
if result != None:
for d in result:
uuid = d[Sensor_Log.uuid_tname]
node_id = d[Sensor_Log.node_id_tname]
event = d[Sensor_Log.event_tname]
recieved_timestamp = d[Sensor_Log.recieved_timestamp_tname]
row_log_obj = Sensor_Log(
uuid=uuid,
node_id=node_id,
event=event,
recieved_timestamp=recieved_timestamp)
logs.append(row_log_obj)
return logs
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def close_ownership_hist(uuid, resident_id, end_datetime=None):
'''
Closes an open period in the ownership table
Inputs
uuid (str) -- Sensor uuid
resident_id (int)
end_datetime (datetime) -- default None, closes with datetime.now()
Raises
AssertionError -- No period open for uuid - resident_id pair
'''
# Check for existing open datetime
openPeriod = False
rdict = sensor_DAO.get_ownership_hist(uuid=uuid,
residentID=resident_id)
for ruuid, rvals in rdict.items():
if rvals[-1][1] == None: openPeriod = True
if openPeriod == False:
raise (AssertionError(
"No open period for uuid, resident_id pair. Unable to close anything"
))
# Construct query
if end_datetime == None: end_datetime = datetime.datetime.now()
query = f"""UPDATE {sensor_DAO.soh_table_name} SET `{sensor_DAO.soh_period_end}` = %s
WHERE `{sensor_DAO.soh_uuid}` = %s
AND `{sensor_DAO.soh_resident_id}` = %s
AND `{sensor_DAO.soh_period_end}` IS NULL"""
feedDict = [end_datetime, uuid, resident_id]
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, feedDict)
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def notifications_count():
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(
f"SELECT * FROM stbern.alert_log WHERE alert_text LIKE '{SENSOR_SUBSTR}%'"
)
result = cursor.fetchall()
if result == None: return 0
else: return len(result)
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_uuids_by_id(id):
'''
Returns a resident (in a dict) based on node_id (in int)
'''
query = 'SELECT uuid FROM {} WHERE resident_id = %s'.format(table_name)
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, (id, ))
results = cursor.fetchall()
if results: return results
else: return []
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def insert_log(self, log):
'''
INSERTs a log entry into the database
Returns success boolean
'''
query = "INSERT INTO {} VALUES('{}', '{}', '{}', '{}', '{}')" \
.format(sysmon_log_DAO.table_name, log.uuid, log.node_id, \
log.event, log.key, log.recieved_timestamp.strftime('%Y-%m-%d %H:%M:%S'))
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
except: raise
finally: factory.close_all(cursor=cursor, connection=connection)
def update_resident_fall_risk(resident_id, status):
'''
Returns a resident (in a dict) based on resident_id (in int)
'''
query = 'UPDATE {} SET `fall_risk` = %s WHERE resident_id = %s'.format(
table_name)
val = (status, resident_id)
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, val)
connection.commit()
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_resident_id_by_resident_name(resident_name):
'''
Returns the name of the resident based on current node_id
'''
query = 'SELECT resident_id FROM {} WHERE name = %s'.format(table_name)
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, (resident_name, ))
result = cursor.fetchone()
return result
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_resident_by_id(node_id):
'''
Returns a resident (in a dict) based on node_id (in int)
'''
query = 'SELECT * FROM {} WHERE node_id = %s'.format(table_name)
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, (node_id, ))
result = cursor.fetchone()
return result
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_toilet_uuid(cls, resident_id):
'''
WARNING: ghetto method for mid terms quick fix
returns the active uuid (str) for the current input resident_id (int)
'''
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
output = None
query = f"SELECT uuid FROM stbern.sensor_ownership_hist WHERE resident_id = {resident_id} AND period_end IS NULL AND uuid LIKE '%m-02'"
try:
cursor.execute(query)
result = cursor.fetchone()
if result:
output = result['uuid']
except Exceptio as e:
print(e)
return output
def get_today_logs(self):
current_datetime = datetime.today()
reset_datetime = datetime.combine(date.today(), time(10))
query_date = datetime.combine(date.today(), time(0))
if current_datetime < reset_datetime:
query_date = datetime.combine(date.today() - timedelta(1), time(0))
query = "SELECT count(*) FROM {} where `datetime` > '{}'".format(shift_log_DAO.table_name, query_date)
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
result = cursor.fetchone()
return result['count(*)']
except:
print("error")
finally:
factory.close_all(cursor=cursor, connection=connection)
def insert_location(location):
"""
Inserts a type into the type table...
Technically should not be used in the front end, unless you wanna implement some gross logic
Inputs
location (str)
"""
query = f"INSERT INTO {sensor_DAO.location_table_name} (`{sensor_DAO.location_col_name}`) VALUES (%s)"
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, [location])
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def insert_shift_log(self, shift_log):
'''
Inserts an entry into the database table
Keyword arguments:
shift_log -- Entities.shift_log, class vars used to create a new DB row
'''
query = """INSERT INTO {} VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" \
.format(shift_log_DAO.table_name)
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, shift_log.var_list)
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def insert_sensor_log(sensor_log):
"""
INSERTs a log entry into the database
Inputs:
sensor_log (Entities.shift_log)
"""
query = "INSERT INTO {} VALUES(%s, %s, %s, %s)" \
.format(sensor_log_DAO.table_name)
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, sensor_log.var_list)
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def insert_facilities(abrv, fullname, description=""):
"""
Inserts a record of facility into the database
Inputs
abrv (str) -- Abreviation
fuyllname (str)
description (str) -- Default ""
"""
query = f"INSERT INTO {sensor_DAO.facility_table_name} (`{sensor_DAO.facility_abrv_cname}`,`{sensor_DAO.facility_fullname_cname}`,`{sensor_DAO.facility_desc_cname}`) VALUES (%s, %s, %s)"
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, [abrv, fullname, description])
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_all_logs(uuid=None, format='list'):
'''
Returns all sysmon logs in the DB
Inputs
uuid (str) -- Filters result by sensor uuid
format (str) -- 'pd' for dataframes, 'list' for list of sysmon objects
'''
# NOTE: cheap way to kick the bigger architectural problems down the road
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
query = f"SELECT * FROM {sysmon_log_DAO.table_name}"
if uuid != None: query += f" WHERE `{Sysmon_Log.uuid_tname}` = %s"
try:
if format == 'pd': return pd.read_sql_query(query, connection)
if uuid == None: cursor.execute(query)
else: cursor.execute(query, [uuid])
results = cursor.fetchall()
logs = []
if results != None:
for result in results:
uuid = result[Sysmon_Log.uuid_tname]
node_id = result[Sysmon_Log.node_id_tname]
event = result[Sysmon_Log.event_tname]
key = result[Sysmon_Log.key_tname]
ts = result[Sysmon_Log.recieved_timestamp_tname]
log = Sysmon_Log(uuid, node_id, event, key, ts)
logs.append(log)
return logs
except: raise
finally: factory.close_all(cursor=cursor, connection=connection)
def get_juvo_target_from_resident_id(_resident_id, date_in_use=None):
'''
Returns the juvo target (int) in use at the current time for the input resident id (int)
TODO: optional input to get juvo target at another point in time
'''
output = 0
query = f"SELECT {Sensor.juvo_target_tname} FROM {sensor_DAO.table_name} WHERE {Sensor.uuid_tname} IN (SELECT {sensor_DAO.soh_uuid} FROM {sensor_DAO.soh_table_name} WHERE {sensor_DAO.soh_resident_id} = {_resident_id} AND {sensor_DAO.soh_uuid} LIKE '%-j-%' AND {sensor_DAO.soh_period_end} is Null)"
try:
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
cursor.execute(query)
result = cursor.fetchone()
if result:
output = result[Sensor.juvo_target_tname]
except Exception as e:
print(e)
return output
def get_juvo_resident_ids(location_filter='bkttm'):
'''
Returns list of resident_id for residents with juvo bed sensors
Not implemented: filter by ALF location
'''
output = []
try:
query = f"SELECT {sensor_DAO.soh_resident_id} FROM {sensor_DAO.soh_table_name} WHERE {sensor_DAO.soh_uuid} IN (SELECT {Sensor.uuid_tname} FROM {sensor_DAO.table_name} WHERE {Sensor.juvo_target_tname} is not Null)"
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
cursor.execute(query)
result = cursor.fetchall()
if result:
for r in result:
output.append(r['resident_id'])
except Exception as e:
print(e)
return output
def update_password(username, password):
alphabet = string.ascii_letters + string.digits
encrypted_password_token = ''.join(
secrets.choice(alphabet) for i in range(20))
encrypted_password = (encrypted_password_token +
password).encode('utf-8')
encrypted_password = hashlib.sha512(encrypted_password).hexdigest()
query = "UPDATE {} SET `encrypted_password` = '{}', `encrypted_password_token` = '{}' WHERE `username` = '{}'".format(
user_DAO.table_name, encrypted_password, encrypted_password_token,
username)
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_last_burglar(uuid, event=0, limit=1):
'''
Inputs:
uuid (str) -- ie "2005-m-01"
event (int) -- default 0
limit (int) -- default 1
Reutrn
Entity.Sysmon_log
'''
query = f"""SELECT * FROM {sysmon_log_DAO.table_name}
WHERE `{Sysmon_Log.uuid_tname}` = "{uuid}"
AND `{Sysmon_Log.key_tname}` = "Burglar"
ORDER BY `{Sysmon_Log.recieved_timestamp_tname}`
DESC LIMIT {limit}"""
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
results = cursor.fetchall()
if results != None:
for result in results:
uuid = result[Sysmon_Log.uuid_tname]
node_id = result[Sysmon_Log.node_id_tname]
event = result[Sysmon_Log.event_tname]
key = result[Sysmon_Log.key_tname]
ts = result[Sysmon_Log.recieved_timestamp_tname]
return Sysmon_Log(uuid, node_id, event, key, ts)
else: return None
except: raise
finally: factory.close_all(cursor=cursor, connection=connection)
def insert_ownership_hist(uuid, resident_id, start_datetime):
"""
Inserts a row of ownership history of sensor into the DB
Inputs
uuid (str) -- Sensor uuid
resident_id (int)
start_datetime (datetime)
Raises
AssertionError -- There is an open period left on the ownership table, close period before adding new owner
-- see sensor_DAO.close_ownership_hist()
"""
# Check for open period / current owner exists
rdict = sensor_DAO.get_ownership_hist(uuid=uuid)
for ruuid, rvals in rdict.items():
if rvals[1] == None:
raise AssertionError(
"There is an open period left on the ownership table, close period before adding new owner"
)
# Add ownership history
query = f"""INSERT INTO {sensor_DAO.soh_table_name} ({sensor_DAO.soh_uuid}, {sensor_DAO.soh_resident_id}, {sensor_DAO.soh_period_start}, {sensor_DAO.soh_period_end})
VALUES (%s, %s, %s, %s)"""
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query, [uuid, resident_id, start_datetime, None])
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def set_min_max_datetime(self):
"""
Sets obj vars and returns max_datetime and min_datetime found in the database
Returns:
(max_datetime, min_datetime) or (None, None) if nothing found
"""
query = """SELECT MAX({}) as 'max' ,
MIN({}) as 'min'
FROM {};""" \
.format(Sensor_Log.recieved_timestamp_tname, \
Sensor_Log.recieved_timestamp_tname, \
sensor_log_DAO.table_name)
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
result = cursor.fetchone()
# set class vars
if result != None:
self.max_datetime = result['max']
self.min_datetime = result['min']
return result['max'], result['min']
else:
return None, None
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_locations():
"""
Returns a list of str, of all sensor types. i.e. "motion", "bed sensor"..etc
"""
query = f"SELECT `{sensor_DAO.location_col_name}` FROM {sensor_DAO.location_table_name}"
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
try:
cursor.execute(query)
result = cursor.fetchall()
locations = []
if result != None:
for r in result:
locations.append(r[sensor_DAO.location_col_name])
return locations
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
def get_ownership_hist(uuid=None,
residentID=None,
type=None,
start_dt=None,
end_dt=None):
'''
Returns ownership hisotry of sensor: Which resident was using the sensor during a period
Inputs:
uuid (str) -- Default None
residentID (int) -- Default None
type (str) -- Default None, sensor type
start_dt (datetime) -- Default None Returns all periods within or overlapping the given start and end dts
end_dt (datetime) -- Default None
Return:
Dict: {"uuid1": [(residentID, startdate, enddate), (residentID, datetime, datetime)],
"uuid2": [(residentID, startdate, None)]}
* NOTE: Final start and end date pairs will have 'None' END_DATETIME as this represents an ongoing period
* NOTE: (startdate, enddate) are (inclusive, exclusive)
'''
queryVals = []
# Construct query
#stbern.sensor_ownership_hist as t1 inner join stbern.sensor as t2 on t1.uuid = t2.uuid
query = f"SELECT * FROM {sensor_DAO.soh_table_name} AS t1 INNER JOIN {sensor_DAO.table_name} AS t2 ON t1.uuid = t2.uuid "
prefix = None
if residentID != None:
prefix = "WHERE" if prefix == None else "AND"
query += f" {prefix} t1.`{sensor_DAO.soh_resident_id}` = %s "
queryVals.append(residentID)
if uuid != None:
prefix = "WHERE" if prefix == None else "AND"
query += f" {prefix} t1.`{sensor_DAO.soh_uuid}` = %s "
queryVals.append(uuid)
if type != None:
prefix = "WHERE" if prefix == None else "AND"
query += f" {prefix} t1.`{sensor_DAO.soh_uuid}` = %s "
queryVals.append(type)
if start_dt != None:
prefix = "WHERE" if prefix == None else "AND"
query += f" {prefix} t1.`{sensor_DAO.soh_period_start}` < \"{end_dt.strftime('%Y-%m-%d %H:%M:%S')}\" "
prefix = " AND "
if end_dt != None:
query += f" {prefix} (`{sensor_DAO.soh_period_end}` IS NULL OR `{sensor_DAO.soh_period_end}` > \"{start_dt.strftime('%Y-%m-%d %H:%M:%S')}\")"
query += f" ORDER BY `{sensor_DAO.soh_period_start}` ASC" # Sort by start periods
# Get connection
factory = connection_manager()
connection = factory.connection
cursor = connection.cursor()
# Read results and return dict
try:
cursor.execute(query, queryVals)
result = cursor.fetchall()
records = defaultdict(list)
if result != None:
for r in result:
rid = r[sensor_DAO.soh_resident_id]
uuid = r[sensor_DAO.soh_uuid]
pStart = r[sensor_DAO.soh_period_start]
pEnd = r[sensor_DAO.soh_period_end]
records[uuid].append((rid, pStart, pEnd))
return records
except:
raise
finally:
factory.close_all(cursor=cursor, connection=connection)
