scripts-pde/ctrl2analytique.py

'''

    Génère les affaires dans la base Analytique à partir des données de la base Contrôles.


    **IMPORTANT**: pour lancer le script sans interaction avec l'utilisateur
    (par ex, dans le cas d'une tâche planifiée), appeller le script avec l'option '-n'.

    @author: olivier.massot, févr. 2018
'''
from datetime import datetime
import logging
import sys

from path import Path

from core import logconf
from core.model import Model
from core.pde import ControlesDb, AnalytiqueDb, mk_workdir, CommunDb

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

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

logger.warning("<<<<<<<<<<<<<<   Mode TEST   >>>>>>>>>>>>>>>>>")
ControlesDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\cg67Parc_data.mdb")
AnalytiqueDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\Db_analytique.mdb")
CommunDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\Commun_Data.mdb")

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

# *** Initialisation
logger.info("Initialisation...")

no_prompt = ("-n" in sys.argv)
if no_prompt:
    logger.info("> Lancé en mode automatique (sans interruption)")

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

# Connexion à Controles
controles_db = ControlesDb(autocommit=False)

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

# Make the working directory
workdir = mk_workdir("ctrl2analytique")
affaires_file = workdir / "affaires.csv"
intervs_file = workdir / "intervs.csv"

errors = []


# > Supprime les fichiers d'import s'il existent
for file in (affaires_file, intervs_file):
    if file.exists():
        logger.debug("Supprime le fichier %s", file)
        file.remove()

class Affaire(Model):
    _FIELDS = ["strLiaisonControle", "strMOeId", "strCommneId", "strLieux",
               "strEntrepriseId", "strMOId", "dtmCommande", "Ref", "blnMarche",
               "dblMarche", "intDevisId", "strCT", "strTypeId",
               "intCoefFG", "strSituation"]

def get_type_id(lngChantierId, bytCommandeId):
    """ Recupère le type de chantier.
    'ZP': Chantier de contrôle d'étanchéité
    'ZC': Chantier de contrôle du compactage
    'ZI': Chantier d'inspection vidéo
    'ZZ': Chantier mixte.
    '': Inconnu
    """
    sql = """SELECT lngChantierId, 'ZP' as type FROM tblEtancheiteBases WHERE [lngChantierId] = {chantier} AND [bytCommandeId] = {commande}
            UNION
            SELECT lngChantierId, 'ZC' as type FROM tblCompactageBases WHERE [lngChantierId] = {chantier}
            UNION
            SELECT lngChantierId, 'ZI' as type FROM tblVideoBases WHERE [lngChantierId] = {chantier};
            """.format(chantier=lngChantierId,
                       commande=bytCommandeId)
    res = controles_db.read_all(sql)

    if len(res) == 0:
        return ""
    elif len(res) == 1:
        return res[0].type
    else:
        return "ZZ"

def get_coeff_k(lngChantierId):

    # On déduit l'année du chantier à partir du code chantier
    annee = "20" + str(lngChantierId)[:2] if len(str(lngChantierId)) == 6 else "200" + str(lngChantierId)[:1]

    # On retrouve dans la table tbl_COEFFG le coefficient correspondant
    return analytique_db.first("SELECT [COEFFG] FROM tbl_COEFFG WHERE [ANNEE] = {}".format(annee)).COEFFG / 100


# *** 1- Import des chantiers Contrôles dans le fichier affaires.csv

nb_affaires, nb_intervs = 0, 0

logger.debug("Génère le fichier %s", affaires_file)
firstline = "\t".join(Affaire._FIELDS + ["\n"])
with open(affaires_file, 'w+') as f:
    f.write(firstline)

# on insère les affaires depuis tblCommandes et tblChantier (le lien est strLiaisonControle)

