def get_formatted_results(state, year, exam_category, username, password):
sess = init_sess(username, password)
results = get_results(sess, state=state, year=year, competition="All", exam_category=exam_category)
headers = ["StdNo", "Name (T)", "Name (E)", 'Exam', "Competition", 'Grade', 'Award']
data = []
for r in results:
data.append([r.std_no, r.name_t, r.name_e, r.exam_e, r.comp_t, r.grade, r.award])
return data, headers
def get_formatted_student_details(state, year, username, password):
sess = init_sess(username, password)
students = get_student_details(sess, state=state, year=year)
headers = [
"StdNo", "IndNo", "Name (T)", "Name (E)", 'DOB', "Division", 'Gender',
'Phone', 'Email'
]
data = []
for r in students:
data.append([
r.std_no, r.ind_no, r.name_t, r.name_e, r.dob, r.division_t,
r.gender, r.phone, r.email
])
return data, headers
def export_to_excel(xls_wb, state, year, exam_category, username, password):
sess = init_sess(username, password)
results = get_results(sess,
state=state,
year=year,
competition="All",
exam_category=exam_category)
ordered_results, division_comp_map, student_data_map = process_results_for_seating_number(
results)
student_data_map = get_updated_student_data_map(student_data_map)
wb = xlsxwriter.Workbook(xls_wb)
row_height = 25
row_title_height = 35
div_header_format = wb.add_format({
'font_name': "Bamini",
'align': 'left',
'bg_color': '#ebf0df',
'bold': 1,
'border': 2,
'font_size': 14,
'valign': 'vcenter'
})
bamini_cell_format = wb.add_format({
'font_name': "Bamini",
'align': 'left',
'font_size': 14,
'border': 1,
'valign': 'vcenter'
})
uc_cell_format = wb.add_format({
'font_name': "Calibri (Body)",
'align': 'left',
'font_size': 14,
'border': 1,
'valign': 'vcenter'
})
for division, division_prefix in DIVISION_ORDER:
ws = wb.add_worksheet(division)
ws.set_default_row(row_height)
comps = [
comp
for comp in sorted(division_comp_map[division],
key=lambda x: division_comp_map[division][x],
reverse=True)
]
comps_bamini = [unicode2bamini(comp) for comp in comps]
div_header = ["Seat POS", "khztu; ,y.", "KOg; ngau;", "KOg; ngau;"
] + comps_bamini
ws.write_row(0, 0, div_header, div_header_format)
ws.set_row(0, row_title_height)
division_data = ordered_results[division]
header_r = 1
if division_prefix == "G":
sorted_stds = sort_std_keys_for_division(division_data)
else:
sorted_stds = sorted(
division_data,
key=lambda x: int(student_data_map[x].seat_pos[-3:]))
for r, std_no in enumerate(sorted_stds):
std_data = student_data_map[std_no]
name_uc = std_data.name_t
seat_pos = std_data.seat_pos
std_comps = division_data[std_no]
name_bamini = std_data.name_bamini
c = 0
ws.write_string(header_r + r, c, std_no, uc_cell_format)
c += 1
ws.write_string(header_r + r, c, seat_pos, uc_cell_format)
c += 1
ws.write_string(header_r + r, c, name_uc, uc_cell_format)
c += 1
ws.write_string(header_r + r, c, name_bamini, bamini_cell_format)
for comp in comps:
c += 1
ws.write_rich_string(header_r + r, c, " ", uc_cell_format)
if comp in std_comps:
grade = std_comps[comp]
grade_e, grade_bamini = GRADE_INFO[grade]
if grade_e != "":
ws.write_rich_string(header_r + r, c, uc_cell_format,
grade_e, bamini_cell_format,
grade_bamini, uc_cell_format)
else:
ws.write_rich_string(header_r + r, c,
bamini_cell_format, grade_bamini,
uc_cell_format)
ws.set_column('A:A', 15)
ws.set_column('B:B', 45)
ws.set_column('C:C', 35)
ws.set_column('D:G', 45)
wb.close()
def export_to_docx(word_doc, state, year, exam_category, username, password):
sess = init_sess(username, password)
results = get_results(sess,
state=state,
year=year,
competition="All",
exam_category=exam_category)
ordered_results, division_comp_map, student_data_map = process_results_for_seating_number(
results)
student_data_map = get_updated_student_data_map(student_data_map)
template = os.path.join(MEDIA_ROOT, "book_template.docx")
document = Document(template)
for division, division_prefix in DIVISION_ORDER:
comps = [
comp
for comp in sorted(division_comp_map[division],
key=lambda x: division_comp_map[division][x],
reverse=True)
]
comps_bamini = [unicode2bamini(comp) for comp in comps]
# convert division unicode to bamini
division_bamini = unicode2bamini(division)
# division heading with state info
division_heading = "{:s} - {:s} - ghpRngw;Nwhh; gl;bay;".format(
division_bamini, STATE_TAMIL[state])
# Title
document.add_paragraph(division_heading, style='Section Title Tamil')
# add table for each division
table = document.add_table(rows=1,
cols=len(comps) + 2,
style='Table Grid')
hdr_cells = table.rows[0].cells
# write headers
# First column Aasana ilakkam
hdr_cells[0].text = "Mrd ,y."
hdr_cells[0].paragraphs[0].style = document.styles[
"Table Header Tamil"]
# first column = fullname in bamini
hdr_cells[1].text = "KOg;ngah;"
hdr_cells[1].paragraphs[0].style = document.styles[
"Table Header Tamil"]
# next columns with competitions details in bamini
for c, comp_bamini in enumerate(comps_bamini):
cell = hdr_cells[c + 2]
cell.text = comp_bamini
cell.paragraphs[0].style = document.styles["Table Header Tamil"]
division_data = ordered_results[division]
if division_prefix == "G":
sorted_stds = sort_std_keys_for_division(division_data)
else:
sorted_stds = sorted(
division_data,
key=lambda x: int(student_data_map[x].seat_pos[-3:]))
for std_no in sorted_stds:
std_data = student_data_map[std_no]
name_uc = std_data.name_t
seat_pos = std_data.seat_pos
name_bamini = std_data.name_bamini
std_comps = division_data[std_no]
row_cells = table.add_row().cells
row_cells[0].text = seat_pos
row_cells[0].paragraphs[0].style = document.styles[
"Table Cell Eng"]
row_cells[1].text = name_bamini
row_cells[1].paragraphs[0].style = document.styles[
"Table Cell Tamil Left"]
# loop through sored order on comps
for c, comp in enumerate(comps):
cell = row_cells[c + 2]
cell.text = ""
if comp in std_comps:
grade = std_comps[comp]
grade_e, grade_bamini = GRADE_INFO[grade]
if grade_e != " ":
eng = cell.paragraphs[0].add_run(grade_e + " ")
eng.font.name = "Calibri"
tamil = cell.paragraphs[0].add_run(grade_bamini)
tamil.font.name = "Bamini"
# document.add_page_break()
document.save(word_doc)
def export_to_excel(xls_wb,
state,
year,
exam_category,
username,
password,
seat_no_map=None):
sess = init_sess(username, password)
results = get_results(sess,
state=state,
year=year,
competition="All",
exam_category=exam_category)
if exam_category == "National":
trophy_rows, num_of_lines = get_trophy_data_rows_national(results)
else:
ordered_results, division_comp_map, student_data_map = process_results_for_seating_number(
results,
exam_category,
seperate_group_comps=False,
seat_no_map=seat_no_map)
trophy_rows, num_of_lines = get_trophy_data_by_rows(
ordered_results, student_data_map)
wb = xlsxwriter.Workbook(xls_wb)
ws = wb.add_worksheet("Trophy_template")
row_height = 20
ws.set_default_row(row_height)
# comps
trophy1_format = wb.add_format({
'font_name': "Arial",
'align': 'center',
'bg_color': 'yellow',
'bold': 1,
'right': 2,
'font_size': 14,
'valign': 'vcenter'
})
trophy2_format = wb.add_format({
'font_name': "Arial",
'align': 'center',
'bg_color': 'yellow',
'bold': 1,
'right': 2,
'font_size': 14,
'valign': 'vcenter'
})
trophy3_format = wb.add_format({
'font_name': "Arial",
'align': 'center',
'bg_color': 'yellow',
'bold': 1,
'right': 2,
'font_size': 14,
'valign': 'vcenter'
})
trophy4_format = wb.add_format({
'font_name': "Arial",
'align': 'center',
'bg_color': 'yellow',
'right': 2,
'font_size': 14,
'valign': 'vcenter'
})
trophy5_format = wb.add_format({
'font_name': "Arial",
'align': 'center',
'bg_color': 'yellow',
'right': 2,
'bottom': 2,
'font_size': 14,
'valign': 'vcenter'
})
note_format = wb.add_format({
'font_name': "Arial",
'align': 'center',
'bg_color': 'yellow',
'font_size': 14,
'valign': 'vcenter'
})
row_title_format = wb.add_format({
'font_name': "Arial",
'align': 'center',
'bg_color': '#ebf0df',
'bold': 1,
'bottom': 2,
'top': 2,
'left': 2,
'right': 2,
'font_size': 14,
'valign': 'vcenter'
})
row_format = wb.add_format({
'font_name': "Calibri (Body)",
'border': 1,
'align': 'left',
'font_size': 11,
'valign': 'vcenter'
})
# trophy headers
trophy1 = "Australian Society of Graduate Tamils (ASoGT)"
ws.merge_range('A1:D1', trophy1, trophy1_format)
trophy2 = "Tamil Competitions {} - {}".format(year, state)
ws.merge_range('A2:D2', trophy2, trophy2_format)
trophy3 = "Full Name (Eg: Asvetha Senthilkumaran) - Number (Eg: P001)"
ws.merge_range('A3:D3', trophy3, trophy3_format)
trophy4 = "Grade (Eg: First Prize) - Competition Name (Eg:Palar - Poetry) "
ws.merge_range('A4:D4', trophy4, trophy4_format)
trophy5 = "Grade (Eg: First Prize) - Competition Name (Eg:Palar - Poetry) "
ws.merge_range('A5:D5', trophy5, trophy5_format)
# write note
note = "Note: Black Print on Gold Plate"
ws.write("E4", note, note_format)
# write row headers
headers = ["Seat No", "Std No", "Full Nmae"] +\
["Line{:d}".format(line_no + 1) for line_no in range(num_of_lines)] +\
["Trophy Size"]
ws.write_row(5, 0, headers, row_title_format)
num_of_header_rows = 6
trophy_column = num_of_lines + 3
for i, trophy_row in enumerate(trophy_rows):
ws.write_row(num_of_header_rows + i, 0, trophy_row)
trophy_size = compute_trophy_size(trophy_row)
if exam_category == "National":
trophy_size = 1
else:
trophy_size = compute_trophy_size(trophy_row)
ws.write(num_of_header_rows + i, trophy_column, trophy_size)
ws.set_column(0, 1, 15)
ws.set_column(2, 2, 30)
ws.set_column(3, trophy_column - 1, 45)
ws.set_column(trophy_column, trophy_column, 15)
# for i, seating_no in enumerate(sorted(results, key=lambda x: int(x[-3:]))):
# result_row = results[seating_no]
# ws.write_row(num_of_header_rows + i, 0, result_row)
# trophy_size = compute_trophy_size(result_row)
# ws.write(num_of_header_rows + i, trophy_column, trophy_size)
# ws.set_column(0, 1, 15)
# ws.set_column(2, 2, 30)
# ws.set_column(3, trophy_column - 1, 45)
# ws.set_column(trophy_column, trophy_column, 15)
wb.close()
def export_to_excel(xls_wb, state, year, exam_category, username, password, seat_no_map=None):
sess = init_sess(username, password)
results = get_results(sess, state=state, year=year, competition="All", exam_category=exam_category)
if exam_category == "National":
sorted_results, student_data_map = sort_national_results(results)
else:
ordered_results, division_comp_map, student_data_map = process_results_for_seating_number(results,exam_category, seperate_group_comps=False, seat_no_map=seat_no_map)
filtered_results = [r for r in results if r.std_no in student_data_map]
sorted_results = sorted(filtered_results, key=lambda x: int(
student_data_map[x.std_no].seat_pos[-3:]))
wb = xlsxwriter.Workbook(xls_wb)
ws = wb.add_worksheet("Certificate_template")
# comps
row_title_format = wb.add_format({
'font_name': "Calibri (Body)",
'align': 'center',
'bg_color': '#ebf0df',
'bold': 1,
'bottom': 2,
'top': 2,
'left': 2,
'right': 2,
'font_size': 14,
'valign': 'vcenter'})
row_title_height = 20
tamil_cell_format = wb.add_format({
'font_name': "Bamini",
'border': 1,
'align': 'left',
'font_size': 14,
'valign': 'vcenter'})
eng_cell_format = wb.add_format({
'font_name': "Calibri (Body)",
'border': 1,
'align': 'left',
'font_size': 14,
'valign': 'vcenter'})
col_headers = REF_DATA.cert_cols_list
col_formats = REF_DATA.cert_cols_format.to_dict()
ws.write_row(0, 0, col_headers, row_title_format)
ws.set_row(0, row_title_height)
header_rows = 0
data_row = 1
col_max_widths = [8]*len(col_headers)
for result in sorted_results:
if result.exam_category not in exam_category:
continue
if exam_category == "National":
cert_state = exam_category
else:
cert_state = state
row_data = get_row_data(result, cert_state, student_data_map)
if row_data is not None:
for col, header in enumerate(col_headers):
col_format = col_formats[header]['FORMAT']
val = row_data[header]
if col_format == "ENG":
cell_format = eng_cell_format
font_width = 1.3
elif col_format == "TAMIL":
cell_format = tamil_cell_format
font_width = 1.3
ws.write_string(data_row, col, val, cell_format)
col_width = int(font_width * len(val))
if col_width > col_max_widths[col]:
col_max_widths[col] = col_width
data_row += 1
for col, val in enumerate(col_headers):
ws.set_column(col, col, col_max_widths[col])
wb.close()
def export_payment_summary(xls_wb, state, year, exam_category, username,
password):
sess = init_sess(username, password)
comp_data_raw = get_competition_details(sess,
state=state,
exam_category=exam_category,
year=year,
competition="All")
get_comp_info(comp_data_raw)
date_comp_sets_by_id, date_comp_sets_by_family = split__data(comp_data_raw)
wb = xlsxwriter.Workbook(xls_wb)
# left_image_path = os.path.join(MEDIA_ROOT, "left_src.jpg")
# right_image_path = os.path.join(MEDIA_ROOT, "right_src.jpg")
# comps
index_height = 170
index_ind_format = wb.add_format({
'font_name': "Impact",
'bold': 3,
'border': 5,
'align': 'center',
'font_size': 80,
'valign': 'vcenter'
})
index_name_format = wb.add_format({
'font_name': "Arial",
'bold': 2,
'border': 5,
'align': 'center',
'font_size': 12,
'valign': 'vcenter'
})
index_exam_format = wb.add_format({
'font_name': "Arial",
'text_wrap': True,
'border': 5,
'align': 'center',
'font_size': 10,
'valign': 'vcenter'
})
# setting up family list
family_header_format = wb.add_format({
'font_name': "Calibri (Body)",
'bg_color': '#ebf0df',
'bold': 1,
'bottom': 2,
'top': 2,
'left': 1,
'right': 1,
'align': 'center',
'font_size': 18,
'valign': 'vcenter'
})
family_row_format = wb.add_format({
'font_name': "Calibri (Body)",
'border': 1,
'font_size': 14,
'valign': 'vcenter',
'align': 'left'
})
family_count_format = wb.add_format({
'font_name': "Calibri (Body)",
'border': 1,
'bold': 2,
'font_size': 20,
'valign': 'vcenter',
'align': 'center'
})
family_ind_format = wb.add_format({
'font_name': "Calibri (Body)",
'bold': 2,
'border': 1,
'font_size': 20,
'valign': 'vcenter',
'align': 'center'
})
# setting up index tags
for date in date_comp_sets_by_id:
comp_sets_by_id = date_comp_sets_by_id[date]
comp_sets_by_family = date_comp_sets_by_family[date]
ws_ids = wb.add_worksheet("ids-" + date)
row = 0
ws_ids.set_column(0, 0, 41.75)
ws_ids.set_column(1, 1, 41.75)
aa = [{
'div': a.split("-")[0],
'id': a.split("-")[1]
} for a in comp_sets_by_id.keys()]
aa.sort(key=itemgetter('div'))
for div, items in groupby(aa, key=itemgetter('div')):
for item in sorted(items, key=lambda x: int(x['id'])):
ind_no = item["div"] + "-" + item['id']
exams = comp_sets_by_id[ind_no]['exams']
rows_per_id = len(exams) + 1
row_height = index_height / float(rows_per_id)
row += 1
ws_ids.merge_range('A{}:A{}'.format(row, row + len(exams)),
ind_no, index_ind_format)
ws_ids.write("B{}".format(row),
comp_sets_by_id[ind_no]['name'],
index_name_format)
ws_ids.set_row(row - 1, row_height)
for exam in exams:
row += 1
ws_ids.write("B{}".format(row), exam, index_exam_format)
ws_ids.set_row(row - 1, row_height)
# family lists
ws_family = wb.add_worksheet("familyList -" + date)
family_row_height = 25
ws_family.set_column(0, 0, 10)
ws_family.set_column(1, 1, 20)
ws_family.set_column(2, 3, 20)
ws_family.set_column(4, 4, 70)
ws_family.set_column(5, 5, 25)
ws_family.write_row(0, 0, [
"Family", "Index No", "First Name", "Last Name", "Exams",
"Comments"
], family_header_format)
ws_family.set_row(0, family_row_height + 10)
row = 2
for count, email_val in enumerate(
sorted(comp_sets_by_family.items(),
key=lambda x: x[1][x[1].keys()[0]]["lname"])):
email, val = email_val
row_family = row
for ind_no in sorted(val):
exams = val[ind_no]['exams']
lname = val[ind_no]['lname']
fname = val[ind_no]['fname']
row_ind = row
for exam in exams:
ws_family.write("E{}".format(row), exam, family_row_format)
ws_family.set_row(row - 1, family_row_height)
row += 1
if (row_ind < row - 1):
ws_family.merge_range('B{}:B{}'.format(row_ind, row - 1),
ind_no, family_ind_format)
ws_family.merge_range('C{}:C{}'.format(row_ind, row - 1),
fname, family_row_format)
ws_family.merge_range('D{}:D{}'.format(row_ind, row - 1),
lname, family_row_format)
ws_family.merge_range('F{}:F{}'.format(row_ind, row - 1),
"", family_row_format)
else:
ws_family.write('B{}'.format(row_ind), ind_no,
family_ind_format)
ws_family.write('C{}'.format(row_ind), fname,
family_row_format)
ws_family.write('D{}'.format(row_ind), lname,
family_row_format)
ws_family.write('F{}'.format(row_ind), "",
family_row_format)
if row_family < (row - 1):
ws_family.merge_range('A{}:A{}'.format(row_family, row - 1),
count, family_count_format)
else:
ws_family.write('A{}'.format(row_family), count,
family_count_format)
wb.close()