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
Example #2
0
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("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