compteur = 0
sql = """ SELECT tblCommandes.lngChantierId, tblCommandes.bytCommandeId, tblChantiers.strSubdivisionId, tblChantiers.strCollectiviteId as ChantierCollectiviteId, tblChantiers.strLocChantier,
          tblChantiers.strEntrepriseId, tblCommandes.strCollectiviteId as CommandeCollectiviteId, tblCommandes.dtmCommande, tblCommandes.strRefCommande, tblCommandes.blnMarche, tblCommandes.dblMtMarche, tblCommandes.strdevis
          FROM tblChantiers INNER JOIN tblCommandes ON tblChantiers.lngChantierId = tblCommandes.lngChantierId
          WHERE (((tblCommandes.sngAffaireIdMos) Is Null Or (tblCommandes.sngAffaireIdMos)=0))
          """

for data in controles_db.read(sql):

    affaire = Affaire()
    affaire.strLiaisonControle = "{}/{}".format(data.lngChantierId, data.bytCommandeId)
    affaire.strMOeId = data.strSubdivisionId
    affaire.strCommneId = data.ChantierCollectiviteId
    affaire.strLieux = data.strLocChantier
    affaire.strEntrepriseId = data.strEntrepriseId
    affaire.strMOId = data.CommandeCollectiviteId
    affaire.dtmCommande = data.dtmCommande
    affaire.Ref = data.strRefCommande
    affaire.blnMarche = data.blnMarche
    affaire.dblMarche = data.dblMtMarche
    affaire.intDevisId = data.strdevis
    affaire.strCT = '1'
    affaire.strTypeId = get_type_id(data.lngChantierId, data.bytCommandeId)
    affaire.intCoefFG = get_coeff_k(data.lngChantierId)
    affaire.strSituation = "En cours"

    with open(affaires_file, 'a') as f:
        f.write(affaire.to_csv())

    compteur += 1

logger.info("> {} affaires ajoutées à {}".format(compteur, affaires_file))

# *** 2- Import des interventions de contrôle du compactage dans le fichier intervs.csv

class Interv(Model):
    _FIELDS = ["strEquipeId", "strEnginId", "strRapportId", "strTypeInterventionId",
               "strTypeInterventionId", "strCatégorieInterventionId", "dblquantite", "strunite", "dtmIntervention",
               "dtmDureeIntervention", "dtmDureeInstallation", "strLiaisonControle", "strArticleId", "intPeriode",
               "remarques", "strgrandeur1", "strgrandeur2", "strgrandeur3",
               "strcaracteristique1", "strcaracteristique2", "strcaracteristique3",
               "dtmImportation", "strTest", "LienAff"
               ]

logger.debug("Génère le fichier %s", intervs_file)
firstline = "\t".join(Interv._FIELDS + ["\n"])
with open(intervs_file, 'w+') as f:
    f.write(firstline)

def engin_existe(strEnginId):
    return analytique_db.exists("SELECT strEnginId FROM tbl_Engin WHERE strEnginId='{}'".format(strEnginId))

def get_periode_validite(date_interv):
    sql = """SELECT intPeriodeValiditeId FROM tblTarifValidite
            WHERE [dtmValiditeDebut] <= #{date_interv}# AND [dtmValiditeFin] > #{date_interv}# AND [bytClasseTarifId]=1
    """.format(date_interv=date_interv)
    return commun_db.first(sql).intPeriodeValiditeId

compteur = 0

