def student_incomplete_course_sections(request): if "inverse" in request.GET: inverse = True else: inverse = False from ecwsp.sis.xl_report import XlReport from ecwsp.work_study.models import StudentWorker AGGREGATE_CRITERIA = {"category__name": "Standards", "cached_substitution": "INC"} # school_year = SchoolYear.objects.filter(start_date__lt=date.today()).order_by('-start_date')[0] school_year = SchoolYear.objects.get(active_year=True) """ if inverse: method = Student.objects.exclude else: method = Student.objects.filter students = method(aggregate__in=Aggregate.objects.filter(course__marking_period__school_year=school_year, **AGGREGATE_CRITERIA).distinct()).distinct() students = students.filter(inactive=False).order_by('year', 'lname', 'fname') """ students = Student.objects.filter(is_active=True).order_by("year", "last_name", "first_name") data = [] titles = ["Last Name", "First Name", "Year", "Work Day", "Incomplete Course Sections"] for student in students: aggs = ( Aggregate.objects.filter(student=student, marking_period__school_year=school_year, **AGGREGATE_CRITERIA) .distinct() .order_by("marking_period__start_date") ) # make sure the student is actually enrolled in these course sections aggs = aggs.filter(course_section__courseenrollment__user=student) if inverse and aggs.count(): continue if not inverse and not aggs.count(): continue try: work_day = StudentWorker.objects.get(username=student.username).day except StudentWorker.DoesNotExist: work_day = None course_section_details = {} for agg in aggs: course_section_detail = course_section_details.get(agg.course_section_id, {}) course_section_detail["name"] = agg.course_section.name marking_periods = course_section_detail.get("marking_periods", []) marking_periods.append(agg.marking_period.shortname) course_section_detail["marking_periods"] = marking_periods course_section_details[agg.course_section_id] = course_section_detail narrative = [] course_section_details = sorted(course_section_details.items(), key=lambda (k, v): (v, k)) for course_section_detail in course_section_details: course_section_detail = course_section_detail[1] # discard the course section id narrative.append( u"{} ({})".format(course_section_detail["name"], u", ".join(course_section_detail["marking_periods"])) ) data.append([student.last_name, student.first_name, student.year, work_day, u"; ".join(narrative)]) report = XlReport() report.add_sheet(data, header_row=titles, title="Sheet1", auto_width=True) return report.as_download()
def report_process_statistics(year): titles = ['Year %s' % (year,) ,] data = [] data.append(['Date', str(datetime.date.today())]) # active prospects data.append([]) data.append(['Admissions Status', '# of prospects']) applicants = Applicant.objects.filter(school_year__in=year) levels = AdmissionLevel.objects.all() for level in levels: lvl_applicants = applicants.filter(level=level).distinct() data.append([level, lvl_applicants.count()]) # application decision data.append([]) data.append(['# of Prospects by Application Decision']) for decision in ApplicationDecisionOption.objects.all(): st_applicants = applicants.filter(application_decision=decision).distinct() data.append([decision, st_applicants.count()]) # by year data.append([]) data.append(['# of Prospects by Grade']) for grade_level in GradeLevel.objects.all(): yr_applicants = applicants.filter(year=grade_level).distinct() data.append([grade_level, yr_applicants.count()]) report = XlReport(file_name="Process_Statistics") report.add_sheet(data, header_row=titles, title="Process Statistics", heading="Process Statistics") return report.as_download()
def supervisor_xls(request): comp = WorkTeam.objects.filter(login=request.user)[0] timesheets = TimeSheet.objects.filter(approved=True).filter(company=comp).order_by('student', 'date',) data = [] titles = ["WorkTeam", "Student", "", "Date", "For Pay?", "Make up?", "Hours Worked", "Company Bill", "Performance", "Student Comment", "Supervisor Comment"] fileName = "Billing_Report" company_total = timesheets.aggregate(Sum('school_net')) data.append([comp.team_name, "", "", "", "", "", "", company_total['school_net__sum']]) studenti = 0 for timesheet in timesheets: data.append(["", timesheet.student.fname, timesheet.student.lname, timesheet.date, timesheet.for_pay, timesheet.make_up, timesheet.hours, timesheet.school_net, timesheet.performance, timesheet.student_accomplishment, timesheet.supervisor_comment,]) studenti += 1 if studenti == timesheets.filter(student__id__iexact=timesheet.student.id).count(): stu_total = timesheets.filter(student__id__iexact=timesheet.student.id).aggregate(Sum('hours'), Sum('student_net'), Sum('school_net')) data.append(["", "", "", "Total", "", "", stu_total['hours__sum'], stu_total['school_net__sum']]) studenti = 0 report = XlReport(file_name="Company Billing") report.add_sheet(data, header_row=titles, title="Company Billing", heading="Company Billing") return report.as_download()
def route_attendance(request): data = [] titles = ['Student First', 'Last', 'Route','Van','Company','Notes'] for ts in timesheets: data.append([]) report = XlReport(file_name="route_attendance") report.add_sheet(data, header_row=titles, title="Route_Attendance") return report.as_download()
def route_attendance(request): data = [] titles = ['Student First', 'Last', 'Route', 'Van', 'Company', 'Notes'] for ts in timesheets: data.append([]) report = XlReport(file_name="route_attendance") report.add_sheet(data, header_row=titles, title="Route_Attendance") return report.as_download()
def student_incomplete_course_sections(request): if 'inverse' in request.GET: inverse = True else: inverse = False from ecwsp.sis.xl_report import XlReport from ecwsp.work_study.models import StudentWorker AGGREGATE_CRITERIA = {'category__name': 'Standards', 'cached_substitution': 'INC'} #school_year = SchoolYear.objects.filter(start_date__lt=date.today()).order_by('-start_date')[0] school_year = SchoolYear.objects.get(active_year=True) ''' if inverse: method = Student.objects.exclude else: method = Student.objects.filter students = method(aggregate__in=Aggregate.objects.filter(course__marking_period__school_year=school_year, **AGGREGATE_CRITERIA).distinct()).distinct() students = students.filter(inactive=False).order_by('year', 'lname', 'fname') ''' students = Student.objects.filter(is_active=True).order_by('year', 'last_name', 'first_name') data = [] titles = ['Last Name', 'First Name', 'Year', 'Work Day', 'Incomplete Course Sections'] for student in students: aggs = Aggregate.objects.filter(student=student, marking_period__school_year=school_year, **AGGREGATE_CRITERIA).distinct().order_by('marking_period__start_date') # make sure the student is actually enrolled in these course sections aggs = aggs.filter(course_section__courseenrollment__user=student) if inverse and aggs.count(): continue if not inverse and not aggs.count(): continue try: work_day = StudentWorker.objects.get(username=student.username).day except StudentWorker.DoesNotExist: work_day = None course_section_details = {} for agg in aggs: course_section_detail = course_section_details.get(agg.course_section_id, {}) course_section_detail['name'] = agg.course_section.name marking_periods = course_section_detail.get('marking_periods', []) marking_periods.append(agg.marking_period.shortname) course_section_detail['marking_periods'] = marking_periods course_section_details[agg.course_section_id] = course_section_detail narrative = [] course_section_details = sorted(course_section_details.items(), key=lambda(k, v): (v, k)) for course_section_detail in course_section_details: course_section_detail = course_section_detail[1] # discard the course section id narrative.append(u'{} ({})'.format(course_section_detail['name'], u', '.join(course_section_detail['marking_periods']))) data.append([student.last_name, student.first_name, student.year, work_day, u'; '.join(narrative)]) report = XlReport() report.add_sheet(data, header_row=titles, title="Sheet1", auto_width=True) return report.as_download()
def fte_by_day(request): cursor = connection.cursor() fte = int(Configuration.get_or_default(name="Students per FTE"[0], default=5).value) cursor.execute("select day, count(*)/" + str(fte) + \ " from work_study_studentworker left join work_study_workteam on work_study_workteam.id = "+\ "work_study_studentworker.placement_id where work_study_workteam.inactive = False group by day;") names = cursor.fetchall() titles = (["Day", "FTE"]) report = XlReport(file_name="report_fteByDay") report.add_sheet(names, header_row=titles, title="FTE by Day of Week", heading="FTE by Day of Week") return report.as_download()
def fail_report(request): from ecwsp.grades.models import Grade form = MarkingPeriodForm(request.POST) if form.is_valid(): marking_periods = form.cleaned_data['marking_period'] students = Student.objects.filter( courseenrollment__course__marking_period__in=marking_periods ).distinct() titles = [''] departments = Department.objects.filter( course__courseenrollment__user__inactive=False).distinct() for department in departments: titles += [department] titles += [ 'Total', '', 'Username', 'Year', 'GPA', '', 'Failed courses' ] passing_grade = float( Configuration.get_or_default('Passing Grade', '70').value) data = [] iy = 3 for student in students: row = [student] ix = 1 # letter A student.failed_grades = Grade.objects.none() for department in departments: failed_grades = Grade.objects.filter( override_final=False, course__department=department, course__courseenrollment__user=student, grade__lte=passing_grade, marking_period__in=marking_periods) row += [failed_grades.count()] student.failed_grades = student.failed_grades | failed_grades ix += 1 row += [ '=sum(b{0}:{1}{0})'.format(str(iy), get_column_letter(ix)), '', student.username, student.year, student.gpa, '', ] for grade in student.failed_grades: row += [grade.course, grade.marking_period, grade.grade] data += [row] iy += 1 report = XlReport(file_name="fail_report") report.add_sheet(data, header_row=titles, title="Failure Report") return report.as_download()
def student_incomplete_courses(request): if 'inverse' in request.GET: inverse = True else: inverse = False from ecwsp.sis.xl_report import XlReport from ecwsp.work_study.models import StudentWorker AGGREGATE_CRITERIA = {'category__name': 'Standards', 'cached_substitution': 'INC'} school_year = SchoolYear.objects.filter(start_date__lt=date.today()).order_by('-start_date')[0] ''' if inverse: method = Student.objects.exclude else: method = Student.objects.filter students = method(aggregate__in=Aggregate.objects.filter(course__marking_period__school_year=school_year, **AGGREGATE_CRITERIA).distinct()).distinct() students = students.filter(inactive=False).order_by('year', 'lname', 'fname') ''' students = Student.objects.filter(is_active=True).order_by('year', 'last_name', 'first_name') data = [] titles = ['Last Name', 'First Name', 'Year', 'Work Day', 'Incomplete Courses'] for student in students: aggs = Aggregate.objects.filter(student=student, marking_period__school_year=school_year, **AGGREGATE_CRITERIA).distinct().order_by('marking_period__start_date') # make sure the student is actually enrolled in these courses aggs = aggs.filter(course__in=student.courseenrollment_set.values_list('course')) if inverse and aggs.count(): continue if not inverse and not aggs.count(): continue try: work_day = StudentWorker.objects.get(username=student.username).day except StudentWorker.DoesNotExist: work_day = None course_details = {} for agg in aggs: course_detail = course_details.get(agg.course_id, {}) course_detail['fullname'] = agg.course.fullname marking_periods = course_detail.get('marking_periods', []) marking_periods.append(agg.marking_period.shortname) course_detail['marking_periods'] = marking_periods course_details[agg.course_id] = course_detail narrative = [] course_details = sorted(course_details.items(), key=lambda(k, v): (v, k)) for course_detail in course_details: course_detail = course_detail[1] # discard the course id narrative.append(u'{} ({})'.format(course_detail['fullname'], u', '.join(course_detail['marking_periods']))) data.append([student.lname, student.fname, student.year, work_day, u'; '.join(narrative)]) report = XlReport() report.add_sheet(data, header_row=titles, title="Sheet1", auto_width=True) return report.as_download()
def fte_by_ind(request): """ FTE by industry """ cursor = connection.cursor() fte = int(Configuration.get_or_default(name="Students per FTE"[0], default=5).value) cursor.execute("select industry_type, count(*)/" + str(fte) + \ " from work_study_studentworker left join work_study_workteam on work_study_workteam.id = "+\ "work_study_studentworker.placement_id where work_study_workteam.inactive = False group by industry_type;") names = cursor.fetchall() titles = (["Industry", "FTE"]) report = XlReport(file_name="report_fteByInd") report.add_sheet(data=names, header_row=titles, title="Analytics Report", heading="FTE by Industry Type") report.add_sheet(data=student_company_day_report(industry_type=True), heading="Detail") return report.as_download()
def fail_report(request): from ecwsp.grades.models import Grade form = MarkingPeriodForm(request.POST) if form.is_valid(): marking_periods = form.cleaned_data['marking_period'] students = Student.objects.filter(courseenrollment__course__marking_period__in=marking_periods).distinct() titles = [''] departments = Department.objects.filter(course__courseenrollment__user__inactive=False).distinct() for department in departments: titles += [department] titles += ['Total', '', 'Username', 'Year','GPA', '', 'Failed courses'] passing_grade = float(Configuration.get_or_default('Passing Grade','70').value) data = [] iy=3 for student in students: row = [student] ix = 1 # letter A student.failed_grades = Grade.objects.none() for department in departments: failed_grades = Grade.objects.filter(override_final=False,course__department=department,course__courseenrollment__user=student,grade__lte=passing_grade,marking_period__in=marking_periods) row += [failed_grades.count()] student.failed_grades = student.failed_grades | failed_grades ix += 1 row += [ '=sum(b{0}:{1}{0})'.format(str(iy),get_column_letter(ix)), '', student.username, student.year, student.gpa, '', ] for grade in student.failed_grades: row += [grade.course, grade.marking_period, grade.grade] data += [row] iy += 1 report = XlReport(file_name="fail_report") report.add_sheet(data, header_row=titles, title="Failure Report") return report.as_download()
def fte_by_pay(request): report = XlReport(file_name="report_fteByPay") student_fte = int(Configuration.get_or_default(name="Students per FTE"[0], default=5).value) cursor = connection.cursor() cursor.execute("select paying, count(*)/" + str(student_fte) + \ " from work_study_studentworker left join work_study_workteam on work_study_workteam.id = "+\ "work_study_studentworker.placement_id where work_study_workteam.inactive = False group by paying;") totals = cursor.fetchall() cursor = connection.cursor() cursor.execute("select team_name, paying, count(*)/" + str(student_fte) + ", funded_by as fte from work_study_studentworker left join work_study_workteam on "+\ "work_study_workteam.id = work_study_studentworker.placement_id group by team_name order by paying, team_name;") company = cursor.fetchall() titles = (["Paying?","FTE"]) report.add_sheet(totals, header_row=titles, title="Totals") titles = (["WorkTeam", "Paying?","FTE", "Funded by"]) report.add_sheet(company, header_row=titles, title="Companies") report.add_sheet(student_company_day_report(paying=True), heading="Detail") return report.as_download()
def report_process_statistics(year): titles = [ 'Year %s' % (year, ), ] data = [] data.append(['Date', str(datetime.date.today())]) # active prospects data.append([]) data.append(['Admissions Status', '# of prospects']) applicants = Applicant.objects.filter(school_year__in=year) levels = AdmissionLevel.objects.all() for level in levels: lvl_applicants = applicants.filter(level=level).distinct() data.append([level, lvl_applicants.count()]) # application decision data.append([]) data.append(['# of Prospects by Application Decision']) for decision in ApplicationDecisionOption.objects.all(): st_applicants = applicants.filter( application_decision=decision).distinct() data.append([decision, st_applicants.count()]) # by year data.append([]) data.append(['# of Prospects by Grade']) for grade_level in GradeLevel.objects.all(): yr_applicants = applicants.filter(year=grade_level).distinct() data.append([grade_level, yr_applicants.count()]) report = XlReport(file_name="Process_Statistics") report.add_sheet(data, header_row=titles, title="Process Statistics", heading="Process Statistics") return report.as_download()
def download_results(self, test): """ Create basic xls report for OMR. Includes summary and details """ # from download_teacher_results() total_test_takers = test.active_testinstance_set.filter( answerinstance__points_earned__gt=0).distinct().count() # Summary sheet data = [[test.name]] data.append(["Points Possible:", test.points_possible]) data.append(["Results collected: %s" % (test.students_test_results, )]) data.append(['Test average: %s' % (test.points_average, )]) data.append(['']) data.append(['Student', 'Points Earned', 'Percentage']) first_student_row = i = 7 for ti in test.active_testinstance_set.annotate( earned=Sum('answerinstance__points_earned')): data.append([ti.student, ti.earned, "=B%s / $B$2" % i]) i += 1 # Make it easier to compare this against download_teacher_results() data.append(['']) i += 1 data.append([ "Students scoring at or above 70%", # number of points possible is in cell B2 '=COUNTIF(B{0}:B{1},">="&0.7*B2)'.format(first_student_row, i - 2), # don't put the decimal inside the string to avoid localization problems '=COUNTIF(C{0}:C{1},">="&0.7)/COUNT(C6:C{1})'.format( first_student_row, i - 2) ]) i += 1 report = XlReport(file_name="OMR Report") report.add_sheet(data, title="Summary", auto_width=True) # Detail sheets data_points = [] data_answers = [] data_abc = [] row_points = ["Student"] row_answers = ["Student"] row_abc = ["Student"] for i, question in enumerate(test.question_set.all()): row_points.append("%s %s" % (question.get_order_start_one, strip_tags(question.question).strip())) row_answers.append("%s %s" % (question.get_order_start_one, strip_tags(question.question).strip())) row_abc.append("Question {0}".format(i + 1)) data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) for test_instance in test.active_testinstance_set.all(): row_points = [] row_answers = [] row_abc = [] row_points.append(test_instance.student) row_answers.append(test_instance.student) row_abc.append(test_instance.student) for question in test.question_set.all(): try: answer = test_instance.answerinstance_set.get( question=question) row_points.append(answer.points_earned) row_answers.append(strip_tags(str(answer.answer)).strip()) i = None if question.type == "True/False": row_abc += [answer.answer] else: for i, x in enumerate(question.answer_set.all()): if x == answer.answer: break row_abc += [chr(65 + i)] except ObjectDoesNotExist: row_points += [''] row_answers += [''] row_abc += [''] data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) report.add_sheet(data_points, title="Detail Points", auto_width=True) report.add_sheet(data_answers, title="Detail Answers", auto_width=True) report.add_sheet(data_abc, title="Answer Sheet", auto_width=True) # Benchmark sheet data = [] row = ['Benchmark'] row2 = ['Points Possible'] benchmarks = Benchmark.objects.filter(question__test=test).distinct() for benchmark in benchmarks: row.append(benchmark) row.append('%') benchmark_points_possible = test.question_set.filter( benchmarks=benchmark).aggregate( Sum('point_value'))['point_value__sum'] # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if benchmark_points_possible is None: benchmark_points_possible = 0 row2.append(benchmark_points_possible) row2.append('') data.append(row) data.append(row2) first_student_row = i = 3 # 3 for third row on spreadsheet for test_instance in test.active_testinstance_set.all(): row = [test_instance.student] a = 2 # the letter b or column b in spreadsheet for benchmark in Benchmark.objects.filter( question__test=test).distinct(): benchmark_points_possible = test_instance.answerinstance_set.filter( question__benchmarks=benchmark).aggregate( Sum('points_earned'))['points_earned__sum'] # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if benchmark_points_possible is None: benchmark_points_possible = 0 row.append(benchmark_points_possible) row.append('={0}{1}/{0}$2'.format(get_column_letter(a), str(i))) a += 2 # skip ahead 2 columns i += 1 data.append(row) # Make it easier to compare this against download_teacher_results() data.append(['']) i += 1 row = ['Students scoring at or above 70%'] col = 2 while col < a: row.append('=COUNTIF({0}{1}:{0}{2},">="&0.7*{0}{3})'.format( get_column_letter(col), first_student_row, i - 2, first_student_row - 1)) col += 1 row.append( '=COUNTIF({0}{1}:{0}{2},">="&0.7)/COUNT({0}{1}:{0}{2})'.format( get_column_letter(col), first_student_row, i - 2)) col += 1 data.append(row) i += 1 report.add_sheet(data, title="Benchmark", auto_width=True) data = [['Benchmark', 'Name', 'Earned', 'Possible', 'Percent']] i = 2 for benchmark in benchmarks: row = [] row += [benchmark.number, benchmark.name] answer_data = AnswerInstance.objects.filter( test_instance__in=test.active_testinstance_set.all(), question__benchmarks=benchmark).aggregate( Sum('points_earned'), Sum('points_possible')) # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if answer_data['points_earned__sum']: row += [answer_data['points_earned__sum']] else: row += [0] # this causes a discrepancy with download_teacher_results() if # a student leaves a question blank. #row += [answer_data['points_possible__sum']] # instead, get the points possible for all questions having this benchmark and # multiply by the number of test takers benchmark_points_possible = test.question_set.filter( benchmarks=benchmark).aggregate( Sum('point_value'))['point_value__sum'] # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if benchmark_points_possible: benchmark_points_possible *= total_test_takers else: benchmark_points_possible = 0 row.append(benchmark_points_possible) row += ['=C{0}/D{0}'.format(str(i))] data += [row] i += 1 report.add_sheet(data, title="Benchmarks for class", auto_width=True) return report.as_download()
def count_items_by_category_across_course_sections(year_category_names, current_marking_period_category_names, item_criteria, category_heading_format, percentage_threshold, course_section_threshold, inverse=False): from ecwsp.sis.xl_report import XlReport from ecwsp.work_study.models import StudentWorker all_category_names = list(year_category_names) all_category_names.extend(current_marking_period_category_names) all_categories = Category.objects.filter(name__in=all_category_names) year_categories = Category.objects.filter(name__in=year_category_names) current_marking_period_categories = Category.objects.filter(name__in=current_marking_period_category_names) titles = ['Last Name', 'First Name', 'Year', 'Work Day'] if not inverse: titles.append('Course Section') for c in all_categories: titles.append(category_heading_format.format(c.name)) #school_year = SchoolYear.objects.filter(start_date__lt=date.today()).order_by('-start_date')[0] school_year = SchoolYear.objects.get(active_year=True) marking_period = school_year.markingperiod_set.filter(show_reports=True, start_date__lt=date.today()).order_by('-start_date')[0] data = [] for student in Student.objects.filter(is_active=True).order_by('year', 'last_name', 'first_name'): try: work_day = StudentWorker.objects.get(username=student.username).day except StudentWorker.DoesNotExist: work_day = None matching_course_sections = [] for course_section in student.coursesection_set.filter(marking_period__school_year=school_year).distinct(): items = Item.objects.filter(Q(category__in=current_marking_period_categories, marking_period=marking_period) | Q(category__in=year_categories), course_section=course_section, mark__student=student).annotate(best_mark=Max('mark__mark')).exclude(best_mark=None) total_item_count = items.count() if not total_item_count: continue course_section_match = False matching_course_section_detail = [course_section.name] # check for combined category matches matching_item_count = items.filter(**item_criteria).count() matching_percentage = round(float(matching_item_count) / total_item_count * 100, 0) if matching_percentage >= percentage_threshold: course_section_match = True for c in all_categories: # check for individual category matches, and get detail for each category if combined matched already total_items_in_category = items.filter(category=c).count() matching_items_in_category = items.filter(**item_criteria).filter(category=c).count() if total_items_in_category: matching_percentage_in_category = round(float(matching_items_in_category) / total_items_in_category * 100) else: matching_percentage_in_category = 0 matching_course_section_detail.append('{}/{} ({}%)'.format(matching_items_in_category, total_items_in_category, matching_percentage_in_category)) if matching_percentage_in_category >= percentage_threshold: course_section_match = True if course_section_match: matching_course_sections.append(matching_course_section_detail) if len(matching_course_sections) >= course_section_threshold: if not inverse: for course_section in matching_course_sections: row = [student.last_name, student.first_name, student.year, work_day] row.extend(course_section) data.append(row) elif inverse: row = [student.last_name, student.first_name, student.year, work_day] data.append(row) report = XlReport() report.add_sheet(data, header_row=titles, heading="Sheet1", auto_width=True) return report.as_download()
def date_based_gpa_report(request): input = request.POST.copy() input['template'] = 1 # Validation hack form = StudentGradeReportWriterForm(input, request.FILES) if form.is_valid(): data = form.cleaned_data try: students = form.get_students(data) except: students = Student.objects.filter(inactive = False).order_by('-year__id') titles = ["Student", "9th", "10th", "11th","12th", "Current"] data = [] current_year = SchoolYear.objects.get(active_year = True) two_years_ago = (current_year.end_date + timedelta(weeks=-(2*52))).year three_years_ago = (current_year.end_date + timedelta(weeks=-(3*52))).year four_years_ago = (current_year.end_date + timedelta(weeks=-(4*52))).year for student in students: row = [] gpa = [None,None,None,None,None] count = 0 #years is years that student has courses/grades years = SchoolYear.objects.filter(markingperiod__show_reports=True,start_date__lt=date.today(),markingperiod__course__courseenrollment__user=student ).exclude(omityeargpa__student=student).distinct().order_by('start_date') #if student has courses from any year and is given a grade level (freshman,sophomore, etc.), #it checks to see if the student's been at cristorey every year or if they transferred in and when current = 0 try: if student.year.id == 12: current = 3 if years[0].start_date.year > two_years_ago: gpa[0] = "N/A" gpa[1] = "N/A" gpa[2] = "N/A" count = 3 elif years[0].start_date.year > three_years_ago: gpa[0] = "N/A" gpa[1] = "N/A" count = 2 elif years[0].start_date.year > four_years_ago: gpa[0] = "N/A" count = 1 elif student.year.id == 11: current = 2 if years[0].start_date.year > two_years_ago: gpa[1] = "N/A" gpa[0] = "N/A" count = 2 elif years[0].start_date.year > three_years_ago: gpa[0] = "N/A" count = 1 elif student.year.id == 10: current = 1 if two_years_ago: gpa[0] = "N/A" count = 1 elif student.year.id == 9: current = 0 except:pass for year in years: #cumulative gpa per year. Adds one day because it was acting weird and not giving me GPA for first year gpa[count] = student.calculate_gpa(year.end_date + timedelta(days=1)) count +=1 #if calculate_gpa does not return a value, it is set to "N/A" if not gpa[0]: gpa[0] = "N/A" if not gpa[1]: gpa[1] = "N/A" if not gpa[2]: gpa[2] = "N/A" if not gpa[3]: gpa[3] = "N/A" gpa[4] = gpa[current] row = [student, gpa[0],gpa[1],gpa[2],gpa[3],gpa[4]] data.append(row) report = XlReport(file_name="gpas_by_year") report.add_sheet(data, header_row=titles, title="GPAs") return report.as_download()
def aggregate_grade_report(request): from ecwsp.grades.models import Grade mp_form = MarkingPeriodForm(request.POST) if mp_form.is_valid(): mps = mp_form.cleaned_data['marking_period'] data = [] titles = ["Teacher", "Range", "No. Students", ""] for level in GradeLevel.objects.all(): titles += [level, ""] ranges = [['100', '90'], ['89.99', '80'], ['79.99', '70'], ['69.99', '60'], ['59.99', '50'], ['49.99', '0']] letter_ranges = ['P', 'F'] for teacher in Faculty.objects.filter(course__marking_period__in=mps).distinct(): data.append([teacher]) grades = Grade.objects.filter( marking_period__in=mps, course__teacher=teacher, student__inactive=False, override_final=False, ).filter( Q(grade__isnull=False) | Q(letter_grade__isnull=False) ) teacher_students_no = grades.distinct().count() if teacher_students_no: for range in ranges: no_students = grades.filter( grade__range=(range[1],range[0]), ).distinct().count() percent = float(no_students) / float(teacher_students_no) percent = ('%.2f' % (percent * 100,)).rstrip('0').rstrip('.') + "%" row = ["", str(range[1]) + " to " + str(range[0]), no_students, percent] for level in GradeLevel.objects.all(): no_students = grades.filter( grade__range=(range[1],range[0]), student__year__in=[level], ).distinct().count() level_students_no = grades.filter( student__year__in=[level], ).distinct().count() percent = "" if level_students_no: percent = float(no_students) / float(level_students_no) percent = ('%.2f' % (percent * 100,)).rstrip('0').rstrip('.') + "%" row += [no_students, percent] data.append(row) for range in letter_ranges: no_students = grades.filter( letter_grade=range, ).distinct().count() if teacher_students_no: percent = float(no_students) / float(teacher_students_no) percent = ('%.2f' % (percent * 100,)).rstrip('0').rstrip('.') + "%" else: percent = "" row = ["", str(range), no_students, percent] for level in GradeLevel.objects.all(): no_students = grades.filter( letter_grade=range, student__year__in=[level], ).distinct().count() level_students_no = grades.filter( student__year__in=[level], ).distinct().count() if level_students_no: percent = float(no_students) / float(level_students_no) percent = ('%.2f' % (percent * 100,)).rstrip('0').rstrip('.') + "%" else: percent = "" row += [no_students, percent] data.append(row) report = XlReport(file_name="aggregate_grade_report") report.add_sheet(data, header_row=titles, title="Teacher aggregate") passing = 70 data = [] titles = ['Grade'] for dept in Department.objects.all(): titles.append(dept) titles.append('') for level in GradeLevel.objects.all(): row = [level] for dept in Department.objects.all(): fails = Grade.objects.filter( marking_period__in=mps, course__department=dept, student__inactive=False, student__year__in=[level], # Shouldn't need __in. Makes no sense at all. grade__lt=passing, override_final=False, ).count() total = Grade.objects.filter( marking_period__in=mps, course__department=dept, student__inactive=False, student__year__in=[level], override_final=False, ).count() if total: percent = float(fails) / float(total) else: percent = 0 percent = ('%.2f' % (percent * 100,)).rstrip('0').rstrip('.') row.append(fails) row.append(percent) data.append(row) report.add_sheet(data, header_row=titles, title="Class Dept aggregate") return report.as_download()
def am_route_attendance(request): # TO DO Implement workday selection!!!! data = [] titles = ["Company", "Address", "City", "State", "Zip", "Contact First", "Contact Last", "Phone", "Grade",'Workday','Gender','Student First','Student Last', 'Cell','Earliest','Latest','Ideal','Schedule', 'Notes', datetime.date.today()] report = None if 'am_route_attendance' in request.POST: fileName = "AM_Routes.xls" else: fileName = "PM_Routes.xls" if not StudentWorkerRoute.objects.all(): messages.error(request, 'You must create at least one route before running this report!') return HttpResponseRedirect(request.META.get('HTTP_REFERER','/')) for route in StudentWorkerRoute.objects.all(): data = [] if 'am_route_attendance' in request.POST: students = StudentWorker.objects.filter(am_route=route, is_active = True) else: students = StudentWorker.objects.filter(pm_route=route, is_active = True) for student in students: row = [] if hasattr(student,'placement') and student.placement: row += [ student.placement, student.placement.address, student.placement.city, student.placement.state, student.placement.zip, ] else: row += ['', '', '', '', ''] if hasattr(student,'primary_contact') and student.primary_contact: row += [ student.primary_contact.fname, student.primary_contact.lname, student.primary_contact.phone, ] else: row += ['', '', ''] row += [ student.year, student.day, student.sex, student.fname, student.lname, ] # all the student's personal numbers, comma-separated row += [','.join(map(str, student.studentnumber_set.all())),] if hasattr(student,'placement') and student.placement: row += [ student.placement.time_earliest, student.placement.time_latest, student.placement.time_ideal, ] # help text for pm_transport_group says it can be left blank if not different than am_transport_group if 'am_route_attendance' in request.POST or student.placement.pm_transport_group is None: row += [unicode(student.placement.am_transport_group),] else: row += [unicode(student.placement.pm_transport_group),] row += [student.placement.directions_to,] else: row += ['', '', '', '',''] row += [student.get_transport_exception_display(), ] data.append(row) if not report: report = XlReport(file_name=fileName) report.add_sheet(data, header_row=titles, title=route.name, heading=route) return report.as_download()
def download_results(self, test): """ Create basic xls report for OMR. Includes summary and details """ # Summary sheet data = [[test.name]] data.append(["Points Possible:", test.points_possible]) data.append(["Results collected: %s" % (test.students_test_results, )]) data.append(['Test average: %s' % (test.points_average, )]) data.append([]) data.append(['Student', 'Points Earned', 'Percentage']) i = 6 for ti in test.testinstance_set.annotate( earned=Sum('answerinstance__points_earned')): data.append([ti.student, ti.earned, "=B%s / $B$2" % i]) i += 1 report = XlReport(file_name="OMR Report") report.add_sheet(data, title="Summary", auto_width=True) # Detail sheets data_points = [] data_answers = [] data_abc = [] row_points = ["Student"] row_answers = ["Student"] row_abc = ["Student"] for i, question in enumerate(test.question_set.all()): row_points.append( "%s %s" % (question.order, strip_tags(question.question).strip())) row_answers.append( "%s %s" % (question.order, strip_tags(question.question).strip())) row_abc.append("Question {0}".format(i + 1)) data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) for test_instance in test.testinstance_set.all(): row_points = [] row_answers = [] row_abc = [] row_points.append(test_instance.student) row_answers.append(test_instance.student) row_abc.append(test_instance.student) for question in test.question_set.all(): try: answer = test_instance.answerinstance_set.get( question=question) row_points.append(answer.points_earned) row_answers.append(strip_tags(answer.answer).strip()) i = None if question.type == "True/False": row_abc += [answer.answer] else: for i, x in enumerate(question.answer_set.all()): if x == answer.answer: break row_abc += [chr(65 + i)] except ObjectDoesNotExist: row_points += [''] row_answers += [''] row_abc += [''] data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) report.add_sheet(data_points, title="Detail Points", auto_width=True) report.add_sheet(data_answers, title="Detail Answers", auto_width=True) report.add_sheet(data_abc, title="Answer Sheet", auto_width=True) # Benchmark sheet data = [] row = ['Benchmark'] row2 = ['Points Possible'] benchmarks = Benchmark.objects.filter(question__test=test).distinct() for benchmark in benchmarks: row.append(benchmark) row.append('%') row2.append( test.question_set.filter(benchmarks=benchmark).aggregate( Sum('point_value'))['point_value__sum']) row2.append('') data.append(row) data.append(row2) i = 3 # 3 for third row on spreadsheet for test_instance in test.testinstance_set.all(): row = [test_instance.student] a = 2 # the letter c or column c in spreadsheet for benchmark in Benchmark.objects.filter( question__test=test).distinct(): row.append( test_instance.answerinstance_set.filter( question__benchmarks=benchmark).aggregate( Sum('points_earned'))['points_earned__sum']) row.append('={0}{1}/{0}$2'.format(get_column_letter(a), str(i))) a += 2 # skip ahead 2 columns i += 1 data.append(row) report.add_sheet(data, title="Benchmark", auto_width=True) data = [['Benchmark', 'Name', 'Earned', 'Possible', 'Percent']] i = 2 for benchmark in benchmarks: row = [] row += [benchmark.number, benchmark.name] answer_data = AnswerInstance.objects.filter( question__test=test, question__benchmarks=benchmark).aggregate( Sum('points_earned'), Sum('points_possible')) row += [answer_data['points_earned__sum']] row += [answer_data['points_possible__sum']] row += ['=C{0}/D{0}'.format(str(i))] data += [row] report.add_sheet(data, title="Benchmarks for class", auto_width=True) return report.as_download()
def download_results(self, test): """ Create basic xls report for OMR. Includes summary and details """ # Summary sheet data = [[test.name]] data.append(["Points Possible:", test.points_possible]) data.append(["Results collected: %s" % (test.students_test_results,)]) data.append(['Test average: %s' % (test.points_average,)]) data.append([]) data.append(['Student', 'Points Earned', 'Percentage']) i = 6 for ti in test.testinstance_set.annotate(earned=Sum('answerinstance__points_earned')): data.append([ti.student, ti.earned, "=B%s / $B$2" % i]) i += 1 report = XlReport(file_name="OMR Report") report.add_sheet(data, title="Summary", auto_width=True) # Detail sheets data_points = [] data_answers = [] data_abc = [] row_points = ["Student"] row_answers = ["Student"] row_abc = ["Student"] for i,question in enumerate(test.question_set.all()): row_points.append("%s %s" % (question.order, strip_tags(question.question).strip())) row_answers.append("%s %s" % (question.order, strip_tags(question.question).strip())) row_abc.append("Question {0}".format(i+1)) data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) for test_instance in test.testinstance_set.all(): row_points = [] row_answers = [] row_abc = [] row_points.append(test_instance.student) row_answers.append(test_instance.student) row_abc.append(test_instance.student) for question in test.question_set.all(): try: answer = test_instance.answerinstance_set.get(question=question) row_points.append(answer.points_earned) row_answers.append(strip_tags(answer.answer).strip()) i = None if question.type == "True/False": row_abc += [answer.answer] else: for i,x in enumerate(question.answer_set.all()): if x == answer.answer: break row_abc += [chr(65+i)] except ObjectDoesNotExist: row_points += [''] row_answers += [''] row_abc += [''] data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) report.add_sheet(data_points, title="Detail Points", auto_width=True) report.add_sheet(data_answers, title="Detail Answers", auto_width=True) report.add_sheet(data_abc, title="Answer Sheet", auto_width=True) # Benchmark sheet data = [] row = ['Benchmark'] row2 = ['Points Possible'] benchmarks = Benchmark.objects.filter(question__test=test).distinct() for benchmark in benchmarks: row.append(benchmark) row.append('%') row2.append(test.question_set.filter(benchmarks=benchmark).aggregate(Sum('point_value'))['point_value__sum']) row2.append('') data.append(row) data.append(row2) i = 3 # 3 for third row on spreadsheet for test_instance in test.testinstance_set.all(): row = [test_instance.student] a = 2 # the letter c or column c in spreadsheet for benchmark in Benchmark.objects.filter(question__test=test).distinct(): row.append(test_instance.answerinstance_set.filter( question__benchmarks=benchmark).aggregate( Sum('points_earned'))['points_earned__sum']) row.append('={0}{1}/{0}$2'.format(get_column_letter(a), str(i))) a += 2 # skip ahead 2 columns i += 1 data.append(row) report.add_sheet(data, title="Benchmark", auto_width=True) data = [['Benchmark', 'Name', 'Earned', 'Possible', 'Percent']] i = 2 for benchmark in benchmarks: row = [] row += [benchmark.number, benchmark.name] answer_data = AnswerInstance.objects.filter( question__test=test, question__benchmarks=benchmark).aggregate( Sum('points_earned'), Sum('points_possible')) row += [answer_data['points_earned__sum']] row += [answer_data['points_possible__sum']] row += ['=C{0}/D{0}'.format(str(i))] data += [row] report.add_sheet(data, title="Benchmarks for class", auto_width=True) return report.as_download()
def grade_analytics(request): def check_if_match(grade, filter, filter_grade): try: if grade == "P": grade = 100 grade = float(grade) filter_grade = float(filter_grade) if filter == 'lt': if grade < filter_grade: return True elif filter == 'lte': if grade <= filter_grade: return True elif filter == 'gt': if grade > filter_grade: return True elif filter == 'gte': if grade >= filter_grade: return True except: pass return False form = GradeFilterForm() if request.method == 'POST': if 'edit' in request.POST: selected = request.POST.getlist('selected') return redirect('/admin/sis/student/?id__in=%s' % (','.join(selected), )) form = GradeFilterForm(request.POST) if form.is_valid(): ids = [] if 'submit_course' in request.POST and 'course' in request.POST: course_selection = CourseSelectionForm(request.POST) if course_selection.is_valid(): for key in request.POST: if key[:9] == "selected_": ids.append(request.POST[key]) students = Student.objects.filter(id__in=ids) if students and course_selection.cleaned_data['course']: for student in students: CourseEnrollment.objects.get_or_create( user=student, course=course_selection.cleaned_data['course'], role="student") messages.success( request, 'Students added to %s!' % (course_selection.cleaned_data['course'].shortname, )) else: messages.success( request, 'Did not enroll, please select students and a course.' ) course_selection = CourseSelectionForm() data = form.cleaned_data students = Student.objects.all() if not data['include_deleted']: students = students.filter(inactive=False) if data['filter_year']: students = students.filter(year__in=data['filter_year']) if data['in_individual_education_program']: students = students.filter(individual_education_program=True) if data['gpa']: # this will be something like filter(cache_gpa__lte=gpa) arg = 'cache_gpa__' + data['gpa_equality'] students = students.filter(**{ arg: data['gpa'], }) courses = Course.objects.filter( courseenrollment__user__in=students, graded=True) if data['this_year']: courses = courses.filter( marking_period__school_year=SchoolYear.objects.get( active_year=True)) elif not data['all_years']: courses = courses.filter( marking_period__start_date__gte=data['date_begin'], marking_period__end_date__lte=data['date_end'], ) if data['marking_period']: courses = courses.filter( marking_period__in=data['marking_period']) students = students.distinct() courses = courses.distinct() mps_selected = [] for mp in data['marking_period']: mps_selected.append(mp.id) show_students = [] max_courses = 0 (date_begin, date_end) = form.get_dates() # Pre load Discipline data if 'ecwsp.discipline' in settings.INSTALLED_APPS: if data['filter_disc_action'] and data['filter_disc'] and data[ 'filter_disc_times']: student_disciplines = students.filter( studentdiscipline__date__range=(date_begin, date_end), studentdiscipline__action=data['filter_disc_action'], ).annotate(action_count=Count('studentdiscipline__action')) if data['aggregate_disc'] and data['aggregate_disc_times']: if data['aggregate_disc_major']: student_aggregate_disciplines = students.filter( studentdiscipline__date__range=(date_begin, date_end), studentdiscipline__action__major_offense=True, ).annotate(action_count=Count('studentdiscipline')) else: student_aggregate_disciplines = students.filter( studentdiscipline__date__range=( date_begin, date_end), ).annotate( action_count=Count('studentdiscipline')) for student in students: student.aggregate_disciplines = 0 for aggr in student_aggregate_disciplines: if aggr.id == student.id: student.aggregate_disciplines = aggr.action_count break # Pre load Attendance data if data['filter_attn'] and data['filter_attn_times']: student_attendances = students.filter( student_attn__date__range=(date_begin, date_end), student_attn__status__absent=True, ).annotate(attn_count=Count('student_attn')) if data['filter_tardy'] and data['filter_tardy_times']: students_tardies = students.filter( student_attn__date__range=(date_begin, date_end), student_attn__status__tardy=True, ).annotate(tardy_count=Count('student_attn')) for student in students: student.tardy_count = 0 for student_tardies in students_tardies: if student_tardies.id == student.id: student.tardy_count = student_tardies.tardy_count break for student in students: # if this is a report, only calculate for selected students. if not 'xls' in request.POST or "selected" in request.POST: num_matched = 0 add_to_list = True # If all criteria is met, add student to list match_all = True student.courses = [] i_courses = 0 student.departments = [] for dept in Department.objects.all(): student.departments.append("") # for each grade for this student course = None done = False grades_text = "" if add_to_list and data['final_grade'] and data[ 'final_grade_filter'] and data['final_grade_times']: for course in student.course_set.filter( id__in=courses): grade = course.get_final_grade(student) if grade: match = check_if_match( grade, data['final_grade_filter'], data['final_grade']) if match: student.courses.append( str(course.shortname) + ' <br/>' + str(grade)) num_matched += 1 i_courses += 1 if max_courses < i_courses: max_courses = i_courses i = 0 for dept in Department.objects.all(): if dept == course.department: student.departments[i] = "X" i += 1 else: match_all = False if data['final_grade_times'] == "*" and not match_all: add_to_list = False elif data[ 'final_grade_times'] != "*" and not num_matched >= int( data['final_grade_times']): add_to_list = False if add_to_list and data['grade'] and data[ 'grade_filter'] and data['grade_times']: # Using just grades for optimization. Rather than for course, for mp, for grade. for grade in student.grade_set.filter( course__in=courses, course__courseenrollment__user=student ).order_by('course__department', 'marking_period').select_related(): if mps_selected == [] or grade.marking_period_id in mps_selected: # if this is a new course, add previous course to student if grade.course != course: if grades_text: student.courses.append( str(course.shortname) + ' <br/>' + grades_text) i_courses += 1 if max_courses < i_courses: max_courses = i_courses i = 0 for dept in Department.objects.all(): if dept == course.department: student.departments[i] = "X" i += 1 grades_text = "" course = grade.course # data['each_marking_period'] and if grade.override_final == False: grade_value = grade.get_grade() match = check_if_match( grade_value, data['grade_filter'], data['grade']) if match: grades_text += str( grade.marking_period.shortname ) + ':' + str(grade_value) + " " num_matched += 1 else: match_all = False if grades_text: student.courses.append( str(course.shortname) + ' <br/>' + grades_text) i_courses += 1 if max_courses < i_courses: max_courses = i_courses i = 0 for dept in Department.objects.all(): if dept == course.department: student.departments[i] = "X" i += 1 grades_text = "" if data['grade_times'] == "*" and not match_all: add_to_list = False if data['grade_times'] != "*" and not num_matched >= int( data['grade_times']): add_to_list = False # Check discipline if add_to_list and 'ecwsp.discipline' in settings.INSTALLED_APPS: if data['filter_disc_action'] and data[ 'filter_disc'] and data['filter_disc_times']: student.action_count = 0 for disc in student_disciplines: if disc.id == student.id: student.action_count = disc.action_count break if ((data['filter_disc'] == "lt" and not student.action_count < int( data['filter_disc_times'])) or (data['filter_disc'] == "lte" and not student.action_count <= int( data['filter_disc_times'])) or (data['filter_disc'] == "gt" and not student.action_count > int( data['filter_disc_times'])) or (data['filter_disc'] == "gte" and not student.action_count >= int( data['filter_disc_times']))): add_to_list = False else: student.courses.append( '%s: %s' % (data['filter_disc_action'], student.action_count)) if data['aggregate_disc'] and data[ 'aggregate_disc_times']: if ((data['aggregate_disc'] == "lt" and not student.aggregate_disciplines < int( data['aggregate_disc_times'])) or (data['aggregate_disc'] == "lte" and not student.aggregate_disciplines <= int(data['aggregate_disc_times'])) or (data['aggregate_disc'] == "gt" and not student.aggregate_disciplines > int(data['aggregate_disc_times'])) or (data['aggregate_disc'] == "gte" and not student.aggregate_disciplines >= int(data['aggregate_disc_times']))): add_to_list = False else: student.courses.append( '%s: %s' % ("Aggregate Discipline", student.aggregate_disciplines)) # Check Attendance if add_to_list and data['filter_attn'] and data[ 'filter_attn_times']: try: student.attn_count = student_attendances.get( id=student.id).attn_count except: student.attn_count = 0 if ((data['filter_attn'] == "lt" and not student.attn_count < int( data['filter_attn_times'])) or (data['filter_attn'] == "lte" and not student.attn_count <= int( data['filter_attn_times'])) or (data['filter_attn'] == "gt" and not student.attn_count > int( data['filter_attn_times'])) or (data['filter_attn'] == "gte" and not student.attn_count >= int( data['filter_attn_times']))): add_to_list = False else: student.courses.append('Absents: %s' % (student.attn_count, )) # Tardies if add_to_list and data['filter_tardy'] and data[ 'filter_tardy_times']: if ((data['filter_tardy'] == "lt" and not student.tardy_count < int( data['filter_tardy_times'])) or (data['filter_tardy'] == "lte" and not student.tardy_count <= int( data['filter_tardy_times'])) or (data['filter_tardy'] == "gt" and not student.tardy_count > int( data['filter_tardy_times'])) or (data['filter_tardy'] == "gte" and not student.tardy_count >= int( data['filter_tardy_times']))): add_to_list = False else: student.courses.append('Tardies: %s' % (student.tardy_count, )) if add_to_list: show_students.append(student) # Print xls report if 'xls' in request.POST or 'xls_asp' in request.POST: pref = UserPreference.objects.get_or_create( user=request.user)[0] titles = ['Student'] data = [] for student in show_students: if unicode(student.id) in request.POST.getlist('selected'): row = [student] pref.get_additional_student_fields( row, student, show_students, titles) i = 0 for course in student.courses: row.append(course.replace("<br/>", " ")) i += 1 # padding data while i < max_courses: row.append("") i += 1 if 'xls_asp' in request.POST: for dept in student.departments: row.append(dept) data.append(row) titles.append('Grades') i = 1 while i < max_courses: titles.append('') i += 1 if 'xls_asp' in request.POST: for dept in Department.objects.all(): titles.append(dept) report = XlReport(file_name="Analytics") report.add_sheet(data, header_row=titles, title="Analytics Report", heading="Analytics Report") return report.as_download() return render_to_response( 'schedule/grade_analytics.html', { 'form': form, 'course_selection': None, 'students': show_students, }, RequestContext(request, {}), ) return render_to_response( 'schedule/grade_analytics.html', { 'form': form, }, RequestContext(request, {}), )
def download_results(self, test): """ Create basic xls report for OMR. Includes summary and details """ # from download_teacher_results() total_test_takers = test.active_testinstance_set.filter( answerinstance__points_earned__gt=0).distinct().count() # Summary sheet data = [[test.name]] data.append(["Points Possible:", test.points_possible]) data.append(["Results collected: %s" % (test.students_test_results,)]) data.append(['Test average: %s' % (test.points_average,)]) data.append(['']) data.append(['Student', 'Points Earned', 'Percentage']) first_student_row = i = 7 for ti in test.active_testinstance_set.annotate(earned=Sum('answerinstance__points_earned')): data.append([ti.student, ti.earned, "=B%s / $B$2" % i]) i += 1 # Make it easier to compare this against download_teacher_results() data.append(['']) i += 1 data.append(["Students scoring at or above 70%", # number of points possible is in cell B2 '=COUNTIF(B{0}:B{1},">="&0.7*B2)'.format(first_student_row, i - 2), # don't put the decimal inside the string to avoid localization problems '=COUNTIF(C{0}:C{1},">="&0.7)/COUNT(C6:C{1})'.format(first_student_row, i - 2)]) i += 1 report = XlReport(file_name="OMR Report") report.add_sheet(data, title="Summary", auto_width=True) # Detail sheets data_points = [] data_answers = [] data_abc = [] row_points = ["Student"] row_answers = ["Student"] row_abc = ["Student"] for i, question in enumerate(test.question_set.all()): row_points.append("%s %s" % (question.get_order_start_one, strip_tags(question.question).strip())) row_answers.append("%s %s" % (question.get_order_start_one, strip_tags(question.question).strip())) row_abc.append("Question {0}".format(i+1)) data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) for test_instance in test.active_testinstance_set.all(): row_points = [] row_answers = [] row_abc = [] row_points.append(test_instance.student) row_answers.append(test_instance.student) row_abc.append(test_instance.student) for question in test.question_set.all(): try: answer = test_instance.answerinstance_set.get(question=question) row_points.append(answer.points_earned) row_answers.append(strip_tags(str(answer.answer)).strip()) i = None if question.type == "True/False": row_abc += [answer.answer] else: for i, x in enumerate(question.answer_set.all()): if x == answer.answer: break row_abc += [chr(65 + i)] except ObjectDoesNotExist: row_points += [''] row_answers += [''] row_abc += [''] data_points.append(row_points) data_answers.append(row_answers) data_abc.append(row_abc) report.add_sheet(data_points, title="Detail Points", auto_width=True) report.add_sheet(data_answers, title="Detail Answers", auto_width=True) report.add_sheet(data_abc, title="Answer Sheet", auto_width=True) # Benchmark sheet data = [] row = ['Benchmark'] row2 = ['Points Possible'] benchmarks = Benchmark.objects.filter(question__test=test).distinct() for benchmark in benchmarks: row.append(benchmark) row.append('%') benchmark_points_possible = test.question_set.filter(benchmarks=benchmark).aggregate( Sum('point_value') )['point_value__sum'] # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if benchmark_points_possible is None: benchmark_points_possible = 0 row2.append(benchmark_points_possible) row2.append('') data.append(row) data.append(row2) first_student_row = i = 3 # 3 for third row on spreadsheet for test_instance in test.active_testinstance_set.all(): row = [test_instance.student] a = 2 # the letter b or column b in spreadsheet for benchmark in Benchmark.objects.filter(question__test=test).distinct(): benchmark_points_possible = test_instance.answerinstance_set.filter( question__benchmarks=benchmark ).aggregate(Sum('points_earned'))['points_earned__sum'] # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if benchmark_points_possible is None: benchmark_points_possible = 0 row.append(benchmark_points_possible) row.append('={0}{1}/{0}$2'.format(get_column_letter(a), str(i))) a += 2 # skip ahead 2 columns i += 1 data.append(row) # Make it easier to compare this against download_teacher_results() data.append(['']) i += 1 row = ['Students scoring at or above 70%'] col = 2 while col < a: row.append('=COUNTIF({0}{1}:{0}{2},">="&0.7*{0}{3})'.format( get_column_letter(col), first_student_row, i - 2, first_student_row - 1)) col += 1 row.append('=COUNTIF({0}{1}:{0}{2},">="&0.7)/COUNT({0}{1}:{0}{2})'.format( get_column_letter(col), first_student_row, i - 2)) col += 1 data.append(row) i += 1 report.add_sheet(data, title="Benchmark", auto_width=True) data = [['Benchmark', 'Name', 'Earned', 'Possible', 'Percent']] i = 2 for benchmark in benchmarks: row = [] row += [benchmark.number, benchmark.name] answer_data = AnswerInstance.objects.filter( test_instance__in=test.active_testinstance_set.all(), question__benchmarks=benchmark).aggregate( Sum('points_earned'), Sum('points_possible')) # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if answer_data['points_earned__sum']: row += [answer_data['points_earned__sum']] else: row += [0] # this causes a discrepancy with download_teacher_results() if # a student leaves a question blank. #row += [answer_data['points_possible__sum']] # instead, get the points possible for all questions having this benchmark and # multiply by the number of test takers benchmark_points_possible = test.question_set.filter(benchmarks=benchmark).aggregate( Sum('point_value'))['point_value__sum'] # aggregate() may return None, which will raise a TypeError upon attempting arithmetic if benchmark_points_possible: benchmark_points_possible *= total_test_takers else: benchmark_points_possible = 0 row.append(benchmark_points_possible) row += ['=C{0}/D{0}'.format(str(i))] data += [row] i += 1 report.add_sheet(data, title="Benchmarks for class", auto_width=True) return report.as_download()
def discipline_report_view(request): form = DisciplineStudentStatistics() merit_form = MeritForm() if request.method == 'POST': if 'merit' in request.POST: merit_form = MeritForm(request.POST) if merit_form.is_valid(): from ecwsp.sis.report import pod_report_generic data = {} l1 = merit_form.cleaned_data['level_one'] l2 = merit_form.cleaned_data['level_two'] l3 = merit_form.cleaned_data['level_three'] l4 = merit_form.cleaned_data['level_four'] start_date = merit_form.cleaned_data['start_date'] end_date = merit_form.cleaned_data['end_date'] students = Student.objects.filter(inactive=False) if merit_form.cleaned_data['sort_by'] == 'year': students = students.order_by('year') elif merit_form.cleaned_data['sort_by'] == 'cohort': students = students.order_by('cache_cohort') disciplines = StudentDiscipline.objects.filter( date__range=(start_date, end_date)).values('students').annotate( Count('pk')) for student in students: disc = 0 for discipline in disciplines: if discipline['students'] == student.id: disc = discipline['pk__count'] break student.disc_count = disc if student.disc_count <= l1: student.merit_level = 1 elif student.disc_count <= l2: student.merit_level = 2 elif student.disc_count <= l3: student.merit_level = 3 elif student.disc_count <= l4: student.merit_level = 4 data['students'] = students template = Template.objects.get_or_create( name="Merit Level Handout")[0] template = template.get_template_path(request) format_type = UserPreference if template: format_type = UserPreference.objects.get_or_create( user=request.user)[0].get_format() return pod_report_generic(template, data, "Merit Handouts", format=format_type) else: form = DisciplineStudentStatistics(request.POST) if form.is_valid(): data = [] start, end = form.get_dates() if 'student' in request.POST: students = Student.objects.all() school_year = SchoolYear.objects.get(active_year=True) if form.cleaned_data['date_begin']: if SchoolYear.objects.filter( end_date__gte=form.cleaned_data['date_begin']): school_year = SchoolYear.objects.filter( end_date__gte=form.cleaned_data['date_begin'] ).order_by('start_date')[0] # students belonging to this school year students = students.filter( course__marking_period__school_year__exact= school_year).distinct() if not form.cleaned_data['include_deleted']: students = students.exclude(inactive=True) if form.cleaned_data['order_by'] == "Year": students = students.order_by('year') subtitles = [ "Student", ] titles = [ "", "Infractions", ] for infr in Infraction.objects.all(): titles.append("") titles.pop() titles.append("Actions") for infr in Infraction.objects.all(): subtitles.append(unicode(infr)) for action in DisciplineAction.objects.all(): subtitles.append(unicode(action)) titles.append("") titles.pop() data.append(subtitles) pref = UserPreference.objects.get_or_create( user=request.user)[0] for student in students: disciplines = student.studentdiscipline_set.all() disciplines = disciplines.filter(date__range=(start, end)) stats = [ unicode(student), ] add = True for infr in Infraction.objects.all(): number = disciplines.filter( infraction=infr, students=student).count() stats.append(number) # check for filter if form.cleaned_data['infraction'] == infr: infraction_discipline = disciplines.filter( infraction=form.cleaned_data['infraction']) if number < form.cleaned_data[ 'minimum_infraction']: add = False for action in DisciplineAction.objects.all(): actions = disciplines.filter( disciplineactioninstance__action=action, students=student).count() stats.append(actions) # check for filter if form.cleaned_data['action'] == action: if actions < form.cleaned_data[ 'minimum_action']: add = False pref.get_additional_student_fields( stats, student, students, titles) if add: data.append(stats) report = XlReport(file_name="disc_stats") report.add_sheet(data, header_row=titles, title="Discipline Stats", heading="Discipline Stats") # By Teacher data = [] titles = ['teacher'] for action in DisciplineAction.objects.all(): titles.append(action) teachers = Faculty.objects.filter( studentdiscipline__isnull=False).distinct() disciplines = StudentDiscipline.objects.filter( date__range=(start, end)) for teacher in teachers: row = [teacher] for action in DisciplineAction.objects.all(): row.append( disciplines.filter(teacher=teacher, action=action).count()) data.append(row) report.add_sheet(data, header_row=titles, heading="By Teachers") return report.as_download() elif 'aggr' in request.POST: disciplines = StudentDiscipline.objects.filter( date__range=(start, end)) if form.cleaned_data['this_year']: school_start = SchoolYear.objects.get( active_year=True).start_date school_end = SchoolYear.objects.get( active_year=True).end_date disciplines = disciplines.filter( date__range=(school_start, school_end)) elif not form.cleaned_data[ 'this_year'] and not form.cleaned_data['all_years']: disciplines = disciplines.filter( date__range=(form.cleaned_data['date_begin'], form.cleaned_data['date_end'])) stats = [] titles = [] for infr in Infraction.objects.all(): titles.append(infr) number = disciplines.filter(infraction=infr).count() stats.append(number) for action in DisciplineAction.objects.all(): titles.append(action) number = 0 for a in DisciplineActionInstance.objects.filter( action=action): number += a.quantity stats.append(number) data.append(stats) report = XlReport(file_name="disc_stats") report.add_sheet(data, header_row=titles, title="Discipline Stats", heading="Discipline Stats") return report.as_download() else: return render_to_response( 'discipline/disc_report.html', { 'request': request, 'form': form }, RequestContext(request, {}), ) return render_to_response( 'discipline/disc_report.html', { 'request': request, 'form': form, 'merit_form': merit_form, }, RequestContext(request, {}), )
def grade_analytics(request): def check_if_match(grade, filter, filter_grade): try: if grade == "P": grade = 100 grade = float(grade) filter_grade = float(filter_grade) if filter == 'lt': if grade < filter_grade: return True elif filter == 'lte': if grade <= filter_grade: return True elif filter == 'gt': if grade > filter_grade: return True elif filter == 'gte': if grade >= filter_grade: return True except: pass return False form = GradeFilterForm() if request.method == 'POST': if 'edit' in request.POST: selected = request.POST.getlist('selected') return redirect('/admin/sis/student/?id__in=%s' % (','.join(selected),)) form = GradeFilterForm(request.POST) if form.is_valid(): ids = [] if 'submit_course' in request.POST and 'course' in request.POST: course_selection = CourseSelectionForm(request.POST) if course_selection.is_valid(): for key in request.POST: if key[:9] == "selected_": ids.append(request.POST[key]) students = Student.objects.filter(id__in=ids) if students and course_selection.cleaned_data['course']: for student in students: CourseEnrollment.objects.get_or_create(user=student, course=course_selection.cleaned_data['course'], role="student") messages.success(request, 'Students added to %s!' % (course_selection.cleaned_data['course'].shortname,)) else: messages.success(request, 'Did not enroll, please select students and a course.') course_selection = CourseSelectionForm() data = form.cleaned_data students = Student.objects.all() if not data['include_deleted']: students = students.filter(inactive=False) if data['filter_year']: students = students.filter(year__in=data['filter_year']) if data['in_individual_education_program']: students = students.filter(individual_education_program=True) if data['gpa']: # this will be something like filter(cache_gpa__lte=gpa) arg = 'cache_gpa__' + data['gpa_equality'] students = students.filter(**{arg: data['gpa'],}) courses = Course.objects.filter(courseenrollment__user__in=students, graded=True) if data['this_year']: courses = courses.filter(marking_period__school_year=SchoolYear.objects.get(active_year=True)) elif not data['all_years']: courses = courses.filter( marking_period__start_date__gte=data['date_begin'], marking_period__end_date__lte=data['date_end'], ) if data['marking_period']: courses = courses.filter(marking_period__in=data['marking_period']) students = students.distinct() courses = courses.distinct() mps_selected = [] for mp in data['marking_period']: mps_selected.append(mp.id) show_students = [] max_courses = 0 (date_begin, date_end) = form.get_dates() # Pre load Discipline data if 'ecwsp.discipline' in settings.INSTALLED_APPS: if data['filter_disc_action'] and data['filter_disc'] and data['filter_disc_times']: student_disciplines = students.filter(studentdiscipline__date__range=(date_begin, date_end), studentdiscipline__action=data['filter_disc_action'], ).annotate(action_count=Count('studentdiscipline__action')) if data['aggregate_disc'] and data['aggregate_disc_times']: if data['aggregate_disc_major']: student_aggregate_disciplines = students.filter(studentdiscipline__date__range=(date_begin, date_end), studentdiscipline__action__major_offense=True, ).annotate(action_count=Count('studentdiscipline')) else: student_aggregate_disciplines = students.filter(studentdiscipline__date__range=(date_begin, date_end), ).annotate(action_count=Count('studentdiscipline')) for student in students: student.aggregate_disciplines = 0 for aggr in student_aggregate_disciplines: if aggr.id == student.id: student.aggregate_disciplines = aggr.action_count break # Pre load Attendance data if data['filter_attn'] and data['filter_attn_times']: student_attendances = students.filter(student_attn__date__range=(date_begin, date_end), student_attn__status__absent=True, ).annotate(attn_count=Count('student_attn')) if data['filter_tardy'] and data['filter_tardy_times']: students_tardies = students.filter(student_attn__date__range=(date_begin, date_end), student_attn__status__tardy=True, ).annotate(tardy_count=Count('student_attn')) for student in students: student.tardy_count = 0 for student_tardies in students_tardies: if student_tardies.id == student.id: student.tardy_count = student_tardies.tardy_count break for student in students: # if this is a report, only calculate for selected students. if not 'xls' in request.POST or "selected" in request.POST: num_matched = 0 add_to_list = True # If all criteria is met, add student to list match_all = True student.courses = [] i_courses = 0 student.departments = [] for dept in Department.objects.all(): student.departments.append("") # for each grade for this student course = None done = False grades_text = "" if add_to_list and data['final_grade'] and data['final_grade_filter'] and data['final_grade_times']: for course in student.course_set.filter(id__in=courses): grade = course.get_final_grade(student) if grade: match = check_if_match(grade, data['final_grade_filter'], data['final_grade']) if match: student.courses.append(str(course.shortname) + ' <br/>' + str(grade)) num_matched += 1 i_courses += 1 if max_courses < i_courses: max_courses = i_courses i = 0 for dept in Department.objects.all(): if dept == course.department: student.departments[i] = "X" i += 1 else: match_all = False if data['final_grade_times'] == "*" and not match_all: add_to_list = False elif data['final_grade_times'] != "*" and not num_matched >= int(data['final_grade_times']): add_to_list = False if add_to_list and data['grade'] and data['grade_filter'] and data['grade_times']: # Using just grades for optimization. Rather than for course, for mp, for grade. for grade in student.grade_set.filter(course__in=courses, course__courseenrollment__user=student).order_by('course__department', 'marking_period').select_related(): if mps_selected == [] or grade.marking_period_id in mps_selected: # if this is a new course, add previous course to student if grade.course != course: if grades_text: student.courses.append(str(course.shortname) + ' <br/>' + grades_text) i_courses += 1 if max_courses < i_courses: max_courses = i_courses i = 0 for dept in Department.objects.all(): if dept == course.department: student.departments[i] = "X" i += 1 grades_text = "" course = grade.course # data['each_marking_period'] and if grade.override_final == False: grade_value = grade.get_grade() match = check_if_match(grade_value, data['grade_filter'], data['grade']) if match: grades_text += str(grade.marking_period.shortname) + ':' + str(grade_value) + " " num_matched += 1 else: match_all = False if grades_text: student.courses.append(str(course.shortname) + ' <br/>' + grades_text) i_courses += 1 if max_courses < i_courses: max_courses = i_courses i = 0 for dept in Department.objects.all(): if dept == course.department: student.departments[i] = "X" i += 1 grades_text = "" if data['grade_times'] == "*" and not match_all: add_to_list = False if data['grade_times'] != "*" and not num_matched >= int(data['grade_times']): add_to_list = False # Check discipline if add_to_list and 'ecwsp.discipline' in settings.INSTALLED_APPS: if data['filter_disc_action'] and data['filter_disc'] and data['filter_disc_times']: student.action_count = 0 for disc in student_disciplines: if disc.id == student.id: student.action_count = disc.action_count break if ((data['filter_disc'] == "lt" and not student.action_count < int(data['filter_disc_times'])) or (data['filter_disc'] == "lte" and not student.action_count <= int(data['filter_disc_times'])) or (data['filter_disc'] == "gt" and not student.action_count > int(data['filter_disc_times'])) or (data['filter_disc'] == "gte" and not student.action_count >= int(data['filter_disc_times'])) ): add_to_list = False else: student.courses.append('%s: %s' % (data['filter_disc_action'], student.action_count)) if data['aggregate_disc'] and data['aggregate_disc_times']: if ((data['aggregate_disc'] == "lt" and not student.aggregate_disciplines < int(data['aggregate_disc_times'])) or (data['aggregate_disc'] == "lte" and not student.aggregate_disciplines <= int(data['aggregate_disc_times'])) or (data['aggregate_disc'] == "gt" and not student.aggregate_disciplines > int(data['aggregate_disc_times'])) or (data['aggregate_disc'] == "gte" and not student.aggregate_disciplines >= int(data['aggregate_disc_times'])) ): add_to_list = False else: student.courses.append('%s: %s' % ("Aggregate Discipline", student.aggregate_disciplines)) # Check Attendance if add_to_list and data['filter_attn'] and data['filter_attn_times']: try: student.attn_count = student_attendances.get(id=student.id).attn_count except: student.attn_count = 0 if ((data['filter_attn'] == "lt" and not student.attn_count < int(data['filter_attn_times'])) or (data['filter_attn'] == "lte" and not student.attn_count <= int(data['filter_attn_times'])) or (data['filter_attn'] == "gt" and not student.attn_count > int(data['filter_attn_times'])) or (data['filter_attn'] == "gte" and not student.attn_count >= int(data['filter_attn_times'])) ): add_to_list = False else: student.courses.append('Absents: %s' % (student.attn_count,)) # Tardies if add_to_list and data['filter_tardy'] and data['filter_tardy_times']: if ((data['filter_tardy'] == "lt" and not student.tardy_count < int(data['filter_tardy_times'])) or (data['filter_tardy'] == "lte" and not student.tardy_count <= int(data['filter_tardy_times'])) or (data['filter_tardy'] == "gt" and not student.tardy_count > int(data['filter_tardy_times'])) or (data['filter_tardy'] == "gte" and not student.tardy_count >= int(data['filter_tardy_times'])) ): add_to_list = False else: student.courses.append('Tardies: %s' % (student.tardy_count,)) if add_to_list: show_students.append(student) # Print xls report if 'xls' in request.POST or 'xls_asp' in request.POST: pref = UserPreference.objects.get_or_create(user=request.user)[0] titles = ['Student'] data = [] for student in show_students: if unicode(student.id) in request.POST.getlist('selected'): row = [student] pref.get_additional_student_fields(row, student, show_students, titles) i = 0 for course in student.courses: row.append(course.replace("<br/>", " ")) i += 1 # padding data while i < max_courses: row.append("") i += 1 if 'xls_asp' in request.POST: for dept in student.departments: row.append(dept) data.append(row) titles.append('Grades') i = 1 while i < max_courses: titles.append('') i += 1 if 'xls_asp' in request.POST: for dept in Department.objects.all(): titles.append(dept) report = XlReport(file_name="Analytics") report.add_sheet(data, header_row=titles, title="Analytics Report", heading="Analytics Report") return report.as_download() return render_to_response('schedule/grade_analytics.html', {'form': form, 'course_selection': None, 'students': show_students,}, RequestContext(request, {}),) return render_to_response('schedule/grade_analytics.html', {'form': form,}, RequestContext(request, {}),)
def am_route_attendance(request): # TO DO Implement workday selection!!!! data = [] titles = [ "Company", "Address", "City", "State", "Zip", "Contact First", "Contact Last", "Phone", "Grade", 'Workday', 'Gender', 'Student First', 'Student Last', 'Cell', 'Earliest', 'Latest', 'Ideal', 'Schedule', 'Notes', datetime.date.today() ] report = None if 'am_route_attendance' in request.POST: fileName = "AM_Routes.xls" else: fileName = "PM_Routes.xls" if not StudentWorkerRoute.objects.all(): messages.error( request, 'You must create at least one route before running this report!') return HttpResponseRedirect(request.META.get('HTTP_REFERER', '/')) for route in StudentWorkerRoute.objects.all(): data = [] if 'am_route_attendance' in request.POST: students = StudentWorker.objects.filter(am_route=route, is_active=True) else: students = StudentWorker.objects.filter(pm_route=route, is_active=True) for student in students: row = [] if hasattr(student, 'placement') and student.placement: row += [ student.placement, student.placement.address, student.placement.city, student.placement.state, student.placement.zip, ] else: row += ['', '', '', '', ''] if hasattr(student, 'primary_contact') and student.primary_contact: row += [ student.primary_contact.fname, student.primary_contact.lname, student.primary_contact.phone, ] else: row += ['', '', ''] row += [ student.year, student.day, student.sex, student.fname, student.lname, ] # all the student's personal numbers, comma-separated row += [ ','.join(map(str, student.studentnumber_set.all())), ] if hasattr(student, 'placement') and student.placement: row += [ student.placement.time_earliest, student.placement.time_latest, student.placement.time_ideal, ] # help text for pm_transport_group says it can be left blank if not different than am_transport_group if 'am_route_attendance' in request.POST or student.placement.pm_transport_group is None: row += [ unicode(student.placement.am_transport_group), ] else: row += [ unicode(student.placement.pm_transport_group), ] row += [ student.placement.directions_to, ] else: row += ['', '', '', '', ''] row += [ student.get_transport_exception_display(), ] data.append(row) if not report: report = XlReport(file_name=fileName) report.add_sheet(data, header_row=titles, title=route.name, heading=route) return report.as_download()
def count_items_by_category_across_course_sections( year_category_names, current_marking_period_category_names, item_criteria, category_heading_format, percentage_threshold, course_section_threshold, inverse=False): from ecwsp.sis.xl_report import XlReport from ecwsp.work_study.models import StudentWorker all_category_names = list(year_category_names) all_category_names.extend(current_marking_period_category_names) all_categories = Category.objects.filter(name__in=all_category_names) year_categories = Category.objects.filter(name__in=year_category_names) current_marking_period_categories = Category.objects.filter( name__in=current_marking_period_category_names) titles = ['Last Name', 'First Name', 'Year', 'Work Day'] if not inverse: titles.append('Course Section') for c in all_categories: titles.append(category_heading_format.format(c.name)) #school_year = SchoolYear.objects.filter(start_date__lt=date.today()).order_by('-start_date')[0] school_year = SchoolYear.objects.get(active_year=True) marking_period = school_year.markingperiod_set.filter( show_reports=True, start_date__lt=date.today()).order_by('-start_date')[0] data = [] for student in Student.objects.filter(is_active=True).order_by( 'year', 'last_name', 'first_name'): try: work_day = StudentWorker.objects.get(username=student.username).day except StudentWorker.DoesNotExist: work_day = None matching_course_sections = [] for course_section in student.coursesection_set.filter( marking_period__school_year=school_year).distinct(): items = Item.objects.filter( Q(category__in=current_marking_period_categories, marking_period=marking_period) | Q(category__in=year_categories), course_section=course_section, mark__student=student).annotate( best_mark=Max('mark__mark')).exclude(best_mark=None) total_item_count = items.count() if not total_item_count: continue course_section_match = False matching_course_section_detail = [course_section.name] # check for combined category matches matching_item_count = items.filter(**item_criteria).count() matching_percentage = round( float(matching_item_count) / total_item_count * 100, 0) if matching_percentage >= percentage_threshold: course_section_match = True for c in all_categories: # check for individual category matches, and get detail for each category if combined matched already total_items_in_category = items.filter(category=c).count() matching_items_in_category = items.filter( **item_criteria).filter(category=c).count() if total_items_in_category: matching_percentage_in_category = round( float(matching_items_in_category) / total_items_in_category * 100) else: matching_percentage_in_category = 0 matching_course_section_detail.append('{}/{} ({}%)'.format( matching_items_in_category, total_items_in_category, matching_percentage_in_category)) if matching_percentage_in_category >= percentage_threshold: course_section_match = True if course_section_match: matching_course_sections.append(matching_course_section_detail) if len(matching_course_sections) >= course_section_threshold: if not inverse: for course_section in matching_course_sections: row = [ student.last_name, student.first_name, student.year, work_day ] row.extend(course_section) data.append(row) elif inverse: row = [ student.last_name, student.first_name, student.year, work_day ] data.append(row) report = XlReport() report.add_sheet(data, header_row=titles, heading="Sheet1", auto_width=True) return report.as_download()
def gradebook_export(request, course_section_id): gradebook_data = gradebook(request, course_section_id, for_export=True) if type(gradebook_data) is not dict: # something we can't use, like a 404 or a redirect return gradebook_data from ecwsp.sis.xl_report import XlReport report = XlReport(file_name=slugify(gradebook_data['course_section'].name)) rows = [] item_attributes = ( 'category', 'name', 'marking_period', 'assignment_type', 'benchmark', 'date', 'points_possible', 'description', ) demonstration_attributes = ( 'name', ) row_counter = 0 # explain all the header rows in the first column for attribute in item_attributes: rows.append([Item._meta.get_field(attribute).verbose_name.title()]) row_counter += 1 for attribute in demonstration_attributes: rows.append([u'Demonstration ' + Demonstration._meta.get_field(attribute).verbose_name.title()]) row_counter += 1 # then list all the students in the first column for student in gradebook_data['students']: rows.append([student]) # fill in the column headers, with column one per item/demonstration for item in gradebook_data['items']: if item.demonstration_set.count(): for dem in item.demonstration_set.all(): row_counter = 0 for attribute in item_attributes: rows[row_counter].append(getattr(item, attribute)) row_counter += 1 for attribute in demonstration_attributes: rows[row_counter].append(getattr(dem, attribute)) row_counter += 1 else: row_counter = 0 for attribute in item_attributes: rows[row_counter].append(getattr(item, attribute)) row_counter += 1 for attribute in demonstration_attributes: rows[row_counter].append('---------') row_counter += 1 # maybe attributes will be user-configurable in the future? if not row_counter: rows.append([]) row_counter = 1 # add one-off label to the bottom header row of the last column rows[row_counter - 1].append('Course Section Average') # save coordinates for formatting later course_section_average_row = row_counter - 1 course_section_average_column = len(rows[row_counter - 1]) - 1 # actually write out the students' grades for student in gradebook_data['students']: for mark in student.marks: rows[row_counter].append(mark.get_grade()) rows[row_counter].append(student.average) row_counter += 1 report.add_sheet(rows, title=gradebook_data['course_section'].name) sheet = report.workbook.get_active_sheet() if report.old_openpyxl: for row_number in range(0, len(item_attributes) + len(demonstration_attributes)): sheet.cell(row=row_number, column=0).style.font.bold = True sheet.cell(row=course_section_average_row, column=course_section_average_column).style.font.bold = True else: for row_number in range(1, len(item_attributes) + len(demonstration_attributes)): cell = sheet.cell(row=row_number, column=1) cell.style = cell.style.copy(font=cell.style.font.copy(bold=True)) cell = sheet.cell(row=course_section_average_row, column=course_section_average_column) cell.style = cell.style.copy(font=cell.style.font.copy(bold=True)) return report.as_download()
def aggregate_grade_report(request): from ecwsp.grades.models import Grade mp_form = MarkingPeriodForm(request.POST) if mp_form.is_valid(): mps = mp_form.cleaned_data['marking_period'] data = [] titles = ["Teacher", "Range", "No. Students", ""] for level in GradeLevel.objects.all(): titles += [level, ""] ranges = [['100', '90'], ['89.99', '80'], ['79.99', '70'], ['69.99', '60'], ['59.99', '50'], ['49.99', '0']] letter_ranges = ['P', 'F'] for teacher in Faculty.objects.filter( course__marking_period__in=mps).distinct(): data.append([teacher]) grades = Grade.objects.filter( marking_period__in=mps, course__teacher=teacher, student__inactive=False, override_final=False, ).filter(Q(grade__isnull=False) | Q(letter_grade__isnull=False)) teacher_students_no = grades.distinct().count() if teacher_students_no: for range in ranges: no_students = grades.filter( grade__range=(range[1], range[0]), ).distinct().count() percent = float(no_students) / float(teacher_students_no) percent = ('%.2f' % (percent * 100, )).rstrip('0').rstrip('.') + "%" row = [ "", str(range[1]) + " to " + str(range[0]), no_students, percent ] for level in GradeLevel.objects.all(): no_students = grades.filter( grade__range=(range[1], range[0]), student__year__in=[level], ).distinct().count() level_students_no = grades.filter( student__year__in=[level], ).distinct().count() percent = "" if level_students_no: percent = float(no_students) / float( level_students_no) percent = ('%.2f' % (percent * 100, ) ).rstrip('0').rstrip('.') + "%" row += [no_students, percent] data.append(row) for range in letter_ranges: no_students = grades.filter( letter_grade=range, ).distinct().count() if teacher_students_no: percent = float(no_students) / float( teacher_students_no) percent = ( '%.2f' % (percent * 100, )).rstrip('0').rstrip('.') + "%" else: percent = "" row = ["", str(range), no_students, percent] for level in GradeLevel.objects.all(): no_students = grades.filter( letter_grade=range, student__year__in=[level], ).distinct().count() level_students_no = grades.filter( student__year__in=[level], ).distinct().count() if level_students_no: percent = float(no_students) / float( level_students_no) percent = ('%.2f' % (percent * 100, ) ).rstrip('0').rstrip('.') + "%" else: percent = "" row += [no_students, percent] data.append(row) report = XlReport(file_name="aggregate_grade_report") report.add_sheet(data, header_row=titles, title="Teacher aggregate") passing = 70 data = [] titles = ['Grade'] for dept in Department.objects.all(): titles.append(dept) titles.append('') for level in GradeLevel.objects.all(): row = [level] for dept in Department.objects.all(): fails = Grade.objects.filter( marking_period__in=mps, course__department=dept, student__inactive=False, student__year__in=[ level ], # Shouldn't need __in. Makes no sense at all. grade__lt=passing, override_final=False, ).count() total = Grade.objects.filter( marking_period__in=mps, course__department=dept, student__inactive=False, student__year__in=[level], override_final=False, ).count() if total: percent = float(fails) / float(total) else: percent = 0 percent = ('%.2f' % (percent * 100, )).rstrip('0').rstrip('.') row.append(fails) row.append(percent) data.append(row) report.add_sheet(data, header_row=titles, title="Class Dept aggregate") return report.as_download()
def attendance_report(request): from ecwsp.sis.xl_report import XlReport form = AttendanceReportForm() daily_form = AttendanceDailyForm() lookup_form = AttendanceViewForm() if request.method == 'POST': if "daily" in request.POST: daily_form = AttendanceDailyForm(request.POST) if daily_form.is_valid(): type = UserPreference.objects.get_or_create(user=request.user)[0].get_format(type="document") return daily_attendance_report( daily_form.cleaned_data['date'], request, daily_form.cleaned_data['include_private_notes'], type=type, ) elif 'studentlookup' in request.POST: lookup_form = AttendanceViewForm(request.POST) if lookup_form.is_valid(): type = UserPreference.objects.get_or_create(user=request.user)[0].get_format(type="document") return attendance_student( request, lookup_form.cleaned_data['student'], all_years=lookup_form.cleaned_data['all_years'], order_by=lookup_form.cleaned_data['order_by'], include_private_notes=lookup_form.cleaned_data['include_private_notes']) else: return render_to_response( 'attendance/attendance_report.html', {'request': request, 'form':form, 'daily_form': daily_form, 'lookup_form': lookup_form}); else: form = AttendanceReportForm(request.POST) if form.is_valid(): attendances = StudentAttendance.objects.all() data = [] titles = [] attendances = attendances.filter(date__range=(form.get_dates())) if 'student' in request.POST: # by student students = Student.objects.all() if not form.cleaned_data['include_deleted']: students = students.filter(is_active=True) students = students.filter() titles.append("Student") titles.append("Total Absences (not half)") titles.append("Total Tardies") for status in AttendanceStatus.objects.exclude(name="Present"): titles.append(status) pref = UserPreference.objects.get_or_create(user=request.user)[0] students_absent = students.filter( student_attn__status__absent=True, student_attn__status__half=False, student_attn__in=attendances).annotate(abs=Count('student_attn')) students_tardy = students.filter( student_attn__status__tardy=True, student_attn__in=attendances).annotate(abs=Count('student_attn')) attn_tardy = attendances.filter(status__tardy=True) students_each_total = {} for status in AttendanceStatus.objects.exclude(name="Present"): students_each_total[status.name] = students.filter( student_attn__status=status, student_attn__in=attendances).annotate(abs=Count('student_attn')) for student in students: add = True row = [] row.append(student) if student in students_absent: total_absent = students_absent.filter(id=student.id)[0].abs else: total_absent = 0 if student in students_tardy: total_tardy = students_tardy.filter(id=student.id)[0].abs else: total_tardy = 0 if (total_absent >= form.cleaned_data['filter_total_absences'] and total_tardy >= form.cleaned_data['filter_total_tardies']): row.append( total_absent ) row.append( total_tardy ) for status in AttendanceStatus.objects.exclude(name="Present"): count = 0 if student in students_each_total[status.name]: count = students_each_total[status.name].filter(id=student.id)[0].abs row.append(count) if (form.cleaned_data['filter_status'] == status and attendances.filter( student=student, status=status).count() < form.cleaned_data['filter_count']): add = False if add: data.append(row) report = XlReport(file_name="attendance_report") report.add_sheet(data, header_row=titles, title="Attendance Report", heading="Attendance Report") elif 'perfect_attendance' in request.POST: form = AttendanceReportForm(request.POST) if form.is_valid(): data = form.cleaned_data template = Template.objects.get_or_create(name="Perfect attendance")[0] template = template.get_template_path(request) if not template: return render_to_response( 'attendance/attendance_report.html', { 'form':form, 'daily_form': daily_form, 'lookup_form': lookup_form}, RequestContext(request, {}),) students = Student.objects.all() perfect_students = [] if not form.cleaned_data['include_deleted']: students = students.filter(is_active=True) for student in students: total_absent = attendances.filter(status__absent=True, student=student).count() total_tardy = attendances.filter(status__tardy=True, student=student).count() if not total_absent and not total_tardy: perfect_students.append(student) format = UserPreference.objects.get_or_create(user=request.user)[0].get_format(type="document") return pod_report_all(template, students=perfect_students, format=format) else: # Aggregate report stats = [] for status in AttendanceStatus.objects.exclude(name="Present"): titles.append(status) number = attendances.filter(status=status).count() stats.append(number) data.append(stats) data.append([]) students = Student.objects.filter(is_active=True).count() absents = attendances.filter(status__absent=True).count() if form.cleaned_data['marking_period']: days = 0 for mp in form.cleaned_data['marking_period']: days += mp.get_number_days() else: days = SchoolYear.objects.get(active_year=True).get_number_days() #percentage = 1.0 - float(absents) / (float(students) * float(days)) percentage = '=1-(B4/(A4*C4))' data.append(['Students', 'Total Absents', 'School days', 'Absent Percentage']) data.append([students, absents, days, percentage]) report = XlReport(file_name="attendance_report") report.add_sheet(data, header_row=titles, title="Attendance Report") return report.as_download() return render_to_response( 'attendance/attendance_report.html', {'form':form, 'daily_form': daily_form, 'lookup_form': lookup_form}, RequestContext(request, {}),)
def attendance_report(request): from ecwsp.sis.xl_report import XlReport form = AttendanceReportForm() daily_form = AttendanceDailyForm() lookup_form = AttendanceViewForm() if request.method == 'POST': if "daily" in request.POST: daily_form = AttendanceDailyForm(request.POST) if daily_form.is_valid(): type = UserPreference.objects.get_or_create(user=request.user)[0].get_format(type="document") return daily_attendance_report( daily_form.cleaned_data['date'], request, daily_form.cleaned_data['include_private_notes'], type=type, ) elif 'studentlookup' in request.POST: lookup_form = AttendanceViewForm(request.POST) if lookup_form.is_valid(): type = UserPreference.objects.get_or_create(user=request.user)[0].get_format(type="document") return attendance_student( request, lookup_form.cleaned_data['student'].id, all_years=lookup_form.cleaned_data['all_years'], order_by=lookup_form.cleaned_data['order_by'], include_private_notes=lookup_form.cleaned_data['include_private_notes']) else: return render_to_response( 'attendance/attendance_report.html', {'request': request, 'form':form, 'daily_form': daily_form, 'lookup_form': lookup_form}); else: form = AttendanceReportForm(request.POST) if form.is_valid(): attendances = StudentAttendance.objects.all() data = [] titles = [] attendances = attendances.filter(date__range=(form.get_dates())) if 'student' in request.POST: # by student students = Student.objects.all() if not form.cleaned_data['include_deleted']: students = students.filter(is_active=True) students = students.filter() titles.append("Student") titles.append("Total Absences (not half)") titles.append("Total Tardies") for status in AttendanceStatus.objects.exclude(name="Present"): titles.append(status) pref = UserPreference.objects.get_or_create(user=request.user)[0] students_absent = students.filter( student_attn__status__absent=True, student_attn__status__half=False, student_attn__in=attendances).annotate(abs=Count('student_attn')) students_tardy = students.filter( student_attn__status__tardy=True, student_attn__in=attendances).annotate(abs=Count('student_attn')) attn_tardy = attendances.filter(status__tardy=True) students_each_total = {} for status in AttendanceStatus.objects.exclude(name="Present"): students_each_total[status.name] = students.filter( student_attn__status=status, student_attn__in=attendances).annotate(abs=Count('student_attn')) for student in students: add = True row = [] row.append(student) if student in students_absent: total_absent = students_absent.filter(id=student.id)[0].abs else: total_absent = 0 if student in students_tardy: total_tardy = students_tardy.filter(id=student.id)[0].abs else: total_tardy = 0 if (total_absent >= form.cleaned_data['filter_total_absences'] and total_tardy >= form.cleaned_data['filter_total_tardies']): row.append( total_absent ) row.append( total_tardy ) for status in AttendanceStatus.objects.exclude(name="Present"): count = 0 if student in students_each_total[status.name]: count = students_each_total[status.name].filter(id=student.id)[0].abs row.append(count) if (form.cleaned_data['filter_status'] == status and attendances.filter( student=student, status=status).count() < form.cleaned_data['filter_count']): add = False if add: data.append(row) report = XlReport(file_name="attendance_report") report.add_sheet(data, header_row=titles, title="Attendance Report", heading="Attendance Report") elif 'perfect_attendance' in request.POST: form = AttendanceReportForm(request.POST) if form.is_valid(): data = form.cleaned_data template = Template.objects.get_or_create(name="Perfect attendance")[0] template = template.get_template_path(request) if not template: return render_to_response( 'attendance/attendance_report.html', { 'form':form, 'daily_form': daily_form, 'lookup_form': lookup_form}, RequestContext(request, {}),) students = Student.objects.all() perfect_students = [] if not form.cleaned_data['include_deleted']: students = students.filter(is_active=True) for student in students: total_absent = attendances.filter(status__absent=True, student=student).count() total_tardy = attendances.filter(status__tardy=True, student=student).count() if not total_absent and not total_tardy: perfect_students.append(student) format = UserPreference.objects.get_or_create(user=request.user)[0].get_format(type="document") return pod_report_all(template, students=perfect_students, format=format) else: # Aggregate report stats = [] for status in AttendanceStatus.objects.exclude(name="Present"): titles.append(status) number = attendances.filter(status=status).count() stats.append(number) data.append(stats) data.append([]) students = Student.objects.filter(is_active=True).count() absents = attendances.filter(status__absent=True).count() if form.cleaned_data['marking_period']: days = 0 for mp in form.cleaned_data['marking_period']: days += mp.get_number_days() else: days = SchoolYear.objects.get(active_year=True).get_number_days() #percentage = 1.0 - float(absents) / (float(students) * float(days)) percentage = '=1-(B4/(A4*C4))' data.append(['Students', 'Total Absents', 'School days', 'Absent Percentage']) data.append([students, absents, days, percentage]) report = XlReport(file_name="attendance_report") report.add_sheet(data, header_row=titles, title="Attendance Report") return report.as_download() return render_to_response( 'attendance/attendance_report.html', {'form':form, 'daily_form': daily_form, 'lookup_form': lookup_form}, RequestContext(request, {}),)
def discipline_report_view(request): form = DisciplineStudentStatistics() merit_form = MeritForm() if request.method == 'POST': if 'merit' in request.POST: merit_form = MeritForm(request.POST) if merit_form.is_valid(): from ecwsp.sis.report import pod_report_generic data = {} l1 = merit_form.cleaned_data['level_one'] l2 = merit_form.cleaned_data['level_two'] l3 = merit_form.cleaned_data['level_three'] l4 = merit_form.cleaned_data['level_four'] start_date = merit_form.cleaned_data['start_date'] end_date = merit_form.cleaned_data['end_date'] students = Student.objects.filter(inactive=False) if merit_form.cleaned_data['sort_by'] == 'year': students = students.order_by('year') elif merit_form.cleaned_data['sort_by'] == 'cohort': students = students.order_by('cache_cohort') disciplines = StudentDiscipline.objects.filter(date__range=(start_date, end_date)).values('students').annotate(Count('pk')) for student in students: disc = 0 for discipline in disciplines: if discipline['students'] == student.id: disc = discipline['pk__count'] break student.disc_count = disc if student.disc_count <= l1: student.merit_level = 1 elif student.disc_count <= l2: student.merit_level = 2 elif student.disc_count <= l3: student.merit_level = 3 elif student.disc_count <= l4: student.merit_level = 4 data['students'] = students template = Template.objects.get_or_create(name="Merit Level Handout")[0] template = template.get_template_path(request) format_type = UserPreference if template: format_type = UserPreference.objects.get_or_create(user=request.user)[0].get_format() return pod_report_generic(template, data, "Merit Handouts", format=format_type) else: form = DisciplineStudentStatistics(request.POST) if form.is_valid(): data = [] start, end = form.get_dates() if 'student' in request.POST: students = Student.objects.all() school_year = SchoolYear.objects.get(active_year=True) if form.cleaned_data['date_begin']: if SchoolYear.objects.filter(end_date__gte=form.cleaned_data['date_begin']): school_year = SchoolYear.objects.filter(end_date__gte=form.cleaned_data['date_begin']).order_by('start_date')[0] # students belonging to this school year students = students.filter(course__marking_period__school_year__exact=school_year).distinct() if not form.cleaned_data['include_deleted'] : students = students.exclude(inactive=True) if form.cleaned_data['order_by'] == "Year": students = students.order_by('year') subtitles = ["Student",] titles = ["","Infractions",] for infr in Infraction.objects.all(): titles.append("") titles.pop() titles.append("Actions") for infr in Infraction.objects.all(): subtitles.append(unicode(infr)) for action in DisciplineAction.objects.all(): subtitles.append(unicode(action)) titles.append("") titles.pop() data.append(subtitles) pref = UserPreference.objects.get_or_create(user=request.user)[0] for student in students: disciplines = student.studentdiscipline_set.all() disciplines = disciplines.filter(date__range=(start, end)) stats = [unicode(student),] add = True for infr in Infraction.objects.all(): number = disciplines.filter(infraction=infr, students=student).count() stats.append(number) # check for filter if form.cleaned_data['infraction'] == infr: infraction_discipline = disciplines.filter(infraction=form.cleaned_data['infraction']) if number < form.cleaned_data['minimum_infraction']: add = False for action in DisciplineAction.objects.all(): actions = disciplines.filter(disciplineactioninstance__action=action, students=student).count() stats.append(actions) # check for filter if form.cleaned_data['action'] == action: if actions < form.cleaned_data['minimum_action']: add = False pref.get_additional_student_fields(stats, student, students, titles) if add: data.append(stats) report = XlReport(file_name="disc_stats") report.add_sheet(data, header_row=titles, title="Discipline Stats", heading="Discipline Stats") # By Teacher data = [] titles = ['teacher'] for action in DisciplineAction.objects.all(): titles.append(action) teachers = Faculty.objects.filter(studentdiscipline__isnull=False).distinct() disciplines = StudentDiscipline.objects.filter(date__range=(start, end)) for teacher in teachers: row = [teacher] for action in DisciplineAction.objects.all(): row.append(disciplines.filter(teacher=teacher, action=action).count()) data.append(row) report.addSheet(data, titles=titles, heading="By Teachers") return report.as_download() elif 'aggr' in request.POST: disciplines = StudentDiscipline.objects.filter(date__range=(start, end)) if form.cleaned_data['this_year']: school_start = SchoolYear.objects.get(active_year=True).start_date school_end = SchoolYear.objects.get(active_year=True).end_date disciplines = disciplines.filter(date__range=(school_start, school_end)) elif not form.cleaned_data['this_year'] and not form.cleaned_data['all_years']: disciplines = disciplines.filter(date__range=(form.cleaned_data['date_begin'], form.cleaned_data['date_end'])) stats = [] titles = [] for infr in Infraction.objects.all(): titles.append(infr) number = disciplines.filter(infraction=infr).count() stats.append(number) for action in DisciplineAction.objects.all(): titles.append(action) number = 0 for a in DisciplineActionInstance.objects.filter(action=action): number += a.quantity stats.append(number) data.append(stats) report = XlReport(file_name="disc_stats") report.add_sheet(data, header_row=titles, title="Discipline Stats", heading="Discipline Stats") return report.as_download() else: return render_to_response('discipline/disc_report.html', {'request': request, 'form': form}, RequestContext(request, {}),) return render_to_response('discipline/disc_report.html', {'request': request, 'form': form, 'merit_form':merit_form,}, RequestContext(request, {}),)
def gradebook_export(request, course_id): gradebook_data = gradebook(request, course_id, for_export=True) if type(gradebook_data) is not dict: # something we can't use, like a 404 or a redirect return gradebook_data from ecwsp.sis.xl_report import XlReport report = XlReport(file_name=slugify(gradebook_data["course"].fullname)) rows = [] item_attributes = ( "category", "name", "marking_period", "assignment_type", "benchmark", "date", "points_possible", "description", ) demonstration_attributes = ("name",) # explain all the header rows in the first column for attribute in item_attributes: rows.append([Item._meta.get_field(attribute).verbose_name.title()]) for attribute in demonstration_attributes: rows.append([u"Demonstration " + Demonstration._meta.get_field(attribute).verbose_name.title()]) # then list all the students in the first column for student in gradebook_data["students"]: rows.append([student]) # fill in the column headers, with column one per item/demonstration for item in gradebook_data["items"]: if item.demonstration_set.count(): for dem in item.demonstration_set.all(): row_counter = 0 for attribute in item_attributes: rows[row_counter].append(getattr(item, attribute)) row_counter += 1 for attribute in demonstration_attributes: rows[row_counter].append(getattr(dem, attribute)) row_counter += 1 else: row_counter = 0 for attribute in item_attributes: rows[row_counter].append(getattr(item, attribute)) row_counter += 1 for attribute in demonstration_attributes: rows[row_counter].append("---------") row_counter += 1 # add one-off label to the bottom header row of the last column rows[row_counter - 1].append("Course Average") # actually write out the students' grades for student in gradebook_data["students"]: for mark in student.marks: rows[row_counter].append(mark.get_grade()) rows[row_counter].append(student.average) row_counter += 1 report.add_sheet(rows, title=gradebook_data["course"].fullname) sheet = report.workbook.get_active_sheet() for row_number in range(0, len(item_attributes) + len(demonstration_attributes)): sheet.cell(row=row_number, column=0).style.font.bold = True return report.as_download()
def date_based_gpa_report(request): input = request.POST.copy() input['template'] = 1 # Validation hack form = StudentGradeReportWriterForm(input, request.FILES) if form.is_valid(): data = form.cleaned_data try: students = form.get_students(data) except: students = Student.objects.filter( inactive=False).order_by('-year__id') titles = ["Student", "9th", "10th", "11th", "12th", "Current"] data = [] current_year = SchoolYear.objects.get(active_year=True) two_years_ago = (current_year.end_date + timedelta(weeks=-(2 * 52))).year three_years_ago = (current_year.end_date + timedelta(weeks=-(3 * 52))).year four_years_ago = (current_year.end_date + timedelta(weeks=-(4 * 52))).year for student in students: row = [] gpa = [None, None, None, None, None] count = 0 #years is years that student has courses/grades years = SchoolYear.objects.filter( markingperiod__show_reports=True, start_date__lt=date.today(), markingperiod__course__courseenrollment__user=student).exclude( omityeargpa__student=student).distinct().order_by( 'start_date') #if student has courses from any year and is given a grade level (freshman,sophomore, etc.), #it checks to see if the student's been at cristorey every year or if they transferred in and when current = 0 try: if student.year.id == 12: current = 3 if years[0].start_date.year > two_years_ago: gpa[0] = "N/A" gpa[1] = "N/A" gpa[2] = "N/A" count = 3 elif years[0].start_date.year > three_years_ago: gpa[0] = "N/A" gpa[1] = "N/A" count = 2 elif years[0].start_date.year > four_years_ago: gpa[0] = "N/A" count = 1 elif student.year.id == 11: current = 2 if years[0].start_date.year > two_years_ago: gpa[1] = "N/A" gpa[0] = "N/A" count = 2 elif years[0].start_date.year > three_years_ago: gpa[0] = "N/A" count = 1 elif student.year.id == 10: current = 1 if two_years_ago: gpa[0] = "N/A" count = 1 elif student.year.id == 9: current = 0 except: pass for year in years: #cumulative gpa per year. Adds one day because it was acting weird and not giving me GPA for first year gpa[count] = student.calculate_gpa(year.end_date + timedelta(days=1)) count += 1 #if calculate_gpa does not return a value, it is set to "N/A" if not gpa[0]: gpa[0] = "N/A" if not gpa[1]: gpa[1] = "N/A" if not gpa[2]: gpa[2] = "N/A" if not gpa[3]: gpa[3] = "N/A" gpa[4] = gpa[current] row = [student, gpa[0], gpa[1], gpa[2], gpa[3], gpa[4]] data.append(row) report = XlReport(file_name="gpas_by_year") report.add_sheet(data, header_row=titles, title="GPAs") return report.as_download()
def gradebook_export(request, course_section_id): gradebook_data = gradebook(request, course_section_id, for_export=True) if type(gradebook_data) is not dict: # something we can't use, like a 404 or a redirect return gradebook_data from ecwsp.sis.xl_report import XlReport report = XlReport(file_name=slugify(gradebook_data['course_section'].name)) rows = [] item_attributes = ( 'category', 'name', 'marking_period', 'assignment_type', 'benchmark', 'date', 'points_possible', 'description', ) demonstration_attributes = ('name', ) row_counter = 0 # explain all the header rows in the first column for attribute in item_attributes: rows.append([Item._meta.get_field(attribute).verbose_name.title()]) row_counter += 1 for attribute in demonstration_attributes: rows.append([ u'Demonstration ' + Demonstration._meta.get_field(attribute).verbose_name.title() ]) row_counter += 1 # then list all the students in the first column for student in gradebook_data['students']: rows.append([student]) # fill in the column headers, with column one per item/demonstration for item in gradebook_data['items']: if item.demonstration_set.count(): for dem in item.demonstration_set.all(): row_counter = 0 for attribute in item_attributes: rows[row_counter].append(getattr(item, attribute)) row_counter += 1 for attribute in demonstration_attributes: rows[row_counter].append(getattr(dem, attribute)) row_counter += 1 else: row_counter = 0 for attribute in item_attributes: rows[row_counter].append(getattr(item, attribute)) row_counter += 1 for attribute in demonstration_attributes: rows[row_counter].append('---------') row_counter += 1 # maybe attributes will be user-configurable in the future? if not row_counter: rows.append([]) row_counter = 1 # add one-off label to the bottom header row of the last column rows[row_counter - 1].append('Course Section Average') # save coordinates for formatting later course_section_average_row = row_counter - 1 course_section_average_column = len(rows[row_counter - 1]) - 1 # actually write out the students' grades for student in gradebook_data['students']: for mark in student.marks: rows[row_counter].append(mark.get_grade()) rows[row_counter].append(student.average) row_counter += 1 report.add_sheet(rows, title=gradebook_data['course_section'].name) sheet = report.workbook.get_active_sheet() if report.old_openpyxl: for row_number in range( 0, len(item_attributes) + len(demonstration_attributes)): sheet.cell(row=row_number, column=0).style.font.bold = True sheet.cell(row=course_section_average_row, column=course_section_average_column).style.font.bold = True else: for row_number in range( 1, len(item_attributes) + len(demonstration_attributes)): cell = sheet.cell(row=row_number, column=1) cell.style = cell.style.copy(font=cell.style.font.copy(bold=True)) cell = sheet.cell(row=course_section_average_row, column=course_section_average_column) cell.style = cell.style.copy(font=cell.style.font.copy(bold=True)) return report.as_download()