o
    Dj{n                     @   s  d dl mZmZ d dlmZmZ d dlmZ d dlZd dl	m
Z
mZmZmZmZmZ d dlmZ d dlmZmZ d dlmZ d	d
lmZ d dlmZ d	dlmZ g dZdd Zdd Zdd Z dd Z!G dd dej"Z#G dd dej"Z$G dd dej%Z&G dd dej"Z'G dd dej"Z(G d d! d!ej"Z)G d"d# d#ej"Z*eee$d$d%d& Z+eee$d$d'd( Z,eeed$d)d* Z-eeed$d+d, Z.eee)d$d-d, Z.G d.d/ d/ej"Z/G d0d1 d1ej%Z0G d2d3 d3ej"Z1G d4d5 d5ej%Z2G d6d7 d7ej"Z3dS )8    )models
connection)post_delete	post_save)receiverN)
MEDIA_ROOTIMAGEMAGICKBDDHEURE_DEBUT	HEURE_FIN
INTERVALLE)Image)SumFchoice   )ContentTypeRestrictedFileField)RichTextField)Eleve)lundimardimercredijeudivendredisamedidimanchec                 C   s   d dd t| D S )N c                 s   s    | ]}t d V  qdS )>ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789Nr   .0i r"   ./home/reyt/e-colle-new/accueil/models/autre.py	<genexpr>   s    z"texte_aleatoire.<locals>.<genexpr>)joinrange)tailler"   r"   r#   texte_aleatoire   s   r(   c                    s   | j   fdd|  D S )z8Renvoie les lignes du curseur sous forme de dictionnairec                    s$   g | ]}t td d  D |qS )c                 S   s   g | ]}|d  qS )r   r"   )r    colr"   r"   r#   
<listcomp>   s    z+dictfetchall.<locals>.<listcomp>.<listcomp>)dictzip)r    rowdescr"   r#   r*      s    z dictfetchall.<locals>.<listcomp>)descriptionfetchall)cursorr"   r.   r#   dictfetchall   s   
r3   c                 C   P   t dkst dkst dkrd| |S t dkrd| |S t dkr&d| |S d	S )
u   Renvoie une chaîne de caractères correspondant à la syntaxe SQL 
    qui permet d'ajouter un objet de type date, dateSQL, avec un nombre 
    de jours, jourSQL
postgresqlpostgresql_psycopg2oraclez{}+{}mysqlz{} + INTERVAL {} DAYsqlite3zdate({},'+{} days')r   r	   format)dateSQLjourSQLr"   r"   r#   date_plus_jour   s   r>   c                 C   r4   )
u   Renvoie une chaîne de caractères correspondant à la syntaxe SQL
    qui permet de faire la différence date1-date2 en nombre de joursr5   r6   r7   z{}-{}r8   zDATEDIFF({},{})r9   zjulianday({})-julianday({})r   r:   )date1date2r"   r"   r#   date_moins_date*   s   rA   c                   @   s   e Zd Zdd eeeeD Zeg dZ	e
je	ddZe
jeddZe
jdddd	Ze
jd
de
jdZG dd dZdd ZdS )Creneauc                 C   s$   g | ]}|d  |d |d fqS )z	{}h{:02d}<   )r;   r   r"   r"   r#   r*   9   s   $ zCreneau.<listcomp>)r   r   r   r   r   r   r   )choicesdefaulti     T)
max_lengthnullblankClasseclassecreneaurelated_name	on_deletec                   @   s   e Zd Zg dZdS )zCreneau.Meta)jourheuresallepkN)__name__
__module____qualname__orderingr"   r"   r"   r#   MetaB       rW   c                 C   s&   d | jjt| j | jd | jd S )Nz{}/{}/{}h{:02d}rC   )r;   classenomsemainerO   rP   selfr"   r"   r#   __str__E   s   &zCreneau.__str__N)rS   rT   rU   r&   r
   r   r   LISTE_HEURE	enumerate