sql = """SELECT tblCompactageIntervs.lngChantierId, tblCompactageIntervs.bytCommandeId, tblCompactageIntervs.bytIntervId, tblCompactageIntervs.strEquipeId,
        tblCompactageEngins.strEnginId, tblCompactageIntervs.lngRapportId, tblCompactageBases.memTravaux, tblCompactageResultats.dtmEssai,
        tblCompactageResultats.dtmDuree, tblCompactagePartChantiers.strTrcRegard, tblMateriaux.strMatériau,
        tblCompactageResultats.bytPartChantierId, tblCompactageIntervs.sngIntervIdMos
        FROM ((tblMateriaux RIGHT JOIN ((((tblCompactageIntervs LEFT JOIN tblCompactageEngins ON tblCompactageIntervs.strEquipeId = tblCompactageEngins.strEquipeId)
        INNER JOIN tblCompactageResultats ON (tblCompactageIntervs.bytIntervId = tblCompactageResultats.bytIntervId)
        AND (tblCompactageIntervs.lngChantierId = tblCompactageResultats.lngChantierId)) INNER JOIN tblCompactagePartChantiers
        ON (tblCompactageResultats.bytPartChantierId = tblCompactagePartChantiers.bytPartChantierId)
        AND (tblCompactageResultats.lngChantierId = tblCompactagePartChantiers.lngChantierId)) INNER JOIN tblCompactageBases
        ON tblCompactageIntervs.lngChantierId = tblCompactageBases.lngChantierId) ON tblMateriaux.strMateriauId = tblCompactagePartChantiers.strMateriauRemblaiId)
        LEFT JOIN tblMateriaux AS tblMateriaux_1 ON tblCompactagePartChantiers.strMateriauEnrobageId = tblMateriaux_1.strMateriauId)
        LEFT JOIN tblMateriaux AS tblMateriaux_2 ON tblCompactagePartChantiers.strMateriauLitId = tblMateriaux_2.strMateriauId
        WHERE (((tblCompactageIntervs.sngIntervIdMos)=0 Or (tblCompactageIntervs.sngIntervIdMos) Is Null))
      """

def get_type_compactage_interv(observation):
    if "ASSAINISEMENT" or "ASSAINISEMENT" in observation:
        return "CC3"
    elif "CABLE" in observation:
        return "CC1"
    elif "A.E.P" in observation:
        return "CC2"
    elif "GAZ" in observation:
        return "CC4"
    else:
        return "CC3"

for data in controles_db.read(sql):
    interv = Interv()

    if not engin_existe(data.strEnginId):
        errors.append("""Intervention compactage {}/{}/{}/{}: l'engin {} n'existe pas"""
                      .format(data.lngChantierId, data.bytCommandeId, data.bytIntervId, data.bytPartChantierId, data.strEnginId))

    interv.strEquipeId = "C{}".format(data.strEquipeId)
    interv.strEnginId = data.strEnginId
    interv.strRapportId = data.strRapportId
    interv.strTypeInterventionId = get_type_compactage_interv(data.memTravaux)
    interv.strCatégorieInterventionId = "CC"
    interv.dblquantite = 1
    interv.strunite = "u"
    interv.dtmIntervention = data.dtmEssai
    interv.dtmDureeIntervention = data.dtmDuree
    interv.dtmDureeInstallation = 0  # Les temps d'installation seront recalculés en fin de traitement
    interv.strLiaisonControle = "{}/{}/{}".format(data.lngChantierId, data.bytCommandeId, data.bytIntervId)
    interv.strArticleId = data.strEnginId
    interv.intPeriode = get_periode_validite(data.dtmEssai)
    interv.remarques = data.strTrcRegard
    interv.strgrandeur1 = data.strMatériau
    interv.strgrandeur2 = data.strMatériau
    interv.strgrandeur3 = data.strMatériau
    interv.strcaracteristique1 = "Matériau remblai"
    interv.strcaracteristique2 = "Matériau lit de pose"
    interv.strcaracteristique3 = "Matériau enrobage"
    interv.dtmImportation = "{}".format(datetime.now().strftime("%Y-%m-%d"))
    interv.strTest = "{}/{}/{}/{}".format(data.lngChantierId, data.bytCommandeId, data.bytIntervId, data.bytPartChantierId)
    interv.LienAff = "{}/{}".format(data.lngChantierId, data.bytCommandeId)

    with open(intervs_file, 'a') as f:
        f.write(interv.to_csv())

    compteur += 1

logger.info("> {} interventions Compactage ajoutées à {}".format(compteur, intervs_file))

