__init__.py

Go to the documentation of this file.

#!/usr/bin/python
# -*- coding: utf-8 -*- 
#
# Pafera app for our version of a learning management system. 
# You'll need to be in the teacher or student groups for most of 
# these APIs to work. 
#
# The focus is on having instant feedback, training good study 
# habits, and being able to run and access the system from 
# any internet connected device.
 
import importlib
import json
import os
import os.path
import random
import re
import shutil
import time
import traceback
import types
import uuid
import datetime
 
from pprint import pprint
 
import dateutil.parser
 
from flask import Response, send_file
from lockbydir.lockbydir import *
 
import markdown
import dateutil.parser
 
from pafera.types import *
from pafera.utils import *
from pafera.db import *
 
from apps.system.user import *
from apps.system.group import *
from apps.system.page import *
 
from apps.learn.initapp import *
from apps.learn.card import *
from apps.learn.schoolclass import *
from apps.learn.problem import *
from apps.learn.answer import *
from apps.learn.schooladministrator import *
from apps.learn.schoolsystemadministrator import *
from apps.learn.school import *
from apps.learn.teacher import *
from apps.learn.student import *
from apps.learn.challenge import *
from apps.learn.challengeresult import *
from apps.learn.studylist import *
from apps.learn.classgrade import *
 
# Prices for items purchasable by students
PRICES = {
  'chair':    1,
  'sofa':     2,
  'desk':     3,
  'TV':       4,
  'door':     5,
  'window':   6,
  'table':    7,
  'bed':      8,
}
 
# Convenience names to work with problems
PROBLEM_CARDFIELDS  = [
  'problemid',
  'answerid',
  'explanationid',
  'choice1id',
  'choice2id',
  'choice3id',
  'choice4id',
  'choice5id',
  'choice6id',
  'choice7id',
]
 
