# -*- coding: utf-8 -*-
"""
/***************************************************************************
 Mnefzh version 2
                                 A QGIS plugin
 Méthode nationale d'évaluation des fonctionnalités des zones humides
                              -------------------
        begin                : 2023-04-26
        git sha              : $Format:%H$
        copyright            : (C) 2023 by Antoine Lemot
        email                : antoine.lemot@cerema.fr
 ***************************************************************************/
"""


from PyQt5.QtWidgets import QFileDialog, QMessageBox
from qgis.core import *
from osgeo import ogr, osr
import sys, os, os.path, sqlite3, shutil, csv, subprocess,  unicodedata
import processing
from processing.tools import dataobjects

from .boite import boite

os.putenv("SPATIALITE_SECURITY", "relaxed")


class creerbase():

    def select_dossier_base(self):
        base = QFileDialog.getExistingDirectory(self, u"sélectionner un dossier pour la base")##, "//172.31.144.14/dossiers/sig/data/6_CARTOTHEQUE/VD/VEEE/2017_MNEFZH")
        self.lineEdit_base.setText(base)

    def select_dossier_mc(self):
        mc,_ = QFileDialog.getOpenFileName(self, u"sélectionner le fichier shp de la zone humide ","C:/Users", '*.shp *.SHP')
        self.lineEdit_mc.setText(mc)

    def select_zc_mc(self):
        zc_mc,_ = QFileDialog.getOpenFileName(self, u"sélectionner le fichier shp de la zone contributive","C:/Users",'*.shp *.SHP')
        self.lineEdit_zc_mc.setText(zc_mc)

    def select_rpg(self):
        rpg,_ = QFileDialog.getOpenFileName(self, u"sélectionner le fichier shp du rpg","C:/Users",'*.shp *.SHP')
        self.lineEdit_rpg.setText(rpg)

    def select_dossier_bdTopo(self):
        bdtopo = QFileDialog.getExistingDirectory(self, u"sélectionner le dossier 1_DONNEES_LIVRAISON de la BdTopo")##, "//172.31.144.14/dossiers/sig/data/1_REFERENTIELS/VECTEUR_IGN/BDTOPO")
        self.lineEdit_bdTopo.setText(bdtopo)


    def create_base(self):


        ### recuperation des autres parametres
        self.detailsPlainTextEdit_2.clear()
        dossier_base = self.lineEdit_base.text()
        nom_base = self.lineEdit_nom_base.text()

        path_base = os.path.join(dossier_base + '/', nom_base + '.sqlite')

        fichier_mc = self.lineEdit_mc.text()
        fichier_zc_mc = self.lineEdit_zc_mc.text()
        rpg = self.lineEdit_rpg.text()
        bd_topo = self.lineEdit_bdTopo.text()


