scripts-pde/qgis_migration_postgis.py

'''
Created on 4 juin 2018

@author: olivier.massot
'''
import logging
import re

from path import Path

from core.pde import CSigDb
from core.sqlformatter import SqlFormatter


logger = logging.getLogger("migration_qgis")
logger.setLevel(logging.DEBUG)
ch = logging.StreamHandler()
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
ch.setFormatter(formatter)
logger.addHandler(ch)

CSigDb.server = "TR-POSTGIS-02"
CSigDb.user = "ControlesSIG_admin"
CSigDb.pwd = "7eMWBhfNW4X8yKNDkaj0"

# Connexion à ControlesSig (postgres)
csig_db = CSigDb(autocommit=False)

Sql = SqlFormatter()

err_file = Path(__file__).parent / "migration_err.csv"
err_file.remove_p()

def add_err(txt):
    err_file.write_lines([txt], append=True)

# nettoyage des tables cibles
csig_db.execute("DELETE FROM t_points_compactage")
csig_db.execute("DELETE FROM t_troncons")
csig_db.execute("DELETE FROM t_regards")
csig_db.execute("DELETE FROM t_chantiers")

csig_db.execute("SELECT setval('t_points_compactage_id_seq', (SELECT MAX(id) FROM t_points_compactage) + 1)")
csig_db.execute("SELECT setval('t_regards_id_seq', (SELECT MAX(id) FROM t_regards) + 1)")
csig_db.execute("SELECT setval('t_troncons_id_seq', (SELECT MAX(id) FROM t_troncons) + 1)")
csig_db.execute("SELECT setval('t_chantiers_id_seq', (SELECT MAX(id) FROM t_chantiers) + 1)")

csig_db.commit()
logger.info("* tables reinitialisees")

# reinitialisation des tables de contrôle
csig_db.execute("DELETE FROM c_types_chantiers")
csig_db.execute("DELETE FROM c_resultats_compactage")
csig_db.execute("DELETE FROM c_resultats_etancheite")
csig_db.execute("DELETE FROM c_resultats_video")

csig_db.execute("""INSERT INTO c_types_chantiers (id, libelle)
                    VALUES (1, 'CE-ITV'), (2, 'CC'), (3, '?') """)


csig_db.execute("""INSERT INTO c_resultats_compactage (id, libelle)
                   VALUES ('+', 'Positif'), ('-', 'Négatif'), ('=', 'Sondage'), ('*', 'Deuxième essai'), ('?', '(Inconnu)') """)


csig_db.execute("""INSERT INTO c_resultats_etancheite (id, libelle)
                    VALUES ('+', 'Positif'), ('-', 'Négatif'), ('?', '(Inconnu)') """)


csig_db.execute("""INSERT INTO c_resultats_video (id, libelle)
                    VALUES ('ABS', 'Pas de défaut'), ('ACC', 'Acceptable'), ('INT', 'Intolérable'), ('?', '(Inconnu)') """)

csig_db.commit()
logger.info("* tables de controle reinitialisees")


# correction des noms chantiers des tables compactage
# > remplacement des '_'
csig_db.execute(r"""UPDATE i_points_compactage SET nomchantie = regexp_replace(nomchantie, '^(\d{5,6})([_-]S?\d*)*[\s_](.*)$', '\1\2 \3')""")
csig_db.execute(r"""UPDATE i_points_compactage_h SET nomchantie = regexp_replace(nomchantie, '^(\d{5,6})([_-]S?\d*)*[\s_](.*)$', '\1\2 \3')""")
csig_db.execute(r"""UPDATE i_emprises_compactage SET nomchantie = regexp_replace(nomchantie, '^(\d{5,6})([_-]S?\d*)*[\s_](.*)$', '\1\2 \3')""")
csig_db.execute(r"""UPDATE i_points_compactage SET nomchantie = regexp_replace(nomchantie, '^(\d{5,6})_(S?[\d+|\s])(.*)$', '\1-\2\3')""")
csig_db.execute(r"""UPDATE i_points_compactage_h SET nomchantie = regexp_replace(nomchantie, '^(\d{5,6})_(S?[\d+|\s])(.*)$', '\1-\2\3')""")
csig_db.execute(r"""UPDATE i_emprises_compactage SET nomchantie = regexp_replace(nomchantie, '^(\d{5,6})_(S?[\d+|\s])(.*)$', '\1-\2\3')""")
csig_db.commit()

