def post(): if bottle.request.is_ajax: section = bottle.request.forms.get("section") startfrom = int(bottle.request.forms.get("startfrom")) data = list() if section == 'all': with dbutils.dbopen() as db: allusers = users.get(0, count=slice(startfrom, USERS_PER_PAGE), dbconnection=db) page = pages.PageBuilder('user_row') if pages.auth.loggedin(): if len(pages.auth.current().friends()) > 0: friends = pages.auth.current().friends(True) page.add_param('myfriends', friends) for user in allusers: page.add_param('user', user) user_tpl = page.template() data.append(user_tpl) return bottle.json_dumps(data) return '' else: if 'search' in bottle.request.forms: query = bottle.request.forms.get('q') with dbutils.dbopen() as db: allusers = users.search(query, dbconnection=db) page = pages.PageBuilder('users', allusers=users.get(allusers, dbconnection=db), count=len(allusers), search=True, search_q=bottle.request.forms.get('q')) if pages.auth.loggedin() and len(pages.auth.current().friends()) > 0: friends = pages.auth.current().friends(True) page.add_param('myfriends', friends) return page
def conversion(): with dbutils.dbopen(**dbutils.logsdb_connection) as db: db.execute("SHOW TABLES;") logs = list() for i in db.last(): table_name = i[0].split('_') if len(table_name)==2: month, year = table_name logs.extend(db.execute("SELECT * FROM {}_{}".format(month, year), dbutils.setdbfields(dbutils.logsdb_connection)['access'])) logs.extend(db.execute("SELECT * FROM logsdb.access", dbutils.setdbfields(dbutils.logsdb_connection)['access'])) unique_ips = {i['ip'] for i in logs} conv = dict() conv['visitors'] = len(unique_ips) registered = {i['user_id'] for i in logs if i['user_id']!=0} conv['registered'] = len(registered) with dbutils.dbopen() as db: reports = db.execute("SELECT * FROM reports WHERE user_id!=0", dbutils.dbfields['reports']) played = {i['user_id'] for i in reports} conv['played'] = len(played) counted = dict() for report in reports: if report['user_id'] in counted: counted[report['user_id']] += 1 else: counted[report['user_id']] = 1 moreplayed = list(filter(lambda x: counted[x]>1, counted)) conv['moreplayed'] = len(moreplayed) return conv
def get(): with dbutils.dbopen() as db: user_id = pages.auth.current().user_id() user = users.get(user_id, dbconnection=db) page = pages.PageBuilder('profile', user=user) page.add_param( 'user_games', games.get_by_id(games.get_user_played_games(user_id, dbconnection=db), dbconnection=db)) page.add_param( 'responsible_games', games.get_by_id(games.get_responsible_games(user_id, dbconnection=db), dbconnection=db)) page.add_param( 'organizer_games', games.get_by_id(games.get_organizer_games(user_id, dbconnection=db), dbconnection=db)) with dbutils.dbopen(**dbutils.logsdb_connection) as logsdb: logsdb.execute( "SELECT COUNT(DISTINCT(ip)), COUNT(ip) FROM access WHERE path='/profile?user_id={}' and user_id!=0" .format(user_id)) page.add_param('views', logsdb.last()[0][1]) page.add_param('uviews', logsdb.last()[0][0]) return page
def logs_page(): with dbutils.dbopen(**dbutils.logsdb_connection) as db: page = pages.PageBuilder('logs', logs=logs.Logs(db), prob_den=finances.probability_density()) with dbutils.dbopen() as db: dates = db.execute("SELECT regdate FROM users ORDER BY regdate ASC") page.add_param("start_date", dates[0][0]) page.add_param("end_date", dates[-1][0]) dates = list(map(lambda x: str(x[0]), dates)) dates_dict = dict() for date in dates: if date in dates_dict: dates_dict[date] += 1 else: dates_dict[date] = 1 #page.add_param("dates", dates) page.add_param("dates_dict", dates_dict) def daterange(start_date, end_date): for n in range(int((end_date - start_date).days)): yield start_date + datetime.timedelta(n) page.add_param("daterange", daterange) return page
def get_by_id(user_id: int): with dbutils.dbopen() as db: if user_id == pages.auth.current().user_id(): raise bottle.redirect('/profile') user = users.get(user_id, dbconnection=db) if len(user) == 0: raise bottle.HTTPError(404) page = pages.PageBuilder('profile', user=user, myfriend=users.are_friends( pages.auth.current().user_id(), user_id, dbconnection=db)) page.add_param( 'user_games', games.get_by_id(games.get_user_played_games(user_id, dbconnection=db), dbconnection=db)) page.add_param( 'responsible_games', games.get_by_id(games.get_responsible_games(user_id, dbconnection=db), dbconnection=db)) page.add_param('organizer_games', games.get_all(dbconnection=db)) with dbutils.dbopen(**dbutils.logsdb_connection) as logsdb: logsdb.execute( "SELECT COUNT(DISTINCT(ip)), COUNT(ip) FROM access WHERE (path='/profile?user_id={}' or path='/profile/{}') and user_id!=0" .format(user_id, user_id)) page.add_param('views', logsdb.last()[0][1]) page.add_param('uviews', logsdb.last()[0][0]) return page
def auth(): email = bottle.request.query.get('email') passwd = bottle.request.query.get('password') ip = bottle.request.remote_addr with dbutils.dbopen() as db: db.execute( "SELECT user_id FROM users WHERE email='{}' AND passwd='{}'". format(email, passwd)) if len(db.last()) == 0: raise Error.invalid_auth() user_id = db.last()[0][0] au = db.execute( "SELECT * FROM api_auth WHERE user_id={}".format(user_id)) token = generate_token() while True: db.execute("SELECT * FROM api_auth WHERE token='{}'".format(token)) if len(db.last()) > 0: token = generate_token() continue if len(au) > 0: db.execute( "UPDATE api_auth SET token='{}', datetime=NOW(), ip='{}' WHERE user_id={} " .format(token, ip, user_id)) else: db.execute( "INSERT INTO api_auth VALUES ('{}', '{}', NOW(), {}, '{}')" .format(token, email, user_id, ip)) break time.sleep(1.5) return {'token': token, 'user_id': user_id}
def get_notifications(): with dbutils.dbopen() as db: user = current_user(db, True) count = notifications.get_count(user.user_id(), dbconnection=db) notif = dict() notif['all'] = list( map( lambda x: x._notification, notifications.get(user.user_id(), type=0, all=count == 0, dbconnection=db))) notif['subscribed'] = list( map( lambda x: x._notification, notifications.get(user.user_id(), type=1, all=count == 0, dbconnection=db))) if user.userlevel.resporgadmin(): notif['responsible'] = list( map( lambda x: x._notification, notifications.get(user.user_id(), type=2, all=count == 0, dbconnection=db))) return {'notifications': notif, 'all': count == 0}
def subscribe(game_id: int): with dbutils.dbopen() as db: user = current_user(db, detalized=True) game = games.get_by_id(game_id, dbconnection=db) if user.banned(): raise Error.game_conflict(2) if 0 < game.capacity() == len(game.subscribed()): raise Error.game_conflict(4) if user.user_id() in set(game.subscribed()): raise Error.game_conflict(5) another_game = games.user_game_intersection(user.user_id(), game, dbconnection=db) if another_game: raise Error.game_conflict(1, another_game.game_id()) games.subscribe(user.user_id(), game_id, dbconnection=db) if game.datetime.tommorow or game.datetime.today: user = users.get(user.user_id(), dbconnection=db) if user.user_id( ) != pages.auth.current().user_id() else pages.auth.current() message = 'На игру "{}" записался {}'.format( create_link.game(game), create_link.user(user)) utils.spool_func( notificating.site.responsible, game.responsible_user_id(), message, 1, game_id, ) return {'status': 'ok'}
def post(): if pages.auth.loggedin() or 'email' not in bottle.request.forms: raise bottle.HTTPError(404) email = bottle.request.forms.get('email') with dbutils.dbopen() as db: db.execute( "SELECT user_id, passwd FROM users WHERE email='{}'".format(email)) if len(db.last()) == 0: return pages.PageBuilder( 'text', message='Неверный email', description='Пользователь с таким email не найден.') user_id = db.last()[0][0] passwd = db.last()[0][1] utils.spool_func(notificating.mail.raw, 'Восстановление пароля', 'Ваш пароль: {}'.format(passwd), email) notificating.site.all(user_id, 'Вы недавно восстанавливливали пароль', 1) return pages.PageBuilder( 'text', message='Проверьте email', description='Вам было отправлено письмо с дальнейшими инструкциями.' )
def get(): with dbutils.dbopen() as db: user_id = pages.auth.current().user_id() user = users.get(user_id, dbconnection=db) page = pages.PageBuilder('profile', user=user) page.add_param('user_games', games.get_by_id(games.get_user_played_games(user_id, dbconnection=db), dbconnection=db)) page.add_param('responsible_games', games.get_by_id(games.get_responsible_games(user_id, dbconnection=db), dbconnection=db)) page.add_param('organizer_games', games.get_by_id(games.get_organizer_games(user_id, dbconnection=db), dbconnection=db)) with dbutils.dbopen(**dbutils.logsdb_connection) as logsdb: logsdb.execute("SELECT COUNT(DISTINCT(ip)), COUNT(ip) FROM access WHERE path='/profile?user_id={}' and user_id!=0".format(user_id)) page.add_param('views', logsdb.last()[0][1]) page.add_param('uviews', logsdb.last()[0][0]) return page
def edit_post(): params = {i: bottle.request.forms.get(i) for i in bottle.request.forms} city_title = params['city'] params.pop('city') if 'ampluas[]' in params: params.pop('ampluas[]') params['ampluas'] = bottle.request.forms.getall('ampluas[]') params['ampluas'] = '|' + '|'.join(params['ampluas']) + '|' else: params['ampluas'] = '' if 'avatar' in params: if isinstance(params['avatar'], str): images.delete_avatar(pages.auth.current().user_id()) params.pop('avatar') elif 'avatar' in bottle.request.files: images.save_avatar(pages.auth.current().user_id(), bottle.request.files.get('avatar')) with dbutils.dbopen() as db: db.execute("SELECT city_id FROM cities WHERE title='{}'".format(city_title)) if len(db.last()) > 0: params['city_id'] = db.last()[0][0] else: params['city_id'] = 1 sql = "UPDATE users SET {} WHERE user_id={}".format( ', '.join(['{}="{}"'.format(i, params[i]) for i in params]), pages.auth.current().user_id()) db.execute(sql) cacher.drop_by_table_name('users', 'user_id', pages.auth.current().user_id()) raise bottle.redirect('/profile')
def edit_post(): params = {i: bottle.request.forms.get(i) for i in bottle.request.forms} city_title = params['city'] params.pop('city') if 'ampluas[]' in params: params.pop('ampluas[]') params['ampluas'] = bottle.request.forms.getall('ampluas[]') params['ampluas'] = '|' + '|'.join(params['ampluas']) + '|' else: params['ampluas'] = '' if 'avatar' in params: if isinstance(params['avatar'], str): images.delete_avatar(pages.auth.current().user_id()) params.pop('avatar') elif 'avatar' in bottle.request.files: images.save_avatar(pages.auth.current().user_id(), bottle.request.files.get('avatar')) with dbutils.dbopen() as db: db.execute( "SELECT city_id FROM cities WHERE title='{}'".format(city_title)) if len(db.last()) > 0: params['city_id'] = db.last()[0][0] else: params['city_id'] = 1 sql = "UPDATE users SET {} WHERE user_id={}".format( ', '.join(['{}="{}"'.format(i, params[i]) for i in params]), pages.auth.current().user_id()) db.execute(sql) cacher.drop_by_table_name('users', 'user_id', pages.auth.current().user_id()) raise bottle.redirect('/profile')
def drop_logs(*args): yesterday = datetime.date.today()-datetime.timedelta(days=1) year = yesterday.year month = yesterday.month sql = """CREATE TABLE logsdb.`{month}_{year}` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `ip` VARCHAR(15) NOT NULL, `time` FLOAT UNSIGNED NOT NULL DEFAULT '0', `httpmethod` VARCHAR(10) NOT NULL DEFAULT 'GET', `path` VARCHAR(200) NOT NULL, `referer` VARCHAR(200) NOT NULL, `user_id` INT UNSIGNED NOT NULL DEFAULT '0', `useragent` VARCHAR(1000) NOT NULL, `error` VARCHAR(5000) NULL DEFAULT NULL, `error_description` VARCHAR(5000) NULL DEFAULT NULL, `traceback` VARCHAR(10000) NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `id` (`id`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM SELECT * FROM logsdb.access WHERE MONTH(datetime)={month} AND YEAR(datetime)={year}; """.format(month=month, year=year) with dbutils.dbopen(**dbutils.logsdb_connection) as db: try: db.execute(sql) except: return else: db.execute("DELETE FROM logsdb.access WHERE MONTH(datetime)={} AND YEAR(datetime)={}".format(month, year))
def autocreate(game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) newgame = { "description": game.description(), "city_id": game.city_id(), "sport_type": game.sport_type(), "game_type": game.game_type(), "court_id": game.court_id(), "duration": game.duration(), "cost": game.cost(), "capacity": game.capacity(), "responsible_user_id": game.responsible_user_id(), "created_by": game.created_by(), "reserved": game.reserved(), "datetime": str(game.datetime()+datetime.timedelta(days=7)).split('.')[0] } intersection = games.court_game_intersection(newgame['court_id'],newgame['datetime'],newgame['duration'], dbconnection=db) if intersection: return pages.PageBuilder('text', message='Обнаружен конфликт', description='В это время уже идет другая <a href="/games/{}">игра</a>'.format( intersection)) page = check_responsible(newgame['responsible_user_id'], newgame['datetime'], newgame['duration'], db) if page: return page game_id = games.add(dbconnection=db, **newgame) assigned_responsible(game_id, int(newgame['responsible_user_id']), db) raise bottle.redirect("/games/edit/{}".format(game_id))
def delete(game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if game.created_by() != pages.auth.current().user_id() and not pages.auth.current().userlevel.admin(): return pages.templates.permission_denied() games.delete(game_id, dbconnection=db) raise bottle.redirect('/games')
def drop_logs(*args): yesterday = datetime.date.today() - datetime.timedelta(days=1) year = yesterday.year month = yesterday.month sql = """CREATE TABLE logsdb.`{month}_{year}` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `ip` VARCHAR(15) NOT NULL, `time` FLOAT UNSIGNED NOT NULL DEFAULT '0', `httpmethod` VARCHAR(10) NOT NULL DEFAULT 'GET', `path` VARCHAR(200) NOT NULL, `referer` VARCHAR(200) NOT NULL, `user_id` INT UNSIGNED NOT NULL DEFAULT '0', `useragent` VARCHAR(1000) NOT NULL, `error` VARCHAR(5000) NULL DEFAULT NULL, `error_description` VARCHAR(5000) NULL DEFAULT NULL, `traceback` VARCHAR(10000) NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `id` (`id`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM SELECT * FROM logsdb.access WHERE MONTH(datetime)={month} AND YEAR(datetime)={year}; """.format(month=month, year=year) with dbutils.dbopen(**dbutils.logsdb_connection) as db: try: db.execute(sql) except: return else: db.execute( "DELETE FROM logsdb.access WHERE MONTH(datetime)={} AND YEAR(datetime)={}" .format(month, year))
def edit(game_id: int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if len(game) == 0: raise bottle.HTTPError(404) if pages.auth.current().user_id() != game.created_by() and \ pages.auth.current().user_id() != game.responsible_user_id() and \ not pages.auth.current().userlevel.admin(): return pages.templates.permission_denied() _sport_types = sport_types.get(0, dbconnection=db) _game_types = game_types.get(0, dbconnection=db) _cities = cities.get(0, dbconnection=db) _courts = courts.get(0, dbconnection=db) responsibles = users.get(0, 2, dbconnection=db) unsubscribed = games.get_unsubscribed_users(game_id, dbconnection=db) unsubscribed_list = list() db.execute( "SELECT * FROM users WHERE user_id IN (SELECT DISTINCT user_id FROM reports WHERE user_id!=0 AND status=2 AND game_id IN (SELECT game_id FROM games WHERE deleted=0 AND datetime+INTERVAL duration MINUTE < NOW() AND court_id='{}' AND sport_type='{}' AND game_type='{}'))" .format( # as long as my dick game.court_id(), game.sport_type(), game.game_type()), dbutils.dbfields['users']) last_users = list(map(lambda x: User(x, db), db.last())) for i in unsubscribed: user = users.get(i[0], dbconnection=db) dt = i[1] unsubscribed_list.append((user, dt)) return pages.PageBuilder('editgame', game=game, sports=_sport_types, game_types=_game_types, cities=_cities, courts=_courts, responsibles=responsibles, unsubscribed=unsubscribed_list, last_users=last_users)
def get_code(): code = bottle.request.query.code try: access_token, user_id, email = vk.auth_code(code, '/auth') except ValueError as e: return pages.PageBuilder( 'auth', error=e.vkerror['error'], error_description=e.vkerror['error_description']) with dbutils.dbopen() as db: db.execute( "SELECT email, passwd FROM users WHERE vkuserid='{}'".format( user_id)) if not email or len(db.last()) == 0: return pages.PageBuilder( 'auth', error='Ошибка авторизации', error_description="Пользователь не найден.") email = db.last()[0][0] password = db.last()[0][1] try: pages.auth.login(email, password) except ValueError: time.sleep(3) return pages.PageBuilder( 'auth', error='Ошибка авторизации', error_description='Неверный email или пароль') raise bottle.redirect('/games')
def index(): with dbutils.dbopen() as db: respdict = get_users(db) fin = finances.get_current_month(dbconnection=db) fin = finances.Finances(fin, db) respdict.update(get_logs(db)) return pages.PageBuilder('admin', fin=fin, **respdict)
def auth(): with dbutils.dbopen(**connection) as db: db.execute("TRUNCATE auth_sessions;") accounts = db.execute("SELECT * FROM accounts", ['login', 'password']) errors = list() success = list() for account in accounts: try: access_token, user_id, expires = vk.auth( account['login'], account['password'], config.vkspam.app_id, config.vkspam.scope) except Exception as e: errors.append((account['login'], e)) else: db.execute( "INSERT INTO auth_sessions (login, access_token) VALUES ('{}', '{}')" .format(account['login'], access_token)) success.append(account['login']) errors = [{ 'login': e[0], 'error': Error.auth_error(*e).json() } for e in errors] if len(success) > 0: if len(errors) == 0: return response({'success': success}) else: return response({'success': success, 'errors': errors}) else: return error({'errors': errors})
def edit_post(game_id: int): with dbutils.dbopen() as db: params = {i: bottle.request.forms.get(i) for i in bottle.request.forms} params.pop('submit_edit') params['datetime'] = params['date'] + ' ' + params['time'] + ':00' params.pop('date') params.pop('time') params.pop('game_id') responsible_old = games.get_by_id( game_id, dbconnection=db).responsible_user_id() if responsible_old != int(params['responsible_user_id']): page = check_responsible(params['responsible_user_id'], params['datetime'], params['duration'].split(' ')[0], db) if page: return page assigned_responsible(game_id, int(params['responsible_user_id']), db) unassigned_responsible(game_id, responsible_old, db) games.update(game_id, dbconnection=db, **params) game = games.get_by_id(game_id, dbconnection=db) if not game.datetime.passed: for user_id in game.subscribed(): utils.spool_func( notificating.site.subscribed, user_id, 'Игра "{}" была отредактирована.<br>Проверьте изменения!'. format(modules.create_link.game(game)), 1, game_id) if responsible_old == int(params['responsible_user_id']): utils.spool_func( notificating.site.responsible, responsible_old, 'Игра "{}" была отредактирована.<br>Проверьте изменения!'. format(modules.create_link.game(game)), 1, game_id) raise bottle.redirect('/games/{}'.format(game_id))
def _writedb(**kwargs): try: with dbutils.dbopen(**dbutils.logsdb_connection) as db: keys = list(kwargs.keys()) if 'traceback' in kwargs: kwargs['traceback'] = base64.b64encode( kwargs['traceback'].encode()).decode() if 'error' in kwargs: kwargs['error'] = kwargs['error'].replace('"', '').replace("'", '') if 'error_description' in kwargs: kwargs['error_description'] = kwargs[ 'error_description'].replace('"', '').replace("'", '') values = [kwargs[i] for i in keys] db.execute( "INSERT INTO access (" + ", ".join(keys) + ") VALUES (" + ','.join(list(map(lambda x: "'{}'".format(x), values))) + ")") except Exception as e: try: print(e) except: pass return uwsgi.SPOOL_OK # return uwsgi.SPOOL_RETRY TODO return uwsgi.SPOOL_OK
def add_post(): with dbutils.dbopen() as db: params = {i: bottle.request.forms.get(i) for i in bottle.request.forms} params.pop('submit_add') params['datetime'] = params['date'] + ' ' + params['time'] + ':00' params.pop('date') params.pop('time') params['created_by'] = pages.auth.current().user_id() intersection = games.court_game_intersection( params['court_id'], params['datetime'], params['duration'].encode().split(b' ')[0].decode(), dbconnection=db) if intersection: return pages.PageBuilder( 'text', message='Обнаружен конфликт', description= 'В это время уже идет другая <a href="/games/{}">игра</a>'. format(intersection)) page = check_responsible(params['responsible_user_id'], params['datetime'], params['duration'].split(' ')[0], db) if page: return page if int(params['capacity']) > 0: params['reserved'] = round(int(params['capacity']) / 4) game_id = games.add(dbconnection=db, **params) assigned_responsible(game_id, int(params['responsible_user_id']), db) return bottle.redirect('/games/{}'.format(game_id))
def edit(game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if len(game) == 0: raise bottle.HTTPError(404) if pages.auth.current().user_id() != game.created_by() and \ pages.auth.current().user_id() != game.responsible_user_id() and \ not pages.auth.current().userlevel.admin(): return pages.templates.permission_denied() _sport_types = sport_types.get(0, dbconnection=db) _game_types = game_types.get(0, dbconnection=db) _cities = cities.get(0, dbconnection=db) _courts = courts.get(0, dbconnection=db) responsibles = users.get(0, 2, dbconnection=db) unsubscribed = games.get_unsubscribed_users(game_id, dbconnection=db) unsubscribed_list = list() db.execute("SELECT * FROM users WHERE user_id IN (SELECT DISTINCT user_id FROM reports WHERE user_id!=0 AND status=2 AND game_id IN (SELECT game_id FROM games WHERE deleted=0 AND datetime+INTERVAL duration MINUTE < NOW() AND court_id='{}' AND sport_type='{}' AND game_type='{}'))".format( # as long as my dick game.court_id(), game.sport_type(), game.game_type()), dbutils.dbfields['users']) last_users = list(map(lambda x: User(x, db), db.last())) for i in unsubscribed: user = users.get(i[0], dbconnection=db) dt = i[1] unsubscribed_list.append((user, dt)) return pages.PageBuilder('editgame', game=game, sports=_sport_types, game_types=_game_types, cities=_cities, courts=_courts, responsibles=responsibles, unsubscribed=unsubscribed_list, last_users=last_users)
def edit_post(game_id:int): with dbutils.dbopen() as db: params = {i: bottle.request.forms.get(i) for i in bottle.request.forms} params.pop('submit_edit') params['datetime'] = params['date'] + ' ' + params['time'] + ':00' params.pop('date') params.pop('time') params.pop('game_id') responsible_old = games.get_by_id(game_id, dbconnection=db).responsible_user_id() if responsible_old != int(params['responsible_user_id']): page = check_responsible(params['responsible_user_id'], params['datetime'], params['duration'].split(' ')[0], db) if page: return page assigned_responsible(game_id, int(params['responsible_user_id']), db) unassigned_responsible(game_id, responsible_old, db) games.update(game_id, dbconnection=db, **params) game = games.get_by_id(game_id, dbconnection=db) if not game.datetime.passed: for user_id in game.subscribed(): utils.spool_func(notificating.site.subscribed, user_id, 'Игра "{}" была отредактирована.<br>Проверьте изменения!'.format( modules.create_link.game(game)), 1, game_id) if responsible_old == int(params['responsible_user_id']): utils.spool_func(notificating.site.responsible, responsible_old, 'Игра "{}" была отредактирована.<br>Проверьте изменения!'.format( modules.create_link.game(game)), 1, game_id) raise bottle.redirect('/games/{}'.format(game_id))
def _get_games(*args): ptype = args[0] sport_type = 0 page_n = 1 if ptype == 'all' or ptype == 'old': page_n = args[1] elif ptype == 'sport': sport_type = args[1] page_n = args[2] with dbutils.dbopen() as db: count = len( games.get_recent(sport_type=sport_type, count=slice(0, 99999), old=ptype == 'old', dbconnection=db)) # TODO: REWORK total_pages = count // GAMES_PER_PAGE + ( 1 if count % GAMES_PER_PAGE != 0 else 0) if page_n > total_pages and count > 0: if not bottle.request.is_ajax: raise bottle.HTTPError(404) else: return {"stop": True, "games": list()} sports = sport_types.get(0, dbconnection=db) if not count: if not bottle.request.is_ajax: return pages.PageBuilder("games", games=list(), sports=sports, bysport=sport_type, old=ptype == 'old', total_count=0) else: return {"stop": True, "games": list()} allgames = games.get_recent( sport_type=sport_type, old=ptype == 'old', count=slice(*modules.pager(page_n, count=GAMES_PER_PAGE)), dbconnection=db) if not bottle.request.is_ajax: return pages.PageBuilder('games', games=allgames, sports=sports, total_count=count, bysport=sport_type, old=ptype == 'old') else: data = {"stop": page_n >= total_pages, "games": list()} page = pages.PageBuilder("game", tab_name="all") for game in allgames: page.add_param("game", game) game_tpl = page.template() data["games"].append(game_tpl) return data
def subscribed_list(game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if len(game) == 0: raise bottle.HTTPError(404) if pages.auth.current().user_id() != game.created_by() and pages.auth.current().user_id() != game.responsible_user_id() and not pages.auth.current().userlevel.admin(): return pages.templates.permission_denied() return pages.PageBuilder('list', game=game)
def delete(game_id: int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if game.created_by() != pages.auth.current().user_id( ) and not pages.auth.current().userlevel.admin(): return pages.templates.permission_denied() games.delete(game_id, dbconnection=db) raise bottle.redirect('/games')
def game_notification(): with dbutils.dbopen() as db: db.execute( "SELECT * FROM games WHERE deleted=0 AND notificated=0 AND NOW()<datetime", dbutils.dbfields['games']) if len(db.last()) == 0: return games = list(map(lambda x: Game(x, dbconnection=db), db.last())) weekdays = list( filter(lambda x: x.datetime.date().weekday() < 5, games)) weekends = list( filter(lambda x: x.datetime.date().weekday() > 4, games)) notify = list() if len(weekdays) > 0: morning_day = list( filter(lambda x: x.datetime.time().hour < 18, weekdays)) if len(morning_day) > 0: now = datetime.datetime.now() for game in morning_day: if game.datetime.tommorow and 18 < now.hour < 19: notify.append(game) evening = list( filter(lambda x: 18 <= x.datetime.time().hour, weekdays)) if len(evening) > 0: now = datetime.datetime.now() for game in evening: if game.datetime.today and 9 < now.hour < 10: notify.append(game) if len(weekends) > 0: morning_day = list( filter(lambda x: x.datetime.time().hour < 18, weekends)) if len(morning_day) > 0: now = datetime.datetime.now() for game in morning_day: if game.datetime.tommorow and 9 < now.hour < 10: notify.append(game) evening = list( filter(lambda x: 18 <= x.datetime.time().hour, weekends)) if len(evening) > 0: now = datetime.datetime.now() for game in evening: if game.datetime.tommorow and 18 < now.hour < 19: notify.append(game) for game in notify: try: send_notification(game) except Exception as e: logging.message( 'Error on sending upcoming game notifications for <{}>'. format(game.game_id()), e) else: db.execute( "UPDATE games SET notificated=1 WHERE game_id={}".format( game.game_id()))
def get_blog(): with dbutils.dbopen() as db: posts = blog.get_posts() tags = blog.get_all_tags() tags_posts = list() for tag in tags: tags_posts.append((tag, len(blog.get_posts_by_tag(tag.tag_id(), dbconnection=db)))) tags_posts = sorted(tags_posts, reverse=True, key= lambda x: x[1])[:11] # первые десять return pages.PageBuilder('blog_main', posts=posts, alltags=tags_posts)
def recount_finances(): with dbutils.dbopen() as db: db.execute("TRUNCATE finances;") db.execute("TRUNCATE responsible_games_salary;") db.execute("DELETE FROM finance_balance WHERE user_id!=0") db.execute("SELECT game_id FROM games WHERE deleted=0 AND datetime<NOW() AND game_id IN (SELECT DISTINCT game_id FROM reports)") for game_id in db.last().copy(): finances.add_game_finances(game_id[0], dbconnection=db) raise bottle.redirect('/admin/finances')
def add(): with dbutils.dbopen() as db: _sport_types = sport_types.get(0, dbconnection=db) _cities = cities.get(0, dbconnection=db) _court_types = court_types.get(0, dbconnection=db) return pages.PageBuilder('addcourt', sport_types=_sport_types, cities=_cities, court_types=_court_types)
def add(): with dbutils.dbopen() as db: _sports = sport_types.get(0, dbconnection=db) _game_types = game_types.get(0, dbconnection=db) _cities = cities.get(0, dbconnection=db) _courts = courts.get(0, dbconnection=db) responsibles = users.get(0, 2, dbconnection=db) return pages.PageBuilder("addgame", sports=_sports, game_types=_game_types, cities=_cities, courts=_courts, responsibles=responsibles)
def notify_array(game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) db.execute("SELECT DISTINCT user_id FROM reports WHERE user_id!=0 AND status=2 AND game_id IN (SELECT game_id FROM games WHERE deleted=0 AND datetime+INTERVAL duration MINUTE < NOW() AND court_id='{}' AND sport_type='{}' AND game_type='{}')".format( # as long as my dick game.court_id(), game.sport_type(), game.game_type())) if len(db.last())==0: return json.dumps({'users':list(), 'count':0}) users_ = users.get(list(map(lambda x: x[0], db.last())), dbconnection=db) users_ = list(map(lambda x: {'user_id':x.user_id(), 'name':str(x.name), 'visits':user_visits(x, db)}, users_)) return json.dumps({'count':len(users_), 'users':users_})
def subscribed_list(game_id: int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if len(game) == 0: raise bottle.HTTPError(404) if pages.auth.current().user_id() != game.created_by( ) and pages.auth.current().user_id() != game.responsible_user_id( ) and not pages.auth.current().userlevel.admin(): return pages.templates.permission_denied() return pages.PageBuilder('list', game=game)
def get(): with dbutils.dbopen() as db: count = db.execute("SELECT COUNT(user_id) FROM users")[0][0] allusers = users.get(0, count=slice(*modules.pager(1, count=USERS_PER_PAGE))) page = pages.PageBuilder('users', allusers=allusers, count=count) if pages.auth.loggedin(): if len(pages.auth.current().friends()) > 0: friends = pages.auth.current().friends(True) page.add_param('myfriends', friends) return page
def settings_post(): with dbutils.dbopen() as db: send_email = 'email_notify' in bottle.request.forms show_phone = bottle.request.forms['phone'] db.execute("UPDATE users SET settings='{}' WHERE user_id={}".format( json.dumps({'send_mail': send_email, 'show_phone': show_phone}), pages.auth.current().user_id())) user = users.get(pages.auth.current().user_id(), dbconnection=db) pages.auth.reloaduser(user._pure) #cacher.drop_by_table_name('users', 'user_id', pages.auth.current().user_id()) raise bottle.redirect("/profile/settings")
def continue_registration(): with dbutils.dbopen() as db: db.execute("SELECT token, email FROM activation WHERE activated=0 AND again=0 AND datetime>datetime+INTERVAL 6 HOUR") if len(db.last())==0: return for pair in db.last(): try: utils.spool_func(notificating.mail.tpl.email_confirm_again, *pair) db.execute("UPDATE activation SET again=1 WHERE email={}".format(pair[1])) except Exception as e: logging.message('Error while sending activation email to <{}>'.format(pair[1]), e)
def edit(): with dbutils.dbopen() as db: _cities = cities.get(0, dbconnection=db) user = users.get(pages.auth.current().user_id(), dbconnection=db) _ampluas = ampluas.get(0, dbconnection=db) _ampluas = { sport_type_title: list(filter(lambda x: x.sport_type(True).title() == sport_type_title, _ampluas)) for sport_type_title in {i.sport_type(True).title() for i in _ampluas}} return pages.PageBuilder('editprofile', user=user, cities=_cities, ampluas=_ampluas, haveavatar=images.have_avatar(pages.auth.current().user_id()))
def get(): with dbutils.dbopen() as db: count = db.execute("SELECT COUNT(user_id) FROM users")[0][0] allusers = users.get( 0, count=slice(*modules.pager(1, count=USERS_PER_PAGE))) page = pages.PageBuilder('users', allusers=allusers, count=count) if pages.auth.loggedin(): if len(pages.auth.current().friends()) > 0: friends = pages.auth.current().friends(True) page.add_param('myfriends', friends) return page
def recount_finances(): with dbutils.dbopen() as db: db.execute("TRUNCATE finances;") db.execute("TRUNCATE responsible_games_salary;") db.execute("DELETE FROM finance_balance WHERE user_id!=0") db.execute( "SELECT game_id FROM games WHERE deleted=0 AND datetime<NOW() AND game_id IN (SELECT DISTINCT game_id FROM reports)" ) for game_id in db.last().copy(): finances.add_game_finances(game_id[0], dbconnection=db) raise bottle.redirect('/admin/finances')
def login(self, email: str, password: str): if self.loggedin(): return with dbutils.dbopen() as db: user = db.execute( "SELECT * FROM users WHERE email='{}' AND passwd='{}'".format( email, password), dbutils.dbfields['users']) if len(user) == 0: raise ValueError("Invalid email or password") #db.execute("UPDATE users SET lasttime=NOW() WHERE user_id={}".format(user[0]['user_id'])) #cacher.drop_by_table_name('users', 'user_id', user[0]['user_id']) set_cookie('user', pickle.dumps(user[0]))
def edit(court_id: int): with dbutils.dbopen() as db: court = courts.get(court_id, dbconnection=db) _sport_types = sport_types.get(0, dbconnection=db) _cities = cities.get(0, dbconnection=db) _court_types = court_types.get(0, dbconnection=db) return pages.PageBuilder('editcourt', sport_types=_sport_types, cities=_cities, court=court, court_types=_court_types)
def get_report(game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if len(game) == 0: raise bottle.HTTPError(404) if game.created_by() != pages.auth.current().user_id() and game.responsible_user_id() != pages.auth.current().user_id() and not pages.auth.current().userlevel.admin(): return pages.templates.permission_denied() if not game.datetime.passed: return pages.templates.message("Вы не можете отправить отчет по игре", "Игра еще не закончилась") return pages.PageBuilder("report", game=game, showreport=game.reported(), ask_autocreate=('ask_autocreate' in bottle.request.query and game.reported()))
def unreserve(user_id:int, game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if game.datetime.passed: raise bottle.HTTPError(404) if pages.auth.current().banned(): return pages.PageBuilder("game", game=game, conflict=2) if user_id not in set(game.reserved_people()): return pages.PageBuilder("game", game=game, conflict=11) games.unsubscribe(user_id, game_id, dbconnection=db) game = games.get_by_id(game_id, dbconnection=db) return pages.PageBuilder("game", game=game)
def login(self, email:str, password:str): if self.loggedin(): return with dbutils.dbopen() as db: user = db.execute( "SELECT * FROM users WHERE email='{}' AND passwd='{}'".format( email, password), dbutils.dbfields['users']) if len(user) == 0: raise ValueError("Invalid email or password") #db.execute("UPDATE users SET lasttime=NOW() WHERE user_id={}".format(user[0]['user_id'])) #cacher.drop_by_table_name('users', 'user_id', user[0]['user_id']) set_cookie('user', pickle.dumps(user[0]))
def message(msg: str, e: Exception): with dbutils.dbopen(**dbutils.logsdb_connection) as db: error = e.__class__.__name__.replace('"', '').replace("'", '') error_description = ','.join(map(str, e.args)).replace('"', '').replace("'", '') traceback = base64.b64encode( modules.extract_traceback(e).encode()).decode() db.execute( "INSERT INTO logsdb.messages (message, error, error_description, traceback) VALUES ('{}', '{}', '{}', '{}')" .format(msg, error, error_description, traceback))
def get_article(post_id:int): with dbutils.dbopen() as db: post = blog.get_post(post_id, dbconnection=db) if not post: raise bottle.HTTPError(404) tags = blog.get_all_tags(dbconnection=db) tags_posts = list() for tag in tags: tags_posts.append((tag, len(blog.get_posts_by_tag(tag.tag_id(), dbconnection=db)))) tags_posts = sorted(tags_posts, reverse=True, key= lambda x: x[1])[:11] # первые десять seo_info = SeoInfo({'tplname':'blog_post', 'keywords':post.keywords(), 'description':post.description()}) return pages.PageBuilder('blog_post', post=post, alltags=tags_posts, seo_info=seo_info)
def unreserve(user_id: int, game_id: int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if game.datetime.passed: raise bottle.HTTPError(404) if pages.auth.current().banned(): return pages.PageBuilder("game", game=game, conflict=2) if user_id not in set(game.reserved_people()): return pages.PageBuilder("game", game=game, conflict=11) games.unsubscribe(user_id, game_id, dbconnection=db) game = games.get_by_id(game_id, dbconnection=db) return pages.PageBuilder("game", game=game)
def oldvactivation(): token = bottle.request.query.get('token') with dbutils.dbopen() as db: db.execute("SELECT email, activated FROM activation WHERE token='{}'".format(token)) if len(db.last())==0: return pages.templates.message('Ошибка', 'Неверный код.') email = db.last()[0][0] activated = db.last()[0][0] if activated==2: return pages.templates.message('Ошибка', 'Вы уже активировали свой профиль.') db.execute("UPDATE activation SET activated=2 WHERE email='{}'".format(email)) return pages.templates.message('Успешно', 'Вы активировали свой профиль.')
def get_by_id(user_id:int): with dbutils.dbopen() as db: if user_id == pages.auth.current().user_id(): raise bottle.redirect('/profile') user = users.get(user_id, dbconnection=db) if len(user) == 0: raise bottle.HTTPError(404) page = pages.PageBuilder('profile', user=user, myfriend=users.are_friends( pages.auth.current().user_id(), user_id, dbconnection=db)) page.add_param('user_games', games.get_by_id(games.get_user_played_games(user_id, dbconnection=db), dbconnection=db)) page.add_param('responsible_games', games.get_by_id(games.get_responsible_games(user_id, dbconnection=db), dbconnection=db)) page.add_param('organizer_games', games.get_all(dbconnection=db)) with dbutils.dbopen(**dbutils.logsdb_connection) as logsdb: logsdb.execute("SELECT COUNT(DISTINCT(ip)), COUNT(ip) FROM access WHERE (path='/profile?user_id={}' or path='/profile/{}') and user_id!=0".format(user_id, user_id)) page.add_param('views', logsdb.last()[0][1]) page.add_param('uviews', logsdb.last()[0][0]) return page
def notify(game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) db.execute("SELECT DISTINCT user_id FROM reports WHERE user_id!=0 AND status=2 AND game_id IN (SELECT game_id FROM games WHERE deleted=0 AND datetime+INTERVAL duration MINUTE < NOW() AND court_id='{}' AND sport_type='{}' AND game_type='{}')".format( # as long as my dick game.court_id(), game.sport_type(), game.game_type())) if len(db.last())==0: return json.dumps({'users':list(), 'count':0}) users_ = users.get(list(map(lambda x: x[0], db.last())), dbconnection=db) users_ = list(filter(lambda x: user_visits(x, db)<3 and x.user_id() not in set(game.subscribed()), users_)) for user in users_: send_notify_email(user.user_id(), game.game_id()) ids = list(map(lambda x: x.user_id(), users_)) return json.dumps({'count':len(ids), 'users':[[user.user_id(), str(user.name)] for user in users_]})
def get_blog(): with dbutils.dbopen() as db: posts = blog.get_posts() tags = blog.get_all_tags() tags_posts = list() for tag in tags: tags_posts.append( (tag, len(blog.get_posts_by_tag(tag.tag_id(), dbconnection=db)))) tags_posts = sorted(tags_posts, reverse=True, key=lambda x: x[1])[:11] # первые десять return pages.PageBuilder('blog_main', posts=posts, alltags=tags_posts)
def logs_page_text(): with dbutils.dbopen(**dbutils.logsdb_connection) as db: logs_ = logs.Logs(db) yield 'Посещений в этом месяце: {} ({} уникальных {}%)'.format(len(logs_.logs), len(logs_.ips), percents(len(logs_.ips), len(logs_.logs))) this_week = sum([len(day) for day in logs_.this_week]) unique_week = {logs_.logs_dict[i]['ip'] for day in logs_.this_week for i in day} yield 'Посещений в на этой неделе: {} ({}%) ({} уникальных {}%)'.format(this_week, percents(this_week, len(logs_.logs)), len(unique_week), percents(len(unique_week), this_week)) unique_day = {logs_.logs_dict[i]['ip'] for i in logs_.today} yield 'Посещений сегодня: {} ({}%) ({} уникальных {}%)'.format(len(logs_.today), percents(len(logs_.today), this_week), len(unique_day), percents(len(unique_day), len(logs_.today))) yield '' registered = len(logs_.ips-set(list(logs_.users_by_ips))) yield 'Пользователей в системе: {} ({}%)'.format(registered, percents(registered, len(logs_.ips)))
def conversion(): with dbutils.dbopen(**dbutils.logsdb_connection) as db: db.execute("SHOW TABLES;") logs = list() for i in db.last(): table_name = i[0].split('_') if len(table_name) == 2: month, year = table_name logs.extend( db.execute( "SELECT * FROM {}_{}".format(month, year), dbutils.setdbfields( dbutils.logsdb_connection)['access'])) logs.extend( db.execute( "SELECT * FROM logsdb.access", dbutils.setdbfields(dbutils.logsdb_connection)['access'])) unique_ips = {i['ip'] for i in logs} conv = dict() conv['visitors'] = len(unique_ips) registered = {i['user_id'] for i in logs if i['user_id'] != 0} conv['registered'] = len(registered) with dbutils.dbopen() as db: reports = db.execute("SELECT * FROM reports WHERE user_id!=0", dbutils.dbfields['reports']) played = {i['user_id'] for i in reports} conv['played'] = len(played) counted = dict() for report in reports: if report['user_id'] in counted: counted[report['user_id']] += 1 else: counted[report['user_id']] = 1 moreplayed = list(filter(lambda x: counted[x] > 1, counted)) conv['moreplayed'] = len(moreplayed) return conv
def fromreserve(user_id:int, game_id:int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if user_id not in set(game.reserved_people()): return pages.PageBuilder("game", game=game, conflict=11) if user_id in set(game.subscribed()): return pages.PageBuilder("game", game=game, conflict=5) if game.capacity() > 0 and len(game.subscribed()) == game.capacity(): return pages.PageBuilder("game", game=game, conflict=4) games.unsubscribe(user_id, game_id, dbconnection=db) games.subscribe(user_id, game_id, dbconnection=db) game = games.get_by_id(game_id, dbconnection=db) return pages.PageBuilder("game", game=game)
def fromreserve(user_id: int, game_id: int): with dbutils.dbopen() as db: game = games.get_by_id(game_id, dbconnection=db) if user_id not in set(game.reserved_people()): return pages.PageBuilder("game", game=game, conflict=11) if user_id in set(game.subscribed()): return pages.PageBuilder("game", game=game, conflict=5) if game.capacity() > 0 and len(game.subscribed()) == game.capacity(): return pages.PageBuilder("game", game=game, conflict=4) games.unsubscribe(user_id, game_id, dbconnection=db) games.subscribe(user_id, game_id, dbconnection=db) game = games.get_by_id(game_id, dbconnection=db) return pages.PageBuilder("game", game=game)
def get(): user_id = pages.auth.current().user_id() with dbutils.dbopen() as db: if 'deleteall' in bottle.request.query: models.notifications.delete(-user_id) raise bottle.redirect('/notifications') count = models.notifications.get_count(user_id, dbconnection=db) notifications = dict() notifications['all'] = models.notifications.get(user_id, type=0, all=count == 0, dbconnection=db) notifications['subscribed'] = models.notifications.get(user_id, type=1, all=count == 0, dbconnection=db) if pages.auth.current().userlevel.resporgadmin(): notifications['responsible'] = models.notifications.get(user_id, type=2, all=count == 0, dbconnection=db) return pages.PageBuilder("notifications", notifications=notifications, all=count == 0)
def new_finances(month:int=0, year:int=0): with dbutils.dbopen() as db: dates = db.execute("SELECT DISTINCT MONTH(datetime), YEAR(datetime) FROM finances ORDER BY datetime DESC") if not month: month = datetime.date.today().month if not year: month = datetime.date.today().month year = datetime.date.today().year outlays = finances.get_outlays_by_date(month, year, dbconnection=db) games_finances = finances.get_by_date(month, year, dbconnection=db) fin = finances.Finances(games_finances, db) dates = list(map(lambda x: ('{}/{}'.format(*x), '{} {}'.format(mydatetime._months[x[0]-1], x[1])), dates)) return pages.PageBuilder('finances', dates=dates, current_date='{}/{}'.format(month, year), fin=fin, outlays=outlays)