523 lines
23 KiB
Python
523 lines
23 KiB
Python
# -*- coding: utf8 -*-
|
|
from sqlalchemy import text
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import (
|
|
scoped_session,
|
|
sessionmaker,
|
|
)
|
|
|
|
from zope.sqlalchemy import ZopeTransactionExtension, mark_changed
|
|
|
|
from datetime import *
|
|
import transaction
|
|
|
|
from .default import (
|
|
execute_query,
|
|
get_agences
|
|
)
|
|
|
|
|
|
def to_int(x):
|
|
try:
|
|
number = int(x.replace(',', '.'))
|
|
return number
|
|
except ValueError:
|
|
return 0
|
|
|
|
|
|
def get_permis(request):
|
|
# lire les types de permis
|
|
query = "SELECT * FROM permis order by cat;"
|
|
results = request.dbsession.execute(query).fetchall()
|
|
return results
|
|
|
|
|
|
def get_all_dept_nais(request):
|
|
query = """SELECT * FROM p_departements ORDER BY code;"""
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
|
|
def get_all_dept_nais_pays(request):
|
|
query = """SELECT DISTINCT pays FROM p_departements ORDER BY pays;"""
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
|
|
def get_codespostaux(request):
|
|
query = "SELECT DISTINCT code_postal FROM p_codespostaux ;"
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
|
|
def get_formules(request):
|
|
"""Lire les formules"""
|
|
query = """SELECT * FROM formules WHERE DateFin >= CURRENT_DATE ORDER BY formule;"""
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
|
|
def get_formules(request):
|
|
"""Lire les formules"""
|
|
query = """SELECT * FROM formules WHERE DateFin >= CURRENT_DATE ORDER BY formule;"""
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
|
|
def get_all_formules(request, permis):
|
|
"""Lire les formules"""
|
|
query = """CALL spGet_FORMULES_valide('{0}','')""".format(permis)
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
|
|
def get_all_filieres(request):
|
|
"""Lire les formules"""
|
|
query = """SELECT * FROM p_filieres ORDER BY code;"""
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
|
|
def isEleveCreated(request, civilite, nom, nom_jf, prenom, date_nais, lieu_nais, dept_nais, formule):
|
|
# vérifier que l'élève n'est pas déjà créé
|
|
query = """SELECT cd_cli as cd_cli FROM eleves WHERE nom=:nom AND prenom=:prenom AND nom_jf=:nom_jf AND date_nais=:date_nais AND lieu_nais=:lieu_nais AND dept_nais=:dept_nais AND formule=:formule AND cloture_le IS NULL AND DATEDIFF(cree_le,CURRENT_DATE) < 15;"""
|
|
results = request.dbsession.execute(query, {'nom': nom, 'prenom': prenom, 'nom_jf': nom_jf, 'date_nais': date_nais, 'lieu_nais': lieu_nais, 'dept_nais': dept_nais, 'formule': formule}).first()
|
|
if results:
|
|
return results.cd_cli
|
|
else:
|
|
return 0
|
|
|
|
|
|
def insert_eleve_by_formule(request, params):
|
|
cd_cli = isEleveCreated(request, params['CIVILITE'], params['NOM'], params['NOM_JF'], params['PRENOM'], datetime.strptime(params['DATE_NAIS'], '%d/%m/%Y'), params['LIEU_NAIS'], params['DEPT_NAIS'], params['FORMULE'])
|
|
if cd_cli > 0:
|
|
return cd_cli
|
|
else:
|
|
# créer une fiche élève selon formule
|
|
params['DATE_NAIS'] = datetime.strptime(params['DATE_NAIS'], '%d/%m/%Y').strftime("%Y-%m-%d")
|
|
# query = """CALL spUpd_ELEVES_ETATCIVIL(0,:CIVILITE, :NOM, :PRENOM, :NOM_JF, :adresse_no_voie, :adresse_extension,:adresse_type_voie, :adresse_nom_voie, '',:CODEPOST, '', :TEL, :TEL2, :TEL3, :TEL4, :PROFESSION, :NATION, :DATE_NAIS, :LIEU_NAIS, :DEPT_NAIS ,'',:TYPE_ENTREPRISE, :NOM_ENTREPRISE, :NePasSpammer, :NePasRelancer,:encours_societe ,:email, 0, 0, '')"""
|
|
query = "CALL spUpd_ELEVES_ETATCIVIL(0,'{CIVILITE}', '{NOM}', '{PRENOM}', '{NOM_JF}', '{adresse_no_voie}','{adresse_extension}', '{adresse_type_voie}', '{adresse_nom_voie}', '','{CODEPOST}', '', '{TEL}', '{TEL2}', '{TEL3}', '{TEL4}', '{PROFESSION}', '{NATION}', '{DATE_NAIS}', '{LIEU_NAIS}', '{DEPT_NAIS}','','{TYPE_ENTREPRISE}', '{NOM_ENTREPRISE}', {NePasSpammer}, {NePasRelancer},{encours_societe} ,'{email}', 0, {agence}, '')".format(**params)
|
|
# créer une fiche élève selon formule
|
|
results = request.dbsession.execute(query, params).first()
|
|
cd_cli = str(results.newcode).zfill(6)
|
|
# mise à jour onglet INSCRIPTION
|
|
query = """CALL spUpd_ELEVES_INSCRIT(:cd_cli,:permis,:formule,0,0,0,0,'',0,0,0,0,0,0,0,0,0,0,0,0,'CAS 1','','',NOW(),'','','','','', '','','','','',0,'','','','','','',0,0,'')"""
|
|
execute_query(request, query, {'cd_cli': cd_cli,
|
|
'permis': params['PERMIS_DEMANDE'],
|
|
'formule': params['FORMULE'],
|
|
})
|
|
# génére les écritures d'inscription selon la formule
|
|
debit_eleve_inscription(request, cd_cli, params['FORMULE'], '0', '')
|
|
return cd_cli
|
|
|
|
|
|
def get_eleves_by_name(request, name, statut):
|
|
if to_int(name) > 0:
|
|
# saisie = numéro
|
|
if len(name) == 1:
|
|
# afiche les 50 derniers ELEVES crées par l'agence
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.cd_cli like :name AND e.statut=0 ORDER BY e.modif_le DESC;"""
|
|
results = request.dbsession.execute(query, {'name': '%'+name+'%', 'statut': statut}).fetchall()
|
|
elif len(name) == 6 :
|
|
# lire l'eleve par son code
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.cd_cli=:name and e.statut < :statut;"""
|
|
results = request.dbsession.execute(query, {'name': name, 'statut': statut}).fetchall()
|
|
else:
|
|
# lire l'eleve par son no de mobile
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.tel2=:name and e.statut < :statut;"""
|
|
results = request.dbsession.execute(query, {'name': name, 'statut': statut}).fetchall()
|
|
else:
|
|
# lire l'eleve par son nom
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.nompren like :name and e.statut < :statut ORDER BY e.nompren;"""
|
|
results = request.dbsession.execute(query, {'name': name + "%", 'statut': statut}).fetchall()
|
|
return results
|
|
|
|
def get_eleves_not_b78_by_name(request, name, statut):
|
|
if to_int(name) > 0:
|
|
# saisie = numéro
|
|
if len(name) == 1:
|
|
# afiche les 50 derniers ELEVES crées par l'agence
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.cd_cli like :name AND e.statut=0 and e.permis_demande <> 'B78' ORDER BY e.modif_le DESC;"""
|
|
results = request.dbsession.execute(query, {'name': '%'+name+'%', 'statut': statut}).fetchall()
|
|
elif len(name) == 6 :
|
|
# lire l'eleve par son code
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.cd_cli=:name and e.statut < :statut and e.permis_demande <> 'B78';"""
|
|
results = request.dbsession.execute(query, {'name': name, 'statut': statut}).fetchall()
|
|
else:
|
|
# lire l'eleve par son no de mobile
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.tel2=:name and e.statut < :statut and e.permis_demande <> 'B78';"""
|
|
results = request.dbsession.execute(query, {'name': name, 'statut': statut}).fetchall()
|
|
else:
|
|
# lire l'eleve par son nom
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.nompren like :name and e.statut < :statut and e.permis_demande <> 'B78' ORDER BY e.nompren;"""
|
|
results = request.dbsession.execute(query, {'name': name + "%", 'statut': statut}).fetchall()
|
|
return results
|
|
|
|
def get_eleves_ajax(request, name):
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.nompren like :name ORDER BY e.nompren;"""
|
|
results = request.dbsession.execute(query, {'name': "%"+name + "%"}).fetchall()
|
|
return results
|
|
|
|
|
|
def get_eleves_by_code(request, login):
|
|
# lire l'eleve connecte
|
|
query = """SELECT e.* FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat WHERE cd_cli=:login;"""
|
|
results = request.dbsession.execute(query, {'login': login}).first()
|
|
return results
|
|
|
|
def get_eleves_by_name78(request, name, statut):
|
|
if to_int(name) > 0:
|
|
# saisie = numéro
|
|
if len(name) == 1:
|
|
# afiche les 50 derniers ELEVES crées par l'agence
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.cd_cli like :name AND e.permis_demande='B78' AND e.statut=0 ORDER BY e.modif_le DESC;"""
|
|
results = request.dbsession.execute(query, {'name': '%'+name+'%', 'statut': statut}).fetchall()
|
|
elif len(name) == 6 :
|
|
# lire l'eleve par son code
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.cd_cli=:name AND e.permis_demande='B78' and e.statut < :statut;"""
|
|
results = request.dbsession.execute(query, {'name': name, 'statut': statut}).fetchall()
|
|
else:
|
|
# lire l'eleve par son no de mobile
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.tel2=:name AND e.permis_demande='B78' and e.statut < :statut;"""
|
|
results = request.dbsession.execute(query, {'name': name, 'statut': statut}).fetchall()
|
|
else:
|
|
# lire l'eleve par son nom
|
|
query = """SELECT e.civilite, e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.nom_entreprise, e.statut
|
|
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
|
|
WHERE e.nompren like :name AND e.permis_demande='B78' and e.statut < :statut ORDER BY e.nompren;"""
|
|
results = request.dbsession.execute(query, {'name': name + "%", 'statut': statut}).fetchall()
|
|
return results
|
|
|
|
|
|
|
|
|
|
|
|
|
|
def get_eleve_compte(request, cd_cli):
|
|
query = """
|
|
SELECT c.*, m.lib4 FROM eleves_cpt c LEFT JOIN p_mode_regl m ON c.mode_regl=m.code
|
|
WHERE cd_cli=:cd_cli AND NOT (qte=0 AND debit+credit+mtval=0 AND (intitule LIKE '%annulé%' OR intitule LIKE '%remplacé%' OR intitule LIKE '%expiré%'))
|
|
ORDER BY c.DATE;"""
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
|
|
return results
|
|
|
|
def get_eleve_compte_by_no_ligne(request, no_ligne):
|
|
# lire l'eleve connecte
|
|
query = """SELECT * FROM eleves_cpt WHERE no_ligne=:no_ligne;"""
|
|
results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
|
|
return results
|
|
|
|
def get_eleve_compte_complete_by_no_ligne(request, no_ligne):
|
|
# lire l'eleve connecte
|
|
query = """SELECT c.*,m.libelle as mode , c.credit*((100.00 + t.taux)/100) as HT FROM eleves_cpt c LEFT JOIN p_mode_regl m ON c.mode_regl=m.code LEFT JOIN p_tva t ON t.code=c.ctva WHERE c.no_ligne=:no_ligne;"""
|
|
results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
|
|
return results
|
|
|
|
def delete_eleve_compte_by_no_ligne(request, no_ligne, cd_cli):
|
|
# lire l'eleve connecte
|
|
query = """DELETE FROM eleves_cpt WHERE no_ligne=:no_ligne;"""
|
|
execute_query(request, query,{'no_ligne': no_ligne})
|
|
query = "CALL spUPD_ELEVES_SOLDE(:cd_cli);"
|
|
execute_query(request, query,{'cd_cli': cd_cli})
|
|
|
|
def get_mode_reglement(request,maxcode):
|
|
# lire l'eleve connecte
|
|
query = """SELECT * FROM p_mode_regl ;"""
|
|
if maxcode != 0 :
|
|
query = """SELECT * FROM p_mode_regl WHERE CODE<:code;"""
|
|
results = request.dbsession.execute(query,{"code":maxcode}).fetchall()
|
|
return results
|
|
|
|
|
|
def get_eleve_docs(request, cd_cli, type):
|
|
query = """SELECT *, j.libelle FROM eleves_upload e INNER JOIN p_justifs j ON e.code = j.code
|
|
WHERE e.cd_cli = :cd_cli AND e.type = :type ORDER BY e.no_tri;"""
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'type': type}).fetchall()
|
|
return results
|
|
|
|
|
|
def get_eleve_suivi(request, cd_cli, no_ligne):
|
|
if no_ligne == 0:
|
|
query = "SELECT * FROM eleves_suivi WHERE cd_cli = :cd_cli ORDER BY cree_le DESC;"
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
|
|
else:
|
|
query = "SELECT * FROM eleves_suivi WHERE no_ligne = :no_ligne;"
|
|
results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
|
|
return results
|
|
|
|
|
|
def update_eleve(request, cd_cli, new_values):
|
|
s = ''
|
|
for param in new_values.keys():
|
|
if s:
|
|
s += ",%s=:%s" % (param, param)
|
|
else:
|
|
s = "%s=:%s" % (param, param)
|
|
new_values['cd_cli'] = cd_cli
|
|
query = "UPDATE eleves SET %s WHERE CD_CLI=:cd_cli" % s
|
|
execute_query(request, query, new_values)
|
|
|
|
|
|
def update_eleve_delettrer(request, no_ligne):
|
|
query = "CALL spUPD_ELEVES_DELETTRER(:no_ligne)"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
|
|
|
|
def update_suivi(request, no_ligne, new_values):
|
|
# formater les champs
|
|
s = ''
|
|
for param in new_values.keys():
|
|
if s:
|
|
s += ",%s=:%s" % (param, param)
|
|
else:
|
|
s = "%s=:%s" % (param, param)
|
|
|
|
if no_ligne == '0':
|
|
query = "INSERT INTO eleves_suivi SET %s" % s
|
|
else:
|
|
new_values['no_ligne'] = no_ligne
|
|
query = "UPDATE eleves_suivi SET %s WHERE no_ligne = :no_ligne;" % s
|
|
execute_query(request, query, new_values)
|
|
|
|
|
|
def delete_suivi(request, no_ligne):
|
|
query = "DELETE FROM eleves_suivi WHERE no_ligne = :no_ligne ;"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
|
|
|
|
def get_moniteurs_by_code(request, cd_mon):
|
|
if cd_mon == '':
|
|
query = """SELECT * FROM moniteurs WHERE obsolete = False ORDER BY NOM;"""
|
|
results = request.dbsession.execute(query).fetchall()
|
|
else:
|
|
# lire le moniteur
|
|
query = """SELECT * FROM moniteurs WHERE cd_mon=:cd_mon;"""
|
|
results = request.dbsession.execute(query, {'cd_mon': cd_mon}).first()
|
|
return results
|
|
|
|
|
|
def get_examens_aff_byCD_CLI(request, cd_cli):
|
|
# lire le moniteur
|
|
query = """SELECT a.*, CONCAT(DATE_FORMAT(a.date,'%d/%m/%Y'), ' - ', DATE_FORMAT(a.heure,'%H:%i')) as date_heure, e.lieu as lieu, s.LIBELLE as libelle FROM examens_aff a
|
|
INNER JOIN examens e ON a.date=e.date AND a.no_exa=e.no_exa AND a.agence=e.agence
|
|
LEFT JOIN p_statuts_exam s ON a.RESULTAT = s.CODE
|
|
WHERE cd_cli = :cd_cli;"""
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
|
|
return results
|
|
|
|
def get_eleves_permis(request, cd_cli):
|
|
# lire les permis obtenus par l'élève
|
|
query = "SELECT * FROM eleves_permis WHERE cd_cli = :cd_cli;"
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
|
|
return results
|
|
|
|
def get_eleves_permis_byNo(request, no_ligne):
|
|
# lire les permis obtenus par l'élève par no_ligne
|
|
query = "SELECT * FROM eleves_permis WHERE no_ligne = :no_ligne;"
|
|
results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
|
|
return results
|
|
|
|
|
|
def get_devis_by_eleve(request, code):
|
|
# lire les devis par le code eleve
|
|
query = """SELECT * FROM devis WHERE cd_cli=:code;"""
|
|
results = request.dbsession.execute(query, {'code': code}).fetchall()
|
|
return results
|
|
|
|
|
|
def get_devis_cpf_by_eleve(request, code):
|
|
# lire les devis CPF par le code eleve
|
|
query = """SELECT * FROM devis_cpf WHERE cd_cli=:code;"""
|
|
results = request.dbsession.execute(query, {'code': code}).fetchall()
|
|
return results
|
|
|
|
|
|
def get_eleve_justifs_manquant(request, cd_cli):
|
|
"""Lire les justifs manquant dans élève"""
|
|
|
|
query = """SELECT p.*, j.LIBELLE FROM permis_justifs p
|
|
INNER JOIN p_justifs j ON j.code = p.code
|
|
INNER JOIN eleves l ON l.cd_cli = :cd_cli
|
|
LEFT JOIN eleves_upload e ON e.code = p.code AND e.cd_cli = :cd_cli
|
|
WHERE p.cat=l.permis_demande AND e.code IS null ORDER BY p.no_tri, p.code;"""
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
|
|
return results
|
|
|
|
|
|
def get_eleve_docs_manquant(request, cd_cli):
|
|
"""Lire les docs manquant dans élève"""
|
|
|
|
query = """SELECT j.* FROM p_justifs j
|
|
INNER JOIN eleves l ON l.cd_cli = :cd_cli
|
|
LEFT JOIN eleves_upload e ON e.code = j.code AND e.cd_cli = :cd_cli
|
|
WHERE j.type='DOC' AND e.code IS null ORDER BY j.code;"""
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
|
|
return results
|
|
|
|
|
|
def get_eleve_justifs(request, no_ligne):
|
|
"""Lire le justif dans élève"""
|
|
|
|
query = """SELECT p.*, j.LIBELLE, j.conditions FROM eleves_upload p
|
|
INNER JOIN p_justifs j ON j.code = p.code
|
|
WHERE p.no_ligne = :no_ligne;"""
|
|
results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
|
|
return results
|
|
|
|
def get_eleve_stage(request, cd_cli):
|
|
# lire les donnees de l'eleve
|
|
query = "call spGet_ELEVES_INFOS(:cd_cli)"
|
|
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
|
|
return results
|
|
|
|
def insert_eleve_justif(request, cd_cli, code, type, cd_uti, cat):
|
|
# générer justifs de l'élève à partir du permis
|
|
query = """INSERT INTO eleves_upload (cd_cli, code, type, cd_uti)
|
|
VALUES (:cd_cli, :code, :type, :cd_uti);"""
|
|
execute_query(request, query, {'cd_cli': cd_cli, 'code': code, 'type': type, 'cd_uti': cd_uti, 'cat': cat})
|
|
|
|
def update_eleve_justif(request, no_ligne, new_values):
|
|
# mettre à jour justifs de l'élève
|
|
s = ''
|
|
for param in new_values.keys():
|
|
if s:
|
|
s += ",%s=:%s" % (param, param)
|
|
else:
|
|
s = "%s=:%s" % (param, param)
|
|
|
|
|
|
new_values['no_ligne'] = no_ligne
|
|
query = "UPDATE eleves_upload SET %s WHERE no_ligne=:no_ligne" % s
|
|
execute_query(request, query, new_values)
|
|
|
|
def delete_eleve_justif(request, no_ligne):
|
|
query = "DELETE FROM eleves_upload WHERE no_ligne = :no_ligne ;"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
|
|
def delete_eleve_permis(request, no_ligne):
|
|
query = "DELETE FROM eleves_permis WHERE no_ligne = :no_ligne ;"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
|
|
def update_eleve_permis(request, cd_cli, no_ligne, new_values):
|
|
# mettre à jour permis de l'élève
|
|
s = ''
|
|
for param in new_values.keys():
|
|
if s:
|
|
s += ",%s=:%s" % (param, param)
|
|
else:
|
|
s = "%s=:%s" % (param, param)
|
|
|
|
if no_ligne == '0':
|
|
s += ",%s=:%s" % ('cd_cli', 'cd_cli')
|
|
new_values['cd_cli'] = cd_cli
|
|
query = "INSERT INTO eleves_permis SET %s" % s
|
|
else:
|
|
new_values['no_ligne'] = no_ligne
|
|
query = "UPDATE eleves_permis SET %s WHERE no_ligne=:no_ligne" % s
|
|
execute_query(request, query, new_values)
|
|
|
|
def eleve_doc_valorise(request,formule,cd_cli):
|
|
query = "call spUPD_ELEVES_VALORISER(:formule,:cd_cli);"
|
|
execute_query(request,query,{'formule':formule,'cd_cli':cd_cli})
|
|
|
|
def get_status(request):
|
|
query = "SELECT * FROM p_statuts ORDER BY CODE;"
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
def get_solde_reel(request,cd_cli):
|
|
query = "SELECT p.encours + e.encours_societe + e.credit - e.mtval as CREDIT FROM eleves e INNER JOIN permis p ON e.permis_demande=p.cat WHERE e.cd_cli=:cd_cli;"
|
|
results = request.dbsession.execute(query,{"cd_cli":cd_cli})
|
|
return results.first()
|
|
|
|
def get_examen_reference(request,cd_cli):
|
|
query = """CALL spGet_TARIFS_byFAM('EXA',:cd_cli,0);"""
|
|
results = request.dbsession.execute(query,{'cd_cli':cd_cli})
|
|
return results.fetchall()
|
|
|
|
def get_eleve_max_lettre(request,cd_cli):
|
|
query = """SELECT MAX(lettre) as maxlettre FROM eleves_cpt WHERE cd_cli= :cd_cli;"""
|
|
results = request.dbsession.execute(query,{'cd_cli':cd_cli})
|
|
return results.first()
|
|
|
|
def update_eleve_lettre(request,no_ligne,lettre):
|
|
query = """ UPDATE eleves_cpt SET lettre=:lettre WHERE no_ligne=:no_ligne ;"""
|
|
execute_query(request, query, {"lettre":lettre,"no_ligne":no_ligne})
|
|
|
|
def update_eleve_delettre(request,cd_cli,lettre,modif_le):
|
|
query = """ UPDATE eleves_cpt SET lettre = 0 , modif_le = :modif_le WHERE cd_cli = :cd_cli AND lettre = :lettre"""
|
|
execute_query(request, query, {"lettre":lettre,"cd_cli":cd_cli,'modif_le':modif_le})
|
|
|
|
def eleve_auto_lettre(request,cd_cli):
|
|
query = """CALL spUPD_ELEVES_LETTRER(:cd_cli)"""
|
|
request.dbsession.execute(query, {"cd_cli":cd_cli})
|
|
|
|
def call_proc_eleve_credit(request,no_ligne,cd_cli,date,mode_regle,intitule,credit,mt1,mt2,mt3,mtval,agence,cd_uti):
|
|
query = """CALL spUpd_ELEVES_CREDIT(:no_ligne,:cd_cli,:date,:mode_regle,:intitule,:credit,:mt1,:mt2,:mt3,:mtval,:agence,:cd_uti) ;"""
|
|
execute_query(request, query, {
|
|
'no_ligne':no_ligne,
|
|
'cd_cli':cd_cli,
|
|
'date':date,
|
|
'mode_regle':mode_regle,
|
|
'intitule':intitule,
|
|
'credit':credit,
|
|
'mt1':mt1,
|
|
'mt2':mt2,
|
|
'mt3':mt3,
|
|
'mtval':mtval,
|
|
'agence':agence,
|
|
'cd_uti':cd_uti
|
|
})
|
|
|
|
def call_proc_eleve_debit(request,no_ligne,cd_cli,date,ref,intitule,qte,debit,dateval,mtval,compte,statut,circuit,agence,cd_uti):
|
|
query = """CALL spUpd_ELEVES_DEBIT(:no_ligne,:cd_cli,:date,:ref,:intitule,:qte,:debit,:dateval,:mtval,:compte,:statut,:circuit,:agence,:cd_uti) ;"""
|
|
execute_query(request, query, {
|
|
'no_ligne':no_ligne,
|
|
'cd_cli':cd_cli,
|
|
'date':date,
|
|
'ref':ref,
|
|
'intitule':intitule,
|
|
'qte':qte,
|
|
'debit':debit,
|
|
'dateval':dateval,
|
|
'mtval':mtval,
|
|
'compte':compte,
|
|
'statut':statut,
|
|
'circuit':circuit,
|
|
'agence':agence,
|
|
'cd_uti':cd_uti
|
|
})
|
|
|
|
def debit_eleve_inscription(request, cd_cli, formule, agence, cd_uti):
|
|
query = "CALL spINS_ELEVES_DEBIT_INSCRIPTION(:formule, :cd_cli, NOW(),:agence, :cd_uti)"
|
|
execute_query(request, query, {'cd_cli': cd_cli, 'formule': formule, 'agence':agence, 'cd_uti':cd_uti})
|
|
return cd_cli
|
|
|
|
def delete_eleve_inscription(request, cd_cli, formule):
|
|
query = "CALL spDEL_ELEVES_DEBIT_INSCRIPTION(:formule, :cd_cli, NOW(),0, '')"
|
|
execute_query(request, query, {'cd_cli': cd_cli, 'formule': formule})
|
|
return cd_cli |