def GRAPH_PROBLEMS_EVOLUTION(problem_id):
db = DB_Connection.database()
rows = db.query("""SELECT problem_elo FROM submission
WHERE problem_id = {}
AND problem_elo IS NOT NULL
ORDER BY id""".format(problem_id),
fetchall=True)
y = [round(x[0] * 100) for x in rows]
y.insert(0, 800)
latest_elo = round(
db.query("SELECT elo_global FROM problem_scores WHERE problem_id = {}".
format(problem_id),
fetchone=True)[0] * 100)
if y[-1] != latest_elo:
y.append(latest_elo)
db.close()
return PLOTLY_LINE_PLOT(
[x for x in range(len(y))],
y,
title="Evolución de la Puntuación ELO del Problema",
x_label="",
y_label="Puntuación ELO")
def GRAPH_PROBLEM_SOLVE_RATIO(problem_id):
db = DB_Connection.database()
if db.query("SELECT COUNT(*) FROM submission WHERE problem_id = {}".format(
problem_id),
fetchone=True)[0] != 0:
user_who_solved_it = db.query(
"""SELECT COUNT(DISTINCT(user_id)) FROM submission
WHERE problem_id = {}
AND (status = 'AC' OR status = 'PE')""".format(problem_id),
fetchone=True)[0]
user_who_havent_solved_yet = db.query(
"""SELECT COUNT(DISTINCT(user_id)) FROM submission
WHERE problem_id = {}
AND (status != 'AC' AND status != 'PE')
AND user_id NOT IN (
SELECT user_id FROM submission
WHERE problem_id = {}
AND (status = 'AC' OR status = 'PE')
)""".format(problem_id, problem_id),
fetchone=True)[0]
values = [user_who_solved_it, user_who_havent_solved_yet]
labels = [
'Usuarios que lo han resuelto',
'Usuarios que aun no lo han resuelto'
]
db.close()
return PLOTLY_PIE_CHART(labels, values, title="Grafica de Resolucion")
else:
db.close()
return '<h2 style="text-align: center; vertical-align: middle; line-height: 200px; height: 200px; color: dimgray;"> Sin Actividad </h2>'
def problem_latest_submissions(problem_id):
db = DB_Connection.database()
rows = db.query(
"SELECT user_id, status, submissionDate FROM submission WHERE problem_id={} ORDER BY submissionDate DESC LIMIT 30"
.format(problem_id),
fetchall=True)
db.close()
return rows
def RECOMMENDATIONS(user_id):
db = DB_Connection.database()
ELOS = db.query(
"SELECT * FROM user_scores WHERE user_id = {}".format(user_id),
fetchone=True)
CATEGORY_CODES = []
CATEGORY_FIELD_NAMES = []
for k, v in ACR_Globals.__CATEGORIES.items():
CATEGORY_CODES.append(k)
CATEGORY_FIELD_NAMES.append(v)
ELOS_PER_CATEGORY = {}
RECOMMENDATIONS_PER_CATEGORY = {}
for i, score in enumerate(ELOS[2:]):
if score != 8.0:
ELOS_PER_CATEGORY[CATEGORY_FIELD_NAMES[i]] = score
RECOMMENDATIONS_PER_CATEGORY[CATEGORY_FIELD_NAMES[i]] = []
ELOS_PER_CATEGORY['Global'] = ELOS[1]
RECOMMENDATIONS_PER_CATEGORY['Global'] = []
for CTGRY, ELO_SCORE in ELOS_PER_CATEGORY.items():
if CTGRY != 'Global':
CODE = CATEGORY_CODES[CATEGORY_FIELD_NAMES.index(CTGRY)]
recommendations = CATEGORIES_RECOMMENDATION(
db, 1, user_id, ELO_SCORE, CODE)
else:
recommendations = GLOBAL_RECOMMENDATION(db, 1, user_id, ELO_SCORE)
for P in recommendations:
RECOMMENDATIONS_PER_CATEGORY[CTGRY].append((P[0], P[1]))
RECOMMENDATIONS_LIST = []
for CTGRY, RECO_LIST in RECOMMENDATIONS_PER_CATEGORY.items():
if CTGRY == 'Global':
CATEGORY_TITLE = CTGRY
else:
CATEGORY_TITLE = ACR_Globals.__CATEGORIES_READABLE[CTGRY]
for RECO in RECO_LIST:
prb = db.query(""" SELECT pb.internalId, pb.title, ps.elo_global
FROM problem pb, problem_scores ps
WHERE ps.problem_id = pb.internalId
AND pb.internalId = {}""".format(RECO[0]),
fetchone=True)
PROBLEM_ID = prb[0]
PROBLEM_TITLE = prb[1]
PROBLEM_SCORE = round(prb[2], 3)
RECOMMENDATIONS_LIST.append(
(PROBLEM_ID, PROBLEM_TITLE, CATEGORY_TITLE, PROBLEM_SCORE))
db.close()
return RECOMMENDATIONS_LIST
def insert_user(user_id, elo):
db = DB_Connection.database()
try:
db.query(
"INSERT INTO user_scores (user_id, elo_global) VALUES ({}, {})".
format(user_id, elo),
commit=True)
db.close()
except Exception as e:
print(e)
db.close()
raise RuntimeError('El usuario ya existe en la BD')
def user_list():
db = DB_Connection.database()
rows = db.query(
"""SELECT user_scores.user_id, COUNT(DISTINCT(submission.problem_id)), SUM(CASE
WHEN submission.status = 'AC' THEN 1
WHEN submission.status = 'PE' THEN 1
ELSE 0 END), user_scores.elo_global
FROM submission RIGHT JOIN user_scores ON submission.user_id = user_scores.user_id
GROUP BY user_scores.user_id
ORDER BY user_scores.user_id ASC""",
fetchall=True)
db.close()
return rows
def GRAPH_ELO_DISTRIBUTION(items):
db = DB_Connection.database()
x = []
for row in db.query("""SELECT elo_global FROM {}""".format(
'user_scores' if items == 'Usuarios' else 'problem_scores'),
fetchall=True):
x.append(row[0] * 100)
db.close()
return PLOTLY_HISTOGRAM_PLOT(
x,
title="Distribución de Puntuación ELO de los {} de ACR".format(items),
x_label="Puntuación ELO",
y_label="% de {}".format(items))
def GRAPH_SUBMISSIONS_PER_MONTHS():
db = DB_Connection.database()
x = []
y = []
for r in db.query(
"SELECT DATE_FORMAT(submissionDate, '%Y-%m'), COUNT(id) FROM submission GROUP BY DATE_FORMAT(submissionDate, '%Y-%m') ORDER BY submissionDate ASC",
fetchall=True):
x.append(r[0])
y.append(r[1])
db.close()
return PLOTLY_BAR_PLOT(x,
y,
ax_type='date',
title="Envios por Mes",
y_label="Nº de Envios",
x_label="Fecha")
def insert_problem(problem_id, elo, title, categories):
db = DB_Connection.database()
try:
db.query(
"INSERT INTO problem (internalId, title) VALUES ({}, '{}')".format(
problem_id, title))
db.query(
"INSERT INTO problem_scores (problem_id, elo_global) VALUES ({}, {})"
.format(problem_id, elo)) # CHECK LATER
for code in categories:
db.query(
"INSERT INTO problemcategories (categoryId, problemId) VALUES ({}, {})"
.format(code, problem_id))
db.conn.commit()
db.close()
except Exception as e:
print(e)
db.close()
raise RuntimeError('El problema ya existe en la BD')
def GRAPH_USER_PROBLEM_PROGRESS(user_id):
db = DB_Connection.database()
values = []
# if user new (no submissions)
if db.query("SELECT COUNT(*) FROM submission WHERE user_id = {}".format(
user_id),
fetchone=True)[0] != 0:
# Problems solved by the user
val = db.query("""SELECT user_id, SUM(CASE
WHEN status = 'AC' THEN 1
WHEN status = 'PE' THEN 1
ELSE 0 END) FROM submission
WHERE user_id = {}
GROUP BY user_id""".format(user_id),
fetchone=True)
values.append(val[1])
# Problems tried by the user
val = db.query(
"""SELECT user_id, COUNT(DISTINCT(problem_id)) FROM submission
WHERE user_id = {}
GROUP BY user_id""".format(user_id),
fetchone=True)
values.append(val[1] - values[0])
else:
values = [0, 0]
# Number of problems
val = db.query("""SELECT COUNT(*) FROM problem_scores""", fetchone=True)
values.append(val[0] - sum(values))
labels = ['Resueltos', 'Intentados, sin resolver', 'Por Hacer']
db.close()
return PLOTLY_PIE_CHART(labels, values, title="Progreso de Problemas")
def RE_CALCULATE_ELOS(elo_type):
db = DB_Connection.database()
start_time = time.time()
try:
RESET_ELOS(db)
CALCULATE_ELOS(db, elo_type)
db.conn.commit()
global __elo_type
__elo_type = elo_type
db.change_elo_type(elo_type)
print("Time spent calculating ELOs: ",
time.time() - start_time,
flush=True)
db.close()
except Exception as e:
print(e)
db.close()
raise RuntimeError('Ha ocurrido un problema al cambiar el tipo de ELO')
def GRAPH_USER_CATEGORIES(user_id):
db = DB_Connection.database()
row = db.query(
"""SELECT * FROM user_scores WHERE user_id = {}""".format(user_id),
fetchone=True)
values = [round(i * 100) for i in row[2:]]
values.append(values[0])
axes = [
'Ad-hoc', 'Recorridos', 'Busqueda', 'Busqueda Binaria', 'Ordenacion',
'Algoritmos voraces', 'Programacion dinamica', 'Divide y venceras',
'Busqueda exhaustiva, vuelta atras',
'Busqueda en el espacio de soluciones', 'Grafos', 'Geometria', 'Ad-hoc'
]
db.close()
return PLOTLY_SPIDER_PLOT(values,
axes, [0, 1600],
title="ELO por Categoria")
def get_easiest_problems():
db = DB_Connection.database()
easy_problems = []
problems_ac = []
for code in ACR_Globals.__CATEGORIES:
rows = db.query("""SELECT pc.problemId, pb.title, ps.elo_global
FROM problemcategories pc, problem_scores ps, problem pb
WHERE pc.categoryId = {}
AND pc.problemId = ps.problem_id
AND pc.problemId = pb.internalId
ORDER BY ps.elo_global ASC
LIMIT 5""".format(code),
fetchall=True)
p_ids = []
for prob in rows:
easy_problems.append(
(prob[0], prob[1], ACR_Globals.__CATEGORIES_READABLE[
ACR_Globals.__CATEGORIES[code]], prob[2]))
p_ids.append(str(prob[0]))
rows = db.query("""SELECT ps.problem_id, SUM(CASE
WHEN su.status = 'AC' THEN 1
WHEN su.status = 'PE' THEN 1
ELSE 0 END) / COUNT(su.id), SUM(CASE
WHEN su.status = 'AC' THEN 1
WHEN su.status = 'PE' THEN 1
ELSE 0 END) / COUNT(DISTINCT(su.user_id))
FROM problem_scores ps, submission su
WHERE ps.problem_id = su.problem_id
AND ps.problem_id in ({})
GROUP BY ps.problem_id
ORDER BY ps.elo_global ASC
LIMIT 5""".format(','.join(p_ids)),
fetchall=True)
for prob in rows:
problems_ac.append((prob[0], prob[1] * 100, prob[2] * 100))
db.close()
return easy_problems, problems_ac
def TRIES_PER_PROBLEM(problem_id):
db = DB_Connection.database()
num_subm = {}
for i in range(1, 21):
num_subm[str(i)] = 0
num_subm['+ de 20'] = 0
rows = db.query("""SELECT user_id, problem_id, SUM(CASE
WHEN status = 'AC' THEN 1
WHEN status = 'PE' THEN 1
ELSE 0 END), COUNT(id)
FROM submission
WHERE problem_id = {}
GROUP BY user_id, problem_id""".format(problem_id),
fetchall=True)
for row in rows:
if row[2] != 0:
if row[3] < 21:
num_subm[str(row[3])] += 1
else:
num_subm['+ de 20'] += 1
total_sum = sum(num_subm.values())
total_sum = total_sum if total_sum != 0 else 1
x = list(num_subm.keys())
y = [(val / total_sum) * 100 for val in num_subm.values()]
db.close()
return PLOTLY_BAR_PLOT(
x,
y,
x_label="Nº de Intentos",
y_label="% de Alumnos",
ax_type='category',
title=
"% de Usuarios que han necesitado X intentos para resolver este problema",
)
def GRAPH_TRIES_AVERAGE():
db = DB_Connection.database()
num_subm = {}
for i in range(1, 21):
num_subm[str(i)] = 0
num_subm['+ de 20'] = 0
rows = db.query("""SELECT user_id, problem_id, SUM(CASE
WHEN status = 'AC' THEN 1
WHEN status = 'PE' THEN 1
ELSE 0 END), COUNT(id)
FROM submission
GROUP BY user_id, problem_id""",
fetchall=True)
for row in rows:
if row[2] != 0:
if row[3] < 21:
num_subm[str(row[3])] += 1
else:
num_subm['+ de 20'] += 1
x = list(num_subm.keys())
total_sum = sum(num_subm.values())
y1 = [(i / total_sum) * 100 for i in num_subm.values()]
y2 = [sum(y1[:i + 1]) for i in range(len(y1))]
db.close()
return PLOTLY_BAR_PLOT_2YAXIS(
x,
y1,
y2,
title=
"% de Usuarios que han necesitado X intentos para resolver un problema",
x_label="Nº de Intentos",
y1_label="% de Alumnos",
y1_name="% de Alumnos",
y2_name="% Acumulado de Alumnos")
def problem_list():
db = DB_Connection.database()
prob_list = db.query("""SELECT pb.internalId, pb.title, COUNT(sb.id)
FROM problem pb LEFT JOIN submission sb
ON pb.internalId = sb.problem_id
GROUP BY pb.internalId
ORDER BY internalId ASC""",
fetchall=True)
problems_ac = db.query("""SELECT ps.problem_id, ps.elo_global,
(SUM(CASE
WHEN su.status = 'AC' THEN 1
WHEN su.status = 'PE' THEN 1
ELSE 0 END) / COUNT(su.id))*100,
(SUM(CASE
WHEN su.status = 'AC' THEN 1
WHEN su.status = 'PE' THEN 1
ELSE 0 END) / COUNT(DISTINCT(su.user_id)))*100
FROM problem_scores ps LEFT JOIN submission su
ON ps.problem_id = su.problem_id
GROUP BY ps.problem_id
ORDER BY ps.problem_id ASC""",
fetchall=True)
categories = {}
for row in db.query(
"SELECT problemId, categoryId FROM problemcategories ORDER BY problemId ASC",
fetchall=True):
try:
if row[0] not in categories:
categories[row[0]] = []
categories[row[0]].append(ACR_Globals.__CATEGORIES_READABLE[
ACR_Globals.__CATEGORIES[row[1]]])
except:
pass
db.close()
return prob_list, problems_ac, categories
def GRAPH_PROBLEM_LANGUAGES(problem_id):
db = DB_Connection.database()
if db.query("SELECT COUNT(*) FROM submission WHERE problem_id = {}".format(
problem_id),
fetchone=True)[0] != 0:
rows = db.query("""SELECT language, COUNT(*) FROM submission
WHERE problem_id = {}
GROUP BY language""".format(problem_id),
fetchall=True)
labels = []
values = []
for row in rows:
labels.append(row[0])
values.append(row[1])
db.close()
return PLOTLY_PIE_CHART(labels,
values,
title="Distribución de Lenguajes")
else:
db.close()
return '<h2 style="text-align: center; vertical-align: middle; line-height: 200px; height: 200px; color: dimgray;"> Sin Actividad </h2>'
def ELO_TYPE():
db = DB_Connection.database()
global __elo_type
__elo_type = db.query("SELECT elo_type FROM elo_type LIMIT 1",
fetchone=True)[0]
db.close()
def insert_submission(user_id, problem_id, language, status):
db = DB_Connection.database()
update_elo = False
# Checks if both user and problem exists
if db.query("SELECT * FROM user_scores WHERE user_id = {}".format(user_id),
fetchone=True) is None:
raise RuntimeError('El usuario no existe en la BD')
if db.query("SELECT * FROM problem_scores WHERE problem_id = {}".format(
problem_id),
fetchone=True) is None:
raise RuntimeError('El problema no existe en la BD')
# Checks if the user already solved the problem
already_solved = db.query("""SELECT user_id, problem_id FROM submission
WHERE user_id = {}
AND problem_id = {}
AND (status = 'AC' or status = 'PE')
GROUP BY user_id, problem_id""".format(user_id, problem_id),
fetchone=True)
if already_solved is not None:
raise RuntimeError('El usuario ya ha resuelto este problema')
# Checks if the user has switched problems
gave_up = False
row = db.query(""" SELECT user_id, problem_id, status FROM submission
WHERE user_id = {}
ORDER BY id DESC
LIMIT 1""".format(user_id),
fetchone=True)
if row is not None:
if row[2] not in ('AC', 'PE'):
last_problem = row[1]
if row[1] != problem_id:
gave_up = True
if __elo_type == 1:
if gave_up:
simulate_fight(db, user_id, last_problem, language, 'WA', 1)
update_elo = True
if status == 'AC':
simulate_fight(db, user_id, last_problem, language, status, 1)
update_elo = True
else:
# Gets the number of tries
tries = db.query(
"""SELECT COUNT(id), user_id, problem_id FROM submission
WHERE user_id = {}
AND problem_id = {}
GROUP BY user_id, problem_id""".format(user_id, problem_id),
fetchone=True)
if tries is not None:
tries = (tries[0] + 1) #% ACR_Globals.__MAX_TRIES
else:
tries = 1
if __elo_type == 2:
if gave_up:
# Checks the number of tries of the previous problem
prev_tries = db.query(
"""SELECT COUNT(id), user_id, problem_id FROM submission
WHERE user_id = {}
AND problem_id = {}
GROUP BY user_id, problem_id""".format(user_id, last_problem),
fetchone=True)
simulate_fight(db, user_id, last_problem, language, 'WA',
prev_tries[0])
update_elo = True
if status == 'AC':
simulate_fight(db, user_id, problem_id, language, status,
tries)
update_elo = True
elif __elo_type == 3:
tries = tries % ACR_Globals.__MAX_TRIES
if gave_up:
# Checks the number of tries of the previous problem
prev_tries = db.query(
"""SELECT COUNT(id), user_id, problem_id FROM submission
WHERE user_id = {}
AND problem_id = {}
GROUP BY user_id, problem_id""".format(user_id, last_problem),
fetchone=True)
prev_tries = prev_tries[0] % ACR_Globals.__MAX_TRIES
simulate_fight(
db, user_id, last_problem, language, 'WA',
ACR_Globals.__MAX_TRIES if prev_tries == 0 else prev_tries)
update_elo = True
if tries == 0 and status != 'AC':
simulate_fight(db, user_id, last_problem, language, status,
ACR_Globals.__MAX_TRIES)
update_elo = True
if status == 'AC':
simulate_fight(
db, user_id, problem_id, language, status,
ACR_Globals.__MAX_TRIES if tries == 0 else tries)
update_elo = True
if not update_elo:
db.query(
"""INSERT INTO submission (user_id, problem_id, language, status, submissionDate)
VALUES ({}, {}, '{}', '{}', '{}')""".format(
user_id, problem_id, language, status,
time.strftime('%Y-%m-%d %H:%M:%S')),
commit=True)
db.close()