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})