# =====================================================================
def cardapi(g):
  """Handles card management including setting child cards and problems.
  """
  results = {}
  
  try:
    LoadTranslation(g, 'learn', 'default', g.session.lang)
    
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    if command == 'search':
      g.db.RequireGroup(['teachers', 'students'], g.T.cantview)
      
      keyword   = StrV(d, 'keyword')
      title     = StrV(d, 'title')
      content   = StrV(d, 'content')
      flags     = IntV(d, 'flags')
      cardtype  = StrV(d, 'type')
      schoolid  = StrV(d, 'schoolid')
      classid   = StrV(d, 'classid')
      start     = IntV(d, 'start', 0, 99999999, 0)
      limit     = IntV(d, 'limit', 10, 1000, 100)
      
      conds   = []
      params  = []
      
      if keyword:
        escapedkeyword = EscapeSQL('%' + keyword + '%')
        
        conds.append("title LIKE ? OR description LIKE ? OR content LIKE ?")
        params.append(escapedkeyword)
        params.append(escapedkeyword)
        params.append(escapedkeyword)
      
      if title:
        conds.append("title LIKE ?")
        params.append(EscapeSQL('%' + title + '%'))
      
      if content:
        conds.append("content LIKE ?")
        params.append(EscapeSQL('%' + content + '%'))
      
      if flags:
        conds.append("flags & ?")
        params.append(flags)
        
      if cardtype:
        if cardtype == 'course':
          conds.append("flags & ?")
          params.append(CARD_COURSE)
      
      if conds:
        conds = "WHERE " + (" AND ").join(conds)
      else:
        conds = ""
      
      chosencards = []
      
      if schoolid or classid:
        if classid:
          c = g.db.Load(learn_schoolclass, FromShortCode(classid), fields = 'rid')
        else:
          c = g.db.Load(learn_school, FromShortCode(schoolid), fields = 'rid')
        
        for r in g.db.Linked(c, 
            learn_card, 
            fields = 'rid, image, sound, video, title, description', 
            start = start, 
            limit = limit
          ):
          chosencards.append(r.ToJSON('rid, image, sound, video, title, description, content'))
      
      cardobjs  = []
      
      cards = g.db.Find(
        learn_card, 
        conds, 
        params, 
        start     = start, 
        limit     = limit,
        orderby   = 'title, content',
        fields    = 'rid, image, sound, video, title, description, content'
      )
      
      for r in cards:
        cardobjs.append(r.ToJSON('rid, image, sound, video, title, description, content'))
        
      results['data']   = cardobjs
      results['count']  = len(cards)
      results['chosen'] = chosencards
    elif command == 'linked':
      g.db.RequireGroup(['teachers', 'students'], g.T.cantview)
      
      cardidcode  = StrV(d, 'cardidcode')
      schoolid    = StrV(d, 'schoolid')
      classid     = StrV(d, 'classid')
      studentid   = StrV(d, 'studentid')
      linktype    = IntV(d, 'linktype')
      
      c = 0
      
      if classid:
        classid = FromShortCode(classid)
        
        if (g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid)
            or g.db.HasLink(learn_schoolclass, classid, learn_student, g.session.userid)
          ):
          c = g.db.Load(learn_schoolclass, classid, 'rid')
        else:
          raise Exception(g.T.cantview)        
      elif schoolid:
        schoolid  = FromShortCode(schoolid)
        
        if g.db.HasLink(learn_school, schoolid, learn_schooladministrator, g.session.userid):
          c = g.db.Load(learn_school, schoolid, 'rid')
        else:
          raise Exception(g.T.cantview)        
      elif studentid:
        if FromShortCode(studentid) == g.session.userid:
          c = g.db.Load(learn_student, g.session.userid, 'userid')
        else:
          raise Exception(g.T.cantview)
      elif cardidcode:
        c = g.db.Load(learn_card, FromShortCode(cardidcode), 'rid')
        
        linktype  = CARD_CHILD
      else:
        c = g.db.Load(learn_teacher, g.session.userid, 'userid')
      
      ls  = []
      
      for r in g.db.Linked(c, learn_card, linktype):
        ls.append(r.ToJSON())
            
      results['data']   = ls
      results['count']  = len(ls)
    elif command == 'save':
      g.db.RequireGroup(['teachers'], g.T.cantchange)
      
      data    = DictV(d, 'data')
      idcode  = StrV(data, 'idcode')
      
      if not data:
        raise Exception(g.T.missingparams)
      
      data['cardtype']    = IntV(data, 'cardtype')
      data['title']       = StrV(data, 'title')
      data['description'] = StrV(data, 'description')
      data['image']       = StrV(data, 'image')
      data['sound']       = StrV(data, 'sound')
      data['video']       = StrV(data, 'video')
      data['markdown']    = StrV(data, 'markdown')
      
      if data['markdown']:
        data['html']        = markdown.markdown(data['markdown'])
      else:
        data['html']        = StrV(data, 'html')
        
      card  = 0
      
      if idcode:
        card  = g.db.Load(learn_card, FromShortCode(idcode))
      else:
        # Since answer cards are repeated so often, especially in math,
        # we save space by not have 1000 cards that simply say "3" over
        # and over again.
        if IntV(data, 'flags') & CARD_PROBLEM and not data['markdown'] and not data['html'] and not data['image'] and not data['sound'] and not data['video']:
          card  = g.db.Find(
            learn_card,
            'WHERE content = ?',
            [
              data['content'],
            ]
          )
            
          if card:
            card  = card[0]
            
        if not card:
          card  = learn_card()
      
      data['ownerid']     = card.ownerid if card.ownerid else g.session.userid
      data['editors']     = card.editors if card.editors else []
      
      card.Set(**data)
      
      # Only allow owners and editors to edit an existing card. 
      # Otherwise, we essentially do copy-on-write to save to a different card.
      if idcode:
        currentusercode = ToShortCode(g.session.userid)
        
        if g.session.userid != card.ownerid and currentusercode not in card.editors:
          card.ownerid  = g.session.userid
          card.rid      = 0;
          
      g.db.Save(card)
      g.db.Commit()
      
      results['data'] = card.ToJSON()
    elif command == 'setchildcards':
      teacher = g.db.Load(learn_teacher, g.session.userid, fields = 'userid, allowedcards')
      
      cardid        = StrV(d, 'cardid')
      childcardids  = ArrayV(d, 'childcardids')
      
      if not cardid:
        raise Exception(g.T.missingparams)
      
      if cardid not in teacher.allowedcards:
        raise Exception(g.T.cantchange)
      
      card = g.db.Load(learn_card, FromShortCode(cardid), 'rid, flags')
      
      if card.flags & CARD_LESSON:
        raise Exception(g.T.lessonscanthavechildcards)
      
      cardobjs   = []
      jsonobjs   = []
      
      for cid in childcardids:        
        childcard  = g.db.Load(learn_card, FromShortCode(cid), 'rid, image, title, description')        
        cardobjs.append(childcard)
        
        o = childcard.ToJSON('image, title, description')
        o['idcode'] = cid
        jsonobjs.append(o)
      
      if childcardids:
        g.db.LinkArray(card, cardobjs, CARD_CHILD)
        
        teacher.Set(
          allowedcards  = teacher.allowedcards + childcardids
        )
      else:
        g.db.Unlink(card, learn_card, CARD_CHILD)
        
      # Update all students to view the new child
      UpdateAllowedCards(g, teacher)
      
      for c in g.db.Linked(teacher, learn_schoolclass, 'userid'):
        for student in g.db.Linked(c, learn_student, 'userid, allowedcards'):
          if cardid in student.allowedcards:
            UpdateAllowedCards(g, student)
      
      g.db.Commit()
      
      results['data'] = jsonobjs
    elif command == 'setproblems':
      teacher = g.db.Load(learn_teacher, g.session.userid, fields = 'userid, allowedcards')
      
      cardid        = StrV(d, 'cardid')
      problemids    = ArrayV(d, 'problemids')
      
      if not cardid:
        raise Exception(g.T.missingparams)
      
      if cardid not in teacher.allowedcards:
        raise Exception(g.T.cantchange)
      
      card = g.db.Load(learn_card, FromShortCode(cardid), 'rid, flags')
      
      if not (card.flags & CARD_LESSON):
        raise Exception(g.T.onlylessonscancontainproblems)
      
      problemobjs   = []
      
      for cid in problemids:        
        problem  = g.db.Load(learn_problem, FromShortCode(cid), 'rid')        
        problemobjs.append(problem)
      
      if problemids:
        g.db.LinkArray(card, problemobjs, CARD_LINK_PROBLEM)
      else:
        g.db.Unlink(card, learn_problem, CARD_LINK_PROBLEM)
      
      g.db.Commit()
      
      results['data'] = GetAllProblems(g, card.rid, CARD_LINK_PROBLEM)
    elif command == 'delete':
      idcodes = ArrayV(d, 'ids')
      
      cards = g.db.LoadMany(learn_card, [FromShortCode(x) for x in idcodes], 'rid, ownerid, editors')
      
      for c in cards:
        if 'admins' in g.session['groups'] or g.session.userid == c.ownerid or ToShortCode(g.session.userid) in c.editors:
          g.db.Delete(c)
      
      g.db.Commit()
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def problemapi(g):
  """Manages problems, which are essentially a collection of cards.
  
  Note that you need to be a teacher in order to create or change 
  problems. Normal students can only view problems.
  """
  results = {}
  
  try:
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    if command == 'search':
      g.db.RequireGroup(['teachers'], g.T.cantview)
      
      keyword   = StrV(d, 'keyword')
      flags     = IntV(d, 'flags')
      start     = IntV(d, 'start', 0, 99999999, 0)
      limit     = IntV(d, 'limit', 10, 1000, 100)
      
      conds   = []
      params  = []
      
      if keyword:
        escapedkeyword = EscapeSQL('%' + keyword + '%')
        
        conds.append("content LIKE ?")
        params.append(escapedkeyword)
      
      if flags:
        conds.append("flags & ?")
        params.append(flags)
        
      if conds:
        conds = "WHERE " + (" AND ").join(conds)
      else:
        conds = ""
      
      problemobjs   = []
      problemcount  = 0
        
      cards = {}
      
      for r in g.db.Find(
          learn_card, 
          conds, 
          params, 
          limit     = 1000,
          orderby   = 'title, content',
          fields    = 'rid, image, sound, video, content',
        ):
        cards[r.rid]  = r.ToJSON('rid, image, sound, video, content')
      
      if cards:
        cardids       = list(cards.keys())
        
        questionmarks = ['?' for x in cardids]
        
        problems  = []
        
        questionmarks = ', '.join(questionmarks) 
        
        problems = g.db.Find(
          learn_problem,
          'WHERE problemid IN (' + questionmarks
            + ') OR answerid IN (' + questionmarks + ')',
          cardids + cardids,
          start   = start,
          limit   = limit,
        )
        
        problemcount  = len(problems)
        
        if problemcount:
          additionalcardids = []
          
          for r in problems:
            if r.problemid and not cards.get(r.problemid):
              additionalcardids.append(r.problemid)
              
            if r.answerid and not cards.get(r.answerid):
              additionalcardids.append(r.answerid)
              
          for r in g.db.LoadMany(
              learn_card, 
              additionalcardids,
              'rid, image, sound, video, content'
            ):
            cards[r.rid]  = r.ToJSON('rid, image, sound, video, content')
            
          for p in problems:
            o = {}
            
            if not p.problemid in cards or not p.answerid in cards:
              continue
            
            problemobj  = cards[p.problemid]
            problemobj['idcode']  = ToShortCode(p.problemid)
            
            answerobj  = cards[p.answerid]
            answerobj['idcode']   = ToShortCode(p.answerid)
            
            o['problem']  = problemobj
            o['answer']   = answerobj
            o['idcode']   = ToShortCode(p.rid)
            
            problemobjs.append(o)
        
      results['data']   = problemobjs
      results['count']  = problemcount
    elif command == 'save':
      g.db.RequireGroup(['teachers'], g.T.cantchange)
      
      data    = DictV(d, 'data')
      idcode  = StrV(data, 'idcode')
      
      if not data:
        raise Exception(g.T.missingparams)
      
      if idcode:
        p  = g.db.Load(learn_problem, FromShortCode(idcode))
      else:
        p  = learn_problem()
        p.Set(ownerid = g.session.userid)
        
      # Convert short codes to IDs
      for r in PROBLEM_CARDFIELDS:
        v = StrV(data, r)
        
        if v:
          data[r] = FromShortCode(v)
        else:
          data[r] = 0
          
      data['timelimit'] = IntV(data, 'timelimit') if IntV(data, 'timelimit') else 0
      data['points']    = IntV(data, 'points') if IntV(data, 'points') else 0
      data['flags']     = IntV(data, 'flags') if IntV(data, 'flags') else 0
      
      if not data['problemid'] or not data['answerid']:
        raise Exception(g.T.missingparams)
        
      # Only allow owners and editors to edit an existing problems. 
      # Otherwise, we essentially do copy-on-write to save to a different problem.
      if idcode:
        currentusercode = ToShortCode(g.session.userid)
        
        if g.session.userid != p.ownerid and currentusercode not in p.editors and not p.HasSameValues(data):
          data['ownerid'] = g.session.userid
          data['rid']     = 0
      
      p.Set(**data)
      
      g.db.Save(p)
      g.db.Commit()
      
      objjson = {
        'idcode':   ToShortCode(p.rid),
      }
      
      for r in PROBLEM_CARDFIELDS:
        v = getattr(p, r, None)
        
        if v:
          objjson[r]  = ToShortCode(v)
        else:
          objjson[r]  = ''
      
      results['data'] = objjson
    elif command == 'delete':
      idcodes = ArrayV(d, 'ids')
      
      problems = g.db.LoadMany(learn_problem, [FromShortCode(x) for x in idcodes], 'rid, ownerid, editors')
      
      for c in problems:
        if 'admins' in g.session['groups'] or g.session.userid == c.ownerid or ToShortCode(g.session.userid) in c.editors:
          g.db.Delete(c)
        else:
          raise Exception(g.T.cantdelete)
      
      g.db.Commit()
    elif command == 'cardproblems':
      allowedcards  = []
      
      # An user can be a teacher for one class but a student for another class, so we load all allowed cards
      try:        
        allowedcards  = g.db.Load(learn_teacher, g.session.userid, fields = 'allowedcards').allowedcards
        caneditcards  = 1
        allowedcards.extend(g.db.Load(learn_student, g.session.userid, fields = 'allowedcards').allowedcards)
      except Exception as e:
        allowedcards  = g.db.Load(learn_student, g.session.userid, fields = 'allowedcards').allowedcards
      
      cardidcodes   = ArrayV(d, 'cardidcodes')
      
      for r in cardidcodes:
        if r not in allowedcards:
          raise Exception(g.T.cantview)
      
      problemtype   = IntV(d, 'problemtype', 0, 99999999, CARD_LINK_PROBLEM)
      
      cardids = [FromShortCode(x) for x in cardidcodes]
      
      results['data'] = GetAllProblems(g, cardids, problemtype)
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def answersapi(g):
  """Used to view and submit answers for a class, which can be viewed
  in the teacher's classroom web interface. Functionality for putting 
  students into groups, purchasing items, or setting prompts for the 
  class are also included.
  """
  T = g.T
  results = {}
  
  try:
    LoadTranslation(g, 'learn', 'challenges', g.session.lang)
    
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    if command == 'changescores':
      classid   = StrV(d, 'classid')
      students  = ArrayV(d, 'students')
      
      if not classid or not students:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      for r in g.db.Find(
          learn_answer, 
          'WHERE userid IN (' + ', '.join(['?' for x in students]) + ')',
          [FromShortCode(x['idcode']) for x in students],
        ):
        
        idcode  = ToShortCode(r.userid)
        student = [x for x in students if x['idcode'] == idcode][0]
        
        right   = IntV(student, 'right')
        wrong   = IntV(student, 'wrong')
        strikes = IntV(student, 'strikes')
        bonus   = IntV(student, 'bonus')
        
        if not right and not wrong and not strikes and not bonus:
          raise Exception(T.missingparams)      
        
        if right:
          r.Set(numright  = r.numright + right)
        
        if wrong:
          r.Set(numwrong  = r.numwrong + wrong)
        
        if strikes:
          r.Set(numstrikes  = r.numstrikes + strikes)
        
        if bonus:
          r.Set(bonusscore  = r.bonusscore + bonus)
        
        r.UpdateScore()
        g.db.Update(r);
        
      g.db.Commit()      
    elif command == 'clearscoreadded':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      g.db.Execute('UPDATE learn_answer SET scoreadded = 0 WHERE classid = ?', FromShortCode(classid))
      g.db.Commit()
    elif command == 'save':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      if (not g.db.HasLink(learn_schoolclass, classid, learn_student, g.session.userid) 
          and not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid)
        ):
        raise Exception(g.T.cantchange)
      
      answer  = StrV(d, 'answer').strip()[:4096]
      
      o  = g.db.Find(
        learn_answer, 
        'WHERE userid = ? and classid = ?', 
        [g.session.userid, classid],
      )
      
      if o:
        o  = o[0]
      else:
        o  = learn_answer()
        
        u = g.db.Load(learn_student, g.session.userid)
        
        flags = ANSWER_ENABLE_COPY_PASTE
        
        if 'teachers' in g.session['groups']:
          flags |=  ANSWER_TEACHER
        
        o.Set(
          displayname   = u.displayname,
          flags         = flags,
          classid       = classid,
          userid        = g.session.userid,
        )
        o.AddHomeworkScores(g)
      
      if not o.flags & ANSWER_LOCKED:
        o.Set(
          classid     = classid,
          userid      = g.session.userid,
          answer      = answer,
          coderesult  = {},
        )
        g.db.Save(o)
        g.db.Commit()
    elif command == 'saveimage':
      classid   = StrV(d, 'classid')
      dataurl   = StrV(d, 'dataurl')
      
      if not classid or not dataurl:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      if (not g.db.HasLink(learn_schoolclass, classid, learn_student, g.session.userid) 
          and not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid)
        ):
        raise Exception(g.T.cantchange)
      
      useridcode  = ToShortCode(g.session.userid)
  
      imageformat, imagedata = dataurl.split(';base64,')
      
      imagedata = base64.b64decode(imagedata)
      
      imagefilebase = 'static/learn/drawings/' + CodeDir(useridcode)
      jpgfile       = imagefilebase + '.jpg'
      webpfile      = imagefilebase + '.webp'
      
      os.makedirs(os.path.split(imagefilebase)[0], exist_ok = True)
      
      with open(jpgfile, 'wb') as f:
        f.write(imagedata)
        
      subprocess.call(['convert', jpgfile, '-quality', '70', '-resize', '640x640>', webpfile])
      
      os.remove(jpgfile)
    elif command == 'get':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      o  = g.db.Find(
            learn_answer, 
            'WHERE userid = ? and classid = ?', 
            [g.session.userid, FromShortCode(classid)]
          )
      
      if o:
        results['data'] = o[0].answer
      else:
        results['data'] = ''
    elif command == 'list':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      ls  = []
      
      for r in g.db.Find(learn_answer, 'WHERE classid = ?', FromShortCode(classid)):
        ls.append(r.ToJSON())
        
      results['data'] = ls
    elif command == 'newclass':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      g.db.Delete(learn_answer, 'WHERE classid = ?', FromShortCode(classid))
      g.db.Commit()
    elif command == 'setgroup':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      idcode    = StrV(d, 'id')
      groupnum  = IntV(d, 'groupnum')
      
      rows  = g.db.Find(
        learn_answer, 
        'WHERE userid = ? AND classid = ?', 
        [FromShortCode(idcode), FromShortCode(classid)]
      )
      
      if rows:
        o = rows[0]
        
        o.Set(groupnum  = groupnum)
        
        g.db.Update(o);
        
        g.db.Commit()
    elif command == 'setgroups':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      students  = DictV(d, 'students')
      
      for r in g.db.Find(
          learn_answer,
          'WHERE classid = ? AND userid IN (' + ', '.join(['?' for x in students.keys()]) + ')',
          [FromShortCode(classid)] + [FromShortCode(x) for x in students.keys()]
        ):
        
        r.Set(groupnum  = students[ToShortCode(r.userid)])
        
        g.db.Update(r)              
      
      g.db.Commit()
    elif command == 'cleargroups':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      g.db.Execute('UPDATE learn_answer SET groupnum = 0 WHERE classid = ?', FromShortCode(classid))
      g.db.Commit()
    elif command == 'resetscores':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      g.db.Execute("""
        UPDATE learn_answer 
        SET numright  = 0,
          numwrong    = 0,
          numstrikes  = 0,
          bonusscore  = 0,
          scoreadded  = 0,
          groupnum    = 0,
          score       = 0
        WHERE classid = ?
      """,
        FromShortCode(classid)
      )
      g.db.Commit()
    elif command == 'getprompt':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      if (not g.db.HasLink(learn_schoolclass, classid, learn_student, g.session.userid) 
          and not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid)
        ):
        raise Exception(g.T.cantview)
      
      o  = g.db.Find(
        learn_answer, 
        'WHERE classid = ? AND userid = ?', 
        [classid, g.session.userid],
      )
      
      if o:
        o = o[0]
        
        results['prompt']     = o.prompt
        results['promptdata'] = o.promptdata
        results['coderesult'] = o.coderesult
        results['items']      = o.items
        results['score']      = o.score
        results['flags']      = o.flags        
      else:
        o = learn_answer()
        
        nameobj = 0
        
        if g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
          o.Set(
            displayname  = g.db.Load(learn_teacher, g.session.userid, 'displayname').displayname,
            flags        = ANSWER_TEACHER | ANSWER_ENABLE_COPY_PASTE,
          );
        else:
          o.Set(
            displayname = g.db.Load(learn_student, g.session.userid, 'displayname').displayname
          );
        
        o.Set(
          classid = classid,
          userid  = g.session.userid,
        )
        
        o.AddHomeworkScores(g)
        
        g.db.Insert(o)
        g.db.Commit()
        
        results['prompt']     = ''
        results['promptdata'] = {}
        results['coderesult'] = ''
        results['items']      = {}
        results['score']      = 0
        results['flags']      = 0
        
      results['challenges'] = GetCurrentChallenges(g, classid, CHALLENGE_CLASSWORK)        
    elif command == 'buyitem':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if (not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_student, g.session.userid) 
          and not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid)
        ):
        raise Exception(g.T.cantview)
      
      itemname  = StrV(d, 'data')
      quantity  = IntV(d, 'quantity')
      
      if itemname not in PRICES.keys():
        raise Exception(g.T.nothingfound)
      
      price = PRICES[itemname]
      
      o  = g.db.Find(
        learn_answer, 
        'WHERE classid = ? AND userid = ?', 
        [FromShortCode(classid), g.session.userid],
      )
      
      if o:
        o = o[0]
        
        # purchasing item
        if quantity > 0:
          total = price * quantity
          
          if o.score < total:
            raise Exception(g.T.notenoughmoney)
          
          if not o.items:
            o.items = {}
          
          if itemname not in o.items:
            o.items[itemname] = quantity
          else:
            o.items[itemname] += quantity
          
          if o.bonusscore > total:
            o.Set(
              bonusscore  = o.bonusscore - total,
              items       = o.items,
            )
          else:
            o.Set(
              numright    = o.numright - total,
              items       = o.items,
            )
            
          o.Set(score = o.score - total)
        # Using an item deletes it from the item store, and is controllable only
        # by teachers
        elif quantity < 0:
          g.db.RequireGroup('teachers', g.T.cantchange)
          
          o = g.db.Find(learn_answer, 'WHERE userid = ?', FromShortCode(StrV(d, 'useridcode')))
                        
          if not o:
            raise Exception(g.T.nothingfound)
          
          o = o[0]
          
          if itemname not in o.items or o.items[itemname] + quantity < 0:
            raise Exception(g.T.nothingfound)
          
          o.items[itemname] += quantity
          o.Set(items = o.items)
          
        g.db.Update(o)
        g.db.Commit()
        
        results['prompt']     = o.prompt
        results['promptdata'] = o.promptdata
        results['coderesult'] = o.coderesult
        results['items']      = o.items
        results['score']      = o.score
      else:
        results['prompt']     = ''
        results['promptdata'] = {}
        results['coderesult'] = ''
        results['items']      = {}
        results['score']      = 0
    elif command == 'setprompt':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      prompt      = StrV(d, 'prompt')
      promptdata  = DictV(d, 'promptdata')
      
      g.db.Execute("""
        UPDATE learn_answer 
        SET prompt = ?
        WHERE classid = ?
      """,
        [prompt, FromShortCode(classid)]
      )
      
      if promptdata:
        g.db.Execute("""
          UPDATE learn_answer 
          SET promptdata = ?
          WHERE classid = ?
        """,
          [json.dumps(promptdata), FromShortCode(classid)]
        )
        
      g.db.Commit()
    elif command == 'setcoderesult':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      studentid  = StrV(d, 'studentid')
      coderesult     = DictV(d, 'coderesult')
      
      if not studentid:
        raise Exception(T.missingparams)
      
      g.db.Execute("""
        UPDATE learn_answer 
        SET coderesult = ?
        WHERE userid  = ? AND classid = ?
      """,
        [
          json.dumps(coderesult),
          FromShortCode(studentid),
          FromShortCode(classid)
        ]
      )
        
      g.db.Commit()
    elif command == 'setflags':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      studentid  = StrV(d, 'studentid')
      flags          = IntV(d, 'flags')
      
      if not studentid:
        raise Exception(T.missingparams)
      
      g.db.Execute("""
        UPDATE learn_answer 
        SET flags = ?
        WHERE userid  = ? AND classid =  ?
      """,
        [
          flags,
          FromShortCode(studentid),
          FromShortCode(classid),
        ]
      )
        
      g.db.Commit()
    elif command == 'setpoints':
      challengeid = StrV(d, 'challengeid')
      studentid   = StrV(d, 'studentid')
      points      = IntV(d, 'points')
      
      if not challengeid:
        raise Exception(g.T.missingparams)
      
      challengeid = FromShortCode(challengeid)
      
      challengeobj  = g.db.Load(learn_challenge, challengeid)
      
      if not g.db.HasLink(learn_schoolclass, challengeobj.classid, learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      if studentid not in challengeobj.results:
        studentobj  = g.db.Load(learn_student, FromShortCode(studentid))
        challengeobj.results[studentid]  = [studentobj.displayname, 0, 0, 0, 0]
        
      challengeobj.results[studentid][4]  = points
      
      challengeobj.UpdateFields('results')
      
      g.db.Update(challengeobj)
      
      cls = g.db.Load(learn_schoolclass, challengeobj.classid)
      
      cls.UpdateGrades(g, FromShortCode(studentid))
      
      g.db.Commit()      
    elif command == 'delete':
      classid     = StrV(d, 'classid')
      studentid   = StrV(d, 'studentid')
      
      if not classid or not studentid:
        raise Exception(g.T.missingparams)
      
      classid   = FromShortCode(classid)
      studentid = FromShortCode(studentid)
      
      if not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      g.db.Execute("""
        DELETE FROM learn_answer 
        WHERE userid  = ? AND classid =  ?
      """,
        [
          studentid,
          classid,
        ]
      )
      
      g.db.Commit()
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def challengeapi(g):
  """Used to create and save challenge results. Note that only teachers
  can create challenges, and students can only save challenges which 
  have already been started in /learn/challenges.html.
  
  Also allows the user to view activity logs and statistics.
  """
  results = {}
  
  try:
    LoadTranslation(g, 'learn', 'default', g.session.lang)
    LoadTranslation(g, 'learn', 'challenges', g.session.lang)
    
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    if command == 'getgrades':
      classid   = StrV(d, 'classid')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      isstudent = g.db.HasLink(learn_schoolclass, classid, learn_student, g.session.userid)
      
      if not isstudent:
        if not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
          raise Exception(g.T.cantview)
      
      results['data']   = [
        r.ToJSON()
        for r in g.db.Find(
          learn_classgrade,
          'WHERE classid = ?',
          classid,
          orderby   = 'finalscore DESC, classpercent DESC, reviewpercent DESC, persistencepercent DESC'
        )
      ]
    elif command == 'getactivitylog' or command == 'getchallengeresults':
      classid       = StrV(d, 'classid')
      studentid     = StrV(d, 'studentid')
      challengetype = IntV(d, 'challengetype')
      starttime     = dateutil.parser.parse(StrV(d, 'starttime')).timestamp()
      endtime       = dateutil.parser.parse(StrV(d, 'endtime')).timestamp()
      
      if not studentid and not classid:
        raise Exception(g.T.missingparams)
      
      classid   = FromShortCode(classid)
      studentid = FromShortCode(studentid) if studentid else ''
      
      isstudent = g.db.HasLink(learn_schoolclass, classid, learn_student, g.session.userid)
      
      if not isstudent:
        if not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
          raise Exception(g.T.cantview)
        
      if not challengetype:
        challengetype = CHALLENGE_HOMEWORK
        
      if command == 'getchallengeresults':
        homeworks = []
        
        for r in g.db.Find(
            learn_challenge,
            'WHERE classid = ? AND starttime >= ? AND starttime <= ? AND challengetype = ?',
            [
              classid,
              starttime,
              endtime,
              challengetype,
            ],
            orderby   = 'endtime DESC',
            fields    = 'rid, title, results',
          ):
          homeworks.append(r.ToJSON('rid, title, results'))
          
        results['data'] = homeworks
        return results
      
      conds   = ['learn_challengeresult.classid = ?', 'learn_challengeresult.starttime >= ?', 'learn_challengeresult.starttime <= ?']
      params  = [classid, starttime, endtime]
      
      if studentid:
        conds.append('userid = ?')
        params.append(studentid)
        
      if challengetype:
        conds.append('challengetype = ?')
        params.append(challengetype)
      
      entries = g.db.Find(
          learn_challengeresult,
          '''JOIN learn_challenge ON learn_challengeresult.challengeid = learn_challenge.rid
          WHERE ''' + ' AND '.join(conds),
          params,
          orderby   = 'starttime DESC',
          fields    = 'learn_challengeresult.rid AS rid, learn_challengeresult.userid AS userid, learn_challengeresult.starttime AS starttime, learn_challenge.challenge AS challenge, learn_challenge.lessonids AS lessonids, score, percentright'
        )
            
      logresults  = []
      
      for r in entries:
        logresults.append({
          'studentid':    ToShortCode(r.userid),
          'starttime':    r.starttime,
          'challenge':    r.challenge,
          'lessons':      '',
          'lessonids':    r.lessonids,
          'score':        r.score,
          'percentright': r.percentright,
        })
        
      lessonids = set()
      
      for r in logresults:
        if r['lessonids']:
          for s in r['lessonids']:
            lessonids.add(FromShortCode(s))
            
      if lessonids:
        lessontitles  = GetCardTitles(g, list(lessonids))
        
        for r in logresults:
          if r['lessonids']:
            lessonnames = []
            
            for s in r['lessonids']:
              lessonnames.append(lessontitles[FromShortCode(s)])
              
            r['lessons']  = ', '.join(lessonnames)
        
      results['data']   = logresults
    elif command == 'search':
      keyword       = StrV(d, 'keyword')
      challenge     = StrV(d, 'challenge', keyword)
      title         = StrV(d, 'title', keyword)
      lessons       = StrV(d, 'lessons', keyword)
      classid       = StrV(d, 'classid')
      start         = IntV(d, 'start', 0, 99999999, 0)
      limit         = IntV(d, 'limit', 10, 1000, 100)
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      if not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
        raise Exception(g.T.cantview)
        
      conds   = []
      params  = [classid]
      
      if challenge:
        conds.append("challenge LIKE ?")
        params.append(EscapeSQL('%' + challenge + '%'))
      
      if title:
        conds.append("title LIKE ?")
        params.append(EscapeSQL('%' + json.dumps(title) + '%'))
      
      if lessons:
        conds.append("lessontitles LIKE ?")
        params.append(EscapeSQL('%' + lessons+ '%'))
      
      if conds:
        conds = "WHERE classid = ? AND (" + (" OR ").join(conds) + ')'
      else:
        conds = "WHERE classid = ? "
      
      challenges  = g.db.Find(
        learn_challenge, 
        conds, 
        params, 
        start     = start, 
        limit     = limit,
        orderby   = 'starttime DESC',
        fields    = 'rid, starttime, endtime, challenge, title, instructions, question, image, sound, video, files, lessonids, problemids, averagescore, averageright, averagepercent, percenttried, percentcomplete, minscore, minpercentage, numpoints, didnttrypenalty, numtries, numproblems, timelimit, challengetype, flags'
      )
      
      challengeobjs = []
      
      timeoffset  = int(g.request.cookies.get('timeoffset'))
      
      for r in challenges:
        r.OffsetTime(-timeoffset * 60)
        
        o = r.ToJSON()
        
        if o['lessonids']:
          o['lessons']  = [
            {
              'idcode':   ToShortCode(k),
              'title':    v,
            }
            for k, v in GetCardTitles(g, [FromShortCode(x) for x in o['lessonids']]).items()
          ]
        else:
          o['lessons']  = ''
        
        if o['problemids']:
          o['problems']  = GetAllProblems(g, [], CARD_LINK_PROBLEM, [FromShortCode(x) for x in o['problemids']])
        else:
          o['problems']  = ''
          
        challengeobjs.append(o)
        
      results['data']   = challengeobjs
      results['count']  = len(challenges)
    elif command == 'save':
      data    = DictV(d, 'data')
      
      challengetype       = IntV(data, 'challengetype')
      idcode              = StrV(data, 'idcode')
      classid             = StrV(data, 'classid')
      title               = StrV(data, 'title')
      instructions        = StrV(data, 'instructions')
      question            = StrV(data, 'question')
      image               = StrV(data, 'image')
      sound               = StrV(data, 'sound')
      video               = StrV(data, 'video')
      files               = ArrayV(data, 'files')
      challenge           = StrV(data, 'challenge')
      starttime           = StrV(data, 'starttime')
      endtime             = StrV(data, 'endtime')
      minscore            = IntV(data, 'minscore')
      minpercentage       = IntV(data, 'minpercentage')
      minpoints           = IntV(data, 'minpoints')
      lessonids           = ArrayV(data, 'lessonids')
      problemids          = ArrayV(data, 'problemids')
      numtries            = IntV(data, 'numtries', 1, 9999, 3)
      numpoints           = IntV(data, 'numpoints', 0, 100, 50)
      didnttrypenalty     = IntV(data, 'didnttrypenalty', 0, 100, 50)
      numproblems         = IntV(data, 'numproblems', 1, 20, 6)
      timelimit           = IntV(data, 'timelimit')
      flags               = IntV(data, 'flags')
      
      if not classid or not challenge or (starttime > endtime):
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
            
      if not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      if idcode:
        obj = g.db.Load(learn_challenge, FromShortCode(idcode))
      else:
        obj = learn_challenge()
        
        cls = g.db.Load(learn_schoolclass, classid)
        
        studentresults  = {
          ToShortCode(x.userid): [x.displayname, CHALLENGE_DIDNT_TRY, 0, 0, didnttrypenalty] for x in g.db.Linked(cls, learn_student, fields = 'userid, displayname')
        }
        
        obj.Set(results = studentresults)
        
      currenttime = time.time()
      endtimestamp  = dateutil.parser.parse(endtime).timestamp()
      
      flags |=  CHALLENGE_NEEDS_ANALYSIS
      
      if not challengetype:
        challengetype = CHALLENGE_HOMEWORK
        
      if challengetype == CHALLENGE_CLASSPARTICIPATION:
        challenge = 'Class Participation'
        
      if not title:
        title = {}
        
        if flags & CHALLENGE_USE_STUDYLIST:
          title[g.session.lang] = g.T.studylist
        elif lessonids:
          title[g.session.lang] = ', '.join(GetCardTitles(g, lessonids).keys())
        else:
          title[g.session.lang] = getattr(g.T, challenge.tolower().replace(' ', ''), g.T.challenge)
        
      obj.Set(
        classid           = classid,
        challengetype     = challengetype,
        title             = title,
        instructions      = instructions,
        question          = question,
        image             = image,
        sound             = sound,
        video             = video,
        files             = files,
        challenge         = challenge,
        starttime         = starttime,
        endtime           = endtime,
        minscore          = minscore,
        minpercentage     = minpercentage,
        minpoints         = minpoints,
        lessonids         = lessonids,
        problemids        = problemids,
        numtries          = numtries,
        numpoints         = numpoints,
        didnttrypenalty   = didnttrypenalty,
        numproblems       = numproblems,
        timelimit         = timelimit,
        flags             = flags,
      )
      
      timeoffset  = int(g.request.cookies.get('timeoffset'))
      
      obj.OffsetTime(timeoffset * 60)
      
      if idcode:
        g.db.Update(obj)
      else:
        g.db.Insert(obj)
        
      g.db.Commit()
      
      results['data'] = obj.ToJSON()
    elif command == 'saveresult' or command == 'saveunfinishedresult':
      data    = DictV(d, 'data')
      
      challengeresultid = StrV(data, 'challengeresultid')
      problemresults    = ArrayV(data, 'problemresults')
      score             = IntV(data, 'score')
      maximumscore      = IntV(data, 'maximumscore')
      percentright      = IntV(data, 'percentright')
      
      if not challengeresultid or not problemresults or not maximumscore:
        raise Exception(g.T.missingparams)
      
      challengeresult = g.db.Load(learn_challengeresult, FromShortCode(challengeresultid))
      
      if challengeresult.userid != g.session.userid:
        raise Exception(g.T.missingparams)
      
      isteacher = g.db.HasLink(learn_schoolclass, challengeresult.classid, learn_teacher, g.session.userid)
      
      challengeobj  = 0
      
      previousresults  = []
      
      if len(challengeresult.lessonids) == 1:
        previousresults  = g.db.Find(
          learn_challengeresult, 
          'WHERE userid = ? AND challenge = ? AND lessonids = ?', 
          [
            challengeresult.userid,
            challengeresult.challenge, 
            challengeresult.lessonids[0],
          ],
          fields  = 'score',
        )            
      elif challengeresult.challengeid:
        previousresults  = g.db.Find(
          learn_challengeresult, 
          'WHERE userid = ? AND challengeid = ?', 
          [
            g.session.userid,
            challengeresult.challengeid
          ],          
          fields  = 'score',
        )
          
      isnewhighscore    = 1
      previoushighscore = 0
      
      if previousresults:
        for r in previousresults:
          if r.score >= score:
            isnewhighscore  = 0
            
          if r.score > previoushighscore:
            previoushighscore = r.score
      
      results['isnewhighscore']     = data['score'] != 0 and isnewhighscore
      results['previoushighscore']  = previoushighscore
      
      flags = challengeresult.flags
      
      if command == 'saveunfinishedresult':
        flags &= CHALLENGERESULT_UNFINISHED
      else:
        flags = (flags & (~CHALLENGERESULT_UNFINISHED)) | CHALLENGERESULT_FINISHED
        
      currenttime = datetime.datetime.now()
      
      results['passed']             = 0
      results['remainingattempts']  = 0
      
      if challengeresult.challengeid:
        challengeobj = g.db.Load(
          learn_challenge, 
          challengeresult.challengeid, 
          'rid, classid, challenge, challengetype, lessonids, starttime, endtime, minscore, minpercentage, numtries, numpoints, results, flags'
        )
        
        if currenttime < challengeobj.starttime:
          raise Exception(g.T.challengenotstarted)
        
        if currenttime > challengeobj.endtime:
          raise Exception(g.T.challengealreadyover)
        
        results['remainingattempts']  = challengeobj.GetNumRemainingAttempts(g)
        
        currentid = ToShortCode(g.session.userid)
        
        if challengeobj.challenge == 'Write Answer':
          challengeresult.Set(
            problemresults  = problemresults,
            endtime         = currenttime,
          )
          g.db.Update(challengeresult)
          g.db.Commit()
          return results
        
        results['bonuspoints']  = 0
        
        answerobj     = 0
        
        if challengeobj.challengetype == CHALLENGE_CLASSWORK and challengeobj.challenge == 'Typing':
          answerobj  = g.db.Find(
            learn_answer,
            'WHERE classid = ? AND userid = ?',
            [challengeobj.classid, g.session.userid]
          )
          
          if answerobj:
            answerobj  = answerobj[0]
            
            answerobj.Set(bonusscore  = answerobj.bonusscore + score)
              
            answerobj.UpdateScore()
            
            g.db.Update(answerobj)
        
        currentname = 0
        
        if currentid in challengeobj.results:
          currentname = challengeobj.results[currentid][0]
        else:
          if isteacher:
            currentname = g.db.Load(learn_teacher, g.session.userid, 'displayname').displayname
          else:
            currentname = g.db.Load(learn_student, g.session.userid, 'displayname').displayname
        
        if ((challengeobj.minscore and score >= challengeobj.minscore) 
            or (challengeobj.minpercentage and percentright >= challengeobj.minpercentage) 
          ):
          flags = (flags & (~CHALLENGERESULT_UNFINISHED)) | CHALLENGERESULT_FINISHED | CHALLENGERESULT_PASSED
          
          results['bonuspoints']  = challengeobj.numpoints
          
          alreadypassed = 0
          
          for r in challengeobj.previousresults:
            if ((challengeobj.minscore and r.score > challengeobj.minscore) 
                or (challengeobj.minpercentage and r.percentright > challengeobj.minpercentage) 
              ):
              alreadypassed = 1
              break;
            
          if not alreadypassed and answerobj:
            answerobj.Set(
              bonusscore  = answerobj.bonusscore + challengeobj.numpoints
            )            
            
            answerobj.UpdateScore()
            
            g.db.Update(answerobj)
            
          results['passed'] = 1
          
          challengeresult.Set(scoreadded  = challengeobj.numpoints)
          
          challengeobj.results[currentid] = [currentname, CHALLENGE_COMPLETED, score, percentright, challengeobj.numpoints]
        else:
          challengeobj.results[currentid] = [currentname, CHALLENGE_FAILED, score, percentright, 0]
          
        challengeobj.Set(results  = challengeobj.results)
        
        g.db.Update(challengeobj)
        
        challengeresult.Set(lessonids = challengeobj.lessonids)
      
      challengeresult.Set(
        problemresults  = problemresults,
        endtime         = currenttime,
        timeused        = (currenttime - challengeresult.starttime).total_seconds(),
        score           = score,
        maximumscore    = maximumscore,
        percentright    = percentright,
        flags           = flags,
      )
      
      g.db.Update(challengeresult)
      
      if challengeobj and problemresults:
        for r in problemresults:
          if isinstance(r, dict) and 'id' in r:
            entry = g.db.Find(
              learn_studylist,
              'WHERE classid = ? AND userid = ? AND problemid = ?',
              [
               challengeobj.classid,
                g.session.userid,
                FromShortCode(r['id']),
              ]
            )
            
            if entry:
              entry = entry[0]
            else:
              entry = learn_studylist()
              entry.Set(
                classid   = challengeobj.classid,
                userid    = g.session.userid,
                problemid = FromShortCode(r['id']),
              )
            
            scorediff = -10
            
            if r['status'] == 'right':
              scorediff = r['timeremaining']
              
            if scorediff > 20:
              scorediff = 20
                  
            if not entry.results:
              entry.results = {}
            
            entry.results[int(currenttime.timestamp())] = scorediff
            
            entry.Set(
              score   = entry.score + scorediff,
              results = entry.results,
            )
              
            g.db.Save(entry)
          
      g.db.Commit()
      
      if challengeobj:
        challengeobj.Analyze(g)
        
        thisclass = g.db.Load(learn_schoolclass, challengeobj.classid)
        thisclass.UpdateGrades(g, g.session.userid)
      
    elif command == 'analyze':
      challengeid = StrV(d, 'challengeid')
      classid     = StrV(d, 'classid')
      
      if not challengeid or not classid:
        raise Exception(g.T.missingparams)
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      challenge = g.db.Load(learn_challenge, FromShortCode(challengeid))
      
      if challenge.flags & CHALLENGE_NEEDS_ANALYSIS:
        challenge.Analyze(g)
        g.db.Commit()
      
      results['data'] = challenge.ToJSON()
    elif command == 'getanswers':
      challengeid   = StrV(d, 'challengeid')
      start         = IntV(d, 'start', 0, 99999999, 0)
      limit         = IntV(d, 'limit', 10, 1000, 100)
      
      if not challengeid:
        raise Exception(g.T.missingparams)
      
      challengeid = FromShortCode(challengeid)
      
      challengeobj  = g.db.Load(learn_challenge, challengeid, 'rid, classid, results')
      
      if not g.db.HasLink(learn_schoolclass, challengeobj.classid, learn_teacher, g.session.userid):
        raise Exception(g.T.cantview)
      
      answers = {}
      
      for r in g.db.Find(
          learn_challengeresult,
          'WHERE challengeid = ?',
          challengeid,
          fields    = 'rid, userid, problemresults',
          orderby   = 'endtime',
        ):
        studentcode = ToShortCode(r.userid)
        answer      = r.problemresults
        points      = challengeobj.results[studentcode][4] if studentcode in challengeobj.results else 0
        
        answers[studentcode]  = {
          'points': points,
        }
        
        if isinstance(answer, list) and len(answer) == 5:
          answers[studentcode]['answer'] = {
            'idcode':     studentcode,
            'content':    answer[0],
            'image':      answer[1],
            'sound':      answer[2],
            'video':      answer[3],
            'files':      answer[4],
          }
        else:
          answers[studentcode]['answer'] = {
            'idcode':     studentcode,
            'content':    '',
            'image':      '',
            'sound':      '',
            'video':      '',
            'files':      '',
          }
      
      for r in g.db.Find(
          learn_challengeresult,
          'WHERE challengeid = ?',
          challengeid,
          fields    = 'rid, problemresults',
          orderby   = 'endtime'
        ):
        
        if r.problemresults and 'answer' in r.problemresults:
          answers[ToShortCode(r.userid)]['answer']  = r.problemresults['answer']
      
      results['data'] = answers
    elif command == 'delete':
      idcodes = ArrayV(d, 'ids')
      classid = StrV(d, 'classid')
      
      if not g.db.HasLink(learn_schoolclass, FromShortCode(classid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantdelete)
      
      g.db.Delete(
        learn_challenge, 
        'WHERE classid = ? AND rid IN (' + ', '.join(['?' for x in idcodes]) + ')',
        [FromShortCode(classid)] + [FromShortCode(x) for x in idcodes],
      )
      
      g.db.Commit()
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
      
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
 
# =====================================================================
def studylistapi(g):
  """Lets the user view study list entries, results, and delete unneeded
  problems.
  
  Note that in most cases, problems are automatically added to the study 
  list when a teacher updates a class, so deleting a problem that is 
  in the study list for that class will only take effect until the next 
  time the teacher changes the class.
  """
  
  results = {}
  
  try:
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    if command == 'search':
      studentid = StrV(d, 'studentid')
      classid   = StrV(d, 'classid')
      start     = IntV(d, 'start', 0, 99999999, 0)
      limit     = IntV(d, 'limit', 10, 1000, 100)
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      if studentid:
        studentid = FromShortCode(studentid)
      
      if studentid and studentid != g.session.userid and not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
        raise Exception(g.T.cantview)
      
      conds   = ['classid = ?', "userid = ?"]
      params  = [classid]
      
      if studentid:
        params.append(studentid)
      else:
        params.append(0)
      
      conds = "WHERE " + (" AND ").join(conds)
      
      entries  = g.db.Find(
        learn_studylist, 
        conds, 
        params, 
        start     = start if studentid else 0, 
        limit     = limit if studentid else 1,
        orderby   = 'score, problemid',
        fields    = 'rid, problemid, score, results'
      )
      
      if not studentid:
        if not entries:
          entry = learn_studylist()
          entry.Set(
            classid   = classid,
            userid    = 0,
            problemid = 0,
            results   = {},
          )
          g.db.Save(entry)
          g.db.Commit()
        else:
          entry = entries[0]
          
        if 'problemids' in entry.results:
          entryobjs = [
            {
              'idcode':     x,
              'problemid':  FromShortCode(x),
              'score':      0,
              'results':    {}
            } 
            for x in entry.results['problemids']
          ]
            
          results['cardids']  = entry.results['problemids']
        else:
          entryobjs = []          
          
      else:
        results['count']  = len(entries)
      
        entryobjs = [x.ToJSON('problemid, score, results') for x in entries]
        
        for x in entryobjs:
          x['idcode'] = ToShortCode(x['problemid'])
        
      
      if entryobjs:
        problems  = GetAllProblems(g, [], CARD_LINK_PROBLEM, [x['problemid'] for x in entryobjs])
        
        for p in problems:
          problemid = FromShortCode(p['idcode'])
          
          for e in entryobjs:
            if problemid == e['problemid']:
              e['problem']  = p
              break
      
      # Clear any deleted problems
      entryobjs = list(filter(lambda x: 'problem' in x, entryobjs))
      
      results['data']   = entryobjs if studentid else entryobjs[start:start + limit]
      
      if not studentid:
        results['count']  = len(entryobjs)
        
    elif command == 'save':
      classid     = StrV(d, 'classid')
      studentid   = StrV(d, 'studentid')
      cardids     = ArrayV(d, 'cardids')
      problemids  = ArrayV(d, 'problemids')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      if studentid:
        studentid = FromShortCode(studentid)
      
      isteacher = g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid)
      
      if (studentid and studentid != g.session.userid) or not isteacher:
        raise Exception(g.T.cantchange)
      
      if cardids:
        cardids = list(set(cardids))
        
        for r in GetAllProblems(g, [FromShortCode(x) for x in cardids], CARD_LINK_PROBLEM):
          problemids.append(r['idcode'])
      
      if problemids:
        cardids = list(set(problemids))
      
      if studentid:
        existingproblems  = [
          ToShortCode(x.problemid)
          for x in g.db.Find(
            learn_studylist,
            'WHERE classid = ? AND userid = ?',
            [
              classid, 
              studentid,
            ],
            fields  = 'problemid'
          )
        ]
            
        newproblems = filter(lambda x: x not in existingproblems, problemids)
        
        for s in newproblems:
          newentry  = learn_studylist()
          newentry.Set(
            classid   = classid,
            userid    = studentid,
            problemid = FromShortCode(s),
            results   = {},
          )
          g.db.Save(newentry)        
      else:
        entry = g.db.Find(
          learn_studylist, 
          'WHERE classid = ? AND userid = 0',
          classid,
        )
        
        if not entry:
          entry = learn_studylist()
          entry.Set(
            classid = classid,
            userid  = 0,
            results = {}
          )
        else:
          entry = entry[0]
          
          if 'problemids' in entry.results:
            problemids.extend(entry.results['problemids'])
            
            problemids  = list(set(problemids))
        
        entry.Set(
          problemid = 0,
          results   = {
            'cardids':    cardids,
            'problemids': problemids,
          }
        )
        
        g.db.Save(entry)
        
        cls = g.db.Load(learn_schoolclass, classid, 'rid')
        
        for r in g.db.Linked(cls, learn_student, fields = 'userid'):
          existingproblems  = [
            ToShortCode(x.problemid)
            for x in g.db.Find(
              learn_studylist,
              'WHERE classid = ? AND userid = ?',
              [
                classid, 
                r.userid,
              ],
              fields  = 'problemid'
            )
          ]
              
          newproblems = filter(lambda x: x not in existingproblems, problemids)
          
          for s in newproblems:
            newentry  = learn_studylist()
            newentry.Set(
              classid     = classid,
              userid      = r.userid,
              problemid   = FromShortCode(s),
              results     = {}
            )
            g.db.Save(newentry)
      
      g.db.Commit()
    elif command == 'delete':
      idcodes   = ArrayV(d, 'ids')
      classid   = StrV(d, 'classid')
      studentid = StrV(d, 'studentid')
      
      if studentid:        
        studentid = FromShortCode(studentid)
        
      classid = FromShortCode(classid)
        
      isteacher = g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid)
      
      if (
          (studentid and studentid != g.session.userid and not isteacher)
          or (not studentid and not isteacher)
        ):
        raise Exception(g.T.cantdelete)
      
      if studentid:
        
        g.db.Delete(
          learn_studylist, 
          'WHERE classid = ? AND userid = ? AND problemid IN (' + ', '.join(['?' for x in idcodes]) + ')',
          [classid] + [studentid] + [FromShortCode(x) for x in idcodes],
        )
        
        g.db.Commit()
      else:
        entry = g.db.Find(
          learn_studylist, 
          'WHERE classid = ? AND userid = 0',
          classid,
        )
        
        if entry and 'problemids' in entry[0].results:
          entry = entry[0]
          
          for r in idcodes:
            entry.results['problemids'].remove(r)
          
          entry.Set(results = entry.results)
          
          g.db.Save(entry)
          
          g.db.Delete(
            learn_studylist, 
            'WHERE classid = ? AND problemid IN (' + ', '.join(['?' for x in idcodes]) + ')',
            [classid] + [FromShortCode(x) for x in idcodes],
          )
          
          g.db.Commit()
      
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
      
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def schoolapi(g):
  """Lets new students find a school that is right for them and 
  handles general school management. You'll need to be a school
  administrator or school system administrator in order to create or 
  change school information.
  """
  results = {}
  
  try:
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    if command == 'search':
      keyword        = StrV(d, 'keyword')
      displayname     = StrV(d, 'displayname', keyword)
      description    = StrV(d, 'description', keyword)
      address        = StrV(d, 'address', keyword)
      phone          = StrV(d, 'phone', keyword)
      email          = StrV(d, 'email', keyword)
      contactinfo    = StrV(d, 'contactinfo', keyword)
      start          = IntV(d, 'start', 0, 99999999, 0)
      limit          = IntV(d, 'limit', 10, 1000, 20)
      
      conds   = []
      params  = []
      
      if description:
        conds.append("description LIKE ?")
        params.append(EscapeSQL('%' + description + '%'))
      
      if address:
        conds.append("address LIKE ?")
        params.append(EscapeSQL('%' + address + '%'))
      
      if phone:
        conds.append("phone LIKE ?")
        params.append(EscapeSQL('%' + phone + '%'))
      
      if email:
        conds.append("email LIKE ?")
        params.append(EscapeSQL('%' + email + '%'))
      
      if contactinfo:
        conds.append("contactinfo LIKE ?")
        params.append(EscapeSQL('%' + contactinfo + '%'))
      
      if conds:
        conds = "WHERE " + (" AND ").join(conds)
      else:
        conds = ""
      
      ls  = []
      
      cards = g.db.Find(
        learn_school, 
        conds, 
        params, 
        start     = start, 
        limit     = limit,
        orderby   = 'displayname'
      )
        
      for c in cards:
        ls.append(c.ToJSON())
        
      results['data']   = ls
      results['count']  = len(ls)
    elif command == 'load':
      schoolid     = StrV(d, 'schoolid')
      
      if not schoolid:
        raise Exception(T.missingparams)
      
      school  = g.db.Load(learn_school, FromShortCode(schoolid))
      
      if not g.db.HasLink(school, FromShortCode(schoolid), learn_schooladministrator, g.session.userid):
        raise Exception(T.cantview)
      
      results['data']   = o.ToJSON()
    elif command == 'getteachers':
      schoolid    = StrV(d, 'schoolid')
      if not schoolid:
        raise Exception(T.missingparams)
      
      school      = g.db.Load(learn_school, FromShortCode(schoolid))
      
      if not g.db.HasLink(school, FromShortCode(schoolid), learn_schooladministrator, g.session.userid):
        raise Exception(T.cantview)
      
      keyword        = StrV(d, 'keyword')
      displayname    = StrV(d, 'displayname', keyword)
      profile        = StrV(d, 'profile', keyword)
      phonenumber    = StrV(d, 'phonenumber', keyword)
      email          = StrV(d, 'email', keyword)
      start          = IntV(d, 'start', 0, 99999999, 0)
      limit          = IntV(d, 'limit', 10, 1000, 100)
      
      conds   = []
      params  = []
      
      if displayname:
        conds.append("displayname LIKE ?")
        params.append(EscapeSQL('%' + displayname + '%'))
      
      if profile:
        conds.append("profile LIKE ?")
        params.append(EscapeSQL('%' + profile + '%'))
      
      if phonenumber:
        conds.append("phonenumber LIKE ?")
        params.append(EscapeSQL('%' + phonenumber + '%'))
      
      if email:
        conds.append("email LIKE ?")
        params.append(EscapeSQL('%' + email + '%'))
      
      conds.append("schoolid = ?")
      params.append(FromShortCode(schoolid))
        
      if conds:
        conds = "WHERE " + (" AND ").join(conds)
      else:
        conds = ""
      
      ls  = []
      
      for r in g.db.Linked(school, 
          learn_teacher, 
          fields = 'userid, displayname, phonenumber',
          cond    = conds,
          params  = params,
          start   = start,
          limit   = limit,
        ):
        o = r.ToJSON('displayname, phonenumber')
        o['idcode'] = ToShortCode(r.userid)
        ls.append(o)
      
      results['data'] = ls
    elif command == 'save':
      newinfo  = d['data']
      
      if not V('newinfo', 'rid') and not g.db.HasGroup('school.system.administrators'):
        raise Exception(g.T.cantcreate)
      
      g.db.RequireGroup('school.administrators', g.T.cantchange)
      
      s = learn_school()
      s.Set(**(d['data']))
      g.db.Save(s)
      
      try:
        a = g.db.Load(learn_schooladministrator, g.session.userid)
      except Exception as e:
        a = learn_administrator()
        a.Set(userid  = g.session.userid)
        g.db.Insert(a)
        
      g.db.Link(s, a)
      
      if V('newinfo', 'rid'):
        for c in g.db.Linked(s, learn_schoolclass):
          c.Set(displayname  = s.displayname)
      
      g.db.Commit()
      
      results['data'] = s.rid
    elif command == 'delete':
      idcodes  = ArrayV(d, 'id')
      
      if not idcodes:
        raise Exception(g.T.missingparams)
        
      g.db.RequireGroup('school.system.administrators', g.T.cantdelete)
      
      for idcode in idcodes:
        s = g.db.Load(learn_school, FromShortCode(idcode))
        
        for a in g.db.Linked(s, learn_schoolclass):
          g.db.Unlink(s, a)
          
        for a in g.db.Linked(s, learn_schooladministrator):
          g.db.Unlink(s, a)
          
        for a in g.db.Linked(s, learn_schoolsystemadministrator):
          g.db.Unlink(s, a)
          
        for a in g.db.Linked(s, learn_teacher):
          g.db.Unlink(s, a)
          
        for a in g.db.Linked(s, learn_student):
          g.db.Unlink(s, a)
          
        g.db.Delete(s)
        
      g.db.Commit()
    elif command == 'getwaitingstudents':
      g.db.RequireGroup('school.administrators', g.T.cantview)
      
      schoolid  = StrV(d, 'schoolid')
      
      if not schoolid:
        raise Exception(g.T.missingparams)
      
      s = g.db.Load(learn_school, FromShortCode(schoolid))
      
      waitingstudents = []
      
      for r in g.db.Linked(s, learn_student, STUDENT_APPLIED, fields = 'userid, displayname, phonenumber'):
        o = r.ToJSON()
        o['idcode']  = ToShortCode(r.userid)
        waitingstudents.append(o)
        
      results['data']   = waitingstudents
      results['count']  = len(waitingstudents)
    elif command == 'approvestudent':
      g.db.RequireGroup('school.administrators', g.T.cantview)
      
      schoolid    = StrV(d, 'schoolid')
      studentid   = StrV(d, 'studentid')
      accept      = IntV(d, 'accept')
      
      if not schoolid or not studentid:
        raise Exception(g.T.missingparams)
      
      school    = g.db.Load(learn_school, FromShortCode(schoolid))
      student   = g.db.Load(learn_student, FromShortCode(studentid))
      
      g.db.Unlink(school, student, STUDENT_APPLIED)
      
      if accept:
        g.db.Link(school, student)      
      
      g.db.Commit()
    elif command == 'getwaitingteachers':
      g.db.RequireGroup('school.administrators', g.T.cantview)
      
      schoolid  = StrV(d, 'schoolid')
      
      if not schoolid:
        raise Exception(g.T.missingparams)
      
      s = g.db.Load(learn_school, FromShortCode(schoolid))
      
      waitingstudents = []
      
      for r in g.db.Linked(s, learn_teacher, TEACHER_APPLIED, fields = 'userid, displayname, phonenumber'):
        o = r.ToJSON()
        o['idcode']  = ToShortCode(r.userid)
        waitingstudents.append(o)
        
      results['data']   = waitingstudents
      results['count']  = len(waitingstudents)
    elif command == 'approveteacher':
      g.db.RequireGroup('school.administrators', g.T.cantview)
      
      schoolid    = StrV(d, 'schoolid')
      teacherid   = StrV(d, 'teacherid')
      accept      = IntV(d, 'accept')
      
      if not schoolid or not teacherid:
        raise Exception(g.T.missingparams)
      
      school    = g.db.Load(learn_school, FromShortCode(schoolid))
      teacher   = g.db.Load(learn_teacher, FromShortCode(teacherid))
      
      g.db.Unlink(school, teacherid, TEACHER_APPLIED)
      
      if accept:
        g.db.Link(school, teacherid)      
      
      g.db.Commit()
    elif command == 'getlinked':
      admin = g.db.Load(learn_schooladministrator, g.session.userid)
      
      results['data'] = [
        x.ToJSON('rid, icon, displayname, description, phone')
        for x in g.db.Linked(
          admin, 
          learn_school, 
          fields = 'rid, icon, displayname, description, phone'
        )
      ]
      results['count']  = len(results['data'])
    elif command == 'addusers':
      admin = g.db.Load(learn_schooladministrator, g.session.userid)
            
      schoolid          = StrV(d, 'schoolid')
      studentids        = ArrayV(d, 'studentids')
      teacherids        = ArrayV(d, 'teacherids')
      administratorids  = ArrayV(d, 'administratorids')
      
      if not schoolid or (not studentids and not teacherids and not administratorids):
        raise Exception(g.T.missingparams)
      
      school  = g.db.Load(learn_school, FromShortCode(schoolid), fields = 'rid')
      
      administratorgroup  = g.db.Find(system_group, 'WHERE groupname = ?', 'school.administrators')[0]
      
      if studentids:
        studentgroup        = g.db.Find(system_group, 'WHERE groupname = ?', 'students')[0]
        
        newusers  = g.db.Find(
          system_user,
          'WHERE rid IN (' + ', '.join(['?' for x in studentids]) + ')',
          [
            FromShortCode(x) for x in studentids
          ],
          fields = 'rid, displayname'
        )
          
        for r in newusers:
          g.db.Link(r, studentgroup)
          
        existingstudents = list(
          g.db.Find(
            learn_student,
            'WHERE userid IN (' + ', '.join(['?' for x in newusers]) + ')',
            [
              x.rid for x in newusers
            ],
            fields = 'rid'
          )
        )
          
        newuserids          = set([x.rid for x in newusers])
        existingstudentids  = set([x.rid for x in existingstudents])
        
        studentstocreate = list(newuserids - existingstudentids)
        
        for r in studentstocreate:
          for s in newusers:
            if s.rid == r:
              newuser = learn_student()
              newuser.Set(
                userid    = s.rid,
                displayname = s.displayname,
              )
              g.db.Save(newuser)
              
              existingstudents.append(newuser)
              break
        
        for r in existingstudents:
          g.db.Link(r, school)
      
      if teacherids:
        teachergroup        = g.db.Find(system_group, 'WHERE groupname = ?', 'teachers')[0]
        
        newusers  = g.db.Find(
          system_user,
          'WHERE rid IN (' + ', '.join(['?' for x in teacherids]) + ')',
          [
            FromShortCode(x) for x in teacherids
          ],
          fields = 'rid, displayname'
        )
          
        for r in newusers:
          g.db.Link(r, teachergroup)
          
        existingteachers = list(
          g.db.Find(
            learn_teacher,
            'WHERE userid IN (' + ', '.join(['?' for x in newusers]) + ')',
            [
              x.rid for x in newusers
            ],
            fields = 'rid'
          )
        )
          
        newuserids          = set([x.rid for x in newusers])
        existingteacherids  = set([x.rid for x in existingteachers])
        
        teacherstocreate    = list(newuserids - existingteacherids)
        
        for r in teacherstocreate:
          for s in newusers:
            if s.rid == r:
              newuser = learn_teacher()
              newuser.Set(
                userid    = s.rid,
                displayname = s.displayname,
              )
              g.db.Save(newuser)
              
              existingteachers.append(newuser)
              break
        
        for r in existingteachers:
          g.db.Link(r, school)
 
      if administratorids:
        administratorgroup        = g.db.Find(system_group, 'WHERE groupname = ?', 'school.administrators')[0]
        
        newusers  = g.db.Find(
          system_user,
          'WHERE rid IN (' + ', '.join(['?' for x in administratorids]) + ')',
          [
            FromShortCode(x) for x in administratorids
          ],
          fields = 'rid, displayname'
        )
          
        for r in newusers:
          g.db.Link(r, administratorgroup)
          
        existingadministrators = list(
          g.db.Find(
            learn_schooladministrator,
            'WHERE userid IN (' + ', '.join(['?' for x in newusers]) + ')',
            [
              x.rid for x in newusers
            ],
            fields = 'rid'
          )
        )
          
        newuserids                = set([x.rid for x in newusers])
        existingadministratorids  = set([x.rid for x in existingadministrators])
        
        administratorstocreate    = list(newuserids - existingadministratorids)
        
        for r in administratorstocreate:
          for s in newusers:
            if s.rid == r:
              newuser = learn_schooladministrator()
              newuser.Set(
                userid      = s.rid,
                displayname = s.displayname,
              )
              g.db.Save(newuser)
              
              existingadministrators.append(newuser)
              break
        
        for r in existingadministrators:
          g.db.Link(r, school)
          
      g.db.Commit()
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def classapi(g):
  """Handles the management of classes for a school. You'll need to be 
  an administrator for that school in order to make any changes to the 
  class information. If you're a teacher, you can change autohomework 
  and other class settings here.
  """
  results = {}
  
  try:
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    if command == 'search':
      keyword        = StrV(d, 'keyword')
      coursenum      = StrV(d, 'coursenum', keyword)
      displayname      = StrV(d, 'displayname', keyword)
      description    = StrV(d, 'description', keyword)
      schedule       = StrV(d, 'schedule', keyword)
      teachers       = StrV(d, 'teachers', keyword)
      schoolid       = StrV(d, 'schoolid')
      start          = IntV(d, 'start', 0, 99999999, 0)
      limit          = IntV(d, 'limit', 10, 1000, 100)
      
      conds   = []
      params  = []
      
      if coursenum:
        conds.append("coursenum LIKE ?")
        params.append(EscapeSQL('%' + coursenum + '%'))
      
      if displayname:
        conds.append("displayname LIKE ?")
        params.append(EscapeSQL('%' + displayname + '%'))
      
      if description:
        conds.append("description LIKE ?")
        params.append(EscapeSQL('%' + description + '%'))
      
      if description:
        conds.append("description LIKE ?")
        params.append(EscapeSQL('%' + description + '%'))
      
      if teachers:
        conds.append("teachers LIKE ?")
        params.append(EscapeSQL('%' + teachers + '%'))
      
      if schoolid:
        conds.append("schoolid = ?")
        params.append(FromShortCode(schoolid))
        
      if conds:
        conds = "WHERE " + (" AND ").join(conds)
      else:
        conds = ""
      
      ls  = []
      
      cards = g.db.Find(
        learn_schoolclass, 
        conds, 
        params, 
        start     = start, 
        limit     = limit,
        orderby   = 'coursenum, schedule, teachers'
      )
        
      for c in cards:
        ls.append(c.ToJSON())
        
      results['data']   = ls
      results['count']  = len(ls)
    elif command == 'load':
      classid     = StrV(d, 'classid')
      
      r  = g.db.Load(learn_schoolclass, FromShortCode(schoolid))
      results['data'] = r.ToJSON()
    elif command == 'get':
      g.db.RequireGroup(['teachers', 'students'], g.T.cantview)
      
      try:
        if g.db.HasGroup('teachers'):
          u = g.db.Load(learn_teacher, g.session.userid)
        else:
          u = g.db.Load(learn_student, g.session.userid)
        
        ls  = []
        
        for r in g.db.Linked(u, learn_schoolclass):
          ls.append(r.ToJSON())
              
        results['data'] = ls
      except Exception as e:
        results['data'] = []
    elif command == 'save':
      newinfo     = d['data']
      schoolid    = StrV(d, 'schoolid')
      
      if not schoolid or not newinfo:
        raise Exception(T.missingparams)
      
      if (not V('newinfo', 'rid') 
          and not g.db.HasLink(learn_school, FromShortCode(schoolid), learn_schooladministrator, g.session.userid)
        ):
        raise Exception(g.T.cantcreate)
      
      s = g.db.Load(learn_school, FromShortCode(schoolid), fields = 'rid, displayname')
      
      c = learn_schoolclass()
      c.Set(**newinfo)
      c.Set(
        schoolid    = s.rid,
        displayname  = s.displayname,
      )
      g.db.Save(c)
      
      g.db.Link(s, c)
      
      g.db.Commit()
      
      results['data'] = s.rid
    elif command == 'delete':
      idcodes = ArrayV(d, 'ids')
      
      if not idcodes:
        raise Exception(g.T.missingparams)
      
      schooladministrator = g.db.Load(learn_schooladministrator, g.session.userid)
      
      administeredschools = [x.rid for x in g.db.Linked(schooladministrator, learn_school, fields = 'rid')]
      
      for idcode in idcodes:
        s = g.db.Load(learn_schoolclass, FromShortCode(idcode))
        
        if s.schoolid not in administeredschools:
          continue
        
        for a in g.db.Linked(s, learn_teacher):
          g.db.Unlink(s, a)
          
        for a in g.db.Linked(s, learn_student):
          g.db.Unlink(s, a)
          
        for a in g.db.Linked(s, learn_school):
          g.db.Unlink(s, a)
          
        g.db.Delete(s)
        
      g.db.Commit()
    elif command == 'setteachers':
      schoolid      = StrV(d, 'schoolid')
      classid       = StrV(d, 'classid')
      teachers      = ArrayV(d, 'teachers')
      
      if not classid or not teachers or not schoolid:
        raise Exception(g.T.missingparams)
      
      if not g.session.userid:
        raise Exception(g.T.needlogin)
      
      if not g.db.HasLink(learn_school, FromShortCode(schoolid), learn_schooladministrator, g.session.userid):
        raise Exception(g.T.cantview)
      
      c = g.db.Load(learn_schoolclass, FromShortCode(classid))
      
      displaynames  = {}
      
      teacherobjs   = list(
        g.db.LoadMany(
          learn_teacher, 
          [FromShortCode(x) for x in teachers],
          'userid, displayname',
        )
      )
          
      for r in teacherobjs:
        for k, v in r.displayname.items():
          if k in displaynames:
            displaynames[k] = displaynames[k] + ', ' + v
          else:
            displaynames[k] = v
            
      c.Set(teachers  = displaynames)
        
      g.db.LinkArray(c, teacherobjs)
      
      g.db.Commit()
    elif command == 'setcurriculum':
      schoolid      = StrV(d, 'schoolid')
      classid       = StrV(d, 'classid')
      cardids       = ArrayV(d, 'cardids')
      
      if not classid or not cardids:
        raise Exception(g.T.missingparams)
      
      if not schoolid:
        raise Exception(g.T.missingparams)
    
      if not g.session.userid:
        raise Exception(g.T.needlogin)
      
      if not g.db.HasLink(learn_school, FromShortCode(schoolid), learn_teacher, g.session.userid):
        raise Exception(g.T.cantview)
      
      c = g.db.Load(learn_schoolclass, FromShortCode(classid))
      
      students  = g.db.Linked(c, learn_student, fields = 'userid')
      teachers  = g.db.Linked(c, learn_teacher, fields = 'userid')
      
      previouscards   = g.db.Linked(c, learn_card, fields = 'rid')
      previouscardids = [x.rid for x in previouscards]
      
      for p in previouscards:
        if ToShortCode(p.rid) not in cardids:
          for student in students:
            g.db.Unlink(p, student)
            
          for teacher in teachers:
            g.db.Unlink(p, teacher)
      
      cardobjs   = []
      
      for cid in cardids:
        
        card  = g.db.Load(learn_card, FromShortCode(cid), 'rid')
        
        cardobjs.append(card)
        
        if card.rid not in previouscardids:
          for student in students:
            g.db.Link(card, student)
            
          for teacher in teachers:
            g.db.Link(card, teacher)
        
      g.db.LinkArray(c, cardobjs)      
      
      # Update access for all affected students and teachers
      updatecardobjs  = list(students) + list(teachers)
      
      if updatecardobjs:
        for r in updatecardobjs:
          UpdateAllowedCards(g, r)
      
      g.db.Commit()
    elif command == 'setstudents':
      schoolid      = StrV(d, 'schoolid')
      classid       = StrV(d, 'classid')
      students      = ArrayV(d, 'students')
      
      if not classid or not students:
        raise Exception(g.T.missingparams)
      
      if not schoolid:
        raise Exception(g.T.missingparams)
    
      if not g.session.userid:
        raise Exception(g.T.needlogin)
      
      if not g.db.HasLink(learn_school, FromShortCode(schoolid), learn_schooladministrator, g.session.userid):
        raise Exception(g.T.cantview)
      
      c = g.db.Load(learn_schoolclass, FromShortCode(classid))
      
      studentobjs   = list(
        g.db.LoadMany(
          learn_student, 
          [FromShortCode(x) for x in students],
          'userid',
        )
      )
        
      g.db.LinkArray(c, studentobjs)
      
      c.Set(numstudents = len(studentobjs))
      
      g.db.Update(c)
      
      courses = g.db.Linked(c, learn_card, 'rid')
      
      for r in studentobjs:
        for s in courses:
          g.db.Link(r, s)
      
      for r in studentobjs:
        UpdateAllowedCards(g, r)
        
      g.db.Commit()
    elif command == 'setautohomework':
      classid             = StrV(d, 'classid')
      challengetypes      = ArrayV(d, 'challengetypes')
      lessonids           = ArrayV(d, 'lessonids')
      problemids          = ArrayV(d, 'problemids')
      minscore            = IntV(d, 'minscore')
      minpercentage       = IntV(d, 'minpercentage')
      numpoints           = IntV(d, 'numpoints')
      problemselect       = StrV(d, 'problemselect')
      numtries            = IntV(d, 'numtries')
      numproblems         = IntV(d, 'numproblems')
      timelimit           = IntV(d, 'timelimit')
      didnttrypenalty     = IntV(d, 'didnttrypenalty')
      usehomeworkmanager  = IntV(d, 'usehomeworkmanager')
      usestudylist        = IntV(d, 'usestudylist')
      timezone            = IntV(d, 'timezone')
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      classid = FromShortCode(classid)
      
      if not g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid):
        raise Exception(g.T.cantchange)
      
      c = g.db.Load(learn_schoolclass, classid, 'rid, autohomework, flags')
      
      if usehomeworkmanager:
        c.flags = c.flags | CLASS_AUTO_HOMEWORK
      else:
        c.flags = c.flags & (~CLASS_AUTO_HOMEWORK)
        
      if usestudylist:
        c.flags = c.flags | CLASS_REVIEW_STUDYLIST
      else:
        c.flags = c.flags & (~CLASS_REVIEW_STUDYLIST)
        
      lessons = [];
        
      if lessonids:
        for k, v in GetCardTitles(g, [FromShortCode(x) for x in lessonids]).items():
          lessons.append({
            'idcode':   ToShortCode(k),
            'title':    v,
          })
      
      problems  = []
      
      if problemids:
        problems  = GetAllProblems(g, [], CARD_LINK_PROBLEM, [FromShortCode(x) for x in problemids])
          
      c.Set(
        resettime     = timezone,
        flags         = c.flags,
        autohomework  = {
          'challengetypes':   challengetypes,
          'lessonids':        lessonids,
          'problemids':       problemids,
          'lessons':          lessons,
          'problems':         problems,
          'minscore':         minscore,
          'minpercentage':    minpercentage,
          'numpoints':        numpoints,
          'problemselect':    problemselect,
          'numtries':         numtries,
          'numproblems':      numproblems,
          'timelimit':        timelimit,          
          'didnttrypenalty':  didnttrypenalty,
        },
      )
        
      g.db.Update(c);
 
      g.db.Commit()
      
      results['data'] = c.ToJSON('flags, resettime, autohomework')
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def studentapi(g):
  """Handles student management. Only a school administrator can use 
  this API.
  """
  results = {}
  
  try:
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    schoolid       = StrV(d, 'schoolid')
    
    if not schoolid:
      raise Exception(g.T.missingparams)
    
    if not g.session.userid:
      raise Exception(g.T.needlogin)
    
    if not g.db.HasLink(learn_school, FromShortCode(schoolid), learn_schooladministrator, g.session.userid):
      raise Exception(g.T.cantview)      
    
    if command == 'search':
      displayname      = StrV(d, 'displayname')
      phonenumber    = StrV(d, 'phonenumber')
      classid        = StrV(d, 'classid')
      start          = IntV(d, 'start', 0, 99999999, 0)
      limit          = IntV(d, 'limit', 10, 1000, 100)
      
      conds   = []
      params  = []
      
      if displayname:
        conds.append("displayname LIKE ?")
        params.append(EscapeSQL('%' + displayname + '%'))
      
      if phonenumber:
        conds.append("phonenumber LIKE ?")
        params.append(EscapeSQL('%' + phonenumber + '%'))
      
      if conds:
        conds = "WHERE " + (" AND ").join(conds)
      else:
        conds = ""
        
      school  = g.db.Load(learn_school, FromShortCode(schoolid), fields = 'rid')
      
      studentobjs  = []
      chosenobjs   = []
      chosenids    = []
      
      if classid:
        ls  = []
        
        c = g.db.Load(learn_schoolclass, FromShortCode(classid))
        
        for r in g.db.Linked(c, learn_student, fields = 'userid, displayname, phonenumber, grade, coins, items'):
          o = r.ToJSON('displayname, phonenumber, grade, coins, items')
          o['idcode'] = ToShortCode(r.userid)
          chosenobjs.append(o)
          chosenids.append(r.userid)
            
      students  = g.db.Linked(
        school,
        learn_student, 
        0,
        start, 
        limit,
        'userid, displayname, phonenumber, grade, coins, items',
        'phonenumber',
        conds, 
        params, 
      )
        
      for r in students:
        if r.userid in chosenids:
          continue
        
        o = r.ToJSON('displayname, phonenumber, grade, coins, items')
        o['headshot'] = ToShortCode(r.userid)
        o['idcode']   = o['headshot']
        studentobjs.append(o)
        
      results['data']   = studentobjs
      results['count']  = len(students)
      results['chosen'] = chosenobjs
    elif command == 'expel':
      idcode  = StrV(d, 'id')
      
      if not idcode:
        raise Exception(g.T.missingparams)
      
      school  = g.db.Load(learn_school, FromShortCode(schoolid), fields = 'rid')
      student = g.db.Load(learn_student, FromShortCode(idcode), fields = 'userid')
        
      g.db.Unlink(school, student)
      g.db.Commit()
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def teacherapi(g):
  """Handles teacher management. Only a school administrator can use 
  this API.
  """
  results = {}
  
  try:
    d = json.loads(g.request.get_data())
    
    command = StrV(d, 'command')
    
    schoolid       = StrV(d, 'schoolid')
    
    if not schoolid:
      raise Exception(g.T.missingparams)
    
    if not g.session.userid:
      raise Exception(g.T.needlogin)
    
    if not g.db.HasLink(learn_school, FromShortCode(schoolid), learn_schooladministrator, g.session.userid):
      raise Exception(g.T.cantview)
    
    if command == 'search':
      keyword        = StrV(d, 'keyword')
      displayname    = StrV(d, 'displayname', keyword)
      phonenumber    = StrV(d, 'phonenumber', keyword)
      email          = StrV(d, 'email', keyword)
      classid        = StrV(d, 'classid')
      start          = IntV(d, 'start', 0, 99999999, 0)
      limit          = IntV(d, 'limit', 10, 1000, 100)
      
      conds   = []
      params  = []
      
      if displayname:
        conds.append("displayname LIKE ?")
        params.append(EscapeSQL('%' + displayname + '%'))
      
      if phonenumber:
        conds.append("phonenumber LIKE ?")
        params.append(EscapeSQL('%' + phonenumber + '%'))
      
      if email:
        conds.append("phonenumber LIKE ?")
        params.append(EscapeSQL('%' + phonenumber + '%'))
      
      if conds:
        conds = "WHERE " + (" AND ").join(conds)
      else:
        conds = ""
      
      school  = g.db.Load(learn_school, FromShortCode(schoolid), fields = 'rid')
      
      teacherobjs  = []
      chosenobjs   = []
      chosenids    = []
      
      if classid:
        ls  = []
        
        c = g.db.Load(learn_schoolclass, FromShortCode(classid))
        
        for r in g.db.Linked(c, learn_teacher, fields = 'userid, displayname, phonenumber, email'):
          o = r.ToJSON('displayname, phonenumber, email')
          o['idcode'] = ToShortCode(r.userid)
          chosenobjs.append(o)
          chosenids.append(r.userid)
            
      teachers  = g.db.Linked(
        school,
        learn_teacher, 
        0,
        start, 
        limit,
        'userid, displayname, phonenumber',
        'phonenumber',
        conds, 
        params, 
      )
        
      for r in teachers:
        if r.userid in chosenids:
          continue
        
        o = r.ToJSON('displayname, phonenumber')
        o['headshot'] = ToShortCode(r.userid)
        o['idcode']   = o['headshot']
        teacherobjs.append(o)
        
      results['data']   = teacherobjs
      results['count']  = len(teachers)
      results['chosen'] = chosenobjs
    elif command == 'expel':
      idcode  = StrV(d, 'id')
      
      if not idcode:
        raise Exception(g.T.missingparams)
      
      school  = g.db.Load(learn_school, FromShortCode(schoolid), fields = 'rid')
      teacher = g.db.Load(learn_teacher, FromShortCode(idcode), fields = 'userid')
        
      g.db.Unlink(school, teacher)
      g.db.Commit()
    else:
      results['error']  = f'{g.T.cantunderstand} "{command}"'
  except Exception as e:
    traceback.print_exc()
    results['error']  = str(e)
  
  return results
 
