def defaultIssueDate(self, schoolyear): db = DB(schoolyear) _date = db.getInfo('TEXT_DATE_OF_ISSUE') if not _date: _date = Dates.getCalendar(schoolyear).get('END') if not _date: flash( "Schuljahresende ('END') fehlt im Kalender für %d" % schoolyear, 'Error') _date = Dates.today() self.DATE_D.data = datetime.date.fromisoformat(_date)
def migratePupils (schoolyear): """Read the pupil data from the previous year and build a preliminary database table for the current (new) year, migrating the class names according to <CONF.MISC.MIGRATE_CLASS> """ # Get pupil data from previous year pdb = Pupils (schoolyear-1) # Maximum year number for various streams: maxyear = {} try: for x in CONF.MISC.STREAM_MAX_YEAR: k, v = x.split (':') maxyear [k] = v except: REPORT.Fail (_BAD_STREAM_MAX_YEAR, val=x) rows = [] for c_old in pdb.classes (): # Increment the year part of the class name try: cnum = int (c_old [:2]) + 1 ctag = c_old [2:] except: REPORT.Fail (_BADCLASSNAME, klass=c_old) c_new = '%02d%s' % (cnum, ctag) for prow in pdb.classPupils (c_old): left = False if prow ['EXIT_D']: # If there is an exit date, assume the pupil has left. left = True else: try: mxy = maxyear [prow ['STREAM']] except: mxy = maxyear [''] if cnum > int (mxy): left = True if left: REPORT.Info (_PUPIL_LEFT, klass=c_old, name=prow.name ()) continue prow ['CLASS'] = c_new rows.append (prow) # Create the database table PUPILS from the loaded pupil data. db = DB (schoolyear, flag='CANCREATE') # Use (CLASS, PSORT) as primary key, with additional index on PID. # This makes quite a small db (without rowid). db.makeTable2 ('PUPILS', PupilData.fields (), data=rows, force=True, pk=('CLASS', 'PSORT'), index=('PID',))
def importPupils(schoolyear, filepath): """Import the pupils data for the given year from the given file. The file must be a 'dbtable' spreadsheet with the correct school-year. """ classes = {} # Ordered field list for the table fields = CONF.TABLES.PUPILS_FIELDNAMES # internal names rfields = fields.values() # external names (spreadsheet headers) table = readDBTable(filepath) try: if int(table.info[_SCHOOLYEAR]) != schoolyear: raise ValueError except: REPORT.Fail(_BADSCHOOLYEAR, filepath=filepath) colmap = [] for f in rfields: try: colmap.append(table.headers[f]) except: # Field not present REPORT.Warn(_MISSINGDBFIELD, filepath=filepath, field=f) colmap.append(None) ### Read the row data rows = [] classcol = table.headers[fields['CLASS']] # class-name column for row in table: rowdata = [None if col == None else row[col] for col in colmap] rows.append(rowdata) # Count pupils in each class klass = rowdata[classcol] try: classes[klass] += 1 except: classes[klass] = 1 # Create the database table PUPILS from the loaded pupil data. db = DB(schoolyear, flag='CANCREATE') # Use (CLASS, PSORT) as primary key, with additional index on PID. # This makes quite a small db (without rowid). db.makeTable2('PUPILS', fields, data=rows, force=True, pk=('CLASS', 'PSORT'), index=('PID', )) return classes
def singleGrades2db(schoolyear, pid, klass, term, date, rtype, grades): """Add or update GRADES table entry for a single pupil and date. <term> is the date of the entry, which may already exist: the TERM field. <date> is the new date, which may be the same as <term>, but can also indicate a change, in which case also the TERM field will be changed. <rtype> is the report type. <grades> is a mapping {sid -> grade}. """ db = DB(schoolyear) gstring = map2grades(grades) db.updateOrAdd('GRADES', { 'KLASS': klass.klass, 'STREAM': klass.stream, 'PID': pid, 'TERM': term if term.isdigit() else date, 'REPORT_TYPE': rtype, 'DATE_D': date, 'GRADES': gstring }, TERM=term, PID=pid )
def getGradeData(schoolyear, pid, term): """Return all the data from the database GRADES table for the given pupil as a mapping. Either term or – in the case of "extra" reports – date is supplied to key the entry. The string in field 'GRADES' is converted to a mapping. If there is grade data, its validity is checked. If there is no grade data, this field is <None>. """ db = DB(schoolyear) gdata = db.select1('GRADES', PID=pid, TERM=term) if gdata: # Convert the grades to a <dict> gmap = dict(gdata) try: gmap['GRADES'] = grades2map(gdata['GRADES']) except ValueError: REPORT.Fail(_BAD_GRADE_DATA, pid=pid, term=term) return gmap return None
def updateGradeReport(schoolyear, pid, term, date, rtype): """Update grade database when building reports. <pid> (pupil-id) and <term> (term or extra date) are used to key the entry in the GRADES table. For term reports, update only DATE_D and REPORT_TYPE fields. This is not used for "extra" reports. """ db = DB(schoolyear) termn = int(term) # check that term (not date) is given try: # Update term. This only works if there is already an entry. db.updateOrAdd ('GRADES', {'DATE_D': date, 'REPORT_TYPE': rtype}, update_only=True, PID=pid, TERM=term ) except UpdateError: REPORT.Bug("No entry in db, table GRADES for: PID={pid}, TERM={term}", pid=pid, term=term)
def exportPupils(schoolyear, filepath): """Export the pupil data for the given year to a spreadsheet file, formatted as a 'dbtable'. """ # Ensure folder exists folder = os.path.dirname(filepath) if not os.path.isdir(folder): os.makedirs(folder) db = DB(schoolyear) classes = {} for row in db.getTable('PUPILS'): klass = row['CLASS'] try: classes[klass].append(row) except: classes[klass] = [row] # Check all fields are present dbfields = set(db.fields) fields = CONF.TABLES.PUPILS_FIELDNAMES for f in fields: try: dbfields.remove(f) except: REPORT.Error(_DB_FIELD_MISSING, field=f) for f in dbfields: REPORT.Error(_DB_FIELD_LOST, field=f) rows = [] for klass in sorted(classes): for vrow in classes[klass]: values = [] for f in fields: try: values.append(vrow[f]) except KeyError: values.append(None) rows.append(vrow) rows.append(None) makeDBTable(filepath, _PUPIL_TABLE_TITLE, fields.values(), rows, [(_SCHOOLYEAR, schoolyear)])
def index(): schoolyear = session['year'] form = DateForm() if form.validate_on_submit(): # POST # Store date of issue _date = form.getDate() db = DB(schoolyear) db.setInfo('TEXT_DATE_OF_ISSUE', _date) # GET form.defaultIssueDate(schoolyear) p = Pupils(schoolyear) _kmap = CONF.TEXT.REPORT_TEMPLATES['Mantelbogen'] klasses = [] for k in p.classes(): klass = Klass(k) if klass.match_map(_kmap): klasses.append(str(klass)) return render_template(os.path.join(_BPNAME, 'index.html'), form=form, heading=_HEADING, klasses=klasses)
def db2grades(schoolyear, term, klass, checkonly=False): """Fetch the grades for the given school-class/group, term, schoolyear. Return a list [(pid, pname, {subject -> grade}), ...] <klass> is a <Klass> instance, which can include a list of streams (including '_' for pupils without a stream). If there are streams, only grades for pupils in one of these streams will be included. """ slist = klass.streams plist = [] # Get the pupils from the pupils db and search for grades for these. pupils = Pupils(schoolyear) db = DB(schoolyear) for pdata in pupils.classPupils(klass): # Check pupil's stream if there is a stream filter pstream = pdata['STREAM'] if slist and (pstream not in slist): continue pid = pdata['PID'] gdata = db.select1('GRADES', PID=pid, TERM=term) if gdata: gstring = gdata['GRADES'] or None if gstring: if gdata['KLASS'] != klass.klass or gdata['STREAM'] != pstream: # Pupil has switched klass and/or stream. # This can only be handled via individual view. gstring = None else: gstring = None if gstring and not checkonly: try: gmap = grades2map(gstring) except ValueError: REPORT.Fail(_BAD_GRADE_DATA, pid=pid, term=term) plist.append((pid, pdata.name(), gmap)) else: plist.append((pid, pdata.name(), gstring)) return plist
def test_02(): """ Initialise PUPILS table from "old" raw data (creation from scratch, no pre-existing table). """ db = DB(_testyear, 'RECREATE') fpath = os.path.join(Paths.getYearPath(_testyear, 'DIR_SCHOOLDATA'), '_test', 'pupil_data_0_raw') REPORT.Test( "Initialise with raw pupil data for school-year %d from:\n %s" % (_testyear, fpath)) rpd = readRawPupils(_testyear, fpath) for klass in sorted(rpd): REPORT.Test("\n +++ Class %s" % klass) for row in rpd[klass]: REPORT.Test(" --- %s" % repr(row)) updateFromRaw(_testyear, rpd) db.renameTable('PUPILS', 'PUPILS0') db.deleteIndexes('PUPILS0') REPORT.Test("Saved in table PUPILS0")
def pupil(pid): """View: select report type and [edit-existing vs. new] for single report. All existing report dates for this pupil will be presented for selection. If there are no existing dates for this pupil, the only option is to construct a new one. Also a report type can be selected. The list might include invalid types as it is difficult at this stage (considering potential changes of stream or even school-class) to determine exactly which ones are valid. """ class _Form(FlaskForm): KLASS = SelectField("Klasse") STREAM = SelectField("Maßstab") EDITNEW = SelectField("Ausgabedatum") RTYPE = SelectField("Zeugnistyp") schoolyear = session['year'] # Get pupil data pupils = Pupils(schoolyear) pdata = pupils.pupil(pid) pname = pdata.name() klass = pdata.getKlass(withStream=True) # Get existing dates. db = DB(schoolyear) rows = db.select('GRADES', PID=pid) dates = [_NEWDATE] for row in db.select('GRADES', PID=pid): dates.append(row['TERM']) # If the stream, or even school-class have changed since an # existing report, the templates and available report types may be # different. To keep it simple, a list of all report types from the # configuration file GRADES.REPORT_TEMPLATES is presented for selection. # An invalid choice can be flagged at the next step. # If there is a mismatch between school-class/stream of the pupil as # selected on this page and that of the existing GRADES entry, a # warning can be shown at the next step. rtypes = [ rtype for rtype in CONF.GRADES.REPORT_TEMPLATES if rtype[0] != '_' ] kname = klass.klass stream = klass.stream form = _Form(KLASS=kname, STREAM=stream, RTYPE=_DEFAULT_RTYPE) form.KLASS.choices = [(k, k) for k in reversed(pupils.classes())] form.STREAM.choices = [(s, s) for s in CONF.GROUPS.STREAMS] form.EDITNEW.choices = [(d, d) for d in dates] form.RTYPE.choices = [(t, t) for t in rtypes] if form.validate_on_submit(): # POST klass = Klass.fromKandS(form.KLASS.data, form.STREAM.data) rtag = form.EDITNEW.data rtype = form.RTYPE.data kmap = CONF.GRADES.REPORT_TEMPLATES[rtype] tfile = klass.match_map(kmap) if tfile: return redirect( url_for('bp_grades.make1', pid=pid, kname=klass, rtag=rtag, rtype=rtype)) else: flash( "Zeugnistyp '%s' nicht möglich für Gruppe %s" % (rtype, klass), "Error") # GET return render_template(os.path.join(_BPNAME, 'pupil.html'), form=form, heading=_HEADING, klass=kname, pname=pname)
def grades2db(schoolyear, gtable, term=None): """Enter the grades from the given table into the database. <schoolyear> is checked against the value in the info part of the table (gtable.info['SCHOOLYEAR']). <term>, if given, is only used as a check against the value in the info part of the table (gtable.info['TERM']). """ # Check school-year try: y = gtable.info.get('SCHOOLYEAR', '–––') yn = int(y) except ValueError: REPORT.Fail(_INVALID_YEAR, val=y) if yn != schoolyear: REPORT.Fail(_WRONG_YEAR, year=y) # Check term rtag = gtable.info.get('TERM', '–––') if term: if term != rtag: REPORT.Fail(_WRONG_TERM, term=term, termf=rtag) # Check klass klass = Klass(gtable.info.get('CLASS', '–––')) # Check validity pupils = Pupils(schoolyear) try: plist = pupils.classPupils(klass) if not plist: raise ValueError except: REPORT.Fail(_INVALID_KLASS, klass=klass) # Filter the relevant pids p2grades = {} p2stream = {} for pdata in plist: pid = pdata['PID'] try: p2grades[pid] = gtable.pop(pid) except KeyError: # The table may include just a subset of the pupils continue p2stream[pid] = pdata['STREAM'] # Anything left unhandled in <gtable>? for pid in gtable: REPORT.Error(_UNKNOWN_PUPIL, pid=pid) #TODO: Sanitize input ... only valid grades? # Now enter to database if p2grades: db = DB(schoolyear) for pid, grades in p2grades.items(): gstring = map2grades(grades) db.updateOrAdd('GRADES', { 'KLASS': klass.klass, 'STREAM': p2stream[pid], 'PID': pid, 'TERM': rtag, 'REPORT_TYPE': None, 'DATE_D': None, 'GRADES': gstring }, TERM=rtag, PID=pid ) REPORT.Info(_NEWGRADES, n=len(p2grades), klass=klass, year=schoolyear, term=rtag) else: REPORT.Warn(_NOPUPILS)
def updateFromRaw(schoolyear, rawdata): """Update the PUPILS table from the supplied raw pupil data. Only the fields supplied in the raw data will be affected. If there is no PUPILS table, create it, leaving fields for which no data is supplied empty. <rawdata>: {klass -> [<_IndexedData> instance, ...]} """ updated = False allfields = list(CONF.TABLES.PUPILS_FIELDNAMES) db = DB(schoolyear, flag='CANCREATE') # Build a pid-indexed mapping of the existing (old) pupil data. # Note that this is read in as <sqlite3.Row> instances! oldclasses = {} classes = set(rawdata) # collect all classes, old and new if db.tableExists('PUPILS'): for pmap in db.getTable('PUPILS'): pid = pmap['PID'] klass = pmap['CLASS'] classes.add(klass) try: oldclasses[klass][pid] = pmap except: oldclasses[klass] = {pid: pmap} # Collect rows for the new PUPILS table: newpupils = [] # Run through the new data, class-by-class for klass in sorted(classes): changed = OrderedDict() # pids with changed fields try: plist = rawdata[klass] except: # Class not in new data updated = True #TODO: do I want to record this (with pupil names??)? REPORT.Warn(_CLASSGONE, klass=klass) continue try: oldpids = oldclasses[klass] except: # A new class updated = True oldpids = {} #? REPORT.Warn(_NEWCLASS, klass=klass) #TODO: only the PIDs are stored, I would need at least their names, for reporting added = [] for pdata in plist: pid = pdata['PID'] prow = [] try: pmap0 = oldpids[pid] except: # A new pupil for f in allfields: try: val = pdata[f] except: val = None prow.append(val) updated = True added.append(pid) else: del (oldpids[pid]) # remove entry for this pid diff = {} # Compare fields for f in allfields: oldval = pmap0[f] try: # Allow for this field not being present in the # new data. val = pdata[f] # Record changed fields if val != oldval: diff[f] = val except: val = oldval prow.append(val) if diff: updated = True changed[pid] = diff newpupils.append(prow) if added: REPORT.Info(_NEWPUPILS, klass=klass, pids=repr(added)) if changed: REPORT.Info(_PUPILCHANGES, klass=klass, data=repr(changed)) if oldpids: REPORT.Info(_OLDPUPILS, klass=klass, pids=repr(list(oldpids))) if updated: REPORT.Info(_REBUILDPUPILS, year=schoolyear) else: REPORT.Warn(_NOUPDATES, year=schoolyear) return # Build database table NEWPUPILS. # Use (CLASS, PSORT) as primary key, with additional index on PID. # This makes quite a small db (without rowid). indexes = db.makeTable2('NEWPUPILS', allfields, data=newpupils, force=True, pk=('CLASS', 'PSORT'), index=('PID', )) db.deleteTable('OLDPUPILS') if db.tableExists('PUPILS'): db.renameTable('PUPILS', 'OLDPUPILS') db.renameTable('NEWPUPILS', 'PUPILS') db.deleteIndexes('PUPILS') db.makeIndexes('PUPILS', indexes)