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