LISTE_JOURr   PositiveSmallIntegerFieldrO   rP   	CharFieldrQ   
ForeignKeyPROTECTrY   rW   r^   r"   r"   r"   r#   rB   8   s    rB   c                   @   s   e Zd Zdd ZejddddZejddejd	Z	ejd
dejd	Z
ejddZejddddZededddgddZdd ZdS )	Programmec                 C   s   dt d d S )Nzprogramme/progrF   z.pdf)r(   instancefilenamer"   r"   r#   update_nameI   s   zProgramme.update_nameSemainez
Semaine(s)F)verbose_namerI   rJ   classeprogrammerL   Matierematiereprogramme2   rG   u   DétailsT)rl   rH   rI   zFichier(pdf)application/pdf@KL rl   	upload_torH   rI   content_typesmax_upload_sizec                 C   s
   | j  S N)titretitler\   r"   r"   r#   r^   R   s   
zProgramme.__str__N)rS   rT   rU   rj   r   ManyToManyFieldr[   rd   re   rY   matiererc   ry   	TextFielddetailr   fichierr^   r"   r"   r"   r#   rf   H   s    rf   c                   @      e Zd Zdd ZdS )FerieManagerc           	   	   C   sd   d tdd}t }||||j|j| ||f | }W d    |S 1 s+w   Y  |S )NzSELECT COUNT(jf.id)             FROM accueil_semaine s             INNER JOIN accueil_jourferie jf             ON {} = %s             WHERE s.numero >= %s AND s.numero < %s AND s.numero %% %s = %szjf.datezs.lundi)r;   rA   r   r2   executenumerofetchone)	r]   rO   r[   duree	frequencemodulorequeter2   nbferiesr"   r"   r#   
recupFerieV   s   


zFerieManager.recupFerieN)rS   rT   rU   r   r"   r"   r"   r#   r   U   rX   r   c                   @   s*   e Zd ZejddZejddZe Z	dS )	JourFerieT)unique   rq   N)
rS   rT   rU   r   	DateFielddaterc   rZ   r   objectsr"   r"   r"   r#   r   a   s    
r   c                   @   sL   e Zd ZejdddgddZejdg ddZejd	d
Ze	dd	d	dZ
dS )Informationu   Expéditeur)r   Administrateur)   u   Secrétariatr   )rl   rD   rE   destinataires))r   Colleurs)r   u   Élèves)   zTout le monderl   rD   Tauto_now_addcustom)config_namerI   rH   N)rS   rT   rU   r   rb   
expediteurdestinataireDateTimeFieldr   r   messager"   r"   r"   r#   r   f   s
    r   c                   @   s   e Zd Zdd ZejddZejddejddZ	ej
ddZejd	d
Zejdd
ZejddZeddddZededdg dddZdS )Messagec                 C   s   d td|dd S )Nz
pj/pj{}.{}rF   .)r;   r(   splitrg   r"   r"   r#   rj   m   s   zMessage.update_nameTr   Usermessagesenvoyes)rH   rN   rM   rE   zlu par: )rl   zListe des destinatairesd   rq   i  )rG   rI   rH   u   Pièce jointe)z
text/plainztext/csvzapplication/mswordzGapplication/vnd.openxmlformats-officedocument.wordprocessingml.documentz
image/jpegz	image/pngz/application/vnd.oasis.opendocument.presentationz.application/vnd.oasis.opendocument.spreadsheetz'application/vnd.oasis.opendocument.textzapplication/vnd.ms-powerpointzIapplication/vnd.openxmlformats-officedocument.presentationml.presentationzapplication/x-rar-compressedzapplication/rtfzapplication/vnd.ms-excelzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetrr   zapplication/zipzapplication/x-7z-compressedrs   rt   N)rS   rT   rU   rj   r   r   r   rd   CASCADEauteurBooleanField	hasAuteurr}   luParlistedestinatairesrc   ry   r   corpsr   pjr"   r"   r"   r#   r   l   s    
r   c                   @   sH   e Zd ZejedejdZejddejdZej	ddZ
ejddZdS )	DestinatairemessagerecurL   r   r   Fr   r   N)rS   rT   rU   r   rd   r   r   r   userr   lurb   reponsesr"   r"   r"   r#   r   |   s
    r   )senderc                 K   s   |j r=|j jd ur?tjt|j j}tj|rt| trAtjtd|j jdd  d }tj|rCt| d S d S d S d S d S )Nimage	   jpg)	r   nameospathr%   r   isfileremover   )r   rh   kwargsr   r   r"   r"   r#   programme_post_delete_function   s   
