def __init__(self, projectid, nx = 10, ny = 10, nper = 1, lay_mode = 0, strt_mode = 0, strs_mode = 0): #mode 0 : properties defined at layer level, mode 1: properties defined at units level
self.id = projectid
self.dictofobjects = {}
cur.execute ("""with objects as (select model_object_id from projects_model_objects where project_id = %s) select distinct discr from model_objects inner join objects on model_objects.id = objects.model_object_id;""",([self.id]))
objects = cur.fetchall()
for theobject in objects:
self.dictofobjects[theobject[0]] = []
cur.execute(""" with objects as (select model_object_id from projects_model_objects where project_id = %s) select id as layerid from model_objects inner join objects on model_objects.id = objects.model_object_id where discr = %s; """, ([self.id, theobject[0]]))
temp = cur.fetchall()
for i in temp:
self.dictofobjects[theobject[0]].append(i[0])
self.nx = nx
self.ny = ny
self.nper = nper
self.lay_mode = lay_mode
self.strt_mode = strt_mode
self.strs_mode = strs_mode
self.perlen = []
self.nstp = []
self.stress_period_list = []
#################### Perlen, Nstp ##################################
if self.strs_mode == 0:
for i in range(self.nper):
self.perlen.append(30)
self.nstp.append(30)
self.stress_period_list.append(i)
else:
for i in range(self.nper):
self.perlen.append(100)
self.nstp.append(100)
self.stress_period_list.append(i)
def __init__(self, id_list):
self.id_list = id_list
self.points = []
self.values_list = []
self.xlist = []
self.ylist = []
p_index = 0
for point in self.id_list:
cur.execute(""" select st_x(geometry) from observation_points where id = %s;""", [point])
x = cur.fetchall()[0][0]
self.points.append([x])
self.xlist.append(x)
cur.execute(""" select st_y(geometry) from observation_points where id = %s;""", [point])
y = cur.fetchall()[0][0]
self.points[p_index].append(y)
self.ylist.append(y)
p_index += 1
val_list = []
for i in range(len(self.id_list)):
val_list.append([])
cur.execute("""with prop_id as (select id from properties where model_object_id = %s) select id from values where property = (select id from prop_id) limit 24;""", [self.id_list[i]])
val_id = cur.fetchall()
for j in val_id:
cur.execute(""" select value from property_time_values where id = %s; """, [j[0]])
val_list[i].append(cur.fetchall()[0][0])
for i in range(len(val_list[0])):
self.values_list.append([])
for j in val_list:
self.values_list[i].append(j[i])
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 __init__(self, area_id, nx, ny):
self.areaid = area_id[0]
self.nx = nx
self.ny = ny
cur.execute(""" SELECT ST_XMIN(geometry) from areas where id = %s; """, ([self.areaid]))
self.xmin = cur.fetchall()[0][0]
cur.execute(""" SELECT ST_XMAX(geometry) from areas where id = %s; """, ([self.areaid]))
self.xmax = cur.fetchall()[0][0]
cur.execute(""" SELECT ST_YMIN(geometry) from areas where id = %s; """, ([self.areaid]))
self.ymin = cur.fetchall()[0][0]
cur.execute(""" SELECT ST_YMAX(geometry) from areas where id = %s; """, ([self.areaid]))
self.ymax = cur.fetchall()[0][0]
self.dx = (self.xmax - self.xmin)/self.nx
self.dy = (self.ymax - self.ymin)/self.ny
def get_all_info_account(acc_no, id, msg):
account = None
sql = None
if msg == "transfer":
sql = "select * from accounts where account_no = :acc_no and account_type != 'fd' and status = 'open'"
cur.execute(sql, {"acc_no": acc_no})
elif msg == "loan":
sql = "select * from accounts where account_no = :acc_no and customer_id = :id and account_type = 'savings' and status = 'open'"
cur.execute(sql, {"id": id, "acc_no": acc_no})
else:
sql = "select * from accounts where account_no = :acc_no and customer_id = :id and account_type != 'fd' and status = 'open'"
cur.execute(sql, {"acc_no": acc_no, "id": id})
res = cur.fetchall()
if len(res) == 0:
return None
account_no = res[0][1]
account_type = res[0][3]
balance = res[0][5]
wd_left = res[0][6]
if account_type == "savings":
account = Savings()
else:
account = Current()
account.set_account_type(account_type)
account.set_balance(balance)
account.set_account_no(account_no)
account.set_withdrawals_left(wd_left)
return account
def issued_books(c_id):
sql = """select a.book_id,a.title,b.status from book a,issuehistory b where a.book_id=b.book_id and b.customer_id= :c_id"""
cur.execute(sql, {"c_id": c_id})
data = cur.fetchall()
for line in data:
print(line[0], line[1], line[2])
con.commit
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 avail_book():
sql = "select * from book where status='Available'"
cur.execute(sql)
data = cur.fetchall()
for line in data:
print(line[0], line[1], line[2], line[3], line[4], line[5])
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 get_report_no_fd_loan():
sql = """select customer_id,first_name,last_name from customers
where customer_id not in (select distinct(customer_id) from accounts_fd) and
customer_id not in (select distinct(customer_id) from accounts_loans)"""
cur.execute(sql)
res = cur.fetchall()
return res
def all_books():
sql = "select * from book"
cur.execute(sql)
data = cur.fetchall()
for line in data:
print(line[0], line[1], line[2], line[3], line[4], line[5])
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 = "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 all_customers():
sql = "select * from customers order by customer_id"
cur.execute(sql)
data = cur.fetchall()
for line in data:
print("ID:", line[0], "FNAME:", line[1], "LNAME:", line[2], "ADDRESS:",
line[3], "PASSWORD: ******")
con.commit
def all_books():
sql = "select * from book"
cur.execute(sql)
data = cur.fetchall()
for line in data:
print("TITLE:", line[0], "AUTHOR:", line[1], "PUBLISHER:", line[2],
"YEAR:", line[3], "BOOK ID:", line[4], "STATUS:", line[5])
con.commit
def issued_books(c_id):
sql = """select a.book_id,a.title,b.status,b.book_date from book a,issuehistory b where a.book_id=b.book_id and b.customer_id= :c_id"""
cur.execute(sql, {"c_id": c_id})
data = cur.fetchall()
for line in data:
print("BOOK ID:", line[0], "TITLE:", line[1], "STATUS:", line[2],
"DATE:", line[3])
con.commit
def set_properties(self):
"""
"""
cur.execute("""SELECT geological_layer_id FROM geological_layers_geological_units WHERE geological_unit_id = %s;""",([self.id]))
self.geological_layer_id = cur.fetchall()[0][0]
for i in self.properties_id: # sets values of the properties to the proprties dictionary
cur.execute("""WITH prop as (SELECT id FROM properties where model_object_id = %s AND property_type_id = %s), id as (select id FROM values where property = (select id from prop)) select value from property_values where id = (select id from id);""",([self.id, i]))
try:
val = cur.fetchall()[0][0]
self.properties[i] = val
except:
self.properties[i] = -9999
cur.execute("""SELECT geological_point_id FROM geological_units where id = %s;""",([self.id]))
self.borehole_id = cur.fetchall()[0][0]
cur.execute("""SELECT top_elevation FROM geological_units where id = %s;""",([self.id]))
self.top = cur.fetchall()[0][0]
cur.execute("""SELECT bottom_elevation FROM geological_units where id = %s;""",([self.id]))
self.bottom = cur.fetchall()[0][0]
cur.execute("""SELECT ST_X(geometry) FROM geological_points WHERE id = %s;""",([self.borehole_id]))
self.x = cur.fetchall()[0][0]
cur.execute("""SELECT ST_Y(geometry) FROM geological_points WHERE id = %s;""",([self.borehole_id]))
self.y = cur.fetchall()[0][0]
def get_loan_fd_report():
sql = """select c.customer_id,c.first_name,c.last_name,sum.loan_amount,sum.amount from
(select al.customer_id,al.loan_amount,af.amount from (select customer_id,sum(loan_amount) as loan_amount from accounts_loans group by customer_id) al,
(select customer_id,sum(amount) as amount from accounts_fd group by customer_id) af
where al.customer_id = af.customer_id) sum,customers c
where sum.customer_id = c.customer_id and sum.loan_amount > sum.amount """
cur.execute(sql)
res = cur.fetchall()
return res
def login_customer(c_id, password):
sql = "select count(*) from customers where customer_id = %s and password = %s"
cur.execute(sql, (c_id, password))
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
def login_admin(id, password):
sql = "select count(*) from admin where admin_id = %s and password = %s"
cur.execute(sql, (id, password))
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
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 login_admin(id, password):
sql = "select count(*) from admin where admin_id = :id and password = :password"
cur.execute(sql, {"id": id, "password": password})
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
def check_customer_exists(id):
sql = "select count(*) from customers where customer_id = %s"
cur.execute(sql, (id, ))
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
def check_book_exists(id):
sql = "select count(*) from book where book_id = :id"
cur.execute(sql, {"id": id})
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
def get_transactions_account(acc_no, date_from, date_to):
sql = """select transaction_date,type,amount,balance from transactions where account_no = :acc_no
and transaction_date between :date_from and :date_to order by transaction_id"""
cur.execute(sql, {
"acc_no": acc_no,
"date_from": date_from,
"date_to": date_to
})
res = cur.fetchall()
return res
def get_all_info_customer(id):
sql = "select * from customers where customer_id = :id"
cur.execute(sql, {"id": id})
res = cur.fetchall()
if len(res) == 0:
return None
customer = Customer()
status = res[0][3]
att = res[0][4]
customer.set_customer_id(id)
return customer
def set_single(self):
"""
Impotrs property values from the database for cases when they are defined for layers - no interpolation is needed, single value for the layer
"""
for i in self.properties_id:
try:
cur.execute("""WITH prop as (SELECT id FROM properties where model_object_id = %s AND model_object_property_type_id = %s) SELECT value FROM property_values INNER JOIN prop ON property_values.id = prop.id;""",([self.id, i]))
val = cur.fetchall()[0][0]
self.single_properties[i] = val
except:
print 'propery '+str(i)+' was not found'
def get_all_info_customer(id):
sql = "select * from customers where customer_id = %s"
cur.execute(sql, (id, ))
res = cur.fetchall()
if len(res) == 0:
return None
customer = Customer()
status = res[0][3]
att = res[0][4]
customer.set_customer_id(id)
customer.set_status(status)
customer.set_login_attempts(att)
return customer
def __init__(self, bo_ids, op, area):
self.area = area
self.bo = bo_ids
self.op = op
self.values_list = op.values_list
self.line = []
for i in self.bo:
cur.execute("""with dump as (select (st_dumppoints(geometry)).* from boundaries where id = %s) select st_x(geom) from dump;""", [i])
xlist = cur.fetchall()
cur.execute("""with dump as (select (st_dumppoints(geometry)).* from boundaries where id = %s) select st_y(geom) from dump;""", [i])
ylist = cur.fetchall()
for i in xlist:
self.line.append(list(i))
for i in range(len(ylist)):
self.line[i] += ylist[i]
self.SPD_multi = {}
#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
self.line_cols, self.line_rows = intersector.line_area_intersect(line = self.line, xmax = self.area.xmax, xmin = self.area.xmin, ymax = self.area.ymax, ymin = self.area.ymin, nx = self.area.nx, ny = self.area.ny)
#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
self.vals = []
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 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 issuebook(c_id):
b_id = input("ENTER BOOK ID TO BE ISSUED\n")
sql = """update book set status='Issued' where book_id= :id"""
cur.execute(sql, {"id": b_id})
con.commit
sql = "Select * from book where book_id= :id"
cur.execute(sql, {"id": b_id})
data = cur.fetchall()
for line in data:
print(line[0], line[1], line[2], line[3], line[4], line[5])
cur.execute("""insert into issuehistory values(:bid,:cid,:stat)""", {
'bid': b_id,
'cid': c_id,
'stat': "Issued"
})
con.commit
print("BOOK ISSUED\n")
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 issuebook(c_id):
b_id = input("ENTER BOOK ID TO BE ISSUED\n")
sql = """update book set status='Issued' where book_id= :id"""
cur.execute(sql, {"id": b_id})
con.commit
sql = "Select * from book where book_id= :id"
cur.execute(sql, {"id": b_id})
data = cur.fetchall()
for line in data:
print("TITLE:", line[0], "AUTHOR:", line[1], "PUBLISHER:", line[2],
"YEAR", line[3], "BOOK ID:", line[4], "STATUS:", line[5])
cur.execute("""insert into issuehistory values(:bid,:cid,:stat,:bdate)""",
{
'bid': b_id,
'cid': c_id,
'stat': "Issued",
'bdate': now
})
con.commit
print("BOOK ISSUED\n")
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 get_fd_report(cus_id):
sql = "select account_no,amount,deposit_term from accounts_fd where customer_id = %s"
cur.execute(sql, (cus_id, ))
res = cur.fetchall()
return res