def write(c,book):
sheet = book.add_sheet("PlanTotalsExpanded") #new sheet
freezePanes(sheet, 1)
columnWidth(sheet, 10)
courseNameStr = 'Course Name'
termNameStr = 'Term'
enrollmentsNameStr = 'Enrollments'
columns = {courseNameStr : 0,
termNameStr : 1,
enrollmentsNameStr : 2} #hardcoded columns
for columnName in columns: #write hardcoded columns
sheet.write(0, columns[columnName], columnName)
hardColumns = len(columns)
c.execute("SELECT DISTINCT course_id FROM courses;")
courseList = c.fetchall()
planList = data.grabFullPlanList(c)
for plan in planList: #writing all column headings for each plan
columns[plan] = planList.index(plan) + hardColumns
sheet.write(0, columns[plan], plan)
count = 1
for course in courseList: #iterate through all the courses
course = course[0]
courseName = data.grabCourseName(c, course) #write down the courses in the first row
sheet.write(count,columns[courseNameStr],courseName)
term = data.grabCourseTerm(c, course)
sheet.write(count, columns[termNameStr], term)
totEnrollments = data.grabEnrollmentNumber(c,course)
sheet.write(count,columns[enrollmentsNameStr], totEnrollments)
for plan in planList: #interate through all the plans
studCount = data.grabStudentPlanEnroll(c, plan[0], course) #gotta unpack that plan
sheet.write(count, columns[plan], studCount)
count = count + 1
return True
def write(c, book):
cutoff = 10 #cutoff range for number of plan enrollments
planBreakdown = book.add_sheet("Plans Breakdown")
columnWidth(planBreakdown, 10)
c.execute("SELECT DISTINCT course_id FROM courses;")
courseList = c.fetchall()
courseNameStr = 'Course Name'
termNameStr = 'Term'
enrollmentsNameStr = 'Enrollments'
planList = data.grabFullPlanList(c)
columns = {courseNameStr : 0,
termNameStr : 1,
enrollmentsNameStr : 2}
hardCodedColumns = len(columns)
sigPlanList = [] #filter out plans with very little enrollments in DBMS courses (eg ENG or COMM plans)
for plan in planList:
'''Iterates through each plan. With a particular plan, iterate through all the courses.
If the number of people in that plan cannot exceed 10 people for any of the courses,
they will not be included in this sheet.
The second for loop FILTERS
'''
studCount = []
addToList = False
for course in courseList:
course = course[0]
studCount.append(data.grabStudentPlanEnroll(c, plan[0], course))
for count in studCount:
if count > cutoff:
addToList = True
break
if addToList: #only add to the new list if there are enough enrollments
sigPlanList.append(plan)
for columnName in columns:
planBreakdown.write(0, columns[columnName], columnName)
for plan in sigPlanList: #only write the relevant plans into each column
columns[plan] = sigPlanList.index(plan) + hardCodedColumns
planBreakdown.write(0, columns[plan], plan)
count = 1
for course in courseList:
'''This for loop WRITES
'''
course = course[0]
courseName = data.grabCourseName(c, course)
planBreakdown.write(count,columns[courseNameStr],courseName)
term = data.grabCourseTerm(c, course)
planBreakdown.write(count, columns[termNameStr], term)
enrollments = data.grabEnrollmentNumber(c, course)
planBreakdown.write(count, columns[enrollmentsNameStr],enrollments)
for plan in sigPlanList:
studCount = data.grabStudentPlanEnroll(c, plan[0], course) #gotta unpack that plan
planBreakdown.write(count, columns[plan], studCount)
count = count + 1
planBreakdown.set_panes_frozen(True)
planBreakdown.set_horz_split_pos(1) #Freeze panes for the first row
return True
def write(c, book):
'''
Tried to imitate what Dan Adams had for his "Enrollment by Program" Sheet
For each program, it will have a column for # of weighted enrollments, and also percentage of total enrollments
'''
sheet = book.add_sheet("Weighted Program Enroll")
twoDecimalStyle = xlwt.XFStyle()
twoDecimalStyle.num_format_str = '0.00'
freezePanes(sheet,1)
informationStartRow = 1
columnWidth(sheet, 9)
c.execute("SELECT DISTINCT course_id FROM courses;")
courseList = c.fetchall()
c.execute("SELECT DISTINCT program FROM students;")
programsList = c.fetchall()
courseNameStr = 'Course Name'
termNameStr = 'Term'
enrollmentNameStr = 'W. Enrollments'
DBMSNameStr = "W. DBMS"
DBMSPerNameStr = "% DBMS"
#List of all the DBMS plans. This excludes the minors.
DBMSPlans = ['LISC-G-BSC',
'LISC-M-BSH',
'LISC-P-BSH',
'LISC-Z-3',
'BCHM-G-BSC',
'BCHM-M-BSH',
'BCHM-P-BSH',
'BCHM-Z-3',
]
ArtSciNameStr = "W. ArtSci" #all other plans with degree program BA, BAH, BSC...
ArtSciPerNameStr = "% ArtSci"
NursNameStr = "W. NURS" #all those with BNURS
NursPerNameStr = "% NURS"
EngNameStr = "W. ENG" #all those with BSCE, , etc
EngPerNameStr = "% ENG"
CommNameStr = "W. COMM"
CommPerNameStr = "% COMM"
columns = {
courseNameStr : 0,
termNameStr : 1,
enrollmentNameStr : 2,
DBMSNameStr : 3,
DBMSPerNameStr: 4,
ArtSciNameStr : 5,
ArtSciPerNameStr : 6,
NursNameStr : 7,
NursPerNameStr : 8,
EngNameStr : 9,
EngPerNameStr : 10,
CommNameStr : 11,
CommPerNameStr : 12,
}
for columnName in columns:
sheet.write(0, columns[columnName], columnName)
count = informationStartRow
courseWeightedTotal = 0
for course in courseList:
course = course[0]
courseWeight = data.grabCourseCredits(c, course)
weighting = courseWeight / 3.0 #the weighting adjusts the number of Full Time Enrollments (FTE)
courseName = data.grabCourseName(c, course)
sheet.write(count, columns[courseNameStr], courseName)
term = data.grabCourseTerm(c, course)
sheet.write(count, columns[termNameStr], term)
enrollments = data.grabEnrollmentNumber(c, course)
enrollments = enrollments * weighting #total weighted
sheet.write(count, columns[enrollmentNameStr], enrollments)
courseWeightedTotal = courseWeightedTotal + enrollments
DBMSEnrollments = 0
for plan in DBMSPlans:
DBMSEnrollments = DBMSEnrollments + data.grabStudentPlanEnroll(c,plan,course)
#DBMS Columns
DBMSEnrollments = DBMSEnrollments * weighting #number of all those in DBMS majors
sheet.write(count,columns[DBMSNameStr],DBMSEnrollments)
DBMSPerEnrollments = DBMSEnrollments / enrollments #Percentage of all those in DBMS majors
sheet.write(count, columns[DBMSPerNameStr], DBMSPerEnrollments * 100, twoDecimalStyle)
#Nursing Columns
NursEnrollments = data.grabStudentEnrollment(c, "BNSC", course)
sheet.write(count, columns[NursNameStr], NursEnrollments)
NursPerEnrollments = NursEnrollments / enrollments
sheet.write(count, columns[NursPerNameStr], NursPerEnrollments * 100, twoDecimalStyle)
#Engineering Columns
EngEnrollments = data.grabStudentEnrollment(c, "BSCE", course)
EngEnrollments = EngEnrollments + data.grabStudentEnrollment(c, "UENG", course)
sheet.write(count, columns[EngNameStr], EngEnrollments)
EngPerEnrollments = EngEnrollments / enrollments
sheet.write(count, columns[EngPerNameStr], EngPerEnrollments * 100, twoDecimalStyle)
#Commerce Columns
CommEnrollments = data.grabStudentEnrollment(c, "BCOM", course)
sheet.write(count, columns[CommNameStr], CommEnrollments)
CommPerEnrollments = CommEnrollments / enrollments
sheet.write(count, columns[CommPerNameStr], CommPerEnrollments * 100, twoDecimalStyle)
#ArtSci Columns
ArtSciEnrollments = enrollments - DBMSEnrollments - NursEnrollments - EngEnrollments - CommEnrollments
sheet.write(count, columns[ArtSciNameStr], ArtSciEnrollments)
ArtSciPerEnrollments = ArtSciEnrollments / enrollments
sheet.write(count, columns[ArtSciPerNameStr], ArtSciPerEnrollments * 100, twoDecimalStyle)
count = count + 1
return True