"r   c                 K   s   zF|j j}tr>|rAtjtd|j jdd  d }tj|sDtdtjt| d |  td| d |  W d S W d S W d S W d S  t	yP   Y d S w )	Nr   r   r   r   zconvert -density 200 z[0] zconvert -resize 50%  )
r   r   r   r   r   r%   r   r   system	Exception)r   rh   r   
nomfichiernomimager"   r"   r#   programme_post_save_function   s   " r   c                 K   s4  |j rtjtjt|j jrttjt|j j}|j	}|dkrz
|d |d  }W n t
y8   d}Y nw |dkr]|t|d df}|j	d d d }||d|d df}n)|dk r|dtd| f}|j	d d d }|d|d|d f}n|d}|tjt|j j d S d S d S d S )N),    r   r   g      ?r   r   r   )photor   r   r   r%   r   r   r   opensizer   resizeintcropsave)r   rh   r   r   r'   ratioabscisseordonneer"   r"   r#   eleve_post_save_function   s*    
r   c                 K   J   |j r|j jd ur!tjt|j j}tj|r#t| d S d S d S d S rx   )r   r   r   r   r%   r   r   r   r   rh   r   r   r"   r"   r#   eleve_post_delete_function      r   c                 K   r   rx   )r   r   r   r   r%   r   r   r   r   r"   r"   r#   r      r   c                   @   s   e Zd ZejdddddZejdddejdZej	d	d
dZ
ej	ddddZejdeeddddZejdeeddddZG dd dZdd ZdS )MatiereECTSColleur
ProfesseurcolleurmatiereECTST)rl   rM   rI   rJ   classematiereECTS)rl   rM   rN   P   u   Matière)rG   rl   rF   u
   Précision)rG   rl   rI   zcoefficient semestre 1   )rl   rD   rH   rI   zcoefficient semestre 2c                   @      e Zd ZdZdS )zMatiereECTS.Meta)rY   rZ   	precisionNrS   rT   rU   unique_togetherr"   r"   r"   r#   rW          rW   c                 C   s    | j rd| j | j S | jS )Nz{}({}))r   r;   rZ   rz   r\   r"   r"   r#   r^      s   zMatiereECTS.__str__N)rS   rT   rU   r   r{   profsrd   r   rY   rc   rZ   r   rb   r`   r&   	semestre1	semestre2rW   r^   r"   r"   r"   r#   r      s    r   c                   @   s6   e Zd Zdd Zdd Zdd Zddd	Zd
