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("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):
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 runApp():
conn = data.connectDB()
c = conn.cursor()
sheetName = "DBMS Enrollment Data.xls" #name of the excel spreadsheet
tuitionGrantSheetName = "Course Tuition and Grant Revenue Totals"
programTotalsSheetName = "Course Breakdown Based on Program"
programPercentTotalsSheetName = "Course Percentage Breakdown based on Faculty"
planTotalsSheetName = "Course Breakdown Based on All Plans"
sigPlanTotalsSheetName = "Course Breakdown Based on Plans (with >10 enrollments)"
yearTotalsSheetName = "Course Breakdown Based on Year"
planBreakdownSheetName = "Course Breakdown Based on Selected Plans"
programYearSheetName = "Program Breakdown Based on Year"
programInfoSheetName = "Program Info"
sheetsOrderedDict = OrderedDict([
(tuitionGrantSheetName, 0),
(programTotalsSheetName, 1),
(planTotalsSheetName, 2),
(sigPlanTotalsSheetName, 3),
(yearTotalsSheetName, 4),
(planBreakdownSheetName, 5),
(programYearSheetName, 6),
(programInfoSheetName, 7),
])
cdLocation = os.getcwd()
excelLocation = cdLocation + "\\" + sheetName
try:
os.remove(excelLocation) #TESTING PURPOSES, REMOVES THE OLD SPREADSHEET EVERY TIME@@@@@@@@@@@@
except WindowsError:
pass
''' Asks user for information that needs to be shown
'''
selectedSheets = UI.sheetsOptionsCheckBox.runScrollingApp(sheetsOrderedDict) #UI module asks for sheets
while not checkError(selectedSheets):
UI.errorMessageBox.runApp(selectedSheets)
selectedSheets = UI.sheetsOptionsCheckBox.runScrollingApp(sheetsOrderedDict)
selectedSheetsKey = [] #used to store the keys of the selected sheets
for name in selectedSheets:
selectedSheetsKey.append(sheetsOrderedDict[name])
''' Sheets that will be written to the outputted workbook
'''
book = xlwt.Workbook()
if sheetsOrderedDict[tuitionGrantSheetName] in selectedSheetsKey:
ExcelOutput.tuitionGrantTotals.write(c,book)
if sheetsOrderedDict[programTotalsSheetName] in selectedSheetsKey:
ExcelOutput.programTotals.write(c,book)
if sheetsOrderedDict[planTotalsSheetName] in selectedSheetsKey:
ExcelOutput.planExpandedTotals.write(c,book)
if sheetsOrderedDict[sigPlanTotalsSheetName] in selectedSheetsKey:
ExcelOutput.planSignificantTotals.write(c,book)
if sheetsOrderedDict[yearTotalsSheetName] in selectedSheetsKey:
ExcelOutput.yearTotals.write(c,book)
if sheetsOrderedDict[planBreakdownSheetName] in selectedSheetsKey:
#Make list of plans with only the plan code e.g. "BCHM"
rawPlanList = data.grabFullPlanList(c)
checkPlanList = []
for plan in rawPlanList:
plan = plan[0] #unpack the tuple
breakPoint = plan.index('-') #find the first occurence of "-" that separates BCHM-M-BSH
plan = plan[0:breakPoint] #splice the string to get just BCHM
try:
temp = checkPlanList.index(plan) #check if the course code BCHM exists in the list
except ValueError:
checkPlanList.append(plan) #if not, it is added to the list
selectedPlans = UI.planOptionsCheckBox.runScrollingApp(checkPlanList)
while not checkError(selectedPlans): #ensure plans are chosen to be output
UI.errorMessageBox(selectedPlans)
selectedPlans = UI.planOptionsCheckBox.runScrollingApp(checkPlanList)
for plan in selectedPlans:
ExcelOutput.planBreakdown.write(c,book,plan)
if sheetsOrderedDict[programYearSheetName] in selectedSheetsKey:
ExcelOutput.programYearTotals.write(c,book)
if sheetsOrderedDict[programInfoSheetName] in selectedSheetsKey:
ExcelOutput.programInfo.write(c,book)
'''Save the file into a specific location. This uses the tkfiledialog module
to select the save location.
The default save name is "DBMS Enrollment Data " and the timestamp date.
A location must be chosen for save location.
'''
timeStam = dateTimeOutput.pythonTime() #only the date of the timestamp will be printed
Tk().withdraw()
filename = asksaveasfilename(defaultextension = '.xls', initialfile = 'DBMS Enrollment Data ' + timeStam[:10])
while filename == '': #a location must be saved
#UI.errorMessageBox.runApp("File was not saved. Please enter valid name.")
filename = asksaveasfilename(defaultextension = '.xls', initialfile = 'DBMS Enrollment Data ' + timeStam[:10])
try:
book.save(filename)
except IndexError: #exists if no sheets are printed (empty file)
pass
except IOError:
UI.errorMessageBox.runApp("Error: Please close all Excel workbooks.")
