def _query(self, querystring, **mappings):
querystring = querystring.replace(r"\r\n", " ")
query = QSqlQuery(self.db)
query.prepare(querystring)
for key, value in mappings.items():
bindvalue = ":{}".format(key)
if re.search(r"{}\b".format(bindvalue), querystring):
query.bindValue(bindvalue, value)
return query
def getFavorits(self, usuari=getpass.getuser().upper()):
'''Comentari explicant'''
if not self.__CONNECTA_BASE_DADES__(usuari):
return []
query = QSqlQuery(self.db)
query.prepare(
"select nom_mapa from QV_MAPES_FAVORITS where iduser=:IDUSER")
query.bindValue(':IDUSER', usuari)
query.exec()
res = []
while query.next():
res.append(query.value(0))
#Consulta
self.__DESCONNECTA_BASE_DADES__(usuari, False)
return res
def checkUserExists(self):
sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE lower(username) = :username"
self.__openDB()
queryCheckUserExists = QSqlQuery(self.db)
queryCheckUserExists.prepare(sql)
queryCheckUserExists.bindValue(":username", self.getUsername())
queryCheckUserExists.exec_()
if (queryCheckUserExists.lastError().number() == -1):
if (queryCheckUserExists.size() > 0):
return True
else:
return False
else:
return False
def deleteUser(self):
sql = "DELETE FROM postnas_search_access_control WHERE lower(username) = :username"
self.__openDB()
queryDeleteUser = QSqlQuery(self.db)
queryDeleteUser.prepare(sql)
queryDeleteUser.bindValue(":username", self.getUsername())
queryDeleteUser.exec_()
if (queryDeleteUser.lastError().number() == -1):
return True
else:
QgsMessageLog.logMessage(
"Datenbankfehler beim Löschen: " +
queryDeleteUser.lastError().text(), 'PostNAS-Suche',
Qgis.Critical)
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 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 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 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 insertUser(self):
if (self.getUsername() != None):
self.__openDB()
sql = "INSERT INTO postnas_search_access_control (username,name,access) VALUES (:username,:name,:access)"
query = QSqlQuery(self.db)
query.prepare(sql)
query.bindValue(":username", self.getUsername().lower())
query.bindValue(":name", self.name)
query.bindValue(":access", self.access)
query.exec_()
if (query.lastError().number() == -1):
return True
else:
return False
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 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 QvLog(Singleton):
def __init__(self, family='QVISTA', logname='DESKTOP', dbLog=_DB_QVISTA):
if hasattr(self, 'db'): # Solo se inicializa una vez
return
self.family = family.upper()
self.logname = logname.upper()
self.usuari = getpass.getuser().upper()
self.db = self.connexio(dbLog)
self.sessio = str(uuid.uuid1())
self.query = QSqlQuery()
def connexio(self, dbLog):
db = QSqlDatabase.addDatabase(dbLog['Database'])
if db.isValid():
db.setHostName(dbLog['HostName'])
db.setPort(dbLog['Port'])
db.setDatabaseName(dbLog['DatabaseName'])
db.setUserName(dbLog['UserName'])
db.setPassword(dbLog['Password'])
if db.open():
return db
return None
def desconnexio(self):
if self.db is None:
return
conName = self.db.connectionName()
self.db.close()
self.db = None
QSqlDatabase.removeDatabase(conName)
def registre(self, topic, params=None):
if self.db is None or self.query is None:
return False
self.query.prepare(
"CALL QV_LOG_WRITE(:IDUSER, :IDSESSION, :FAMILY, :LOGNAME, :TOPIC, :PARAMS)"
)
self.query.bindValue(':IDUSER', self.usuari)
self.query.bindValue(':IDSESSION', self.sessio)
self.query.bindValue(':FAMILY', self.family)
self.query.bindValue(':LOGNAME', self.logname)
self.query.bindValue(':TOPIC', topic)
self.query.bindValue(':PARAMS', params)
ok = self.query.exec_()
return ok
def inici(self):
ok = self.registre('LOG_INICI')
return ok
def fi(self):
ok = self.registre('LOG_FI')
self.desconnexio()
return ok
def error(self):
if self.db is None or self.query is None:
return None
else:
return self.query.lastError().text()
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")
