Coverage for app/logic/bonner.py: 40%

1from collections import defaultdict 

2from datetime import date 

3from peewee import IntegrityError, SQL, fn 

4 

5import xlsxwriter 

6 

7from app import app 

8from app.models.bonnerCohort import BonnerCohort 

9from app.models.certificationRequirement import CertificationRequirement 

10from app.models.event import Event 

11from app.models.eventParticipant import EventParticipant 

12from app.models.eventRsvp import EventRsvp 

13from app.models.requirementMatch import RequirementMatch 

14from app.models.user import User 

15from app.models.eventCohort import EventCohort 

16from app.models.term import Term 

17from app.logic.createLogs import createRsvpLog 

18from app.models.certification import Certification 

19def makeBonnerXls(selectedYear, noOfYears=1): 

20 """ 

21 Create and save a BonnerStudents.xlsx file with all of the current and former bonner students. 

22 Working with XLSX files: https://xlsxwriter.readthedocs.io/index.html 

23 

24 Params: 

25 selectedYear: The cohort year of interest. 

26 noOfYears: The number of years to be downloaded. 

27 

28 Returns: 

29 The file path and name to the newly created file, relative to the web root. 

30 """ 

31 selectedYear = int(selectedYear) 

32 filepath = app.config['files']['base_path'] + '/BonnerStudents.xlsx' 

33 workbook = xlsxwriter.Workbook(filepath, {'in_memory': True}) 

34 worksheet = workbook.add_worksheet('students') 

35 bold = workbook.add_format({'bold': True}) 

36 

37 worksheet.write('A1', 'Cohort Year', bold) 

38 worksheet.set_column('A:A', 10) 

39 worksheet.write('B1', 'Student', bold) 

40 worksheet.set_column('B:B', 20) 

41 worksheet.write('C1', 'B-Number', bold) 

42 worksheet.set_column('C:C', 10) 

43 worksheet.write('D1', 'Student Email', bold) 

44 worksheet.set_column('D:D', 20) 

45 

46 # bonner event titles 

47 bonnerEvents = CertificationRequirement.select().where(CertificationRequirement.certification==Certification.BONNER).order_by(CertificationRequirement.order.asc()) 

48 bonnerEventInfo = {bonnerEvent.id:(bonnerEvent.name, index + 4) for index, bonnerEvent in enumerate(bonnerEvents)} 

49 currentLetter = "E" # next column 

50 for bonnerEvent in bonnerEvents: 

51 worksheet.write(f"{currentLetter}1", bonnerEvent.name, bold) 

52 worksheet.set_column(f"{currentLetter}:{currentLetter}", 30) 

53 currentLetter = chr(ord(f"{currentLetter}") + 1) 

54 

55 if noOfYears == "all": 

56 students = BonnerCohort.select(BonnerCohort, User).join(User).order_by(BonnerCohort.year.desc(), User.lastName) 

57 else: 

58 noOfYears = int(noOfYears) 

59 startingYear = selectedYear - noOfYears + 1 

60 students = BonnerCohort.select(BonnerCohort, User).where(BonnerCohort.year.between(startingYear, selectedYear)).join(User).order_by(BonnerCohort.year.desc(), User.lastName) 

61 

62 prev_year = 0 

63 row = 0 

64 for student in students: 

65 if prev_year != student.year: 

66 row += 1 

67 prev_year = student.year 

68 worksheet.write(row, 0, f"{student.year} - {student.year+1}", bold) 

69 

70 worksheet.write(row, 1, student.user.fullName) 

71 worksheet.write(row, 2, student.user.bnumber) 

72 worksheet.write(row, 3, student.user.email) 

73 

74 # set event fields to the default "incomplete" status 

75 for eventName, eventSpreadsheetPosition in bonnerEventInfo.values(): 

76 worksheet.write(row, eventSpreadsheetPosition, "Incomplete") 

77 

78 bonnerEventsAttended = ( 

79 RequirementMatch 

80 .select() 

81 .join(Event, on=(RequirementMatch.event == Event.id)) 

82 .join(EventParticipant, on=(RequirementMatch.event == EventParticipant.event)) 

83 .join(CertificationRequirement, on=(RequirementMatch.requirement == CertificationRequirement.id)) 

84 .join(User, on=(EventParticipant.user == User.username)) 

85 .where((CertificationRequirement.certification_id == Certification.BONNER) & (User.username == student.user.username)) 

86 ) 

87 

88 certRequirementDates = {} 

89 for attendedEvent in bonnerEventsAttended: 

90 if bonnerEventInfo.get(attendedEvent.requirement.id): 

91 completedEvent = bonnerEventInfo[attendedEvent.requirement.id] 

92 if completedEvent[1] not in certRequirementDates: 

93 certRequirementDates[completedEvent[1]] = [] 

94 certRequirementDates[completedEvent[1]].append(attendedEvent.event.startDate.strftime('%m/%d/%Y')) 

95 

96 for tableIndex, dates in certRequirementDates.items(): 

97 worksheet.write(row, tableIndex, ", ".join(sorted(dates))) 

98 row += 1 

99 

100 workbook.close() 

101 

102 return filepath 

103 

104def getBonnerCohorts(limit=None, currentYear=date.today().year): 

105 """ 

106 Return a dictionary with years as keys and a list of bonner users as values. Returns empty lists for 

107 intermediate years, or the last 5 years if there are no older records. 

108 """ 

109 bonnerCohorts = list(BonnerCohort.select(BonnerCohort, User).join(User).order_by(BonnerCohort.year).execute()) 

110 

111 firstYear = currentYear - 4 if not bonnerCohorts else min(currentYear - 4, bonnerCohorts[0].year) 

112 lastYear = currentYear if not bonnerCohorts else max(currentYear, bonnerCohorts[-1].year) 

113 

114 

115 cohorts = { year: [] for year in range(firstYear, lastYear + 1) } 

116 for cohort in bonnerCohorts: 

117 cohorts[cohort.year].append(cohort.user) 

118 

119 # slice off cohorts that go over our limit starting with the earliest 

120 if limit: 

121 cohorts = dict(sorted(list(cohorts.items()), key=lambda e: e[0], reverse=True)[:limit]) 

122 

123 return cohorts 

124 

125 

126 

127def rsvpForBonnerCohort(year, event): 

128 """ 

129 Adds an EventRsvp record to the given event for each user in the given Bonner year. 

130 """ 

131 EventRsvp.insert_from(BonnerCohort.select(BonnerCohort.user, event, SQL('NOW()')) 

132 .where(BonnerCohort.year == year), 

133 [EventRsvp.user, EventRsvp.event, EventRsvp.rsvpTime]).on_conflict(action='IGNORE').execute() 

134 

135def addBonnerCohortToRsvpLog(year, event): 

136 """ This method adds the table information in the RSVP Log page""" 

137 bonnerCohort = list(BonnerCohort.select(fn.CONCAT(User.firstName, ' ', User.lastName).alias("fullName")) 

138 .join(User, on=(User.username == BonnerCohort.user)) 

139 .where(BonnerCohort.year == year)) 

140 for bonner in bonnerCohort: 

141 fullName = bonner.fullName 

142 createRsvpLog(eventId=event, content=f"Added {fullName} to RSVP list.")