diff --git a/leaderboard.py b/leaderboard.py index f7eb194..6aef063 100644 --- a/leaderboard.py +++ b/leaderboard.py @@ -1,227 +1,227 @@ #!/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): obj = { "username": username, "avatar": avatar, "rank": rank, "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 = 'https://upload.wikimedia.org/wikipedia/commons/thumb/0/0a/Gnome-stock_person.svg/200px-Gnome-stock_person.svg.png' # 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', + conn = pymysql.connect(db=config['DB_NAME'], 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): result = [] rank = 1 for row in data: result.append(get_leaderboard_obj(row[1], row[2], row[3], rank)) 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): 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): cur = conn.cursor() with cur: sql = get_sql(duration, category) 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) leaderboard_list = get_leaderboard_list(data) user_details = get_user_details(username, leaderboard_list) if limit == None or offset == None: result = get_res(200, username, user_details['avatar'], user_details['rank'], user_details['category_count'], category, duration, limit, offset, leaderboard_list) else: limit = int(limit) offset = int(offset) result = get_res(200, username, user_details['avatar'], user_details['rank'], user_details['category_count'], category, duration, limit, offset, leaderboard_list[offset:limit+offset]) print(result) except: print(get_res_error('500', username, 'Internal Server Error')) sys.exit(0) diff --git a/update_avatar.py b/update_avatar.py index 4c70ec8..e6cff28 100644 --- a/update_avatar.py +++ b/update_avatar.py @@ -1,75 +1,75 @@ #!/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_res(status, user, message): response = {'status': status, 'user': user, 'message': message} return jsonify(response) # Fetch params if 'QUERY_STRING' in os.environ: QS = os.environ['QUERY_STRING'] qs = cgi.parse_qs(QS) try: avatar = qs['avatar'][0] username = qs['user'][0].replace('_', ' ') except: print(get_res('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', + conn = pymysql.connect(db=config['DB_NAME'], host=config['DB_HOST'], user=config['DB_USER'], password=config['DB_PASS'], charset="utf8", ) else: print(get_res('500', username, 'Unable to connect to database')) sys.exit(0) ##### PROGRAM #### def get_sql(username, avatar): return """UPDATE leaderboard_user as t1, (select user_id from leaderboard_user where username='{username}') as t2 SET t1.user_avatar = '{avatar}' WHERE t1.user_id = t2.user_id;""".format( username=username, avatar=avatar) def update_avatar(username, avatar): if len(username) > 0 and len(avatar) > 0: cur = conn.cursor() with cur: sql = get_sql(username, avatar) cur.execute(sql) conn.commit() try: update_avatar(username, avatar) print(get_res('200', username, 'Avatar Updated')) except: print(get_res('500', username, 'Internal Server Error')) sys.exit(0) diff --git a/update_leaderboard.py b/update_leaderboard.py index 6244e23..31c4a4a 100644 --- a/update_leaderboard.py +++ b/update_leaderboard.py @@ -1,105 +1,105 @@ #!/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_res(status, user, message): response = {'status': status, 'user': user, 'message': message} return jsonify(response) # Fetch params if 'QUERY_STRING' in os.environ: QS = os.environ['QUERY_STRING'] qs = cgi.parse_qs(QS) try: username = qs['user'][0].replace('_', ' ') except: print(get_res('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', + conn = pymysql.connect(db=config['DB_NAME'], host=config['DB_HOST'], user=config['DB_USER'], password=config['DB_PASS'], charset="utf8", ) else: print(get_res('500', username, 'Unable to connect to database')) sys.exit(0) ##### PROGRAM #### 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_data(username, weekly, yearly, all_time, _type): return """UPDATE leaderboard_{_type} as t1, (select user_id from leaderboard_user where username='{username}') as t2 SET t1.weekly = '{weekly}', t1.yearly = '{yearly}', t1.all_time = '{all_time}' WHERE t1.user_id = t2.user_id;""".format( username=username, weekly=weekly, yearly=yearly, all_time=all_time, _type=_type) def update_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() try: data_used = get_used_data(username) update_user_data(username, data_used['weekly'], data_used['yearly'], data_used['all_time'], 'used') data_nearby = get_nearby_data(username) update_user_data(username, data_nearby['weekly'], data_nearby['yearly'], data_nearby['all_time'], 'nearby') data_upload = get_upload_data(username) update_user_data(username, data_upload['weekly'], data_upload['yearly'], data_upload['all_time'], 'upload') print(get_res('200', username, 'Data Updated')) except: print(get_res('500', username, 'Internal Server Error')) sys.exit(0)