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)