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)