def file_column_name(self): if self.obsids_from_selection is not None: if self.obsids_from_selection.isChecked(): return Obsids_from_selection() selected = ru(self.combobox.currentText()) if self.static_checkbox.isChecked(): selected = StaticValue(ru(self.combobox.currentText())) if self.notnull and not selected: raise utils.UsageError( ru( QCoreApplication.translate( u'ColumnEntry', u'Import error, the column %s must have a value')) % self.db_column) if selected and not self.static_checkbox.isChecked( ) and selected not in self.file_header: raise utils.UsageError( ru( QCoreApplication.translate( u'ColumnEntry', u'Import error, the chosen file column for the column %s did not exist in the file header.' )) % self.db_column) else: return selected
def get_data_from_qgislayer(self, w_qual_lab_layer): """ obsid, date_time, report, {parameter} || ', ' || CASE WHEN {unit} IS NULL THEN '' ELSE {unit} END, reading_txt """ fields = w_qual_lab_layer.fields() fieldnames = [field.name() for field in fields] columns = [ 'obsid', 'date_time', 'report', 'parameter', 'unit', 'reading_txt' ] for column in columns: if not column in fieldnames: raise utils.UsageError( ru( QCoreApplication.translate( 'CompactWqualReport', 'The chosen layer must contain column %s')) % column) indexes = {column: fields.indexFromName(column) for column in columns} data = {} for feature in w_qual_lab_layer.getSelectedFeatures(): attrs = feature.attributes() obsid = attrs[indexes['obsid']] date_time = attrs[indexes['date_time']] report = attrs[indexes['report']] parameter = attrs[indexes['parameter']] unit = attrs[indexes['unit']] reading_txt = attrs[indexes['reading_txt']] par_unit = ', '.join([x for x in [parameter, unit] if x]) data.setdefault(obsid, {}).setdefault(date_time, {}).setdefault( report, {})[par_unit] = reading_txt return data
def get_data_from_qgislayer(self, w_qual_lab_layer, columns): """ """ fields = w_qual_lab_layer.fields() fieldnames = [field.name() for field in fields] if any([column not in fieldnames for column in columns]): raise utils.UsageError( ru( QCoreApplication.translate( 'CompactWqualReport', 'The chosen layer must contain columns %s')) % str(columns)) indexes = {column: fields.indexFromName(column) for column in columns} features = [ f for f in w_qual_lab_layer.getFeatures('True') if f.id() in w_qual_lab_layer.selectedFeatureIds() ] file_data = [columns] file_data.extend( [[ru(feature.attributes()[indexes[col]]) for col in columns] for feature in features if feature.isValid()]) df = pd.DataFrame(file_data[1:], columns=file_data[0]) df['date_time'] = pd.to_datetime(df['date_time']) num_features = len(file_data) - 1 invalid_features = len(features) - num_features if invalid_features: msgfunc = utils.MessagebarAndLog.warning else: msgfunc = utils.MessagebarAndLog.info msgfunc(bar_msg=ru( QCoreApplication.translate( 'CompactWqualReport', 'Layer processed with %s selected features, %s read features and %s invalid features.' )) % (str(w_qual_lab_layer.selectedFeatureCount()), str(num_features), str(invalid_features))) return df
def drillreport(self): general_metadata = [ x for x in self.general_metadata.toPlainText().split('\n') if x ] geo_metadata = [ x for x in self.geo_metadata.toPlainText().split('\n') if x ] strat_columns = [ x for x in self.strat_columns.toPlainText().split('\n') if x ] header_in_table = self.header_in_table.isChecked() skip_empty = self.skip_empty.isChecked() include_comments = self.include_comments.isChecked() obsids = sorted( utils.getselectedobjectnames(qgis.utils.iface.activeLayer()) ) # selected obs_point is now found in obsid[0] general_metadata_header = self.general_metadata_header.text() geo_metadata_header = self.geo_metadata_header.text() strat_columns_header = self.strat_columns_header.text() comment_header = self.comment_header.text() empty_row_between_obsids = self.empty_row_between_obsids.isChecked() topleft_topright_colwidths = self.topleft_topright_colwidths.text( ).split(';') general_colwidth = self.general_colwidth.text().split(';') geo_colwidth = self.geo_colwidth.text().split(';') decimal_separator = self.decimal_separator.text() if not obsids: utils.MessagebarAndLog.critical(bar_msg=ru( QCoreApplication.translate( 'DrillreportUi', 'Must select at least 1 obsid in selected layer'))) raise utils.UsageError() self.save_stored_settings() drillrep = Drillreport(obsids, self.ms, general_metadata, geo_metadata, strat_columns, header_in_table, skip_empty, include_comments, general_metadata_header, geo_metadata_header, strat_columns_header, comment_header, empty_row_between_obsids, topleft_topright_colwidths, general_colwidth, geo_colwidth, decimal_separator)
def ask_for_stored_settings(self, stored_settings): old_string = utils.anything_to_string_representation( stored_settings, itemjoiner=',\n', pad=' ', dictformatter='{\n%s}', listformatter='[\n%s]', tupleformatter='(\n%s, )') msg = ru( QCoreApplication.translate( 'DrillreportUi', 'Replace the settings string with a new settings string.')) new_string = qgis.PyQt.QtWidgets.QInputDialog.getText( None, ru( QCoreApplication.translate('DrillreportUi', "Edit settings string")), msg, qgis.PyQt.QtWidgets.QLineEdit.Normal, old_string) if not new_string[1]: raise utils.UserInterruptError() new_string_text = ru(new_string[0]) if not new_string_text: return {} try: as_dict = ast.literal_eval(new_string_text) except Exception as e: utils.MessagebarAndLog.warning(bar_msg=ru( QCoreApplication.translate( 'DrillreportUi', 'Translating string to dict failed, see log message panel') ), log_msg=str(e)) raise utils.UsageError() else: return as_dict
def __init__(self, db_settings=None): """ Manuals for db connectors: https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/connector.py https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/postgis/connector.py https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/spatialite/connector.py """ self.conn = None self.cursor = None self.connector = None if db_settings is None: db_settings = QgsProject.instance().readEntry( "Midvatten", "database")[0] if isinstance(db_settings, str): #Test if the db_setting is an old database if os.path.isfile(db_settings): db_settings = {'spatialite': {'dbpath': db_settings}} else: if not db_settings: raise utils.UsageError( ru( QCoreApplication.translate( 'DbConnectionManager', 'Database setting was empty. Check DB tab in Midvatten settings.' ))) else: try: db_settings = ast.literal_eval(db_settings) except: raise utils.UsageError( ru( QCoreApplication.translate( 'DbConnectionManager', 'Database could not be set. Check DB tab in Midvatten settings.' ))) elif isinstance(db_settings, dict): # Assume it the dict is a valid db_settings dict. pass else: raise Exception( ru( QCoreApplication.translate( 'DbConnectionManager', "DbConnectionManager programming error: db_settings must be either a dict like {'spatialite': {'dbpath': 'x'} or a string representation of it. Was: %s" )) % ru(db_settings)) db_settings = ru(db_settings, keep_containers=True) self.db_settings = db_settings self.dbtype = list(self.db_settings.keys())[0] self.connection_settings = list(self.db_settings.values())[0] self.uri = QgsDataSourceUri() if self.dbtype == 'spatialite': self.dbpath = ru(self.connection_settings['dbpath']) if not os.path.isfile(self.dbpath): raise utils.UsageError( ru( QCoreApplication.translate( 'DbConnectionManager', 'Database error! File "%s" not found! Check db tab in Midvatten settings!' )) % self.dbpath) self.check_db_is_locked() #Create the database if it's not existing self.uri.setDatabase(self.dbpath) try: self.connector = spatialite_connector.SpatiaLiteDBConnector( self.uri) except Exception as e: utils.MessagebarAndLog.critical(bar_msg=ru( QCoreApplication.translate( 'DbConnectionManager', 'Connecting to spatialite db %s failed! Check that the file or path exists.' )) % self.dbpath, log_msg=ru( QCoreApplication.translate( 'DbConnectionManager', 'msg %s')) % str(e)) raise elif self.dbtype == 'postgis': connection_name = self.connection_settings['connection'].split( '/')[0] self.postgis_settings = get_postgis_connections()[connection_name] self.uri.setConnection(self.postgis_settings['host'], self.postgis_settings['port'], self.postgis_settings['database'], self.postgis_settings['username'], self.postgis_settings['password']) try: self.connector = postgis_connector.PostGisDBConnector(self.uri) except Exception as e: print(str(e)) if 'no password supplied' in str(e): utils.MessagebarAndLog.warning(bar_msg=ru( QCoreApplication.translate( 'DbConnectionManager', 'No password supplied for postgis connection'))) raise utils.UserInterruptError() else: raise if self.connector is not None: self.conn = self.connector.connection self.cursor = self.conn.cursor()
def start_import(self): """ TODO: I have NO IDEA where the dummy parameter is coming from. It gets the value False for some reason! :param dummy: :return: """ if self.file_data is None: raise utils.UsageError(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Error, must select a file first!'))) translation_dict = self.table_chooser.get_translation_dict() file_data = copy.deepcopy(self.file_data) dest_table = self.table_chooser.import_method foreign_keys = db_utils.get_foreign_keys(dest_table) foreign_key_obsid_tables = [tname for tname, colnames in foreign_keys.items() for colname in colnames if colname[0] == 'obsid'] if len(foreign_key_obsid_tables) == 1: foreign_key_obsid_table = foreign_key_obsid_tables[0] else: foreign_key_obsid_table = dest_table for file_column in list(translation_dict.keys()): alter_colnames = [] new_value = None # Check if obsid should be set from selection and add an obsid-column if so. if isinstance(file_column, Obsids_from_selection): selected = utils.get_selected_features_as_tuple() if len(selected) != 1: utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('GeneralCsvImportGui', 'Import error, must select 1 obsid')), duration=60) return 'cancel' alter_colnames = ['obsid'] new_value = selected[0] elif isinstance(file_column, StaticValue): if translation_dict[file_column]: alter_colnames = translation_dict[file_column] new_value = file_column.value for alter_colname in alter_colnames: if alter_colnames is not None and new_value is not None: try: colindex = file_data[0].index(alter_colname) except ValueError: colindex = len(file_data[0]) file_data[0].append(alter_colname) for row in file_data[1:]: if colindex + 1 < len(file_data[0]): row[colindex] = new_value else: row.append(new_value) #[row.insert(obsidindex, selected[0]) if obsidindex + 1 < len(file_data[0]) else row.append(selected[0]) for row in file_data[1:]] del translation_dict[file_column] translation_dict[alter_colname] = [alter_colname] columns_factors = self.table_chooser.get_columns_factors_dict() #Translate column names and add columns that appear more than once file_data = self.translate_and_reorder_file_data(file_data, translation_dict) file_data = self.convert_comma_to_points_for_double_columns(file_data, self.tables_columns_info[dest_table]) if columns_factors: file_data = self.multiply_by_factor(file_data, columns_factors) file_data = self.remove_preceding_trailing_spaces_tabs(file_data) if foreign_key_obsid_table and foreign_key_obsid_table != dest_table and 'obsid' in file_data[0]: file_data = utils.filter_nonexisting_values_and_ask(file_data, 'obsid', utils.get_all_obsids(foreign_key_obsid_table), try_capitalize=False) file_data = self.reformat_date_time(file_data) importer = import_data_to_db.midv_data_importer() answer = importer.general_import(dest_table=dest_table, file_data=file_data) utils.stop_waiting_cursor() if self.close_after_import.isChecked(): self.close()
def populate_postgis_db(self, verno, user_select_CRS='y', EPSG_code='4326'): dbconnection = db_utils.DbConnectionManager() db_settings = dbconnection.db_settings if not isinstance(db_settings, str): self.db_settings = ru(utils.anything_to_string_representation(dbconnection.db_settings)) else: self.db_settings = ru(db_settings) if dbconnection.dbtype != 'postgis': raise utils.UsageError('Database type postgis not selected, check Midvatten settings!') dbconnection.execute('CREATE EXTENSION IF NOT EXISTS postgis;') result = dbconnection.execute_and_fetchall('select version(), PostGIS_full_version();') versionstext = ', '.join(result[0]) utils.stop_waiting_cursor() set_locale = self.ask_for_locale() utils.start_waiting_cursor() if user_select_CRS=='y': utils.stop_waiting_cursor() EPSGID=str(self.ask_for_CRS(set_locale)[0]) utils.start_waiting_cursor() else: EPSGID=EPSG_code if EPSGID=='0' or not EPSGID: raise utils.UserInterruptError() filenamestring = "create_db.sql" SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filenamestring) qgisverno = Qgis.QGIS_VERSION#We want to store info about which qgis-version that created the db replace_word_replace_with = [ ('CHANGETORELEVANTEPSGID', ru(EPSGID)), ('CHANGETOPLUGINVERSION', ru(verno)), ('CHANGETOQGISVERSION', ru(qgisverno)), ('CHANGETODBANDVERSION', 'PostGIS version %s' % ru(versionstext)), ('CHANGETOLOCALE', ru(set_locale)), ('double', 'double precision'), ('"', ''), ('rowid as rowid', 'CTID as rowid'), ('POSTGIS ', '')] created_tables_sqls = {} with open(SQLFile, 'r') as f: f.readline() # first line is encoding info.... lines = [ru(line) for line in f] sql_lines = ['{};'.format(l) for l in ' '.join(lines).split(';') if l] for linenr, line in enumerate(sql_lines): if all([line, not line.startswith("#"), 'InitSpatialMetadata' not in line, 'SPATIALITE' not in line, line.replace(';', '').strip().replace('\n', '').replace('\r', '')]): sql = self.replace_words(line, replace_word_replace_with) try: dbconnection.execute(sql) except: try: print(str(sql)) print("numlines: " + str(len(sql_lines))) print("Error on line nr {}".format(str(linenr))) print("before " + sql_lines[linenr - 1]) if linenr + 1 < len(sql_lines): print("after " + sql_lines[linenr + 1 ]) except: pass raise else: _sql = sql.lstrip('\r').lstrip('\n').lstrip() if _sql.startswith('CREATE TABLE'): tablename = ' '.join(_sql.split()).split()[2] created_tables_sqls[tablename] = sql #lines = [self.replace_words(line.decode('utf-8').rstrip('\n').rstrip('\r'), replace_word_replace_with) for line in f if all([line,not line.startswith("#"), 'InitSpatialMetadata' not in line])] #db_utils.sql_alter_db(lines) self.insert_datadomains(set_locale, dbconnection) execute_sqlfile(get_full_filename('insert_obs_points_triggers_postgis.sql'), dbconnection) execute_sqlfile(get_full_filename('insert_functions_postgis.sql'), dbconnection) self.add_metadata_to_about_db(dbconnection, created_tables_sqls) dbconnection.vacuum() dbconnection.commit_and_closedb() """ #The intention is to keep layer styles in the database by using the class AddLayerStyles but due to limitations in how layer styles are stored in the database, I will put this class on hold for a while. #Finally add the layer styles info into the data base AddLayerStyles(dbpath) """ utils.stop_waiting_cursor()
def __init__(self, settingsdict, num_data_cols, rowheader_colwidth_percent, empty_row_between_tables, page_break_between_tables, from_active_layer, sql_table): #show the user this may take a long time... utils.start_waiting_cursor() self.nr_header_rows = 3 reportfolder = os.path.join(QDir.tempPath(), 'midvatten_reports') if not os.path.exists(reportfolder): os.makedirs(reportfolder) reportpath = os.path.join(reportfolder, "w_qual_report.html") f = codecs.open(reportpath, "wb", "utf-8") #write some initiating html rpt = r"""<head><title>%s</title></head>""" % ru( QCoreApplication.translate( 'Wqualreport', 'water quality report from Midvatten plugin for QGIS')) rpt += r""" <meta http-equiv="content-type" content="text/html; charset=utf-8" />""" #NOTE, all report data must be in 'utf-8' rpt += "<html><body>" f.write(rpt) if from_active_layer: utils.pop_up_info( ru( QCoreApplication.translate( 'CompactWqualReport', 'Check that exported number of rows are identical to expected number of rows!\nFeatures in layers from sql queries can be invalid and then excluded from the report!' )), 'Warning!') w_qual_lab_layer = qgis.utils.iface.activeLayer() if w_qual_lab_layer is None: raise utils.UsageError( ru( QCoreApplication.translate('CompactWqualReport', 'Must select a layer!'))) if not w_qual_lab_layer.selectedFeatureCount(): w_qual_lab_layer.selectAll() data = self.get_data_from_qgislayer(w_qual_lab_layer) else: data = self.get_data_from_sql(sql_table, utils.getselectedobjectnames()) report_data, num_data = self.data_to_printlist(data) utils.MessagebarAndLog.info(bar_msg=ru( QCoreApplication.translate( 'CompactWqualReport', 'Created report from %s number of rows.')) % str(num_data)) for startcol in range(1, len(report_data[0]), num_data_cols): printlist = [[row[0]] for row in report_data] for rownr, row in enumerate(report_data): printlist[rownr].extend( row[startcol:min(startcol + num_data_cols, len(row))]) filtered = [row for row in printlist if any(row[1:])] self.htmlcols = len(filtered[0]) self.WriteHTMLReport( filtered, f, rowheader_colwidth_percent, empty_row_between_tables=empty_row_between_tables, page_break_between_tables=page_break_between_tables) # write some finishing html and close the file f.write("\n</body></html>") f.close() utils.stop_waiting_cursor( ) # now this long process is done and the cursor is back as normal if report_data: QDesktopServices.openUrl(QUrl.fromLocalFile(reportpath))
def __init__(self, settingsdict, num_data_cols, rowheader_colwidth_percent, empty_row_between_tables, page_break_between_tables, from_active_layer, sql_table, sort_parameters_alphabetically, sort_by_obsid, date_time_as_columns, date_time_format, method, data_column): #show the user this may take a long time... reportfolder = os.path.join(QDir.tempPath(), 'midvatten_reports') if not os.path.exists(reportfolder): os.makedirs(reportfolder) reportpath = os.path.join(reportfolder, "w_qual_report.html") f = codecs.open(reportpath, "wb", "utf-8") #write some initiating html rpt = r"""<head><title>%s</title></head>""" % ru( QCoreApplication.translate( 'Wqualreport', 'water quality report from Midvatten plugin for QGIS')) rpt += r""" <meta http-equiv="content-type" content="text/html; charset=utf-8" />""" #NOTE, all report data must be in 'utf-8' rpt += "<html><body>" f.write(rpt) if date_time_as_columns: data_columns = [ 'obsid', 'date_time', 'report', 'parameter', 'unit', data_column ] else: data_columns = [ 'obsid', 'date_time', 'parameter', 'unit', data_column ] if from_active_layer: w_qual_lab_layer = qgis.utils.iface.activeLayer() if w_qual_lab_layer is None: raise utils.UsageError( ru( QCoreApplication.translate('CompactWqualReport', 'Must select a layer!'))) if not w_qual_lab_layer.selectedFeatureCount(): w_qual_lab_layer.selectAll() df = self.get_data_from_qgislayer(w_qual_lab_layer, data_columns) else: df = self.get_data_from_sql(sql_table, utils.getselectedobjectnames(), data_columns) if date_time_as_columns: columns = ['obsid', 'date_time', 'report'] rows = ['parunit'] values = [data_column] report_data = self.data_to_printlist( df, list(columns), list(rows), values, sort_parameters_alphabetically, sort_by_obsid, method, date_time_format) else: columns = ['obsid'] rows = ['parunit', 'date_time'] values = [data_column] report_data = self.data_to_printlist( df, list(columns), list(rows), values, sort_parameters_alphabetically, sort_by_obsid, method, date_time_format) # Split the data into separate tables with the specified number of columns for startcol in range(len(rows), len(report_data[0]), num_data_cols): printlist = [row[:len(rows)] for row in report_data] for rownr, row in enumerate(report_data): printlist[rownr].extend( row[startcol:min(startcol + num_data_cols, len(row))]) filtered = [row for row in printlist if any(row[len(rows):])] self.htmlcols = len(filtered[0]) self.write_html_table( filtered, f, rowheader_colwidth_percent, empty_row_between_tables=empty_row_between_tables, page_break_between_tables=page_break_between_tables, nr_header_rows=len(columns), nr_row_header_columns=len(rows)) # write some finishing html and close the file f.write("\n</body></html>") f.close() if report_data: QDesktopServices.openUrl(QUrl.fromLocalFile(reportpath))