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() # 回滚