d ZdS )NoteECTSManagerc              	   C   s  g }|D ]-}d}t  }|||j|j|jf t|}W d    n1 s'w   Y  || qd}t  }|||j|jf t|}W d    n1 sQw   Y  |jdkrd}t  }|||jf t|}W d    n1 sxw   Y  d}	t  }||	|jf t|}
W d    n1 sw   Y  |r|jdkrdtttdd	 |D  |fS dtttd
d	 |D  |||
fS |jdkrd|gfS dt||fS )Na  SELECT DISTINCT e.id id_eleve, u.first_name prenom,u.last_name nom, m.nom matiere, m.precision, n1.note note1, n2.note note2            FROM accueil_eleve e            INNER JOIN accueil_user u            ON u.eleve_id=e.id            CROSS JOIN accueil_matiereects m            INNER JOIN accueil_matiereects_profs mp            ON mp.matiereects_id = m.id            LEFT OUTER JOIN accueil_noteects n1            ON n1.matiere_id=m.id AND n1.semestre = 1 AND n1.eleve_id = e.id AND n1.cube = e.cube            LEFT OUTER JOIN accueil_noteects n2            ON n2.matiere_id=m.id AND n2.semestre = 2 AND n2.eleve_id = e.id AND n2.cube = e.cube            WHERE m.classe_id=%s AND e.classe_id=%s AND m.id=%s            ORDER BY u.last_name,u.first_nameae  SELECT DISTINCT e.id id_eleve, u.first_name prenom,u.last_name nom, n.note note            FROM accueil_eleve e            INNER JOIN accueil_user u            ON u.eleve_id=e.id            LEFT OUTER JOIN accueil_noteglobaleects n            ON n.eleve_id = e.id AND n.annee = %s            WHERE e.classe_id=%s            ORDER BY u.last_name,u.first_namer   ad  SELECT DISTINCT e.id id_eleve, u.first_name prenom,u.last_name nom, n.note note            FROM accueil_eleve e            INNER JOIN accueil_user u            ON u.eleve_id=e.id            LEFT OUTER JOIN accueil_noteglobaleects n            ON n.eleve_id = e.id AND n.annee = 1            WHERE e.classe_id=%s            ORDER BY u.last_name,u.first_namead  SELECT DISTINCT e.id id_eleve, u.first_name prenom,u.last_name nom, n.note note            FROM accueil_eleve e            INNER JOIN accueil_user u            ON u.eleve_id=e.id            LEFT OUTER JOIN accueil_noteglobaleects n            ON n.eleve_id = e.id AND n.annee = 3            WHERE e.classe_id=%s            ORDER BY u.last_name,u.first_namer   Fc                 S      g | ]}|qS r"   r"   r    noter"   r"   r#   r*         z(NoteECTSManager.note.<locals>.<listcomp>c                 S   r   r"   r"   r   r"   r"   r#   r*   	  r   T)	r   r2   r   rR   r3   appendanneelistr,   )r]   rY   matieres
listeNotesr|   r   r2   notesnotesbisrequete2notesterr"   r"   r#   r      s>   









 $

zNoteECTSManager.notec                 C   sb   d}t  !}||tdd |D |j|j|jf t|}W d    |S 1 s*w   Y  |S )Na  SELECT u.first_name prenom, u.last_name nom, ne1.note semestre1, ne2.note semestre2        FROM accueil_matiereects me        INNER JOIN accueil_classe cl        ON me.classe_id = cl.id        INNER JOIN accueil_eleve e        ON e.classe_id=cl.id AND e.id IN %s        INNER JOIN accueil_user u        ON u.eleve_id=e.id        LEFT OUTER JOIN accueil_noteects ne1        ON ne1.eleve_id = e.id AND ne1.semestre =1 AND ne1.matiere_id=%s AND ne1.cube = e.cube        LEFT OUTER JOIN accueil_noteects ne2        ON ne2.eleve_id = e.id AND ne2.semestre =2 AND ne2.matiere_id=%s AND ne2.cube = e.cube        WHERE me.id=%s        ORDER BY u.last_name,u.first_namec                 S      g | ]}|j qS r"   rR   r    elever"   r"   r#   r*         z.NoteECTSManager.noteEleves.<locals>.<listcomp>)r   r2   r   tuplerR   r3   )r]   r|   listeElevesr   r2   r   r"   r"   r#   
noteEleves  s   
(

zNoteECTSManager.noteElevesc              
   C   s6  |j d u r	g g fS |j jdkrMttjj|ddddddddd}tjj|dd	 }t	jjd|d
}|d | ||d  |
 sGd fS |d jfS |j jdkr|jsttjj|ddddddddd}tjj|ddd  }ttjj|dddddddddd}tjj|dddd  }t	jjd|d
}t	jjd|d
}|d | ||d  |
 sd n|d j|d | ||d  |
 sd fS |d jfS ttjj|ddddddddd}tjj|ddd  }ttjj|dddddddddd}tjj|dddd  }ttjj|dddddddddd}	tjj|dddd  }
t	jjd|d
}t	jjd|d
}t	jjd|d
}|d | ||d  |
 sjd n|d j|d | ||d  |
 sd n|d j|	d |
 |	|
