def asPlainText(self, data={}):
if 'dry-run' in data:
yield "*** DRY RUN ***\n"
total_reclaimed = 0
for (studentId, unclaimed) in Session.execute("""
SELECT a.studentId,
SUM(coinsAwarded) -
COALESCE((SELECT SUM(amount) FROM coinAward ca WHERE ca.studentId = a.studentId), 0)
AS unclaimed
FROM answer a, student s
WHERE coinsAwarded > 0
AND a.studentId = s.studentId AND s.hostId = 1
AND a.timeEnd < CURDATE() - INTERVAL 2 YEAR
AND a.studentId NOT IN (SELECT DISTINCT c.studentId FROM coinAward c WHERE c.awardTime >= CURDATE() - INTERVAL 2 YEAR)
GROUP BY a.studentId
HAVING unclaimed > 0
"""):
total_reclaimed += int(unclaimed)
if 'dry-run' in data:
txId = 'DRY_RUN'
else:
txId = coin.sendTransaction(EIAS_WALLET,
unclaimed,
message="Auto-reclaim of awards")
# Worked, so update database
Session.add(
db.CoinAward(
studentId=studentId,
amount=int(unclaimed),
walletId=EIAS_WALLET,
txId=txId,
awardTime=utcnow(
), # NB: So it gets mocked in the tests
))
Session.flush()
yield "StudentId: %d Unclaimed: %d Transaction: %s\n" % (
studentId,
unclaimed,
txId,
)
yield "Total: %d\n" % total_reclaimed
def ingestData(data):
idMap = collections.defaultdict(dict)
inserts = {}
# Check all host keys match our stored versions, and map to our IDs
for host in data['host']:
try:
dbHost = Session.query(db.Host).filter_by(
hostKey=host['hostKey'],
fqdn=host['fqdn'],
).one()
except NoResultFound:
raise ValueError("Unknown host %s:%s, cannot import results" % (host['hostKey'], host['fqdn']))
idMap['hostId'][host['hostId']] = dbHost.hostId
# Map students to our IDs
inserts['student'] = 0
for student in data['student']:
try:
dbStudent = (Session.query(db.Student)
.filter(db.Student.hostId == idMap['hostId'][student['hostId']])
.filter(db.Student.userName == student['userName'])
.one())
dbStudent.userName = student['userName']
dbStudent.email = student['eMail']
except NoResultFound:
dbStudent = db.Student(
hostId=idMap['hostId'][student['hostId']],
userName=student['userName'],
eMail=student['eMail'],
)
Session.add(dbStudent)
Session.flush()
inserts['student'] += 1
idMap['studentId'][student['studentId']] = dbStudent.studentId
Session.flush()
# Map questions to our IDs
for question in data['question']:
try:
dbQuestionId = (Session.query(db.Question.questionId)
.filter(db.Question.plonePath == question['plonePath'])
.one())
except NoResultFound:
raise ValueError("Missing question at %s" % question['plonePath'])
idMap['questionId'][question['questionId']] = dbQuestionId[0]
Session.flush()
# Map lectures to our IDs
inserts['lecture'] = 0
for lecture in data['lecture']:
try:
dbLecture = (Session.query(db.Lecture)
.filter(db.Lecture.hostId == idMap['hostId'][lecture['hostId']])
.filter(db.Lecture.plonePath == lecture['plonePath'])
.one())
dbLecture.plonePath = lecture['plonePath']
except NoResultFound:
dbLecture = db.Lecture(
hostId=idMap['hostId'][lecture['hostId']],
plonePath=lecture['plonePath'],
)
Session.add(dbLecture)
Session.flush()
inserts['lecture'] += 1
idMap['lectureId'][lecture['lectureId']] = dbLecture.lectureId
Session.flush()
# Any answer entries we fetch should be at least as new as the oldest incoming entry
minVal = None
for a in data['answer']:
if minVal is None or a['timeEnd'] < minVal:
minVal = a['timeEnd']
answerFilter = db.Answer.timeEnd.__ge__(datetime.datetime.utcfromtimestamp(minVal or 0))
minVal = None
for a in data['coin_award']:
if minVal is None or a['awardTime'] < minVal:
minVal = a['awardTime']
coinAwardFilter = db.CoinAward.awardTime.__ge__(datetime.datetime.utcfromtimestamp(minVal or 0))
if 'lecture_global_setting' in data:
inserts['lecture_global_setting'] = 0
for (dataEntry, dbEntry) in findMissingEntries(
data['lecture_global_setting'],
Session.query(db.LectureGlobalSetting)
.filter(db.LectureGlobalSetting.lectureId.in_(idMap['lectureId'].values()))
.order_by(
db.LectureGlobalSetting.lectureId,
db.LectureGlobalSetting.lectureVersion,
db.LectureGlobalSetting.key,
),
sortCols=['lectureId', 'lectureVersion', 'key'],
idMap=idMap):
dataEntry['creationDate'] = datetime.datetime.utcfromtimestamp(dataEntry['creationDate'])
Session.add(db.LectureGlobalSetting(**dataEntry))
inserts['lecture_global_setting'] += 1
Session.flush()
if 'lecture_student_setting' in data:
inserts['lecture_student_setting'] = 0
for (dataEntry, dbEntry) in findMissingEntries(
data['lecture_student_setting'],
Session.query(db.LectureStudentSetting)
.filter(db.LectureStudentSetting.lectureId.in_(idMap['lectureId'].values()))
.filter(db.LectureStudentSetting.studentId.in_(idMap['studentId'].values()))
.order_by(
db.LectureStudentSetting.lectureId,
db.LectureStudentSetting.lectureVersion,
db.LectureStudentSetting.studentId,
db.LectureStudentSetting.key,
),
sortCols=['lectureId', 'lectureVersion', 'studentId', 'key'],
idMap=idMap):
dataEntry['creationDate'] = datetime.datetime.utcfromtimestamp(dataEntry['creationDate'])
Session.add(db.LectureStudentSetting(**dataEntry))
inserts['lecture_student_setting'] += 1
Session.flush()
inserts['ug_question'] = 0
for (dataEntry, dbEntry) in findMissingEntries(
data['ug_question'],
Session.query(db.UserGeneratedQuestion)
.order_by(db.UserGeneratedQuestion.ugQuestionGuid),
sortCols=['ugQuestionGuid'],
ignoreCols=['ugQuestionId'],
idMap=idMap):
Session.add(db.UserGeneratedQuestion(**dataEntry))
inserts['ug_question'] += 1
Session.flush()
inserts['ug_answer'] = 0
for (dataEntry, dbEntry) in findMissingEntries(
data['ug_answer'],
Session.query(db.UserGeneratedAnswer)
.filter(db.UserGeneratedAnswer.studentId.in_(idMap['studentId'].values()))
.order_by(db.UserGeneratedAnswer.studentId, db.UserGeneratedAnswer.ugQuestionGuid),
sortCols=['studentId', 'ugQuestionGuid'],
ignoreCols=['ugAnswerId'],
idMap=idMap):
if dataEntry['studentId'] is not None:
Session.add(db.UserGeneratedAnswer(**dataEntry))
inserts['ug_answer'] += 1
else:
# If studentId is None, we've selected it but not relevant to current data
# (the dump isn't selective enough). Ignore it, will import eventually
continue
Session.flush()
# Filter out answer student/question/timeEnd combinations already stored in DB
inserts['answer'] = 0
for (dataEntry, dbEntry) in findMissingEntries(
data['answer'],
Session.query(db.Answer)
.filter(db.Answer.lectureId.in_(idMap['lectureId'].values()))
.filter(db.Answer.studentId.in_(idMap['studentId'].values()))
.filter(answerFilter)
.order_by(db.Answer.lectureId, db.Answer.studentId, db.Answer.timeEnd),
sortCols=['lectureId', 'studentId', 'timeEnd'],
ignoreCols=['answerId'],
idMap=idMap,
returnUpdates=True):
if dbEntry:
# Coins awarded might have been updated afer the fact
dbEntry.coinsAwarded = dataEntry['coinsAwarded']
else:
Session.add(db.Answer(**dataEntry))
inserts['answer'] += 1
Session.flush()
if 'lecture_setting' in data:
inserts['lecture_setting'] = 0
for (dataEntry, dbEntry) in findMissingEntries(
data['lecture_setting'],
Session.query(db.DeprecatedLectureSetting)
.filter(db.DeprecatedLectureSetting.lectureId.in_(idMap['lectureId'].values()))
.filter(db.DeprecatedLectureSetting.studentId.in_(idMap['studentId'].values()))
.order_by(db.DeprecatedLectureSetting.lectureId, db.DeprecatedLectureSetting.studentId, db.DeprecatedLectureSetting.key),
sortCols=['lectureId', 'studentId', 'key'],
idMap=idMap):
Session.add(db.DeprecatedLectureSetting(**dataEntry))
inserts['lecture_setting'] += 1
Session.flush()
inserts['coin_award'] = 0
for (dataEntry, dbEntry) in findMissingEntries(
data['coin_award'],
Session.query(db.CoinAward)
.filter(db.CoinAward.studentId.in_(idMap['studentId'].values()))
.filter(coinAwardFilter)
.order_by(db.CoinAward.studentId, db.CoinAward.awardTime),
sortCols=['studentId', 'awardTime'],
ignoreCols=['coinAwardId'],
idMap=idMap):
Session.add(db.CoinAward(**dataEntry))
inserts['coin_award'] += 1
Session.flush()
Session.flush()
return inserts
def asDict(self, data=None):
"""Show coins given to student"""
student = self.getCurrentStudent()
(lastAwardTime, walletId, coinClaimed) = (
Session.query(
func.max(db.CoinAward.awardTime),
func.max(
db.CoinAward.walletId), #TODO: Should be last, not max
func.sum(db.CoinAward.amount),
).filter(db.CoinAward.studentId == student.studentId).first())
if coinClaimed is None:
lastAwardTime = 0
coinClaimed = 0
walletId = ''
history = []
coinAwarded = 0
for row in (Session.query(
db.Answer.timeEnd, db.Answer.coinsAwarded,
db.Lecture.plonePath).join(db.Lecture).filter(
db.Lecture.hostId == self.getDbHost().hostId).filter(
db.Answer.studentId == student.studentId).filter(
db.Answer.practice == False).filter(
db.Answer.coinsAwarded > 0).order_by(
db.Answer.timeEnd, db.Lecture.plonePath)):
coinAwarded += row[1]
history.insert(
0,
dict(lecture=row[2],
time=calendar.timegm(row[0].timetuple())
if row[1] else None,
amount=row[1],
claimed=(coinAwarded <= coinClaimed
and row[0] <= lastAwardTime)))
# Check if wallet ID is provided, if so pay up.
txId = None
if data is not None and data.get('walletId', None):
walletId = data['walletId']
# Validate Captcha if not a unittest wallet
if walletId.startswith('$$UNITTEST'):
pass
elif walletId == '$$DONATE:EIAS':
walletId = EIAS_WALLET
else:
remote_addr = self.request.get('HTTP_X_FORWARDED_FOR',
'').split(',')[0]
if not remote_addr:
remote_addr = self.request.get('REMOTE_ADDR')
if captcha:
res = captcha.submit(data.get('captchaResponse', ''),
coin_config.CAPTCHA_KEY, remote_addr)
if res.error_code:
raise ValueError("Could not validate CAPTCHA")
elif not res.is_valid:
raise ValueError("Invalid CAPTCHA")
# Have we already given out our maximum for today?
dailyTotalAward = (Session.query(func.sum(
db.CoinAward.amount)).filter(db.CoinAward.awardTime > (
utcnow() - datetime.timedelta(days=1))).one())[0] or 0
if dailyTotalAward > MAX_DAILY_AWARD:
raise ValueError(
"We have distributed all awards available for today")
# Has this student already got their coins for the hour?
hourlyStudentTotal = (Session.query(func.sum(
db.CoinAward.amount)).filter(
db.CoinAward.studentId == student.studentId).filter(
db.CoinAward.awardTime >
(utcnow() -
datetime.timedelta(hours=1))).one())[0] or 0
coinOwed = min(
coinAwarded - coinClaimed,
MAX_STUDENT_HOURLY_AWARD - hourlyStudentTotal,
)
if coinOwed == 0 and (coinAwarded - coinClaimed) > 0:
raise ValueError("You cannot redeem any more awards just yet")
# Perform transaction
txId = coin.sendTransaction(walletId, coinOwed)
# Worked, so update database
Session.add(
db.CoinAward(
studentId=student.studentId,
amount=int(coinOwed),
walletId=walletId,
txId=txId,
awardTime=utcnow(), # NB: So it gets mocked in the tests
))
Session.flush()
# Worked, so should be even now
for h in history:
h['claimed'] = True
coinClaimed += coinOwed
return dict(
walletId=walletId,
history=history,
coin_available=int(coinAwarded - coinClaimed),
tx_id=txId,
)