Ejemplo n.º 1
0
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()
Ejemplo n.º 2
0
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()
Ejemplo n.º 3
0
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()
Ejemplo n.º 4
0
 def import_just_standard_test(self, test=None):
     inserted = 0
     msg = ""
     try:
         sheet = self.book.sheet_by_index(0) # Just get the first one
         inserted = self.import_standard_test(sheet, test)
         msg += "%s standard tests inserted <br/>" % (inserted)
     except: pass
     
     if msg == "":
         msg = "No files found. Check if sheets are named correctly. "
     
     msg += unicode(self.errors) + " error(s). "
     
     filename = 'import_error.xls'
     if len(self.error_data):
         report = XlReport()
         save = False
         for key, error_page in self.error_data.items():
             if len(error_page):
                 save = True
                 report.add_sheet(error_page, header_row=self.error_titles[key][0], title=key)
         if save:
             report.save(filename)
         else:
             filename = None
     return msg, filename
Ejemplo n.º 5
0
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()
Ejemplo n.º 6
0
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()
Ejemplo n.º 7
0
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()  
Ejemplo n.º 8
0
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()
Ejemplo n.º 9
0
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()
Ejemplo n.º 10
0
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()  
Ejemplo n.º 11
0
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()
Ejemplo n.º 12
0
 def magic_import_everything(self):
     """Import a workbook using sheet names to determine what to import"""
     self.make_log_entry()
     inserted = 0
     updated = 0
     msg = ""
     
     sheet = self.get_sheet_by_case_insensitive_name("standard test")
     if sheet:
         inserted = self.import_standard_test(sheet)
         msg += "%s standard tests inserted <br/>" % (inserted)
     sheet = self.get_sheet_by_case_insensitive_name("alumni note")
     if sheet:
         inserted, updated = self.import_alumni_note(sheet)
         msg += "%s alumni note entries inserted,<br/>" % (inserted,)
         
     sheet = self.get_sheet_by_case_insensitive_name("alumni email")
     if sheet:
         inserted, updated = self.import_alumni_email(sheet)
         msg += "%s alumni email inserted,<br/>" % (inserted,)
     sheet = self.get_sheet_by_case_insensitive_name("alumni number")
     if sheet:
         inserted, updated = self.import_alumni_number(sheet)
         msg += "%s alumni numbers inserted,<br/>" % (inserted,)
         
     sheet = self.get_sheet_by_case_insensitive_name("college enrollment")
     if sheet:
         inserted, updated = self.import_college_enrollment(sheet)
         msg += "%s college enrollments inserted, %s college enrollments updated. <br/>" % (inserted, updated)
     
     if msg == "":
         msg = "No files found. Check if sheets are named correctly. "
     
     msg += unicode(self.errors) + " error(s). "
     
     filename = 'import_error.xlsx'
     if len(self.error_data):
         self.log.errors = True
         self.log.save()
         report = XlReport()
         save = False
         for key, error_page in self.error_data.items():
             if len(error_page):
                 save = True
                 report.add_sheet(error_page, header_row=self.error_titles[key][0], title=key)
         if save:
             report.save(filename)
         else:
             filename = None
     return msg, filename
Ejemplo n.º 13
0
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()
Ejemplo n.º 14
0
    def import_just_standard_test(self, test=None):
        inserted = 0
        msg = ""
        try:
            sheet = self.book.sheet_by_index(0)  # Just get the first one
            inserted = self.import_standard_test(sheet, test)
            msg += "%s standard tests inserted <br/>" % (inserted)
        except:
            pass

        if msg == "":
            msg = "No files found. Check if sheets are named correctly. "

        msg += unicode(self.errors) + " error(s). "

        filename = 'import_error.xls'
        if len(self.error_data):
            report = XlReport()
            save = False
            for key, error_page in self.error_data.items():
                if len(error_page):
                    save = True
                    report.add_sheet(error_page,
                                     header_row=self.error_titles[key][0],
                                     title=key)
            if save:
                report.save(filename)
            else:
                filename = None
        return msg, filename
Ejemplo n.º 15
0
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()
Ejemplo n.º 16
0
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()
Ejemplo n.º 17
0
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()
Ejemplo n.º 18
0
    def magic_import_everything(self):
        """Import a workbook using sheet names to determine what to import"""
        self.make_log_entry()
        inserted = 0
        updated = 0
        msg = ""

        sheet = self.get_sheet_by_case_insensitive_name("standard test")
        if sheet:
            inserted = self.import_standard_test(sheet)
            msg += "%s standard tests inserted <br/>" % (inserted)
        sheet = self.get_sheet_by_case_insensitive_name("alumni note")
        if sheet:
            inserted, updated = self.import_alumni_note(sheet)
            msg += "%s alumni note entries inserted,<br/>" % (inserted, )

        sheet = self.get_sheet_by_case_insensitive_name("alumni email")
        if sheet:
            inserted, updated = self.import_alumni_email(sheet)
            msg += "%s alumni email inserted,<br/>" % (inserted, )
        sheet = self.get_sheet_by_case_insensitive_name("alumni number")
        if sheet:
            inserted, updated = self.import_alumni_number(sheet)
            msg += "%s alumni numbers inserted,<br/>" % (inserted, )

        sheet = self.get_sheet_by_case_insensitive_name("college enrollment")
        if sheet:
            inserted, updated = self.import_college_enrollment(sheet)
            msg += "%s college enrollments inserted, %s college enrollments updated. <br/>" % (
                inserted, updated)

        if msg == "":
            msg = "No files found. Check if sheets are named correctly. "

        msg += unicode(self.errors) + " error(s). "

        filename = 'import_error.xlsx'
        if len(self.error_data):
            self.log.errors = True
            self.log.save()
            report = XlReport()
            save = False
            for key, error_page in self.error_data.items():
                if len(error_page):
                    save = True
                    report.add_sheet(error_page,
                                     header_row=self.error_titles[key][0],
                                     title=key)
            if save:
                report.save(filename)
            else:
                filename = None
        return msg, filename
Ejemplo n.º 19
0
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()
Ejemplo n.º 20
0
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()
Ejemplo n.º 21
0
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()
Ejemplo n.º 22
0
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()  
Ejemplo n.º 23
0
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()
Ejemplo n.º 24
0
    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()
Ejemplo n.º 25
0
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()
Ejemplo n.º 26
0
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()
Ejemplo n.º 27
0
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()
Ejemplo n.º 28
0
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()
Ejemplo n.º 29
0
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()
Ejemplo n.º 30
0
 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()
Ejemplo n.º 31
0
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, {}),)
Ejemplo n.º 32
0
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()
Ejemplo n.º 33
0
    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()
Ejemplo n.º 34
0
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.template_report import TemplateReport
                report = TemplateReport(request.user)
                report.filename = 'Merit Handouts'
                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
                report.data['students'] = students
                template = Template.objects.get_or_create(
                    name="Merit Level Handout")[0]
                template = template.get_template_path(request)
                if template:
                    return report.pod_save(template)
        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, {}),
    )
Ejemplo n.º 35
0
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, {}),)
Ejemplo n.º 36
0
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()
Ejemplo n.º 37
0
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()
Ejemplo n.º 38
0
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, {}),)
Ejemplo n.º 39
0
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, {}),)
Ejemplo n.º 40
0
    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()
Ejemplo n.º 41
0
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, {}),
    )