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()