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 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_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 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 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 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 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(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 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(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 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 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 delete_client(cls, client_id):
sql = f"DELETE FROM clients WHERE id ={client_id} RETURNING client_name;"
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
record = cursor.fetchone()
if record:
return "Success!", 205
return "no such client exist", 404
def update_account(self, change):
sql = "UPDATE accounts SET deposit_amount=%s, withdraw_amount=%s, account_balance=%s WHERE account_id=%s " \
"RETURNING * "
cursor = connection.cursor()
cursor.execute(sql, change.deposit_amount, change.withdraw_amount, change.account_balance)
connection.commit()
record = cursor.fetchone()
return Account(record[0], record[1], record[2], record[3])
def create_client(self, client):
sql = "INSERT INTO clients VALUES (DEFAULT,%s,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (client.first_name, client.last_name, client.pin))
connection.commit()
rec = cursor.fetchone()
new_client = Client(rec[0], rec[1], rec[2], rec[3]).json()
return new_client
def create_client(self, client):
sql = "INSERT INTO clients VALUES (DEFAULT,%s,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (client.name, client.email, client.password))
connection.commit()
record = cursor.fetchone()
new_client = Client(record[0], record[1], record[2], record[3])
return new_client
def update_employee(self, change):
sql = "UPDATE employees SET name=%s WHERE employee_id=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change.name, change.employee_id))
connection.commit()
log(f"Updating an employee")
record = cursor.fetchone()
new_employee = Employee(record[0], record[1], record[2], record[3],
record[4], record[5], record[6])
return new_employee
def add_acc_to_client(self, client_id):
sql = "INSERT INTO accounts Values(default, default, %s, accounttype = 1, accountbalance = 20) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
connection.commit()
record = cursor.fetchone()
new_account = Account(record[0], record[1], record[2], record[3],
record[4])
return new_account
def deposit_into_account(cls, client_id, account_id, amount):
account = cls.get_account_with_id(client_id, account_id)
if not account:
return "no account or client exists", 404
else:
sql = f"UPDATE accounts SET amount ={amount + account[0].amount} WHERE id = {account_id} AND client_id={client_id};"
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
return "deposit complete", 200
def create_client(self, client):
sql = "INSERT INTO clients VALUES(default, %s, %s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (client.first_name, client.last_name))
connection.commit()
record = cursor.fetchone()
new_client = Client(record[0], record[1], record[2])
return new_client
def create_account(self, account):
sql = "INSERT INTO accounts VALUES(default, default, %s, %s, %s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (account.client_id, account.account_type, account.account_balance))
connection.commit()
record = cursor.fetchone()
new_account = Account(record[0], record[1], record[2], record[3], record[4])
return new_account
def update_employeeRole(self, change, empid): # Update Employee Table
sql = "UPDATE employee SET emprole=%s WHERE empid=%s RETURNING *"
cursor = connection.cursor()
# print("test if change",change)
# print("test if empid", empid)
cursor.execute(sql, change.emprole.change.empid)
connection.commit()
record = cursor.fetchone()
# print(record[0],record[0],record[0],record[0],record[0],record[0],record[0],record[0])
return Employee(record[0])
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 create_client(self, client):
sql = "INSERT INTO clients VALUES(DEFAULT, %s) RETURNING *;"
cursor = connection.cursor()
# TypeError: Object of type Client is not JSON serializable
cursor.execute(sql, (client.client_name, ))
record = cursor.fetchone()
# cursor.execute("ROLLBACK")
# because this is insertion which is part of the DML, the data needs to be committed after it's added
connection.commit()
returned_val = Client(client_id=record[0], client_name=record[1])
return returned_val
def update_employee(self, change): # Update Employee Table
sql = "UPDATE employee SET firstname = %s, midname=%s, lastname=%s, phone=%s , email=%s, address=%s,emprole=%s, username=%s ,password=%s WHERE empid=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(
sql, (change.firstname, change.midname, change.lastname,
change.phone, change.email, change.address, change.emprole,
change.username, change.password, change.empid))
connection.commit()
record = cursor.fetchone()
return Employee(record[0], record[1], record[2], record[3], record[4],
record[5], record[6], record[7], record[8], record[9])
def create_client(self, bank):
sql = "INSERT INTO clients VALUES string(DEFAULT,%s,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (bank.firstname, bank.lastname, bank.address))
connection.commit()
record = cursor.fetchone()
print(record)
new_client = Bank(record[0], record[1], record[2])
print(new_client)
return new_client
def update_user(self, change): # Update user Table
sql = "UPDATE users SET first_name = %s, last_name=%s, password=%s WHERE id=%s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change.first_name, change.last_name,
change.password, change.users_id))
connection.commit()
record = cursor.fetchone()
user = Users(record[0], record[1], record[2], record[3], record[4],
record[5])
return user
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 update_acct(acct):
sql = "UPDATE banking.accounts SET acctnum = %s,balance= %s, userid = %s WHERE acctid = %s RETURNING *"
cursor = connection.cursor()
cursor.execute(
sql, (acct.acct_num, acct.balance, acct.userid, acct.acct_id))
connection.commit()
record = cursor.fetchone()
acct1 = Accounts(int(record[0]), int(record[1]), int(record[2]),
int(record[3]))
return acct1
