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)