def add_topic(subject, topic): try: if subject == 'measure_topic': topic = MeasureTopic(topic=topic, school=db_utils.school()) elif subject == 'subject_topic': topic = SubjectTopic(topic=topic, school=db_utils.school()) db.session.add(topic) db.session.commit() except Exception as e: log.error(u'Could not add {}, topic {}: {}'.format(subject, topic, e)) utils.flash_plus(u'Kan onderwerp niet toevoegen', e) return redirect(url_for('settings.show_topics'))
def add(action_done=False): try: if action_done: absent_teacher_ids = request.form.getlist('my-select[]') replaced_by_teacher_id = request.form['replaced_by'] school = db_utils.school() academic_year = db_utils.academic_year() if absent_teacher_ids: for t_id in absent_teacher_ids: r = ReplacementTeacher( replacing_id=t_id, replaced_by_id=replaced_by_teacher_id, school=school, academic_year=academic_year) db.session.add(r) r.first_replacement_teacher = True db.session.commit() else: form = forms.AddForm() teachers = [(i, t, '') for i, t in db_teacher.db_teacher_list(select=True, full_name=True)] return render_template('settings/replacements/replacement.html', form=form, all_teachers=teachers, title='Voeg een vervanger toe', action='add', role='add', subject='settings.replacements') except Exception as e: log.error(u'Could not add replacement {}'.format(e)) utils.flash_plus(u'Kan vervanger niet toevoegen', e) db.session.rollback() return redirect(url_for('settings.replacements.show'))
def delete_classmoments(academic_year, valid_from): classmoments = Schedule.query.filter( Schedule.academic_year == academic_year, Schedule.school == db_utils.school(), Schedule.valid_from == valid_from).all() for c in classmoments: db.session.delete(c) db.session.commit()
def db_classgroup_dict(): q = db.session.query( Classgroup, Grade).join(Grade).filter(Grade.school == db_utils.school()).all() d = { '{} {}'.format(i.Grade.code, i.ClassGroup.code): i.ClassGroup for i in q } return d
def edit(action_done=False, id=-1): try: if action_done: absent_teacher_ids = request.form.getlist('my-select[]') replaced_by_teacher_id = request.form['replaced_by'] school = db_utils.school() academic_year = db_utils.academic_year() for r in db_replacement.replacement_list( id=replaced_by_teacher_id): db.session.delete(r) if absent_teacher_ids: for t_id in absent_teacher_ids: r = ReplacementTeacher( replacing_id=t_id, replaced_by_id=replaced_by_teacher_id, school=school, academic_year=academic_year) db.session.add(r) r.first_replacement_teacher = True db.session.commit() else: chbx_id_list = request.form.getlist('chbx') if chbx_id_list: id = int(chbx_id_list[0]) # only the first one can be edited absent_teacher_ids = db_replacement.replacement_list( id=id, ids_only=True) # absent_teachers = db_teacher.db_teacher_list(select=True, full=True, id_list=replacements) all_teachers = db_teacher.db_teacher_list(select=True, full_name=True) filtered_teachers = [] for i, t in all_teachers: if i in absent_teacher_ids: filtered_teachers.append((i, t, 'selected')) else: filtered_teachers.append((i, t, '')) form = forms.EditForm() form.replaced_by.choices = [ (id, db_teacher.db_teacher(id=id, full_name=True)) ] return render_template( 'settings/replacements/replacement.html', form=form, all_teachers=filtered_teachers, title='Wijzig een vervanger toe', action='edit', role='edit', subject='settings.replacements') except Exception as e: log.error(u'Could not edit replacement {}'.format(e)) utils.flash_plus(u'Kan vervanger niet wijzigen', e) db.session.rollback() return redirect(url_for('settings.replacements.show'))
def db_teacher(id=None, code=None, full_name=False): if id: teacher = Teacher.query.get(id) elif code: teacher = Teacher.query.filter( Teacher.code == func.binary(code), Teacher.school == db_utils.school()).first() if not full_name: return teacher elif teacher: return '{} ({} {})'.format(teacher.code, teacher.first_name, teacher.last_name) return None
def action_done(action=None, id=-1): try: if utils.button_pressed('save'): if action == 'add': teacher_id, day_hour, grade_id, lesson_id, changed_item = db_user.session_get_grade_filter() if current_user.teacher and current_user.is_strict_user: teacher = current_user.teacher else: teacher = Teacher.query.filter(Teacher.id == teacher_id, Teacher.school == db_utils.school()).first() lesson = db_lesson.db_lesson(lesson_id) measure_note = request.form['measure_note'] subject_note = request.form['subject_note'] extra_attention = 'chkb_extra_attention' in request.form subjects = request.form.getlist('subject') measures = request.form.getlist('measure') timestamp = datetime.datetime.strptime('{} {}:{}:{}'.format(request.form['txt-date'], 23, 59, int(request.form['hour'])), '%d-%m-%Y %H:%M:%S') duplicate_remarks = False # iterate over all students involved. Create an remark per student. # link the measures and remark-subjects to the remark for s in request.form.getlist('student_id'): student = Student.query.get(int(s)) if student: if db_remark.check_if_duplicate(student, timestamp, measure_note, subject_note, extra_attention, measures, subjects): duplicate_remarks = True else: # save new remark remark = Remark(student=student, lesson=lesson, teacher=teacher, timestamp=timestamp, measure_note=measure_note, subject_note=subject_note, grade=student.classgroup.grade, extra_attention=extra_attention, school=db_utils.school(), academic_year=db_utils.academic_year()) for s in subjects: subject = RemarkSubject(topic=SubjectTopic.query.get(int(s)), remark=remark) db.session.add(subject) for m in measures: measure = RemarkMeasure(topic=MeasureTopic.query.get(int(m)), remark=remark) db.session.add(measure) db.session.add(remark) db.session.commit() if duplicate_remarks: utils.flash_plus('Er bestaat al een opmerking voor deze leerling(en) op dit tijdstip.<br>De opmerkingen worden samengevoegd') return redirect(url_for('grade.show')) if utils.button_pressed('hide'): for s in request.form.getlist('student_id'): student = Student.query.get(int(s)) if student: student.hidden = True db.session.commit() except Exception as e: utils.flash_plus(u'Kan opmerking niet opslaan', e) log.error(u'Cannot save remarks {}'.format(e)) return redirect(url_for('grade.show'))
def db_grade_list(teacher=None, html_select=False, in_schedule=True): if html_select: q = db.session.query(Grade.id, Grade.code) else: q = Grade.query if teacher: q = db_schedule.query_filter(q.join(Classgroup, Grade.id == Classgroup.grade_id).join(Schedule, Schedule.classgroup_id == Classgroup.id))\ .filter(Schedule.teacher == teacher) else: if in_schedule: q = db_schedule.query_filter(q.join(Classgroup, Grade.id == Classgroup.grade_id).join(Schedule)) else: q = q.filter(Grade.school == db_utils.school()) return q.distinct(Grade.code).order_by(Grade.code).all()
def db_lesson_list(teacher=None, html_select=False, schedule=True): if html_select: q = db.session.query(Lesson.id, Lesson.code) else: q = Lesson.query if teacher: q = db_schedule.query_filter( q.join(Schedule)).filter(Schedule.teacher == teacher) else: if schedule: q = db_schedule.query_filter(q.join(Schedule)) else: q = q.filter(Lesson.school == db_utils.school()) return q.distinct(Lesson.code).order_by(Lesson.code).all()
def query_filter(query_in): now = datetime.datetime.now() if db_setting.get_global_setting_sim_dayhour_state(): try: now = datetime.datetime.strptime( db_setting.get_global_setting_sim_dayhour(), '%d-%m-%Y %H:%M') except Exception as e: pass schedule = Schedule.query.filter( Schedule.valid_from <= now).distinct().order_by( desc(Schedule.valid_from)).first() if not schedule: return query_in return query_in.filter(Schedule.school == db_utils.school(), Schedule.academic_year == db_utils.academic_year(), Schedule.valid_from == schedule.valid_from)
def delete_test_remarks(): academic_year = request.form['selected_academic_year'] try: remarks = Remark.query.filter( Remark.school == db_utils.school(), Remark.academic_year == db_utils.academic_year(), Remark.test == True).all() for r in remarks: if r.extra_measure and r.first_remark: db.session.delete(r.extra_measure) db.session.delete(r) db.session.commit() log.info(u'Removed test remarks') utils.flash_plus( u'Test opmerkingen verwijderd voor jaar {} '.format(academic_year)) except Exception as e: log.error(u'Could not delete test remarks error {}'.format(e)) utils.flash_plus( u'Kan test opmerkingen voor jaar {} niet verwijderen'.format( academic_year), e) return redirect(url_for('settings.show_tests'))
def db_teacher_list(select=False, schedule=True, full_name=False, id_list=None): if select: if full_name: q = db.session.query(Teacher.id, Teacher.code, Teacher.first_name, Teacher.last_name) else: q = db.session.query(Teacher.id, Teacher.code) else: q = Teacher.query if schedule: q = db_schedule.query_filter(q.join(Schedule)) else: q = q.filter(Teacher.school == db_utils.school()) if id_list: q = q.filter(Teacher.id.in_((id_list))) q = q.distinct().order_by(Teacher.code).all() if not full_name: return q return [(i, '{} ({} {})'.format(c, f, l)) for i, c, f, l in q]
def db_filter_grade(teacher_id, dayhour_str, grade_id, lesson_id, changed_item=None): #filter on teacher, timeslot , grade and lesson #priority is as follows: #- if teacher is changed: determine timeslot from current time and find grade and lesson from timetable #- if timeslot is changed: from teacher and timeslot determine grade and lesson from timetable # If this does not work, pick first grade for that teacher #- if grade is changed : from teacher, timeslot and grade, try to determine lesson from timetable. # If this does not work, pick first available lesson for that grade #- if lesson is changed : go with the flow... :-) teacher = None grade = None lesson = None d = 0 h = 0 teacher = Teacher.query.get(teacher_id) if changed_item == 'teacher': d, h = time_to_timeslot() #try to find the classmoment, equal to or earlier than the given day and hour schedules_found = db_schedule.query_filter(Schedule.query.join(Teacher)).filter(Schedule.teacher == teacher)\ .order_by(Schedule.day.desc(), Schedule.hour.desc()).all() dh = d * 10 + h for schedule in schedules_found: if dh >= schedule.day * 10 + schedule.hour: break else: schedule = None if not schedule: schedule = db_schedule.query_filter(Schedule.query.join(Teacher)).filter(Schedule.teacher == teacher).order_by(Schedule.day, Schedule.hour).first() if schedule: d = schedule.day h = schedule.hour changed_item = 'dayhour' else: d, h = Schedule.decode_dayhour(dayhour_str) if changed_item == 'dayhour': #fetch classgroups from timetable schedules = db_schedule.query_filter(Schedule.query.join(Teacher)).filter(Schedule.day == d, Schedule.hour == h, Schedule.teacher == teacher)\ .order_by(Schedule.day, Schedule.hour).all() if schedules: #the classmoment points to a single teacher, grade and lesson return schedules #just pick the first grade from all grades grade = Grade.query.distinct(Grade.code).order_by(Grade.code).first() changed_item = 'grade' else: grade = Grade.query.get(grade_id) if changed_item == 'grade': #find the first lesson, taken by given grade lesson = db_schedule.query_filter(Lesson.query.join(Schedule, Lesson.id == Schedule.lesson_id) .join(Classgroup, Classgroup.id == Schedule.classgroup_id).join(Grade, Grade.id == Classgroup.grade_id) .join(Teacher, Teacher.id == Schedule.teacher_id))\ .filter(Grade.id == grade.id, Schedule.teacher == teacher).distinct(Lesson.code).order_by(Lesson.code).first() if not lesson: #just pick the first lesson lesson = Lesson.query.filter(Lesson.school == db_utils.school()).distinct(Lesson.code).order_by(Lesson.code).first() else: lesson = Lesson.query.get(lesson_id) #create a dummy classmoment schedules = [] classgroups = Classgroup.query.filter(Classgroup.grade == grade).all() for c in classgroups: schedules.append(Schedule(day=d, hour=h, school=db_utils.school(), academic_year=db_utils.academic_year(), teacher=teacher , lesson=lesson, classgroup=c)) return schedules
def db_classgroup_list(grade=None): q = Classgroup.query.join(Grade).filter(Grade.school == db_utils.school()) if grade: q = q.filter(Classgroup.grade == grade) q = q.all() return q
def add_test_remarks(): academic_year = request.form['selected_academic_year'] nbr_test_students = int(request.form['nbr-test-students']) add_extra_measure = 'chkb-extra-measure' in request.form random.seed() try: classmoments = db_schedule.query_filter( Schedule.query.join(Classgroup, Grade, Lesson, Teacher)).all() students = db_schedule.query_filter( Student.query.join(Classgroup, Grade, Schedule)).all() for i in range(nbr_test_students): student = random.choice(students) dates = remark_dates[i % len(remark_dates)] for d in dates: classmoment = random.choice(classmoments) h = random.randint(1, 9) timestamp = datetime.datetime.strptime( '{}/20{} {}:{}:{}'.format(d, academic_year[2:4], 23, 59, h), '%d/%m/%Y %H:%M:%S') remark = Remark(student=student, grade=student.classgroup.grade, timestamp=timestamp, lesson=classmoment.lesson, teacher=classmoment.teacher, measure_note='', subject_note='TESTOPMERKING', school=db_utils.school(), academic_year=db_utils.academic_year(), test=True, extra_attention=random.choice( [True, False, False, False])) s = random.choice(db_subject_topic.db_subject_topic_list()) m = random.choice(db_measure_topic.db_measure_topic_list()) subject = RemarkSubject(topic=s, remark=remark) measure = RemarkMeasure(topic=m, remark=remark) db.session.add(subject) db.session.add(measure) db.session.add(remark) if add_extra_measure: matched_remarks, non_matched_remarks = db_remark.db_filter_remarks_to_be_reviewed( academic_year, test=True, commit=False) for s, rll in matched_remarks: for id, extra_measure, rl in rll: rids = [i.id for i in rl] db_remark.db_add_extra_measure( rids, 'TEST: extra sanctie voor {} {}'.format( s.first_name, s.last_name), commit=False) db_remark.db_tag_remarks_as_reviewed() log.info(u'Added test remarks') utils.flash_plus( u'Test opmerkingen toegevoegd voor jaar {} '.format(academic_year)) except Exception as e: log.error(u'Could not add test remarks error {}'.format(e)) utils.flash_plus( u'Kan test opmerkingen voor jaar {} niet toevoegen'.format( academic_year), e) return redirect(url_for('settings.show_tests'))
def upload_schedule(rfile): try: # format csv file : log.info(u'Import timetable from : {}'.format(rfile)) academic_year = request.form['selected_academic_year'] valid_from = datetime.datetime.strptime( request.form['select-date-from'], '%d-%m-%Y') # first, delete current timetable delete_classmoments(academic_year, valid_from) fieldnames = [ 'VOLGNUMMER', 'KLAS', 'LEERKRACHT', 'VAK', 'LOKAAL', 'DAG', 'UUR' ] timetable_file = csv.DictReader(rfile, fieldnames=fieldnames, delimiter=',', encoding='utf-8-sig') nbr_classmoments = 0 nbr_lessons = 0 error_message = '' nbr_grades = 0 nbr_classgroups = 0 grades = {g.code: g for g in db_grade.db_grade_list(in_schedule=False)} classgroups = {c.code: c for c in db_classgroup.db_classgroup_list()} lessons = {l.code: l for l in db_lesson.db_lesson_list(schedule=False)} teachers = { t.code: t for t in db_teacher.db_teacher_list(schedule=False) } for t in timetable_file: # skip empy records if t['KLAS'] != '' and t['LEERKRACHT'] != '' and t[ 'VAK'] != '' and t['DAG'] != '' and t['UUR'] != '': if t['LEERKRACHT'] in teachers: find_teacher = teachers[t['LEERKRACHT']] classgroup_code = t['KLAS'] if len(classgroup_code.split(' ')) == 1: grade_code = classgroup_code else: grade_code = classgroup_code.split(' ')[0] lesson_code = t['VAK'] # check for grade. If it not exists, add it first if grade_code in grades: find_grade = grades[grade_code] else: find_grade = Grade(code=grade_code, school=db_utils.school()) db.session.add(find_grade) grades[grade_code] = find_grade nbr_grades += 1 # check for classgroup, if not present, add if classgroup_code in classgroups: find_classgroup = classgroups[classgroup_code] else: find_classgroup = Classgroup(code=classgroup_code, grade=find_grade) db.session.add(find_classgroup) classgroups[classgroup_code] = find_classgroup nbr_classgroups += 1 # add lesson, if not already present if lesson_code in lessons: find_lesson = lessons[lesson_code] else: find_lesson = Lesson(code=lesson_code, school=db_utils.school()) db.session.add(find_lesson) lessons[lesson_code] = find_lesson nbr_lessons += 1 classmoment = Schedule(day=int(t['DAG']), hour=int(t['UUR']), classgroup=find_classgroup, teacher=find_teacher, lesson=find_lesson, school=db_utils.school(), academic_year=int(academic_year), valid_from=valid_from) db.session.add(classmoment) nbr_classmoments += 1 else: log.info(u'import timetable: teacher not found: {}'.format( t['LEERKRACHT'])) error_message += u'{} : niet gevonden<br>'.format( t['LEERKRACHT']) # load schedule for teacher XXXX period = day = 1 teacher = db_teacher.db_teacher(code='XXXX') lesson = db_lesson.db_lesson_list()[0] for g in db_grade.db_grade_list(): classgroups = db_classgroup.db_classgroup_list(grade=g) for cg in classgroups: classmoment = Schedule(day=day, hour=period, classgroup=cg, teacher=teacher, lesson=lesson, school=db_utils.school(), academic_year=int(academic_year), valid_from=valid_from) db.session.add(classmoment) period += 1 if period > 9: period = 1 day += 1 db.session.commit() log.info( u'import: added {} classmoments, {} grades, {} classgroups and {} lessons' .format(nbr_classmoments, nbr_grades, nbr_classgroups, nbr_lessons)) if error_message == '': utils.flash_plus( u'Lesrooster is geïmporteerd, {} lestijden, {} klassen, {} klasgroepen en {} lessen toegevoegd' .format(nbr_classmoments, nbr_grades, nbr_classgroups, nbr_lessons)) else: utils.flash_plus(u'Lesrooster kan niet worden geïmporteerd', format(error_message)) except Exception as e: utils.flash_plus(u'Kan bestand niet importeren', e) return