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