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 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 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 update_courses(self, change, courseid): # Update Courses Table
sql = "UPDATE courses SET coursename=%s WHERE courseid=%s "
cursor = connection.cursor()
cursor.execute(sql, change.coursename)
connection.commit()
record = cursor.fetchone()
return Courses(int(record[0]), record[1], int(record[2]))
def get_all_accounts_in_range(cls, client_id, range1, range2):
sql = f"SELECT * FROM accounts where client_id ={client_id} AND amount BETWEEN {range1} AND {range2};"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
accounts = [Account(elem[0], elem[1], elem[2]) for elem in records]
return accounts
def get_all_accounts_for_client(cls, client_id):
sql = f"select * from accounts where client_id ={client_id};"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
accounts = [Account(elem[0], elem[1], elem[2]) for elem in records]
return accounts
def get_account_with_id(cls, client_id, account_id):
sql = f"SELECT * FROM(SELECT * FROM accounts where client_id = {client_id}) m WHERE id ={account_id};"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
accounts = [Account(elem[0], elem[1], elem[2]) for elem in records]
return accounts
def get_client(cls, client_id):
sql = f" select * from clients where id = {client_id} "
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
client = [Client(elem[0], elem[1]) for elem in records]
return client
def get_all_clients(cls):
sql = "SELECT * FROM clients"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
clients = [Client(elem[0], elem[1]) for elem in records]
return clients
def update_user(self, change):
sql = "UPDATE users SET firstname = %s, lastname=%s, phone=%s WHERE id =%s"
cursor = connection.cursor()
cursor.execute(
sql,
(change.firstname, change.lastname, change.phone, change.user_id))
connection.commit()
def delete_employee(self, employee_id):
sql = "DELETE FROM employees WHERE employee_id = %s"
log(f"Deleting an employee")
cursor = connection.cursor()
cursor.execute(sql, [employee_id])
connection.commit()
return 'deleted'
def get_course_by_id(course_id):
sql = "SELECT * FROM courses WHERE id=%s"
cursor = connection.cursor()
cursor.execute(sql, [course_id])
record = cursor.fetchone()
course = Courses(record[0], record[1], record[2])
return course
def create_account(self, account):
sql = 'INSERT INTO account VALUES (DEFAULT, %s, %s) RETURNING *'
cursor = connection.cursor()
cursor.execute(sql, (account.account_id, account.account_balance, account.cl_id))
connection.commit()
record = cursor.fetchone()
new_account = Account(record[0], record[1], record[2])
return new_account
def create_user(user):
sql = "Insert into banking.users Values(default,%s,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (user.username, user.first_name, user.last_name))
connection.commit()
record = cursor.fetchone()
new_user = Users(record[0], record[1], record[2], record[3])
return new_user
def delete_account_with_id(cls, client_id, account_id):
account = cls.get_account_with_id(client_id, account_id)
if not account:
return "no account or client exists", 404
sql = f"DELETE FROM accounts WHERE id = {account_id} AND client_id={client_id};"
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
return "Deleted account"
def update_client(cls, client):
sql = f"UPDATE clients SET client_name = '{client.name}'WHERE id = {int(client.client_id)} RETURNING client_name;"
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
record = cursor.fetchone()
if record:
return "Done!"
return "no such client exist", 404
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 create_acct(account):
sql = "Insert into banking.accounts values (default,%s,%s,%s) returning *"
cursor = connection.cursor()
cursor.execute(sql,
(account.acct_num, account.balance, account.userid))
connection.commit()
record = cursor.fetchone()
new_acct = Accounts(record[0], record[1], record[2], record[3])
return new_acct
def update_client(self, change):
sql = "UPDATE clients SET client_name=%s WHERE client_id=%s RETURNING * "
cursor = connection.cursor()
cursor.execute(sql, (change.client_name, change.client_id))
connection.commit()
record = cursor.fetchone()
return Client(record[0], record[1])
def update_client(self, change):
sql = 'UPDATE client SET first_name=%s WHERE id = %s RETURNING *'
cursor = connection.cursor()
cursor.execute(sql, (change.client_first_name, change.client_id))
record = cursor.fetchone()
new_client = Client(record[0], record[1], record[2], record[3],
record[4], record[5]).json()
return new_client
def create_account(self, account):
sql = "INSERT INTO account VALUES(DEFAULT, %s, %s, %s, %s, %s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql,
(account.account_type, account.amt_dep, account.amt_withdraw, account.status, account.userid))
connection.commit()
record = cursor.fetchone()
return Account(Account(record[0], record[1], record[2], float(record[3]), record[4]))
def get_all_student_courses(): # retrieve all student courses
sql = "SELECT * FROM student_courses"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
studentcourses_list = []
for record in records:
student_courses = StudentCourses(record[0], record[1], record[2])
studentcourses_list.append(student_courses.json())
return studentcourses_list
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 all_courses(self): # Retrieve all Course from courses and return
sql = "SELECT * FROM courses"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
courses_list = []
for record in records:
course = Courses(record[0], record[1], record[2])
courses_list.append(course.json())
return courses_list
def all_role(self): # Retrieve all Role from Role and return
sql = "SELECT * FROM roles"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
roles_list = []
for record in records:
role = Roles(record[0], record[1], record[2])
roles_list.append(role.json())
return roles_list
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_courses_by_teacher_id(teacher_id):
sql = "SELECT * FROM courses WHERE teacher_id=%s"
cursor = connection.cursor()
cursor.execute(sql, [teacher_id])
records = cursor.fetchall()
courses_list = []
for record in records:
course = Courses(record[0], record[1], record[2])
courses_list.append(course.json())
return courses_list
def get_client(self, client_id):
sql = "SELECT * FROM clients WHERE client_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])
else:
raise UserNotFound(f"Client with id: {client_id} - Not Found")
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 get_all_courses():
sql = "SELECT * FROM courses"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
course_list = []
for record in records:
courses = Courses(record[0], record[1], record[2])
course_list.append(courses)
return course_list
