''' 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()