scripts-pde/analytique2facture.py

'''

    Génère des factures dans la base Factures à partir des données de Tarification de Analytique

    @author: olivier.massot, févr. 2018
'''
from datetime import datetime
import logging
import string
import sys

from path import Path

from core import logconf
from core.model import Model
from core.pde import FacturesDb, AnalytiqueDb, CommunDb


logger = logging.getLogger("analytique2facture")
logconf.start("analytique2facture", logging.DEBUG)

class EnTete(Model):
    pass

class Ligne(Model):
    def __init__(self):
        # Valeurs fixes
        self.bytNbDecQuantite = 2
        self.dblCnvUniteCoef = 1
        self.bytNbDecQuantiteConvertie = 2
        self.bytNbDecPU = 2
        self.bytLigneSousTotal = 1
        self.intLigneIdRattachement = 0
        self.blnLigneVisible = True
        self.blnLigneGeneree = False
        self.bytGenerateurId = 0
        self.bytClasseRemiseArticleId = 0
        self.bytTVAArticleId = 0
        self.bytTVAId = 0
        self.dblTVATaux = 0
        self.bytUniteIdQuantite = 0
        self.bytUniteIdPrix = 0

class SqlFormatter(string.Formatter):
    def format_field(self, value, format_spec):
        if value is None:
            value = "Null"
            format_spec = ""
        if format_spec == "date":
            value = "#{:%Y-%m-%d %H:%M:%S}#".format(value)
            format_spec = ""

        formatted = super().format_field(value, "")
        formatted = formatted.replace("'", "''")
        formatted = formatted.replace("\"", "''")
        formatted = formatted.replace("\t", " ")

        if format_spec == "text":
            formatted = "'{}'".format(formatted)

        return formatted
Sql = SqlFormatter()

# # POUR TESTER, décommenter les lignes suivantes
##-----------------------------------------------

logger.warning("<<<<<<<<<<<<<<   Mode TEST   >>>>>>>>>>>>>>>>>")
AnalytiqueDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\Db_analytique.mdb")
FacturesDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\Facture_data.mdb")
CommunDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\Commun_Data.mdb")

##-----------------------------------------------

# Connexion à Analytique
analytique_db = AnalytiqueDb(autocommit=False)

# Connexion à Controles
facture_db = FacturesDb(autocommit=False)

# Connexion à CommunDb
commun_db = CommunDb(autocommit=False)

current = "{:%m/%Y}".format(datetime.now())
# mois_facturation = input("Veuillez renseigner le mois de facturation [defaut: {}] ('q' pour quitter): ".format(current))  # Format: voir avec jacky
# if mois_facturation == 'q':
#     sys.exit(1)
mois_facturation = "01/2018"

sql = """SELECT * FROM tbl_Tarification
        WHERE strStatut='A facturer'
        ORDER BY DblAffaireId, DblTarifId DESC;
        """

# Liste les interventions à facturer par affaire
a_facturer = {}
for interv in analytique_db.read(sql):
    if not interv.DblAffaireId in a_facturer.keys():
        a_facturer[interv.DblAffaireId] = []
    a_facturer[interv.DblAffaireId].append(interv)

if not a_facturer:
    logger.info("Aucune facture à créer - Opération annulée")
    sys.exit(1)

# Pour chaque facture, on va ajouter une ligne dans tblPieceEntete, et une ligne par intervention dans tblPieceLigne
# > NB: On ne touche pas aux interventions de cette affaire qui ont déja été facturées

