Coverage for app/logic/spreadsheet.py: 100%

1from importlib.abc import ResourceReader 

2from os import major 

3import xlsxwriter 

4from peewee import fn, Case, JOIN 

5from collections import defaultdict 

6 

7from app import app 

8from app.models.eventParticipant import EventParticipant 

9from app.models.user import User 

10from app.models.program import Program 

11from app.models.event import Event 

12from app.models.term import Term 

13 

14 

15def getUniqueVolunteers(academicYear): 

16 uniqueVolunteers = (EventParticipant.select(fn.DISTINCT(EventParticipant.user_id), fn.CONCAT(User.firstName, ' ', User.lastName), User.bnumber) 

17 .join(User).switch(EventParticipant) 

18 .join(Event) 

19 .join(Term) 

20 .where(Term.academicYear == academicYear) 

21 .order_by(EventParticipant.user_id)) 

22 

23 return uniqueVolunteers.tuples() 

24 

25 

26def getVolunteerProgramEventByTerm(term): 

27 volunteersByTerm = (EventParticipant.select(fn.CONCAT(User.firstName, ' ', User.lastName), EventParticipant.user_id, Program.programName, Event.name) 

28 .join(User).switch(EventParticipant) 

29 .join(Event) 

30 .join(Program) 

31 .where(Event.term_id == term) 

32 .order_by(EventParticipant.user_id)) 

33 

34 return volunteersByTerm.tuples() 

35 

36 

37def totalVolunteerHours(academicYear): 

38 query = (EventParticipant.select(fn.SUM(EventParticipant.hoursEarned)) 

39 .join(Event, on=(EventParticipant.event == Event.id)) 

40 .join(Term, on=(Event.term == Term.id)) 

41 .where(Term.academicYear == academicYear) 

42 ) 

43 

44 return query.tuples() 

45 

46 

47def volunteerProgramHours(academicYear): 

48 volunteerProgramHours = (EventParticipant.select(Program.programName, EventParticipant.user_id, fn.SUM(EventParticipant.hoursEarned)) 

49 .join(Event, on=(EventParticipant.event_id == Event.id)) 

50 .join(Program, on=(Event.program_id == Program.id)) 

51 .join(Term, on=(Event.term == Term.id)) 

52 .where(Term.academicYear == academicYear) 

53 .group_by(Program.programName, EventParticipant.user_id)) 

54 

55 return volunteerProgramHours.tuples() 

56 

57 

58def onlyCompletedAllVolunteer(academicYear): 

59 subQuery = (EventParticipant.select(EventParticipant.user_id) 

60 .join(Event) 

61 .join(Term) 

62 .where(Event.name != "All Volunteer Training", Term.academicYear == academicYear)) 

63 

64 onlyAllVolunteer = (EventParticipant.select(EventParticipant.user_id, fn.CONCAT(User.firstName, " ", User.lastName)) 

65 .join(User).switch(EventParticipant) 

66 .join(Event) 

67 .join(Term) 

68 .where(Event.name == "All Volunteer Training", Term.academicYear == academicYear, EventParticipant.user_id.not_in(subQuery))) 

69 

70 return onlyAllVolunteer.tuples() 

71 

72 

73def volunteerHoursByProgram(academicYear): 

74 query = (Program.select(Program.programName, fn.SUM(EventParticipant.hoursEarned).alias('sum')) 

75 .join(Event) 

76 .join(EventParticipant, on=(Event.id == EventParticipant.event_id)) 

77 .join(Term, on=(Term.id == Event.term)) 

78 .where(Term.academicYear == academicYear) 

79 .group_by(Program.programName) 

80 .order_by(Program.programName)) 

81 

82 return query.tuples() 

83 

84 

85def volunteerMajorAndClass(academicYear, column, reorderClassLevel=False): 

86 majorAndClass = (User.select(Case(None, ((column.is_null(), "Unknown"),), column), fn.COUNT(fn.DISTINCT(EventParticipant.user_id)).alias('count')) 

87 .join(EventParticipant, on=(User.username == EventParticipant.user_id)) 

88 .join(Event, on=(EventParticipant.event_id == Event.id)) 

89 .join(Term, on=(Event.term == Term.id)) 

90 .where(Term.academicYear == academicYear) 

91 .group_by(column)) 