# =====================================================================
def texttomp3(g):
  """A simple text to speech API for students and teachers to play 
  around with. You'll need to install the festival speech synthesis
  program on your server to use this.
  """
  if not g.urlargs:
    abort(500)
    
  if ('students' not in g.session['groups'] 
      and 'teachers' not in g.session['groups']
    ):
    raise Exception(g.T.cantview)
  
  text  = g.urlargs[0]
  
  filename  = re.sub(r'\W', '_', text)
  
  cachedir  = '/srv/flasksite/libs/texttomp3'
  
  textfile  = cachedir + '/' + filename + '.txt'
  mp3file   = cachedir + '/' + filename + '.mp3'
  
  if os.path.exists(mp3file):
    return send_file(mp3file)
  
  os.makedirs(cachedir, exist_ok = True)
  
  with open(textfile, 'w') as f:
    f.write(g.urlargs[0])
  
  p1  = subprocess.Popen(['text2wave', textfile], stdout = subprocess.PIPE)
  
  output  = subprocess.check_output(['lame', '-', mp3file], stdin = p1.stdout)
  
  p1.wait()
  
  return send_file(mp3file)
 
# =====================================================================
def opustotext(g):
  """Speech recognition API using the Kaldi project. Of course, it would 
  be nice if we could simply use Google's speech recognition, but since
  Google servers are blocked in China, we have to use our own solution.
  
  Note that speech recognition is a very expensive and memory intensive
  operation, so we use the DLock API to run only a single instance at 
  a time. If you have a faster server, feel free to change the 
  numprocesses variable to the number of processor cores that you have.
  """
  results = {};
  
  try:
    if ('students' not in g.session['groups'] 
        and 'teachers' not in g.session['groups']
      ):
      raise Exception(g.T.cantview)
    
    data  = g.request.get_data()
    
    contentlength = g.request.headers.get('content-length')
    
    if contentlength:
      contentlength  = int(contentlength)
    else:
      raise Exception(g.T.nodatauploaded)
    
    if contentlength > (1024 * 64):
      raise Exception(g.T.toobig)
    
    filename      = uuid.uuid4().hex + '.ogg'
    
    cachedir      = '/srv/flasksite/static/learn/opus'
    
    os.makedirs(cachedir, exist_ok = True)
    
    uploadedfile  = cachedir + '/' + filename
    
    # Clean older recordings
    currenttime = time.time()
    
    for r in os.listdir(cachedir):
      fullpath  = cachedir + '/' + r
      
      if os.stat(fullpath).st_mtime + 3600 < currenttime:
        os.remove(fullpath)
    
    with open(uploadedfile, 'wb') as f:
      f.write(g.request.get_data())
    
    results['uploadedfile'] = '/learn/opus/' + filename
    
    # Really simple load balancer for the expensive speech recognition
    # operation. However, this implementation is cross-platform and works well 
    # enough for low powered devices
    numprocesses  = 1
    lock  = DLock('opustotext' + str(random.randint(1, numprocesses)))
    
    acquired  = lock.LoopWhileLocked_ThenLocking()
    
    if acquired:
      output = subprocess.check_output(
        [
          '/srv/flasksite/libs/vosk/recognizespeech.py',
          uploadedfile
        ],
        text  = True
      )
            
      if not output:
        results['error']  = g.T.nothingfound
      else:
        results['output'] = output
    else:
      results['error']  = g.T.serverbusy
  except Exception as e:
    results['error']  = f"Error processing request: {e}"
  
  return results
 
