forked from CEN-RA/Plugin_QGIS
209 lines
11 KiB
Python
209 lines
11 KiB
Python
# -*- coding: utf-8 -*-
|
|
|
|
from __future__ import absolute_import
|
|
# Import the PyQt and QGIS libraries
|
|
from builtins import next
|
|
from builtins import str
|
|
from builtins import object
|
|
import qgis
|
|
from qgis.PyQt.QtCore import QSettings
|
|
from qgis.PyQt.QtWidgets import QAction, QMenu, QDialog
|
|
from qgis.PyQt.QtGui import QIcon
|
|
from PyQt5.QtCore import *
|
|
from PyQt5.QtGui import *
|
|
from PyQt5 import QtGui
|
|
from qgis.core import *
|
|
from qgis.core import QgsDataSourceUri
|
|
|
|
from .tools.PythonSQL import *
|
|
from .tools.resources import (
|
|
load_ui,
|
|
resources_path,
|
|
login_base,
|
|
send_issues,
|
|
)
|
|
from .issues import CenRa_Issues
|
|
|
|
from qgis.utils import iface
|
|
import os.path
|
|
import webbrowser, os
|
|
import psycopg2
|
|
import psycopg2.extras
|
|
import base64
|
|
|
|
EDITOR_CLASS = load_ui('CenRa_Copie_base.ui')
|
|
|
|
class Copie_Editor(QDialog, EDITOR_CLASS):
|
|
|
|
def __init__(self, parent=None):
|
|
_ = parent
|
|
super().__init__()
|
|
self.setupUi(self)
|
|
self.settings = QgsSettings()
|
|
self.s = QSettings()
|
|
self.setWindowIcon(QtGui.QIcon(resources_path('icons','icon.png')))
|
|
self.iface = iface
|
|
|
|
def raise_(self):
|
|
"""Run method that performs all the real work"""
|
|
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 :
|
|
# Récupération des sources de la couche active
|
|
list_sources = layer.source().split(" ")
|
|
# dbname
|
|
source_db = [s for s in list_sources if "dbname" in s][0].split("'")[1]
|
|
# schema
|
|
source_schema = [s for s in list_sources if "table" in s][0].split('"')[1]
|
|
# tablename
|
|
source_tablename = [s for s in list_sources if "table" in s][0].split('"')[3]
|
|
|
|
if source_db != sigdb:
|
|
#self.iface.messageBar().pushMessage(u"Un référentiel ne peut être copié, utilisez les filtres !", level=QgsMessageBar.CRITICAL, duration=10)
|
|
self.iface.messageBar().pushMessage("Ooops", u"Vous ne pouvez copier des couches que dans sigXX", level=Qgis.Critical, duration=5)
|
|
|
|
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 de la liste des schemas de la base de donnees
|
|
SQL = """WITH list_schema AS (
|
|
SELECT catalog_name, schema_name
|
|
FROM information_schema.schemata
|
|
WHERE schema_name <> 'information_schema'
|
|
AND schema_name !~ E'^pg_'
|
|
ORDER BY schema_name
|
|
)
|
|
|
|
SELECT string_agg(schema_name,',')
|
|
FROM list_schema
|
|
GROUP BY catalog_name"""
|
|
|
|
cur.execute(SQL)
|
|
|
|
list_brut = str(next(cur))
|
|
|
|
list = list_brut [2:-3]
|
|
listItems = list.split(",")
|
|
|
|
con.close()
|
|
|
|
self.schema.clear()
|
|
self.schema.addItems(listItems)
|
|
self.schema.setCurrentIndex(-1) # Pour ne pas commencer la liste au premier schema
|
|
|
|
|
|
self.table_source.setText(source_schema + "." + source_tablename) # Affiche le nom de la table source
|
|
# show the dialog
|
|
self.show()
|
|
# Run the dialog event loop
|
|
result = self.exec_()
|
|
# See if OK was pressed
|
|
if result:
|
|
#******************************debut script*********************************
|
|
|
|
|
|
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()
|
|
# Récupération de la couche active
|
|
layer = self.iface.activeLayer()
|
|
|
|
# Récupération des sources de la couche active
|
|
list_sources = layer.source().split(" ")
|
|
# dbname
|
|
source_db = [s for s in list_sources if "dbname" in s][0].split("'")[1]
|
|
# schema
|
|
source_schema = [s for s in list_sources if "table" in s][0].split('"')[1]
|
|
# tablename
|
|
source_tablename = [s for s in list_sources if "table" in s][0].split('"')[3]
|
|
|
|
if self.schema.currentIndex() == -1 :
|
|
QMessageBox.warning(None, "Oups :", "Veuillez choisir un dossier de destination.")
|
|
return
|
|
|
|
schema = self.schema.currentText()
|
|
|
|
if self.table_destination.text() == '' :
|
|
QMessageBox.warning(None, "Oups :", "Veuillez choisir un nom de destination.")
|
|
return
|
|
|
|
if self.annee.text() == 'aaaa' or self.annee.text() == '':
|
|
tablename = schema + "_" + self.table_destination.text().lower()
|
|
else :
|
|
tablename = schema + "_" + self.table_destination.text().lower() + "_" + self.annee.text()
|
|
|
|
tablename_qgis = tablename[1:] # Permet d'enlever le "_", ajouter a la premiere etape, dans qgis
|
|
|
|
if self.table_vide.isChecked() == 1 :
|
|
SQL_table = "CREATE TABLE " + schema + "." + tablename + " AS SELECT * FROM " + source_schema + "." + source_tablename + " LIMIT 0;"
|
|
else :
|
|
SQL_table = "CREATE TABLE " + schema + "." + tablename + " AS SELECT * FROM " + source_schema + "." + source_tablename
|
|
|
|
|
|
SQL_pkey = "ALTER TABLE " + schema + "." + tablename + " ADD CONSTRAINT " + tablename + "_pkey" + " PRIMARY KEY (gid)"
|
|
SQL_sequence_01 = "CREATE SEQUENCE " + schema + "." + tablename + "_gid_seq" + " INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;"
|
|
SQL_sequence_02 = "ALTER TABLE " + schema + "." + tablename + " ALTER COLUMN gid SET DEFAULT nextval(\'" + schema + "." + tablename + "_gid_seq\'::regclass);"
|
|
SQL_sequence_03 = "SELECT setval(\'" + schema + "." + tablename + "_gid_seq\'::regclass, (SELECT max(gid) AS max_gid FROM " + schema + "." + tablename + "));"
|
|
SQL_sequence_04 = "ALTER SEQUENCE " + schema + "." + tablename + "_gid_seq" + " OWNED BY " + schema + "." + tablename + ".gid;"
|
|
|
|
SQL_trigger_area_m2 = "CREATE TRIGGER area_m2" + tablename + " BEFORE INSERT OR UPDATE ON " + schema + "." + tablename + " FOR EACH ROW EXECUTE PROCEDURE ref.area_m2();"
|
|
SQL_trigger_area_ha = "CREATE TRIGGER area_ha" + tablename + " BEFORE INSERT OR UPDATE ON " + schema + "." + tablename + " FOR EACH ROW EXECUTE PROCEDURE ref.area_ha();"
|
|
SQL_trigger_length_m = "CREATE TRIGGER length_m" + tablename + " BEFORE INSERT OR UPDATE ON " + schema + "." + tablename + " FOR EACH ROW EXECUTE PROCEDURE ref.length_m();"
|
|
SQL_trigger_length_km = "CREATE TRIGGER length_km" + tablename + " BEFORE INSERT OR UPDATE ON " + schema + "." + tablename + " FOR EACH ROW EXECUTE PROCEDURE ref.length_km();"
|
|
SQL_trigger_coordonnees = "CREATE TRIGGER coordonnees" + tablename + " BEFORE INSERT OR UPDATE ON " + schema + "." + tablename + " FOR EACH ROW EXECUTE PROCEDURE ref.coordonnees();"
|
|
|
|
cur.execute(SQL_table)
|
|
cur.execute(SQL_pkey)
|
|
cur.execute(SQL_sequence_01)
|
|
cur.execute(SQL_sequence_02)
|
|
cur.execute(SQL_sequence_03)
|
|
cur.execute(SQL_sequence_04)
|
|
|
|
if layer.wkbType() == QgsWkbTypes.PointGeometry :
|
|
cur.execute(SQL_trigger_coordonnees)
|
|
|
|
if layer.wkbType() == QgsWkbTypes.LineGeometry :
|
|
cur.execute(SQL_trigger_length_m)
|
|
cur.execute(SQL_trigger_length_km)
|
|
|
|
if layer.wkbType() == QgsWkbTypes.PolygonGeometry :
|
|
cur.execute(SQL_trigger_area_m2)
|
|
cur.execute(SQL_trigger_area_ha)
|
|
|
|
con.commit()
|
|
|
|
### Affichage de la table
|
|
uri = QgsDataSourceUri()
|
|
# set host name, port, database name, username and password
|
|
uri.setConnection(host ,port ,dbname ,user ,mdp)
|
|
# set database schema, table name, geometry column and optionaly subset (WHERE clause)
|
|
uri.setDataSource(schema, tablename, geom)
|
|
|
|
layer = self.iface.addVectorLayer(uri.uri(), tablename_qgis, "postgres")
|
|
|
|
con.commit()
|
|
con.close()
|
|
|
|
#self.iface.messageBar().pushMessage("Table \"" + source_schema + "." + source_tablename + u"\" copiée dans \"" + schema + "." + tablename + "\"." , level=QgsMessageBar.INFO, duration=10)
|
|
self.iface.messageBar().pushMessage("Bravo!", "Table \"" + source_schema + "." + source_tablename + u"\" copiée dans \"" + schema + "." + tablename + "\".", level=Qgis.Success, duration=5)
|
|
pass
|