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






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("Mise à jour de tblso_Rate_Analyse")



        # 1. Extrait les données des essais
#         sql = """SELECT SI_T.SI_Spare1, SI_T.SI_Spare2, SI_T.SI_MediaNumber1, S_T.S_SectionFlow, S_T.S_StartNode, S_T.S_StartNodeType,
#                     S_T.S_StartNodeCoord_Z, S_T.S_EndNode, S_T.S_EndNodeType, 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_EndStreet, P_T.P_Name, P_T.P_Date, S_T.S_Location, S_T.S_StartStreet, S_T.S_Spare2, SI_T.SI_AutoNumber,
#                     S_T.S_PipeShape, S_T.S_Pipelength, S_T.S_Spare3, SI_T.SI_ID, S_T.S_ID, SI_T.SI_InspName, S_T.S_SectionPurpose,
#                     S_T.S_SectionUse, S_T.S_SectionType, SI_T.SI_Spare0 AS Test_ecoulement, SI_T.SI_Date, SI_T.SI_ReasonOfInspection,
#                     S_T.S_SectionPurpose, SO_T.SO_ID, SO_T.SO_Rate, SO_T.SO_Photonumber1, SO_T.SO_Photonumber2
#                 FROM ((P_T INNER JOIN S_T ON P_T.P_ID = S_T.S_Project_ID) INNER JOIN SI_T ON S_T.S_ID = SI_T.SI_Section_ID)
#                     INNER JOIN SO_T ON SI_T.SI_ID = SO_T.SO_Inspecs_ID
#                 WHERE SO_T.SO_Rate <= 6 or SO_T.SO_Rate Is Null
#                 ORDER BY SI_T.SI_InspName, SI_T.SI_MediaNumber1
#                 """

#         sql = """




        # 2. Enrichir / transformer les données

        # * SI_Spare1                             => lngChantierId (convertir en num)
        # * SI_Spare2                             => bytIntervId (convertir en num)
        # * SI_MediaNumber1                       => [Classement troncons]
        # * S_StartNode, S_EndNode, S_SectionFlow => Nom_troncon ("Start-End" si S_SectionFlow == 2, sinon "End-Start")
        # * S_StartNodeType                       => S_StartNodeType (get_lib, tbl='S_StartNodeType'; trim)
        # * S_SectionFlow                         => Sens_ecoul ('>>' si SectionFlow = '1', '<<' si SectionFlow=2, '' sinon)
        # * S_StartNodeCoord_Z                    => S_StartNodeCoord_Z
        # * S_EndNodeCoord_Z                      => S_EndNodeCoord_Z
        # * S_Sectionlength
        # * S_EndNodeType                         => S_EndNodeType (get_lib, tbl='S_EndNodeType'; trim)
        # ** S_SectionPurpose                      => [Nature Reseau] (get_lib, tbl='S_SectionPurpose')
        # ** S_SectionUse                          => [Fonction Reseau] (get_lib, tbl='S_SectionUse')
        # ** S_SectionType                         => [Type de Réseau] (get_lib, tbl='S_SectionType')
        # S_PipeMaterial                        => Materiau (get_lib, tbl='S_PipeMaterial')
        # *                                    ET => MateriauCourt (strMateriauCourt FROM tblMateriaux WHERE strWincanId='{}') (c'est celui qui est utilisé)
        # * S_YearLayed                           => ANNEE_POSE
        # * S_PipeDia                             => Diametre (get_lib, tbl='S_PipeDia')
        # * S_Situation                           => Route (get_lib, tbl='S_Situation')
        # * S_Spare1                              => NRoute
        # S_EndStreet                           => Rue
        # P_Name                                => P_Name
        # P_Date                                => P_Date
        # S_Location                            => Loc (strTiersMnemo FROM tblTiers WHERE strCompteComptable='{}')
        # S_StartStreet                         => MOu
        # S_Spare2                              => N°CHIMERE
        # SI_AutoNumber                         => SI_AutoNumber
        # * S_PipeShape                           => S_PipeShape
        # * S_Pipelength                          => S_Pipelength
        # * S_Spare3                              => nb_Arbres (get_lib, tbl='S_Spare3')
        # SI_ID
        # S_ID
        # ? SI_InspName
        # * SI_Spare0                             => Test_ecoulement
        # * SI_Date                               => SI_Date
        # SI_ReasonOfInspection                 => raison_insp (get_lib, tbl='SI_ReasonOfInspection')
        # * SO_Photonumber1, SO_Photonumber2      => nb_photos (VraiFaux([SO_Photonumber2] Est Pas Null;1;0)+VraiFaux([SO_Photonumber1] Est Pas Null;1;0))


        # 3. Reorganisation


        # Les données sont croisées de cette manière:
        # Regroupement des lignes sur la plupart des champs
        # Compte de SO_ID
        # cpt_photos = somme de nb_photos
        # Les résultats (SO_Rate) passent en colonnes, avec un decompte par valeur (comprise entre 1 et 5)




        # 4. Insertion
        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
                        )
                SELECT  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]
                FROM r_so_rate_Analyse
                WHERE lngChantierId= {} AND bytIntervId= {}
                """.format(chantier, interv)

        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("Mise à jour du resultat global dans tblvideointervs")
        resGlobal = ''
        "Update  tblvideointervs set strResGlobal='{}' WHERE lngChantierId= {} AND bytIntervId= {}".format(resGlobal, chantier, interv)

        # Met à jour la table tblVideoBases pour marquer le chantier comme traité
        logger.info("Mise à jour de tblVideoBases")
        sql = "update tblVideoBases set blnWincan=-1,bytNbInterv={} where  lngChantierId={}".format(interv, chantier)

        # Met à jour les données du réseau dans tblChantiers
        logger.info("Mise à jour des données du réseau dans tblChantiers")
        fctReseauId, natureReseauId, typeReseauId = 0, 0, 0
        sql = "update tblChantiers set  bytFoncReseauId ={} , bytNatureReseauId={}, bytTypeReseauId={} where  lngChantierId={}".format(fctReseauId, natureReseauId, typeReseauId, chantier)

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