Coverage for app/logic/volunteerSpreadsheet.py: 100%
155 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-04-16 19:08 +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"]
50 subquery = (base.select(fn.DISTINCT(EventParticipant.user_id).alias('user_id'), fn.CONCAT(User.firstName, ' ', User.lastName).alias("fullname"), User.bnumber)
51 .where(Event.isService == True)).alias('subq')
52 query = Select().from_(subquery).select(subquery.c.fullname, fn.CONCAT(subquery.c.user_id,'@berea.edu'), subquery.c.bnumber)
54 return (columns,query.tuples().execute(mainDB))
57def volunteerProgramHours(academicYear):
58 base = getBaseQuery(academicYear)
60 columns = ["Program Name", "Volunteer Hours", "Volunteer Name", "Volunteer Email", "Volunteer B-Number"]
61 query = (base.select(Program.programName,
62 fn.SUM(EventParticipant.hoursEarned),
63 fn.CONCAT(User.firstName, ' ', User.lastName),
64 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
65 User.bnumber)
66 .where(Event.isService == True)
67 .group_by(Program.programName, EventParticipant.user_id))
69 return (columns, query.tuples())
71def onlyCompletedAllVolunteer(academicYear):
72 base = getBaseQuery(academicYear)
73 base2 = getBaseQuery(academicYear)
75 columns = ["Full Name", "Email", "B-Number"]
76 subQuery = base2.select(EventParticipant.user_id).where(~Event.isAllVolunteerTraining)
78 query = (base.select(fn.CONCAT(User.firstName, ' ', User.lastName),
79 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
80 User.bnumber)
81 .where(Event.isAllVolunteerTraining, EventParticipant.user_id.not_in(subQuery)))
83 return (columns, query.tuples())
85def totalHours(academicYear):
86 base = getBaseQuery(academicYear)
88 columns = ["Total Service Hours", "Total Training Hours", "Other Participation Hours"]
89 query = base.select(fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
90 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
91 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
93 return (columns, query.tuples())
95def totalHoursByProgram(academicYear):
96 base = getBaseQuery(academicYear)
98 columns = ["Program", "Service Hours", "Training Hours", "Other Hours"]
99 query = (base.select(Program.programName,
100 fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
101 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
102 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
103 .group_by(Program.programName)
104 .order_by(Program.programName))
106 return (columns, query.tuples())
108def makeCase(fieldname):
109 return Case(fieldname,((1, "Yes"),(0, "No"),),"None")
111def getAllTermData(term):
112 base = getBaseQuery(term.academicYear)
114 columns = ["Program Name", "Event Name", "Event Description", "Event Date", "Event Start Time", "Event End Time", "Event Location",
115 "Food Provided", "Labor Only", "Training Event", "RSVP Required", "Service Event", "Engagement Event", "All Volunteer Training",
116 "RSVP Limit", "Series #", "Is Repeating Event", "Contact Name", "Contact Email",
117 "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",
118 "Hours Earned"]
119 query = (base.select(Program.programName,Event.name, Event.description, Event.startDate, Event.timeStart, Event.timeEnd, Event.location,
120 makeCase(Event.isFoodProvided), makeCase(Event.isLaborOnly), makeCase(Event.isTraining), makeCase(Event.isRsvpRequired), makeCase(Event.isService), makeCase(Event.isEngagement), makeCase(Event.isAllVolunteerTraining),
121 Event.rsvpLimit, Event.seriesId, makeCase(Event.isRepeating), Event.contactName, Event.contactEmail,
122 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,
123 EventParticipant.hoursEarned)
124 .where(Event.term == term))
126 return (columns,query.tuples())
128def volunteerMajorAndClass(academicYear, column, classLevel=False):
129 base = getBaseQuery(academicYear)
131 columns = ["Major", "Count"]
132 query = (base.select(Case(None, ((column.is_null(), "Unknown"),), column), fn.COUNT(fn.DISTINCT(EventParticipant.user_id)).alias('count'))
133 .where(Event.isService == True)
134 .group_by(column))
136 if classLevel:
137 columns = ["Class Level", "Count"]
138 query = query.order_by(Case(None, ((column == "Freshman", 1),
139 (column == "Sophomore", 2),
140 (column == "Junior", 3),
141 (column == "Senior", 4),
142 (column == "Graduating", 5),
143 (column == "Non-Degree", 6),
144 (column.is_null(), 7)),
145 8))
146 else:
147 query = query.order_by(SQL("count").desc())
149 return (columns, query.tuples())
152def repeatParticipantsPerProgram(academicYear):
153 base = getBaseQuery(academicYear)
155 columns = ["Volunteer", "Program Name", "Event Count"]
156 query = (base.select(fn.CONCAT(User.firstName, " ", User.lastName).alias('fullName'),
157 Program.programName.alias("programName"),
158 fn.COUNT(EventParticipant.event_id).alias('event_count'))
159 .where(Event.isService == True)
160 .group_by(User.firstName, User.lastName, Event.program)
161 .having(fn.COUNT(EventParticipant.event_id) > 1)
162 .order_by(Event.program, User.lastName))
164 return (columns, query.tuples())
167def repeatParticipants(academicYear):
168 base = getBaseQuery(academicYear)
170 columns = ["Number of Events", "Full Name", "Email", "B-Number"]
171 query = (base.select(fn.COUNT(EventParticipant.user_id).alias('count'),
172 fn.CONCAT(User.firstName, ' ', User.lastName),
173 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
174 User.bnumber)
175 .group_by(User.firstName, User.lastName)
176 .having(fn.COUNT(EventParticipant.user_id) > 1)
177 .order_by(SQL("count").desc()))
179 return (columns, query.tuples())
182def getRetentionRate(academicYear):
183 fallParticipationDict = termParticipation(getFallTerm(academicYear))
184 springParticipationDict = termParticipation(getSpringTerm(academicYear))
186 retentionList = []
187 retentionRateDict = calculateRetentionRate(fallParticipationDict, springParticipationDict)
188 for program, retentionRate in retentionRateDict.items():
189 retentionList.append((program, str(round(retentionRate * 100, 2)) + "%"))
191 columns = ["Program", "Retention Rate"]
192 return (columns, retentionList)
195def termParticipation(term):
196 base = getBaseQuery(term.academicYear)
198 participationQuery = (base.select(Event.program, EventParticipant.user_id.alias('participant'), Program.programName.alias("programName"))
199 .where(Event.term == term)
200 .order_by(EventParticipant.user))
202 programParticipationDict = defaultdict(list)
203 for result in participationQuery.dicts():
204 programName = result['programName']
205 participant = result['participant']
206 programParticipationDict[programName].append(participant)
208 return dict(programParticipationDict)
211def removeNullParticipants(participantList):
212 return list(filter(lambda participant: participant, participantList))
215def calculateRetentionRate(fallDict, springDict):
216 retentionDict = {}
217 for program in fallDict:
218 fallParticipants = set(removeNullParticipants(fallDict[program]))
219 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
220 retentionRate = 0.0
221 try:
222 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
223 except ZeroDivisionError:
224 pass
225 retentionDict[program] = retentionRate
227 return retentionDict
229def laborAttendanceByTerm(term):
230 fullName = fn.CONCAT(User.firstName, ' ', User.lastName).alias('fullName')
231 email = fn.CONCAT(User.username, '@berea.edu').alias('email')
232 meetingsAttended = fn.COUNT(fn.DISTINCT(Event.id)).alias('meetingsAttended')
234 validEvent = (
235 (EventParticipant.event == Event.id) &
236 (Event.term == term) &
237 (Event.isLaborOnly == True) &
238 (Event.deletionDate.is_null()) &
239 (Event.isCanceled == False))
241 CLTerm = Term.alias()
242 laborMembers = (
243 CeltsLabor
244 .select(fn.DISTINCT(CeltsLabor.user_id))
245 .join(CLTerm, on=(CeltsLabor.term == CLTerm.id))
246 .where(
247 (CeltsLabor.term == term) |
248 ((CLTerm.academicYear == term.academicYear) & (CeltsLabor.isAcademicYear == True))
249 ))
251 laborQuery = (
252 CeltsLabor
253 .select(fullName, User.bnumber, email, meetingsAttended)
254 .join(User)
255 .switch(CeltsLabor)
256 .join(EventParticipant, JOIN.LEFT_OUTER, on=(CeltsLabor.user == EventParticipant.user))
257 .join(Event, JOIN.LEFT_OUTER,on=validEvent)
258 .where(CeltsLabor.user.in_(laborMembers))
259 .group_by(CeltsLabor.user))
261 nonLaborQuery = (
262 EventParticipant
263 .select(fullName, User.bnumber, email, meetingsAttended)
264 .join(User)
265 .switch(EventParticipant)
266 .join(Event,on=validEvent)
267 .where(EventParticipant.user.not_in(laborMembers))
268 .group_by(EventParticipant.user))
270 query = laborQuery.union(nonLaborQuery).order_by(SQL('fullName'))
271 columns = ("Full Name", "B-Number", "Email", "Meetings Attended")
273 return (columns, query.tuples())
275def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
276 # assumes the length of the column titles matches the length of the data
277 (columnTitles, dataTuples) = sheetData
278 worksheet = workbook.add_worksheet(sheetName)
279 bold = workbook.add_format({'bold': True})
281 worksheet.write_string(0, 0, sheetName, bold)
282 if sheetDesc:
283 worksheet.write_string(1, 0, sheetDesc)
285 for column, title in enumerate(columnTitles):
286 worksheet.write(3, column, title, bold)
288 for row, rowData in enumerate(dataTuples):
289 for column, value in enumerate(rowData):
290 # dates and times should use their text representation
291 if isinstance(value, (datetime, date, time)):
292 value = str(value)
294 worksheet.write(row + 4, column, value)
296 # set the width to the size of the text, with a maximum of 50 characters
297 for column, title in enumerate(columnTitles):
298 # put all of the data in each column into a list
299 columnData = [title] + [rowData[column] for rowData in dataTuples]
301 # find the largest item in the list (and cut it off at 50)
302 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
304 worksheet.set_column(column, column, setColumnWidth + 3)
307def createSpreadsheet(academicYear):
308 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
309 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
311 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
312 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
313 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
314 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
315 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.")
316 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
317 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event during {academicYear}.")
318 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.")
319 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.")
321 fallTerm = getFallTerm(academicYear)
322 springTerm = getSpringTerm(academicYear)
323 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).")
324 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).")
326 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
327 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
329 workbook.close()
331 return filepath