# =====================================================================
def coursesfunc(g):  
  """The helper function for /learn/courses.html.
  
  This loads the main card, child cards, problems, and challenges 
  for the JavaScript renderer.
  """
  result  = ''
  
  try:
    if not g.urlargs:
      return '''
          <div class="Error">
            No course ID given.
          </div>
        '''
    else:
      classid       = g.urlargs.pop(0)
      
      if not classid:
        raise Exception(g.T.missingparams)
      
      stackleft     = len(g.urlargs)
      cardstack     = []
      childstacks   = []
      breadcrumbs   = []
      siblingcards  = []
      childcards    = []
      childcardids  = []
      parentcard    = 0
      c             = 0
      problems      = 0
      caneditcards  = 0
      allowedcards  = []
      
      # An user can be a teacher for one class but a student for another class, so we load all allowed cards
      try:        
        allowedcards  = g.db.Load(learn_teacher, g.session.userid, fields = 'allowedcards').allowedcards
        caneditcards  = 1
        allowedcards.extend(g.db.Load(learn_student, g.session.userid, fields = 'allowedcards').allowedcards)
      except Exception as e:
        allowedcards  = g.db.Load(learn_student, g.session.userid, fields = 'allowedcards').allowedcards
      
      ls  = []
      
      for arg in g.urlargs:
        if arg not in allowedcards:
          raise Exception(g.T.cantview)
        
        if c:
          parentcard  = c
        
        c = g.db.Load(learn_card, FromShortCode(arg), 'rid, title, description, content, html, image, sound, video, flags')
        
        breadcrumbs.append(arg)
        
        siblingcards  = childcards
        
        childcards    = g.db.Linked(c, learn_card, CARD_CHILD, fields = 'rid, title, description, image')
        
        stackleft -= 1
        
        childcardids  = [ToShortCode(x.rid) for x in childcards]            
        
        card  = c.ToJSON('title, description, content, html, image, sound, video, flags')
                         
        card['idcode']  = ToShortCode(c.rid)
        
        cardstack.append(card)
        
        childcardobjs = []
        
        for x in childcards:
          childcardobj  = x.ToJSON('title, description, image')
          
          childcardobj['idcode']  = ToShortCode(x.rid)
          
          childcardobjs.append(childcardobj)
        
        childstacks.append(childcardobjs)
                  
        if not stackleft and c.flags & CARD_LESSON:
          problems  = GetAllProblems(g, [c.rid], CARD_LINK_PROBLEM)      
      
      ls.append(f'''
        <script>
        
        G.classid       = '{classid}';
        G.cardstack     = {json.dumps(cardstack)};
        G.childstacks   = {json.dumps(childstacks)};
        G.problems      = {json.dumps(problems)};
        G.caneditcards  = {1 if caneditcards else 0};
        
        </script>
        
        <div class="ButtonBar CourseNavBar"></div>
        <div class="MainCard"></div>
        <div class="ButtonBar MainCardBar"></div>
        <div class="ChildCards FlexGrid16"></div>
        <div class="ButtonBar ChildCardBar"></div>
        <div class="ProblemToggles"></div>
        <div class="ProblemCards FlexGrid16"></div>
        <div class="ButtonBar ProblemBar"></div>
        <div class="ChallengeCards FlexGrid16"></div>
        <div class="SiblingNav Pad50"></div>
      ''')
        
      return '\n'.join(ls)
  except Exception as e:
    traceback.print_exc()
    result = '<div class="Error">Error processing: ' + str(e) + '</div>'
  
  return result
 
