Coverage for app/logic/volunteerSpreadsheet.py: 23%
141 statements
« prev ^ index » next coverage.py v7.10.2, created at 2025-12-18 19:28 +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)
212def removeNullParticipants(participantList):
213 return list(filter(lambda participant: participant, participantList))
216def calculateRetentionRate(fallDict, springDict):
217 retentionDict = {}
218 for program in fallDict:
219 fallParticipants = set(removeNullParticipants(fallDict[program]))
220 springParticipants = set(removeNullParticipants(springDict.get(program, [])))
221 retentionRate = 0.0
222 try:
223 retentionRate = len(fallParticipants & springParticipants) / len(fallParticipants)
224 except ZeroDivisionError:
225 pass
226 retentionDict[program] = retentionRate
228 return retentionDict
231def makeDataXls(sheetName, sheetData, workbook, sheetDesc=None):
232 # assumes the length of the column titles matches the length of the data
233 (columnTitles, dataTuples) = sheetData
234 worksheet = workbook.add_worksheet(sheetName)
235 bold = workbook.add_format({'bold': True})
237 worksheet.write_string(0, 0, sheetName, bold)
238 if sheetDesc:
239 worksheet.write_string(1, 0, sheetDesc)
241 for column, title in enumerate(columnTitles):
242 worksheet.write(3, column, title, bold)
244 for row, rowData in enumerate(dataTuples):
245 for column, value in enumerate(rowData):
246 # dates and times should use their text representation
247 if isinstance(value, (datetime, date, time)):
248 value = str(value)
250 worksheet.write(row + 4, column, value)
252 # set the width to the size of the text, with a maximum of 50 characters
253 for column, title in enumerate(columnTitles):
254 # put all of the data in each column into a list
255 columnData = [title] + [rowData[column] for rowData in dataTuples]
257 # find the largest item in the list (and cut it off at 50)
258 setColumnWidth = min(max(len(str(x)) for x in columnData),50)
260 worksheet.set_column(column, column, setColumnWidth + 3)
263def createSpreadsheet(academicYear):
264 filepath = f"{app.config['files']['base_path']}/volunteer_data_{academicYear}.xlsx"
265 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True})
267 makeDataXls("Total Hours", totalHours(academicYear), workbook, sheetDesc=f"All participation hours for {academicYear}.")
268 makeDataXls("Total Hours By Program", totalHoursByProgram(academicYear), workbook, sheetDesc=f"All participation hours by program for {academicYear}.")
269 makeDataXls("Program Volunteers", volunteerProgramHours(academicYear), workbook, sheetDesc="Total program service hours for each volunteer.")
270 makeDataXls("Volunteers By Major", volunteerMajorAndClass(academicYear, User.major), workbook, sheetDesc="All volunteers who participated in service events, by major.")
271 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.")
272 makeDataXls("Repeat Participants", repeatParticipants(academicYear), workbook, sheetDesc="Students who participated in multiple events, whether earning service hours or not.")
273 makeDataXls("Unique Volunteers", getUniqueVolunteers(academicYear), workbook, sheetDesc=f"All students who participated in at least one service event during {academicYear}.")
274 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.")
275 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.")
277 fallTerm = getFallTerm(academicYear)
278 springTerm = getSpringTerm(academicYear)
279 makeDataXls(fallTerm.description, getAllTermData(fallTerm), workbook, sheetDesc= "All event participation for the term, excluding deleted or canceled events.")
280 makeDataXls(springTerm.description, getAllTermData(springTerm), workbook, sheetDesc="All event participation for the term, excluding deleted or canceled events.")
282 workbook.close()
284 return filepath