# -*- coding: utf-8 -*- """ /*************************************************************************** SICEN A QGIS plugin Filtre sur données SICEN ------------------- begin : 2014-10-17 copyright : (C) 2014 by Guillaume COSTES (CENRA) email : guillaume.costes@espaces-naturels.fr ***************************************************************************/ /*************************************************************************** * * * This program is free software; you can redistribute it and/or modify * * it under the terms of the GNU General Public License as published by * * the Free Software Foundation; either version 2 of the License, or * * (at your option) any later version. * * * ***************************************************************************/ """ """from __future__ import absolute_import # Import the PyQt and QGIS libraries from builtins import str from builtins import object from PyQt4.QtCore import * from PyQt4.QtGui import * from qgis.core import * from qgis.gui import * from qgis.utils import iface""" from builtins import str from builtins import object import os from PyQt5.QtCore import * from PyQt5.QtGui import * from qgis.PyQt import uic from qgis.core import * from qgis.core import QgsDataSourceUri, QgsWkbTypes, QgsLayerTreeGroup, QgsLayerTreeLayer from qgis.PyQt.QtGui import QFont from qgis.PyQt.QtWidgets import QMenu, QAction, QDialog, QDialogButtonBox, QLabel, QApplication, QTabWidget, QWidget, QGroupBox, QGridLayout, QComboBox, QCheckBox, QCalendarWidget, QToolBox, QFrame, QLineEdit, QSizePolicy, QFileDialog #Modifié # Initialize Qt resources from file resources.py from . import resources_rc # Import the code for the dialog from .sicendialog import SICENDialog from .tools.PythonSQL import * from .tools.resources import maj_verif import os.path import qgis import datetime import csv from .forms.about_form import SicenAboutDialog import psycopg2 import psycopg2.extras import base64 #import socket import os #import sys class SICEN(object): def __init__(self, iface): # Save reference to the QGIS interface self.iface = iface # initialize plugin directory self.plugin_dir = os.path.dirname(__file__) # initialize locale locale = QSettings().value("locale/userLocale")[0:2] localePath = os.path.join(self.plugin_dir, 'i18n', 'sicen_{}.qm'.format(locale)) if os.path.exists(localePath): self.translator = QTranslator() self.translator.load(localePath) if qVersion() > '4.3.3': QCoreApplication.installTranslator(self.translator) # Create the dialog (after translation) and keep reference self.dlg = SICENDialog() maj_verif('CenRa_SICEN') version = qgis.utils.pluginMetadata('CenRa_SICEN','version') # Display About window on first use s = QSettings() versionUse = s.value("sicen/version", 1, type=str) if str(versionUse) != str(version) : s.setValue("sicen/version", str(version)) self.open_about_dialog() def initGui(self): self.toolBar = self.iface.addToolBar("SICEN") self.toolBar.setObjectName("SICEN") ## Create action that will start plugin configuration self.action = QAction( QIcon(":/plugins/sicen/sicen.png"), u"Ouverture des données SICEN", self.iface.mainWindow()) # connect the action to the run method self.action.triggered.connect(self.ouverture) # Add toolbar button and menu item self.toolBar.addAction(self.action) self.iface.addPluginToMenu(u"SICEN", self.action) ## Create action that will start plugin configuration self.action = QAction( QIcon(":/plugins/sicen/sicen_export.png"), u"Export liste d'espèces", self.iface.mainWindow()) # connect the action to the run method self.action.triggered.connect(self.export) # Add toolbar button and menu item self.toolBar.addAction(self.action) self.iface.addPluginToMenu(u"SICEN", self.action) self.menu = QMenu() self.menu.setTitle( QCoreApplication.translate( "SICEN","&SICEN" ) ) self.sicen_ouverture = QAction( QIcon(":/plugins/SICEN/sicen.png"), QCoreApplication.translate("SICEN", u"Ouverture des données SICEN" ), self.iface.mainWindow() ) self.sicen_export = QAction( QIcon(":/plugins/SICEN/sicen_export.png"), QCoreApplication.translate("SICEN", u"Export liste d'espèces" ), self.iface.mainWindow() ) self.menu.addActions( [self.sicen_ouverture, self.sicen_export] ) menu_bar = self.iface.mainWindow().menuBar() actions = menu_bar.actions() lastAction = actions[ len( actions ) - 1 ] menu_bar.insertMenu( lastAction, self.menu ) self.sicen_ouverture.triggered.connect(self.ouverture) self.sicen_export.triggered.connect(self.export) def open_about_dialog(self): dialog = SicenAboutDialog(self.iface) dialog.exec_() def unload(self): # Remove the plugin menu item and icon self.iface.removePluginMenu(u"&SICEN", self.action) self.iface.removeToolBarIcon(self.action) # run method that performs all the real work def ouverture(self): first_conn = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=first_cnx password=" + password) first_cur = first_conn.cursor(cursor_factory = psycopg2.extras.DictCursor) first_cur.execute("SELECT mdp_w, login_w FROM pg_catalog.pg_user t1, admin_sig.vm_users_sig t2 WHERE t2.oid = t1.usesysid AND (login_w = '" + os_user + "' OR login_w = '" + os_user + "')") res_ident = first_cur.fetchone() mdp = base64.b64decode(str(res_ident[0])).decode('utf-8') user = res_ident[1] con = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=" + user + " password=" + mdp) cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor) first_conn.close() ## Creation des listes deroulantes # Listing de valeur des champs SQL_observateur = u"""SELECT DISTINCT nom || '_' || prenom as nom FROM md.personne ORDER BY nom""" SQL_nom_com = u"""SELECT DISTINCT nom FROM saisie.commune ORDER BY nom""" SQL_nom_vern = u"""SELECT DISTINCT nom_vern FROM _agregation_ra.observations_table ORDER BY nom_vern""" SQL_nom_complet = u"""SELECT DISTINCT nom_complet FROM _agregation_ra.observations_table ORDER BY nom_complet""" SQL_ordre = u"""SELECT DISTINCT ordre FROM _agregation_ra.observations_table ORDER BY ordre""" # Generation des listes cur.execute(SQL_observateur) list_observateur = cur.fetchall() cur.execute(SQL_nom_com) list_nom_com = cur.fetchall() cur.execute(SQL_nom_vern) list_nom_vern = cur.fetchall() cur.execute(SQL_nom_complet) list_nom_complet = cur.fetchall() cur.execute(SQL_ordre) list_ordre = cur.fetchall() con.close() # Ajout des items dans les combobox self.dlg.observateur_1.clear() i = 0 while i < len(list_observateur): self.dlg.observateur_1.addItems(list_observateur[i]) i=i+1 self.dlg.observateur_1.setCurrentIndex(-1) # Pour ne pas commencer la liste au premier item self.dlg.observateur_2.clear() i = 0 while i < len(list_observateur): self.dlg.observateur_2.addItems(list_observateur[i]) i=i+1 self.dlg.observateur_2.setCurrentIndex(-1) self.dlg.observateur_3.clear() i = 0 while i < len(list_observateur): self.dlg.observateur_3.addItems(list_observateur[i]) i=i+1 self.dlg.observateur_3.setCurrentIndex(-1) self.dlg.observateur_4.clear() i = 0 while i < len(list_observateur): self.dlg.observateur_4.addItems(list_observateur[i]) i=i+1 self.dlg.observateur_4.setCurrentIndex(-1) self.dlg.nom_com_1.clear() i = 0 while i < len(list_nom_com): self.dlg.nom_com_1.addItems(list_nom_com[i]) i=i+1 self.dlg.nom_com_1.setCurrentIndex(-1) self.dlg.nom_com_2.clear() i = 0 while i < len(list_nom_com): self.dlg.nom_com_2.addItems(list_nom_com[i]) i=i+1 self.dlg.nom_com_2.setCurrentIndex(-1) self.dlg.nom_vern.clear() i = 0 while i < len(list_nom_vern): self.dlg.nom_vern.addItems(list_nom_vern[i]) i=i+1 self.dlg.nom_vern.setCurrentIndex(-1) self.dlg.nom_complet.clear() i = 0 while i < len(list_nom_complet): self.dlg.nom_complet.addItems(list_nom_complet[i]) i=i+1 self.dlg.nom_complet.setCurrentIndex(-1) self.dlg.ordre.clear() i = 0 while i < len(list_ordre): self.dlg.ordre.addItems(list_ordre[i]) i=i+1 self.dlg.ordre.setCurrentIndex(-1) # show the dialog self.dlg.show() # Run the dialog event loop result = self.dlg.exec_() # See if OK was pressed if result == 1: first_conn = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=first_cnx password=" + password) first_cur = first_conn.cursor(cursor_factory = psycopg2.extras.DictCursor) first_cur.execute("SELECT mdp_w, login_w FROM pg_catalog.pg_user t1, admin_sig.vm_users_sig t2 WHERE t2.oid = t1.usesysid AND (login_w = '" + os_user + "' OR login_w = '" + os_user + "')") res_ident = first_cur.fetchone() mdp = base64.b64decode(str(res_ident[0])).decode('utf-8') user = res_ident[1] con = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=" + user + " password=" + mdp) cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor) first_conn.close() # Requete filtre observateur if self.dlg.observateur_1.currentIndex() != -1 : sql_obs1 = """ "observateur" LIKE '%""" + self.dlg.observateur_1.currentText() + "%' " else : sql_obs1 = '' if self.dlg.observateur_2.currentIndex() != -1 : sql_obs2 = """ "observateur" LIKE '%""" + self.dlg.observateur_2.currentText() + "%' " else : sql_obs2 = '' if self.dlg.observateur_3.currentIndex() != -1 : sql_obs3 = """ "observateur" LIKE '%""" + self.dlg.observateur_3.currentText() + "%' " else : sql_obs3 = '' if self.dlg.observateur_4.currentIndex() != -1 : sql_obs4 = """ "observateur" LIKE '%""" + self.dlg.observateur_4.currentText() + "%' " else : sql_obs4 = '' reqwhere_obs = sql_obs1 + 'OR' + sql_obs2 + 'OR' + sql_obs3 + 'OR' + sql_obs4 while reqwhere_obs[-2:] == 'OR' : reqwhere_obs = reqwhere_obs[:-2] while reqwhere_obs[:2] == 'OR' : reqwhere_obs = reqwhere_obs[2:] # Requete communes # Connexion a la BD #con = psycopg2.connect("dbname="+ dbname + " user=" + user + " host=" + host + " password=" + password + " port=" + port) #cur = con.cursor() if self.dlg.nom_com_1.currentIndex() != -1 : nom_com = self.dlg.nom_com_1.currentText() SQL_code_INSEE = u"""SELECT DISTINCT code_insee FROM saisie.commune WHERE nom = '""" + nom_com + "'" cur.execute(SQL_code_INSEE) list_code_INSEE = cur.fetchall() code_INSEE = list_code_INSEE[0][0] sql_insee_1 = """ "code_insee" = '""" + code_INSEE + "' " else : sql_insee_1 = '' if self.dlg.nom_com_2.currentIndex() != -1 : nom_com = self.dlg.nom_com_2.currentText() SQL_code_INSEE = u"""SELECT DISTINCT code_insee FROM saisie.commune WHERE nom = '""" + nom_com + "'" cur.execute(SQL_code_INSEE) list_code_INSEE = cur.fetchall() code_INSEE = list_code_INSEE[0][0] sql_insee_2 = """ "code_insee" = '""" + code_INSEE + "' " else : sql_insee_2 = '' reqwhere_insee = sql_insee_1 + 'OR' + sql_insee_2 while reqwhere_insee[-2:] == 'OR' : reqwhere_insee = reqwhere_insee[:-2] while reqwhere_insee[:2] == 'OR' : reqwhere_insee = reqwhere_insee[2:] con.close() # Requete date today = datetime.datetime.now().strftime("%Y-%m-%d") date_min = self.dlg.date_min.selectedDate().toString("yyyy-MM-dd") date_max = self.dlg.date_max.selectedDate().toString("yyyy-MM-dd") if date_min != today : sql_date_min = """ "date_obs" >= '""" + date_min + "' " else : sql_date_min = '' if date_max != today : sql_date_max = """ "date_obs" <= '""" + date_max + "' " else : sql_date_max = '' reqwhere_date = sql_date_min + 'AND' + sql_date_max while reqwhere_obs[-3:] == 'AND' : reqwhere_obs = reqwhere_obs[:-3] while reqwhere_obs[:3] == 'AND' : reqwhere_obs = reqwhere_obs[3:] # Requete regne if self.dlg.Animalia.isChecked() == 1 : sql_Animalia = """"regne" = 'Animalia'""" else : sql_Animalia = '' if self.dlg.Plantae.isChecked() == 1 : sql_Plantae = """"regne" = 'Plantae'""" else : sql_Plantae = '' reqwhere_regne = sql_Animalia + 'OR' + sql_Plantae while reqwhere_regne[-2:] == 'OR' : reqwhere_regne = reqwhere_regne[:-2] while reqwhere_regne[:2] == 'OR' : reqwhere_regne = reqwhere_regne[2:] # Requete ordre if self.dlg.ordre.currentIndex() != -1 : reqwhere_ordre = """"ordre" = '""" + self.dlg.ordre.currentText() + "'" else : reqwhere_ordre = '' # Requete sp if self.dlg.nom_vern.currentIndex() != -1 : sql_nom_vern = """"nom_vern" = '""" + self.dlg.nom_vern.currentText() + "'" else : sql_nom_vern = '' if self.dlg.nom_complet.currentIndex() != -1 : sql_nom_complet = """"nom_complet" = '""" + self.dlg.nom_complet.currentText() + "'" else : sql_nom_complet = '' reqwhere_sp = sql_nom_vern + 'OR' + sql_nom_complet while reqwhere_sp[-2:] == 'OR' : reqwhere_sp = reqwhere_sp[:-2] while reqwhere_sp[:2] == 'OR' : reqwhere_sp = reqwhere_sp[2:] # Requete filtre patrimonialite if self.dlg.LRD_01.isChecked() == 1 : sql_lrd01 = """ lrd01 IN ('VU','EN','RE','CR') """ else : sql_lrd01 = '' if self.dlg.LRD_07.isChecked() == 1 : sql_lrd07 = """ lrd07 IN ('VU','EN','RE','CR') """ else : sql_lrd07 = '' if self.dlg.LRD_26.isChecked() == 1 : sql_lrd26 = """ lrd26 IN ('VU','EN','RE','CR') """ else : sql_lrd26 = '' if self.dlg.LRD_38.isChecked() == 1 : sql_lrd38 = """ lrd38 IN ('VU','EN','RE','CR', 'AS-1', 'AS-2', 'AS-3') """ else : sql_lrd38 = '' if self.dlg.LRD_42.isChecked() == 1 : sql_lrd42 = """ lrd42 IN ('VU','EN','RE','CR') """ else : sql_lrd42 = '' if self.dlg.LRD_69.isChecked() == 1 : sql_lrd69 = """ lrd69 IN ('VU','EN','RE','CR') """ else : sql_lrd69 = '' if self.dlg.LRD_73.isChecked() == 1 : sql_lrd73 = """ lrd73 IN ('VU','EN','RE','CR') """ else : sql_lrd73 = '' if self.dlg.LRD_74.isChecked() == 1 : sql_lrd74 = """ lrd74 IN ('VU','EN','RE','CR') """ else : sql_lrd74 = '' if self.dlg.LRR_Flore.isChecked() == 1 : sql_LRR_Flore = u""" "TAXO_GROUPE" IN ('Plantes') AND (lrr like '%CR%' OR lrr like '%EN%' OR lrr like '%EW%' OR lrr like '%RE%' OR lrr like '%VU%')""" else : sql_LRR_Flore = '' if self.dlg.LRR_Oiseaux.isChecked() == 1 : sql_LRR_Oiseaux = u""" "TAXO_GROUPE" = 'Oiseaux' AND (lrr like '%RE %' OR lrr like '%CR %' OR lrr like '%EN %' OR lrr like '%VU %') """ else : sql_LRR_Oiseaux = '' if self.dlg.LRR_Autre.isChecked() == 1 : sql_LRR_Autre = u""" (lrr like '%RE %' OR lrr like '%CR %' OR lrr like '%EN %' OR lrr like '%VU %') AND "TAXO_GROUPE" NOT IN ('Oiseaux', 'Plantes') """ else : sql_LRR_Autre = '' if self.dlg.LRN_Flore.isChecked() == 1 : sql_LRN_Flore = u""" "TAXO_GROUPE" ilike 'plantes' AND (lrr like '%CR%' OR lrr like '%EN%' OR lrr like '%EW%' OR lrr like '%RE%' OR lrr like '%VU%') """ else : sql_LRN_Flore = '' if self.dlg.LRN_Oiseaux.isChecked() == 1 : sql_LRN_Oiseaux = u""" "TAXO_GROUPE" = 'Oiseaux' AND (lrn like '%RE %' OR lrn like '%CR %' OR lrn like '%EN %' OR lrn like '%VU %') """ else : sql_LRN_Oiseaux = '' if self.dlg.LRN_Ortho.isChecked() == 1 : sql_LRN_Ortho = u""" "TAXO_GROUPE" ilike 'orthoptères' AND (lrn like '1%' OR lrn like '2%' OR lrn like '3%') """ else : sql_LRN_Ortho = '' if self.dlg.LRN_Autre.isChecked() == 1 : sql_LRN_Autre = u""" (lrr like '%CR%' OR lrr like '%EN%' OR lrr like '%EW%' OR lrr like '%RE%' OR lrr like '%VU%') AND "TAXO_GROUPE" NOT IN ('Oiseaux', 'Plantes', 'Orthoptères') """ else : sql_LRN_Autre = '' if self.dlg.LRE.isChecked() == 1 : sql_lre = """ lre IN ('VU','EN','RE','CR', 'EW', 'EX') """ else : sql_lre = '' if self.dlg.UICN.isChecked() == 1 : sql_UICN = """ lrm IN ('VU','EN','RE','CR', 'EW', 'EX') """ else : sql_UICN = '' if self.dlg.PATRIMONIALITE.isChecked() == 1 : sql_patrimonialite = """"patrimonialite" IS NOT NULL """ else : sql_patrimonialite = '' if self.dlg.PATRI01.isChecked() == 1 : sql_patri01 = """"patri01" IS NOT NULL """ else : sql_patri01 = '' if self.dlg.PATRI07.isChecked() == 1 : sql_patri07 = """"patri07" IS NOT NULL """ else : sql_patri07 = '' if self.dlg.PATRI26.isChecked() == 1 : sql_patri26 = """"patri26" IS NOT NULL """ else : sql_patri26 = '' if self.dlg.PATRI38.isChecked() == 1 : sql_patri38 = """"patri38" IS NOT NULL """ else : sql_patri38 = '' if self.dlg.PATRI42.isChecked() == 1 : sql_patri42 = """"patri42" IS NOT NULL """ else : sql_patri42 = '' if self.dlg.PATRI69.isChecked() == 1 : sql_patri69 = """"patri69" IS NOT NULL """ else : sql_patri69 = '' if self.dlg.PATRI73.isChecked() == 1 : sql_patri73 = """"patri73" IS NOT NULL """ else : sql_patri73 = '' if self.dlg.PATRI74.isChecked() == 1 : sql_patri74 = """"patri74" IS NOT NULL """ else : sql_patri74 = '' if self.dlg.ZNIEFF.isChecked() == 1 : sql_znieff = """"znieff" IS NOT NULL """ else : sql_znieff = '' if self.dlg.DH_AnnII.isChecked() == 1 : sql_dh_an2 = """"dh_an2" IS NOT NULL """ else : sql_dh_an2 = '' if self.dlg.PD_38.isChecked() == 1 : sql_pd38 = """"pd38" IS NOT NULL """ else : sql_pd38 = '' if self.dlg.PD_42.isChecked() == 1 : sql_pd42 = """"pd42" IS NOT NULL """ else : sql_pd42 = '' if self.dlg.PN.isChecked() == 1 : sql_pn = """"pn" IS NOT NULL """ else : sql_pn = '' if self.dlg.PR.isChecked() == 1 : sql_pr = """"pr" IS NOT NULL """ else : sql_pr = '' reqwhere_pat = sql_lrd01 + 'OR' + sql_lrd07 + 'OR' + sql_lrd26 + 'OR' + sql_lrd38 + 'OR' + sql_lrd42 + 'OR' + sql_lrd69 + 'OR' + sql_lrd73 + 'OR' + sql_lrd74 + 'OR' + sql_lre + 'OR' + sql_LRN_Flore + 'OR' + sql_LRN_Oiseaux + 'OR' + sql_LRN_Ortho + 'OR' + sql_LRN_Autre + 'OR' + sql_LRR_Flore + 'OR' + sql_LRR_Oiseaux + 'OR' + sql_LRR_Autre + 'OR' + sql_UICN + 'OR' + sql_pd38 + 'OR' + sql_pd42 + 'OR' + sql_pn + 'OR' + sql_pr + 'OR' + sql_dh_an2 + 'OR' + sql_patri01 + 'OR' + sql_patri07 + 'OR' + sql_patri26 + 'OR' + sql_patri38 + 'OR' + sql_patri42 + 'OR' + sql_patri69 + 'OR' + sql_patri73 + 'OR' + sql_patri74 + 'OR' + sql_patrimonialite while reqwhere_pat.find('OROR') != -1 : reqwhere_pat = reqwhere_pat.replace('OROR','OR') while reqwhere_pat[-2:] == 'OR' : reqwhere_pat = reqwhere_pat[:-2] while reqwhere_pat[:2] == 'OR' : reqwhere_pat = reqwhere_pat[2:] # Requete geom if self.dlg.bouton_geom.isChecked() == 1 : layer = self.iface.activeLayer() if layer == None : #self.iface.messageBar().pushMessage(u"Vous devez sélectionner une table !", level=QgsMessageBar.WARNING, duration=5) self.iface.messageBar().pushMessage("Ooops", u"Vous devez sélectionner une table !", level=Qgis.Warning, duration=5) else : selection = layer.selectedFeatures() if layer.selectedFeatureCount() >= 1 : GEOM = '' for feature in selection: geom = feature.geometry() poly = "ST_GeomFromText('"+geom.asWkt()+"',2154)," GEOM = GEOM + poly poly = GEOM[0:-1] #print(poly) buffer = self.dlg.buffer.text() reqwhere_geom = " ST_intersects(geometrie,ST_Buffer(ST_Collect(array[" + poly + "]), " + buffer + " ))" # Requete finale reqwhere_final = '(' + reqwhere_obs + ')' + 'AND' + reqwhere_insee + 'AND' + reqwhere_ordre + 'AND' + reqwhere_sp + 'AND' + '(' + reqwhere_pat + ')' + 'AND' + reqwhere_regne + 'AND' + reqwhere_date + 'AND' + reqwhere_geom # Nettoyage de la requete finale while reqwhere_final.find('ANDAND') != -1 : reqwhere_final = reqwhere_final.replace('ANDAND','AND') while reqwhere_final.find('AND()AND') != -1 : reqwhere_final = reqwhere_final.replace('AND()AND','AND') while reqwhere_final[-3:] == 'AND' : reqwhere_final = reqwhere_final[:-3] while reqwhere_final[:5] == '()AND' : reqwhere_final = reqwhere_final[5:] while reqwhere_final[-5:] == 'AND()' : reqwhere_final = reqwhere_final[:-5] while reqwhere_final[:3] == 'AND' : reqwhere_final = reqwhere_final[3:] ## Affichage des tables table_name = 'observations' uri = QgsDataSourceUri() uri.setConnection(host ,port ,dbname ,user ,mdp) if self.dlg.centroide.isChecked() == 1 : ### Centroide if reqwhere_final.find('()') != -1 : reqwhere_final = '' print(reqwhere_final) uri.setDataSource("_agregation_ra", "observations_centroide", "geometrie", reqwhere_final, "gid") layer = self.iface.addVectorLayer(uri.uri(), table_name + '_centroides', "postgres") else : first_conn = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=first_cnx password=" + password) first_cur = first_conn.cursor(cursor_factory = psycopg2.extras.DictCursor) first_cur.execute("SELECT mdp_w, login_w FROM pg_catalog.pg_user t1, admin_sig.vm_users_sig t2 WHERE t2.oid = t1.usesysid AND (login_w = '" + os_user + "' OR login_w = '" + os_user + "')") res_ident = first_cur.fetchone() mdp = base64.b64decode(str(res_ident[0])).decode('utf-8') user = res_ident[1] con = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=" + user + " password=" + mdp) cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor) first_conn.close() # Creation group de couches #i = qgis.utils.iface #legende = i.legendInterface() legende = QgsProject.instance().layerTreeRoot() #nomgroupe = 'Observations' #groupe = legende.addGroup(nomgroupe,False) groupe = legende.insertGroup(1, "Observations") groupe = legende.findGroup("Observations") # Récupération de la liste de groupes #toc = iface.layerTreeView() #toc = qgis.utils.iface.legendInterface() #groups = toc. #groups = toc.groups() #index = groups.index(nomgroupe) index = 1 ### Polygone reqwhere_final_poly = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POLYGON%'" + ')' if reqwhere_final_poly.find('()') != -1 : reqwhere_final_poly = "(GeometryType(geometrie) LIKE '%POLYGON%')" SQL_count_poly = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_poly cur.execute(SQL_count_poly) list_count_poly = cur.fetchall() count_poly = list_count_poly[0][0] if count_poly != 0: uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_poly, "gid") #layer_poly = self.iface.addVectorLayer(uri.uri(), table_name + '_poly', "postgres") layer_poly = QgsVectorLayer(uri.uri(), table_name + '_poly', "postgres") QgsProject.instance().addMapLayer(layer_poly, False) #legende.moveLayer(layer_poly,index) #groupe.addLayer(layer_poly) groupe.insertChildNode(0, QgsLayerTreeLayer(layer_poly)) ### Point reqwhere_final_point = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POINT%'" + ')' if reqwhere_final_point.find('()') != -1 : reqwhere_final_point = "(GeometryType(geometrie) LIKE '%POINT%')" SQL_count_point = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_point cur.execute(SQL_count_point) list_count_point = cur.fetchall() count_point = list_count_point[0][0] if count_point != 0: uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_point, "gid") #layer_point = self.iface.addVectorLayer(uri.uri(), table_name + '_point', "postgres") layer_point = QgsVectorLayer(uri.uri(), table_name + '_point', "postgres") QgsProject.instance().addMapLayer(layer_point, False) #legende.moveLayer(layer_point,index) #groupe.addLayer(layer_point) groupe.insertChildNode(0, QgsLayerTreeLayer(layer_point)) ### Ligne reqwhere_final_ligne = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%LINE%'" + ')' if reqwhere_final_ligne.find('()') != -1 : reqwhere_final_ligne = "(GeometryType(geometrie) LIKE '%LINE%')" SQL_count_ligne = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_ligne cur.execute(SQL_count_ligne) list_count_ligne = cur.fetchall() count_ligne = list_count_ligne[0][0] if count_ligne != 0: uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_ligne, "gid") #layer_ligne = self.iface.addVectorLayer(uri.uri(), table_name + '_ligne', "postgres") layer_ligne = QgsVectorLayer(uri.uri(), table_name + '_ligne', "postgres") QgsProject.instance().addMapLayer(layer_ligne, False) #legende.moveLayer(layer_ligne,index) #groupe.addLayer(layer_ligne) groupe.insertChildNode(0, QgsLayerTreeLayer(layer_ligne)) # ajout des couches dans le groupe Observations #legende.setGroupExpanded (index, True) #self.iface.messageBar().pushMessage(u"Extraction réussie : ", u" Pensez à renommer vos couches pour plus de lisibilité.", level=QgsMessageBar.INFO, duration=10) self.iface.messageBar().pushMessage("Info", u"Extraction réussie : ", u" Pensez à renommer vos couches pour plus de lisibilité.", level=Qgis.Success, duration=4) elif layer.selectedFeatureCount() == 0 : #self.iface.messageBar().pushMessage(u"Vous devez sélectionner au moins un polygone !", level=QgsMessageBar.WARNING, duration=5) self.iface.messageBar().pushMessage("Ooops", u"Vous devez sélectionner au moins un polygone !", level=Qgis.Warning, duration=5) reqwhere_geom = '' else : #self.iface.messageBar().pushMessage(u"Vous devez sélectionner qu'un seul polygone !", level=QgsMessageBar.WARNING, duration=5) self.iface.messageBar().pushMessage("Ooops", u"Vous ne devez sélectionner qu'un seul polygone !", level=Qgis.Warning, duration=5) reqwhere_geom = '' else : # Requete finale reqwhere_final = '(' + reqwhere_obs + ')' + 'AND' + reqwhere_insee + 'AND' + reqwhere_ordre + 'AND' + reqwhere_sp + 'AND' + '(' + reqwhere_pat + ')' + 'AND' + reqwhere_regne + 'AND' + reqwhere_date # Nettoyage de la requete finale while reqwhere_final.find('ANDAND') != -1 : reqwhere_final = reqwhere_final.replace('ANDAND','AND') while reqwhere_final.find('AND()AND') != -1 : reqwhere_final = reqwhere_final.replace('AND()AND','AND') while reqwhere_final[-3:] == 'AND' : reqwhere_final = reqwhere_final[:-3] while reqwhere_final[:5] == '()AND' : reqwhere_final = reqwhere_final[5:] while reqwhere_final[-5:] == 'AND()' : reqwhere_final = reqwhere_final[:-5] while reqwhere_final[:3] == 'AND' : reqwhere_final = reqwhere_final[3:] ## Affichage des tables table_name = 'observations' uri = QgsDataSourceUri() uri.setConnection(host ,port ,dbname ,user ,mdp) ## Centroide if self.dlg.centroide.isChecked() == 1 : if reqwhere_final.find('()') != -1 : reqwhere_final = '' uri.setDataSource("_agregation_ra", "observations_centroide", "geometrie", reqwhere_final, "gid") layer = self.iface.addVectorLayer(uri.uri(), table_name + '_centroides', "postgres") else : first_conn = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=first_cnx password=" + password) first_cur = first_conn.cursor(cursor_factory = psycopg2.extras.DictCursor) first_cur.execute("SELECT mdp_w, login_w FROM pg_catalog.pg_user t1, admin_sig.vm_users_sig t2 WHERE t2.oid = t1.usesysid AND (login_w = '" + os_user + "' OR login_w = '" + os_user + "')") res_ident = first_cur.fetchone() mdp = base64.b64decode(str(res_ident[0])).decode('utf-8') user = res_ident[1] con = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=" + user + " password=" + mdp) cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor) first_conn.close() # Creation group de couches i = qgis.utils.iface #legende = i.legendInterface() legende = QgsProject.instance().layerTreeRoot() #nomgroupe = 'Observations' #groupe = legende.addGroup(nomgroupe,False) groupe = legende.insertGroup(1, "Observations") groupe = legende.findGroup("Observations") # Récupération de la liste de groupes #toc = iface.layerTreeView() #toc = qgis.utils.iface.legendInterface() #groups = toc. #groups = toc.groups() #index = groups.index(nomgroupe) index = 1 ### Polygone reqwhere_final_poly = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POLYGON%'" + ')' if reqwhere_final_poly.find('()') != -1 : reqwhere_final_poly = "(GeometryType(geometrie) LIKE '%POLYGON%')" SQL_count_poly = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_poly cur.execute(SQL_count_poly) list_count_poly = cur.fetchall() count_poly = list_count_poly[0][0] if count_poly != 0: uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_poly, "gid") #layer_poly = self.iface.addVectorLayer(uri.uri(), table_name + '_poly', "postgres") layer_poly = QgsVectorLayer(uri.uri(), table_name + '_poly', "postgres") QgsProject.instance().addMapLayer(layer_poly, False) #legende.moveLayer(layer_poly,index) #groupe.addLayer(layer_poly) groupe.insertChildNode(0, QgsLayerTreeLayer(layer_poly)) ### Point reqwhere_final_point = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POINT%'" + ')' if reqwhere_final_point.find('()') != -1 : reqwhere_final_point = "(GeometryType(geometrie) LIKE '%POINT%')" SQL_count_point = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_point cur.execute(SQL_count_point) list_count_point = cur.fetchall() count_point = list_count_point[0][0] if count_point != 0: uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_point, "gid") #layer_point = self.iface.addVectorLayer(uri.uri(), table_name + '_point', "postgres") layer_point = QgsVectorLayer(uri.uri(), table_name + '_point', "postgres") QgsProject.instance().addMapLayer(layer_point, False) #legende.moveLayer(layer_point,index) #groupe.addLayer(layer_point) groupe.insertChildNode(0, QgsLayerTreeLayer(layer_point)) ### Ligne reqwhere_final_ligne = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%LINE%'" + ')' if reqwhere_final_ligne.find('()') != -1 : reqwhere_final_ligne = "(GeometryType(geometrie) LIKE '%LINE%')" SQL_count_ligne = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_ligne cur.execute(SQL_count_ligne) list_count_ligne = cur.fetchall() count_ligne = list_count_ligne[0][0] if count_ligne != 0: uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_ligne, "gid") #layer_ligne = self.iface.addVectorLayer(uri.uri(), table_name + '_ligne', "postgres") layer_ligne = QgsVectorLayer(uri.uri(), table_name + '_ligne', "postgres") QgsProject.instance().addMapLayer(layer_ligne, False) #legende.moveLayer(layer_ligne,index) #groupe.addLayer(layer_ligne) groupe.insertChildNode(0, QgsLayerTreeLayer(layer_ligne)) # ajout des couches dans le groupe Observations #legende.setGroupExpanded (index, True) #self.iface.messageBar().pushMessage(u"Extraction réussie : ", u" Pensez à renommer vos couches pour plus de lisibilité.", level=QgsMessageBar.INFO, duration=10) self.iface.messageBar().pushMessage("Info", u"Extraction réussie : ", u" Pensez à renommer vos couches pour plus de lisibilité.", level=Qgis.Success, duration=10) pass def export(self): layer = self.iface.activeLayer() if layer == None : #self.iface.messageBar().pushMessage(u"Vous devez sélectionner une table !", level=QgsMessageBar.WARNING, duration=5) self.iface.messageBar().pushMessage("Ooops", u"Vous devez sélectionner une table !", level=Qgis.Warning, duration=5) else : selection = layer.selectedFeatures() if (layer.selectedFeatureCount() == 1) : for feature in selection: geom = feature.geometry() poly = geom.asWkt() buffer = '100' first_conn = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=first_cnx password=" + password) first_cur = first_conn.cursor(cursor_factory = psycopg2.extras.DictCursor) first_cur.execute("SELECT mdp_w, login_w FROM pg_catalog.pg_user t1, admin_sig.vm_users_sig t2 WHERE t2.oid = t1.usesysid AND (login_w = '" + os_user + "' OR login_w = '" + os_user + "')") res_ident = first_cur.fetchone() mdp = base64.b64decode(str(res_ident[0])).decode('utf-8') user = res_ident[1] con = psycopg2.connect("host=" + host + " port=" + port + " dbname="+dbname+" user=" + user + " password=" + mdp) cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor) first_conn.close() SQL_list_sp = u"""SELECT DISTINCT classe::text,ordre::text,nom_complet::text,split_part(nom_vern::text,',',1) as nom_vern,cd_ref::text as cd_ref,max(COALESCE(date_obs, date_debut_obs))::text AS derniere_obs FROM _agregation_ra.observations_table WHERE ST_intersects(geometrie,ST_Buffer(ST_geomFromText('""" + poly + "',2154), " + buffer + " ))GROUP BY classe::text, ordre::text, nom_complet::text, nom_vern::text, cd_ref::text ORDER BY nom_complet::text""" cur.execute(SQL_list_sp) data_sp = cur.fetchall() chemin_fichier, __ = QFileDialog.getSaveFileName(None, 'Enregistrer sous...', r"C:\Users\\Desktop\\" + str(os.environ.get("USERNAME")) + '_liste_sp.csv', "Fichiers CSV (*.csv)") #file = open(chemin_fichier, 'wb') file = open(chemin_fichier, 'w', newline='', encoding='utf-8') #file.write('\ufeff'.encode('utf8')) # BOM (optionel...Permet a Excel d'ouvrir proprement le fichier en UTF-8) file.write('\ufeff') writer = csv.writer(file, delimiter = ';') # délimiteur ';' pour faciliter l'ouverture avec Excel writer.writerow(['Classe', 'Ordre', 'Nom Complet', 'Nom Vernaculaire','cd_ref', 'Date Derniere Observation']) # Création des entêtes for row in data_sp : # Boucle d'écriture ligne par ligne dans le csv #print(1) list_sp = [] if row[0] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement row0 = str(row[0]) row0 = '' else : row0 = row[0] #print(row0) list_sp.append(row0) if row[1] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement row1 = str(row[1]) row1 = '' else : row1 = row[1] list_sp.append(row1) if row[2] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement row2 = str(row[2]) row2 = '' else : row2 = row[2] list_sp.append(row2) if row[3] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement row3 = str(row[3]) row3 = '' else : row3 = row[3] list_sp.append(row3) if row[4] == None : row4 = str(row[4]) row4 = '' else : row4 = row[4] list_sp.append(row4) if row[5] == None : row5 = str(row[5]) row5 = '' else : row5 = row[5] print(row5) list_sp.append(row5) writer.writerow(list_sp) file.close() #self.iface.messageBar().pushMessage(u"Export réussi dans " + chemin_fichier , level=QgsMessageBar.INFO, duration=5) self.iface.messageBar().pushMessage("Ooops", u"Export réussi dans " + chemin_fichier, level=Qgis.Success, duration=5) elif (layer.selectedFeatureCount() == 0) : #self.iface.messageBar().pushMessage(u"Vous devez sélectionner au moins un polygone !", level=QgsMessageBar.WARNING, duration=5) self.iface.messageBar().pushMessage("Ooops", u"Vous devez sélectionner au moins un polygone !", level=Qgis.Warning, duration=5) else : #self.iface.messageBar().pushMessage(u"Vous devez sélectionner qu'un seul polygone !", level=QgsMessageBar.WARNING, duration=5) self.iface.messageBar().pushMessage("Ooops", u"Vous ne devez sélectionner qu'un seul polygone !", level=Qgis.Warning, duration=5)