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)