# =====================================================================
def homefunc(g):
  """The helper function for /learn/home.html.
  
  This loads the classes and challenges for the JavaScript renderer
  in addition to generating autohomework challenges and doing analyses
  on challenges which have ended.
  """
  LoadTranslation(g, 'learn', 'default', g.session.lang)
  LoadTranslation(g, 'learn', 'challenges', g.session.lang)
  
  isadministrator = 0
  
  studentjson         = {}
  teacherjson         = {}
  
  teacherclasses  = []
  studentclasses  = []
  
  currenttime  = time.time()
  
  if 'school.administrators' in g.session['groups']:
    isadministrator = 1
    
  if 'teachers' in g.session['groups']:
    try:
      teacher = g.db.Load(learn_teacher, g.session.userid, fields = 'userid, displayname')
      
      teacherjson = teacher.ToJSON()
      teacherjson['idcode'] = ToShortCode(teacher.userid)
      
      for r in g.db.Linked(teacher, learn_schoolclass):
        r.GenerateAutoHomework(g)
        
        o = r.ToJSON()
        
        o['courses']  = [
          x.ToJSON('rid, title')
          for x in g.db.Linked(r, learn_card, fields = 'rid, title')
        ]
        
        o['students'] = {
          ToShortCode(x.userid): x.displayname
          for x in g.db.Linked(r, learn_student, fields = 'userid, displayname')
        }        
 
        teacherclasses.append(o)      
    except Exception as e:
      traceback.print_exc()
      #pass
  
  student = g.db.Load(
    learn_student, 
    g.session.userid,
    'userid, displayname, coins, items'
  )
  
  studentjson = student.ToJSON()
  studentjson['idcode'] = ToShortCode(student.userid)
  
  for r in g.db.Linked(student, learn_schoolclass):
    r.GenerateAutoHomework(g)
    
    o = r.ToJSON()
    
    o['courses']  = [
      x.ToJSON('rid, title')
      for x in g.db.Linked(r, learn_card, fields = 'rid, title')
    ]
    
    o['students'] = {
      ToShortCode(x.userid): x.displayname
      for x in g.db.Linked(r, learn_student, fields = 'userid, displayname')
    }
    
    o['challenges'] = GetCurrentChallenges(g, r.rid, CHALLENGE_HOMEWORK)
    
    r.UpdateChallenges(g)
    
    studentclasses.append(o)
    
  return f'''
    <script>
    
    G.isadministrator = {isadministrator};
    G.teacher         = {json.dumps(teacherjson)};
    G.teacherclasses  = {json.dumps(teacherclasses)};
    G.student         = {json.dumps(studentjson)};
    G.studentclasses  = {json.dumps(studentclasses)};
    
    </script>
    
    <div class="PageLoadContent"></div>
  '''
 