d  |
 sd f	S |d jf	S )Nr   )r   matiere__classe__anneematiere__nommatiere__precisionmatiere__semestre1matiere__semestre2r   semestre)r   r  )r   r   r   r   )r   r   r  F)r   r   cube)r   r   r  r  Tr   )rY   r   r   NoteECTSr   filtervalues_listorder_bycountNoteGlobaleECTSexistsr   r  distinct)r]   r   r   r   ngnotes2	semestre3ng1ng2notes3	semestre5ng3r"   r"   r#   notePDF"  s2   
*.*,V*,,zNoteECTSManager.notePDFFc              	   C   s  |j sdS |j j|j }|j jdk}|j jdko|j }|j jdko$|j}|r=tj|\}}}	t| t| t|	 n|rLtj|\}}}	}
}}ntj|\	}}}	}
}}}}}d}d }}g }|	durrd}d|	 }ddg}n]d}|r|D ]}||d |d  7 }|d dkr||d 7 }qx|dkrd}d}n|dg7 }|rd}|D ]}||d	 |d  7 }|d dkr||d	 7 }q|dkr||7 }||7 }|dg7 }|dkr|dkrdg fS t|| |fS |durd}d| }n|dks|d	krh|rhddg}d}d}d}d}|
r5|
D ]}||d |d  7 }|d dkr$||d 7 }q|dkr0d}d}n|d	g7 }|rg|D ]}||d	 |d  7 }|d dkrS||d	 7 }q:|dkrg||7 }||7 }|dg7 }nu|d	kr|sddg}d}d}d}d}|r|D ]}||d |d  7 }|d dkr||d 7 }q|dkrd}d}n|d	g7 }|r|D ]}||d	 |d  7 }|d dkr||d	 7 }q|dkr||7 }||7 }|dg7 }|dks|d	kr|s|dur|g d
fS || dkr dg fS t|| ||  |fS |dur|g dfS d}d}d}d}g d
}|rR|D ]}||d |d  7 }|d dkrA||d 7 }q(|dkrMd}d}n|dg7 }|r|D ]}||d	 |d  7 }|d dkrp||d	 7 }qW|dkr||7 }||7 }|dg7 }|durt|d|  d|  |fS ||  krdkrn nt|| | d|  |fS |dkrd|fS || |fS )u   
        remvoie le triplet (m,l) où
        - m est la moyenne pondérée dess crédits ECTS
        - l une liste d'entiers indiquant les semestres validés
        r   r   r   NrC         r   r   )r   r   r   r  )r   r   r   r  r     r  x   )rY   r   r  r  r   r  printround)r]   r   r  r   annee1annee2annee3sem1sem2r  sem3sem4r  sem5sem6r  sp1coeff1coefflr   spcoeff2sp2coeff3sp3r"   r"   r#   moyenneECTS@  s  





















zNoteECTSManager.moyenneECTSc           
      C   s,  t dkrd}nd}t }|||jf t|}W d    n1 s$w   Y  dgd }|D ]a}d}|d rA|d  d7  < nd}|d rP|d  d7  < nd}|d	 r_|d
  d7  < nd}d }}	|d dkru|d  d7  < d}|d dkr|d  d7  < d}	|d  ||p|	 7  < q0||fS )Nr8   a  SELECT u.first_name prenom, u.last_name nom, e.id, e.ddn, e.ldn, e.ine, SUM(CASE WHEN ne.semestre = 1 THEN m.semestre1 ELSE 0 END) sem1, ng.note note,            SUM(CASE WHEN ne.semestre = 2 THEN m.semestre2 ELSE 0 END) sem2            FROM accueil_classe cl            INNER JOIN accueil_eleve e            ON e.classe_id=cl.id            INNER JOIN accueil_user u            ON u.eleve_id=e.id            LEFT OUTER JOIN accueil_noteects ne            ON ne.eleve_id = e.id AND ne.note != 5 AND ne.cube = e.cube            LEFT OUTER JOIN accueil_matiereects m            ON ne.matiere_id = m.id AND m.classe_id = cl.id            LEFT OUTER JOIN accueil_noteglobaleects ng            ON ((ng.annee = 3 AND e.cube) OR (ng.annee = cl.annee)) AND ng.eleve_id = e.id            WHERE cl.id = %s            GROUP BY u.last_name, u.first_name, e.id, e.ddn, e.ldn, e.ine, ng.note            ORDER BY u.last_name, u.first_namea  SELECT u.first_name prenom, u.last_name nom, e.id, e.ddn, e.ldn, e.ine, SUM(m1.semestre1) sem1, SUM(m2.semestre2) sem2, ng.note note            FROM accueil_classe cl            INNER JOIN accueil_eleve e            ON e.classe_id=cl.id            INNER JOIN accueil_user u            ON u.eleve_id=e.id            LEFT OUTER JOIN accueil_noteects ne            ON ne.eleve_id = e.id AND ne.note != 5 AND ne.cube = e.cube            LEFT OUTER JOIN accueil_matiereects m1            ON ne.matiere_id = m1.id AND ne.semestre = 1 AND m1.classe_id = cl.id            LEFT OUTER JOIN accueil_matiereects m2            ON ne.matiere_id = m2.id AND ne.semestre = 2 AND m2.classe_id = cl.id            LEFT OUTER JOIN accueil_noteglobaleects ng            ON ((ng.annee = 3 AND e.cube) OR (ng.annee = cl.annee)) AND ng.eleve_id = e.id            WHERE cl.id = %s            GROUP BY u.last_name, u.first_name, e.id, e.ddn, e.ldn, e.ine, ng.note            ORDER BY u.last_name, u.first_namer   r  r   ddnldniner   Fr  r   r   Tr  r  r  )r	   r   r2   r   rR   r3   )
