655 lines
32 KiB
Python
655 lines
32 KiB
Python
# -*- coding: utf8 -*-
|
||
from datetime import *
|
||
from dateutil.relativedelta import *
|
||
|
||
from .default import (
|
||
execute_query,
|
||
update_eleve_solde,
|
||
)
|
||
|
||
def get_rendez_vous_moniteur(request, agence, cd_mon, datedeb, datefin):
|
||
|
||
# lire les rdv de l'ITC
|
||
query = """
|
||
SELECT c.DATE, c.noplan, c.qte, c.nom, c.cd_cli, c.no_ligne, c.date_valeur, c.ref, c.statut, c.comment, c.lieu_rdv, c.circuit, c.fin_reservation, e.NOM_ENTREPRISE
|
||
FROM eleves_cpt c INNER JOIN eleves e ON c.cd_cli = e.cd_cli
|
||
WHERE c.agence=:agence AND c.date >= :datedeb AND c.date <= :datefin AND c.cd_mon=:cd_mon AND c.noplan AND c.qte
|
||
ORDER BY c.agence, c.date, c.noplan;"""
|
||
results = request.dbsession.execute(
|
||
query, {'agence': agence, 'datedeb': datedeb, 'datefin': datefin, 'cd_mon': cd_mon}).fetchall()
|
||
return results
|
||
|
||
def get_rendez_vous_annules(request, agence, cd_mon, datedeb, datefin):
|
||
|
||
# lire les rdv annulés
|
||
query = """
|
||
SELECT c.DATE, c.noplan, c.qte, c.nom, c.cd_cli, c.no_ligne, c.ref, c.statut, c.comment, c.cd_uti, c.circuit, c.intitule, e.NOM_ENTREPRISE
|
||
FROM eleves_cpt c INNER JOIN eleves e ON c.cd_cli = e.cd_cli
|
||
WHERE c.agence=:agence AND c.date >= :datedeb AND c.date <= :datefin AND c.cd_cli > 100000 AND c.cd_mon=:cd_mon AND c.intitule LIKE "%h annulé%" AND c.qte=0
|
||
ORDER BY c.agence, c.date;"""
|
||
results = request.dbsession.execute(
|
||
query, {'agence': agence, 'datedeb': datedeb, 'datefin': datefin, 'cd_mon': cd_mon}).fetchall()
|
||
return results
|
||
|
||
def get_rendezvous_by_noligne(request, no_ligne):
|
||
# lire le rdv B
|
||
query = """
|
||
SELECT c.*, e.TYPE_ENTREPRISE, e.NOM_ENTREPRISE
|
||
FROM eleves_cpt c INNER JOIN eleves e ON c.cd_cli = e.cd_cli
|
||
WHERE c.no_ligne = :no_ligne;"""
|
||
results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
|
||
return results
|
||
|
||
def get_statuts_lecon(request):
|
||
query = "CALL spGet_P_TABLES_byCODE('P_STATUTS_LECON', 0);"
|
||
results = request.dbsession.execute(query).fetchall()
|
||
return results
|
||
|
||
def update_rdvb(request, no_ligne, old_cd_cli, cd_cli, date, heure, qte, ref, cd_mon, agence, comment, statut, typeHeure, nDelai, cd_cli_old, cd_uti):
|
||
query = "CALL spUpd_PLANNING_B(:no_ligne,:old_cd_cli,:cd_cli,:date,:heure,:qte,:ref,:cd_mon,:agence,0,:comment,:statut,:typeHeure,:nDelai,:cd_cli_old,:cd_uti);"
|
||
execute_query(request, query, {'no_ligne': no_ligne, 'old_cd_cli': old_cd_cli, 'cd_cli': cd_cli, 'date': date, 'heure': heure, 'qte': qte, 'ref': ref,
|
||
'cd_mon': cd_mon, 'agence': agence, 'comment': comment, 'statut': statut, 'typeHeure': typeHeure,
|
||
'nDelai': nDelai, 'cd_cli_old': cd_cli_old, 'cd_uti': cd_uti})
|
||
|
||
query = "CALL spUpd_PLANNING_B_VALORISER(:cd_cli);"
|
||
execute_query(request, query, {'cd_cli': cd_cli})
|
||
update_eleve_solde(request, cd_cli)
|
||
|
||
def delete_eleve_cpt(request, no_ligne, cd_cli, noplan, statut, cd_uti):
|
||
query = "UPDATE eleves_cpt SET ref=NULL,debit=0,mtval=0,qte=0,noplan=0,fin_reservation=NULL,intitule='RDV de :noplan h annulé (:statut)', cd_uti=:cd_uti WHERE no_ligne=:no_ligne;"
|
||
execute_query(request, query, {
|
||
'no_ligne': no_ligne, 'noplan': noplan, 'statut': statut, 'cd_uti': cd_uti})
|
||
|
||
query = "CALL spUpd_PLANNING_B_VALORISER(:cd_cli);"
|
||
execute_query(request, query, {'cd_cli': cd_cli})
|
||
update_eleve_solde(request, cd_cli)
|
||
|
||
def validate_rdvb(request, no_ligne):
|
||
query = """ UPDATE eleves_cpt SET DATE_VALEUR=CURRENT_DATE() WHERE no_ligne=:no_ligne """
|
||
execute_query(request, query, {'no_ligne': no_ligne})
|
||
|
||
def get_pla_stage_by_semaine(request, type_stage, semaine, groupe):
|
||
query = "SELECT * FROM pla_stage WHERE TYPE=:type AND SEMAINE=:semaine AND GROUPE=:groupe;"
|
||
results = request.dbsession.execute(
|
||
query, {"semaine": semaine, 'groupe': groupe, 'type': type_stage}).first()
|
||
return results
|
||
|
||
def get_pla_moto_by_date(request, type_stage, groupe, date):
|
||
query = "SELECT * FROM pla_moto WHERE TYPE=:type AND DATE=:date AND GROUPE=:groupe;"
|
||
results = request.dbsession.execute(
|
||
query, {"date": date, 'groupe': groupe, 'type': type_stage}).first()
|
||
return results
|
||
|
||
def get_pla_moto_ligne_by_date(request, type_stage, groupe, date, cd_cli):
|
||
query = "SELECT * FROM pla_moto_lignes WHERE TYPE=:type AND DATE=:date AND GROUPE=:groupe AND CD_CLI=:cd_cli;"
|
||
results = request.dbsession.execute(
|
||
query, {"date": date, 'groupe': groupe, 'type': type_stage, 'cd_cli': cd_cli}).first()
|
||
return results
|
||
|
||
def insert_pla_stage(request, new_values):
|
||
s = ''
|
||
for param in new_values.keys():
|
||
if s:
|
||
s += ",%s=:%s" % (param, param)
|
||
else:
|
||
s = "%s=:%s" % (param, param)
|
||
|
||
query = "INSERT pla_stage SET %s ;" % s
|
||
execute_query(request, query, new_values)
|
||
|
||
def insert_pla_moto(request, new_values):
|
||
s = ''
|
||
for param in new_values.keys():
|
||
if s:
|
||
s += ",%s=:%s" % (param, param)
|
||
else:
|
||
s = "%s=:%s" % (param, param)
|
||
|
||
query = "INSERT pla_moto SET %s ;" % s
|
||
execute_query(request, query, new_values)
|
||
|
||
def duplicate_pla_moto_by_semaine(request, type_stage, start, dest, cd_uti):
|
||
query = "CALL spDUPP_PLA_MOTO_byWEEK(:type,:start,:dest,:cd_uti);"
|
||
execute_query(request, query, {
|
||
'type': type_stage, 'start': start, 'dest': dest, 'cd_uti': cd_uti})
|
||
|
||
|
||
def update_pla_stage(request, new_values, type_stage, semaine, groupe):
|
||
s = ''
|
||
for param in new_values.keys():
|
||
if s:
|
||
s += ",%s=:%s" % (param, param)
|
||
else:
|
||
s = "%s=:%s" % (param, param)
|
||
new_values['TYPE'] = type_stage
|
||
new_values['SEMAINE'] = semaine
|
||
new_values['GROUPE'] = groupe
|
||
query = "UPDATE pla_stage SET %s WHERE SEMAINE=:SEMAINE AND TYPE=:TYPE AND GROUPE=:GROUPE;" % s
|
||
execute_query(request, query, new_values)
|
||
|
||
|
||
def update_pla_motos(request, type_stage, date, groupe, new_values):
|
||
s = ''
|
||
for param in new_values.keys():
|
||
if s:
|
||
s += ",%s=:%s" % (param, param)
|
||
else:
|
||
s = "%s=:%s" % (param, param)
|
||
new_values['TYPE'] = type_stage
|
||
new_values['DATE'] = date
|
||
new_values['GROUPE'] = groupe
|
||
query = "UPDATE pla_moto SET %s WHERE DATE=:DATE AND TYPE=:TYPE AND GROUPE=:GROUPE;" % s
|
||
execute_query(request, query, new_values)
|
||
# results = request.dbsession.execute(query,new_values)
|
||
# return results
|
||
|
||
def update_motos_lig(request, no_ligne, new_values):
|
||
s = ''
|
||
for param in new_values.keys():
|
||
if param == "STATUT":
|
||
new_values['STATUT'] = int(new_values['STATUT'])
|
||
if s:
|
||
s += ",%s=:%s" % (param, param)
|
||
else:
|
||
s = "%s=:%s" % (param, param)
|
||
|
||
new_values['no_ligne'] = no_ligne
|
||
query = "UPDATE pla_moto_lignes SET %s WHERE no_ligne=:no_ligne" % s
|
||
execute_query(request, query, new_values)
|
||
|
||
def update_motos_lig_avec_control(request, params):
|
||
query1 = "CALL spUpd_PLA_MOTO_LIG(:TYPE, :DATE, :GROUPE,:OLD_CD_CLI, :CD_CLI, :STATUT, :LIEU,:COMMENT, :QTE, :REF, :Route ,:AGENCE, :SOLDE, :LIGNE_CPT, :CD_UTI)"
|
||
execute_query(request, query1, params)
|
||
if params['TYPE'] == "E":
|
||
query_val = "CALL spUpd_PLANNING_B_VALORISER(:CD_CLI)"
|
||
else:
|
||
query_val = "CALL spUpd_PLANNING_A_VALORISER(:CD_CLI, :REF);"
|
||
execute_query(request, query_val, params)
|
||
|
||
def insert_motos_lig(request, new_values):
|
||
s = ''
|
||
for param in new_values.keys():
|
||
if s:
|
||
s += ",%s=:%s" % (param, param)
|
||
else:
|
||
s = "%s=:%s" % (param, param)
|
||
query = "INSERT INTO pla_moto_lignes SET %s ;" % s
|
||
execute_query(request, query, new_values)
|
||
|
||
def update_motos_lig_circuit(request, type, date, cd_cli, cd_circuit):
|
||
# maj le code moniteur circuit
|
||
query = "UPDATE pla_moto_lignes SET cd_circuit = :cd_circuit WHERE type = :type and date = :date and cd_cli = :cd_cli AND valide = 'N';"
|
||
execute_query(request, query, {
|
||
'type': type, 'date': date, 'cd_cli': cd_cli, 'cd_circuit': cd_circuit})
|
||
|
||
def update_motos_lig_cd_mon(request, type, date, cd_cli, cd_mon):
|
||
# maj le code moniteur
|
||
query = "UPDATE pla_moto_lignes SET cd_mon = :cd_mon WHERE type = :type and date = :date and cd_cli = :cd_cli AND valide = 'N';"
|
||
execute_query(request, query, {
|
||
'type': type, 'date': date, 'cd_cli': cd_cli, 'cd_mon': cd_mon})
|
||
|
||
def update_stages_lig(request, no_ligne, new_values, isInsert=False):
|
||
type = no_ligne[0:1]
|
||
semaine = no_ligne[1:7]
|
||
groupe = no_ligne[7:8]
|
||
cd_cli = no_ligne[8:14]
|
||
s = ''
|
||
for param in new_values.keys():
|
||
if param == "STATUT":
|
||
new_values['STATUT'] = int(new_values['STATUT'])
|
||
if s:
|
||
s += ",%s=:%s" % (param, param)
|
||
else:
|
||
s = "%s=:%s" % (param, param)
|
||
|
||
new_values['TYPE'] = type
|
||
new_values['SEMAINE'] = semaine
|
||
new_values['GROUPE'] = groupe
|
||
if isInsert:
|
||
# query = "INSERT INTO pla_stage_lignes SET %s ,TYPE=:TYPE,SEMAINE=:SEMAINE,GROUPE=:GROUPE;"% s
|
||
query = "CALL spUpd_PLA_STAGE_LIG(:TYPE, :SEMAINE, :GROUPE, 0, :CD_CLI, :NOM, '', :REF, :STATUT, :AGENCE, 0, 0, :CD_UTI)"
|
||
else:
|
||
new_values['CD_CLI_OLD'] = cd_cli
|
||
# query = "UPDATE pla_stage_lignes SET %s WHERE TYPE=:TYPE and SEMAINE=:SEMAINE and GROUPE=:GROUPE and CD_CLI=:CD_CLI_OLD" % s
|
||
query = "CALL spUpd_PLA_STAGE_LIG(:TYPE, :SEMAINE, :GROUPE, :CD_CLI_OLD, :CD_CLI, :NOM, '', :REF, :STATUT, :AGENCE, 0, :LIGNE_CPT, :CD_UTI)"
|
||
|
||
execute_query(request, query, new_values)
|
||
|
||
|
||
def get_stage_lig_by_date(request, type_stage, date_stage, groupe):
|
||
# lire toutes les lignes stage
|
||
if type_stage == 'M':
|
||
query = """
|
||
SELECT l.*, s.debut, s.fin, e.credit-e.debit as solde, e.cree_le, e.h1plateau_le, e.permis_demande, e.formule, e.dossier_date,
|
||
CONCAT(e.heures_prises,'/',e.hroute_prises,' h') as heures, e.TR_P_NB, e.TR_R_NB, e.TR_P_DATE, e.TR_R_DATE, e.perime_le FROM pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type_stage AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.groupe=:groupe ORDER BY l.cd_cli;"""
|
||
results = request.dbsession.execute(
|
||
query, {'type_stage': type_stage, 'date_stage': date_stage, 'groupe': groupe}).fetchall()
|
||
elif type_stage == 'B':
|
||
# stage B
|
||
if groupe == 'A' or groupe == 'B':
|
||
query = """
|
||
SELECT l.*, s.debut, s.fin, e.credit-e.debit as solde, e.dossier_date, e.niveau, e.formule, e.filiere, e.nom_entreprise, e.cd_mon AS CD_REF,
|
||
CONCAT(e.heures_prises,'/',e.heures_prevues,' h') as heures, e.perime_le FROM pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type_stage AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.groupe IN ('A','B') ORDER BY l.cd_cli;"""
|
||
else:
|
||
query = """
|
||
SELECT l.*, s.debut, s.fin, e.credit-e.debit as solde, e.dossier_date, e.niveau, e.formule, e.filiere, e.nom_entreprise, e.cd_mon AS CD_REF,
|
||
CONCAT(e.heures_prises,'/',e.heures_prevues,' h') as heures, e.perime_le FROM pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type_stage AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.groupe=:groupe ORDER BY l.cd_cli;"""
|
||
results = request.dbsession.execute(
|
||
query, {'type_stage': type_stage, 'date_stage': date_stage, 'groupe': groupe}).fetchall()
|
||
else:
|
||
query = """
|
||
SELECT l.*, s.debut, s.fin, e.credit-e.debit as solde, e.cree_le, e.permis_demande, e.formule, e.dossier_date,
|
||
CONCAT(e.heures_prises,'/',e.hroute_prises,' h') as heures, e.TR_P_DATE, e.TR_R_DATE, e.perime_le FROM pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type_stage AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.groupe=:groupe ORDER BY l.cd_cli;"""
|
||
results = request.dbsession.execute(
|
||
query, {'type_stage': type_stage, 'date_stage': date_stage, 'groupe': groupe}).fetchall()
|
||
return results
|
||
|
||
def get_motos_lig_by_date(request, type, date_stage, groupe):
|
||
# lire les planning motos
|
||
if groupe == '':
|
||
query = """
|
||
SELECT l.*, e.credit-e.debit as solde, e.dossier_date, e.niveau, e.formule, e.filiere, e.nom_entreprise, e.cd_mon,
|
||
CONCAT(e.heures_prises,'/',e.hroute_prises,' h') as heures, e.perime_le, e.tr_p_ok FROM pla_moto_lignes l
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type AND l.date = :date_stage ORDER BY l.cd_cli;"""
|
||
else:
|
||
query = """
|
||
SELECT l.*, e.credit-e.debit as solde, e.cree_le, e.h1plateau_le, e.permis_demande, e.formule, e.dossier_date, e.nom_entreprise, e.niveau, e.filiere, e.cd_mon,
|
||
CONCAT(e.heures_prises,'h - ',e.hroute_prises,'h') as heures, e.TR_P_NB, e.TR_R_NB, e.TR_P_DATE, e.TR_R_DATE, e.perime_le, e.tr_p_ok FROM pla_moto_lignes l
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type AND l.date = :date_stage AND l.groupe=:groupe ORDER BY l.cd_cli;"""
|
||
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'date_stage': date_stage, 'groupe': groupe}).fetchall()
|
||
return results
|
||
|
||
def get_motos_lig_by_noligne(request, no_ligne):
|
||
# lire les stage B
|
||
query = """
|
||
SELECT l.*, e.TYPE_ENTREPRISE, e.NOM_ENTREPRISE FROM pla_moto_lignes l
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.no_ligne = :no_ligne;"""
|
||
results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
|
||
return results
|
||
|
||
def get_lieux_by_type(request, type_groupe):
|
||
query = """ SELECT * from p_lieux WHERE TYPE=:type_groupe;"""
|
||
results = request.dbsession.execute(
|
||
query, {'type_groupe': type_groupe}).fetchall()
|
||
return results
|
||
|
||
def get_stages_lig_by_noligne(request, no_ligne):
|
||
# lire les stage B
|
||
type = no_ligne[0:1]
|
||
semaine = no_ligne[1:7]
|
||
groupe = no_ligne[7:8]
|
||
cd_cli = no_ligne[8:14]
|
||
query = """
|
||
SELECT l.*, e.TYPE_ENTREPRISE, e.NOM_ENTREPRISE FROM pla_stage_lignes l
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type and l.semaine = :semaine and l.groupe = :groupe and l.cd_cli = :cd_cli;"""
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'semaine': semaine, 'groupe': groupe, 'cd_cli': cd_cli}).first()
|
||
return results
|
||
|
||
|
||
def get_stage_lig_by_groupe(request, type, semaine, groupe):
|
||
# lire les stage B
|
||
query = """
|
||
SELECT l.*, s.debut, s.fin, e.credit-e.debit as solde, e.dossier_date, e.niveau, e.formule, e.filiere,
|
||
CONCAT(e.heures_prises,'/',e.heures_prevues) as heures, e.perime_le FROM pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
INNER JOIN eleves e ON l.cd_cli = e.cd_cli
|
||
WHERE l.type = :type and l.semaine = :semaine and l.groupe = :groupe;"""
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'semaine': semaine, 'groupe': groupe}).fetchall()
|
||
return results
|
||
|
||
|
||
def get_stage_lig_by_cd_cli(request, type, cd_cli):
|
||
# lire les stage B
|
||
query = """
|
||
SELECT l.* FROM pla_stage_lignes l WHERE l.type = :type and l.cd_cli = :cd_cli;"""
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'cd_cli': cd_cli}).first()
|
||
return results
|
||
|
||
|
||
def stage_lig_by_eleve(request, cd_cli, type_stage, semaine, groupe):
|
||
query = """ SELECT * FROM pla_stage_lignes WHERE type = :type and cd_cli = :cd_cli and groupe=:groupe and semaine=:semaine; """
|
||
results = request.dbsession.execute(
|
||
query, {'type': type_stage, 'cd_cli': cd_cli, 'semaine': semaine, 'groupe': groupe}).fetchall()
|
||
return results
|
||
|
||
|
||
def motos_lig_by_eleve(request, cd_cli, type_stage, date, groupe):
|
||
query = """ SELECT * FROM pla_moto_lignes WHERE type = :type and cd_cli = :cd_cli and groupe=:groupe and date=:date; """
|
||
results = request.dbsession.execute(
|
||
query, {'type': type_stage, 'cd_cli': cd_cli, 'date': date, 'groupe': groupe}).fetchall()
|
||
return results
|
||
|
||
def get_stages(request, type_stage):
|
||
TODAY = date.today()
|
||
# début = aujourd'hui - 1 semaines
|
||
d = TODAY + relativedelta(weeks=-6)
|
||
datedeb = d.strftime('%Y-%m-01')
|
||
# fin = aujourd'hui + 2 mois
|
||
d = TODAY + relativedelta(months=+6)
|
||
datefin = d.strftime('%Y-%m-01')
|
||
|
||
# lire les stages
|
||
query = "SELECT * FROM pla_stage WHERE type = :type_stage AND debut >= :datedeb AND debut <= :datefin ORDER BY debut"
|
||
results = request.dbsession.execute(
|
||
query, {'type_stage': type_stage, 'datedeb': datedeb, 'datefin': datefin}).fetchall()
|
||
return results
|
||
|
||
def get_moniteurs_by_aff(request, agence, date_deb, date_fin, planning='ALL'):
|
||
query = "CALL spGet_MONITEURS_AFF_byAGENCE(:agence, :date_deb, :date_fin, :planning)"
|
||
results = request.dbsession.execute(
|
||
query, {'agence': agence, 'date_deb': date_deb, 'date_fin': date_fin, 'planning': planning}).fetchall()
|
||
return results
|
||
|
||
def get_moniteurs_by_activite(request, date_deb, date_fin, act1, act2):
|
||
# moniteurs ayant une activite en cours + dans le futur dans l’agence
|
||
query = """
|
||
SELECT moniteurs.* FROM eleves_cpt
|
||
INNER JOIN moniteurs ON moniteurs.cd_mon = eleves_cpt.cd_mon
|
||
WHERE (eleves_cpt.cd_cli=:act1 OR eleves_cpt.cd_cli=:act2) AND eleves_cpt.date >= :date_deb AND eleves_cpt.date <=:date_fin AND qte
|
||
GROUP BY cd_mon ORDER BY nom;"""
|
||
results = request.dbsession.execute(
|
||
query, {'act1': act1, 'act2': act2, 'date_deb': date_deb, 'date_fin': date_fin}).fetchall()
|
||
return results
|
||
|
||
def get_motos(request, type):
|
||
TODAY = date.today()
|
||
# début = aujourd'hui - 1 semaines
|
||
d = TODAY + relativedelta(weeks=-4)
|
||
datedeb = d.strftime('%Y-%m-01')
|
||
# fin = aujourd'hui + 2 mois
|
||
d = TODAY + relativedelta(months=+6)
|
||
datefin = d.strftime('%Y-%m-01')
|
||
|
||
# lire les plannings de type motos
|
||
query = "SELECT * FROM pla_moto WHERE type=:type AND date >= :datedeb AND date <= :datefin ORDER BY date"
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'datedeb': datedeb, 'datefin': datefin}).fetchall()
|
||
return results
|
||
|
||
def get_motos_byId(request, type, date, groupe):
|
||
# lire le planning MOTO par son ID : type - date - groupe
|
||
query = "SELECT * FROM pla_moto WHERE type=:type AND date = :date AND groupe = :groupe;"
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'date': date, 'groupe': groupe}).first()
|
||
return results
|
||
|
||
def update_stageb_lig_moniteur(request, date_stage, cd_cli, cd_stage):
|
||
# maj les stages B
|
||
query = """
|
||
UPDATE pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
SET l.cd_mon = :cd_stage
|
||
WHERE l.type = 'B' AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.cd_cli = :cd_cli AND l.valide = 'N';"""
|
||
execute_query(request, query, {
|
||
'date_stage': date_stage, 'cd_cli': cd_cli, 'cd_stage': cd_stage})
|
||
|
||
|
||
def update_stageb_lig_circuit(request, date_stage, cd_cli, cd_circuit):
|
||
# maj les stages B
|
||
query = """
|
||
UPDATE pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
SET l.cd_circuit = :cd_circuit
|
||
WHERE l.type = 'B' AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.cd_cli = :cd_cli AND l.valide = 'N';"""
|
||
execute_query(request, query, {
|
||
'date_stage': date_stage, 'cd_cli': cd_cli, 'cd_circuit': cd_circuit})
|
||
|
||
|
||
def update_stageb_lig_seance(request, date_stage, cd_cli, seance):
|
||
# maj les stages B
|
||
query = """
|
||
UPDATE pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
SET l.seance = :seance
|
||
WHERE l.type = 'B' AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.cd_cli = :cd_cli AND l.valide = 'N';"""
|
||
execute_query(request, query, {
|
||
'date_stage': date_stage, 'cd_cli': cd_cli, 'seance': seance})
|
||
|
||
|
||
def update_stage_lig_statut(request, type, date_stage, cd_cli):
|
||
# maj les stages B
|
||
query = """
|
||
UPDATE pla_stage_lignes l
|
||
INNER JOIN pla_stage s ON s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
|
||
SET l.statut = 1
|
||
WHERE l.type = :type AND date(s.debut) <= :date_stage AND s.fin >= :date_stage AND l.cd_cli = :cd_cli AND l.statut = 0 ;"""
|
||
execute_query(request, query, {'type': type,
|
||
'date_stage': date_stage, 'cd_cli': cd_cli})
|
||
|
||
def update_stage_lig_by_cd_cli(request, type_stage, semaine, groupe, cd_cli, statut, valide, agence, ligne_cpt, libelle, date_valeur):
|
||
query = """ UPDATE pla_stage_lignes SET statut=:statut, valide=:valide, agence=:agence WHERE type=:type AND semaine=:semaine AND groupe=:groupe AND cd_cli=:cd_cli; """
|
||
execute_query(request, query, {'type': type_stage, 'semaine': semaine, 'groupe': groupe,
|
||
'cd_cli': cd_cli, 'statut': statut, 'valide': valide, 'agence': agence})
|
||
|
||
query2 = """ CALL spUPD_ELEVES_VALO_STAGE(:ligne_cpt, :statut, :agence)"""
|
||
execute_query(request, query2, {
|
||
'statut': statut, 'ligne_cpt': ligne_cpt, 'agence': agence})
|
||
|
||
# update eleve_cpt
|
||
query3 = """ UPDATE eleves_cpt SET INTITULE=:libelle,AGENCE=:agence,STATUT=:statut,DATE_VALEUR=:date_valeur WHERE no_ligne=:ligne_cpt ;"""
|
||
execute_query(request, query3, {'libelle': libelle, 'statut': statut,
|
||
'ligne_cpt': ligne_cpt, 'agence': agence, 'date_valeur': date_valeur})
|
||
|
||
def update_motos_lig_by_cd_cli(request, type_planning, date, groupe, cd_cli, statut, valide, agence, ligne_cpt, ref, cd_mon):
|
||
query = """ UPDATE pla_moto_lignes SET statut=:statut, valide=:valide, agence=:agence WHERE type=:type AND date=:date AND groupe=:groupe AND cd_cli=:cd_cli; """
|
||
execute_query(request, query, {'type': type_planning, 'date': date, 'groupe': groupe,
|
||
'cd_cli': cd_cli, 'statut': statut, 'valide': valide, 'agence': agence})
|
||
if type_planning == "E":
|
||
query2 = """ CALL spUpd_PLANNING_B_VALORISER(:cd_cli,)"""
|
||
execute_query(request, query2, {'cd_cli': cd_cli})
|
||
else:
|
||
query2 = """ CALL spUpd_PLANNING_A_VALORISER(:cd_cli, :ref)"""
|
||
execute_query(request, query2, {'cd_cli': cd_cli, 'ref': ref, })
|
||
|
||
# update eleve_cpt
|
||
query3 = """ UPDATE eleves_cpt SET fin_reservation=NULL,STATUT=:statut,DATE_VALEUR=CURRENT_DATE() WHERE no_ligne=:ligne_cpt ;"""
|
||
execute_query(request, query3, {'statut': statut, 'ligne_cpt': ligne_cpt})
|
||
# post validate
|
||
if type_planning == "A":
|
||
query4 = """ CALL spUpd_ATTENTES('2R', :cd_cli, 1, True, :cd_mon,'AEM') ;"""
|
||
execute_query(request, query4, {'cd_cli': cd_cli, 'cd_mon': cd_mon})
|
||
elif type_planning == "D":
|
||
query4 = """ CALL spUpd_ATTENTES(ETG, :cd_cli, 1, True, :cd_mon,'AEM') """
|
||
execute_query(request, query4, {'cd_cli': cd_cli, 'cd_mon': cd_mon})
|
||
elif type_planning == "E":
|
||
query4 = """ CALL spUpd_ATTENTES(‘B’, :cd_cli, 1, True, :cd_mon,'AEM') """
|
||
execute_query(request, query4, {'cd_cli': cd_cli, 'cd_mon': cd_mon})
|
||
|
||
def update_pla_stage_by_semaine(request, type_stage, semaine, groupe, agence, valide, cd_uti):
|
||
query = "UPDATE pla_stage SET AGENCE=:agence, VALIDE=:valide, CD_UTI=:cd_uti WHERE TYPE=:type AND SEMAINE=:semaine AND GROUPE=:groupe;"
|
||
execute_query(request, query, {"semaine": semaine, 'groupe': groupe,
|
||
'type': type_stage, 'agence': agence, 'valide': valide, 'cd_uti': cd_uti})
|
||
|
||
def update_pla_moto_by_date(request, type_stage, date, groupe, agence, valide, cd_uti):
|
||
query = "UPDATE pla_moto SET AGENCE=:agence, VALIDE=:valide, CD_UTI=:cd_uti WHERE TYPE=:type AND DATE=:date AND GROUPE=:groupe;"
|
||
execute_query(request, query, {"date": date, 'groupe': groupe,
|
||
'type': type_stage, 'agence': agence, 'valide': valide, 'cd_uti': cd_uti})
|
||
|
||
def delete_pla_stage_by_semaine(request, type_stage, semaine, groupe):
|
||
query = "DELETE FROM pla_stage WHERE TYPE=:type AND SEMAINE=:semaine AND GROUPE=:groupe;"
|
||
execute_query(request, query, {
|
||
"semaine": semaine, 'groupe': groupe, 'type': type_stage})
|
||
|
||
def delete_pla_moto_by_date(request, type_stage, date, groupe):
|
||
query = "DELETE FROM pla_moto WHERE TYPE=:type AND DATE=:date AND GROUPE=:groupe;"
|
||
execute_query(request, query, {"date": date,
|
||
'groupe': groupe, 'type': type_stage})
|
||
|
||
|
||
def controleSoldeResa(request, cd_cli, ref):
|
||
if cd_cli > 100000:
|
||
# Controler le solde de l'élève
|
||
query = "CALL spCTL_SOLDE_RESA(:cd_cli, :ref)"
|
||
results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'ref': ref}).first()
|
||
return results.resultat
|
||
else:
|
||
return 0
|
||
|
||
def controleDateExport(request, dateRdv, agence):
|
||
date = datetime.strptime(dateRdv, '%Y-%m-%d').date()
|
||
# lire la date dernière export en compta
|
||
query = "SELECT * FROM p_agences WHERE code = 6;"
|
||
result = request.dbsession.execute(query).first()
|
||
if date <= result.DERN_EXPORT:
|
||
return False
|
||
else:
|
||
query = "SELECT * FROM p_agences WHERE code = :agence;"
|
||
result = request.dbsession.execute(query, {'agence': agence}).first()
|
||
if date <= result.DERN_VALIDATION:
|
||
return False
|
||
else:
|
||
return True
|
||
|
||
def controlAffectationEtAgece(request, cd_mon, dateRDV, heureRDV, agence):
|
||
query = """SELECT * FROM moniteurs_aff WHERE cd_mon = :cd_mon AND agence=:agence order by DATEF ASC"""
|
||
results = request.dbsession.execute(
|
||
query, {'cd_mon': cd_mon, 'agence': agence})
|
||
dateRDV = datetime.strptime(dateRDV, '%Y-%m-%d').date()
|
||
lastdate = None
|
||
for result in results:
|
||
lastdate = result
|
||
dateFin = result['DATEF']
|
||
dateDebut = result['DATED']
|
||
if dateDebut <= dateRDV <= dateFin:
|
||
isMatin = time(int(result['DMATIN']), 0) <= time(
|
||
int(heureRDV), 0) <= time(int(result['FMATIN']), 0)
|
||
isSoir = time(int(result['DSOIR']), 0) <= time(
|
||
int(heureRDV), 0) <= time(int(result['FSOIR']), 0)
|
||
if (isMatin or isSoir):
|
||
return True, result
|
||
else:
|
||
lastdate = None
|
||
|
||
return False, lastdate
|
||
|
||
def PlanningHasPending(request, type, date, groupe):
|
||
# y a-t-il des élèves provisoires dans le stage ?
|
||
query = "CALL spGet_PLA_MOTO_LIGNES(:type, :date, :groupe, -1)"
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'date': date, 'groupe': groupe}).fetchall()
|
||
if len(results) > 0:
|
||
return True
|
||
else:
|
||
return False
|
||
|
||
def StageHasPending(request, type, semaine, groupe):
|
||
# y a-t-il des élèves provisoires dans le stage ?
|
||
query = "CALL spGet_PLA_STAGE_PROVISOIRES(:type, :semaine, :groupe)"
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'semaine': semaine, 'groupe': groupe}).fetchall()
|
||
if len(results) > 0:
|
||
return True
|
||
else:
|
||
return False
|
||
|
||
|
||
def planningHasPending(request, type_stage, date, groupe):
|
||
query = "SELECT s.* FROM pla_moto_lignes s INNER JOIN eleves e ON s.cd_cli = e.cd_cli WHERE s.type=:type AND s.date=:date AND s.groupe=:groupe AND e.agence = 0 ORDER BY s.cd_cli;"
|
||
results = request.dbsession.execute(
|
||
query, {'type': type, 'date': date, 'groupe': groupe}).fetchall()
|
||
if len(results) > 0:
|
||
return True
|
||
else:
|
||
return False
|
||
|
||
def get_examens_prevu(request, cd_cli):
|
||
# lire l'examen prévu de l'élève
|
||
query = "SELECT * FROM examens_aff WHERE cd_cli = :cd_cli AND date > CURRENT_DATE();"
|
||
results = request.dbsession.execute(query, {'cd_cli': cd_cli}).first()
|
||
return results
|
||
|
||
def get_ref_by_stage_type(request, type_stage):
|
||
query = """ SELECT DISTINCT REF FROM pla_stage_lignes WHERE TYPE=:type AND REF IS NOT NULL AND REF != ""; """
|
||
results = request.dbsession.execute(query, {'type': type_stage}).fetchall()
|
||
return results
|
||
|
||
def get_ref_by_motos_type(request, type_stage):
|
||
query = """ SELECT DISTINCT REF FROM pla_moto_lignes WHERE TYPE=:type AND REF IS NOT NULL AND REF != ""; """
|
||
results = request.dbsession.execute(query, {'type': type_stage}).fetchall()
|
||
return results
|
||
|
||
def get_p_semaines(request, type_planning):
|
||
query = "CALL spGet_P_SEMAINES(:type_planning);"
|
||
results = request.dbsession.execute(query, {'type_planning': type_planning}).fetchall()
|
||
return results
|
||
|
||
def insert_semaine_type(request, cd_mon, noSemaine, dateDepart, nbSemaine, increment, agence, logged_in):
|
||
# insérer la semaine-type noSemaine par QTE fois à partir de la date dateDepart
|
||
n = 1
|
||
for i in range(nbSemaine):
|
||
# lire la semaine type
|
||
query = "SELECT * FROM p_semaine_type WHERE code_sem = :noSemaine;"
|
||
semaines = request.dbsession.execute(query, {'noSemaine': noSemaine}).fetchall()
|
||
|
||
for semaine in semaines:
|
||
dateType = semaine.dh_debut
|
||
dateHeure = dateDepart + relativedelta(days=dateType.weekday() - 1)
|
||
|
||
# l'heure existe déjà dans le planning ?
|
||
query = "SELECT DATE FROM eleves_cpt WHERE agence=:agence AND date=:date AND noplan=:noplan AND cd_mon=:cd_mon;"
|
||
item = request.dbsession.execute(query, {'agence': agence, "date": dateHeure.strftime('%Y-%m-%d'),
|
||
'noplan': dateType.hour, 'cd_mon': cd_mon}).fetchall()
|
||
if len(item) == 0:
|
||
# non, créer l'heure
|
||
query = """REPLACE INTO eleves_cpt (date, noplan, cd_cli, intitule, nom, qte, ref, cd_mon, agence, cd_uti) VALUES
|
||
(:date, :noplan, :cd_cli, :intitule, :intitule, :qte, :cd_cli, :cd_mon, :agence, :logged_in);"""
|
||
execute_query(request, query, {"date": dateHeure.strftime('%Y-%m-%d'),'noplan': dateType.hour,
|
||
'cd_cli': semaine.ACTIVITE, 'intitule': semaine.INTITULE, 'qte': semaine.qte,
|
||
'cd_mon': cd_mon, 'agence': agence, 'logged_in': logged_in} )
|
||
|
||
# changer de semaine
|
||
n = n + increment
|
||
dateDepart = dateDepart + relativedelta(days=7 * increment)
|
||
|
||
def delete_pla_moto_by_cd_cli(request, type_stage, groupe, date, cd_cli):
|
||
query = "DELETE FROM pla_moto_lignes WHERE TYPE=:type AND DATE=:date AND GROUPE=:groupe AND CD_CLI=:cd_cli; "
|
||
execute_query(request, query, {
|
||
"date": date, 'groupe': groupe, 'type': type_stage, 'cd_cli': cd_cli})
|
||
|
||
def valoriser_heure(request, cd_cli):
|
||
query = "CALL spUpd_PLANNING_B_VALORISER(:cd_cli);"
|
||
execute_query(request, query, {'cd_cli': cd_cli})
|
||
|
||
def validate_heure_rdvb(request, no_ligne, date, qte, statut, cd_cli, cd_mon, agence):
|
||
query = """ UPDATE eleves_cpt SET DATE_VALEUR=:date, fin_reservation=NULL, QTE=:qte, STATUT=:statut WHERE no_ligne=:no_ligne """
|
||
execute_query(request, query, {
|
||
'no_ligne': no_ligne, 'date': date, 'qte': qte, 'statut': statut})
|
||
if str(statut) == '8':
|
||
query2 = """ "CALL spUpd_ATTENTES('B',:cd_cli, 1, True ,:cd_mon,'AEM') """
|
||
execute_query(request, query2, {'cd_cli': cd_cli, 'cd_mon': cd_mon})
|
||
query3 = """ CALL spUPD_LAST_VALIDATION(:agence,:date) """
|
||
execute_query(request, query3, {'agence': agence, 'date': date})
|
||
|
||
def get_eleve_cpt(request, date, agence):
|
||
query = """ SELECT * FROM eleves_cpt WHERE DATE=:date AND AGENCE=:agence AND NOPLAN <> 0 AND DATE_VALEUR is NULL"""
|
||
results = request.dbsession.execute(
|
||
query, {"date": date, 'agence': agence}).fetchall()
|
||
return results
|
||
|
||
def get_rendez_vous_b_debut_apres_now(request, agence, date):
|
||
datetimenow = datetime.now()
|
||
query = """ SELECT e.* FROM eleves_cpt e WHERE e.NoPlan <>0 AND e.REF IN ('HCB', 'HCB78') AND CONVERT(concat(CONVERT(e.DATE,DATE),' ',CONVERT(concat(e.NoPlan,':00:00'), TIME)), DATETIME) >= :datetime AND e.AGENCE = :agence AND e.DATE=:date;"""
|
||
results = request.dbsession.execute(
|
||
query, {'agence': agence, 'date': date, 'datetime': datetimenow}).fetchall()
|
||
return results |