# > mise en up case
csig_db.execute(r"UPDATE i_emprises SET si_inspnam = upper(si_inspnam)")
csig_db.execute(r"UPDATE i_emprises_compactage SET nomchantie = upper(nomchantie)")
csig_db.execute(r"UPDATE i_points_compactage SET nomchantie = upper(nomchantie)")
csig_db.execute(r"UPDATE i_points_compactage_h SET nomchantie = upper(nomchantie)")
csig_db.execute(r"UPDATE i_regards SET si_inspnam = upper(si_inspnam)")
csig_db.execute(r"UPDATE i_troncons SET si_inspnam = upper(si_inspnam)")
csig_db.execute(r"UPDATE i_regards_h SET si_inspnam = upper(si_inspnam)")
csig_db.execute(r"UPDATE i_troncons_h SET si_inspnam = upper(si_inspnam)")
csig_db.execute(r"UPDATE i_troncons SET dg = upper(dg)")
csig_db.execute(r"UPDATE i_troncons_h SET dg = upper(dg)")
csig_db.commit()

# > resultats manquants
csig_db.execute(r"UPDATE i_points_compactage SET resultat = '?' WHERE resultat IS Null")
csig_db.execute(r"UPDATE i_regards SET resetanch = '?' WHERE resetanch IS Null")
csig_db.execute(r"UPDATE i_regards_h SET resetanch = '?' WHERE resetanch IS Null")
csig_db.execute(r"UPDATE i_troncons SET dg = '?' WHERE dg IS Null")
csig_db.execute(r"UPDATE i_troncons_h SET dg = '?' WHERE dg IS Null")
csig_db.execute(r"UPDATE i_troncons SET resetanch = '?' WHERE resetanch IS Null")
csig_db.execute(r"UPDATE i_troncons_h SET resetanch = '?' WHERE resetanch IS Null")
csig_db.execute(r"UPDATE i_troncons SET dg = '?' WHERE dg = 'NULL'")
csig_db.commit()

logger.info("* corrections effectuées")

rx = re.compile(r"^(\d{5,6})(-S?\d{0,2})*[\s_]?(.*)$")

def norm(inspname):
    if inspname is None:
        inspname = ""
    return inspname


def find_num_chantier(inspname):
    parsed = rx.search(inspname)
    if not parsed:
        logger.error("Numero de chantier illisible: {}".format(inspname))
        return 0
    return parsed.group(1)


# 1. import des chantiers

logger.info("* import: i_emprises")
qry = csig_db.read("""SELECT * FROM i_emprises""")

for row in qry:

    inspname = norm(row.si_inspnam)
    num_chantier = find_num_chantier(inspname)

    csig_db.execute(Sql.format(""" INSERT INTO t_chantiers( id_type_chantier, numero, nom, geom, x_label, y_label, angle_label, archive )
                        VALUES (1, {}, {:text}, {:text}, {}, {}, {}, False)
                    """, num_chantier, inspname, row.geom, row.x_etiq, row.y_etiq, row.angle_etiq))

logger.info("* import: i_emprises_compactage")
qry = csig_db.read("""SELECT * FROM i_emprises_compactage""")

for row in qry:

    inspname = norm(row.nomchantie)

    num_chantier = find_num_chantier(inspname)

    csig_db.execute(Sql.format(""" INSERT INTO t_chantiers( id_type_chantier, numero, nom, geom, x_label, y_label, angle_label, archive )
                        VALUES (2, {}, {:text}, {:text}, {}, {}, {}, False)
                    """, num_chantier, inspname, row.geom, row.x_etiq, row.y_etiq, row.angle_etiq))


# 2. import des essais de compactage

logger.info("* import: i_points_compactage")
qry = csig_db.read("""SELECT * FROM i_points_compactage""")

for row in qry:

    inspname = norm(row.nomchantie)

    chantier = csig_db.first(Sql.format("SELECT id FROM t_chantiers WHERE nom={:text}", inspname))
    if not chantier:
        logger.error("Chantier de compactage introuvable: {}".format(inspname))
        add_err("Chantier de compactage manquant|{}".format(inspname))
        continue

    res_cc = row.resultat
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_compactage WHERE id={:text}", row.resultat)):
        logger.error("Resultat compactage inconnu ({}, {})".format(inspname, row.resultat))
        res_cc = "?"

    csig_db.execute(Sql.format("""INSERT INTO t_points_compactage(numero, nom, id_chantier, res_cc, geom, x_label, y_label, angle_label, archive)
                       VALUES ({}, {:text}, {}, {:text}, {:text}, {}, {}, {}, False)
                    """, row.numero, row.name, chantier.id, res_cc, row.geom, row.x_etiq, row.y_etiq, row.angle_etiq))


