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 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 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 = %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")
Esempio n. 5
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 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 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 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 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 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
Esempio n. 11
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
Esempio n. 12
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
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 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
Esempio n. 15
0
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 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 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
Esempio n. 18
0
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
Esempio n. 19
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")
Esempio n. 20
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 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 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 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 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
Esempio n. 27
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'
Esempio n. 28
0
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()
Esempio n. 29
0
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()
Esempio n. 30
0
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 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()
Esempio n. 32
0
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, 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])
Esempio n. 34
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
Esempio n. 35
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 = []
Esempio n. 36
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]