Coverage for app/logic/volunteerSpreadsheet.py: 100%
146 statements
« prev ^ index » next coverage.py v7.10.2, created at 2026-05-01 14:29 +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.eventParticipant import EventParticipant
9from app.models.user import User
10from app.models.program import Program
11from app.models.event import Event
12from app.models.term import Term
14### READ ME FIRST! #################################################################
15#
16# It's very important that we understand the distinction between volunteers earning
17# service hours and other things that we track in our system, like student labor,
18# bonner students, trainings, etc. The way we use 'volunteer' may not necessarily
19# be the way CELTS uses it.
20#
21####################################################################################
23def getFallTerm(academicYear):
24 return Term.get(Term.description % "Fall%", Term.academicYear == academicYear)
26def getSpringTerm(academicYear):
27 return Term.get(Term.description % "Spring%", Term.academicYear == academicYear)
30def getBaseQuery(academicYear):
32 # As we add joins to this query, watch out for duplicate participant rows being added
34 return (EventParticipant.select()
35 .join(User).switch(EventParticipant)
36 .join(Event)
37 .join(Program).switch(Event)
38 .join(Term)
39 .where(Term.academicYear == academicYear,
40 Event.deletionDate == None,
41 Event.isCanceled == False)
42 .order_by(Event.startDate))
45def getUniqueVolunteers(academicYear):
46 base = getBaseQuery(academicYear)
48 columns = ["Full Name", "Email", "B-Number"]
49 subquery = (base.select(fn.DISTINCT(EventParticipant.user_id).alias('user_id'), fn.CONCAT(User.firstName, ' ', User.lastName).alias("fullname"), User.bnumber)
50 .where(Event.isService == True)).alias('subq')
51 query = Select().from_(subquery).select(subquery.c.fullname, fn.CONCAT(subquery.c.user_id,'@berea.edu'), subquery.c.bnumber)
53 return (columns,query.tuples().execute(mainDB))
56def volunteerProgramHours(academicYear):
57 base = getBaseQuery(academicYear)
59 columns = ["Program Name", "Volunteer Hours", "Volunteer Name", "Volunteer Email", "Volunteer B-Number"]
60 query = (base.select(Program.programName,
61 fn.SUM(EventParticipant.hoursEarned),
62 fn.CONCAT(User.firstName, ' ', User.lastName),
63 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
64 User.bnumber)
65 .where(Event.isService == True)
66 .group_by(Program.programName, EventParticipant.user_id))
68 return (columns, query.tuples())
70def onlyCompletedAllVolunteer(academicYear):
71 base = getBaseQuery(academicYear)
72 base2 = getBaseQuery(academicYear)
74 columns = ["Full Name", "Email", "B-Number"]
75 subQuery = base2.select(EventParticipant.user_id).where(~Event.isAllVolunteerTraining)
77 query = (base.select(fn.CONCAT(User.firstName, ' ', User.lastName),
78 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
79 User.bnumber)
80 .where(Event.isAllVolunteerTraining, EventParticipant.user_id.not_in(subQuery)))
82 return (columns, query.tuples())
84def totalHours(academicYear):
85 base = getBaseQuery(academicYear)
87 columns = ["Total Service Hours", "Total Training Hours", "Other Participation Hours"]
88 query = base.select(fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
89 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
90 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
92 return (columns, query.tuples())
94def totalHoursByProgram(academicYear):
95 base = getBaseQuery(academicYear)
97 columns = ["Program", "Service Hours", "Training Hours", "Other Hours"]
98 query = (base.select(Program.programName,
99 fn.SUM(Case(None,((Event.isService, EventParticipant.hoursEarned),),0)),
100 fn.SUM(Case(None,((Event.isTraining, EventParticipant.hoursEarned),),0)),
101 fn.SUM(Case(None,((~Event.isService & ~Event.isTraining, EventParticipant.hoursEarned),),0)))
102 .group_by(Program.programName)
103 .order_by(Program.programName))
105 return (columns, query.tuples())
107def makeCase(fieldname):
108 return Case(fieldname,((1, "Yes"),(0, "No"),),"None")
110def getAllTermData(term):
111 base = getBaseQuery(term.academicYear)
113 columns = ["Program Name", "Event Name", "Event Description", "Event Date", "Event Start Time", "Event End Time", "Event Location",
114 "Food Provided", "Labor Only", "Training Event", "RSVP Required", "Service Event", "Engagement Event", "All Volunteer Training",
115 "RSVP Limit", "Series #", "Is Repeating Event", "Contact Name", "Contact Email",
116 "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",
117 "Hours Earned"]
118 query = (base.select(Program.programName,Event.name, Event.description, Event.startDate, Event.timeStart, Event.timeEnd, Event.location,
119 makeCase(Event.isFoodProvided), makeCase(Event.isLaborOnly), makeCase(Event.isTraining), makeCase(Event.isRsvpRequired), makeCase(Event.isService), makeCase(Event.isEngagement), makeCase(Event.isAllVolunteerTraining),
120 Event.rsvpLimit, Event.seriesId, makeCase(Event.isRepeating), Event.contactName, Event.contactEmail,
121 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,
122 EventParticipant.hoursEarned)
123 .where(Event.term == term))
125 return (columns,query.tuples())
127def volunteerMajorAndClass(academicYear, column, classLevel=False):
128 base = getBaseQuery(academicYear)
130 columns = ["Major", "Count"]
131 query = (base.select(Case(None, ((column.is_null(), "Unknown"),), column), fn.COUNT(fn.DISTINCT(EventParticipant.user_id)).alias('count'))
132 .where(Event.isService == True)
133 .group_by(column))
135 if classLevel:
136 columns = ["Class Level", "Count"]
137 query = query.order_by(Case(None, ((column == "Freshman", 1),
138 (column == "Sophomore", 2),
139 (column == "Junior", 3),
140 (column == "Senior", 4),
141 (column == "Graduating", 5),
142 (column == "Non-Degree", 6),
143 (column.is_null(), 7)),
144 8))
145 else:
146 query = query.order_by(SQL("count").desc())
148 return (columns, query.tuples())
151def repeatParticipantsPerProgram(academicYear):
152 base = getBaseQuery(academicYear)
154 columns = ["Volunteer", "Program Name", "Event Count"]
155 query = (base.select(fn.CONCAT(User.firstName, " ", User.lastName).alias('fullName'),
156 Program.programName.alias("programName"),
157 fn.COUNT(EventParticipant.event_id).alias('event_count'))
158 .where(Event.isService == True)
159 .group_by(User.firstName, User.lastName, Event.program)
160 .having(fn.COUNT(EventParticipant.event_id) > 1)
161 .order_by(Event.program, User.lastName))
163 return (columns, query.tuples())
166def repeatParticipants(academicYear):
167 base = getBaseQuery(academicYear)
169 columns = ["Number of Events", "Full Name", "Email", "B-Number"]
170 query = (base.select(fn.COUNT(EventParticipant.user_id).alias('count'),
171 fn.CONCAT(User.firstName, ' ', User.lastName),
172 fn.CONCAT(EventParticipant.user_id,'@berea.edu'),
173 User.bnumber)
174 .group_by(User.firstName, User.lastName)
175 .having(fn.COUNT(EventParticipant.user_id) > 1)
176 .order_by(SQL("count").desc()))
178 return (columns, query.tuples())
181def getRetentionRate(academicYear):
182 fallParticipationDict = termParticipation(getFallTerm(academicYear))
183 springParticipationDict = termParticipation(getSpringTerm(academicYear))
185 retentionList = []
186 retentionRateDict = calculateRetentionRate(fallParticipationDict, springParticipationDict)
187 for program, retentionRate in retentionRateDict.items():
188 retentionList.append((program, str(round(retentionRate * 100, 2)) + "%"))
190 columns = ["Program", "Retention Rate"]
191 return (columns, retentionList)
194def termParticipation(term):
195 base = getBaseQuery(term.academicYear)
197 participationQuery = (base.select(Event.program, EventParticipant.user_id.alias('participant'), Program.programName.alias("programName"))
198 .where(Event.term == term)
199 .order_by(EventParticipant.user))
201 programParticipationDict = defaultdict(list)
202 for result in participationQuery.dicts():
203 programName = result['programName']
204 participant = result['participant']
205 programParticipationDict[programName].append(participant)
207 return dict(programParticipationDict)
209def graduatingSeniorsVolunteerHours(academicYear):
210 columns = ["Full Name", "Email", "B-Number", "Unique Volunteer Semesters", "Total Volunteer Hours"]
212 currentSeniors = (EventParticipant
213 .select(EventParticipant.user_id)
214 .join(User).switch(EventParticipant)
215 .join(Event)
216 .join(Term)
217 .where(Term.academicYear == academicYear, User.rawClassLevel.in_(["Senior", "Graduating"]), Event.isService == True,
218 Event.deletionDate == None, Event.isCanceled == False))
220 query = (EventParticipant
221 .select(fn.CONCAT(User.firstName, ' ', User.lastName),
222 fn.CONCAT(User.username, '@berea.edu'),
223 User.bnumber,
224 fn.COUNT(fn.DISTINCT(Event.term)).alias("semester_count"),
225 fn.SUM(EventParticipant.hoursEarned).alias("total_hours"))
226 .join(User).switch(EventParticipant)
227 .join(Event)
228 .where(Event.isService == True, Event.deletionDate == None, Event.isCanceled == False, EventParticipant.user_id.in_(currentSeniors))
229 .group_by(User.bnumber)
230 .having(fn.COUNT(fn.DISTINCT(Event.term)) >= 4)
231 .order_by(SQL("semester_count").desc()))
233 return (columns, query.tuples())
236def removeNullParticipants(participantList):
237 return list(filter(lambda participant: participant, participantList))
240def calculateRetentionRate(fallDict, springDict):
241 retentionDict = {}
242 for program in fallDict:
243 fallParticipants = set(removeNullParticipants(fallDict[program]))
244 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
245 retentionRate = 0.0
246 try:
247 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
248 except ZeroDivisionError:
249 pass
250 retentionDict[program] = retentionRate
252 return retentionDict
254def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
255 # assumes the length of the column titles matches the length of the data
256 (columnTitles, dataTuples) = sheetData
257 worksheet = workbook.add_worksheet(sheetName)
258 bold = workbook.add_format({'bold': True})
260 worksheet.write_string(0, 0, sheetName, bold)
261 if sheetDesc:
262 worksheet.write_string(1, 0, sheetDesc)
264 for column, title in enumerate(columnTitles):
265 worksheet.write(3, column, title, bold)
267 for row, rowData in enumerate(dataTuples):
268 for column, value in enumerate(rowData):
269 # dates and times should use their text representation
270 if isinstance(value, (datetime, date, time)):
271 value = str(value)
273 worksheet.write(row + 4, column, value)
275 # set the width to the size of the text, with a maximum of 50 characters
276 for column, title in enumerate(columnTitles):
277 # put all of the data in each column into a list
278 columnData = [title] + [rowData[column] for rowData in dataTuples]
280 # find the largest item in the list (and cut it off at 50)
281 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
283 worksheet.set_column(column, column, setColumnWidth + 3)
286def createSpreadsheet(academicYear):
287 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
288 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
290 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
291 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
292 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
293 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
294 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.")
295 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
296 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event during {academicYear}.")
297 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.")
298 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.")
299 makeDataXls("Graduating Seniors", graduatingSeniorsVolunteerHours(academicYear), workbook, sheetDesc="Graduating seniors who have earned any number of service hours for at least 4 unique semesters.")
301 fallTerm = getFallTerm(academicYear)
302 springTerm = getSpringTerm(academicYear)
303 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
304 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
306 workbook.close()
308 return filepath