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 sys

from path import Path  # @UnusedImport

from core import logconf
from core.pde import FacturesDb, AnalytiqueDb, CommunDb, EnTete, Ligne
from core.sqlformatter import SqlFormatter


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


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

# 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")
# logger.handlers = [h for h in logger.handlers if (type(h) == logging.StreamHandler)]
# logger.warning("<<<<<<<<<<<<<<   Mode TEST   >>>>>>>>>>>>>>>>>")

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

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

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

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

Sql = SqlFormatter()

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)

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,\r\n ATTENDEZ IMPERATIVEMENT DE RECEVOIR LE TITRE DE PERCEPTION EMANANT DE LA PAIERIE DEPARTEMENTALE"

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

    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

        if interv.strUnite:
            unite = commun_db.first("SELECT * FROM tblUnite WHERE strUniteCourt='{}'".format(interv.strUnite))
            ligne.bytUniteIdQuantite = unite.bytUniteId
            ligne.bytUniteIdPrix = unite.bytUniteId
        else:
            ligne.bytUniteIdQuantite = 0
            ligne.bytUniteIdPrix = 0
        ligne.dblPUhtBrutDev = interv.dblPrixUnitaire
        ligne.dblPUhtNetDev = interv.dblPrixUnitaire
        ligne.dblPThtNetDev = interv.dblPrixTotal
        ligne.blnPrestation = True
        ligne.memObs = "Intervention le {:%d/%m/%Y}{}".format(interv.dtmFin, "\r\nRapport n° : {}".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 = "{:03.02f} EUR/{}".format(interv.dblPrixUnitaire, interv.strUnite)
        ligne.strQteAff = "{:03.02f} {}".format(interv.dblQuantite, interv.strUnite)

        # on met a jour tbl_Tarification.strNumFacture et strStatut pour marquer la ligne comme facturée.
        # !! Cette opération ne sera committée qu'à la fin du traitement de cette intervention
        sql = Sql.format(""" UPDATE tbl_Tarification SET strStatut = 'Facturée', strNumFacture = {:text}
                              WHERE DblTarifId={}""", piece_id, interv.DblTarifId)
        analytique_db.execute(sql)

        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.dblQte = 0
    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.dblQte = 0
    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.dblQte = 0
    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_ht
    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)
    facture_db.execute(sql)

    for ligne in lignes:
        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)
        facture_db.execute(sql)

    facture_db.commit()
    analytique_db.commit()