r]   rY   r   r2   creditstotalcreditattestr  r  r"   r"   r#   r1    s8   


zNoteECTSManager.creditsN)F)rS   rT   rU   r   r   r  r-  r1  r"   r"   r"   r#   r      s    ?
 r   c                   @   sn   e Zd ZejedejdZejeejdZ	ej
dddZej
eddZejd	d
dZe ZG dd dZdS )r     Élèverl   rN   )rN   r  ))r   z1er semestre)r   u   2ème semestrer   ABCDEFrD   r  F)rl   rE   c                   @   r   )zNoteECTS.Meta)r   r|   r  r  Nr   r"   r"   r"   r#   rW   !  r   rW   N)rS   rT   rU   r   rd   r   r   r   r   r|   rb   r  r`   r   r   r  r   r   rW   r"   r"   r"   r#   r    s    r  c                   @   r   )NoteGlobaleECTSManagerc              	   C   s\   d}t  }||||jtdd |D f t|}W d    |S 1 s'w   Y  |S )NaH  SELECT u.first_name prenom, u.last_name nom, n.note note_globale        FROM accueil_eleve e        INNER JOIN accueil_user u        ON u.eleve_id=e.id        LEFT OUTER JOIN accueil_noteglobaleects n        ON n.eleve_id = e.id AND n.annee=%s         WHERE e.classe_id=%s AND e.id IN %s        ORDER BY u.last_name,u.first_namec                 S   r   r"   r   r   r"   r"   r#   r*   /  r   z5NoteGlobaleECTSManager.noteEleves.<locals>.<listcomp>)r   r2   r   rR   r   r3   )r]   rY   r   r   r   r2   r   r"   r"   r#   r   %  s   
"

z!NoteGlobaleECTSManager.noteElevesN)rS   rT   rU   r   r"   r"   r"   r#   r9  $  rX   r9  c                   @   sB   e Zd ZejedejdZejdddZ	eje
ddZe ZdS )	r	  r5  r6  u   année))r   u   1ère année)r   u   2ème année)r   u   3eme annéer   r7  r8  N)rS   rT   rU   r   rd   r   r   r   rb   r   r`   r   r9  r   r"   r"   r"   r#   r	  3  s
    
r	  )4	django.dbr   r   django.db.models.signalsr   r   django.dispatchr   r   ecolle.settingsr   r   r	   r
   r   r   PILr   django.db.modelsr   r   randomr   contenttyper   ckeditor.fieldsr   r   r   r[   r(   r3   r>   rA   ModelrB   rf   Managerr   r   r   r   r   r   r   r   r   r   r   r  r9  r	  r"   r"   r"   r#   <module>   sP    










  M