# *** 3- Import des interventions de contrôle d'étanchéité dans le fichier intervs.csv
compteur = 0

sql = """SELECT tblEtancheiteIntervs.lngChantierId, tblEtancheiteIntervs.bytCommandeId, tblEtancheiteIntervs.bytIntervId, tblEtancheiteIntervs.strEquipeId,
            tblEtancheiteIntervs.lngRapportId, tblEtancheitePartChantiers.bytTypeEssai, tblMateriaux.strMateriauId, tblMateriaux.strMatériau,
            tblEtancheitePartChantiers.intDiametre, tblEtancheitePartChantiers.sngLgHt, tblEtancheitePartChantiers.intNbJoint, tblEtancheiteResultats.dtmDuree,
            tblEtancheiteResultats.dtmEssai, tblEtancheitePartChantiers.strTrcRegard, tblEtancheiteResultats.bytPartChantierId
        FROM ((tblEtancheiteIntervs INNER JOIN tblEtancheiteResultats ON (tblEtancheiteIntervs.lngChantierId = tblEtancheiteResultats.lngChantierId)
            AND (tblEtancheiteIntervs.bytIntervId = tblEtancheiteResultats.bytIntervId)) INNER JOIN tblEtancheitePartChantiers
            ON (tblEtancheiteResultats.lngChantierId = tblEtancheitePartChantiers.lngChantierId)
            AND (tblEtancheiteResultats.bytPartChantierId = tblEtancheitePartChantiers.bytPartChantierId)) INNER JOIN tblMateriaux
            ON tblEtancheitePartChantiers.strMateriauId = tblMateriaux.strMateriauId
        WHERE (((tblEtancheiteIntervs.sngIntervIdMos)=0 Or (tblEtancheiteIntervs.sngIntervIdMos) Is Null));
      """

def get_engin_etancheite(equipe, diametre, materiau, type_essai):
    """ retourne l'engin correspondant à l'essai en fonction eds caractéristiques de l'essai """
    sql = """SELECT strEnginId FROM tblEtancheiteEngins
            WHERE ([strEquipeId] = '{}') AND ([intDiametre] = {}) AND ([strMateriauId] = '{}') AND ([bytTypeEssaiId] ={})
            """.format(equipe, diametre, materiau, type_essai)
    row = controles_db.first(sql)
    return row.strEnginId if row else ""

for data in controles_db.read(sql):
    interv = Interv()

    interv.strEquipeId = "C{}".format(data.strEquipeId)
    interv.strEnginId = get_engin_etancheite(data.strEquipeId, data.intDiametre, data.strMateriauId, data.bytTypeEssai)
    interv.strRapportId = data.lngRapportId
    interv.strTypeInterventionId = "CE{}".format(data.bytTypeEssai)
    interv.strCatégorieInterventionId = "CE"
    interv.dblquantite = data.intNbJoint
    interv.strunite = "u"
    interv.dtmIntervention = data.dtmEssai
    interv.dtmDureeIntervention = data.dtmDuree
    interv.dtmDureeInstallation = 0  # Les temps d'installation seront recalculés en fin de traitement
    interv.strLiaisonControle = "{}/{}/{}".format(data.lngChantierId, data.bytCommandeId, data.bytIntervId)
    interv.strArticleId = interv.strEnginId
    interv.intPeriode = get_periode_validite(data.dtmEssai)
    interv.remarques = data.strTrcRegard
    interv.strgrandeur1 = data.strMatériau
    interv.strgrandeur2 = data.intDiametre
    interv.strgrandeur3 = data.sngLgHt
    interv.strcaracteristique1 = "Matériau"
    interv.strcaracteristique2 = "Diamètre"
    interv.strcaracteristique3 = "Longueur"
    interv.strunite2 = "mm"
    interv.strunite3 = "m"
    interv.dtmImportation = "{}".format(datetime.now().strftime("%Y-%m-%d"))
    interv.strTest = "{}/{}/{}/{}".format(data.lngChantierId, data.bytCommandeId, data.bytIntervId, data.bytPartChantierId)
    interv.LienAff = "{}/{}".format(data.lngChantierId, data.bytCommandeId)

    with open(intervs_file, 'a') as f:
        f.write(interv.to_csv())

    compteur += 1

