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