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 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): 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