示例#1
0
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))
示例#2
0
	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="没有这个客户")
示例#3
0
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
示例#4
0
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
示例#5
0
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))
示例#6
0
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()
示例#7
0
def clear():
    print("Limpando Dados ... ")

    with open('../sql/clear_database.sql', 'r', encoding='utf-8') as f:
        content = f.read()

        query(content)
        f.close()
示例#8
0
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))
示例#9
0
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()
示例#10
0
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
示例#11
0
	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
示例#12
0
    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
示例#13
0
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
示例#14
0
    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="数据库连结失败。请联系管理员。")	
示例#15
0
def ifcumana(cuna):
	sql="select * from cumanatable where cuna=%s"
	rec=database.query(sql,cuna)
	if len(rec)>0:
		return False
	else:
		return True
示例#16
0
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
示例#17
0
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 ""
示例#18
0
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
示例#19
0
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	
示例#20
0
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 ""
示例#21
0
	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)
示例#22
0
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 ""
示例#23
0
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 ""
示例#24
0
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
示例#25
0
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
示例#26
0
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
示例#27
0
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 ""
示例#28
0
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
示例#29
0
	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
示例#30
0
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
示例#31
0
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>"			
示例#32
0
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
示例#33
0
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
示例#34
0
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