def orderInfo(uuid):
uName=db.query("SELECT Name from User WHERE UserID = $userID",vars={'userID':uuid})[0]['Name']
ordered=db.query("SELECT * FROM User_Ticket WHERE UserID = $userID ", vars={'userID':uuid})
ordercount=db.query("SELECT COUNT(*) AS C FROM User_Ticket WHERE UserID = $userID ", vars={'userID':uuid})[0]
replayText=uName+'-'+uuid+u'已订：\n'
replyText=replayText
if ordercount['C']==0:
replyText=replayText
else:
for i in range(ordercount['C']):
#tic=ordered[i]['TicketID']
tic=db.query("SELECT * FROM Ticket WHERE TicketID = $ticketID ", vars={'ticketID':ordered[i]['TicketID']})[0]
tic_tid=tic['TicketID']
tic_date=tic['Date']
tic_direction=tic['Direction']
#replayText=tic_direction
if tic_direction==1:
string=u'雁栖湖至玉泉路'
elif tic_direction==0:
string=u'玉泉路至雁栖湖'
date_time = datetime.datetime.strptime(tic_date,'%Y-%m-%d')
today_time=datetime.datetime.today()
if date_time+ datetime.timedelta(days=1) >= today_time : ##当天订票显示
replayText=replayText+tic_tid+':'+tic_date+'-- '+string+'\n'
replyText=replayText
return replyText
def init_schema(cls, db):
"""Initialises the database schema"""
# create table
with db.transaction():
db.query("""
CREATE TABLE access_keys (
key_id INTEGER PRIMARY KEY,
key TEXT NOT NULL
)
""")
def renderDmdPool(obj, userid, username):
db = connectDB()
demands = []
query = db.query("SELECT orders.requestid, orders.orderid, orders.dmd, orders.quote, orders.destination, orders.time FROM orders, dmdpool WHERE orders.orderid = dmdpool.orderid ORDER BY dmdpool.dmdid DESC")
if len(query) > 0:
for item in query:
query2 = db.query("SELECT username, telephone FROM user WHERE userid = $rid AND userid <> $uid", vars = {'rid': item.requestid, 'uid': userid})
for item2 in query2:
demand = [item.dmd, item.destination, item2.username, item2.telephone, item.quote, item.time, item.orderid]
demands.append(demand)
obj.render('demandpool.html', userid = userid, username = username, demands = demands)
def delete_note(id):
if xconfig.DB_ENGINE == "sqlite":
db = xtables.get_file_table()
sql = "UPDATE file SET is_deleted = 1, mtime=$mtime where id = $id"
db.query(sql, vars=dict(mtime=xutils.format_datetime(), id=id))
else:
note = get_by_id(id)
if note:
note.mtime = xutils.format_datetime()
note.is_deleted = 1
kv_put_note(id, note)
update_children_count(note.parent_id)
def renderService(obj, userid, username):
db = connectDB()
services = []
query = db.query("SELECT * FROM orders WHERE serveid = $uid ORDER BY orderid DESC", vars={'uid': int(userid)})
if len(query) > 0:
for item in query:
query2 = db.query("SELECT username, telephone FROM user WHERE userid = $uid", vars = {'uid': int(item.requestid)})
for item2 in query2:
service = [item.dmd, item.destination, item2.username, item2.telephone, item.quote, item.time, str(item.status), item.orderid]
services.append(service)
obj.render('service.html', userid = userid, username = username, services = services)
def init_schema(cls, db):
"""Initialises the database schema"""
# create table
with db.transaction():
db.query("""
CREATE TABLE channels (
channel INTEGER UNSIGNED NOT NULL,
name TEXT NOT NULL,
CONSTRAINT channel_index UNIQUE (channel)
)
""")
def visit_note(id):
if xconfig.DB_ENGINE == "sqlite":
db = xtables.get_file_table()
sql = "UPDATE file SET visited_cnt = visited_cnt + 1, atime=$atime where id = $id"
db.query(sql, vars=dict(atime=xutils.format_datetime(), id=id))
else:
note = get_by_id(id)
if note:
note.atime = xutils.format_datetime()
if note.visited_cnt is None:
note.visited_cnt = 0
note.visited_cnt += 1
kv_put_note(id, note)
def init_schema(cls, db):
"""Initialises the database schema"""
# create table
db.query("""
CREATE TABLE {0} (
channel INTEGER UNSIGNED NOT NULL,
timestamp DATETIME(3) NOT NULL,
value INTEGER NOT NULL,
FOREIGN KEY(channel) REFERENCES channels(channel),
CONSTRAINT sample_index UNIQUE (channel, timestamp)
ON CONFLICT IGNORE
)
""".format(cls.TABLE_NAME))
def init_schema(cls, db):
"""Initialises the database schema"""
# create table
with db.transaction():
db.query("""
CREATE TABLE channel_access (
channel INTEGER UNSIGNED NOT NULL,
key_id INTEGER NOT NULL,
mode TINYINT UNSIGNED NOT NULL,
FOREIGN KEY(channel) REFERENCES channels(channel),
FOREIGN KEY(key_id) REFERENCES access_keys(key_id)
)
""")
def rdb_list_tag(user_name):
db = xtables.get_file_tag_table()
sql = """SELECT LOWER(name) AS name, COUNT(*) AS amount FROM file_tag
WHERE (user=$user OR is_public=1)
GROUP BY LOWER(name) ORDER BY amount DESC, name ASC"""
tag_list = list(db.query(sql, vars=dict(user=user_name)))
return tag_list
def renderRequest(obj, userid, username):
db = connectDB()
requests = []
query = db.query("SELECT * FROM orders WHERE requestid = $uid ORDER BY orderid DESC", vars={'uid': int(userid)})
if len(query) > 0:
for item in query:
if item.serveid:
query2 = db.query("SELECT username, telephone FROM user WHERE userid = $sid", vars = {'sid': int(item.serveid)})
for item2 in query2:
request = [item.dmd, item.destination, item2.username, item2.telephone, item.quote, item.time, str(item.status), item.orderid]
requests.append(request)
else:
request = [item.dmd, item.destination, '', '', item.quote, item.time, str(item.status), item.orderid]
requests.append(request)
obj.render('request.html', userid = userid, username = username, requests = requests)
def get_category(name=None, limit=None):
db = get_db()
if limit is None:
limit = 200
sql = "SELECT * from file where is_deleted != 1 and parent_id = 0 AND type = 'group' "
if name is not None:
sql += " AND groups = %r" % name
sql += " ORDER BY priority DESC, sctime DESC limit %s" % limit
all = db.query(sql)
return [FileDO.fromDict(item) for item in all]
def init_db_wrapper(dbpath):
db = web.db.SqliteDB(db=dbpath)
db.query("PRAGMA temp_store = MEMORY;")
# 启用Memory-Mapped I/O
db.query("PRAGMA mmap_size=268435456;")
db.query("PRAGMA synchronous = NORMAL;")
DBWrapper._pool[dbpath] = db
def login(username, password):
db = connectDB()
mmd5 = hashlib.md5()
mmd5.update(password)
pwd = mmd5.hexdigest()
query = db.query("SELECT userid, username FROM user WHERE username = $usr AND password = $pwd", vars={'usr': username, 'pwd' : pwd})
if len(query) == 1:
for item in query:
return [True, item.userid, item.username]
else:
return [False]
def list_tag(user_name):
t = Timer()
t.start()
cache_key = "%s@tag_list" % user_name
tag_list = xutils.cache_get(cache_key)
tag_list = None
if tag_list is None:
db = xtables.get_file_tag_table()
sql = """SELECT LOWER(name) AS name, COUNT(*) AS amount FROM file_tag
WHERE (user=$user OR is_public=1)
GROUP BY LOWER(name) ORDER BY amount DESC, name ASC"""
tag_list = list(db.query(sql, vars=dict(user=user_name)))
xutils.cache_put(cache_key, tag_list, 60 * 10)
t.stop()
xutils.trace("NoteDao.ListTag", "", t.cost_millis())
return tag_list
def _usr_(obj):
db = web.database(
dbn = 'mysql',
host = sae.const.MYSQL_HOST,
port = int(sae.const.MYSQL_PORT),
user = sae.const.MYSQL_USER,
passwd = sae.const.MYSQL_PASS,
db = sae.const.MYSQL_DB
)
username = obj.get_argument('username')
query = db.query("SELECT * FROM user WHERE username = $usr", vars={'usr': username})
if len(query) > 0:
dic = {'action':'usr', 'status':'failure', 'reason':'User Name Exist'}
obj.write(str(dic) + '\n')
else:
dic = {'action':'usr', 'status':'success', 'reason':''}
obj.write(str(dic) + '\n')
def generate_loc(domain, stub_output):
zips = db.query(Zipcode).all()
output = []
for z in zips:
record = str(z.code)+"."+domain+". 86400 IN LOC "+str(abs(z.lat_deg))+" "+str(z.lat_min)+" "+str(z.lat_sec)
if z.lat_deg >= 0:
record += " N "
else:
record += " S "
record += str(abs(z.lon_deg))+" "+str(z.lon_min)+" "+str(z.lon_sec)
if z.lon_deg >= 0:
record += " E "
else:
record += " W "
record += "0.00m 0.00m 0.00m 0.00m"
output.append(record)
with open(stub_output, 'w+t') as f:
f.write("\n".join(output)+"\n")
def login(username, password):
db = web.database(
dbn = 'mysql',
host = sae.const.MYSQL_HOST,
port = int(sae.const.MYSQL_PORT),
user = sae.const.MYSQL_USER,
passwd = sae.const.MYSQL_PASS,
db = sae.const.MYSQL_DB
)
mmd5 = hashlib.md5()
mmd5.update(password)
pwd = mmd5.hexdigest()
query = db.query("SELECT userid, username FROM user WHERE username = $usr AND password = $pwd", vars={'usr': username, 'pwd' : pwd})
if len(query) == 1:
for item in query:
return [True, item.userid, item.username]
else:
return [False]
def get_all_count():
result=db.query("select count(*) as sign_count from morning_sign")
count=result[0]
return count.sign_count
def POST(self):
str_xml = web.data() #获得post来的数据
xml = etree.fromstring(str_xml)#进行XML解析
#content=xml.find("Content").text#获得用户所输入的内容
msgType=xml.find("MsgType").text
fromUser=xml.find("FromUserName").text
toUser=xml.find("ToUserName").text
mc=pylibmc.Client() #初始化一个memcache实例保存用户操作
today=datetime.date.today()
if msgType == "event":
mscontent = xml.find("Event").text
if mscontent == "subscribe":
replayText = u'欢迎关注我，我是国科大校车订票系统，很高兴为您服务，输入help查看操作指令'
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if msgType == 'text':
today=datetime.date.today()
content=xml.find("Content").text
if content.lower() == 'help':
replayText = u'''1.查询余票\n2.订校车票\n3.订单信息\n4.乘车时间\n5.退校车票\n
或者点击URL 登录网页版订票'''
orderurl="schoolbusbookdemo.sinaapp.com"
replayText=replayText+orderurl
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.lower() == 'bye':
mc.delete(fromUser+'_simi')
return self.render.reply_text(fromUser,toUser,int(time.time()),u'您已退出了和小黄鸡的交谈中，输入help来显示操作指令')
if content.lower()=='simi':
mc.set(fromUser+'_simi','simi')
return self.render.reply_text(fromUser,toUser,int(time.time()),u'小黄鸡is ready，请尽情调戏吧！输入bye退出交谈')
if content.startswith('4') or content== '时间':
replayText=Timetable()
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.startswith('1') or content == '查询余票':
#curDate = time.strftime('%Y-%m-%d',time.localtime())
direction=1
#replayText=today + datetime.timedelta(days=1)
replayText=u'余票信息：\n雁栖湖至玉泉路:\n'+today.strftime("%Y-%m-%d")+u' 余 '\
+str(db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction", vars={'direction':1,'date':today.strftime("%Y-%m-%d")})[0].Remain)+'\n'\
+(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")+u' 余 '\
+str(db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction", vars={'direction':1,'date':(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")})[0].Remain)+'\n'\
+(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")+u' 余 '\
+str(db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction", vars={'direction':1,'date':(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")})[0].Remain)+'\n'\
+u'玉泉路至雁栖湖：\n' +today.strftime("%Y-%m-%d")+u' 余 '\
+str(db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction", vars={'direction':0,'date':today.strftime("%Y-%m-%d")})[0].Remain)+'\n'\
+(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")+u' 余 '\
+str(db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction", vars={'direction':0,'date':(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")})[0].Remain)+'\n'\
+(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")+u' 余 '\
+str(db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction", vars={'direction':0,'date':(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")})[0].Remain)+'\n'
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.startswith('2')or content== '订校车票':
replayText=u'''输入订票人信息：
格式“#学号#密码#手机号码”'''
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.startswith('#'):
content_list=content.split('#')
userID=content_list[1]
pwd=content_list[2]
phone=content_list[3]
replayText=userID
mc.set(fromUser+'_uID',userID)
mc.set(fromUser+'_upwd',pwd)
mc.set(fromUser+'uphone',phone)
#def validateStudent(studentid,passwd):
#res=db.query("SELECT COUNT(*) AS C FROM User WHERE userID = $id AND Password = $password",vars={'id':userID,'password':pwd})[0]
ifvalid=validateStudent(userID,pwd)
if(ifvalid<1):
replayText=u'学号或密码输入错误'
else:
replayText=u'选择订票类型：\n雁栖湖至玉泉路:\n'\
+u'D01. '+today.strftime("%Y-%m-%d")+'\n'\
+u'D02. '+(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")+'\n'\
+u'D03. '+(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")+'\n'\
+u'玉泉路至雁栖湖：\n'\
+u'D04. '+today.strftime("%Y-%m-%d")+'\n'\
+u'D05. '+(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")+'\n'\
+u'D06. '+(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")+'\n'\
+u'''PS：输入时间前的编码订票
建议先输入“1”查看“余票”
只能订两天以内车票^ ^'''
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
#mcxhj = mc.get(fromUser+'_uID')
if content.startswith('D0'):
tno=content.split('0')[1]
uID=mc.get(fromUser+'_uID')
#replayText=tno
ordirection=1
orderdate=today.strftime("%Y-%m-%d")
if(tno=='1'):
ordirection=1
orderdate=today.strftime("%Y-%m-%d")
if(tno=='2'):
ordirection=1
orderdate=(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
if(tno=='3'):
ordirection=1
orderdate=(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")
if(tno=='4'):
ordirection=0
orderdate=today.strftime("%Y-%m-%d")
if( tno=='5'):
ordirection=0
orderdate=(today + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
if(tno=='6'):
ordirection=0
orderdate=(today + datetime.timedelta(days=2)).strftime("%Y-%m-%d")
res=db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction FOR UPDATE ", vars={'direction':ordirection,'date':orderdate})[0]
curremain=res.Remain
tID=res.TicketID
tip=u'''请选择其他日期或路线
输入“2”选择订票类型^ ^'''
if(curremain <= 0):
replayText=u'余票为0'+tip
else:
ifordered=db.query("SELECT COUNT(*)AS C FROM User_Ticket WHERE UserID = $userID AND TicketID = $ticketID", vars={'userID':uID,'ticketID':tID})[0]
if ifordered['C'] >0:
replayText=u'您已经订过该时间该路线的车票'+tip
#replayText=ifordered['C']
else:
db.query("UPDATE Ticket SET Remain= $remain -1 WHERE Date = $date AND Direction = $direction", vars={'remain':curremain,'direction':ordirection,'date':orderdate})
res=db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction FOR UPDATE ", vars={'direction':ordirection,'date':orderdate})[0]
curremain=res.Remain
if(curremain<0):
db.query("UPDATE Ticket SET Remain= $remain +1 WHERE Date = $date AND Direction = $direction", vars={'remain':curremain,'direction':ordirection,'date':orderdate})
replayText=u'余票为0'+tip
else:
db.query("INSERT INTO User_Ticket (UserID,TicketID) VALUES ($userID,$ticketID)",vars={'userID':uID,'ticketID':tID})
replayText=u'订票成功，车票号为：'+tID +u'输入“detail”或“订单信息”查看订单'
#qr = qrcode.QRCode(
#version=2,
#error_correction=qrcode.constants.ERROR_CORRECT_L,
#box_size=10,
#border=1
#)
#qr.add_data(uID+"::"+orderdate+"::"+string+"::"+tID)
#qr.make(fit=True)
#img = qr.make_image()
#img.save("ticket_qrcode.png")
#replayText='abc (toUser,fromUser,createTime,replayText,title,description,picurl,url)'
title=u'订票成功，车票信息：'
description=u'出示二维码乘车'
ask=uID+'+'+orderdate+'+'+str(ordirection)+'+'+str(tID)
ask = ask.encode('UTF-8')
enask = urllib2.quote(ask)
baseurl = "http://qrcoder.sinaapp.com?t="+enask+"&f=json"
url = baseurl
resp = urllib2.urlopen(url)
#replayText=resp
reson = json.loads(resp.read())
replayText=u'订票成功，车票号为：'+tID +u'\n输入“detail”或“订单信息”查看订单,车票二维码：'+reson['url']
#api="http://qrcoder.sinaapp.com?t=hello world"
#response = urllib2.urlopen(api)
#html = response.read()
#qrcodeinfo = json.loads(html)
#picurl=qrcodeinfo['url']
#text=qrcodeinfo['text']
#url=picurl
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
#return self.render.reply_article(fromUser,toUser,int(time.time()),title,description,picurl,url)
if content.lower()=='detail': ###当前用户全部订票信息
uID=mc.get(fromUser+'_uID')
replayText=orderInfo(uID)
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.startswith('3'):
replayText=u'''查看订单，请输入订票人信息：
格式“&学号”'''
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.startswith('&'):
content_list=content.split('&')
userID=content_list[1]
replayText=orderInfo(userID)
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.startswith('5')or content== '退校车票':
replayText=u'''退票操作，请输入订单信息：
格式“@学号@密码@车票号”
'''
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
if content.startswith('@'):
content_list=content.split('@')
userID=content_list[1]
pwd=content_list[2]
tID=content_list[3]
ifvalid=validateStudent(userID,pwd)
if(ifvalid<1):
replayText=u'学号或密码输入错误'
else:
ifordered=db.query("SELECT COUNT(*)AS C FROM User_Ticket WHERE UserID = $userID AND TicketID = $ticketID", vars={'userID':userID,'ticketID':tID})[0]
if ifordered['C'] >0:
db.query("DELETE FROM User_Ticket WHERE UserID = $userID AND TicketID = $ticketID", vars={'userID':userID,'ticketID':tID})
db.query("UPDATE Ticket SET Remain= Remain +1 WHERE TicketID = $ticketID", vars={'ticketID':tID})
replayText=u'退票成功，感谢您的通知'
else:
replayText=u'没有该订单，请确认后再退票'
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
mcsimi = mc.get(fromUser+'_simi')
if mcsimi =='simi':
res = Simi(content)
replytext = res['text']
#reply_text = res['response']
#if u'微信' in reply_text:
# reply_text = u"小黄鸡脑袋出问题了，请换个问题吧~"
return self.render.reply_text(fromUser,toUser,int(time.time()),replytext)
else:
#replayText = u'输入help查看操作指令,正在开发中，敬请期待，您刚刚说'+content
replayText = u'''输入help查看操作指令
输入simi调戏小黄鸡！'''
return self.render.reply_text(fromUser,toUser,int(time.time()),replayText)
logging.debug("debug log ")
def get_ip(): # 得到所有的代理IP
return db.query("SELECT * FROM dailiip")
def delete_all_ip():
return db.query("DELETE FROM dailiip")
def get_id():
return db.query("SELECT id FROM morning_sign")
def find_user(user):
return db.query("SELECT * FROM morning_sign WHERE user=%r" % (user))
def reset():
db.query("UPDATE morning_sign SET istoday=0,timetoday=NULL,record = record*2 WHERE id >0")
def clearIstoday():
db.query("UPDATE morning_sign SET istoday=0")
def clearall():
db.query("UPDATE morning_sign SET total=0,istoday=0,timetoday=NULL,record = 0 WHERE id >0")
def get_user():
return db.query("SELECT user FROM morning_enroll")
def get_record_rank(user):
result=db.query("select count(*) as order_id from morning_sign where all_record >(select all_record from morning_sign where user='******')"%user)
item=result[0]
return item.order_id+1
def get_sign_count(user):
rank=db.query("SELECT count(*) as sign_id FROM `morning_sign`as m WHERE m.istoday=1")
result=rank[0]
return result.sign_id+1
def get_sign_name_time():
result=db.query("SELECT username,timetoday FROM `morning_sign`as m where m.istoday=1 order by timetoday limit 10")
return result
def get_rank():
result=db.query("SELECT username,all_record FROM `morning_sign` order by all_record desc limit 30")
return result
def visit_by_id(id):
db = get_db()
# return db.update(satime=dateutil.format_time(), visited_cnt=visited_cnt+1, where=dict(id=id))
sql = "update file set visited_cnt = visited_cnt + 1, satime='%s' where id = %s and visited_cnt < %s" % \
(dateutil.format_time(), id, MAX_VISITED_CNT)
return db.query(sql)
def visit_by_id(id, db=None):
sql = "UPDATE file SET visited_cnt = visited_cnt + 1, atime='%s' where id = %s " % \
(dateutil.format_time(), id)
return db.query(sql)
def validateStudent(uuid,passwd):
res=db.query("SELECT COUNT(*) AS C FROM User WHERE userID = $id AND Password = $password",vars={'id':uuid,'password':passwd})[0]
ifvalid=res['C']
return ifvalid
def getTicketcontent():
#return db.select('Ticket', order='Date')
curDate = time.strftime('%Y-%m-%d',time.localtime())
return db.query("SELECT * FROM Ticket WHERE Date = $date AND Direction = $direction", vars={'direction':1,'date':curDate})