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