scripts-pde/wincan2ctrl.py

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

    olivier.massot, mai 2018
'''
import logging

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

chantier = 185000
interv = 1
commande_id = 1


# 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




logger.info("# Traitement du chantier %s, intervention %s", chantier, interv)

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
        """

for nontraite in wincan_db.read(sql):

    # Liste les inspections pas encore traitées
    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(nontraite.SI_InspName)


    for inspection in wincan_db.read(sql):

        reason_lib = get_lib("SI_REASONOfINSPECTION", inspection.SI_ReasonOfInspection)

        # 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, interv, inspection.mindate, inspection.maxdate, inspection.Equipe, inspection.Mat, commande_id, inspection.long_insp, inspection.InspMethod, reason_lib)
        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, interv, 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()