Coverage for app/logic/volunteerSpreadsheet.py: 93%
173 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-08-24 19:35 +0000
1from os import major
2import xlsxwriter
3from peewee import ModelSelect, fn, Case, JOIN, SQL, Select
4from playhouse.shortcuts import model_to_dict
5from collections import defaultdict
6from datetime import date, datetime,time
7from app import app
8from app.models import mainDB
9from app.models.celtsLabor import CeltsLabor
10from app.models.eventParticipant import EventParticipant
11from app.models.user import User
12from app.models.program import Program
13from app.models.event import Event
14from app.models.term import Term
16### READ ME FIRST! #################################################################
17#
18# It's very important that we understand the distinction between volunteers earning
19# service hours and other things that we track in our system, like student labor,
20# bonner students, trainings, etc. The way we use 'volunteer' may not necessarily
21# be the way CELTS uses it.
22#
23####################################################################################
25def getFallTerm(academicYear):
26 return Term.get(Term.description % "Fall%", Term.academicYear == academicYear)
28def getSpringTerm(academicYear):
29 return Term.get(Term.description % "Spring%", Term.academicYear == academicYear)
32def getBaseQuery(academicYear):
34 # As we add joins to this query, watch out for duplicate participant rows being added
36 return (EventParticipant.select()
37 .join(User).switch(EventParticipant)
38 .join(Event)
39 .join(Program).switch(Event)
40 .join(Term)
41 .where(Term.academicYear == academicYear,
42 Event.deletionDate == None,
43 Event.isCanceled == False)
44 .order_by(Event.startDate))
47def getUniqueVolunteers(academicYear):
48 base = getBaseQuery(academicYear)
50 columns = ["Full Name", "Email", "B-Number", "Term"]
51 subquery = (base.select(fn.DISTINCT(EventParticipant.user_id).alias('user_id'),
52 fn.CONCAT(User.firstName, ' ', User.lastName).alias("fullname"),
53 User.bnumber,
54 Term.description.alias("term"))
55 .where(Event.isService == True)).alias('subq')
57 query = Select().from_(subquery).select(subquery.c.fullname,
58 fn.CONCAT(subquery.c.user_id,'@berea.edu'),
59 subquery.c.bnumber,
60 subquery.c.term)
62 return (columns, query.tuples().execute(mainDB))
65def volunteerProgramHours(academicYear):
66 base = getBaseQuery(academicYear)
68 columns = ["Program Name", "Volunteer Hours", "Volunteer Name", "Volunteer Email", "Volunteer B-Number"]
69 query = (base.select(Program.programName,
70 fn.SUM(EventParticipant.hoursEarned),
71 fn.CONCAT(User.firstName, ' ', User.lastName),
72 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
73 User.bnumber)
74 .where(Event.isService == True)
75 .group_by(Program.programName, EventParticipant.user_id))
77 return (columns, query.tuples())
79def onlyCompletedAllVolunteer(academicYear):
80 base = getBaseQuery(academicYear)
81 base2 = getBaseQuery(academicYear)
83 columns = ["Full Name", "Email", "B-Number"]
84 subQuery = base2.select(EventParticipant.user_id).where(~Event.isAllVolunteerTraining)
86 query = (base.select(fn.CONCAT(User.firstName, ' ', User.lastName),
87 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
88 User.bnumber)
89 .where(Event.isAllVolunteerTraining, EventParticipant.user_id.not_in(subQuery)))
91 return (columns, query.tuples())
93def totalHours(academicYear):
94 base = getBaseQuery(academicYear)
96 columns = ["Total Service Hours", "Total Training Hours", "Other Participation Hours"]
97 query = base.select(fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
98 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
99 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
101 return (columns, query.tuples())
103def totalHoursByProgram(academicYear):
104 base = getBaseQuery(academicYear)
106 columns = ["Program", "Service Hours", "Training Hours", "Other Hours"]
107 query = (base.select(Program.programName,
108 fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
109 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
110 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
111 .group_by(Program.programName)
112 .order_by(Program.programName))
114 return (columns, query.tuples())
116def makeCase(fieldname):
117 return Case(fieldname,((1, "Yes"),(0, "No"),),"None")
119def getAllTermData(term):
120 base = getBaseQuery(term.academicYear)
122 columns = ["Program Name", "Event Name", "Event Description", "Event Date", "Event Start Time", "Event End Time", "Event Location",
123 "Food Provided", "Includes Labor", "Training Event", "RSVP Required", "Service Event", "Engagement Event", "All Volunteer Training",
124 "RSVP Limit", "Series #", "Is Repeating Event", "Contact Name", "Contact Email",
125 "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",
126 "Hours Earned"]
127 query = (base.select(Program.programName,Event.name, Event.description, Event.startDate, Event.timeStart, Event.timeEnd, Event.location,
128 makeCase(Event.isFoodProvided), makeCase(Event.allowsLabor), makeCase(Event.isTraining), makeCase(Event.isRsvpRequired), makeCase(Event.isService), makeCase(Event.isEngagement), makeCase(Event.isAllVolunteerTraining),
129 Event.rsvpLimit, Event.seriesId, makeCase(Event.isRepeating), Event.contactName, Event.contactEmail,
130 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,
131 EventParticipant.hoursEarned)
132 .where(Event.term == term))
134 return (columns,query.tuples())
136def volunteerMajorAndClass(academicYear, column, classLevel=False):
137 base = getBaseQuery(academicYear)
139 columns = ["Major", "Count"]
140 query = (base.select(Case(None, ((column.is_null(), "Unknown"),), column), fn.COUNT(fn.DISTINCT(EventParticipant.user_id)).alias('count'))
141 .where(Event.isService == True)
142 .group_by(column))
144 if classLevel:
145 columns = ["Class Level", "Count"]
146 query = query.order_by(Case(None, ((column == "Freshman", 1),
147 (column == "Sophomore", 2),
148 (column == "Junior", 3),
149 (column == "Senior", 4),
150 (column == "Graduating", 5),
151 (column == "Non-Degree", 6),
152 (column.is_null(), 7)),
153 8))
154 else:
155 query = query.order_by(SQL("count").desc())
157 return (columns, query.tuples())
160def repeatParticipantsPerProgram(academicYear):
161 base = getBaseQuery(academicYear)
163 columns = ["Volunteer", "Program Name", "Event Count"]
164 query = (base.select(fn.CONCAT(User.firstName, " ", User.lastName).alias('fullName'),
165 Program.programName.alias("programName"),
166 fn.COUNT(EventParticipant.event_id).alias('event_count'))
167 .where(Event.isService == True)
168 .group_by(User.firstName, User.lastName, Event.program)
169 .having(fn.COUNT(EventParticipant.event_id) > 1)
170 .order_by(Event.program, User.lastName))
172 return (columns, query.tuples())
175def repeatParticipants(academicYear):
176 base = getBaseQuery(academicYear)
178 columns = ["Number of Events", "Full Name", "Email", "B-Number"]
179 query = (base.select(fn.COUNT(EventParticipant.user_id).alias('count'),
180 fn.CONCAT(User.firstName, ' ', User.lastName),
181 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
182 User.bnumber)
183 .group_by(User.firstName, User.lastName)
184 .having(fn.COUNT(EventParticipant.user_id) > 1)
185 .order_by(SQL("count").desc()))
187 return (columns, query.tuples())
190def getRetentionRate(academicYear):
191 fallParticipationDict = termParticipation(getFallTerm(academicYear))
192 springParticipationDict = termParticipation(getSpringTerm(academicYear))
194 retentionList = []
195 retentionRateDict = calculateRetentionRate(fallParticipationDict, springParticipationDict)
196 for program, retentionRate in retentionRateDict.items():
197 retentionList.append((program, str(round(retentionRate * 100, 2)) + "%"))
199 columns = ["Program", "Retention Rate"]
200 return (columns, retentionList)
203def termParticipation(term):
204 base = getBaseQuery(term.academicYear)
206 participationQuery = (base.select(Event.program, EventParticipant.user_id.alias('participant'), Program.programName.alias("programName"))
207 .where(Event.term == term)
208 .order_by(EventParticipant.user))
210 programParticipationDict = defaultdict(list)
211 for result in participationQuery.dicts():
212 programName = result['programName']
213 participant = result['participant']
214 programParticipationDict[programName].append(participant)
216 return dict(programParticipationDict)
218def graduatingSeniorsVolunteerHours(academicYear):
219 columns = ["Full Name", "Email", "B-Number", "Unique Volunteer Semesters", "Total Volunteer Hours"]
221 currentSeniors = (User.select().where(User.rawClassLevel.in_(["Senior", "Graduating"])))
223 query = (EventParticipant
224 .select(fn.CONCAT(User.firstName, ' ', User.lastName),
225 fn.CONCAT(User.username, '@berea.edu'),
226 User.bnumber,
227 fn.COUNT(fn.DISTINCT(Event.term)).alias("semester_count"),
228 fn.SUM(EventParticipant.hoursEarned).alias("total_hours"))
229 .join(User).switch(EventParticipant)
230 .join(Event)
231 .where(Event.isService == True,
232 Event.deletionDate == None,
233 Event.isCanceled == False,
234 EventParticipant.user_id.in_(currentSeniors))
235 .group_by(User.bnumber)
236 .having(fn.COUNT(fn.DISTINCT(Event.term)) >= 4)
237 .order_by(SQL("semester_count").desc()))
239 return (columns, query.tuples())
242def removeNullParticipants(participantList):
243 return list(filter(lambda participant: participant, participantList))
246def calculateRetentionRate(fallDict, springDict):
247 retentionDict = {}
248 for program in fallDict:
249 fallParticipants = set(removeNullParticipants(fallDict[program]))
250 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
251 retentionRate = 0.0
252 try:
253 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
254 except ZeroDivisionError:
255 pass
256 retentionDict[program] = retentionRate
258 return retentionDict
260def laborAttendanceByTerm(term):
261 fullName = fn.CONCAT(User.firstName, ' ', User.lastName).alias('fullName')
262 email = fn.CONCAT(User.username, '@berea.edu').alias('email')
263 meetingsAttended = fn.COUNT(fn.DISTINCT(Event.id)).alias('meetingsAttended')
265 validEvent = (
266 (EventParticipant.event == Event.id) &
267 (Event.term == term) &
268 (Event.isLaborOnly == True) &
269 (Event.deletionDate.is_null()) &
270 (Event.isCanceled == False))
272 CLTerm = Term.alias()
273 laborMembers = (
274 CeltsLabor
275 .select(fn.DISTINCT(CeltsLabor.user_id))
276 .join(CLTerm, on=(CeltsLabor.term == CLTerm.id))
277 .where(
278 (CeltsLabor.term == term) |
279 ((CLTerm.academicYear == term.academicYear) & (CeltsLabor.isAcademicYear == True))
280 ))
282 laborQuery = (
283 CeltsLabor
284 .select(fullName, User.bnumber, email, meetingsAttended)
285 .join(User)
286 .switch(CeltsLabor)
287 .join(EventParticipant, JOIN.LEFT_OUTER, on=(CeltsLabor.user == EventParticipant.user))
288 .join(Event, JOIN.LEFT_OUTER,on=validEvent)
289 .where(CeltsLabor.user.in_(laborMembers))
290 .group_by(CeltsLabor.user))
292 nonLaborQuery = (
293 EventParticipant
294 .select(fullName, User.bnumber, email, meetingsAttended)
295 .join(User)
296 .switch(EventParticipant)
297 .join(Event,on=validEvent)
298 .where(EventParticipant.user.not_in(laborMembers))
299 .group_by(EventParticipant.user))
301 query = laborQuery.union(nonLaborQuery).order_by(SQL('fullName'))
302 columns = ("Full Name", "B-Number", "Email", "Meetings Attended")
304 return (columns, query.tuples())
306def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
307 # assumes the length of the column titles matches the length of the data
308 (columnTitles, dataRows) = sheetData
309 worksheet = workbook.add_worksheet(sheetName)
310 bold = workbook.add_format({'bold': True})
312 worksheet.write_string(0, 0, sheetName, bold)
313 if sheetDesc:
314 worksheet.write_string(1, 0, sheetDesc)
316 for column, title in enumerate(columnTitles):
317 worksheet.write(3, column, title, bold)
319 if type(dataRows) == list:
320 for row, rowData in enumerate(dataRows):
321 col_idx = 0
322 for value in rowData:
323 # dates and times should use their text representation
324 if isinstance(value, (datetime, date, time)):
325 value = str(value)
327 worksheet.write(row + 4, col_idx, str(value))
328 col_idx += 1
330 elif type(dataRows) == dict:
331 idx = 0
332 # Each participant
333 for row, rowData in dataRows.items():
334 idx2 = 0
335 # All participant data
336 for key, value in dict(rowData).items():
337 if key == "userObj":
338 # Data inside user object
339 for key, userObjVal in model_to_dict(rowData[key], only=(User.username, User.bnumber, User.email, User.phoneNumber, User.firstName, User.lastName, User.cpoNumber, User.major, User.rawClassLevel, User.dietRestriction, User.lastHandbookSignature)).items():
340 worksheet.write(idx + 4, idx2, userObjVal)
341 idx2 += 1
342 else:
343 # participation data outside user object
344 worksheet.write(idx + 4, idx2, str(value))
345 idx2 += 1
347 idx += 1
349def createSpreadsheet(academicYear):
350 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
351 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
353 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
354 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
355 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
356 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
357 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.")
358 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
359 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event per term during {academicYear}.")
360 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.")
361 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.")
362 makeDataXls("Graduating Seniors", graduatingSeniorsVolunteerHours(academicYear), workbook, sheetDesc="Graduating seniors who have earned any number of service hours for at least 4 unique semesters.")
364 fallTerm = getFallTerm(academicYear)
365 springTerm = getSpringTerm(academicYear)
366 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).")
367 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).")
369 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
370 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
372 workbook.close()
374 return filepath