def delete(friend1, friend2): """删除好友关系""" tmp = friend1 if friend1 > friend2: friend1 = friend2 friend2 = tmp database.query('delete from friends where friend1 = "%s" and friend2 = "%s"' % (friend1, friend2))
def post(self): saon=self.get_argument('saon') cuna=self.get_argument('cuna') wana=self.get_argument('wana') sape=self.get_argument("sape") mape=self.get_argument("mape") sql="insert into saoncustomertable (saon,cuna,date) values (%s,%s,%s)" sqlc="select saon from saoncustomertable where saon=%s" sqlcs="select * from customerinfotable where cuna=%s" recs=database.query(sqlcs,cuna) if recs: if not len(saon)==0: try: rec=database.query(sqlc,saon) if len(rec)>0: self.write("<center><h1>出货文件号重复。</h1></center>") elif len(rec)==0: database.execute(sql,saon,cuna,strtoday()) self.render("static/saoncustomerc.html",saon=saon,wana=wana,sape=sape,mape=mape) except: self.render("static/msg.html",msg="数据库连结失败。请联系管理员。") else: self.render("static/msg.html",msg="请输入正确的出货文件号") else:self.render("static/msg.html",msg="没有这个客户")
def confirm(friendFrom, friendTo): """好友确认""" if is_not_confirm(friendFrom, friendTo): database.query("update addFriend set state = '1' where friendFrom = '%s' and friendTo = '%s' " % (friendFrom, friendTo)) return True return False
def confirmdelievery(saon): try: cuna=database.query("select cuna from saoncustomertable where saon=%s",saon)[0]['cuna'] ds=getonetobedealwithtable(saon) l=len(ds) i=0 while i<l: #ds.ix[i]['banu'] ds.ix[i]['quan'] ds.ix[i]['wana'] sqld="delete from stocktable where banu=%s and wana=%s" sqlu="update stocktable set quan=%s where banu=%s and wana=%s" sqlq="select quan from stocktable where banu=%s and wana=%s" sqli="insert into delieverytable (saon,cuna,mana,banu,exda,past,quan,wana,sape,mape,sapr,owda,insf) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" try: cq=database.query(sqlq,ds.ix[i]['banu'],ds.ix[i]['wana'])[0]['quan'] if cq==0: database.execute(sqld,ds.ix[i]['banu'],ds.ix[i]['wana']) else: pass except: pass ###write to delieverytable database.execute(sqli,saon,cuna,ds.ix[i]['mana'],ds.ix[i]['banu'],ds.ix[i]['exda'],ds.ix[i]['past'],ds.ix[i]['quan'],ds.ix[i]['wana'],ds.ix[i]['sape'],ds.ix[i]['mape'],ds.ix[i]['sapr'],strtoday(),ds.ix[i]['insf']) i=i+1 return True except: return False
def save(friend1, friend2): """保存好友关系""" if not is_friend(friend1, friend2): if friend1 > friend2: tmp = friend1 friend1 = friend2 friend2 = tmp database.query("""insert into friends values('%s', '%s')""" % (friend1, friend2))
def create_tables(): print("Criando as Tabelas ... ") with open('../sql/create_tables.sql', 'r', encoding='utf-8') as f: content = f.read() query(content) f.close()
def clear(): print("Limpando Dados ... ") with open('../sql/clear_database.sql', 'r', encoding='utf-8') as f: content = f.read() query(content) f.close()
def save(u_name, r_name, sex, birth_year, birth_month, birth_day, school_type, school_name, school_grade): """保存用户的基本信息""" if database.is_exist('select * from baseInfo where uName = "%s" ' % u_name): database.query("""update baseInfo set realName = '%s', sex = '%s', birthYear = %d, birthMonth = %d, birthDay = %d, school_type='%c', school_name = '%s', school_grade = %d where uName = '%s'""" % (r_name, sex, birth_year, birth_month, birth_day, school_type, school_name, school_grade, u_name)) else: database.query("""insert into baseInfo values('%s', '%s', '%c', %d, %d, %d, '%s', '%s', %d) """ % (u_name, r_name, sex, birth_year, birth_month, birth_day, school_type, school_name, school_grade))
def import_data(): print("Importando Dados ... ") generate_import_file() with open('../sql/import_data.sql', 'r', encoding='utf-8') as f: content = f.read() query(content) f.close()
def getfrarall(): sqln="select frar from normalfreighttable" sqld="select frar from dangerfreighttable" rsl=[] recn=database.query(sqln) for recno in recn: rsl.append(recno) recd=database.query(sqld) for recdo in recd: rsl.append(recdo) return rsl
def post(self): startd=self.get_argument('startd') stopd=self.get_argument('stopd') rqkw=self.get_argument("rqkw") rqop=self.get_argument("rqop") try: startd=startd.split("-")[0]+startd.split("-")[1]+startd.split("-")[2] stopd=stopd.split("-")[0]+stopd.split("-")[1]+stopd.split("-")[2] except: startd=strtoday()[0:5]+"0101" stopd=str(int(strtoday()[0:5])+1)+"0101" if rqop=="puon": kpinu="%"+rqkw+"%" rec=database.query("select * from putinwhtable where puon like %s and ewda > %s and ewda < %s",kpinu,startd,stopd) if len(rec)>0: df=pd.DataFrame(rec) amount=(df["past"]*df["quan"]).sum() df=df.set_index("id") df=df.reset_index(drop=True) ldf=df.index.tolist() self.render("static/rqpiresult.html",df=df,ldf=ldf) else: self.render("static/msg.html",msg="查询结果为空") elif rqop=="banu": kbanu="%"+rqkw+"%" rec=database.query("select * from putinwhtable where banu like %s and ewda > %s and ewda < %s",kbanu,startd,stopd) if len(rec)>0: df=pd.DataFrame(rec) amount=(df["past"]*df["quan"]).sum() df=df.set_index("id") df=df.reset_index(drop=True) ldf=df.index.tolist() self.render("static/rqpiresult.html",df=df,ldf=ldf) else: self.render("static/msg.html",msg="查询结果为空") elif rqop=="mana": kmana="%"+rqkw+"%" rec=database.query("select * from putinwhtable where mana like %s and ewda > %s and ewda < %s",kmana,startd,stopd) if len(rec)>0: df=pd.DataFrame(rec) amount=(df["past"]*df["quan"]).sum() df=df.set_index("id") df=df.reset_index(drop=True) ldf=df.index.tolist() self.render("static/rqpiresult.html",df=df,ldf=ldf) else: self.render("static/msg.html",msg="查询结果为空") else: pass
def post(self): kword=self.get_argument('kword') waname=self.get_argument('wana') opkw=self.get_argument('opkw') if opkw=="banu": nameq="%"+kword+"%" sql="select * from stocktable where banu like %s order by mana asc, past asc,right(exda,4) asc ,left(exda,2) asc" rec=database.query(sql,nameq) if len(rec): dfda=rectodataframe(rec) #dfd=dfda.sort_values(by='exda'.decode('utf-8'),ascending=True) dfd=dfda if len(dfd): if not waname=="all": dfw=dfd[dfd.wana==waname] else:dfw=dfd df=dfw.reset_index(drop=True) quantity=(df.quan*df.past).sum() amount=df.quan.sum() ldf=df.index.tolist() self.render("static/rqstockresult.html",df=df,quantity=quantity,amount=amount,ldf=ldf) else:self.render("static/msg.html",msg="您查询的结果为空.") else:self.render("static/msg.html",msg="您查询的结果为空.") elif opkw=="mana": nameq="%"+kword+"%" sql="select * from stocktable where mana like %s order by mana , past asc,right(exda,4) asc ,left(exda,2) asc" rec=database.query(sql,nameq) if len(rec): dfda=rectodataframe(rec) #dfd=dfda.sort_values(by='exda'.decode('utf-8'),ascending=True) dfd=dfda if len(dfd): if not waname=="all": dfw=dfd[dfd.wana==waname] else:dfw=dfd df=dfw.reset_index(drop=True) quantity=(df.quan*df.past).sum() amount=df.quan.sum() ldf=df.index.tolist() self.render("static/rqstockresult.html",df=df,quantity=quantity,amount=amount,ldf=ldf) else:self.render("static/msg.html",msg="您查询的结果为空.") else:self.render("static/msg.html",msg="您查询的结果为空.") else:pass
def gettobetransinfo(banu,wana): sql="select * from stocktable where banu=%s and wana=%s" rs=database.query(sql,banu,wana) if len(rs)>0: df=function.pd.DataFrame(rs) else:df='' return df
def post(self): saon=self.get_argument('saon') cuna=self.get_argument('cuna') cunaq="%"+cuna+"%" sql="select cuna from customerinfotable where cuna like %s " sl=getsaleslist() al=getasisterlist() try : rec=database.query(sql,cunaq) if len(rec)==1: rcuna=rec[0]["cuna"] self.render("static/selectcustomerconfirm.html",saon=saon,cuna=rcuna,sl=sl,al=al) elif len(rec)>1: l=len(rec) i=0 lc=[] while i<l: lc.append(rec[i]["cuna"]) i=i+1 self.render("static/selectcustomerm.html",saon=saon,cuna=lc) else: self.render("static/selectcustomer.html",cuna="没有这种客户,请重新输入。") except: self.render("static/msg.html",msg="数据库连结失败。请联系管理员。")
def ifcumana(cuna): sql="select * from cumanatable where cuna=%s" rec=database.query(sql,cuna) if len(rec)>0: return False else: return True
def detailstock(): rec=database.query("select mana from stocktable") df=pd.DataFrame(rec) manal=df["mana"].drop_duplicates().tolist() rl=[] ###detail stock list stocklist=eachstockname() stock=[] for sl in stocklist: sd={} es=eachstock(sl) sd[sl]=es stock.append(sd) ##each mana for ml in manal: dml={} dml["mana"]=ml ##eachmana detail in eachstock for estock in stock: stockname=estock.keys()[0] sndf=estock.values()[0] sndfml=sndf[sndf.mana==ml] if len(sndfml)>0: dml[stockname]=(sndfml.quan*sndfml.past).sum() else: dml[stockname]=0 rl.append(dml) return rl
def recstock(mana,past,quan,wana): mana=mana past=past quan=quan wana=wana manaq="%"+mana+"%" sql="select * from stocktable where mana like %s order by right(exda,4) asc ,left(exda,2) asc" rec=database.query(sql,manaq) if len(rec): dfda=rectodataframe(rec) dfd=dfda[(dfda.wana==wana)&(dfda.past==past)&(dfda.quan>0)] #dfw=dfd.sort_values(by='exda'.decode('utf-8'),ascending=True) dfw=dfd df=dfw.reset_index(drop=True) qs=(df.quan * df.past).sum() if qs<quan: return "" else: aloc=findloc(df["quan"],divmod(quan,past)[0]) ax=aloc[0] dfa=df.head(ax+1) dfa=df.head(ax+1) dfa=dfa.reset_index(drop=True) dfa["quan"][ax]=aloc[1] return dfa else: return ""
def undodelievery(saon): ds=getonetobedealwithtable(saon) l=len(ds) i=0 ewda=strtoday() try: while i<l: #ds.ix[i]['banu'] ds.ix[i]['quan'] ds.ix[i]['wana'] sqlu="update stocktable set quan=%s where banu=%s and wana=%s" sqlq="select quan from stocktable where banu=%s and wana=%s" sqli="insert into stocktable (wana,mana,banu,exda,past,quan,pagw,ewda,pana) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)" try: cq=database.query(sqlq,ds.ix[i]['banu'],ds.ix[i]['wana'])[0]['quan'] if cq==0: database.execute(sqlu,ds.ix[i]['quan'],ds.ix[i]['banu'],ds.ix[i]['wana']) else: cq=cq+ds.ix[i]['quan'] database.execute(sqlu,cq,ds.ix[i]['banu'],ds.ix[i]['wana']) except: database.execute(sqli,ds.ix[i]['wana'],ds.ix[i]['mana'],ds.ix[i]['banu'],ds.ix[i]['exda'],ds.ix[i]['past'],ds.ix[i]['quan'],ds.ix[i]['pagw'],ewda,'公斤/桶'.decode('utf8')) i=i+1 database.execute("delete from tobedealwithtable where saon=%s",saon) database.execute("delete from insurancepremiumtable where saon=%s",saon) database.execute("delete from freighttable where saon=%s",saon) database.execute("delete from saoncustomertable where saon=%s",saon) return True except: return False
def getasisterlist(): sqls="select username from namepwd where level=%s" rsl=[] recn=database.query(sqls,"as") for recno in recn: rsl.append(recno) return rsl
def recstockb(banu,past,quan,wana): #get banu,past,quan and wana infomation. banu=banu past=past quan=quan wana=wana #select all banu (in fact will only one banu in each wana),order by expire shirt to long. sql="select * from stocktable where banu=%s and wana=%s and past=%s order by right(exda,4) asc ,left(exda,2) asc" rec=database.query(sql,banu,wana,past) if len(rec): dfda=rectodataframe(rec) dfd=dfda[dfda.quan>0] dfw=dfd.sort_values(by='exda'.decode('utf-8'),ascending=True) df=dfw.reset_index(drop=True) qs=df.quan.sum() if qs<quan: return "" else: aloc=findloc(df["quan"],quan) ax=aloc[0] dfa=df.head(ax+1) dfa=df.head(ax+1) dfa=dfa.reset_index(drop=True) dfa["quan"][ax]=aloc[1] return dfa else: return ""
def get(self): sql="select * from namepwd" dr=database.query(sql) df=pd.DataFrame(dr) df=df.reset_index(drop=True) ldf=df.index.tolist() self.render("static/viewunresult.html",df=df,ldf=ldf)
def databasetabletodataframe2(a,b): sql="select * from "+a+" where cuna like %s" rec=database.query(sql,b) if len(rec)>0: df=pd.DataFrame(rec) dfd=df.set_index("id") return dfd else: return ""
def databasetabletodataframe(a): sql="select * from "+a rec=database.query(sql) if len(rec)>0: df=pd.DataFrame(rec) dfd=df.set_index("id") return dfd else: return ""
def tobeconfirmdelieverydataframe(saon): saon=saon sqlm="select * from tobedealwithtable where saon=%s" recm=database.query(sqlm,saon) drecm=pd.DataFrame(recm) drecm=drecm.set_index("id") drecm=drecm.reset_index(drop=True) return drecm
def getnewwanaquan(banu,nwana): sql="select * from stocktable where banu=%s and wana=%s" rs=database.query(sql,banu,nwana) if len(rs)>0: df=function.pd.DataFrame(rs) nquan=df.quan[0] else: nquan=-1 return nquan
def undo(saon): sql="select * from delieverytable where saon=%s" rec=database.query(sql,saon) if len(rec)>0: df=function.pd.DataFrame(rec) return df[['wana','mana','banu','exda','past','quan']] else: df="" return df
def exdarq(n): dy=n[0:4] dm=n[4:6] sql="select * from stocktable where (left(exda,2)<=%s and right(exda,4)<=%s ) or (right(exda,4)<%s) order by mana, right(exda,4) asc ,left(exda,2) asc" rec=database.query(sql,dm,dy,dy) if len(rec)>0: return rec else: return ""
def findpagw(banu,past): sql="select * from putinwhtable where banu=%s and past=%s" rs=database.query(sql,banu,past) if len(rs)>0: df=function.pd.DataFrame(rs) pagw=df.pagw[0] else: pagw=-1 return pagw
def newfunc(self): username=self.get_secure_cookie('speedfairuser') rec=database.query("select level from namepwd where username=%s",username) level=rec[0]['level'] if level =="L": f(self) else:self.render("static/msg.html",msg="超出操作权限") return f
def partdelieverydetail(saon): sql="select * from tobedealwithtable where saon=%s order by mana asc,past asc" if database: pdd=database.query(sql,saon) pdddf=pd.DataFrame(pdd) pdddf=pdddf.reset_index(drop=True) #ldf=pdddf.index.tolist()#提取索引并转换成list else: self.write("can't connect with databse") return pdddf
def getinfotobedealwithtable(): try: rec=database.query("select * from tobedealwithtable") d=pd.DataFrame(rec) #df=d.set_index("id") df=d.drop(["id"],axis=1) df=df.reset_index(drop=True) return df except: return "<center><h1>数据库连结失败。请联系管理员。</h1></center>"
def display_results(DOC_ROOT, userid, source): ''' Display temp and hum graph for source belonging to user. Args: DOC_ROOT (str): Path to project directory userid (str): Unique id for user source (int): Source number Returns: template (str): Updated HTML template ''' # query last 24 hours and save graphs queryResults = db.query(DOC_ROOT, userid, source, 0, 23) temp = [i[0] for i in queryResults] hum = [i[1] for i in queryResults] # If no data was retrieved, go to 404 page if len(temp) == 0 or len(hum) == 0: with open(DOC_ROOT+"/frontend/html/404.template.html",'r') as descriptor: return descriptor.read() db.save_graph(DOC_ROOT, range(24), temp, hum, userid, source) # Modify html file with open(DOC_ROOT+"/frontend/html/display_results.template.html",'r') as descriptor: template = descriptor.read() # Get user + source specific files temp_path = '/tmp_files/'+userid+'_'+source+'_temp.png' hum_path = '/tmp_files/'+userid+'_'+source+'_hum.png' # Get label of source label = db.get_label(DOC_ROOT, userid, source) ### Find and Replace HTML variables # Check that images exist if not os.path.isfile(DOC_ROOT + temp_path) or not os.path.isfile(DOC_ROOT + hum_path): print('ERROR: Image path does not exist!') return template; # Check if variables in html file find_temp = template.find("{{temperature.png}}") find_hum = template.find("{{humidity.png}}") find_label = template.find("{{source_label}}") if find_temp!=-1 and find_hum!=-1 and find_label!=-1: # Replace variables template = template.replace("{{temperature.png}}", temp_path) template = template.replace("{{humidity.png}}", hum_path) template = template.replace("{{source_label}}", label) else: print("ERROR: HTML variable missing!") return template
def getcustomerallmana(cuna): sql="select mana from delieverytable where cuna=%s" rec=database.query(sql,cuna) ltep=[] if len(rec)>0: df=pd.DataFrame(rec) dfl=df["mana"].tolist() dflt=list(set(dfl)) return dflt else: return ltep
def getrecentprice(cuna,mana): sql='select * from delieverytable where cuna=%s and mana=%s order by owda desc' rec=database.query(sql,cuna,mana) list=[] if len(rec)>0: df=pd.DataFrame(rec) insf=df.head(1).insf[0].encode("utf-8") sapr=df.head(1).sapr[0].encode("utf-8") list.append(insf) list.append(sapr) return list else: return list