def get_unmatched_students():
"""Get all valid unmatched responses, sorted by course."""
course_db = CourseDB()
course_data = list(course_db.get_all_course_data())
questionnaire_db = QuestionnaireDB()
data = pd.DataFrame(columns=["course_id", "pre", "post"])
for course in course_data:
course_id = course["id"]
def get_unmatched(pre_post, course_id):
other = "post" if pre_post == "pre" else "pre"
res = questionnaire_db.execute(
"""
SELECT
student.code
FROM
student, student_{0}, student_course
WHERE
student_{0}.student_id = student_course.student_id
AND
student_course.course_id = ?
AND
student_{0}.valid_control = 1
AND
student_{0}.valid_time = 1
AND
student.id = student_{0}.student_id
AND student_{0}.student_id NOT IN (
SELECT
student_id
FROM student_{1}
)
""".format(pre_post, other), (course_id, ))
return res
pre = get_unmatched("pre", course_id)
for row in pre:
data = data.append(
{
"course_id": course_id,
"pre": row[0],
"post": ""
},
ignore_index=True)
post = get_unmatched("post", course_id)
for row in post:
data = data.append(
{
"course_id": course_id,
"pre": "",
"post": row[0]
},
ignore_index=True)
data.to_csv("/app/instance/unmatched.csv", index=False)
def export():
"""Export all matched responses to a csv at /app/instace/export.csv."""
cols = [
"course_id",
"experience_id",
"program_id",
"course_type_id",
"traditional_id",
*["q_you_pre_" + str(i) for i in range(1, 31)],
*["q_you_post_" + str(i) for i in range(1, 31)],
*["q_expert_pre_" + str(i) for i in range(1, 31)],
*["q_expert_post_" + str(i) for i in range(1, 31)],
*["q_mark_" + str(i) for i in range(1, 24)],
]
data = {}
for col in cols:
data[col] = []
course_db = CourseDB()
questionnaire_db = QuestionnaireDB()
course_data = list(course_db.get_all_course_data())
for course in course_data:
course_id = course["id"]
metadata_name = ("experience_id", "program_id", "course_type_id",
"traditional_id")
metadata = [course[name] for name in metadata_name]
matched = questionnaire_db.get_matched_responses(course_id,
disagreement=True)
for i in range(matched.size()):
data["course_id"].append(course_id)
for indx, col in enumerate(metadata_name):
data[col].append(metadata[indx])
for indx in range(1, 31):
data["q_you_pre_" + str(indx)].append(
matched.q_you_pre.responses[i][indx - 1])
data["q_you_post_" + str(indx)].append(
matched.q_you_post.responses[i][indx - 1])
data["q_expert_pre_" + str(indx)].append(
matched.q_expert_pre.responses[i][indx - 1])
data["q_expert_post_" + str(indx)].append(
matched.q_expert_post.responses[i][indx - 1])
for indx in range(1, 24):
data["q_mark_" + str(indx)].append(
matched.q_mark.responses[i][indx - 1])
df = pd.DataFrame(data)
# shuffle the data
df = df.sample(frac=1).reset_index(drop=True)
df.to_csv("/app/instance/export.csv")
def get_unknown_courses():
"""Get all course codes that could not be matched."""
course_db = CourseDB()
course_data = list(course_db.get_all_course_data())
data = pd.DataFrame(columns=["known_courses", "unknown_courses"])
for course in course_data:
data = data.append(
{
"known_courses": course["identifier"],
"unknwon_courses": ""
},
ignore_index=True)
questionnaire_db = QuestionnaireDB()
res = questionnaire_db.execute(
"SELECT course_code FROM student_unknown_course", ())
for row in res:
if row[0] is not None and row[0].strip() != "":
data = data.append({
"known_courses": "",
"unknwon_courses": row[0]
},
ignore_index=True)
data.to_csv("/app/instance/unknown.csv", index=False)