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)