scripts-pde/gf2analytique.py

'''
Created on 29 juin 2017

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

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

# TODO: vérifier le résultat des requêtes
# TODO: proposer une méthode de correction des erreurs
# TODO: configurer logging
# TODO: envoi mail auto

logger = logging.getLogger("factures")
logging.basicConfig(level=logging.INFO)
# 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")

analysed, updated, errors = 0, 0, 0

def shortuid():
    """ 15 cars decimal uuid """
    base = int(time.time()) << 32
    rand = random.SystemRandom().getrandbits(32)
    return hex((base + rand))[2:-1]

def format_date(dat):
    return datetime.strptime(str(dat)[:10], "%Y-%m-%d").strftime("%m/%d/%Y")

def nz(val, default="Null"):
    return val if val else default

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

def send(facture):
    """ ventile les données dans les tables de la base AnalytiqueDb """
    uid = shortuid()

    sql = """INSERT INTO tbl_Factures ( intExercice, strEnveloppe, strEngagement, strLiquidation, strObjet, strTiers,
          strTiersLibelle, strMotsClefs, intOperation, dtmDeb, strNomenclature0, dblMontantTVA,
          dblMontantTotal, strService, strUidImport, strORIGINE_DONNEES )
          VALUES ({intExercice}, '{strEnveloppe}', '{strEngagement}', '{strLiquidation}', '{strObjet}','{strTiers}',
          '{strTiersLibelle}', '{strMotsClefs}', {intOperation}, #{dtmDeb}#, '{strNomenclature0}',{dblMontantTVA},
          {dblMontantTotal}, '{strService}', '{strUidImport}', '{strORIGINE_DONNEES}')
          """.format(
                     intExercice=facture["numExBudget"],
                     strEnveloppe=facture["numEnv"],
                     strEngagement=facture["numMandat"],
                     strLiquidation=facture["numLiqMandat"],
                     strObjet=nz(facture["libCout"]),
                     strTiers=facture["numTiers"],
                     strTiersLibelle=facture["libRai"],
                     strMotsClefs=nz(facture["refIntMandat"]),
                     intOperation=nz(facture["codePeriode"]),
                     dtmDeb=format_date(facture["dateDepDelai"]),
                     strNomenclature0=facture["typeNomencMarche"],
                     dblMontantTVA=facture["mntTvaMandat"],
                     dblMontantTotal=facture["mntVent"],
                     strService='7710',
                     strUidImport=uid,
                     strORIGINE_DONNEES='ASTRE'
                     )
    logger.debug("> %s", sql)
    analytique_db.execute(sql)

    factureId = analytique_db.first("SELECT dblFactureId FROM tbl_Factures WHERE [strUidImport]='{}'".format(uid))["dblfactureid"]
    logger.debug("retrieve dblFactureId: %s", factureId)

    if facture["codeAxe"] == "ENGIN":

        materiel = analytique_db.first("SELECT intlMaterielID FROM tbl_Materiel WHERE [txtMateriel]='{}'".format(facture["codeCout"]))
        materielId = materiel["intlmaterielid"] if materiel else '859'

        logger.debug("retrieve intlMaterielID: %s", materielId)

        sql = """INSERT INTO tbl_Facture_Engin ( dblFactureId, txtMateriel, strLibelle, strType, dblMontant, intlMaterielID )
                VALUES ({}, '{}', '{}', '{}', {}, {})
        """.format(factureId,
                   facture["codeCout"],
                   nz(facture["libCout"]),
                   facture["libRai"],
                   facture["mntVent"],
                   materielId
                   )

    elif facture["codeAxe"] == "AFFAI":

        sql = """INSERT INTO tbl_Facture_Affaire ( dblFactureId, strAffaireId, strType, strLibelle, dblMontant )
              VALUES ({}, '{}', '{}', '{}', {})
              """.format(factureId,
                   facture["codeCout"],
                   facture["libCout"],
                   facture["libRai"],
                   facture["mntVent"]
                   )

    logger.debug("> %s", sql)
    analytique_db.execute(sql)

    sql = """INSERT INTO tbl_Mandatement ( dblFacture, strNumMandat, dtmMandat, strBordereau )
          VALUES ({}, '{}', #{}#, '{}')
          """.format(factureId,
                   facture["numMandat"],
                   format_date(facture["dateMandat"]),
                   facture["numBj"]
                   )

    logger.debug("> %s", sql)
    analytique_db.execute(sql)
    analytique_db.commit()

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


def freeze(facture):
    pass

########

if __name__ == "__main__":

    prompt_for_correction = "-c" in sys.argv

    for facture in ws:
        if not facture:
            continue

        analysed += 1

        record = analytique_db.first("""SELECT dblFactureId
                                        FROM tbl_Factures
                                        WHERE strService='7710'
                                        AND intExercice={}
                                        AND strLiquidation='{}'
                                        """.format(facture["numExBudget"], facture["numLiqMandat"]))
        if record:
            # already imported
            continue

        # correction auto des codes chantiers
        if re.match(r"\d{2}5\d{3}", facture["codeCout"]):
            facture["codeCout"] += "/1"

        if not is_valid(facture, prompt_for_correction):
            logger.warning("INVALID DATA: numEnv= %s", facture["numEnv"])
            errors += 1
            continue

        send(facture)
        updated += 1

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