## $Id: export.py 15062 2018-06-25 10:02:45Z henrik $ ## ## Copyright (C) 2011 Uli Fouquet & Henrik Bettermann ## This program is free software; you can redistribute it and/or modify ## it under the terms of the GNU General Public License as published by ## the Free Software Foundation; either version 2 of the License, or ## (at your option) any later version. ## ## This program is distributed in the hope that it will be useful, ## but WITHOUT ANY WARRANTY; without even the implied warranty of ## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the ## GNU General Public License for more details. ## ## You should have received a copy of the GNU General Public License ## along with this program; if not, write to the Free Software ## Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA ## """Exporters for student related stuff. """ import os import grok from datetime import datetime, timedelta from zope.component import getUtility from waeup.kofa.interfaces import ( IExtFileStore, IFileStoreNameChooser, IKofaUtils) from waeup.kofa.interfaces import MessageFactory as _ from waeup.kofa.students.catalog import StudentsQuery, CourseTicketsQuery from waeup.kofa.students.interfaces import ( IStudent, IStudentStudyCourse, IStudentStudyLevel, ICourseTicket, IStudentOnlinePayment, ICSVStudentExporter, IBedTicket) from waeup.kofa.students.vocabularies import study_levels from waeup.kofa.utils.batching import ExporterBase from waeup.kofa.utils.helpers import iface_names, to_timezone def get_students(site, stud_filter=StudentsQuery()): """Get all students registered in catalog in `site`. """ return stud_filter.query() def get_studycourses(students): """Get studycourses of `students`. """ return [x.get('studycourse', None) for x in students if x is not None] def get_levels(students): """Get all studylevels of `students`. """ levels = [] for course in get_studycourses(students): for level in course.values(): levels.append(level) return levels def get_tickets(students, **kw): """Get course tickets of `students`. If code is passed through, filter course tickets which belong to this course code and meet level=level and level_session=level_session. If not, but ct_level and ct_session are passed through, filter course tickets which meet level==ct_level and level_session==ct_session. """ tickets = [] code = kw.get('code', None) level = kw.get('level', None) session = kw.get('session', None) ct_level = kw.get('ct_level', None) ct_session = kw.get('ct_session', None) if code is None: for level_obj in get_levels(students): for ticket in level_obj.values(): if ct_level not in ('all', None): if level_obj.level in (10, 999, None) \ and int(ct_level) != level_obj.level: continue if level_obj.level not in range( int(ct_level), int(ct_level)+100, 10): continue if ct_session not in ('all', None) and \ int(ct_session) != level_obj.level_session: continue tickets.append(ticket) else: for level_obj in get_levels(students): for ticket in level_obj.values(): if ticket.code != code: continue if level not in ('all', None): if level_obj.level in (10, 999, None) \ and int(level) != level_obj.level: continue if level_obj.level not in range( int(level), int(level)+100, 10): continue if session not in ('all', None) and \ int(session) != level_obj.level_session: continue tickets.append(ticket) return tickets def get_tickets_for_lecturer(students, **kw): """Get course tickets of `students`. Filter course tickets which belong to this course code and which are editable by lecturers. """ tickets = [] code = kw.get('code', None) level_session = kw.get('session', None) level = kw.get('level', None) for level_obj in get_levels(students): for ticket in level_obj.values(): if ticket.code != code: continue if not ticket.editable_by_lecturer: continue if level not in ('all', None): if level_obj.level in (10, 999, None) \ and int(level) != level_obj.level: continue if level_obj.level not in range(int(level), int(level)+100, 10): continue if level_session not in ('all', None) and \ int(level_session) != level_obj.level_session: continue tickets.append(ticket) return tickets def get_payments(students, p_states=None, **kw): """Get all payments of `students` within given payment_date period. """ date_format = '%d/%m/%Y' payments = [] p_start = kw.get('payments_start') p_end = kw.get('payments_end') paycat = kw.get('paycat') paysession = kw.get('paysession') for student in students: for payment in student.get('payments', {}).values(): if p_start and p_end: if not payment.payment_date: continue payments_start = datetime.strptime(p_start, date_format) payments_end = datetime.strptime(p_end, date_format) tz = getUtility(IKofaUtils).tzinfo payments_start = tz.localize(payments_start) payments_end = tz.localize(payments_end) + timedelta(days=1) payment_date = to_timezone(payment.payment_date, tz) if payment_date < payments_start or payment_date > payments_end: continue if p_states and not payment.p_state in p_states: continue if paycat not in ('all', None) and payment.p_category != paycat: continue if paysession not in ('all', None) and payment.p_session != paysession: continue payments.append(payment) return payments def get_bedtickets(students): """Get all bedtickets of `students`. """ tickets = [] for student in students: for ticket in student.get('accommodation', {}).values(): tickets.append(ticket) return tickets class StudentExporterBase(ExporterBase): """Exporter for students or related objects. This is a baseclass. """ grok.baseclass() grok.implements(ICSVStudentExporter) grok.provides(ICSVStudentExporter) def filter_func(self, x, **kw): return x def get_filtered(self, site, **kw): """Get students from a catalog filtered by keywords. students_catalog is the default catalog. The keys must be valid catalog index names. Returns a simple empty list, a list with `Student` objects or a catalog result set with `Student` objects. .. seealso:: `waeup.kofa.students.catalog.StudentsCatalog` """ # Pass only given keywords to create FilteredCatalogQuery objects. # This way we avoid # trouble with `None` value ambivalences and queries are also # faster (normally less indexes to ask). Drawback is, that # developers must look into catalog to see what keywords are # valid. if kw.get('catalog', None) == 'coursetickets': coursetickets = CourseTicketsQuery(**kw).query() students = [] for ticket in coursetickets: students.append(ticket.student) return list(set(students)) # Payments can be filtered by payment date and payment category. # These parameters are not keys of the catalog and must thus be # removed from kw. try: del kw['payments_start'] del kw['payments_end'] del kw['paycat'] del kw['paysession'] except KeyError: pass # Coursetickets can be filtered by level and session. # These parameters are not keys of the catalog and must thus be # removed from kw. try: del kw['ct_level'] del kw['ct_session'] except KeyError: pass query = StudentsQuery(**kw) return query.query() def get_selected(self, site, selected): """Get set of selected students. Returns a simple empty list or a list with `Student` objects. """ students = [] students_container = site.get('students', {}) for id in selected: student = students_container.get(id, None) if student is None: # try matric number result = list(StudentsQuery(matric_number=id).query()) if result: student = result[0] else: continue students.append(student) return students def export(self, values, filepath=None): """Export `values`, an iterable, as CSV file. If `filepath` is ``None``, a raw string with CSV data is returned. """ writer, outfile = self.get_csv_writer(filepath) for value in values: self.write_item(value, writer) return self.close_outfile(filepath, outfile) def export_all(self, site, filepath=None): """Export students into filepath as CSV data. If `filepath` is ``None``, a raw string with CSV data is returned. """ return self.export(self.filter_func(get_students(site)), filepath) def export_student(self, student, filepath=None): return self.export(self.filter_func([student]), filepath=filepath) def export_filtered(self, site, filepath=None, **kw): """Export items denoted by `kw`. If `filepath` is ``None``, a raw string with CSV data should be returned. """ data = self.get_filtered(site, **kw) return self.export(self.filter_func(data, **kw), filepath=filepath) def export_selected(self,site, filepath=None, **kw): """Export data for selected set of students. """ selected = kw.get('selected', []) data = self.get_selected(site, selected) return self.export(self.filter_func(data, **kw), filepath=filepath) class StudentExporter(grok.GlobalUtility, StudentExporterBase): """The Student Exporter first filters the set of students by searching the students catalog. Then it exports student base data of this set of students. """ grok.name('students') fields = tuple(sorted(iface_names(IStudent))) + ( 'password', 'state', 'history', 'certcode', 'is_postgrad', 'current_level', 'current_session') title = _(u'Students') def mangle_value(self, value, name, context=None): """The mangler prepares the history messages and adds a hash symbol at the end of the phone number to avoid annoying automatic number transformation by Excel or Calc.""" if name == 'history': value = value.messages if 'phone' in name and value is not None: # Append hash '#' to phone numbers to circumvent # unwanted excel automatic value = str('%s#' % value) return super( StudentExporter, self).mangle_value( value, name, context=context) class StudentStudyCourseExporter(grok.GlobalUtility, StudentExporterBase): """The Student Study Course Exporter first filters the set of students by searching the students catalog. Then it exports the data of the current study course container of each student from this set. It does not export their content. """ grok.name('studentstudycourses') fields = tuple(sorted(iface_names(IStudentStudyCourse))) + ('student_id',) title = _(u'Student Study Courses') def filter_func(self, x, **kw): return get_studycourses(x) def mangle_value(self, value, name, context=None): """The mangler determines the certificate code and the student id. """ if name == 'certificate' and value is not None: # XXX: hopefully cert codes are unique site-wide value = value.code if name == 'student_id' and context is not None: student = context.student value = getattr(student, name, None) return super( StudentStudyCourseExporter, self).mangle_value( value, name, context=context) class StudentStudyLevelExporter(grok.GlobalUtility, StudentExporterBase): """The Student Study Level Exporter first filters the set of students by searching the students catalog. Then it exports the data of the student's study level container data but not their content (course tickets). The exporter iterates over all objects in the students' ``studycourse`` containers. """ grok.name('studentstudylevels') fields = tuple(sorted(iface_names( IStudentStudyLevel))) + ( 'student_id', 'number_of_tickets','certcode') title = _(u'Student Study Levels') def filter_func(self, x, **kw): return get_levels(x) def mangle_value(self, value, name, context=None): """The mangler determines the student id, nothing else. """ if name == 'student_id' and context is not None: student = context.student value = getattr(student, name, None) return super( StudentStudyLevelExporter, self).mangle_value( value, name, context=context) class CourseTicketExporter(grok.GlobalUtility, StudentExporterBase): """The Course Ticket Exporter exports course tickets. Usually, the exporter first filters the set of students by searching the students catalog. Then it collects and iterates over all ``studylevel`` containers of the filtered student set and finally iterates over all items inside these containers. If the course code is passed through, the exporter uses a different catalog. It searches for students in the course tickets catalog and exports those course tickets which belong to the given course code and also meet level and session passed through at the same time. This happens if the exporter is called at course level in the academic section. """ grok.name('coursetickets') fields = tuple(sorted(iface_names(ICourseTicket) + ['level', 'code', 'level_session'])) + ('student_id', 'certcode', 'display_fullname') title = _(u'Course Tickets') def filter_func(self, x, **kw): return get_tickets(x, **kw) def mangle_value(self, value, name, context=None): """The mangler determines the student's id and fullname. """ if context is not None: student = context.student if name in ('student_id', 'display_fullname') and student is not None: value = getattr(student, name, None) return super( CourseTicketExporter, self).mangle_value( value, name, context=context) class DataForLecturerExporter(grok.GlobalUtility, StudentExporterBase): """The Data for Lecturer Exporter searches for students in the course tickets catalog and exports those course tickets which belong to the given course code, meet level and session passed through at the same time, and which are editable by lecturers. This exporter can only be called at course level in the academic section. """ grok.name('lecturer') fields = ('matric_number', 'student_id', 'display_fullname', 'level', 'code', 'level_session', 'score') title = _(u'Data for Lecturer') def filter_func(self, x, **kw): return get_tickets_for_lecturer(x, **kw) def mangle_value(self, value, name, context=None): """The mangler determines the student's id and fullname. """ if context is not None: student = context.student if name in ('matric_number', 'reg_number', 'student_id', 'display_fullname',) and student is not None: value = getattr(student, name, None) return super( DataForLecturerExporter, self).mangle_value( value, name, context=context) class StudentPaymentExporter(grok.GlobalUtility, StudentExporterBase): """The Student Payment Exporter first filters the set of students by searching the students catalog. Then it exports student payment tickets by iterating over the items of the student's ``payments`` container. If the payment period is given, only tickets, which were paid in payment period, are considered for export. """ grok.name('studentpayments') fields = tuple( sorted(iface_names( IStudentOnlinePayment, exclude_attribs=False, omit=['display_item', 'certificate', 'student']))) + ( 'student_id','state','current_session') title = _(u'Student Payments') def filter_func(self, x, **kw): return get_payments(x, **kw) def mangle_value(self, value, name, context=None): """The mangler determines the student's id, registration state and current session. """ if context is not None: student = context.student if name in ['student_id','state', 'current_session'] and student is not None: value = getattr(student, name, None) return super( StudentPaymentExporter, self).mangle_value( value, name, context=context) class StudentUnpaidPaymentExporter(StudentPaymentExporter): """The Student Unpaid Payment Exporter works just like the Student Payments Exporter but it exports only unpaid tickets. This exporter is designed for finding and finally purging outdated payment ticket. """ grok.name('studentunpaidpayments') title = _(u'Student Unpaid Payments') def filter_func(self, x, **kw): return get_payments(x, p_states=('unpaid',) , **kw) class DataForBursaryExporter(StudentPaymentExporter): """The DataForBursary Exporter works just like the Student Payment Exporter but it exports much more information about the student. It combines payment and student data in one table in order to spare postprocessing of two seperate export files. The exporter is primarily used by bursary officers who have exclusively access to this exporter. The exporter exports ``paid`` and ``waived`` payment tickets. """ grok.name('bursary') def filter_func(self, x, **kw): return get_payments(x, p_states=('paid', 'waived'), **kw) fields = tuple( sorted(iface_names( IStudentOnlinePayment, exclude_attribs=False, omit=['display_item', 'certificate', 'student']))) + ( 'student_id','matric_number','reg_number', 'firstname', 'middlename', 'lastname', 'state','current_session', 'entry_session', 'entry_mode', 'faccode', 'depcode','certcode') title = _(u'Payment Data for Bursary') def mangle_value(self, value, name, context=None): """The mangler fetches the student data. """ if context is not None: student = context.student if name in [ 'student_id','matric_number', 'reg_number', 'firstname', 'middlename', 'lastname', 'state', 'current_session', 'entry_session', 'entry_mode', 'faccode', 'depcode', 'certcode'] and student is not None: value = getattr(student, name, None) return super( StudentPaymentExporter, self).mangle_value( value, name, context=context) class BedTicketExporter(grok.GlobalUtility, StudentExporterBase): """The Bed Ticket Exporter first filters the set of students by searching the students catalog. Then it exports bed tickets by iterating over the items of the student's ``accommodation`` container. """ grok.name('bedtickets') fields = tuple( sorted(iface_names( IBedTicket, exclude_attribs=False, omit=['display_coordinates', 'maint_payment_made']))) + ( 'student_id', 'actual_bed_type') title = _(u'Bed Tickets') def filter_func(self, x, **kw): return get_bedtickets(x) def mangle_value(self, value, name, context=None): """The mangler determines the student id and the type of the bed which has been booked in the ticket. """ if context is not None: student = context.student if name in ['student_id'] and student is not None: value = getattr(student, name, None) if name == 'bed' and value is not None: value = getattr(value, 'bed_id', None) if name == 'actual_bed_type': value = getattr(getattr(context, 'bed', None), 'bed_type', None) return super( BedTicketExporter, self).mangle_value( value, name, context=context) class SchoolFeePaymentsOverviewExporter(StudentExporter): """The School Fee Payments Overview Exporter first filters the set of students by searching the students catalog. Then it exports some student base data together with the total school fee amount paid in each year over a predefined year range (current year - 9, ... , current year + 1). """ grok.name('sfpaymentsoverview') curr_year = datetime.now().year year_range = range(curr_year - 11, curr_year + 1) year_range_tuple = tuple([str(year) for year in year_range]) fields = ('student_id', 'matric_number', 'display_fullname', 'state', 'certcode', 'faccode', 'depcode', 'is_postgrad', 'current_level', 'current_session', 'current_mode', 'entry_session', 'reg_number' ) + year_range_tuple title = _(u'Student School Fee Payments Overview') def mangle_value(self, value, name, context=None): """The mangler summarizes the school fee amounts made per year. It iterates over all paid school fee payment tickets and adds together the amounts paid in a year. Waived payments are marked ``waived``. """ if name in self.year_range_tuple and context is not None: value = 0 for ticket in context['payments'].values(): if ticket.p_category == 'schoolfee' and \ ticket.p_session == int(name): if ticket.p_state == 'waived': value = 'waived' break if ticket.p_state == 'paid': try: value += ticket.amount_auth except TypeError: pass if value == 0: value = '' elif isinstance(value, float): value = round(value, 2) return super( StudentExporter, self).mangle_value( value, name, context=context) class SessionPaymentsOverviewExporter(StudentExporter): """The Session Payments Overview Exporter first filters the set of students by searching the students catalog. Then it exports some student base data together with the total amount paid in predefined payment categories over the previous three session (referring to current academic session). Sample output: header: ``...schoolfee13,schoolfee14,schoolfee15,gown13,gown14,gown15...`` data: ``...2000.0,,3000.0,,,1000.0,...`` This csv data string means that the student paid 2000.0 school fee in 2013 and 3000.0 in 2015. S/He furthermore paid 1000.0 for gown rental in 2015. """ grok.name('sessionpaymentsoverview') paycats = ('schoolfee', 'clearance', 'gown', 'transcript') regular_fields = ('student_id', 'matric_number', 'display_fullname', 'state', 'certcode', 'faccode', 'depcode', 'is_postgrad', 'current_level', 'current_session', 'current_mode', 'entry_session', 'reg_number' ) title = _(u'Session Payments Overview') @property def paycatyears(self): cas = grok.getSite()['configuration'].current_academic_session paycatyears = [] if cas: year_range = range(cas - 2, cas+1) year_range_tuple = tuple([str(year)[2:] for year in year_range]) paycatyears = [ cat+year for cat in self.paycats for year in year_range_tuple] return paycatyears @property def fields(self): return self.regular_fields + tuple(self.paycatyears) def mangle_value(self, value, name, context=None): """ """ amounts = dict() for catyear in self.paycatyears: amounts[catyear] = 0.0 if name[:-2] in self.paycats and context is not None: for ticket in context['payments'].values(): if ticket.p_category == name[:-2]: if ticket.p_state in ('waived', 'paid'): if str(ticket.p_session)[2:] == name[-2:]: amounts[name] += ticket.amount_auth if amounts[name] == 0.0: value = '' elif isinstance(amounts[name], float): value = round(amounts[name], 2) return super( StudentExporter, self).mangle_value( value, name, context=context) class StudentStudyLevelsOverviewExporter(StudentExporter): """The Student Study Levels Overview Exporter first filters the set of students by searching the students catalog. Then it exports some student base data together with the session key of registered levels. Sample output: header: ``...100,110,120,200,210,220,300...`` data: ``...2010,,,2011,2012,,2013...`` This csv data string means that level 100 was registered in session 2010/2011, level 200 in session 2011/2012, level 210 (200 on 1st probation) in session 2012/2013 and level 300 in session 2013/2014. """ grok.name('studylevelsoverview') avail_levels = tuple([str(x) for x in study_levels(None)]) fields = ('student_id', ) + ( 'state', 'certcode', 'faccode', 'depcode', 'is_postgrad', 'entry_session', 'current_level', 'current_session', ) + avail_levels title = _(u'Student Study Levels Overview') def mangle_value(self, value, name, context=None): """The mangler checks if a given level has been registered. It returns the ``level_session`` attribute of the student study level object if the named level exists. """ if name in self.avail_levels and context is not None: value = '' for level in context['studycourse'].values(): if level.level == int(name): value = '%s' % level.level_session break return super( StudentExporter, self).mangle_value( value, name, context=context) class ComboCardDataExporter(grok.GlobalUtility, StudentExporterBase): """Like all other exporters the Combo Card Data Exporter first filters the set of students by searching the students catalog. Then it exports some student base data which are neccessary to print for the Interswitch combo card (identity card for students). The output contains a ``passport_path`` column which contains the filesystem path of the passport image file. If no path is given, no passport image file exists. """ grok.name('combocard') fields = ('display_fullname', 'student_id','matric_number', 'certificate', 'faculty', 'department', 'passport_path') title = _(u'Combo Card Data') def mangle_value(self, value, name, context=None): """The mangler determines the titles of faculty, department and certificate. It also computes the path of passport image file stored in the filesystem. """ certificate = context['studycourse'].certificate if name == 'certificate' and certificate is not None: value = certificate.title if name == 'department' and certificate is not None: value = certificate.__parent__.__parent__.longtitle if name == 'faculty' and certificate is not None: value = certificate.__parent__.__parent__.__parent__.longtitle if name == 'passport_path' and certificate is not None: file_id = IFileStoreNameChooser(context).chooseName( attr='passport.jpg') os_path = getUtility(IExtFileStore)._pathFromFileID(file_id) if not os.path.exists(os_path): value = None else: value = '/'.join(os_path.split('/')[-4:]) return super( ComboCardDataExporter, self).mangle_value( value, name, context=context)