def sign():
#numImages=int(request.query.numImages)
userID=request.query.userID
token='\''+request.query.token+'\''
sql="select * from Token where userID =%s and generatedToken=%s" % (userID,
token)
#return sql
result=mysql.query(sql,1)
if not result:
return json.dumps({"status":"wrong"})
params={}
params["public_id"]=uuid.uuid4().hex[:-7]+produceFileID()
#for i in range(0, numImages):
# params["public_ids"][i]=uuid.uuid4().hex[:-7]+produceFileID()
params["timestamp"] = now()
api_key="739771188655879"
api_secret= "jXP-X6O9aK_FnZEWe8viRLVQXyU"
#global params
params = imageServer.cleanup_params(params)
params["signature"] = imageServer.api_sign_request(params, api_secret)
params["api_key"] = api_key
sql="insert into photos(userId, link) values('%s', '%s')"%(userID, params["p
ublic_id"])
mysql.query(sql,0)
return params
def searchGym():
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
userID=data['userID']
token=data['token']
sql="select * from Token where userID ='%s' and generatedToken='%s'" %(userI
D, token)
#return sql
response={}
result=mysql.query(sql,1)
if not result:
response['status']="wrong password or username"
return response
latitude=data['latitude']
longtitude=data['longtitude']
sql="""SET @lat = '%s';SET @lon = '%s'; """%(latitude,longtitude)
mysql.query(sql,0)
sql="""select * from location join
(SELECT id, (6371 * acos( cos( radians(latitude) ) * cos( radians( @lat ) )
*
cos( radians( @lon ) - radians(longtitude) ) + sin( radians(latitude) ) * si
n( radians( @lat ) ) ) ) as distance
FROM location
where latitude between @lat-1 and @lat+1
and longtitude between @lon-1 and @lon+1
having distance<50) as dis
on location.id=dis.id"""
response['locations']=mysql.query(sql,2)
return json.dumps(response)
def create():
response={}
try:
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
#data=json.load(request.body.read().decode('utf8'))
firstName='\''+data['firstName']+'\''
lastName='\''+data['lastName']+'\''
nickName='\''+data['nickName']+'\''
telephone='\''+data['telephone']+'\''
email='\''+data['email']+'\''
userPass='******''+utils.md5(data['userPass'])+'\''
sql="insert into User (firstName, lastName, nickName, telephone,email,us
erPass) values(%s, %s,%s, %s,%s, %s)" %(firstName,lastName,nickName,telephone,em
ail, userPass)
userID=mysql.query(sql,0)[0]
#print (userID)
token=uuid.uuid4().hex
response['userID']=userID
response['token']=token
userIDstr='\''+str(userID)+'\''
token='\''+token+'\''
sql="insert into Token (created_at, userID, generatedToken) values (TIME
STAMPADD(MONTH,1,CURRENT_TIMESTAMP), %s, %s)" % (userIDstr, token)
mysql.query(sql,1)
def all_alert_history(since=None):
'''
Get all alerts.
'''
if since == None:
return Mysql.query('''SELECT * FROM alerts_history ORDER BY id DESC''', "alerts")
else:
return Mysql.query('''SELECT * FROM alerts_history WHERE createDate BETWEEN DATE_SUB(CURDATE(),INTERVAL %s DAY) AND DATE_SUB(CURDATE(),INTERVAL -1 DAY) ORDER BY id DESC''' % (int(since)), "alerts")
def all_users(since=None):
'''
Get all users from the db.
'''
if since == None or since == 0:
return Mysql.query('''SELECT * FROM users ORDER BY id DESC''', "users")
else:
return Mysql.query('''SELECT * FROM users WHERE id > %s ORDER BY id DESC''' % (since), "users")
def all_teams(id=None):
'''
Get all teams from the db.
'''
if id == None or id == 0:
return Mysql.query('''SELECT * FROM teams ORDER BY id DESC''', "teams")
else:
return Mysql.query('''SELECT * FROM teams WHERE id > %s ORDER BY id DESC''' % (id), "teams")
def all_alerts(since=None):
'''
Get current alerts. This means the current status for each environment/colo/host/service.
'''
if since == None or since == 0:
return Mysql.query('''SELECT * FROM alerts ORDER BY id DESC''', "alerts")
else:
return Mysql.query('''SELECT * FROM alerts WHERE id > %s ORDER BY id DESC''' % (since), "alerts")
def getCoachInfo(id):
response={}
sql="select * from trainerPlaces join location where trainerPlaces.gymID=location.id and trainerPlaces.trainerID='%s'" %(id)
response['locations']=mysql.query(sql,2)
sql="select * from reviews where toUserID='%s'" %(id)
response['reviews']=mysql.query(sql,2)
sql="select * from photos where userID='%s'" %(id)
response['images']=mysql.query(sql,2)
sql="select * from TrainerTime where trainerID='%s'" %(id)
response['timeslots']=str(mysql.query(sql,2))
return json.dumps(response)
def paging_active(team=None):
'''
All active paging alerts
'''
if team == None:
return Mysql.query('''SELECT * FROM alerts WHERE status > 0 and ack != true and tags REGEXP '(page|^page,|,page,|,page$)' ORDER BY id DESC''', "alerts")
else:
all_alerts = Mysql.query('''SELECT * FROM alerts WHERE status > 0 and ack != true and tags REGEXP '(%s|^%s,|,%s,|,%s$)(=?page|^page,|,page,|,page$)' ORDER BY id DESC''' % (team,team,team,team), "alerts")
team_alerts = []
for a in all_alerts:
if team in a.tags.split(','):
team_alerts.append(a)
return team_alerts
def postMessage():
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
userID=data['userID']
token=data['token']
sql="select * from Token where userID ='%s' and generatedToken='%s'" % (userID, token)
result=mysql.query(sql,1)
if not result:
return json.dumps({"status":"wrong"})
toUserID=data['toUserID']
message=data['message']
sql="insert into messages (fromUserID, toUserID, content) values('%s','%s','%s')"%(userID,toUserID,message)
mysql.query(sql,0)
return json.dumps({"status":"message inertion successful"})
def notify():
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
userID=data['userID']
token=data['token']
sql="select * from Token where userID ='%s' and generatedToken='%s'" % (userID, token)
result=mysql.query(sql,1)
if not result:
return json.dumps({"status":"wrong"})
toUserID=data['toUserID']
message=data['message']
sql="select regID from androidRegID where userID ='%s' " %(toUserID);
regID=mysql.query(sql,1)
utils.push(regID, message)
def invest(self):
index = self.tableWidget.currentRow()
if index < 0:
QMessageBox.information(self, "温馨提示", "请先选择充值的账户", QMessageBox.Yes)
return
value, ok = QInputDialog.getInt(self, "正在充值", "请输入充值金额:", 0, 0, 10000, 10)
if ok:
reply = MyQMessageBox("充值确认",
"正在给 " + self.tableWidget.item(index, 1).text() + " 充值" + str(
value) + '元，是否充值',
'确认', '取消')
if reply == 16384:
person = mysql.query('user_tb', '_id', self.tableWidget.item(index, 0).text())
balance = float(person[0][4]) + value
mysql.update(self.tableWidget.item(index, 0).text(), 'balance', str(balance))
QMessageBox.information(self, "温馨提示", "充值成功", QMessageBox.Yes)
bills.bill_in(str(value), person[0][1], person[0][2], person[0][3], balance,
self.tableWidget.item(index, 0).text())
if self.is_card_search:
self.search_data_by_card(str(person[0][3]))
elif self.is_search:
self.search_data()
else:
self.load_initial_data(self.tableWidget.verticalScrollBar().value())
self.load_initial_bill()
self.load_initial_bill()
def post(self):
pid = self.get_argument('order')
pid = int(pid)
sql = 'select order_history.uid as uid,diet.name as name,num,order_history.price as price,cook_history.fid as cook,cash_history.fid as cashier,status from cash_history,order_history,cook_history,diet where cash_history.uid=order_history.uid and order_history.uid=cook_history.uid and order_history.did=diet.did and order_history.pid=%s' % pid
result = mysql.query(sql)
print result
result2 = mysql.get_all('faculty')
faculty = {}
for one in result2:
faculty[one['fid']] = one['name']
total = 0
for one in result:
one['price'] = one['num'] * one['price']
one['cook'] = faculty[one['cook']]
one['cashier'] = faculty[one['cashier']]
if one['status'] == 'success':
total += one['price']
result.append({
'name': '',
'num': 'all',
'price': total,
'cook': '',
'cashier': '',
'status': ''
})
response = {'status': 'ok', 'pid': pid, 'items': result}
self.write(json_encode(response))
def post(self):
sql = 'select faculty.fid, name, role, passwd from faculty, password where faculty.fid = password.fid'
result = mysql.query(sql)
result.sort(key=lambda x: x['fid'])
#print result
response = {'status': 'ok', 'workers': result}
self.write(json_encode(response))
def get_order(time_from, time_to):
if not isinstance(time_from, float) or not isinstance(time_to, float):
return None
sql = 'select did, sum(num) as n from order_history where stamp >= %s and stamp < %s group by did order by did' % (
time_from, time_to)
cursor = mysql.query(sql)
res = []
total = 0
for row in cursor.fetchall():
did = row[0]
num = row[1]
t = filter(lambda x: x['did'] == did, data.diet)
if len(t) != 1:
continue
item = t[0]
total += item['price'] * num
res.append({
'did': did,
'name': item['name'],
'sum': num,
'price': item['price'],
'total': item['price'] * num
})
for item in res:
item['percentage'] = '%.2f' % (item['total'] * 100 / total)
res.sort(key=lambda x: x['did'])
return res
def get_feedback(time_from, time_to):
if not isinstance(time_from, float) or not isinstance(time_to, float):
return None
sql = 'select did, fb, sum(num) from feedback where stamp >= %s and stamp < %s group by did, fb' % (
time_from, time_to)
cursor = mysql.query(sql)
res = map(
lambda x: {
'did': x['did'],
'name': x['name'],
'num': 0,
'good': 0,
'normal': 0,
'bad': 0
}, data.diet)
for row in cursor.fetchall():
did = row[0]
fb = row[1]
num = row[2]
t = filter(lambda x: x['did'] == did, res)
if len(t) != 1:
continue
item = t[0]
item['num'] += num
if fb == -1:
item['bad'] = num
elif fb == 0:
item['normal'] = num
elif fb == 1:
item['good'] = num
res.sort(key=lambda x: x['did'])
return res
def one_cook_flow(fid, start, end):
t1 = time.mktime(start.timetuple())
t2 = time.mktime(end.timetuple())
#print 't1, t2:', t1, ',', t2
result = {}
sql = 'select diet.did, sum(num) as number from diet,order_history,cook_history where diet.did = order_history.did and order_history.uid = cook_history.uid and fid = "%s" and cook_history.stamp > %s and cook_history.stamp < %s group by diet.did' % (
fid, t1, t2)
rows = mysql.query(sql)
diet = logic.diet
for row in rows:
did = row['did']
result[did] = {'did': did, 'num': row['number']}
for k, v in diet.items():
if k in result:
result[k]['name'] = v['name']
else:
result[k] = {'did': k, 'name': v['name'], 'num': 0}
############################################################
sql = 'select diet.did, sum(num) as number, fb from diet,order_history,cook_history,feedback where diet.did = order_history.did and order_history.uid = cook_history.uid and cook_history.uid = feedback.uid and fid = "%s" and cook_history.stamp > %s and cook_history.stamp < %s group by diet.did,fb' % (
fid, t1, t2)
rows = mysql.query(sql)
for row in rows:
did = row['did']
num = row['number']
if row['fb'] == -1:
result[did]['bad-num'] = num
elif row['fb'] == 0:
result[did]['normal-num'] = num
elif row['fb'] == 1:
result[did]['good-num'] = num
for k, v in result.items():
if 'good-num' not in v:
v['good-num'] = 0
if 'normal-num' not in v:
v['normal-num'] = 0
if 'bad-num' not in v:
v['bad-num'] = 0
v['fb-num'] = v['good-num'] + v['normal-num'] + v['bad-num']
if v['fb-num'] == 0:
v['good-rate'] = 0
v['bad-rate'] = 0
else:
v['good-rate'] = v['good-num'] * 100 / float(v['fb-num'])
v['bad-rate'] = v['bad-num'] * 100 / float(v['fb-num'])
result = result.values()
result.sort(key=lambda x: x['did'])
return result
def search_bill(self, id=None, card=None):
if id:
id = mysql.query('bill_tb', 'id', id)
elif card:
id = mysql.query('bill_tb', 'card', card)
else:
line = self.lineEdit.text()
if not line:
QMessageBox.information(self, "温馨提示", "请输入姓名、手机号或卡号（支持模糊搜索）", QMessageBox.Yes)
return
id = mysql.like('bill_tb', 'name', line) + mysql.like('bill_tb', 'card', line)
if len(line) >= 3:
id += mysql.like('bill_tb', 'phone', line)
if not id:
QMessageBox.information(self, "温馨提示", "未能找到对应的姓名、手机号或卡号", QMessageBox.Yes)
return False
else:
for i in range(self.tableWidget_2.rowCount()):
self.tableWidget_2.removeRow(0)
for row in id:
inx = id.index(row)
self.tableWidget_2.insertRow(inx)
for i in range(len(row)):
item = QTableWidgetItem()
if str(row[i]).isdigit: # 使得数字排序能够正常的运行
item.setData(Qt.DisplayRole, row[i])
else:
item.setText(row[i])
item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled) # 无法编辑
item.setTextAlignment(Qt.AlignCenter)
if row[i] == '充值':
item.setForeground(QBrush(QColor(50, 205, 116)))
elif row[i] == '消费':
item.setForeground(QBrush(QColor(233, 96, 58)))
elif row[i] == '修改':
item.setForeground(QBrush(QColor(0, 0, 255)))
self.tableWidget_2.setItem(inx, i, item)
del item
self.tableWidget_2.sortItems(0, Qt.DescendingOrder)
self.is_search = True
self.horizontalFrame_2.hide()
return True
def get_team_names():
'''
Return an array of all team names
'''
names = []
teams = Mysql.query('''SELECT * FROM teams ORDER BY id DESC''', "teams")
for team in teams:
names.append(team.name)
return names
def get_user_names():
'''
Return an array of all user names
'''
names = []
users = Mysql.query('''SELECT * FROM users ORDER BY id DESC''', "users")
for user in users:
names.append(user.name)
return names
def check_dependencies(self):
'''
Checks to see if the dependencies of this alert are in ok status or not. Return true if all are OK
'''
alerts = Mysql.query('''SELECT id from alerts where ack = false and status != 0 and (environment = "%s") and (colo = "" or colo = "%s") and (host = "" or host = "%s") and service = ""''' % (self.environment, self.colo, self.host), 'alerts')
if len(alerts) > 0:
return False
else:
return True
def add_person(self):
name = self.lineEdit.text().strip()
phone = self.lineEdit_1.text().strip()
card = self.lineEdit_2.text().strip()
balance = self.doubleSpinBox.text() or 0
type = self.lineEdit_3.text().strip()
if phone != '' and mysql.query_str('user_tb', 'phone', phone):
QMessageBox.information(self, "温馨提示", "手机号已存在。", QMessageBox.Yes)
return
if card == '':
QMessageBox.information(self, "温馨提示", "请填写卡号", QMessageBox.Yes)
return
if not card.isdigit():
QMessageBox.information(self, "温馨提示", "请填写数字", QMessageBox.Yes)
return
if mysql.query('user_tb', 'card', card):
QMessageBox.information(self, "温馨提示", "卡号已存在。", QMessageBox.Yes)
return
mysql.add_person(name, phone, card, balance, type)
id = mysql.query('user_tb', 'card', card)
if id:
bills.person_in(str(balance), name, phone, card, balance, id[0][0])
else:
QMessageBox.information(self, "温馨提示", "请至少填写卡号或手机号", QMessageBox.Yes)
return
self.lineEdit.clear()
self.lineEdit_1.clear()
self.lineEdit_2.clear()
self.doubleSpinBox.setValue(0)
self.lineEdit_3.clear()
self.lineEdit.setFocus()
max_card = mysql.max('bill_tb')[0][0] or 0
self.lineEdit_2.setPlaceholderText('当前最高卡号：' + str(max_card) + '，推荐填' + str(int(max_card) + 1))
AddWindow.close()
MainWindow.search_data_by_card(card)
MainWindow.check_page_data()
MainWindow.load_initial_bill()
def load(self, id):
'''
Load an alert with a specific id.
'''
logging.debug("Loading alert: %s" % id)
try:
self.__dict__.update(Mysql.query('''SELECT * FROM alerts_history WHERE id = %s LIMIT 1''' % (id), "alerts")[0].__dict__)
self.status = to_int_status(self.status)
except Exception, e:
Util.strace(e)
return False
def load(self, id):
'''
load a user with a specific id
'''
logging.debug("Loading user: %s" % id)
try:
self.__dict__.update(Mysql.query('''SELECT * FROM users WHERE id = %s LIMIT 1''' % (id), "users")[0].__dict__)
except Exception, e:
Util.strace(e)
self.id = 0
return False
def get_user_by_phone(phone):
'''
Load user by their phone number (pattern matching by 'ends with')
'''
try:
x = Mysql.query('''SELECT * FROM users WHERE phone LIKE '%%%s' LIMIT 1''' % phone, "users")
if len(x) == 0: return False
return x[0]
except Exception, e:
logging.error(e.__str__())
return False
def get_alerts_with_filter(filt,sort,limit, offset=0, table="alerts", count=False):
'''
This returns a list of alerts that meets the filter supplied
SECURITY RISK: MYSQL INJECTION
'''
if count == True:
query = "SELECT COUNT(id) as count FROM %s " % table
else:
query = "SELECT * FROM %s " % table
if len(filt) > 0:
query += " WHERE "
search_terms = []
for ft in filt:
key = ft.split(':')[0].strip()
value = ft.split(':')[1].strip()
if value[0] == '-':
negative = True
value = value[1:]
else:
negative = False
if key == "tags":
if negative:
isnot = "NOT "
else:
isnot = ""
search_terms.append("tags %sREGEXP '(%s|^%s,|,%s,|,%s$)'" % (isnot,value,value,value,value))
elif key == "message":
if negative:
search_terms.append("message NOT LIKE '%s'" % (value))
else:
search_terms.append("message LIKE '%s'" % (value))
else:
if key == "status":
value = to_int_status(value)
if negative:
search_terms.append("%s != '%s'" % (key, value))
else:
search_terms.append("%s = '%s'" % (key, value))
query += ' and '.join(search_terms)
if sort == "ASC" or sort == "oldest" or sort == "old":
query += " order by createDate ASC"
else:
query += " order by createDate DESC"
if limit is not None and limit > 0:
query += " limit %s, %s" % (offset, limit)
if count == True:
return Mysql.rawquery(query)
else:
return Mysql.query(query,'alerts')
def login():
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
email=data['email']
userPass=utils.md5(data['userPass'])
sql="select * from User where email='%s' and userPass='******'" %(email, userPass)
#return sql
response={}
result=mysql.query(sql,1)
if result:
response['status']="login successful"
else:
response['status']="wrong password or username"
return response
def tokenLogin():
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
userID=data['userID']
token=data['token']
sql="select * from Token where userID ='%s' and generatedToken='%s'" %(userID, token)
#return sql
response={}
result=mysql.query(sql,1)
if result:
response['status']="login successful"
else:
response['status']="wrong password or username"
return response
def submit(self):
reply = QMessageBox.information(self, "消费确认",
self.name + "消费" + str(self.cost) + '元？', QMessageBox.Yes | QMessageBox.No)
if reply == 16384:
person = mysql.query('user_tb', '_id', self.id)
balance = float(person[0][4]) - self.cost
if balance < 0:
QMessageBox.information(self, "温馨提示", "余额不足，请充值", QMessageBox.Yes)
else:
mysql.update(self.id, 'balance', str(balance))
QMessageBox.information(self, "温馨提示", "消费成功", QMessageBox.Yes)
bills.bill_out(str(self.cost), str(person[0][1]), str(person[0][2]), str(person[0][3]), balance,
self.id)
MainWindow.load_initial_data()
MainWindow.load_initial_bill()
self.close()
def load(self, id):
'''
Load a rule with a specific id.
'''
logging.debug("Loading rule: %s" % id)
try:
self.__dict__.update(Mysql.query('''SELECT * FROM inbound_rules WHERE id = %s LIMIT 1''' % (id), 'inbound_rules')[0].__dict__)
if isinstance(self.status, str) and len(self.status) > 1:
if self.status.upper() == "OK":
self.status = 0
elif self.status.upper() == "WARNING":
self.status = 1
elif self.status.upper() == "CRITICAL":
self.status = 2
else:
self.status = 3
except Exception, e:
Util.strace(e)
return False
def addLocation():
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
phoneNumber=data['phoneNumber']
website=data['website']
name=data['name']
placeType=data['placeType']
address=data['address']
city=data['city']
zipcode=data['zipcode']
state=data['state']
latitude=data['latitude']
longtitude=data['longtitude']
sql="insert into location(name, website,phoneNumber,placeType, address, city,zipcode,state,latitude,longtitude) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(name, website,phoneNumber,placeType, address, city,zipcode,state,latitude,longtitude)
result=mysql.query(sql,0)
if result:
return ("successful")
else:
return ("server error")
def get_cook_range(fid):
sql = 'select did from cook_do where fid = "%s"' % fid
dids = mysql.query(sql)
all = False
for one in dids:
if one['did'] == 'all':
all = True
break
result = []
if all:
result.append({'did': 'all', 'name': 'all', 'cid': ''})
else:
for one in dids:
item = logic.diet.get(one['did'])
result.append({
'did': one['did'],
'name': item['name'],
'cid': item['cid']
})
return result
def get_cookinfo(time_from, time_to):
r = re.compile(r'^([0-9]{4})\.([0-9]{2}|[0-9])\.([0-9]{2}|[0-9])$')
if not r.match(time_from) or not r.match(time_to):
return None
time_from = util.strtostamp(time_from)
time_to = util.strtostamp(time_to)
sql = 'select fid, did, sum(num) from cook_history where stamp >= %s and stamp < %s group by fid, did' % (
time_from, time_to)
cursor = mysql.query(sql)
res = filter(lambda x: x['role'] == 'cook', data.faculty)
res = map(
lambda x: {
'fid': x['fid'],
'name': x['name'],
'num': 0,
'num_per': 0,
'total': 0,
'total_per': 0
}, res)
num_total = 0
total_total = 0
for row in cursor.fetchall():
fid = row[0]
did = row[1]
num = row[2]
d = filter(lambda x: x['did'] == did, data.diet)
item = filter(lambda x: x['fid'] == fid, res)
if len(d) != 1 or len(item) != 1:
continue
d = d[0]
item = item[0]
item['num'] += num
item['total'] += num * d['price']
num_total += num
total_total += num * d['price']
for row in res:
row['num_per'] = '%.2d' % (row['num'] * 100 / num_total)
row['total_per'] = '%.2d' % (row['total'] * 100 / total_total)
res.sort(key=lambda x: x['fid'])
return res, num_total, total_total
def get_rules(environment, colo, host, service, status, tag):
'''
return a list of rule objections
'''
rules = []
all_rules = Mysql.query('''SELECT * FROM inbound_rules ORDER BY createDate''', 'inbound_rules')
if None == environment == colo == host == service == status == tag:
return all_rules
if status is not None:
status = Alert.to_int_status(status)
for rule in all_rules:
if compare_rule_vals(rule.environment, environment) is False: continue
if compare_rule_vals(rule.colo ,colo) is False: continue
if compare_rule_vals(rule.host ,host) is False: continue
if compare_rule_vals(rule.service ,service) is False: continue
if compare_rule_vals(rule.status ,status) is False: continue
if rule.tag is not None:
if tag is not None:
if tag.lower() not in rule.tag.lower().split(','): continue
rules.insert(0,rule);
return rules
def add_person(self):
name = self.lineEdit.text().strip()
phone = self.lineEdit_1.text().strip()
card = self.lineEdit_2.text().strip()
balance = self.doubleSpinBox.text()
type = self.lineEdit_3.text().strip()
if phone != '' and mysql.query_str('user_tb', 'phone', phone) and phone != DetailWindow.phone:
QMessageBox.information(self, "温馨提示", "手机号已存在。", QMessageBox.Yes)
return
if mysql.query('user_tb', 'card', card) and card != DetailWindow.card:
QMessageBox.information(self, "温馨提示", "卡号已存在。", QMessageBox.Yes)
return
if self.name != name:
mysql.update(self.id, 'name', name)
mysql.update_bill(self.id, 'name', name)
if self.phone != phone:
mysql.update(self.id, 'phone', phone)
mysql.update_bill(self.id, 'phone', phone)
if self.card != card:
mysql.update(self.id, 'card', card)
mysql.update_bill(self.id, 'card', card)
if self.type != type:
mysql.update(self.id, 'type', type)
# mysql.update_bill(self.id, 'type', type)
if self.balance != balance:
mysql.update(self.id, 'balance', balance)
cost = round(float(balance) - float(self.balance), 2)
bills.bill_change(cost, name, phone, card, balance, self.id)
QMessageBox.information(self, "温馨提示", "修改成功！", QMessageBox.Yes)
DetailWindow.close()
if MainWindow.is_search:
MainWindow.search_data_by_card(card)
else:
MainWindow.load_initial_data(MainWindow.tableWidget.verticalScrollBar().value())
MainWindow.load_initial_bill()
def get_onecookinfo(fid, time_from, time_to):
r = re.compile(r'^([0-9]{4})\.([0-9]{2}|[0-9])\.([0-9]{2}|[0-9])$')
if not r.match(time_from) or not r.match(time_to):
return None
time_from = util.strtostamp(time_from)
time_to = util.strtostamp(time_to)
sql = 'select * from cook_history where fid = %s and stamp >= %s and stamp < %s' % (
fid, time_from, time_to)
sql = 'select feedback.did, fb, sum(feedback.num) from (' + sql + ') as p, feedback where p.did = feedback.did group by feedback.did, fb '
cursor = mysql.query(sql)
res = map(
lambda x: {
'did': x['did'],
'name': x['name'],
'num': 0,
'good': 0,
'normal': 0,
'bad': 0
}, data.diet)
for row in cursor.fetchall():
did = row[0]
fb = row[1]
num = row[2]
t = filter(lambda x: x['did'] == did, res)
if len(t) != 1:
continue
item = t[0]
item['num'] += num
if fb == -1:
item['bad'] = num
elif fb == 0:
item['normal'] = num
elif fb == 1:
item['good'] = num
res.sort(key=lambda x: x['did'])
return res
def consume(self):
index = self.tableWidget.currentRow()
if index < 0:
messageBox = QMessageBox()
messageBox.setWindowTitle('温馨提示')
messageBox.setText('请先选择要消费的会员')
messageBox.setStandardButtons(QMessageBox.Yes)
buttonY = messageBox.button(QMessageBox.Yes)
buttonY.setText('好的')
messageBox.exec_()
return
value, ok = QInputDialog.getDouble(self, "正在消费", "请输入消费金额:", 0, 0, 1000000, 2)
if ok:
reply = MyQMessageBox("消费确认", "确认 " + self.tableWidget.item(index, 1).text() + " 消费了" + str(
value) + '元?',
'确认', '取消')
if reply == 16384:
person = mysql.query('user_tb', '_id', self.tableWidget.item(index, 0).text())
balance = float(person[0][4]) - value
if balance < 0:
QMessageBox.warning(self, "温馨提示", "余额不足，请充值")
else:
mysql.update(self.tableWidget.item(index, 0).text(), 'balance', str(balance))
QMessageBox.information(self, "温馨提示", "消费成功", QMessageBox.Yes)
bills.bill_out(str(value), str(person[0][1]), str(person[0][2]), str(person[0][3]), balance,
self.tableWidget.item(index, 0).text())
if self.is_card_search:
self.search_data_by_card(str(person[0][3]))
elif self.is_search:
self.search_data()
else:
self.load_initial_data(self.tableWidget.verticalScrollBar().value())
self.load_initial_bill()
def search_data(self, card=None):
if not card:
line = self.lineEdit.text()
if not line:
QMessageBox.information(self, "温馨提示", "请输入姓名、手机号或卡号（支持模糊搜索）", QMessageBox.Yes)
return
if card:
id = mysql.query('user_tb', 'card', card)
else:
id = mysql.like('user_tb', 'name', line) + mysql.like('user_tb', 'card', line)
if len(line) >= 3:
id += mysql.like('user_tb', 'phone', line)
if not id:
QMessageBox.information(self, "温馨提示", "未能找到对应的姓名、手机号或卡号", QMessageBox.Yes)
else:
for i in range(self.tableWidget.rowCount()):
self.tableWidget.removeRow(0)
for row in id:
inx = id.index(row)
self.tableWidget.insertRow(inx)
for i in range(len(row)):
item = QTableWidgetItem()
if str(row[i]).isdigit: # 使得数字排序能够正常的运行
item.setData(Qt.DisplayRole, row[i])
else:
item.setText(row[i])
item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled) # 无法编辑
item.setTextAlignment(Qt.AlignCenter)
self.tableWidget.setItem(inx, i, item)
del item
self.tableWidget.sortItems(0, Qt.AscendingOrder)
self.is_search = True
self.horizontalFrame.hide()
def frequency(day, request=0, kitchen=0, cash=0):
year = day.year
month = day.month
day = day.day
pattern = '(\d+):(\d+)-(\d+):(\d+)'
#print logic.info['time']
m = re.match(pattern, logic.info['time'])
h1 = int(m.group(1))
m1 = int(m.group(2))
h2 = int(m.group(3))
m2 = int(m.group(4))
start = datetime.datetime(year, month, day, h1, m1)
end = datetime.datetime(year, month, day, h2, m2)
nodes = time_nodes(start, end, interval='30m')
t1 = time.mktime(start.timetuple())
t2 = time.mktime(end.timetuple())
intervals = []
pos = 0
while pos < len(nodes) - 1:
n1 = nodes[pos]
n2 = nodes[pos + 1]
intervals.append(
(time.mktime(n1.timetuple()), time.mktime(n2.timetuple())))
pos += 1
result = []
#for request
if request == 1:
sql = 'select * from request where stamp >= %s and stamp < %s' % (t1,
t2)
rows = mysql.query(sql)
nums = []
while len(nums) < len(intervals):
nums.append(0)
for row in rows:
for span in intervals:
if row['stamp'] >= span[0] and row['stamp'] < span[1]:
index = intervals.index(span)
nums[index] += 1
break
t = []
for index in range(len(intervals)):
n1 = intervals[index][0]
n2 = intervals[index][1]
n1 = datetime.datetime.fromtimestamp(n1)
n2 = datetime.datetime.fromtimestamp(n2)
n1_str = n1.strftime('%H:%M')
n2_str = n2.strftime('%H:%M')
t.append({'from': n1_str, 'to': n2_str, 'num': nums[index]})
table = {'type': 'request', 'rows': t}
result.append(table)
if kitchen == 1:
sql = 'select num, cook_history.stamp from cook_history, order_history where cook_history.uid = order_history.uid and cook_history.stamp >= %s and cook_history.stamp < %s' % (
t1, t2)
rows = mysql.query(sql)
nums = []
while len(nums) < len(intervals):
nums.append(0)
for row in rows:
for span in intervals:
if row['stamp'] >= span[0] and row['stamp'] < span[1]:
index = intervals.index(span)
nums[index] += row['num']
break
t = []
for index in range(len(intervals)):
n1 = intervals[index][0]
n2 = intervals[index][1]
n1 = datetime.datetime.fromtimestamp(n1)
n2 = datetime.datetime.fromtimestamp(n2)
n1_str = n1.strftime('%H:%M')
n2_str = n2.strftime('%H:%M')
t.append({'from': n1_str, 'to': n2_str, 'num': nums[index]})
table = {'type': 'kitchen', 'rows': t}
result.append(table)
if cash == 1:
sql = 'select max(stamp) as time from cash_history where stamp > %s and stamp < %s group by pid' % (
t1, t2)
rows = mysql.query(sql)
nums = []
while len(nums) < len(intervals):
nums.append(0)
for row in rows:
for span in intervals:
if row['time'] >= span[0] and row['time'] < span[1]:
index = intervals.index(span)
nums[index] += 1
break
t = []
for index in range(len(intervals)):
n1 = intervals[index][0]
n2 = intervals[index][1]
n1 = datetime.datetime.fromtimestamp(n1)
n2 = datetime.datetime.fromtimestamp(n2)
n1_str = n1.strftime('%H:%M')
n2_str = n2.strftime('%H:%M')
t.append({'from': n1_str, 'to': n2_str, 'num': nums[index]})
table = {'type': 'cash', 'rows': t}
result.append(table)
return result
def get_user_by_name(name):
'''
Return a user object by giving the name of that user
'''
return Mysql.query('''SELECT * FROM users WHERE name = "%s" LIMIT 1''' % (name), "users")[0]
import requests
from mysql import query
#python调用接口
#1.构造请求
u = "http://118.24.105.78:2333/get_title_img"
res = requests.get(u)
# print(res.json())
# print(res.text)
#2.判断结果：断言实现判断
assert res.status_code == 200 #判断请求码是否为200
assert res.json()["status"] == 200 #判断返回数据中的status值是否为200
data = res.json()["data"]
for i in data:
sql = "select * from t_title_img where id = {}".format(i["id"])
r = query(sql)
assert len(r) != 0
# sql = "select * from t_title_img where id = 270 or id = 271"
# r = query(sql)
# print(r)
return json.dumps(results)
@route('/getImg/<id>')
def getImg(id):
sql="select * from photos where userID='%s'" %(id)
results={}
results=mysql.query(sql,2)
return json.dumps(results)
@route('/coachInfo/<id>')
def getCoachInfo(id):
response={}
sql="select * from trainerPlaces join location where trainerPlaces.gymID=loc
ation.id and trainerPlaces.trainerID='%s'" %(id)
response['locations']=mysql.query(sql,2)
sql="select * from reviews where toUserID='%s'" %(id)
response['reviews']=mysql.query(sql,2)
sql="select * from photos where userID='%s'" %(id)
response['images']=mysql.query(sql,2)
sql="select * from TrainerTime where trainerID='%s'" %(id)
response['timeslots']=str(mysql.query(sql,2))
return json.dumps(response)
#return response
@route('/searchGym', method='post')
def searchGym():
reader = codecs.getreader("utf-8")
data = json.load(reader(request.body))
userID=data['userID']
token=data['token']
def one_diet_fb_cook(did, start, end):
start = time.mktime(start.timetuple())
end = time.mktime(end.timetuple())
result = {}
sql = 'select fid, fb, sum(num) as number from order_history, feedback, cook_history where order_history.uid = feedback.uid and order_history.uid = cook_history.uid and did = "%s" and cook_history.stamp > %s and cook_history.stamp < %s group by fid, fb' % (
did, start, end)
rows = mysql.query(sql)
for row in rows:
fid = row['fid']
if row['fid'] not in result:
result[fid] = {'fid': fid}
if row['fb'] == -1:
result[fid]['bad-num'] = row['number']
elif row['fb'] == 0:
result[fid]['normal-num'] = row['number']
elif row['fb'] == 1:
result[fid]['good-num'] = row['number']
sql = 'select fid, sum(num) as number from order_history, cook_history where order_history.uid = cook_history.uid and order_history.did = "%s" and cook_history.stamp > %s and cook_history.stamp < %s group by fid' % (
did, start, end)
rows = mysql.query(sql)
for row in rows:
fid = row['fid']
if fid not in result:
result[fid] = {'fid': fid}
result[fid]['num'] = row['number']
cooks = []
for k, v in logic.faculty.items():
if v['role'].find('cook') >= 0:
cooks.append({'fid': k, 'name': v['name']})
cook_result = []
for cook in cooks:
fid = cook['fid']
if fid not in result:
result[fid] = {}
if 'fid' not in result[fid]:
result[fid]['fid'] = fid
if 'name' not in result[fid]:
result[fid]['name'] = logic.faculty.get(fid)['name']
if 'num' not in result[fid]:
result[fid]['num'] = 0
if 'bad-num' not in result[fid]:
result[fid]['bad-num'] = 0
if 'normal-num' not in result[fid]:
result[fid]['normal-num'] = 0
if 'good-num' not in result[fid]:
result[fid]['good-num'] = 0
if 'fb-num' not in result[fid]:
result[fid]['fb-num'] = result[fid]['good-num'] + result[fid][
'normal-num'] + result[fid]['bad-num']
if result[fid]['fb-num'] == 0:
result[fid]['good-rate'] = 0
result[fid]['normal-rate'] = 0
result[fid]['bad-rate'] = 0
else:
result[fid]['good-rate'] = result[fid][
'good-num'] * 100.0 / result[fid]['fb-num']
result[fid]['normal-rate'] = result[fid][
'normal-num'] * 100.0 / result[fid]['fb-num']
result[fid]['bad-rate'] = result[fid]['bad-num'] * 100.0 / result[
fid]['fb-num']
cook_result.append(result[fid])
cook_result.sort(key=lambda x: x['fid'])
return cook_result
def flow_data(start, end):
start = time.mktime(start.timetuple())
end = time.mktime(end.timetuple())
result = {}
sql = 'select order_history.did,name,diet.price,sum(num) as number, sum(order_history.price*order_history.num) as sales from diet,order_history,cash_history where diet.did = order_history.did and order_history.uid = cash_history.uid and status ="success" and order_history.stamp > %s and order_history.stamp < %s group by order_history.did' % (
start, end)
rows = mysql.query(sql)
num = 0
sales = 0
for row in rows:
did = row['did']
if did not in result:
result[did] = {}
result[did]['did'] = did
result[did]['name'] = row['name']
result[did]['price'] = row['price']
result[did]['num'] = row['number']
result[did]['sales'] = row['sales']
num += row['number']
sales += row['sales']
sql = 'select diet.did,name,fb,sum(num) as number from diet,order_history,feedback where order_history.uid = feedback.uid and order_history.did = diet.did and order_history.stamp > %s and order_history.stamp < %s group by diet.did,fb' % (
start, end)
rows = mysql.query(sql)
for row in rows:
did = row['did']
if did not in result:
result[did] = {}
if row['fb'] == -1:
result[did]['bad-num'] = row['number']
elif row['fb'] == 0:
result[did]['normal-num'] = row['number']
elif row['fb'] == 1:
result[did]['good-num'] = row['number']
for k, v in logic.diet.items():
if k not in result:
result[k] = {}
if 'did' not in result[k]:
result[k]['did'] = v['did']
if 'name' not in result[k]:
result[k]['name'] = v['name']
if 'price' not in result[k]:
result[k]['price'] = v['price']
if 'num' not in result[k]:
result[k]['num'] = 0
if 'sales' not in result[k]:
result[k]['sales'] = 0
if 'bad-num' not in result[k]:
result[k]['bad-num'] = 0
if 'normal-num' not in result[k]:
result[k]['normal-num'] = 0
if 'good-num' not in result[k]:
result[k]['good-num'] = 0
if 'fb-num' not in result[k]:
result[k]['fb-num'] = result[k]['good-num'] + result[k][
'normal-num'] + result[k]['bad-num']
if 'bad-rate' not in result[k]:
if result[k]['fb-num'] == 0:
result[k]['bad-rate'] = 0
else:
result[k]['bad-rate'] = result[k]['bad-num'] * 100.0 / result[
k]['fb-num']
if 'normal-rate' not in result[k]:
if result[k]['fb-num'] == 0:
result[k]['normal-rate'] = 0
else:
result[k]['normal-rate'] = result[k][
'normal-num'] * 100.0 / result[k]['fb-num']
if 'good-rate' not in result[k]:
if result[k]['fb-num'] == 0:
result[k]['good-rate'] = 0
else:
result[k]['good-rate'] = result[k][
'good-num'] * 100.0 / result[k]['fb-num']
if 'num-rate' not in result[k]:
if num == 0:
result[k]['num-rate'] = 0
else:
result[k]['num-rate'] = result[k]['num'] * 100.0 / num
if 'sales-rate' not in result[k]:
if sales == 0:
result[k]['sales-rate'] = 0
else:
result[k]['sales-rate'] = result[k]['sales'] * 100.0 / sales
result = result.values()
result.sort(key=lambda x: x['did'])
return result
def queryMysql(self, sql):
db = mysql.connect()
mysql.query(db, sql)
mysql.close(db)
def get_trend(num, base):
if not isinstance(num, int):
return None
if num > 12 or num < 0:
num = 12
label = []
end = range(num)
end.reverse()
start = map(lambda x: x + 1, end)
if base == 'day':
day = datetime.timedelta(days=1)
today = datetime.date.today()
time_from = map(lambda x: time.mktime((today - x * day).timetuple()),
start)
time_to = map(lambda x: time.mktime((today - x * day).timetuple()),
end)
label = map(lambda x: util.stamptostr(x), time_from)
elif base == 'week':
week = datetime.timedelta(weeks=1)
today = datetime.date.today()
time_from = map(lambda x: time.mktime((today - x * week).timetuple()),
start)
time_to = map(lambda x: time.mktime((today - x * week).timetuple()),
end)
label = map(lambda x: util.stamptostr(x), time_from)
elif base == 'month':
today = datetime.date.today()
year = today.year
month = today.month
def get_date(x):
if month - x > 0:
return year, month - x
else:
return year - 1, month - x + 12
time_from = map(
lambda x: datetime.date(get_date(x)[0],
get_date(x)[1], 1), start)
time_to = map(
lambda x: datetime.date(get_date(x)[0],
get_date(x)[1], 1), end)
#print time_from
#print time_to
time_from = map(lambda x: time.mktime(x.timetuple()), time_from)
time_to = map(lambda x: time.mktime(x.timetuple()), time_to)
label = map(lambda x: util.stamptostr(x), time_from)
sql = 'select did, sum(num) from order_history where stamp >= %s and stamp < %s group by did'
interval = zip(time_from, time_to)
res = []
for i in interval:
cursor = mysql.query(sql % i)
total = 0
for row in cursor.fetchall():
did = row[0]
num = row[1]
t = filter(lambda x: x['did'] == did, data.diet)
if len(t) != 1:
continue
item = t[0]
total += item['price'] * num
res.append(total)
return label, res
import mysql str = 'select id,github from coin' mysql.query(str)
def sqlQuery(sql=""):
db = mysql.connect()
mysql.query(db, sql)
mysql.close(db)
import json, sys, mysql
#print json.dumps({"message":"this is s test"})
mysql.query("select * from cuentas where id in (1,29,30)")
def get_current_alert(environment,colo,host,service):
'''
Get the current status of an alert specified by environment, colo, host, and service
'''
return Mysql.query('''SELECT * FROM alerts WHERE environment = "%s" and colo = "%s" and host = "%s" and service = "%s" LIMIT 1''' % (environment, colo, host, service), "alerts")
def check_paging_alerts():
'''
This returns a list of paging alerts that needs a notification sent out.
'''
return Mysql.query('''SELECT * FROM alerts WHERE status > 0 and ack != true AND (NOW() - lastAlertSent) > %s and tags REGEXP '(page|^page,|,page,|,page$)' ORDER BY id DESC''' % (conf['page_alert_interval']), "alerts")
def check_alerts():
'''
This returns a list of alerts that needs a notification sent out.
'''
return Mysql.query('''SELECT * FROM alerts WHERE status > 0 and ack != true AND (NOW() - lastAlertSent) > %s ORDER BY id DESC''' % (conf['alert_interval']), "alerts")
def getImg(id):
sql="select * from photos where userID='%s'" %(id)
results={}
results=mysql.query(sql,2)
return json.dumps(results)
