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
