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)
