def transfer_funds(self, client_id, id_of_BA_taken_from,
id_of_BA_depos_into, amount):
self.proj_zero_log("Attempting to transfer funds...")
sql = "select * from bank_accounts where holder_id = %s and id = %s"
cursor = connection.cursor()
cursor.execute(sql, (int(client_id), int(id_of_BA_taken_from)))
record = cursor.fetchone()
if record:
if float(amount) <= float(str(record[3])):
# Taking from 1st account...
sql = "update bank_accounts set funds=%s where holder_id = %s and id = %s returning *"
cursor = connection.cursor()
cursor.execute(sql, (float(str(record[3])) - int(amount),
int(client_id), int(id_of_BA_taken_from)))
connection.commit()
# Depositing to 2nd account...
sql = "select * from bank_accounts where holder_id = %s and id = %s"
cursor = connection.cursor()
cursor.execute(sql, (int(client_id), int(id_of_BA_depos_into)))
record = cursor.fetchone()
if record:
sql = "update bank_accounts set funds=%s where holder_id = %s and id = %s returning *"
cursor = connection.cursor()
cursor.execute(sql,
(float(amount) + float(str(record[3])),
int(client_id), int(id_of_BA_depos_into)))
connection.commit()
record = cursor.fetchone()
if record:
return BankAcct(record[0], record[1], record[2],
float(str(record[3])), record[4])
else:
raise ResourceNotFound("Client or account not found.")
# End of deposit to 2nd account.
else:
raise ResourceUnavailable(
"Insufficient funds for that transfer.")
else:
raise ResourceNotFound("Client or account not found.")
def withdraw_funds(self, client_id, bank_acct_id, amount):
self.proj_zero_log("Attempting to withdraw funds...")
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:
if float(amount) <= float(str(record[3])):
sql = "update bank_accounts set funds=%s where holder_id = %s and id = %s returning *"
cursor = connection.cursor()
cursor.execute(sql, (float(str(record[3])) - float(amount),
int(client_id), int(bank_acct_id)))
connection.commit()
record = cursor.fetchone()
if record:
return BankAcct(record[0], record[1], record[2],
float(str(record[3])), record[4])
else:
raise ResourceUnavailable(
"Insufficient funds for that withdrawal.")
else:
raise ResourceNotFound("Bank account not found.")
def update_book(self, book: Book) -> Book:
try:
BookDaoLocal.book_table[book.book_id] = book
return book
except KeyError as e:
raise ResourceNotFound(
f'book with ID {book.book_id} was not found')
def setup_process_for_tr(self, employee):
if employee.direct_supervisor == 0:
if employee.department_head == 0:
step = 9
else:
step = 5
else:
step = 1
end_date = date.today() + timedelta(days=5)
process_item = Process(0, "pending", step, str(end_date), str(False),
employee.employee_id)
sql = "INSERT INTO process (process_name, step, begin_date, completed, tr_instance_id) " \
"VALUES (%s,%s,%s,%s,%s) returning *"
cursor = connection.cursor()
cursor.execute(sql, (process_item.process_name, process_item.step,
process_item.begin_date, process_item.completed,
process_item.tr_instance_id))
connection.commit()
record = cursor.fetchone()
cursor.close()
if record:
return Process(record[0], record[1], record[2], record[3],
record[4], record[5])
else:
raise ResourceNotFound(
f"The grading data was not formatted for the database.")
def get_client(self, client_id): # 404 if no such client exists
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(f"Client with id: {client_id} - NOT FOUND")
def get_product_id(cls, product_id):
sql = "SELECT * FROM products WHERE product_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [product_id])
record = cursor.fetchone()
if record:
return Products(record[0], record[1], record[2], record[3], record[4])
else:
raise ResourceNotFound(f"Product with ID {product_id} - Not Found")
def get_user_by_id(cls, user_id):
sql = "SELECT * FROM users WHERE user_id= %s ;"
cursor = connection.cursor()
cursor.execute(sql, [user_id])
record = cursor.fetchone()
if record:
return User(record[0], record[1], record[2])
else:
raise ResourceNotFound(
f"User with ID {user_id} does not exist. Please try again.")
def create_client(self, client): # 201
sql = "INSERT INTO clients (name) VALUES (%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [client.client_name])
connection.commit()
record = cursor.fetchone()
if record:
return Client(record[0], record[1])
else:
raise ResourceNotFound(f"Client could not be created")
def get_transfer_account_into(self, account):
sql = "SELECT * FROM accounts WHERE id = %s AND account_client_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [account.account_id, account.account_client_id])
record = cursor.fetchone()
if record:
new_amount = (float(record[2]) + float(account.account_amount))
return Account(record[0], record[1], new_amount, record[3])
else:
raise ResourceNotFound(f"Client id and account id does not exist.")
def delete_product_from_cart(cart_id):
sql = "DELETE FROM product_cart WHERE product_cart_id = %s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [cart_id])
connection.commit()
record = cursor.fetchone()
if record:
return ProductCart(record[0], record[1], record[2], record[3], record[4]).json()
else:
raise ResourceNotFound(f"Cart with ID {cart_id} does not exist. Please try again.")
def get_user(user_id):
sql = "SELECT * FROM banking.users where userid = %s"
cursor = connection.cursor()
cursor.execute(sql, [user_id])
record = cursor.fetchone()
if record:
return Users(record[0], record[1], record[2], record[3])
else:
raise ResourceNotFound(f"User with ID {user_id} - Not Found")
def get_account(self, account_id):
sql = "SELECT * FROM accounts WHERE id = %s"
cursor = connection.cursor()
cursor.execute(sql, [account_id])
record = cursor.fetchone()
if record:
return Account(record[0], record[1], record[2], record[3], record[4])
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" # %s is a placeholder for string
cursor = connection.cursor()
cursor.execute(sql, [client_id])
record = cursor.fetchone()
if record:
return Client(record[0], record[1]).json()
else:
raise ResourceNotFound(f"Client with id: {client_id} - Not Found")
def get_deposit(self, account):
sql = "SELECT * FROM accounts WHERE id = %s AND account_client_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [account.account_id, account.account_client_id])
record = cursor.fetchone()
if record:
return Account(record[0], record[1],
(float(record[2]) + account.account_amount),
record[3]).json()
else:
raise ResourceNotFound(f"Client id and account id does not exist.")
def get_user_by_id(userid): # Retrieve User by ID from users and return
sql = "SELECT * FROM users where id= %s"
cursor = connection.cursor()
cursor.execute(sql, [userid])
record = cursor.fetchone()
if record:
return Users(record[0], record[1], record[2], record[3], record[4],
record[5])
else:
raise ResourceNotFound(f"User with id: {userid} - Not Found")
def update_client(self, client): # 404 if no such client exists
sql = "UPDATE clients SET name=%s WHERE id= %s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (client.client_name, int(client.client_id)))
connection.commit()
record = cursor.fetchone()
if record:
return Client(record[0], record[1])
else:
raise ResourceNotFound(
f"Client record {client.client_id} does not exist.")
def get_courses_by_id(course_id):
sql = "SELECT * FROM student_courses where id=%s"
cursor = connection.cursor()
cursor.execute(sql, [course_id])
record = cursor.fetchone()
if record:
return StudentCourses(record[0], record[1], record[2])
else:
raise ResourceNotFound(
f"Student course id: {course_id} - Not Found")
def get_record(self, username, fk1=None, fk2=None):
sql = "SELECT * FROM employees WHERE username=%s"
cursor = connection.cursor()
cursor.execute(sql, [username])
record = cursor.fetchone()
if record:
return Employee(record[0])
else:
raise ResourceNotFound(f"Employee with user {username} not found.")
def get_user(self, user_id):
sql = "SELECT * FROM users WHERE id =%s"
cursor = connection.cursor()
cursor.execute(sql, [user_id])
record = cursor.fetchone()
if record:
return User(record[0], record[1], record[2], record[3])
else:
raise ResourceNotFound(f"User with id: {user_id} - Not Found")
def get_client(self, client_id):
sql = "SELECT * FROM clients WHERE clientid = %s"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
record = cursor.fetchone()
if record:
return Client(record[0], record[1], record[2])
else:
raise ResourceNotFound(f"Client with id: {client_id} - Not Found")
def get_record(self, position_name, fk1=None, fk2=None):
sql = "SELECT * FROM permissions WHERE position_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [position_name])
record = cursor.fetchone()
if record:
return Permissions(record[0], record[1], record[2])
else:
raise ResourceNotFound(f"Permissions for position {position_name} not found.")
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_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_record(self, department_name, fk1=None, fk2=None):
sql = "SELECT * FROM departments WHERE department_name=%s"
cursor = connection.cursor()
cursor.execute(sql, [department_name])
record = cursor.fetchone()
if record:
return Department(record[0], record[1])
else:
raise ResourceNotFound(
f"Department with name {department_name} 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_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 get_account(acct_id):
try:
sql = "SELECT * FROM banking.accounts where acctid = %s"
cursor = connection.cursor()
cursor.execute(sql, [acct_id])
records = cursor.fetchall()
for record in records:
acct1 = Accounts(int(record[0]), int(record[1]),
int(record[2]), int(record[3]))
return acct1
except KeyError:
raise ResourceNotFound(f"Account with id: {acct_id} - NOT FOUND")
def get_client(self, client_id):
sql = 'SELECT * FROM client WHERE id = %s'
cursor = connection.cursor()
cursor.execute(sql, [client_id])
record = cursor.fetchone()
if record:
return Client(record[0], record[1], record[2], record[3],
record[4], record[5]).json()
else:
raise ResourceNotFound(f'Client with id: {client_id} - Not Found')
def delete_cart_items_from_user_id(user_id):
try:
print(user_id)
sql = "DELETE FROM product_cart WHERE user_id = %s"
cursor = connection.cursor()
print("Here1")
cursor.execute(sql, [user_id])
print("Here2")
connection.commit()
return "Cart Deleted"
except Exception as e:
raise ResourceNotFound(f"Order does not exist. Please try again.")
def add_product(product_cart):
sql = "INSERT INTO product_cart VALUES(default,%s, %s, %s, %s, %s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [product_cart.product_id, product_cart.user_id,
product_cart.product_name, product_cart.product_price, product_cart.quantity])
connection.commit()
record = cursor.fetchone()
if record:
returned_product = ProductCart(record[0], record[1], record[2], record[3], record[4], record[5])
return returned_product
else:
raise ResourceNotFound(f"User with ID {product_cart.user_id} does not exist. Please try again.")