def createReport(openbadgerDB, csolDB):
cur = csolDB.cursor()
queryString = ("SELECT "
"CASE "
"WHEN age < 13 THEN 'Under 13' "
"WHEN age BETWEEN 13 AND 17 THEN '13-17' "
"WHEN age BETWEEN 18 AND 24 THEN '18-24' "
"WHEN age > 24 THEN 'Over 24' "
"END AS age_range, "
"COUNT(*) AS count "
"FROM (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM csol.Learners) AS derived "
"GROUP BY age_range "
"ORDER BY FIELD(age_range, 'Under 13', '13-17', '18-24', 'Over 24'); ")
cur.execute(queryString)
report = Report('Learners by Age', 2)
for row in cur.fetchall():
age = row[0]
count = row[1]
report.addRow([age, count])
return report
def createReport(openbadgerDB, csolDB):
cur = csolDB.cursor()
queryString = ('SELECT '
'CASE '
' WHEN gender = "male" THEN "Male" '
' WHEN gender = "female" THEN "Female" '
' ELSE "Not Specified" '
'END AS gender_readable, '
'COUNT(*) as count '
'FROM csol.Learners '
'GROUP BY gender '
'ORDER BY FIELD(gender_readable, "Female", "Male", "Not Specified");')
cur.execute(queryString)
report = Report('Learners by Gender', 2)
for row in cur.fetchall():
gender = row[0]
count = row[1]
if gender is None or gender == '':
gender = 'Not Specified'
report.addRow([gender.title(), count])
return report
def createReport(openbadgerDB, csolDB):
badges = openbadgerDB.badges
steamBadgeDocuments = openbadgerDB.badges.find( {'categoryAward' : { '$nin': ['', None] } }, fields=['_id']);
steamBadges = []
for document in steamBadgeDocuments:
steamBadges.append(document['_id'])
badgeinstances = openbadgerDB.badgeinstances
pipeline = [{'$match' : {'badge' : { '$in' : steamBadges } } },
{'$group' : {'_id' : '$user', 'count' : {'$sum' : 1}}},
{'$group' : {'_id' : '$count', 'count' : {'$sum' : 1}}}]
results = badgeinstances.aggregate(pipeline)['result']
report = Report('Learners by STEAM Badges Earned', 2)
countList = [0,0,0,0,0]
for row in results:
countList[row['_id']-1] = row['count']
for i in range(len(countList)):
report.addRow([i+1, countList[i]])
return report
def createReport(openbadgerDB, csolDB):
learnersByZip = helper_queries.getLearnersByProperty('zipCode')
report = Report('Top Zip Codes with Learners', 2)
sortedLearnersByZip = sorted(learnersByZip.iteritems(), key=operator.itemgetter(1), reverse=True)
for zip, count in sortedLearnersByZip:
report.addRow([zip.title().decode('latin-1'), count])
return report
def createReport(openbadgerDB, csolDB):
activeLearnersBySchool = helper_queries.getLearnersByProperty('school', badgeThreshold=1)
report = Report('Top Schools with Active Learners', 2)
sortedActiveLearnersBySchool = sorted(activeLearnersBySchool.iteritems(), key=operator.itemgetter(1), reverse=True)
for school, count in sortedActiveLearnersBySchool:
report.addRow([school.title().decode('latin-1'), count])
return report
def createReport(openbadgerDB, csolDB):
badgesBySchool = helper_queries.getBadgesByProperty('school')
learnersBySchool = helper_queries.getLearnersByProperty('school')
activeLearnersBySchool = helper_queries.getLearnersByProperty('school', badgeThreshold=1)
report = Report('Statistics by School', 4)
schools = sorted(learnersBySchool.keys())
for school in schools:
report.addRow([school.title().decode('latin-1'), badgesBySchool[school], learnersBySchool[school], activeLearnersBySchool[school]])
return report
def createReport(openbadgerDB, csolDB):
badgesByZip = helper_queries.getBadgesByProperty('zipCode')
learnersByZip = helper_queries.getLearnersByProperty('zipCode')
activeLearnersByZip = helper_queries.getLearnersByProperty('zipCode', badgeThreshold=1)
report = Report('Statistics by Zip', 4)
zips = sorted(learnersByZip.keys())
for zip in zips:
report.addRow([zip, badgesByZip[zip], learnersByZip[zip], activeLearnersByZip[zip]])
return report
def createReport(openbadgerDB, csolDB):
cur = csolDB.cursor()
report = Report('Other Data', 2)
queryString = ("SELECT COUNT(*) FROM Learners")
cur.execute(queryString)
learnerCount = cur.fetchone()[0]
report.addRow(['Number of Learners', learnerCount])
badgeInstanceCount = openbadgerDB.badgeinstances.count()
report.addRow(['Number of Badges Awarded', badgeInstanceCount])
steamBadgeDocuments = openbadgerDB.badges.find( {'categoryAward' : { '$nin': ['', None] } }, fields=['_id']);
steamBadges = []
for document in steamBadgeDocuments:
steamBadges.append(document['_id'])
steamBadgeInstances = openbadgerDB.badgeinstances.aggregate([{'$match' : {'badge' : { '$in' : steamBadges } } }] )
steamBadgeInstanceCount = len(steamBadgeInstances['result'])
report.addRow(['Number of STEAM Badges Awarded', steamBadgeInstanceCount])
queryString = ("SELECT COUNT(*) FROM Playlists")
cur.execute(queryString)
wishlistCount = cur.fetchone()[0]
report.addRow(['Number of Badges Wishlisted', wishlistCount])
return report
def createReport(openbadgerDB, csolDB):
cur = csolDB.cursor()
queryString = ("SELECT DISTINCT cpsStudentId FROM Learners "
"ORDER BY cpsStudentId ASC ")
cur.execute(queryString)
report = Report('Student IDs', 1)
for row in cur.fetchall():
id = row[0]
if id != '':
report.addRow([id])
return report