I wrote a script that generate student result sheet from a database. It generates the report sheet but the problem is it doesnt do what I want it to do quite well. I want it to generate a page per each row from the database. It does that too. What it doesn't do is on each page there are four tables. The first table sat beneath the Logo and the school informations(Address and what not). This table contains the student information(Name and so on.) The second table is the table that housed the scores in each subject. The third and fourth tables are sitting beside each other 20pt below the result sheet table.
I used the value of 15pt to represent each cell of the table heights and widths, to easily calculate the height of the table(cell_heights*num_table_rows). This will enable me to know the end of each table and know where to place the next table, after using the wrapOn function. I have tried what I think make sense to me and I am at a loss, hence, I seek the help of experts here.
Thanks as you consider helping out. Below is the code.
def print_result_sheet(self, event):
student_data_list = []
for child in self.panel2.GetChildren():
child.Destroy()
if self.search_exam_class_name_text.GetValue()!="" and self.search_exam_term_name_text.GetValue()!="" and self.search_exam_session_name_text.GetValue()!="":
result_data = []
result_data_list = []
student_info_table_list = []
result_table_list = []
psychomotor_table_list = []
psychomotor_data_list = []
affective_table_list = []
affective_data_list = []
student_data = []
index = 0
canvs = canvas.Canvas('result_sheet' + self.search_exam_class_name_text.GetValue()+ '.pdf', pagesize=letter)
# Fetcth both student personal record and examination records from two separate tables
student_rec_data = cursor.execute("select student.firstname, student.midlename, student.lastname, student.dob, student.gender, student.parent, student.parentphone, student.parentaddress, exam.exam_id, exam.scores, exam.total_scores, rank() over(order by exam.total_scores desc) as rank from student join exam on student.student_id=exam.exam_id where student.class = ? and exam.class=?", (self.search_exam_class_name_text.GetValue().lower(),self.search_exam_class_name_text.GetValue().lower())).fetchall()
for student_rec in student_rec_data:
firstname = student_rec['firstname']
midlename = student_rec['midlename']
lastname = student_rec['lastname']
parent = student_rec['parent']
gender = student_rec['gender']
dob = student_rec['dob']
parent_address = student_rec['parentaddress']
scores = student_rec['scores']
total_scores = student_rec['total_scores']
rank = student_rec['rank']
student_name = firstname + ' ' +midlename + ' ' + lastname
subject_dict = eval(scores)
idx = 0
#doc = SimpleDocTemplate('result_sheet' + self.search_exam_class_text.GetValue(), pagesize=portrait(letter))
#Create a dict containing alphabet as key for the highest and lowest score for the grade.
grade_dict = {'A':[70, 100], 'B':[60, 70], 'C':[50,60], 'D':[40, 50], 'E':[0, 40]}
student_data=[
['Student Name:', student_name, 'Number in Class:',len(subject_dict) ], ['Position:', rank, 'Class:',self.search_exam_class_name_text.GetValue() ],
['dob:', dob, 'Gender:', gender], ['Parent:', parent , 'Parent Address:', parent_address ]
]
result_data = [['Subject','Marks', '', '', '', '','grade', 'Teacher\'s Name', 'Remark'], ['',VerticalParagraph('Test'), VerticalParagraph('Test2.'), VerticalParagraph('C.A'),VerticalParagraph('Exam'), VerticalParagraph('Total'), VerticalParagraph('Grade')] ]
affective_data =[['AFFECTIVE DOMAIN', 'RATINGS'], ['','5', '4', '3', '2', '1'], ['Honesty', '', '', '','', ''], ['Punctuality', '', '', '','', ''],['Neatness', '', '', '','', ''],['Active in Class', '', '', '','', ''], ['Does Assignment', '', '', '','',''], ['Respects elders/Authority', '', '', '', '', '']]
psychomotor_data =[['PSYCHOMOTOR DOMAIN', 'RATINGS'], ['','5', '4', '3', '2', '1'],['Honesty', '', '', '','', ''], ['Punctuality', '', '', '','', ''],['Neatness', '', '', '','', ''], ['Active in Class', '', '', '','', ''], ['Does Assignment', '', '', '','',''], ['Respects elders/Authority', '', '', '', '', '']]
student_info_table_h = 4*15
student_info_table_w = 4*30
student_info_table_cell_w = student_info_table_w/4
student_info_table_cell_h = student_info_table_h/4
print(f"student info height is: {student_info_table_h}")
affective_domain_table_h = 4*15
affective_domain_table_w = 4*30
affective_domain_table_cell_w = student_info_table_w/4
affective_domain_table_cell_h = student_info_table_h/4
result_table_h = (len(subject_dict)+2)*15
result_table_w = 9*30
result_table_cell_w = result_table_w/9
result_table_cell_h = result_table_h/(len(subject_dict)+2)
print(subject_dict)
idx=0
for key, value in subject_dict.items():
#print(key, value)
scores = list(value)
print(scores)
#Compare the total score which is at index 4 of the value list of the score dictionary with the subject as key, with the grads score.
if scores[4]<=float(grade_dict['A'][1]) and scores[4]>=float(grade_dict['A'][0]):
grade = 'A'
elif scores[4]<=float(grade_dict['B'][1]) and scores[4]>=float(grade_dict['B'][0]):
grade = 'B'
elif scores[4]<=float(grade_dict['C'][1]) and scores[4]>=float(grade_dict['C'][0]):
grade = 'C'
elif scores[4]<=float(grade_dict['D'][1]) and scores[4]>=float(grade_dict['D'][0]):
grade = 'D'
elif scores[4]<=float(grade_dict['E'][1]) and scores[4]>=float(grade_dict['E'][0]):
grade = 'E'
#Convert dictionary values into list and the list items values to string
mark_data = list(map(str, list(map(float, scores))))
mark_data.insert(0, key)
#Insert the subject at index 0
#mark_data.insert(0,list(key))
print(mark_data)
mark_data.append(grade)
mark_data.append('Zakari Abdul')
mark_data.append('Very Good')
result_data.append(mark_data)
result_data.append(['Total', '', '', '', '', total_scores,],)
print(result_data)
#Append the above list student_dta to student data list.
affective_data_list.append(affective_data)
affective_table = Table(affective_data_list[index], colWidths=None)
affective_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.white), # Header row background color
('TEXTCOLOR', (0, 0), (-1, 0), colors.black), # Header text color
('ALIGN', (0, 0), (-1, -1), 'LEFT'),
('GRID', (0, 0), (-1, -1), 1, colors.black),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 8),
('BOTTOMPADDING', (0, 0), (-1, 0), 0),
('TOPPADDING', (0, 0), (-1, 0), 0),
('LEFTPADDING', (0, 0), (-1, 0), 0),
('RIGHTPADDING', (0, 0), (-1, 0), 0),
('SPAN', (1,0), (5, 0)),
('SPAN', (0,0), (0, 1)),
]))
#Add the above table to a list.
affective_table_list.append(affective_table)
psychomotor_data_list.append(psychomotor_data)
#result_data_list.append(result_data)
psychomotor_table = Table(psychomotor_data_list[index], colWidths=None)
psychomotor_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.white), # Header row background color
('TEXTCOLOR', (0, 0), (-1, 0), colors.black), # Header text color
('ALIGN', (0, 0), (-1, -1), 'LEFT'),
('GRID', (0, 0), (-1, -1), 1, colors.black),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 8),
('BOTTOMPADDING', (0, 0), (-1, 0), 0),
('TOPPADDING', (0, 0), (-1, 0), 0),
('LEFTPADDING', (0, 0), (-1, 0), 0),
('RIGHTPADDING', (0, 0), (-1, 0), 0),
('SPAN', (1,0), (5, 0)),
('SPAN', (0,0), (0, 1)),
]))
#Add the above table to a list.
psychomotor_table_list.append(psychomotor_table)
#Create a table to store student info and style the table.
student_data_list.append(student_data)
student_info_table = Table(student_data_list[index], colWidths=None, rowHeights=None)
student_info_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.white), # Header row background color
('TEXTCOLOR', (0, 0), (-1, 0), colors.black), # Header text color
('ALIGN', (0, 0), (-1, -1), 'LEFT'),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 8),
('BOTTOMPADDING', (0, 0), (-1, 0), 0),
('TOPPADDING', (0, 0), (-1, 0), 0),
('LEFTPADDING', (0, 0), (-1, 0), 0),
('RIGHTPADDING', (0, 0), (-1, 0), 0),
]))
#Add the above table to a list.
student_info_table_list.append(student_info_table)
#print(result_data)
#Create a table to store the scores in examination and style the
#result_data_list.append(result_data)
result_table = Table(result_data, colWidths=None, rowHeights=None)
row_heights = [None] + [result_table_cell_h] * 14
result_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.white), # Header row background color
('TEXTCOLOR', (0, 0), (-1, 0), colors.black), # Header text color
('ALIGN', (0, 0), (-1, -1), 'LEFT'),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 8),
#('GRID', (0,0), (-1,0)),
('BOTTOMPADDING', (0, 0), (-1, 0), 0),
('TOPPADDING', (0, 0), (-1, 0), 0),
('LEFTPADDING', (0, 0), (-1, 0), 0),
('RIGHTPADDING', (0, 0), (-1, 0), 0),
('BACKGROUND', (0, 1), (-1, -1), colors.white), # Data rows background color
('GRID', (0, 0), (-1, -1), 1, colors.black),
('SPAN', (1,0), (5,0),),
('SPAN', (0,0), (0,1),),
('SPAN', (6,0), (6,1),),
('SPAN', (7,0), (7,1),),
('SPAN', (8,0), (8,1),),
]))
result_table_list.append(result_table)
#chart_style = TableStyle([('ALIGN', (0, 0), (-1, -1), 'CENTER'),
#('VALIGN', (0, 0), (-1, -1), 'CENTER')])
school_logo = 'SE.jpg'
passport = 'SE.jpg'
school_name = "Madarasatul Hayatul Islam Academy"
school_address = "GUF-ANTI Itobe."
#school_phone = "Madarasatul Hayatul Islam Academy"
school_motto = "Knowledge is Power."
student_passport = Image('SE.jpg', width=100, height=100)
index+=1
#print(grade_dict)
i=0
search_exam_record=cursor.execute("select *, rank() over(order by total_scores desc) as rank from exam where class=? and term=? and session=? ", (self.search_exam_class_name_text.GetValue().lower(), self.search_exam_term_name_text.GetValue(), self.search_exam_session_name_text.GetValue())).fetchall()
print(len(search_exam_record))
for exam_record in search_exam_record:
student_name = exam_record['studentname']
studentid = exam_record['student_id']
subject_dict_str = exam_record['scores']
subject_dict = eval(subject_dict_str)
self.subject_list = list(subject_dict.keys())
#print(list(subject_dict[self.subject_list[count]])[4])
self.subject_score_list = list(subject_dict.values())
total_score= exam_record['total_scores']
self.student_id_subject_score[studentid] = subject_dict
rank = exam_record['rank']
rank = str(rank)
average_score = total_score/len(subject_dict)
average_score = round(average_score, 2)
if rank[-1]=='1':
rank = rank+'st'
elif rank[-1]=='2':
rank = rank+'nd'
elif rank[-1] =='3':
rank = rank+'rd'
else:
rank = rank+'th'
width, height = letter
canvs.drawInlineImage(school_logo, width/2-25, height-50, width=50, height=50)
canvs.drawCentredString(width/2, height-70, school_name)
canvs.drawCentredString(width/2, height-90, school_address)
canvs.drawCentredString(width/2, height-110, school_motto)
canvs.drawInlineImage(passport, width-110, height-120, width=0, height=50)
student_info_table_w, student_table_info_h = student_info_table_list[i].wrapOn(canvs, (width/2) + (width/4), height-200 )
result_table_w, result_table_hs = result_table_list[i].wrapOn(canvs, (width/2) + (width/4), height-150-result_table_h-20)
affective_table_w, affective_table_h = affective_table_list[i].wrapOn(canvs, (width/2)+(width/4), height-150-student_info_table_h-20-affective_domain_table_h )
psychomotor_table_w, psychomotor_table_h = psychomotor_table_list[i].wrapOn(canvs, (width/2)+(width/4), height-150-student_info_table_h-20-affective_domain_table_h )
spacing = 140
total_width = affective_table_w + psychomotor_table_w + spacing
student_table_height = height-150
result_table_height = student_table_height-student_info_table_h-20
affective_table_height = student_table_height-student_info_table_h-20-result_table_h
psychomotor_table_height = student_table_height-student_info_table_h-20-result_table_h
start_y = height-150
student_table_cell_h = student_table_info_h/4
result_table_cell_h = result_table_h /len(subject_dict)+2
x_offset = (width-total_width)/3
y_offset = (height-max(affective_table_h, psychomotor_table_h))/3.3
print(f"result table height is: {result_table_hs}")
student_info_table_list[i].drawOn(canvs, student_info_table_w/6, height-200)
result_table_list[i].drawOn(canvs, result_table_w/8, result_table_hs-20)
affective_table_list[i].drawOn(canvs, x_offset, affective_table_h-20)
psychomotor_table_list[i].drawOn(canvs, x_offset + affective_table_w + spacing , affective_table_h-20)
canvs.showPage()
i+=1
canvs.save()
os.startfile('result_sheet'+self.search_exam_class_name_text.GetValue() + '.pdf')
else:
wx.MessageBox("Please fill the required field")
```