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):
sheet = book.add_sheet("YearTotals")
columnWidth(sheet, 7)
freezePanes(sheet,1)
c.execute("SELECT DISTINCT course_id FROM courses;")
courseList = c.fetchall()
courseNameStr = 'Course Name'
termNameStr = 'Term'
enrollmentsNameStr = 'Enrollments'
columns = {courseNameStr : 0,
termNameStr : 1,
enrollmentsNameStr : 2}
hardCodedColumns = len(columns)
for columnName in columns:
sheet.write(0, columns[columnName], columnName)
c.execute("SELECT DISTINCT proj_level FROM students;")
yearList = c.fetchall()
for year in yearList:
columns[year] = yearList.index(year) + hardCodedColumns
sheet.write(0, columns[year],"Year " + str(year[0]))
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)
enrollments = data.grabEnrollmentNumber(c, course)
sheet.write(count, columns[enrollmentsNameStr], enrollments)
for year in yearList: #iterate through years 1,2,3,4
yearCount = data.grabStudentYearEnroll(c, year[0], course)
sheet.write(count, columns[year], yearCount) #write to the sheet enrollment(course,year)
count = count + 1
return True
def write (c,book,plan):
'''Grabs only plans specified in the function call. If plan is 'BCHM',
it will find BCHM-G_BSC, BCHM-M-BSH, BCHM-P-BSH
'''
sheet = book.add_sheet(str(plan) + " Breakdown")
planRow = 0
yearRow = 1
informationStartRow = 2
courseNameStr = 'Course Name'
termStr = 'Term'
sheet.write(planRow,0, courseNameStr)
columnWidth(sheet,13)
c.execute("SELECT DISTINCT course_id FROM courses;")
courseList = c.fetchall()
c.execute("SELECT DISTINCT proj_level FROM students;")
yearList = c.fetchall()
c.execute("SELECT DISTINCT plan FROM students WHERE plan LIKE \'" + str(plan) + "%\';")
planList = c.fetchall() #will find all the plans that begin with BCHM
c.execute("SELECT DISTINCT plan2 FROM students WHERE plan2 LIKE \'" + str(plan) + "%\';")
plan2List = c.fetchall() #will find all the plans in plan2 that begin with BCHM
c.execute("SELECT DISTINCT plan3 FROM students WHERE plan3 LIKE \'" + str(plan) + "%\';")
plan3List = c.fetchall() #will find all the plans in plan3 that begin with BCHM
for plan in plan2List: #MERGING plan2 values with plan
try:
temp = planList.index(plan) #check if it is in the original plan list already
except ValueError:
if plan[0] != '': #make sure the plan isn't NULL or "" (which means there is no second plan)
planList.append(plan) #if error arises, the plan is not in the original plan
for plan in plan3List: #MERGING plan3 values with plan + plan2
try:
temp = planList.index(plan)
except ValueError:
if plan[0] != '':
planList.append(plan)
columnsExp = defaultdict(dict)
count = yearRow
for plan in planList:
for year in yearList:
columnsExp[plan][year] = count #setting up the columns
count = count + 1
sheet.write(planRow,columnsExp[plan][year], str(plan[0])) #outputs the Plan heading
sheet.write(yearRow,columnsExp[plan][year], "Year: " + str(year[0])) #outputs the year heading
count = informationStartRow
for course in courseList: #iterate throught courses
course = course[0]
courseName = data.grabCourseName(c, course)
sheet.write(count, 0, courseName)
for plan in planList: #iterate through the plans
for year in yearList: #iterate through all the years
enroll = data.grabStudentPlanYearEnroll(c,plan[0],year[0],course) #output the enrollment based on year/plan
sheet.write(count, columnsExp[plan][year], enroll)
count = count + 1
freezePanes(sheet,2)
return True
def write(c,book):
sheet = book.add_sheet("ProgramTotals")
freezePanes(sheet,1)
informationStartRow = 1
columnWidth(sheet, 6)
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 = 'Enrollments'
fYrArtsStr = 'Arts (1st)' #these help differentiate between 1st year artsci HONOURS and upper year artsci HONOURS
fYrSciStr = 'Science (1st)'
upYrArtsStr = 'Arts Hon (2-4)'
upYrSciStr = 'Science Hon (2-4)'
columns = {
courseNameStr : 0,
termNameStr : 1,
enrollmentNameStr : 2,
fYrArtsStr : 3,
fYrSciStr : 4,
upYrArtsStr : 5,
upYrSciStr : 6,
} #Creating all the headings (Course Name, BA, BAH, BSCH...) and hard code 1st year arts and sci
for columnName in columns: #write hardcoded column headings (Name, total enrollment, etc)
sheet.write(0, columns[columnName], columnName)
hardColumns = len(columns)
for program in programsList: #Writing all the column headings to the excel sheet
columns[program] = programsList.index(program) + hardColumns
sheet.write(0, columns[program], program)
count = informationStartRow
for course in courseList: #Outputs the course codes in column 0 (ANAT 215, 216)
course = course[0] #unpack the tuple
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)
sheet.write(count, columns[enrollmentNameStr], enrollments)
for program in programsList: #Outputs enrollments for all programs (except 1st year Arts Sci)
studCount = data.grabStudentEnrollment(c, program[0], course)
sheet.write(count, columns[program], studCount)
#Breaking down between 1st year and upper year Arts and Sci HONOURS
firstYrArts = data.grabStudentProgYearEnroll(c, "BAH", 1, course)
firstYrSci = data.grabStudentProgYearEnroll(c, "BSCH",1, course)
upYrArts = data.grabStudentEnrollment(c, "BAH", course) - firstYrArts
upYrSci = data.grabStudentEnrollment(c, "BSCH", course) - firstYrSci
sheet.write(count, columns[fYrArtsStr], firstYrArts)
sheet.write(count, columns[fYrSciStr], firstYrSci)
sheet.write(count, columns[upYrArtsStr], upYrArts)
sheet.write(count, columns[upYrSciStr], upYrSci)
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):
''' Outputs the sheet with all the revenue values.
'''
sheet = book.add_sheet("Revenue Course Totals")
freezePanes(sheet, 1)
twoDecimalStyle = xlwt.XFStyle() #styling for using two decimals
twoDecimalStyle.num_format_str = '0.00'
columnWidth(sheet, 14)
courseNameStr = 'Course Name' #hard coded values
termNameStr = 'Term'
creditsNameStr = 'Credits'
enrollmentNameStr = 'Enrollments'
grantValNameStr = 'Grant Value ($)'
tuitValNameStr = 'Tuition Value ($)'
totRevNameStr = 'Total Revenue ($)'
grantStudNameStr = 'Grant per Student'
tuitStudNameStr = 'Tuition per Student'
revStudNameStr = 'Revenue per Student'
columns = {courseNameStr : 0,
termNameStr : 1,
creditsNameStr : 2,
enrollmentNameStr : 3,
grantValNameStr : 4,
tuitValNameStr : 5,
totRevNameStr : 6,
grantStudNameStr : 7,
tuitStudNameStr : 8,
revStudNameStr : 9
}
for heading, colNum in columns.iteritems():
sheet.write(0, colNum, heading)
c.execute("SELECT DISTINCT course_id FROM courses;")
courseList = c.fetchall()
enrollTot = 0 #HARDCODE FOR THE TOTALS OF CERTAIN METRICS
grantTot = 0
tuitionTot = 0
totalTot = 0
count = 1 #used to increment the row number
for course in courseList:
course = course[0] #unpack the tuple
courseName = data.grabCourseName(c, course)
sheet.write(count, columns[courseNameStr], courseName)
term = data.grabCourseTerm(c, course)
sheet.write(count, columns[termNameStr], term)
credits = data.grabCourseCredits(c,course)
sheet.write(count, columns[creditsNameStr] , credits, twoDecimalStyle)
enrollment = data.grabEnrollmentNumber(c, course)
enrollTot = enrollTot + enrollment
sheet.write(count, columns[enrollmentNameStr] , enrollment)
grantVal = grant.runAppCourse(course)
grantTot = grantTot + grantVal
sheet.write(count, columns[grantValNameStr], grantVal, twoDecimalStyle)
tuitionVal = tuition.runAppCourse(course)
tuitionTot = tuitionTot + tuitionVal
sheet.write(count, columns[tuitValNameStr], tuitionVal, twoDecimalStyle)
total = grantVal + tuitionVal
totalTot = totalTot + total
sheet.write(count, columns[totRevNameStr], total, twoDecimalStyle)
grantPerStud = grantVal / enrollment
sheet.write(count, columns[grantStudNameStr], grantPerStud, twoDecimalStyle)
tuitionPerStud = tuitionVal / enrollment
sheet.write(count, columns[tuitStudNameStr], tuitionPerStud, twoDecimalStyle)
revenuePerStud = total / enrollment
sheet.write(count, columns[revStudNameStr], revenuePerStud, twoDecimalStyle)
count = count + 1 #used to icnrement row number
sheet.write(count,columns[courseNameStr], "Totals")
sheet.write(count,columns[enrollmentNameStr], enrollTot) #HARDCODE FOR THE TOTALS OF CERTAIN METRICS
sheet.write(count, columns[grantValNameStr], grantTot, twoDecimalStyle)
sheet.write(count,columns[tuitValNameStr], tuitionTot, twoDecimalStyle)
sheet.write(count,columns[totRevNameStr], totalTot, twoDecimalStyle)
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
