def login(provide_username, provide_keyword): conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() IfSuccess = False truth_username = utils.base64decode(provide_username) truth_keyword = utils.base64decode(provide_keyword) provide_name = "" sql = "select \"name\" from dispatcher where \"username\"='{0}' and \"keyword\"='{1}';" \ .format(truth_username, truth_keyword) try: cursor.execute(sql) # 执行SQL res = cursor.fetchall() if (len(res) != 0): IfSuccess = True truth_name = res[0][0] provide_name = utils.base64encode(truth_name) except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 cursor.close() conn.close() text = {"ifSuccess": IfSuccess, "username": provide_username, "name": provide_name} return json.dumps(text, ensure_ascii=False, indent=4)
def add_order(id, receiverAddressId, deliverUsername, goodDescription, receivingOption, username): truth_username = utils.base64decode(username) truth_deliverusername = utils.base64decode(deliverUsername) receiverAddressId = int(receiverAddressId) receivingOption = int(receivingOption) conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() t = time.time() now_time = round(t * 1000) sql = ( 'INSERT INTO deliveryorder(id,receiveraddressid,receiverusername,deliverusername,gooddescription,receivingoption,' 'callbackstate,orderstate,deschangedtimes,entrytime)' "VALUES('%s',%d,'%s','%s','%s',%d,0,0,0,%d)") % ( id, receiverAddressId, truth_username, truth_deliverusername, goodDescription, receivingOption, now_time) try: cursor.execute(sql) # 执行SQL conn.commit() text = {"ifSucess": True} except Exception as e: print("异常信息为:", e) text = {"ifSucess": False} conn.rollback() # 回滚 cursor.close() conn.close() return json.dumps(text, ensure_ascii=False, indent=4)
def dispatcher_welcome(): id = request.args.get("username") name = request.args.get("name") try: tru_id = utils.base64decode(id) tru_name = utils.base64decode(name) res = dispatcher.check_login(tru_id, tru_name) if (res == True): return render_template('dispatcher/index.html') else: return redirect(url_for('dispatcher_login')) # url重定向 except: return redirect(url_for('dispatcher_login'))
def add_address(id, accountUsername, receiverName, province, city, addressdetail, addresslon, addresslat, phonenum): truth_username = utils.base64decode(accountUsername) conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() sql = ( 'INSERT INTO receiveraddressinfo(id,accountusername,receivername,province,city,addressdetail,addresslon,addresslat,phonenum)' "VALUES('%d','%s','%s','%s','%s','%s','%lf','%lf','%s')") % ( id, truth_username, receiverName, province, city, addressdetail, addresslon, addresslat, phonenum) try: cursor.execute(sql) # 执行SQL conn.commit() text = {"ifSucess": True} except Exception as e: print("异常信息为:", e) text = {"ifSucess": False} conn.rollback() # 回滚 cursor.close() conn.close() return json.dumps(text, ensure_ascii=False, indent=4)
def address_query(username, search_keyword): conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() provide_username = username para = [] truth_username = utils.base64decode(provide_username) id = [] accountusername = [] province = [] city = [] addressdetail = [] addresslon = [] addresslat = [] phonenum = [] receivername = [] sql = "select * from receiveraddressinfo where accountusername='******'".format( truth_username) try: cursor.execute(sql) # 执行SQL res = cursor.fetchall() for list in res: id.append(list[0]) accountusername.append(list[1]) province.append(list[3]) city.append(list[4]) addressdetail.append(list[5]) addresslon.append(list[6]) addresslat.append(list[7]) phonenum.append(list[8]) receivername.append(list[2]) except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 cursor.close() conn.close() for i in range(len(id)): if (search_keyword in addressdetail[i] or search_keyword in province[i] or search_keyword in city[i]): text = { "id": id[i], "accountUsername": provide_username, "receiverName": receivername[i], "province": province[i], "city": city[i], "addressDetail": addressdetail[i], "addresslon": addresslon[i], "addresslat": addresslat[i], "phoneNum": phonenum[i] } para.append(text) return json.dumps(para, ensure_ascii=False, indent=4)
def order_callback_queryall(username): truth_username = utils.base64decode(username) conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() sql = "select t_order.id,t_add.receivername,t_add.addressdetail,t_order.gooddescription,t_order.callbackstate " \ "from deliveryorder as t_order, receiveraddressinfo as t_add " \ "where deliverusername='******' and t_add.id=t_order.receiveraddressid " \ "and t_add.accountusername=t_order.receiverusername and t_order.orderstate=4".format( truth_username) para = [] try: cursor.execute(sql) # 执行SQL res = cursor.fetchall() for list in res: text = { "id": list[0], "receiverName": list[1], "receiverAddress": list[2], "goodDescription": list[3], "callbackState": list[4] } para.append(text) except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 cursor.close() conn.close() return json.dumps(para, ensure_ascii=False, indent=4)
def send_nearbynodes(username): truth_username = utils.base64decode(username) conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() sql = "select * from deliver where username='******'" % (truth_username) province = "" city = "" lat = 0 lon = 0 try: cursor.execute(sql) # 执行SQL res = cursor.fetchall() province = res[0][3] city = res[0][4] lon = res[0][6] lat = res[0][7] except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 sql = "SELECT express.id,express.address,ST_Distance(ST_Transform(st_geomfromtext('POINT({0} {1})',4326), 32650), " \ "ST_Transform(point_new.log_point, 32650)) as distance,point.longtitude, point.latitude,express.province,express.city " \ "FROM point_new,express,point " \ "where ST_DWithin(ST_Transform(st_geomfromtext('POINT({0} {1})',4326),32650),ST_Transform(point_new.log_point,32650),5000) " \ "and point.address=express.address " \ "and express.address=point_new.address " \ "order by distance,express.id;".format(lon, lat) options = [] all_address = [] try: cursor.execute(sql) res = cursor.fetchall() for r in res: id = r[0] address = r[1] distance = r[2] lng = r[3] lat = r[4] node_province = r[5] node_city = r[6] if (node_city in city or node_province in city) and address not in all_address: option = { "id": id, "address": address, "distance": str(int(distance)) + "米", "lng": lng, "lat": lat } options.append(option) all_address.append(address) except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 return json.dumps(options)
def register(provide_username, provide_keyword, provide_name, phoneNum, province, city, addressDetail, lon, lat): conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() truth_username = utils.base64decode(provide_username) truth_keyword = utils.base64decode(provide_keyword) truth_name = utils.base64decode(provide_name) IfSuccess = True IfExist = False sql = "select * from deliver where \"username\"='{0}'".format( truth_username) try: cursor.execute(sql) # 执行SQL res = cursor.fetchall() if (len(res) != 0): IfSuccess = False IfExist = True else: cursor1 = conn.cursor() sql1 = "INSERT INTO deliver VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')".format( truth_username, truth_keyword, truth_name, province, city, addressDetail, lon, lat, phoneNum) try: cursor1.execute(sql1) # 执行SQL conn.commit() except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 cursor1.close() except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 cursor.close() conn.close() text = {"ifSucess": IfSuccess, "ifExist": IfExist} return json.dumps(text, ensure_ascii=False, indent=4)
def query_all_order(username): conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() provide_username = username truth_username = utils.base64decode(provide_username) para = [] sql = "select torder.id,torder.gooddescription,tadd.receivername," \ "deliver.name,tadd.city,tadd.addressdetail,torder.entrytime,torder.callbackstate," \ "torder.orderstate,torder.deschangedtimes,torder.receivingoption " \ "from receiveraddressinfo as tadd,deliveryorder as torder,deliver " \ "where torder.receiverusername='******' " \ "and tadd.accountusername=torder.receiverusername " \ "and torder.receiveraddressid = tadd.id " \ "and deliver.username=torder.deliverusername".format( truth_username) try: cursor.execute(sql) # 执行SQL res = cursor.fetchall() for list in res: text = { "id": list[0], "accountUsername": provide_username, "goodDescription": list[1], "receiverName": list[2], "deliverName": list[3], "addressDetail": list[4] + list[5], "entryTime": list[6], "callbackState": list[7], "orderState": list[8], "desChangedTimes": list[9], "receivingOption": list[10] } para.append(text) except Exception as e: print("异常信息为:", e) conn.rollback() cursor.close() conn.close() return json.dumps(para, ensure_ascii=False, indent=4)
def basic_info(username): conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() truth_username = utils.base64decode(username) sql = "select \"name\",\"addresslon\",\"addresslat\" from deliver where \"username\"='{0}';" \ .format(truth_username) cursor.execute(sql) # 执行SQL res = cursor.fetchall() name = res[0][0] lng = res[0][1] lat = res[0][2] text = {"username": username, "name": name, "lng": lng, "lat": lat} return json.dumps(text, ensure_ascii=False, indent=4)
def edit_address(id, accountUsername, receiverName, province, city, addressdetail, addresslon, addresslat, phonenum): truth_username = utils.base64decode(accountUsername) conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() id = int(id) sql0 = "select id,accountusername from receiveraddressinfo where id=%d and accountusername='******'" % ( id, truth_username) id_new = [] username_new = [] try: cursor.execute(sql0) # 执行SQL res = cursor.fetchall() for list in res: id_new.append(list[0]) username_new.append(list[1]) except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚 if (len(id_new) == 0): text = {"ifSucess": False} return json.dumps(text, ensure_ascii=False, indent=4) sql = "update receiveraddressinfo set receivername='%s',province='%s',city='%s',addressdetail='%s',addresslon=%lf,addresslat=%lf,phonenum='%s'" \ "where id=%d and accountusername='******'" % ( receiverName, province, city, addressdetail, addresslon, addresslat, phonenum, id, truth_username) try: cursor.execute(sql) # 执行SQL conn.commit() text = {"ifSucess": True} except Exception as e: print("异常信息为:", e) text = {"ifSucess": False} conn.rollback() # 回滚 cursor.close() conn.close() return json.dumps(text, ensure_ascii=False, indent=4)
def order_unfinished_num(username): conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() truth_username = utils.base64decode(username) sql = "select * from deliveryorder where receiverusername='******' and orderstate!=5" % ( truth_username) unfinished_num = 0 try: cursor.execute(sql) # 执行SQL res = cursor.fetchall() unfinished_num = len(res) text = {"orderNum": unfinished_num} cursor.close() conn.close() return json.dumps(text, ensure_ascii=False, indent=4) except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚
def user_delete_address(): id = request.args.get("id") username = request.args.get("username") username = utils.base64decode(username) result = user.delete_address(id, username) return result
def extractText(node): if node.attrib.get('{http://www.w3.org/1999/02/22-rdf-syntax-ns#}datatype') == 'http://www.w3.org/2001/XMLSchema#base64Binary': return base64decode(node.text) else: return node.text
import base64 import utils u = '123456' encode = utils.base64encode(u) print(encode) print(utils.base64decode(encode)) str = 'Y2hlbmdmZW5nZ3Vp' t = base64.b64decode(str).decode() print(t)
def mapOrder_queryAll(username): truth_username = utils.base64decode(username) conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port) cursor = conn.cursor() try: sql = "select id,nodenumnow,gooddescription from deliveryorder where receivingoption=1 and orderstate=2 and receiverusername='******'" % ( truth_username) cursor.execute(sql) # 执行SQL res = cursor.fetchall() id = [] nodenumnow = [] gooddescription = [] node_addressdetail = [] nodelon = [] nodelat = [] isgroup = [] for list in res: id.append(list[0]) nodenumnow.append(list[1]) gooddescription.append(list[2]) nodeid = [] for i in range(len(id)): sql1 = "select nodeid from routeinfo where orderid='%s' and sequenceid=%d" % ( id[i], nodenumnow[i]) cursor.execute(sql1) # 执行SQL res = cursor.fetchall() node_id = res[0][0] nodeid.append(node_id) sql2 = "select express.address, point.longtitude, point.latitude " \ "from express,point where express.id={0} and express.address = point.address".format( node_id) cursor.execute(sql2) # 执行SQL res = cursor.fetchall() node_addressdetail.append(res[0][0]) nodelon.append(res[0][1]) nodelat.append(res[0][2]) isgroup.append(False) basicinfo = [] for i in range(len(id)): text = { "id": id[i], "addressDetail": (node_addressdetail[i]), "goodDescription": gooddescription[i], "nodeLon": nodelon[i], "nodeLat": nodelat[i] } basicinfo.append(text) mapinfo = [] groupid = [] groupnumber = 1 for i in range(len(id)): groupid.append(0) for i in range(len(id)): if (isgroup[i] == True): continue isgroup[i] = True groupid[i] = groupnumber for j in range(len(id)): if (i == j or isgroup[j] == True): continue if (nodeid[i] == nodeid[j]): isgroup[j] = True groupid[j] = groupnumber groupnumber += 1 groupnumber -= 1 for i in range(groupnumber): groupnum = i + 1 orders = [] ordernum = 0 for j in range(len(groupid)): if (groupid[j] == groupnum): ordernum += 1 nodeaddress = node_addressdetail[j] nodeLon = nodelon[j] nodeLat = nodelat[j] text = {"orderId": id[j], "orderGood": gooddescription[j]} orders.append(text) text = { "nodeAddress": nodeaddress, "nodeLon": nodeLon, "nodeLat": nodeLat, "orderNum": ordernum, "orders": orders } mapinfo.append(text) final_result = {"basicInfo": basicinfo, "mapInfo": mapinfo} return json.dumps(final_result, ensure_ascii=False, indent=4) except Exception as e: print("异常信息为:", e) conn.rollback() # 回滚