for affaireId, interventions in a_facturer.items():

    piece_id = facture_db.first("SELECT Max(lngpieceId) As Id FROM tblPieceEntete").Id + 1
    if str(piece_id)[:2] != mois_facturation[-2:]:
        # On démarre une nouvelle année
        piece_id = 10000 * int(mois_facturation[:2]) + 1

    affaire = analytique_db.first("SELECT * FROM tbl_Affaires WHERE dblAffaireId={}".format(affaireId))
    if not affaire:
        logger.error("Affaire {} - L'affaire n'existe pas dans tbl_Affaires".format(affaireId))
        continue

    if not affaire.strMOId:
        logger.error("Affaire {} - Destinataire manquant dans tbl_Affaires".format(affaireId))
        continue

    destinataire = commun_db.first("SELECT * FROM tblTiers WHERE lngTiersId={}".format(affaire.strMOId))
    if not destinataire:
        logger.warning("Affaire {} - Destinataire introuvable dans tblTiers ('{}'), remplacé par tiers n°190".format(affaireId, affaire.strMOId))
        destinataire = commun_db.first("SELECT * FROM tblTiers WHERE lngTiersId=190")

    entete = EnTete()
    entete.lngPieceId = piece_id

    # on recupere les champs de tblTiers depuis le champ lngASTRE (champ n°3) jusqu'au champ strCodeProduit (champ n°26)
    # ATTENTION: le champ 'strPaysIdIso3166-A2' n'etant pas un nom de propriété valide, il est renomme en strPaysIdIso3166 dans le modèle.
    entete.lngTiersId = destinataire.lngTiersId
    entete.lngASTRE = destinataire.lngASTRE
    entete.strCodeProduit = destinataire.strCodeProduit
    entete.bytTitreId = destinataire.bytTitreId
    entete.strCodeAdresse = destinataire.strCodeAdresse
    entete.strAdresse1 = destinataire.strAdresse1
    entete.strAdresse2 = destinataire.strAdresse2
    entete.strAdresse3 = destinataire.strAdresse3
    entete.strAdresse4 = destinataire.strAdresse4
    entete.strAdresse5 = destinataire.strAdresse5
    entete.strPaysIdIso3166 = destinataire.field_11
    entete.bytTVATiersId = destinataire.bytTVATiersId
    entete.strTelephone = destinataire.strTelephone
    entete.strTelecopie = destinataire.strTelecopie
    entete.strPortable = destinataire.strPortable
    entete.strEMail = destinataire.strEMail
    entete.strWeb = destinataire.strWeb
    entete.strLangueIdIso639 = destinataire.strLangueIdIso639
    entete.strCompteComptable = destinataire.strCompteComptable
    entete.strDeviseIdIso4217 = destinataire.strDeviseIdIso4217
    entete.bytReglementId = destinataire.bytReglementId
    entete.bytClasseTarifId = destinataire.bytClasseTarifId
    entete.bytNbExFacture = destinataire.bytNbExFacture
    entete.bytClasseRemiseTiersId = destinataire.bytClasseRemiseTiersId
    entete.bytNbExFacture = destinataire.bytNbExFacture
    entete.strStatTiers = destinataire.strStatTiers

    # Valeurs fixes
    entete.lngDocId = 0
    entete.strLangueIdIso639 = "fr"
    entete.strDeviseIdIso4217 = "EUR"
    entete.bytReglementId = 1
    entete.bytClasseTarifId = 1
    entete.bytClasseRemiseTiersId = 1
    entete.bytNbExFacture = 1
    entete.strStatTiers = ""
    entete.bytSituationIdPrincipale = 2
    entete.bytSituationIdSecondaire = 41
    entete.bytTypeDocumentId = 50
    entete.strStatDocEntete = "PI00"
    entete.dblCoursDevise = 1
    entete.dtmCoursDevise = datetime(2002, 1, 1)
    entete.bytTypeTarif = 1
    entete.strReglement = "POUR ACQUITTER CET ETAT DE REDEVANCE, ATTENDEZ IMPERATIVEMENT DE RECEVOIR LE TITRE DE PERCEPTION EMANANT DE LA PAIERIE DEPARTEMENTALE"

    # Commentaires
    entete.memObsEntete = "[Mois : {mois_facturation}] " \
                          "[Lieu de travail : ]{affaire.strLieux} " \
                          "[V/Cde : ] {affaire.Ref} du {affaire.dtmCommande:%d/%m/%Y}" \
                          "".format(mois_facturation=mois_facturation,
                                    affaire=affaire).replace("'", "")

    entete.memObsInterne = "N° Affaire analytique : {}".format(affaire.strLiaisonControle)

    entete.strStatDocEntete = "PC00" if ("/" in affaire.strLiaisonControle or affaire.strLiaisonControle == "RDBOEC") else "PT00"

    entete.strUserIdCreation = "script"
    entete.strUserIdLastMod = "script"

    i = 0
    lignes = []
    for interv in interventions:
        i += 1

        ligne = Ligne()
        ligne.lngPieceId = piece_id
        ligne.intLigneId = i
        ligne.strArticleId = interv.strArticleId

        article = commun_db.first("SELECT * FROM tblArticle WHERE strArticleId='{}'".format(interv.strArticleId))
        ligne.strArticle = article.strArticle if article else ""
        ligne.bytTVAArticleId = article.bytTVAArticleId if article else 0

        tva = commun_db.first("SELECT bytTVAId FROM tblTVA WHERE bytTVATiersId={} AND bytTVAArticleId={}".format(destinataire.bytTVATiersId, ligne.bytTVAArticleId))
        ligne.bytTVAId = tva.bytTVAId

        taux = commun_db.first("SELECT dblTVATaux FROM tblTVATaux WHERE bytTVAId={}".format(ligne.bytTVAId))
        ligne.dblTVATaux = taux.dblTVATaux

        ligne.dblQte = interv.dblQuantite

        unite = commun_db.first("SELECT * FROM tblUnite WHERE strUniteCourt='{}'".format(interv.strUnite))
        ligne.bytUniteIdQuantite = unite.bytUniteId
        ligne.bytUniteIdPrix = unite.bytUniteId
        ligne.dblPUhtBrutDev = interv.dblPrixUnitaire
        ligne.dblPUhtNetDev = interv.dblPrixUnitaire
        ligne.dblPThtNetDev = interv.dblPrixTotal
        ligne.blnPrestation = True
        ligne.memObs = "Intervention le {}{}".format(interv.dtmFin, "\nRapport no : {}".format(interv.strRapportId) if interv.strRapportId else "")
        ligne.blnLigneGeneree = True
        ligne.bytGenerateurId = 1
        ligne.dblPThtNet = interv.dblPrixTotal
        ligne.bytClasseRemiseArticleId = 1
        ligne.dblPUSaisie = interv.dblPrixUnitaire
        ligne.strPUAff = "{} EUR/{}".format(interv.dblPrixUnitaire, interv.strUnite)
        ligne.strQteAff = "{:03.02f} {}".format(interv.dblQuantite, interv.strUnite)

        lignes.append(ligne)

    # Toutes les lignes ont été ajoutées: on créé maintenant les lignes de totaux
    montant_ht = float(sum([ligne.dblPThtNetDev for ligne in lignes]))
    taux_tva = float(lignes[0].dblTVATaux)
    montant_tva = (0.01 * taux_tva) * montant_ht
    montant_ttc = montant_ht + montant_tva

    # Sous-total HT (ligne 32000)
    ligne_total_1 = Ligne()
    ligne_total_1.lngPieceId = piece_id
    ligne_total_1.intLigneId = 32000
    ligne_total_1.strArticleId = ""
    ligne_total_1.strArticle = "Total H.T."
    ligne_total_1.dblPThtNetDev = montant_ht
    ligne_total_1.dblPTttcNetDev = montant_ttc
    ligne_total_1.bytLigneSousTotal = 10
    ligne_total_1.blnLigneVisible = False
    ligne_total_1.dblPThtNet = montant_ht
    ligne_total_1.dblPTttcNet = round(montant_ttc, 2)
    ligne_total_1.dblPUSaisie = round(montant_ht, 2)
    ligne_total_1.strPUAff = "{:03.02f} EUR".format(montant_ht)

    lignes.append(ligne_total_1)

    # Sous-total TVA (ligne 32001)
    ligne_total_2 = Ligne()
    ligne_total_2.lngPieceId = piece_id
    ligne_total_2.intLigneId = 32001
    ligne_total_2.strArticleId = ""
    ligne_total_2.strArticle = """TVA 1 : {taux_tva}% H.T. : {montant_ht:03.02f} T.V.A. :
                                 {montant_tva:03.02f} T.T.C. : {montant_ttc:03.02f}:""".format(taux_tva=taux_tva,
                                                                                               montant_ht=montant_ht,
                                                                                               montant_tva=montant_tva,
                                                                                               montant_ttc=montant_ttc)
    ligne_total_2.bytTVAId = lignes[0].bytTVAId
    ligne_total_2.dblTVATaux = taux_tva
    ligne_total_2.dblPThtNetDev = montant_ht
    ligne_total_2.dblPTttcNetDev = montant_ttc
    ligne_total_2.bytLigneSousTotal = 11
    ligne_total_2.blnLigneVisible = False
    ligne_total_2.dblPThtNet = montant_ht
    ligne_total_2.dblPTttcNet = round(ligne_total_1.dblPTttcNetDev, 2)
    ligne_total_2.dblPUSaisie = round(montant_tva, 2)
    ligne_total_2.strPUAff = "{:03.02f} EUR".format(montant_tva)

    lignes.append(ligne_total_2)

    # Sous-total TTC (ligne 32500)
    ligne_total_3 = Ligne()
    ligne_total_3.lngPieceId = piece_id
    ligne_total_3.intLigneId = 32500
    ligne_total_3.strArticleId = ""
    ligne_total_3.strArticle = "Total T.T.C."
    ligne_total_3.dblPThtNetDev = montant_ht
    ligne_total_3.dblPTttcNetDev = montant_ttc
    ligne_total_3.bytLigneSousTotal = 12
    ligne_total_3.blnLigneVisible = False
    ligne_total_3.dblPThtNet = montant_ttc
    ligne_total_3.dblPTttcNet = round(montant_ttc, 2)
    ligne_total_3.dblPUSaisie = round(montant_ttc, 2)
    ligne_total_3.strPUAff = "{:03.02f} EUR".format(montant_ttc)

    lignes.append(ligne_total_3)



    logger.info("* Nouvelle facture: {}".format(entete.lngPieceId))

    # Insertion de l'en-tête
    sql = Sql.format("""
            INSERT INTO tblPieceEntete ( lngPieceId, lngDocId, lngTiersId, lngASTRE, strCodeProduit, bytTitreId, strCodeAdresse, strAdresse1, strAdresse2,
                                        strAdresse3, strAdresse4, strAdresse5, [strPaysIdIso3166-A2], bytTVATiersId, strTelephone, strTelecopie, strPortable,
                                        strEMail, strWeb, strLangueIdIso639, strCompteComptable, strDeviseIdIso4217, bytReglementId, strReglement,
                                        bytClasseTarifId, bytClasseRemiseTiersId, bytNbExFacture, strStatTiers, bytSituationIdPrincipale, bytSituationIdSecondaire,
                                        memObsEntete, memObsInterne, bytTypeDocumentId, strStatDocEntete, dblCoursDevise, dtmCoursDevise,
                                        strUserIdCreation, strUserIdLastMod )
            VALUES ({entete.lngPieceId}, {entete.lngDocId}, {entete.lngTiersId}, {entete.lngASTRE}, {entete.strCodeProduit:text}, {entete.bytTitreId},
                    {entete.strCodeAdresse:text}, {entete.strAdresse1:text}, {entete.strAdresse2:text}, {entete.strAdresse3:text}, {entete.strAdresse4:text}, {entete.strAdresse5:text},
                    {entete.strPaysIdIso3166:text}, {entete.bytTVATiersId}, {entete.strTelephone:text}, {entete.strTelecopie:text}, {entete.strPortable:text}, {entete.strEMail:text},
                    {entete.strWeb:text}, {entete.strLangueIdIso639:text}, {entete.strCompteComptable:text}, {entete.strDeviseIdIso4217:text}, {entete.bytReglementId},
                    {entete.strReglement:text}, {entete.bytClasseTarifId}, {entete.bytClasseRemiseTiersId}, {entete.bytNbExFacture}, {entete.strStatTiers:text},
                    {entete.bytSituationIdPrincipale}, {entete.bytSituationIdSecondaire}, {entete.memObsEntete:text}, {entete.memObsInterne:text},
                    {entete.bytTypeDocumentId}, {entete.strStatDocEntete:text}, {entete.dblCoursDevise}, {entete.dtmCoursDevise:date},
                    {entete.strUserIdCreation:text}, {entete.strUserIdLastMod:text})
            """, entete=entete)

    logger.debug(sql)
    facture_db.execute(sql)

    for ligne in lignes:

        # Complete les champs manquants
        for field in ["bytTVAArticleId", "bytTVAId", "dblTVATaux", "dblQte", "bytUniteIdQuantite",
                     "bytNbDecQuantite", "bytUniteIdPrix", "dblCnvUniteCoef", "bytNbDecQuantiteConvertie", "dblPUhtBrutDev", "dblPUttcBrutDev", "dblPUhtNetDev",
                     "dblPUttcNetDev", "bytNbDecPU", "dblPThtBrutDev", "dblPTttcBrutDev", "dblPThtNetDev", "dblPTttcNetDev", "strStatArticle", "strStatDocLigne",
                     "dblTauxRemise1", "dblTauxRemise2", "dblTauxRemise3", "blnPrestation", "strCompteComptable", "memObs", "memObsInterne", "bytLigneSousTotal",
                     "intLigneIdRattachement", "blnLigneVisible", "blnLigneGeneree", "bytGenerateurId", "dblPUhtBrut", "dblPUttcBrut", "dblPUhtNet", "dblPUttcNet",
                     "dblPThtBrut", "dblPTttcBrut", "dblPThtNet", "dblPTttcNet", "bytClasseRemiseArticleId", "dblPUSaisie", "strPUAff", "strQteAff"]:
            if not hasattr(ligne, field):
                setattr(ligne, field, None)


        sql = Sql.format("""
                INSERT INTO tblPieceLigne ( lngPieceId, intLigneId, strArticleId, strArticle, bytTVAArticleId, bytTVAId, dblTVATaux, dblQte, bytUniteIdQuantite,
                                             bytNbDecQuantite, bytUniteIdPrix, dblCnvUniteCoef, bytNbDecQuantiteConvertie, dblPUhtBrutDev, dblPUttcBrutDev, dblPUhtNetDev,
                                             dblPUttcNetDev, bytNbDecPU, dblPThtBrutDev, dblPTttcBrutDev, dblPThtNetDev, dblPTttcNetDev, strStatArticle, strStatDocLigne,
                                             dblTauxRemise1, dblTauxRemise2, dblTauxRemise3, blnPrestation, strCompteComptable, memObs, memObsInterne, bytLigneSousTotal,
                                             intLigneIdRattachement, blnLigneVisible, blnLigneGeneree, bytGenerateurId, dblPUhtBrut, dblPUttcBrut, dblPUhtNet, dblPUttcNet,
                                             dblPThtBrut, dblPTttcBrut, dblPThtNet, dblPTttcNet, bytClasseRemiseArticleId, dblPUSaisie, strPUAff, strQteAff )
                VALUES ({ligne.lngPieceId}, {ligne.intLigneId}, {ligne.strArticleId:text}, {ligne.strArticle:text}, {ligne.bytTVAArticleId}, {ligne.bytTVAId}, {ligne.dblTVATaux},
                        {ligne.dblQte}, {ligne.bytUniteIdQuantite}, {ligne.bytNbDecQuantite}, {ligne.bytUniteIdPrix}, {ligne.dblCnvUniteCoef}, {ligne.bytNbDecQuantiteConvertie},
                        {ligne.dblPUhtBrutDev}, {ligne.dblPUttcBrutDev}, {ligne.dblPUhtNetDev}, {ligne.dblPUttcNetDev}, {ligne.bytNbDecPU}, {ligne.dblPThtBrutDev},
                        {ligne.dblPTttcBrutDev}, {ligne.dblPThtNetDev}, {ligne.dblPTttcNetDev}, {ligne.strStatArticle:text}, {ligne.strStatDocLigne:text},
                        {ligne.dblTauxRemise1}, {ligne.dblTauxRemise2}, {ligne.dblTauxRemise3}, {ligne.blnPrestation}, {ligne.strCompteComptable:text},
                        {ligne.memObs:text}, {ligne.memObsInterne:text}, {ligne.bytLigneSousTotal}, {ligne.intLigneIdRattachement}, {ligne.blnLigneVisible},
                        {ligne.blnLigneGeneree}, {ligne.bytGenerateurId}, {ligne.dblPUhtBrut}, {ligne.dblPUttcBrut}, {ligne.dblPUhtNet}, {ligne.dblPUttcNet},
                        {ligne.dblPThtBrut}, {ligne.dblPTttcBrut}, {ligne.dblPThtNet}, {ligne.dblPTttcNet}, {ligne.bytClasseRemiseArticleId}, {ligne.dblPUSaisie},
                        {ligne.strPUAff:text}, {ligne.strQteAff:text})
                """, ligne=ligne)
        logger.debug(sql)
        facture_db.execute(sql)

        # Maj tbl_Tarification.strNumFacture et strStatut pour marquer la ligne comme facturée.
        sql = Sql.format(""" UPDATE tbl_Tarification SET strStatut = 'Facturée', strNumFacture = '{}'
                              WHERE DblTarifId={}; """, piece_id, interv.DblTarifId)
        logger.debug(sql)
        analytique_db.execute(sql)

    facture_db.commit()
    analytique_db.commit()