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

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 

18 

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 bonnerEventsId = 1 

48 bonnerEvents = CertificationRequirement.select().where(CertificationRequirement.certification==bonnerEventsId).order_by(CertificationRequirement.order.asc()) 

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

50 allBonnerSpreadsheetPosition = 7 

51 currentLetter = "E" # next column 

52 for bonnerEvent in bonnerEvents: 

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

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

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

56 

57 if noOfYears == "all": 

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

59 else: 

60 noOfYears = int(noOfYears) 

61 startingYear = selectedYear - noOfYears + 1 

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

63 

64 prev_year = 0 

65 row = 0 

66 for student in students: 

67 if prev_year != student.year: 

68 row += 1 

69 prev_year = student.year 

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

71 

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

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

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

75 

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

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

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

79 

80 bonnerEventsAttended = ( 

81 RequirementMatch 

82 .select() 

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

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

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

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

87 .where((CertificationRequirement.certification_id == bonnerEventsId) & (User.username == student.user.username)) 

88 ) 

89 

90 allBonnerMeetingDates = [] 

91 for attendedEvent in bonnerEventsAttended: 

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

93 completedEvent = bonnerEventInfo[attendedEvent.requirement.id] 

94 worksheet.write(row, completedEvent[1], attendedEvent.event.startDate.strftime('%m/%d/%Y')) 

95 if completedEvent[0] == "All Bonner Meeting": 

96 allBonnerMeetingDates.append(attendedEvent.event.startDate.strftime('%m/%d/%Y')) 

97 else: 

98 raise Exception("Untracked requirements found in attended events. Debug required.") 

99 

100 worksheet.write(row, allBonnerSpreadsheetPosition, ", ".join(sorted(allBonnerMeetingDates))) 

101 

102 row += 1 

103 

104 workbook.close() 

105 

106 return filepath 

107 

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

109 """ 

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

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

112 """ 

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

114 

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

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

117 

118 

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

120 for cohort in bonnerCohorts: 

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

122 

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

124 if limit: 

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

126 

127 return cohorts 

128 

129 

130 

131def rsvpForBonnerCohort(year, event): 

132 """ 

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

134 """ 

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

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

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

138 

139def addBonnerCohortToRsvpLog(year, event): 

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

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

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

143 .where(BonnerCohort.year == year)) 

144 for bonner in bonnerCohort: 

145 fullName = bonner.fullName 

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