################### Dernieres verifications avant lancement ###################

        ####### Verification du remplissage des champs #######

        if dossier_base == '':
            boite.boite_rouge(self.toolButton_base, self.lineEdit_base, self.label_base)
            boite.message(self,u"Vous n'avez pas renseigné de dossier pour l'analyse",u"Remplisser le champ avant de relancer").exec_()
            return

        if nom_base == '':
            self.lineEdit_nom_base.setStyleSheet('QLineEdit {border: 2px solid #FF0000}')
            self.label_nom_base.setStyleSheet('QLabel {color:#FF0000}')
            boite.message(self,u"Vous n'avez pas renseigné de nom pour l'analyse",u"Remplisser le champ avant de relancer").exec_()
            return

        if fichier_mc == '':
            boite.boite_rouge(self.toolButton_mc, self.lineEdit_mc, self.label_mc)
            boite.message(self,u"Vous n'avez pas renseigné de zone humide",u"Remplisser le champ avant de relancer").exec_()
            return

        ### La zone contributive n'est plus obligatoire dans le v2
        if self.checkBox_2.isChecked() and fichier_zc_mc == '':
            boite.boite_rouge(self.toolButton_zc_mc, self.lineEdit_zc_mc, self.label_zc_mc)
            boite.message(self,u"Vous n'avez pas renseigné de zone contributive",u"Remplisser le champ avant de relancer").exec_()
            return

        if rpg == '':
            boite.boite_rouge(self.toolButton_rpg, self.lineEdit_rpg, self.label_rpg)
            boite.message(self,u"Vous n'avez pas renseigné de RPG",u"Remplisser le champ avant de relancer").exec_()
            return

        if bd_topo == '':
            boite.boite_rouge(self.toolButton_bdTopo, self.lineEdit_bdTopo, self.label_bdTopo)
            boite.message(self,u"Vous n'avez pas renseigné de BD TOPO®",u"Remplisser le champ avant de relancer").exec_()
            return


        ####### Verification de l'existance d'une base du même nom #######

        if nom_base != '':
            a  = os.listdir(dossier_base)
            if nom_base + ".sqlite" in a:
                self.lineEdit_nom_base.setStyleSheet('QLineEdit {border: 2px solid #FF0000}')
                self.label_nom_base.setStyleSheet('QLabel {color:#FF0000}')
                boite.message(self, u"la base " + nom_base + u" existe déjà",u"Renommer l'analyse avant de relancer").exec_()
                return


        # ####### Verification du SRC du RPG #######
        # layer = QgsVectorLayer(rpg, "rpg", "ogr")
        # lyrCRS = layer.crs().authid()
        # if lyrCRS != 'EPSG:2154':
        #     self.lineEdit_rpg.setStyleSheet('QLineEdit {border: 2px solid #FF0000}')
        #     self.label_nom_base.setStyleSheet('QLabel {color:#FF0000}')
        #     boite.message(self, u"le RPG choisi n'est pas en projection 2154",
        #                   u"Reprojeter le RPG en EPSG:2154 - RGF93 v1 / Lambert-93").exec_()
        #     return

        # ####### Verification du SRC du site #######
        # layer = QgsVectorLayer(fichier_mc, "fichier_mc", "ogr")
        # lyrCRS = layer.crs().authid()
        # if lyrCRS != 'EPSG:2154':
        #     self.lineEdit_rpg.setStyleSheet('QLineEdit {border: 2px solid #FF0000}')
        #     self.label_nom_base.setStyleSheet('QLabel {color:#FF0000}')
        #     boite.message(self, u"le site choisi n'est pas en projection 2154",
        #                   u"Reprojeter le site en EPSG:2154 - RGF93 v1 / Lambert-93").exec_()
        #     return

        # ####### Verification du SRC de la zone contributive #######
        #
        #     layer = QgsVectorLayer(fichier_zc_mc, "fichier_zc_mc", "ogr")
        #     lyrCRS = layer.crs().authid()
        #     if lyrCRS != 'EPSG:2154':
        #         self.lineEdit_rpg.setStyleSheet('QLineEdit {border: 2px solid #FF0000}')
        #         self.label_nom_base.setStyleSheet('QLabel {color:#FF0000}')
        #         boite.message(self, u"la zone contributive choisie n'est pas en projection 2154",
        #                       u"Reprojeter la zone contributive en EPSG:2154 - RGF93 v1 / Lambert-93").exec_()
        #         return



