Coverage for app/logic/volunteerSpreadsheet.py: 100%
149 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-03-26 20:07 +0000
1from os import major
2import xlsxwriter
3from peewee import fn, Case, JOIN, SQL, Select
4from collections import defaultdict
5from datetime import date, datetime,time
6from app import app
7from app.models import mainDB
8from app.models.celtsLabor import CeltsLabor
9from app.models.eventParticipant import EventParticipant
10from app.models.user import User
11from app.models.program import Program
12from app.models.event import Event
13from app.models.term import Term
15### READ ME FIRST! #################################################################
16#
17# It's very important that we understand the distinction between volunteers earning
18# service hours and other things that we track in our system, like student labor,
19# bonner students, trainings, etc. The way we use 'volunteer' may not necessarily
20# be the way CELTS uses it.
21#
22####################################################################################
24def getFallTerm(academicYear):
25 return Term.get(Term.description % "Fall%", Term.academicYear == academicYear)
27def getSpringTerm(academicYear):
28 return Term.get(Term.description % "Spring%", Term.academicYear == academicYear)
31def getBaseQuery(academicYear):
33 # As we add joins to this query, watch out for duplicate participant rows being added
35 return (EventParticipant.select()
36 .join(User).switch(EventParticipant)
37 .join(Event)
38 .join(Program).switch(Event)
39 .join(Term)
40 .where(Term.academicYear == academicYear,
41 Event.deletionDate == None,
42 Event.isCanceled == False)
43 .order_by(Event.startDate))
46def getUniqueVolunteers(academicYear):
47 base = getBaseQuery(academicYear)
49 columns = ["Full Name", "Email", "B-Number"]
50 subquery = (base.select(fn.DISTINCT(EventParticipant.user_id).alias('user_id'), fn.CONCAT(User.firstName, ' ', User.lastName).alias("fullname"), User.bnumber)
51 .where(Event.isService == True)).alias('subq')
52 query = Select().from_(subquery).select(subquery.c.fullname, fn.CONCAT(subquery.c.user_id,'@berea.edu'), subquery.c.bnumber)
54 return (columns,query.tuples().execute(mainDB))
57def volunteerProgramHours(academicYear):
58 base = getBaseQuery(academicYear)
60 columns = ["Program Name", "Volunteer Hours", "Volunteer Name", "Volunteer Email", "Volunteer B-Number"]
61 query = (base.select(Program.programName,
62 fn.SUM(EventParticipant.hoursEarned),
63 fn.CONCAT(User.firstName, ' ', User.lastName),
64 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
65 User.bnumber)
66 .where(Event.isService == True)
67 .group_by(Program.programName, EventParticipant.user_id))
69 return (columns, query.tuples())
71def onlyCompletedAllVolunteer(academicYear):
72 base = getBaseQuery(academicYear)
73 base2 = getBaseQuery(academicYear)
75 columns = ["Full Name", "Email", "B-Number"]
76 subQuery = base2.select(EventParticipant.user_id).where(~Event.isAllVolunteerTraining)
78 query = (base.select(fn.CONCAT(User.firstName, ' ', User.lastName),
79 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
80 User.bnumber)
81 .where(Event.isAllVolunteerTraining, EventParticipant.user_id.not_in(subQuery)))
83 return (columns, query.tuples())
85def totalHours(academicYear):
86 base = getBaseQuery(academicYear)
88 columns = ["Total Service Hours", "Total Training Hours", "Other Participation Hours"]
89 query = base.select(fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
90 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
91 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
93 return (columns, query.tuples())
95def totalHoursByProgram(academicYear):
96 base = getBaseQuery(academicYear)
98 columns = ["Program", "Service Hours", "Training Hours", "Other Hours"]
99 query = (base.select(Program.programName,
100 fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
101 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
102 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
103 .group_by(Program.programName)
104 .order_by(Program.programName))
106 return (columns, query.tuples())
108def makeCase(fieldname):
109 return Case(fieldname,((1, "Yes"),(0, "No"),),"None")
111def getAllTermData(term):
112 base = getBaseQuery(term.academicYear)
114 columns = ["Program Name", "Event Name", "Event Description", "Event Date", "Event Start Time", "Event End Time", "Event Location",
115 "Food Provided", "Labor Only", "Training Event", "RSVP Required", "Service Event", "Engagement Event", "All Volunteer Training",
116 "RSVP Limit", "Series #", "Is Repeating Event", "Contact Name", "Contact Email",
117 "Student First Name", "Student Last Name", "Student Email", "Student B-Number", "Student Phone", "Student CPO", "Student Major", "Student Has Graduated", "Student Class Level", "Student Dietary Restrictions",
118 "Hours Earned"]
119 query = (base.select(Program.programName,Event.name, Event.description, Event.startDate, Event.timeStart, Event.timeEnd, Event.location,
120 makeCase(Event.isFoodProvided), makeCase(Event.isLaborOnly), makeCase(Event.isTraining), makeCase(Event.isRsvpRequired), makeCase(Event.isService), makeCase(Event.isEngagement), makeCase(Event.isAllVolunteerTraining),
121 Event.rsvpLimit, Event.seriesId, makeCase(Event.isRepeating), Event.contactName, Event.contactEmail,
122 User.firstName, User.lastName, fn.CONCAT(User.username,'@berea.edu'), User.bnumber, User.phoneNumber,User.cpoNumber,User.major, makeCase(User.hasGraduated), User.rawClassLevel, User.dietRestriction,
123 EventParticipant.hoursEarned)
124 .where(Event.term == term))
126 return (columns,query.tuples())
128def volunteerMajorAndClass(academicYear, column, classLevel=False):
129 base = getBaseQuery(academicYear)
131 columns = ["Major", "Count"]
132 query = (base.select(Case(None, ((column.is_null(), "Unknown"),), column), fn.COUNT(fn.DISTINCT(EventParticipant.user_id)).alias('count'))
133 .where(Event.isService == True)
134 .group_by(column))
136 if classLevel:
137 columns = ["Class Level", "Count"]
138 query = query.order_by(Case(None, ((column == "Freshman", 1),
139 (column == "Sophomore", 2),
140 (column == "Junior", 3),
141 (column == "Senior", 4),
142 (column == "Graduating", 5),
143 (column == "Non-Degree", 6),
144 (column.is_null(), 7)),
145 8))
146 else:
147 query = query.order_by(SQL("count").desc())
149 return (columns, query.tuples())
152def repeatParticipantsPerProgram(academicYear):
153 base = getBaseQuery(academicYear)
155 columns = ["Volunteer", "Program Name", "Event Count"]
156 query = (base.select(fn.CONCAT(User.firstName, " ", User.lastName).alias('fullName'),
157 Program.programName.alias("programName"),
158 fn.COUNT(EventParticipant.event_id).alias('event_count'))
159 .where(Event.isService == True)
160 .group_by(User.firstName, User.lastName, Event.program)
161 .having(fn.COUNT(EventParticipant.event_id) > 1)
162 .order_by(Event.program, User.lastName))
164 return (columns, query.tuples())
167def repeatParticipants(academicYear):
168 base = getBaseQuery(academicYear)
170 columns = ["Number of Events", "Full Name", "Email", "B-Number"]
171 query = (base.select(fn.COUNT(EventParticipant.user_id).alias('count'),
172 fn.CONCAT(User.firstName, ' ', User.lastName),
173 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
174 User.bnumber)
175 .group_by(User.firstName, User.lastName)
176 .having(fn.COUNT(EventParticipant.user_id) > 1)
177 .order_by(SQL("count").desc()))
179 return (columns, query.tuples())
182def getRetentionRate(academicYear):
183 fallParticipationDict = termParticipation(getFallTerm(academicYear))
184 springParticipationDict = termParticipation(getSpringTerm(academicYear))
186 retentionList = []
187 retentionRateDict = calculateRetentionRate(fallParticipationDict, springParticipationDict)
188 for program, retentionRate in retentionRateDict.items():
189 retentionList.append((program, str(round(retentionRate * 100, 2)) + "%"))
191 columns = ["Program", "Retention Rate"]
192 return (columns, retentionList)
195def termParticipation(term):
196 base = getBaseQuery(term.academicYear)
198 participationQuery = (base.select(Event.program, EventParticipant.user_id.alias('participant'), Program.programName.alias("programName"))
199 .where(Event.term == term)
200 .order_by(EventParticipant.user))
202 programParticipationDict = defaultdict(list)
203 for result in participationQuery.dicts():
204 programName = result['programName']
205 participant = result['participant']
206 programParticipationDict[programName].append(participant)
208 return dict(programParticipationDict)
211def removeNullParticipants(participantList):
212 return list(filter(lambda participant: participant, participantList))
215def calculateRetentionRate(fallDict, springDict):
216 retentionDict = {}
217 for program in fallDict:
218 fallParticipants = set(removeNullParticipants(fallDict[program]))
219 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
220 retentionRate = 0.0
221 try:
222 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
223 except ZeroDivisionError:
224 pass
225 retentionDict[program] = retentionRate
227 return retentionDict
229def laborAttendanceByTerm(term):
230 laborQuery = ( #so that all Celts Labor students appear even if they didn't attend anything
231 CeltsLabor
232 .select(
233 fn.CONCAT(User.firstName, ' ', User.lastName).alias('fullName'),
234 User.bnumber,
235 fn.CONCAT(User.username, '@berea.edu').alias('email'),
236 fn.COUNT(fn.DISTINCT(Event.id)).alias('meetingsAttended')
237 )
238 .join(User)
239 .switch(CeltsLabor)
240 .join(
241 EventParticipant,
242 JOIN.LEFT_OUTER,
243 on=(CeltsLabor.user == EventParticipant.user)
244 )
245 .join(
246 Event,
247 JOIN.LEFT_OUTER,
248 on=(
249 (EventParticipant.event == Event.id) &
250 (Event.term == term) &
251 (Event.isLaborOnly == True) &
252 (Event.deletionDate.is_null()) &
253 (Event.isCanceled == False)
254 )
255 )
256 .where(
257 (CeltsLabor.term == term)
258 )
259 .group_by(CeltsLabor.user)
260 )
262 nonLaborQuery = ( #so that non-labor attendees who are not in CeltsLabor also appear
263 EventParticipant
264 .select(
265 fn.CONCAT(User.firstName, ' ', User.lastName).alias('fullName'),
266 User.bnumber,
267 fn.CONCAT(User.username, '@berea.edu').alias('email'),
268 fn.COUNT(EventParticipant.event_id).alias('meetingsAttended')
269 )
270 .join(User)
271 .switch(EventParticipant)
272 .join(
273 Event,
274 on=(
275 (EventParticipant.event == Event.id) &
276 (Event.term == term) &
277 (Event.isLaborOnly == True) &
278 (Event.deletionDate.is_null()) &
279 (Event.isCanceled == False)
280 )
281 )
282 .join(
283 CeltsLabor,
284 JOIN.LEFT_OUTER,
285 on=(EventParticipant.user == CeltsLabor.user) &
286 (CeltsLabor.term == term)
287 )
288 .where(CeltsLabor.user.is_null())
289 .group_by(EventParticipant.user)
290 )
292 query = laborQuery.union(nonLaborQuery).order_by(SQL('fullName'))
293 columns = ("Full Name", "B-Number", "Email", "Meetings Attended")
295 return (columns, query.tuples())
298def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
299 # assumes the length of the column titles matches the length of the data
300 (columnTitles, dataTuples) = sheetData
301 worksheet = workbook.add_worksheet(sheetName)
302 bold = workbook.add_format({'bold': True})
304 worksheet.write_string(0, 0, sheetName, bold)
305 if sheetDesc:
306 worksheet.write_string(1, 0, sheetDesc)
308 for column, title in enumerate(columnTitles):
309 worksheet.write(3, column, title, bold)
311 for row, rowData in enumerate(dataTuples):
312 for column, value in enumerate(rowData):
313 # dates and times should use their text representation
314 if isinstance(value, (datetime, date, time)):
315 value = str(value)
317 worksheet.write(row + 4, column, value)
319 # set the width to the size of the text, with a maximum of 50 characters
320 for column, title in enumerate(columnTitles):
321 # put all of the data in each column into a list
322 columnData = [title] + [rowData[column] for rowData in dataTuples]
324 # find the largest item in the list (and cut it off at 50)
325 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
327 worksheet.set_column(column, column, setColumnWidth + 3)
330def createSpreadsheet(academicYear):
331 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
332 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
334 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
335 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
336 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
337 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
338 makeDataXls("Volunteers By Class Level", volunteerMajorAndClass(academicYear, User.rawClassLevel, classLevel=True), workbook, sheetDesc="All volunteers who participated in service events, by class level. Our source for this data does not seem to be particularly accurate.")
339 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
340 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event during {academicYear}.")
341 makeDataXls("Only All Volunteer Training", onlyCompletedAllVolunteer(academicYear), workbook, sheetDesc="Students who participated in an All Volunteer Training, but did not participate in any service events.")
342 makeDataXls("Retention Rate By Semester", getRetentionRate(academicYear), workbook, sheetDesc="The percentage of students who participated in service events in the fall semester who also participated in a service event in the spring semester. Does not currently account for fall graduations.")
344 fallTerm = getFallTerm(academicYear)
345 springTerm = getSpringTerm(academicYear)
346 makeDataXls(f"Labor Attendance {fallTerm.description}", laborAttendanceByTerm(fallTerm), workbook,sheetDesc=f"Number of labor-only events attended in {fallTerm.description} for each labor student and non-labor attendees, including zero attendance (for labor students).")
347 makeDataXls(f"Labor Attendance {springTerm.description}", laborAttendanceByTerm(springTerm), workbook, sheetDesc=f"Number of labor-only events attended in {springTerm.description} for each labor student and non-labor attendees, including zero attendance (for labor students).")
349 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
350 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
352 workbook.close()
354 return filepath