Coverage for app/logic/serviceLearningCoursesData.py: 86%
130 statements
« prev ^ index » next coverage.py v7.2.7, created at 2024-03-18 17:14 +0000
1from flask import session, g
2import re as regex
3from openpyxl import load_workbook
4from app.models.course import Course
5from app.models.user import User
6from app.models.term import Term
7from app.models.courseInstructor import CourseInstructor
8from app.models.courseParticipant import CourseParticipant
9from app.models.courseStatus import CourseStatus
10from app.models.courseQuestion import CourseQuestion
11from app.models.questionNote import QuestionNote
12from app.models.note import Note
13from app.models.attachmentUpload import AttachmentUpload
14from app.models.term import Term
15from app.models import DoesNotExist
16from app.logic.createLogs import createAdminLog
17from app.logic.fileHandler import FileHandler
18from app.logic.term import addPastTerm
20def getServiceLearningCoursesData(user):
21 """Returns dictionary with data used to populate Service-Learning proposal table"""
22 courses = (Course.select(Course, Term, User, CourseStatus)
23 .join(CourseInstructor).switch()
24 .join(Term).switch()
25 .join(CourseStatus).switch()
26 .join(User)
27 .where((CourseInstructor.user==user)|(Course.createdBy==user))
28 .order_by(Course.term.desc(), Course.status))
30 courseDict = {}
31 for course in courses:
32 otherInstructors = (CourseInstructor.select(CourseInstructor, User).join(User).where(CourseInstructor.course==course))
33 faculty = [f"{instructor.user.firstName} {instructor.user.lastName}" for instructor in otherInstructors]
36 courseDict[course.id] = {"id":course.id,
37 "creator":f"{course.createdBy.firstName} {course.createdBy.lastName}",
38 "name":course.courseName,
39 "faculty": faculty,
40 "term": course.term,
41 "status": course.status.status}
42 return courseDict
44def withdrawProposal(courseID):
45 """Withdraws proposal of ID passed in. Removes foreign keys first.
46 Key Dependencies: QuestionNote, CourseQuestion, CourseParticipant,
47 CourseInstructor, Note"""
49 # delete syllabus
50 try:
51 syllabi = AttachmentUpload.select().where(AttachmentUpload.course==courseID)
52 for syllabus in syllabi:
53 FileHandler(courseId = courseID).deleteFile(syllabus.id)
55 except DoesNotExist:
56 print(f"File, {AttachmentUpload.fileName}, does not exist.")
58 # delete course object
59 course = Course.get(Course.id == courseID)
60 courseName = course.courseName
61 questions = CourseQuestion.select().where(CourseQuestion.course == course)
62 notes = list(Note.select(Note.id)
63 .join(QuestionNote)
64 .where(QuestionNote.question.in_(questions))
65 .distinct())
66 course.delete_instance(recursive=True)
67 for note in notes:
68 note.delete_instance()
70 createAdminLog(f"Withdrew SLC proposal: {courseName}")
72def renewProposal(courseID, term):
73 """
74 Renews proposal of ID passed in for the selected term.
75 Sets status to in progress.
76 """
77 oldCourse = Course.get_by_id(courseID)
78 newCourse = Course.get_by_id(courseID)
79 newCourse.id = None
80 newCourse.term = Term.get_by_id(term)
81 newCourse.status = CourseStatus.IN_PROGRESS
82 newCourse.isPreviouslyApproved = True
83 newCourse.save()
84 questions = CourseQuestion.select().where(CourseQuestion.course==oldCourse)
85 for question in questions:
86 CourseQuestion.create(course=newCourse.id,
87 questionContent=question.questionContent,
88 questionNumber=question.questionNumber)
90 instructors = CourseInstructor.select().where(CourseInstructor.course==oldCourse.id)
91 for instructor in instructors:
92 CourseInstructor.create(course=newCourse.id,
93 user=instructor.user)
95 return newCourse
97def parseUploadedFile(filePath):
98 """
99 Parse an Excel document at the given `filePath` for courses and
100 course participants.
102 The return value will be a tuple. The second value is a list of
103 error message tuples. The first tuple value is the error message,
104 and the second is a 0 or 1 indicating whether the error is a
105 'general' error - 1 - or an error on a specific course, term, or
106 person. The first value is a dictionary keyed by the term
107 description. Each value is another dictionary, with a key for
108 'displayMsg' and 'errorMsg', and a 'courses' key whose value is
109 a dictionary with keys for the courses in the term. Each course
110 has a 'displayMsg' and 'errorMsg' key, and a 'students' key
111 that has a list of dictionaries with 'user', 'displayMsg', and
112 'errorMsg' keys.
113 E.g.,
114 {
115 "Fall 2021": {
116 "displayMsg": "",
117 "errorMsg": "",
118 "courses": {
119 "CSC 330": {
120 "displayMsg: "CSC 330 will be created",
121 "errorMsg: "",
122 "students": [
123 {'user':'ramsayb2',
124 'displayMsg': 'Brian Ramsay',
125 'errorMsg': ''},
126 {'user':'B0073235',
127 'displayMsg': '',
128 'errorMsg': 'ERROR: B0073235 does not exist!'}]
129 },
130 "CSC 226": {
131 "displayMsg": "CSC 226 matched to existing course 'Data Structures'.",
132 "errorMsg": "",
133 "students": [
134 {'user':'ramsayb2',
135 'displayMsg': 'Brian Ramsay',
136 'errorMsg': ''},
137 {'user':'lamichhanes',
138 'displayMsg': 'Sandesh Lamichhane',
139 'errorMsg': ''}]
140 }
141 }
142 }
143 }
144 """
145 excelData = load_workbook(filename=filePath)
146 excelSheet = excelData.active
148 result= {}
149 errors = []
150 term = ''
151 course = ''
152 cellRow = 0
154 for row in excelSheet.iter_rows():
155 cellRow += 1
156 cellVal = row[0].value
157 if not cellVal:
158 continue
160 # Look for a Term. Examples: Fall 2020 or Spring B 2021
161 if regex.search(r"\b[a-zA-Z]{3,}( [AB])? \d{4}\b", str(cellVal)):
162 errorMsg = ''
163 if "Spring A" in cellVal or "Spring B" in cellVal:
164 cellVal = "Spring " + cellVal.split()[-1]
165 if "Fall A" in cellVal or "Fall B" in cellVal:
166 cellVal = "Fall " + cellVal.split()[-1]
168 if cellVal.split()[0] not in ["Summer", "Spring", "Fall", "May"]:
169 errorMsg = f"ERROR: '{cellVal}' is not a valid term in row {cellRow}."
170 else:
171 latestTerm = Term.select().order_by(Term.termOrder.desc()).get()
172 isFutureTerm = latestTerm.termOrder < Term.convertDescriptionToTermOrder(cellVal)
173 if isFutureTerm:
174 errors.append(f"ERROR: '{cellVal}' is a future term in row {cellRow}.")
175 else:
176 validTerm = Term.get_or_none(Term.description == cellVal)
178 term = cellVal
179 result[term] = {
180 'displayMsg': term,
181 'errorMsg': errorMsg,
182 'courses': {}
183 }
184 if errorMsg:
185 errors.append((errorMsg,0))
187 # Look for a Course. Examples: FRN134 CSC 226
188 elif regex.search(r"\b[A-Z]{2,4} ?\d{3}\b", str(cellVal)):
189 errorMsg = displayMsg = ''
190 if not term:
191 displayMsg = cellVal
192 errorMsg = "ERROR: No term was given for this course"
193 else:
194 existingCourse = Course.get_or_none(Course.courseAbbreviation == cellVal)
195 displayMsg = f'{cellVal} will be created.'
196 if existingCourse:
197 displayMsg = f"{cellVal} matched to the existing course {existingCourse.courseName}."
199 course = cellVal
200 result[term]['courses'][course] = {
201 'displayMsg': displayMsg,
202 'errorMsg': errorMsg,
203 'students': []
204 }
205 if errorMsg:
206 errors.append((errorMsg,0))
208 # Look for a B-Number. Example: B00123456
209 elif regex.search(r"\b[B]\d{8}\b", str(cellVal)):
210 errorMsg = displayMsg = ''
211 if not course:
212 errorMsg = "ERROR: No course is connected to this student"
213 else:
214 existingUser = User.get_or_none(User.bnumber == cellVal)
215 if existingUser:
216 displayMsg = f"{existingUser.firstName} {existingUser.lastName}"
217 existingUser = existingUser.username
218 else:
219 errorMsg = f'ERROR: {row[1].value} with B# "{row[0].value}" does not exist.'
221 result[term]['courses'][course]['students'].append({
222 'user': (existingUser or cellVal),
223 'displayMsg': displayMsg,
224 'errorMsg': errorMsg})
225 if errorMsg:
226 errors.append((errorMsg,0))
228 elif cellVal: # but didn't match the regex
229 errors.append((f'ERROR: "{cellVal}" in row {cellRow} of the Excel document does not appear to be a term, course, or valid B#.',1))
232 return result, errors
234def saveCourseParticipantsToDatabase(cpPreview):
235 for term,terminfo in cpPreview.items():
236 termObj = Term.get_or_none(description = term) or addPastTerm(term)
237 if not termObj:
238 print(f"Unable to find or create term {term}")
239 continue
241 for course, courseinfo in terminfo['courses'].items():
242 if 'errorMsg' in courseinfo and courseinfo['errorMsg']:
243 print(f"Unable to save course {course}. {courseinfo['errorMsg']}")
244 continue
246 courseObj = Course.get_or_create(
247 courseAbbreviation = course,
248 term = termObj,
249 defaults = {"CourseName" : "",
250 "sectionDesignation" : "",
251 "courseCredit" : "1",
252 "term" : termObj,
253 "status" : 4,
254 "createdBy" : g.current_user,
255 "serviceLearningDesignatedSections" : "",
256 "previouslyApprovedDescription" : "" })[0]
258 for userDict in courseinfo['students']:
259 if userDict['errorMsg']:
260 print(f"Unable to save student. {userDict['errorMsg']}")
261 continue
263 CourseParticipant.get_or_create(user=userDict['user'],
264 course=courseObj,
265 hoursEarned=20)