def find_attendances_gap_grouped(days):
import datetime
import tablib
from import_export.formats import base_formats
from student_registration.schools.models import School
from student_registration.attendances.models import BySchoolByDay
base = date(2016, 10, 1)
dates = []
weekend = [5, 6]
for x in range(0, days):
d = base + datetime.timedelta(days=x)
if d.weekday() not in weekend:
dates.append(d)
schools = School.objects.filter(ndshift_school__isnull=False).distinct()
content = []
data = tablib.Dataset()
data.headers = [
'CERD',
'School name',
'District',
'Governorate',
'total'
]
for school in schools:
ctr = 0
for d in dates:
try:
attendance = BySchoolByDay.objects.get(school_id=school.id, attendance_date=d)
except BySchoolByDay.DoesNotExist:
ctr += 1
data.append([
school.number,
school.name,
school.location,
school.location.parent,
ctr
])
file_format = base_formats.XLSX()
file_object = open("attendances_gap_grouped_by_school.xlsx", "w")
file_object.write(file_format.export_data(data))
file_object.close()
def xlsx_export_action(modeladmin, request, queryset):
formats = modeladmin.get_export_formats()
file_format = base_formats.XLSX()
export_data = modeladmin.get_export_data(file_format,
queryset,
request=request)
content_type = file_format.get_content_type()
# Django 1.7 uses the content_type kwarg instead of mimetype
try:
response = HttpResponse(export_data, content_type=content_type)
except TypeError:
response = HttpResponse(export_data, mimetype=content_type)
response['Content-Disposition'] = 'attachment; filename=%s' % (
modeladmin.get_export_filename(file_format), )
return response
def export_to_excel(request, resource_class, queryset):
file_format = base_formats.XLSX()
resource = resource_class()
data = resource_class().export(queryset)
export_data = file_format.export_data(data)
model = resource.Meta.model
content_type = file_format.get_content_type()
response = HttpResponse(export_data, content_type=content_type)
date_str = timezone.localtime(timezone.now()).strftime('%Y-%m-%d')
filename = "%s-%s.%s" % (model.__name__,
date_str,
file_format.get_extension())
response['Content-Disposition'] = 'attachment; filename=%s' % filename
return response
def setUp(self):
self.format = base_formats.XLSX()
def export_2ndshift_data():
from .models import ImportedData
enrollments = ImportedData.objects.filter(
enrollment__education_year='2017/2018')
print(
ImportedData.objects.filter(
enrollment__education_year='2017/2018').count())
print(
ImportedData.objects.filter(
enrollment__education_year='2016/2017').count())
print(
ImportedData.objects.filter(
enrollment__education_year__isnull=True).count())
# enrollments = ImportedData.objects.all()[0:120000]
# enrollments = ImportedData.objects.all()[120000:240000]
# enrollments = ImportedData.objects.all()[240000:300000]
data = tablib.Dataset()
data.headers = [
'created',
'modified',
'dropout_status',
'disabled',
'moved',
'Last non formal education - result',
'Last non formal education - round',
'Is the child participated in an ALP/2016-2 program',
'Last formal education - result',
'Last formal education - year',
'Last formal education - school shift',
'Last formal education - school type',
'Last formal education - school',
'Last formal education - CERD',
'Last formal education - level',
'Serial number in previous school',
'Last attendance date',
'last absence date',
'Current Section',
'Current Class',
'Education year',
'Phone prefix',
'Phone number',
'Student living address',
'Student ID Number',
'Student ID Type',
'Registered in UNHCR',
'Mother nationality',
'Mother fullname',
'Student nationality',
'Student age',
# 'Student birthday',
'Place of birth',
'year',
'month',
'day',
'Sex',
'Student first name',
'Student father name',
'Student last name',
# 'Student fullname',
'First time registered?',
'Student outreached?',
'Have barcode with him?',
'Outreach barcode',
'Registration date',
'School',
'School number',
'District',
'Governorate'
]
content = []
for enrollment in enrollments:
line = enrollment.enrollment
# if line['education_year'] == '2016/2017':
# continue
content = [
line['created'],
line['modified'],
line['dropout_status'],
line['disabled'],
line['moved'],
line['last_informal_edu_final_result_name'],
line['last_informal_edu_round_name'],
line['participated_in_alp'],
line['last_year_result'],
line['last_education_year'],
line['last_school_shift'],
line['last_school_type'],
line['last_school_name'],
line['last_school_number'],
line['last_education_level_name'],
line['number_in_previous_school'],
line['last_attendance_date'],
line['last_absent_date'],
line['section_name'],
line['classroom_name'],
line['education_year'],
line['student_phone_prefix'],
line['student_phone'],
line['student_address'],
line['student_id_number'],
line['student_id_type'],
line['student_registered_in_unhcr'],
line['student_mother_nationality'],
line['student_mother_fullname'],
line['student_nationality'],
line['student_age'],
line['student_place_of_birth'],
line['student_birthday_year'],
line['student_birthday_month'],
line['student_birthday_day'],
line['student_sex'],
line['student_first_name'],
line['student_father_name'],
line['student_last_name'],
# line['student_fullname'],
line['new_registry'],
line['student_outreached'],
line['have_barcode'],
line['outreach_barcode'],
line['registration_date'],
line['school_name'],
line['school_number'],
line['district'],
line['governorate'],
]
data.append(content)
file_format = base_formats.XLSX()
data = file_format.export_data(data)
return data
def export_attendance_data(params=None, return_data=False):
from .models import ImportedData
queryset = ImportedData.objects.filter(type='attendance')
data = tablib.Dataset()
data.headers = [
_('School number'),
_('School'),
_('School type'),
_('Education year/ALP round'),
_('District'),
_('Governorate'),
_('Attendance date'),
_('Validation status'),
_('Validation date'),
_('Close reason'),
_('Exam day'),
_('Level'),
_('Section'),
_('Student fullname'),
_('Sex'),
_('Age'),
_('Attendance status'),
_('Absence reason')
]
content = []
for enrollment in queryset:
line = enrollment.enrollment
# try:
# print(line['students'])
# print(type(line['students']))
# except Exception:
# print('---------------')
# print(line)
# continue
if not line['students']:
continue
for level_section in line['students']:
attendances = line['students'][level_section]
students = attendances['students']
for student in students:
content = [
line['school_number'],
line['school_name'],
line['school_type'],
line['education_year']
if line['education_year'] else line['alp_round'],
line['district'],
line['governorate'],
line['attendance_date'],
line['validation_date'],
line['validation_status'],
line['close_reason'],
attendances['exam_day'],
student['level_name'],
student['section_name'],
student['student_fullname'],
student['student_sex'],
student['student_age'],
student['status'],
student['absence_reason']
if 'absence_reason' in student else '',
]
data.append(content)
file_format = base_formats.XLSX()
data = file_format.export_data(data)
file_object = open("attendances_data.xlsx", "w")
file_object.write(data)
file_object.close()
def export_winterization(return_data=False):
from student_registration.winterization.models import Assessment
queryset = Assessment.objects.all()
data = tablib.Dataset()
data.headers = [
'_id',
'p_code',
'p_code_name',
'district',
'cadastral',
'location_type',
'assistance_type',
'phone_number',
'phone_owner',
'latitude',
'longitude',
'first_name',
'middle_name',
'last_name',
'mothers_name',
'relationship_type',
'family_count',
'disabilities',
'official_id',
'gender',
'dob',
'marital_status',
'creation_date',
'completion_date',
'partner_name',
'moving_location',
'new_district',
'new_cadastral',
'0 to 3 months',
'3 to 12 months',
'1 year old',
'2 years old',
'3 years old',
'4 years old',
'5 years old',
'6 years old',
'7 years old',
'8 years old',
'9 years old',
'10 years old',
'11 years old',
'12 years old',
'13 years old',
'14 years old',
'male',
'female',
'3 months kit',
'12 months kit',
'2 years kit',
'3 years kit',
'5 years kit',
'7 years kit',
'9 years kit',
'12 years kit',
'14 years kit',
'3 months kit Completed',
'12 months kit Completed',
'2 years kit Completed',
'3 years kit Completed',
'5 years kit Completed',
'7 years kit Completed',
'9 years kit Completed',
'12 years kit Completed',
'14 years kit Completed',
]
content = []
for line in queryset:
content = [
line.get_id(),
line.location_p_code,
line.location_p_code_name,
line.district,
line.cadastral,
line.location_type,
line.locations_type,
line.assistance_type,
line.phone_number,
line.phone_owner,
line.latitude,
line.longitude,
line.first_name,
line.middle_name,
line.last_name,
line.mothers_name,
line.relationship_type,
line.family_count,
line.disabilities,
line.official_id,
line.gender,
line.dob,
line.marital_status,
line.creation_date,
line.completion_date,
line.partner_name,
line.moving_location,
line.new_district,
line.new_cadastral,
line.get_0_to_3_months(),
line.get_3_to_12_months(),
line.get_1_year_old(),
line.get_2_years_old(),
line.get_3_years_old(),
line.get_4_years_old(),
line.get_5_years_old(),
line.get_6_years_old(),
line.get_7_years_old(),
line.get_8_years_old(),
line.get_9_years_old(),
line.get_10_years_old(),
line.get_11_years_old(),
line.get_12_years_old(),
line.get_13_years_old(),
line.get_14_years_old(),
line.male,
line.female,
line.get_3_months_kit(),
line.get_12_months_kit(),
line.get_2_years_kit(),
line.get_3_years_kit(),
line.get_5_years_kit(),
line.get_7_years_kit(),
line.get_9_years_kit(),
line.get_12_years_kit(),
line.get_14_years_kit(),
line.get_3_months_kit_completed(),
line.get_12_months_kit_completed(),
line.get_2_years_kit_completed(),
line.get_3_years_kit_completed(),
line.get_5_years_kit_completed(),
line.get_7_years_kit_completed(),
line.get_9_years_kit_completed(),
line.get_12_years_kit_completed(),
line.get_14_years_kit_completed(),
]
data.append(content)
timestamp = '{}-{}'.format('winter', time.time())
file_format = base_formats.XLSX()
data = file_format.export_data(data)
if return_data:
return data
store_file(data, timestamp)
return True
def export_attendance_by_student(params=None, return_data=False):
from student_registration.attendances.models import Absentee
queryset = Absentee.objects.all()
data = tablib.Dataset()
data.headers = [
'school_id',
'school_number',
'school_name',
'governorate',
'district',
'education_year/alp_round',
'education_year_id',
'alp_round_id',
'level_name',
'level',
'section_name',
'section',
'student_id',
'student_first_name',
'student_father_name',
'student_last_name',
'student_mother_fullname',
'student_sex',
'student_age',
'last_attendance_date',
'attended_days',
'total_attended_days',
'last_absent_date',
'absent_days',
'total_absent_days',
'last_modification_date',
]
content = []
for line in queryset:
school = line.school
school_location = school.location
school_location_parent = school_location.parent
education_year = line.education_year
alp_round = line.alp_round
if not education_year:
continue
content = [
school.id, school.number, school.name, school_location_parent.name,
school_location.name, education_year.name,
education_year.id if education_year else '',
alp_round.id if alp_round else '', line.level_name, line.level,
line.section_name, line.section, line.student_id,
line.student.first_name, line.student.father_name,
line.student.last_name, line.student.mother_fullname,
line.student.sex, line.student.age, line.last_attendance_date,
line.attended_days, line.total_attended_days,
line.last_absent_date, line.absent_days, line.total_absent_days,
line.last_modification_date
]
data.append(content)
timestamp = '{}-{}'.format('attendance_by_student', time.time())
file_format = base_formats.XLSX()
data = file_format.export_data(data)
if return_data:
return data
store_file(data, timestamp)
return True
def export_attendance(params=None, return_data=False):
from student_registration.attendances.models import Attendance
queryset = Attendance.objects.all()
if 'school' in params:
queryset = queryset.filter(school_id=params['school'])
if 'date' in params:
queryset = queryset.filter(attendance_date=params['date'])
if 'from_date' in params:
queryset = queryset.filter(attendance_date__gte=params['from_date'])
if 'to_date' in params:
queryset = queryset.filter(attendance_date__lte=params['to_date'])
if 'gov' in params:
queryset = queryset.filter(school__location__parent=int(params['gov']))
if 'school_type' in params:
school_type = params['school_type']
queryset = queryset.filter(school_type=school_type)
if school_type == 'ALP':
queryset = queryset.filter(alp_round__current_round=True)
if school_type == '2nd-shift':
queryset = queryset.filter(education_year__current_year=True)
data = tablib.Dataset()
data.headers = [
_('School number'),
_('School'),
_('School type'),
_('Education year/ALP round'),
_('District'),
_('Governorate'),
_('Attendance date'),
_('Validation status'),
_('Validation date'),
_('Validated by'),
_('Close reason'),
_('Exam day'),
_('Level'),
_('Section'),
_('Student fullname'),
_('Sex'),
_('Age'),
_('Attendance status'),
_('Absence reason'),
_('Dropout')
]
content = []
for line in queryset:
school = line.school
school_location = school.location
school_location_parent = school_location.parent
education_year = line.education_year
alp_round = line.alp_round
if not line.students:
continue
for level_section in line.students:
attendances = line.students[level_section]
students = attendances['students']
for student in students:
content = [
school.number,
school.name,
line.school_type,
education_year.name if education_year else alp_round.name,
school_location.name,
school_location_parent.name,
line.attendance_date,
line.validation_date,
line.validation_status,
line.validation_owner.username
if line.validation_owner else '',
line.close_reason,
attendances['exam_day'],
student['level_name'],
student['section_name'],
student['student_fullname'],
student['student_sex'],
student['student_age'],
student['status'],
student['absence_reason']
if 'absence_reason' in student else '',
student['dropout'] if 'dropout' in student else '',
]
data.append(content)
timestamp = '{}-{}'.format('attendance', time.time())
file_format = base_formats.XLSX()
data = file_format.export_data(data)
if return_data:
return data
store_file(data, timestamp)
return True
def setUp(self):
self.format = base_formats.XLSX()
self.filename = os.path.join(os.path.dirname(__file__), os.path.pardir,
'exports', 'books.xlsx')
def generate_2ndshift_report(school=0, location=0, email=None, user=None):
from student_registration.enrollments.models import Enrollment
offset = 120000
limit = 180000
# queryset = Enrollment.objects.all()[offset:limit]
queryset = Enrollment.objects.all()
data = tablib.Dataset()
data.headers = [
_('Student status'),
_('Final Grade'),
_('Linguistic field/Arabic'),
_('Sociology field'),
_('Physical field'),
_('Artistic field'),
_('Linguistic field/Foreign language'),
_('Scientific domain/Mathematics'),
_('Scientific domain/Sciences'),
_('Biology'),
_('Chemistry'),
_('Physic'),
_('Science'),
_('Math'),
_('History'),
_('Geography'),
_('Education'),
_('Foreign language'),
_('Arabic'),
_('ALP result'),
_('ALP round'),
_('ALP level'),
_('Is the child participated in an ALP/2016-2 program'),
_('Result'),
_('Education year'),
_('School'),
_('Last education level'),
_('Current Section'),
_('Current Class'),
_('Phone prefix'),
_('Phone number'),
_('Student living address'),
_('Student ID Number'),
_('Student ID Type'),
_('Registered in UNHCR'),
_('Mother nationality'),
_('Mother fullname'),
_('Student nationality'),
_('Student age'),
_('Student birthday'),
_('year'),
_('month'),
_('day'),
_('Sex'),
_('Student fullname'),
_('School'),
_('School number'),
_('District'),
_('Governorate')
]
content = []
for line in queryset:
if not line.student or not line.school:
continue
content = [
line.exam_result,
line.exam_total,
line.exam_result_linguistic_ar,
line.exam_result_sociology,
line.exam_result_physical,
line.exam_result_artistic,
line.exam_result_linguistic_en,
line.exam_result_mathematics,
line.exam_result_sciences,
line.exam_result_bio,
line.exam_result_chemistry,
line.exam_result_physic,
line.exam_result_science,
line.exam_result_math,
line.exam_result_history,
line.exam_result_geo,
line.exam_result_education,
line.exam_result_language,
line.exam_result_arabic,
line.last_informal_edu_final_result.name if line.last_informal_edu_final_result else '',
line.last_informal_edu_round.name if line.last_informal_edu_round else '',
line.last_informal_edu_level.name if line.last_informal_edu_level else '',
_(line.participated_in_alp) if line.participated_in_alp else '',
_(line.last_year_result) if line.last_year_result else '',
line.last_education_year if line.last_education_year else '',
_(line.last_school_type) if line.last_school_type else '',
line.last_education_level.name if line.last_education_level else '',
line.section.name if line.section else '',
line.classroom.name if line.classroom else '',
line.student.phone_prefix,
line.student.phone,
line.student.address,
line.student.id_number,
line.student.id_type.name if line.student.id_type else '',
line.registered_in_unhcr,
line.student.mother_nationality.name if line.student.mother_nationality else '',
line.student.mother_fullname,
line.student.nationality_name(),
line.student.calc_age,
line.student.birthday,
line.student.birthday_year,
line.student.birthday_month,
line.student.birthday_day,
_(line.student.sex) if line.student.sex else '',
line.student.__unicode__(),
line.school.name,
line.school.number,
line.school.location.name,
line.school.location.parent.name,
]
data.append(content)
file_format = base_formats.XLSX()
file_object = open("enrolment_data.xlsx", "w")
file_object.write(file_format.export_data(data))
file_object.close()
def get_export_formats(self):
if not base_formats.XLSX().can_export():
return super(OneDollarUserAdmin, self).get_export_formats()
return [base_formats.XLSX]