def TryGetUserByCardId(CardId): results = db.query("select * from users where CardId = '{}'" .format(CardId)) if results == None or len(results) <= 0: return None result = results[0] return User(Id = result['Id'],Username = result['Username'],ChatId = result['ChatId'],CardId = result['CardId'])
def getCompletedRequests(): db_results = db.query("SELECT ud.Username, r.RoomName, ar.DateRequested, ar.IsApproved FROM access_request ar " + "JOIN rooms r on ar.RoomId = r.Id " + "JOIN users ud on ar.UserId = ud.Id where ar.IsApproved is not null") if db_results == None or len(db_results) <= 0: return {} return db_results
def GetAccessLogForRoom(RoomId): db_results = db.query( "select * from access_logs where RoomId = {} order by Time".format( RoomId)) if db_results == None or len(db_results) <= 0: return {} return db_results
def TryGetUserById(Id): results = db.query("select * from users where Id = {}" .format(Id)) if len(results) <= 0: return None result = results[0] return User(Id = result['Id'],Username = result['Username'],ChatId = result['ChatId'],CardId = result['CardId'])
def IsRoomAvailable(RoomId): db_results = db.query( "SELECT * FROM access_logs al " + "where al.RoomId = {} and al.IsValid = 1 and al.Exit_time is null". format(RoomId)) if db_results == None or len(db_results) <= 0: return True return False
def GetUsers(): UsersList = [] results = db.query("select * from users") if len(results) <= 0: return None for r in results: UsersList.append(User(Id = r['Id'],Username = r['Username'],ChatId = r['ChatId'],CardId = r['CardId'])) return UsersList
def GetChatIdUsers(): UsersList = [] results = db.query("select * from users where ChatId is not null") if results == None or len(results) <= 0: return None for r in results: UsersList.append(User(Id = r['Id'],Username = r['Username'],ChatId = r['ChatId'],CardId = r['CardId'])) return UsersList
def TryGetRoomById(Id): results = db.query("select * from rooms where Id = {}".format(Id)) if results == None or len(results) <= 0: return None result = results[0] return Room(Id=result["Id"], RoomName=result["RoomName"], IotDeviceName=result["IotDeviceName"], IoTLightDevice=result["IoTLightDevice"])
def GetLatestExitAccessLog(RoomId): db_results = db.query( "SELECT r.RoomName,al.Id, al.Time, al.Exit_time FROM access_logs al " + "JOIN rooms r on al.RoomId = r.Id where al.IsValid = 1 and al.RoomId = {} order by al.Id desc LIMIT 1" .format(RoomId)) if db_results == None or len(db_results) <= 0: return {} return db_results
def GetAvgEnviroInfoByDay(RoomId): db_results = db.query( "SELECT Avg(temp) 'AvgTemp', time from enviro_info where roomId = {} group by DAY(time)" .format(RoomId)) humid = [] temp = [] for x in db_results: temp.append([x['time'], x['AvgTemp']]) return data_to_json(temp)
def updateRequestApprovalStatus(requestID, approval): if (requestID != None and approval != None): result = db.update("update access_request set IsApproved = {} where Id = {}".format(approval, requestID)) if result is True and approval == '1': requestObject = db.query("SELECT roomID, UserId from access_request where id = {}".format(requestID)) result = requestObject[0] id = db.insert("insert into access_rights(RoomId, UserId) values({}, {})".format(result['roomID'], result['UserId'])) return result else: return False
def GetInvalidExitedAccessLogForRoom(RoomId): db_results = db.query( "SELECT r.RoomName, u.Username, al.Time FROM access_logs al " + "JOIN rooms r on al.RoomId = r.Id " + "JOIN users u on al.UserId = u.Id " + "where al.RoomId = {} and al.IsValid = 0 order by al.Time desc". format(RoomId)) if db_results == None or len(db_results) <= 0: return {} return db_results
def TryGetRoomByLight(DeviceName): results = db.query( "select * from rooms where IoTLightDevice = '{}'".format( DeviceName)) if len(results) <= 0: return None result = results[0] return Room(Id=result["Id"], RoomName=result["RoomName"], IotDeviceName=result["IotDeviceName"], IoTLightDevice=result["IoTLightDevice"])
def GetTop10EnviroInfo(RoomId): db_results = db.query( "SELECT ei.temp, ei.humidity, ei.time FROM enviro_info ei where ei.roomId = {} order by ei.time desc limit 10" .format(RoomId)) results_flipped = db_results[::-1] humid = [] temp = [] for x in results_flipped: humid.append([x['time'], x['humidity']]) temp.append([x['time'], x['temp']]) return data_to_json({"temp": temp, "humidity": humid})
def GetAllRooms(): RoomsList = [] results = db.query("select * from rooms") if results == None or len(results) <= 0: return None for r in results: RoomsList.append( Room(Id=r["Id"], RoomName=r["RoomName"], IotDeviceName=r["IotDeviceName"], IoTLightDevice=r["IoTLightDevice"])) return RoomsList
def GetLatestEnviroInfo(RoomId): db_results = db.query( "SELECT ei.temp, ei.humidity, ei.light_value, ei.time FROM enviro_info ei where ei.roomId = {} order by ei.time desc limit 1" .format(RoomId)) if db_results == None or len(db_results) <= 0: return None result = db_results[0] return data_to_json({ "time": result['time'], "temp": result['temp'], "humidity": result['humidity'], "light": result['light_value'] })
def TryLogin(self): result = db.query("select * from users where Username = '******'" .format(self.Username)) if result == None or len(result) <= 0: return False StoredHash = result[0]['PasswordHash'].encode("utf-8") if bcrypt.hashpw(self.Password.encode("utf-8"),StoredHash) != StoredHash: return False self.Id = result[0]['Id'] self.CardId = result[0]['CardId'] self.UserType = result[0]['UserType'] self.UpdateLastLogin() return True
def TryGetRoomByRoomName(RoomName): RoomsList = [] results = db.query( "select * from rooms where lower(RoomName) = '{}'".format( RoomName.lower())) if results == None or len(results) <= 0: return None for r in results: RoomsList.append( Room(Id=r["Id"], RoomName=r["RoomName"], IotDeviceName=r["IotDeviceName"], IoTLightDevice=r["IoTLightDevice"])) return RoomsList
def checkIfAlreadyRequested(roomID, userID): currentTime = datetime.datetime.now() db_results = db.query("SELECT * from access_request where userID = {} and roomID = {}".format(userID, roomID)) #have not created a request to this room if db_results == None or len(db_results) <= 0: return 0 result = db_results[0] status = result['IsApproved'] if status is not None: if status == 1: return 1 if status == 0: approvalDelay = result['DateRequested'] + datetime.timedelta(minutes = 10) if currentTime < approvalDelay: return 2 return 3 else: return 4
def GetAllAccessRights(): return data_to_json( db.query( "SELECT ac.Id, u.Username,u.Id UserId, r.RoomName,r.Id RoomId from access_rights ac " + "JOIN users u ON u.Id = ac.UserId " + "JOIN rooms r ON r.Id = ac.RoomId"))
def GetMotionEventsCount(): results = db.query("select * from motion_events") if results == None: return 0 return len(results)
def GetMotionEvents(roomId): results = db.query("select me.Id, r.RoomName, me.Time, me.FilePath from motion_events me JOIN rooms r ON me.RoomId = r.Id where me.RoomId = {} order by me.Time desc".format(roomId)) if results == None or len(results) <= 0: return {} return results
def GetMotionEventsById(Id): results = db.query("select * from motion_events where Id = {}".format(Id)) if len(results) <= 0: return None result = results[0] return MotionEvent(Id = result["Id"],RoomId = result["RoomId"],Time = result["Time"],FilePath = result["FilePath"])
def GetAllRoomsJSON(): return data_to_json(db.query("select * from rooms"))
def getUserAccessRequests(UserId): db_results = db.query("SELECT r.RoomName, ar.DateRequested, ar.IsApproved FROM access_request ar " "JOIN rooms r on ar.RoomId = r.Id where ar.UserId = {}".format(UserId)) if db_results == None or len(db_results) <= 0: return {} return db_results
def HasAccessRight(self): return len( db.query( "SELECT * from access_rights where userId = {} and roomId = {}" .format(self.UserId, self.RoomId))) > 0
def getUserAccessRights(UserId): return data_to_json( db.query("SELECT r.RoomName from access_rights ac " + "JOIN rooms r ON r.Id = ac.RoomId where ac.UserId = {}". format(UserId)))