def get_users_cache(user_list):
user_list = ', '.join([f"({i})" for i in user_list])
res = db_instance.fetch_rows(
f"SELECT * FROM computed_cache WHERE uuid in ({user_list}) AND deleted_at IS NULL"
)
res = [parse(UserCache.fields, row) for row in res] if res else []
return res
def get_groups():
res = db_instance.fetch_rows(
"SELECT * FROM groups WHERE deleted_at IS NULL")
if res:
res = [parse(Group.fields, row) for row in res]
return res
else:
return []
def get_logs_by_module(module_list):
module_list = ', '.join([f"({i})" for i in module_list])
query = f"""
SELECT l.id, l.upid, u.cluster, l.ucid, l.uuid, l.attempt_timestamp, l.problem_number, l.exercise_problem_repeat_session, l.is_correct,
l.total_sec_taken, l.total_attempt_cnt, l.used_hint_cnt, l.is_hint_used, l.is_downgrade, l.is_upgrade, l.user_level
FROM logs l
LEFT JOIN contents c
ON l.ucid = c.id
LEFT JOIN algorithm_cache u
ON l.uuid = u.uuid
WHERE
c.level2_id IN ({module_list})
AND
l.deleted_at IS NULL
ORDER BY l.attempt_timestamp
"""
res = db_instance.fetch_rows(query)
return res
def get_users_bin(user_list, cluster):
user_list = ', '.join([f"({i})" for i in user_list])
query = f"""
SELECT
a.uuid, u.name, a.bin
FROM
users u
LEFT JOIN
algorithm_cache a
ON
u.id = a.uuid
WHERE
a.uuid IN ({user_list})
AND
a.cluster = {cluster}
AND
u.deleted_at IS NULL
"""
res = db_instance.fetch_rows(query)
return res
def filter_users_by_clusters(user_list, cluster_list):
user_list = ', '.join([f"({i})" for i in user_list])
cluster_list = ', '.join([f"({i})" for i in cluster_list])
query = f"""
SELECT
*
FROM
computed_cache
LEFT JOIN
algorithm_cache
ON
computed_cache.uuid = algorithm_cache.uuid
WHERE
computed_cache.uuid IN ({user_list})
AND
algorithm_cache.cluster IN ({cluster_list})
AND
computed_cache.deleted_at IS NULL
"""
res = db_instance.fetch_rows(query)
res = [parse(UserCache.fields, row) for row in res] if res else []
return res
def get_users(user_list):
user_list = ', '.join([f"({i})" for i in user_list])
query = f"SELECT * FROM users WHERE id in ({user_list}) AND deleted_at IS NULL"
res = db_instance.fetch_rows(query)
res = [parse(User.fields, row) for row in res]
return res
def get_all_users():
query = "SELECT * FROM users WHERE deleted_at IS NULL"
res = db_instance.fetch_rows(query)
res = [parse(User.fields, row) for row in res]
return res
def get_contents():
res = db_instance.fetch_rows(
f"SELECT * FROM contents WHERE deleted_at IS NULL")
res = [parse(Content.fields, row) for row in res]
return res
def get_clusters():
res = db_instance.fetch_rows(
f"SELECT * FROM clusters WHERE deleted_at IS NULL")
res = [parse(Cluster.fields, row) for row in res]
return res
def get_users_cache():
res = db_instance.fetch_rows(
"SELECT * FROM computed_cache WHERE deleted_at IS NULL")
res = [parse(UserCache.fields, row) for row in res] if res else []
return res
def get_logs_by_user(user_list):
user_list = ', '.join([f"({i})" for i in user_list])
res = db_instance.fetch_rows(f"SELECT * FROM logs WHERE uuid IN ({user_list}) AND deleted_at IS NULL")
res = [parse(Log.fields, row) for row in res]
return res
def get_group_modules(group_id):
res = db_instance.fetch_rows(
"SELECT uuid FROM group_modules WHERE group_id = %s AND deleted_at IS NULL",
(group_id, ))
res = [row['module_id'] for row in res]
return res
def get_enrollments(group_id):
res = db_instance.fetch_rows(
"SELECT uuid FROM group_enrollments WHERE group_id = %s AND deleted_at IS NULL",
(group_id, ))
res = [row['uuid'] for row in res]
return res
