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_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 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(court_id, city_id: int = 1, dbconnection: dbutils.DBConnection = None) -> Court: if isinstance(court_id, str) and len(court_id.split(',')) > 0: court_id = splitstrlist(court_id) if len(court_id) == 1: court_id = court_id[0] if isinstance(court_id, list) and len(court_id) == 0: return list() if isinstance(court_id, int) and court_id != 0: dbconnection.execute( "SELECT * FROM courts WHERE court_id={} AND city_id={}".format( court_id, city_id), dbutils.dbfields['courts']) elif isinstance(court_id, list): dbconnection.execute( "SELECT * FROM courts WHERE court_id IN (" + ','.join(map(str, court_id)) + ") AND city_id={}".format(city_id), dbutils.dbfields['courts']) elif court_id == 0: dbconnection.execute( "SELECT * FROM courts WHERE city_id={}".format(city_id), dbutils.dbfields['courts']) if len(dbconnection.last()) == 0: return list() courts = dbconnection.last() courts = list(map(lambda x: Court(x, dbconnection=dbconnection), courts)) if isinstance(court_id, int) and court_id != 0: return courts[0] elif isinstance(court_id, list) or court_id == 0: return courts
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(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(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(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(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 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 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 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 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_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 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(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(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 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 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 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 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 __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 wrapper(*args, **kwargs): mydb = False if 'dbconnection' not in kwargs or \ ('dbconnection' in kwargs and (not kwargs['dbconnection'] or (kwargs['dbconnection'] and kwargs['dbconnection'].closed))): mydb = True kwargs['dbconnection'] = DBConnection() try: result = func(*args, **kwargs) finally: if mydb: kwargs['dbconnection'].close() return result
def user_game_intersection(user_id: int, game: Game, dbconnection: dbutils.DBConnection = None) -> Game: sql = ( " SELECT * FROM games WHERE game_id!='{game_id}'" " AND game_id IN (SELECT game_id FROM usergames WHERE user_id='{user_id}' AND status=2)" " 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(datetime=game.datetime, duration=game.duration(), user_id=user_id, game_id=game.game_id()) dbconnection.execute(sql, dbutils.dbfields['games']) if len(dbconnection.last()) > 0: return Game(dbconnection.last()[0], dbconnection=dbconnection) return None
def subscribe(user_id: int, game_id: int, reserved: bool = False, 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: if reserved: usergames_reserve(user_id, game_id, dbconnection=dbconnection) else: usergames_subscribe(user_id, game_id, dbconnection=dbconnection) else: status = dbconnection.last()[0][0] if status == 2 or status == 1 and reserved: return if (status == 1 or status == 0) and not reserved: usergames_subscribe(user_id, game_id, dbconnection=dbconnection) write_future_notifications(user_id, game_id, dbconnection=dbconnection) if status == 0 and reserved: usergames_reserve(user_id, game_id, dbconnection=dbconnection)
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 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 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 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_recent(court_id: int = 0, city_id: int = 1, sport_type: int = 0, count: slice = slice(0, 20), old: bool = False, dbconnection: dbutils.DBConnection = None) -> list: sql = "SELECT * FROM games WHERE {} ORDER BY DATETIME {} LIMIT {}, {}" where = list() where.append("city_id={}".format(city_id)) where.append("deleted=0") where.append("datetime+INTERVAL duration MINUTE {} NOW()".format( '>' if not old else '<')) if court_id: where.append("court_id={}".format(court_id)) if sport_type: where.append("sport_type={}".format(sport_type)) sql = sql.format(' AND '.join(where), 'ASC' if not old else 'DESC', count.start if count.start else 0, count.stop) games = dbconnection.execute(sql, dbutils.dbfields['games']) games = list(map(lambda x: Game(x, dbconnection=dbconnection), games)) return games
def status(email:str, dbconnection:dbutils.DBConnection=None) -> int: return dbconnection.execute("SELECT activated FROM activation WHERE email='{}'".format(email))[0][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 __init__(self, db: dbutils.DBConnection): self._db = db self.logs = db.execute( "SELECT * FROM logsdb.access WHERE MONTH(datetime)=MONTH(NOW()) AND YEAR(datetime)=YEAR(NOW()) ORDER BY datetime DESC", dbutils.setdbfields(dbutils.logsdb_connection)['access']) self.logs_dict = dict() self.dates = set() self.logs_by_date = dict() self.ips = set() self.logs_by_ip = dict() self.paths = set() self.logs_by_paths = dict() self.users = set() self.logs_by_users = dict() self.users_by_ips = dict() self.ips_by_users = dict() for log in self.logs: self.logs_dict[log['id']] = log if log['datetime']: date = str(log['datetime'].date()) else: date = '00.00.00' if date not in self.dates: self.dates.add(date) if date not in self.logs_by_date: self.logs_by_date[date] = [log['id']] else: self.logs_by_date[date].append(log['id']) if log['ip'] not in self.ips: self.ips.add(log['ip']) if log['ip'] not in self.logs_by_ip: self.logs_by_ip[log['ip']] = [log['id']] else: self.logs_by_ip[log['ip']].append(log['id']) path = log['path'].split('?')[0] if path not in self.paths: self.paths.add(path) if path not in self.logs_by_ip: self.logs_by_paths[path] = [log['id']] else: self.logs_by_paths[path].append(log['id']) if log['user_id'] != 0: if log['user_id'] not in self.users: self.users.add(log['user_id']) if log['user_id'] not in self.logs_by_ip: self.logs_by_users[log['user_id']] = [log['id']] else: self.logs_by_users[log['user_id']].append(log['id']) if log['user_id'] not in self.ips_by_users: self.ips_by_users[log['user_id']] = {log['ip']} else: self.ips_by_users[log['user_id']].add(log['ip']) if log['ip'] not in self.users_by_ips: self.users_by_ips[log['ip']] = {log['user_id']} else: self.users_by_ips[log['ip']].add(log['user_id']) today = datetime.date.today() dates = [ str(today + datetime.timedelta(days=i)) for i in range(0 - today.weekday(), 7 - today.weekday()) ] self.this_week = [ self.logs_by_date[date] for date in dates if date in self.logs_by_date ] self.today = self.logs_by_date[str(today)]
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 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 user_visits(user: User, db: dbutils.DBConnection) -> int: return db.execute( "SELECT COUNT(*) FROM reports WHERE status=2 AND user_id='{}' AND game_id IN (SELECT game_id FROM games WHERE datetime BETWEEN NOW() AND NOW()-INTERVAL 30 DAY)" .format(user.user_id()))[0][0]
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_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_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 setvkuserid(user_id:int, vkuserid:int, dbconnection:dbutils.DBConnection=None): dbconnection.execute("UPDATE users SET vkuserid={} WHERE user_id={}".format(vkuserid, user_id))
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 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_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_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 setvkuserid(user_id: int, vkuserid: int, dbconnection: dbutils.DBConnection = None): dbconnection.execute( "UPDATE users SET vkuserid={} WHERE user_id={}".format( vkuserid, user_id))
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 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 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_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_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 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 banned(user_id: int, dbconnection: dbutils.DBConnection = None) -> bool: dbconnection.execute( "SELECT user_id FROM banned WHERE user_id={}".format(user_id)) return len(dbconnection.last()) != 0
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_ban_message(user_id: int, dbconnection: dbutils.DBConnection = None) -> str: if not banned(user_id, dbconnection=dbconnection): raise ValueError("User <{}> not banned".format(user_id)) return dbconnection.execute( "SELECT reason FROM banned WHERE user_id={}".format(user_id))[0][0]
def raw(user_id:int, text:str, level:int=0, game_id:int=0, type:int=0, date_time:str=None, dbconnection:dbutils.DBConnection=None): dbconnection.execute( "INSERT INTO notifications (user_id, text, level, game_id, type, datetime) VALUES ({}, '{}', {}, {}, {}, {})".format( user_id, text, level, game_id, type, 'NOW()' if not date_time else "{}".format(date_time))) cacher.drop('notifications_count', user_id)
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)