def post(self, request, *args, **kwargs):
data = json.loads(request.body)
scl = data['school_id']
cls = data['the_class']
sec = data['section']
school = School.objects.get(id=scl)
the_class = Class.objects.get(school=school, standard=cls)
section = Section.objects.get(school=school, section=sec)
excel_file_name = 'Promotion_List_%s_%s.xlsx' % (str(the_class.standard), str(section.section))
output = StringIO.StringIO(excel_file_name)
workbook = xlsxwriter.Workbook(output)
sheet = workbook.add_worksheet('Promotion List')
fmt = format()
title = workbook.add_format(fmt.get_title())
header = workbook.add_format(fmt.get_header())
cell_normal = workbook.add_format(fmt.get_cell_normal())
cell_center = workbook.add_format(fmt.get_cell_center())
cell_left = workbook.add_format(fmt.get_cell_left())
absent_format = workbook.add_format(fmt.get_absent_format())
title_text = 'Student Promotion List for class %s-%s for Academic session 2019-20' % (cls, sec)
sheet.merge_range('A1:F1', title_text, title)
sheet.set_column('A:A', 4)
sheet.set_column('B:B', 8)
sheet.set_column('C:C', 15)
sheet.set_column('D:D', 15)
sheet.set_column('E:E', 25)
sheet.set_footer('&LClass Teacher Signature&RPrincipal Signature')
sheet.set_paper(9)
sheet.fit_to_pages(1, 1)
start_col = 0
row = 2
col = start_col
sheet.write(row, col, ugettext("S No."), header)
col += 1
sheet.write(row, col, ugettext("Reg No"), header)
col += 1
sheet.write(row, col, ugettext("Student"), header)
col += 1
sheet.write(row, col, ugettext("Promotion Status"), header)
col += 1
sheet.write(row, col, ugettext("Not Promotion Reason"), header)
row += 1
col = start_col
s_no = 1
students = Student.objects.filter(current_class=the_class,
current_section=section, active_status=True).order_by('fist_name')
for student in students:
print('\ndealing with: %s' % student)
sheet.write_number(row, col, s_no, cell_center)
col += 1
reg_no = student.student_erp_id
print(reg_no)
sheet.write_string(row, col, reg_no, cell_center)
col += 1
sheet.write_string(row, col, ugettext(student.fist_name + ' ' + student.last_name), cell_left)
col += 1
try:
result = ExamResult.objects.get(student=student)
promotion_status = result.status
if promotion_status:
sheet.write_string(row, col, 'Promoted', cell_normal)
else:
sheet.write_string(row, col, 'Not Promoted', absent_format)
col += 1
no_promotion_reason = result.detain_reason
sheet.write_string(row, col, no_promotion_reason, cell_normal)
except Exception as e:
print('exception 23122019-A from test_management.py %s %s' % (e.message, type(e)))
print('failed to retrieve promotion status for %s' % student)
row += 1
s_no += 1
col = start_col
workbook.close()
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=' + excel_file_name
response.write(output.getvalue())
return response
def get(self, request, *args, **kwargs):
school_id = self.request.query_params.get('school_id')
school = School.objects.get(id=school_id)
exam_title = self.request.query_params.get('exam_title')
exam = Exam.objects.get(school=school, title=exam_title)
classes = ['I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX', 'X', 'XI', 'XII']
start_class = exam.start_class
end_class = exam.end_class
start = classes.index(start_class)
end = classes.index(end_class)
wings = get_wings(school)
junior_classes = wings['junior_classes']
middle_classes = wings['middle_classes']
ninth_tenth = wings['ninth_tenth']
higher_classes = wings['higher_classes']
if start_class in middle_classes:
subjects = ['English', 'Hindi', 'Sanskrit', 'French', 'Science',
'Mathematics', 'Social Studies', 'Computer'
]
if start_class in ninth_tenth:
subjects = ['English', 'Hindi', 'Sanskrit', 'French', 'Science',
'Mathematics', 'Social Studies', 'FIT'
]
if start_class in higher_classes:
subjects = ['English', 'Mathematics', 'Biology', 'Physics', 'Chemistry', 'Economics', 'Accountancy',
'Business Studies', 'Computer Science', 'Fine Arts', 'History', 'Informatics Practices',
'Physical Education'
]
file_name = 'Unscheduled_test.xlsx'
output = StringIO.StringIO(file_name)
file = xlsxwriter.Workbook(output)
sheet_name = 'Unscheduled_test.xlsx'
sheet = file.add_worksheet(sheet_name)
sheet.set_portrait()
sheet.set_paper(9)
sheet.fit_to_pages(1, 0)
fmt = format()
title_format = file.add_format(fmt.get_title())
title_format.set_border()
header = file.add_format(fmt.get_header())
big_font = file.add_format(fmt.get_large_font())
big_font.set_color('#33691E')
medium_font = file.add_format(fmt.get_medium_fong())
medium_font.set_color('#827717')
section_heading = file.add_format(fmt.get_section_heading())
section_heading.set_color('#4E342E')
cell_bold = file.add_format(fmt.get_cell_bold())
cell_bold.set_border()
cell_center = file.add_format(fmt.get_cell_center())
cell_center.set_border()
cell_left = file.add_format(fmt.get_cell_left())
cell_left.set_border()
cell_normal = file.add_format(fmt.get_cell_normal())
cell_normal.set_border()
sheet.merge_range('A1:D1', 'Unscheduled Tests for %s' % exam_title, header)
row = 1
col = 0
s_no = 1
sheet.write_string(row, col, "S No", cell_bold)
col += 1
sheet.write_string(row, col, "Class", cell_bold)
col += 1
sheet.write_string(row, col, "Subject", cell_bold)
row += 1
col = 0
for a_class in classes[start:end + 1]:
the_class = Class.objects.get(school=school, standard=a_class)
sections = Section.objects.filter(school=school)
for section in sections:
students = Student.objects.filter(current_class=the_class, current_section=section)
if students.count() > 0:
for a_subject in subjects:
try:
subject = Subject.objects.get(school=school, subject_name=a_subject)
test = ClassTest.objects.get(exam=exam, subject=subject,
the_class=the_class, section=section)
print('test for %s exam %s class %s-%s has been created' %
(subject, exam, the_class, section))
except Exception as e:
print('exception 21022020-A from exam test_management.py %s %s' % (e.message, type(e)))
if subject is None:
print('%s does not exist in the scheme of %s' % (a_subject, school))
continue
print('test for %s exam %s class %s-%s has not been created' %
(subject, exam, the_class, section))
sheet.write_number(row, col, s_no, cell_normal)
s_no += 1
col += 1
class_section = '%s-%s' % (the_class, section)
sheet.write_string(row, col, class_section, cell_normal)
col += 1
sheet.write_string(row, col, a_subject, cell_normal)
row += 1
col = 0
subject = None
file.close()
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s' % file_name
response.write(output.getvalue())
return response
def post(self, request, *args, **kwargs):
data = json.loads(request.body)
school_id = data['school_id']
school = School.objects.get(id=school_id)
file_name = 'class_teacher.xlsx'
output = StringIO.StringIO(file_name)
file = xlsxwriter.Workbook(output)
fmt = format()
title_format = file.add_format(fmt.get_title())
title_format.set_border()
header = file.add_format(fmt.get_header())
cell_bold = file.add_format(fmt.get_cell_bold())
cell_bold.set_border()
cell_center = file.add_format(fmt.get_cell_center())
cell_center.set_border()
cell_left = file.add_format(fmt.get_cell_left())
cell_left.set_border()
cell_normal = file.add_format(fmt.get_cell_normal())
cell_normal.set_border()
sheet_name = 'Class Teacher List'
sheet = file.add_worksheet(sheet_name)
sheet.set_portrait()
sheet.set_paper(9)
sheet.fit_to_pages(1, 0)
sheet.set_footer('&L%s&R%s' % (school.school_name, 'Class Teacher List'))
sheet.set_column('A:A', 5)
sheet.set_column('B:B', 8)
sheet.set_column('C:C', 8)
sheet.set_column('D:D', 20)
sheet.set_column('E:E', 15)
sheet.merge_range('A1:E1', school.school_name, header)
sheet.merge_range('A2:E2', 'Class Teacher List', header)
row = 2
col = 0
s_no = 1
sheet.write_string(row, col, 'S No', cell_bold)
col += 1
sheet.write_string(row, col, 'Class', cell_bold)
col += 1
sheet.write_string(row, col, 'Section', cell_bold)
col += 1
sheet.write_string(row, col, 'Class Teacher', cell_bold)
col += 1
sheet.write_string(row, col, 'Mobile Number', cell_bold)
row += 1
col = 0
classes = Class.objects.filter(school=school).order_by('sequence')
sections = Section.objects.filter(school=school).order_by('section')
for a_class in classes:
for section in sections:
students = Student.objects.filter(current_class=a_class, current_section=section)
if students.count() < 1:
print('class %s-%s of %s is empty. skipping...' % (a_class, section, school))
continue
print('determining the class teacher for class %s-%s in %s' % (a_class, section, school))
sheet.write_number(row, col, s_no, cell_normal)
s_no += 1
col += 1
sheet.write_string(row, col, a_class.standard, cell_normal)
col += 1
sheet.write_string(row, col, section.section, cell_normal)
col += 1
try:
ct = ClassTeacher.objects.get(school=school, standard=a_class, section=section)
class_teacher = ct.class_teacher
name = '%s %s' % (class_teacher.first_name, class_teacher.last_name)
sheet.write_string(row, col, name, cell_left)
col += 1
mobile = class_teacher.mobile
sheet.write_string(row, col, mobile, cell_normal)
except Exception as e:
print('exception 04022020-D from teacher views.py %s %s' % (e.message, type(e)))
print('class teacher for class %s-%s of %s not set' % (a_class, section, school))
sheet.write_string(row, col, 'Not Set', cell_bold)
col += 1
sheet.write_string(row, col, 'Not Set', cell_bold)
row += 1
col = 0
file.close()
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s' % file_name
response.write(output.getvalue())
return response
def get(self, request, *args, **kwargs):
school_id = request.query_params.get('school_id')
school = School.objects.get(id=school_id)
standard = request.query_params.get('standard')
the_class = Class.objects.get(school=school, standard=standard)
excel_file_name = '%s_%s_master_data.xlsx' % (str(school_id),
the_class)
print('excel_file_name = %s' % excel_file_name)
output = StringIO.StringIO(excel_file_name)
workbook = xlsxwriter.Workbook(output)
fmt = format()
cell_bold = workbook.add_format(fmt.get_cell_bold())
cell_bold.set_border()
cell_normal = workbook.add_format(fmt.get_cell_normal())
cell_normal.set_border()
cell_left = workbook.add_format(fmt.get_cell_left())
cell_left.set_border()
t1_sheet = workbook.add_worksheet('Term I')
t2_sheet = workbook.add_worksheet('Term II')
cons_sheet = workbook.add_worksheet('Consolidated')
t1_sheet.set_column('B:B', 20)
t2_sheet.set_column('B:B', 20)
cons_sheet.set_column('B:B', 20)
wings = get_wings(school)
middle_classes = wings['middle_classes']
ninth_tenth = wings['ninth_tenth']
higher_classes = wings['higher_classes']
if standard not in higher_classes:
subject_list = [
'English', 'Hindi', 'Sanskrit', 'French', 'Mathematics',
'Science', 'Social Studies', 'Computer'
]
# prepare excel headers
row = 0
col = 0
t1_sheet.write_string(row, col, 'S No', cell_bold)
t2_sheet.write_string(row, col, 'S No', cell_bold)
cons_sheet.write_string(row, col, 'S No', cell_bold)
col += 1
t1_sheet.write_string(row, col, 'Student', cell_bold)
t2_sheet.write_string(row, col, 'Student', cell_bold)
col += 1
t1_sheet.write_string(row, col, 'Class', cell_bold)
t2_sheet.write_string(row, col, 'Class', cell_bold)
cons_sheet.write_string(row, col, 'Class', cell_bold)
col += 1
t1_sheet.write_string(row, col, 'Section', cell_bold)
t2_sheet.write_string(row, col, 'Section', cell_bold)
cons_sheet.write_string(row, col, 'Section', cell_bold)
col += 1
for subject in subject_list:
t1_sheet.write_string(row, col, subject, cell_bold)
t2_sheet.write_string(row, col, subject, cell_bold)
cons_sheet.write_string(row, col, subject, cell_bold)
col += 1
t1_sheet.write_string(row, col, 'Total', cell_bold)
t2_sheet.write_string(row, col, 'Total', cell_bold)
cons_sheet.write_string(row, col, 'Total', cell_bold)
row += 1
col = 0
s_no = 1
students = Student.objects.filter(
current_class=the_class).order_by('fist_name').order_by(
'current_section')
for student in students:
print(
'extracting marks in each subject for %s of %s-%s' %
(student, student.current_class, student.current_section))
t1_sheet.write_number(row, col, s_no, cell_normal)
t2_sheet.write_number(row, col, s_no, cell_normal)
cons_sheet.write_number(row, col, s_no, cell_normal)
s_no += 1
col += 1
student_name = '%s %s' % (student.fist_name, student.last_name)
t1_sheet.write_string(row, col, student_name, cell_left)
t2_sheet.write_string(row, col, student_name, cell_left)
cons_sheet.write_string(row, col, student_name, cell_left)
col += 1
t1_sheet.write_string(row, col, student.current_class.standard,
cell_normal)
t2_sheet.write_string(row, col, student.current_class.standard,
cell_normal)
cons_sheet.write_string(row, col,
student.current_class.standard,
cell_normal)
col += 1
t1_sheet.write_string(row, col,
student.current_section.section,
cell_normal)
t2_sheet.write_string(row, col,
student.current_section.section,
cell_normal)
cons_sheet.write_string(row, col,
student.current_section.section,
cell_normal)
col += 1
exams = Exam.objects.filter(school=school,
exam_type='term',
end_class='VIII')
for a_subject in subject_list:
subject = Subject.objects.get(school=school,
subject_name=a_subject)
subject_total = 0.0
try:
analysis1 = SubjectAnalysis.objects.get(
student=student, exam=exams[0], subject=subject)
marks = analysis1.total_marks
subject_total += float(marks)
t1_sheet.write_number(row, col, marks, cell_normal)
except Exception as e:
print(
'exception 07012020-A from analytics master_data.py %s %s'
% (e.message, type(e)))
print(
'cound not retrieve subject marks for %s subject %s in %s exam for %s of %s-%s'
% (student, subject, exams[0], student, the_class,
student.current_section))
try:
analysis2 = SubjectAnalysis.objects.get(
student=student, exam=exams[1], subject=subject)
marks = analysis2.total_marks
subject_total += float(marks)
t2_sheet.write_number(row, col, marks, cell_normal)
except Exception as e:
print(
'exception 07012020-B from analytics master_data.py %s %s'
% (e.message, type(e)))
print(
'cound not retrieve subject marks for %s subject %s in %s exam for %s of %s-%s'
% (student, subject, exams[0], student, the_class,
student.current_section))
cons_sheet.write_number(row, col, subject_total,
cell_normal)
col += 1
try:
total_marks1 = StudentTotalMarks.objects.get(
student=student, exam=exams[0]).total_marks
t1_sheet.write_number(row, col, total_marks1)
except Exception as e:
print(
'exception 07012020-C from analytics master_data.py %s %s'
% (e.message, type(e)))
print(
'cound not retrieve total marks for %s in %s exam for %s of %s-%s'
% (student, exams[0], student, the_class,
student.current_section))
try:
total_marks2 = StudentTotalMarks.objects.get(
student=student, exam=exams[1]).total_marks
t2_sheet.write_number(row, col, total_marks2)
except Exception as e:
print(
'exception 07012020-D from analytics master_data.py %s %s'
% (e.message, type(e)))
print(
'cound not retrieve total marks for %s in %s exam for %s of %s-%s'
% (student, exams[1], student, the_class,
student.current_section))
row += 1
col = 0
workbook.close()
# 17/01/2019 - upload file to cloud storage
try:
storage_client = storage.Client()
bucket = storage_client.get_bucket('classup')
file_path = 'classup2/Analytics/school/%s/%s' % (str(school_id),
excel_file_name)
blob = bucket.blob(file_path)
blob.upload_from_string(output.getvalue())
print(
'successfully uploaded %s master data file to cloud storage' %
file_path)
except Exception as e:
print('exception 17012020-A from analytics master_data.py %s %s' %
(e.message, type(e)))
print('failed to upload %s to cloud storage' % file_path)
response = HttpResponse(content_type='application/vnd.ms-excel')
response[
'Content-Disposition'] = 'attachment; filename=' + excel_file_name
response.write(output.getvalue())
return response
def get(self, request, *args, **kwargs):
school_id = request.query_params.get('school_id')
school = School.objects.get(id=school_id)
standard = request.query_params.get('standard')
the_class = Class.objects.get(school=school, standard=standard)
storage_client = storage.Client()
bucket = storage_client.get_bucket('classup')
master_data_file = '%s_%s_master_data.xlsx' % (str(school_id),
the_class)
file_path = 'classup2/Analytics/school/%s/%s' % (str(school_id),
master_data_file)
blob = bucket.blob(file_path)
local_path = 'analytics/%s' % master_data_file
blob.download_to_filename(local_path)
analytics_file_name = 'class_%s_analytics.xlsx' % the_class
output = StringIO.StringIO(analytics_file_name)
analytics = xlsxwriter.Workbook(output)
fmt = format()
title_format = analytics.add_format(fmt.get_title())
title_format.set_border()
header = analytics.add_format(fmt.get_header())
big_font = analytics.add_format(fmt.get_large_font())
big_font.set_color('#33691E')
medium_font = analytics.add_format(fmt.get_medium_fong())
medium_font.set_color('#827717')
section_heading = analytics.add_format(fmt.get_section_heading())
section_heading.set_color('#4E342E')
cell_bold = analytics.add_format(fmt.get_cell_bold())
cell_bold.set_border()
cell_normal = analytics.add_format(fmt.get_cell_normal())
cell_normal.set_border()
cell_left = analytics.add_format(fmt.get_cell_left())
cell_left.set_border()
cell_small = analytics.add_format(fmt.get_cell_small())
cell_small.set_border()
cell_red = analytics.add_format(fmt.get_cell_red())
cell_green = analytics.add_format(fmt.get_cell_green())
perc_format = analytics.add_format(fmt.get_perc_format())
big_perc_format = analytics.add_format(fmt.get_big_perc_format())
perc_format.set_color('#37474F')
colors = fmt.get_colors()
sheets = ['Term I', 'Term II']
for a_sheet in sheets:
sheet = pd.read_excel(local_path, sheet_name=a_sheet, index_col=0)
wb_sheet = analytics.add_worksheet(a_sheet)
wb_sheet.set_landscape()
wb_sheet.set_paper(9) # A4 paper
wb_sheet.fit_to_pages(1, 0)
header_text = 'Analytics Sheet Class %s' % (the_class)
wb_sheet.set_header('&L%s&RPage &P of &N' % header_text)
wb_sheet.merge_range(
'A1:L1', '%s Analytics for class %s term: %s' %
(school, the_class, a_sheet), title_format)
student_df = sheet['Student']
total_students = student_df.count()
wb_sheet.set_column('B:B', 4)
wb_sheet.set_column('C:C', 14)
wb_sheet.set_column('D:D', 10)
wb_sheet.set_column('E:E', 12)
wb_sheet.set_column('F:F', 12)
wb_sheet.set_column('G:G', 10)
wb_sheet.set_column('H:H', 14)
wb_sheet.set_column('I:I', 10)
wb_sheet.set_column('J:J', 14)
wb_sheet.set_column('K:K', 7)
row = 2
col = 3
wb_sheet.merge_range(row, col, row + 1, col, 'Class', medium_font)
col += 1
wb_sheet.merge_range(row, col, row + 1, col, the_class.standard,
big_font)
col += 1
wb_sheet.merge_range(row, col, row + 1, col, 'Students',
medium_font)
col += 1
wb_sheet.merge_range(row, col, row + 1, col, total_students,
big_font)
col += 1
sections_df = sheet['Section']
sections = sections_df.unique()
wb_sheet.merge_range(row, col, row + 1, col, 'Sections',
medium_font)
col += 1
wb_sheet.merge_range(row, col, row + 1, col, sections_df.nunique(),
big_font)
# student section wise distribution analytics
section_wise_students = sections_df.value_counts()
student_in_section = []
for section in sections:
student_in_section.append(section_wise_students[section])
row += 4
col = 4
categories = [
a_sheet, row, col, row + sections_df.nunique() - 1, col
]
wb_sheet.write_column(row, col, sections, cell_bold)
col += 1
values = [a_sheet, row, col, row + sections_df.nunique() - 1, col]
wb_sheet.write_column(row, col, student_in_section, cell_bold)
chart = analytics.add_chart({'type': 'column'})
chart.set_style(28)
chart.set_legend({'none': True})
chart.set_y_axis({
'visible': False,
'major_gridlines': {
'visible': False
},
'min': 0,
'max': 50
})
chart.add_series({
'categories': categories,
'values': values,
'marker': {
'type': 'automatic'
},
'gradient': {
'colors': ['#311B92', '#7986CB']
},
'data_labels': {
'value': True
},
})
chart.set_title({
'name': 'Section wise Students',
'name_font': {
'size': 14,
'color': colors[random.randrange(len(colors))]
}
})
wb_sheet.insert_chart('D6', chart, {'x_offset': 10, 'y_offset': 0})
# total marks analytics for all sections
row = 20
col = 3
wb_sheet.merge_range(row, col, row + 1, col + 2,
'Total Marks Analysis', section_heading)
total_marks_df = sheet[['Student', 'Section', 'Total']]
max_marks = 700.00
row += 2
wb_sheet.merge_range(row, col, row + 1, col, 'Highest',
medium_font)
col += 1
highest = round(total_marks_df['Total'].max(), 2) / max_marks
wb_sheet.merge_range(row, col, row + 1, col, highest,
big_perc_format)
col += 1
wb_sheet.merge_range(row, col, row + 1, col, 'Average',
medium_font)
col += 1
average = round(total_marks_df['Total'].mean(), 2) / max_marks
wb_sheet.merge_range(row, col, row + 1, col, average,
big_perc_format)
col += 1
wb_sheet.merge_range(row, col, row + 1, col, 'Min', medium_font)
col += 1
min = round(total_marks_df['Total'].min(), 2) / max_marks
wb_sheet.merge_range(row, col, row + 1, col, min, big_perc_format)
# analytics for each section separate - highest, average and min
row += 4
col = 4
wb_sheet.write_string(row, col, 'Section', cell_bold)
col += 1
wb_sheet.write_string(row, col, 'Highest', cell_bold)
col += 1
wb_sheet.write_string(row, col, 'Average', cell_bold)
col += 1
wb_sheet.write_string(row, col, 'Min', cell_bold)
row += 1
col = 4
wb_sheet.write_column(row, col, sections, cell_bold)
# as we have consolidated analytics for all sections readily available, insert it now
row += sections_df.nunique()
wb_sheet.write_string(row, col, 'All Sections', cell_bold)
col += 1
wb_sheet.write_number(row, col, highest, perc_format)
col += 1
wb_sheet.write_number(row, col, average, perc_format)
col += 1
wb_sheet.write_number(row, col, min, perc_format)
# now, go to the top of this table to insert section specific analytics
row -= sections_df.nunique()
col = 5
categories = [
a_sheet, row, col - 1, row + sections_df.nunique(), col - 1
]
for section in sections:
subset = total_marks_df['Section'] == section
highest = round(
total_marks_df[subset]['Total'].max() / max_marks, 2)
wb_sheet.write_number(row, col, highest, perc_format)
col += 1
average = round(
total_marks_df[subset]['Total'].mean() / max_marks, 2)
wb_sheet.write_number(row, col, average, perc_format)
col += 1
min = round(total_marks_df[subset]['Total'].min() / max_marks,
2)
wb_sheet.write_number(row, col, min, perc_format)
row += 1
col = 5
chart = analytics.add_chart({'type': 'column'})
chart.set_style(26)
chart.set_legend({'position': 'bottom'})
chart.set_y_axis({
'major_gridlines': {
'visible': False
},
'min': 0,
'max': 1
})
values = [a_sheet, row - sections_df.nunique(), col, row, col]
chart.add_series({
'categories': categories,
'name': 'Highest',
'values': values,
'marker': {
'type': 'automatic'
},
'data_labels': {
'value': True,
'font': {
'name': 'Consolas',
'size': 8,
}
},
'gradient': {
'colors': ['#1B5E20', '#66BB6A']
},
'overlap': 0,
})
col += 1
values = [a_sheet, row - sections_df.nunique(), col, row, col]
chart.add_series({
'name': 'Average',
'values': values,
'marker': {
'type': 'automatic'
},
'gradient': {
'colors': ['#9E9D24', '#D4E157']
},
'data_labels': {
'value': True,
'font': {
'name': 'Consolas',
'size': 8,
'rotation': -90
}
},
})
col += 1
values = [a_sheet, row - sections_df.nunique(), col, row, col]
chart.add_series({
'name': 'Min',
'values': values,
'marker': {
'type': 'automatic'
},
'gradient': {
'colors': ['#FF5252', '#B71C1C']
},
'data_labels': {
'value': True,
'font': {
'name': 'Consolas',
'size': 8,
'rotation': -90
}
},
})
chart.set_title({
'name':
'Class %s - Section wise Total marks comparison' % the_class,
'name_font': {
'name': 'Calibri',
'color': colors[random.randrange(len(colors))],
'size': 12
}
})
row = 36
col = 0
wb_sheet.insert_chart(row, col, chart, {
'x_offset': 20,
'y_offset': 10
})
# show the names of top 10 students
top_10 = total_marks_df.nlargest(10, ['Total'])
row += 1
col = 7
wb_sheet.merge_range(row, col, row, col + 4,
'Class %s Top 10 students' % the_class,
header)
row += 1
wb_sheet.write_string(row, col, 'Rank', cell_bold)
col += 1
wb_sheet.merge_range(row, col, row, col + 1, 'Student', cell_bold)
col += 2
wb_sheet.write_string(row, col, 'Section', cell_bold)
col += 1
wb_sheet.write_string(row, col, '%', cell_bold)
row += 1
col = 7
s_no = 1
for idx in top_10.index:
wb_sheet.write_number(row, col, s_no, cell_normal)
s_no += 1
col += 1
wb_sheet.merge_range(row, col, row, col + 1,
top_10['Student'][idx], cell_normal)
col += 2
wb_sheet.write_string(row, col, top_10['Section'][idx],
cell_normal)
col += 1
wb_sheet.write_number(row, col,
top_10['Total'][idx] / max_marks,
perc_format)
row += 1
col = 7
# student distribution in percentage segments (> 85, 85-70, 70-50, < 50)
slabs = [
'Above 85%', 'Between 85%-70%', 'Between 70%-50%', 'Below 50%'
]
slab_values = []
above_85 = total_marks_df[total_marks_df['Total'] >= (
max_marks * .85)]['Student'].count()
print('above_85=%d' % above_85)
slab_values.append(
round(float(above_85) / float(total_students), 2))
between_85_70 = total_marks_df[
(total_marks_df['Total'] < (max_marks * .85))
& (total_marks_df['Total'] >
(max_marks * .70))]['Student'].count()
print('between_85_70=%d' % between_85_70)
slab_values.append(
round(float(between_85_70) / float(total_students), 2))
between_70_50 = total_marks_df[
(total_marks_df['Total'] < (max_marks * .7))
& (total_marks_df['Total'] >
(max_marks * .50))]['Student'].count()
print('between_70_50=%d' % between_70_50)
slab_values.append(
round(float(between_70_50) / float(total_students), 2))
below_50 = total_marks_df[total_marks_df['Total'] <= (
max_marks * .5)]['Student'].count()
print('below_50=%d' % below_50)
slab_values.append(
round(float(below_50) / float(total_students), 2))
print(slab_values)
row += 5
col = 5
wb_sheet.merge_range(row, col, row + 1, col + 2,
'Total Marks Slab wise', medium_font)
col += 1
row += 2
wb_sheet.write_column(row, col, slabs)
categories = [a_sheet, row, col, row + 3, col]
col += 1
wb_sheet.write_column(row, col, slab_values, perc_format)
values = [a_sheet, row, col, row + 3, col]
col = 5
chart = analytics.add_chart({'type': 'pie'})
chart.set_style(10)
chart.set_legend({'position': 'bottom'})
chart.set_title({
'name':
'Class %s - Slab Wise Marks All Section ' % the_class,
'name_font': {
'name': 'Calibri',
'color': colors[random.randrange(len(colors))],
'size': 12
}
})
col += 1
chart.add_series({
'categories':
categories,
'values':
values,
'data_labels': {
'value': True,
'position': 'outside_end',
'leader_lines': True
},
'points': [
{
'gradient': {
'colors': ['#1B5E20', '#A5D6A7']
}
},
{
'gradient': {
'colors': ['#01579B', '#039BE5']
}
},
{
'gradient': {
'colors': ['#9E9D24', '#D4E157']
}
},
{
'gradient': {
'colors': ['#FF5252', '#B71C1C']
}
},
],
})
col = 3
wb_sheet.insert_chart(row, col, chart, {
'x_offset': 25,
'y_offset': 0
})
# Show slab wise distribution of marks for each section separately
row += 17
col = 0
wb_sheet.merge_range(row, col, row + 1, col + 5,
'Slab Wise Total Marks for Each Section',
medium_font)
idx1 = 0
for section in sections:
print('total marks slab wise for section: %s' % section)
this_section_df = total_marks_df[total_marks_df['Section'] ==
section]
student_count = student_in_section[idx1]
idx1 += 1
slab_values = []
above_85 = this_section_df[this_section_df['Total'] >= (
max_marks * .85)]['Student'].count()
print('above_85=%d' % above_85)
slab_values.append(
round(float(above_85) / float(student_count), 2))
between_85_70 = \
this_section_df[(this_section_df['Total'] < (max_marks * .85)) & (this_section_df['Total'] >
(max_marks * .70))][
'Student'].count()
print('between_85_70=%d' % between_85_70)
slab_values.append(
round(float(between_85_70) / float(student_count), 2))
between_70_50 = \
this_section_df[(this_section_df['Total'] < (max_marks * .7)) & (this_section_df['Total'] >
(max_marks * .50))][
'Student'].count()
print('between_70_50=%d' % between_70_50)
slab_values.append(
round(float(between_70_50) / float(student_count), 2))
below_50 = this_section_df[this_section_df['Total'] <= (
max_marks * .5)]['Student'].count()
print('below_50=%d' % below_50)
slab_values.append(
round(float(below_50) / float(student_count), 2))
print(slab_values)
row += 2
col = 3
wb_sheet.write_column(row, col, slabs)
categories = [a_sheet, row, col, row + 3, col]
col += 1
wb_sheet.write_column(row, col, slab_values, perc_format)
values = [a_sheet, row, col, row + 3, col]
chart = analytics.add_chart({'type': 'pie'})
chart.set_style(10)
chart.set_size({'x_scale': .8, 'y_scale': .85})
chart.set_legend({'position': 'right'})
chart.set_title({
'name':
'Class %s-%s - Slab Wise Marks ' % (the_class, section),
'name_font': {
'name': 'Calibri',
'color': colors[random.randrange(len(colors))],
'size': 12
}
})
chart.add_series({
'categories':
categories,
'values':
values,
'data_labels': {
'value': True,
'position': 'outside_end',
'leader_lines': True
},
'points': [
{
'gradient': {
'colors': ['#1B5E20', '#A5D6A7']
}
},
{
'gradient': {
'colors': ['#01579B', '#039BE5']
}
},
{
'gradient': {
'colors': ['#9E9D24', '#D4E157']
}
},
{
'gradient': {
'colors': ['#FF5252', '#B71C1C']
}
},
],
})
col = 1
wb_sheet.insert_chart(row, col, chart, {
'x_offset': 0,
'y_offset': 0
})
# besides this pie chart show the toppers (> 85%) & trailers (< 50%) students
toppers = this_section_df[this_section_df['Total'] > (
max_marks * .85)].nlargest(20, 'Total')
trailers = this_section_df[this_section_df['Total'] < (
max_marks * .5)].nsmallest(20, 'Total')
col = 7
wb_sheet.merge_range(row, col, row, col + 1,
'Students in Top Slab', header)
wb_sheet.merge_range(row, col + 2, row, col + 3,
'Students in Bottom Slab', header)
row += 1
topper_row = row
trailers_row = row
for idx in toppers.index:
wb_sheet.write_string(topper_row, col,
toppers['Student'][idx], cell_green)
col += 1
wb_sheet.write_number(topper_row, col,
toppers['Total'][idx] / max_marks,
perc_format)
topper_row += 1
col = 7
col = 9
for idx in trailers.index:
wb_sheet.write_string(trailers_row, col,
trailers['Student'][idx], cell_red)
col += 1
wb_sheet.write_number(trailers_row, col,
trailers['Total'][idx] / max_marks,
perc_format)
trailers_row += 1
col = 9
row += 15
col = 0
subject_list = [
'English', 'Hindi', 'Mathematics', 'Science', 'Social Studies',
'Computer'
]
row += 1
for subject in subject_list:
wb_sheet.merge_range(row, col, row + 1, col + 4,
'Subject Analysis: %s' % subject,
section_heading)
subject_df = sheet[['Student', 'Section', subject]]
max_marks = 100.00
highest = round(subject_df[subject].max(), 2) / max_marks
print('highest in %s: %.2f' % (subject, highest))
average = round(subject_df[subject].mean(), 2) / max_marks
print('average in %s: %.2f' % (subject, average))
min = round(subject_df[subject].min(), 2) / max_marks
print('min in %s: %.2f' % (subject, min))
row += 3
col += 4
wb_sheet.write_string(row, col, 'Section', cell_small)
col += 1
wb_sheet.write_string(row, col, 'Highest', cell_small)
col += 1
wb_sheet.write_string(row, col, 'Average', cell_small)
col += 1
wb_sheet.write_string(row, col, 'Min', cell_small)
row += 1
col -= 3
wb_sheet.write_column(row, col, sections, cell_small)
row += sections_df.nunique()
wb_sheet.write_string(row, col, 'All Sections', cell_small)
# as we have consolidated analytics for all sections readily available, insert it now
col += 1
wb_sheet.write_number(row, col, highest, perc_format)
col += 1
wb_sheet.write_number(row, col, average, perc_format)
col += 1
wb_sheet.write_number(row, col, min, perc_format)
# now, go to the top of this table to insert section specific analytics
row -= sections_df.nunique()
col -= 2
categories = [
a_sheet, row, col - 1, row + sections_df.nunique(), col - 1
]
for section in sections:
subset = subject_df['Section'] == section
highest = round(
subject_df[subset][subject].max() / max_marks, 2)
print('highest in %s section %s: %.2f' %
(subject, section, highest))
wb_sheet.write_number(row, col, highest, perc_format)
col += 1
average = round(
subject_df[subset][subject].mean() / max_marks, 2)
print('average in %s section %s: %.2f' %
(subject, section, average))
wb_sheet.write_number(row, col, average, perc_format)
col += 1
min = round(subject_df[subset][subject].min() / max_marks,
2)
print('min in %s section %s: %.2f' %
(subject, section, min))
wb_sheet.write_number(row, col, min, perc_format)
col = 5
row += 1
chart = analytics.add_chart({'type': 'column'})
chart.set_style(28)
chart.set_legend({'position': 'bottom'})
chart.set_y_axis({
'major_gridlines': {
'visible': False
},
'min': 0,
'max': 1
})
values = [a_sheet, row - sections_df.nunique(), col, row, col]
chart.add_series({
'categories': categories,
'name': 'Highest',
'values': values,
'marker': {
'type': 'automatic'
},
'data_labels': {
'value': True,
'font': {
'name': 'Consolas',
'size': 8,
}
},
'gradient': {
'colors': ['#1B5E20', '#66BB6A']
},
'overlap': 0,
})
col += 1
values = [a_sheet, row - sections_df.nunique(), col, row, col]
chart.add_series({
'name': 'Average',
'values': values,
'marker': {
'type': 'automatic'
},
'gradient': {
'colors': ['#9E9D24', '#D4E157']
},
'data_labels': {
'value': True,
'font': {
'name': 'Consolas',
'size': 8,
'rotation': -90
}
},
})
col += 1
values = [a_sheet, row - sections_df.nunique(), col, row, col]
chart.add_series({
'name': 'Min',
'values': values,
'marker': {
'type': 'automatic'
},
'gradient': {
'colors': ['#FF5252', '#B71C1C']
},
'data_labels': {
'value': True,
'font': {
'name': 'Consolas',
'size': 8,
'rotation': -90
}
},
})
chart.set_title({
'name':
'Class %s - Section wise %s marks comparison' %
(the_class, subject),
'name_font': {
'name': 'Calibri',
'color': colors[random.randrange(len(colors))],
'size': 12
}
})
row -= sections_df.nunique() + 2
col = 3
wb_sheet.insert_chart(row, col, chart, {
'x_offset': 20,
'y_offset': 20
})
# show the names of top & bottom 10 students in this subject
row += 20
col = 1
wb_sheet.merge_range(
row, col, row, col + 3,
'Class %s Top 10 students in %s' % (the_class, subject),
header)
wb_sheet.merge_range(
row, col + 5, row, col + 8,
'Class %s Bottom 10 students in %s' % (the_class, subject),
header)
row += 1
wb_sheet.write_string(row, col, 'Rank', cell_bold)
wb_sheet.write_string(row, col + 5, 'Rank', cell_bold)
col += 1
wb_sheet.write_string(row, col, 'Student', cell_bold)
wb_sheet.write_string(row, col + 5, 'Student', cell_bold)
col += 1
wb_sheet.write_string(row, col, 'Section', cell_bold)
wb_sheet.write_string(row, col + 5, 'Section', cell_bold)
col += 1
wb_sheet.write_string(row, col, '%', cell_bold)
wb_sheet.write_string(row, col + 5, '%', cell_bold)
row += 1
col -= 3
rank = 1
top_10 = subject_df.nlargest(10, subject)
for idx in top_10.index:
wb_sheet.write_number(row, col, rank, cell_small)
rank += 1
col += 1
wb_sheet.write_string(row, col, top_10['Student'][idx],
cell_green)
col += 1
wb_sheet.write_string(row, col, top_10['Section'][idx],
cell_small)
col += 1
wb_sheet.write_number(row, col,
top_10[subject][idx] / max_marks,
perc_format)
row += 1
col -= 3
rank = 1
bottom_10 = subject_df.nsmallest(10, subject)
row -= 10
col += 5
for idx in bottom_10.index:
wb_sheet.write_number(row, col, rank, cell_small)
rank += 1
col += 1
wb_sheet.write_string(row, col, bottom_10['Student'][idx],
cell_red)
col += 1
wb_sheet.write_string(row, col, bottom_10['Section'][idx],
cell_small)
col += 1
wb_sheet.write_number(row, col,
bottom_10[subject][idx] / max_marks,
perc_format)
row += 1
col -= 3
# student distribution in percentage segments (> 85, 85-70, 70-50, < 50)
col = 0
row += 2
wb_sheet.merge_range(row, col, row + 1, col + 5,
'%s - Marks slab wise' % subject,
medium_font)
idx1 = 0
for section in sections:
if section == 'A':
row += 3
col = 1
if section == 'B':
col = 7
if section == 'C':
row += 15
col = 1
if section == 'D':
col = 7
slab_values = []
this_section_df = subject_df[subject_df['Section'] ==
section]
student_count = student_in_section[idx1]
idx1 += 1
above_85 = this_section_df[this_section_df[subject] >= (
max_marks * .85)][subject].count()
print('above_85=%d' % above_85)
slab_values.append(
round(float(above_85) / float(student_count), 2))
between_85_70 = \
this_section_df[(this_section_df[subject] < (max_marks * .85)) & (this_section_df[subject] >
(max_marks * .70))][
subject].count()
print('between_85_70=%d' % between_85_70)
slab_values.append(
round(float(between_85_70) / float(student_count), 2))
between_70_50 = \
this_section_df[(this_section_df[subject] < (max_marks * .7)) & (this_section_df[subject] >
(max_marks * .50))][
subject].count()
print('between_70_50=%d' % between_70_50)
slab_values.append(
round(float(between_70_50) / float(student_count), 2))
below_50 = this_section_df[this_section_df[subject] <= (
max_marks * .5)][subject].count()
print('below_50=%d' % below_50)
slab_values.append(
round(float(below_50) / float(student_count), 2))
wb_sheet.write_column(row, col, slabs)
categories = [a_sheet, row, col, row + 3, col]
col += 1
wb_sheet.write_column(row, col, slab_values, perc_format)
values = [a_sheet, row, col, row + 3, col]
chart = analytics.add_chart({'type': 'doughnut'})
chart.set_style(14)
chart.set_size({'x_scale': .8, 'y_scale': .85})
chart.set_legend({'position': 'right'})
chart.set_title({
'name':
'Class %s-%s %s - Slab Wise Marks ' %
(the_class, section, subject),
'name_font': {
'name': 'Calibri',
'color': colors[random.randrange(len(colors))],
'size': 12
}
})
chart.add_series({
'categories':
categories,
'values':
values,
'data_labels': {
'value': True,
'font': {
'name': 'Calibri',
'color': '#3E2723',
'size': 8
}
},
'points': [
{
'gradient': {
'colors': ['#1B5E20', '#A5D6A7']
}
},
{
'gradient': {
'colors': ['#01579B', '#039BE5']
}
},
{
'gradient': {
'colors': ['#9E9D24', '#D4E157']
}
},
{
'gradient': {
'colors': ['#FF5252', '#B71C1C']
}
},
],
})
if section in ['A', 'C']:
col = 1
else:
col = 6
wb_sheet.insert_chart(row, col, chart, {
'x_offset': 0,
'y_offset': 0
})
wb_sheet.set_h_pagebreaks([row + 15])
row += 19
col = 0
os.remove(local_path)
try:
analytics.close()
except Exception as e:
print(e.message, type(e))
response = HttpResponse(content_type='application/vnd.ms-excel')
response[
'Content-Disposition'] = 'attachment; filename=%s' % analytics_file_name
response.write(output.getvalue())
return response
def get(self, request, *args, **kwargs):
school_id = request.query_params.get('school_id')
school = School.objects.get(id=school_id)
standard = request.query_params.get('the_class')
the_class = Class.objects.get(school=school, standard=standard)
cbse_file_name = 'cbse.xlsx'
output = StringIO.StringIO(cbse_file_name)
cbse_file = xlsxwriter.Workbook(output)
fmt = format()
title_format = cbse_file.add_format(fmt.get_title())
title_format.set_border()
header = cbse_file.add_format(fmt.get_header())
big_font = cbse_file.add_format(fmt.get_large_font())
big_font.set_color('#33691E')
medium_font = cbse_file.add_format(fmt.get_medium_fong())
medium_font.set_color('#827717')
section_heading = cbse_file.add_format(fmt.get_section_heading())
section_heading.set_color('#4E342E')
cell_bold = cbse_file.add_format(fmt.get_cell_bold())
cell_bold.set_border()
cell_center = cbse_file.add_format(fmt.get_cell_center())
cell_center.set_border()
cell_left = cbse_file.add_format(fmt.get_cell_left())
cell_left.set_border()
cell_normal = cbse_file.add_format(fmt.get_cell_normal())
cell_normal.set_border()
sections = Section.objects.filter(school=school)
subject_list = [
'English', 'Hindi', 'Sanskrit', 'French', 'Mathematics', 'Science',
'Social Studies'
]
for section in sections:
students = Student.objects.filter(current_class=the_class,
current_section=section)
if students.count() > 0:
for a_subject in subject_list:
print('dealing with section: %s, subject: %s' %
(section, a_subject))
subject = Subject.objects.get(school=school,
subject_name=a_subject)
sheet_name = '%s-%s-%s' % (the_class, section, a_subject)
sheet = cbse_file.add_worksheet(sheet_name)
sheet.set_portrait()
sheet.set_paper(9)
sheet.fit_to_pages(1, 0)
sheet.set_footer(
'&L%s&R%s' %
('Subject Teacher', 'Class Teacher as checker'))
sheet.set_column('A:A', 5)
sheet.set_column('B:B', 10)
sheet.set_column('C:C', 20)
sheet.set_column('D:I', 6)
sheet.set_column('J:J', 8)
sheet.set_column('K:M', 6)
sheet.set_column('N:N', 8)
sheet.merge_range('A1:N1', school.school_name, header)
sheet.merge_range('A2:N2', 'INTERNAL ASSESSMENT RECORD',
header)
sheet.merge_range('A3:N3', 'PART I - SCHOLASTIC AREAS',
header)
row = 3
col = 0
sheet.merge_range(row, col, row, col + 1,
'Class: %s-%s' % (the_class, section),
cell_bold)
col += 11
sheet.merge_range(row, col, row, col + 2,
'Subject: %s' % a_subject, cell_bold)
row += 1
col = 0
sheet.merge_range(row, col, row, col + 1,
'Session: 2019-20', cell_bold)
col += 11
sheet.merge_range(row, col, row, col + 2,
'Code:____________', cell_bold)
row += 1
col = 0
sheet.merge_range(row, col, row + 3, col, 'S No',
cell_bold)
col += 1
sheet.merge_range(row, col, row + 3, col, 'CBSE Roll No',
cell_bold)
col += 1
sheet.merge_range(row, col, row + 3, col,
'Name of Student', cell_bold)
col += 1
sheet.merge_range(row, col, row, col + 6,
'Periodic Test(05)', cell_center)
col += 7
sheet.merge_range(row, col, row + 2, col, 'Note Book(5)',
cell_bold)
col += 1
sheet.merge_range(row, col, row + 2, col,
'Sub Enrich ACT (5)', cell_bold)
col += 1
sheet.merge_range(row, col, row + 2, col, 'Portfolio(5)',
cell_bold)
col += 1
sheet.merge_range(row, col, row + 2, col, 'Total (20)',
cell_bold)
row += 1
col = 3
sheet.merge_range(row, col, row, col + 2, 'Marks Obtained',
cell_center)
col += 3
sheet.merge_range(row, col, row, col + 2, 'Weightage',
cell_center)
col += 3
sheet.merge_range(row, col, row + 1, col,
'Average of Best 2 PTS', cell_bold)
row += 1
col = 3
sheet.write_string(row, col, 'PT - I', cell_bold)
col += 1
sheet.write_string(row, col, 'PT-2 (HY)', cell_bold)
col += 1
sheet.write_string(row, col, 'PT-3 (PB)', cell_bold)
col += 1
sheet.write_string(row, col, 'PT - I', cell_bold)
col += 1
sheet.write_string(row, col, 'PT-2 (HY)', cell_bold)
col += 1
sheet.write_string(row, col, 'PT-3 (PB)', cell_bold)
row += 1
col = 3
sheet.write_string(row, col, 'Out of 30', cell_bold)
col += 1
sheet.write_string(row, col, 'Out of 80', cell_bold)
col += 1
sheet.write_string(row, col, 'Out of 80', cell_bold)
col += 1
sheet.write_string(row, col, '5%', cell_bold)
col += 1
sheet.write_string(row, col, '5%', cell_bold)
col += 1
sheet.write_string(row, col, '5%', cell_bold)
col += 1
sheet.write_string(row, col, 'A=5', cell_bold)
col += 1
sheet.write_string(row, col, 'B=5', cell_bold)
col += 1
sheet.write_string(row, col, 'C=5', cell_bold)
col += 1
sheet.write_string(row, col, 'D=5', cell_bold)
col += 1
sheet.write_string(row, col, 'A + B + C + D =20',
cell_bold)
row = 9
col = 0
s_no = 1
exams = [
'PT I (IX - X)', 'Term-I (IX - X)',
'Pre Board (X - XII)'
]
for student in students:
print(
'dealing with student: %s of class: %s-%s subject: %s'
% (student, the_class, section, a_subject))
sheet.write_number(row, col, s_no, cell_normal)
s_no += 1
col += 1
try:
entry = CBSERollNo.objects.get(student=student)
cbse_roll_no = entry.cbse_roll_no
sheet.write_string(row, col, cbse_roll_no,
cell_normal)
except Exception as e:
print(
'exception 01022020-B from exam cbse.py %s %s'
% (e.message, type(e)))
print(
'could not retrieve the cbse roll no for %s' %
student)
col += 1
sheet.write_string(
row, col,
'%s %s' % (student.fist_name, student.last_name),
cell_left)
col += 1
index = 1
for an_exam in exams:
exam = Exam.objects.get(school=school,
title=an_exam)
try:
class_test = ClassTest.objects.get(
exam=exam,
subject=subject,
the_class=the_class,
section=section)
try:
test_result = TestResults.objects.get(
class_test=class_test, student=student)
marks = test_result.marks_obtained
sheet.write_number(row, col, marks,
cell_normal)
cell = xl_rowcol_to_cell(row, col)
if index == 1:
formula = '=ROUND(%s/6,0)' % cell
else:
formula = '=ROUND(%s/16,0)' % cell
sheet.write_formula(
row, col + 3, formula, cell_normal)
index += 1
if exam.exam_type == 'term':
term_results = TermTestResult.objects.get(
test_result=test_result)
note_book = term_results.note_book_marks
sheet.write_number(
row, col + 6, note_book,
cell_normal)
sub_enrich = term_results.sub_enrich_marks
sheet.write_number(
row, col + 7, sub_enrich,
cell_normal)
portfolio = term_results.multi_asses_marks
sheet.write_number(
row, col + 8, portfolio,
cell_normal)
except Exception as e:
print(
'exception 02022020-A from exam cbse.py %s %s'
% (e.message, type(e)))
print(
'for %s, could not retrieve the marks for exam %s, subject %s '
% (student, an_exam, subject))
col += 1
except Exception as e:
print(
'exception 02022020-B from exam cbse.py %s %s'
% (e.message, type(e)))
print(
'test for %s in exam %s was not scheduled'
% (subject, an_exam))
col += 1
# average of best of two PTs
start_cell = xl_rowcol_to_cell(row, col)
end_cell = xl_rowcol_to_cell(row, col + 2)
formula = '=ROUND(SUM(LARGE(%s:%s,{1,2}))/2,0)' % (
start_cell, end_cell)
sheet.write_formula(row, col + 3, formula, cell_normal)
# total out of 20 (average of best of two PTs + notebook + portfolio + sub_enrich
start_cell = xl_rowcol_to_cell(row, col + 3)
end_cell = xl_rowcol_to_cell(row, col + 6)
formula = '=SUM(%s:%s)' % (start_cell, end_cell)
sheet.write_formula(row, col + 7, formula, cell_normal)
row += 1
col = 0
cbse_file.close()
response = HttpResponse(content_type='application/vnd.ms-excel')
response[
'Content-Disposition'] = 'attachment; filename=%s' % cbse_file_name
response.write(output.getvalue())
return response