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 delete(id_):
cur = conn.cursor()
try:
cur.callproc('public.location_delete', [id_])
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(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(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 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 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 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 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 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 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()