def get_all_cameras_for_server(server): try: cameras_array = [] camera_link_query = Query() camera_link_query.TransactionType = eTransactionType.Query camera_link_query.Sql ="SELECT cs.CameraID FROM tCameraServer cs WHERE cs.ServerID = %s" camera_link_query.Args = [str(server.ServerID)] camera_ids_array = DBConn.single_query(camera_link_query) if len(camera_ids_array) > 0: camera_ids_string = "" for camera_id in camera_ids_array: value = camera_id.get("CameraID") camera_ids_string += str(value) + "," camera_ids_string = camera_ids_string[:-1] camera_query = Query() camera_query.TransactionType = eTransactionType.MultiSelectQuery camera_query.Sql = sql_select + sql_from + " WHERE c.CameraID IN (?)" camera_query.Args = [str(camera_ids_string)] camera_dict_list = DBConn.single_query(camera_query) for camera_dict in camera_dict_list: camera_obj = Camera() camera_obj.mapper(camera_dict) cameras_array.append(camera_obj) return cameras_array except Exception as err: print(err) ServerBL.update_server_status(server.ServerID, ServerStatus.Error.value) return []
def __insert_camera_log(camera_id, status_id, message): insert_query = Query() insert_query.TransactionType = eTransactionType.Insert insert_query.Sql = "INSERT INTO tCameraLog(CameraID, CameraStatusID, CameraMessage, LogDateTime) VALUES (%s, %s, %s, %s)" insert_query.Args = [ str(camera_id), str(status_id), message, datetime.now() ] DBConn.single_query(insert_query)
def __insert_server_log(server_id, status_id, message): insert_query = Query() insert_query.TransactionType = eTransactionType.Insert insert_query.Sql = "INSERT INTO tServerLog(ServerID, ServerStatusID, ServerMessage, LogDateTime) VALUES (%s, %s, %s, %s)" insert_query.Args = [ str(server_id), str(status_id), str(message), datetime.now() ] DBConn.single_query(insert_query)
def verify_unused_username(username): query = "SELECT u.Username FROM tUsers u WHERE u.Username = '******';" result = DBConn.query_return(query) if len(result) > 0: return False else: return True
def insert_default_values_into_db(mac_address): server = Server() server.Name = "Server" server.MacAddress = mac_address server.InternalAddress = get_local_ip() server.ExternalAddress = get_external_ip() server.PortNumber = 8089 server.ServerStatusID = ServerStatus.ServerBootStart.value server.DirectoryPath = "/VideoSecurityServer" insert_query = Query() insert_query.TransactionType = eTransactionType.Insert insert_query.Sql = "INSERT INTO tServers (Name, MacAddress, InternalAddress, ExternalAddress, PortNumber, ServerStatusID, DirectoryPath) VALUES (%s,%s,%s,%s,%s,%s,%s)" insert_query.Args = [ str(server.Name), str(server.MacAddress), str(server.InternalAddress), str(server.ExternalAddress), str(server.PortNumber), str(server.ServerStatusID), str(server.DirectoryPath) ] server.ServerID = DBConn.single_query(insert_query) __insert_server_log(server.ServerID, ServerStatus.ServerBootStart.value, "Server is starting.")
def verify_unused_email(email): query = "SELECT u.Email FROM tUsers u WHERE u.Email = '" + email + "';" result = DBConn.query_return(query) if len(result) > 0: return False else: return True
def insert_default_values_into_db(mac_address): camera = Camera() camera.Name = "Camera" camera.MacAddress = mac_address camera.InternalAddress = get_local_ip() camera.ExternalAddress = get_external_ip() camera.PortNumber = 8089 camera.CameraStatusID = CameraStatus.CameraBootStart.value camera.DirectoryPath = "" insert_query = Query() insert_query.TransactionType = eTransactionType.Insert insert_query.Sql = "INSERT INTO tCameras (Name, MacAddress, InternalAddress, ExternalAddress, PortNumber, CameraStatusID, DirectoryPath) VALUES (%s, %s, %s, %s, %s, %s, %s)" insert_query.Args = [ str(camera.Name), str(camera.MacAddress), str(camera.InternalAddress), str(camera.ExternalAddress), str(camera.PortNumber), str(camera.CameraStatusID), str(camera.DirectoryPath) ] camera.CameraID = DBConn.single_query(insert_query) __insert_camera_log(camera.CameraID, CameraStatus.CameraBootStart.value, "Camera is starting.")
def update_startup_values_into_db(camera): camera.InternalAddress = get_local_ip() camera.ExternalAddress = get_external_ip() camera.CameraStatusID = CameraStatus.CameraBootStart.value update_query = Query() update_query.TransactionType = eTransactionType.Update update_query.Sql = "UPDATE tCameras SET InternalAddress = %s, ExternalAddress = %s, CameraStatusID = %s WHERE CameraID = %s" update_query.Args = [ str(camera.InternalAddress), str(camera.ExternalAddress), str(camera.CameraStatusID), str(camera.CameraID) ] DBConn.single_query(update_query) __insert_camera_log(camera.CameraID, CameraStatus.CameraBootStart.value, "Camera is starting.")
def update_startup_values_into_db(server): server.InternalAddress = get_local_ip() server.ExternalAddress = get_external_ip() server.ServerStatusID = ServerStatus.ServerBootStart.value update_query = Query() update_query.TransactionType = eTransactionType.Update update_query.Sql = "UPDATE tServers SET InternalAddress = %s, ExternalAddress = %s, ServerStatusID = %s WHERE ServerID = %s" update_query.Args = [ str(server.InternalAddress), str(server.ExternalAddress), str(server.ServerStatusID), str(server.ServerID) ] DBConn.single_query(update_query) __insert_server_log(server.ServerID, ServerStatus.ServerBootStart.value, "Server is starting.")
def get_user_salt(username): query = "SELECT u.Salt FROM tUsers u WHERE u.Username = '******';" result = DBConn.query_return(query) if len(result) == 1: salt = next(iter(result))["Salt"] # Encode back into byte array salt = salt.encode("utf-8", "ignore") return salt
def delete(password_id): password_detail_id = None query = "SELECT p.PasswordDetailID FROM tPassword p WHERE p.PasswordID = " + str( password_id) + ";" result = DBConn.query_return(query) for dictionary in result: password_detail_id = list(dictionary.values())[0] if password_detail_id is not None: query = "DELETE FROM tPassword p WHERE p.PasswordID = " + str( password_id) + ";" DBConn.query_update(query, False) query = "DELETE FROM tPasswordDetail pd WHERE pd.PasswordDetailID = " + str( password_detail_id) + ";" DBConn.query_update(query, False) return True else: return False
def login(user): if user.UserName is not None and user.Password is not None: user.Password = get_hashed_password(user.UserName, user.Password) # The 'BINARY' keyword forces case sensitive searches query = "SELECT u.UserID, u.Salt, u.Email, u.FirstName, u.LastName FROM tUsers u WHERE u.Username = '******' and u.Password = BINARY '" + user.Password + "';" result = DBConn.query_return(query) user.mapper(result) return user else: return
def insert(password): # Detail Entry query = "INSERT INTO tPasswordDetail (PasswordType, PasswordName, PasswordUser, PasswordSite, PasswordPassword, PasswordNote) " \ "VALUES ('" + password.PasswordType + "', '" + password.PasswordName + "', '" + password.PasswordUser + "', '" + password.PasswordSite + "', '" + password.PasswordPassword + "', '" + password.PasswordNote + "');" password_detail_id = DBConn.query_insert(query) if password_detail_id != 0: # Password Entry timestamp = str(datetime.now()) query = "INSERT INTO tPassword (PasswordDetailID, UserID, DateCreated, DateModified) VALUES (" + str( password_detail_id) + ", " + str( password.UserID) + ", NOW(), NOW());" password.PasswordID = DBConn.query_update(query, True) if password.PasswordID != 0: password.DateCreated = timestamp password.DateModified = timestamp return password else: return password else: # return will 0 ID return password
def fill(user): if user.UserID == 0: return None else: query = "SELECT u.Username, u.Salt, u.Email, u.FirstName, u.LastName, u.Password FROM tUsers u WHERE u.UserID = " + str( user.UserID) + ";" result = DBConn.query_return(query) if len(result) > 0: user.mapper(result) else: user.UserID = 0 return user
def fill(password): if password.PasswordID == 0: return None else: query = "SELECT p.PasswordID, p.DateCreated, p.DateModified, p.UserID, pd.PasswordType, pd.PasswordName, pd.PasswordUser, pd.PasswordSite, pd.PasswordPassword, pd.PasswordNote " \ "FROM tPassword p LEFT JOIN tPasswordDetail pd ON p.PasswordDetailID = pd.PasswordDetailID " \ "WHERE p.PasswordID = " + password.PasswordID + ";" result = DBConn.query_return(query) if len(result) > 0: password.mapper(result) else: password.PasswordID = 0 return password
def get_all_user_passwords(user_id, user_password): passwords = [] query = "SELECT p.PasswordID, p.DateCreated, p.DateModified, p.UserID, pd.PasswordType, pd.PasswordName, pd.PasswordUser, pd.PasswordSite, pd.PasswordPassword, pd.PasswordNote, u.UserID " \ "FROM tPassword p " \ "LEFT JOIN tPasswordDetail pd ON p.PasswordDetailID = pd.PasswordDetailID " \ "LEFT JOIN tUsers u ON u.UserID = p.UserID " \ "WHERE u.UserID = " + user_id + " AND u.Password = '******';" result = DBConn.query_return(query) for row in result: password = Password() password.mapper(row) passwords.append(password) return passwords
def get_server_from_mac_address(mac_address): server = Server() query = Query() query.TransactionType = eTransactionType.Query query.Sql = sql_select + sql_from + " WHERE s.MacAddress = %s" query.Args = [str(mac_address)] result = DBConn.single_query(query) if len(result) > 0: server.mapper(result[0]) return server else: return None
def get_camera_from_mac_address(mac_address): camera = Camera() query = Query() query.TransactionType = eTransactionType.Query query.Sql = sql_select + sql_from + " WHERE c.MacAddress = %s" query.Args = [str(mac_address)] result = DBConn.single_query(query) if len(result) > 0: camera.mapper(result[0]) return camera else: return None
def signup(user): if not verify_unused_email(user.Email): raise EmailUnavailableException() if not verify_unused_username(user.UserName): raise UsernameUnavailableException() user.Password, user.Salt = hash_password(str(user.Password)) query = "INSERT INTO tUsers (Username, Password, Salt, Email, FirstName, LastName) VALUES ('" + user.UserName + "', '" + user.Password + "', '" + user.Salt + "', '" + user.Email + "', '" + user.FirstName + "', '" + user.LastName + "');" new_id = DBConn.query_update(query, True) if new_id != 0: user.UserID = new_id return user else: # return will 0 ID return user
def __update_current_camera_status(camera_id, status_id): update_query = Query() update_query.TransactionType = eTransactionType.Update update_query.Sql = "Update tCameras SET CameraStatusID = %s WHERE CameraID = %s" update_query.Args = [str(status_id), str(camera_id)] DBConn.single_query(update_query)
def __update_current_server_status(server_id, status_id): update_query = Query() update_query.TransactionType = eTransactionType.Update update_query.Sql = "UPDATE tServers SET ServerStatusID = %s WHERE ServerID = %s" update_query.Args = [str(status_id), str(server_id)] DBConn.single_query(update_query)