def getAllInstalled(self, testLoad=True):
""" Build the localCache """
self.localCache = {}
# reversed list of the plugin paths: first system plugins -> then user plugins -> finally custom path(s)
pluginPaths = list(plugin_paths)
pluginPaths.reverse()
for pluginsPath in pluginPaths:
isTheSystemDir = (pluginPaths.index(pluginsPath) == 0
) # The curent dir is the system plugins dir
if isTheSystemDir:
# temporarily add the system path as the first element to force loading the readonly plugins, even if masked by user ones.
sys.path = [pluginsPath] + sys.path
try:
pluginDir = QDir(pluginsPath)
pluginDir.setFilter(QDir.AllDirs)
for key in pluginDir.entryList():
if key not in [".", ".."]:
path = QDir.convertSeparators(pluginsPath + "/" + key)
# readOnly = not QFileInfo(pluginsPath).isWritable() # On windows testing the writable status isn't reliable.
readOnly = isTheSystemDir # Assume only the system plugins are not writable.
# only test those not yet loaded. Loaded plugins already proved they're o.k.
# failedToLoad = settings.value("/PythonPlugins/watchDog/" + key) is not None
testLoadThis = testLoad and key not in qgis.utils.plugins
plugin = self.getInstalledPlugin(key,
path=path,
readOnly=readOnly,
testLoad=testLoadThis)
self.localCache[key] = plugin
if key in self.localCache.keys() and compareVersions(
self.localCache[key]["version_installed"],
plugin["version_installed"]) == 1:
# An obsolete plugin in the "user" location is masking a newer one in the "system" location!
self.obsoletePlugins += [key]
except:
# it's not necessary to stop if one of the dirs is inaccessible
pass
if isTheSystemDir:
# remove the temporarily added path
sys.path.remove(pluginsPath)
def getAllInstalled(self, testLoad=True):
""" Build the localCache """
self.localCache = {}
# reversed list of the plugin paths: first system plugins -> then user plugins -> finally custom path(s)
pluginPaths = list(plugin_paths)
pluginPaths.reverse()
for pluginsPath in pluginPaths:
isTheSystemDir = pluginPaths.index(pluginsPath) == 0 # The curent dir is the system plugins dir
if isTheSystemDir:
# temporarily add the system path as the first element to force loading the readonly plugins, even if masked by user ones.
sys.path = [pluginsPath] + sys.path
try:
pluginDir = QDir(pluginsPath)
pluginDir.setFilter(QDir.AllDirs)
for key in pluginDir.entryList():
if key not in [".", ".."]:
path = QDir.convertSeparators(pluginsPath + "/" + key)
# readOnly = not QFileInfo(pluginsPath).isWritable() # On windows testing the writable status isn't reliable.
readOnly = isTheSystemDir # Assume only the system plugins are not writable.
# only test those not yet loaded. Loaded plugins already proved they're o.k.
# failedToLoad = settings.value("/PythonPlugins/watchDog/" + key) is not None
testLoadThis = testLoad and key not in qgis.utils.plugins
plugin = self.getInstalledPlugin(key, path=path, readOnly=readOnly, testLoad=testLoadThis)
self.localCache[key] = plugin
if (
key in self.localCache.keys()
and compareVersions(self.localCache[key]["version_installed"], plugin["version_installed"])
== 1
):
# An obsolete plugin in the "user" location is masking a newer one in the "system" location!
self.obsoletePlugins += [key]
except:
# it's not necessary to stop if one of the dirs is inaccessible
pass
if isTheSystemDir:
# remove the temporarily added path
sys.path.remove(pluginsPath)
def load(self, layer, visible=True, collapsed_legend=False,
collapsed_group=True):
settings = QSettings("CatAIS", "VeriSO")
module_name = settings.value("project/appmodule")
provider = settings.value("project/provider")
db_host = settings.value("project/dbhost")
db_port = settings.value("project/dbport")
db_name = settings.value("project/dbname")
db_schema = settings.value("project/dbschema")
db_user = settings.value("project/dbuser")
db_pwd = settings.value("project/dbpwd")
db_admin = settings.value("project/dbadmin")
db_admin_pwd = settings.value("project/dbadminpwd")
epsg = settings.value("project/epsg")
if not db_schema:
self.message_bar.pushMessage("Error", tr(
"Missing database schema parameter."),
QgsMessageBar.CRITICAL, duration=0)
return
if not db_host:
self.message_bar.pushMessage("Error", tr(
"Missing database host parameter."), QgsMessageBar.CRITICAL,
duration=0)
return
if not db_name:
self.message_bar.pushMessage("Error", tr(
"Missing database name parameter."), QgsMessageBar.CRITICAL,
duration=0)
return
if not db_port:
self.message_bar.pushMessage("Error", tr(
"Missing database port parameter."), QgsMessageBar.CRITICAL,
duration=0)
return
if not db_user:
self.message_bar.pushMessage("Error", tr(
"Missing database user parameter."), QgsMessageBar.CRITICAL,
duration=0)
return
if not db_pwd:
self.message_bar.pushMessage("Error", tr(
"Missing database user password parameter."),
QgsMessageBar.CRITICAL, duration=0)
return
if not db_admin:
self.message_bar.pushMessage("Error", tr(
"Missing database administrator parameter."),
QgsMessageBar.CRITICAL, duration=0)
return
if not db_admin_pwd:
self.message_bar.pushMessage("Error", tr(
"Missing database administrator password parameter."),
QgsMessageBar.CRITICAL, duration=0)
return
if not provider:
self.message_bar.pushMessage("Error", tr(
"Missing provider parameter. Cannot load layer."),
QgsMessageBar.CRITICAL, duration=0)
return
if not module_name:
self.message_bar.pushMessage("Error", tr(
"Missing module name parameter. Cannot load layer."),
QgsMessageBar.CRITICAL, duration=0)
return
try:
# Postgres
if layer["type"] == "postgres":
featuretype = str(layer["featuretype"])
title = layer["title"]
key = str(layer["key"])
try:
readonly = (layer["readonly"])
except:
readonly = True
try:
geom = str(layer["geom"])
if geom == "":
geom = None
except:
geom = None
try:
style = str(layer["style"])
except:
style = ""
try:
group = str(layer["group"])
except:
group = None
try:
sql = str(layer["sql"])
except:
sql = ""
# Overwrite the active project settings/parameters to add
# *any* postgres layers.
try:
params = layer["params"]
provider = layer["type"]
db_host = params["dbhost"]
db_port = str(params["dbport"])
db_name = params["dbname"]
db_schema = params["dbschema"]
db_user = params["dbuser"]
db_pwd = params["dbpwd"]
db_admin = params["dbadmin"]
db_admin_pwd = params["dbadminpwd"]
except:
pass
uri = QgsDataSourceURI()
if readonly:
uri.setConnection(db_host, db_port, db_name, db_user,
db_pwd)
else:
uri.setConnection(db_host, db_port, db_name, db_admin,
db_admin_pwd)
uri.setDataSource(db_schema, featuretype, geom, sql, key)
my_layer = QgsVectorLayer(uri.uri(), title, provider)
# WMS / WMTS:
# WMTS is a bit ugly since we need to know the tileMatrixSet:
# Load layer manually in QGIS once an look for the tileMatrixSet
# in the layer properties.
elif layer["type"] in["wms", "wmts"]:
url = layer["url"]
title = layer["title"]
layers = layer["layers"]
format = layer["format"]
try:
tilematrixset = layer["tilematrixset"]
except:
tilematrixset = None
try:
crs = layer["crs"]
except:
crs = "EPSG:" + str(epsg)
try:
styles = layer["styles"]
except:
styles = ""
try:
group = layer["group"]
except:
group = None
try:
style = layer["style"]
except:
style = ""
my_layers = layers.split(",")
my_styles = styles.split(",")
layer_string = ""
style_string = ""
for my_layer in my_layers:
layer_string += "&layers=" + my_layer
# So werden einfach leere Styles requested.
# Korrekterweise wäre style=qml und wmsstyle = Style der
# vom WMS requested wird.
style_string += "&styles="
if layer["type"] == "wms":
uri = "IgnoreGetMapUrl=1&crs=" + crs + layer_string + \
style_string + "&format=" + format + "&url=" + url
else:
uri = "crs=" + crs + layer_string + style_string + \
"&format=" + format + "&tileMatrixSet=" + \
tilematrixset + "&url=" + url
my_layer = QgsRasterLayer(uri, title, "wms", False)
# local ogr and gdal formats
elif layer["type"] in ["gdal", "ogr"]:
title = layer["title"]
url = layer["url"]
try:
style = str(layer["style"])
except:
style = ""
try:
group = str(layer["group"])
except:
group = None
if layer["type"] == 'ogr':
my_layer = QgsVectorLayer(url, title, layer["type"])
else:
my_layer = QgsRasterLayer(url, title)
else:
self.message_bar.pushMessage(
"Error",
tr(
"Data provider not yet supported: ") + str(
layer["type"]), QgsMessageBar.CRITICAL,
duration=0)
return
if style != "":
if style.startswith('global_qml'):
qml_dir = "/plugins/veriso/"
else:
qml_dir = "/plugins/veriso/modules/%s/qml/" % \
module_name
"""qml_path = QDir.convertSeparators(QDir.cleanPath(
QgsApplication.qgisSettingsDirPath() +
qml_dir + style))"""
qml_path=QDir.convertSeparators(QDir.cleanPath(
(os.path.realpath(__file__)).split("plugins")[0] +qml_dir + style))
qml = QDir.convertSeparators(QDir.cleanPath(qml_path))
my_layer.loadNamedStyle(qml)
if not my_layer.isValid():
# str(title) throws some ascii out of range error...
self.message_bar.pushMessage("Error", title + tr(
" is not valid layer."), QgsMessageBar.CRITICAL,
duration=0)
return
else:
# QgsMapLayerRegistry.instance().addMapLayer(my_layer)
if group: # Layer soll in eine bestimmte Gruppe hinzugefügt
# werden.
QgsMapLayerRegistry.instance().addMapLayer(my_layer, False)
my_group_node = self.root.findGroup(group)
if not my_group_node: # Gruppe noch nicht vorhanden.
my_group_node = self.root.addGroup(group)
# Achtung: Das ist eher ein Workaround. Meines
# Erachtens hats noch einen Bug.
# Mit QgsMapLayerRegistry.instance().addMapLayer(
# my_layer, False) wird
# ein Layer noch nicht in die Legende gehängt.
# Anschliessend kann man ihn
# mit my_layer_node = self.root.addLayer(my_layer)
# der Legende hinzufügen.
# Das führt aber dazu, dass was mit dem MapCanvas
# nicht mehr stimmt, dh.
# .setExtent() funktioniert nicht mehr richtig. Wird
# der Layer jedoch direkt
# in die Legende gehängt, funktioniert .setExtent()
# tadellos. Jetzt wird halt
# momentan der Layer direkt eingehängt und
# anschliessend in die gewünschte
# Gruppe verschoben.
# Kleiner (positiver) Nebeneffekt: Der Layer ist
# defaultmässig ausgeschaltet.
#
# NEIN: Anscheinend ist es ein Problem wenn man dann
# layer_node.setVisible(Qt.Checked)
# macht. Dann funktionierts nicht mehr. -> Wieder
# zurückändern auf einfachere Methode.
# "Umweg": Hat Probleme gemacht, falls ein Gruppe
# "active" war. Dann wurden der neue
# Layer ebenfalls (zusätzlich) ihr hinzugefügt.
# print my_layer.id()
# my_layer_node = self.root.findLayer(
# my_layer.id())
# print my_layer_node
# cloned_layer = my_layer_node.clone()
# print cloned_layer
# my_group_node.insertChildNode(0,
# cloned_layer)
# self.root.removeChildNode(
# my_layer_node)
# my_layer_node = self.root.findLayer(
# my_layer.id()) # Layer bekommt neuen layer_node.
# "Direkt(er)"
my_layer_node = my_group_node.insertLayer(0, my_layer)
else:
QgsMapLayerRegistry.instance().addMapLayer(my_layer, False)
my_layer_node = self.root.addLayer(my_layer)
my_layer_node.setVisible(Qt.Unchecked)
my_layer_node.setCustomProperty("showFeatureCount",True)
if visible:
my_layer_node.setVisible(Qt.Checked)
if collapsed_legend:
my_layer_node.setExpanded(False)
else:
my_layer_node.setExpanded(True)
if 'my_group_node' in locals():
if collapsed_group:
my_group_node.setExpanded(False)
else:
my_group_node.setExpanded(True)
return my_layer
except Exception as e:
self.message_bar.pushMessage("Error", str(e),
QgsMessageBar.CRITICAL, duration=0)
QgsMessageLog.logMessage(str(e), "VeriSO", QgsMessageLog.CRITICAL)
return
def get_postprocessing_queries(self):
"""Gets the SQL queries that are stored in the sqlite database for
the postprocessing process which is done in postgis.
Language support: Everything that is not french or italian will be
german.
Returns:
False: If the queries could not be fetched from the sqlite
database. Otherwise a list with the SQL queries.
"""
# originial
""" filename = QDir.convertSeparators(QDir.cleanPath(
QgsApplication.qgisSettingsDirPath() +
"/python/plugins/veriso/modules/" + self.app_module +
"/postprocessing/postprocessing.db"))"""
# Hack
filename = QDir.convertSeparators(
QDir.cleanPath((os.path.realpath(__file__)).split("python")[0] +
"/python/plugins/veriso/modules/" +
self.app_module +
"/postprocessing/postprocessing.db"))
self.report_progress("Info: getting postprocessing queries...")
try:
# This is NOT the project db
connection_name = 'postprocessing_' + self.app_module
db = open_sqlite_db(filename, connection_name)
locale = QSettings().value('locale/userLocale')[0:2]
if locale == "fr":
lang = locale
elif locale == "it":
lang = locale
else:
lang = "de"
sql = "SELECT * FROM postprocessing " \
"WHERE (lang = '%s' " \
"OR lang IS NULL) AND apply = 1 " \
"ORDER BY 'order', ogc_fid;" % lang
query = db.exec_(sql)
if not query.isActive():
message = "Database query not active."
raise VerisoError(
message, long_message=QSqlQuery.lastError(query).text())
queries = []
record = query.record()
while next(query):
sql_query = str(query.value(record.indexOf("sql_query")))
sql_query = sql_query.replace("$$DBSCHEMA", self.db_schema)
sql_query = sql_query.replace("$$USER", self.db_user)
sql_query = sql_query.replace("$$EPSG", self.epsg)
queries.append(sql_query)
db.close()
del db
return queries
except Exception as e:
message = "Something went wrong while catching postprocessing " \
"queries from sqlite database. You need to delete the " \
"database schema manually."
raise VerisoError(message, e)
def update_projects_database(self):
"""Updates the sqlite projects database.
Returns:
False: When there an error occured. Otherswise True.
"""
error_message = ("Something went wrong while updating projects "
"database. You need to delete the database schema "
"manually.")
try:
# Create a new projects database if there is none (copy one from
# the templates).
if self.projects_database == "":
template = QDir.convertSeparators(
QDir.cleanPath(
QgsApplication.qgisSettingsDirPath() +
"/python/plugins/veriso/templates/template_projects"
".db"))
self.projects_database = QDir.convertSeparators(
QDir.cleanPath(self.projects_root_directory +
"/projects.db"))
shutil.copyfile(template, self.projects_database)
self.settings.setValue("options/general/projects_database",
self.projects_database)
db = get_projects_db()
project_root_directory = QDir.convertSeparators(
QDir.cleanPath(self.projects_root_directory + "/" +
str(self.db_schema)))
values = (self.db_schema, self.db_schema, self.db_host,
self.db_name, self.db_port, self.db_schema, self.db_user,
self.db_pwd, self.db_admin, self.db_admin_pwd, self.epsg,
self.ili, self.app_module, self.app_module_name,
self.projects_root_directory, project_root_directory,
self.data_date, self.notes, self.itf)
values = "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'," \
"'%s', '%s', 'postgres', '%s', '%s', '%s', '%s', '%s', " \
"'%s', '%s', '%s', '%s')" % values
sql = "INSERT INTO projects (id, displayname, dbhost, dbname, " \
"dbport, dbschema, dbuser, dbpwd, dbadmin, dbadminpwd, " \
"provider, epsg, ilimodelname, appmodule, appmodulename, " \
"projectrootdir, projectdir, datadate, notes, itf)" + values
query = db.exec_(sql)
if not query.isActive():
message = "Error while updating projects database."
raise VerisoError(
message, long_message=QSqlQuery.lastError(query).text())
db.close()
self.projectsDatabaseHasChanged.emit()
return True
except Exception as e:
raise VerisoError(error_message, e)
def run(self):
project_id = self.settings.value("project/id")
epsg = self.settings.value("project/epsg")
self.project_dir = self.settings.value("project/projectdir")
self.project_id = self.settings.value("project/id")
locale = QSettings().value('locale/userLocale')[
0:2] # this is for multilingual legends
if locale == "fr":
pass
elif locale == "it":
pass
else:
locale = "de"
if not project_id:
self.message_bar.pushCritical(
"Error",
_translate("VeriSO_PNF_Seltene_Objekte", "project_id not "
"set", None))
return
QApplication.setOverrideCursor(Qt.WaitCursor)
try:
group = _translate("VeriSO_PNF_Seltene_Objekte",
"Checklayer - Seltene Objekte", None)
group += " (" + str(project_id) + ")"
layer = {
"type":
"postgres",
"title":
_translate("VeriSO_PNF_Seltene_Objekte", "BB.BoFlaeche", None),
"featuretype":
"bodenbedeckung_boflaeche",
"geom":
"geometrie",
"key":
"ogc_fid",
"sql":
"",
"readonly":
True,
"group":
group,
"style":
"bb/bb_bb_plan.qml"
}
vlayer = self.layer_loader.load(layer, True, False)
layer = {
"type":
"postgres",
"title":
_translate("VeriSO_PNF_Seltene_Objekte", "EO.Einzelobjekt",
None),
"featuretype":
"einzelobjekte_einzelobjekt",
"geom":
"",
"key":
"ogc_fid",
"sql":
"",
"readonly":
True,
"group":
group
}
eoeolayer = self.layer_loader.load(layer, False, False)
layer = {
"type":
"postgres",
"title":
_translate("VeriSO_PNF_Seltene_Objekte", "EO.Flaechenelement",
None),
"featuretype":
"einzelobjekte_flaechenelement",
"geom":
"geometrie",
"key":
"ogc_fid",
"sql":
"",
"readonly":
True,
"group":
group,
"style":
"eo/eo_fl_bb_plan.qml"
}
eoflayer = self.layer_loader.load(layer, True, False)
if eoflayer <> False:
self.iface.legendInterface().setLayerVisible(eoflayer, True)
if eoeolayer <> False and eoflayer <> False:
joinLayerId = eoeolayer.id()
for feature in eoeolayer.getFeatures():
joinIdx = feature.fieldNameIndex("ogc_fid")
for t_feature in eoflayer.getFeatures():
targetIdx = t_feature.fieldNameIndex("flaechenelement_von")
joinInfo = QgsVectorJoinInfo()
joinInfo.joinFieldIndex = joinIdx
joinInfo.joinFieldName = "ogc_fid"
joinInfo.joinLayerId = joinLayerId
joinInfo.targetFieldIndex = targetIdx
joinInfo.targetFieldName = "flaechenelement_von"
joinInfo.memoryCache = True
eoflayer.addJoin(joinInfo)
layer = {
"type":
"postgres",
"title":
_translate("VeriSO_PNF_Seltene_Objekte", "EO.Linienelement",
None),
"featuretype":
"einzelobjekte_linienelement",
"geom":
"geometrie",
"key":
"ogc_fid",
"sql":
"",
"readonly":
True,
"group":
group,
"style":
"eo/eo_li_bb_plan.qml"
}
eolilayer = self.layer_loader.load(layer, True, False)
if eolilayer <> False:
self.iface.legendInterface().setLayerVisible(eolilayer, True)
if eoeolayer <> False and eolilayer <> False:
joinLayerId = eoeolayer.id()
joinProvider = eoeolayer.dataProvider()
joinProvider.attributeIndexes()
joinIdx = joinProvider.fieldNameIndex("ogc_fid")
targetProvider = eolilayer.dataProvider()
targetProvider.attributeIndexes()
targetIdx = targetProvider.fieldNameIndex("linienelement_von")
joinInfo = QgsVectorJoinInfo()
joinInfo.joinFieldIndex = joinIdx
joinInfo.joinFieldName = "ogc_fid"
joinInfo.joinLayerId = joinLayerId
joinInfo.targetFieldIndex = targetIdx
joinInfo.targetFieldName = "linienelement_von"
joinInfo.memoryCache = True
eolilayer.addJoin(joinInfo)
layer = {
"type":
"postgres",
"title":
_translate("VeriSO_PNF_Seltene_Objekte", "Li.Liegenschaft",
None),
"featuretype":
"liegenschaften_liegenschaft",
"geom":
"geometrie",
"key":
"ogc_fid",
"sql":
"",
"readonly":
True,
"group":
group,
"style":
"li/liegenschaft_ortho.qml"
}
vlayer = self.layer_loader.load(layer, True, False)
layer = {
"type":
"postgres",
"title":
_translate("VeriSO_PNF_Seltene_Objekte",
"BB.Boeschungsbauwerk", None),
"featuretype":
"t_boeschungbwerke",
"geom":
"the_geom",
"key":
"ogc_fid",
"sql":
"",
"readonly":
True,
"group":
group,
"style":
"checks/checklayer_punkt.qml"
}
vlayer = self.layer_loader.load(layer, True, False)
layer = {
"type":
"postgres",
"title":
_translate("VeriSO_PNF_Seltene_Objekte", "EO.Brunnen", None),
"featuretype":
"t_brunnen",
"geom":
"the_geom",
"key":
"ogc_fid",
"sql":
"",
"readonly":
True,
"group":
group,
"style":
"checks/checklayer_punkt.qml"
}
vlayer = self.layer_loader.load(layer, True, False)
QApplication.restoreOverrideCursor()
# Export feature count in excel file.
try:
settings = QSettings("CatAIS", "VeriSO")
module_name = settings.value("project/appmodule")
provider = settings.value("project/provider")
dbhost = settings.value("project/dbhost")
db_port = settings.value("project/dbport")
dbname = settings.value("project/dbname")
dbschema = settings.value("project/dbschema")
dbuser = settings.value("project/dbuser")
dbpwd = settings.value("project/dbpwd")
projectId = settings.value("project/id")
project_dir = settings.value("project/projectdir")
db = QSqlDatabase.addDatabase("QPSQL", "SelteneObjekte")
db.setDatabaseName(dbname)
db.setHostName(dbhost)
db.setUserName(dbuser)
db.setPassword(dbpwd)
if db.open() == False:
QMessageBox.critical(
None, "QGeoApp",
QCoreApplication.translate(
"QGeoApp", "Could not open database:\n") +
str(db.lastError().driverText()))
return
filename = QDir.convertSeparators(
QDir.cleanPath(
os.path.join(project_dir, "seltene_objekte.xlsx")))
# Create excel file.
wb = pycel.Workbook(filename)
#wb.country_code = 41
style1 = wb.add_format({'bold': True})
style2 = wb.add_format({'italic': True})
# Bodenbedeckung
sql = "SELECT CASE WHEN anz IS NULL THEN 0 ELSE anz END, bb_art.itfcode as art, bb_art.ilicode as art_txt "
sql += "FROM ("
sql += "SELECT count(art) as anz, art_txt, art"
sql += " FROM " + dbschema + ".bodenbedeckung_boflaeche"
sql += " WHERE art IN (7, 9, 19, 20, 21, 22, 30, 33, 35, 36, 37, 38, 39, 40)"
sql += " GROUP BY art, art_txt"
sql += ") bb"
sql += " FULL JOIN " + dbschema + ".bodenbedeckung_bbart bb_art ON bb_art.itfcode = bb.art"
sql += " WHERE bb_art.itfcode IN (7, 9, 19, 20, 21, 22, 30, 33, 35, 36, 37, 38, 39, 40)"
sql += " ORDER BY bb_art.itfcode;"
query = db.exec_(sql)
if query.isActive() == False:
QMessageBox.critical(
None, "QGeoApp",
QCoreApplication.translate(
"QGeoApp",
"Error occured while fetching data informations."))
return
ws = wb.add_worksheet(u'BB seltene Objekte')
ws.paper_size_code = 8
ws.print_centered_vert = False
ws.print_centered_horz = False
ws.top_margin = 1.0
ws.left_margin = 1.0
ws.bottom_margin = 1.0
ws.portrait = True
ws.write(0, 0, str("BB seltene Objekte: "), style1)
ws.write(0, 1, str(projectId))
ws.write(2, 0, str("Art"))
ws.write(2, 1, str("Art-Text"))
ws.write(2, 2, str("Anzahl"))
i = 0
record = query.record()
anz_index = record.indexOf("anz")
art_index = record.indexOf("art")
art_txt_index = record.indexOf("art_txt")
while query.next():
anz = str(query.value(anz_index))
art = str(query.value(art_index))
art_txt = str(query.value(art_txt_index))
ws.write(3 + i, 0, art)
ws.write(3 + i, 1, art_txt)
ws.write(3 + i, 2, anz)
i += 1
# Einzelobjekte
sql = "SELECT CASE WHEN anz IS NULL THEN 0 ELSE anz END, eo_art.itfcode as art, eo_art.ilicode as art_txt"
sql += " FROM"
sql += " ("
sql += " SELECT count(art) as anz, art_txt, art"
sql += " FROM " + dbschema + ".einzelobjekte_einzelobjekt"
sql += " WHERE art IN (9, 14, 15, 16, 17, 18, 23, 30, 31, 35, 36, 37, 38, 40, 42)"
sql += " GROUP BY art, art_txt"
sql += ") eo"
sql += " FULL JOIN " + dbschema + ".einzelobjekte_eoart eo_art ON eo_art.itfcode = eo.art"
sql += " WHERE eo_art.itfcode IN (9, 14, 15, 16, 17, 18, 23, 30, 31, 35, 36, 37, 38, 40, 42)"
sql += " ORDER BY eo_art.itfcode"
query = db.exec_(sql)
if query.isActive() == False:
QMessageBox.critical(
None, "QGeoApp",
QCoreApplication.translate(
"QGeoApp",
"Error occured while fetching data informations."))
return
ws = wb.add_worksheet(u'EO seltene Objekte')
ws.paper_size_code = 8
ws.print_centered_vert = False
ws.print_centered_horz = False
ws.top_margin = 1.0
ws.left_margin = 1.0
ws.bottom_margin = 1.0
ws.portrait = True
ws.write(0, 0, str("EO seltene Objekte: "), style1)
ws.write(0, 1, projectId)
ws.write(2, 0, str("Art"))
ws.write(2, 1, str("Art-Text"))
ws.write(2, 2, str("Anzahl"))
i = 0
record = query.record()
while query.next():
anz = str(query.value(record.indexOf("anz")))
art = str(query.value(record.indexOf("art")))
art_txt = str(query.value(record.indexOf("art_txt")))
ws.write(3 + i, 0, art)
ws.write(3 + i, 1, art_txt)
if int(anz) > 0:
ws.write(3 + i, 2, anz, style1)
else:
ws.write(3 + i, 2, anz)
i += 1
#file = QDir.convertSeparators(QDir.cleanPath(project_dir + os.sep + "seltene_objekte.xls"))
try:
wb.close()
QMessageBox.information(
None, "",
QCoreApplication.translate(
"QGeoAppModule.PNF", "File written:\n") + filename)
except IOError:
QMessageBox.warning(
None, "",
QCoreApplication.translate(
"QGeoAppModule.PNF",
"File <b>not</b> written!<br>") + filename)
return
db.close()
except:
QMessageBox.critical(
None, "QGeoApp",
QCoreApplication.translate(
"QGeoApp",
"Error exporting data from database to excel."))
except Exception:
QApplication.restoreOverrideCursor()
exc_type, exc_value, exc_traceback = sys.exc_info()
self.message_bar.pushMessage(
"Error",
str(traceback.format_exc(exc_traceback)),
level=QgsMessageBar.CRITICAL,
duration=0)
QApplication.restoreOverrideCursor()
def export_to_excel(self, vlayer):
try:
import xlsxwriter
except Exception as e:
self.message_bar.pushMessage("Error", str(e),
level=Qgis.Critical,
duration=0)
return
# Create excel file.
filename = QDir.convertSeparators(
QDir.cleanPath(
os.path.join(self.project_dir, "lfp3_pro_ts.xlsx")))
workbook = xlsxwriter.Workbook(filename)
fmt_bold = workbook.add_format({'bold': True, 'font_name': 'Cadastra'})
fmt_bold_border = workbook.add_format(
{'bold': True, 'border': 1, 'font_name': 'Cadastra'})
fmt_border = workbook.add_format(
{'border': 1, 'font_name': 'Cadastra'})
fmt_border_decimal = workbook.add_format(
{'border': 1, 'font_name': 'Cadastra', 'num_format': '0.00'})
fmt_header = workbook.add_format(
{'bg_color': '#CACACA', 'border': 1, 'font_name': 'Cadastra'})
fmt_italic = workbook.add_format(
{'italic': True, 'border': 1, 'font_name': 'Cadastra'})
fmt_sum = workbook.add_format({
'bold': True, 'font_color': 'blue',
'border': 1, 'font_name': 'Cadastra'
})
fmt_sum_decimal = workbook.add_format({
'bold': True,
'font_color': 'blue',
'border': 1,
'font_name': 'Cadastra',
'num_format': '0.00'
})
# Create the worksheet for the points defects.
worksheet = workbook.add_worksheet(
_translate("VeriSO_V+D_FP3", u'LFP3 pro TS', None))
worksheet.set_paper(9)
worksheet.set_portrait()
# Write project name into worksheet.
worksheet.write(0, 0, _translate("VeriSO_V+D_FP3", "Operat: ", None),
fmt_bold)
worksheet.write(0, 1, self.project_id, fmt_bold)
# Write headers.
worksheet.write(4, 0,
_translate("VeriSO_V+D_FP3", "Toleranzstufe", None),
fmt_header)
worksheet.write(4, 1,
_translate("VeriSO_V+D_FP3", "Fläche TS [ha]", None),
fmt_header)
worksheet.write(4, 2,
_translate("VeriSO_V+D_FP3", "Ist-Anzahl LFP3", None),
fmt_header)
worksheet.write(4, 3,
_translate("VeriSO_V+D_FP3", "Soll-Anzahl LFP3", None),
fmt_header)
worksheet.write(4, 4,
_translate("VeriSO_V+D_FP3", "Ist-Soll LFP3", None),
fmt_header)
# Loop through features and add them to worksheet.
iterator = vlayer.getFeatures()
j = 0
ts_idx = vlayer.fieldNameIndex("toleranzstufe")
area_idx = vlayer.fieldNameIndex("flaeche")
current_idx = vlayer.fieldNameIndex("ist_anzahl")
target_idx = vlayer.fieldNameIndex("soll_anzahl")
start_row = 5
sum_area = 0
sum_current = 0
sum_target = 0
sum_diff = 0
for feature in iterator:
ts = feature.attributes()[ts_idx]
area = feature.attributes()[area_idx]
current = feature.attributes()[current_idx]
target = feature.attributes()[target_idx]
worksheet.write(start_row + j, 0, ts, fmt_bold_border)
worksheet.write(start_row + j, 1, area, fmt_border_decimal)
worksheet.write(start_row + j, 2, current, fmt_border)
worksheet.write(start_row + j, 3, target, fmt_border)
worksheet.write(start_row + j, 4, (current - target), fmt_border)
sum_area += area
sum_current += current
sum_target += target
sum_diff += (current - target)
j += 1
# do not forget sum/total
worksheet.write(start_row + j, 0,
_translate("VeriSO_V+D_FP3", "Total", None),
fmt_bold_border)
worksheet.write(start_row + j, 1, sum_area, fmt_sum_decimal)
worksheet.write(start_row + j, 2, sum_current, fmt_sum)
worksheet.write(start_row + j, 3, sum_target, fmt_sum)
worksheet.write(start_row + j, 4, sum_diff, fmt_sum)
# Close excel file.
workbook.close()
def run(self):
try:
import xlsxwriter
except Exception as e:
self.message_bar.pushMessage("Error",
str(e),
level=QgsMessageBar.CRITICAL,
duration=0)
return
try:
settings = QSettings("CatAIS", "VeriSO")
module_name = settings.value("project/appmodule")
provider = settings.value("project/provider")
db_host = settings.value("project/dbhost")
db_port = settings.value("project/dbport")
db_name = settings.value("project/dbname")
db_schema = settings.value("project/dbschema")
db_user = settings.value("project/dbuser")
db_pwd = settings.value("project/dbpwd")
# db_admin = settings.value("project/dbadmin")
# db_admin_pwd = settings.value("project/dbadminpwd")
project_id = settings.value("project/id")
project_dir = settings.value("project/projectdir")
if not db_schema:
self.message_bar.pushCritical(
"Error", self.tr("Missing database schema parameter."))
return
if not db_host:
self.message_bar.pushCritical(
"Error", self.tr("Missing database host parameter."))
return
if not db_name:
self.message_bar.pushCritical(
"Error", self.tr("Missing database name parameter."))
return
if not db_port:
self.message_bar.pushCritical(
"Error", self.tr("Missing database port parameter."))
return
if not db_user:
self.message_bar.pushCritical(
"Error", self.tr("Missing database user parameter."))
return
if not db_pwd:
self.message_bar.pushCritical(
"Error",
self.tr("Missing database user password parameter."))
return
if not provider:
self.message_bar.pushCritical(
"Error",
self.tr("Missing provider parameter. Cannot load "
"layer."))
return
if not module_name:
self.message_bar.pushCritical(
"Error",
self.tr("Missing module name parameter. Cannot load "
"layer."))
return
uri = QgsDataSourceURI()
uri.setConnection(db_host, db_port, db_name, db_user, db_pwd)
uri.setDataSource(db_schema, "t_maengel", "the_geom", "",
"ogc_fid")
vlayer_points = QgsVectorLayer(uri.uri(), "Maengel (Punkte)",
"postgres")
uri = QgsDataSourceURI()
uri.setConnection(db_host, db_port, db_name, db_user, db_pwd)
uri.setDataSource(db_schema, "t_maengel_linie", "the_geom", "",
"ogc_fid")
vlayer_lines = QgsVectorLayer(uri.uri(), "Maengel (Linien)",
"postgres")
"""uri = QgsDataSourceURI()
uri.setConnection(db_host, db_port, db_name, db_user, db_pwd)
uri.setDataSource(db_schema, "t_maengel_polygon", "the_geom", "",
"ogc_fid")
vlayer_polygons = QgsVectorLayer(uri.uri(), "Maengel (Polygon)",
"postgres")"""
if not vlayer_points.isValid():
self.message_bar.pushMessage(
"Error",
tr("Could not load defects layer.", self.tr_tag, None),
level=QgsMessageBar.CRITICAL,
duration=0)
return
if not vlayer_lines.isValid():
self.message_bar.pushMessage(
"Error",
tr("Could not load defects layer.", self.tr_tag, None),
level=QgsMessageBar.CRITICAL,
duration=0)
return
"""if not vlayer_polygons.isValid():
self.message_bar.pushMessage("Error",
tr("Could not load defects layer.",
self.tr_tag,
None),
level=QgsMessageBar.CRITICAL,
duration=0)
return"""
"""if (vlayer_points.featureCount() == 0 and
vlayer_lines.featureCount() == 0 and
vlayer_polygons.featureCount() == 0)"""
if (vlayer_points.featureCount() == 0
and vlayer_lines.featureCount() == 0):
self.message_bar.pushInfo(
"Information",
tr("Defects layer are empty.", self.tr_tag, None))
return
# Create excel file.
filename = QDir.convertSeparators(
QDir.cleanPath(os.path.join(project_dir, "maengel.xlsx")))
workbook = xlsxwriter.Workbook(filename)
fmt_bold = workbook.add_format({'bold': True})
fmt_italic = workbook.add_format({'italic': True})
fmt_2dec = workbook.add_format({'num_format': '0.00'})
fmt_3dec = workbook.add_format({'num_format': '0.000'})
# 28/02/13 12:00
fmt_date = workbook.add_format({'num_format': 'dd/mm/yy hh:mm'})
# Create the worksheet for the points defects.
worksheet_points = workbook.add_worksheet(
tr(u'Mängelliste (Punkte)', self.tr_tag, None))
worksheet_points.set_paper(9)
worksheet_points.set_portrait()
# Write project name into worksheet.
worksheet_points.write_string(0, 0,
tr("Operat: ", self.tr_tag, None),
fmt_bold)
worksheet_points.write_string(0, 1, project_id, fmt_bold)
# Write defects. Loop through field to write header.
# Then loop through features.
provider = vlayer_points.dataProvider()
attrs = provider.fields()
# types = []
i = 0
for i in range(len(attrs)):
worksheet_points.write_string(4, i, str(attrs.at(i).name()),
fmt_italic)
# types.append(attrs.at(i).type())
worksheet_points.write_string(
4, i + 1, tr("Y-Koordinate", self.tr_tag, None), fmt_italic)
worksheet_points.write_string(
4, i + 2, tr("X-Koordinate", self.tr_tag, None), fmt_italic)
iterator = vlayer_points.getFeatures()
j = 0
for feat in iterator:
geom = feat.geometry()
point = geom.asPoint()
attrs = feat.attributes()
k = 0
for attr in attrs:
fmt = None
if type(attr) is QDate:
# this is to avoid:
# Unsupported type
# <class 'PyQt4.QtCore.QDateTime'> in write()
fmt = fmt_date
attr = attr.toPyDate()
worksheet_points.write_string(5 + j, k, str(attr), fmt)
k += 1
worksheet_points.write_number(5 + j, k, point.x(), fmt_3dec)
worksheet_points.write_number(5 + j, k + 1, point.y(),
fmt_3dec)
j += 1
# Create the worksheet for the line defects.
worksheet_lines = workbook.add_worksheet(
tr(u'Mängelliste (Linien)', self.tr_tag, None))
worksheet_lines.set_paper(9)
worksheet_lines.set_portrait()
# Write project name into worksheet.
worksheet_lines.write_string(0, 0, tr("Operat: ", self.tr_tag,
None), fmt_bold)
worksheet_lines.write_string(0, 1, project_id, fmt_bold)
# Write defects. Loop through field to write header.
# Then loop through features.
provider = vlayer_lines.dataProvider()
attrs = provider.fields()
# types = []
for i in range(len(attrs)):
worksheet_lines.write_string(4, i, str(attrs.at(i).name()),
fmt_italic)
# types.append(attrs.at(i).type())
worksheet_lines.write_string(4, i + 1,
tr("Y-Koordinate", self.tr_tag, None),
fmt_italic)
worksheet_lines.write_string(4, i + 2,
tr("X-Koordinate", self.tr_tag, None),
fmt_italic)
worksheet_lines.write_string(4, i + 3,
tr(u"Länge [hm]", self.tr_tag, None),
fmt_italic)
iterator = vlayer_lines.getFeatures()
j = 0
for feat in iterator:
geom = feat.geometry()
point = geom.vertexAt(0)
attrs = feat.attributes()
k = 0
for attr in attrs:
fmt = None
if type(attr) == QDate:
# this is to avoid:
# Unsupported type
# <class 'PyQt4.QtCore.QDateTime'> in write()
fmt = fmt_date
attr = attr.toPyDate()
worksheet_lines.write_string(5 + j, k, str(attr), fmt)
k += 1
worksheet_lines.write_number(5 + j, k, point.x(), fmt_3dec)
worksheet_lines.write_number(5 + j, k + 1, point.y(), fmt_3dec)
worksheet_lines.write_number(5 + j, k + 2, geom.length(),
fmt_2dec)
j += 1
"""# Create the worksheet for the polygon defects.
worksheet_polygons = workbook.add_worksheet(
tr(u'Mängelliste (Polygone)', self.tr_tag,
None))
worksheet_polygons.set_paper(9)
worksheet_polygons.set_portrait()
# Write project name into worksheet.
worksheet_polygons.write(0, 0,
tr("Operat: ", self.tr_tag,
None), fmt_bold)
worksheet_polygons.write(0, 1, project_id, fmt_bold)
# Write defects. Loop through field to write header.
# Then loop through features.
provider = vlayer_polygons.dataProvider()
attrs = provider.fields()
# types = []
for i in range(len(attrs)):
worksheet_polygons.write(4, i, str(attrs.at(i).name()),
fmt_italic)
# types.append(attrs.at(i).type())
worksheet_polygons.write(4, i + 1,
tr("Y-Koordinate", self.tr_tag, None),
fmt_italic)
worksheet_polygons.write(4, i + 2,
tr("X-Koordinate", self.tr_tag, None),
fmt_italic)
worksheet_polygons.write(4, i + 3,
tr(u"Fläche [m2]", self.tr_tag, None),
fmt_italic)
worksheet_polygons.write(4, i + 4,
tr(u"Umfang [hm]", self.tr_tag, None),
fmt_italic)
iterator = vlayer_polygons.getFeatures()
j = 0
for feat in iterator:
geom = feat.geometry()
point = geom.vertexAt(0)
attrs = feat.attributes()
k = 0
for attr in attrs:
fmt = None
if type(attr) == QDateTime:
# this is to avoid:
# Unsupported type
# <class 'PyQt4.QtCore.QDateTime'> in write()
fmt = fmt_date
attr = attr.toPyDateTime()
worksheet_polygons.write(5 + j, k, attr, fmt)
k += 1
worksheet_polygons.write(5 + j, k, point.x(), fmt_3dec)
worksheet_polygons.write(5 + j, k + 1, point.y(), fmt_3dec)
worksheet_polygons.write(5 + j, k + 2, geom.area(),
fmt_2dec)
worksheet_polygons.write(5 + j, k + 3, geom.length(),
fmt_2dec)
j += 1"""
# Close excel file.
workbook.close()
self.message_bar.pushInfo(
"Information",
tr("Defect(s) written: ", self.tr_tag, None) + str(filename))
except Exception as e:
message = "Error while writing defects file."
self.message_bar.pushMessage("Error",
tr(self.tr_tag, message, None),
level=QgsMessageBar.CRITICAL,
duration=0)
QgsMessageLog.logMessage(str(e), "VeriSO", QgsMessageLog.CRITICAL)
return
