220 lines
10 KiB
Python
220 lines
10 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 dateutil.relativedelta
|
|
import transaction
|
|
|
|
def execute_query(request, query, params):
|
|
"""Execute query and mark session as changed"""
|
|
request.dbsession.execute(query, params)
|
|
mark_changed(request.dbsession)
|
|
transaction.commit()
|
|
|
|
def insert_log(request, proc, msg):
|
|
query = "INSERT t_log_nuit (proc,msg) VALUES (:proc,:msg);"
|
|
execute_query(request, query, {'proc': proc, 'msg' :msg})
|
|
|
|
def truncate_log(request):
|
|
query = "TRUNCATE t_log_nuit;"
|
|
execute_query(request, query, {})
|
|
|
|
def get_log(request):
|
|
# lire les rappels non envoyés
|
|
query = "SELECT * FROM t_log_nuit;"
|
|
results = request.dbsession.execute(query, {}).fetchall()
|
|
return results
|
|
|
|
def get_email_rappels(request):
|
|
# lire les rappels non envoyés
|
|
query = "SELECT r.*, e.email, e.payeur_nom, e.payeur_email FROM email_rappels r INNER JOIN eleves e ON r.cd_cli = e.cd_cli WHERE ISNULL(envoye_le) ORDER BY no_id;"
|
|
results = request.dbsession.execute(query, {}).fetchall()
|
|
return results
|
|
|
|
def get_email_resa(request, type):
|
|
# lire les rappels non envoyés
|
|
query = "CALL spGet_EMAIL_RESA(:type);"
|
|
results = request.dbsession.execute(query, {'type': type}).fetchall()
|
|
return results
|
|
|
|
def get_reservations(request):
|
|
# lire les réservation non WEB dans planning A et B
|
|
query = """
|
|
SELECT c.ref, c.date, c.fin_reservation, c.cd_cli, c.no_ligne, c.noplan, c.circuit, c.debit FROM eleves_cpt c
|
|
WHERE NOT isnull(fin_reservation) AND ref IN ("HCB", "HCB78" ,"HCA3", "HCA4", "TA", "TB","TB78" ) AND cd_uti <> 'WEB' ORDER BY date;"""
|
|
results = request.dbsession.execute(query, {}).fetchall()
|
|
return results
|
|
|
|
def update_rappels(request):
|
|
query = "CALL spUPD_RAPPELS();"
|
|
execute_query(request, query, {})
|
|
|
|
def update_email_rappels(request, no_id):
|
|
query = "UPDATE email_rappels SET envoye_le = NOW() WHERE no_id = :no_id;"
|
|
execute_query(request, query, {'no_id': no_id})
|
|
|
|
def update_email_resa(request, no_id):
|
|
query = "UPDATE email_resa SET envoye_le = NOW() WHERE no_id = :no_id;"
|
|
execute_query(request, query, {'no_id': no_id})
|
|
|
|
def get_societes(request, societe):
|
|
if societe == '0':
|
|
query = "SELECT * FROM p_societe;"
|
|
results = request.dbsession.execute(query, {'societe': societe}).fetchall()
|
|
else:
|
|
query = "SELECT * FROM p_societe WHERE societe = :societe;"
|
|
results = request.dbsession.execute(query, {'societe': societe}).first()
|
|
return results
|
|
|
|
def get_message(request, ref):
|
|
query = "SELECT * FROM tarifs WHERE ref = :ref;"
|
|
results = request.dbsession.execute(query, {'ref': ref}).first()
|
|
return results
|
|
|
|
def update_planning_B_confirm(request, action, no_ligne, cd_cli):
|
|
if action == "C":
|
|
# confirmer la resa
|
|
query = "UPDATE eleves_cpt SET fin_reservation = NULL WHERE no_ligne = :no_ligne;"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
else:
|
|
# annuler la resa
|
|
query = """UPDATE eleves_cpt SET debit=0, mtval=0, qte=0, fin_reservation = NULL,
|
|
intitule=CONCAT('Résa ', ref, ' de ', noplan, 'h expirée'), ref=NULL, noplan=0 WHERE no_ligne = :no_ligne;
|
|
"""
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
# recalculer le solde de l'élève
|
|
query = "CALL spUPD_ELEVES_SOLDE(:cd_cli);"
|
|
execute_query(request, query, {'cd_cli':cd_cli})
|
|
|
|
|
|
def insert_email_resa(request, ref, cd_cli, type, date, heure, statut):
|
|
query = """INSERT INTO email_resa (ref, cd_cli, resa_date, resa_type, resa_statut)
|
|
VALUES (:ref, :cd_cli, DATE_ADD(:date, INTERVAL :heure HOUR), :type, :statut);"""
|
|
execute_query(request, query, {'ref': ref, 'cd_cli': cd_cli, 'type': type, 'date': date, 'heure': heure, 'statut': statut})
|
|
|
|
def get_solde_eleve(request, cd_cli, ref):
|
|
# 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
|
|
|
|
def update_planning_A_confirm(request, action, no_ligne, cd_cli):
|
|
if action == "C":
|
|
# confirmer la resa dans le compte
|
|
query = "UPDATE eleves_cpt SET fin_reservation = NULL WHERE no_ligne = :no_ligne;"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
# confirmer la resa dans le planning
|
|
query = "UPDATE pla_moto_lignes SET fin_reservation = NULL WHERE ligne_cpt = :no_ligne;"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
else:
|
|
# annuler la resa dans le compte
|
|
query = """UPDATE eleves_cpt SET debit=0, mtval=0, qte=0, fin_reservation = NULL,
|
|
intitule=CONCAT('Résa ', ref, ' expirée'), ref=NULL, noplan=0 WHERE no_ligne = :no_ligne;"""
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
# recalculer le solde
|
|
query = "CALL spUPD_ELEVES_SOLDE(:cd_cli);"
|
|
execute_query(request, query, {'cd_cli':cd_cli})
|
|
# annuler la resa dans le planning
|
|
query = "DELETE FROM pla_moto_lignes WHERE ligne_cpt = :no_ligne;"
|
|
execute_query(request, query, {'no_ligne': no_ligne})
|
|
|
|
def purge_obsoletes(request):
|
|
|
|
# ----- Purger les anciens TICKETS_CB de plus de 6 mois
|
|
query = "DELETE FROM tickets_cb WHERE date < DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);"
|
|
execute_query(request, query, {})
|
|
# ----- Purger les anciens notifications de plus de 6 mois
|
|
query = "DELETE FROM email_resa WHERE date(cree_le) < DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);"
|
|
execute_query(request, query, {})
|
|
# ----- Purger les les anciens notifications de rappels de plus de 6 mois
|
|
query = "DELETE FROM email_rappels WHERE date(cree_le) < DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);"
|
|
execute_query(request, query, {})
|
|
# ----- supprimer les fiches PROSPECTS inactives et cloturees antérieures de 5 années
|
|
query = "DELETE FROM prospects WHERE modif_le < DATE_SUB(CURRENT_DATE(), INTERVAL 5 YEAR);"
|
|
execute_query(request, query, {})
|
|
|
|
|
|
# -- Purger les anciens /////
|
|
query = "DELETE FROM eleves_cpt WHERE date < DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) AND ref = '1';"
|
|
execute_query(request, query, {})
|
|
# -- supprimer les activités du plannings B antérieurs de 4 années
|
|
query = "DELETE FROM eleves_cpt WHERE cd_cli < 100000 and date < DATE_SUB(CURRENT_DATE(), INTERVAL 4 YEAR);"
|
|
execute_query(request, query, {})
|
|
# -- Purger du Planning B les rdv annulés, remplacés ou expirés de plus de 4 années
|
|
query = """DELETE FROM eleves_cpt WHERE date < DATE_SUB(CURRENT_DATE(), INTERVAL 4 YEAR) AND qte=0
|
|
AND (INSTR(intitule,'annulé') OR INSTR(intitule,'remplacé') OR INSTR(intitule,'expiré'));"""
|
|
execute_query(request, query, {})
|
|
|
|
# -- supprimer les plannings MOTO de + de 3 ans
|
|
query = """DELETE A.* FROM pla_moto A LEFT OUTER JOIN pla_moto_lignes B
|
|
ON A.type=B.type AND A.date=B.date AND A.groupe=B.groupe
|
|
WHERE B.date IS NULL and A.date < DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR);"""
|
|
execute_query(request, query, {})
|
|
# -- supprimer les plannings Stage antérieurs de 4 années
|
|
query = """DELETE A.* FROM pla_stage A LEFT OUTER JOIN pla_stage_lignes B
|
|
ON A.type=B.type AND A.semaine=B.semaine AND A.groupe=B.groupe
|
|
WHERE B.semaine IS NULL and A.modif_le < DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR);"""
|
|
execute_query(request, query, {})
|
|
# -- supprimer les examens sans inscrits
|
|
query = """DELETE A.* FROM examens A LEFT OUTER JOIN examens_aff B
|
|
ON A.date=B.date AND A.no_exa=B.no_exa AND A.agence=B.agence
|
|
WHERE B.date IS NULL and A.date < DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR);"""
|
|
execute_query(request, query, {})
|
|
|
|
# -- supprimer les affectations de moniteurs - 5 ans
|
|
query = "DELETE FROM bd_aem.moniteurs_aff WHERE year(datef) < year(CURRENT_DATE) - 5;"
|
|
execute_query(request, query, {})
|
|
# -- supprimer les moniteurs sans affectations
|
|
query = """DELETE A.* FROM moniteurs A LEFT OUTER JOIN moniteurs_aff B ON A.cd_mon=B.cd_mon
|
|
WHERE B.agence IS NULL and A.modif_le < DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR);"""
|
|
execute_query(request, query, {})
|
|
|
|
# -- effacer les demandes de mot de passe oubliés
|
|
query = "UPDATE eleves SET mdp_oublie=NULL, mdp_oublie_date=NULL WHERE mdp_oublie_date < DATE_SUB(NOW(), INTERVAL 2 day);"
|
|
execute_query(request, query, {})
|
|
# -- MAJ la date de la dernière opération sur lélève
|
|
query = """UPDATE eleves
|
|
SET dern_ope_le = (SELECT max(date) FROM eleves_cpt WHERE cd_cli = eleves.cd_cli)
|
|
WHERE CD_CLI > 100000;"""
|
|
execute_query(request, query, {})
|
|
|
|
|
|
def update_statistiques(request):
|
|
# ----- MAJ de la table statistiques CHARTS_DATA mois M
|
|
query = "CALL spUPD_CHARTS_DATA(CURRENT_DATE)"
|
|
execute_query(request, query, {})
|
|
# ----- MAJ de la table statistiques CHARTS_DATA mois M-1
|
|
query = "CALL spUPD_CHARTS_DATA(DATE_SUB(current_date, INTERVAL 1 MONTH))"
|
|
execute_query(request, query, {})
|
|
# ----- MAJ de la table statistiques CHARTS_DATA2 mois M-1
|
|
query = "CALL spUPD_CHARTS_DATA2(DATE_SUB(current_date, INTERVAL 1 MONTH))"
|
|
execute_query(request, query, {})
|
|
|
|
def lettrage_auto(request):
|
|
# parcourir les élève ACTIFS
|
|
query = "SELECT cd_cli FROM eleves WHERE cd_cli > 100000 AND statut < 10;"
|
|
results = request.dbsession.execute(query, {}).fetchall()
|
|
|
|
for item in results:
|
|
# lettrer toutes les écritures à cette date
|
|
query = "CALL spUPD_ELEVES_LETTRER(:cd_cli);"
|
|
execute_query(request, query, {'cd_cli': item.cd_cli})
|
|
return len(results)
|
|
|
|
def get_justify_not_found(request,date):
|
|
query = """SELECT * FROM eleves_upload WHERE cree_le >= :date AND nom_fic IS NOT NULL AND taille_fic <> 0 """
|
|
results = request.dbsession.execute(query, {'date':date}).fetchall()
|
|
return results
|
|
|
|
def update_justify_not_found(request,no_ligne):
|
|
query = """UPDATE eleves_upload SET nom_fic=NULL, taille_fic=0, valide=0 WHERE no_ligne=:no_ligne"""
|
|
execute_query(request,query, {'no_ligne':no_ligne}) |