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   >>>>>>>>>>>>>>>>>")

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

def main():

    # 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':
        return

    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")
        return

    # 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)


        # Mise à jour du compteur dans tblParametresFacture
        max_piece_id = facture_db.first("SELECT MAX(lngpieceId) as lngpieceId FROM tblPieceEntete").lngpieceId

        sql = """"UPDATE tblParametresFacture
                  SET tblParametresFacture.strParametreValeur = '{}'
                  WHERE tblParametresFacture.strParametreNom='NoPiece'
                  """.format(max_piece_id)

        facture_db.execute(sql)

        facture_db.commit()
        analytique_db.commit()

if __name__ == "__main__":
    main()

    logger.info("-- Fin --")