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
Esempio n. 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("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
Esempio n. 7
0
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