def check_history():
sql = """select * from issuehistory"""
cur.execute(sql)
data = cur.fetchall()
for line in data:
print("Book ID:", line[0], "Customer ID:", line[1], "STATUS:", line[2])
con.commit()
def change_address_customer(ch, id, addr):
if ch == 1:
sql = "update address set line1 = :line1 where customer_id = :id"
cur.execute(sql, {"line1": addr, "id": id})
elif ch == 2:
sql = "update address set line2 = :line2 where customer_id = :id"
cur.execute(sql, {"line2": addr, "id": id})
elif ch == 3:
sql = "update address set state = :state where customer_id = :id"
cur.execute(sql, {"state": addr, "id": id})
elif ch == 4:
sql = "update address set city = :city where customer_id = :id"
cur.execute(sql, {"city": addr, "id": id})
elif ch == 5:
sql = "update address set pincode = :pincode where customer_id = :id"
cur.execute(sql, {"pincode": addr, "id": id})
else:
return
con.commit()
print("Details Updated Successfully")
def update_customer(customer):
id = customer.get_customer_id()
status = customer.get_status()
att = customer.get_login_attempts()
sql = "update customers set status = :status,login_attempts = :att where customer_id = :id"
cur.execute(sql, {"status": status, "att": att, "id": id})
con.commit()
def update_customer(customer):
id = customer.get_customer_id()
status = customer.get_status()
att = customer.get_login_attempts()
sql = "update customers set status = %s,login_attempts = %s where customer_id = %s"
cur.execute(sql, (status, att, id))
con.commit()
def open_new_account_customer(account, cus_id):
withdrawals_left = None
account_type = account.get_account_type()
bal = account.get_balance()
opened_on = datetime.datetime.now().strftime("%Y-%m-%d")
status = AccountStatus.open
if account_type == AccountType.savings:
withdrawals_left = 10
sql = "select date_add(CURRENT_DATE(), INTERVAL 1 MONTH) from dual"
cur.execute(sql)
res = cur.fetchall()
next_date = res[0][0].strftime("%Y-%m-%d")
sql = "insert into accounts(customer_id, opened_on, account_type, status, balance, withdrawals_left, next_reset_date) " \
"values(%s, %s, %s, %s, %s, %s, %s);"
data = (cus_id, opened_on, account_type.value, status.value, bal,
withdrawals_left, next_date)
cur.execute(sql, data)
acc_no = int(cur.lastrowid)
if account_type.value == "fd":
term = account.get_deposit_term()
sql = "insert into fd values (%s,%s,%s)"
data = (acc_no, bal, term)
cur.execute(sql, data)
con.commit()
print("Account Opened Successfully")
print("Account No is : ", acc_no)
def change_address_customer(ch, _id, addr):
if ch == 1:
sql = "update address set line1 = %s where customer_id = %s"
cur.execute(sql, (addr, _id))
elif ch == 2:
sql = "update address set line2 = %s where customer_id = %s"
cur.execute(sql, (addr, _id))
elif ch == 3:
sql = "update address set state = %s where customer_id = %s"
cur.execute(sql, (addr, _id))
elif ch == 4:
sql = "update address set city = %s where customer_id = %s"
cur.execute(sql, (addr, _id))
elif ch == 5:
sql = "update address set pincode = %s where customer_id = %s"
cur.execute(sql, (addr, _id))
else:
return
con.commit()
print("Details Updated Successfully")
def open_new_account_customer(account,cus_id):
withdrawals_left = None
account_type = account.get_account_type()
bal = account.get_balance()
opened_on = datetime.datetime.now().strftime("%d-%b-%Y")
status = "open"
sql = "select account_no_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
acc_no = res[0][0]
if account_type == "savings":
withdrawals_left = 10
sql = "select add_months(sysdate,1) from dual"
cur.execute(sql)
res = cur.fetchall()
next_date = res[0][0].strftime("%d-%b-%Y")
sql = "insert into accounts values(:cus_id,:acc_no,:opened_on,:acc_type,:status,:bal,:wd,:next_date)"
cur.execute(sql , {"cus_id":cus_id, "acc_no":acc_no, "opened_on":opened_on, "acc_type":account_type, "status":status, "bal":bal, "wd":withdrawals_left, "next_date":next_date})
if account_type == "fd":
term = account.get_deposit_term()
sql = "insert into fd values (:acc_no,:amount,:term)"
cur.execute(sql, {"acc_no":acc_no, "term":term, "amount":bal})
con.commit()
print("Account Opened Successfully!!")
print("Account No is : ",acc_no)
print("\nNOTE: Keep this ID and Dont share it with anyone!")
input("Press any key to continue ...")
system('CLS')
print("\n##### Welcome To ONLINE BANKING TERMINAL #####\n")
def money_withdraw_customer(account, amount, msg):
acc_type = account.get_account_type()
wd_left = account.get_withdrawals_left()
bal = account.get_balance()
acc_no = account.get_account_no()
type = "debit"
sql = "update accounts set balance = :bal where account_no = :acc_no"
cur.execute(sql, {"bal": bal, "acc_no": acc_no})
sql = "select transaction_id_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
t_id = res[0][0]
sql = "insert into transactions values (:t_id,:acc_no,:type,:amount,:bal,:date_on)"
date = datetime.datetime.now().strftime("%d-%b-%Y")
cur.execute(
sql, {
"t_id": t_id,
"acc_no": acc_no,
"type": type,
"amount": amount,
"bal": bal,
"date_on": date
})
if acc_type == "savings" and msg != "transfer":
wd_left -= 1
sql = "update accounts set withdrawals_left = :wd_left where account_no = :acc_no"
cur.execute(sql, {"wd_left": wd_left, "acc_no": acc_no})
con.commit()
def make_all_tables3():
sql = """create table issuehistory(
book_id varchar2(5),
customer_id number(5),
status varchar2(15) check (status in('Issued','Returned')),
)"""
cur.execute(sql)
con.commit()
def get_loan_customer(acc_no,loan_amt,loan_term):
sql = "select loan_id_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
loan_id = res[0][0]
sql = "insert into loans values (:acc_no,:loan_id,:amount,:loan_term)"
cur.execute(sql , {"acc_no":acc_no, "loan_id":loan_id, "loan_term":loan_term, "amount":loan_amt})
con.commit()
print("Loan Availed Successfully")
def make_all_tables2():
sql = """create table admin(
admin_id number(5),
password varchar2(10))"""
cur.execute(sql)
con.commit()
sql = "insert into admin values(227,'helloadmin')"
cur.execute(sql)
con.commit()
def make_all_tables():
sql = """create table customers(
customer_id number(5) primary key,
first_name varchar2(10),
last_name varchar2(10),
address varchar2(40),
password varchar2(20))"""
cur.execute(sql)
con.commit()
def make_all_tables1():
sql = """create table book(
title varchar2(15),
author varchar2(30),
publisher varchar2(30),
pub_year number(4),
book_id varchar2(30) primary key,
status varchar2(10) CHECK (status in('Issued','Due','Available')))"""
cur.execute(sql)
con.commit()
def money_deposit_customer(account, amount):
bal = account.get_balance()
acc_no = account.get_account_no()
type = TransactionType.credit
sql = "update accounts set balance = %s where account_no = %s"
cur.execute(sql, (bal, acc_no))
sql = "insert into transactions(account_no, type, amount, balance, transaction_date) values (%s, %s, %s, %s, %s);"
date = datetime.datetime.now().strftime("%Y-%m-%d")
data = (acc_no, type.value, amount, bal, date)
cur.execute(sql, data)
con.commit()
def close_account_customer(account):
acc_no = account.get_account_no()
balance = account.get_balance()
sql = "update accounts set status='closed',balance = 0 where account_no = :acc_no"
cur.execute(sql, {"acc_no": acc_no})
closed_on = datetime.datetime.now().strftime("%d-%b-%Y")
sql = "insert into closed_accounts values(:acc_no,:closed_on)"
cur.execute(sql, {"acc_no": acc_no, "closed_on": closed_on})
print("Account Closed Successfully !")
print("Rs ", balance, " will be delivered to your address shortly")
con.commit()
def close_account_customer(account):
acc_no = account.get_account_no()
balance = account.get_balance()
sql = "update accounts set status='closed',balance = 0 where account_no = %s"
cur.execute(sql, (acc_no, ))
closed_on = datetime.datetime.now().strftime("%Y-%m-%d")
sql = "insert into closed_accounts values(%s, %s)"
cur.execute(sql, (acc_no, closed_on))
print("Account Closed Successfully !")
print("Rs ", balance, " will be delivered to your address shortly")
con.commit()
def add_book(book):
title = book.get_title()
author = book.get_author()
publish=book.get_publish()
pub_year = book.get_pub_year()
b_id=book.get_b_id()
status=book.get_status()
sql = "insert into book values(:title,:author,:publish,:pub_year,:id,:status)"
cur.execute(sql, {"title":title, "author":author, "publish":publish , "pub_year":pub_year, "id":b_id, "status":status})
con.commit()
print("Book Added")
def money_deposit_customer(account,amount):
bal = account.get_balance()
acc_no = account.get_account_no()
type = "credit"
sql = "update accounts set balance = :bal where account_no = :acc_no"
cur.execute(sql , {"bal":bal, "acc_no":acc_no})
sql = "select transaction_id_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
t_id = res[0][0]
sql = "insert into transactions values (:t_id,:acc_no,:type,:amount,:bal,:date_on)"
date = datetime.datetime.now().strftime("%d-%b-%Y")
cur.execute(sql , {"t_id":t_id, "acc_no":acc_no, "type":type , "amount":amount , "bal":bal, "date_on":date})
con.commit()
def money_withdraw_customer(account, amount, msg):
acc_type = account.get_account_type()
wd_left = account.get_withdrawals_left()
bal = account.get_balance()
acc_no = account.get_account_no()
type = TransactionType.debit
sql = "update accounts set balance = %s where account_no = %s"
cur.execute(sql, (bal, acc_no))
sql = "insert into transactions(account_no, type, amount, balance, transaction_date) values (%s, %s, %s, %s, %s);"
date = datetime.datetime.now().strftime("%Y-%m-%d")
data = (acc_no, type.value, amount, bal, date)
cur.execute(sql, data)
# TODO: add .value to account type in production
if acc_type == AccountType.savings and msg != "transfer":
wd_left -= 1
sql = "update accounts set withdrawals_left = %s where account_no = %s"
cur.execute(sql, (wd_left, acc_no))
con.commit()
def make_tables():
sql = " SELECT COUNT(*) FROM CUSTOMERS"
cur.execute(sql)
result = cur.fetchall()
if result[0][0] !=0:
return
sql = """CREATE TABLE CUSTOMERS(
CUSTOMER_ID INT NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(10) NOT NULL,
LAST_NAME VARCHAR(10) NOT NULL,
STATUS VARCHAR(10),
PASSWORD VARCHAR(10)
PRIMARY KEY (CUSTOMER_ID));"""
cur.execute(sql)
sql = """"CREATE TABLE ADDRESS(
CUSTOMER_ID INT NOT NUL AUTO_INCREMENT,
LINE VARCHAR(30),
CITY VARCHAR(30),
COUNTRY VARCHAR(30),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID));"""
cur.execute(sql)
sql = """"CREATE TABLE ACCOUNTS(
CUSTOMER_ID INT NOT NULL AUTO_INCREMENT,
ACCOUNT_NO INT NOT NULL AUTO_INCREMENT,
OPENED_ON TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ACCOUNT_TYPE VARCAHR(10),
STATUS VARCHAR(20),
BALANCE INT,
FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID).
PRIMARY KEY (ACCOUNT_NO));"""
cur.execute(sql)
sql = """"CREATE TABLE TRANSACTIONS(
TRANSACTION_ID INT NOT NULL AUTO_INCREMENT,
ACCOUNT_NO INT,
AMMOUNT INT,
BALANCE INT,
FOREIGN KEY(ACCOUNT_NO) REFERENCES ACCOUNTS(ACCOUNT_NO));"""
cur.execute(sql)
con.commit()
def sign_up_customer(customer):
fname = customer.get_first_name()
lname = customer.get_last_name()
address = customer.get_address()
password = customer.get_password()
sql = "select customer_id_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
c_id = res[0][0]
sql = "insert into customers values(:id,:fname,:lname,:address,:password)"
cur.execute(
sql, {
"id": c_id,
"fname": fname,
"lname": lname,
"password": password,
"address": address
})
con.commit()
print("Congratulations ! Your Account was Created Successfully")
print("Your Customer ID : ", c_id)
def sign_up_customer(customer):
fname = customer.get_first_name()
lname = customer.get_last_name()
password = customer.get_password()
sql = "select customer_id_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
id = res[0][0]
status = customer.get_status()
att = customer.get_login_attempts()
sql = "insert into customers values(:id,:fname,:lname,:status,:att,:password)"
cur.execute(sql, {"id":id, "fname":fname, "lname":lname , "password":password, "status":status, "att":att})
line1 = customer.get_addr_line1()
line2 = customer.get_addr_line2()
city = customer.get_addr_city()
state = customer.get_addr_state()
pincode = customer.get_addr_pincode()
sql = "insert into address values(:id,:line1,:line2,:city,:state,:pincode)"
cur.execute(sql, {"id":id, "line1":line1, "line2":line2, "city":city, "state":state, "pincode":pincode} )
con.commit()
print("Congratulations ! Your Account was Created Successfully")
print("Your Customer ID : ",id)
def sign_up_customer(customer):
fname = customer.get_first_name()
lname = customer.get_last_name()
password = customer.get_password()
status = customer.get_status()
att = customer.get_login_attempts()
sql = "insert into customers(first_name, last_name, status, login_attempts, password) " \
"values(%s,%s,%s,%s,%s)"
data = (fname, lname, status, att, password)
cur.execute(sql, data)
customer_id = int(cur.lastrowid)
line1 = customer.get_addr_line1()
line2 = customer.get_addr_line2()
city = customer.get_addr_city()
state = customer.get_addr_state()
pincode = customer.get_addr_pincode()
sql = "insert into address values(%s,%s,%s,%s,%s,%s)"
data = (customer_id, line1, line2, city, state, pincode)
cur.execute(sql, data)
con.commit()
print("Congratulations ! Your Account was Created Successfully")
print("Your Customer ID : ", customer_id)
def open_new_account_customer(account, cus_id):
withdrawals_left = None
account_type = account.get_account_type()
bal = account.get_balance()
opened_on = datetime.datetime.now().strftime("%d-%b-%Y")
status = "open"
sql = "select account_no_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
acc_no = res[0][0]
if account_type == "savings":
withdrawals_left = 10
sql = "select add_months(sysdate,1) from dual"
cur.execute(sql)
res = cur.fetchall()
next_date = res[0][0].strftime("%d-%b-%Y")
sql = "insert into accounts values(:cus_id,:acc_no,:opened_on,:acc_type,:status,:bal,:wd,:next_date)"
cur.execute(
sql, {
"cus_id": cus_id,
"acc_no": acc_no,
"opened_on": opened_on,
"acc_type": account_type,
"status": status,
"bal": bal,
"wd": withdrawals_left,
"next_date": next_date
})
if account_type == "fd":
term = account.get_deposit_term()
sql = "insert into fd values (:acc_no,:amount,:term)"
cur.execute(sql, {"acc_no": acc_no, "term": term, "amount": bal})
con.commit()
print("Account Opened Successfully")
print("Account No is : ", acc_no)
def change_password(id, addr):
sql = "update customers set password = :pass where customer_id = :id"
cur.execute(sql, {"pass": addr, "id": id})
con.commit()
print("Password Updated Successfully")
def make_all_tables():
sql = "select count(*) from user_tables where table_name = 'CUSTOMERS'"
cur.execute(sql)
res = cur.fetchall()
if res[0][0] != 0:
return
sql = """create table customers(
customer_id number(5) primary key,
first_name varchar2(10),
last_name varchar2(10),
status varchar2(10),
login_attempts number(3),
password varchar2(20))"""
cur.execute(sql)
sql = """create table address(
customer_id number(5),
line1 varchar2(30),
line2 varchar2(30),
city varchar2(30),
state varchar2(30),
pincode number(6),
constraint fk_addr foreign key(customer_id) references customers(customer_id))"""
cur.execute(sql)
sql = """create table accounts(
customer_id number(5),
account_no number(5) primary key,
opened_on date,
account_type varchar2(10),
status varchar2(10),
balance number(8),
withdrawals_left number(3),
next_reset_date date,
constraint fk_acc foreign key(customer_id) references customers(customer_id))"""
cur.execute(sql)
sql = """create table fd(
account_no number(5) primary key,
amount number(8),
deposit_term number(5),
constraint fk_fd_acc foreign key(account_no) references accounts(account_no))"""
cur.execute(sql)
sql = """create table loans(
customer_account_no number(5),
loan_id number(5) primary key,
loan_amount number(8),
repay_term number(5),
constraint fk_loan_acc foreign key(customer_account_no) references accounts(account_no))"""
cur.execute(sql)
sql = """create table transactions(
transaction_id number(5) primary key,
account_no number(5),
type varchar2(10),
amount number(8),
balance number(8),
transaction_date date,
constraint fk_transaction_account_no foreign key(account_no) references accounts(account_no))"""
cur.execute(sql)
sql = """create table admin(
admin_id number(5),
password varchar2(10))"""
cur.execute(sql)
sql = """create table closed_accounts(
account_no number(5),
closed_on date,
constraint fk_closed_acc foreign key(account_no) references accounts(account_no))"""
cur.execute(sql)
sql = """create or replace view accounts_fd as
select a.customer_id,a.account_no,fd.amount,fd.deposit_term from accounts a,fd where a.account_no = fd.account_no"""
cur.execute(sql)
sql = """create or replace view accounts_loans as
select a.customer_id,a.account_no,loans.loan_id,loans.loan_amount,loans.repay_term from accounts a,loans
where a.account_no = loans.customer_account_no"""
cur.execute(sql)
sql = """create sequence customer_id_sequence
start with 1
increment by 1
nocycle"""
cur.execute(sql)
sql = """create sequence account_no_sequence
start with 1
increment by 1
nocycle"""
cur.execute(sql)
sql = """create sequence transaction_id_sequence
start with 1
increment by 1
nocycle"""
cur.execute(sql)
sql = """create sequence loan_id_sequence
start with 1
increment by 1
nocycle"""
cur.execute(sql)
sql = "insert into admin values(227,'helloadmin')"
cur.execute(sql)
con.commit()
def remove_book(book):
b_id=book.get_b_id()
sql="delete from book where book_id= :id"
cur.execute(sql,{"id":b_id})
con.commit()
print("Book Removed")
def reset_login_attempts(id):
sql = "update customers set login_attempts = 3 where customer_id = :id"
cur.execute(sql, {"id": id})
con.commit()
def reset_withdrawals():
sql = """update accounts set withdrawals_left = 10,next_reset_date = add_months(next_reset_date,1)
where account_type = 'savings' and sysdate >= next_reset_date"""
cur.execute(sql)
con.commit()
def remove_Customer(c_id):
sql="delete from customers where customer_id= :id"
cur.execute(sql,{"id":c_id})
con.commit()
print("Customer Removed")
