forked from ScoDoc/ScoDoc
341 lines
12 KiB
Python
341 lines
12 KiB
Python
# -*- mode: python -*-
|
|
# -*- coding: utf-8 -*-
|
|
|
|
##############################################################################
|
|
#
|
|
# Gestion scolarite IUT
|
|
#
|
|
# Copyright (c) 1999 - 2022 Emmanuel Viennet. All rights reserved.
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; either version 2 of the License, or
|
|
# (at your option) any later version.
|
|
#
|
|
# This program is distributed in the hope that it will be useful,
|
|
# but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
# GNU General Public License for more details.
|
|
#
|
|
# You should have received a copy of the GNU General Public License
|
|
# along with this program; if not, write to the Free Software
|
|
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
|
|
#
|
|
# Emmanuel Viennet emmanuel.viennet@viennet.net
|
|
#
|
|
##############################################################################
|
|
|
|
"""Feuille excel pour preparation des jurys
|
|
"""
|
|
import time
|
|
|
|
from openpyxl.styles.numbers import FORMAT_NUMBER_00
|
|
|
|
from flask import flash
|
|
from flask import request
|
|
from flask_login import current_user
|
|
|
|
from app.comp import res_sem
|
|
from app.comp.res_compat import NotesTableCompat
|
|
from app.models import FormSemestre, Identite, ScolarAutorisationInscription
|
|
from app.scodoc import sco_abs
|
|
from app.scodoc import sco_codes_parcours
|
|
from app.scodoc import sco_groups
|
|
from app.scodoc import sco_etud
|
|
from app.scodoc import sco_excel
|
|
from app.scodoc import sco_formsemestre
|
|
from app.scodoc import sco_cursus
|
|
from app.scodoc import sco_preferences
|
|
import app.scodoc.sco_utils as scu
|
|
import sco_version
|
|
|
|
|
|
def feuille_preparation_jury(formsemestre_id):
|
|
"Feuille excel pour preparation des jurys"
|
|
formsemestre = FormSemestre.query.get_or_404(formsemestre_id)
|
|
nt: NotesTableCompat = res_sem.load_formsemestre_results(formsemestre)
|
|
etuds: Identite = nt.get_inscrits(order_by="moy") # tri par moy gen
|
|
sem = sco_formsemestre.get_formsemestre(formsemestre_id)
|
|
|
|
etud_groups = sco_groups.formsemestre_get_etud_groupnames(formsemestre_id)
|
|
main_partition_id = sco_groups.formsemestre_get_main_partition(formsemestre_id)[
|
|
"partition_id"
|
|
]
|
|
|
|
prev_moy_ue = scu.DictDefault(defaultvalue={}) # ue_code_s : { etudid : moy ue }
|
|
prev_ue_acro = {} # ue_code_s : acronyme (à afficher)
|
|
prev_moy = {} # moyennes gen sem prec
|
|
moy_ue = scu.DictDefault(defaultvalue={}) # ue_acro : moyennes { etudid : moy ue }
|
|
ue_acro = {} # ue_code_s : acronyme (à afficher)
|
|
moy = {} # moyennes gen
|
|
moy_inter = {} # moyenne gen. sur les 2 derniers semestres
|
|
code = {} # decision existantes s'il y en a
|
|
autorisations = {}
|
|
prev_code = {} # decisions sem prec
|
|
assidu = {}
|
|
parcours = {} # etudid : parcours, sous la forme S1, S2, S2, S3
|
|
groupestd = {} # etudid : nom groupe principal
|
|
nbabs = {}
|
|
nbabsjust = {}
|
|
for etud in etuds:
|
|
Se = sco_cursus.get_situation_etud_cursus(
|
|
etud.to_dict_scodoc7(), formsemestre_id
|
|
)
|
|
if Se.prev:
|
|
formsemestre_prev = FormSemestre.query.get_or_404(
|
|
Se.prev["formsemestre_id"]
|
|
)
|
|
ntp: NotesTableCompat = res_sem.load_formsemestre_results(formsemestre_prev)
|
|
for ue in ntp.get_ues_stat_dict(filter_sport=True):
|
|
ue_status = ntp.get_etud_ue_status(etud.id, ue["ue_id"])
|
|
ue_code_s = (
|
|
ue["ue_code"] + "_%s" % ntp.sem["semestre_id"]
|
|
) # code indentifiant l'UE
|
|
prev_moy_ue[ue_code_s][etud.id] = ue_status["moy"] if ue_status else ""
|
|
prev_ue_acro[ue_code_s] = (ue["numero"], ue["acronyme"], ue["titre"])
|
|
prev_moy[etud.id] = ntp.get_etud_moy_gen(etud.id)
|
|
prev_decision = ntp.get_etud_decision_sem(etud.id)
|
|
if prev_decision:
|
|
prev_code[etud.id] = prev_decision["code"]
|
|
if prev_decision["compense_formsemestre_id"]:
|
|
prev_code[etud.id] += "+" # indique qu'il a servi a compenser
|
|
|
|
moy[etud.id] = nt.get_etud_moy_gen(etud.id)
|
|
for ue in nt.get_ues_stat_dict(filter_sport=True):
|
|
ue_status = nt.get_etud_ue_status(etud.id, ue["ue_id"])
|
|
ue_code_s = f'{ue["ue_code"]}_{nt.sem["semestre_id"]}'
|
|
moy_ue[ue_code_s][etud.id] = ue_status["moy"] if ue_status else ""
|
|
ue_acro[ue_code_s] = (ue["numero"], ue["acronyme"], ue["titre"])
|
|
|
|
if Se.prev:
|
|
try:
|
|
moy_inter[etud.id] = (moy[etud.id] + prev_moy[etud.id]) / 2.0
|
|
except (KeyError, TypeError):
|
|
pass
|
|
|
|
decision = nt.get_etud_decision_sem(etud.id)
|
|
if decision:
|
|
code[etud.id] = decision["code"]
|
|
if decision["compense_formsemestre_id"]:
|
|
code[etud.id] += "+" # indique qu'il a servi a compenser
|
|
assidu[etud.id] = {False: "Non", True: "Oui"}.get(decision["assidu"], "")
|
|
|
|
autorisations_etud = ScolarAutorisationInscription.query.filter_by(
|
|
etudid=etud.id, origin_formsemestre_id=formsemestre_id
|
|
).all()
|
|
autorisations[etud.id] = ", ".join(
|
|
[f"S{x.semestre_id}" for x in autorisations_etud]
|
|
)
|
|
# parcours:
|
|
parcours[etud.id] = Se.get_parcours_descr()
|
|
# groupe principal (td)
|
|
groupestd[etud.id] = ""
|
|
for s in Se.etud["sems"]:
|
|
if s["formsemestre_id"] == formsemestre_id:
|
|
groupestd[etud.id] = etud_groups.get(etud.id, {}).get(
|
|
main_partition_id, ""
|
|
)
|
|
# absences:
|
|
e_nbabs, e_nbabsjust = sco_abs.get_abs_count(etud.id, sem)
|
|
nbabs[etud.id] = e_nbabs
|
|
nbabsjust[etud.id] = e_nbabs - e_nbabsjust
|
|
|
|
# Codes des UE "semestre précédent":
|
|
ue_prev_codes = list(prev_moy_ue.keys())
|
|
ue_prev_codes.sort(
|
|
key=lambda x, prev_ue_acro=prev_ue_acro: prev_ue_acro[ # pylint: disable=undefined-variable
|
|
x
|
|
]
|
|
)
|
|
# Codes des UE "semestre courant":
|
|
ue_codes = list(moy_ue.keys())
|
|
ue_codes.sort(
|
|
key=lambda x, ue_acro=ue_acro: ue_acro[x] # pylint: disable=undefined-variable
|
|
)
|
|
|
|
sid = sem["semestre_id"]
|
|
sn = sp = ""
|
|
if sid >= 0:
|
|
sn = f"S{sid}"
|
|
if prev_moy: # si qq chose dans precedent
|
|
sp = f"S{sid - 1}"
|
|
|
|
sheet = sco_excel.ScoExcelSheet(sheet_name=f"Prepa Jury {sn}")
|
|
# génération des styles
|
|
style_bold = sco_excel.excel_make_style(size=10, bold=True)
|
|
style_center = sco_excel.excel_make_style(halign="center")
|
|
style_boldcenter = sco_excel.excel_make_style(bold=True, halign="center")
|
|
style_moy = sco_excel.excel_make_style(
|
|
bold=True, halign="center", bgcolor=sco_excel.COLORS.LIGHT_YELLOW
|
|
)
|
|
style_note = sco_excel.excel_make_style(
|
|
halign="right", number_format=FORMAT_NUMBER_00
|
|
)
|
|
style_note_bold = sco_excel.excel_make_style(
|
|
halign="right", bold=True, number_format="General"
|
|
)
|
|
|
|
# Première ligne
|
|
sheet.append_single_cell_row(
|
|
"Feuille préparation Jury %s" % scu.unescape_html(sem["titreannee"]), style_bold
|
|
)
|
|
sheet.append_blank_row()
|
|
|
|
# Ligne de titre
|
|
titles = ["Rang"]
|
|
if sco_preferences.get_preference("prepa_jury_nip"):
|
|
titles.append("NIP")
|
|
if sco_preferences.get_preference("prepa_jury_ine"):
|
|
titles.append("INE")
|
|
titles += [
|
|
"etudid",
|
|
"Civ.",
|
|
"Nom",
|
|
"Prénom",
|
|
"Naissance",
|
|
"Bac",
|
|
"Spe",
|
|
"Rg Adm",
|
|
"Parcours",
|
|
"Groupe",
|
|
]
|
|
if prev_moy: # si qq chose dans precedent
|
|
titles += [prev_ue_acro[x][1] for x in ue_prev_codes] + [
|
|
f"Moy {sp}",
|
|
f"Décision {sp}",
|
|
]
|
|
titles += [ue_acro[x][1] for x in ue_codes] + [f"Moy {sn}"]
|
|
if moy_inter:
|
|
titles += [f"Moy {sp}-{sn}"]
|
|
titles += ["Abs", "Abs Injust."]
|
|
if code:
|
|
titles.append("Proposit. {sn}")
|
|
if autorisations:
|
|
titles.append("Autorisations")
|
|
# titles.append('Assidu')
|
|
sheet.append_row(sheet.make_row(titles, style_boldcenter))
|
|
# if prev_moy:
|
|
# tit_prev_moy = "Moy " + sp
|
|
# # col_prev_moy = titles.index(tit_prev_moy)
|
|
# tit_moy = "Moy " + sn
|
|
# col_moy = titles.index(tit_moy)
|
|
# col_abs = titles.index("Abs")
|
|
|
|
def fmt(x):
|
|
"reduit les notes a deux chiffres"
|
|
x = scu.fmt_note(x, keep_numeric=False)
|
|
try:
|
|
return float(x)
|
|
except:
|
|
return x
|
|
|
|
i = 1 # numero etudiant
|
|
for etud in etuds:
|
|
cells = []
|
|
cells.append(sheet.make_cell(str(i)))
|
|
if sco_preferences.get_preference("prepa_jury_nip"):
|
|
cells.append(sheet.make_cell(etud.code_nip))
|
|
if sco_preferences.get_preference("prepa_jury_ine"):
|
|
cells.append(sheet.make_cell(etud.code_ine))
|
|
admission = etud.admission.first()
|
|
cells += sheet.make_row(
|
|
[
|
|
etud.id,
|
|
etud.civilite_str,
|
|
sco_etud.format_nom(etud.nom),
|
|
sco_etud.format_prenom(etud.prenom),
|
|
etud.date_naissance,
|
|
admission.bac,
|
|
admission.specialite,
|
|
admission.classement,
|
|
parcours[etud.id],
|
|
groupestd[etud.id],
|
|
]
|
|
)
|
|
co = len(cells)
|
|
if prev_moy:
|
|
for ue_acro in ue_prev_codes:
|
|
cells.append(
|
|
sheet.make_cell(
|
|
fmt(prev_moy_ue.get(ue_acro, {}).get(etud.id, "")), style_note
|
|
)
|
|
)
|
|
co += 1
|
|
cells.append(
|
|
sheet.make_cell(fmt(prev_moy.get(etud.id, "")), style_bold)
|
|
) # moy gen prev
|
|
cells.append(
|
|
sheet.make_cell(fmt(prev_code.get(etud.id, "")), style_moy)
|
|
) # decision prev
|
|
co += 2
|
|
|
|
for ue_acro in ue_codes:
|
|
cells.append(
|
|
sheet.make_cell(
|
|
fmt(moy_ue.get(ue_acro, {}).get(etud.id, "")), style_note
|
|
)
|
|
)
|
|
co += 1
|
|
cells.append(
|
|
sheet.make_cell(fmt(moy.get(etud.id, "")), style_note_bold)
|
|
) # moy gen
|
|
co += 1
|
|
if moy_inter:
|
|
cells.append(sheet.make_cell(fmt(moy_inter.get(etud.id, "")), style_note))
|
|
cells.append(sheet.make_cell(str(nbabs.get(etud.id, "")), style_center))
|
|
cells.append(sheet.make_cell(str(nbabsjust.get(etud.id, "")), style_center))
|
|
if code:
|
|
cells.append(sheet.make_cell(code.get(etud.id, ""), style_moy))
|
|
cells.append(sheet.make_cell(autorisations.get(etud.id, ""), style_moy))
|
|
# l.append(assidu.get(etud.id, ''))
|
|
sheet.append_row(cells)
|
|
i += 1
|
|
#
|
|
sheet.append_blank_row()
|
|
# Explications des codes
|
|
codes = list(sco_codes_parcours.CODES_EXPL.keys())
|
|
codes.sort()
|
|
sheet.append_single_cell_row("Explication des codes")
|
|
for code in codes:
|
|
sheet.append_row(
|
|
sheet.make_row(["", "", "", code, sco_codes_parcours.CODES_EXPL[code]])
|
|
)
|
|
sheet.append_row(
|
|
sheet.make_row(
|
|
[
|
|
"",
|
|
"",
|
|
"",
|
|
"ADM+",
|
|
"indique que le semestre a déjà servi à en compenser un autre",
|
|
]
|
|
)
|
|
)
|
|
# UE : Correspondances acronyme et titre complet
|
|
sheet.append_blank_row()
|
|
sheet.append_single_cell_row("Titre des UE")
|
|
if prev_moy:
|
|
for ue in ntp.get_ues_stat_dict(filter_sport=True):
|
|
sheet.append_row(sheet.make_row(["", "", "", ue["acronyme"], ue["titre"]]))
|
|
for ue in nt.get_ues_stat_dict(filter_sport=True):
|
|
sheet.append_row(sheet.make_row(["", "", "", ue["acronyme"], ue["titre"]]))
|
|
#
|
|
sheet.append_blank_row()
|
|
sheet.append_single_cell_row(
|
|
"Préparé par %s le %s sur %s pour %s"
|
|
% (
|
|
sco_version.SCONAME,
|
|
time.strftime("%d/%m/%Y"),
|
|
request.url_root,
|
|
current_user,
|
|
)
|
|
)
|
|
xls = sheet.generate()
|
|
flash("Feuille préparation jury générée")
|
|
return scu.send_file(
|
|
xls,
|
|
f"PrepaJury{sn}",
|
|
scu.XLSX_SUFFIX,
|
|
mime=scu.XLSX_MIMETYPE,
|
|
)
|