361 lines
20 KiB
Python
361 lines
20 KiB
Python
# -*- coding: utf8 -*-
|
|
from .default import (
|
|
execute_query,
|
|
)
|
|
|
|
def generer_brouillard(request, logged_in, date_fin):
|
|
""" Générer le brouillard jusqu'à la date de fin
|
|
|
|
PHASE 1 :
|
|
- Lire les comptes clients valorisés dans la période
|
|
- Cumuler les écritures de réglement et de ventes par TVA et Agences dans T_ECRITURES
|
|
|
|
PHASE 2 :
|
|
- remplir le libellé pour des comptes de ventes
|
|
|
|
PHASE 3 :
|
|
- Générer les écritures dans T_SYBEL"
|
|
"""
|
|
message = ""
|
|
mtTVA_encaisse = 0
|
|
|
|
# RAZ la table T_SYBEL pour extraire les écritures comptable
|
|
delete_t_sybel(request, logged_in)
|
|
|
|
# lire la societe CERRA pour mémoriser ses paramètres
|
|
query = "CALL spGet_SOCIETES('C');"
|
|
soc = request.dbsession.execute(query).first()
|
|
szCompteAux = soc.CPTE_AUX
|
|
szCompteTVA = soc.CPTE_TVA
|
|
szJournalVente = soc.JNL_VTE
|
|
|
|
query = """SELECT c.*, t.taux as TTVA, a.JNL_BNQ, a.CPT_BNQ, a.CENTRE FROM eleves_cpt c
|
|
INNER JOIN p_tva t ON c.ctva = t.code
|
|
INNER JOIN p_agences a ON c.agence = a.code
|
|
WHERE c.exporte_le IS NULL AND c.date_valeur <= :date_fin AND societe='C' AND cd_cli > 100000
|
|
AND c.credit + c.mtval <> 0 ORDER BY c.date_valeur"""
|
|
ecritures = request.dbsession.execute(query, {'date_fin': date_fin.strftime("%Y-%m-%d")}).fetchall()
|
|
|
|
for ecr in ecritures:
|
|
# contrôler la date de valeur : ne doit pas être en dehors du mois sélectionné
|
|
dateValeur = ecr.DATE_VALEUR
|
|
if dateValeur.month != date_fin.month :
|
|
message = "Une écriture du %s de %s - %s est hors des limites de l'export. Impossible de continuer." % \
|
|
(dateValeur.strftime("%d-%m-%Y"), ecr.CD_CLI, ecr.NOM)
|
|
return message
|
|
|
|
# origine de l'élève
|
|
nOrigine = str(ecr.CD_CLI)[0:1]
|
|
sDateValeur = dateValeur.strftime("%Y-%m-%d")
|
|
|
|
# écriture de réglement
|
|
if ecr.MODE_REGL > 0:
|
|
# règlement ESP, CHQ, CB, CBW ?
|
|
# import pdb;pdb.set_trace()
|
|
if ecr.MODE_REGL <= 4:
|
|
query = "CALL spUPD_T_ECRITURES(:logged_in, 'BNQ_D', :agence, :mode_regl,0, :date_valeur, :credit);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'agence': ecr.AGENCE, 'mode_regl': ecr.MODE_REGL,
|
|
'date_valeur': sDateValeur, 'credit': ecr.CREDIT})
|
|
# TVA exo
|
|
# query = "CALL spUPD_T_ECRITURES(:logged_in, 'BNQ_C', :agence, 0, :origine, :date_valeur, :credit);"
|
|
# execute_query(request, query, {'logged_in': logged_in, 'agence': ecr.AGENCE, 'origine': nOrigine,
|
|
# 'date_valeur': sDateValeur, 'credit': ecr.MT2})
|
|
|
|
# TVA 1
|
|
query = "CALL spUPD_T_ECRITURES(:logged_in, 'BNQ_C', :agence, 1, :origine, :date_valeur, :credit);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'agence': ecr.AGENCE, 'origine': nOrigine,
|
|
'date_valeur': sDateValeur, 'credit': ecr.MT3})
|
|
# TVA 2
|
|
query = "CALL spUPD_T_ECRITURES(:logged_in, 'BNQ_C', :agence, 2, :origine, :date_valeur, :credit);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'agence': ecr.AGENCE, 'origine': nOrigine,
|
|
'date_valeur': sDateValeur, 'credit': ecr.MT1})
|
|
|
|
# Mode=5 : Prêt 1 € ?
|
|
if ecr.MODE_REGL == 5:
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '13', :date_valeur, :compte, :centre, :lib, :credit, 'C', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '411000',
|
|
'centre': 'CNORM' + nOrigine, 'lib': ecr.INTITULE[:25], 'credit': ecr.CREDIT, 'ref': 'VIRT' + str(ecr.CD_CLI)})
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '13', :date_valeur, :compte, :centre, :lib, :credit, 'D', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '512200',
|
|
'centre': 'CNORM' + nOrigine, 'lib': ecr.INTITULE[:25], 'credit': ecr.CREDIT, 'ref': 'VIRT' + str(ecr.CD_CLI)})
|
|
|
|
# Mode=6 : virement compte à compte ?
|
|
if ecr.MODE_REGL == 6:
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '12', :date_valeur, :compte, :centre, :lib, :credit, 'C', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '411000',
|
|
'centre': 'CNORM' + nOrigine, 'lib': ecr.INTITULE[:25], 'credit': ecr.CREDIT, 'ref': 'VIRT' + str(ecr.CD_CLI)})
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '12', :date_valeur, :compte, :centre, :lib, :credit, 'D', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '581000',
|
|
'centre': 'CNORM' + nOrigine, 'lib': ecr.INTITULE[:25], 'credit': ecr.CREDIT, 'ref': 'VIRT' + str(ecr.CD_CLI)})
|
|
# Mode=7 : virement ?
|
|
if ecr.MODE_REGL == 7:
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '10', :date_valeur, :compte, :centre, :lib, :credit, 'C', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '411000',
|
|
'centre': 'CNORM' + nOrigine, 'lib': ecr.INTITULE[:25], 'credit': ecr.CREDIT, 'ref': 'VIRT' + str(ecr.CD_CLI)})
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '10', :date_valeur, :compte, :centre, :lib, :credit, 'D', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '512000',
|
|
'centre': 'CNORM' + nOrigine, 'lib': ecr.INTITULE[:25], 'credit': ecr.CREDIT, 'ref': 'VIRT' + str(ecr.CD_CLI)})
|
|
# Mode=8 : remboursement ?
|
|
if ecr.MODE_REGL == 8:
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '13', :date_valeur, :compte, :centre, :lib, :credit, 'C', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '411000',
|
|
'centre': 'CNORM' + nOrigine, 'lib':'REMBOURSEMENT ' + ecr.NOM[:11], 'credit': ecr.CREDIT, 'ref': 'REMB' + str(ecr.CD_CLI)})
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', '13', :date_valeur, :compte, :centre, :lib, :credit, 'D', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'date_valeur': sDateValeur, 'compte': '512000',
|
|
'centre': 'CNORM' + nOrigine, 'lib': 'REMBOURSEMENT ' + ecr.NOM[:11], 'credit': ecr.CREDIT, 'ref': 'REMB' + str(ecr.CD_CLI)})
|
|
# Mode=9 : impayés ?
|
|
if ecr.MODE_REGL == 9:
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', :jnl_bnq, :date_valeur, :compte, :centre, :lib, :credit, 'C', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'jnl_bnq': ecr.JNL_BNQ, 'date_valeur': sDateValeur, 'compte': '411000',
|
|
'centre': 'CNORM' + nOrigine, 'lib': 'IMPAYE ' + ecr.NOM[:18], 'credit': ecr.CREDIT, 'ref': 'IMPA' + str(ecr.CD_CLI)})
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', :jnl_bnq, :date_valeur, :compte, :centre, :lib, :credit, 'D', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'jnl_bnq': ecr.JNL_BNQ, 'date_valeur': sDateValeur, 'compte': ecr.CPT_BNQ,
|
|
'centre': 'CNORM' + nOrigine, 'lib': 'IMPAYE ' + ecr.NOM[:18], 'credit': ecr.CREDIT, 'ref': 'IMPA' + str(ecr.CD_CLI)})
|
|
|
|
else:
|
|
# === cumuler les ecritures de VENTES si no compte saisi
|
|
if ecr.COMPTE:
|
|
# import pdb;pdb.set_trace()
|
|
# cumuler ecriture de vente
|
|
query = "CALL spUPD_T_ECRITURES(:logged_in, 'VTE_D', :origine, :ctva, 0, :date_valeur, :mtval);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'origine': nOrigine, 'ctva': ecr.CTVA,
|
|
'date_valeur': date_fin.strftime("%Y-%m-%d"), 'mtval': ecr.MTVAL})
|
|
# compte déja crée ?
|
|
query = "CALL spGET_T_SYBEL(:logged_in,'C', :vte, :date_fin, :compte, :centre);"
|
|
item = request.dbsession.execute(query, {'logged_in': logged_in, 'vte': szJournalVente, 'date_fin': date_fin.strftime("%Y-%m-%d"),
|
|
'compte': ecr.COMPTE, 'centre': ecr.CENTRE}).first()
|
|
if item:
|
|
OldMontant = item.MONTANT
|
|
else:
|
|
# non, creer
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', :jnl, :date_valeur, :compte, :centre, :lib, :credit, 'C', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'jnl': szJournalVente, 'date_valeur': date_fin.strftime("%Y-%m-%d"),
|
|
'compte': ecr.COMPTE, 'centre': ecr.CENTRE, 'lib': ecr.INTITULE[:25], 'credit': 0, 'ref': 'AEM-' + date_fin.strftime("%d%m%y")})
|
|
|
|
OldMontant = 0
|
|
|
|
# cumuler le montant HT et cumuler le total TVA
|
|
mtHT = round(ecr.MTVAL / (1 + (ecr.TTVA / 100)), 2)
|
|
mtTVA_encaisse += ecr.MTVAL- mtHT
|
|
|
|
query = "CALL spUPD_T_SYBEL(:logged_in, 'C', :jnl, :date_valeur, :compte, :centre, :credit);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'jnl': szJournalVente, 'date_valeur': date_fin.strftime("%Y-%m-%d"),
|
|
'compte': ecr.COMPTE, 'centre': ecr.CENTRE, 'credit': OldMontant + mtHT})
|
|
|
|
# PHASE 2 :
|
|
# - remplir le libellé pour des comptes de ventes
|
|
|
|
query = "UPDATE t_sybel INNER JOIN p_comptes c ON t_sybel.compte = c.code SET t_sybel.libelle = c.libelle WHERE t_sybel.compte > 700000;"
|
|
execute_query(request, query, {})
|
|
|
|
# remplir le libellé pour le type 'BNQ_D'
|
|
query = """UPDATE t_ecritures t INNER JOIN P_AGENCES a ON t.agence = a.code INNER JOIN P_MODE_REGL m ON t.code1 = m.code
|
|
SET t.libelle = CONCAT('REM ', m.lib4, ' Ag:', t.agence), t.compte = a.cpt_bnq, t.journal=a.jnl_bnq
|
|
WHERE user = :logged_in AND t.type='BNQ_D';"""
|
|
execute_query(request, query, {'logged_in': logged_in})
|
|
|
|
# import pdb; pdb.set_trace()
|
|
# remplir le libellé pour le type 'BNQ_C'
|
|
query = """UPDATE t_ecritures t INNER JOIN P_AGENCES a ON t.agence = a.code INNER JOIN P_TVA m ON t.code1 = m.code
|
|
SET t.libelle = CONCAT('REGLEM ', m.lib4, ' Ag:', t.agence), t.compte = CONCAT('C',m.lib4,t.code2), t.journal=a.jnl_bnq
|
|
WHERE user = :logged_in AND t.type='BNQ_C'; """
|
|
execute_query(request, query, {'logged_in': logged_in})
|
|
|
|
# remplir le libellé pour le type 'VTE_D'
|
|
query = """UPDATE t_ecritures t INNER JOIN P_AGENCES a ON t.agence = a.code INNER JOIN P_TVA m ON t.code1 = m.code
|
|
SET t.libelle = CONCAT('VENTE ', m.lib4, ' Ag:', t.agence), t.compte = CONCAT('C',m.lib4,t.agence), t.journal=a.jnl_bnq
|
|
WHERE user = :logged_in AND t.type='VTE_D';"""
|
|
execute_query(request, query, {'logged_in': logged_in})
|
|
|
|
# PHASE 3 :
|
|
|
|
# - Générer les écritures dans T_SYBEL"
|
|
query = "CALL spGEN_T_SYBEL(:logged_in, 'C', :jnl, :compte, :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'jnl': szJournalVente, 'compte': szCompteAux, 'ref': 'AEM-' + date_fin.strftime("%d%m%y")})
|
|
|
|
# - Générer la ligne TVA ENCAISSE
|
|
query = "CALL spINS_T_SYBEL(:logged_in, 'C', :jnl, :date_valeur, :compte, '', 'TVA COLLECTE', :credit, 'C', :ref);"
|
|
execute_query(request, query, {'logged_in': logged_in, 'jnl': szJournalVente, 'date_valeur': date_fin.strftime("%Y-%m-%d"), 'compte': szCompteTVA,
|
|
'credit': mtTVA_encaisse, 'ref': 'AEM-' + date_fin.strftime("%d%m%y")})
|
|
|
|
return message
|
|
|
|
def get_brouillard(request, logged_in):
|
|
query = "SELECT * FROM t_sybel WHERE user=:logged_in ORDER BY journal, date, compte, centre;";
|
|
results = request.dbsession.execute(query, {'logged_in': logged_in})
|
|
return results.fetchall()
|
|
|
|
def generer_export(request, logged_in, path_fichier):
|
|
|
|
# ouvrir le fichier export compta, encodage ANSI
|
|
fichier = open(path_fichier, mode="w", newline='\r\n', encoding="cp1252")
|
|
|
|
# ecrire la ligne d'entete
|
|
fichier.write("CERRA MARIETTON" + '\n')
|
|
|
|
# Lire les écritures extraites
|
|
ecritures = get_brouillard(request, logged_in)
|
|
for ecr in ecritures:
|
|
# si montant < 0, le rendre positif et inverser le sens
|
|
montant = ecr.MONTANT
|
|
sens = ecr.SENS
|
|
if montant < 0:
|
|
montant *= -1
|
|
if sens == "C":
|
|
sens = "D"
|
|
else:
|
|
sens = "C"
|
|
|
|
# nouvelle ligne, longueur = 104 charactères
|
|
ligne1 = ''
|
|
# code Journal (3 car.)
|
|
ligne1 += ecr.JOURNAL.ljust(3)
|
|
# date de pièce (format jjmmaa)
|
|
ligne1 += ecr.DATE.strftime("%d%m%y")
|
|
# type de pièce (2 car.)
|
|
ligne1 += "OD"
|
|
# compte général (13 caractères)
|
|
ligne1 += ecr.COMPTE.ljust(13)
|
|
# compte auxilliare ou analytique
|
|
if len(ecr.CENTRE) > 0:
|
|
# compte auxilliaire ?
|
|
if ecr.COMPTE[:3] == "411":
|
|
ligne1 += "X" + ecr.CENTRE.ljust(13)
|
|
else:
|
|
ligne1 += " "*14
|
|
else:
|
|
ligne1 += " "*14
|
|
# Référence de l'écriture (13 caractères vides)
|
|
ligne1 += ecr.REFERENCE.ljust(13)
|
|
# libelle (25 caractères)
|
|
ligne1 += ecr.LIBELLE.ljust(26)
|
|
# date echeance (format jj/mm/aa)
|
|
ligne1 += ecr.DATE.strftime("%d%m%y")
|
|
# sens : D=Débit, C=Crédit)
|
|
ligne1 += sens
|
|
# montant (20 caractères)
|
|
szmontant = "%0.2f" % montant
|
|
ligne1 += szmontant.rjust(20)
|
|
# centre anal. et compte non auxilliaire ?
|
|
if len(ecr.CENTRE) > 0 and ecr.COMPTE[:3] != "411":
|
|
# écrire la ligne generale
|
|
fichier.write(ligne1 + 'N\n')
|
|
# puis préparer la ligne analytique
|
|
ligne1 = ligne1[0:24] + "A" + ecr.CENTRE.ljust(13) + ligne1[38:]
|
|
# ecrire la ligne 1
|
|
fichier.write(ligne1 + 'N\n')
|
|
|
|
fichier.close()
|
|
return
|
|
|
|
def update_cpt_export(request, date_fin):
|
|
# marquer les lignes de compte exportées
|
|
query = """UPDATE eleves_cpt SET modif_le=modif_le, exporte_le = CURRENT_DATE()
|
|
WHERE exporte_le IS NULL AND date_valeur <= :date_fin AND cd_cli > 100000 AND credit + mtval <> 0;"""
|
|
execute_query(request, query, {'date_fin': date_fin.strftime("%Y-%m-%d")})
|
|
|
|
def delete_t_sybel(request, userid):
|
|
"""supprimer le fichier extraction"""
|
|
query = "CALL spDel_T_SYBEL(:userid);"
|
|
execute_query(request, query, {'userid': userid})
|
|
|
|
def get_caisse(request, agence, date):
|
|
query = """SELECT c.*, e.permis_demande, CONCAT(m.code,'-',m.lib4) AS lib4 FROM eleves_cpt c
|
|
INNER JOIN eleves e ON c.cd_cli = e.cd_cli
|
|
INNER JOIN p_mode_regl m ON c.mode_regl = m.code
|
|
WHERE c.agence=:agence AND c.date=:date AND c.credit <> 0 ORDER BY c.mode_regl, c.cd_cli;"""
|
|
results = request.dbsession.execute(query, {'agence': agence, 'date': date.strftime('%Y-%m-%d')}).fetchall()
|
|
return results
|
|
|
|
def update_contrats_auto(request, agence, logged_in):
|
|
query = "CALL spUpd_SOLDER_CONTRAT_B(:agence, :logged_in);"
|
|
results = request.dbsession.execute(query, {'agence': agence, 'logged_in': logged_in})
|
|
return results.first()
|
|
|
|
def update_contrats_moto(request, agence, logged_in):
|
|
query = "CALL spUpd_SOLDER_CONTRAT_2R(:agence, :logged_in);"
|
|
results = request.dbsession.execute(query, {'agence': agence, 'logged_in': logged_in})
|
|
return results.first()
|
|
|
|
def generer_balance(request, logged_in, permis, date_debut, date_fin):
|
|
# Générer la balance CLIENTS
|
|
|
|
# Noter date heure du début et critères de la génération
|
|
criteres = permis + ', du ' + date_debut.strftime('%d-%m-%Y') + ' au ' + date_fin.strftime('%d-%m-%Y')
|
|
query = "UPDATE societes SET balance_debut=NOW(), balance_fin=NULL, balance_criteres=:criteres WHERE CODE = 'C';"
|
|
execute_query(request, query, {'criteres': criteres})
|
|
|
|
# RAZ la table T_G_LIVRE pour extraire les eleves de cet examen
|
|
query = "TRUNCATE t_g_livre;"
|
|
execute_query(request, query, {})
|
|
|
|
# Créer une ligne pour tous les ELEVES non cloturés, inactif et dont le compte est positif
|
|
query = """INSERT INTO t_g_livre (user,societe,cat,agence,cd_cli,intitule,formule,solde,credit,mtval)
|
|
SELECT :logged_in, societe, permis_demande, LEFT(CAST(cd_cli AS CHAR), 1), cd_cli, LEFT(nompren, 30), formule, 0, 0, 0
|
|
FROM ELEVES WHERE agence > 0 AND societe='C' AND permis_demande = :permis;"""
|
|
execute_query(request, query, {'logged_in': logged_in, 'permis': permis})
|
|
|
|
# MAJ le solde et le total des montants valeur et credit
|
|
query = "SELECT * FROM t_g_livre WHERE user = :logged_in;"
|
|
results = request.dbsession.execute(query, {'logged_in': logged_in}).fetchall()
|
|
|
|
for item in results:
|
|
# calculer le solde avant période
|
|
query = "SELECT COALESCE(SUM(credit-mtval),0) AS solde FROM eleves_cpt WHERE cd_cli=:cd_cli AND date_valeur < :date_debut;"
|
|
res = request.dbsession.execute(query, {'cd_cli': item.CD_CLI, 'date_debut': date_debut.strftime("%Y-%m-%d")}).first()
|
|
if res:
|
|
solde = res.solde
|
|
else:
|
|
solde = 0
|
|
|
|
# calculer les totaux credit et mtval de la période
|
|
query = """SELECT COALESCE(SUM(credit),0) AS credit, COALESCE(SUM(mtval),0) AS mtval
|
|
FROM eleves_cpt WHERE cd_cli=:cd_cli AND date_valeur >= :date_debut AND date_valeur <= :date_fin;"""
|
|
res = request.dbsession.execute(query, {'cd_cli': item.CD_CLI, 'date_debut': date_debut.strftime("%Y-%m-%d"), 'date_fin': date_fin.strftime("%Y-%m-%d")}).first()
|
|
if res:
|
|
credit = res.credit
|
|
mtval = res.mtval
|
|
else:
|
|
credit = 0
|
|
mtval = 0
|
|
|
|
if solde != 0 or credit != 0 or mtval != 0:
|
|
query = "UPDATE t_g_livre SET solde=:solde, credit=:credit, mtval=:mtval WHERE user=:logged_in AND cd_cli=:cd_cli;"
|
|
execute_query(request, query, {'logged_in': logged_in, 'cd_cli': item.CD_CLI,
|
|
'solde': solde, 'credit': credit, 'mtval': mtval})
|
|
else:
|
|
query = "DELETE FROM t_g_livre WHERE user=:logged_in AND cd_cli=:cd_cli;"
|
|
execute_query(request, query, {'logged_in': logged_in, 'cd_cli': item.CD_CLI})
|
|
|
|
# Noter date heure du début génération
|
|
query = "UPDATE societes SET balance_fin=NOW() WHERE CODE = 'C';"
|
|
execute_query(request, query, {})
|
|
|
|
def get_balance(request, logged_in):
|
|
query = "SELECT * FROM t_g_livre WHERE user = :logged_in ORDER BY user, agence, cd_cli;"
|
|
results = request.dbsession.execute(query, {'logged_in': logged_in}).fetchall()
|
|
return results
|
|
|
|
def get_recap(request, cd_mon1, cd_mon2, date1, date2):
|
|
# lire les activités de moniteurs de date à date
|
|
# ignorer les activités non imputables (NePasRelancer <> 0)
|
|
query = """
|
|
SELECT m.NOM, c.CD_MON, c.INTITULE, c.REF, c.STATUT, sum(c.QTE) AS SUM_QTE FROM eleves_cpt c
|
|
INNER JOIN moniteurs m ON c.CD_MON = m.CD_MON
|
|
INNER JOIN eleves e ON c.CD_CLI = e.CD_CLI
|
|
WHERE c.CD_MON >= :cd_mon1 AND c.CD_MON <= :cd_mon2 AND c.DATE >= :date1 AND c.DATE <= :date2 AND
|
|
c.NoPlan > 0 AND e.NePasRelancer = 0
|
|
GROUP BY m.NOM, c.REF, c.STATUT;"""
|
|
results = request.dbsession.execute(query, {'cd_mon1': cd_mon1, 'cd_mon2': cd_mon2, 'date1': date1 ,'date2': date2}).fetchall()
|
|
return results
|
|
|
|
def get_moniteurs_byCode(request):
|
|
query = "SELECT * FROM moniteurs ORDER BY cd_mon;"
|
|
results = request.dbsession.execute(query)
|
|
return results.fetchall()
|
|
|
|
def get_dates_balance(request):
|
|
query = "SELECT * FROM societes WHERE CODE = 'C';"
|
|
results = request.dbsession.execute(query).first()
|
|
return results.balance_debut, results.balance_fin, results.balance_criteres
|