Coverage for app/logic/volunteerSpreadsheet.py: 25%
147 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-05-06 15:03 +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 = (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
261def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
262 # assumes the length of the column titles matches the length of the data
263 (columnTitles, dataTuples) = sheetData
264 worksheet = workbook.add_worksheet(sheetName)
265 bold = workbook.add_format({'bold': True})
267 worksheet.write_string(0, 0, sheetName, bold)
268 if sheetDesc:
269 worksheet.write_string(1, 0, sheetDesc)
271 for column, title in enumerate(columnTitles):
272 worksheet.write(3, column, title, bold)
274 for row, rowData in enumerate(dataTuples):
275 for column, value in enumerate(rowData):
276 # dates and times should use their text representation
277 if isinstance(value, (datetime, date, time)):
278 value = str(value)
280 worksheet.write(row + 4, column, value)
282 # set the width to the size of the text, with a maximum of 50 characters
283 for column, title in enumerate(columnTitles):
284 # put all of the data in each column into a list
285 columnData = [title] + [rowData[column] for rowData in dataTuples]
287 # find the largest item in the list (and cut it off at 50)
288 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
290 worksheet.set_column(column, column, setColumnWidth + 3)
293def createSpreadsheet(academicYear):
294 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
295 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
297 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
298 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
299 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
300 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
301 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.")
302 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
303 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event per term during {academicYear}.")
304 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.")
305 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.")
306 makeDataXls("Graduating Seniors", graduatingSeniorsVolunteerHours(academicYear), workbook, sheetDesc="Graduating seniors who have earned any number of service hours for at least 4 unique semesters.")
308 fallTerm = getFallTerm(academicYear)
309 springTerm = getSpringTerm(academicYear)
310 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
311 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
313 workbook.close()
315 return filepath