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
