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