logger.info("* import: i_points_compactage_h")
qry = csig_db.read("""SELECT * FROM i_points_compactage_h""")

for row in qry:

    inspname = norm(row.nomchantie)

    chantier = csig_db.first(Sql.format("SELECT id FROM t_chantiers WHERE nom={:text}", inspname))
    if not chantier:
        logger.error("Chantier de compactage introuvable: {}".format(inspname))
        add_err("Chantier de compactage manquant|{}".format(inspname))
        continue

    res_cc = row.resultat
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_compactage WHERE id={:text}", row.resultat)):
        logger.error("Resultat compactage inconnu ({})".format(inspname))
        res_cc = "?"

    csig_db.execute(Sql.format("""INSERT INTO t_points_compactage(numero, nom, id_chantier, res_cc, geom, x_label, y_label, angle_label, archive)
                       VALUES ({}, {:text}, {}, {:text}, {:text}, {}, {}, True)
                    """, row.numero, row.name, chantier.id, res_cc, row.geom, row.x_etiq, row.y_etiq, row.angle_etiq))


# 3. import des essais des regards

logger.info("* import: i_regards")
qry = csig_db.read("""SELECT * FROM i_regards""")

for row in qry:

    inspname = norm(row.si_inspnam)

    if csig_db.exists(Sql.format("SELECT id FROM t_regards WHERE s_autonumber={} AND nom={:text}", row.s_autonumb, row.id_node)):
        logger.error("Ce regard a déjà été créé: {}, {}".format(row.s_autonumb, row.id_node))

    chantier = csig_db.first(Sql.format("SELECT id FROM t_chantiers WHERE nom={:text}", inspname))
    if not chantier:
        logger.error("Chantier vidéo introuvable: {}".format(inspname))
        add_err("Chantier ITV manquant|{}".format(inspname))
        continue

    res_ce = row.resetanch
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_etancheite WHERE id={:text}", row.resetanch)):
        logger.error("Resultat etancheite inconnu ({})".format(inspname))
        res_ce = "?"

    csig_db.execute(Sql.format("""INSERT INTO t_regards(nom, id_chantier, res_ce, s_autonumber, geom, label, x_label, y_label, angle_label, archive)
                       VALUES ({:text}, {}, {:text}, {}, {:text}, {:text}, {}, {}, {}, False)
                    """, row.id_node, chantier.id, res_ce, row.s_autonumb, row.geom, row.etiquette, row.x_etiq, row.y_etiq, row.angle_etiq))


logger.info("* import: i_regards_h")
qry = csig_db.read("""SELECT * FROM i_regards_h""")

for row in qry:

    inspname = norm(row.si_inspnam)

    if csig_db.exists(Sql.format("SELECT id FROM t_regards WHERE s_autonumber={} AND nom='{}'", row.s_autonumb, row.id_node)):
        logger.error("Ce regard a déjà été créé: {}, {}".format(row.s_autonumb, row.id_node))

    chantier = csig_db.first(Sql.format("SELECT id FROM t_chantiers WHERE nom={:text}", row.si_inspnam))
    if not chantier:
        logger.error("Chantier vidéo introuvable: {}".format(row.si_inspnam))
        add_err("Chantier ITV manquant|{}".format(inspname))
        continue

    res_ce = row.resetanch
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_etancheite WHERE id={:text}", row.resetanch)):
        logger.error("Resultat etancheite inconnu ({})".format(row.nomchantie))
        res_ce = "?"

    csig_db.execute(Sql.format("""INSERT INTO t_regards(nom, id_chantier, res_ce, s_autonumber, geom, label, x_label, y_label, angle_label, archive)
                       VALUES ({:text}, {}, {:text}, {}, {:text}, {:text}, {}, {}, {}, True)
                    """, row.id_node, chantier.id, res_ce, row.s_autonumb, row.geom, row.etiquette, row.x_etiq, row.y_etiq, row.angle_etiq))

logger.info("* regards importés")

# 3. import des essais sur troncons

logger.info("* import: i_troncons")
qry = csig_db.read("""SELECT * FROM i_troncons""")

