示例#1
0
 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])
示例#3
0
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")
示例#4
0
 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
示例#14
0
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
示例#15
0
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
示例#16
0
    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
示例#20
0
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
示例#22
0
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
示例#26
0
 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
示例#28
0
 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 = []
示例#29
0
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")
示例#32
0
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()
示例#33
0
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)
示例#35
0
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)
示例#36
0
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