def getClassMean(data, columns):
columnStr = ''
for x in columns:
columnStr = columnStr + x + ', '
schDets = getSchoolDetails()
subjects_lower_forms = schDets['lower_subjects']
if data['form'] == 1 or 2:
no_of_subjects = subjects_lower_forms
else:
no_of_subjects = 8
cursor = db.cursor()
# to create dynamic sum of all subjects eg IFNULL(Eng, 0)+IFNULL(Kis, 0)+IFNULL(Mat, 0)+...
sum_cols = ''
indexes = len(columns) - 1
for key, val in enumerate(columns):
if key == indexes: # To avoid adding + after the last subject
sum_cols = sum_cols + 'IFNULL(' + val + ', 0)'
else:
sum_cols = sum_cols + 'IFNULL(' + val + ', 0)' + '+'
# To get all classes ie if class_id =0, exam_id > 0 ie exam has been selected
if data["class_id"] == 0 and data["exam_id"] > 0:
sql = "SELECT SUM(%s)/%s AS mean \
FROM exam_results er \
JOIN exams e ON e.exam_id = er.exam_id \
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d \
JOIN classes c ON c.class_id = u.class_id AND c.form_name = %d AND u.deleted = %d \
WHERE e.exam_id = %d GROUP BY er.exam_result_id ORDER BY mean DESC" % (sum_cols, no_of_subjects, 0, int(data['form']), 0, data['exam_id'])
else:
sql = "SELECT SUM(%s)/%s AS mean \
FROM exam_results er \
JOIN exams e ON e.exam_id = er.exam_id \
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d \
JOIN classes c ON c.class_id = u.class_id AND c.class_id = %d AND u.deleted = %d \
WHERE e.exam_id = %d GROUP BY er.exam_result_id ORDER BY mean DESC" % (sum_cols, no_of_subjects, 0, int(data['class_id']), 0, data['exam_id'])
try:
cursor.execute(sql)
data = [item[0] for item in cursor.fetchall()]
if data[0] is not None:
mean = calculateMean(data)
# mean = getGrade(data[0])
else:
mean = ""
ret = mean
except(MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
return ret
def getStudentMean(marks_array, form):
schDets = getSchoolDetails()
subjects_lower_forms = schDets['lower_subjects']
if form == 1 or 2:
no_of_subjects = subjects_lower_forms
else:
no_of_subjects = 8
avg = sum(marks_array) / no_of_subjects
return round(avg, 2)
def checkIfAllSubjectsAreFilledOneRow(result_id, subjects, form):
form = int(form)
schDets = getSchoolDetails()
subjects_lower_forms = schDets['lower_subjects']
get_cols = ''
indexes = len(subjects) - 1 # -1 because first index is 0
for key, val in enumerate(subjects):
if key == indexes: # To avoid adding , after the last subject
get_cols = get_cols + val
else:
get_cols = get_cols + val + ', '
cursor = db.cursor()
sql = """SELECT %s FROM `exam_results` WHERE exam_result_id = %s""" % (
get_cols, result_id)
try:
cursor.execute(sql)
dataArray = []
# Get all column values that aren't null in order to count how many subjects have been filled
for row in cursor.fetchall():
for key, value in enumerate(subjects):
if row[key] is not None:
dataArray.append(row[key])
no_of_subjects_filled = len(dataArray)
if form < 3:
if no_of_subjects_filled < subjects_lower_forms:
ret = False
else:
ret = True
else:
if no_of_subjects_filled < 8:
ret = False
else:
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def getTotalOneRowLowerForms(result_id, subjects):
cursor = db.cursor()
# to create dynamic string of all subjects in one row eg Eng, Kis, Mat, Bio.....
cols = ''
indexes = len(subjects) - 1 # -1 because first index is 0
for key, val in enumerate(subjects):
if key == indexes: # To avoid adding + after the last subject
cols = cols + val
else:
cols = cols + val + ', '
sql = """SELECT %s FROM `exam_results` WHERE exam_result_id = %s""" % (
cols, result_id)
try:
cursor.execute(sql)
# data = [row[0] for row in cursor.fetchall()]
# ret = data[0]
points = 0
total = 0
for row in cursor:
for key, value in enumerate(subjects):
if row[key] is not None:
total += row[key]
points += getPointsF1nF2(row[key])
schDets = getSchoolDetails()
subjects_lower_forms = schDets['lower_subjects']
ret = {
'total': total,
'points': points,
'mean': round((float(points) / subjects_lower_forms), 1)
}
except (MySQLdb.Error, MySQLdb.Warning) as e:
# print e
ret = False
return ret
def getResultsByStudentAndExamID(exam_data, columns, subject_names, subject_ids, compulsory):
schDets = getSchoolDetails()
lower_subjects = schDets['lower_subjects']
data = exam_data.copy()
cursor = db.cursor()
columnStr = ''
# for x in columns:
# columnStr = columnStr + x + ', '
indexes = len(columns) - 1
for key, val in enumerate(columns):
if key == indexes: # To avoid adding , after the last subject
columnStr = columnStr + val
else:
columnStr = columnStr + val + ', '
sql = """SELECT `exam_result_id`, `student_id`, er.exam_id, form, form_pos, class_pos, total, points, mean, %s
FROM exam_results er
JOIN exams e ON e.exam_id = er.exam_id
WHERE er.exam_id = %s AND student_id = %s""" % (columnStr, data['exam_id'], data['student_id'])
try:
cursor.execute(sql)
# Sample data to prevent error on first load
oneSubjectResult = {
'subject': "No data available",
'mean': "",
'grade': "",
'rank': ""
}
resultData = {
'exam_result_id': 0,
'student_id': 0,
'exam_id': 0,
'form': 0,
'form_pos': 0,
'class_pos': 0,
'total': 0,
'points': 0,
'mean': 0,
'mean_grade': 0,
'subjectData': [oneSubjectResult],
'students_in_class': 0,
'students_in_form': 0,
}
dataArray = []
for row in cursor:
resultData = {
'exam_result_id': row[0],
'student_id': row[1],
'exam_id': row[2],
'form': row[3],
'form_pos': row[4],
'class_pos': row[5],
'total': row[6],
'points': row[7],
'mean': row[8],
'students_in_class': getNoOfStudentsInClass(data['class_id']),
'students_in_form': getNoOfStudentsInForm(data['form']),
}
if int(data['form']) < 3:
# resultData['mean_grade'] = getGradeForm1n2(resultData['points'])
resultData['mean_grade'] = getMeanGrade(resultData['mean'])
else:
# resultData['mean_grade'] = getGradeForm3n4(resultData['points']/7)
resultData['mean_grade'] = getMeanGrade(resultData['mean'])
for key, val in enumerate(columns):
subjectData = {
'subject': subject_names[key],
'mean': "" if row[8 + 1 + key] is None else row[8 + 1 + key],
}
if int(data['form']) < 3:
subjectData['grade'] = getGradeForm1n2(row[8 + 1 + key])
else:
subjectData['grade'] = getGradeForm3n4(row[8 + 1 + key])
if row[8 + 1 + key] is not None:
# Get position in form
# set class_id value to 0 in order to get position in form
data['class_id'] = 0
class_results = getExamResults(data, [val])
for result in class_results:
if result['student_id'] == data['student_id']:
subjectData['rank'] = result['number']
else:
subjectData['rank'] = ""
dataArray.append(subjectData)
resultData['subjectData'] = dataArray
ret = resultData
except(MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
return ret