def get_moderating_posts(dbconnection: dbutils.DBConnection = None) -> list:
dbconnection.execute("SELECT * FROM blog.posts WHERE status=0",
_fields['posts'])
if len(dbconnection.last()) == 0: return list()
return list(
map(lambda x: BlogPost(x, dbconnection=dbconnection),
dbconnection.last()))
def add_post(title:str, content:str, created_by:int, tags:list, dbconnection:dbutils.DBConnection=None) -> int:
content = base64.b64encode(content.encode()).decode()
dbconnection.execute("INSERT INTO blog.posts (keywords, description, title, content, created_by, datetime) VALUES ('', '', '{}', '{}', {}, NOW())".format(title, content, created_by))
post_id = dbconnection.execute("SELECT last_insert_id() FROM blog.posts")[0][0]
for tag_id in tags:
dbconnection.execute("INSERT INTO blog.post_tags VALUES ({}, {})".format(post_id, tag_id))
return post_id
def get(user_id: int,
all: bool = False,
type: int = -1,
dbconnection: dbutils.DBConnection = None) -> list:
query = "SELECT * FROM notifications WHERE datetime<NOW() AND user_id={}".format(
user_id)
options = list()
if not all:
options.append("`read`=0")
if type >= 0:
options.append("type={}".format(type))
if len(options) > 0:
query += ' AND '
query += ' AND '.join(options)
query += ' ORDER BY DATETIME DESC'
if all:
query += ' LIMIT 40'
dbconnection.execute(query, dbutils.dbfields['notifications'])
if len(dbconnection.last()) == 0: return list()
notifications = dbconnection.last()
notifications = list(
map(lambda x: Notification(x, dbconnection=dbconnection),
notifications))
return notifications
def get_subscribed(command_id: int,
dbconnection: dbutils.DBConnection = None) -> list:
dbconnection.execute(
"SELECT user_id FROM usergames WHERE comand_id='{}'".format(
command_id))
if len(dbconnection.last()) == 0: return list()
return list(map(lambda x: x[0], dbconnection.last()))
def get_posts_by_tags(tag_ids: list,
dbconnection: dbutils.DBConnection = None) -> list:
dbconnection.execute(
"SELECT * FROM blog.posts WHERE post_id IN (SELECT post_id FROM blog.post_tags WHERE tag_id IN ({})) AND status>=0"
.format(', '.join(tag_ids)), _fields['posts'])
if len(dbconnection.last()) == 0: return list()
return list(map(lambda x: Tag(x), dbconnection.last()))
def are_friends(user_id_1: int,
user_id_2: int,
dbconnection: dbutils.DBConnection = None) -> bool:
dbconnection.execute(
"SELECT * FROM friends WHERE user_id1={} AND user_id2={}".format(
user_id_1, user_id_2))
return len(dbconnection.last()) != 0
def get_tag(tag_id: int,
dbconnection: dbutils.DBConnection = None) -> BlogPost:
dbconnection.execute(
"SELECT * FROM blog.tags WHERE tag_id={}".format(tag_id),
_fields['posts'])
if len(dbconnection.last()) == 0: return None
return Tag(dbconnection.last()[0])
def ban(user_id: int, reason: str, dbconnection: dbutils.DBConnection = None):
if banned(user_id, dbconnection=dbconnection):
raise ValueError("User already banned")
dbconnection.execute(
"INSERT INTO banned (user_id, reason) VALUES (user_id, '{}')".format(
user_id, reason))
cacher.drop_by_table_name('banned', 'user_id', user_id)
def unsubscribe(user_id: int,
game_id: int,
dbconnection: dbutils.DBConnection = None):
dbconnection.execute(
"SELECT status FROM usergames WHERE user_id={} AND game_id={}".format(
user_id, game_id))
if len(dbconnection.last()) == 0:
return
status = dbconnection.last()[0][0]
if status == 2 or status == 1:
if status == 2:
delete_future_notifications(user_id,
game_id,
dbconnection=dbconnection)
usergames_unsubscribe(user_id, game_id, dbconnection=dbconnection)
elif status == 0:
return
game = get_by_id(game_id, dbconnection=dbconnection)
if game.reserved() > 0 and len(game.reserved_people()) > 0:
for user_id in game.reserved_people():
utils.spool_func(
notificating.site.subscribed,
user_id, 'В игре "{}" освободилось место!'.format(
create_link.game(game)), 1, game_id)
utils.spool_func(notificating.mail.tpl.notify_reserved, game,
user_id)
def get_by_id(game_id, dbconnection: dbutils.DBConnection = None) -> Game:
if isinstance(game_id, str) and len(game_id.split(',')) > 0:
game_id = splitstrlist(game_id)
if len(game_id) == 1:
game_id = game_id[0]
if isinstance(game_id, list) and len(game_id) == 0: return list()
if isinstance(game_id, int):
dbconnection.execute(
"SELECT * FROM games WHERE game_id={}".format(game_id),
dbutils.dbfields['games'])
elif isinstance(game_id, list):
dbconnection.execute(
"SELECT * FROM games WHERE game_id IN (" +
','.join(map(str, game_id)) + ")", dbutils.dbfields['games'])
if len(dbconnection.last()) == 0: return list()
games = dbconnection.last()
games = list(map(lambda x: Game(x, dbconnection=dbconnection), games))
if isinstance(game_id, int):
return games[0]
elif isinstance(game_id, list):
return games
def get_post(post_id: int,
dbconnection: dbutils.DBConnection = None) -> BlogPost:
dbconnection.execute(
"SELECT * FROM blog.posts WHERE post_id={}".format(post_id),
_fields['posts'])
if len(dbconnection.last()) == 0: return None
return BlogPost(dbconnection.last()[0], dbconnection=dbconnection)
def get_responsible_games(user_id: int,
dbconnection: dbutils.DBConnection = None) -> list:
dbconnection.execute(
"SELECT game_id FROM games WHERE responsible_user_id='{}' and deleted=0"
.format(user_id))
return list(
map(lambda x: x[0],
dbconnection.last())) if len(dbconnection.last()) > 0 else list()
def get_subscribed_games(user_id: int,
dbconnection: dbutils.DBConnection = None) -> list:
dbconnection.execute(
"SELECT game_id FROM usergames WHERE user_id='{}' AND status=2".format(
user_id))
return list(
map(lambda x: x[0],
dbconnection.last())) if len(dbconnection.last()) > 0 else list()
def get_reserved_to_game(game_id: int,
dbconnection: dbutils.DBConnection = None) -> list:
dbconnection.execute(
"SELECT user_id FROM usergames WHERE game_id='{}' AND status=1".format(
game_id))
return list(
map(lambda x: x[0],
dbconnection.last())) if len(dbconnection.last()) > 0 else list()
def get_organizer_games(user_id: int,
dbconnection: dbutils.DBConnection = None) -> list():
dbconnection.execute(
"SELECT game_id FROM games WHERE created_by='{}' and deleted=0".format(
user_id))
return list(
map(lambda x: x[0],
dbconnection.last())) if len(dbconnection.last()) > 0 else list()
def delete_future_notifications(user_id: int,
game_id: int,
dbconnection: dbutils.DBConnection = None):
dbconnection.execute(
"SELECT notification_id FROM notifications WHERE type=1 AND game_id={} AND user_id={} AND DATETIME>NOW()"
.format(game_id, user_id))
if len(dbconnection.last()) == 0:
return
notifications.delete(list(map(lambda x: x[0], dbconnection.last())))
def add(dbconnection: dbutils.DBConnection = None, **kwargs) -> int:
sql = 'INSERT INTO games ({dbkeylist}) VALUES ({dbvaluelist})'
keylist = list(kwargs.keys())
sql = sql.format(dbkeylist=', '.join(keylist),
dbvaluelist=', '.join(
["'{}'".format(str(kwargs[key])) for key in keylist]))
dbconnection.execute(sql)
dbconnection.execute('SELECT last_insert_id() FROM games')
return dbconnection.last()[0][0]
def remove_friend(user_id: int,
friend_id: int,
dbconnection: dbutils.DBConnection = None):
if not are_friends(user_id, friend_id, dbconnection=dbconnection):
raise ValueError("User <{}> do not have friend <{}>".format(
user_id, friend_id))
dbconnection.execute(
"DELETE FROM friends WHERE user_id1={} AND user_id2={}".format(
user_id, friend_id))
cacher.drop_by_table_name('friends', 'user_id', user_id)
def add_friend(user_id: int,
friend_id: int,
dbconnection: dbutils.DBConnection = None):
if are_friends(user_id, friend_id, dbconnection=dbconnection):
raise ValueError("User <{}> already have friend <{}>".format(
user_id, friend_id))
dbconnection.execute(
"INSERT INTO friends (user_id1, user_id2) VALUES ({},{})".format(
user_id, friend_id))
cacher.drop_by_table_name('friends', 'user_id', user_id)
def get_comands(comand_id:(list, int), dbconnection:dbutils.DBConnection=None) -> Comand:
if isinstance(comand_id, list) and len(comand_id) == 0: return list()
if isinstance(comand_id, int):
dbconnection.execute("SELECT * FROM comands WHERE comand_id='{}'".format(comand_id))
elif isinstance(comand_id, list):
dbconnection.execute(
"SELECT * FROM comands WHERE comand_id IN ({})".format(','.join(list(map(str, comand_id)))))
return list(map(lambda x: Comand(x, dbconnection), dbconnection.last()))
def get_comands(comand_id: (list, int),
dbconnection: dbutils.DBConnection = None) -> Comand:
if isinstance(comand_id, list) and len(comand_id) == 0: return list()
if isinstance(comand_id, int):
dbconnection.execute(
"SELECT * FROM comands WHERE comand_id='{}'".format(comand_id))
elif isinstance(comand_id, list):
dbconnection.execute(
"SELECT * FROM comands WHERE comand_id IN ({})".format(','.join(
list(map(str, comand_id)))))
return list(map(lambda x: Comand(x, dbconnection), dbconnection.last()))
def get(user_id,
userlevel: int = -1,
count: slice = slice(0, 20),
dbconnection: dbutils.DBConnection = None) -> User:
if isinstance(user_id, str) and len(user_id.split(',')) > 0:
user_id = splitstrlist(user_id)
if len(user_id) == 1:
user_id = user_id[0]
if isinstance(user_id, list) and len(user_id) == 0: return list()
sql = "SELECT * FROM users WHERE user_id='{}'".format(user_id)
if isinstance(user_id, int) and user_id != 0:
sql = "SELECT * FROM users WHERE user_id='{}'".format(user_id)
elif isinstance(user_id, list):
sql = "SELECT * FROM users WHERE user_id IN (" + ','.join(
map(str, user_id)) + ")"
elif user_id == 0:
sql = "SELECT * FROM users"
if userlevel >= 0 or isinstance(userlevel, set):
if user_id == 0:
sql += ' WHERE '
else:
sql += ' AND '
if isinstance(userlevel, int):
sql += " LOCATE('|{}|', userlevel) ".format(userlevel)
else:
' AND '.join(
map(lambda x: "LOCATE('|{}|', userlevel)".format(x),
userlevel))
sql += " ORDER BY played_games DESC"
if user_id == 0:
sql += " LIMIT {}, {}".format(count.start if count.start else 0,
count.stop)
dbconnection.execute(sql, dbutils.dbfields['users'])
if len(dbconnection.last()) == 0: return list()
users = dbconnection.last()
users = list(map(lambda x: User(x, dbconnection=dbconnection), users))
if isinstance(user_id, int) and user_id != 0:
return users[0]
elif isinstance(user_id, list) or user_id == 0:
return users
def get_game_stats(user_id: int,
dbconnection: dbutils.DBConnection = None) -> dict:
sql = "SELECT game_id, status FROM reports WHERE user_id={} AND STATUS=2".format(
user_id)
usergames = dbconnection.execute(sql)
game_ids = list(map(lambda x: x[0],
usergames)) if len(usergames) != 0 else list()
games = get_by_id(game_ids, dbconnection=dbconnection)
info = dict()
info['total'] = 0
info['sport_types'] = dict()
info['beautiful'] = dict()
assert isinstance(games, list)
for game in games:
assert isinstance(game, Game)
if game.sport_type() not in info:
info[game.sport_type()] = 0
info[game.sport_type()] += game.duration()
info['total'] += game.duration()
if game.sport_type() not in info['sport_types']:
info['sport_types'][game.sport_type()] = game.sport_type(
True).title()
for key in {key for key in info if isinstance(key, int)}:
info['beautiful'][key] = format_duration(info[key])
info['beautiful']['total'] = format_duration(info['total'])
return info
def usergames_set(user_id: int,
game_id: int,
status: int,
dbconnection: dbutils.DBConnection = None):
dbconnection.execute(
"SELECT COUNT(*) FROM usergames WHERE user_id={} AND game_id={}".
format(user_id, game_id))
if dbconnection.last()[0][0] == 0:
sql = "INSERT INTO usergames (user_id, game_id, status) VALUES ({}, {}, {})".format(
user_id, game_id, status)
else:
sql = "UPDATE usergames SET status={} WHERE user_id={} AND game_id={}".format(
status, user_id, game_id)
dbconnection.execute(sql)
games_cache.drop(game_id)
cacher.drop_by_table_name('usergames', 'game_id', game_id)
def add_post(title: str,
content: str,
created_by: int,
tags: list,
dbconnection: dbutils.DBConnection = None) -> int:
content = base64.b64encode(content.encode()).decode()
dbconnection.execute(
"INSERT INTO blog.posts (keywords, description, title, content, created_by, datetime) VALUES ('', '', '{}', '{}', {}, NOW())"
.format(title, content, created_by))
post_id = dbconnection.execute(
"SELECT last_insert_id() FROM blog.posts")[0][0]
for tag_id in tags:
dbconnection.execute(
"INSERT INTO blog.post_tags VALUES ({}, {})".format(
post_id, tag_id))
return post_id
def get_count(user_id: int,
all: bool = False,
dbconnection: dbutils.DBConnection = None) -> int:
return int(
dbconnection.execute(
"SELECT COUNT(*) FROM notifications WHERE datetime<NOW() AND user_id={}{}"
.format(user_id, ' AND `read`=0' if not all else ''))[0][0])
def current_user(db: dbutils.DBConnection, detalized: bool = False):
token = bottle.request.query.get('token')
user_id = db.execute(
"SELECT user_id FROM api_auth WHERE token='{}'".format(token))[0][0]
if detalized:
return users.get(user_id, dbconnection=db)
return user_id
def get(user_id:int, all:bool=False, type:int=-1, dbconnection:dbutils.DBConnection=None) -> list:
query = "SELECT * FROM notifications WHERE datetime<NOW() AND user_id={}".format(user_id)
options = list()
if not all:
options.append("`read`=0")
if type >= 0:
options.append("type={}".format(type))
if len(options) > 0:
query += ' AND '
query += ' AND '.join(options)
query += ' ORDER BY DATETIME DESC'
if all:
query += ' LIMIT 40'
dbconnection.execute(query, dbutils.dbfields['notifications'])
if len(dbconnection.last()) == 0: return list()
notifications = dbconnection.last()
notifications = list(map(lambda x: Notification(x, dbconnection=dbconnection), notifications))
return notifications
def get(user_id, userlevel:int=-1, count:slice=slice(0, 20), dbconnection:dbutils.DBConnection=None) -> User:
if isinstance(user_id, str) and len(user_id.split(',')) > 0:
user_id = splitstrlist(user_id)
if len(user_id) == 1:
user_id = user_id[0]
if isinstance(user_id, list) and len(user_id) == 0: return list()
sql = "SELECT * FROM users WHERE user_id='{}'".format(user_id)
if isinstance(user_id, int) and user_id != 0:
sql = "SELECT * FROM users WHERE user_id='{}'".format(user_id)
elif isinstance(user_id, list):
sql = "SELECT * FROM users WHERE user_id IN (" + ','.join(map(str, user_id)) + ")"
elif user_id == 0:
sql = "SELECT * FROM users"
if userlevel >= 0 or isinstance(userlevel, set):
if user_id==0:
sql += ' WHERE '
else:
sql += ' AND '
if isinstance(userlevel, int):
sql += " LOCATE('|{}|', userlevel) ".format(userlevel)
else:
' AND '.join(map(lambda x: "LOCATE('|{}|', userlevel)".format(x), userlevel))
sql += " ORDER BY played_games DESC"
if user_id == 0:
sql += " LIMIT {}, {}".format(count.start if count.start else 0, count.stop)
dbconnection.execute(sql, dbutils.dbfields['users'])
if len(dbconnection.last()) == 0: return list()
users = dbconnection.last()
users = list(map(lambda x: User(x, dbconnection=dbconnection), users))
if isinstance(user_id, int) and user_id != 0:
return users[0]
elif isinstance(user_id, list) or user_id == 0:
return users
def court_game_intersection(court_id: int,
datetime: str,
duration: int,
dbconnection: dbutils.DBConnection = None) -> Game:
sql = (
" SELECT * FROM games WHERE court_id='{court_id}'"
" AND deleted=0 AND ("
" (datetime BETWEEN '{datetime}' AND '{datetime}' + INTERVAL '{duration}' MINUTE)"
" OR "
" (datetime + INTERVAL duration MINUTE BETWEEN '{datetime}' AND '{datetime}' + INTERVAL '{duration}' MINUTE))"
" AND "
" datetime!='{datetime}' AND datetime!=('{datetime}' + INTERVAL '{duration}' MINUTE)"
" AND "
" (datetime + INTERVAL duration MINUTE)!='{datetime}' AND (datetime + INTERVAL duration MINUTE)!=('{datetime}' + INTERVAL '{duration}' MINUTE)"
)
sql = sql.format(court_id=court_id, datetime=datetime, duration=duration)
dbconnection.execute(sql, dbutils.dbfields['games'])
if len(dbconnection.last()) > 0:
return Game(dbconnection.last()[0], dbconnection=dbconnection)
return None
def search(query:str, dbconnection:dbutils.DBConnection=None) -> list:
query = list(map(lambda x: '%' + x + '%', query.split(' ')))
sql = "SELECT user_id FROM users WHERE "
if len(query) == 1:
sql += "first_name LIKE '{first}' OR last_name LIKE '{first}'".format(first=query[0])
else:
sql += ("first_name LIKE '{first}' AND last_name LIKE '{last}'"
" OR "
"first_name LIKE '{last}' AND last_name LIKE '{first}'").format(first=query[0], last=query[1])
users = dbconnection.execute(sql)
return list(map(lambda x: x[0], users)) if len(users) > 0 else list()
def user(user_id: int, db: dbutils.DBConnection) -> dict:
user = db.execute(
"SELECT * FROM users WHERE user_id={}".format(user_id),
dbutils.dbfields['users'])
if len(user) == 0: return None
else: user = user[0]
keys = set(user.keys())
keys.difference_update(_available_fields['users_get']['restricted'])
user_dict = {key: user[key] for key in keys}
formatters.user(user_dict, db)
return user_dict
def calc_game(game_id:int, dbconnection:dbutils.DBConnection=None) -> dict:
game = dbconnection.execute("SELECT * FROM games WHERE game_id={}".format(game_id), dbutils.dbfields['games'])[0]
game = Game(game, dbconnection=dbconnection)
reports = dbconnection.execute("SELECT * FROM reports WHERE game_id={}".format(game_id), dbutils.dbfields['reports'])
additional_charges = dbconnection.execute("SELECT * FROM additional_charges WHERE game_id={}".format(game_id), dbutils.dbfields['additional_charges'])
finances = dict()
finances['game_id'] = game_id
finances['datetime'] = game.datetime()
finances['capacity'] = game.capacity()
finances['cost'] = game.cost()
finances['sport_id'] = game.sport_type()
finances['responsible_user_id'] = game.responsible_user_id()
finances['created_by'] = game.created_by()
finances['visited'] = len(list(filter(lambda x: x['status']!=0, reports)))
finances['empty'] = finances['capacity']-finances['visited']
if finances['empty']<0: finances['empty']=0
finances['lost_empty'] = finances['empty']*finances['cost']
finances['notvisited'] = len(list(filter(lambda x: x['status']==0, reports)))
finances['lost_notvisited'] = finances['notvisited']*finances['cost']
finances['notpayed'] = len(list(filter(lambda x: x['status']==1, reports)))
finances['lost_notpayed'] = finances['notpayed']*finances['cost']
finances['playedpayed'] = len(list(filter(lambda x: x['status']==2, reports)))
finances['real_income'] = finances['playedpayed']*finances['cost']
finances['ideal_income'] = finances['cost']*finances['capacity'] if finances['capacity']>0 else finances['real_income']
finances['rent_charges'] = game.court_id(True).cost()*(game.duration()/60)
finances['additional_charges'] = sum([i['cost'] for i in additional_charges])
finances['profit'] = finances['real_income']-finances['additional_charges']-finances['rent_charges']
dbconnection.execute("SELECT percents FROM responsible_salary WHERE user_id={}".format(game.responsible_user_id()))
finances['responsible_salary'] = 0
if len(dbconnection.last())>0:
finances['responsible_salary'] = round((finances['profit'])*(dbconnection.last()[0][0]/100))
finances['profit'] -= finances['responsible_salary']
finances['real_profit'] = finances['profit'] + finances['rent_charges']
return finances
def get(city_id, dbconnection: dbutils.DBConnection = None) -> City:
if isinstance(city_id, str) and len(city_id.split(',')) > 0:
city_id = splitstrlist(city_id)
if len(city_id) == 1:
city_id = city_id[0]
if isinstance(city_id, list) and len(city_id) == 0: return list()
if isinstance(city_id, int) and city_id != 0:
dbconnection.execute(
"SELECT * FROM cities WHERE city_id='{}'".format(city_id),
dbutils.dbfields['cities'])
elif isinstance(city_id, list):
dbconnection.execute(
"SELECT * FROM cities WHERE city_id IN (" +
','.join(map(str, city_id)) + ")", dbutils.dbfields['cities'])
elif city_id == 0:
dbconnection.execute("SELECT * FROM cities",
dbutils.dbfields['cities'])
if len(dbconnection.last()) == 0: return list()
cities = dbconnection.last()
cities = list(map(lambda x: City(x), cities))
if isinstance(city_id, int) and city_id != 0:
return cities[0]
elif isinstance(city_id, list) or city_id == 0:
return cities
def __init__(self, game_finances_list:list, db:dbutils.DBConnection):
self.games = game_finances_list
self.games_dict = {game.game_id():game for game in self.games}
self.sports = {sport.sport_id():sport for sport in sport_types.get(0, dbconnection=db)}
self.real_games = db.execute("SELECT * FROM games WHERE game_id IN ({})".format(
', '.join(list(map(str, self.games_dict.keys())))), dbutils.dbfields['games']) if len(self.games)>0 else list()
self.real_games_dict = {game['game_id']:game for game in self.real_games}
summ = lambda field: sum([getattr(game, field)() for game in self.games])
self.ideal_income = summ('ideal_income')
self.empty = summ('empty')
self.lost_empty = summ('lost_empty')
self.notvisited = summ('notvisited')
self.lost_notvisited = summ('lost_notvisited')
self.notpayed = summ('notpayed')
self.lost_notpayed = summ('lost_notpayed')
self.real_income = summ('real_income')
self.rent_charges = summ('rent_charges')
self.additional_charges = summ('additional_charges')
self.profit = summ('profit')
self.responsible_salary = summ('responsible_salary')
self.real_profit = summ('real_profit')
self.game_by_responsible = group(lambda x: x.responsible_user_id(), self.games)
a = db.execute("SELECT * FROM responsible_games_salary WHERE game_id IN ({})".format(
', '.join([str(game.game_id()) for game in self.games])), dbutils.dbfields['responsible_games_salary']) if len(self.games)>0 else list()
self.salary = group(lambda x: x['user_id'], a)
self.users_get = users.get
self.sport_games = group(lambda x: x.sport_id(), self.games)
self.sport_money = dict()
for sport_id in self.sport_games:
self.sport_money[sport_id] = sum([game.real_profit() for game in self.sport_games[sport_id]])
a = db.execute("SELECT * FROM finance_balance WHERE user_id!=0 AND YEAR(date)={} AND MONTH(date)={}".format(
self.games[-1].datetime().date().year, self.games[0].datetime().date().month
), dbutils.dbfields['finance_balance']) if len(self.games)>0 else list()
self.user_salary = {i['user_id']:i['value'] for i in a}
def get(city_id, dbconnection:dbutils.DBConnection=None) -> City:
if isinstance(city_id, str) and len(city_id.split(',')) > 0:
city_id = splitstrlist(city_id)
if len(city_id) == 1:
city_id = city_id[0]
if isinstance(city_id, list) and len(city_id) == 0: return list()
if isinstance(city_id, int) and city_id != 0:
dbconnection.execute("SELECT * FROM cities WHERE city_id='{}'".format(city_id), dbutils.dbfields['cities'])
elif isinstance(city_id, list):
dbconnection.execute("SELECT * FROM cities WHERE city_id IN (" + ','.join(map(str, city_id)) + ")",
dbutils.dbfields['cities'])
elif city_id == 0:
dbconnection.execute("SELECT * FROM cities", dbutils.dbfields['cities'])
if len(dbconnection.last()) == 0: return list()
cities = dbconnection.last()
cities = list(map(lambda x: City(x), cities))
if isinstance(city_id, int) and city_id != 0:
return cities[0]
elif isinstance(city_id, list) or city_id == 0:
return cities
def get(sport_id, dbconnection:dbutils.DBConnection=None) -> list:
if isinstance(sport_id, str) and len(sport_id.split(',')) > 0:
sport_id = splitstrlist(sport_id)
if len(sport_id) == 1:
sport_id = sport_id[0]
if isinstance(sport_id, list) and len(sport_id) == 0: return list()
if isinstance(sport_id, int) and sport_id != 0:
dbconnection.execute("SELECT * FROM sport_types WHERE sport_id='{}'".format(sport_id),
dbutils.dbfields['sport_types'])
elif isinstance(sport_id, list):
dbconnection.execute(
"SELECT * FROM sport_types WHERE sport_id IN (" + ','.join(map(str, sport_id)) + ")",
dbutils.dbfields['sport_types'])
elif sport_id == 0:
dbconnection.execute("SELECT * FROM sport_types", dbutils.dbfields['sport_types'])
if len(dbconnection.last()) == 0: return list()
sport_types = dbconnection.last()
sport_types = list(map(lambda x: SportType(x), sport_types))
if isinstance(sport_id, int) and sport_id != 0:
return sport_types[0]
elif isinstance(sport_id, list) or sport_id == 0:
return sport_types
def get(type_id, dbconnection:dbutils.DBConnection=None) -> GameType:
if isinstance(type_id, str) and len(type_id.split(',')) > 0:
type_id = splitstrlist(type_id)
if len(type_id) == 1:
type_id = type_id[0]
if isinstance(type_id, list) and len(type_id) == 0: return list()
if isinstance(type_id, int) and type_id != 0:
dbconnection.execute("SELECT * FROM game_types WHERE type_id='{}'".format(type_id),
dbutils.dbfields['game_types'])
elif isinstance(type_id, list):
dbconnection.execute(
"SELECT * FROM game_types WHERE type_id IN (" + ','.join(map(str, type_id)) + ")",
dbutils.dbfields['game_types'])
elif type_id == 0:
dbconnection.execute("SELECT * FROM game_types", dbutils.dbfields['game_types'])
if len(dbconnection.last()) == 0: return list()
game_types = dbconnection.last()
game_types = list(map(lambda x: GameType(x, dbconnection=dbconnection), game_types))
if isinstance(type_id, int) and type_id != 0:
return game_types[0]
elif isinstance(type_id, list) or type_id == 0:
return game_types
def get(amplua_id, dbconnection: dbutils.DBConnection = None) -> Amplua:
# FIXME вроде как str передается иногда
if isinstance(amplua_id, list) and len(amplua_id) == 0: return list()
if isinstance(amplua_id, int) and amplua_id != 0:
dbconnection.execute(
"SELECT * FROM ampluas WHERE amplua_id='{}'".format(amplua_id),
dbutils.dbfields['ampluas'])
elif isinstance(amplua_id, list):
dbconnection.execute(
"SELECT * FROM ampluas WHERE amplua_id IN (" +
','.join(map(str, amplua_id)) + ")", dbutils.dbfields['ampluas'])
elif amplua_id == 0:
dbconnection.execute("SELECT * FROM ampluas",
dbutils.dbfields['ampluas'])
if len(dbconnection.last()) == 0: return list()
ampluas = dbconnection.last()
ampluas = list(map(lambda x: Amplua(x, dbconnection=dbconnection),
ampluas))
if isinstance(amplua_id, int) and amplua_id != 0:
return ampluas[0]
elif isinstance(amplua_id, list) or amplua_id == 0:
return ampluas
def most_active(dbconnection:dbutils.DBConnection=None):
data = dbconnection.execute("SELECT user_id, game_id FROM reports WHERE game_id in (SELECT game_id FROM games WHERE MONTH(datetime)=MONTH(NOW()) AND YEAR(datetime)=YEAR(NOW())) AND status=2")
if len(data)==0:
return None
counted = dict()
for tu in data:
user_id, game_id = tu
if user_id not in counted:
counted[user_id] = 1
else:
counted[user_id] += 1
best = max(counted, key=lambda x: counted[x])
for user_id in counted:
if user_id!=best and counted[user_id]==counted[best]:
return None
return {'user':get(best, dbconnection=dbconnection), 'count':counted[best]}
def create(email:str, dbconnection:dbutils.DBConnection=None) -> str:
dbconnection.execute("SELECT activated, token FROM activation WHERE email='{}'".format(email))
if len(dbconnection.last())>0:
if dbconnection.last()[0][0]==1:
raise ValueError('{} already activated'.format(email), email, dbconnection.last()[0][1])
elif dbconnection.last()[0][0]==2:
raise KeyError('{} already registrated'.format(email), email)
else:
dbconnection.execute("DELETE FROM activation WHERE email='{}'".format(email))
token = modules.generate_token()
dbconnection.execute("INSERT INTO activation (email, token) VALUES ('{}', '{}')".format(email, token))
return token
def get(game_id:int, dbconnection:dbutils.DBConnection=None) -> dict:
# registered: user_id:int -> status:int, unregistered: name:str -> (status:int, phone:str)
resp = {'registered': dict(), 'unregistered': dict(), 'additional':list()}
dbconnection.execute("SELECT user_id, status FROM reports WHERE user_id>0 AND game_id={}".format(game_id))
if len(dbconnection.last()) > 0:
for user in dbconnection.last():
user_id, status = user
resp['registered'][user_id] = status
dbconnection.execute("SELECT name, phone, status FROM reports WHERE user_id=0 AND game_id={}".format(game_id))
if len(dbconnection.last()) > 0:
for user in dbconnection.last():
name, phone, status = user[0], user[1], user[2]
resp['unregistered'][name] = (status, phone)
dbconnection.execute("SELECT description, cost FROM additional_charges WHERE game_id={}".format(game_id))
if len(dbconnection.last()) > 0:
for i in dbconnection.last():
resp['additional'].append(i)
return resp
def get(amplua_id, dbconnection:dbutils.DBConnection=None) -> Amplua:
# FIXME вроде как str передается иногда
if isinstance(amplua_id, list) and len(amplua_id) == 0: return list()
if isinstance(amplua_id, int) and amplua_id != 0:
dbconnection.execute("SELECT * FROM ampluas WHERE amplua_id='{}'".format(amplua_id),
dbutils.dbfields['ampluas'])
elif isinstance(amplua_id, list):
dbconnection.execute("SELECT * FROM ampluas WHERE amplua_id IN (" + ','.join(map(str, amplua_id)) + ")",
dbutils.dbfields['ampluas'])
elif amplua_id == 0:
dbconnection.execute("SELECT * FROM ampluas", dbutils.dbfields['ampluas'])
if len(dbconnection.last()) == 0: return list()
ampluas = dbconnection.last()
ampluas = list(map(lambda x: Amplua(x, dbconnection=dbconnection), ampluas))
if isinstance(amplua_id, int) and amplua_id != 0:
return ampluas[0]
elif isinstance(amplua_id, list) or amplua_id == 0:
return ampluas
def get_tags(post_id:int, dbconnection:dbutils.DBConnection=None) -> list:
dbconnection.execute("SELECT * FROM blog.tags WHERE tag_id IN (SELECT tag_id FROM blog.post_tags WHERE post_id={})".format(post_id), _fields['tags'])
if len(dbconnection.last())==0: return list()
return list(map(lambda x: Tag(x), dbconnection.last()))
def get_subscribed(command_id:int, dbconnection:dbutils.DBConnection=None) -> list:
dbconnection.execute("SELECT user_id FROM usergames WHERE comand_id='{}'".format(command_id))
if len(dbconnection.last()) == 0: return list()
return list(map(lambda x: x[0], dbconnection.last()))
def get_comand_ids(game_id:int, dbconnection:dbutils.DBConnection=None) -> list:
dbconnection.execute("SELECT comand_id FROM comands WHERE game_id='{}'".format(game_id))
if len(dbconnection.last()) == 0: return list()
return list(map(lambda x: x[0], dbconnection.last()))
def remove_friend(user_id:int, friend_id:int, dbconnection:dbutils.DBConnection=None):
if not are_friends(user_id, friend_id, dbconnection=dbconnection):
raise ValueError("User <{}> do not have friend <{}>".format(user_id, friend_id))
dbconnection.execute("DELETE FROM friends WHERE user_id1={} AND user_id2={}".format(user_id, friend_id))
cacher.drop_by_table_name('friends', 'user_id', user_id)
def setvkuserid(user_id:int, vkuserid:int, dbconnection:dbutils.DBConnection=None):
dbconnection.execute("UPDATE users SET vkuserid={} WHERE user_id={}".format(vkuserid, user_id))
def status(email:str, dbconnection:dbutils.DBConnection=None) -> int:
return dbconnection.execute("SELECT activated FROM activation WHERE email='{}'".format(email))[0][0]
def get_friends(user_id:int, dbconnection:dbutils.DBConnection=None) -> list:
friends = dbconnection.execute("SELECT user_id2 FROM friends WHERE user_id1={}".format(user_id))
return list(map(lambda x: x[0], friends)) if len(friends) > 0 else list()
def get_all_tags(dbconnection:dbutils.DBConnection=None) -> list:
dbconnection.execute("SELECT * FROM blog.tags", _fields['tags'])
return list(map(lambda x: Tag(x), dbconnection.last()))
def get_post(post_id:int, dbconnection:dbutils.DBConnection=None) -> BlogPost:
dbconnection.execute("SELECT * FROM blog.posts WHERE post_id={}".format(post_id), _fields['posts'])
if len(dbconnection.last())==0: return None
return BlogPost(dbconnection.last()[0], dbconnection=dbconnection)
def get_posts_by_tags(tag_ids:list, dbconnection:dbutils.DBConnection=None) -> list:
dbconnection.execute("SELECT * FROM blog.posts WHERE post_id IN (SELECT post_id FROM blog.post_tags WHERE tag_id IN ({})) AND status>=0".format(', '.join(tag_ids)), _fields['posts'])
if len(dbconnection.last())==0: return list()
return list(map(lambda x: Tag(x), dbconnection.last()))
def get_moderating_posts(dbconnection:dbutils.DBConnection=None) -> list:
dbconnection.execute("SELECT * FROM blog.posts WHERE status=0", _fields['posts'])
if len(dbconnection.last())==0: return list()
return list(map(lambda x: BlogPost(x, dbconnection=dbconnection), dbconnection.last()))
def register(email:str, dbconnection:dbutils.DBConnection=None):
dbconnection.execute("UPDATE activation SET activated=2 WHERE email='{}'".format(email))
cacher.drop_by_table_name('activation', 'email', email)
def get_tag(tag_id:int, dbconnection:dbutils.DBConnection=None) -> BlogPost:
dbconnection.execute("SELECT * FROM blog.tags WHERE tag_id={}".format(tag_id), _fields['posts'])
if len(dbconnection.last())==0: return None
return Tag(dbconnection.last()[0])
def get(token:str, dbconnection:dbutils.DBConnection=None) -> str:
dbconnection.execute("SELECT email FROM activation WHERE token='{}'".format(token))
if len(dbconnection.last())==0:
raise ValueError('No email for <{}>'.format(token))
return dbconnection.last()[0][0]
def get_count(user_id:int, all:bool=False, dbconnection:dbutils.DBConnection=None) -> int:
return int(dbconnection.execute("SELECT COUNT(*) FROM notifications WHERE datetime<NOW() AND user_id={}{}".format(
user_id, ' AND `read`=0' if not all else ''))[0][0])
def are_friends(user_id_1:int, user_id_2:int, dbconnection:dbutils.DBConnection=None) -> bool:
dbconnection.execute("SELECT * FROM friends WHERE user_id1={} AND user_id2={}".format(user_id_1, user_id_2))
return len(dbconnection.last()) != 0