92 

93 if reorderClassLevel: 

94 majorAndClass = majorAndClass.order_by(Case(None, ((column == "Freshman", 1), 

95 (column == "Sophomore", 2), 

96 (column == "Junior", 3), 

97 (column == "Senior", 4), 

98 (column == "Graduating", 5), 

99 (column == "Non-Degree", 6), 

100 (column.is_null(), 7)), 

101 8)) 

102 else: 

103 majorAndClass = majorAndClass.order_by(column.asc(nulls='LAST')) 

104 

105 return majorAndClass.tuples() 

106 

107 

108def repeatVolunteersPerProgram(academicYear): 

109 repeatPerProgramQuery = (EventParticipant.select(fn.CONCAT(User.firstName, " ", User.lastName).alias('fullName'), 

110 Program.programName.alias("programName"), 

111 fn.COUNT(EventParticipant.event_id).alias('event_count')) 

112 .join(Event, on=(EventParticipant.event_id == Event.id)) 

113 .join(Program, on=(Event.program == Program.id)) 

114 .join(User, on=(User.username == EventParticipant.user_id)) 

115 .join(Term, on=(Event.term == Term.id)) 

116 .where(Term.academicYear == academicYear) 

117 .group_by(User.firstName, User.lastName, Event.program) 

118 .having(fn.COUNT(EventParticipant.event_id) > 1) 

119 .order_by(Event.program, User.lastName)) 

120 

121 return repeatPerProgramQuery.tuples() 

122 

123 

124def repeatVolunteers(academicYear): 

125 repeatAllProgramQuery = (EventParticipant.select(fn.CONCAT(User.firstName, " ", User.lastName), fn.COUNT(EventParticipant.user_id).alias('count')) 

126 .join(User, on=(User.username == EventParticipant.user_id)) 

127 .join(Event, on=(EventParticipant.event == Event.id)) 

128 .join(Term, on=(Event.term == Term.id)) 

129 .where(Term.academicYear == academicYear) 

130 .group_by(User.firstName, User.lastName) 

131 .having(fn.COUNT(EventParticipant.user_id) > 1)) 

132 

133 return repeatAllProgramQuery.tuples() 

134 

135 

136def getRetentionRate(academicYear): 

137 retentionList = [] 

138 fall, spring = academicYear.split("-") 

139 fallParticipationDict = termParticipation(f"Fall {fall}") 

140 springParticipationDict = termParticipation(f"Spring {spring}") 

141 

142 retentionRateDict = calculateRetentionRate(fallParticipationDict, springParticipationDict) 

143 for program, retentionRate in retentionRateDict.items(): 

144 retentionList.append((program, str(round(retentionRate * 100, 2)) + "%")) 

145 

146 return retentionList 

147 

148 

149def termParticipation(termDescription): 

150 participationQuery = (Event.select(Event.program, EventParticipant.user_id.alias('participant'), Program.programName.alias("programName")) 

151 .join(EventParticipant, JOIN.LEFT_OUTER, on=(Event.id == EventParticipant.event)) 

152 .join(Program, on=(Event.program == Program.id)) 

153 .join(Term, on=(Event.term_id == Term.id)) 

154 .where(Term.description == termDescription) 

155 .order_by(EventParticipant.user)) 

156 

157 programParticipationDict = defaultdict(list) 

158 for result in participationQuery.dicts(): 

159 programName = result['programName'] 

160 participant = result['participant'] 

161 programParticipationDict[programName].append(participant) 

162 

163 return dict(programParticipationDict) 

164 

165 

166def removeNullParticipants(participantList): 

167 return list(filter(lambda participant: participant, participantList)) 

168 

169 

170def calculateRetentionRate(fallDict, springDict): 

171 retentionDict = {} 

172 for program in fallDict: 

