def courseTuition(connDB, course_id):
#grab all students who enrolled in that course
connDB.execute('''SELECT stud_id
FROM students
WHERE course1 = ? or
course2 = ? or
course3 = ? or
course4 = ? or
course5 = ? or
course6 = ? or
course7 = ? or
course8 = ? or
course9 = ? or
course10 = ?;''',(course_id,course_id,course_id,course_id,course_id,course_id,course_id,course_id,course_id,course_id))
studentsList = connDB.fetchall()
courseTuitionTotal = 0
for student in studentsList:
stude_id = student[0]
program = extractData.grabStudentProgram(connDB, stude_id)
unitFee = extractData.grabUnitFees(connDB, program)
courseCredit = extractData.grabCourseCredits(connDB,course_id)
tuitionGenerated = courseCredit * unitFee
courseTuitionTotal = courseTuitionTotal + tuitionGenerated
return courseTuitionTotal
def courseGrant(connDB,course_id):
#grab all students who enrolled in that course
connDB.execute('''SELECT stud_id
FROM students
WHERE course1 = ? or
course2 = ? or
course3 = ? or
course4 = ? or
course5 = ? or
course6 = ? or
course7 = ? or
course8 = ? or
course9 = ? or
course10 = ?;''',(course_id,course_id,course_id,course_id,course_id,course_id,course_id,course_id,course_id,course_id))
studentsList = connDB.fetchall() #list of all students
courseGrantTotal = 0 #cumulative total of the grants award to the course
for student in studentsList: #iterates through all the students
stude_id = student[0]
program = data.grabStudentProgram(connDB, stude_id)
year = data.grabStudentYear(connDB, stude_id)
formulaFee = data.grabFormulaFee(connDB, program)
normalUnits = data.grabNormalUnits(connDB,program)
if year == 1 and "BA" in program: #accounts for first year students, since
program = [("1st Year Arts"),]
elif year == 1 and "BSC" in program:
program = [("1st Year Science"),]
programWeight = data.grabProgramWeight (connDB, program)
BIU = data.grabBIU(connDB)
if BIU == 0 or programWeight == 0: #prevents total BIU to go below zero
totalBIU = 0
else:
totalBIU = (BIU * programWeight) - formulaFee
courseCredits = data.grabCourseCredits(connDB, course_id)
if normalUnits == 0: #prevents ZeroDivisionError, in the case that normalUnits is divisor and == 0
proportion = 0
else:
proportion = courseCredits / normalUnits
grantGenerated = proportion * totalBIU
courseGrantTotal = courseGrantTotal + grantGenerated
return courseGrantTotal
def studentGrant(connDB,stude_id):
program = data.grabStudentProgram(connDB, stude_id) #tuple
year = data.grabStudentYear(connDB,stude_id)
plan = data.grabStudentPlan(connDB,stude_id)
formulaFee = data.grabFormulaFee(connDB, program)
coursesRaw = data.grabStudentCourses(connDB, stude_id)
normalUnits = data.grabNormalUnits(connDB, program)
if year == 1 and "BA" in program: #accounts for first year students, since they have different weightings than upper years
program = [("1st Year Arts"),]
elif year == 1 and "BSC" in program:
program = [("1st Year Science"),]
programWeight = data.grabProgramWeight(connDB, program)
BIU = data.grabBIU(connDB)
for i in reversed(range(len(coursesRaw))):
if coursesRaw[i] == None:
courses = coursesRaw[:i] #removes the None courses
creditsList = []
for course_id in courses:
creditsList.append(data.grabCourseCredits(connDB, course_id)) #tuple
creditSum = 0.0
for credit in creditsList: #sum up all the credits that the student was enrolled
temp = credit[0]
creditSum = creditSum + temp #used to unpack the tuple
if BIU == 0 or programWeight == 0: #prevents totalBIU from being negative
totalBIU = 0
else:
totalBIU = (BIU * programWeight) - formulaFee
proportion = creditSum / normalUnits
grantGenerated = proportion * totalBIU
return grantGenerated
def studentTuition(connDB, stude_id): #pass in the student and calculate tuition of the student program = extractData.grabStudentProgram(connDB, stude_id) coursesRaw = extractData.grabStudentCourses(connDB, stude_id) unitFee = extractData.grabUnitFees(connDB,program) for i in reversed(range(len(coursesRaw))): if coursesRaw[i] == None: #eliminates the none courses courses = coursesRaw[:i] creditsList = [] for course_id in courses: #finds the credits of each course that the student took creditsList.append(extractData.grabCourseCredits(connDB, course_id)) creditSum = 0 for credit in creditsList: #sum up all the credits that the student was enrolled in creditSum = creditSum + (credit) #used to unpack the tuple revenueGenerated = creditSum * unitFee #formula to calculate revenue from each student return revenueGenerated
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
