def validate_all(pre_post, course_name):
"""Set the time validity for all students of a course to 1."""
course_db = CourseDB()
course_id = course_db.get_course_id(course_name)[0]
questionnaire_db = QuestionnaireDB()
if pre_post not in ["pre", "post"]:
print("Unknown pre_post specifier {}, should be 'pre' or 'post'"
"".format(pre_post))
return
query = """
UPDATE
student_{}
SET
valid_time = 1
WHERE student_id IN (
SELECT
student_id
FROM
student_course
WHERE
course_id = ?
)
""".format(pre_post)
questionnaire_db.execute(query, (course_id, ))
questionnaire_db.db.commit()
def test_get_close_db(app):
with app.app_context():
db = QuestionnaireDB()
assert db() is g.questionnaire_db
# after the context the db connection gets teared down
with pytest.raises(sqlite3.ProgrammingError):
db.execute('SELECT 1')
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_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 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)
def create_reports():
"""Create the reports for all finished courses."""
course_db = CourseDB()
questionnaire_db = QuestionnaireDB()
finished_courses = course_db.get_postsurveys_starting_before(
datetime.timedelta(days=15))
for (course_id, course_identifier) in finished_courses:
report_dir = os.path.join(current_app.instance_path, course_identifier)
if os.path.exists(report_dir):
continue
matched_responses = questionnaire_db.get_matched_responses(course_id)
similar_courses = course_db.get_similar_course_ids(course_id)
similar_responses = copy.deepcopy(matched_responses)
for (similar_id, ) in similar_courses:
matched = questionnaire_db.get_matched_responses(similar_id)
similar_responses.append(matched)
os.mkdir(report_dir)
os.chdir(report_dir)
if matched_responses.size() == 0:
SendEmail(
'*****@*****.**',
'Kurs {} hat keine gematched Antworten'.format(
course_identifier),
'Der Kurs {} mit id {} hat keine gematched Antworten. '
'Der Report konnte nicht erzeugt werden.'.format(
course_identifier, course_id))
log.warning(
'Course {} with id {} has no matched responses'.format(
course_identifier, course_id))
continue
generate_plots(matched_responses, similar_responses)
count_pre, count_post = questionnaire_db.get_course_numbers(course_id)
name, count_students = course_db.get_course_report_info(course_id)
with current_app.open_resource('util/report_template.txt', 'r') as f:
content = f.read().format(
course_name=sanitize_name(name),
course_pre=count_pre,
course_post=count_post,
course_matched=matched_responses.size(),
course_reported=count_students,
course_ratio=matched_responses.size() / count_students,
similar_matched=similar_responses.size(),
)
with open(os.path.join(report_dir, 'report.tex'), 'w') as f:
f.write(content)
latexmk_command = ['latexmk', '-pdf', '-quiet', '-f', 'report.tex']
latexmk_clean = ['latexmk', '-c', 'report.tex']
failure = subprocess.call(latexmk_command,
stdout=subprocess.DEVNULL,
stderr=subprocess.DEVNULL)
if failure:
SendEmail(
'*****@*****.**',
'Fehler bei Report für Kurs {}'.format(course_identifier),
'Der Report für den Kurs {} mit id {} konnte nicht '
'erzeugt werden.'.format(course_identifier, course_id))
log.error('Error while processing of the tex-file for course '
'{} with id {}'.format(course_identifier, course_id))
continue
subprocess.call(latexmk_clean,
stdout=subprocess.DEVNULL,
stderr=subprocess.DEVNULL)
click.echo('Generated Report for {} with {} matched responses'.format(
course_identifier, matched_responses.size()))
click.echo('Finished Reports')
def test_insert_data(app, MonkeyCourseDBCourses):
data = {
'personal_code': ['a', 'a', 'b', 'a'],
'course_id': [1, 1, 1, 0],
'pre_post': [1, 2, 2, 1],
'valid_control': [True, False, True, True],
'valid_time': [False, True, True, True],
}
len_data = len(data['personal_code'])
data['start'] = [datetime.datetime.today() for _ in range(len_data)]
data['end'] = [datetime.datetime.today() for _ in range(len_data)]
start = int(time.mktime(data['start'][0].timetuple()))
end = int(time.mktime(data['end'][0].timetuple()))
for i in range(1, 31):
data['q{:d}_1'.format(i)] = [1 for _ in range(len_data)]
data['q{:d}_2'.format(i)] = [1 for _ in range(len_data)]
for i in range(1, 24):
data['post_{:d}'.format(i)] = [1 for _ in range(len_data)]
df = pd.DataFrame(data=data)
with app.app_context():
questionnaire_db = QuestionnaireDB()
questionnaire_db.insert_data(df)
students = list(questionnaire_db.select_all_entries('student'))
students_list = []
for row in students:
students_list.append(list(row))
assert students_list[0] == [1, 'a']
assert students_list[1] == [2, 'b']
assert students_list[2] == [3, 'a']
student_course = list(
questionnaire_db.select_all_entries('student_course'))
student_course_list = []
for row in student_course:
student_course_list.append(list(row))
assert student_course_list[0] == [1, 1, 1]
assert student_course_list[1] == [2, 2, 1]
student_unknown = list(
questionnaire_db.select_all_entries('student_unknown_course'))
student_unknown_list = []
for row in student_unknown:
student_unknown_list.append(list(row))
assert student_unknown_list[0] == [1, 3, '0']
q_pre = list(questionnaire_db.select_all_entries('questionnaire_pre'))
q_pre_list = []
for row in q_pre:
q_pre_list.append(list(row))
assert q_pre_list[0] == [1, 1, 1]
assert q_pre_list[1] == [2, 4, 4]
q_post = list(
questionnaire_db.select_all_entries('questionnaire_post'))
q_post_list = []
for row in q_post:
q_post_list.append(list(row))
assert q_post_list[0] == [1, 2, 2, 1]
assert q_post_list[1] == [2, 3, 3, 2]
s_pre = list(questionnaire_db.select_all_entries('student_pre'))
s_pre_list = []
for row in s_pre:
s_pre_list.append(list(row))
assert s_pre_list[0] == [1, 1, 1, start, end, 1, 0]
assert s_pre_list[1] == [2, 3, 2, start, end, 1, 1]
s_post = list(questionnaire_db.select_all_entries('student_post'))
s_post_list = []
for row in s_post:
s_post_list.append(list(row))
assert s_post_list[0] == [1, 1, 1, start, end, 0, 1]
assert s_post_list[1] == [2, 2, 2, start, end, 1, 1]