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

1from collections import defaultdict 

2from datetime import date 

3from peewee import IntegrityError, SQL 

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 

11 

12def makeBonnerXls(): 

13 """ 

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

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

16 

17 Returns: 

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

19 """ 

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

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

22 worksheet = workbook.add_worksheet('students') 

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

24 

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

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

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

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

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

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

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

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

33 

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

35 

36 prev_year = 0 

37 row = 0 

38 for student in students: 

39 if prev_year != student.year: 

40 row += 1 

41 prev_year = student.year 

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

43 

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

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

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

47 

48 row += 1 

49 

50 workbook.close() 

51 

52 return filepath 

53 

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

55 """ 

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

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

58 """ 

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

60 

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

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

63 

64 

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

66 for cohort in bonnerCohorts: 

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

68 

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

70 if limit: 

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

72 

73 return cohorts 

74 

75def rsvpForBonnerCohort(year, event): 

76 """ 

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

78 """ 

79 EventRsvp.insert_from(BonnerCohort.select(BonnerCohort.user, event, SQL('NOW()')).where(BonnerCohort.year == year),[EventRsvp.user, EventRsvp.event, EventRsvp.rsvpTime]).on_conflict(action='IGNORE').execute()