def get_detail_category_chart_data(self, count_id, status, section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select cat.code, cat.name, count(det.id_category) from "
"comptages.count_detail as det "
"join comptages.category as cat "
"on det.id_category = cat.id "
"join comptages.lane as lan "
"on det.id_lane = lan.id "
"where det.id_count = {} "
"and det.import_status = {} "
"and lan.id_section = '{}' "
"group by det.id_category, cat.code, cat.name "
"order by cat.code;".format(count_id, status, section_id))
query.exec_(query_str)
labels = []
values = []
while query.next():
labels.append("{} ({})".format(str(query.value(1)),
str(query.value(0))))
values.append(query.value(2))
return labels, values
def getStructureDict(self):
'''
Gets database structure according to the edgv version
'''
self.checkAndOpenDb()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem getting database structure: ") +
query.lastError().text())
while query.next():
className = str(query.value(0))
classSql = str(query.value(1))
if className.split('_')[0] == 'complexos' or className.split(
'_')[-1] in ['p', 'l', 'a']:
if className not in list(classDict.keys()):
classDict[className] = dict()
classSql = classSql.split(className)[1]
sqlList = classSql.replace('(', '').replace(')', '').replace(
'\"', '').replace('\'', '').split(',')
for s in sqlList:
fieldName = str(s.strip().split(' ')[0])
classDict[className][fieldName] = fieldName
if 'GEOMETRY' in list(classDict[className].keys()):
classDict[className]['GEOMETRY'] = 'geom'
if 'geometry' in list(classDict[className].keys()):
classDict[className]['geometry'] = 'geom'
if 'OGC_FID' in list(classDict[className].keys()):
classDict[className]['OGC_FID'] = 'id'
return classDict
def get_count_dates(self):
query = QSqlQuery(self.db)
query_str = ("select start_process_date, end_process_date "
"from comptages.count where id = {};".format(
self.count_id))
query.exec_(query_str)
result = []
while query.next():
start_date = query.value(0)
end_date = query.value(1)
i = 0
while True:
date = start_date.addDays(i)
if date <= end_date:
result.append(
(int(date.toString('yyyy')), int(date.toString('MM')),
int(date.toString('dd'))))
i += 1
else:
break
return result
def get_aggregate_category_chart_data(self, count_id, status, section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select sum(cls.value), cat.code, cat.name from "
"comptages.count_aggregate as agg "
"join comptages.count_aggregate_value_cls as cls "
"on agg.id = cls.id_count_aggregate "
"join comptages.category as cat "
"on cls.id_category = cat.id "
"join comptages.lane as lan "
"on agg.id_lane = lan.id "
"where agg.id_count = {} and agg.type = 'CLS' "
"and agg.import_status = {} "
"and lan.id_section = '{}' "
"group by cat.code, cat.name "
"order by cat.code;".format(count_id, status, section_id))
query.exec_(query_str)
labels = []
values = []
while query.next():
labels.append("{} ({})".format(str(query.value(2)),
str(query.value(1))))
values.append(query.value(0))
return labels, values
def test_data_detail(self):
self.db.open()
query = QSqlQuery(self.db)
query.exec_("SELECT id FROM comptages.installation \
WHERE name = '64080011';")
query.next()
installation_id = query.value(0)
query.exec_("SELECT id FROM comptages.model \
WHERE name = 'M660_LT';")
query.next()
model_id = query.value(0)
query.exec_("select id from comptages.sensor_type \
where name = 'Tube'")
query.next()
sensor_type_id = query.value(0)
query.exec_("select id from comptages.class \
where name = 'SWISS10'")
query.next()
class_id = query.value(0)
query_str = (
"INSERT INTO comptages.count(id, "
"start_process_date, end_process_date, start_service_date, "
"end_service_date, id_sensor_type, id_model, id_class, "
"id_installation) "
"VALUES (1, '2018-12-18', '2018-12-20', '2018-12-18', "
"'2018-12-20', {}, {}, {}, {});".format(
sensor_type_id, model_id, class_id, installation_id))
query.exec_(query_str)
task = self.comptages.import_file(
os.path.join(
self.test_data_path,
'data_loader_simple_detail.V01'),
1)
task.waitForFinished()
# Let the time to the db to finish the writing
time.sleep(1)
data_loader = DataLoader(
1, '64080011', self.layers.IMPORT_STATUS_QUARANTINE)
count_data = data_loader.load()
self.assertEqual(
[1, 0, 3, 0, 1, 3, 0, 1, 0, 0, 0, 0, 2],
count_data.day_data[0].hour_data[15].direction_data[0].speed_data)
self.assertEqual(
[1, 1, 2, 1, 1, 1, 1, 1, 1, 1],
count_data.day_data[0].hour_data[15].direction_data[0].category_data)
self.assertEqual(20, count_data.day_data[0].total())
self.assertEqual(16, count_data.day_data[0].light_vehicles())
self.assertEqual(4, count_data.day_data[0].heavy_vehicles())
self.assertEqual(20.0, count_data.day_data[0].percent_heavy_vehicles())
def get_detail_time_chart_data_day_by_direction(self, count_id, day,
status, direction,
section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select date_part('hour', timestamp), "
"date_part('hour', timestamp) + 1, "
"count(date_part('hour', timestamp)) "
"from comptages.count_detail as cou "
"join comptages.lane as lan on "
"cou.id_lane = lan.id "
"where id_count = {} and "
"date_trunc('day', timestamp) = '{}' "
"and import_status = {} "
"and lan.direction = {} "
"and lan.id_section = '{}' "
"group by date_part('hour', timestamp);".format(
count_id, day, status, direction, section_id))
query.exec_(query_str)
x = [
"00h-01h", "01h-02h", "02h-03h", "03h-04h", "04h-05h", "05h-06h",
"06h-07h", "07h-08h", "08h-09h", "09h-10h", "10h-11h", "11h-12h",
"12h-13h", "13h-14h", "14h-15h", "15h-16h", "16h-17h", "17h-18h",
"18h-19h", "19h-20h", "20h-21h", "21h-22h", "22h-23h", "23h-00h"
]
y = [None] * 24
while query.next():
y[int(query.value(0))] = query.value(2)
return x, y
def get_aggregate_speed_chart_data(self, count_id, status, section_id):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = ("select sum(spd.value), spd.low, spd.high from "
"comptages.count_aggregate as agg "
"join comptages.count_aggregate_value_spd as spd "
"on agg.id = spd.id_count_aggregate "
"join comptages.lane as lan "
"on agg.id_lane = lan.id "
"where agg.id_count = {} and agg.type = 'SPD' "
"and agg.import_status = {} "
"and lan.id_section = '{}'"
"group by spd.low, spd.high "
"order by spd.low;".format(count_id, status, section_id))
query.exec_(query_str)
x = []
y = []
while query.next():
x.append("{}-{} km/h".format(str(query.value(1)),
str(query.value(2))))
y.append(query.value(0))
return x, y
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 getAllEdgvDomainsFromTableName(self, table):
"""
EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB.
It checks for all attributes found.
:param table: (str) layer to be checked for its EDGV mapping.
:param table: (QgsVectorLayer) overloaded method - layer to be checked for its EDGV mapping.
:param field: (str) field to be checked.
:return: (dict) value map for all attributes that have one.
"""
ret = defaultdict(dict)
currentLayer = table if isinstance(table, QgsVectorLayer) else self.layerByName(table)
if currentLayer.isValid():
try:
uri = currentLayer.dataProvider().uri()
if uri.host() == '':
db = QSqlDatabase('QSQLITE')
db.setDatabaseName(
uri.uri().split("|")[0].strip() if uri.uri().split("|")[0].strip().endswith(".gpkg") \
else uri.database()
)
sql = 'select code, code_name from dominios_{field} order by code'
else:
db = QSqlDatabase('QPSQL')
db.setHostName(uri.host())
db.setPort(int(uri.port()))
db.setDatabaseName(uri.database())
db.setUserName(uri.username())
db.setPassword(uri.password())
sql = 'select code, code_name from dominios.{field} order by code'
if not db.open():
db.close()
return ret
for field in currentLayer.fields():
fieldName = field.name()
if fieldName in self.specialEdgvAttributes():
# EDGV "special" attributes that are have different domains depending on
# which class it belongs to
if self.edgvVersion(db) in ("2.1.3 Pro", "3.0 Pro"):
cat = table if isinstance(table, str) else table.name()
# Pro versions now follow the logic "{attribute}_{CLASS_NAME}"
cat = cat.rsplit("_", 1)[0].split("_", 1)[-1]
else:
cat = (table if isinstance(table, str) else table.name()).split("_")[0]
fieldN = "{attribute}_{cat}".format(attribute=fieldName, cat=cat)
query = QSqlQuery(sql.format(field=fieldN), db)
else:
query = QSqlQuery(sql.format(field=fieldName), db)
if not query.isActive():
continue
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[fieldName][code_name] = code
db.close()
except:
pass
return ret
def getLayersWithElements(self, layerList):
self.checkAndOpenDb()
lyrWithElemList = []
for lyr in layerList:
# schema=self.getTableSchemaFromDb(lyr)
sql = self.gen.getElementCountFromLayer(lyr)
query = QSqlQuery(sql, self.db)
query.next()
if query.value(0) is not None and query.value(0) > 1:
lyrWithElemList.append(lyr)
return lyrWithElemList
def populate_lane_dict(self):
query = QSqlQuery(self.db)
query_str = (
"select l.number, l.id from comptages.count as c "
"join comptages.installation as i on i.id = c.id_installation "
"join comptages.lane as l on l.id_installation = i.id "
"where c.id = {};".format(self.count_id))
query.exec_(query_str)
while query.next():
self.lanes[int(query.value(0))] = int(query.value(1))
def getEdgvDomainsFromTableName(self, table, field=None):
"""
EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB.
:param table: (str) layer to be checked for its EDGV mapping.
:param table: (QgsVectorLayer) overloaded method - layer to be checked for its EDGV mapping.
:param field: (str) field to be checked.
:return: (dict) value map.
"""
ret = dict()
currentLayer = table if isinstance(
table, QgsVectorLayer) else self.layerByName(table)
if currentLayer.isValid():
try:
uri = currentLayer.dataProvider().uri()
field = field or self.currentField()
if field in self.specialEdgvAttributes():
# EDGV "special" attributes that are have different domains depending on
# which class it belongs to
category = self.currentLayerName().split("_")[0]
field = "{attribute}_{cat}".format(attribute=field,
cat=category)
if uri.host() == '':
db = QSqlDatabase('QSQLITE')
db.setDatabaseName(
uri.uri().split("|")[0].strip() if uri.uri().split("|")[0].strip().endswith(".gpkg") \
else uri.database()
)
sql = 'select code, code_name from dominios_{field} order by code'.format(
field=field)
else:
db = QSqlDatabase('QPSQL')
db.setHostName(uri.host())
db.setPort(int(uri.port()))
db.setDatabaseName(uri.database())
db.setUserName(uri.username())
db.setPassword(uri.password())
sql = 'select code, code_name from dominios.{field} order by code'.format(
field=field)
if not db.open():
db.close()
return ret
query = QSqlQuery(sql, db)
if not query.isActive():
return ret
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
db.close()
except:
pass
return ret
def makeValueRelationDict(self, table, codes):
"""
Makes the value relation dictionary (multi valued attributes)
"""
self.checkAndOpenDb()
ret = dict()
in_clause = '(%s)' % ",".join(map(str, codes))
sql = self.gen.makeRelationDict(table, in_clause)
query = QSqlQuery(sql, self.db)
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
return ret
def getGeomColumnTupleList(self, showViews=False):
"""
list in the format [(table_schema, table_name, geometryColumn, geometryType, tableType)]
centroids are hidden by default
"""
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomColumnTupleList(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom tuple list: ") +
query.lastError().text())
geomList = []
while query.next():
if edgvVersion in ['2.1.3', 'FTer_2a_Ed']:
geomList.append((query.value(0).split('_')[0],
'_'.join(query.value(0).split('_')[1::]),
query.value(1), query.value(2), 'BASE TABLE'))
else:
geomList.append(
(query.value(0).split('_')[0],
'_'.join(query.value(0).split('_')[1::]), query.value(1),
self.getResolvedGeomType(int(query.value(2))),
'BASE TABLE'))
return geomList
def test_category_chart_aggregate(self):
self.db.open()
query = QSqlQuery(self.db)
query.exec_("SELECT id FROM comptages.installation \
WHERE name = '64080011';")
query.next()
installation_id = query.value(0)
query.exec_("SELECT id FROM comptages.model \
WHERE name = 'M660_LT';")
query.next()
model_id = query.value(0)
query.exec_("select id from comptages.sensor_type \
where name = 'Tube'")
query.next()
sensor_type_id = query.value(0)
query_str = (
"INSERT INTO comptages.count(id, "
"start_process_date, end_process_date, start_service_date, "
"end_service_date, id_sensor_type, id_model, id_installation) "
"VALUES (1, '2018-12-18', '2018-12-20', '2018-12-18', "
"'2018-12-20', {}, {}, {});".format(sensor_type_id, model_id,
installation_id))
query.exec_(query_str)
task = self.comptages.import_file(
os.path.join(self.test_data_path, 'category_chart_aggregate.i00'),
1)
task.waitForFinished()
# Let the time to the db to finish the writing
time.sleep(1)
label, values = self.layers.get_aggregate_category_chart_data(
1, self.layers.IMPORT_STATUS_QUARANTINE, '64080011')
self.assertEqual(label.index('CAR (1)'), values.index(10))
self.assertEqual(label.index('MR (2)'), values.index(20))
self.assertEqual(label.index('PW (3)'), values.index(30))
self.assertEqual(label.index('PW+ANH (4)'), values.index(40))
self.assertEqual(label.index('LIE (5)'), values.index(50))
self.assertEqual(label.index('LIE+ANH (6)'), values.index(60))
self.assertEqual(label.index('LIE+AUFL (7)'), values.index(70))
self.assertEqual(label.index('LW (8)'), values.index(80))
self.assertEqual(label.index('LZ (9)'), values.index(90))
self.assertEqual(label.index('SZ (10)'), values.index(100))
def testCreateEmptyLayer(self):
# cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True)
self.execSQLCommand("DELETE FROM user_sdo_geom_metadata where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True)
uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)"
exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Point, crs=QgsCoordinateReferenceSystem(4326), overwrite=True)
self.assertEqual(exporter.errorCount(), 0)
self.assertEqual(exporter.errorCode(), 0)
# check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist)
self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"')
# check that metadata table has been correctly populated
query = QSqlQuery(self.conn)
self.assertTrue(query.exec_("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'"))
self.assertTrue(query.next())
self.assertEqual(query.value(0), "GEOM")
self.assertEqual(query.value(1), 4326)
query.finish()
# no feature, so we cannot guess the geometry type, so the layer is not valid
# but srid is set for provider in case you want to add a feature even if the layer is invalid!
# layer sourceCrs is empty because the layer is not considered spatial (not know geometry type)
vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
self.assertFalse(vl.isValid())
self.assertEqual(vl.dataProvider().sourceCrs().authid(), "EPSG:4326")
# so we set the geometry type
vl = QgsVectorLayer(self.dbconn + ' sslmode=disable type=POINT table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
self.assertTrue(vl.isValid())
self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326")
f = QgsFeature(vl.fields())
f.setGeometry(QgsGeometry.fromWkt('POINT (43.5 1.42)'))
vl.dataProvider().addFeatures([f])
query = QSqlQuery(self.conn)
self.assertTrue(query.exec_('SELECT "l"."GEOM"."SDO_SRID" from "QGIS"."EMPTY_LAYER" "l"'))
self.assertTrue(query.next())
self.assertEqual(query.value(0), 4326)
query.finish()
# now we can autodetect geom type and srid
vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
self.assertTrue(vl.isValid())
self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326")
def get_characteristic_speeds(self, count_id, hour, direction):
self.init_db_connection()
query = QSqlQuery(self.db)
result = []
query_str = ("select count(*) from comptages.count_detail as det join "
"comptages.lane as lan on det.id_lane = lan.id "
"where det.id_count = {} and lan.direction = {} "
"and date_part('hour', det.timestamp) = {};".format(
count_id, direction, hour))
query.exec_(query_str)
if query.next():
count = query.value(0)
else:
return [0, 0, 0, 0]
percent = []
percent.append(int(count * 0.15))
percent.append(int(count * 0.5))
percent.append(int(count * 0.85))
for i in percent:
if i < 0:
i = 0
query_str = (
"select det.speed from comptages.count_detail as det join "
"comptages.lane as lan on det.id_lane = lan.id "
"where det.id_count = {} and lan.direction = {} "
"and date_part('hour', det.timestamp) = {} "
"order by speed "
"offset ({}-1) rows "
"fetch next 1 rows only;".format(count_id, direction, hour, i))
query.exec_(query_str)
query.next()
result.append(query.value(0))
query_str = ("select coalesce(avg(det.speed), 0) from "
"comptages.count_detail as det join "
"comptages.lane as lan on det.id_lane = lan.id "
"where det.id_count = {} and lan.direction = {} "
"and date_part('hour', det.timestamp) = {};".format(
count_id, direction, hour))
query.exec_(query_str)
query.next()
result.append(query.value(0))
return result
def getDbsFromServer(self, name):
"""
Gets server databases
name: server name
"""
gen = self.factory.createSqlGenerator(driver=DsgEnums.DriverPostGIS)
(host, port, user, password) = self.getServerConfiguration(name)
database = 'postgres'
postgisDb = self.dbFactory.createDbFactory(DsgEnums.DriverPostGIS)
postgisDb.connectDatabaseWithParameters(host, port, database, user,
password)
if not postgisDb.db.open():
QgsMessageLog.logMessage(db.lastError().text(), "DSGTools Plugin",
Qgis.Critical)
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr('A problem occurred! Check log for details.'))
query = QSqlQuery(gen.getDatabasesFromServer(), postgisDb.db)
if not query.isActive():
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr("Problem executing query: ") +
query.lastError().text())
dbList = []
while query.next():
dbList.append(query.value(0))
postgisDb.closeDatabase()
return self.browseServer(dbList, host, port, user, password)
def on_pbSearchStr_clicked(self):
# qDebug("on_pbSearchStr_clicked: text={}".format(self.leStr.text()))
qry = QSqlQuery(self.db)
self.cbxStrassen.blockSignals(True)
self.cbxStrassen.clear()
if qry.exec_(u"SELECT k.schluesselgesamt, k.bezeichnung || coalesce(', ' || g.bezeichnung,'') FROM ax_lagebezeichnungkatalogeintrag k LEFT OUTER JOIN ax_gemeinde g ON k.land=g.land AND k.regierungsbezirk=g.regierungsbezirk AND k.kreis=g.kreis AND k.gemeinde::int=g.gemeinde::int AND g.endet IS NULL WHERE lower(k.bezeichnung) LIKE {0} AND k.endet IS NULL ORDER BY k.bezeichnung || coalesce(', ' || g.bezeichnung,'')".format(quote(self.leStr.text().lower() + '%'))):
while qry.next():
self.cbxStrassen.addItem(qry.value(1), qry.value(0))
self.cbxStrassen.blockSignals(False)
self.lblResult.setText(u"Keine Straßen gefunden" if self.cbxStrassen.count() == 0 else u"{} Straßen gefunden".format(self.cbxStrassen.count()))
self.cbxStrassen.setEnabled(self.cbxStrassen.count() > 0)
self.cbxStrassen.setCurrentIndex(0 if self.cbxStrassen.count() == 1 else -1)
self.on_cbxStrassen_currentIndexChanged(self.cbxStrassen.currentIndex())
def get_runs(self):
q = QSqlQuery(db=self.db)
q.exec_('select run from hsrr.run_info order by run')
runs = []
while q.next():
runs.append(q.value(0))
return runs
def getLayersWithElementsV2(self, layerList, useInheritance=False):
self.checkAndOpenDb()
lyrWithElemList = []
for layer in layerList:
if isinstance(layer, dict):
schema = layer['tableSchema']
lyr = layer['tableName']
else:
if '.' in layer:
schema, lyr = layer.replace('"', '').split('.')
else:
lyr = layer
schema = self.getTableSchemaFromDb(lyr)
sql = self.gen.getElementCountFromLayerV2(schema, lyr,
useInheritance)
query = QSqlQuery(sql, self.db)
if not query.next():
# use may not have permission to read the table from schema
QgsMessageLog.logMessage(
self.tr("Unable to read table {0}. Error message: '{1}'")\
.format(
self.db.databaseName(),
self.db.lastError().databaseText()
),
"DSGTools Plugin",
Qgis.Warning
)
continue
if query.value(0) > 0:
lyrWithElemList.append(lyr)
return lyrWithElemList
def populate_category_dict(self):
if 'CLASS' not in self.file_header:
return
class_name = self.file_header['CLASS']
query = QSqlQuery(self.db)
query_str = (
"select cat.code, cc.id_category from "
"comptages.class_category as cc "
"join comptages.category as cat on cc.id_category = cat.id "
"join comptages.class as cl on cl.id = cc.id_class "
"where cl.name = '{}';".format(class_name))
query.exec_(query_str)
while query.next():
self.categories[int(query.value(0))] = int(query.value(1))
def populate_category_and_light_index(self):
query = QSqlQuery(self.db)
query_str = (
"select cat.id, cat.light from comptages.count as cou "
"join comptages.class_category as cc on "
"cou.id_class = cc.id_class "
"join comptages.category as cat on cat.id = cc.id_category "
"where cou.id = {};".format(self.count_id))
query.exec_(query_str)
i = 0
while query.next():
self.categories.append(int(query.value(0)))
if query.value(1):
self.light_vehicles.append(i)
i += 1
def check_geom(self, layer, pk, wkt, wkt_ref=None, check_valid=True):
"""
Add geom to the layer and check everything is OK
"""
table = layer.dataProvider().uri().table()
# insert geom in layer
self.assertTrue(layer.startEditing())
feature = QgsFeature(layer.fields())
geom = QgsGeometry.fromWkt(wkt)
feature.setAttributes([pk])
feature.setGeometry(geom)
self.assertTrue(layer.addFeature(feature))
self.assertTrue(layer.commitChanges())
if check_valid:
self.assertTrue(self.conn)
query = QSqlQuery(self.conn)
sql = """select p.GEOM.st_isvalid() from QGIS.{} p where "pk" = {}""".format(
table, pk)
res = query.exec_(sql)
self.assertTrue(res, sql + ': ' + query.lastError().text())
query.next()
valid = query.value(0)
self.assertTrue(
valid,
"geometry '{}' inserted in database is not valid".format(wkt))
query.finish()
expected_wkt = wkt if wkt_ref is None else wkt_ref
res_wkt = layer.getFeature(pk).geometry().asWkt()
self.assertTrue(
compareWkt(res_wkt, expected_wkt, 0.00001),
"\nactual = {}\nexpected = {}".format(res_wkt, expected_wkt))
def testCreateEmptyLayer(self):
# cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True)
self.execSQLCommand("DELETE FROM user_sdo_geom_metadata where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True)
uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)"
exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Point, crs=QgsCoordinateReferenceSystem(4326), overwrite=True)
self.assertEqual(exporter.errorCount(), 0)
self.assertEqual(exporter.errorCode(), 0)
# check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist)
self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"')
# check that metadata table has been correctly populated
query = QSqlQuery(self.conn)
self.assertTrue(query.exec_("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'"))
self.assertTrue(query.next())
self.assertEqual(query.value(0), "GEOM")
self.assertEqual(query.value(1), 4326)
query.finish()
vl = QgsVectorLayer(
self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" sql=',
'test', 'oracle')
self.assertTrue(vl.isValid())
def get_sections_with_data_of_count(self, count_id, status):
self.init_db_connection()
query = QSqlQuery(self.db)
query_str = (
"select distinct(lan.id_section) from comptages.count_aggregate "
"as cou "
"join comptages.lane as lan "
"on lan.id = cou.id_lane "
"where cou.id_count = {0} "
"and import_status = {1} "
"union "
"select distinct(lan.id_section) from comptages.count_detail "
"as cou "
"join comptages.lane as lan "
"on lan.id = cou.id_lane "
"where cou.id_count = {0} "
"and import_status = {1} ".format(count_id, status))
result = []
query.exec_(query_str)
while query.next():
result.append(query.value(0))
return result
def on_pbSearchStr_clicked(self):
# qDebug("on_pbSearchStr_clicked: text={}".format(self.leStr.text()))
qry = QSqlQuery(self.db)
self.cbxStrassen.blockSignals(True)
self.cbxStrassen.clear()
if qry.exec_(u"SELECT k.schluesselgesamt, k.bezeichnung || coalesce(', ' || g.bezeichnung,'') FROM ax_lagebezeichnungkatalogeintrag k LEFT OUTER JOIN ax_gemeinde g ON k.land=g.land AND k.regierungsbezirk=g.regierungsbezirk AND k.kreis=g.kreis AND k.gemeinde::int=g.gemeinde::int AND g.endet IS NULL WHERE k.bezeichnung LIKE {0} AND k.endet IS NULL ORDER BY k.bezeichnung || coalesce(', ' || g.bezeichnung,'')".format(quote(self.leStr.text() + '%'))):
while qry.next():
self.cbxStrassen.addItem(qry.value(1), qry.value(0))
self.cbxStrassen.blockSignals(False)
self.lblResult.setText(u"Keine Straßen gefunden" if self.cbxStrassen.count() == 0 else u"{} Straßen gefunden".format(self.cbxStrassen.count()))
self.cbxStrassen.setEnabled(self.cbxStrassen.count() > 0)
self.cbxStrassen.setCurrentIndex(0 if self.cbxStrassen.count() == 1 else -1)
self.on_cbxStrassen_currentIndexChanged(self.cbxStrassen.currentIndex())
def test_speed_chart_detail(self):
self.db.open()
query = QSqlQuery(self.db)
query.exec_("SELECT id FROM comptages.installation \
WHERE name = '64080011';")
query.next()
installation_id = query.value(0)
query.exec_("SELECT id FROM comptages.model \
WHERE name = 'M660_LT';")
query.next()
model_id = query.value(0)
query.exec_("select id from comptages.sensor_type \
where name = 'Tube'")
query.next()
sensor_type_id = query.value(0)
query_str = (
"INSERT INTO comptages.count(id, "
"start_process_date, end_process_date, start_service_date, "
"end_service_date, id_sensor_type, id_model, id_installation) "
"VALUES (1, '2018-12-18', '2018-12-20', '2018-12-18', "
"'2018-12-20', {}, {}, {});".format(sensor_type_id, model_id,
installation_id))
query.exec_(query_str)
task = self.comptages.import_file(
os.path.join(self.test_data_path, 'speed_chart_detail.V01'), 1)
task.waitForFinished()
# Let the time to the db to finish the writing
time.sleep(1)
x, y = self.layers.get_detail_speed_chart_data(
1, self.layers.IMPORT_STATUS_QUARANTINE, '64080011')
self.assertEqual([
'0-10 km/h', '10-20 km/h', '20-30 km/h', '30-40 km/h',
'40-50 km/h', '50-60 km/h', '60-70 km/h', '70-80 km/h',
'80-90 km/h', '90-100 km/h', '100-110 km/h', '110-120 km/h',
'120-999 km/h'
], x)
self.assertEqual([1, 0, 3, 0, 0, 4, 0, 0, 1, 0, 0, 0, 2], y)
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 getQmlRecordDict(self, inputLayer):
self.checkAndOpenDb()
if isinstance(inputLayer, list):
sql = self.gen.getQmlRecords(inputLayer)
else:
sql = self.gen.getQmlRecords([inputLayer])
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting qmlRecordDict: ") +
query.lastError().text())
qmlDict = dict()
while query.next():
if isinstance(inputLayer, list):
qmlDict[query.value(0)] = query.value(1)
else:
return query.value(1)
return qmlDict
def getAllEdgvDomainsFromTableName(self, schema, table):
"""
EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB.
It checks for all attributes found.
:param table: (str) layer to be checked for its EDGV mapping.
:return: (dict) value map for all attributes that have one.
"""
self.abstractDb.checkAndOpenDb()
ret = defaultdict(dict)
db = self.abstractDb.db
edgv = self.abstractDb.getDatabaseVersion()
domainMap = self.domainMapping(edgv)
fullTablaName = schema + "_" + table
sql = 'select code, code_name from dominios_{field} order by code'
for fieldName in self.tableFields(fullTablaName):
if fullTablaName in domainMap:
domains = domainMap[fullTablaName]
# if domain mapping is not yet available for current version
if fieldName in domains:
# replace this method over querying db for the table...
domainTable = domains[fieldName][0]
else:
# non-mapped attribute
continue
query = QSqlQuery(sql.format(field=domainTable), db)
elif fieldName in self.specialEdgvAttributes():
# EDGV "special" attributes that are have different domains depending on
# which class it belongs to
if edgv in ("2.1.3 Pro", "3.0 Pro"):
# Pro versions now follow the logic "{attribute}_{CLASS_NAME}"
cat = table.rsplit("_", 1)[0].split("_", 1)[-1]
else:
cat = table.split("_")[0]
attrTable = "{attribute}_{cat}".format(attribute=fieldName, cat=cat)
query = QSqlQuery(sql.format(field=attrTable), db)
else:
query = QSqlQuery(sql.format(field=fieldName), db)
if not query.isActive():
continue
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[fieldName][code_name] = code
return ret
def on_cbxStrassen_currentIndexChanged(self, index):
# qDebug(u"on_cbxStrassen_currentIndexChanged: index={} text={}".format(self.cbxStrassen.currentIndex(), self.cbxStrassen.currentText()))
qry = QSqlQuery(self.db)
schluesselgesamt = self.cbxStrassen.itemData(self.cbxStrassen.currentIndex())
self.cbxHNR.blockSignals(True)
self.cbxHNR.clear()
if qry.exec_(u"SELECT h.hausnummer FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k ON h.land=k.land AND h.regierungsbezirk=k.regierungsbezirk AND h.kreis=k.kreis AND h.gemeinde=k.gemeinde AND h.lage=k.lage WHERE k.schluesselgesamt={0} ORDER BY NULLIF(regexp_replace(h.hausnummer, E'\\\\D', '', 'g'), '')::int".format(quote(schluesselgesamt))):
while qry.next():
self.cbxHNR.addItem(qry.value(0))
if self.cbxHNR.count() > 1:
self.cbxHNR.addItem("Alle")
self.cbxHNR.blockSignals(False)
self.cbxHNR.setEnabled(self.cbxHNR.count() > 0)
self.cbxHNR.setCurrentIndex(0 if self.cbxHNR.count() == 1 else -1)
def fetchall(self, db, sql):
rows = []
qry = QSqlQuery(db)
if qry.exec_(sql):
rec = qry.record()
while qry.next():
row = {}
for i in range(0, rec.count()):
v = "%s" % qry.value(i)
if v == "NULL":
v = ''
row[rec.fieldName(i)] = v.strip()
rows.append(row)
else:
qDebug("Exec failed: " + qry.lastError().text())
return rows
def loadModels(self, error=True):
self.settings.servicE = self.leSERVICE.text()
self.settings.host = self.leHOST.text()
self.settings.port = self.lePORT.text()
self.settings.dbname = self.leDBNAME.text()
self.settings.schema = self.leSCHEMA.text()
self.settings.uid = self.leUID.text()
self.settings.pwd = self.lePWD.text()
if hasattr(qgis.gui, 'QgsAuthConfigSelect'):
self.settings.authcfg = self.authCfgSelect.configId()
self.twModellarten.clearContents()
self.cbxSignaturkatalog.clear()
(db, conninfo) = self.plugin.opendb()
if not db:
if error:
QMessageBox.critical(None, "ALKIS", u"Datenbankverbindung schlug fehl.")
self.twModellarten.clearContents()
self.twModellarten.setDisabled(True)
self.twModellarten.setRowCount(0)
self.settings.load()
return
modelle = self.settings.modellarten
if modelle is None:
modelle = ['DLKM', 'DKKM1000']
qry = QSqlQuery(db)
if qry.exec_("SELECT 1 FROM information_schema.tables WHERE table_schema={} AND table_name='po_modelle'".format(quote(self.plugin.settings.schema))) and qry.next():
sql = "SELECT modell,n FROM po_modelle WHERE modell IS NOT NULL ORDER BY n DESC"
else:
sql = """
SELECT modell,count(*)
FROM (
SELECT unnest(modell) AS modell FROM po_points UNION ALL
SELECT unnest(modell) AS modell FROM po_lines UNION ALL
SELECT unnest(modell) AS modell FROM po_polygons UNION ALL
SELECT unnest(modell) AS modell from po_labels
) AS foo
WHERE modell IS NOT NULL
GROUP BY modell
ORDER BY count(*) DESC
"""
if qry.exec_(sql):
res = {}
while qry.next():
res[qry.value(0)] = qry.value(1)
self.twModellarten.setRowCount(len(res))
i = 0
for k, n in sorted(iter(list(res.items())), key=operator.itemgetter(1), reverse=True):
item = QTableWidgetItem(k)
item.setCheckState(Qt.Checked if (item.text() in modelle) else Qt.Unchecked)
self.twModellarten.setItem(i, 0, item)
item = QTableWidgetItem(str(n))
self.twModellarten.setItem(i, 1, item)
i += 1
self.twModellarten.resizeColumnsToContents()
self.twModellarten.setEnabled(True)
else:
self.twModellarten.clearContents()
self.twModellarten.setDisabled(True)
self.twModellarten.setRowCount(0)
if qry.exec_("SELECT id,name FROM alkis_signaturkataloge"):
while qry.next():
self.cbxSignaturkatalog.addItem(qry.value(1), int(qry.value(0)))
self.cbxSignaturkatalog.setEnabled(True)
else:
self.cbxSignaturkatalog.addItem(u"Farbe", -1)
self.cbxSignaturkatalog.setCurrentIndex(max([0, self.cbxSignaturkatalog.findData(self.settings.signaturkatalog)]))
self.settings.load()
def gfzn(self):
g = self.cbxGemarkung.itemData(self.cbxGemarkung.currentIndex()) if self.cbxGemarkung.currentIndex() >= 0 else None
f = self.cbxFlur.itemData(self.cbxFlur.currentIndex()) if self.cbxFlur.currentIndex() >= 0 else None
z = self.cbxFSZ.itemData(self.cbxFSZ.currentIndex()) if self.cbxFSZ.currentIndex() >= 0 else None
n = self.cbxFSN.itemData(self.cbxFSN.currentIndex()) if self.cbxFSN.currentIndex() >= 0 else None
where = []
if g is not None and g != "":
where.append("gemashl='%s'" % g)
if f is not None and f != "":
where.append("flr='%s'" % f)
if z is not None and n is not None and z != "" and n != "":
where.append("flsnrk='%s/%s'" % (z, n))
elif z is not None and z != "":
where.append("flsnrk LIKE '%s/%%'" % z)
elif n is not None and n != "":
where.append("flsnrk LIKE '%%/%s'" % n)
where = u" WHERE {}".format(u" AND ".join(where)) if where else ""
qry = QSqlQuery(self.db)
# qDebug(u"WHERE:{}".format(where))
for cbx, sql, val in [
[
self.cbxGemarkung,
"SELECT {0} FROM gema_shl a LEFT OUTER JOIN gem_shl b USING (gemshl){1} GROUP BY {0} ORDER BY {0}".format(
"a.gemashl,a.gemarkung||' ('||a.gemashl||coalesce(', '||b.gemname,'')||')'",
u" JOIN flurst c USING (gemashl){0}".format(where) if where != "" else ""
),
g,
],
[
self.cbxFlur,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("flr", where),
f,
],
[
self.cbxFSZ,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',1)", where),
z,
],
[
self.cbxFSN,
"SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',2)", where),
n,
],
]:
cbx.blockSignals(True)
cbx.clear()
cbx.addItem("Alle", "")
# qDebug(u"SQL:{} [{}]".format(sql, val))
if qry.exec_(sql):
d = 0 if qry.record().count() == 1 else 1
while qry.next():
cbx.addItem(qry.value(d), qry.value(0))
cbx.setCurrentIndex(cbx.findData(val))
cbx.blockSignals(False)
if where == "":
return
hits = 0
if qry.exec_(u"SELECT count(*) FROM flurst{}".format(where)) and qry.next():
hits = qry.value(0)
if hits > 0 and hits < int(self.leHighlightThreshold.text()):
self.evaluate()
else:
self.lblResult.setText(u"{} Flurstücke gefunden".format(hits) if hits > 0 else u"Keine Flurstücke gefunden")
def evaluate(self):
if not self.plugin.initLayers():
return False
if self.tabWidget.currentWidget() == self.tabLabels:
text = self.leSuchbegriff.text()
if text != "":
if self.cbTeiltreffer.isChecked():
# Teiltreffer
text = u"lower(text) LIKE %s" % quote("%%%s%%" % text.lower())
else:
# Exakter Treffer
text = u"text=%s" % quote(text)
qry = QSqlQuery(self.db)
sql = u"SELECT count(*),st_extent(coalesce(point,line)) FROM po_labels WHERE {0}".format(text)
if qry.exec_(sql) and qry.next() and qry.value(0) > 0:
self.lblResult.setText("{} Objekte gefunden".format(qry.value(0)))
self.plugin.zoomToExtent(qry.value(1), self.plugin.pointMarkerLayer.crs())
else:
self.lblResult.setText("Keine Objekte gefunden")
return False
else:
text = "false"
self.plugin.pointMarkerLayer.setSubsetString(text)
self.plugin.lineMarkerLayer.setSubsetString(text)
self.updateButtons()
elif self.tabWidget.currentWidget() == self.tabGFF:
g = self.cbxGemarkung.itemData(self.cbxGemarkung.currentIndex())
f = self.cbxFlur.itemData(self.cbxFlur.currentIndex())
z = self.cbxFSZ.itemData(self.cbxFSZ.currentIndex())
n = self.cbxFSN.itemData(self.cbxFSN.currentIndex())
flsnr = ""
flsnr += ("%" if g is None or g == "" else g) + "-"
flsnr += ("%" if f is None or f == "" else f) + "-"
flsnr += ("%" if z is None or z == "" else z) + "/"
flsnr += ("%" if n is None or n == "" else n)
# qDebug(u"flsnr:{}".format(flsnr))
fs = self.plugin.highlight(where=u"EXISTS (SELECT * FROM fs WHERE gml_id=fs_obj AND alb_key LIKE %s)" % quote(flsnr), zoomTo=True)
self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden")
self.updateButtons(fs)
elif self.tabWidget.currentWidget() == self.tabFLSNR:
hits = 0
m = re.search("(\\d+)(-\\d+)?-(\\d+)(/\\d+)?", self.leFLSNR.text())
if m:
g, f, z, n = int(m.group(1)), m.group(2), int(m.group(3)), m.group(4)
f = int(f[1:]) if f else 0
n = int(n[1:]) if n else 0
flsnr = "%06d" % g
flsnr += "%03d" % f if f > 0 else "___"
flsnr += "%05d" % z
flsnr += "%04d" % n if n > 0 else "____"
flsnr += "%"
fs = self.plugin.highlight(where=u"flurstueckskennzeichen LIKE %s" % quote(flsnr), zoomTo=True)
hits = len(fs)
self.lblResult.setText(u"{} Flurstücke gefunden".format(hits) if hits > 0 else u"Keine Flurstücke gefunden")
self.updateButtons(fs)
elif self.tabWidget.currentWidget() == self.tabSTRHNR:
text = self.leStr.text()
if text != "":
m = re.search("^(.*)\\s+(\\d+[a-zA-Z]?)$", text)
if m:
strasse, ha = m.group(1), m.group(2)
fs = self.plugin.highlight(where=u"EXISTS (SELECT * FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k ON h.land=k.land AND h.regierungsbezirk=k.regierungsbezirk AND h.kreis=k.kreis AND h.gemeinde=k.gemeinde AND h.lage=k.lage WHERE ARRAY[h.gml_id] <@ ax_flurstueck.weistauf AND lower(k.bezeichnung) LIKE {0} AND h.hausnummer={1})".format(quote(strasse.lower() + '%'), quote(ha.upper())), zoomTo=True)
if len(fs) > 0:
self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)))
else:
self.lblResult.setText(u"Keine Flurstücke gefunden")
self.updateButtons(fs)
if self.cbxHNR.isEnabled():
hnr = self.cbxHNR.currentText()
sql = u"EXISTS (SELECT * FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k USING (land,regierungsbezirk,kreis,gemeinde,lage) WHERE ARRAY[h.gml_id] <@ ax_flurstueck.weistauf AND k.schluesselgesamt={0}{1})"
if hnr == "Alle":
sql += u" OR EXISTS (SELECT * FROM ax_lagebezeichnungohnehausnummer h JOIN ax_lagebezeichnungkatalogeintrag k USING (land,regierungsbezirk,kreis,gemeinde,lage) WHERE ARRAY[h.gml_id] <@ ax_flurstueck.zeigtauf AND k.schluesselgesamt={0})"
fs = self.plugin.highlight(
where=sql.format(
quote(self.cbxStrassen.itemData(self.cbxStrassen.currentIndex())),
' AND h.hausnummer={0}'.format(quote(hnr)) if hnr != "Alle" else ""
),
zoomTo=True
)
self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden")
self.updateButtons(fs)
elif self.tabWidget.currentWidget() == self.tabEigentuemer:
where = []
for e in self.leEigentuemer.text().split():
where.append("lower(name1) LIKE " + quote('%' + e.lower() + '%'))
if where:
fs = self.plugin.retrieve(u"gml_id IN (SELECT fs_obj FROM fs JOIN eignerart a ON fs.alb_key=a.flsnr JOIN eigner e ON a.bestdnr=e.bestdnr AND %s)" % " AND ".join(where))
if len(fs) == 0:
qDebug(u"Kein Flurstück gefunden")
self.updateButtons()
return False
if not self.plugin.logQuery("eigentuemerSuche", self.leEigentuemer.text(), [i['flsnr'] for i in fs]):
self.lblResult.setText(u"Flurstücke werden ohne Protokollierung nicht angezeigt.")
self.updateButtons()
return False
fs = self.plugin.highlight(fs=fs, zoomTo=True)
self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden")
self.updateButtons(fs)
return True
def getPage(self, fs):
(db, conninfo) = self.plugin.opendb()
if db is None:
return None
qry = QSqlQuery(db)
if qry.exec_("SELECT 1 FROM information_schema.columns WHERE table_schema={} AND table_name='eignerart' AND column_name='anteil'".format(quote(self.plugin.settings.schema))) and qry.next():
exists_ea_anteil = qry.value(0) == 1
else:
exists_ea_anteil = False
html = ""
for i in range(0, len(fs)):
flsnr = fs[i]['flsnr']
best = self.fetchall(db, (
"SELECT " +
"ea.bvnr" +
",'' as pz" +
",(SELECT eignerart FROM eign_shl WHERE ea.b=b) as eignerart" +
",%s as anteil" +
",ea.ff_stand AS zhist" +
",b.bestdnr" +
",b.gbbz" +
",b.gbblnr" +
",b.bestfl" +
",b.ff_stand AS bhist" +
" FROM eignerart ea" +
" JOIN bestand b ON ea.bestdnr = b.bestdnr" +
" WHERE ea.flsnr = '%s'" +
" ORDER BY zhist,bhist,b") % ("ea.anteil" if exists_ea_anteil else "''", flsnr)
)
res = self.fetchall(db, "SELECT f.*,g.gemarkung FROM flurst f LEFT OUTER JOIN gema_shl g ON (f.gemashl=g.gemashl) WHERE f.flsnr='%s' AND f.ff_stand=0" % flsnr)
if len(res) == 1:
res = res[0]
else:
QMessageBox.information(None, "Fehler", u"Flurstück %s nicht gefunden.\n[%s]" % (flsnr, repr(fs)))
return None
res['datum'] = QDate.currentDate().toString("d. MMMM yyyy")
res['hist'] = 0
if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['strassen', 'str_shl']])) and qry.next() and qry.value(0):
res['str'] = self.fetchall(db, "SELECT sstr.strname,str.hausnr FROM str_shl sstr JOIN strassen str ON str.strshl=sstr.strshl WHERE str.flsnr='%s' AND str.ff_stand=0" % flsnr)
if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['nutz_21', 'nutz_shl']])) and qry.next() and qry.value(0):
res['nutz'] = self.fetchall(db, "SELECT n21.*, nu.nutzshl, nu.nutzung FROM nutz_21 n21, nutz_shl nu WHERE n21.flsnr='%s' AND n21.nutzsl=nu.nutzshl AND n21.ff_stand=0" % flsnr)
if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['klas_3x', 'kls_shl']])) and qry.next() and qry.value(0):
res['klas'] = self.fetchall(db, "SELECT sum(fl::int) AS fl, min(kls.klf_text) AS klf_text FROM klas_3x kl, kls_shl kls WHERE kl.flsnr='%s' AND kl.klf=kls.klf AND kl.ff_stand=0 GROUP BY kls.klf" % flsnr)
if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['ausfst', 'afst_shl']])) and qry.next() and qry.value(0):
res['afst'] = self.fetchall(db, "SELECT au.*, af.afst_txt FROM ausfst au,afst_shl af WHERE au.flsnr='%s' AND au.ausf_st=af.ausf_st AND au.ff_stand=0" % flsnr)
if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['bestand', 'eignerart', 'eign_shl']])) and qry.next() and qry.value(0):
res['best'] = self.fetchall(db, "SELECT ea.bvnr,'' as pz,(SELECT eignerart FROM eign_shl WHERE ea.b = b) as eignerart,%s as anteil,ea.ff_stand AS zhist,b.bestdnr,b.gbbz,b.gbblnr,b.bestfl,b.ff_stand AS bhist FROM eignerart ea JOIN bestand b ON ea.bestdnr = b.bestdnr WHERE ea.flsnr='%s' ORDER BY zhist,bhist,b" % (
"ea.anteil" if exists_ea_anteil else "''",
flsnr
))
if qry.exec_("SELECT has_table_privilege('eigner', 'SELECT')") and qry.next() and qry.value(0):
for b in res['best']:
b['bse'] = self.fetchall(db, "SELECT * FROM eigner WHERE bestdnr='%s' AND ff_stand=0" % b['bestdnr'])
# for k,v in res.iteritems():
# qDebug( u"%s:%s\n" % ( k, unicode(v) ) )
html = u"""
<HTML xmlns="http://www.w3.org/1999/xhtml">
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</HEAD>
<BODY>
<style>
.fls_tab{width:100%%;empty-cells:show}
.fls_headline{font-weight:bold;font-size:4em;}
.fls_headline_col{background-color:#EEEEEE;width:100%%;height:30px;text-align:left;}
.fls_time {background-color:#EEEEEE;font-weight:bold;font-size:4em;text-align:right;width:100%%}
.fls_col_names{font-weight:bold;}
.fls_col_values{vertical-align:top;}
.fls_bst{width:100%%;empty-cells:show}
.fls_hr{border:dotted 1px;color:#080808;}
.fls_footnote{text-align:center;}
</style>
<TABLE class="fls_tab" border="0" width="100%%" cellspacing="0">
<TR class="fls_headline">
<TD colspan="3" class="fls_headline_col">Flurstücksnachweis</TD><TD class="fls_time" colspan="4" align="right">%(datum)s</TD></TR>
</TR>
<TR><TD colspan="7"> </TD></TR>
<TR>
<TD colspan="7"><h3>Flurstück %(gemashl)s-%(flr)s-%(flsnrk)s<hr style="width:100%%"></h3></TD>
</TR>
<TR class="fls_col_names">
<TD width="15%%">Gemarkung</TD>
<TD width="6%%">Flur</TD>
<TD width="15%%">Flurstück</TD>
<TD width="20%%">Flurkarte</TD>
<TD width="17%%">Entstehung</TD>
<TD width="17%%">Fortführung</TD>
<TD width="5%%">Fläche</TD>
</TR>
<TR class="fls_col_values">
<TD>%(gemashl)s<br>%(gemarkung)s</TD>
<TD>%(flr)s</TD>
<TD>%(flsnrk)s</TD>
<TD>%(flurknr)s</TD>
<TD>%(entst)s</TD>
<TD>%(fortf)s</TD>
<TD>%(flsfl)s m²</TD>
</TR>
</TABLE>
""" % res
if res['blbnr']:
html += u"""
<TABLE class="fls_tab" border="0" width="100%%">
<TR class="fls_col_names">
<TD width="21%%"></TD>
<TD width="79%%">Baulastenblattnr.</TD>
</TR>
<TR class="fls_col_values">
<TD></TD>
<TD>%(blbnr)s</TD>
</TR>
</TABLE>
""" % res
if res['lagebez'] or res['anl_verm']:
html += u"""
<TABLE class="fls_tab" border="0" width="100%%">
<TR class="fls_col_names">
<TD width="21%%"></TD>
<TD width="52%%">Lage</TD>
<TD width="27%%">Anliegervermerk</TD>
</TR>
<TR class="fls_col_values">
<TD></TD>
<TD>%(lagebez)s</TD>
<TD>%(anl_verm)s</TD>
</TR>
</TABLE>
""" % res
if 'str' in res:
if res['str']:
html += u"""
<TABLE border="0" class="fls_tab" width="100%">
<TR class="fls_col_names">
<TD width="21%"></TD>
<TD width="52%">Strasse</TD>
<TD width="27%">Hausnummer</TD>
</TR>
"""
for strres in res['str']:
html += u"""
<TR class="fls_col_values">
<TD></TD><TD>%(strname)s</TD><TD>%(hausnr)s</TD></TR>
</TR>
""" % strres
html += u"""
</TABLE>
"""
if 'nutz' in res:
html += u"""
<TABLE border="0" class="fls_tab" width="100%">
<TR class="fls_col_names"><TD width="21%"></TD><TD width="69%">Nutzung</TD><TD width="10%">Fläche</TD></TR>
"""
if res['nutz']:
for nutz in res['nutz']:
html += u"""
<TR class="fls_col_values"><TD></TD><TD>21%(nutzshl)s - %(nutzung)s</TD><TD>%(fl)s m²</TD></TR>
""" % nutz
else:
html += u"""
<TR class="fls_col_values"><TD></TD><TD colspan=2>Keine</TD></TR>
"""
html += u"""
</TABLE>
"""
if 'klas' in res:
html += u"""
<TABLE border="0" class="fls_tab" width="100%">
<TR class="fls_col_names"><TD width="21%"></TD><TD width="69%">Klassifizierung(en)</TD><TD width="10%">Fläche</TD></TR>
"""
if res['klas']:
for klas in res['klas']:
html += u"""
<TR class="fls_col_values"><TD></TD><TD>%(klf_text)s</TD><TD>%(fl)s m²</TD></TR>
""" % klas
else:
html += u"""
<TR class="fls_col_values"><TD></TD><TD colspan=2>Keine</TD></TR>
"""
html += u"""
</TABLE>
"""
if 'afst' in res:
html += u"""
<TABLE border="0" class="fls_tab" width="100%">
<TR class="fls_col_names"><TD width="21%"></TD><TD width="79%">Ausführende Stelle(n)</TD></TR>
"""
if res['afst']:
for afst in res['afst']:
html += u"""
<TR class="fls_col_values"><TD></TD><TD>%(afst_txt)s</TD></TR>
""" % afst
else:
html += u"""
<TR class="fls_col_values"><TD></TD><TD colspan=2>Keine</TD></TR>
"""
html += u"""
</TABLE>
"""
if 'best' in res:
if res['best']:
html += u"""
<br>
<TABLE border="0" class="fls_bst" width="100%">
<TR><TD colspan="6"><h3>Bestände<hr style="width:100%"></h3></TD></TR>
"""
for best in res['best']:
html += u"""
<TR class="fls_col_names">
<TD>Bestandsnummer</TD>
<TD>Grundbuchbezirk</TD>
<TD colspan="2">Grundbuchblattnr.</TD>
<TD>Anteil</TD>
</TR>
<TR class="fls_col_values">
<TD>%(bestdnr)s</TD>
<TD>%(gbbz)s</TD>
<TD colspan="2">%(gbblnr)s</TD>
<TD>%(anteil)s</TD>
</TR>
<TR class="fls_col_names">
<TD></TD>
<TD>Buchungskennz.</TD>
<TD>BVNR</TD>
<TD>PZ</TD>
""" % best
if res['hist']:
html += u"""
<TD>Hist. Bestand</TD><TD>Hist. Zuordnung</TD>
"""
else:
html += u"""
<TD></TD><TD></TD>
"""
html += u"""
</TR>
<TR class="fls_col_values">
<TD></TD>
<TD>%(eignerart)s</TD>
<TD>%(bvnr)s</TD>
<TD>%(pz)s</TD>
""" % best
html += "<TD>%s</TD>" % ("ja" if res['hist'] and best['bhist'] else "")
html += "<TD>%s</TD>" % ("ja" if res['hist'] and best['zhist'] else "")
html += u"""
</TR>
"""
if 'bse' in best:
if best['bse']:
html += u"""
<TR class="fls_col_names"><TD>Anteil</TD><TD colspan="5">Namensinformation</TD></TR>
"""
for bse in best['bse']:
html += u"""
<TR class="fls_col_values">
<TD>%(antverh)s</TD>
<TD colspan="5">%(name1)s %(name2)s<br>%(name3)s<br>%(name4)s</TD>
</TR>
""" % bse
else:
html += u"""
<p>Kein Eigentümer gefunden.</p>
"""
html += u"""
<TR><TD colspan="6"><hr class="fls_hr"></TD></TR>
"""
html += u"""
"""
footnote = self.plugin.settings.footnote
if footnote:
html += u"""
<TR><TD colspan="7" class="fls_footnote">%s</TD></TR>
""" % footnote
html += u"""
</TABLE>
</BODY>
</HTML>
"""
return html
def __init__(self, plugin):
QDialog.__init__(self)
self.setupUi(self)
self.plugin = plugin
s = QSettings("norBIT", "norGIS-ALKIS-Erweiterung")
v = QIntValidator()
v.setBottom(1)
self.leHighlightThreshold.setValidator(v)
self.leHighlightThreshold.setText(str(s.value("highlightThreshold", 1000)))
(db, conninfo) = self.plugin.opendb()
self.db = db
qry = QSqlQuery(db)
if not qry.exec_("SELECT has_table_privilege('eigner', 'SELECT')") or not qry.next() or not qry.value(0):
self.tabWidget.removeTab(self.tabWidget.indexOf(self.tabEigentuemer))
self.replaceButton = self.buttonBox.addButton(u"Ersetzen", QDialogButtonBox.ActionRole)
self.addButton = self.buttonBox.addButton(u"Hinzufügen", QDialogButtonBox.ActionRole)
self.removeButton = self.buttonBox.addButton(u"Entfernen", QDialogButtonBox.ActionRole)
self.clearButton = self.buttonBox.addButton(u"Leeren", QDialogButtonBox.ActionRole)
self.replaceButton.clicked.connect(self.replaceClicked)
self.addButton.clicked.connect(self.addClicked)
self.removeButton.clicked.connect(self.removeClicked)
self.clearButton.clicked.connect(self.clearClicked)
self.cbxStrassen.setEnabled(False)
self.cbxHNR.setEnabled(False)
self.pbLabelSearch.clicked.connect(self.evaluate)
self.pbOwnerSearch.clicked.connect(self.evaluate)
self.pbSearchFSK.clicked.connect(self.evaluate)
self.highlighted = set(self.plugin.highlighted())
self.lblResult.setText(u"{} Objekte bereits gewählt.".format(len(self.highlighted)) if len(self.highlighted) > 0 else "")
self.restoreGeometry(QSettings("norBIT", "norGIS-ALKIS-Erweiterung").value("searchgeom", QByteArray(), type=QByteArray))
self.tabWidget.setCurrentIndex(s.value("suchmodus", 0, type=int))
self.cbxGemarkung.currentIndexChanged.connect(self.gfzn)
self.cbxFlur.currentIndexChanged.connect(self.gfzn)
self.cbxFSZ.currentIndexChanged.connect(self.gfzn)
self.cbxFSN.currentIndexChanged.connect(self.gfzn)
self.gfzn()
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")
