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

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.logic.createLogs import createRsvpLog 

12 

13def makeBonnerXls(): 

14 """ 

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

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

17 

18 Returns: 

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

20 """ 

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

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

23 worksheet = workbook.add_worksheet('students') 

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

25 

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

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

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

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

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

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

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

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

34 

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

36 

37 prev_year = 0 

38 row = 0 

39 for student in students: 

40 if prev_year != student.year: 

41 row += 1 

42 prev_year = student.year 

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

44 

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

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

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

48 

49 row += 1 

50 

51 workbook.close() 

52 

53 return filepath 

54 

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

56 """ 

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

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

59 """ 

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

61 

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

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

64 

65 

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

67 for cohort in bonnerCohorts: 

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

69 

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

71 if limit: 

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

73 

74 return cohorts 

75 

76 

77 

78def rsvpForBonnerCohort(year, event): 

79 """ 

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

81 """ 

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

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

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

85 

86def addBonnerCohortToRsvpLog(year, event): 

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

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

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

90 .where(BonnerCohort.year == year)) 

91 for bonner in bonnerCohort: 

92 fullName = bonner.fullName 

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