def create_xls_administrative_data(user, education_group_years_qs, filters, order_data):
# Make select_related/prefetch_related in order to have low DB HIT
education_group_years = education_group_years_qs.filter(
education_group_type__category=education_group_categories.TRAINING
).select_related(
'education_group_type',
'academic_year',
).prefetch_related(
Prefetch(
'education_group__mandate_set',
queryset=Mandate.objects.prefetch_related('mandatary_set')
),
Prefetch(
'offeryearcalendar_set',
queryset=OfferYearCalendar.objects.select_related('academic_calendar__sessionexamcalendar')
)
)
found_education_groups = ordering_data(education_group_years, order_data)
working_sheets_data = prepare_xls_content_administrative(found_education_groups)
header_titles = _get_translated_header_titles()
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION_ADMINISTRATIVE,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME_ADMINISTRATIVE,
xls_build.HEADER_TITLES: header_titles,
xls_build.WS_TITLE: WORKSHEET_TITLE_ADMINISTRATIVE
}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def generate_xls_teaching_material(user, learning_units):
""" Generate a XLS file with all filtered learning_units where the course material is required """
titles = [
str(_('code')).title(),
str(_('Title')),
str(_('Req. Entity')).title(),
str(_('bibliography')).title(),
str(_('teaching materials')).title(),
str(_('online resources')).title(),
]
rows = [lu for lu in learning_units if lu.teachingmaterial_set.filter(mandatory=True)]
file_parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.FILENAME: XLS_FILENAME,
xls_build.USER: get_name_or_username(user),
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: _("Teaching material"),
xls_build.STYLED_CELLS: {
Style(alignment=Alignment(wrap_text=True)): _get_text_wrapped_cells(len(rows)),
}
}
working_sheets_data = _filter_required_teaching_material(learning_units)
return xls_build.generate_xls(prepare_xls_parameters_list(working_sheets_data, file_parameters))
def create_xls_administrative_data(user, education_group_years_qs, filters, order_data):
# Make select_related/prefetch_related in order to have low DB HIT
education_group_years = education_group_years_qs.filter(
education_group_type__category=education_group_categories.TRAINING
).select_related(
'education_group_type',
'academic_year',
).prefetch_related(
Prefetch(
'education_group__mandate_set',
queryset=Mandate.objects.prefetch_related('mandatary_set')
),
Prefetch(
'offeryearcalendar_set',
queryset=OfferYearCalendar.objects.select_related('academic_calendar__sessionexamcalendar')
)
)
found_education_groups = ordering_data(education_group_years, order_data)
working_sheets_data = prepare_xls_content_administrative(found_education_groups)
header_titles = _get_translated_header_titles()
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION_ADMINISTRATIVE,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME_ADMINISTRATIVE,
xls_build.HEADER_TITLES: header_titles,
xls_build.WS_TITLE: WORKSHEET_TITLE_ADMINISTRATIVE
}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def create_xls_attributions(user, found_learning_units, filters):
titles = learning_unit_titles_part1() + learning_unit_titles_part2() + [str(_('Tutor')),
"{} ({})".format(str(_('Tutor')),
str(_('email'))),
str(_('Function')),
str(_('Substitute')),
str(_('Beg. of attribution')),
str(_('Attribution duration')),
str(_('Attrib. vol1')),
str(_('Attrib. vol2')),
]
xls_data = prepare_xls_content_with_attributions(found_learning_units, len(titles))
working_sheets_data = xls_data.get('data')
cells_with_top_border = xls_data.get('cells_with_top_border')
cells_with_white_font = xls_data.get('cells_with_white_font')
parameters = {xls_build.DESCRIPTION: _('Learning units list with attributions'),
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: WORKSHEET_TITLE,
xls_build.STYLED_CELLS: {xls_build.STYLE_BORDER_TOP: cells_with_top_border,
Style(font=Font(color=Color('00FFFFFF')),): cells_with_white_font},
xls_build.COLORED_ROWS: {Style(font=BOLD_FONT): [0]}
}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def generate_xls_teaching_material(user, learning_units):
""" Generate a XLS file with all filtered learning_units where the course material is required """
titles = [
str(_('code')).title(),
str(_('Title')),
str(_('Req. Entity')).title(),
str(_('bibliography')).title(),
str(_('teaching materials')).title(),
"{} - {}".format(_('online resources'), settings.LANGUAGE_CODE_FR).title(),
"{} - {}".format(_('online resources'), settings.LANGUAGE_CODE_EN).title(),
]
rows = [lu for lu in learning_units if lu.teachingmaterial_set.filter(mandatory=True)]
file_parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.FILENAME: XLS_FILENAME,
xls_build.USER: get_name_or_username(user),
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: _("Teaching material"),
xls_build.STYLED_CELLS: {
Style(alignment=Alignment(wrap_text=True)): _get_text_wrapped_cells(len(rows)),
}
}
learning_units = _annotate_with_pedagogy_info(learning_units)
working_sheets_data = _filter_required_teaching_material(learning_units)
return xls_build.generate_xls(prepare_xls_parameters_list(working_sheets_data, file_parameters))
def create_xls_comparison(user, learning_unit_years, filters, academic_yr_comparison):
working_sheets_data = []
cells_modified_with_green_font = []
cells_with_top_border = []
if learning_unit_years:
luys_for_2_years = _get_learning_unit_yrs_on_2_different_years(academic_yr_comparison, learning_unit_years)
data = prepare_xls_content(luys_for_2_years)
working_sheets_data = data.get('data')
cells_modified_with_green_font = data.get(CELLS_MODIFIED_NO_BORDER)
cells_with_top_border = data.get(CELLS_TOP_BORDER)
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: learning_unit_titles(),
xls_build.WS_TITLE: WORKSHEET_TITLE,
}
dict_styled_cells = {}
if cells_modified_with_green_font:
dict_styled_cells[xls_build.STYLE_MODIFIED] = cells_modified_with_green_font
if cells_with_top_border:
dict_styled_cells[xls_build.STYLE_BORDER_TOP] = cells_with_top_border
if dict_styled_cells:
parameters[xls_build.STYLED_CELLS] = dict_styled_cells
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def create_xls_comparison(user, learning_unit_years, filters,
academic_yr_comparison):
working_sheets_data = []
cells_modified_with_green_font = []
cells_with_top_border = []
if learning_unit_years:
luys_for_2_years = _get_learning_unit_yrs_on_2_different_years(
academic_yr_comparison, learning_unit_years)
data = prepare_xls_content(luys_for_2_years)
working_sheets_data = data.get('data')
cells_modified_with_green_font = data.get(CELLS_MODIFIED_NO_BORDER)
cells_with_top_border = data.get(CELLS_TOP_BORDER)
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: LEARNING_UNIT_TITLES,
xls_build.WS_TITLE: WORKSHEET_TITLE,
}
dict_styled_cells = {}
if cells_modified_with_green_font:
dict_styled_cells.update(
{xls_build.STYLE_MODIFIED: cells_modified_with_green_font})
if cells_with_top_border:
dict_styled_cells.update(
{xls_build.STYLE_BORDER_TOP: cells_with_top_border})
if dict_styled_cells:
parameters.update({xls_build.STYLED_CELLS: dict_styled_cells})
return xls_build.generate_xls(
xls_build.prepare_xls_parameters_list(working_sheets_data, parameters),
filters)
def create_xls_proposal_comparison(user, learning_units_with_proposal,
filters):
data = prepare_xls_content_for_comparison(learning_units_with_proposal)
working_sheets_data = data.get('data')
cells_modified_with_green_font = data.get(CELLS_MODIFIED_NO_BORDER)
cells_with_top_border = data.get(CELLS_TOP_BORDER)
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION_COMPARISON,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_COMPARISON_FILENAME,
xls_build.HEADER_TITLES: COMPARISON_PROPOSAL_TITLES,
xls_build.WS_TITLE: COMPARISON_WORKSHEET_TITLE,
}
dict_styled_cells = {}
if cells_modified_with_green_font:
dict_styled_cells[
xls_build.STYLE_MODIFIED] = cells_modified_with_green_font
if cells_with_top_border:
dict_styled_cells[xls_build.STYLE_BORDER_TOP] = cells_with_top_border
if dict_styled_cells:
parameters[xls_build.STYLED_CELLS] = dict_styled_cells
return xls_build.generate_xls(
xls_build.prepare_xls_parameters_list(working_sheets_data, parameters),
filters)
def create_xls_educational_information_and_specifications(
user, learning_units, request):
titles = _get_titles()
working_sheet_data = prepare_xls_educational_information_and_specifications(
learning_units, request)
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: WORKSHEET_TITLE,
xls_build.ROW_HEIGHT: {
'height': 30,
'start': 2,
'stop': (len(learning_units)) + 1
},
xls_build.STYLED_CELLS: {
WRAP_TEXT_STYLE:
_get_wrapped_cells_educational_information_and_specifications(
learning_units, len(titles))
}
}
return xls_build.generate_xls(
xls_build.prepare_xls_parameters_list(working_sheet_data, parameters))
def configure_parameters(user):
return {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: PROPOSAL_TITLES,
xls_build.WS_TITLE: WORKSHEET_TITLE
}
def create_xls(user, found_learning_units, filters):
working_sheets_data = prepare_xls_content(found_learning_units)
parameters = {xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: LEARNING_UNIT_TITLES,
xls_build.WS_TITLE: WORKSHEET_TITLE}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def create_xls(user, found_education_groups_param, filters, order_data):
found_education_groups = ordering_data(found_education_groups_param, order_data)
working_sheets_data = prepare_xls_content(found_education_groups)
parameters = {xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: EDUCATION_GROUP_TITLES,
xls_build.WS_TITLE: WORKSHEET_TITLE}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def prepare_xls_parameters_list(user, working_sheets_data):
return {xls_build.LIST_DESCRIPTION_KEY: _(XLS_DESCRIPTION),
xls_build.FILENAME_KEY: _(XLS_FILENAME),
xls_build.USER_KEY: get_name_or_username(user),
xls_build.WORKSHEETS_DATA:
[{xls_build.CONTENT_KEY: working_sheets_data,
xls_build.HEADER_TITLES_KEY: _prepare_titles(),
xls_build.WORKSHEET_TITLE_KEY: _(WORKSHEET_TITLE),
}
]}
def prepare_xls_parameters_list(user, working_sheets_data):
return {xls_build.LIST_DESCRIPTION_KEY: _(XLS_DESCRIPTION),
xls_build.FILENAME_KEY: _(XLS_FILENAME),
xls_build.USER_KEY: get_name_or_username(user),
xls_build.WORKSHEETS_DATA:
[{xls_build.CONTENT_KEY: working_sheets_data,
xls_build.HEADER_TITLES_KEY: PROPOSAL_TITLES,
xls_build.WORKSHEET_TITLE_KEY: _(WORKSHEET_TITLE),
}
]}
def create_xls(user, found_learning_units, filters):
titles = learning_unit_titles_part1() + learning_unit_titles_part2()
working_sheets_data = prepare_xls_content(found_learning_units)
parameters = {xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: WORKSHEET_TITLE}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def create_xls(user, found_education_groups_param, filters, order_data):
found_education_groups = ordering_data(found_education_groups_param, order_data)
working_sheets_data = prepare_xls_content(found_education_groups)
parameters = {xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: EDUCATION_GROUP_TITLES,
xls_build.WS_TITLE: WORKSHEET_TITLE}
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def create_xls(user):
working_sheets_data = _prepare_xls_content(Prospect.objects.all())
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: _get_titles(),
xls_build.WS_TITLE: WORKSHEET_TITLE
}
return xls_build.generate_xls(
xls_build.prepare_xls_parameters_list(working_sheets_data, parameters))
def create_xls_registration(user, registrations_list, form):
working_sheets_data = prepare_xls_content(registrations_list)
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: _get_titles(),
xls_build.WS_TITLE: WORKSHEET_TITLE
}
return xls_build.generate_xls(
xls_build.prepare_xls_parameters_list(working_sheets_data, parameters),
form_filters(form))
def _get_parameters_configurable_list(learning_units, titles, user):
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: WORKSHEET_TITLE,
xls_build.STYLED_CELLS: {
WRAP_TEXT_STYLE:
_get_wrapped_cells(learning_units,
_get_col_letter(titles, HEADER_PROGRAMS),
_get_col_letter(titles, HEADER_TEACHERS))
},
xls_build.COLORED_ROWS: _get_colored_rows(learning_units),
}
return parameters
def _get_parameters_configurable_list(learning_units, titles, user):
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: WORKSHEET_TITLE,
xls_build.STYLED_CELLS: {
WRAP_TEXT_STYLE: _get_wrapped_cells(
learning_units,
_get_col_letter(titles, HEADER_PROGRAMS),
_get_col_letter(titles, HEADER_TEACHERS)
)
},
xls_build.COLORED_ROWS: _get_colored_rows(learning_units),
}
return parameters
def create_xls_proposal_comparison(user, learning_units_with_proposal, filters):
data = prepare_xls_content_for_comparison(learning_units_with_proposal)
working_sheets_data = data.get('data')
cells_modified_with_green_font = data.get(CELLS_MODIFIED_NO_BORDER)
cells_with_top_border = data.get(CELLS_TOP_BORDER)
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION_COMPARISON,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_COMPARISON_FILENAME,
xls_build.HEADER_TITLES: COMPARISON_PROPOSAL_TITLES,
xls_build.WS_TITLE: COMPARISON_WORKSHEET_TITLE,
}
dict_styled_cells = {}
if cells_modified_with_green_font:
dict_styled_cells[xls_build.STYLE_MODIFIED] = cells_modified_with_green_font
if cells_with_top_border:
dict_styled_cells[xls_build.STYLE_BORDER_TOP] = cells_with_top_border
if dict_styled_cells:
parameters[xls_build.STYLED_CELLS] = dict_styled_cells
return xls_build.generate_xls(xls_build.prepare_xls_parameters_list(working_sheets_data, parameters), filters)
def generate_xls_teaching_material(user, learning_units):
""" Generate a XLS file with all filtered learning_units where the course material is required """
titles = [
str(_('code')).title(),
str(_('title')).title(),
str(_('requirement_entity_small')).title(),
str(_('bibliography')).title(),
str(_('teaching materials')).title(),
str(_('online resources')).title(),
]
file_parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.FILENAME: XLS_FILENAME,
xls_build.USER: get_name_or_username(user),
xls_build.HEADER_TITLES: titles,
xls_build.WS_TITLE: _("Teaching material"),
}
working_sheets_data = _filter_required_teaching_material(learning_units)
return xls_build.generate_xls(
prepare_xls_parameters_list(working_sheets_data, file_parameters))
def configure_parameters(user):
return {xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
xls_build.HEADER_TITLES: PROPOSAL_TITLES,
xls_build.WS_TITLE: WORKSHEET_TITLE}
