def todaylesson(request):
if request.is_ajax():
return {}
if request.path.startswith("/checkin/ck"):
return {}
if hasattr(request, 'user'):
nowlessontime = getnowlessontime()
if request.user.isteacher:
data = Lesson.objects.filter(
course__in=request.user.teacher_set.get().course_set.all(),
term=nowlessontime['term'],
day=nowlessontime['day'],
week=nowlessontime['week']).select_related('course').order_by(
'time').all()
else:
studentcourse = Studentcourse.objects.filter(
student__user=request.user).values_list('course', flat=True)
lesson = Lesson.objects.filter(course__in=studentcourse,
term=nowlessontime['term'],
day=nowlessontime['day'],
week=nowlessontime['week'])
data = lesson.select_related('course').order_by('time').all()
return {'todaylesson': data}
else:
return {}
def home(request):
if request.user.isteacher:
teacher = Teacher.objects.get(user=request.user)
termcourse = teacher.course_set.filter(
schoolterm=getCurrentSchoolYearTerm()['term'])
uncommithomework = None
unscorehomework = Homeworkcommit.objects.filter(
coursehomework__course__in=termcourse, score=None).count()
else:
student = Student.objects.get(user=request.user)
termcourse = Studentcourse.objects.filter(
student=student,
course__schoolterm=getCurrentSchoolYearTerm()['term']).values_list(
'course', flat=True)
termcourse = Course.objects.filter(id__in=termcourse)
commithomework = Homeworkcommit.objects.filter(
student=student).values_list('coursehomework', flat=True)
uncommithomework = Coursehomework.objects.filter(
course__in=termcourse).exclude(
id__in=commithomework).select_related('course').all()
unscorehomework = None
nowlessontime = getnowlessontime()
lesson = Lesson.objects.filter(course__in=termcourse,
week=nowlessontime['week']).order_by(
'week', 'day', 'time')
return render(
request, 'home.html', {
'term':
getCurrentSchoolYearTerm(),
'uncommithomework':
uncommithomework,
'unscorehomework':
unscorehomework,
'termcourse':
termcourse.all(),
'weeklesson':
lesson.select_related('course').select_related('classroom').all(),
})
def auto_stop_lesson(before):
nowlessontime = getnowlessontime()
week = nowlessontime['week']
day = nowlessontime['day']
time = nowlessontime['time']
term = nowlessontime['term']
qs = {
'today': Q(week=week, day=day, time__lt=time + 1 - F('length')),
# 上课时间 + 课程长度 - 1 < 现在时间 => 上课时间 < 现在时间 + 1 - 课程长度
'week': Q(week=week, day__lt=day),
'term': Q(week__lt=week)
}
qbefore = {
'today': qs['today'], # stop lessons in today
'week': qs['today'] | qs['week'], # stop lessons in this week
'all':
qs['today'] | qs['term'] | qs['week'] # stop lesson in this term
}
unstoplessons = Lesson.objects.filter(term=term).filter(qbefore[before])
count = unstoplessons.filter(status=LESSON_STATUS_NOW).update(
status=LESSON_STATUS_END)
if count:
logger.info('autostop %d lessons this %s' % (count, before))
return count
def lesson_data(request):
now_term = request.GET.get('term', None)
now_week = request.GET.get('week', None)
now_day = request.GET.get('day', None)
if not now_day:
now_lesson_time = getnowlessontime()
now_week = now_lesson_time['week']
now_day = now_lesson_time['day']
now_term = now_lesson_time['term']
today_lessons = Lesson.objects.filter(week=now_week,
day=now_day,
term=now_term)
checkindata = Checkin.objects.filter(
lesson=OuterRef('id')).values('lesson_id').annotate(
count=Count('*')).values('count')
today_lessons = today_lessons.select_related('course').select_related(
'classroom').select_related('course__department')
today_lessons = today_lessons.annotate(
actually=Subquery(checkindata.filter(status__in=[
CHECKIN_STATUS_EARLY, CHECKIN_STATUS_SUCCESS, CHECKIN_STATUS_LATE,
CHECKIN_STATUS_LATEEARLY
]),
output_field=IntegerField()),
late=Subquery(checkindata.filter(status=CHECKIN_STATUS_LATE),
output_field=IntegerField()),
early=Subquery(checkindata.filter(status=CHECKIN_STATUS_EARLY),
output_field=IntegerField()),
late_early=Subquery(
checkindata.filter(status=CHECKIN_STATUS_LATEEARLY),
output_field=IntegerField()),
should=Subquery(checkindata, output_field=IntegerField()),
ask=Subquery(checkindata.filter(status__gte=10),
output_field=IntegerField()),
)
rows = []
for p in today_lessons.all():
ld = {
'id':
p.id,
'serialnumber':
p.course.serialnumber,
'title':
p.course.title,
'time':
"%d-%d" % (p.time, p.time + p.length - 1),
'location':
p.classroom.location,
'teacher':
",".join(p.course.teachers.values_list('name', flat=True)),
'teach_class':
p.course.teachclass.name if p.course.teachclass else None,
'department':
p.course.department.name if p.course.department else None,
'should':
p.should if p.isnow() or p.isend() else p.shouldnumber,
'actually':
p.actually,
'late':
p.late,
'early':
p.early,
'late_early':
p.late_early,
'asknumber':
p.asknumber if p.checkincount else p.ask,
'attendance':
"%.2f%%" %
(p.actually / p.shouldnumber * 100) if p.actually else None,
'checkincount':
p.checkincount,
'status':
p.status,
'starttime':
datetime.datetime.strftime(p.starttime, '%Y-%m-%d %I:%M %p')
if p.starttime else None,
'endtime':
datetime.datetime.strftime(p.endtime, '%Y-%m-%d %I:%M %p')
if p.endtime else None,
}
rows.append(ld)
return HttpResponse(json.dumps(rows), content_type="application/json")
def today_data(request):
data = {}
now_term = request.GET.get('term', None)
now_week = request.GET.get('week', None)
now_day = request.GET.get('day', None)
if not now_day:
now_lesson_time = getnowlessontime()
now_week = now_lesson_time['week']
now_day = now_lesson_time['day']
now_term = now_lesson_time['term']
else:
now_lesson_time = {'week': now_week, 'day': now_day, 'term': now_term}
class_time_count = Classtime.objects.count()
class_times = [(time, time + 1) for time in range(1, class_time_count, 2)]
class_times_str = ['%s-%s' % (time[0], time[1]) for time in class_times]
data['class_times'] = class_times_str
data.update(**now_lesson_time)
today_lessons = Lesson.objects.filter(week=now_week,
day=now_day,
term=now_term)
data['today_lessons'] = today_lessons.count()
today_start_lessons = today_lessons.filter(status__in=[
LESSON_STATUS_NOW, LESSON_STATUS_CHECKIN, LESSON_STATUS_CHECKIN_ADD,
LESSON_STATUS_CHECKIN_AGAIN, LESSON_STATUS_END
])
data['today_start_lessons'] = today_start_lessons.count()
today_start_checkin_lessons = today_start_lessons.filter(
checkincount__gt=0)
data['today_start_checkin_lessons'] = today_start_checkin_lessons.count()
# course_status
course_status_data = Checkin.objects.filter(
lesson__in=today_start_lessons).aggregate(
normal=Count(Case(When(status=CHECKIN_STATUS_NORMAL, then=1))),
private_ask=Count(
Case(When(status=CHECKIN_STATUS_PRIVATE_ASK, then=1))),
success=Count(Case(When(status=CHECKIN_STATUS_SUCCESS, then=1))),
early=Count(Case(When(status=CHECKIN_STATUS_EARLY, then=1))),
late=Count(Case(When(status=CHECKIN_STATUS_LATE, then=1))),
lateearly=Count(Case(When(status=CHECKIN_STATUS_LATEEARLY,
then=1))),
public_ask=Count(
Case(When(status=CHECKIN_STATUS_PUBLIC_ASK, then=1))),
sick_ask=Count(Case(When(status=CHECKIN_STATUS_SICK_ASK, then=1))),
)
course_status_data['arrive'] = course_status_data['success'] + course_status_data['early'] + \
course_status_data['late'] + course_status_data['lateearly']
course_status_data['ask'] = course_status_data['private_ask'] + course_status_data['public_ask'] + \
course_status_data['sick_ask']
data['course_status_data'] = course_status_data
# student_status
student_all_count = Checkin.objects.filter(
lesson__in=today_start_lessons).distinct('student').count()
student_all_arrive = Checkin.objects.filter(lesson__in=today_start_lessons). \
annotate(all=Count('status'), num=Count(Case(When(status=CHECKIN_STATUS_SUCCESS, then=1)))).filter(num=F('all'))
student_all_arrive.query.group_by = 'student'
student_all_arrive = student_all_arrive.count()
student_all_normal = Checkin.objects.filter(lesson__in=today_start_lessons). \
annotate(all=Count('status'), num=Count(Case(When(status=CHECKIN_STATUS_NORMAL, then=1)))).filter(num=F('all'))
student_all_normal.query.group_by = 'student'
student_all_normal = student_all_normal.count()
student_public_ask = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
status=CHECKIN_STATUS_PUBLIC_ASK).distinct('student').count()
student_private_ask = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
status=CHECKIN_STATUS_PRIVATE_ASK).distinct('student').count()
student_sick_ask = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
status=CHECKIN_STATUS_SICK_ASK).distinct('student').count()
student_other = Checkin.objects.filter(
lesson__in=today_start_lessons).exclude(status__in=[
CHECKIN_STATUS_SUCCESS, CHECKIN_STATUS_NORMAL,
CHECKIN_STATUS_PUBLIC_ASK, CHECKIN_STATUS_PRIVATE_ASK,
CHECKIN_STATUS_SICK_ASK
]).distinct('student').count()
student_late_count = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
Q(status=CHECKIN_STATUS_LATE)
| Q(status=CHECKIN_STATUS_LATEEARLY)).distinct('student').count()
student_early_count = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
Q(status=CHECKIN_STATUS_EARLY)
| Q(status=CHECKIN_STATUS_LATEEARLY)).distinct('student').count()
student_normal_count = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
status=CHECKIN_STATUS_NORMAL).distinct('student').count()
course_late_count = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
Q(status=CHECKIN_STATUS_LATE)
| Q(status=CHECKIN_STATUS_LATEEARLY)).count()
course_early_count = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
Q(status=CHECKIN_STATUS_EARLY)
| Q(status=CHECKIN_STATUS_LATEEARLY)).count()
course_normal_count = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(
status=CHECKIN_STATUS_EARLY).count()
data.update({
'student_all_arrive': student_all_arrive,
'student_all_normal': student_all_normal,
'student_public_ask': student_public_ask,
'student_private_ask': student_private_ask,
'student_sick_ask': student_sick_ask,
'student_other': student_other,
'student_late_count': student_late_count,
'student_early_count': student_early_count,
'course_late_count': course_late_count,
'course_early_count': course_early_count,
'course_normal_count': course_normal_count,
'student_all_count': student_all_count,
'student_normal_count': student_normal_count
})
# Department status
department_list = Department.objects.filter(
pk__in=Student.objects.distinct('department').values_list(
'department'))
data['department_list'] = [d.name for d in department_list]
department_normal_value = []
department_ask_value = []
department_success_value = []
department_late_value = []
department_early_value = []
department_lateearly_value = []
for d in department_list:
res = Checkin.objects.filter(
lesson__in=today_start_lessons,
student__in=d.student_set.all()).aggregate(
normal=Count(Case(When(status=CHECKIN_STATUS_NORMAL, then=1))),
ask=Count(
Case(
When(status__in=[
CHECKIN_STATUS_PRIVATE_ASK,
CHECKIN_STATUS_PUBLIC_ASK, CHECKIN_STATUS_SICK_ASK
],
then=1))),
success=Count(Case(When(status=CHECKIN_STATUS_SUCCESS,
then=1))),
early=Count(Case(When(status=CHECKIN_STATUS_EARLY, then=1))),
late=Count(Case(When(status=CHECKIN_STATUS_LATE, then=1))),
lateearly=Count(
Case(When(status=CHECKIN_STATUS_LATEEARLY, then=1))),
)
department_normal_value.append(res['normal'])
department_ask_value.append(res['ask'])
department_success_value.append(res['success'])
department_late_value.append(res['late'])
department_early_value.append(res['early'])
department_lateearly_value.append(res['lateearly'])
data.update({
'department_normal_value': department_normal_value,
'department_ask_value': department_ask_value,
'department_success_value': department_success_value,
'department_late_value': department_late_value,
'department_early_value': department_early_value,
'department_lateearly_value': department_lateearly_value
})
# Course time status
coursetime_normal_value = []
coursetime_ask_value = []
coursetime_success_value = []
coursetime_late_value = []
coursetime_early_value = []
coursetime_lateearly_value = []
coursetime_arrive_value = []
for cs in class_times:
time_filter = Q(lesson__time__gte=cs[0], lesson__length__gte=cs[1] - F('lesson__time'),
lesson__time__lte=cs[1]) | \
Q(lesson__time__lte=cs[0], lesson__length__gte=cs[1] - F('lesson__time')) | \
Q(lesson__time__lte=cs[0], lesson__length__lte=cs[1] - F('lesson__time'),
lesson__length__gte=cs[0] - F('lesson__time'))
res = Checkin.objects.filter(
lesson__in=today_start_lessons).filter(time_filter).aggregate(
normal=Count(Case(When(status=CHECKIN_STATUS_NORMAL, then=1))),
ask=Count(
Case(
When(status__in=[
CHECKIN_STATUS_PRIVATE_ASK,
CHECKIN_STATUS_PUBLIC_ASK, CHECKIN_STATUS_SICK_ASK
],
then=1))),
success=Count(Case(When(status=CHECKIN_STATUS_SUCCESS,
then=1))),
early=Count(Case(When(status=CHECKIN_STATUS_EARLY, then=1))),
late=Count(Case(When(status=CHECKIN_STATUS_LATE, then=1))),
lateearly=Count(
Case(When(status=CHECKIN_STATUS_LATEEARLY, then=1))),
)
coursetime_normal_value.append(res['normal'])
coursetime_ask_value.append(res['ask'])
coursetime_success_value.append(res['success'])
coursetime_late_value.append(res['late'])
coursetime_early_value.append(res['early'])
coursetime_lateearly_value.append(res['lateearly'])
coursetime_arrive_value.append(res['lateearly'] + res['early'] +
res['late'] + res['success'])
data.update({
'coursetime_normal_value': coursetime_normal_value,
'coursetime_ask_value': coursetime_ask_value,
'coursetime_success_value': coursetime_success_value,
'coursetime_late_value': coursetime_late_value,
'coursetime_early_value': coursetime_early_value,
'coursetime_lateearly_value': coursetime_lateearly_value,
'coursetime_arrive_value': coursetime_arrive_value
})
return HttpResponse(json.dumps(data), content_type="application/json")
def term_data(request):
cursor = connection.cursor()
data = {}
now_lesson_time = getnowlessontime()
now_term = request.GET.get('term', None)
if not now_term:
now_week = now_lesson_time['week']
now_term = now_lesson_time['term']
else:
if now_term == now_lesson_time['term']:
now_week = now_lesson_time['week']
else:
now_lesson_time['term'] = now_term
startdate, enddate = getTermDate(now_term)
time = enddate - startdate
day = time.days
now_week = 0
while day >= 7:
day = day - 7
now_week += 1
now_week += 1
data.update(**now_lesson_time)
term_lessons = Lesson.objects.filter(term=now_term)
data['term_lessons'] = term_lessons.count()
term_start_lessons = term_lessons.filter(status__in=[
LESSON_STATUS_NOW, LESSON_STATUS_CHECKIN, LESSON_STATUS_CHECKIN_ADD,
LESSON_STATUS_CHECKIN_AGAIN, LESSON_STATUS_END
])
data['term_start_lessons'] = term_start_lessons.count()
term_start_checkin_lessons = term_start_lessons.filter(checkincount__gt=0)
data['term_start_checkin_lessons'] = term_start_checkin_lessons.count()
# course_status
course_status_data = Checkin.objects.filter(
lesson__in=term_start_lessons).aggregate(
normal=Count(Case(When(status=CHECKIN_STATUS_NORMAL, then=1))),
private_ask=Count(
Case(When(status=CHECKIN_STATUS_PRIVATE_ASK, then=1))),
success=Count(Case(When(status=CHECKIN_STATUS_SUCCESS, then=1))),
early=Count(Case(When(status=CHECKIN_STATUS_EARLY, then=1))),
late=Count(Case(When(status=CHECKIN_STATUS_LATE, then=1))),
lateearly=Count(Case(When(status=CHECKIN_STATUS_LATEEARLY,
then=1))),
public_ask=Count(
Case(When(status=CHECKIN_STATUS_PUBLIC_ASK, then=1))),
sick_ask=Count(Case(When(status=CHECKIN_STATUS_SICK_ASK, then=1))),
)
course_status_data['arrive'] = course_status_data['success'] + course_status_data['early'] + \
course_status_data['late'] + course_status_data['lateearly']
course_status_data['ask'] = course_status_data['private_ask'] + course_status_data['public_ask'] + \
course_status_data['sick_ask']
data['course_status_data'] = course_status_data
# student_status
student_all_count = Checkin.objects.filter(
lesson__in=term_start_lessons).distinct('student').count()
# can't use because group_by don't work
# student_all_arrive = Checkin.objects.filter(lesson__in=term_start_lessons). \
# annotate(all=Count('status'), num=Count(Case(When(status=CHECKIN_STATUS_SUCCESS, then=1)))).filter(num=F('all'))
# student_all_arrive.query.group_by = 'student'
# student_all_arrive = student_all_arrive.count()
cursor.execute(
'SELECT COUNT(*) FROM (SELECT "Checkin"."studentid" FROM "Checkin" WHERE "Checkin"."lessonid" IN (SELECT U0."id" AS Col1 FROM "Lesson" U0 WHERE (U0."term" = %s AND U0."status" IN (2, 3, 4, 10, 7))) GROUP BY "Checkin"."studentid" HAVING COUNT(CASE WHEN "Checkin"."status" = %s THEN 1 ELSE NULL END) = (COUNT("Checkin"."status"))) subquery;',
[now_term, CHECKIN_STATUS_SUCCESS])
student_all_arrive = cursor.fetchone()[0]
cursor.execute(
'SELECT COUNT(*) FROM (SELECT "Checkin"."studentid" FROM "Checkin" WHERE "Checkin"."lessonid" IN (SELECT U0."id" AS Col1 FROM "Lesson" U0 WHERE (U0."term" = %s AND U0."status" IN (2, 3, 4, 10, 7))) GROUP BY "Checkin"."studentid" HAVING COUNT(CASE WHEN "Checkin"."status" = %s THEN 1 ELSE NULL END) = (COUNT("Checkin"."status"))) subquery;',
[now_term, CHECKIN_STATUS_NORMAL])
student_all_normal = cursor.fetchone()[0]
student_public_ask = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
status=CHECKIN_STATUS_PUBLIC_ASK).distinct('student').count()
student_private_ask = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
status=CHECKIN_STATUS_PRIVATE_ASK).distinct('student').count()
student_sick_ask = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
status=CHECKIN_STATUS_SICK_ASK).distinct('student').count()
student_other = Checkin.objects.filter(
lesson__in=term_start_lessons).exclude(status__in=[
CHECKIN_STATUS_SUCCESS, CHECKIN_STATUS_NORMAL,
CHECKIN_STATUS_PUBLIC_ASK, CHECKIN_STATUS_PRIVATE_ASK,
CHECKIN_STATUS_SICK_ASK
]).distinct('student').count()
student_late_count = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
Q(status=CHECKIN_STATUS_LATE)
| Q(status=CHECKIN_STATUS_LATEEARLY)).distinct('student').count()
student_early_count = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
Q(status=CHECKIN_STATUS_EARLY)
| Q(status=CHECKIN_STATUS_LATEEARLY)).distinct('student').count()
student_normal_count = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
status=CHECKIN_STATUS_NORMAL).distinct('student').count()
course_late_count = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
Q(status=CHECKIN_STATUS_LATE)
| Q(status=CHECKIN_STATUS_LATEEARLY)).count()
course_early_count = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
Q(status=CHECKIN_STATUS_EARLY)
| Q(status=CHECKIN_STATUS_LATEEARLY)).count()
course_normal_count = Checkin.objects.filter(
lesson__in=term_start_lessons).filter(
status=CHECKIN_STATUS_EARLY).count()
data.update({
'student_all_arrive': student_all_arrive,
'student_all_normal': student_all_normal,
'student_public_ask': student_public_ask,
'student_private_ask': student_private_ask,
'student_sick_ask': student_sick_ask,
'student_other': student_other,
'student_late_count': student_late_count,
'student_early_count': student_early_count,
'course_late_count': course_late_count,
'course_early_count': course_early_count,
'course_normal_count': course_normal_count,
'student_all_count': student_all_count,
'student_normal_count': student_normal_count
})
# term time status
termtime_normal_value = []
termtime_ask_value = []
termtime_success_value = []
termtime_late_value = []
termtime_early_value = []
termtime_lateearly_value = []
termtime_arrive_value = []
termtime_arrive_ratio = []
x = []
for week in range(1, now_week + 1):
for day in range(0, 7):
res = Checkin.objects.filter(
lesson__in=term_start_lessons,
lesson__day=day,
lesson__week=week).aggregate(
normal=Count(
Case(When(status=CHECKIN_STATUS_NORMAL, then=1))),
ask=Count(
Case(
When(status__in=[
CHECKIN_STATUS_PRIVATE_ASK,
CHECKIN_STATUS_PUBLIC_ASK,
CHECKIN_STATUS_SICK_ASK
],
then=1))),
success=Count(
Case(When(status=CHECKIN_STATUS_SUCCESS, then=1))),
early=Count(Case(When(status=CHECKIN_STATUS_EARLY,
then=1))),
late=Count(Case(When(status=CHECKIN_STATUS_LATE, then=1))),
lateearly=Count(
Case(When(status=CHECKIN_STATUS_LATEEARLY, then=1))),
)
if res['lateearly'] + res['early'] + res['late'] + res[
'success'] == 0:
continue
termtime_normal_value.append(res['normal'])
termtime_ask_value.append(res['ask'])
termtime_success_value.append(res['success'])
termtime_late_value.append(res['late'])
termtime_early_value.append(res['early'])
termtime_lateearly_value.append(res['lateearly'])
arrive_count = res['lateearly'] + res['early'] + res['late'] + res[
'success']
should_count = arrive_count + res['normal']
termtime_arrive_value.append(arrive_count)
termtime_arrive_ratio.append(
int(arrive_count * 100.0 / should_count))
x.append("%d-%d" % (week, day))
data.update({
'xAxis': x,
'termtime_normal_value': termtime_normal_value,
'termtime_ask_value': termtime_ask_value,
'termtime_success_value': termtime_success_value,
'termtime_late_value': termtime_late_value,
'termtime_early_value': termtime_early_value,
'termtime_lateearly_value': termtime_lateearly_value,
'termtime_arrive_ratio': termtime_arrive_ratio,
'termtime_arrive_value': termtime_arrive_value
})
return HttpResponse(json.dumps(data), content_type="application/json")
def generate_checkin_daily_excel():
now_lesson_time = getnowlessontime()
now_week = now_lesson_time['week']
now_day = now_lesson_time['day']
now_term = now_lesson_time['term']
today_lessons = Lesson.objects.filter(week=now_week, day=now_day, term=now_term)
count = today_lessons.count()
if count == 0:
return
name = "daily/%s.xlsx" % datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d')
workbook = xlsxwriter.Workbook(settings.MEDIA_ROOT + '/' + name)
worksheet = workbook.add_worksheet()
other = workbook.add_format({'bold': True, 'fg_color': '#D7E4BC'})
date_format = workbook.add_format({'bold': True, 'fg_color': '#D7E4BC', 'num_format': 'yyyy-mm-dd'})
merge_format = workbook.add_format({
'bold': True,
'font_size': 20,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('A1:N1', settings.SCHOOL_NAME + u'每日考勤记录', merge_format)
worksheet.write('A2', now_term + u'学期', other)
worksheet.write('B2', u'第%d周' % now_week, other)
day_string = day_to_week_string(now_day)
worksheet.write('C2', day_string, other)
worksheet.merge_range('D2:K2', '', other)
worksheet.merge_range('L2:N2', datetime.datetime.now(), date_format)
checkindata = Checkin.objects.filter(lesson=OuterRef('id')).values('lesson_id').annotate(count=Count('*')).values(
'count')
today_lessons = today_lessons.select_related('course').select_related('classroom').select_related(
'course__department')
today_lessons = today_lessons.annotate(
actually=Subquery(checkindata.filter(
status__in=[CHECKIN_STATUS_EARLY, CHECKIN_STATUS_SUCCESS, CHECKIN_STATUS_LATE, CHECKIN_STATUS_LATEEARLY]),
output_field=IntegerField()),
late=Subquery(checkindata.filter(status=CHECKIN_STATUS_LATE), output_field=IntegerField()),
early=Subquery(checkindata.filter(status=CHECKIN_STATUS_EARLY), output_field=IntegerField()),
late_early=Subquery(checkindata.filter(status=CHECKIN_STATUS_LATEEARLY), output_field=IntegerField()),
should=Subquery(checkindata, output_field=IntegerField()),
ask=Subquery(checkindata.filter(status__gte=10), output_field=IntegerField()),
)
row_offset = 2
columns = [
{'header': u'课程名称'},
{'header': u'课程编号'},
{'header': u'时间'},
{'header': u'教师'},
{'header': u'教学班'},
{'header': u'开课院系'},
{'header': u'应到'},
{'header': u'实到'},
{'header': u'迟到'},
{'header': u'早退'},
{'header': u'迟&早'},
{'header': u'请假'},
{'header': u'出勤率', 'format': workbook.add_format({'num_format': '0.00%'})},
{'header': u'签到次数'},
]
data = []
for index, p in enumerate(today_lessons.all()):
data.append([
p.course.title,
p.course.serialnumber,
"%d-%d" % (p.time, p.time + p.length - 1),
",".join(p.course.teachers.values_list('name', flat=True)),
p.course.teachclass.name if p.course.teachclass else None,
p.course.department.name if p.course.department else None,
p.should if p.isnow() or p.isend() else p.shouldnumber,
p.actually if p.actually else 0,
p.late if p.late else 0,
p.early if p.early else 0,
p.late_early if p.late_early else 0,
p.asknumber if p.checkincount else p.ask,
(p.actually * 1.0 / p.shouldnumber) if p.actually else None,
p.checkincount if p.checkincount else 0
])
worksheet.add_table(row_offset, 0, row_offset + count, 13,
{'data': data, 'columns': columns, 'style': 'Table Style Light 11'})
worksheet.set_column('A:A', 34)
worksheet.set_column('B:B', 33)
worksheet.set_column('C:C', 6)
worksheet.set_column('D:D', 12)
worksheet.set_column('E:E', 14)
worksheet.set_column('F:F', 17)
worksheet.set_column('G:L', 5.83)
worksheet.set_column('M:M', 7.83)
worksheet.set_column('N:N', 9.83)
workbook.close()
ch = CheckinHistory(term=now_term, day=now_day, week=now_week, course_count=count)
ch.file.name = name
ch.save()
logger.info("[generate_checkin_daily_excel] Successful generate")
emails = DailySubscibe.objects.values_list('user__email', flat=True)
if len(emails) > 0:
email = EmailMessage(
u'【checkinsystem】%s学期 第%d周 %s 考勤数据' % (now_term, now_week, day_string),
u'自动发送，请勿回复此邮件',
settings.SERVER_EMAIL,
emails,
)
email.attach_file(ch.file.path)
email.send(fail_silently=True)
logger.info("[generate_checkin_daily_excel] Successful send email")
return name