################### Traitements ###################


        ####### Creation de la base et import de la zone contributive #######

        driver = ogr.GetDriverByName('SQLite')

        data_source = driver.CreateDataSource(path_base, options=['SPATIALITE=YES'])
        srs = osr.SpatialReference()
        srs.ImportFromEPSG(2154)

        ####### connexion a la base #######
        conn = sqlite3.dbapi2.connect(path_base)
        cur = conn.cursor()
        conn.enable_load_extension(True)
        conn.execute("SELECT load_extension('mod_spatialite')")

        req_sql = "SELECT ImportShp('{0}', '{1}', 'CP850', 2154, 'geometry' ,'pk')".format(os.path.splitext(fichier_zc_mc)[0], 'zc_' + nom_base)
        print(req_sql)

        try:
            conn.execute(req_sql)
            a1 = u"création de la base et import de la table zc réussi !"
            details = ''
            details += "\n" + a1
            self.detailsPlainTextEdit_2.appendPlainText(details)
            self.progressBar.setValue(10)
        except BaseException as erreur:
            boite.message(self, str(erreur),u"Erreur lors de la création de la base : contacter l'admin").exec_()
            return
            pass


        ####### création de l'index spatial pour zc_mc #######

        try:
            req_sql = "SELECT CreateSpatialIndex('zc_%s','GEOMETRY');" % (nom_base)
            cur.execute(req_sql)
        except BaseException as erreur:
            boite.message(self, str(erreur),
                              u"Erreur  lors de la création de l index spatial pour la zc_mc : contacter l'admin").exec_()
            return
            pass

        ####### import du site #######

        req_sql = "SELECT ImportShp('{0}', '{1}', 'CP850', 2154, 'geometry' ,'pk')".format(os.path.splitext(fichier_mc)[0], nom_base)
        print(req_sql)

        try:
            conn.execute(req_sql)
            a2 = u"import de la table mc réussi !"
            details = ''
            details += "\n" + a2
            self.detailsPlainTextEdit_2.appendPlainText(details)
            self.progressBar.setValue(20)
        except BaseException as erreur:
            boite.message(self, str(erreur),u"Erreur lors de l'import du site : contacter l'admin").exec_()
            return
            pass


        req_sql = "SELECT ElementaryGeometries('{}', 'geometry', '{}_singlepoly','gid','fid') as geom FROM {};".format(nom_base,nom_base,nom_base)
        conn.execute(req_sql)



        ####### Calcul de l'emprise spatiale du buffer 5 km autour du site ou de la zc #######


        try :
            if fichier_zc_mc == '' :
                req_sql = "select" \
                          " st_minx(st_envelope(st_buffer(geometry, 5000))) as xmin," \
                          " st_miny(st_envelope(st_buffer(geometry, 5000))) as ymin," \
                          " st_maxx(st_envelope(st_buffer(geometry, 5000))) as xmax," \
                          " st_maxy(st_envelope(st_buffer(geometry, 5000))) as ymax" \
                          " from  %s" % (nom_base)
                cur.execute(req_sql)
                tables = cur.fetchall()
                for table in tables:
                    xmin = table[0]
                    ymin = table[1]
                    xmax = table[2]
                    ymax = table[3]
                etendue = str(xmin) + ',' + str(xmax) + ',' + str(ymin) + ',' + str(ymax) + " [EPSG:2154]"
            else :
                req_sql = "select" \
                          " st_minx(st_envelope(st_buffer(geometry, 5000))) as xmin," \
                          " st_miny(st_envelope(st_buffer(geometry, 5000))) as ymin," \
                          " st_maxx(st_envelope(st_buffer(geometry, 5000))) as xmax," \
                          " st_maxy(st_envelope(st_buffer(geometry, 5000))) as ymax" \
                          " from  zc_%s" % (nom_base)
                cur.execute(req_sql)
                tables = cur.fetchall()
                for table in tables:
                    xmin = table[0]
                    ymin = table[1]
                    xmax = table[2]
                    ymax = table[3]
                etendue = str(xmin) + ',' + str(xmax) + ',' + str(ymin) + ',' + str(ymax) + " [EPSG:2154]"
        except BaseException as erreur:
            boite.message(self, str(erreur), u"Erreur lors de la définition de l'étendue : contacter l'admin").exec_()
            return
            pass


        ####### import de toutes les tables de la BdTopo #######

        list_tables = ['BATI_INDIFFERENCIE', 'BATI_INDUSTRIEL', 'BATI_REMARQUABLE', 'TERRAIN_SPORT',
                       'TRONCON_VOIE_FERREE',
                       'ROUTE_PRIMAIRE', 'ROUTE_SECONDAIRE', 'ZONE_VEGETATION',
                       'TRONCON_COURS_EAU', 'LIGNE_ELECTRIQUE', 'POINT_EAU']
        list_tables_v3 = ['BATIMENT', 'TERRAIN_DE_SPORT',
                          'TRONCON_DE_VOIE_FERREE',
                          'TRONCON_DE_ROUTE', 'ZONE_DE_VEGETATION',
                          'TRONCON_HYDROGRAPHIQUE', 'LIGNE_ELECTRIQUE', 'DETAIL_HYDROGRAPHIQUE']
        numero = 20

        ##### Si encodage en UTF-8 ####

        if not self.checkBox.isChecked() or self.checkBox.isChecked():
            dossier_temp = dossier_base + "/shp_temp"

            if os.path.exists(dossier_temp) :
                shutil.rmtree(dossier_temp)
            os.mkdir(dossier_temp)

            #### chargement des shp d origine dans la base ####
            for dossier, sous_dossiers, fichiers in os.walk(bd_topo):
                for fichier in fichiers:
                    nom_table, extension = os.path.splitext(fichier)
                    nom_table_lower = nom_table.lower()
                    if extension == ".shp" or extension == '.SHP':
                        if nom_table in list_tables or nom_table in list_tables_v3:
                            path = os.path.join(dossier, fichier)
                            table = nom_table_lower


                            output = 'spatialite://dbname=\'{0}\' table="{1}" (GEOMETRY)'.format(path_base,table)
                            context = dataobjects.createContext()
                            context.setInvalidGeometryCheck(QgsFeatureRequest.GeometryNoCheck)

                            try:


                                processus = processing.run('native:extractbyextent',
                                               { 'CLIP' : False,
                                                 'EXTENT' : etendue,
                                                 'INPUT' : path,
                                                 'OUTPUT' : output }, context=context)
                                print(processus)

                                numero = numero + 1
                                self.progressBar.setValue(numero)
                                details = ''
                                a3 = u'import de la table ' + table + u' de la BdTopo réussi !'
                                details += "\n" + a3
                                self.detailsPlainTextEdit_2.appendPlainText(details)
                            except BaseException as erreur:
                                boite.message(self, str(erreur), u" Erreur lors de l'import des tables de la BD TOPO® (ed >= 171) : contacter l'admin").exec_()
                                return
                                pass


        details = ''
        a3 = u'import des tables de la BdTopo réussi !'
        details += "\n" + a3
        self.detailsPlainTextEdit_2.appendPlainText(details)




        ####### zone tampon du site : zt #######

        try:
            req_sql = "drop table if exists zt_%s;" % (nom_base)
            cur.execute(req_sql)
            req_sql = "create table zt_%s as " \
                      "select pk, st_multi(st_difference(st_buffer(GEOMETRY, 50),GEOMETRY)) as GEOMETRY from %s" % (nom_base, nom_base)
            cur.execute(req_sql)
            req_sql = "SELECT RecoverGeometryColumn('zt_%s', 'GEOMETRY', 2154, 'MULTIPOLYGON', 'XYZ')" % (nom_base)
            cur.execute(req_sql)
            req_sql = "SELECT RecoverGeometryColumn('zt_%s', 'GEOMETRY', 2154, 'MULTIPOLYGON', 'XY')" % (nom_base)
            cur.execute(req_sql)
            req_sql = "SELECT CreateSpatialIndex('zt_%s','GEOMETRY');" % (nom_base)
            cur.execute(req_sql)

        except BaseException as erreur :
            boite.message(self, str(erreur),u"Erreur lors de la création de la zone tampon : contacter l'admin").exec_()
            return
            pass

        self.progressBar.setValue(40)
        a4 = u'création de la zone tampon réussie !'
        details = ''
        details += "\n" + a4
        self.detailsPlainTextEdit_2.appendPlainText(details)


        ####### zone paysage du site : zp #######

        try :
            req_sql = "drop table if exists zp_%s;" % (nom_base)
            cur.execute(req_sql)
            req_sql = "create table zp_%s as " \
                      "select pk, st_Multi(st_buffer(GEOMETRY, 1000)) as GEOMETRY from %s" % (nom_base, nom_base)
            cur.execute(req_sql)
            req_sql = "SELECT RecoverGeometryColumn('zp_%s', 'GEOMETRY', 2154, 'MULTIPOLYGON', 'XYZ')" % (nom_base)
            cur.execute(req_sql)
            req_sql = "SELECT RecoverGeometryColumn('zp_%s', 'GEOMETRY', 2154, 'MULTIPOLYGON', 'XY')" % (nom_base)
            cur.execute(req_sql)
            req_sql = "SELECT CreateSpatialIndex('zp_%s','GEOMETRY');" % (nom_base)
            cur.execute(req_sql)

        except BaseException as erreur:
            boite.message(self, str(erreur),
                          u"Erreur lors de la création de la zone paysage : contacter l'admin").exec_()
            return
            pass

        self.progressBar.setValue(50)
        a5 = u'création de la zone paysage réussie !'
        details = ''
        details += "\n" + a5
        self.detailsPlainTextEdit_2.appendPlainText(details)


        ####### Table emporte-pièces : zone_contributive et paysage #######

        #### Decoupage des tables ####
        if not self.checkBox_2.isChecked() or fichier_zc_mc == '':
            liste_patron = ['zp_' + nom_base]
        elif self.checkBox_2.isChecked() and fichier_zc_mc != '':
            liste_patron = ['zc_' + nom_base, 'zp_' + nom_base]

        numero = 50

        try :
            req_sql = req_sql = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
            cur.execute(req_sql)
            tables = cur.fetchall()

            for table in tables:
                t = table[0]
                t_upper = t.upper()
                if t_upper in list_tables or  t_upper in list_tables_v3:
                    for patron in liste_patron:

                        ### découpage des tables de la BdTopo
                        req_sql = "drop table if exists {0}_{1};".format(patron, t)
                        cur.execute(req_sql)

                        if t_upper == 'BATIMENT' :
                            req_sql = "create table {0}_{1} as select a.*, st_multi(st_buffer(st_intersection(b.GEOMETRY, a.GEOMETRY),0)) as GEOM  " \
                                      "from {2} as a, {3} as b " \
                                      "where st_intersects(b.GEOMETRY, a.GEOMETRY) and leger = 'Non';".format(patron, t, t, patron)
                            cur.execute(req_sql)

                        elif t_upper == 'DETAIL_HYDROGRAPHIQUE' :
                            req_sql = "create table {0}_{1} as select a.*, st_multi(st_makevalid(st_intersection(b.GEOMETRY, a.GEOMETRY))) as GEOM  " \
                                      "from {2} as a, {3} as b " \
                                      "where st_intersects(b.GEOMETRY, a.GEOMETRY) and (nature = 'Source captée' or nature = 'Fontaine' or nature LIKE '{4}') ;".format(patron, t, t, patron, '%Point%')
                            print (req_sql)
                            cur.execute(req_sql)

                        elif t_upper == 'TRONCON_DE_ROUTE' :
                            req_sql = "create table {0}_{1} as select a.*, st_multi(st_makevalid(st_intersection(b.GEOMETRY, a.GEOMETRY))) as GEOM " \
                                      "from {2} as a, {3} as b " \
                                      "where st_intersects(b.GEOMETRY, a.GEOMETRY) and (nature not in ('Sentier','Bac ou liaison maritime', 'Chemin', 'Escalier') ) ;".format(patron, t, t, patron)
                            print (req_sql)
                            cur.execute(req_sql)

                        else :
                            req_sql = "create table {0}_{1} as select a.*, st_multi(st_makevalid(st_intersection(b.GEOMETRY, a.GEOMETRY))) as GEOM  " \
                                      "from {2} as a, {3} as b " \
                                      "where st_intersects(b.GEOMETRY, a.GEOMETRY);".format(patron, t, t, patron)
                            cur.execute(req_sql)



                        if  t_upper == 'TRONCON_DE_ROUTE' or t_upper == 'TRONCON_HYDROGRAPHIQUE' \
                                or t_upper == 'TRONCON_DE_VOIE_FERREE'                        \
                                or t_upper == 'ROUTE_PRIMAIRE' or t_upper == 'ROUTE_SECONDAIRE' \
                                or t_upper == 'TRONCON_COURS_EAU' or t_upper == 'LIGNE_ELECTRIQUE' \
                                or t_upper == 'TRONCON_VOIE_FERREE':
                            req_sql = "SELECT RecoverGeometryColumn('{0}_{1}', 'GEOM', 2154, 'MULTILINESTRING', 'XY')".format(patron, t)
                            cur.execute(req_sql)
                            req_sql = "SELECT RecoverGeometryColumn('{0}_{1}', 'GEOM', 2154, 'MULTILINESTRING', 'XYZ')".format(patron, t)
                            cur.execute(req_sql)
                        elif t_upper == 'POINT_EAU' or t_upper == 'DETAIL_HYDROGRAPHIQUE':
                            req_sql = "SELECT RecoverGeometryColumn('{0}_{1}', 'GEOM', 2154, 'MULTIPOINT', 'XY')".format(patron, t)
                            cur.execute(req_sql)
                            req_sql = "SELECT RecoverGeometryColumn('{0}_{1}', 'GEOM', 2154, 'MULTIPOINT', 'XYZ')".format(patron, t)
                            cur.execute(req_sql)
                        else:
                            req_sql = "SELECT RecoverGeometryColumn('{0}_{1}', 'GEOM', 2154, 'MULTIPOLYGON', 'XY')".format(patron, t)
                            cur.execute(req_sql)
                            req_sql = "SELECT RecoverGeometryColumn('{0}_{1}', 'GEOM', 2154, 'MULTIPOLYGON', 'XYZ')".format(patron, t)
                            cur.execute(req_sql)

                        req_sql = "SELECT CreateSpatialIndex('{0}_{1}','GEOM');".format(patron, t)
                        cur.execute(req_sql)

                        numero = numero + 1
                        self.progressBar.setValue(numero)
                        details = ''
                        a6 = u'découpage de la table ' + t + u' de la BdTopo avec la ' + patron.split('_')[0] + u' réussi !'
                        details += "\n" + a6
                        self.detailsPlainTextEdit_2.appendPlainText(details)

        except BaseException as erreur:
            boite.message(self, str(erreur),"Erreur ligne {}".format(sys.exc_info()[-1].tb_lineno),
                          u"Erreur lors du découpage des tables de la Bd Topo : contacter l'admin").exec_()
            return
            pass


        a7 = u'découpage des tables de la BdTopo réussi !'
        details = ''
        details += "\n" + a7
        self.detailsPlainTextEdit_2.appendPlainText(details)


        ####### Import du RPG #######

        str_rpg = "rpg"

        output = 'spatialite://dbname=\'{0}\' table="{1}" (GEOMETRY)'.format(path_base,str_rpg)

        try:

            processus = processing.run('native:extractbyextent',
                                       {'CLIP': False,
                                        'EXTENT': etendue,
                                        'INPUT': rpg,
                                        'OUTPUT': output},context=context)
            print(processus)


        except BaseException as erreur:
            boite.message(self, str(erreur),
                          u"Erreur lors de l import du RPG : contacter l'admin").exec_()
            return
            pass


        self.progressBar.setValue(90)
        a8 = u'import du RPG réussi !'
        details = ''
        details += "\n" + a8
        self.detailsPlainTextEdit_2.appendPlainText(details)

        ####### Decoupage du RPG suivant la zone contributive #######

        if self.checkBox_2.isChecked() and fichier_zc_mc != '':
            try :
                req_sql = "drop table if exists zc_%s_%s;" % (nom_base, str_rpg)
                cur.execute(req_sql)
                req_sql = "create table zc_%s_%s as" \
                          " select a.*, st_multi(st_buffer(st_intersection(b.GEOMETRY, a.GEOMETRY),0)) as GEOM " \
                          "from %s as a, zc_%s as b " \
                          "where st_intersects(b.GEOMETRY, st_makevalid(a.GEOMETRY));" % (nom_base, str_rpg, str_rpg, nom_base)
                cur.execute(req_sql)
                req_sql = "SELECT RecoverGeometryColumn('zc_%s_%s', 'GEOM', 2154, 'MULTIPOLYGON', 'XY')" % (nom_base, str_rpg)
                cur.execute(req_sql)
                req_sql = "SELECT RecoverGeometryColumn('zc_%s_%s', 'GEOM', 2154, 'MULTIPOLYGON', 'XYZ')" % (nom_base, str_rpg)
                cur.execute(req_sql)
                req_sql = "SELECT CreateSpatialIndex('zc_%s_%s','GEOM');" % (nom_base, str_rpg)
                cur.execute(req_sql)

                self.progressBar.setValue(95)
                a9 = u'decoupage du RPG suivant la zone contributive réussi !'
                details = ''
                details += "\n" + a9
                self.detailsPlainTextEdit_2.appendPlainText(details)

            except BaseException as erreur:
                boite.message(self, str(erreur),
                              u"Erreur lors du découpage du RPG avec la zone contributive : contacter l'admin").exec_()
                return
                pass



        ####### Decoupage du rpg suivant la zone paysage #######
        try :
            req_sql = "drop table if exists zp_%s_%s;" % (nom_base, str_rpg)
            cur.execute(req_sql)
            req_sql = "create table zp_%s_%s as" \
                      " select a.*, st_multi(st_buffer(st_intersection(b.GEOMETRY, a.GEOMETRY),0)) as GEOM " \
                      "from %s as a, zp_%s as b " \
                      "where st_intersects(b.GEOMETRY, st_makevalid(a.GEOMETRY));" % (nom_base, str_rpg, str_rpg, nom_base)
            cur.execute(req_sql)
            req_sql = "SELECT RecoverGeometryColumn('zp_%s_%s', 'GEOM', 2154, 'MULTIPOLYGON', 'XY')" % (nom_base, str_rpg)
            cur.execute(req_sql)
            req_sql = "SELECT RecoverGeometryColumn('zp_%s_%s', 'GEOM', 2154, 'MULTIPOLYGON', 'XYZ')" % (nom_base, str_rpg)
            cur.execute(req_sql)
            req_sql = "SELECT CreateSpatialIndex('zp_%s_%s','GEOM');" % (nom_base, str_rpg)
            cur.execute(req_sql)

        except BaseException as erreur:
            boite.message(self, str(erreur),
                          u"Erreur lors du découpage du RPG avec la zone paysage : contacter l'admin").exec_()
            return
            pass


        if self.checkBox_2.isChecked():
            self.progressBar.setValue(97)
        elif not self.checkBox_2.isChecked():
            self.progressBar.setValue(100)
        a10 = u'decoupage du RPG suivant la zone paysage réussi !'
        details = ''
        details += "\n" + a10
        self.detailsPlainTextEdit_2.appendPlainText(details)


        ####### Creation de la table Bati total #######
                ### pour surface construite : superficie totale des zones bati dans la zone contributive (en ha 3 chiffres après virgule)
                #### Fusionner les 4 tables ensembles : bati_total
                # bati_indifferencie
                # bati_industriel
                # bati_remarquable
                # terrain_sport


        if self.checkBox_2.isChecked() and fichier_zc_mc != '':
            if self.checkBox_3.isChecked():
                try :
                    req_sql = "drop table if exists zc_%s_bati_total;" % (nom_base)
                    cur.execute(req_sql)
                    req_sql = "create table zc_%s_bati_total as " \
                              "select pk,id,GEOM from zc_%s_batiment union " \
                              "select pk,id,GEOM from zc_%s_terrain_de_sport; " % (nom_base, nom_base,nom_base)
                    print (req_sql)
                    cur.execute(req_sql)
                    req_sql = "SELECT RecoverGeometryColumn('zc_%s_bati_total', 'GEOM', 2154, 'MULTIPOLYGON', 'XY')" % (nom_base)
                    cur.execute(req_sql)
                    req_sql = "SELECT RecoverGeometryColumn('zc_%s_bati_total', 'GEOM', 2154, 'MULTIPOLYGON', 'XYZ')" % (nom_base)
                    cur.execute(req_sql)

                except BaseException as erreur:
                    boite.message(self, str(erreur),
                                  u"Erreur lors de la création de la table bati total : contacter l'admin").exec_()
                    return
                    pass


                self.progressBar.setValue(100)
                a11 = u'création de la table zone_bati_total réussie !'
                details = ''
                details += "\n" + a11
                self.detailsPlainTextEdit_2.appendPlainText(details)

            else :
                try:
                    req_sql = "drop table if exists zc_%s_bati_total;" % (nom_base)
                    cur.execute(req_sql)
                    req_sql = "create table zc_%s_bati_total as " \
                              "select pk,id,GEOM from zc_%s_bati_indifferencie union " \
                              "select pk,id,GEOM from zc_%s_bati_industriel union " \
                              "select pk,id,GEOM from zc_%s_bati_remarquable union " \
                              "select pk,id,GEOM from zc_%s_terrain_sport; " % (
                              nom_base, nom_base, nom_base, nom_base, nom_base)
                    cur.execute(req_sql)
                    req_sql = "SELECT RecoverGeometryColumn('zc_%s_bati_total', 'GEOM', 2154, 'MULTIPOLYGON', 'XY')" % (
                        nom_base)
                    cur.execute(req_sql)
                    req_sql = "SELECT RecoverGeometryColumn('zc_%s_bati_total', 'GEOM', 2154, 'MULTIPOLYGON', 'XYZ')" % (
                        nom_base)
                    cur.execute(req_sql)

                except BaseException as erreur:
                    boite.message(self, str(erreur),
                                  u"Erreur lors de la création de la table bati total : contacter l'admin").exec_()
                    return
                    pass

                self.progressBar.setValue(100)
                a11 = u'création de la table zone_bati_total réussie !'
                details = ''
                details += "\n" + a11
                self.detailsPlainTextEdit_2.appendPlainText(details)


        ####### Filtre sur les couches BATIMENT, DETAIL_HYDROGRAPHIQUE et TRONCON_DE_ROUTE de la BD TOPO V3 #######
        try:
                req_sql = "delete from batiment where leger = 'Oui';"
                cur.execute(req_sql)
                req_sql = "delete from detail_hydrographique where nature != 'Source captée' and nature != 'Fontaine' and nature NOT LIKE '%s';" % ('%Point')
                cur.execute(req_sql)
                req_sql = "delete from troncon_de_route where nature in ('Sentier','Bac ou liaison maritime', 'Chemin', 'Escalier');"
                cur.execute(req_sql)
                conn.commit()
                conn.close()
        except :
            pass

        conn.close()
        shutil.rmtree(dossier_temp)

        ################### FIN DU PROCESSUS ###################
        msg4 = QMessageBox()
        msg4.setIcon(QMessageBox.Information)
        msg4.setText(u"L'export des données dans la base .sqlite est fini !!")
        msg4.setInformativeText(u"Vous pouvez utiliser la base !!")
        msg4.setWindowTitle(u"Avertissement")
        msg4.exec_()