scripts-pde/wincan2ctrl.py

'''
    Met à jour la base controle avec les données d'un chantier Wincan

    usage: wincan2ctrl [-c=idchantier] [-i=idinterv] [-m=idcommande] [-n=inspectioname]

    Options:
      -c                      Passe le code chantier
      -i                      Passe le numero d'intervention
      -m                      Passe le numero de commande
      -n                      Passe le nom de l'inpection wincan
      -h, --help              Affiche l'aide

    @olivier.massot, mai 2018
'''
import logging
import sys

from docopt import docopt
from path import Path

from core import logconf
from core.db import AccessSqlHelper
from core.pde import ControlesDb, WincanDb, CommunDb, InspectionTronconWincan
from core.sqlformatter import SqlFormatter


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


# # POUR TESTER, décommenter les lignes suivantes
# > Lancer le script /resources/test_wincan2ctrl.py pour reinitialiser les données de la base de test
##-----------------------------------------------

ControlesDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\cg67Parc_data.mdb")
WincanDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\Wincan\parc_2007\DB\PARC_2007.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   >>>>>>>>>>>>>>>>>")

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

logger.info("Initialisation...")

Sql = SqlFormatter()
sqlHelper = AccessSqlHelper

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

# Connexion à Wincan
wincan_db = WincanDb(autocommit=False)

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


def get_lib(tbl_id, lib_id):
    return wincan_db.first(Sql.format("""SELECT LIB_ID, LIB_LIB, TLB_ID
                                        FROM LISTE_LIBELLE_WINCAN
                                        WHERE TLB_ID='{}' AND LIB_ID={}""", tbl_id, lib_id)).LIB_LIB

def get_materiau(wincan_id):
    return controles_db.first(Sql.format("""SELECT strMateriauCourt
                                            FROM tblMateriaux
                                            WHERE strWincanId='{}'""", wincan_id)).strMateriauCourt

# Parse les arguments
args = docopt(__doc__, help=False)
chantier_id = args["-c"] if args["-c"] else 0
interv_id = args["-i"] if args["-i"] else 0
commande_id = args["-m"] if args["-m"] else 0
inspname = args["-n"] if args["-n"] else ""


# Demande à l'utilisateur de saisir les informations qui n'ont pas été passées en argument
if not inspname:
    # Propose une liste d'inspections possibles
    sql = """SELECT SI_InspName, Max(SI_Date) AS DateMax, SI_Spare1, SI_Spare2
                FROM SI_T
                GROUP BY SI_InspName, SI_Spare1, SI_Spare2
                HAVING SI_spare1 Is Null OR Len([SI_spare1])=0
                ORDER BY Max(SI_T.SI_Date) DESC
            """
    candidats = wincan_db.read_all(sql)

    print("Veuillez choisir une inspection Wincan à traiter en saisissant son numéro:")
    for i, candidat in enumerate(candidats):
        print("[{}] - {}".format(i, candidat))

    while not inspname:
        reponse = input("> ")
        try:
            inspname = candidats[int(reponse)].SI_InspName
        except ValueError:
            logger.error("(!) Valeur invalide")

if not chantier_id:
    chantier_id = input("Veuillez saisir le code chantier: ")
if not interv_id:
    interv_id = input("Veuillez saisir le numero de l'intervention: ")
if not commande_id:
    commande_id = input("Veuillez saisir le code de la commande: ")


# Affiche les infos et demande confirmation avant ed lancer le traitement
logger.info("## Traitement de l'inspection Wincan: %s", inspname)
logger.info("Code chantier: %s", inspname)
logger.info("Numero d'intervention: %s", inspname)
logger.info("Numero de commande: %s", inspname)
if input("Voulez-vous continuer? (o/n)") != 'o':
    if input("Etes-vous sûr de vouloir annuler l'opération? (o/n)") == 'o':
        logger.info("Opération annulée par l'utilisateur")
        sys.exit()



sql = """SELECT SI_T.SI_InspName, Sum(S_T.S_Sectionlength) AS Long_insp, Min(SI_T.SI_Date) AS mindate, Max(SI_T.SI_Date) AS maxdate, Max(SI_T.SI_Operator) AS Equipe, Max(SI_T.SI_Vehicle) AS Mat, Max(SI_T.SI_InspMethod) AS InspMethod, Max(SI_T.SI_ReasonOfInspection) AS MaxDeSI_ReasonOfInspection, Min(S_T.S_SectionPurpose) AS nature, Min(S_T.S_SectionUse) AS Fonction, Min(S_T.S_SectionType) AS Type
        FROM S_T INNER JOIN SI_T ON S_T.S_ID = SI_T.SI_Section_ID
        GROUP BY SI_T.SI_InspName
        HAVING Sum(S_T.S_Sectionlength) Is Not Null AND Max(SI_T.SI_Operator) Is Not Null AND Max(SI_T.SI_Vehicle) Is Not Null AND Max(SI_T.SI_InspMethod) Is Not Null AND SI_InspName='{}'
        """.format(inspname)

