Coverage for app/logic/volunteerSpreadsheet.py: 100%
161 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-07-08 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", "Term"]
50 subquery = (base.select(fn.DISTINCT(EventParticipant.user_id).alias('user_id'),
51 fn.CONCAT(User.firstName, ' ', User.lastName).alias("fullname"),
52 User.bnumber,
53 Term.description.alias("term"))
54 .where(Event.isService == True)).alias('subq')
56 query = Select().from_(subquery).select(subquery.c.fullname,
57 fn.CONCAT(subquery.c.user_id,'@berea.edu'),
58 subquery.c.bnumber,
59 subquery.c.term)
61 return (columns, query.tuples().execute(mainDB))
64def volunteerProgramHours(academicYear):
65 base = getBaseQuery(academicYear)
67 columns = ["Program Name", "Volunteer Hours", "Volunteer Name", "Volunteer Email", "Volunteer B-Number"]
68 query = (base.select(Program.programName,
69 fn.SUM(EventParticipant.hoursEarned),
70 fn.CONCAT(User.firstName, ' ', User.lastName),
71 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
72 User.bnumber)
73 .where(Event.isService == True)
74 .group_by(Program.programName, EventParticipant.user_id))
76 return (columns, query.tuples())
78def onlyCompletedAllVolunteer(academicYear):
79 base = getBaseQuery(academicYear)
80 base2 = getBaseQuery(academicYear)
82 columns = ["Full Name", "Email", "B-Number"]
83 subQuery = base2.select(EventParticipant.user_id).where(~Event.isAllVolunteerTraining)
85 query = (base.select(fn.CONCAT(User.firstName, ' ', User.lastName),
86 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
87 User.bnumber)
88 .where(Event.isAllVolunteerTraining, EventParticipant.user_id.not_in(subQuery)))
90 return (columns, query.tuples())
92def totalHours(academicYear):
93 base = getBaseQuery(academicYear)
95 columns = ["Total Service Hours", "Total Training Hours", "Other Participation Hours"]
96 query = base.select(fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
97 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
98 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
100 return (columns, query.tuples())
102def totalHoursByProgram(academicYear):
103 base = getBaseQuery(academicYear)
105 columns = ["Program", "Service Hours", "Training Hours", "Other Hours"]
106 query = (base.select(Program.programName,
107 fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
108 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
109 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
110 .group_by(Program.programName)
111 .order_by(Program.programName))
113 return (columns, query.tuples())
115def makeCase(fieldname):
116 return Case(fieldname,((1, "Yes"),(0, "No"),),"None")
118def getAllTermData(term):
119 base = getBaseQuery(term.academicYear)
121 columns = ["Program Name", "Event Name", "Event Description", "Event Date", "Event Start Time", "Event End Time", "Event Location",
122 "Food Provided", "Labor Only", "Training Event", "RSVP Required", "Service Event", "Engagement Event", "All Volunteer Training",
123 "RSVP Limit", "Series #", "Is Repeating Event", "Contact Name", "Contact Email",
124 "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",
125 "Hours Earned"]
126 query = (base.select(Program.programName,Event.name, Event.description, Event.startDate, Event.timeStart, Event.timeEnd, Event.location,
127 makeCase(Event.isFoodProvided), makeCase(Event.isLaborOnly), makeCase(Event.isTraining), makeCase(Event.isRsvpRequired), makeCase(Event.isService), makeCase(Event.isEngagement), makeCase(Event.isAllVolunteerTraining),
128 Event.rsvpLimit, Event.seriesId, makeCase(Event.isRepeating), Event.contactName, Event.contactEmail,
129 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,
130 EventParticipant.hoursEarned)
131 .where(Event.term == term))
133 return (columns,query.tuples())
135def volunteerMajorAndClass(academicYear, column, classLevel=False):
136 base = getBaseQuery(academicYear)
138 columns = ["Major", "Count"]
139 query = (base.select(Case(None, ((column.is_null(), "Unknown"),), column), fn.COUNT(fn.DISTINCT(EventParticipant.user_id)).alias('count'))
140 .where(Event.isService == True)
141 .group_by(column))
143 if classLevel:
144 columns = ["Class Level", "Count"]
145 query = query.order_by(Case(None, ((column == "Freshman", 1),
146 (column == "Sophomore", 2),
147 (column == "Junior", 3),
148 (column == "Senior", 4),
149 (column == "Graduating", 5),
150 (column == "Non-Degree", 6),
151 (column.is_null(), 7)),
152 8))
153 else:
154 query = query.order_by(SQL("count").desc())
156 return (columns, query.tuples())
159def repeatParticipantsPerProgram(academicYear):
160 base = getBaseQuery(academicYear)
162 columns = ["Volunteer", "Program Name", "Event Count"]
163 query = (base.select(fn.CONCAT(User.firstName, " ", User.lastName).alias('fullName'),
164 Program.programName.alias("programName"),
165 fn.COUNT(EventParticipant.event_id).alias('event_count'))
166 .where(Event.isService == True)
167 .group_by(User.firstName, User.lastName, Event.program)
168 .having(fn.COUNT(EventParticipant.event_id) > 1)
169 .order_by(Event.program, User.lastName))
171 return (columns, query.tuples())
174def repeatParticipants(academicYear):
175 base = getBaseQuery(academicYear)
177 columns = ["Number of Events", "Full Name", "Email", "B-Number"]
178 query = (base.select(fn.COUNT(EventParticipant.user_id).alias('count'),
179 fn.CONCAT(User.firstName, ' ', User.lastName),
180 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
181 User.bnumber)
182 .group_by(User.firstName, User.lastName)
183 .having(fn.COUNT(EventParticipant.user_id) > 1)
184 .order_by(SQL("count").desc()))
186 return (columns, query.tuples())
189def getRetentionRate(academicYear):
190 fallParticipationDict = termParticipation(getFallTerm(academicYear))
191 springParticipationDict = termParticipation(getSpringTerm(academicYear))
193 retentionList = []
194 retentionRateDict = calculateRetentionRate(fallParticipationDict, springParticipationDict)
195 for program, retentionRate in retentionRateDict.items():
196 retentionList.append((program, str(round(retentionRate * 100, 2)) + "%"))
198 columns = ["Program", "Retention Rate"]
199 return (columns, retentionList)
202def termParticipation(term):
203 base = getBaseQuery(term.academicYear)
205 participationQuery = (base.select(Event.program, EventParticipant.user_id.alias('participant'), Program.programName.alias("programName"))
206 .where(Event.term == term)
207 .order_by(EventParticipant.user))
209 programParticipationDict = defaultdict(list)
210 for result in participationQuery.dicts():
211 programName = result['programName']
212 participant = result['participant']
213 programParticipationDict[programName].append(participant)
215 return dict(programParticipationDict)
217def graduatingSeniorsVolunteerHours(academicYear):
218 columns = ["Full Name", "Email", "B-Number", "Unique Volunteer Semesters", "Total Volunteer Hours"]
220 currentSeniors = (User.select().where(User.rawClassLevel.in_(["Senior", "Graduating"])))
222 query = (EventParticipant
223 .select(fn.CONCAT(User.firstName, ' ', User.lastName),
224 fn.CONCAT(User.username, '@berea.edu'),
225 User.bnumber,
226 fn.COUNT(fn.DISTINCT(Event.term)).alias("semester_count"),
227 fn.SUM(EventParticipant.hoursEarned).alias("total_hours"))
228 .join(User).switch(EventParticipant)
229 .join(Event)
230 .where(Event.isService == True,
231 Event.deletionDate == None,
232 Event.isCanceled == False,
233 EventParticipant.user_id.in_(currentSeniors))
234 .group_by(User.bnumber)
235 .having(fn.COUNT(fn.DISTINCT(Event.term)) >= 4)
236 .order_by(SQL("semester_count").desc()))
238 return (columns, query.tuples())
241def removeNullParticipants(participantList):
242 return list(filter(lambda participant: participant, participantList))
245def calculateRetentionRate(fallDict, springDict):
246 retentionDict = {}
247 for program in fallDict:
248 fallParticipants = set(removeNullParticipants(fallDict[program]))
249 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
250 retentionRate = 0.0
251 try:
252 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
253 except ZeroDivisionError:
254 pass
255 retentionDict[program] = retentionRate
257 return retentionDict
259def laborAttendanceByTerm(term):
260 fullName = fn.CONCAT(User.firstName, ' ', User.lastName).alias('fullName')
261 email = fn.CONCAT(User.username, '@berea.edu').alias('email')
262 meetingsAttended = fn.COUNT(fn.DISTINCT(Event.id)).alias('meetingsAttended')
264 validEvent = (
265 (EventParticipant.event == Event.id) &
266 (Event.term == term) &
267 (Event.isLaborOnly == True) &
268 (Event.deletionDate.is_null()) &
269 (Event.isCanceled == False))
271 CLTerm = Term.alias()
272 laborMembers = (
273 CeltsLabor
274 .select(fn.DISTINCT(CeltsLabor.user_id))
275 .join(CLTerm, on=(CeltsLabor.term == CLTerm.id))
276 .where(
277 (CeltsLabor.term == term) |
278 ((CLTerm.academicYear == term.academicYear) & (CeltsLabor.isAcademicYear == True))
279 ))
281 laborQuery = (
282 CeltsLabor
283 .select(fullName, User.bnumber, email, meetingsAttended)
284 .join(User)
285 .switch(CeltsLabor)
286 .join(EventParticipant, JOIN.LEFT_OUTER, on=(CeltsLabor.user == EventParticipant.user))
287 .join(Event, JOIN.LEFT_OUTER,on=validEvent)
288 .where(CeltsLabor.user.in_(laborMembers))
289 .group_by(CeltsLabor.user))
291 nonLaborQuery = (
292 EventParticipant
293 .select(fullName, User.bnumber, email, meetingsAttended)
294 .join(User)
295 .switch(EventParticipant)
296 .join(Event,on=validEvent)
297 .where(EventParticipant.user.not_in(laborMembers))
298 .group_by(EventParticipant.user))
300 query = laborQuery.union(nonLaborQuery).order_by(SQL('fullName'))
301 columns = ("Full Name", "B-Number", "Email", "Meetings Attended")
303 return (columns, query.tuples())
305def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
306 # assumes the length of the column titles matches the length of the data
307 (columnTitles, dataTuples) = sheetData
308 worksheet = workbook.add_worksheet(sheetName)
309 bold = workbook.add_format({'bold': True})
311 worksheet.write_string(0, 0, sheetName, bold)
312 if sheetDesc:
313 worksheet.write_string(1, 0, sheetDesc)
315 for column, title in enumerate(columnTitles):
316 worksheet.write(3, column, title, bold)
318 for row, rowData in enumerate(dataTuples):
319 for column, value in enumerate(rowData):
320 # dates and times should use their text representation
321 if isinstance(value, (datetime, date, time)):
322 value = str(value)
324 worksheet.write(row + 4, column, value)
326 # set the width to the size of the text, with a maximum of 50 characters
327 for column, title in enumerate(columnTitles):
328 # put all of the data in each column into a list
329 columnData = [title] + [rowData[column] for rowData in dataTuples]
331 # find the largest item in the list (and cut it off at 50)
332 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
334 worksheet.set_column(column, column, setColumnWidth + 3)
337def createSpreadsheet(academicYear):
338 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
339 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
341 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
342 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
343 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
344 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
345 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.")
346 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
347 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event per term during {academicYear}.")
348 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.")
349 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.")
350 makeDataXls("Graduating Seniors", graduatingSeniorsVolunteerHours(academicYear), workbook, sheetDesc="Graduating seniors who have earned any number of service hours for at least 4 unique semesters.")
352 fallTerm = getFallTerm(academicYear)
353 springTerm = getSpringTerm(academicYear)
354 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).")
355 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).")
357 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
358 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
360 workbook.close()
362 return filepath