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