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