for row in qry:

    inspname = norm(row.si_inspnam)

    chantier = csig_db.first(Sql.format("SELECT id FROM t_chantiers WHERE nom={:text}", inspname))
    if not chantier:
        logger.error("Chantier vidéo introuvable: {}".format(inspname))
        add_err("Chantier ITV manquant|{}".format(inspname))
        continue

    r1 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND s_autonumber={}", row.s_startnod, row.s_autonumb))
    if not r1:
        r1 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND id_chantier={}", row.s_startnod, chantier.id))
        if not r1:
            logger.error("Regard introuvable:  {}, {}, {}".format(row.s_startnod, row.s_autonumb, inspname))
            add_err("Regard introuvable|{}|{}|{}".format(row.s_startnod, row.s_autonumb, inspname))
            continue

    r2 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND s_autonumber={}", row.s_endnode, row.s_autonumb))
    if not r2:
        r2 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND id_chantier={}", row.s_endnode, chantier.id))
        if not r2:
            logger.error("Regard introuvable: {}, {}, {}".format(row.s_endnode, row.s_autonumb, inspname))
            add_err("Regard introuvable|{}|{}|{}".format(row.s_startnod, row.s_autonumb, inspname))
            continue

    res_ce = row.resetanch
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_etancheite WHERE id={:text}", row.resetanch)):
        logger.error("Resultat compactage inconnu ({})".format(inspname))
        res_ce = "?"
    res_itv = row.dg
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_video WHERE id={:text}", row.dg)):
        logger.error("Resultat vidéo inconnu ({})".format(inspname))
        res_itv = "?"

    csig_db.execute(Sql.format("""INSERT INTO t_troncons( nom, id_chantier, id_regard_depart, id_regard_fin, s_autonumber, si_autonumber, res_itv, res_ce, geom, archive )
                       VALUES ({:text}, {}, {}, {}, {}, {}, {:text}, {:text}, {:text}, False)
                    """, row.nomtroncon, chantier.id, r1.id, r2.id, row.s_autonumb, row.si_autonum, res_itv, res_ce, row.geom))


logger.info("* import: i_troncons_h")
qry = csig_db.read("""SELECT * FROM i_troncons_h""")

for row in qry:

    inspname = norm(row.si_inspnam)

    chantier = csig_db.first(Sql.format("SELECT id FROM t_chantiers WHERE nom={:text}", inspname))
    if not chantier:
        logger.error("Chantier vidéo introuvable: {}".format(inspname))
        add_err("Chantier ITV manquant|{}".format(inspname))
        continue

    r1 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND s_autonumber={}", row.s_startnod, row.s_autonumb))
    if not r1:
        r1 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND id_chantier={}", row.s_startnod, chantier.id))
        if not r1:
            logger.error("Regard introuvable:  {}, {}, {}".format(row.s_startnod, row.s_autonumb, inspname))
            add_err("Regard introuvable|{}|{}|{}".format(row.s_startnod, row.s_autonumb, inspname))
            continue

    r2 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND s_autonumber={}", row.s_endnode, row.s_autonumb))
    if not r2:
        r2 = csig_db.first(Sql.format("SELECT id FROM t_regards WHERE nom={:text} AND id_chantier={}", row.s_endnode, chantier.id))
        if not r2:
            logger.error("Regard introuvable: {}, {}, {}".format(row.s_endnode, row.s_autonumb, inspname))
            add_err("Regard introuvable|{}|{}|{}".format(row.s_startnod, row.s_autonumb, inspname))
            continue

    res_ce = row.resetanch
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_etancheite WHERE id={:text}", row.resetanch)):
        logger.error("Resultat etancheite inconnu ({})".format(inspname))
        res_ce = "?"
    res_itv = row.dg
    if not csig_db.exists(Sql.format("SELECT id FROM c_resultats_video WHERE id={:text}", row.dg)):
        logger.error("Resultat vidéo inconnu ({})".format(inspname))
        res_itv = "?"

    csig_db.execute(Sql.format("""INSERT INTO t_troncons( nom, id_chantier, id_regard_depart, id_regard_fin, s_autonumber, si_autonumber, res_itv, res_ce, geom, archive )
                       VALUES ({:text}, {}, {}, {}, {}, {}, {:text}, {:text}, {:text}, True)
                    """, row.nomtroncon, chantier.id, r1.id, r2.id, row.s_autonumb, row.si_autonum, res_itv, res_ce, row.geom))

logger.info("Import terminé")

csig_db.commit()