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))