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

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.eventRsvp import EventRsvp 

10from app.models.user import User 

11from app.models.eventCohort import EventCohort 

12from app.logic.createLogs import createRsvpLog 

13 

14def makeBonnerXls(): 

15 """ 

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

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

18 

19 Returns: 

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

21 """ 

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

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

24 worksheet = workbook.add_worksheet('students') 

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

26 

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

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

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

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

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

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

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

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

35 

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

37 

38 prev_year = 0 

39 row = 0 

40 for student in students: 

41 if prev_year != student.year: 

42 row += 1 

43 prev_year = student.year 

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

45 

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

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

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

49 

50 row += 1 

51 

52 workbook.close() 

53 

54 return filepath 

55 

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

57 """ 

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

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

60 """ 

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

62 

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

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

65 

66 

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

68 for cohort in bonnerCohorts: 

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

70 

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

72 if limit: 

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

74 

75 return cohorts 

76 

77 

78 

79def rsvpForBonnerCohort(year, event): 

80 """ 

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

82 """ 

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

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

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

86 

87def addBonnerCohortToRsvpLog(year, event): 

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

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

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

91 .where(BonnerCohort.year == year)) 

92 for bonner in bonnerCohort: 

93 fullName = bonner.fullName 

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