def edit(local): # id = request.args.get('local') type = search(local) print("LOCAL::::",local) if request.method == 'POST': option(request.form, local, "edit_id") return redirect('/' + local) if type=="sellers": # print("id:",id) head = ["id","pwd"] sql = "SELECT * FROM sellers WHERE local=\'{}\'".format(local) info = list(sqlQuery_(sql)[0]) info = [info[3],info[1],info[5]] print("ready for edit:",info) return render_template("edit_p.html",info=info,head=head) elif type=="deliveries": return render_template("edit_d.html") elif type=="customers": head = ["id", "pwd"] sql = "SELECT * FROM customers WHERE local=\'{}\'".format(local) info = list(sqlQuery_(sql)[0]) info = [info[3],info[1],info[5]] # [local, name, pwd] print("ready for edit:",info) return render_template("edit_p.html",info=info,head=head) else: print("error 02.") return render_template("/"+local+".html")
def edit(sid): # phoneNum = request.args.get('phone-num') # print("phone number:",phoneNum) c_sid = request.args.get('c_sid') head = ["sid","phone","email","position"] if sid.startswith("admin"): if c_sid==None: # if phoneNum==None: return render_template("add.html", head=head[0:3], owner="admin") # sql = f"SELECT sid,phone,email FROM contacts WHERE phone=\'{phoneNum}\';" sql = f"SELECT sid,phone,email FROM contacts WHERE sid=\'{c_sid}\';" line = sqlQuery_(sql) line = list(line[0]) return render_template("s_edit.html", con_data=line, head=head[0:3], sid=sid) # students else: if c_sid==None: # if phoneNum==None: return render_template("add.html", head=head, owner=sid) print("sid:",sid) if sid.startswith("2009003125"): contacts_name = "grass_corp" elif sid.startswith("2013004394"): contacts_name = "fire_corp" elif sid.startswith("2014005004"): contacts_name = "water_corp" else: contacts_name = None # sql = f"SELECT sid,phone,email,position FROM {contacts_name} WHERE phone=\'{phoneNum}\';" sql = f"SELECT sid,phone,email,position FROM {contacts_name} WHERE sid=\'{c_sid}\';" line = sqlQuery_(sql) line = list(line[0]) line[1] = line[1].replace(' ','') print("\n\n\n",line) return render_template("edit.html", con_data=line, head=head, sid=sid) return render_template("error.html",msg="error02")
def checkOrder(local): if request.method == 'POST': print("00") order_id = request.form.get("order_id") sid = request.form.get("sid") print("order_id:",order_id) menu_info = sqlQuery_("""SELECT M.menu, B.cnt FROM menues M, basket B, orders O WHERE O.order_id = %s AND O.order_id = B.order_id AND B.menuid = M.menuid;""",(order_id,)) print("menu_list:",menu_info) deliver_info = sqlQuery_("""SELECT D.did, D.name, (D.lat - S.lat)^2 + (D.lng - S.lng)^2 AS distance FROM deliveries D, stores S WHERE D.stock < 5 AND S.sid = %s ORDER BY distance ASC limit 5;""",(sid,)) print("deliver_info:",deliver_info) print("local:",local) order_status = sqlQuery_("""SELECT status FROM orders WHERE order_id=%s""",(order_id,)) return render_template("sellerOrder.html",menu_info=menu_info,local=local,sid=sid,order_id=order_id,deliver_info=deliver_info,order_status=order_status)
def contacts(sid): sql = f"SELECT sid,password,sname,sex,major_id,tutor_id,grade FROM students WHERE sid=\'{sid}\';" line = sqlQuery_(sql) line = list(line[0]) line[0] = line[0].replace(' ','') line[1] = line[1].replace(' ','') sql = f"SELECT sid,phone,email FROM contacts;" hyContacts = sqlQuery_(sql) if sid.startswith("2009003125"): contacts_name = "grass_corp" elif sid.startswith("2013004394"): contacts_name = "fire_corp" elif sid.startswith("2014005004"): contacts_name = "water_corp" else: contacts_name = None sql = f"SELECT sid,phone,email,position FROM {contacts_name};" stuContacts = sqlQuery_(sql) # print("\n~~\n",list(stuContacts[0])) stu = [] for stuContact in stuContacts: stuContact = list(stuContact) stuContact[1] = stuContact[1].replace(' ','') stu.append(stuContact) # stuContact[1] = list(stuContact[1]).replace(' ','') print("stucon~:",stu) head = ["sid","phone","email","position","Edit/Delete"] return render_template("contacts.html", stu_data = line, con_data = hyContacts, user_data = stu, head=head[0:3], head2=head)
def newOrder(local): if request.method == 'POST': option(request.form, local, "save_order") return redirect("/"+local) try: tmp = sqlQuery_("""SELECT cid,lat,lng FROM customers WHERE local=%s""",(local,))[0] except IndexError: return redirect('/') customer_info = [] for t in tmp: customer_info.append(t) print("customer:",customer_info) dt = datetime.datetime.now() print("~~~~~~~:",dt.hour) if dt.hour==0: timeNow = 2400+dt.minute else: timeNow = dt.hour*100+dt.minute near_stores = sqlQuery_("""SELECT S.sid, (S.lat - C.lat)^2 + (S.lng - C.lng)^2 as distance, S.sname FROM stores S, customers C, store_schedules SS WHERE S.sid = SS.sid AND day_no = %s AND SS.holiday = false AND SS.opened <= %s AND SS.closed >= %s AND C.cid = %s ORDER BY distance ASC limit 100;""",(dt.weekday(),timeNow,timeNow,customer_info[0], ) ) print(near_stores) return render_template("orderMenu.html",local=local,near_stores=near_stores)
def portal(sid): if request.method == 'POST': option(request.form, sid) print("sid:",sid) conn = pg.connect(conn_str) cur = conn.cursor() if sid=="admin": sql = f"SELECT sid,password,sname,sex,major_id,tutor_id,grade FROM students;" students = sqlQuery_(sql) # students[0] = students[0].replace(' ','') ss = [] for student in students: print(student) tmp = [] for item in student: if item!= None and isinstance(item,int)==False: tmp.append(item.replace(' ','')) else: tmp.append(' ') ss.append(tmp) # print("00.",ss) # print("========================") sql = f"SELECT sid,phone,email FROM contacts;" cc = sqlQuery_(sql) print("cc:",cc) res = [] for c in cc: tmp = [] for item in c: if item != None: tmp.append(item.replace(' ','')) else: tmp.append(' '); res.append(tmp) # print("://",res) head1 = ["학번","비밀번호","이름","전공","학년","지도교수","성적","edit/delete"] head2= ["sid","phone","email","edit/delete"] context = [head1,head2] return render_template("portal_admin.html", con_data = res, context=context, students=ss) else: # EDIT LATER sql = f"SELECT sid,password,sname,sex,major_id,tutor_id,grade from students WHERE sid=\'{sid}\';" line = sqlQuery_(sql) print("04.",line) line = list(line[0]) print("\n\nLINE"+line) line[0] = line[0].replace(' ','') print("line2:",line) sql = f"SELECT sid,phone,email FROM contacts WHERE sid=\'{sid}\';" cc = sqlQuery_(sql) head = ["학번","이름","전공","학년","지도교수"] return render_template("portal.html", stu_data = line, con_data = cc, head=head)
def orderWithId(local,sid): menu_list = sqlQuery_("""SELECT M.menuid, M.menu FROM menues M, stores S WHERE S.sid=%s AND M.sid = S.sid;""",(sid,)) q = sqlQuery_("""SELECT payment FROM customers WHERE local=%s""",(local,)) payment_list = json.loads(q[0][0]) tmp = [[],[]] print(";",payment_list) payment=None print(tmp) return render_template("storeInfo.html",menu_list=menu_list,local=local,payment_list=payment_list,payment=payment,sid=sid)
def search(local): sql = "SELECT * FROM sellers WHERE local=\'{}\'".format(local) personInfo = sqlQuery_(sql) if len(personInfo) >= 1: return "sellers" sql = "SELECT * FROM deliveries WHERE local=\'{}\'".format(local) personInfo = sqlQuery_(sql) if len(personInfo) >= 1: return "deliveries" sql = "SELECT * FROM customers WHERE local=\'{}\'".format(local) personInfo = sqlQuery_(sql) if len(personInfo) >= 1: return "customers" return None
def editPayment(local): if request.method=='POST': option(request.form, local, "updatePayment") sid = request.form.get('sid') print("I got sid!!",sid) q = sqlQuery_("""SELECT payment FROM customers WHERE local=%s""",(local,)) payment_list = json.loads(q[0][0]) # sid = return render_template("editPayment.html",local=local,sid=sid,payment_list=payment_list)
def s_edit(): head = ["sid", "password", "sname", "sex", "major_id", "tutor_id", "grade"] sid = request.args.get('sid') print("sid::::", sid, "/") if sid == None: return render_template("s_add.html", head=head) sid = sid.replace(' ', '') sql = f"SELECT sid,password,sname,sex,major_id,tutor_id,grade FROM students WHERE sid=\'{sid}\';" line = sqlQuery_(sql) line = list(line[0]) return render_template("s_edit.html", head=head, con_data=line, sid=sid)
def login(): sid = request.form.get('sid') passwd = request.form.get('passwd') print("login! sid:",sid,"/pwd:",passwd,"/") sql = f"SELECT sid, password FROM students WHERE sid=\'{sid}\' AND password=\'{passwd}\';" rows = sqlQuery_(sql) print("rows:",rows) if len(rows)!=1: return render_template("error.html", msg="Wrong ID/Password") print(f"{sid}, {passwd}") return redirect(f"/{sid}")
def searchResult(local): store_results = None if request.method == 'POST': res = request.form.get('address') if res: store_results = sqlQuery_("""SELECT sid, NULL, sname FROM stores WHERE address LIKE %s""",('%%'+res+'%%',)) res = request.form.get('tag') if res: store_results = sqlQuery_("""SELECT T.sid, NULL, S.sname FROM stores S, store_tags T WHERE T.name LIKE %s AND T.sid=S.sid""",('%%'+res+'%%',)) res = request.form.get('store_name') if res: store_results = sqlQuery_("""SELECT sid, NULL, sname FROM stores WHERE sname LIKE %s""",('%%'+res+'%%',)) if store_results is None: store_results = [] return render_template("searchResult.html",store_results=store_results,local=local)
def store(local): if request.method == 'POST': print("10") if request.form.get('tags'): option(request.form, local, "edit_tag") elif request.form.get('accept'): option(request.form, local, "order_accept") elif request.form.get('decline'): option(request.form, local, "order_decline") else: print("request!!!!") print("before:",request.form.get('before_menu')) print("after:",request.form.get('after_menu')) option(request.form, local, "edit_store") sid = request.args.get('sid') # sid : store num print("sid:",sid) print("local:",local) menues = sqlQuery_("""SELECT menu from menues WHERE sid=%s""",(sid,)) menu_list = [] for menu in menues: menu_list.append(menu[0]) tags = sqlQuery_("""SELECT name from store_tags WHERE sid=%s""",(sid,)) oids = sqlQuery_("SELECT order_id, status FROM orders WHERE sid = %s",(sid,)) print(oids) # order_list = [] # for id in oids: order_list.append(id[0]) # print(order_list) if tags: res = [] for t in tags: res.append(t[0]) return render_template("manage.html",menu_list = menu_list, sid=sid, local=local, tag_list=res, order_list=oids) else: return render_template("manage.html",menu_list = menu_list, sid=sid, local=local, tag_list=[],order_list=oids)
def option_person(Form, local): # name/pwd change type = search(local) sql = "SELECT name, passwd FROM {} WHERE local=\'{}\'".format(type, local) personInfo = sqlQuery_(sql) # print("origin:",list(personInfo[0])) # print("after:",Form.get("name"),Form.get("password")) if Form.get("save"): sql = "UPDATE {} SET name=\'{}\', passwd=\'{}\' WHERE local=\'{}\'".format( type, Form.get("name"), Form.get("password"), local) sqlQuery(sql) print("edit") else: print("cancel")
def count(sid): head = ["domain_name","count"] sql = f"SELECT * FROM contacts;" lines = sqlQuery_(sql) dic = {} for line in lines: tmp = line[2].split('@') if len(tmp)==2: if tmp[1] in dic.items(): dic[tmp[1]]+=1 else: dic[tmp[1]]=1 domains = [] for x,y in list(dic.items()): domains.append([x,y]) print(domains) return render_template("count.html",head=head,cnt_data=domains)
def option_updatePayment(Form, local): q = sqlQuery_("""SELECT payment FROM customers WHERE local=%s""", (local, )) payment_list = json.loads(q[0][0]) # account editing if Form.get("edit_acc"): origin_accNum = int(Form.get("origin_accNum")) get_acc_num = int(Form.get("get_acc_num")) get_acc_bid = int(Form.get("get_acc_bid")) for payment in payment_list: if payment['type'] == 'account' and payment['data'][ 'acc_num'] == origin_accNum: payment['data']['acc_num'] = get_acc_num payment['data']['bid'] = get_acc_bid tmp = json.dumps(payment_list) sqlQuery("""UPDATE customers SET payment=%s WHERE local=%s""", (tmp, local)) print("require / account edit ::: ", origin_accNum, get_acc_num, get_acc_bid) # card editing elif Form.get("edit_card"): origin_card = int(Form.get("origin_card")) get_card_num = int(Form.get("get_card_num")) for payment in payment_list: if payment['type'] == 'card' and payment['data'][ 'card_num'] == origin_card: payment['data']['card_num'] = get_card_num tmp = json.dumps(payment_list) sqlQuery("""UPDATE customers SET payment=%s WHERE local=%s""", (tmp, local)) print("require / card edit ::: ", origin_card, get_card_num) # account added elif Form.get("add_acc"): add_acc_num = int(Form.get("add_acc_num")) add_acc_bid = int(Form.get("add_acc_bid")) res = { 'type': 'account', 'data': { 'bid': add_acc_bid, 'acc_num': add_acc_num } } payment_list.append(res) tmp = json.dumps(payment_list) sqlQuery("""UPDATE customers SET payment=%s WHERE local=%s""", (tmp, local)) print("require / account add ::: ", add_acc_num, add_acc_bid) # card added elif Form.get("add_card"): add_card_num = int(Form.get("add_card_num")) res = {'type': 'card', 'data': {'card_num': add_card_num}} payment_list.append(res) tmp = json.dumps(payment_list) sqlQuery("""UPDATE customers SET payment=%s WHERE local=%s""", (tmp, local)) elif Form.get("del_acc"): origin_accNum = int(Form.get("origin_accNum")) print("::", payment_list) idx = None for i, payment in enumerate(payment_list): if payment['type'] == 'account' and payment['data'][ 'acc_num'] == origin_accNum: idx = i if idx is not None: del (payment_list[idx]) else: raise ValueError('삭제할 계좌 번호가 없음') tmp = json.dumps(payment_list) sqlQuery("""UPDATE customers SET payment=%s WHERE local=%s""", (tmp, local)) elif Form.get("del_card"): origin_card = int(Form.get("origin_card")) print("origin:", origin_card) print("payment1:", payment_list) idx = None for i, payment in enumerate(payment_list): if payment['type'] == 'card' and payment['data'][ 'card_num'] == origin_card: idx = i print("idx:", idx) if idx is not None: del (payment_list[idx]) else: raise ValueError('삭제할 카드 번호가 없음') print("payment2:", payment_list) tmp = json.dumps(payment_list) sqlQuery("""UPDATE customers SET payment=%s WHERE local=%s""", (tmp, local))
def portal(local): # if request.method == 'POST': # option(request.form, local, "edit_id") if local == 'favicon.ico': return '' print("hi",local) conn = pg.connect(conn_str) cur = conn.cursor() type = search(local) print("type:",type) if type=="sellers": storeInfo = sqlQuery_("""SELECT * FROM stores WHERE seller_id=(SELECT seller_id FROM sellers S WHERE S.local=%s);""",(local,)) personInfo = sqlQuery_("""SELECT * FROM sellers WHERE local=%s""",(local,)) if len(personInfo)>=1: tmp = [] for store in storeInfo: tmp.append(list(store)) rows = [[stores_menu,sellers_menu],tmp,list(personInfo[0])] try: order_list = sqlQuery_("""SELECT order_id, status FROM orders WHERE sid = (SELECT sid FROM stores WHERE seller_id=(SELECT seller_id FROM sellers WHERE local=%s));""",(local,))[0] except IndexError: order_list = [] print("~:",order_list) return render_template("portal_s.html", info=rows,order_list=order_list) elif type=="deliveries": rows = sqlQuery_("""SELECT * FROM deliveries WHERE local=%s""",(local,))[0] personInfo = sqlQuery_("""SELECT * FROM sellers WHERE local=%s""",(local,)) # if len(personInfo)>=1: # rows = [rows,personInfo] try: orderDelivering = sqlQuery_("""SELECT O.order_id, S.sname, M.menu, O.timestmp, O.status FROM orders O, stores S, menues M, basket B, deliveries D WHERE D.did = %s AND B.cnt >0 AND O.status = 'delivering' AND B.menuid = M.menuid AND O.order_id = B.order_id AND O.sid=S.sid AND D.did=O.did ORDER BY O.timestmp DESC;""",(rows[0],)) except IndexError: orderDelivering = [] print(":::",orderDelivering) print("info::",rows) return render_template("portal_d.html", info=rows,orderDelivering=orderDelivering) elif type=="customers": if request.method == 'POST': option(request.form, local, "delivery_fin") return redirect("/"+local) print("customers") personInfo = sqlQuery_("SELECT * FROM customers WHERE local=%s",(local,)) if len(personInfo)>=1: rows = [customers_menu,list(personInfo[0])] try: orderComplete = sqlQuery_("""SELECT O.order_id, S.sname, M.menu, O.payment, O.timestmp FROM orders O, stores S, menues M, basket B WHERE O.cid = %s AND B.cnt >0 AND O.order_id = B.order_id AND B.menuid = M.menuid AND O.sid = S.sid AND O.status = 'completed' ORDER BY O.timestmp DESC;""",(rows[1][0],)) except IndexError: orderComplete = [] print(".:",orderComplete) try: orderWaiting = sqlQuery_("""SELECT O.order_id, S.sname, M.menu, O.payment, O.timestmp, O.status FROM orders O, stores S, menues M, basket B WHERE O.cid = %s AND B.cnt >0 AND O.status = 'waiting' AND B.menuid = M.menuid AND O.order_id = B.order_id AND O.sid=S.sid ORDER BY O.timestmp DESC;""",(rows[1][0],)) except IndexError: orderWaiting = [] try: orderDelivering = sqlQuery_("""SELECT O.order_id, S.sname, M.menu, O.payment, O.timestmp, O.status, D.name FROM orders O, stores S, menues M, basket B, deliveries D WHERE O.cid = %s AND B.cnt >0 AND O.status = 'delivering' AND B.menuid = M.menuid AND O.order_id = B.order_id AND O.sid=S.sid AND D.did=O.did ORDER BY O.timestmp DESC;""",(rows[1][0],)) except IndexError: orderDelivering = [] print("...:",orderWaiting) print("//",orderDelivering) return render_template("portal_c.html", info=rows,orderComplete=orderComplete,orderWaiting=orderWaiting,orderDelivering=orderDelivering) else: print("error 06") row = [] return render_template("portal_"+type[0]+".html", info=rows)