# =====================================================================
def challengesfunc(g):  
  """The helper function for /learn/challenges.html.
  
  This loads the challenge information for the JavaScript renderer.
  """
  args    = g.request.args
  
  classid     = args.get('classid', '')
  challenge   = args.get('challenge', '')
  challengeid = args.get('challengeid', '')
  lessonids   = args.get('lessonids', '')
  problemids  = args.get('problemids', '')
  
  if lessonids:
    lessonids = lessonids.split(',')
  else:
    lessonids = []
  
  if problemids:
    problemids = problemids.split(',')
  else:
    problemids  = []
    
  LoadTranslation(g, 'learn', 'challenges', g.session.lang)
  
  if not classid or (not challenge and not challengeid) or (not challengeid and not lessonids and not problemids):
    raise Exception(g.T.missingparams)
  
  classid = FromShortCode(classid)
  
  challengeid = FromShortCode(challengeid) if challengeid else 0
    
  isteacher = g.db.HasLink(learn_schoolclass, classid, learn_teacher, g.session.userid)
  
  # Only teachers can select random challenges
  if challenge and not isteacher:
    raise Exception(g.T.cantview)
  
  # Limit challenges to students enrolled in this class
  if not isteacher and not g.db.HasLink(learn_schoolclass, classid, learn_student, g.session.userid):
    raise Exception(g.T.cantview)
  
  problems  = []
  
  challengeresult = learn_challengeresult()  
  previousanswer  = []
    
  challengeobj      = 0
  remainingattempts = 0
  
  if challengeid:
    challengeobj = g.db.Load(
      learn_challenge, 
      challengeid
    )
    
    currenttime = time.time()
    
    if not isteacher and currenttime < challengeobj.starttime.timestamp():
      diff = challengeobj.starttime - datetime.datetime.fromtimestamp(currenttime)
      
      return f'''<h1>{g.T.challengenotstarted}</h1>
    
<p class="Error Pad50">
  {g.T.pleasecomebackat} {diff.days * 24 + int((diff.seconds % 86400) / 3600)}:{int((diff.seconds % 3600) / 60):02}:{int(diff.seconds % 60):02}
</p>
 
<div class="ButtonBar">
  <a class="Color1">
    {g.T.back}
  </a>
</div>
'''
    
    if not isteacher and currenttime > challengeobj.endtime.timestamp():
      return f'''<h1>{g.T.challengealreadyover}</h1>
    
<p class="Error Pad50">
  {g.T.betterlucknexttime}
<p>
 
<div class="ButtonBar">
  <a class="Color1">
    {g.T.back}
  </a>
</div>
'''
    
    challenge = challengeobj.challenge.split(' ')
    challenge = '.'.join(challenge).lower()
    
    remainingattempts = challengeobj.GetNumRemainingAttempts(g)
    
    if remainingattempts == 0 and not isteacher:
      return f'''<h1>{g.T.nomoretriesavailable}</h1>
    
<p class="Error Pad50">
  {g.T.nomoretriesavailableexplanation}
</p>
 
<div class="ButtonBar">
  <a class="Color1">
    {g.T.back}
  </a>
</div>
'''
    previousanswer  = ['', '', '', '', []]
 
    if (challengeobj.challenge == 'Write Answer' 
        and challengeobj.previousresults 
      ):
      challengeresult = challengeobj.previousresults[0]
      previousanswer  = challengeresult.problemresults
 
    if challengeobj.flags & CHALLENGE_USE_STUDYLIST:
      studylist = learn_studylist()
      studylist.Set(
        classid   = classid,
        userid    = g.session.userid,
      )
      problems.extend(studylist.GetProblems(g, challengeobj.numproblems))
    else:
      problems.extend(
        GetAllProblems(
          g, 
          [FromShortCode(x) for x in challengeobj.lessonids], 
          CARD_LINK_PROBLEM,
          [FromShortCode(x) for x in challengeobj.problemids], 
        )
      )
  elif lessonids or problemids:
    problems.extend(
      GetAllProblems(
        g, 
        [FromShortCode(x) for x in lessonids], 
        CARD_LINK_PROBLEM,
        [FromShortCode(x) for x in problemids], 
      )
    )
      
  if challengeid and problems and challengeobj.numproblems:
    problems  = random.sample(problems, challengeobj.numproblems)
  
  challengeresult.Set(
    userid          = g.session.userid,
    starttime       = datetime.datetime.now(),
    endtime         = 0,
    timeused        = 0,
    classid         = classid,
    challenge       = challenge,
    challengeid     = challengeid,
    lessonids       = ','.join(sorted(lessonids)),
    problemids      = ','.join(sorted(problemids)),
    flags           = CHALLENGERESULT_UNFINISHED,
    problemresults  = [],
    score           = 0,
    maximumscore    = 0,
    percentright    = 0,
  );
  
  g.db.Save(challengeresult)
  g.db.Commit()
  
  challengejson = {}
  
  if challengeobj:
    challengejson = challengeobj.ToJSON('rid, title, instructions, image, sound, video, files, numtries, minscore, minpercentage, timelimit, flags')
    challengejson['question'] = BestTranslation(challengeobj.question, g.session.lang)
    
    if challengeobj.challenge == 'Write Answer':
      challengejson['title']  = {'en': g.T.questionanswers}
  
  return f'''
<script>
 
G.classid             = '{classid}';
G.challenge           = '{challenge}';
G.challengeid         = '{challengeid}';
G.challengeresultid   = '{ToShortCode(challengeresult.rid)}';
 
G.remainingattempts   = {remainingattempts};
G.challengeobj        = {json.dumps(challengejson)};
G.lessonids           = {json.dumps(lessonids)};
G.problemids          = {json.dumps(problemids)};
 
G.problems            = {json.dumps(problems)};
G.previousanswer      = {json.dumps(previousanswer)};
 
</script>
'''
 
