Coverage for app/logic/volunteerSpreadsheet.py: 25%
147 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-04-27 20:35 +0000
1from importlib.abc import ResourceReader
2from os import major
3import xlsxwriter
4from peewee import fn, Case, JOIN, SQL, Select
5from collections import defaultdict
6from datetime import date, datetime,time
8from app import app
9from app.models import mainDB
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", "Labor Only", "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.isLaborOnly), 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 = (EventParticipant
222 .select(EventParticipant.user_id)
223 .join(User).switch(EventParticipant)
224 .join(Event)
225 .join(Term)
226 .where(Term.academicYear == academicYear,
227 User.rawClassLevel.in_(["Senior", "Graduating"]),
228 Event.isService == True,
229 Event.deletionDate == None,
230 Event.isCanceled == False)
231 .tuples())
233 query = (EventParticipant
234 .select(fn.CONCAT(User.firstName, ' ', User.lastName),
235 fn.CONCAT(User.username, '@berea.edu'),
236 User.bnumber,
237 fn.COUNT(fn.DISTINCT(Event.term)).alias("semester_count"),
238 fn.SUM(EventParticipant.hoursEarned).alias("total_hours"))
239 .join(User).switch(EventParticipant)
240 .join(Event)
241 .where(Event.isService == True,
242 Event.deletionDate == None,
243 Event.isCanceled == False,
244 EventParticipant.user_id.in_(currentSeniors))
245 .group_by(User.bnumber)
246 .having(fn.COUNT(fn.DISTINCT(Event.term)) >= 4)
247 .order_by(SQL("semester_count").desc()))
249 return (columns, query.tuples())
252def removeNullParticipants(participantList):
253 return list(filter(lambda participant: participant, participantList))
256def calculateRetentionRate(fallDict, springDict):
257 retentionDict = {}
258 for program in fallDict:
259 fallParticipants = set(removeNullParticipants(fallDict[program]))
260 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
261 retentionRate = 0.0
262 try:
263 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
264 except ZeroDivisionError:
265 pass
266 retentionDict[program] = retentionRate
268 return retentionDict
271def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
272 # assumes the length of the column titles matches the length of the data
273 (columnTitles, dataTuples) = sheetData
274 worksheet = workbook.add_worksheet(sheetName)
275 bold = workbook.add_format({'bold': True})
277 worksheet.write_string(0, 0, sheetName, bold)
278 if sheetDesc:
279 worksheet.write_string(1, 0, sheetDesc)
281 for column, title in enumerate(columnTitles):
282 worksheet.write(3, column, title, bold)
284 for row, rowData in enumerate(dataTuples):
285 for column, value in enumerate(rowData):
286 # dates and times should use their text representation
287 if isinstance(value, (datetime, date, time)):
288 value = str(value)
290 worksheet.write(row + 4, column, value)
292 # set the width to the size of the text, with a maximum of 50 characters
293 for column, title in enumerate(columnTitles):
294 # put all of the data in each column into a list
295 columnData = [title] + [rowData[column] for rowData in dataTuples]
297 # find the largest item in the list (and cut it off at 50)
298 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
300 worksheet.set_column(column, column, setColumnWidth + 3)
303def createSpreadsheet(academicYear):
304 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
305 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
307 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
308 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
309 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
310 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
311 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.")
312 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
313 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event per term during {academicYear}.")
314 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.")
315 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.")
316 makeDataXls("Graduating Seniors", graduatingSeniorsVolunteerHours(academicYear), workbook, sheetDesc="Graduating seniors who have earned any number of service hours for at least 4 unique semesters.")
318 fallTerm = getFallTerm(academicYear)
319 springTerm = getSpringTerm(academicYear)
320 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
321 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
323 workbook.close()
325 return filepath