623 lines
28 KiB
Python
623 lines
28 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
/***************************************************************************
|
|
CreateSessionAF
|
|
A QGIS plugin
|
|
Permet de créer une nouvelle session d'animation foncière
|
|
-------------------
|
|
begin : 2015-10-02
|
|
git sha : $Format:%H$
|
|
copyright : (C) 2015 by CEN Savoie
|
|
email : a.lesconnec@cen-savoie.org
|
|
***************************************************************************/
|
|
|
|
/***************************************************************************
|
|
* *
|
|
* 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 PyQt5 import QtCore, QtGui
|
|
from PyQt5.QtCore import *
|
|
from PyQt5.QtGui import *
|
|
from PyQt5.QtWidgets import QAction, QTableWidgetItem, QMessageBox
|
|
from qgis.core import *
|
|
|
|
# Initialize Qt resources from file resources.py
|
|
from pluginsCenSavoie_v3 import resources
|
|
|
|
# Import the code for the dialog
|
|
from pluginsCenSavoie_v3.tools.create_sessionAF_dialog import CreateSessionAFDialog
|
|
|
|
# Import du fichier de configuration
|
|
from pluginsCenSavoie_v3.tools.confPython import bd_cen_host, bd_cen_port, bd_cen_name, bd_cen_mdp_1st_cnx
|
|
|
|
import os.path
|
|
import sys
|
|
import qgis
|
|
import os
|
|
import psycopg2
|
|
import psycopg2.extras
|
|
import base64
|
|
import re
|
|
from time import localtime, strftime
|
|
|
|
class CreateSessionAF:
|
|
"""QGIS Plugin Implementation."""
|
|
|
|
def __init__(self, iface, cenToolbar):
|
|
"""Constructor.
|
|
|
|
:param iface: An interface instance that will be passed to this class
|
|
which provides the hook by which you can manipulate the QGIS
|
|
application at run time.
|
|
:type iface: QgsInterface
|
|
"""
|
|
# Variable HOTE de l'accès à la base de données
|
|
global host
|
|
host = bd_cen_host
|
|
global port
|
|
port = bd_cen_port
|
|
global bdd
|
|
bdd = bd_cen_name
|
|
global mdp_1st_cnx
|
|
mdp_1st_cnx = bd_cen_mdp_1st_cnx
|
|
global os_user
|
|
if sys.platform in ('linux','darwin'):
|
|
os_user = os.environ['USER']
|
|
else:
|
|
os_user = os.environ['USERNAME']
|
|
|
|
# Save reference to the QGIS interface
|
|
self.iface = iface
|
|
self.canvas = self.iface.mapCanvas()
|
|
|
|
# initialize plugin directory
|
|
self.plugin_dir = os.path.dirname(__file__)
|
|
|
|
# Create the dialog (after translation) and keep reference
|
|
self.dlg = CreateSessionAFDialog()
|
|
|
|
# Add toolbar button
|
|
self.action= QAction(QIcon(":/plugins/pluginsCenSavoie_v3/icons/createSessionAF.png"), QCoreApplication.translate('CreateSessionAF', u'Créer une Session d''AF'), self.iface.mainWindow())
|
|
self.action.triggered.connect(self.run)
|
|
self.action.setEnabled(False)
|
|
cenToolbar.addAction(self.action)
|
|
self.canvas.selectionChanged.connect(self.toggle)
|
|
self.iface.layerTreeView().currentLayerChanged.connect(self.toggle)
|
|
|
|
def toggle(self):
|
|
layer = self.iface.activeLayer()
|
|
if layer and layer.type() == layer.VectorLayer:
|
|
if layer.selectedFeatureCount() > 0:
|
|
provider = layer.dataProvider()
|
|
source_postgis = provider.dataSourceUri()
|
|
source_postgis = source_postgis.replace('"', '')
|
|
if (source_postgis.count('table=animation_fonciere.parcelles_dispo_saf') == 1):
|
|
self.action.setEnabled(True)
|
|
else:
|
|
self.action.setEnabled(False)
|
|
else:
|
|
self.action.setEnabled(False)
|
|
else:
|
|
self.action.setEnabled(False)
|
|
|
|
def run(self):
|
|
"""Run method that performs all the real work"""
|
|
layer = self.iface.activeLayer()
|
|
if layer and layer.type() == layer.VectorLayer:
|
|
provider = layer.dataProvider()
|
|
source_postgis = provider.dataSourceUri()
|
|
source_postgis = source_postgis.replace('"', '')
|
|
if (source_postgis.count('table=animation_fonciere.parcelles_dispo_saf') != 1):
|
|
QMessageBox.critical(None, "Attention", "Vous devez sélectionnez des objets depuis la vue 'parcelles_dispo_saf'")
|
|
else:
|
|
if layer.selectedFeatureCount() < 1:
|
|
QMessageBox.critical(None, "Attention", "Vous devez sélectionnez au moins une parcelle")
|
|
else:
|
|
global user, mdp
|
|
first_conn = psycopg2.connect("host=" + host + " port=" + port + " dbname=" + bdd + " user=first_cnx password=" + mdp_1st_cnx)
|
|
first_cur = first_conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
|
|
first_cur.execute("SELECT mdp_cen, usename FROM pg_catalog.pg_user t1, admin_sig.utilisateurs t2 WHERE t2.oid = t1.usesysid AND (utilisateur_id = '" + os_user + "' OR utilisateur_os = '" + os_user + "')")
|
|
res_ident = first_cur.fetchone()
|
|
mdp = base64.b64decode(str(res_ident[0])).decode('utf-8')
|
|
user = res_ident[1]
|
|
first_conn.close()
|
|
|
|
self.dlg.tbl_parcelle.setColumnCount(3)
|
|
self.dlg.tbl_parcelle.setHorizontalHeaderItem(0, QTableWidgetItem('Par ID'))
|
|
self.dlg.tbl_parcelle.setHorizontalHeaderItem(1, QTableWidgetItem('Priorite'))
|
|
self.dlg.tbl_parcelle.setHorizontalHeaderItem(2, QTableWidgetItem('cad_site_id'))
|
|
self.dlg.tbl_parcelle.cellClicked.connect(self.changeCellValue)
|
|
|
|
site_id = []
|
|
self.canvas = self.iface.mapCanvas()
|
|
|
|
rowCount = 0
|
|
for feature in layer.selectedFeatures():
|
|
site_id.append(feature["site_id"])
|
|
rowCount += 1
|
|
self.dlg.tbl_parcelle.setRowCount(rowCount)
|
|
|
|
if rowCount > 0:
|
|
ligne = 0
|
|
col = 0
|
|
for feature in layer.selectedFeatures():
|
|
self.dlg.tbl_parcelle.setItem(ligne, 0, QTableWidgetItem(feature["par_id"]))
|
|
self.dlg.tbl_parcelle.setItem(ligne, 1, QTableWidgetItem('1'))
|
|
self.dlg.tbl_parcelle.setItem(ligne, 2, QTableWidgetItem(feature["tbl_cad_site_id"]))
|
|
ligne += 1
|
|
self.dlg.tbl_parcelle.setEnabled(True)
|
|
else:
|
|
self.dlg.tbl_parcelle.setEnabled(False)
|
|
self.dlg.tbl_parcelle.hideColumn(2)
|
|
self.dlg.tbl_parcelle.resizeColumnsToContents()
|
|
self.dlg.tbl_parcelle.sortItems(0, 0)
|
|
|
|
rq_saf_geom = "SELECT st_multi(st_union(geom)) as geom FROM animation_fonciere.parcelles_dispo_saf WHERE par_id IN ("
|
|
for feature in layer.selectedFeatures():
|
|
rq_saf_geom += "'" + str(feature["par_id"]) + "',"
|
|
rq_saf_geom = rq_saf_geom[0:(len(rq_saf_geom)-1)] + ")"
|
|
|
|
tbl_site_id = list(set(site_id))
|
|
self.dlg.InitFormulaire(tbl_site_id, rq_saf_geom, 'load', host, port, bdd, user, mdp)
|
|
self.dlg.img_logo.setPixmap(QPixmap(":/plugins/pluginsCenSavoie_v3/tools/images/Logo_CEN_Savoie.png"))
|
|
self.dlg.lbl_fond.setPixmap(QPixmap(":/plugins/pluginsCenSavoie_v3/tools/images/fond_70.png"))
|
|
self.dlg.lbl_fond.stackUnder(self.dlg.button_box)
|
|
|
|
# show the dialog
|
|
self.dlg.show()
|
|
# Run the dialog event loop
|
|
result = self.dlg.exec_()
|
|
|
|
# See if OK was pressed
|
|
if result:
|
|
curs = QCursor()
|
|
curs.setShape(Qt.WaitCursor)
|
|
qgis.utils.iface.mainWindow().centralWidget().setCursor(curs)
|
|
|
|
conn = psycopg2.connect("host=" + host + " port=" + port + " dbname=" + bdd + " user=" + user + " password=" + mdp)
|
|
cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
|
|
|
|
rq_dnuper = """
|
|
SELECT DISTINCT
|
|
t6.dnuper
|
|
FROM
|
|
cadastre.r_prop_cptprop_cen t6
|
|
JOIN cadastre.cptprop_cen t5 USING (dnupro)
|
|
JOIN cadastre.cadastre_cen t3 USING (dnupro)
|
|
JOIN cadastre.lots_cen t2 USING (lot_id)
|
|
WHERE
|
|
t6.dnuper NOT IN (
|
|
SELECT DISTINCT dnuper FROM animation_fonciere.rel_proprio_interloc WHERE dnuper IS NOT NULL
|
|
UNION
|
|
SELECT DISTINCT dnuper FROM animation_fonciere.interlocuteurs WHERE dnuper IS NOT NULL
|
|
) AND
|
|
t3.date_fin = 'N.D.' AND t2.par_id IN ("""
|
|
for r in range(self.dlg.tbl_parcelle.rowCount()):
|
|
if self.dlg.tbl_parcelle.isRowHidden(r) == False:
|
|
rq_dnuper += "'" + str(self.dlg.tbl_parcelle.item(r, 0).text()) + "',"
|
|
rq_dnuper = rq_dnuper[0:(len(rq_dnuper)-1)] + ")"
|
|
with conn:
|
|
with conn.cursor() as cur:
|
|
cur.execute(rq_dnuper)
|
|
res_dnuper = cur.fetchall()
|
|
i=0
|
|
while i < len(res_dnuper): #Boucle sur toutes les options de niveau 1
|
|
rq_check_dnuper_missed = """
|
|
WITH t1 AS (
|
|
SELECT nom_usage||'*$'||prenom_usage||'*$'||ccoqua||'*$'||REPLACE(jdatnss, '-', '')||'*$'||COALESCE(NULLIF(dldnss, ''), 'N.D.')||'*$'||dlign6 as check, dnuper FROM cadastre.proprios_cen
|
|
),
|
|
t_update AS (
|
|
UPDATE animation_fonciere.interlocuteurs
|
|
SET dnuper = t1.dnuper FROM t1
|
|
WHERE
|
|
interlocuteurs.dnuper IS NULL AND
|
|
t1.check = nom_usage||'*$'||prenom_usage||'*$'||ccoqua||'*$'||REPLACE(jdatnss, '-', '')||'*$'||COALESCE(NULLIF(dldnss, ''), 'N.D.')||'*$'||dlign6
|
|
RETURNING interlocuteurs.interloc_id, interlocuteurs.dnuper
|
|
)
|
|
INSERT INTO animation_fonciere.rel_proprio_interloc (SELECT dnuper, interloc_id FROM t_update WHERE dnuper || interloc_id::text NOT IN (SELECT dnuper || interloc_id::text
|
|
FROM animation_fonciere.rel_proprio_interloc))
|
|
RETURNING *"""
|
|
cur.execute(rq_check_dnuper_missed)
|
|
res_check_dnuper_missed = cur.fetchone()
|
|
if cur.rowcount == 0 :
|
|
|
|
rq_new_interloc_id = "SELECT nextval('animation_fonciere.interloc_id_seq'::regclass)";
|
|
cur.execute(rq_new_interloc_id)
|
|
res_new_interloc_id = cur.fetchone()
|
|
new_interloc_id = res_new_interloc_id[0]
|
|
|
|
rq_insert_interloc = """
|
|
INSERT INTO animation_fonciere.interlocuteurs (
|
|
interloc_id,
|
|
etat_interloc_id,
|
|
dnuper,
|
|
ccoqua,
|
|
ddenom,
|
|
nom_usage,
|
|
nom_naissance,
|
|
prenom_usage,
|
|
dnomlp,
|
|
dprnlp,
|
|
jdatnss,
|
|
dldnss,
|
|
dlign3,
|
|
dlign4,
|
|
dlign5,
|
|
dlign6,
|
|
email,
|
|
fixe_domicile,
|
|
fixe_travail,
|
|
portable_domicile,
|
|
portable_travail,
|
|
fax,
|
|
gtoper,
|
|
ccogrm,
|
|
dnatpr,
|
|
dsglpm,
|
|
commentaires,
|
|
annee_matrice,
|
|
maj_user,
|
|
maj_date,
|
|
dqualp,
|
|
dnomus,
|
|
dprnus,
|
|
dforme)
|
|
(SELECT
|
|
""" + str(new_interloc_id) + """,
|
|
1,
|
|
t1.dnuper,
|
|
t1.ccoqua,
|
|
t1.ddenom,
|
|
t1.nom_usage,
|
|
t1.nom_naissance,
|
|
t1.prenom_usage,
|
|
t1.dnomlp,
|
|
t1.dprnlp,
|
|
t1.jdatnss,
|
|
t1.dldnss,
|
|
t1.dlign3,
|
|
t1.dlign4,
|
|
t1.dlign5,
|
|
t1.dlign6,
|
|
t1.email,
|
|
t1.fixe_domicile,
|
|
t1.fixe_travail,
|
|
t1.portable_domicile,
|
|
t1.portable_travail,
|
|
t1.fax,
|
|
t1.gtoper,
|
|
t1.ccogrm,
|
|
t1.dnatpr,
|
|
t1.dsglpm,
|
|
t1.commentaires,
|
|
t1.annee_matrice,
|
|
t1.maj_user,
|
|
t1.maj_date,
|
|
t1.dqualp,
|
|
t1.dnomus,
|
|
t1.dprnus,
|
|
t1.dforme
|
|
FROM cadastre.proprios_cen t1
|
|
WHERE t1.dnuper = '""" + res_dnuper[i][0] + """')"""
|
|
cur.execute(rq_insert_interloc)
|
|
conn.commit()
|
|
|
|
rq_insert_rel_proprio_interloc = "INSERT INTO animation_fonciere.rel_proprio_interloc (dnuper, interloc_id) VALUES ('" + str(res_dnuper[i][0]) + "', " + str(new_interloc_id) + ")"
|
|
cur.execute(rq_insert_rel_proprio_interloc)
|
|
conn.commit()
|
|
|
|
i=i+1
|
|
|
|
if self.dlg.saf_maj.text() == 'N.P.':
|
|
rq_new_session_af_id = "SELECT nextval('animation_fonciere.session_af_id_seq'::regclass)";
|
|
cur.execute(rq_new_session_af_id)
|
|
res_new_session_af_id = cur.fetchone()
|
|
session_af_id = res_new_session_af_id[0]
|
|
|
|
rq_insert_saf = "INSERT INTO animation_fonciere.session_af (session_af_id, session_af_lib, date_creation, date_maj, utilisateur, origine_id, geom) VALUES (" + str(session_af_id) + ", '" + str(self.dlg.saf_name.text()) + "', '" + str(self.dlg.saf_date.text()) + "', '" + str(self.dlg.saf_date.text()) + "', '" + str(self.dlg.saf_utilisateur.text()) + "', '" + str(self.dlg.saf_origin.text()) + "', (" + str(rq_saf_geom) + "))"
|
|
cur.execute(rq_insert_saf)
|
|
conn.commit()
|
|
else :
|
|
session_af_id = self.dlg.saf_maj.text()
|
|
|
|
for r in range(self.dlg.tbl_parcelle.rowCount()):
|
|
for cad_site_id in self.dlg.tbl_parcelle.item(r, 2).text().split(','): #Pour chaque cad_site_id
|
|
rq_check_cad_site_id = """
|
|
SELECT entite_af_id
|
|
FROM
|
|
animation_fonciere.rel_eaf_foncier
|
|
JOIN animation_fonciere.rel_saf_foncier USING (rel_eaf_foncier_id)
|
|
WHERE cad_site_id = """ + str(cad_site_id) + """ AND session_af_id = """ + str(session_af_id)
|
|
cur.execute(rq_check_cad_site_id)
|
|
if cur.rowcount == 0:
|
|
#On recherche si une entite_af_id existe avec les propriétaires de la parcelle
|
|
rq_check_entite_af_id = "SELECT COALESCE(animation_fonciere.f_get_eaf("+str(cad_site_id)+"), 0)"
|
|
cur.execute(rq_check_entite_af_id)
|
|
res_check_entite_af_id = cur.fetchone()
|
|
if cur.rowcount == 1 and res_check_entite_af_id[0] != 0: #si une EAF est trouvée
|
|
entite_af_id = res_check_entite_af_id[0]
|
|
elif cur.rowcount == 1 and res_check_entite_af_id[0] == 0: #sinon il faut tout ajouter
|
|
rq_new_eaf_id = "SELECT nextval('animation_fonciere.entite_af_id_seq'::regclass)";
|
|
cur.execute(rq_new_eaf_id)
|
|
res_new_eaf_id = cur.fetchone()
|
|
entite_af_id = res_new_eaf_id[0]
|
|
rq_insert_entite_af = "INSERT INTO animation_fonciere.entite_af (entite_af_id) VALUES (" + str(entite_af_id) + ")"
|
|
cur.execute(rq_insert_entite_af)
|
|
conn.commit()
|
|
else:
|
|
entite_af_id = 0
|
|
|
|
#On recherche si un rel_eaf_foncier_id existe pour la parcelle dans cette EAF
|
|
rq_check_rel_eaf_foncier_id = "SELECT rel_eaf_foncier_id FROM animation_fonciere.rel_eaf_foncier WHERE cad_site_id = "+str(cad_site_id)+" AND entite_af_id = "+str(entite_af_id)
|
|
cur.execute(rq_check_rel_eaf_foncier_id)
|
|
res_check_rel_eaf_foncier_id = cur.fetchone()
|
|
if cur.rowcount == 1 : #si un rel_eaf_foncier_id est trouvé
|
|
rel_eaf_foncier_id = res_check_rel_eaf_foncier_id[0]
|
|
elif cur.rowcount == 0 : #sinon il faut l'ajouter
|
|
rq_new_rel_eaf_foncier_id = "SELECT nextval('animation_fonciere.rel_eaf_foncier_id_seq'::regclass)";
|
|
cur.execute(rq_new_rel_eaf_foncier_id)
|
|
res_new_rel_eaf_foncier_id = cur.fetchone()
|
|
rel_eaf_foncier_id = res_new_rel_eaf_foncier_id[0]
|
|
rq_insert_rel_eaf_foncier = "INSERT INTO animation_fonciere.rel_eaf_foncier (rel_eaf_foncier_id, cad_site_id, entite_af_id) VALUES (" +str(rel_eaf_foncier_id)+","+str(cad_site_id)+","+str(entite_af_id)+")"
|
|
cur.execute(rq_insert_rel_eaf_foncier)
|
|
conn.commit()
|
|
else:
|
|
rel_eaf_foncier_id = 0
|
|
|
|
#On recherche si un rel_saf_foncier_id existe pour la parcelle dans cette EAF dans cette SAF
|
|
rq_check_rel_saf_foncier = "SELECT rel_saf_foncier_id FROM animation_fonciere.rel_saf_foncier WHERE rel_eaf_foncier_id = " + str(rel_eaf_foncier_id) + " AND session_af_id = " + str(session_af_id)
|
|
cur.execute(rq_check_rel_saf_foncier)
|
|
res_check_rel_saf_foncier = cur.fetchone()
|
|
if cur.rowcount == 1 : #si un rel_saf_foncier_id est trouvé
|
|
rel_saf_foncier_id = res_check_rel_saf_foncier[0]
|
|
elif cur.rowcount == 0 : #sinon il faut tout ajouter
|
|
rq_new_rel_saf_foncier_id = "SELECT nextval('animation_fonciere.rel_saf_foncier_id_seq'::regclass)";
|
|
cur.execute(rq_new_rel_saf_foncier_id)
|
|
res_new_rel_saf_foncier_id = cur.fetchone()
|
|
rel_saf_foncier_id = res_new_rel_saf_foncier_id[0]
|
|
rq_insert_rel_saf_foncier = "INSERT INTO animation_fonciere.rel_saf_foncier (rel_saf_foncier_id, rel_eaf_foncier_id, session_af_id, paf_id) VALUES (" + str(rel_saf_foncier_id) + "," + str(rel_eaf_foncier_id) + "," + str(session_af_id) + ", " + str(self.dlg.tbl_parcelle.item(r, 1).text()) + ")"
|
|
cur.execute(rq_insert_rel_saf_foncier)
|
|
conn.commit()
|
|
else:
|
|
rel_saf_foncier_id = 0
|
|
|
|
rq_lst_interloc_id = """
|
|
SELECT DISTINCT
|
|
interloc_id,
|
|
COALESCE(ccodro, 'N.P.') as ccodro,
|
|
COALESCE(ccodem, 'N.P.') as ccodem,
|
|
etat_interloc_id
|
|
FROM
|
|
animation_fonciere.rel_proprio_interloc
|
|
JOIN animation_fonciere.interlocuteurs USING (interloc_id)
|
|
JOIN cadastre.proprios_cen ON (proprios_cen.dnuper = interlocuteurs.dnuper OR proprios_cen.dnuper = rel_proprio_interloc.dnuper)
|
|
JOIN cadastre.r_prop_cptprop_cen ON (r_prop_cptprop_cen.dnuper = proprios_cen.dnuper)
|
|
JOIN cadastre.cptprop_cen USING (dnupro)
|
|
JOIN cadastre.cadastre_cen USING (dnupro)
|
|
JOIN foncier.cadastre_site USING (cad_cen_id)
|
|
WHERE cad_site_id = """ + str(cad_site_id)
|
|
cur.execute(rq_lst_interloc_id)
|
|
res_lst_interloc_id = cur.fetchall()
|
|
i=0
|
|
while i < len(res_lst_interloc_id):
|
|
if (res_lst_interloc_id[i][3] == 4):
|
|
type_interloc_id = 21
|
|
else:
|
|
type_interloc_id = 1
|
|
rq_check_rel_saf_foncier_interlocs = """
|
|
SELECT
|
|
rel_saf_foncier_interlocs_id
|
|
FROM
|
|
animation_fonciere.rel_saf_foncier_interlocs
|
|
WHERE
|
|
interloc_id = """ + str(res_lst_interloc_id[i][0]) + """ AND
|
|
rel_saf_foncier_id = """ + str(rel_saf_foncier_id) + """ AND type_interloc_id IN (1, 21)"""
|
|
cur.execute(rq_check_rel_saf_foncier_interlocs)
|
|
res_check_rel_saf_foncier_interlocs = cur.fetchone()
|
|
if cur.rowcount == 0 :
|
|
rq_insert_rel_saf_foncier_interlocs = "INSERT INTO animation_fonciere.rel_saf_foncier_interlocs (interloc_id, rel_saf_foncier_id, type_interloc_id, ccodro, ccodem) VALUES (" + str(res_lst_interloc_id[i][0]) + "," + str(rel_saf_foncier_id)+", " + str(type_interloc_id) + ", CASE WHEN '" + str(res_lst_interloc_id[i][1]) + "' = 'N.P.' THEN NULL ELSE '" + str(res_lst_interloc_id[i][1]) + "' END, CASE WHEN '" + str(res_lst_interloc_id[i][2]) + "' = 'N.P.' THEN NULL ELSE '" + str(res_lst_interloc_id[i][2]) + "' END)"
|
|
cur.execute(rq_insert_rel_saf_foncier_interlocs)
|
|
conn.commit()
|
|
i=i+1
|
|
|
|
rq_lst_interloc_id = """
|
|
SELECT DISTINCT
|
|
interloc_id,
|
|
type_interloc_id
|
|
FROM
|
|
animation_fonciere.interlocuteurs
|
|
JOIN animation_fonciere.rel_saf_foncier_interlocs USING (interloc_id)
|
|
JOIN animation_fonciere.rel_saf_foncier USING (rel_saf_foncier_id)
|
|
JOIN animation_fonciere.rel_eaf_foncier USING (rel_eaf_foncier_id)
|
|
JOIN animation_fonciere.d_type_interloc USING (type_interloc_id)
|
|
JOIN animation_fonciere.d_groupe_interloc USING (groupe_interloc_id)
|
|
WHERE type_interloc_id NOT IN (1, 2) AND all_eaf = 't' AND entite_af_id = """ + str(entite_af_id)
|
|
cur.execute(rq_lst_interloc_id)
|
|
res_lst_interloc_id = cur.fetchall()
|
|
i=0
|
|
while i < len(res_lst_interloc_id):
|
|
rq_check_rel_saf_foncier_interlocs = """
|
|
SELECT
|
|
rel_saf_foncier_interlocs_id
|
|
FROM
|
|
animation_fonciere.rel_saf_foncier_interlocs
|
|
WHERE
|
|
interloc_id = """ + str(res_lst_interloc_id[i][0]) + """ AND
|
|
rel_saf_foncier_id = """ + str(rel_saf_foncier_id) + """ AND type_interloc_id =""" + str(res_lst_interloc_id[i][1])
|
|
cur.execute(rq_check_rel_saf_foncier_interlocs)
|
|
res_check_rel_saf_foncier_interlocs = cur.fetchone()
|
|
if cur.rowcount == 0 :
|
|
rq_insert_rel_saf_foncier_interlocs = "INSERT INTO animation_fonciere.rel_saf_foncier_interlocs (interloc_id, rel_saf_foncier_id, type_interloc_id, ccodro, ccodem) VALUES (" + str(res_lst_interloc_id[i][0]) + "," + str(rel_saf_foncier_id)+", " + str(res_lst_interloc_id[i][1]) + ", NULL, NULL)"
|
|
cur.execute(rq_insert_rel_saf_foncier_interlocs)
|
|
conn.commit()
|
|
i=i+1
|
|
|
|
i=0
|
|
while i < len(res_lst_interloc_id):
|
|
# Après avoir éventuellement ajouter des parcelles, on vérifie ses relations au sein de l'EAF pour mettre à jour automatiquement les table interloc_referent et interloc_substitution
|
|
# L'interlocuteur est représenté par quelqu'un
|
|
rq_insert_interloc_substitution = """
|
|
WITH t1 AS (
|
|
SELECT DISTINCT
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_interlocs_id,
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_id,
|
|
CASE WHEN rel_saf_foncier_interlocs_id IN (SELECT rel_saf_foncier_interlocs_id_vp FROM animation_fonciere.interloc_substitution) THEN 'assoc' ELSE 'N.D.' END as eval
|
|
FROM
|
|
animation_fonciere.rel_saf_foncier_interlocs
|
|
JOIN animation_fonciere.d_type_interloc USING (type_interloc_id)
|
|
JOIN animation_fonciere.d_groupe_interloc USING (groupe_interloc_id)
|
|
JOIN animation_fonciere.rel_saf_foncier USING (rel_saf_foncier_id)
|
|
JOIN animation_fonciere.rel_eaf_foncier USING (rel_eaf_foncier_id)
|
|
WHERE
|
|
session_af_id = """ + str(session_af_id) + """ AND
|
|
entite_af_id = """ + str(entite_af_id) + """ AND
|
|
interloc_id = """ + str(res_lst_interloc_id[i][0]) + """ AND
|
|
can_be_under_subst = 't'
|
|
),
|
|
t_subst AS (
|
|
SELECT DISTINCT interloc_id
|
|
FROM
|
|
t1
|
|
JOIN animation_fonciere.rel_saf_foncier_interlocs USING (rel_saf_foncier_id)
|
|
JOIN animation_fonciere.interloc_substitution ON (rel_saf_foncier_interlocs_id_is = rel_saf_foncier_interlocs.rel_saf_foncier_interlocs_id)
|
|
WHERE t1.eval = 'assoc'
|
|
),
|
|
t2 AS (
|
|
SELECT DISTINCT
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_interlocs_id,
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_id,
|
|
rel_saf_foncier_interlocs.interloc_id
|
|
FROM
|
|
animation_fonciere.rel_saf_foncier_interlocs
|
|
JOIN animation_fonciere.rel_saf_foncier USING (rel_saf_foncier_id)
|
|
JOIN animation_fonciere.rel_eaf_foncier USING (rel_eaf_foncier_id)
|
|
JOIN t_subst USING (interloc_id)
|
|
)
|
|
INSERT INTO animation_fonciere.interloc_substitution (
|
|
SELECT
|
|
t1.rel_saf_foncier_interlocs_id as rel_saf_foncier_interlocs_id_vp,
|
|
t2.rel_saf_foncier_interlocs_id as rel_saf_foncier_interlocs_id_is
|
|
FROM
|
|
t1
|
|
JOIN t2 USING (rel_saf_foncier_id)
|
|
WHERE t1.eval = 'N.D.'
|
|
)"""
|
|
cur.execute(rq_insert_interloc_substitution)
|
|
conn.commit()
|
|
|
|
# L'interlocuteur est référencé par quelqu'un
|
|
rq_insert_interloc_referent = """
|
|
WITH t1 AS (
|
|
SELECT DISTINCT
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_interlocs_id,
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_id,
|
|
CASE WHEN rel_saf_foncier_interlocs_id IN (SELECT rel_saf_foncier_interlocs_id FROM animation_fonciere.interloc_referent) THEN 'assoc' ELSE 'N.D.' END as eval
|
|
FROM
|
|
animation_fonciere.rel_saf_foncier_interlocs
|
|
JOIN animation_fonciere.d_type_interloc USING (type_interloc_id)
|
|
JOIN animation_fonciere.d_groupe_interloc USING (groupe_interloc_id)
|
|
JOIN animation_fonciere.rel_saf_foncier USING (rel_saf_foncier_id)
|
|
JOIN animation_fonciere.rel_eaf_foncier USING (rel_eaf_foncier_id)
|
|
WHERE
|
|
session_af_id = """ + str(session_af_id) + """ AND
|
|
entite_af_id = """ + str(entite_af_id) + """ AND
|
|
interloc_id = """ + str(res_lst_interloc_id[i][0]) + """ AND
|
|
can_be_under_ref = 't'
|
|
),
|
|
t_ref AS (
|
|
SELECT DISTINCT interloc_id
|
|
FROM
|
|
t1
|
|
JOIN animation_fonciere.rel_saf_foncier_interlocs USING (rel_saf_foncier_id)
|
|
JOIN animation_fonciere.interloc_referent ON (rel_saf_foncier_interlocs_id_ref = rel_saf_foncier_interlocs.rel_saf_foncier_interlocs_id)
|
|
WHERE t1.eval = 'assoc'
|
|
),
|
|
t2 AS (
|
|
SELECT DISTINCT
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_interlocs_id,
|
|
rel_saf_foncier_interlocs.rel_saf_foncier_id,
|
|
rel_saf_foncier_interlocs.interloc_id
|
|
FROM
|
|
animation_fonciere.rel_saf_foncier_interlocs
|
|
JOIN animation_fonciere.rel_saf_foncier USING (rel_saf_foncier_id)
|
|
JOIN animation_fonciere.rel_eaf_foncier USING (rel_eaf_foncier_id)
|
|
JOIN t_ref USING (interloc_id)
|
|
)
|
|
INSERT INTO animation_fonciere.interloc_referent (
|
|
SELECT
|
|
t2.rel_saf_foncier_interlocs_id as rel_saf_foncier_interlocs_id_ref,
|
|
t1.rel_saf_foncier_interlocs_id
|
|
FROM
|
|
t1
|
|
JOIN t2 USING (rel_saf_foncier_id)
|
|
WHERE t1.eval = 'N.D.'
|
|
)"""
|
|
|
|
cur.execute(rq_insert_interloc_referent)
|
|
conn.commit()
|
|
|
|
i=i+1
|
|
|
|
if self.dlg.saf_maj.text() != 'N.P.':
|
|
rq_update_saf_geom = """
|
|
UPDATE animation_fonciere.session_af SET geom = (
|
|
SELECT
|
|
st_multi(st_union(t1.geom)) as geom
|
|
FROM
|
|
cadastre.lots_cen t2
|
|
JOIN cadastre.parcelles_cen t1 USING (par_id)
|
|
JOIN cadastre.cadastre_cen t3 USING (lot_id)
|
|
JOIN foncier.cadastre_site t4 USING (cad_cen_id)
|
|
JOIN animation_fonciere.rel_eaf_foncier USING (cad_site_id)
|
|
JOIN animation_fonciere.rel_saf_foncier USING (rel_eaf_foncier_id)
|
|
WHERE
|
|
rel_saf_foncier.session_af_id = """+str(session_af_id)+""" AND
|
|
t3.date_fin = 'N.D.' AND
|
|
t4.date_fin = 'N.D.')
|
|
WHERE session_af_id = """+str(session_af_id)
|
|
cur.execute(rq_update_saf_geom)
|
|
conn.commit()
|
|
|
|
rq_clean_eaf = "SELECT animation_fonciere.f_clean_eaf()";
|
|
test = 'NOT OK'
|
|
eaf=0
|
|
while test != 'OK':
|
|
cur.execute(rq_clean_eaf)
|
|
if (cur.rowcount == 0 or eaf > 25) :
|
|
test = 'OK'
|
|
eaf=eaf+1
|
|
|
|
curs = QCursor()
|
|
curs.setShape(Qt.ArrowCursor)
|
|
qgis.utils.iface.mainWindow().centralWidget().setCursor(curs)
|
|
self.canvas.refresh()
|
|
else:
|
|
QMessageBox.critical(None, "Attention", "Vous devez sélectionnez des objets depuis la vue 'parcelles_dispo_saf'")
|
|
|
|
def changeCellValue(self, ligne, col):
|
|
conn = psycopg2.connect("host=" + host + " port=" + port + " dbname=" + bdd + " user=" + user + " password=" + mdp)
|
|
cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
|
|
if self.dlg.tbl_parcelle.item(ligne, 1).text() == '1' and col != 0:
|
|
self.dlg.tbl_parcelle.setItem(ligne, 1, QTableWidgetItem('2'))
|
|
elif self.dlg.tbl_parcelle.item(ligne, 1).text() == '2' and col != 0:
|
|
self.dlg.tbl_parcelle.setItem(ligne, 1, QTableWidgetItem('3'))
|
|
elif self.dlg.tbl_parcelle.item(ligne, 1).text() == '3' and col != 0:
|
|
self.dlg.tbl_parcelle.setItem(ligne, 1, QTableWidgetItem('1'))
|
|
if col == 0:
|
|
feature_gid = self.dlg.tbl_parcelle.item(ligne, 0).text()
|
|
with conn:
|
|
with conn.cursor() as cur:
|
|
cur.execute("SELECT st_xmin(st_buffer(t1.geom,10)) as xmin, st_xmax(st_buffer(t1.geom,10)) as xmax, st_ymin(st_buffer(t1.geom,10)) as ymin, st_ymax(st_buffer(t1.geom,10)) as ymax FROM animation_fonciere.parcelles_dispo_saf t1 WHERE t1.par_id = '"+ self.dlg.tbl_parcelle.item(ligne, 0).text() + "'")
|
|
coord = cur.fetchone()
|
|
xmin = coord[0]
|
|
ymin = coord[2]
|
|
xmax = coord[1]
|
|
ymax = coord[3]
|
|
self.canvas.setExtent(QgsRectangle(xmin, ymin, xmax, ymax))
|
|
self.canvas.refresh()
|
|
self.dlg.tbl_parcelle.item(ligne, col).setSelected(False) |