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)))
