def route_attendance(request): data = [] titles = ['Student First', 'Last', 'Route', 'Van', 'Company', 'Notes'] fileName = "route_attendance.xls" for ts in timesheets: data.append([]) report = xlsReport(data, titles, fileName, heading="Route_Attendance") return report.finish()
def route_attendance(request): data = [] titles = ['Student First', 'Last', 'Route','Van','Company','Notes'] fileName = "route_attendance.xls" for ts in timesheets: data.append([]) report = xlsReport(data, titles, fileName, heading="Route_Attendance") return report.finish()
def fte_by_day(request): fileName = "report_fteByDay.xls" 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 = xlsReport(names, titles, fileName, heading="FTE by Day of Week") report.addSheet(student_company_day_report(), heading="Detail") return report.finish()
def fte_by_day(request): fileName = "report_fteByDay.xls" 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 = xlsReport(names, titles, fileName, heading="FTE by Day of Week") report.addSheet(student_company_day_report(), heading="Detail") return report.finish()
def student_incomplete_courses(request): if 'inverse' in request.GET: inverse = True else: inverse = False from ecwsp.sis.xlsReport import xlsReport 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(inactive=False).order_by('year', 'lname', 'fname') 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') 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)]) return xlsReport(data, titles, 'report.xls', heading='Sheet1', heading_top=False, auto_width=True).finish()
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"] fileName = "Billing_Report.xls" 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]) 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 = xlsReport(data, titles, fileName, heading="Company Billing") return report.finish()
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" ] fileName = "Billing_Report.xls" 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 ]) 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 = xlsReport(data, titles, fileName, heading="Company Billing") return report.finish()
def attendance_report(request): from ecwsp.sis.xlsReport import xlsReport 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'], daily_form.cleaned_data['include_private_notes'], type=type, request=request, ) 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( 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'], type=type) 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(inactive=False) 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 pref.get_additional_student_fields(row, student, students, titles) if add: data.append(row) report = xlsReport(data, titles, "attendance_report.xls", 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(inactive=False) 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) elif 'attendance_daily_stat' in request.POST: form = AttendanceReportForm(request.POST) if form.is_valid(): days = AttendanceDailyStat.objects.filter(date__range=form.get_dates()) data = [] titles = ['Date', 'Present', 'Absent', 'Absent Percentage'] row = 3 for day in days: # Formula C3/(B3+C3) percentage = xlwt.Formula("C" + str(row) + "/(B" +str(row) + "+C" + str(row) + ')') data.append([day.date, day.present, day.absent, percentage]) row += 1 report = xlsReport( data, titles, "attendance_daily_stats_report.xls", heading="Attendance Daily Stats") 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(inactive=False).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 = xlwt.Formula("1-(B6/(A6*C6))") data.append(['Students', 'Total Absents', 'School days', 'Absent Percentage']) data.append([students, absents, days, percentage]) report = xlsReport(data, titles, "attendance_report.xls", heading="Attendance Report") return report.finish() return render_to_response( 'attendance/attendance_report.html', {'form':form, 'daily_form': daily_form, 'lookup_form': lookup_form}, RequestContext(request, {}),)
def am_route_attendance(request): # TO DO Implement workday selection!!!! data = [] titles = [ "Company", "Address", "City", "State", "Zip", "Contact First", "Contact Last", "Phone", "Grade", 'Workday', 'Gender', 'Student First', 'Student Last', 'Cell', 'Earliest', 'Latest', 'Ideal', 'Schedule', 'Notes', datetime.date.today() ] report = None if 'am_route_attendance' in request.POST: fileName = "AM_Routes.xls" else: fileName = "PM_Routes.xls" if not StudentWorkerRoute.objects.all(): messages.error( request, 'You must create at least one route before running this report!') return HttpResponseRedirect(request.META.get('HTTP_REFERER', '/')) for route in StudentWorkerRoute.objects.all(): data = [] if 'am_route_attendance' in request.POST: students = StudentWorker.objects.filter(am_route=route, inactive=False) else: students = StudentWorker.objects.filter(pm_route=route, inactive=False) 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 = xlsReport(data, titles, fileName, heading=unicode(route)) else: report.addSheet(data, titles, heading=unicode(route)) return report.finish()
def attendance_report(request): from ecwsp.sis.xlsReport import xlsReport 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'], daily_form.cleaned_data['include_private_notes'], type=type, request=request, ) 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( 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'], type=type) 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(inactive=False) 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 pref.get_additional_student_fields( row, student, students, titles) if add: data.append(row) report = xlsReport(data, titles, "attendance_report.xls", 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(inactive=False) 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) elif 'attendance_daily_stat' in request.POST: form = AttendanceReportForm(request.POST) if form.is_valid(): days = AttendanceDailyStat.objects.filter( date__range=form.get_dates()) data = [] titles = [ 'Date', 'Present', 'Absent', 'Absent Percentage' ] row = 3 for day in days: # Formula C3/(B3+C3) percentage = xlwt.Formula("C" + str(row) + "/(B" + str(row) + "+C" + str(row) + ')') data.append([ day.date, day.present, day.absent, percentage ]) row += 1 report = xlsReport(data, titles, "attendance_daily_stats_report.xls", heading="Attendance Daily Stats") 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(inactive=False).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 = xlwt.Formula("1-(B6/(A6*C6))") data.append([ 'Students', 'Total Absents', 'School days', 'Absent Percentage' ]) data.append([students, absents, days, percentage]) report = xlsReport(data, titles, "attendance_report.xls", heading="Attendance Report") return report.finish() return render_to_response( 'attendance/attendance_report.html', { 'form': form, 'daily_form': daily_form, 'lookup_form': lookup_form }, RequestContext(request, {}), )
def count_items_by_category_across_courses(year_category_names, current_marking_period_category_names, item_criteria, category_heading_format, percentage_threshold, course_threshold, inverse=False): from ecwsp.sis.xlsReport import xlsReport 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') 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] 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(inactive=False).order_by('year', 'lname', 'fname'): try: work_day = StudentWorker.objects.get(username=student.username).day except StudentWorker.DoesNotExist: work_day = None matching_courses = [] for course in student.course_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=course, 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_match = False matching_course_detail = [course.fullname] # 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_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_detail.append('{}/{} ({}%)'.format(matching_items_in_category, total_items_in_category, matching_percentage_in_category)) if matching_percentage_in_category >= percentage_threshold: course_match = True if course_match: matching_courses.append(matching_course_detail) if len(matching_courses) >= course_threshold: if not inverse: for course in matching_courses: row = [student.lname, student.fname, student.year, work_day] row.extend(course) data.append(row) elif inverse: row = [student.lname, student.fname, student.year, work_day] data.append(row) return xlsReport(data, titles, 'report.xls', heading='Sheet1', heading_top=False, auto_width=True).finish()
def count_items_by_category_across_courses( year_category_names, current_marking_period_category_names, item_criteria, category_heading_format, percentage_threshold, course_threshold, inverse=False): from ecwsp.sis.xlsReport import xlsReport 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') 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] 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(inactive=False).order_by( 'year', 'lname', 'fname'): try: work_day = StudentWorker.objects.get(username=student.username).day except StudentWorker.DoesNotExist: work_day = None matching_courses = [] for course in student.course_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=course, 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_match = False matching_course_detail = [course.fullname] # 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_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_detail.append('{}/{} ({}%)'.format( matching_items_in_category, total_items_in_category, matching_percentage_in_category)) if matching_percentage_in_category >= percentage_threshold: course_match = True if course_match: matching_courses.append(matching_course_detail) if len(matching_courses) >= course_threshold: if not inverse: for course in matching_courses: row = [ student.lname, student.fname, student.year, work_day ] row.extend(course) data.append(row) elif inverse: row = [student.lname, student.fname, student.year, work_day] data.append(row) return xlsReport(data, titles, 'report.xls', heading='Sheet1', heading_top=False, auto_width=True).finish()
def student_incomplete_courses(request): if 'inverse' in request.GET: inverse = True else: inverse = False from ecwsp.sis.xlsReport import xlsReport 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(inactive=False).order_by( 'year', 'lname', 'fname') 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') 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) ]) return xlsReport(data, titles, 'report.xls', heading='Sheet1', heading_top=False, auto_width=True).finish()
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, inactive = False) else: students = StudentWorker.objects.filter(pm_route=route, inactive = False) 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 = xlsReport(data, titles, fileName, heading=unicode(route)) else: report.addSheet(data, titles, heading=unicode(route)) return report.finish()