def get_bcgw_column_key(db_table,user_name,user_pass):
# estimate a unique id column for an oracle table if OBJECTID does not exist
owner,table = db_table.split('.')
driver ="QOCISPATIAL"
conn_name = "bcgw_conn"
if not QSqlDatabase.contains(conn_name):
db = QSqlDatabase.addDatabase(driver,conn_name)
else:
db = QSqlDatabase.database(conn_name)
db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov')
db.setUserName(user_name)
db.setPassword(user_pass)
db.open()
if not db.open():
print ("Failed Connection from find_bcgw_the_geom")
q = QSqlQuery(db)
sql = f"SELECT cols.column_name \
FROM all_tab_cols cols where cols.table_name = '{table}' and cols.COLUMN_NAME like \'OBJECTID\'"
q.exec(sql)
if q.first():
key_c = q.value(0)
else:
sql = f"SELECT COLUMN_NAME FROM all_tab_cols where table_name = '{table}' \
order by COLUMN_ID FETCH FIRST 1 ROWS ONLY"
q.exec(sql)
if q.first():
key_c = q.value(0)
db.close()
return key_c
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 get_bcgw_geomcolumn(db_table,user_name,user_pass):
# get the name of the geometry column for oracle table
owner,table = db_table.split('.')
driver ="QOCISPATIAL"
conn_name = "bcgw_conn"
if not QSqlDatabase.contains(conn_name):
db = QSqlDatabase.addDatabase(driver,conn_name)
else:
db = QSqlDatabase.database(conn_name)
db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov')
db.setUserName(user_name)
db.setPassword(user_pass)
db.open()
if not db.open():
print ("Failed Connection from find_bcgw_the_geom")
q = QSqlQuery(db)
query ="SELECT COLUMN_NAME from all_tab_columns where OWNER = '{}' AND TABLE_NAME = '{}' AND DATA_TYPE = 'SDO_GEOMETRY'".format(owner,table)
q.exec(query)
q.first()
geom_c = q.value(0)
db.close()
return geom_c
def get_bcgw_table_geomtype(db_table,geom_column_name,user_name,user_pass):
# get geometry type from oracle table - oracle stores multiple types so
# this returns the maximum type ie multiline, multipolygon, multipoint if
# present in geometry
owner,table = db_table.split('.')
driver ="QOCISPATIAL"
conn_name = "bcgw_conn"
if not QSqlDatabase.contains(conn_name):
db = QSqlDatabase.addDatabase(driver,conn_name)
else:
db = QSqlDatabase.database(conn_name)
db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov')
db.setUserName(user_name)
db.setPassword(user_pass)
db.open()
if not db.open():
print ("Failed Connection from find_bcgw_the_geom")
q = QSqlQuery(db)
query = f"SELECT MAX(t.{geom_column_name}.GET_GTYPE()) AS geometry_type from {owner}.{table} t"
q.exec(query)
q.first()
type_num = q.value(0)
if type_num == 1:
geom_t = 'Point'
elif type_num == 2:
geom_t = 'LineString'
elif type_num == 3:
geom_t = 'Polygon'
elif type_num == 7:
geom_t = 'MultiPolygon'
elif type_num ==5:
geom_t = 'MulitPoint'
elif type_num ==6:
geom_t = 'MultiLineString'
else:
db.close()
raise TypeError
db.close()
return geom_t
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
