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 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 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 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 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 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 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 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 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_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 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 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 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 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_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 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 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 is_data_aggregate(self): query = QSqlQuery(self.db) query_str = ("select id from comptages.count_aggregate " "where id_count = {}".format(self.count_id)) query.exec_(query_str) if query.next(): return True return False
def getTableSchemaFromDb(self, table): self.checkAndOpenDb() sql = self.gen.getFullTablesName(table) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting full table name: ") + query.lastError().text()) while query.next(): return query.value(0).split('_')[0]
def populate_list_of_highlighted_sections(self, start_date=None, end_date=None, permanent=None, sensor_type_id=None, tjm=None, axe=None, sector=None): """Return a list of highlighted sections. Directly on the db for performances""" self.highlighted_sections = [] self.init_db_connection() query = QSqlQuery(self.db) wheres = [] if start_date: wheres.append( "c.start_process_date >= '{}'::date".format(start_date)) if end_date: wheres.append("c.end_process_date <= '{}'::date".format(end_date)) if permanent is not None: wheres.append("i.permanent = '{}'::bool".format(permanent)) if sensor_type_id: wheres.append("c.id_sensor_type = {}".format(sensor_type_id)) if tjm: if tjm[1] >= 30000: wheres.append("c.tjm >= {}".format(tjm[0])) else: wheres.append("c.tjm between {} and {}".format(tjm[0], tjm[1])) if axe: wheres.append("s.owner = '{}' and s.road = '{}'".format( axe[0], axe[1])) if sector: wheres.append( "ST_Intersects(s.geometry, sec.geometry) and sec.id = {}". format(sector)) where_str = '' if wheres: where_str = "where " + " and ".join(wheres) query_str = ("select distinct l.id_section from comptages.lane as l " "inner join comptages.installation as i on " "(l.id_installation = i.id) inner join " "comptages.count as c on (i.id = c.id_installation) " "inner join comptages.section as s on" "(l.id_section = s.id), comptages.sector as sec " "{};".format(where_str)) query.exec_(query_str) while query.next(): self.highlighted_sections.append(str(query.value(0)).strip())
def read_attributes(self): query = QSqlQuery(self.db) query_str = ( "select cou.remarks, sty.name, mdl.name, cla.name " "from comptages.count as cou " "join comptages.sensor_type as sty on cou.id_sensor_type = sty.id " "join comptages.model as mdl on cou.id_model = mdl.id " "join comptages.class as cla on cou.id_class = cla.id " "where cou.id = {} ".format(self.count_id)) query.exec_(query_str) query.next() self.attributes['remarks'] = query.value(0) self.attributes['sensor_type'] = query.value(1) self.attributes['model'] = query.value(2) self.attributes['class'] = query.value(3) query_str = ( "select sec.owner, sec.road, sec.way, sec.start_pr, sec.end_pr, " "sec.start_dist, sec.end_dist, sec.place_name, " "lan.direction, lan.direction_desc " "from comptages.section as sec " "inner join comptages.lane as lan on sec.id = lan.id_section " "where sec.id = '{}' ".format(self.section_id)) query.exec_(query_str) while query.next(): self.attributes['owner'] = query.value(0) self.attributes['road'] = query.value(1) self.attributes['way'] = query.value(2) self.attributes['start_pr'] = query.value(3) self.attributes['end_pr'] = query.value(4) self.attributes['start_dist'] = query.value(5) self.attributes['end_dist'] = query.value(6) self.attributes['place_name'] = query.value(7) if int(query.value(8)) == 1: self.attributes['dir1'] = query.value(9) elif int(query.value(8)) == 2: self.attributes['dir2'] = query.value(9)
def is_data_detail(self, count_id): self.init_db_connection() query = QSqlQuery(self.db) query_str = ("select id from comptages.count_detail " "where id_count = {}".format(count_id)) query.exec_(query_str) if query.next(): return True return False
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) query.next() if query.value(0) > 0: lyrWithElemList.append(lyr) return lyrWithElemList
def browseServer(self, dbList, host, port, user, password): """ Browses server for EDGV databases dbList: databases list host: server host ip address port: server port user: user name password: password """ canLoad = True if self.superNeeded: canLoad = False try: if self.serverWidget.abstractDb.checkSuperUser(): canLoad = True else: QMessageBox.warning( self, self.tr('Info!'), self. tr('Connection refused. Connect with a super user to inspect server.' )) return [] except Exception as e: QMessageBox.critical(self, self.tr('Critical!'), ':'.join(e.args)) if canLoad: progress = ProgressWidget( 1, len(dbList), self.tr('Loading databases from server... '), parent=self) progress.initBar() gen = self.factory.createSqlGenerator( driver=DsgEnums.DriverPostGIS) edvgDbList = [] for database in dbList: postgisDb = self.dbFactory.createDbFactory( DsgEnums.DriverPostGIS) postgisDb.connectDatabaseWithParameters( host, port, database, user, password) if not postgisDb.db.open(): qgis.utils.iface.messageBar().pushMessage( 'DB :' + database + '| msg: ' + postgisDb.db.lastError().databaseText(), level=Qgis.Critical) query = QSqlQuery(postgisDb.db) if query.exec_(gen.getEDGVVersion()): while query.next(): version = query.value(0) if version: edvgDbList.append((database, version)) progress.step() return edvgDbList
def countElements(self, layers): """ Counts the number of elements in each layer present in layers """ self.checkAndOpenDb() listaQuantidades = [] for layer in layers: (schema, className) = self.getTableSchema(layer) if layer.split('_')[-1].lower() in ['p', 'l', 'a' ] or schema == 'complexos': sql = self.gen.getElementCountFromLayer(layer) query = QSqlQuery(sql, self.db) query.next() number = query.value(0) if not query.exec_(sql): raise Exception( self.tr("Problem counting elements: ") + query.lastError().text()) listaQuantidades.append([layer, number]) return listaQuantidades
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 getDatabaseVersion(self): ''' Gets the database version ''' self.checkAndOpenDb() version = '2.1.3' sql = self.gen.getEDGVVersion() query = QSqlQuery(sql, self.db) # if not query.isActive(): # raise Exception(self.tr("Problem getting database version: ")+query.lastError().text()) while query.next(): version = query.value(0) return version
def check_sensor_of_lane(self, lane_id): """ Check id a lane is registered in the sensor table""" self.init_db_connection() query = QSqlQuery(self.db) query_str = ("select id from comptages.sensor where id_lane = {0};". format(lane_id)) query.exec_(query_str) if query.next(): return True return False
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()
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 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
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")