def show_user_info():
global rows
cur = conn.cursor()
cur.execute("select fullname, phone_number from systemUser where account_id = "+str(account_id))
info = cur.fetchone()
# info = [fullname, phone_number]
if role == 1 or role == 0:
cur.execute("select balance from account where account_id = " + str(account_id))
balance = cur.fetchone()[0]
if role == 1:
rows = show_account()
all_account = [{"account_id": r[0], "balance": r[1],
"account_status": r[2], "role": r[3]} for r in rows]
# role_name = "Bank clerk"
return render_template('manager.html', logStatus=0, card_holder=info[0],
card_number=account_id, balance=balance, account=all_account)
elif role == 0:
rows = show_account(account_id)
# role_name = "Customer" (ctm)
ctm_account = [{"account_id": r[0], "balance": r[1],
"account_status": r[2], "role": r[3]} for r in rows]
return render_template('index.html', logStatus=0, card_holder=info[0],
card_number=account_id, balance=balance, account=ctm_account)
return render_template('index.html', logStatus=1)
def login():
global account_id, role
username = request.form.get('username')
password = request.form.get('login_pass')
cur = conn.cursor()
cur.execute("select password_hash, account_id from loginInfo where login_name = "+"\'"+username+"\';")
record = cur.fetchone()
""" record = [password_hash, acc_login]"""
if not check_password_hash(record[0], password):
return render_template('warning.html', message="Invalid user name or password!")
else:
cur.execute("select * from account where account_id = "+str(record[1]))
rows = cur.fetchone()
""" rows = [account_id, balance, acc_status, role] """
if not rows[2]:
""" <==> if rows[2] == false"""
return render_template('warning.html', message="Your account is being LOCKED !")
else:
account_id = record[1]
if rows[3] == 0:
""" customer """
role = 0
return redirect(url_for('show_user_info'))
else:
""" bank clerk """
role = 1
return redirect(url_for('show_user_info'))
def send_money():
send_money_form = SendMoneyForm()
if send_money_form.is_submitted():
global receiver_id, money_amt
money_amt = send_money_form.money_amt.data
receiver_id = send_money_form.receiver_account.data
message = send_money_form.message.data
cur = conn.cursor()
cur.execute("select balance from account natural join systemUser where account_id = "+str(account_id))
balance_check = cur.fetchone()[0]
if balance_check < money_amt:
return render_template('warning.html',
message="Money amount which will be sent is more than BALANCE!")
else:
cur.execute("select fullname from systemUser where account_id = "+str(receiver_id))
receiver_name = cur.fetchone()[0]
if len(receiver_name) == 0:
return render_template('warning.html',
message="No suitable receiver existed!")
else:
cur.execute("select fullname from systemUser where account_id = "+str(account_id))
sender_name = cur.fetchone()[0]
return render_template('send_confirm.html', receiver_id=receiver_id, receiver_name=receiver_name,
money_amt=money_amt, fullname=sender_name, sender_id=account_id, message=message, role=role)
return render_template('send_money.html', form=send_money_form, role=role)
def delete(id_):
cur = conn.cursor()
try:
cur.callproc('public.location_delete', [id_])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def update(id_, name, description):
cur = conn.cursor()
try:
cur.callproc('public.location_update', [id_, name, description])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def insert(measurementtype):
cur = conn.cursor()
try:
cur.callproc('public.measurement_type_insert', [measurementtype.name, measurementtype.description])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def insert(devicetype):
cur = conn.cursor()
try:
cur.callproc('public.device_type_insert',
[devicetype.name, devicetype.description])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def insert(location):
cur = conn.cursor()
try:
cur.callproc('public.location_insert',
[location.name, location.description])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def insert(device):
cur = conn.cursor()
try:
cur.callproc('public.reporting_device_insert',
[device.name, device.description, device.lastipaddress, device.device_type_id,
device.location_id])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def insert(measurement):
cur = conn.cursor()
try:
cur.callproc('public.measurement_insert', [
measurement.measurement_type_id,
measurement.reporting_device_id, measurement.location_id,
measurement.measured_value, measurement.measured_date
])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def show_account(account_id=None):
cur = conn.cursor()
sql = "select * from account "
if account_id:
sql += "where account_id = " + str(account_id)
sql += "order by account_id"
cur.execute(sql)
rows = cur.fetchall()
conn.commit()
cur.close()
# conn.close()
return rows
def lock_confirm():
cur = conn.cursor()
if role == 1:
""" bank clerk """
acc_lock = request.form.get('acc_lock')
cur.execute("update account set acc_status = false where account_id = "+str(acc_lock))
return redirect(url_for('show_user_info'))
elif role == 0:
""" customer """
cur.execute("update account set acc_status = false where account_id = "+str(account_id))
return redirect(url_for('logout'))
def lock_account():
cur = conn.cursor()
global acc_lock
if role == 1:
"""bank clerk """
return render_template('acc_lock_by_clerk.html')
elif role == 0:
""" customer """
cur.execute("select fullname from systemUser where account_id = " + str(account_id))
ctm_name = cur.fetchone()[0]
return render_template('acc_lock_by_ctm.html', account_id=account_id, ctm_name=ctm_name)
def get(id):
cur = conn.cursor()
try:
cur.callproc('public.device_type_get', [id])
except psycopg2.Error as e:
print(e)
row = cur.fetchall()
cur.close()
if row:
return DeviceTypeModel(row[0], row[1], row[2])
else:
return None
def personal_info():
psn_in4_form = RegisterPersonalInfoForm()
if psn_in4_form.is_submitted():
global fullname
fullname = psn_in4_form.fullname.data
address = psn_in4_form.address.data
phone_number = psn_in4_form.phone_number.data
cur = conn.cursor()
cur.execute("insert into systemUser(fullname,address,phone_number,account_id) values"
"(\'{0}\',\'{1}\',\'{2}\',{3})".format(fullname, address, phone_number, created_id))
conn.commit()
return render_template('successful.html', fullname=fullname, phone_number=phone_number, account_id=created_id)
return render_template('personal_info.html', form=psn_in4_form)
def get(id):
cur = conn.cursor()
try:
cur.callproc('public.measurement_get', [id])
except psycopg2.Error as e:
print(e)
row = cur.fetchall()
cur.close()
if row:
return MeasurementModel(row[0], row[1], row[2], row[3], row[4],
row[5])
else:
return None
def get_last_measured_ipaddress():
cur = conn.cursor()
try:
cur.execute("SELECT * FROM get_last_ipaddress_measurement_view")
# cur.callproc('public.get_last_ipaddress_measurement')
except psycopg2.Error as e:
print(e)
row = cur.fetchone()
cur.close()
if row:
return row
else:
return None
def get(id):
cur = conn.cursor()
# cur.execute("SELECT * FROM users WHERE ssn='%s'" % ssn)
try:
cur.callproc('public.location_get', [id])
except psycopg2.Error as e:
print(e)
row = cur.fetchall()
cur.close()
if row:
return LocationModel(row[0], row[1], row[2])
else:
return None
def show_transaction_history(account_id=None):
cur = conn.cursor()
sql = "select t.tran_id, t.tran_date, " \
"sa.account_id, ssu.fullname, t.money_amt, ra.account_id, rsu.fullname " \
"from transaction t " \
"inner join account ra on ra.account_id = t.receiver_id " \
"inner join systemUser rsu on rsu.account_id = ra.account_id " \
"inner join account sa on sa.account_id = t.sender_id " \
"inner join systemUser ssu on ssu.account_id = sa.account_id "
if account_id:
sql += "where t.sender_id = " + str(account_id) + " or t.receiver_id = " + str(account_id)
cur.execute(sql)
rows = cur.fetchall()
conn.commit()
return rows
def get_user(ssn):
cur = conn.cursor()
# cur.execute("SELECT * FROM users WHERE ssn='%s'" % ssn)
try:
cur.callproc('public.user_get', [ssn])
except psycopg2.Error as e:
print(e)
row = cur.fetchone()
cur.close()
if row:
return UserModel(row[0], row[1], row[2], row[3], row[4], row[5])
else:
return None
def confirm_send_money():
cur = conn.cursor()
cur.execute("update account set balance = balance + "+str(money_amt)
+ " where account_id = "+str(receiver_id))
conn.commit()
cur.execute("update account set balance = balance - "+str(money_amt)
+ " where account_id = "+str(account_id))
conn.commit()
moment = datetime.datetime.now()
cur.execute("insert into transaction(money_amt,sender_id,receiver_id,tran_date)"
"values(" + str(money_amt) + "," + str(account_id) + "," + str(receiver_id) +
",\'"+moment.strftime('%Y-%m-%d %X')+"\')")
conn.commit()
return render_template('sent_successful.html', role=role)
def get_all():
cur = conn.cursor()
try:
cur.callproc('public.location_get_all')
except psycopg2.Error as e:
print(e)
rows = cur.fetchall()
cur.close()
if rows:
ret = []
for row in rows:
ret.append(LocationModel(row[1], row[2], row[0]))
return ret
else:
return None
def get_data_by_device(device_id):
cur = conn.cursor()
try:
cur.callproc('public.measurement_get_data_by_device', [device_id])
except psycopg2.Error as e:
print(e)
rows = cur.fetchall()
cur.close()
if rows:
ret = []
for row in rows:
ret.append(
[int(time.mktime(row[0].timetuple())) * 1000, row[1]])
return ret
else:
return None
def insert(user):
cur = conn.cursor()
try:
"""
cur.execute(
"INSERT INTO USERS (ssn, first_name, last_name, user_name, password, description) \
VALUES ('%s', '%s', '%s', '%s', '%s', '%s')" %
(user.ssn, user.firstname, user.lastname, user.username, user.password, user.description))
"""
cur.callproc('public.user_insert', [
user.ssn, user.firstname, user.lastname, user.username,
user.password, user.description
])
except psycopg2.Error as e:
print(e)
conn.commit()
cur.close()
def get_all():
cur = conn.cursor()
# cur.execute("SELECT * FROM users WHERE ssn='%s'" % ssn)
try:
cur.callproc('public.reporting_device_get_all')
except psycopg2.Error as e:
print(e)
rows = cur.fetchall()
cur.close()
if rows:
ret = []
for row in rows:
ret.append(ReportingDeviceModel(row[0], row[1], row[2], row[3], row[4], row[5]))
return ret
else:
return None
def get_all():
cur = conn.cursor()
try:
cur.callproc('public.measurement_get_all')
except psycopg2.Error as e:
print(e)
rows = cur.fetchall()
cur.close()
if rows:
ret = []
for row in rows:
ret.append(
MeasurementModel(row[0], row[1], row[2], row[3], row[4],
row[5]))
return ret
else:
return None
def show_trans_history():
cur = conn.cursor()
if role == 1:
""" bank clerk """
all_rows = show_transaction_history()
all_trans = [{"tran_id": x[0], "tran_date": x[1], "sender_id": x[2], "sender_name": x[3],
"money_amt": x[4], "receiver_id": x[5], "receiver_name": x[6]} for x in all_rows]
return render_template('show_transaction.html', transaction=all_trans, role=1,
account_id=account_id, cus_name="")
elif role == 0:
""" customer """
cus_rows = show_transaction_history(account_id)
cus_trans = [{"tran_id": x[0], "tran_date": x[1], "sender_id": x[2], "sender_name": x[3],
"money_amt": x[4], "receiver_id": x[5], "receiver_name": x[6]} for x in cus_rows]
cur.execute("select fullname from systemUser where account_id = " + str(account_id))
cus_name = cur.fetchone()[0]
return render_template('show_transaction.html', transaction=cus_trans, account_id=account_id,
cus_name=cus_name, role=0)
def register():
global created_id
if role == 0:
""" this is a customer """
return render_template('warning.html',
message="Customer do not have the right to register another account !!")
elif role == 1:
""" this is a bank clerk """
rgt_acc_form = RegisterAccountForm()
cur = conn.cursor()
login_name = request.form.get('login_name')
if rgt_acc_form.is_submitted():
cur.execute("select * from loginInfo where login_name = '"+str(login_name)+"\';")
is_exist = cur.fetchall()
if len(is_exist) != 0:
return render_template('warning.html',
message="This username already existed !!!")
else:
balance = request.form.get('balance')
password_hash = generate_password_hash(request.form.get('register_pass'))
if request.form.get('role') == "Bank clerk":
created_role = 1
else:
created_role = 0
cur.execute("insert into account(account_id,balance,acc_status,role) values "
"(nextval('acc_sequence'),{0},true,{1});".format(balance, created_role))
conn.commit()
cur.execute("select account_id from account order by account_id DESC limit 1;")
created_id = cur.fetchone()[0]
""" created_id = cur.fetchone --> wrong! """
cur.execute("insert into loginInfo values"
"(\'{0}\',\'{1}\',{2})".format(login_name, password_hash, created_id))
conn.commit()
return redirect(url_for('personal_info'))
def unlock_confirm():
if role == 1:
cur = conn.cursor()
acc_unlock = request.form.get('acc_unlock')
cur.execute("update account set acc_status = true where account_id = "+str(acc_unlock))
return redirect(url_for('show_user_info'))
def createdb():
"""Creates the db tables."""
from app.db import conn
try:
cur = conn.cursor()
# user
cur.execute("""
CREATE TABLE users (
ssn BIGINT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
user_name VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL)""")
# admin
cur.execute("""
CREATE TABLE admins (
SSN BIGINT NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
user_name VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
description VARCHAR(255),
PRIMARY KEY(SSN)
)""")
# location
cur.execute("""
CREATE TABLE location (
id SERIAL NOT NULL,
name VARCHAR(40) NOT NULL,
description VARCHAR(100),
PRIMARY KEY(id)
)""")
# measurement_type
cur.execute("""
CREATE TABLE measurement_type (
id SERIAL NOT NULL,
name VARCHAR(40) NOT NULL,
description VARCHAR(100),
PRIMARY KEY(id)
)""")
# device_type
cur.execute("""
CREATE TABLE device_type (
id SERIAL NOT NULL,
name VARCHAR(40) NOT NULL,
description VARCHAR(100),
PRIMARY KEY(id)
)""")
# reporting_device
cur.execute("""
CREATE TABLE reporting_device (
id SERIAL NOT NULL,
name VARCHAR(40) NOT NULL,
description VARCHAR(100),
lastIpaddress VARCHAR(50),
device_type_id INT NOT NULL,
location_id INT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(device_type_id) REFERENCES device_type(id),
FOREIGN KEY(location_id) REFERENCES location(id)
)""")
# measurrment
cur.execute("""
CREATE TABLE measurement (
id SERIAL NOT NULL,
measurement_type_id INT NOT NULL,
reporting_device_id INT NOT NULL,
location_id INT NOT NULL,
measured_value FLOAT,
measured_date TIMESTAMP,
PRIMARY KEY(id),
FOREIGN KEY(measurement_type_id) REFERENCES measurement_type(id),
FOREIGN KEY(reporting_device_id) REFERENCES reporting_device(id),
FOREIGN KEY(location_id) REFERENCES location(id)
)""")
# location
cur.execute("""
CREATE TABLE user_create_log (
id SERIAL NOT NULL ,
ssn BIGINT,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id)
)""")
conn.commit()
cur.close()
conn.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
