def transfer_account(date, amount, from_account, to_account):
cur = conn.cursor()
sql = """
INSERT INTO Transfers(transfer_date, amount, from_account, to_account)
VALUES (%s, %s, %s, %s)
"""
cur.execute(sql, (date, amount, from_account, to_account))
conn.commit()
cur.close()
def insert_Customers(name, CPR_number, password):
cur = conn.cursor()
sql = """
INSERT INTO Customers(name, CPR_number, password)
VALUES (%s, %s, %s)
"""
cur.execute(sql, (name, CPR_number, password))
conn.commit()
cur.close()
def select_Employees(id):
cur = conn.cursor()
sql = """
SELECT * FROM Employees
WHERE id = %s
"""
cur.execute(sql, (id, ))
user = Employees(cur.fetchone()) if cur.rowcount > 0 else None
cur.close()
return user
def select_Customers(CPR_number):
cur = conn.cursor()
sql = """
SELECT * FROM Customers
WHERE CPR_number = %s
"""
cur.execute(sql, (CPR_number, ))
user = Customers(cur.fetchone()) if cur.rowcount > 0 else None
cur.close()
return user
def update_CheckingAccount(amount, CPR_number):
cur = conn.cursor()
sql = """
UPDATE CheckingAccount
SET amount = %s
WHERE CPR_number = %s
"""
cur.execute(sql, (amount, CPR_number))
conn.commit()
cur.close()
def select_investments_certificates_sum(CPR_number):
print(CPR_number)
cur = conn.cursor()
sql = """
SELECT account_number, cpr_number, created_date, sum
FROM vw_cd_sum
WHERE cpr_number = %s
"""
cur.execute(sql, (CPR_number, ))
tuple_resultset = cur.fetchall()
cur.close()
return tuple_resultset
def select_investments(CPR_number):
cur = conn.cursor()
sql = """
SELECT i.account_number, a.cpr_number, a.created_date
FROM investmentaccounts i
JOIN accounts a ON i.account_number = a.account_number
WHERE a.cpr_number = %s
"""
cur.execute(sql, (CPR_number, ))
tuple_resultset = cur.fetchall()
cur.close()
return tuple_resultset
def select_investments_with_certificates(CPR_number):
cur = conn.cursor()
sql = """
SELECT i.account_number, a.cpr_number, a.created_date
, cd.cd_number, start_date, maturity_date, rate, amount
FROM investmentaccounts i
JOIN accounts a ON i.account_number = a.account_number
JOIN certificates_of_deposit cd ON i.account_number = cd.account_number
WHERE a.cpr_number = %s
"""
cur.execute(sql, (CPR_number, ))
tuple_resultset = cur.fetchall()
cur.close()
return tuple_resultset
def manageCustomer():
if not current_user.is_authenticated:
flash('Please Login.', 'danger')
return redirect(url_for('Login.login'))
form = TransferForm()
if form.validate_on_submit():
amount = form.amount.data
cur = conn.cursor()
sql = """
UPDATE CheckingAccount
SET amount = %s
WHERE CPR_number = %s
"""
cur.execute(sql, (amount, CPR_number))
conn.commit()
cur.close()
flash('Transfer succeed!', 'success')
return redirect(url_for('Login.home'))
return render_template('transfer.html', title='Transfer', form=form)
def select_emp_cus_accounts(emp_cpr_number):
cur = conn.cursor()
sql = """
SELECT
e.name employee
, c.name customer
, cpr_number
, account_number
FROM manages m
NATURAL JOIN accounts
NATURAL JOIN customers c
JOIN employees e ON m.emp_cpr_number = e.id
WHERE emp_cpr_number = %s
;
"""
cur.execute(sql, (emp_cpr_number, ))
tuple_resultset = cur.fetchall()
cur.close()
return tuple_resultset
def load_user(user_id):
cur = conn.cursor()
schema = 'customers'
id = 'cpr_number'
if str(user_id).startswith('60'):
schema = 'employees'
id = 'id'
user_sql = sql.SQL("""
SELECT * FROM {}
WHERE {} = %s
""").format(sql.Identifier(schema), sql.Identifier(id))
cur.execute(user_sql, (int(user_id), ))
if cur.rowcount > 0:
return Employees(
cur.fetchone()) if schema == 'employees' else Customers(
cur.fetchone())
else:
return None