def checkUserHasEigentuemerAccess(self):
if (self.getUsername() != None):
self.__openDB()
sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE access IN (0,1) AND lower(username) = :username"
queryEigentuemerAccess = QSqlQuery(self.db)
queryEigentuemerAccess.prepare(sql)
queryEigentuemerAccess.bindValue(":username", self.getUsername())
queryEigentuemerAccess.exec_()
if (queryEigentuemerAccess.lastError().number() == -1):
if (queryEigentuemerAccess.size() > 0):
return True
else:
return False
else:
return False
else:
return False
def __insertLogEntry(self,requestType,search,result):
self.__openDB()
sql = "INSERT INTO postnas_search_logging (datum,username,requestType,search,result) VALUES (:datum,:username,:requestType,:search,:result)"
query = QSqlQuery(self.db)
query.prepare(sql)
query.bindValue(":datum",datetime.datetime.now().isoformat())
query.bindValue(":username",self.username)
query.bindValue(":requestType",requestType)
query.bindValue(":search",search)
query.bindValue(":result",str(result).replace("u'","'").replace("\'","\"").replace("[","{").replace("]","}"))
query.exec_()
if(query.lastError().number() == -1):
return True
else:
return False
def setUsername(self, username):
self.username = username
if (self.checkUserExists()):
sql = "SELECT name,access FROM postnas_search_access_control WHERE lower(username) = :username"
self.__openDB()
queryLoadUserData = QSqlQuery(self.db)
queryLoadUserData.prepare(sql)
queryLoadUserData.bindValue(":username", self.getUsername())
queryLoadUserData.exec_()
if (queryLoadUserData.size() == 1):
while (queryLoadUserData.next()):
self.setName(
queryLoadUserData.value(
queryLoadUserData.record().indexOf("name")))
self.setAccess(
queryLoadUserData.value(
queryLoadUserData.record().indexOf("access")))
def eliminaFavorit(self, mapa, usuari=getpass.getuser().upper()):
if not self.__CONNECTA_BASE_DADES__(usuari):
QMessageBox.critical(
"Atenció",
"No s'ha pogut eliminar el mapa de favorits. Intenteu-ho més tard, si us plau"
)
return False
query = QSqlQuery(self.db)
query.prepare(
"delete from QV_MAPES_FAVORITS where iduser=:IDUSER and nom_mapa=:NOM_MAPA"
)
query.bindValue(':IDUSER', usuari)
query.bindValue(':NOM_MAPA', mapa)
if not query.exec():
QMessageBox.critical(
"Atenció",
"No s'ha pogut eliminar el mapa de favorits. Intenteu-ho més tard, si us plau"
)
self.__DESCONNECTA_BASE_DADES__(usuari)
return True
def afegeixFavorit(self, mapa, usuari=getpass.getuser().upper()):
if not self.__CONNECTA_BASE_DADES__(usuari):
QMessageBox.critical(
None, "Atenció",
"No s'ha pogut afegir el mapa a favorits. Intenteu-ho més tard, si us plau"
)
return False
query = QSqlQuery(self.db)
query.prepare(
"insert into QV_MAPES_FAVORITS (iduser, nom_mapa) values (:IDUSER,:NOM_MAPA)"
)
query.bindValue(':IDUSER', usuari)
query.bindValue(':NOM_MAPA', mapa)
if not query.exec():
QMessageBox.critical(
None, "Atenció",
"No s'ha pogut afegir el mapa a favorits. Intenteu-ho més tard, si us plau"
)
self.__DESCONNECTA_BASE_DADES__(usuari)
return True
def executeQuery(self, db, sql, feat=None):
try:
fparams = self.preparesql(feat)
if self.debug:
self.info.log("sql:", sql)
self.info.log("fparams:", fparams)
sqlexe = sql % fparams
if self.debug: self.info.log("SQL execute:", sqlexe)
# if db is not specified, or is invalid, the application's default database is used.
# If query is not an empty string, it will be executed
query = QSqlQuery(db)
# query = db.exec_(sql)#obsolete
query.prepare(sqlexe)
self.result = query.exec_()
if not self.result:
self.info.log(query.lastError().databaseText())
self.info.gtoWarning("Database Error:" +
query.lastError().databaseText())
except Exception as e:
self.info.err(e)
def updateUser(self, username_old):
if (self.getUsername() != None):
self.__openDB()
sql = "UPDATE postnas_search_access_control SET username = :username, name = :name, access = :access WHERE username = :username_old"
query = QSqlQuery(self.db)
query.prepare(sql)
query.bindValue(":username", self.getUsername().lower())
query.bindValue(":username_old", username_old)
query.bindValue(":name", self.name)
query.bindValue(":access", self.access)
query.exec_()
if (query.lastError().number() == -1):
return True
else:
QgsMessageLog.logMessage(
"Datenbankfehler beim Update: " + query.lastError().text(),
'PostNAS-Suche', Qgis.Critical)
return False
else:
return False
def getAccessModes(self):
sql = "SELECT id,bezeichnung FROM postnas_search_accessmode"
self.__openDB()
queryLoadAccessModes = QSqlQuery(self.db)
queryLoadAccessModes.prepare(sql)
queryLoadAccessModes.exec_()
results = []
if (queryLoadAccessModes.size() > 0):
while (queryLoadAccessModes.next()):
list = {
'id':
queryLoadAccessModes.value(
queryLoadAccessModes.record().indexOf("id")),
'bezeichnung':
queryLoadAccessModes.value(
queryLoadAccessModes.record().indexOf("bezeichnung"))
}
results.append(list)
return results
def loadUserAccessTable(self):
sql = "SELECT lower(username) as username,name,bezeichnung FROM postnas_search_access_control LEFT JOIN postnas_search_accessmode ON postnas_search_access_control.access = postnas_search_accessmode.id"
self.__openDB()
queryLoadAccessTable = QSqlQuery(self.db)
queryLoadAccessTable.prepare(sql)
queryLoadAccessTable.exec_()
results = []
if (queryLoadAccessTable.size() > 0):
while (queryLoadAccessTable.next()):
list = {
'username':
queryLoadAccessTable.value(
queryLoadAccessTable.record().indexOf("username")),
'name':
queryLoadAccessTable.value(
queryLoadAccessTable.record().indexOf("name")),
'access':
queryLoadAccessTable.value(
queryLoadAccessTable.record().indexOf("bezeichnung"))
}
results.append(list)
return results
class Db(object):
def __init__(self, iface, utils):
self.iface = iface
self.utils = utils
self.layers = self.get_layers(utils.cfg)
self.schema = self.get_schema(utils.cfg)
self.db = None
self.get_db(utils.cfg)
def get_schema(self, cfg):
return cfg.get('schema', 'public')
def set_schema(self, schema):
self.schema = schema
def get_layers(self, cfg):
return cfg.get('layers', [
"departamento", "circunscripcion", "seccion", "chacra", "quinta",
"fraccion", "manzana", "parcela", "subparcela"
])
def set_layers(self, layers):
self.layers = layers
def get_db(self, cfg):
if QSqlDatabase.contains("midb"):
if self.db:
if self.db.isOpen():
self.db.close()
QSqlDatabase.removeDatabase("midb")
self.db = QSqlDatabase.addDatabase("QPSQL", "midb")
self.db.setHostName(cfg.get('host', 'localhost'))
self.db.setPort(int(cfg.get('port', 5432)))
self.db.setDatabaseName(cfg.get('dbname', ''))
self.db.setUserName(cfg.get('user', ''))
self.db.setPassword(cfg.get('pswd', ''))
self.query = QSqlQuery(self.db)
def connect(self):
ok = self.db.open()
return self.db.isValid()
#return ok
def execute(self, sql, value, layer, idx):
self.query.prepare(sql)
self.query.addBindValue(value)
if self.query.exec_():
return self.proccess(layer, idx)
else:
sqlError = self.query.lastError()
self.iface.messageBar().pushCritical('Error',
sqlError.databaseText())
return None
def get_layer(self, idx):
return self.layers[idx]
def get_by_nomencla(self, nomencla, idx):
if self.connect():
pda = 'null::int'
pdo = 'null::varchar'
if idx >= 7:
pda = 'pda::int'
if idx == 0:
pdo = 'nam'
layer = self.get_layer(idx)
sql = "SELECT cca, {} as pda, ST_Astext(geom), {} as nam FROM {}.{} WHERE cca = ?".format(
pda, pdo, self.schema, layer)
return self.execute(sql, nomencla, layer, idx)
else:
self.iface.messageBar().pushCritical(
'Error', 'No se pudo conectar a la base de datos')
def get_by_partida(self, pdopda):
if pdopda == '0' * 9:
return []
if self.connect():
layer = self.get_layer(7)
sql = "SELECT cca, pda, ST_Astext(geom) FROM {}.{} WHERE pda = ?".format(
self.schema, layer)
return self.execute(sql, pdopda, layer, 7)
else:
self.iface.messageBar().pushCritical(
'Error', 'No se pudo conectar a la base de datos')
def proccess(self, layer, idx):
result = []
while self.query.next():
cca = self.query.value(0)
partida = self.query.value(1)
if partida:
try:
partida = int(partida[3:])
except ValueError:
partida = None
if idx == 0:
nomenclatura = self.query.value(3)
else:
nomenclatura = self.utils.format_nomenclatura(cca)
row = {
"id": "{}.{}".format(layer, cca),
"partido": int(cca[:3]),
"partida": partida,
"nomenclatura": nomenclatura,
"codigo": cca,
"wkt": self.query.value(2),
"layer": layer.capitalize()
}
result.append(row)
return result
class QtSqlDBCursor(object):
def __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
def close(self):
self.qry.finish()
def execute(self, operation, parameters=[]):
if len(parameters) == 0:
if not self.qry.exec_(operation):
raise ExecError(self.qry.lastError().databaseText())
else:
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for i in range(len(parameters)):
self.qry.bindValue(i, parameters[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
self.rowcount = self.qry.size()
self.description = []
for c in range(self.qry.record().count()):
f = self.qry.record().field(c)
if f.type() == QVariant.Date:
t = Date
elif f.type() == QVariant.Time:
t = Time
elif f.type() == QVariant.DateTime:
t = Timestamp
elif f.type() == QVariant.Double:
t = float
elif f.type() == QVariant.Int:
t = int
elif f.type() == QVariant.String:
t = str
elif f.type() == QVariant.ByteArray:
t = str
else:
continue
self.description.append([
f.name(), # name
t, # type_code
f.length(), # display_size
f.length(), # internal_size
f.precision(), # precision
None, # scale
f.requiredStatus() != QSqlField.Required # null_ok
])
def executemany(self, operation, seq_of_parameters):
if len(seq_of_parameters) == 0:
return
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for r in seq_of_parameters:
for i in range(len(r)):
self.qry.bindValue(i, r[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
def scroll(self, row):
return self.qry.seek(row)
def fetchone(self):
if not self.qry.next():
return None
row = []
for i in range(len(self.description)):
value = self.qry.value(i)
if (isinstance(value, QDate) or isinstance(value, QTime)
or isinstance(value, QDateTime)):
value = value.toString()
elif isinstance(value, QByteArray):
value = u"GEOMETRY"
# value = value.toHex()
row.append(value)
return row
def fetchmany(self, size=10):
rows = []
while len(rows) < size:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def fetchall(self):
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def setinputsize(self, sizes):
raise ExecError("nyi")
def setoutputsize(self, size, column=None):
raise ExecError("nyi")
class QvApp(Singleton):
def __init__(self):
if hasattr(self, 'gh'): # Se inicializa una vez
return
self.gh = None
self.ruta, self.rutaBase = self.calcRuta() # Path de la aplicación
self.cfg = self.readCfg() # Config de instalación
val = self.paramCfg("Debug", "False") # Errores no controlados
if val != "True":
sys.excepthook = _fatalError
self.entorn = self.calcEntorn() # 'DSV' o 'PRO'
self.usuari = getpass.getuser().upper() # Id de usuario
self.sessio = str(uuid.uuid1()) # Id único de sesión
self.intranet = self.calcIntranet() # True si en la intranet
self.dbQvista = _DB_QVISTA[self.entorn] # Conexión Oracle entorno
self.dbGeo = QvSqlite().dbGeoConnexio() # Conexión Geocod SQlite
self.proxy = self.setProxy() # Establecer proxy
val = self.paramCfg('Log', 'False') # Activación log
if val == 'True':
self.log = True
else:
self.log = False
val = self.paramCfg('Github', 'False') # Establecer rama Github
if val == 'False':
self.github = None
elif val == 'True':
self.github = 'master'
else:
self.github = val
if self.github is None:
self.gh = None
else:
val = self.paramCfg('Id', '')
self.gh = QvGithub(self.data(), self.github, val)
val = self.paramCfg('Stdout', 'False') # Activación fichero salida
if val == 'True':
try:
# print(os.getcwd())
sys.stdout = open('../Salida.txt', 'w')
except Exception:
print('Error al redirigir stdout')
self.dbLog = None
self.queryLog = None
self.familyLog = None
self.nameLog = None
self.queryGeo = None
self.appQgis = None
self.idioma = None
self.qtTranslator = None
self.qgisTranslator = None
self.locale = QLocale("ca-ES")
QgsPythonRunner.setInstance(QvPythonRunner()) # Ejecuciones Python
def data(self):
txt = ''
txt += 'Nom: ' + self.paramCfg('Nom', '???') + '\n'
txt += 'Entorn: ' + self.entorn + '\n'
txt += 'Branca: ' + self.github + '\n'
txt += 'Intranet: ' + str(self.intranet) + '\n'
txt += 'Usuari: ' + self.usuari + '\n'
txt += 'Sessió: ' + self.sessio + '\n'
txt += '___' + '\n'
return txt
def calcRuta(self):
try:
q1 = 'qVista/'
q2 = 'Codi/'
f = sys.argv[0]
f = f.replace('\\', '/')
fUp = f.upper()
q = q1 + q2
qUp = q.upper()
n = fUp.find(qUp)
if n >= 0:
ruta = f[:n+len(q)]
rutaBase = f[:n+len(q1)]
return ruta, rutaBase
else:
return '', ''
except Exception as err:
self.bugException(err)
return '', ''
def readCfg(self):
try:
nom = 'install.cfg'
fich = self.rutaBase + nom
if not os.path.isfile(fich):
fich = self.ruta + nom
fp = open(fich, 'r', encoding='utf-8-sig')
cfg = json.load(fp)
fp.close()
return cfg
except Exception as err:
self.bugException(err)
return dict()
def paramCfg(self, name, default):
if hasattr(self, 'cfg') and self.cfg is not None:
return self.cfg.get(name, default)
else:
return default
def setProxy(self):
try:
val = self.paramCfg('Proxy', 'False')
if self.intranet and val == 'True':
proxy = QNetworkProxy()
proxy.setType(QNetworkProxy.DefaultProxy)
proxy.setHostName = _PROXY['HostName']
proxy.setPort = _PROXY['Port']
proxy.setApplicationProxy(proxy)
return proxy
else:
return None
except Exception as err:
self.bugException(err)
return None
def calcEntorn(self):
val = self.paramCfg('Producció', 'False')
if val == 'True':
return 'PRO'
else:
return 'DSV'
def calcIntranet(self):
return os.path.isdir(_PATH_PRO)
def carregaIdioma(self, app, idioma='ca'):
if app is None:
return
self.appQgis = app
self.idioma = self.paramCfg('Idioma', idioma)
self.qtTranslator = QTranslator()
self.qgisTranslator = QTranslator()
path = QLibraryInfo.location(QLibraryInfo.TranslationsPath)
self.qtTranslator.load("qt_" + idioma, path)
app.installTranslator(self.qtTranslator)
path = app.i18nPath()
path = path.replace('/./', '/')
self.qgisTranslator.load("qgis_" + idioma, path)
app.installTranslator(self.qgisTranslator)
self.locale = QLocale(self.idioma + "-ES")
def llegirFitxerText(self, nomFich):
try:
txt = ''
file = Path(nomFich)
if file.is_file():
with file.open():
txt = file.read_text()
return txt
except Exception:
return ''
def carregaAjuda(self, objecte):
try:
nom = type(objecte).__name__
if self.idioma is not None and self.idioma != '':
nomFich = nom + '_' + self.idioma + '.html'
else:
nomFich = nom + '.html'
txt = self.llegirFitxerText(_PATH_HELP + nomFich)
if txt == '':
txt = self.llegirFitxerText(_PATH_HELP + 'WorkInProgress.html')
return txt
except Exception as e:
print(str(e))
return ''
def zoomFactor(self):
zoomFactor = QApplication.desktop().screen().logicalDpiX() / QvFuncions.DPI
return zoomFactor
def nomUsuari(self):
return QvFuncions.getUserName(self.usuari)
def versioQgis(self):
return Qgis.QGIS_VERSION
# Metodos db QVISTA
def dbLogConnexio(self):
if not self.intranet:
return
try:
if self.dbLog is None:
db = QSqlDatabase.addDatabase(self.dbQvista['Database'], 'LOG')
if db.isValid():
db.setHostName(self.dbQvista['HostName'])
db.setPort(self.dbQvista['Port'])
db.setDatabaseName(self.dbQvista['DatabaseName'])
db.setUserName(self.dbQvista['UserName'])
db.setPassword(self.dbQvista['Password'])
if db.open():
self.dbLog = db
except Exception:
self.dbLog = None
def dbLogDesconnexio(self):
if not self.intranet:
return
try:
if self.dbLog is not None:
conName = self.dbLog.connectionName()
self.dbLog.close()
self.dbLog = None
QSqlDatabase.removeDatabase(conName)
except Exception:
self.dbLog = None
# Metodos de LOG en Oracle
def logInici(self, family='QVISTA', logname='DESKTOP', params=None):
if not self.log:
return False
self.dbLogConnexio()
if self.dbLog is None:
return False
self.familyLog = family.upper()
self.nameLog = logname.upper()
self.queryLog = QSqlQuery(self.dbLog)
return self.logRegistre('LOG_INICI', params)
def logRegistre(self, topic, params=None):
if not self.log or self.dbLog is None or self.queryLog is None:
return False
try:
self.queryLog.prepare("CALL QV_LOG_WRITE(:IDUSER, :IDSESSION, :FAMILY, :LOGNAME, :TOPIC, :PARAMS)")
self.queryLog.bindValue(':IDUSER', self.usuari)
self.queryLog.bindValue(':IDSESSION', self.sessio)
self.queryLog.bindValue(':FAMILY', self.familyLog)
self.queryLog.bindValue(':LOGNAME', self.nameLog)
self.queryLog.bindValue(':TOPIC', topic)
self.queryLog.bindValue(':PARAMS', params)
ok = self.queryLog.exec_()
return ok
except Exception:
return False
def logFi(self, params=None):
ok = self.logRegistre('LOG_FI', params)
self.dbLogDesconnexio()
return ok
def logError(self):
if not self.log or self.dbLog is None or self.queryLog is None:
return 'Log no actiu'
try:
return self.queryLog.lastError().text()
except Exception:
return None
# Metodos de geocodificación
def geocod(self, tipusVia, variant, codi, numIni, lletraIni='', numFi='', lletraFi=''):
self.dbLogConnexio()
if self.dbLog is None:
return None, None
if self.queryGeo is None:
self.queryGeo = QSqlQuery(self.dbLog)
try:
self.queryGeo.prepare("CALL QV_GEOCOD(:TIPUSVIA, :VARIANTE, :CODIGO, " +
":NUMINI, :LETRAINI, :NUMFIN, :LETRAFIN, :X, :Y)")
self.queryGeo.bindValue(':TIPUSVIA', tipusVia)
self.queryGeo.bindValue(':VARIANTE', variant)
self.queryGeo.bindValue(':CODIGO', codi)
self.queryGeo.bindValue(':NUMINI', numIni)
self.queryGeo.bindValue(':LETRAINI', lletraIni)
self.queryGeo.bindValue(':NUMFIN', numFi)
self.queryGeo.bindValue(':LETRAFIN', lletraFi)
self.queryGeo.bindValue(':X', 0.0, QSql.Out)
self.queryGeo.bindValue(':Y', 0.0, QSql.Out)
ok = self.queryGeo.exec_()
if ok:
x = self.queryGeo.boundValue(':X')
if not isinstance(x, float):
x = None
y = self.queryGeo.boundValue(':Y')
if not isinstance(y, float):
y = None
return x, y
except Exception:
return None, None
# Métodos de reporte de bugs con Github
def bugUser(self, tit, desc):
if self.gh is not None:
return self.gh.postUser(tit, desc)
else:
return False
def bugException(self, err):
ok = False
if self.gh is not None:
ok = self.gh.reportBug()
val = self.paramCfg('Debug', 'False')
if val == 'True':
raise err
return ok
def bugFatalError(self, type, value, tb):
if self.gh is not None:
return self.gh.reportBug(type, value, tb)
else:
return False
class QtSqlDBCursor(object):
def __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
def close(self):
self.qry.finish()
def execute(self, operation, parameters=[]):
if len(parameters) == 0:
if not self.qry.exec_(operation):
raise ExecError(self.qry.lastError().databaseText())
else:
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for i in range(len(parameters)):
self.qry.bindValue(i, parameters[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
self.rowcount = self.qry.size()
self.description = []
for c in range(self.qry.record().count()):
f = self.qry.record().field(c)
if f.type() == QVariant.Date:
t = Date
elif f.type() == QVariant.Time:
t = Time
elif f.type() == QVariant.DateTime:
t = Timestamp
elif f.type() == QVariant.Double:
t = float
elif f.type() == QVariant.Int:
t = int
elif f.type() == QVariant.String:
t = str
elif f.type() == QVariant.ByteArray:
t = str
else:
continue
self.description.append([
f.name(), # name
t, # type_code
f.length(), # display_size
f.length(), # internal_size
f.precision(), # precision
None, # scale
f.requiredStatus() != QSqlField.Required # null_ok
])
def executemany(self, operation, seq_of_parameters):
if len(seq_of_parameters) == 0:
return
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for r in seq_of_parameters:
for i in range(len(r)):
self.qry.bindValue(i, r[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
def scroll(self, row):
return self.qry.seek(row)
def fetchone(self):
if not next(self.qry):
return None
row = []
for i in range(len(self.description)):
value = self.qry.value(i)
if (isinstance(value, QDate) or
isinstance(value, QTime) or
isinstance(value, QDateTime)):
value = value.toString()
elif isinstance(value, QByteArray):
value = u"GEOMETRY"
# value = value.toHex()
row.append(value)
return row
def fetchmany(self, size=10):
rows = []
while len(rows) < size:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def fetchall(self):
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def setinputsize(self, sizes):
raise ExecError("nyi")
def setoutputsize(self, size, column=None):
raise ExecError("nyi")
