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