173 fallParticipants = set(removeNullParticipants(fallDict[program])) 

174 springParticipants = set(removeNullParticipants(springDict.get(program, []))) 

175 retentionRate = 0.0 

176 try: 

177 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants) 

178 except ZeroDivisionError: 

179 pass 

180 retentionDict[program] = retentionRate 

181 

182 return retentionDict 

183 

184 

185def makeDataXls(getData, columnTitles, sheetName, workbook): 

186 worksheet = workbook.add_worksheet(sheetName) 

187 bold = workbook.add_format({'bold': True}) 

188 

189 worksheet.write_string(0, 0, sheetName) 

190 

191 for column, title in enumerate(columnTitles): 

192 worksheet.write(1, column, title, bold) 

193 

194 for column, rowData in enumerate(getData): 

195 for data, value in enumerate(rowData): 

196 worksheet.write(column + 2, data, value) 

197 

198 for column, title in enumerate(columnTitles): 

199 columnData = [title] + [rowData[column] for rowData in getData] 

200 setColumnWidth = max(len(str(x)) for x in columnData) 

201 worksheet.set_column(column, column, setColumnWidth + 3) 

202 

203 

204def createSpreadsheet(academicYear): 

205 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx" 

206 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True}) 

207 

208 hoursByProgramColumns = ["Program", "Hours"] 

209 volunteerMajorColumns = ["Major", "Count"] 

210 volunteerClassColumns = ["Class Level", "Count"] 

211 repeatProgramEventVolunteerColumns = ["Volunteer", "Program Name", "Event Count"] 

212 repeatAllProgramVolunteerColumns = ["Volunteer", "Number of Events"] 

213 volunteerProgramRetentionRateAcrossTermColumns = ["Program", "Retention Rate"] 

214 uniqueVolunteersColumns = ["Username", "Full Name", "B-Number"] 

215 totalVolunteerHoursColumns = ["Total Volunteer Hours"] 

216 volunteerProgramHoursColumns = ["Program Name", "Volunteer Username", "Volunteer Hours"] 

217 onlyCompletedAllVolunteerColumns = ["Username", "Full Name"] 

218 volunteerProgramEventByTerm = ["Full Name", "Username", "Program Name", "Event Name"] 

219 

220 makeDataXls(volunteerHoursByProgram(academicYear), hoursByProgramColumns, "Total Hours By Program", workbook) 

221 makeDataXls(volunteerMajorAndClass(academicYear, User.major), volunteerMajorColumns, "Volunteers By Major", workbook) 

222 makeDataXls(volunteerMajorAndClass(academicYear, User.classLevel, reorderClassLevel=True), volunteerClassColumns, "Volunteers By Class Level", workbook) 

223 makeDataXls(repeatVolunteersPerProgram(academicYear), repeatProgramEventVolunteerColumns, "Repeat Volunteers Per Program", workbook) 

224 makeDataXls(repeatVolunteers(academicYear), repeatAllProgramVolunteerColumns, "Repeat Volunteers All Programs", workbook) 

225 makeDataXls(getRetentionRate(academicYear), volunteerProgramRetentionRateAcrossTermColumns, "Retention Rate By Semester", workbook) 

226 makeDataXls(getUniqueVolunteers(academicYear), uniqueVolunteersColumns, "Unique Volunteers", workbook) 

227 makeDataXls(totalVolunteerHours(academicYear), totalVolunteerHoursColumns, "Total Hours", workbook) 

228 makeDataXls(volunteerProgramHours(academicYear), volunteerProgramHoursColumns, "Volunteer Hours By Program", workbook) 

229 makeDataXls(onlyCompletedAllVolunteer(academicYear), onlyCompletedAllVolunteerColumns, "Only All Volunteer Training", workbook) 

230 makeDataXls(getVolunteerProgramEventByTerm(Term.get_or_none(Term.description == f"Fall {academicYear.split('-')[0]}")), volunteerProgramEventByTerm, f"Fall {academicYear.split('-')[0]}", workbook) 

231 

232 workbook.close() 

233 

234 return filepath