logger.info("> {} interventions Etanchéité ajoutées à {}".format(compteur, intervs_file))

# *** 4- Import des interventions d'inspection vidéo dans le fichier intervs.csv
compteur = 0

sql = """SELECT tblVideoIntervs.lngChantierId, tblVideoIntervs.bytCommandeId, tblVideoIntervs.bytIntervId, tblVideoIntervs.strEquipeId,
        tblVideoEngins.strEnginId, tblVideoIntervs.lngRapportId, First(tblso_rate_Analyse.MateriauCourt) AS strmateriau, tblVideoIntervs.lngTroncon,
        tblVideoIntervs.sngNbJourFact, First(tblso_rate_Analyse.MaxDeDiametre) AS diam, tblVideoIntervs.dtmDuree, tblVideoIntervs.dtmIntervDu,
        First(tblVideoIntervs.memObservation) AS memObservation, tblChantiers.strEntrepriseId
        FROM ((tblVideoEngins RIGHT JOIN tblVideoIntervs ON tblVideoEngins.strEquipeId = tblVideoIntervs.strEquipeId) INNER JOIN tblso_rate_Analyse ON
        (tblVideoIntervs.lngChantierId = tblso_rate_Analyse.lngChantierId) AND (tblVideoIntervs.bytIntervId = tblso_rate_Analyse.bytIntervId)) INNER JOIN
        tblChantiers ON tblVideoIntervs.lngChantierId = tblChantiers.lngChantierId
        WHERE (((tblVideoIntervs.sngIntervIdMos) Is Null Or (tblVideoIntervs.sngIntervIdMos)=0))
        GROUP BY tblVideoIntervs.lngChantierId, tblVideoIntervs.bytCommandeId, tblVideoIntervs.bytIntervId, tblVideoIntervs.strEquipeId,
        tblVideoIntervs.lngRapportId, tblVideoIntervs.lngTroncon, tblVideoIntervs.sngNbJourFact, tblVideoIntervs.dtmDuree,
        tblVideoIntervs.dtmIntervDu, tblVideoEngins.strEnginId, tblChantiers.strEntrepriseId
        """

for data in controles_db.read(sql):
    interv = Interv()


    if not engin_existe(data.strEnginId):
        errors.append("""Intervention compactage {}/{}/{}/1: l'engin {} n'existe pas"""
                      .format(data.lngChantierId, data.bytCommandeId, data.bytIntervId, data.strEnginId))

    interv.strEquipeId = "C{}".format(data.strEquipeId)
    interv.strEnginId = data.strEnginId
    interv.strRapportId = data.lngRapportId
    interv.strTypeInterventionId = "CI1" if data.strEntrepriseId != 195 else "CI2"
    interv.strCatégorieInterventionId = "CI"
    interv.dblquantite = data.sngNbJourFact
    interv.strunite = "j"
    interv.dtmIntervention = data.dtmIntervDu
    interv.dtmDureeIntervention = data.dtmDuree
    interv.dtmDureeInstallation = 0  # Les temps d'installation seront recalculés en fin de traitement
    interv.strLiaisonControle = "{}/{}/{}".format(data.lngChantierId, data.bytCommandeId, data.bytIntervId)
    interv.strArticleId = data.strEnginId
    interv.intPeriode = get_periode_validite(data.dtmIntervDu)
    interv.remarques = data.memObservation
    interv.strgrandeur1 = data.strmateriau
    interv.strgrandeur2 = data.diam
    interv.strgrandeur3 = data.lngTroncon
    interv.strcaracteristique1 = "Matériau"
    interv.strcaracteristique2 = "Diamètre"
    interv.strcaracteristique3 = "Longueur inspectée"
    interv.strunite2 = "mm"
    interv.strunite3 = "m"
    interv.dtmImportation = "{}".format(datetime.now().strftime("%Y-%m-%d"))
    interv.strTest = "{}/{}/{}/1".format(data.lngChantierId, data.bytCommandeId, data.bytIntervId)
    interv.LienAff = "{}/{}".format(data.lngChantierId, data.bytCommandeId)

    with open(intervs_file, 'a') as f:
        f.write(interv.to_csv())

    compteur += 1

