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()
