scripts-pde/gf2analytique.py

'''
Created on 29 juin 2017

@author: olivier.massot
'''
from datetime import datetime
import logging
import re

from path import Path

from core.pde import AnalytiqueDb
from core.webservice import GfWebservice


# TODO: proposer une méthode de correction des erreurs
# TODO: configurer logging
# TODO: envoi mail auto
logger = logging.getLogger("factures")
logging.basicConfig(filename=r'log\factures_{:%Y%m%d_%H%M}.log'.format(datetime.now()),
                    level=logging.INFO)

logger.info("Initialization")

# Connect to factures.mdb
analytique_db = AnalytiqueDb(autocommit=False)

# Connect to the astre gf webservice
ws = GfWebservice("GetPDEFactures")

workdir = Path(r".\work")
workdir.mkdir_p()
workdir /= "gf2analytique"
workdir.mkdir_p()

errfile = workdir / "err.csv"

class AlreadyImported(Exception):
    pass

class NotImported(Exception):
    pass

class InvalidData(Exception):
    pass

class InvalidAxe(Exception):
    pass

class Facture():
    def __init__(self):
        self._factureId = None
        self.numExBudget = None
        self.codeColl = None
        self.codeBudg = None
        self.numEnv = None
        self.codeSection = None
        self.typeMvt = None
        self.numMandat = None
        self.numLiqMandat = None
        self.numLigneMandat = None
        self.codeAxe = None
        self.libAxe = None
        self.codeCout = None
        self.libCout = None
        self.dateMandat = None
        self.numBj = None
        self.numTiers = None
        self.libRai = None
        self.refIntMandat = None
        self.codePeriode = None
        self.dateDepDelai = None
        self.typeNomencMarche = None
        self.mntTtcMandat = None
        self.mntTvaMandat = None
        self.mntVent = None

    @property
    def factureId(self):
        if self._factureId is None:
            try:
                self._factureId = self._get_facture_id()
            except (KeyError, AttributeError, TypeError):
                raise NotImported()
        return self._factureId

    @classmethod
    def from_webservice(cls, wsdata):
        facture = cls()
        for key, value in wsdata.items():
            facture.__dict__[key] = value
        facture.autocorrection()
        return facture

    def is_imported(self):
        try:
            return self.factureId > 0
        except NotImported:
            return False

    def autocorrection(self):
        # correction auto des codes chantiers
        if re.match(r"\d{2}5\d{3}", self.codeCout):
            self.codeCout += "/1"
        self.libRai = self.libRai.replace("'", "''")

    def is_valid(self):
        """ controle la validité des données d'une facture """
        if not int(self.numExBudget) > 2000:
            logger.warning("Exercice budgetaire invalide: %s", self.numExBudget)
            return False
        if self.codeColl != "CG67":
            logger.warning("Code collectivité invalide: %s", self.codeColl)
            return False
        if self.codeBudg != "02":
            logger.warning("Code budgetaire invalide: %s", self.codeBudg)
            return False
        if self.codeAxe == "ENGIN":
            # Controle l'existence du materiel
            if not analytique_db.first("SELECT intlMaterielID FROM tbl_materiel WHERE txtMateriel='{}'".format(self.codeCout)):
                logger.warning("Le materiel n'existe pas: %s", self.codeCout)
                return False
        elif self.codeAxe == "AFFAI":
            # Controle l'existence de l'affaire
            if not analytique_db.first("SELECT dblAffaireId FROM tbl_Affaires WHERE strLiaisonControle='{}'".format(self.codeCout)):
                logger.warning("L'affaire n'existe pas: %s", self.codeCout)
                return False
        else:
            # CodeAxe invalide
            logger.warning("Code axe inconnu: %s", self.codeAxe)
            return False
        return True

    def send_to_db(self):

        if self.is_imported():
            raise AlreadyImported()

        if not self.is_valid():
            raise InvalidData()

        self._insert_factures()

        if self.codeAxe == "ENGIN":
            self._insert_factures_engins()

        elif self.codeAxe == "AFFAI":
            self._insert_factures_affaires()

        self._insert_mandatement()

        analytique_db.commit()

        logger.info("* imported: %s", self.factureId)

    def _get_facture_id(self):
        sql = """SELECT dblFactureId FROM tbl_Factures
                    WHERE intExercice = {}
                    AND strEnveloppe = '{}'
                    AND strLiquidation = '{}'
                    AND strAxe = '{}'
                    AND
                      ((intLiquidationLigne = {} AND strCentreCout='{}')
                      OR (intLiquidationLigne IS NULL))
                """.format(self.numExBudget,
                           self.numEnv,
                           self.numLiqMandat,
                           self.codeAxe,
                           self.numLigneMandat,
                           self.codeCout)

#         logger.debug("> %s", sql)
        factureId = analytique_db.first(sql)["dblfactureid"]
