def analysis(data): card_id = data[0] game_id = data[1] datetime_now = datetime.now() query = """ select end_time from tbl_rent where id_user=(select id from tbl_user where card_id='%s' limit 1) and id_game=%d limit 1 """ % (card_id, game_id) db = database() result = db.select(query) db.close() if result is None: query = """ select if(( select device_count from tbl_game where id=%d limit 1) >= (select count(id) from tbl_rent where id_game=%d and end_time is null) , 1, -1) """ % (game_id, game_id) db = database() result = db.select(query) db.close() if result == 1: result = start_rent(card_id, game_id, datetime_now) return result elif result == -1: return 'no device' else: end_rent(card_id, game_id, datetime_now) end_time = datetime_now total_seconds = end_time - datetime_now total_seconds = total_seconds.total_seconds() query = """select price_per_hour from tbl_game where id=%d""" % ( game_id) db = database() result = db.select(query) db.close() price_per_hour = result[0][0] pay = price_per_hour / 3600 pay *= total_seconds result = Tools.update_user_price_by_card_id(card_id, pay) return result
def add_player(info): competition_id = int(info[0]) player_id = int(info[1]) competition_type = int(info[2]) query = "" if competition_type == 2: query = """select id from tbl_tournament_player where id_user=%d and id_tournament=(select id from tbl_tournament where id_competition=%d limit 1)""" % (player_id, competition_id) elif competition_type == 1: query = """select id from tbl_league_player where id_user=%d and id_league=(select id from tbl_league where id_competition=%d limit 1)""" % (player_id, competition_id) db = database() result = db.insert(query) db.close() if len(str(result).strip()) != 0: return 'duplicate' if competition_type == 2: # Tournament query = """ insert into tbl_tournament_player (id_user, id_tournament, status) values (%d, (select id from tbl_tournament where id_competition=%d limit 1), -1) """ % (player_id, competition_id) elif competition_type == 1: # League query = """ insert into tbl_league_player (id_user, id_league, status) values (%d, (select id from tbl_league where id_competition=%d limit 1), -1) """ % (player_id, competition_id) db = database() result = db.insert(query) db.close() if result is None: return 'false' if result: return 'true' else: return 'false'
def new_competition(data): nickname = data[0] id_game = int(data[1]) competition_type = enum_competition_type(int(data[2])) team_count = int(data[3]) signup_date = data[4] start_date = data[5] admin_id = int(data[6]) query = """ insert into tbl_competition (id_game, count, date_signup, date_start, name, admin_id) values (%d, %d, '%s', '%s', '%s', %d) """ % (id_game, team_count, signup_date, start_date, nickname, admin_id) db = database() result = db.insert(query) query = """ select id from tbl_competition where name='%s' order by name DESC """ % (nickname) result = db.select(query) db.close() competition_id = (result[0][0]) result = None if competition_type == enum_competition_type.Tournament: query = """ insert into tbl_tournament (id_competition, status) values (%d, %d) """ % (competition_id, -2) db = database() result = db.insert(query) db.close() elif competition_type == enum_competition_type.League: query = """ insert into tbl_league (id_competition) values (%d) """ % (competition_id) db = database() result = db.insert(query) db.close() return result
def next_round(tournament_id): query = """ select name, id_game, count, date_signup, date_start from tbl_competition join tbl_tournament on tbl_tournament.id_competition=tbl_competition.id where tbl_tournament.id=%d limit 1 """ % (tournament_id) db = database() data = db.select(query) db.close() nickname = data[0][0] id_game = int(data[0][1]) competition_type = enum_competition_type.Tournament team_count = int(data[0][3]) signup_date = data[0][4] start_date = data[0][5] spliter = Analysis.spliter data = nickname + spliter + id_game + spliter + competition_type + spliter + team_count + spliter + \ signup_date + spliter + start_date result = Competition.new_competition(data) query = """ select tbl_tournament_playing_user.id_user from tbl_tournament_playing join tbl_tournament_playing_user on tbl_tournament_playing_user.id_tournament_playing=tbl_tournament_playing.id where tbl_tournament_playing.id_tournament=%d and win=1 """ % (tournament_id) db = database() result = db.select(query) db.close() new_tournament_id = get_last_tournament_id() for i in result: id_user = int(i[0]) type = enum_competition_type.Tournament data = [id_user, new_tournament_id] Competition.signup_to_competition_by_userid(data, type) return True
def signup_level_2(data): id = int(data[0]) avatar = int(data[1]) if len(data[1]) > 0 else None city = int(data[2]) if len(data[2]) > 0 else None phonenumber = data[3] if len(data[3]) > 0 else None favorite_game = int(data[4]) if len(data[4]) > 0 else None credit_card = data[5] if len(data[5]) > 0 else None email = data[6] if len(data[6]) > 0 else None query = """ update tbl_user set avatar=%d , city=%d , phonenumber='%s' , favorite_game=%d , credit_card_number='%s' , email='%s' where id=%d """ % (avatar, city, phonenumber, favorite_game, credit_card, email, id) db = database() result = db.update(query) if not result: return 'false' db.close() result = 'true' return result
def getReview(): json = request.get_json() db = database() # create database object dp = dataPreprocessing() # create preprocessing object # The sentence preprocessing preProcessedSentence = dp.sentencePreprocessing(json['Review']) # sent length sentLength = dp.sentenceLength(preProcessedSentence) # calcualte how many pos and neg in the sentece labelweight = dp.calcSentenceLabelPercentage(json['Review']) # calculate the ovaerall weight in the sentence Totalweight = dp.calcSentenceWeight(json['Review']) # show the label of the sentence label = dp.sentenceLabel(Totalweight) resType = json['Restaurant Type'] ID = int(json['ID']) resName = json['Restaurant Name'] # insert the data to mysql database insertTupleValues = (json['Review'], preProcessedSentence, resName, ID, sentLength, labelweight, Totalweight, label, resType) InsertStatement = """INSERT INTO reviews(`originalReviews`,`PreprocessedReviews`,`Restaurant`,`ID`,`total_length`,`weight`,`total_percentage`,`label`,`RestaurantType`) VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s")""" insertUserData = db.insertData(InsertStatement, insertTupleValues) insertUserData # will return the sent json from ios to ios back and this will confirm data insertion in python return jsonify(json)
def get_usere_id_by_username(username): query = "select id from tbl_user where username=''" % username db = database() result = db.select(query) result = result[0][0] db.close() return result
def get_competition_info(info): type = int(info[0]) if type == 0: type = enum_competition_type.League elif type == 1: type = enum_competition_type.Tournament game_id = int(info[1]) city_id = int(info[2]) query = "" if type == enum_competition_type.League: query = """ select tbl_competition.id, tbl_competition.count, tbl_competition.name from tbl_competition join tbl_league on tbl_league.id_competition=tbl_competition.id where tbl_competition.city=%d and tbl_competition.id_game=%d """ % (city_id, game_id) elif type == enum_competition_type.Tournament: query = """ select tbl_competition.id, tbl_competition.count, tbl_competition.name from tbl_competition join tbl_tournament on tbl_tournament.id_competition=tbl_competition.id join tbl_admin on tbl_admin.id=tbl_competition.admin_id where tbl_admin.city_id=%d and tbl_competition.id_game=%d """ % (city_id, game_id) db = database() result = db.select(query) db.close() if result is None: return 'false' import ConvertData result = ConvertData.ConvertData().convert_to_json(result) return result
def get_paying(admin_id): admin_id = int(admin_id) query = """ select tbl_tournament_playing.id, tbl_user.name from tbl_tournament_playing join tbl_user join tbl_tournament_playing_user join tbl_tournament join tbl_competition on tbl_tournament_playing.id_tournament=tbl_tournament.id and tbl_tournament.id_competition=tbl_competition.id and tbl_tournament_playing_user.id_tournament_playing=tbl_tournament_playing.id and tbl_tournament_playing_user.id_user=tbl_user.id where tbl_competition.admin_id=%d """ % (admin_id) db = database() result = db.select(query) db.close() result = ConvertData().convert_to_json(result) return result
def get_games_list(): query = "select id, name from tbl_game" db = database() result = db.select(query) db.close() result = ConvertData().convert_to_json(result) return result
def get_admin_description(id): id = int(id[0]) query = "select description from tbl_admin where id=%d" % id db = database() result = db.select(query)[0][0] db.close() if result is None: return 'false' return result
def get_sliders(): query = """select id, name, url from tbl_slider limit 5""" db = database() result = db.select(query) db.close() if result is None: return 'false' import ConvertData result = ConvertData.ConvertData().convert_to_json(result) return result
def order_food(data): username = data[0] admin_id = int(data[1]) info = data[2] info = ConvertData().convert_from_json(info) now_date = datetime.datetime.now() # credit = Tools.get_user_price_by_card_id(username) price = 0 for index, value in enumerate(info): db = database() query = "select price from tbl_facility where id=%d limit 1" % ( value[0]) result = db.select(query) current_price = result[0][0] current_price -= current_price current_price *= int(value[1]) price += current_price db.close() # if credit < price: # return 'no credit' result = Tools.update_user_price_by_card_id(username, price) if result is False: return False for index, value in enumerate(info): query = """insert into tbl_order (id_user, id_facility, count, active, is_delivered, date, id_admin) values ((select id from tbl_user where username='******' limit 1), %d, %d, 0, 0, '%s', %d)""" % \ (username, int(value[0]), int(value[1]), now_date, admin_id) db = database() result = db.insert(query) if result is False: return False db.close() return True
def start(tournament_id): tournament_id = int(tournament_id) query = """ select id_user from tbl_tournament_player where id_tournament=%d """ % (tournament_id) db = database() result = db.select(query) db.close() teams = [team for team in result[0]] teams = Tournament().single_elimination(teams) for i in teams: query = """ insert into tbl_tournament_playing (id_tournament) values (%d) """ % (tournament_id) db = database() result = db.insert(query) tournament_playing_id = \ db.select( 'select id from tbl_tournament_playing where id_tournament=%d order by id DESC ' % (tournament_id))[ 0][0] tournament_playing_id = int(tournament_playing_id) db.close() for j in range(2): query = """ insert into tbl_tournament_playing_user (id_user, id_tournament_playing) values (%d, %d) """ % (int(i[j]), tournament_playing_id) db = database() result = db.insert(query) db.close() return True
def get_last_tournament_id(): query = """ select id from tbl_tournament order by id desc limit 1 """ db = database() result = db.select(query) result = result[0][0] result = int(result) return result
def get_available_leagues(): query = """ select tbl_league.id, tbl_competition.name from tbl_competition join tbl_league on tbl_competition.id=tbl_league.id_competition """ db = database() result = db.select(query) db.close() result = ConvertData().convert_to_json(result) return result
def check_for_start(tournament_id): for i in tournament_id: db = database() tmp_tournament_id = int(i[0]) args = [tmp_tournament_id, 0] query = 'select @_tournament_check_start_1' result = db.callproc('tournament_check_start', args, query)[0][0] db.close() if result == 0: pass else: start(tournament_id)
def check_for_start_pre(admin_id): admin_id = int(admin_id) query = """ select tbl_tournament.id from tbl_tournament join tbl_competition on tbl_tournament.id_competition=tbl_competition.id where admin_id=%d """ % (admin_id) db = database() result = db.select(query) db.close() check_for_start(result)
def get_next_game(id): id = int(id) query_get_date = """ SELECT tbl_league_playing.date FROM tbl_league_playing join tbl_league_playing_user on tbl_league_playing_user.id_league_playing=tbl_league_playing.id where tbl_league_playing_user.id_user=%d order by tbl_league_playing.date desc limit 1 """ % id query_get_competitor_name = """ select name from tbl_league_playing_user join tbl_user on tbl_user.id=tbl_league_playing_user.id_user where id_league_playing=(select id_league_playing from tbl_league_playing_user where id_user=%d) and id_user!=%d """ % (id, id) query_get_league_name = """ select name from tbl_competition join tbl_league join tbl_league_player on tbl_league_player.id_league=tbl_league.id and tbl_league.id_competition=tbl_competition.id where tbl_league_player.id_user=%d """ % id db = database() league_name = db.select(query_get_league_name) if league_name is not None: league_name = league_name[0][0] else: league_name = '' competitor_name = db.select(query_get_competitor_name) if competitor_name is not None: competitor_name = competitor_name[0][0] else: competitor_name = '' league_date = db.select(query_get_date) if league_date is not None: league_date = str(league_date[0][0]) else: league_date = '' db.close() import json result = [competitor_name, league_date, league_name] result = json.dumps(result) return result
def __init__(self, postID: int): self.__db = database(pid=postID) self.__tid = postID self.__executeCommand = self.__db.executeCommand() self.__dbTotalChange = 0 def autoWrite(): for i in self.__executeCommand: self.__dbTotalChange = int(i) self.autoWriteThread = threading.Thread(target=autoWrite) self.autoWriteThread.start() Avalon.thread_lock = threading.Lock()
def start_rent(card_id, game_id, datatime_now): query = """ insert into tbl_rent (id_user, id_game, start_time) values ((select id from tbl_user where card_id='%s'), %d, '%s') """ % (card_id, game_id, datatime_now) db = database() result = db.update(query) db.close() return result
def get_foot_orders(data): admin_id = int(data[0]) query = """select tbl_order.id, tbl_user.name, tbl_facility.name, tbl_order.count from tbl_order join tbl_user join tbl_facility on tbl_order.id_user=tbl_user.id and tbl_order.id_facility=tbl_facility.id where tbl_order.is_delivered=0 and tbl_order.id_admin=%d limit 200""" % admin_id db = database() result = db.select(query) db.close() result = ConvertData().convert_to_json(result) return result
def get_admins_list(info): city_id = info[0] query = """ select id, name from tbl_admin""" # where city_id=%d # """ % city_id db = database() result = db.select(query) db.close() if result is None: return 'false' import ConvertData result = ConvertData.ConvertData().convert_to_json(result) return result
def WeightandStatisticsRestaurant(self, ID=None, RestaurantName=None): positive_counter = 0 negative_counter = 0 neutral_counter = 0 resRankData = {} # How many pos and neg and neutral if ID != None and RestaurantName == None: db = database() userSqlQuery = """SELECT * FROM reviews WHERE ID = %s""" % int(ID) mydatafromDB = db.getData(userSqlQuery) for index, columns in mydatafromDB.iloc[:].iterrows(): if mydatafromDB.at[index, 'label'] == "'Positive'": positive_counter += 1 elif mydatafromDB.at[index, 'label'] == "'Negative'": negative_counter += 1 elif mydatafromDB.at[index, 'label'] == "'Neutral'": neutral_counter += 1 totalWeightForRank = positive_counter - (negative_counter + neutral_counter) return totalWeightForRank elif ID != None and RestaurantName != None: db = database() userSqlQuery = """SELECT * FROM reviews WHERE ID = %s""" % int(ID) mydatafromDB = db.getData(userSqlQuery) for index, columns in mydatafromDB.iloc[:].iterrows(): if mydatafromDB.at[index, 'label'] == "'Positive'": positive_counter += 1 elif mydatafromDB.at[index, 'label'] == "'Negative'": negative_counter += 1 elif mydatafromDB.at[index, 'label'] == "'Neutral'": neutral_counter += 1 resRankData.update({ "Positive": positive_counter, "Negative": negative_counter, "Neutral": neutral_counter }) return resRankData
def end_rent(card_id, game_id, datetime_now): query = """ update tbl_rent set end_time='%s' where id_user=(select id from tbl_user where card_id='%s') and id_game='%s' """ % (datetime_now, card_id, game_id) db = database() result = db.update(query) db.close() return result
def showComments(): db = database() # create database object json = request.get_json() resID = json['ID'] resType = json['Restaurant Type'] negCommentsSqlQuery = """SELECT * FROM `reviews` WHERE ID = %d AND RestaurantType = "%s" AND label = "'Negative'" """ % ( int(resID), str("'" + resType + "'")) posCommentsSqlQuery = """SELECT * FROM `reviews` WHERE ID = %d AND RestaurantType = "%s" AND label = "'Positive'" """ % ( int(resID), str("'" + resType + "'")) negdatafromDB = db.getData(negCommentsSqlQuery) posdatafromDB = db.getData(posCommentsSqlQuery) finalNegComm = negdatafromDB['OriginalReviews'].head().values.tolist() finalPosComm = posdatafromDB['OriginalReviews'].head().values.tolist() finalAllCommDict = { "PositiveComments": finalNegComm, "NegativeComments": finalPosComm } return jsonify(finalAllCommDict)
def update_user_price_by_card_id(username, price): # query = "select price from tbl_user where card_id=%'s' limit 1" # db = database() # result = db.select(query) # db.close() # # current_price = result[0][0] # if int(current_price) < price: # return 'no credit' query = """update tbl_user, (SELECT * FROM tbl_user where username='******' limit 1) src set tbl_user.price=src.price - %d where tbl_user.id=src.id""" % (username, int(price)) db = database() result = db.update(query) db.close() return result
def __init__(self): self.db = database() self.headers = { 'Host': 'aweme.snssdk.com', 'accept-encoding': 'gzip', 'accept-language': 'zh-CN,zh;q=0.9', 'pragma': 'no-cache', 'cache-control': 'no-cache', 'upgrade-insecure-requests': '1', 'user-agent': "com.ss.android.ugc.aweme/800 (Linux; U; Android 5.1.1; zh_CN; ALP-AL00; Build/HUAWEIALP-AL00; Cronet/58.0.2991.0)", }
def __init__(self): self.db = database() # self.driver = webdriver.Chrome() self.HEADERS = { 'accept-encoding': 'gzip, deflate, br', 'accept-language': 'zh-CN,zh;q=0.9', 'pragma': 'no-cache', 'cache-control': 'no-cache', 'upgrade-insecure-requests': '1', # 'user-agent': "com.ss.android.ugc.aweme/800 (Linux; U; Android 5.1.1; zh_CN; SM-G955F; Build/JLS36C; Cronet/58.0.2991.0)", 'user-agent': "Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10A403 Safari/8536.25", }
def signup_to_competition_by_userid(data, type): user_id = data[0] status = -1 if type == enum_competition_type.League: league_id = int(data[1]) query = """ INSERT INTO tbl_league_player ( id_user, id_league, status ) VALUES(%d, %d, %d) """ % (user_id, league_id, status) elif type == enum_competition_type.Tournament: tournament_id = int(data[1]) query = """ INSERT INTO tbl_tournament_player ( id_user, id_tournament, status ) VALUES(%d, %d, %d) """ % (user_id, tournament_id, status) db = database() result = db.insert(query) db.close() return result