for inspection in wincan_db.read(sql):

    # insère les inspections dans la table tblVideoIntervs de Controles

    logger.info("Mise à jour de tblVideoIntervs")
    sql = """INSERT INTO tblVideoIntervs ( lngChantierId, bytIntervId, dtmIntervDu, dtmIntervAu, strEquipeId, intlMaterielID, bytCommandeId, lngTroncon, SI_InspMethod, SI_ReasonOfInspection )
            SELECT {} AS chantier, {} AS interv, {} AS mindate, {} AS maxdate, {} AS equipe, {} AS mat, {} AS commande, {} AS long_insp, {} AS methode, {} AS reason
            """.format(chantier_id, interv_id, inspection.mindate, inspection.maxdate, inspection.Equipe, inspection.Mat, commande_id, inspection.long_insp, inspection.InspMethod, get_lib("SI_REASONOfINSPECTION", inspection.SI_ReasonOfInspection))
    controles_db.execute(sql)

    # Met a jour les champs SI_Spare1 et SI_Spare2 de la table Wincan pour marquer les lignes comme importées
    logger.info("Mise à jour en retour de SI_T")
    sql = "update SI_T set SI_Spare1={}, SI_Spare2={} where  SI_InspName='{}'".format(chantier_id, interv_id, inspection.SI_InspName)
    wincan_db.execute(sql)

    # Met a jour la table tbl_so_rate de Controles
    logger.info("Traitement des inspections de tronçons")

    # Extrait les données des tronçons

    sql = """SELECT SI_T.SI_ID, S_T.S_ID, SI_T.SI_InspName, SI_T.SI_Spare1, SI_T.SI_Spare2, SI_T.SI_AutoNumber, SI_T.SI_MediaNumber1, S_T.S_SectionFlow,
                S_T.S_StartNode, S_T.S_EndNode, S_T.S_StartNodeType, S_T.S_EndNodeType, S_T.S_StartNodeCoord_Z, S_T.S_EndNodeCoord_Z,
                S_T.S_Sectionlength, S_T.S_SectionPurpose, S_T.S_SectionUse, S_T.S_SectionType, S_T.S_PipeMaterial, S_T.S_YearLayed,
                S_T.S_PipeDia, S_T.S_Situation, S_T.S_Spare1, S_T.S_PipeShape, S_T.S_Pipelength, S_T.S_Spare3,
                SI_T.SI_Spare0 AS Test_ecoulement, SI_T.SI_Date
            FROM S_T INNER JOIN SI_T ON S_T.S_ID = SI_T.SI_Section_ID
            """

    for data in wincan_db.read(sql):

        logger.info("* Traitement de %s (S_ID: %s)", data.SI_InspName, data.S_ID)

        inspection = InspectionTronconWincan()

        inspection.s_guid = data.S_ID
        inspection.si_guid = data.SI_ID
        inspection.nom_chantier = data.SI_InspName
        inspection.lng_chantier_id = data.SI_Spare1
        inspection.byt_interv_id = data.SI_Spare2
        inspection.si_autonumber = data.SI_AutoNumber
        inspection.classement_troncons = data.SI_MediaNumber1
        inspection.nom_troncon = ("{0}-{1}" if data.S_SectionFlow == 2 else "{1}-{0}").format(data.S_StartNode, data.S_EndNode)
        inspection.startnode_type = get_lib(data.S_T.S_StartNodeType, "S_StartNodeType")
        inspection.endnode_type = get_lib(data.S_T.S_StartNodeType, "S_EndNodeType")
        inspection.sens_ecoul = ">>" if data.S_SectionFlow == '1' else ('<<' if data.S_SectionFlow == '2' else '')
        inspection.startnode_z = data.S_StartNodeCoord_Z
        inspection.endnode_z = data.S_EndNodeCoord_Z
        inspection.section_length = data.S_Sectionlength
        inspection.section_purpose = data.S_SectionPurpose
        inspection.section_use = data.S_SectionUse
        inspection.section_type = data.S_SectionType
        inspection.materiau = get_materiau(data.S_PipeMaterial)
        inspection.annee_pose = data.S_YearLayed
        inspection.diametre = get_lib(data.S_PipeDia, "S_PipeDia")
        inspection.route = get_lib(data.S_Situation, "S_Situation")
        inspection.n_route = data.S_Spare1
        inspection.pipe_shape = data.S_PipeShape
        inspection.pipe_length = data.S_Pipelength
        inspection.arbres = get_lib(data.S_Spare3, "S_Spare3")
        inspection.test_ecoulement = data.SI_Spare0
        inspection.si_date = data.SI_Date

        sql = """ SELECT SO_T.SO_ID, SO_T.SO_Rate, SO_T.SO_Photonumber1, SO_T.SO_Photonumber2
                    FROM SO_T
                    WHERE SO_T.SO_Inspecs_ID='{}'
                """.format(data.SI_ID)

        # Parcours les opérations réalisées au cours de l'inspection du tronçon
        for opdata in wincan_db.read(sql):
            inspection.nb_ops += 1

            if opdata.SO_Rate == 1:
                inspection.rate_1 += 1
            elif opdata.SO_Rate == 2:
                inspection.rate_2 += 1
            elif opdata.SO_Rate == 3:
                inspection.rate_3 += 1
            elif opdata.SO_Rate == 4:
                inspection.rate_4 += 1
            elif opdata.SO_Rate == 5:
                inspection.rate_5 += 1
            elif opdata.SO_Rate >= 6:
                logger.error("Attention: une valeur de [SO_Rate] supérieure à 5 a été enregistrée (SO_ID: %s)", opdata.SO_ID)

            if opdata.SO_Photonumber1:
                inspection.cpt_photos += 1
            if opdata.SO_Photonumber2:
                inspection.cpt_photos += 1

        logger.info("\t- Mise à jour de tblso_Rate_Analyse")
        sql = """INSERT INTO tblso_Rate_Analyse (
                        lngChantierId, bytIntervId, SI_InspName, SI_AutoNumber,
                        [Classement tronons], Nom_troncon, S_StartNodeType, Sens_ecoul, S_EndNodeType,
                        S_PipeShape, MateriauCourt, SI_Date, nb_Arbres, ANNEE_POSE,
                        Route, NRoute, Test_ecoulement, MaxDeS_StartNodeCoord_Z, MaxDeS_EndNodeCoord_Z,
                        MaxDeS_Sectionlength, MaxDeS_Pipelength, MaxDeDiametre, cpt_Photos, [Total de SO_ID],
                        1, 2, 3, 4, 5
                        )
                VALUES ({inspection.lng_chantier_id}, {inspection.byt_interv_id}, {inspection.nom_chantier}, {inspection.si_autonumber},
                        {inspection.classement_troncons}, {inspection.nom_troncon}, {inspection.startnode_type}, {inspection.sens_ecoul}, {inspection.endnode_type},
                        {inspection.pipe_shape}, {inspection.materiau}, {inspection.si_date}, {inspection.arbres}, {inspection.annee_pose},
                        {inspection.route}, {inspection.n_route}, {inspection.test_ecoulement}, {inspection.startnode_z}, {inspection.endnode_z},
                        {inspection.section_length}, {inspection.pipe_length}, {inspection.diametre}, {inspection.nb_photos}, {inspection.nb_ops},
                        {inspection.rate_1}, {inspection.rate_2}, {inspection.rate_3}, {inspection.rate_4}, {inspection.rate_5}
                        )
                """.format(inspection)
        controles_db.execute(sql)

        # Met à jour tblvideointervs.strResGlobal avec le resultat global
        #  le resultat global vaut '-' si un de ces trois champs n'est pas nul: tbl_so_rate.1, tbl_so_rate.2, tbl_so_rate.3
        logger.info("\t- Mise à jour du resultat global dans tblvideointervs")
        sql = """UPDATE  tblvideointervs
                 SET strResGlobal='{}'
                 WHERE lngChantierId= {} AND bytIntervId= {}
                """.format('-' if (inspection.rate_1 or inspection.rate_2 or inspection.rate_3) else '+',
                           inspection.lng_chantier_id,
                           inspection.byt_interv_id)
        controles_db.execute(sql)

        # Met à jour la table tblVideoBases pour marquer le chantier comme traité
        logger.info("\t- Mise à jour de tblVideoBases")
        sql = """UPDATE tblVideoBases
                 SET blnWincan=-1,bytNbInterv={}
                 WHERE lngChantierId={}
                 """.format(inspection.byt_interv_id,
                            inspection.lng_chantier_id)
        controles_db.execute(sql)

        # Met à jour les données du réseau dans tblChantiers
        logger.info("\t- Mise à jour des données du réseau dans tblChantiers")
        sql = """UPDATE tblChantiers
                 SET  bytFoncReseauId ={} , bytNatureReseauId={}, bytTypeReseauId={}
                 WHERE lngChantierId={}
                 """.format(inspection.section_use,
                            inspection.section_purpose,
                            inspection.section_type,
                            inspection.inspection.lng_chantier_id)
        controles_db.execute(sql)


    logger.info("Commit des modifications")
    controles_db.commit()
    wincan_db.commit()