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