Coverage for app/logic/volunteerSpreadsheet.py: 25%
147 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-04-10 21:06 +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"]
51 subquery = (base.select(fn.DISTINCT(EventParticipant.user_id).alias('user_id'), fn.CONCAT(User.firstName, ' ', User.lastName).alias("fullname"), User.bnumber)
52 .where(Event.isService == True)).alias('subq')
53 query = Select().from_(subquery).select(subquery.c.fullname, fn.CONCAT(subquery.c.user_id,'@berea.edu'), subquery.c.bnumber)
55 return (columns,query.tuples().execute(mainDB))
58def volunteerProgramHours(academicYear):
59 base = getBaseQuery(academicYear)
61 columns = ["Program Name", "Volunteer Hours", "Volunteer Name", "Volunteer Email", "Volunteer B-Number"]
62 query = (base.select(Program.programName,
63 fn.SUM(EventParticipant.hoursEarned),
64 fn.CONCAT(User.firstName, ' ', User.lastName),
65 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
66 User.bnumber)
67 .where(Event.isService == True)
68 .group_by(Program.programName, EventParticipant.user_id))
70 return (columns, query.tuples())
72def onlyCompletedAllVolunteer(academicYear):
73 base = getBaseQuery(academicYear)
74 base2 = getBaseQuery(academicYear)
76 columns = ["Full Name", "Email", "B-Number"]
77 subQuery = base2.select(EventParticipant.user_id).where(~Event.isAllVolunteerTraining)
79 query = (base.select(fn.CONCAT(User.firstName, ' ', User.lastName),
80 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
81 User.bnumber)
82 .where(Event.isAllVolunteerTraining, EventParticipant.user_id.not_in(subQuery)))
84 return (columns, query.tuples())
86def totalHours(academicYear):
87 base = getBaseQuery(academicYear)
89 columns = ["Total Service Hours", "Total Training Hours", "Other Participation Hours"]
90 query = base.select(fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
91 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
92 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
94 return (columns, query.tuples())
96def totalHoursByProgram(academicYear):
97 base = getBaseQuery(academicYear)
99 columns = ["Program", "Service Hours", "Training Hours", "Other Hours"]
100 query = (base.select(Program.programName,
101 fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
102 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
103 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
104 .group_by(Program.programName)
105 .order_by(Program.programName))
107 return (columns, query.tuples())
109def makeCase(fieldname):
110 return Case(fieldname,((1, "Yes"),(0, "No"),),"None")
112def getAllTermData(term):
113 base = getBaseQuery(term.academicYear)
115 columns = ["Program Name", "Event Name", "Event Description", "Event Date", "Event Start Time", "Event End Time", "Event Location",
116 "Food Provided", "Labor Only", "Training Event", "RSVP Required", "Service Event", "Engagement Event", "All Volunteer Training",
117 "RSVP Limit", "Series #", "Is Repeating Event", "Contact Name", "Contact Email",
118 "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",
119 "Hours Earned"]
120 query = (base.select(Program.programName,Event.name, Event.description, Event.startDate, Event.timeStart, Event.timeEnd, Event.location,
121 makeCase(Event.isFoodProvided), makeCase(Event.isLaborOnly), makeCase(Event.isTraining), makeCase(Event.isRsvpRequired), makeCase(Event.isService), makeCase(Event.isEngagement), makeCase(Event.isAllVolunteerTraining),
122 Event.rsvpLimit, Event.seriesId, makeCase(Event.isRepeating), Event.contactName, Event.contactEmail,
123 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,
124 EventParticipant.hoursEarned)
125 .where(Event.term == term))
127 return (columns,query.tuples())
129def volunteerMajorAndClass(academicYear, column, classLevel=False):
130 base = getBaseQuery(academicYear)
132 columns = ["Major", "Count"]
133 query = (base.select(Case(None, ((column.is_null(), "Unknown"),), column), fn.COUNT(fn.DISTINCT(EventParticipant.user_id)).alias('count'))
134 .where(Event.isService == True)
135 .group_by(column))
137 if classLevel:
138 columns = ["Class Level", "Count"]
139 query = query.order_by(Case(None, ((column == "Freshman", 1),
140 (column == "Sophomore", 2),
141 (column == "Junior", 3),
142 (column == "Senior", 4),
143 (column == "Graduating", 5),
144 (column == "Non-Degree", 6),
145 (column.is_null(), 7)),
146 8))
147 else:
148 query = query.order_by(SQL("count").desc())
150 return (columns, query.tuples())
153def repeatParticipantsPerProgram(academicYear):
154 base = getBaseQuery(academicYear)
156 columns = ["Volunteer", "Program Name", "Event Count"]
157 query = (base.select(fn.CONCAT(User.firstName, " ", User.lastName).alias('fullName'),
158 Program.programName.alias("programName"),
159 fn.COUNT(EventParticipant.event_id).alias('event_count'))
160 .where(Event.isService == True)
161 .group_by(User.firstName, User.lastName, Event.program)
162 .having(fn.COUNT(EventParticipant.event_id) > 1)
163 .order_by(Event.program, User.lastName))
165 return (columns, query.tuples())
168def repeatParticipants(academicYear):
169 base = getBaseQuery(academicYear)
171 columns = ["Number of Events", "Full Name", "Email", "B-Number"]
172 query = (base.select(fn.COUNT(EventParticipant.user_id).alias('count'),
173 fn.CONCAT(User.firstName, ' ', User.lastName),
174 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
175 User.bnumber)
176 .group_by(User.firstName, User.lastName)
177 .having(fn.COUNT(EventParticipant.user_id) > 1)
178 .order_by(SQL("count").desc()))
180 return (columns, query.tuples())
183def getRetentionRate(academicYear):
184 fallParticipationDict = termParticipation(getFallTerm(academicYear))
185 springParticipationDict = termParticipation(getSpringTerm(academicYear))
187 retentionList = []
188 retentionRateDict = calculateRetentionRate(fallParticipationDict, springParticipationDict)
189 for program, retentionRate in retentionRateDict.items():
190 retentionList.append((program, str(round(retentionRate * 100, 2)) + "%"))
192 columns = ["Program", "Retention Rate"]
193 return (columns, retentionList)
196def termParticipation(term):
197 base = getBaseQuery(term.academicYear)
199 participationQuery = (base.select(Event.program, EventParticipant.user_id.alias('participant'), Program.programName.alias("programName"))
200 .where(Event.term == term)
201 .order_by(EventParticipant.user))
203 programParticipationDict = defaultdict(list)
204 for result in participationQuery.dicts():
205 programName = result['programName']
206 participant = result['participant']
207 programParticipationDict[programName].append(participant)
209 return dict(programParticipationDict)
211def graduatingSeniorsVolunteerHours(academicYear):
212 columns = ["Full Name", "Email", "B-Number", "Unique Volunteer Semesters", "Total Volunteer Hours"]
214 currentSeniors = (EventParticipant
215 .select(EventParticipant.user_id)
216 .join(User).switch(EventParticipant)
217 .join(Event)
218 .join(Term)
219 .where(Term.academicYear == academicYear,
220 User.rawClassLevel.in_(["Senior", "Graduating"]),
221 Event.isService == True,
222 Event.deletionDate == None,
223 Event.isCanceled == False)
224 .tuples())
226 query = (EventParticipant
227 .select(fn.CONCAT(User.firstName, ' ', User.lastName),
228 fn.CONCAT(User.username, '@berea.edu'),
229 User.bnumber,
230 fn.COUNT(fn.DISTINCT(Event.term)).alias("semester_count"),
231 fn.SUM(EventParticipant.hoursEarned).alias("total_hours"))
232 .join(User).switch(EventParticipant)
233 .join(Event)
234 .where(Event.isService == True,
235 Event.deletionDate == None,
236 Event.isCanceled == False,
237 EventParticipant.user_id.in_(currentSeniors))
238 .group_by(User.bnumber)
239 .having(fn.COUNT(fn.DISTINCT(Event.term)) >= 4)
240 .order_by(SQL("semester_count").desc()))
242 return (columns, query.tuples())
245def removeNullParticipants(participantList):
246 return list(filter(lambda participant: participant, participantList))
249def calculateRetentionRate(fallDict, springDict):
250 retentionDict = {}
251 for program in fallDict:
252 fallParticipants = set(removeNullParticipants(fallDict[program]))
253 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
254 retentionRate = 0.0
255 try:
256 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
257 except ZeroDivisionError:
258 pass
259 retentionDict[program] = retentionRate
261 return retentionDict
264def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
265 # assumes the length of the column titles matches the length of the data
266 (columnTitles, dataTuples) = sheetData
267 worksheet = workbook.add_worksheet(sheetName)
268 bold = workbook.add_format({'bold': True})
270 worksheet.write_string(0, 0, sheetName, bold)
271 if sheetDesc:
272 worksheet.write_string(1, 0, sheetDesc)
274 for column, title in enumerate(columnTitles):
275 worksheet.write(3, column, title, bold)
277 for row, rowData in enumerate(dataTuples):
278 for column, value in enumerate(rowData):
279 # dates and times should use their text representation
280 if isinstance(value, (datetime, date, time)):
281 value = str(value)
283 worksheet.write(row + 4, column, value)
285 # set the width to the size of the text, with a maximum of 50 characters
286 for column, title in enumerate(columnTitles):
287 # put all of the data in each column into a list
288 columnData = [title] + [rowData[column] for rowData in dataTuples]
290 # find the largest item in the list (and cut it off at 50)
291 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
293 worksheet.set_column(column, column, setColumnWidth + 3)
296def createSpreadsheet(academicYear):
297 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
298 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
300 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
301 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
302 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
303 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
304 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.")
305 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
306 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event during {academicYear}.")
307 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.")
308 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.")
309 makeDataXls("Graduating Seniors", graduatingSeniorsVolunteerHours(academicYear), workbook, sheetDesc="Graduating seniors who have earned any number of service hours for at least 4 unique semesters.")
311 fallTerm = getFallTerm(academicYear)
312 springTerm = getSpringTerm(academicYear)
313 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
314 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
316 workbook.close()
318 return filepath