924 lines
42 KiB
Python
924 lines
42 KiB
Python
# -*- coding: utf-8 -*-
|
|
|
|
from builtins import str
|
|
from builtins import object
|
|
import os
|
|
|
|
# from qgis.PyQt import uic
|
|
from qgis.core import QgsDataSourceUri, QgsLayerTreeLayer, QgsProject, QgsVectorLayer, Qgis
|
|
from qgis.PyQt.QtGui import QIcon
|
|
from qgis.PyQt.QtWidgets import QMenu, QAction, QFileDialog
|
|
from qgis.PyQt.QtCore import QSettings, QTranslator, QCoreApplication, qVersion
|
|
|
|
# Initialize Qt resources from file resources.py
|
|
from . import resources_rc
|
|
# Import the code for the dialog
|
|
from .sicendialog import SICENDialog
|
|
|
|
import os.path
|
|
import qgis
|
|
import datetime
|
|
import csv
|
|
|
|
from .forms.about_form import AboutDialog
|
|
|
|
# import psycopg2
|
|
# import psycopg2.extras
|
|
# import base64
|
|
import socket
|
|
import os
|
|
|
|
from .tools.resources import maj_verif, pyperclip
|
|
pyperclip()
|
|
try:
|
|
from .tools.PythonSQL import login_base
|
|
except ValueError:
|
|
print('Pas de fichier PythonSQL')
|
|
|
|
# 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())
|
|
self.action.setEnabled(False)
|
|
if os.access('N:/', os.R_OK):
|
|
self.action.setEnabled(True)
|
|
# 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())
|
|
self.action.setEnabled(False)
|
|
IPAddr = socket.gethostbyname(socket.gethostname())
|
|
if IPAddr[0:11] == "100.100.100":
|
|
self.action.setEnabled(True)
|
|
elif IPAddr[0:9] == "192.168.0":
|
|
self.action.setEnabled(True)
|
|
elif IPAddr[0:9] == "192.168.1":
|
|
self.action.setEnabled(True)
|
|
# 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 = AboutDialog(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):
|
|
|
|
account = login_base("account")
|
|
user = account[0]
|
|
mdp = account[1]
|
|
host = account[2]
|
|
port = account[3]
|
|
dbname = account[4]
|
|
cur = account[7]
|
|
con = account[8]
|
|
|
|
# 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
|
|
# Pour ne pas commencer la liste au premier item
|
|
self.dlg.observateur_1.setCurrentIndex(-1)
|
|
|
|
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:
|
|
|
|
account = login_base("account")
|
|
user = account[0]
|
|
mdp = account[1]
|
|
host = account[2]
|
|
port = account[3]
|
|
dbname = account[4]
|
|
cur = account[7]
|
|
con = account[8]
|
|
|
|
# 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_znieff + '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 is 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:
|
|
|
|
account = login_base("account")
|
|
user = account[0]
|
|
mdp = account[1]
|
|
host = account[2]
|
|
port = account[3]
|
|
dbname = account[4]
|
|
cur = account[7]
|
|
con = account[8]
|
|
|
|
# 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.Info, 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:
|
|
|
|
account = login_base("account")
|
|
user = account[0]
|
|
mdp = account[1]
|
|
host = account[2]
|
|
port = account[3]
|
|
dbname = account[4]
|
|
cur = account[7]
|
|
con = account[8]
|
|
|
|
# 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.Info, duration=10)
|
|
|
|
pass
|
|
|
|
def export(self):
|
|
|
|
layer = self.iface.activeLayer()
|
|
|
|
if layer is 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'
|
|
|
|
account = login_base("account")
|
|
# user = account[0]
|
|
# mdp = account[1]
|
|
# host = account[2]
|
|
# port = account[3]
|
|
# dbname = account[4]
|
|
cur = account[7]
|
|
# con = account[8]
|
|
|
|
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')
|
|
# délimiteur ';' pour faciliter l'ouverture avec Excel
|
|
writer = csv.writer(file, delimiter=';')
|
|
# Création des entêtes
|
|
writer.writerow(['Classe', 'Ordre', 'Nom Complet', 'Nom Vernaculaire', 'cd_ref', 'Date Derniere Observation'])
|
|
|
|
# Boucle d'écriture ligne par ligne dans le csv
|
|
for row in data_sp:
|
|
# print(1)
|
|
list_sp = []
|
|
# Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
if row[0] is None:
|
|
row0 = str(row[0])
|
|
row0 = ''
|
|
else:
|
|
row0 = row[0]
|
|
# print(row0)
|
|
list_sp.append(row0)
|
|
# Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
if row[1] is None:
|
|
row1 = str(row[1])
|
|
row1 = ''
|
|
else:
|
|
row1 = row[1]
|
|
list_sp.append(row1)
|
|
# Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
if row[2] is None:
|
|
row2 = str(row[2])
|
|
row2 = ''
|
|
else:
|
|
row2 = row[2]
|
|
list_sp.append(row2)
|
|
# Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
if row[3] is None:
|
|
row3 = str(row[3])
|
|
row3 = ''
|
|
else:
|
|
row3 = row[3]
|
|
list_sp.append(row3)
|
|
if row[4] is None:
|
|
row4 = str(row[4])
|
|
row4 = ''
|
|
else:
|
|
row4 = row[4]
|
|
list_sp.append(row4)
|
|
if row[5] is 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.Info, 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)
|