diff --git a/leaderboard.py b/leaderboard.py index 1b8f9f1..1c7b232 100644 --- a/leaderboard.py +++ b/leaderboard.py @@ -1,234 +1,236 @@ #!/usr/bin/env python3 import os import cgi import sys import json import yaml import pymysql import requests # Print header print('Content-type: application/json\n') def jsonify(response): return json.dumps(response) -def get_leaderboard_obj(username, avatar, category_count, rank): +def get_leaderboard_obj(username, avatar, category_count, rank, offset=None): + if offset == None: + offset = 0 obj = { "username": username, "avatar": avatar, - "rank": rank, + "rank": rank+int(offset), "category_count": category_count } return obj def get_res(status, username, avatar, rank, category_count, category, duration, limit, offset, leaderboard_list): response = { "status": status, "username": username, "avatar": avatar, "rank": rank, "category_count": category_count, "category": category, "duration": duration, "limit": limit, "offset": offset, "leaderboard_list": leaderboard_list } return jsonify(response) def get_res_error(status, user, message): response = {'status': status, 'user': user, 'message': message} return jsonify(response) offset = None limit = None avatar = 'Gnome-stock_person.svg' # Fetch params if 'QUERY_STRING' in os.environ: QS = os.environ['QUERY_STRING'] qs = cgi.parse_qs(QS) try: if 'offset' in qs: offset = qs['offset'][0] if 'limit' in qs: limit = qs['limit'][0] if 'avatar' in qs: avatar = qs['avatar'][0] username = qs['user'][0].replace('_', ' ') duration = qs['duration'][0] category = qs['category'][0] except: print(get_res_error('400', None, 'Invalid Parameters')) sys.exit(0) # Load config __dir__ = os.path.dirname(__file__) config = yaml.safe_load(open(os.path.join(__dir__, 'config.yaml'))) conn = pymysql.connect(db='s53794__leaderboard', host=config['DB_HOST'], user=config['DB_USER'], password=config['DB_PASS'], charset="utf8", ) else: print(get_res_error('500', username, 'Unable to connect to database')) sys.exit(0) ##### PROGRAM #### def username_exists(username): url = 'https://tools.wmflabs.org/commons-android-app/tool-commons-android-app/username_exists.py?user={username}'.format( username=username) body = json.loads(requests.get(url).content) return body['exists'] def get_nearby_data(username): url = 'https://tools.wmflabs.org/commons-android-app/tool-commons-android-app/nearby_by_user_and_duration.py?user={username}'.format( username=username) body = json.loads(requests.get(url).content) return body['data'] def get_used_data(username): url = 'https://tools.wmflabs.org/commons-android-app/tool-commons-android-app/used_images_by_user_and_duration.py?user={username}'.format( username=username) body = json.loads(requests.get(url).content) return body['data'] def get_upload_data(username): url = 'https://tools.wmflabs.org/commons-android-app/tool-commons-android-app/uploads_by_user_and_duration.py?user={username}'.format( username=username) body = json.loads(requests.get(url).content) return body['data'] def get_sql_user_creation(username, avatar): if len(username) > 0 and len(avatar) > 0: if username_exists(username): return """INSERT INTO leaderboard_user (username, user_avatar) VALUES ('{username}', '{avatar}');""".format(username=username, avatar=avatar) return '' def get_sql_user_data(username, weekly, yearly, all_time, _type): return """INSERT INTO leaderboard_{_type} (user_id, weekly, yearly, all_time) VALUES ((select user_id from leaderboard_user where username='{username}'), {weekly}, {yearly}, {all_time});""".format( username=username, weekly=weekly, yearly=yearly, all_time=all_time, _type=_type) def add_user(username, avatar): cur = conn.cursor() with cur: sql = get_sql_user_creation(username, avatar) cur.execute(sql) conn.commit() def add_user_data(username, weekly, yearly, all_time, _type): cur = conn.cursor() with cur: sql = get_sql_user_data(username, weekly, yearly, all_time, _type) cur.execute(sql) conn.commit() def username_exists_in_leaderboard(username): cur = conn.cursor() with cur: sql = """SELECT count(*) from leaderboard_user where username = '{username}'""".format( username=username) cur.execute(sql) data = cur.fetchall() if int(data[0][0]) > 0: return True else: return False -def get_leaderboard_list(data): +def get_leaderboard_list(data, offset=None): result = [] rank = 1 for row in data: - result.append(get_leaderboard_obj(row[1], row[2], row[3], rank)) + result.append(get_leaderboard_obj(row[1], row[2], row[3], rank, offset)) rank += 1 return result def get_user_details(username, leaderboard_list): return next(item for item in leaderboard_list if item['username'] == username) def get_sql(duration, category, offset=None, limit=None): if limit != None and offset != None: return """select leaderboard_user.user_id, leaderboard_user.username, leaderboard_user.user_avatar, leaderboard_{category}.{duration} from leaderboard_{category} LEFT JOIN (leaderboard_user) on (leaderboard_{category}.user_id = leaderboard_user.user_id) order by {duration} desc limit {limit} offset {offset};""".format(duration=duration, category=category, limit=limit, offset=offset) else: return """select leaderboard_user.user_id, leaderboard_user.username, leaderboard_user.user_avatar, leaderboard_{category}.{duration} from leaderboard_{category} LEFT JOIN (leaderboard_user) on (leaderboard_{category}.user_id = leaderboard_user.user_id) order by {duration} desc;""".format(duration=duration, category=category) def fetch_leaderboard_list(duration, category, offset=None, limit=None): cur = conn.cursor() with cur: sql = get_sql(duration, category, offset, limit) cur.execute(sql) data = cur.fetchall() return data def add_user_to_leaderboard(username, avatar): # This function will add user to the leaderboard add_user(username, avatar) data_used = get_used_data(username) add_user_data(username, data_used['weekly'], data_used['yearly'], data_used['all_time'], 'used') data_nearby = get_nearby_data(username) add_user_data(username, data_nearby['weekly'], data_nearby['yearly'], data_nearby['all_time'], 'nearby') data_upload = get_upload_data(username) add_user_data(username, data_upload['weekly'], data_upload['yearly'], data_upload['all_time'], 'upload') try: if not username_exists_in_leaderboard(username): add_user_to_leaderboard(username, avatar) data = fetch_leaderboard_list(duration, category, offset, limit) - leaderboard_list = get_leaderboard_list(data) + leaderboard_list = get_leaderboard_list(data, offset) if limit == None or offset == None: user_details = get_user_details(username, leaderboard_list) else: # We need to fetch the complete list if limit or offset is provided to get the provided user's rank complete_data = fetch_leaderboard_list(duration, category) - leaderboard_list = get_leaderboard_list(complete_data) + leaderboard_list = get_leaderboard_list(complete_data, offset) user_details = get_user_details(username, leaderboard_list) result = get_res(200, username, user_details['avatar'], user_details['rank'], user_details['category_count'], category, duration, limit, offset, leaderboard_list) print(result) except: print(get_res_error('500', username, 'Internal Server Error')) sys.exit(0)