#         logger.debug("retrieve dblFactureId: %s", factureId)
        return factureId

    def _insert_factures(self):
        sql = """INSERT INTO tbl_Factures ( intExercice,
                                            strLiquidation,
                                            intLiquidationLigne,
                                            strEngagement,
                                            strEnveloppe,
                                            strService,
                                            strTiers,
                                            strTiersLibelle,
                                            strMotsClefs,
                                            dtmDeb,
                                            intOperation,
                                            strNomenclature0,
                                            strAXE,
                                            strCentreCout,
                                            strObjet,
                                            dblMontantTotal,
                                            dblMontantTVA,
                                            strORIGINE_DONNEES
                                        )
                  VALUES ({intExercice},
                          '{strLiquidation}',
                          {intLiquidationLigne},
                          '{strEngagement}',
                          '{strEnveloppe}',
                          '{strService}',
                          '{strTiers}',
                          '{strTiersLibelle}',
                          '{strMotsClefs}',
                          #{dtmDeb}#,
                          {intOperation},
                          '{strNomenclature0}',
                          '{strAxe}',
                          '{strCentreCout}',
                          '{strObjet}',
                          {dblMontantTotal},
                          {dblMontantTVA},
                          '{strORIGINE_DONNEES}'
                          )
              """.format(
                         intExercice=self.numExBudget,
                         strLiquidation=self.numLiqMandat,
                         intLiquidationLigne=self.numLigneMandat,
                         strEngagement=self.numMandat,
                         strEnveloppe=self.numEnv,
                         strService='7710',
                         strTiers=self.numTiers,
                         strTiersLibelle=self.libRai,
                         strMotsClefs=AnalytiqueDb.nz(self.refIntMandat),
                         dtmDeb=AnalytiqueDb.format_date(self.dateDepDelai),
                         intOperation=AnalytiqueDb.nz(self.codePeriode, "Null"),
                         strNomenclature0=self.typeNomencMarche,
                         strAxe=self.codeAxe,
                         strCentreCout=self.codeCout,
                         strObjet=AnalytiqueDb.format_date(self.dateMandat, out_format="%d/%m/%Y"),
                         dblMontantTVA=self.mntTvaMandat,
                         dblMontantTotal=self.mntVent,
                         strORIGINE_DONNEES='ASTRE'
                         )
        logger.debug("> %s", sql)
        analytique_db.execute(sql)

    def _insert_factures_engins(self):
        if self.codeAxe != "ENGIN":
            raise InvalidAxe()

        materiel = analytique_db.first("SELECT intlMaterielID FROM tbl_Materiel WHERE [txtMateriel]='{}'".format(self.codeCout))
        materielId = materiel["intlmaterielid"] if materiel else '859'
        logger.debug("retrieve intlMaterielID: %s", materielId)

        sql = """INSERT INTO tbl_Facture_Engin ( intlMaterielID, txtMateriel, dblFactureId, strLibelle, dblMontant, strType )
                VALUES ({}, '{}', {}, '{}', {}, '{}')
        """.format(materielId,
                   self.codeCout,
                   self.factureId,
                   AnalytiqueDb.nz(self.libCout),
                   self.mntVent,
                   self.libRai
                   )
        logger.debug("> %s", sql)
        analytique_db.execute(sql)

    def _insert_factures_affaires(self):
        if self.codeAxe != "AFFAI":
            raise InvalidAxe()

        sql = """INSERT INTO tbl_Facture_Affaire ( strAffaireId, dblFactureId, strLibelle, dblMontant, strType )
              VALUES ('{}', {}, '{}', {}, '{}')
              """.format(self.codeCout,
                         self.factureId,
                         self.libRai ,
                         self.mntVent,
                         AnalytiqueDb.nz(self.libCout),
                         )
        logger.debug("> %s", sql)
        analytique_db.execute(sql)

    def _insert_mandatement(self):
        sql = """INSERT INTO tbl_Mandatement ( dblFacture, strNumMandat, dtmMandat, strBordereau )
              VALUES ({}, '{}', #{}#, '{}')
              """.format(self.factureId,
                         self.numMandat,
                         AnalytiqueDb.format_date(self.dateMandat),
                         self.numBj
                       )
        logger.debug("> %s", sql)
        analytique_db.execute(sql)

    def dump_to_err(self):

        fields = ["numExBudget", "numEnv", "numLiqMandat", "codeAxe", "codeCout"]

        try:
            with open(errfile, 'r') as f:
                content = f.read()
        except FileNotFoundError:
            content = ""
        if not content:
            line = "\t".join(fields)
            with open(errfile, 'a') as f:
                f.writelines([line])

        line = "\t".join([getattr(self, field) for field in fields])

        with open(errfile, 'a') as f:
            f.writelines([line])

########

if __name__ == "__main__":


    analysed, updated, errors = 0, 0, 0
    for wsdata in ws:
        analysed += 1

        facture = Facture.from_webservice(wsdata)

        try:
            facture.send_to_db()
            updated += 1
        except AlreadyImported:
            pass
        except InvalidData:
            facture.dump_to_err()
            errors += 1

    logger.info("Import terminé: {} lignes traitées / {} importées / {} erreurs".format(analysed, updated, errors))