logger.info("> {} interventions ITV ajoutées à {}".format(compteur, intervs_file))

# *** 5- Interruption pour corection manuelle des données (si nécessaire)
logging.info("Les données à importer ont été ajoutées aux fichiers '{}' et '{}'".format(affaires_file, intervs_file))

if errors:
    logging.error("<!> Des erreurs ont été détectées dans les données à importer. <!>")
    for msg in errors:
        logging.error(msg)

prompt = ""
while prompt != "v":
    prompt = input(">> Veuillez contrôler les données, puis taper 'v' pour continuer, ou 'q' pour quitter...")
    if prompt == "q":
        sys.exit(1)


# *** 6- Insertion des données dans les tables Analytique
with open(affaires_file) as f:
    next(f)  # saute la première ligne

    for line in f:
        affaire = Affaire.from_csv(line)

        sql = """ INSERT INTO tbl_Affaires ( strMOId, strMOeId, strEntrepriseId, strCommneId, strLieux, strTypeId, dtmCommande, Ref,
                                            blnMarche, dblMarche, intTypeContrat, strCT, strAvancement, strLiaisonControle, intDevisId, blnTarification,
                                            blnAnalyse, remarques, strSituation, dtmFin, intCoefFG )
           SELECT {strMOId}, {strMOeId}, {strEntrepriseId}, {strCommneId}, {strLieux}, {strTypeId}, {dtmCommande},
           {Ref}, {blnMarche}, {dblMarche}, {intTypeContrat}, {strCT}, {strAvancement}, {strLiaisonControle}, {intDevisId},
           {blnTarification}, {blnAnalyse}, {remarques}, {strSituation}, {dtmFin}, {intCoefFG}
           """.format(strMOId=affaire.strMOId,
                      strMOeId=affaire.strMOeId,
                      strEntrepriseId=affaire.strEntrepriseId,
                      strCommneId=affaire.strCommneId,
                      dtmCommande=affaire.dtmCommande,
                      Ref=affaire.Ref,
                      blnMarche=affaire.blnMarche,
                      dblMarche=affaire.dblMarche,
                      intTypeContrat=affaire.intTypeContrat,
                      strCT=affaire.strCT,
                      strAvancement=affaire.strAvancement,
                      strLiaisonControle=affaire.strLiaisonControle,
                      intDevisId=affaire.intDevisId,
                      blnTarification=affaire.blnTarification,
                      blnAnalyse=affaire.blnAnalyse,
                      remarques=affaire.remarques,
                      strSituation=affaire.strSituation,
                      dtmFin=affaire.dtmFin,
                      intCoefFG=affaire.intCoefFG)

        analytique_db.execute(sql)

        affaire.affaireId = analytique_db.first("SELECT TOP 1 DblAffaireId FROM tbl_Affaires ORDER BY DblAffaireId DESC").DblAffaireId
        # Stocker cette valeur dans un dico associéeau strLiaisonControle, pour la maj de tblIntervs

        # Insérer ici les intervs liées à l'affaire (?)

        # Maj champs MOS, puis committer


with open(intervs_file) as f:
    next(f)  # saute la première ligne

    for line in f:
        interv = Interv.from_csv(line)


# Reprendre la fonction PostVerif

# Maj des temps d'installation