def getpiece():
begin = time.time()
email = session.get('username')
dbcnn()
cursor = g.cnn.cursor()
try:
sql = "select c.cityid, b.cityname_cn from userinfo a, city_config b, service_city c where a.id = c.userid and c.cityid = b.cityid and a.email = %s"
data = (email, )
cursor.execute(sql, data)
lists = cursor.fetchall()
index = ['cityid', 'cityname_cn']
resu = toDict(index, lists)
sql = "select id, cityid, name_cn, price, left(ltrim(description),15), cover from frag where cityid = %s and name_cn is not null and name_cn <> '' order by id limit 12"
data = (int(resu[0]['cityid']), )
cursor.execute(sql, data)
piece = cursor.fetchall()
index = ['id', 'cityid', 'name_cn', 'price', 'description', 'cover']
piecelist = toDict(index, piece)
data = [{"citylist": resu, "piecelist": piecelist}]
data = json.dumps(data)
except Exception as e:
print('-------Error on %s\r\n-------Exception:%s' %
(request.path, format(e)))
end = time.time()
print(begin - end)
return data
def getitineraryinfo(xcdzid):
if isSession():
email = session.get('username')
dbcnn()
cursor = g.cnn.cursor()
try:
sql = "select i.id, i.providerid, i.xcdzid, i.title, i.coverimg, i.startdate, i.enddate from userinfo u, itinerary i " \
"where u.id = i.providerid and u.email = %s and i.xcdzid = %s"
data = (email, xcdzid)
cursor.execute(sql, data)
lists = cursor.fetchall()
index = [
'id', 'providerid', 'xcdzid', 'title', 'coverimg', 'begindate',
'enddate'
]
da = toDict(index, lists)
itineraryid = da[0]['id']
da[0]['cover'] = da[0]['coverimg'].split("/")[-1]
sql = "select id, itineraryid, lightdes, lightimg from itinerarylight where itineraryid = %s"
data = (itineraryid, )
cursor.execute(sql, data)
li = cursor.fetchone()
light = {}
light['id'] = li[0]
light['itineraryid'] = li[1]
light['lightdes'] = li[2]
light['lightimg'] = li[3]
light['light'] = light['lightimg'].split("/")[-1]
da[0]['light'] = light
data = json.dumps(da)
except Exception as e:
print('error!{}'.format(e))
return data
def addexpense():
if isSession():
if request.method == "POST":
email = session.get("username")
itineraryid = request.form['itineraryid']
name = request.form['name']
price = request.form['price']
currency = request.form['currency']
cnt = request.form['cnt']
try:
dbcnn()
cursor = g.cnn.cursor()
sql = "insert into expense (itineraryid, ex_name, ex_price, currency, ex_cnt) VALUES (%s, %s, %s, %s, %s)"
data = (itineraryid, name, int(price), currency, int(cnt))
cursor.execute(sql, data)
g.cnn.commit()
sql = "select itineraryid, ex_name, ex_price, currency, ex_cnt, ex_price*ex_cnt from expense where itineraryid = %s "
data = (itineraryid, )
cursor.execute(sql, data)
lists = cursor.fetchall()
index = [
'itineraryid', 'name', 'price', 'currency', 'cnt', 'total'
]
data = toDict(index, lists)
data = json.dumps(data)
except Exception as e:
print("error{}!".format(e))
return data
def getcontinent():
dbcnn()
cursor = g.cnn.cursor()
try:
sql = "select continentid, continentname_cn from continent_config"
cursor.execute(sql)
lists = cursor.fetchall()
index = ['continentid', 'continentname_cn']
resu = toDict(index, lists)
data = json.dumps(resu)
except Exception as e:
print('-------Error on %s\r\n-------Exception:%s' %
(request.path, format(e)))
return data
def search(word):
try:
dbcnn()
cursor = g.cnn.cursor()
sql = "select id, cityid, name_cn, price, left(ltrim(description),15), cover from frag where name_cn like '%" + word + "%' or name_en like '%" + word + "%'"
print(sql)
# data = (word, word)
cursor.execute(sql)
lists = cursor.fetchall()
index = ['id', 'cityid', 'name_cn', 'price', 'description', 'cover']
res = toDict(index, lists)
data = json.dumps(res)
except Exception as e:
print("error{}!".format(e))
return data
def getexpense(id):
try:
dbcnn()
cursor = g.cnn.cursor()
sql = "select itineraryid, ex_name, ex_price, currency, ex_cnt, ex_price*ex_cnt from expense where itineraryid = %s "
data = (id, )
cursor.execute(sql, data)
lists = cursor.fetchall()
index = ['itineraryid', 'name', 'price', 'currency', 'cnt', 'total']
data = toDict(index, lists)
data = json.dumps(data)
except Exception as e:
print("error{}!".format(e))
return data
def getcity():
dbcnn()
cursor = g.cnn.cursor()
try:
countryid = request.args.get('id')
sql = "select cityid, cityname_cn from city_config where " \
"countryid = %s" % countryid
cursor.execute(sql)
lists = cursor.fetchall()
index = ['cityid', 'cityname_cn']
resu = toDict(index, lists)
data = json.dumps(resu)
except Exception as e:
print('-------Error on %s\r\n-------Exception:%s' %
(request.path, format(e)))
return data
def getcitypiece(cityid, type=0, count=1):
dbcnn()
cursor = g.cnn.cursor()
try:
if int(type) == 0:
sql = "select id, cityid, name_cn, price, left(ltrim(description), 15), cover from frag where cityid = %s and name_cn <> '' order by id limit %s, 12"
data = (int(cityid), (int(count) - 1) * 12)
else:
sql = "select id, cityid, name_cn, price, left(ltrim(description), 15), cover from frag where cityid = %s and type = %s and name_cn <> '' order by id limit %s, 12"
data = (int(cityid), int(type) - 1, (int(count) - 1) * 12)
cursor.execute(sql, data)
piece = cursor.fetchall()
index = ['id', 'cityid', 'name_cn', 'price', 'description', 'cover']
piecelist = toDict(index, piece)
data = json.dumps(piecelist)
except Exception as e:
print('-------Error on %s\r\n-------Exception:%s' %
(request.path, format(e)))
return data
def getmypiece():
id = session.get('user_id')
dbcnn()
cursor = g.cnn.cursor()
try:
sql = "select m.id, m.cityid, m.name_cn, m.price, "
"left(ltrim(m.description),15), m.cover from frag m where "
"m.sup_id = %s and m.providerid = u.id order by id = limit 12"
data = (str(id), )
cursor.execute(sql, data)
piece = cursor.fetchall()
index = ['id', 'cityid', 'name_cn', 'price', 'description', 'cover']
piecelist = toDict(index, piece)
data = [{"piecelist": piecelist}]
data = json.dumps(data)
print(data)
except Exception as e:
print('-------Error on %s\r\n-------Exception:%s' %
(request.path, format(e)))
return data
def getpiecebyid(id):
begin = time.time()
# email = session.get('username')
dbcnn()
cursor = g.cnn.cursor()
try:
sql = "select id, cityid, name_cn, price, trim(description), cover, period, ticket, tel, url, address, lat, lng from frag where id = %s"
data = (int(id), )
cursor.execute(sql, data)
piece = cursor.fetchall()
index = [
'id', 'cityid', 'name_cn', 'price', 'description', 'cover',
'period', 'ticket', 'tel', 'url', 'address', 'lat', 'lng'
]
piecelist = toDict(index, piece)
data = [{"piecelist": piecelist}]
data = json.dumps(data)
except Exception as e:
print('-------Error on %s\r\n-------Exception:%s' %
(request.path, format(e)))
end = time.time()
print(begin - end)
return data