def get_products(cls):
try:
sql = "SELECT * FROM products;"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
products = []
for product in records:
products.append(Products(product[0], product[1], product[2], product[3], product[4]))
return products
except Exception as e:
raise ResourceNotFound(f"Credentials do not match any existing records. Please try again.")
def get_account(self, account_id):
sql = "SELECT * FROM account where account_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [account_id])
record = cursor.fetchone()
if record:
return Account(record[0], float(record[1]), record[2])
else:
raise ResourceNotFound(
f"Account with ID: {account_id} - Not Found")
def delete_client(
self, client_id): # 404 if no such client exists & 205 == success
sql = "DELETE FROM clients WHERE id=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
connection.commit()
record = cursor.fetchone()
if record:
return Client(record[0], record[1])
else:
raise ResourceNotFound(
f"Client record {client_id} does not exist.")
def get_account(self, account):
sql = "SELECT * FROM accounts WHERE id = %s AND account_client_id = %s"
cursor = connection.cursor()
cursor.execute(
sql, (int(account.account_id), int(account.account_client_id)))
record = cursor.fetchone()
if record:
return Account(record[0], record[1], float(record[2]),
record[3]).json()
else:
raise ResourceNotFound(
f"Account id or client account id - NOT FOUND")
def get_tuition(self, empid): # Retrieve a single Tuition by passing a Employee ID
sql = "SELECT * FROM tuition WHERE empid=%s"
cursor = connection.cursor()
cursor.execute(sql, [empid])
record = cursor.fetchone()
if record:
return Tuition(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7],
record[8], record[9])
else:
raise ResourceNotFound(f" This Employee id: {empid} doesn't have a Tuition created yet")
def get_account(self, account_id):
pass
sql = "SELECT * FROM accounts WHERE account_id = %s" # %s is a placeholder for string
cursor = connection.cursor()
cursor.execute(sql, [account_id])
record = cursor.fetchone()
if record:
return Account(record[0], record[1], record[2], record[3])
else:
raise ResourceNotFound(f"Account with id: {account_id} - Not Found")
def get_client(self, client_id):
sql = "SELECT * FROM clients WHERE client_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
rec = cursor.fetchone()
if rec:
client = Client(rec[0], rec[1], rec[2], rec[3])
return client
else:
raise ResourceNotFound("Client ID not found")
def update_accounts_after_transfer(self, account):
sql = "UPDATE accounts SET amount=%s WHERE id = %s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [account.account_amount, account.account_id])
connection.commit()
record = cursor.fetchone()
if record:
return Account(record[0], record[1], float(record[2]),
record[3]).json()
else:
raise ResourceNotFound(
f"No account has account and client id provided")
def get_movie(self, movie_id):
sql = "SELECT * FROM movies WHERE id = %s"
cursor = connection.cursor()
cursor.execute(sql, [movie_id])
record = cursor.fetchone()
if record:
return Movie(record[0], record[1], record[2], float(record[3]),
record[4], record[5])
else:
raise ResourceNotFound(f"Movie with id: {movie_id} - Not Found")
def update_client(self, change):
sql = "UPDATE client SET username = %s WHERE client_id = %s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change.username, change.client_id))
connection.commit()
record = cursor.fetchone()
if record:
return Client(record[0], record[1])
else:
raise ResourceNotFound(
f"Client with ID: {change.client_id} - Not Found")
def employee_authentication(self, employee):
sql = "SELECT * FROM employee where employee_name = %s AND title = %s"
cursor = connection.cursor()
cursor.execute(sql, (employee.name, employee.title))
record = cursor.fetchone()
cursor.close()
if record:
return Employee(record[0], record[1], record[2], record[3],
float(record[4]), record[5])
else:
raise ResourceNotFound(
f"Employee record could not be verified with input data.")
def get_employee_by_id(self, employee_id):
sql = "SELECT * FROM employee where id = %s"
cursor = connection.cursor()
cursor.execute(sql, [employee_id])
record = cursor.fetchone()
cursor.close()
if record:
return Employee(record[0], record[1], record[2], record[3],
float(record[4]), record[5])
else:
raise ResourceNotFound(
f"Employee record could not be verified with input data.")
def update_client(self, change):
sql = "UPDATE clients SET firstname = %s, lastname= %s WHERE clientid = %s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql,
(change.first_name, change.last_name, change.client_id))
connection.commit()
record = cursor.fetchone()
if record:
updated_client = Client(record[0], record[1], record[2])
return updated_client
else:
raise ResourceNotFound(f"Client ID: {change.client_id}- NOT FOUND")
def create_grading_type(self, grading_type):
sql = "INSERT INTO grading_type (graded, score, tr_instance_id) VALUES (%s,%s,%s) returning *"
cursor = connection.cursor()
cursor.execute(sql, (grading_type.graded, grading_type.score,
grading_type.tr_instance_id))
connection.commit()
record = cursor.fetchone()
cursor.close()
if record:
return GradingType(record[0], record[1], record[2], record[3])
else:
raise ResourceNotFound(
f"The grading data was not formatted for the database.")
def create_grading(self, process):
sql = "INSERT INTO grading (verification_person, tr_instance_id) VALUES RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (process.step, process.tr_instance_id))
connection.commit()
record = cursor.fetchone()
cursor.close()
if record:
return Grading(record[0], record[1], record[2], record[3],
record[4])
else:
raise ResourceNotFound(
f"The database doesn't accept the input types entered")
def get_record(self, tuition_name, fk1=None, fk2=None):
sql = "SELECT * FROM tuition_types WHERE tuition_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [tuition_name])
record = cursor.fetchone()
if record:
return TuitionType(record[0], record[1], record[2],
float(record[3]))
else:
raise ResourceNotFound(
f"Tuition by name {tuition_name} not found.")
def get_user_credentials(cls, user_credentials):
sql = "SELECT * FROM user_credentials WHERE username= %s AND password_ = %s"
cursor = connection.cursor()
cursor.execute(sql,
[user_credentials.user_name, user_credentials.password])
record = cursor.fetchone()
if record:
return UserCredentials(record[0], record[1], record[2])
else:
raise ResourceNotFound(
f"Credentials do not match any existing records. Please try again."
)
def get_record(self, password, fk1=None, fk2=None):
sql = "SELECT * FROM employee_information WHERE password=%s"
cursor = connection.cursor()
cursor.execute(sql, [password])
record = cursor.fetchone()
if record:
return EmployeeInformation(record[0], record[1], record[2],
record[3], record[4], record[5],
record[6], record[7])
else:
raise ResourceNotFound(
f"Password does not match credentials in database.")
def delete_bank_acct(self, client_id, bank_acct_id):
self.proj_zero_log("Attempting to delete bank account...")
sql = "delete from bank_accounts where holder_id = %s and id = %s returning *"
cursor = connection.cursor()
cursor.execute(sql, (int(client_id), int(bank_acct_id)))
connection.commit()
record = cursor.fetchone()
if not record:
raise ResourceNotFound("Client or account not found.")
def delete_account(self, account):
sql = "DELETE FROM accounts WHERE id=%s AND account_client_id = %s RETURNING *"
cursor = connection.cursor()
cursor.execute(
sql, (int(account.account_id), int(account.account_client_id)))
connection.commit()
record = cursor.fetchone()
if record:
changed_account = Account(record[0], record[1], float(record[2]),
record[3])
return changed_account
else:
raise ResourceNotFound(
f"No account has account and client id provided")
def create_account(self, account):
self.client_id_existence_test(int(account.account_client_id))
sql = "INSERT INTO accounts (name,amount, account_client_id) VALUES (%s, %s, %s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (account.account_name, account.account_amount,
account.account_client_id))
connection.commit()
record = cursor.fetchone()
if record:
return Account(record[0], record[1], float(record[2]), record[3])
else:
raise ResourceNotFound(
f"The Client id enter can't be verified. Unable to create an account without an activity client id"
)
def get_all_products_from_cart_by_user_id(user_id):
sql = "SELECT * FROM product_cart where user_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [user_id])
connection.commit()
records = cursor.fetchall()
cart_list = []
if records:
for record in records:
product = ProductCart(record[0], record[1], record[2], record[3], record[4], record[5])
cart_list.append(product)
return cart_list
else:
raise ResourceNotFound(f"User with ID {user_id} does not exist. Please try again.")
def update_account(self, change):
sql = "UPDATE accounts Set clientId=%s, accounttype=%s, accountbalance=%s where accountid = %s"
cursor = connection.cursor()
cursor.execute(sql, (change.clientId, change.accounttype, change.accountbalance))
connection.commit()
record = cursor.fetchone()
if record:
updated_account = Account(record[0], record[1], record[2], record[3], record[4])
return updated_account
else:
raise ResourceNotFound(f"there is no account with that ID.")
def get_client(self, client_id):
self.proj_zero_log("Attempting to get client...")
sql = "select * from clients where id = %s"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
record = cursor.fetchone()
if record:
return Client(record[0], record[1])
else:
raise ResourceNotFound("Client not found.")
def get_record(self, case_id, fk1=None, fk2=None):
sql = "SELECT * FROM trainings WHERE case_id=%s"
cursor = connection.cursor()
cursor.execute(sql, [case_id])
record = cursor.fetchone()
if record:
return Training(record[0], record[1], record[2], record[3],
record[4], record[5], record[6], record[7],
record[8], record[9], record[10], record[11],
float(record[12]), record[13], record[14],
record[15])
else:
raise ResourceNotFound("No submission found for that ID.")
def get_employee(
self,
empid): # Retrieve a single employee by passing an EmployeeID
sql = "SELECT * FROM employee WHERE empid =%s"
cursor = connection.cursor()
cursor.execute(sql, [empid])
record = cursor.fetchone()
if record:
return Employee(record[0], record[1], record[2], record[3],
record[4], record[5], record[6], record[7],
record[8], record[9])
else:
raise ResourceNotFound(f"Employee with id: {empid} - Not Found")
def login_employees(
self, username, password
): # Retrieve a single employee by passing user name and password
sql = "SELECT * FROM employee WHERE username =%s and password =%s"
cursor = connection.cursor()
cursor.execute(sql, [username, password])
record = cursor.fetchone()
if record:
return Employee(record[0], record[1], record[2], record[3],
record[4], record[5], record[6], record[7],
record[8], record[9])
else:
raise ResourceNotFound(
f"Employee with id: {username} or {username} - Not Found")
def get_all_accounts(self, client_id):
sql = "SELECT * FROM accounts WHERE account_client_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
records = cursor.fetchall()
account_list = []
if records:
for record in records:
account = Account(record[0], record[1], float(record[2]),
record[3])
account_list.append(account.json())
return account_list
else:
raise ResourceNotFound(
f"Client with id: {client_id} has no accounts.")
def all_accounts(self):
sql = "SELECT * FROM accounts"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
account_list = []
if records:
for record in records:
account = Account(record[0], record[1], record[2], record[3], record[4])
account_list.append(account.json())
else:
raise ResourceNotFound(f"Currently there are no accounts- Not Found")
def get_bank_acct(self, client_id, bank_acct_id):
self.proj_zero_log("Attempting to get bank account...")
sql = "select * from bank_accounts where holder_id = %s and id = %s"
cursor = connection.cursor()
cursor.execute(sql, (int(client_id), int(bank_acct_id)))
record = cursor.fetchone()
if record:
return BankAcct(record[0], record[1], record[2],
float(str(record[3])), record[4])
else:
raise ResourceNotFound("Bank account not found.")