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):
sheet = book.add_sheet("WeightedProgramTotals")
freezePanes(sheet, 1)
columnWidth(sheet, 10)
c.execute("SELECT DISTINCT course_id FROM courses;")
courseList = c.fetchall()
planList = data.grabFullPlanList(c)
def write(c, book):
'''Setup of the sheet
'''
sheet = book.add_sheet("ProgramYearTotals") #name the sheet
freezePanes(sheet, 1)
columnWidthProg(sheet, 8)
c.execute("SELECT DISTINCT program FROM students;") #find all the program names
programList = c.fetchall()
c.execute("SELECT DISTINCT proj_level FROM students;") #find all the years (1,2,3,4)
yearList = c.fetchall()
programNameStr = 'Program Name'
totalEnrollNameStr = 'Total Enrollments'
columns = {programNameStr : 0,
totalEnrollNameStr : 1}
hardCodedColumns = len(columns)
for heading, colNum in columns.iteritems(): #write the headings
sheet.write(0,colNum,heading)
for year in yearList:
columns[year] = yearList.index(year) + hardCodedColumns
sheet.write(0, columns[year], "Year " + str(year[0]))
yearTotal = [0] * (len(yearList) + 1) #initialize the list to store totals
''' Writing the data into each cell based on program and year
'''
count = 1
for program in programList:
program = program[0]
sheet.write(count, columns[programNameStr], program)
progTotal = 0
for year in yearList:
yearCount = data.grabProgramYearEnroll(c, program, year[0])
sheet.write(count, columns[year], yearCount)
yearTotal[year[0]] = yearTotal[year[0]] + yearCount
progTotal = progTotal + yearCount #counting the total number in each program
sheet.write(count, columns[totalEnrollNameStr], progTotal)
count = count + 1
'''Add the totals at the very bottom of the list
'''
sheet.write(count, columns[programNameStr], "Totals") #outputting the totals of each year
sheet.write(count, columns[totalEnrollNameStr], sum(yearTotal))
for year in yearList:
sheet.write(count, columns[year], yearTotal[year[0]])
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):
sheet = book.add_sheet("Program Info")
twoDecimalStyle = xlwt.XFStyle() #styling for using two decimals
twoDecimalStyle.num_format_str = '0.00'
columnWidth(sheet,14)
c.execute("SELECT DISTINCT program_id FROM program_info;")
programList = c.fetchall()
programNameStr = 'Program Name'
enrollmentNameStr = 'Enrollment'
unitFeeNameStr = 'Unit Fees'
formulaFeeNameStr = 'Formula Fees'
progWeightNameStr = 'Program Weight'
normUnitsNameStr = 'Normal Units'
BIUNameStr = 'BIU Value'
columns = {programNameStr : 0,
enrollmentNameStr : 1,
unitFeeNameStr : 2,
formulaFeeNameStr : 3,
progWeightNameStr : 4,
normUnitsNameStr : 5,
BIUNameStr : 6,
}
for columnName in columns:
sheet.write(0,columns[columnName],columnName)
count = 1
for program in programList:
program = program[0]
programName = data.grabProgName(c,program)
sheet.write(count,columns[programNameStr], programName)
programName = [programName] #make into tuple
enrollment = data.grabProgEnrollment(c,programName)
sheet.write(count, columns[enrollmentNameStr],enrollment)
unitFee = data.grabUnitFees(c,programName)
sheet.write(count, columns[unitFeeNameStr], unitFee, twoDecimalStyle)
formulaFee = data.grabFormulaFee(c,programName)
sheet.write(count,columns[formulaFeeNameStr], formulaFee, twoDecimalStyle)
progWeight = data.grabProgramWeight(c, programName)
sheet.write(count, columns[progWeightNameStr], progWeight, twoDecimalStyle)
normUnits = data.grabNormalUnits(c,programName)
sheet.write(count, columns[normUnitsNameStr], normUnits)
count = count + 1
BIUVal = data.grabBIU(c)
sheet.write(1,columns[BIUNameStr],BIUVal)
freezePanes(sheet,1)
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