# =====================================================================
def cleanup(g):    
  """The standard Pafera app cleanup handler. 
  
  Since Pafera is focused on speed and fault tolerance rather than 
  pure correctness, we don't use foreign keys and check constraints
  in the database layer. This function checks for broken links and
  rows and deletes them from the database.
  """
  g.db.RequireGroup('admins', g.T.cantchange)
  
  ls  = ['<pre>']
  
  ls.append('Cleaning up cards')
  
  for r in g.db.Find(learn_card, fields = 'rid, title, content'):
    s = g.db.Execute('''
          SELECT rid 
          FROM learn_problem
          WHERE problemid = ?
            OR answerid = ?
            OR explanationid = ?
            OR choice1id = ?
            OR choice2id = ?
            OR choice3id = ?
            OR choice4id = ?
            OR choice5id = ?
            OR choice6id = ?
            OR choice7id = ?
          LIMIT 1
        ''',
        [
          r.rid,
          r.rid,
          r.rid,
          r.rid,
          r.rid,
          r.rid,
          r.rid,
          r.rid,
          r.rid,
          r.rid,
        ]
      )
        
    if not s:
      ls.append(f'''\tFound orphaned card {r.rid}\t{r.title}\t{r.content}''')
  
  ls.append('Cleaning up problems')
  
  for r in g.db.Find(learn_problem, fields = 'rid, problemid, answerid'):
    s = g.db.Execute('''
        SELECT rid 
        FROM learn_card
        WHERE rid IN (?, ?)
      ''',
      [
        r.problemid,
        r.answerid,
      ]
    )
        
    if len(s) != 2:
      ls.append(f'''\tFound broken problem {r.rid}\t{r.problemid}\t{r.answerid}''')    
  
  ls.append('Cleaning up study list')
  
  idstodelete = []
  
  for r in g.db.Execute('''
      SELECT COUNT(*) as count, classid, userid, problemid
      FROM learn_studylist
      GROUP BY classid, userid, problemid
      HAVING count > 1
    '''):
    
    ls.append(f'''{r[0]}\t{r[1]}\t{r[2]}\tr{r[3]}''')
    
    numtodelete = r[0] - 1
    numdeleted  = 0
    currentnum  = 1
    
    for s in g.db.Execute('''
          SELECT rid, results
          FROM learn_studylist
          WHERE classid = ?
            AND userid = ?
            AND problemid = ?
        ''',
        [
          r[1],
          r[2],
          r[3],
        ]
      ):
          
      if numtodelete != numdeleted and (s[1] == '{}' or currentnum == r[0]):
        ls.append(f'''\tDeleting rid {s[0]}''')
        idstodelete.append(s[0])
        numdeleted  +=  1
        
      currentnum  += 1
    
  if idstodelete:
    print(f'Cleaned up {len(idstodelete)} duplicate entries')
    
    g.db.Execute(f'''
        DELETE FROM learn_studylist
        WHERE rid IN ({', '.join(['?' for x in idstodelete])})
      ''',
      idstodelete
    )
    
  ls.append('</pre>')
  
  g.db.Commit()
    
  return '\n'.join(ls)
 
# *********************************************************************
ROUTES  = {
  'cardapi':          cardapi,
  'problemapi':       problemapi,
  'answersapi':       answersapi,
  'schoolapi':        schoolapi,
  'classapi':         classapi,
  'studentapi':       studentapi,
  'teacherapi':       teacherapi,
  'challengeapi':     challengeapi,
  'studylistapi':     studylistapi,
  'texttomp3':        texttomp3,
  'opustotext':       opustotext,
  'coursesfunc':      coursesfunc,
  'homefunc':         homefunc,
  'challengesfunc':   challengesfunc,
  'cleanup':          cleanup,
}