def showSurvey(self):
#lyr = self.iface.activeLayer() # THIS IS TSPLOT-method, GETS THE SELECTED LAYER
lyr = self.layer
ids = lyr.selectedFeatureIds()
if len(ids) == 0:
utils.pop_up_info(ru(QCoreApplication.translate(' Stratigraphy', "No selection")), ru(QCoreApplication.translate(' Stratigraphy', "No features are selected")))
return
# initiate the datastore if not yet done
self.initStore()
utils.start_waiting_cursor() # Sets the mouse cursor to wait symbol
try: # return from store.getData is stored in data only if no object belonging to DataSanityError class is created
self.data = self.store.getData(ids, lyr) # added lyr as an argument!!!
except DataSanityError as e: # if an object 'e' belonging to DataSanityError is created, then do following
print("DataSanityError %s"%str(e))
utils.stop_waiting_cursor()
utils.pop_up_info(ru(QCoreApplication.translate(' Stratigraphy', "Data sanity problem, obsid: %s\n%s")) % (e.sond_id, e.message))
return
except Exception as e: # if an object 'e' belonging to DataSanityError is created, then do following
print("exception : %s"%str(e))
utils.stop_waiting_cursor()
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate(' Stratigraphy', "The stratigraphy plot failed, check Midvatten plugin settings and your data!")))
return
utils.stop_waiting_cursor() # Restores the mouse cursor to normal symbol
# show widget
w = SurveyDialog()
#w.widget.setData2_nosorting(data) #THIS IS IF DATA IS NOT TO BE SORTED!!
w.widget.setData(self.data) #THIS IS ONLY TO SORT DATA!!
w.show()
self.w = w # save reference so it doesn't get deleted immediately This has to be done both here and also in midvatten instance
def delete_selected_range(self, table_name):
""" Deletes the current selected range from the database from w_levels_logger
:return: De
"""
current_loaded_obsid = self.obsid
selected_obsid = self.load_obsid_and_init()
if current_loaded_obsid != selected_obsid:
utils.pop_up_info(ru(QCoreApplication.translate('Calibrlogger', "Error!\n The obsid selection has been changed but the plot has not been updated. No deletion done.\nUpdating plot.")))
self.update_plot()
return
elif selected_obsid is None:
utils.pop_up_info(ru(QCoreApplication.translate('Calibrlogger', "Error!\n No obsid was selected. No deletion done.\nUpdating plot.")))
self.update_plot()
return
fr_d_t = str((self.FromDateTime.dateTime().toPyDateTime() - datetime.datetime(1970,1,1)).total_seconds())
to_d_t = str((self.ToDateTime.dateTime().toPyDateTime() - datetime.datetime(1970,1,1)).total_seconds())
sql_list = []
sql_list.append(r"""DELETE FROM "%s" """%table_name)
sql_list.append(r"""WHERE obsid = '%s' """%selected_obsid)
# This %s is db formatting for seconds. It is not used as python formatting!
sql_list.append(r"""AND CAST(strftime('%s', date_time) AS NUMERIC) """)
sql_list.append(r""" > '%s' """%fr_d_t)
# This %s is db formatting for seconds. It is not used as python formatting!
sql_list.append(r"""AND CAST(strftime('%s', date_time) AS NUMERIC) """)
sql_list.append(r""" < '%s' """%to_d_t)
sql = ''.join(sql_list)
really_delete = utils.Askuser("YesNo", ru(QCoreApplication.translate('Calibrlogger', "Do you want to delete the period %s to %s for obsid %s from table %s?"))%(str(self.FromDateTime.dateTime().toPyDateTime()), str(self.ToDateTime.dateTime().toPyDateTime()), selected_obsid, table_name)).result
if really_delete:
utils.start_waiting_cursor()
db_utils.sql_alter_db(sql)
utils.stop_waiting_cursor()
self.update_plot()
def execute(self, sql, all_args=None):
"""
:param sql:
:param all_args: A list of lists of equal lenght to sql (if sql is a list) containing arguments for ? in the
corresponding sql.
:return:
"""
if isinstance(sql, str):
sql = [sql]
elif not isinstance(sql, (list, tuple)):
raise TypeError(ru(QCoreApplication.translate('DbConnectionManager', 'DbConnectionManager.execute: sql must be type string or a list/tuple of strings. Was %s'))%ru(type(sql)))
for idx, line in enumerate(sql):
if all_args is None:
try:
self.cursor.execute(line)
except Exception as e:
textstring = ru(QCoreApplication.translate('sql_load_fr_db', """DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % (ru(line), ru(str(e)))
utils.MessagebarAndLog.warning(
bar_msg=utils.sql_failed_msg(),
log_msg=textstring)
raise
elif isinstance(all_args, (list, tuple)):
args = all_args[idx]
try:
self.cursor.execute(line, args)
except Exception as e:
textstring = ru(QCoreApplication.translate('sql_load_fr_db', """DB error!\n SQL causing this error:%s\nusing args %s\nMsg:\n%s""")) % (ru(line), ru(args), ru(str(e)))
utils.MessagebarAndLog.warning(
bar_msg=utils.sql_failed_msg(),
log_msg=textstring)
raise
else:
raise TypeError(ru(QCoreApplication.translate('DbConnectionManager', 'DbConnectionManager.execute: all_args must be a list/tuple. Was %s')) % ru(type(all_args)))
def update_settings(self, _db_settings):
db_settings = None
if not _db_settings or _db_settings is None:
return
try:
db_settings = ast.literal_eval(_db_settings)
except:
utils.MessagebarAndLog.warning(log_msg=ru(QCoreApplication.translate('DatabaseSettings', 'Reading db_settings failed using string %s'))%_db_settings)
else:
pass
for setting in [db_settings, _db_settings]:
if isinstance(setting, str):
# Assume that the db_settings is an old spatialite database
if os.path.isfile(setting) and setting.endswith('.sqlite'):
db_settings = {'spatialite': {'dbpath': setting}}
break
if isinstance(db_settings, dict):
for dbtype, settings in db_settings.items():
self.dbtype_combobox = dbtype
self.choose_dbtype()
for setting_name, value in settings.items():
try:
if hasattr(self.db_settings_obj, str(setting_name)):
setattr(self.db_settings_obj, str(setting_name), value)
else:
utils.MessagebarAndLog.warning(log_msg=ru(QCoreApplication.translate('DatabaseSettings', "Databasetype %s didn' t have setting %s"))%(dbtype, setting_name))
except:
print(str(setting_name))
raise
else:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('DatabaseSettings', "Could not load database settings. Select database again!")), log_msg=ru(QCoreApplication.translate('DatabaseSettings', 'Tried to load db_settings string %s'))%_db_settings)
def load_from_active_layer(self, only_selected=False):
self.file_data = None
self.table_chooser.file_header = None
active_layer = utils.get_active_layer()
if not active_layer:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('GeneralCsvImportGui', 'Import error, no layer selected.')))
return None
if not only_selected:
active_layer.selectAll()
features = list(active_layer.getSelectedFeatures())
file_data = [[ru(field.name()) for field in active_layer.fields()]]
[file_data.append([ru(attr) if all([ru(attr).strip() != 'NULL' if attr is not None else '', attr is not None]) else '' for attr in feature]) for feature in features]
geometries = [feature.geometry().asWkt() if feature.geometry().asWkt() else None for feature in features]
if any(geometries):
geom_name = 'geometry'
while geom_name in file_data[0]:
geom_name += '_'
file_data[0].append(geom_name)
[file_data[idx+1].append(wkt) for idx, wkt in enumerate(geometries)]
self.file_data = file_data
self.table_chooser.file_header = file_data[0]
def import_foreign_keys(self, dbconnection, goal_table, temptablename, foreign_keys, existing_columns_in_temptable):
#TODO: Empty foreign keys are probably imported now. Must add "case when...NULL" to a couple of sql questions here
#What I want to do:
# import all foreign keys from temptable that doesn't already exist in foreign key table
# insert into fk_table (to1, to2) select distinct from1(cast as), from2(cast as) from temptable where concatted_from_and_case_when_null not in concatted_to_and_case_when_null
for fk_table, from_to_fields in foreign_keys.items():
from_list = [x[0] for x in from_to_fields]
to_list = [x[1] for x in from_to_fields]
if not all([_from in existing_columns_in_temptable for _from in from_list]):
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import of foreign keys failed, see log message panel')), log_msg=ru(QCoreApplication.translate('midv_data_importer', 'There were keys missing for importing to fk_table %s, so no import was done.'))%fk_table)
continue
nr_fk_before = dbconnection.execute_and_fetchall('''select count(*) from %s''' % fk_table)[0][0]
table_info = db_utils.db_tables_columns_info(table=fk_table, dbconnection=dbconnection)[fk_table]
column_headers_types = dict([(row[1], row[2]) for row in table_info])
null_replacement_string = 'NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL'
concatted_from_string = '||'.join(["CASE WHEN %s is NULL THEN '%s' ELSE %s END"%(x, null_replacement_string, x) for x in from_list])
concatted_to_string = '||'.join(["CASE WHEN %s is NULL THEN '%s' ELSE %s END"%(x, null_replacement_string, x) for x in to_list])
sql = u'INSERT INTO %s (%s) SELECT DISTINCT %s FROM %s AS b WHERE %s NOT IN (SELECT %s FROM %s) AND %s'%(fk_table,
u', '.join([u'"{}"'.format(k) for k in to_list]),
u', '.join([u'''CAST("b"."%s" as "%s")'''%(k, column_headers_types[to_list[idx]]) for idx, k in enumerate(from_list)]),
temptablename,
concatted_from_string,
concatted_to_string,
fk_table,
' AND '.join([''' b.{} IS NOT NULL and b.{} != '' '''.format(k, k, k) for k in from_list]))
dbconnection.execute(sql)
nr_fk_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % fk_table)[0][0]
if nr_fk_after > nr_fk_before:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total %s rows were imported to foreign key table %s while importing to %s.'))%(str(nr_fk_after - nr_fk_before), fk_table, goal_table))
def __init__(self, tables_columns):
super(DistinctValuesBrowser, self).__init__()
self.browser_label = qgis.PyQt.QtWidgets.QLabel(ru(QCoreApplication.translate('DistinctValuesBrowser', 'DB browser:')))
self.table_label = qgis.PyQt.QtWidgets.QLabel(ru(QCoreApplication.translate('DistinctValuesBrowser', 'Table')))
self._table_list = qgis.PyQt.QtWidgets.QComboBox()
self.column_label = qgis.PyQt.QtWidgets.QLabel(ru(QCoreApplication.translate('DistinctValuesBrowser', 'Column')))
self._column_list = qgis.PyQt.QtWidgets.QComboBox()
self.distinct_value_label = qgis.PyQt.QtWidgets.QLabel(ru(QCoreApplication.translate('DistinctValuesBrowser', 'Distinct values')))
self._distinct_value = qgis.PyQt.QtWidgets.QComboBox()
self._distinct_value.setEditable(True)
self._table_list.addItem('')
self._table_list.addItems(sorted(tables_columns.keys()))
self._table_list.currentIndexChanged.connect(
lambda: self.replace_items(self._column_list, tables_columns.get(self.table_list, [])))
self._column_list.currentIndexChanged.connect(
lambda: self.replace_items(self._distinct_value, self.get_distinct_values(self.table_list, self.column_list)))
for widget in [self.browser_label, self.table_label, self._table_list,
self.column_label, self._column_list, self.distinct_value_label,
self._distinct_value]:
self.layout.addWidget(widget)
def load_files(self):
charset = utils.ask_for_charset()
if not charset:
raise utils.UserInterruptError()
filename = utils.select_files(only_one_file=True, extension=ru(QCoreApplication.translate('GeneralCsvImportGui', "Comma or semicolon separated csv file %s;;Comma or semicolon separated csv text file %s;;Comma or semicolon separated file %s"))%('(*.csv)', '(*.txt)', '(*.*)'))
if isinstance(filename, (list, tuple)):
filename = filename[0]
filename = ru(filename)
delimiter = utils.get_delimiter(filename=filename, charset=charset, delimiters=[',', ';'])
self.file_data = self.file_to_list(filename, charset, delimiter)
header_question = utils.Askuser(question="YesNo", msg=ru(QCoreApplication.translate('GeneralCsvImportGui', """Does the file contain a header?""")))
utils.start_waiting_cursor()
if header_question.result:
# Remove duplicate header entries
header = self.file_data[0]
seen = set()
seen_add = seen.add
remove_cols = [idx for idx, x in enumerate(header) if x and (x in seen or seen_add(x))]
self.file_data = [[col for idx, col in enumerate(row) if idx not in remove_cols] for row in self.file_data]
self.table_chooser.file_header = self.file_data[0]
else:
header = ['Column ' + str(colnr) for colnr in range(len(self.file_data[0]))]
self.table_chooser.file_header = header
self.file_data.reverse()
self.file_data.append(header)
self.file_data.reverse()
utils.stop_waiting_cursor()
def sanityCheck(self, _surveys):
""" does a sanity check on retreived data """
surveys = {}
for (obsid, survey) in _surveys.items():
# check whether there's at least one strata information
if len(survey.strata) == 0:
#raise DataSanityError(str(obsid), "No strata information")
try:
print(str(obsid) + " has no strata information")
except:
pass
continue
#del surveys[obsid]#simply remove the item without strata info
else:
# check whether the depths are valid
top1 = survey.strata[0].depthTop
bed1 = survey.strata[0].depthBot
for strato in survey.strata[1:]:
# top (n) < top (n+1)
if top1 > strato.depthTop:
raise DataSanityError(str(obsid), ru(QCoreApplication.translate('SurveyStore', "Top depth is incorrect (%.2f > %.2f)")) % (top1, strato.depthTop))
# bed (n) < bed (n+1)
if bed1 > strato.depthBot:
raise DataSanityError(str(obsid), ru(QCoreApplication.translate('SurveyStore', "Bed depth is incorrect (%.2f > %.2f)")) % (bed1, strato.depthBot))
# bed (n) = top (n+1)
if bed1 != strato.depthTop:
raise DataSanityError(str(obsid), ru(QCoreApplication.translate('SurveyStore', "Top and bed depth don't match (%.2f != %.2f)")) % (bed1, strato.depthTop))
top1 = strato.depthTop
bed1 = strato.depthBot
surveys[obsid] = survey
return surveys
def print_selected_features(self):
""" Returns a list of obsid as unicode
thelayer is an optional argument, if not given then activelayer is used
"""
activelayer = utils.get_active_layer()
if activelayer is not self.activelayer:
self.reload_combobox()
utils.MessagebarAndLog.warning(
bar_msg=ru(QCoreApplication.translate('ValuesFromSelectedFeaturesGui', 'Column list reloaded. Select column and press Ok.')))
return None
self.selected_column = self.columns.currentText()
selected_values = utils.getselectedobjectnames(thelayer=self.activelayer, column_name=self.selected_column)
if not selected_values:
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('ValuesFromSelectedFeaturesGui',
'No features selected!')))
else:
if self.unique_sorted_list_checkbox.isChecked():
selected_values = sorted(set(selected_values))
nr = len(selected_values)
utils.MessagebarAndLog.info(bar_msg=ru(
QCoreApplication.translate('ValuesFromSelectedFeaturesGui',
'List of %s selected %s written to log'))%(str(nr), self.selected_column),
log_msg='{} IN ({})'.format(self.selected_column,
', '.join(["'{}'".format(value) if value is not None else 'NULL'
for value in selected_values])))
self.close()
def secplot_default_template():
loaded_template = {}
loaded_template['ticklabels_Text_set_fontsize'] = {'fontsize': 10}
loaded_template['Axes_set_xlabel'] = {
'xlabel': ru(QCoreApplication.translate('SectionPlot', "Distance along section")),
'fontsize': 10}
loaded_template['Axes_set_xlim'] = None # Tuple like (min, max)
loaded_template['Axes_set_ylim'] = None # Tuple like (min, max)
loaded_template['Axes_set_ylabel'] = {
'ylabel': ru(QCoreApplication.translate('SectionPlot', "Level, masl")),
'fontsize': 10}
loaded_template['dems_Axes_plot'] = {'DEFAULT': {'marker': 'None',
'linestyle': '-',
'linewidth': 1}}
loaded_template['drillstop_Axes_plot'] = {'marker': '^',
'markersize': 8,
'linestyle': '',
'color': 'black'}
loaded_template['geology_Axes_bar'] = {'edgecolor': 'black'}
loaded_template['grid_Axes_grid'] = {'b': True,
'which': 'both',
'color': '0.65',
'linestyle': '-'}
loaded_template['layer_Axes_annotate'] = {'xytext': (5, 0),
'textcoords': 'offset points',
'ha': 'left',
'va': 'center',
'fontsize': 9,
'bbox': {'boxstyle': 'square,pad=0.05',
'fc': 'white',
'edgecolor': 'white',
'alpha': 0.6}}
loaded_template['legend_Axes_legend'] = {'loc': 0,
'framealpha': 1,
'fontsize': 10}
loaded_template['legend_Text_set_fontsize'] = 10
loaded_template['legend_Frame_set_facecolor'] = '1'
loaded_template['legend_Frame_set_fill'] = False
loaded_template['obsid_Axes_annotate'] = {'xytext': (0, 10),
'textcoords': 'offset points',
'ha': 'center',
'va': 'top',
'fontsize': 9,
'rotation': 0,
'bbox': {'boxstyle': 'square,pad=0.05', 'fc': 'white',
'edgecolor': 'white', 'alpha': 0.4}}
loaded_template['obsid_Axes_bar'] = {'edgecolor': 'black',
'fill': False,
'linewidth': 0.5}
loaded_template['plot_height'] = None
loaded_template['plot_width'] = None
loaded_template[
'Figure_subplots_adjust'] = {} # {"top": 0.95, "bottom": 0.15, "left": 0.09, "right": 0.97}
loaded_template['wlevels_Axes_plot'] = {'DEFAULT': {'markersize': 6,
'marker': 'v',
'linestyle': '-',
'linewidth': 1}}
return loaded_template
def write_strat_data(self, strat_data, _strat_columns, table_header, strat_sql_columns_list, decimal_separator):
if table_header:
rpt = r"""<P><U><B><font size=3>%s</font></B></U></P>""" % table_header
else:
rpt = r''
strat_columns = [x.split(';')[0] for x in _strat_columns]
col_widths = [x.split(';')[1] if len(x.split(';')) == 2 else '1*' for x in _strat_columns]
rpt += r"""<TABLE style="font-family:'Ubuntu'; font-size:8pt; font-weight:400; font-style:normal;" WIDTH=100% BORDER=0 CELLPADDING=0 class="no-spacing" CELLSPACING=0>"""
for col_width in col_widths:
rpt += r"""<COL WIDTH={}>""".format(col_width)
rpt += r"""<p style="font-family:'Ubuntu'; font-size:8pt; font-weight:400; font-style:normal;">"""
headers_txt = OrderedDict([('stratid', ru(QCoreApplication.translate('Drillreport2_strat', 'Layer number'))),
('depth', ru(QCoreApplication.translate('Drillreport2_strat', 'level (m b gs)'))),
('depthtop', ru(QCoreApplication.translate('Drillreport2_strat', 'top of layer (m b gs)'))),
('depthbot', ru(QCoreApplication.translate('Drillreport2_strat', 'bottom of layer (m b gs)'))),
('geology', ru(QCoreApplication.translate('Drillreport2_strat', 'geology, full text'))),
('geoshort', ru(QCoreApplication.translate('Drillreport2_strat', 'geology, short'))),
('capacity', ru(QCoreApplication.translate('Drillreport2_strat', 'capacity'))),
('development', ru(QCoreApplication.translate('Drillreport2_strat', 'development'))),
('comment', ru(QCoreApplication.translate('Drillreport2_strat', 'comment')))])
if len(strat_data) > 0:
rpt += r"""<TR VALIGN=TOP>"""
for header in strat_columns:
rpt += r"""<TD><P><font size=2><u>{}</font></P></u></TD>""".format(headers_txt[header])
rpt += r"""</TR>"""
for rownr, row in enumerate(strat_data):
rpt += r"""<TR VALIGN=TOP>"""
for col in strat_columns:
if col == 'depth':
try:
depthtop_idx = strat_sql_columns_list.index('depthtop')
depthbot_idx = strat_sql_columns_list.index('depthbot')
except ValueError:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('Drillreport2',
'Programming error, depthtop and depthbot columns was supposed to exist')))
rpt += r"""<TD><P><font size=1> </font></P></TD>""".format(value)
else:
depthtop = '' if row[depthtop_idx] == 'NULL' else row[depthtop_idx].replace('.', decimal_separator)
depthbot = '' if row[depthbot_idx] == 'NULL' else row[depthbot_idx].replace('.', decimal_separator)
rpt += r"""<TD><P><font size=1>{}</font></P></TD>""".format(' - '.join([depthtop, depthbot]))
else:
value_idx = strat_sql_columns_list.index(col)
value = '' if row[value_idx] == 'NULL' else row[value_idx]
if col in ('depthtop', 'depthbot') and decimal_separator != '.':
value = value.replace('.', decimal_separator)
rpt += r"""<TD><P><font size=1>{}</font></P></TD>""".format(value)
rpt += r"""</TR>"""
rpt += r"""</p>"""
rpt += r"""</TABLE>"""
return rpt
def setlastcalibration(self, obsid):
if not obsid=='':
self.lastcalibr = self.getlastcalibration(obsid)
text = ru(QCoreApplication.translate('Calibrlogger', """There is no earlier known position for the logger in %s"""))%self.selected_obsid
if self.lastcalibr:
if all([self.lastcalibr[0][0], self.lastcalibr[0][1] is not None, self.lastcalibr[0][1] != '']):
text = ru(QCoreApplication.translate('Calibrlogger', "Last pos. for logger in %s was %s masl at %s"))%(obsid, str(self.lastcalibr[0][1]), str(self.lastcalibr[0][0]))
self.INFO.setText(text)
def replace_items(combobox, items):
combobox.clear()
combobox.addItem('')
try:
combobox.addItems(ru(items, keep_containers=True))
except TypeError:
for item in items:
combobox.addItem(ru(item))
def settings_strings_dialogs(self):
msg = ru(QCoreApplication.translate('ExportToFieldLogger', 'Edit the settings string for input fields browser and restart export fieldlogger dialog\nto load the change.'))
browser_updated = self.ask_and_update_settings([self.parameter_browser], self.stored_settingskey_parameterbrowser, msg)
msg = ru(QCoreApplication.translate('ExportToFieldLogger', 'Edit the settings string for input fields groups and restart export fieldlogger dialog\nto load the change.'))
groups_updated = self.ask_and_update_settings(self.parameter_groups, self.stored_settingskey, msg)
if browser_updated or groups_updated:
utils.pop_up_info(ru(QCoreApplication.translate('ExportToFieldLogger', 'Settings updated. Restart Export to Fieldlogger dialog\nor press "Save settings" to undo.')))
def calcselected(self):
obsids = ru(utils.getselectedobjectnames(self.layer), keep_containers=True)
if not obsids:
utils.pop_up_info(ru(QCoreApplication.translate('Calclvl',
'Adjustment aborted! No obsids selected.')),
ru(QCoreApplication.translate('Calclvl', 'Error')))
else:
self.calc(obsids)
def __init__(self, parent, settingsdict1={}, obsid=''):
utils.start_waiting_cursor()#show the user this may take a long time...
self.obsid = obsid
self.log_pos = None
self.y_pos = None
self.meas_ts = None
self.head_ts = None
self.head_ts_for_plot = None
self.level_masl_ts = None
self.loggerpos_masl_or_offset_state = 1
self.settingsdict = settingsdict1
qgis.PyQt.QtWidgets.QDialog.__init__(self, parent)
self.setAttribute(qgis.PyQt.QtCore.Qt.WA_DeleteOnClose)
self.setupUi(self) # Required by Qt4 to initialize the UI
self.setWindowTitle(ru(QCoreApplication.translate('Calibrlogger', "Calculate water level from logger"))) # Set the title for the dialog
self.INFO.setText(ru(QCoreApplication.translate('Calibrlogger', "Select the observation point with logger data to be adjusted.")))
self.log_calc_manual.setText("<a href=\"https://github.com/jkall/qgis-midvatten-plugin/wiki/4.-Edit-data\">Midvatten manual</a>")
# Create a plot window with one single subplot
self.calibrplotfigure = plt.figure()
self.axes = self.calibrplotfigure.add_subplot( 111 )
self.canvas = FigureCanvas( self.calibrplotfigure )
self.mpltoolbar = NavigationToolbar( self.canvas, self.widgetPlot )
self.layoutplot.addWidget( self.canvas )
self.layoutplot.addWidget( self.mpltoolbar )
self.show()
self.cid =[]
self.pushButtonSet.clicked.connect(lambda x: self.set_logger_pos())
self.pushButtonAdd.clicked.connect(lambda x: self.add_to_level_masl())
self.pushButtonFrom.clicked.connect(lambda x: self.set_from_date_from_x())
self.pushButtonTo.clicked.connect(lambda x: self.set_to_date_from_x())
self.L1_button.clicked.connect(lambda x: self.set_adjust_data('L1_date', 'L1_level'))
self.L2_button.clicked.connect(lambda x: self.set_adjust_data('L2_date', 'L2_level'))
self.M1_button.clicked.connect(lambda x: self.set_adjust_data('M1_date', 'M1_level'))
self.M2_button.clicked.connect(lambda x: self.set_adjust_data('M2_date', 'M2_level'))
self.pushButton_from_extent.clicked.connect(lambda: self.FromDateTime.setDateTime(num2date(self.axes.get_xbound()[0])))
self.pushButton_to_extent.clicked.connect(lambda: self.ToDateTime.setDateTime(num2date(self.axes.get_xbound()[1])))
self.pushButtonupdateplot.clicked.connect(lambda x: self.update_plot())
self.pushButtonLpos.clicked.connect(lambda x: self.catch_old_level())
self.pushButtonMpos.clicked.connect(lambda x: self.catch_new_level())
self.pushButtonMpos.setEnabled(False)
self.pushButtonCalcBestFit.clicked.connect(lambda x: self.logger_pos_best_fit())
self.pushButtonCalcBestFit.setToolTip(ru(QCoreApplication.translate('Calibrlogger', 'This will calibrate all values inside the chosen period\nusing the mean difference between head_cm and w_levels measurements.\n\nThe search radius is the maximum time distance allowed\n between a logger measurement and a w_level measurement.')))
self.pushButtonCalcBestFit2.clicked.connect(lambda x: self.level_masl_best_fit())
self.pushButtonCalcBestFit2.setToolTip(ru(QCoreApplication.translate('Calibrlogger', 'This will calibrate all values inside the chosen period\nusing the mean difference between level_masl and w_levels measurements.\n\nThe search radius is the maximum time distance allowed\n between a logger measurement and a w_level measurement.')))
self.pushButton_delete_logger.clicked.connect(lambda: self.delete_selected_range('w_levels_logger'))
self.adjust_trend_button.clicked.connect(lambda x: self.adjust_trend_func())
self.get_search_radius()
# Populate combobox with obsid from table w_levels_logger
self.load_obsid_from_db()
utils.stop_waiting_cursor()#now this long process is done and the cursor is back as normal
def set_combobox(combobox, value, add_if_not_exists=True):
index = combobox.findText(ru(value))
if index != -1:
combobox.setCurrentIndex(index)
else:
if add_if_not_exists:
combobox.addItem(ru(value))
index = combobox.findText(ru(value))
combobox.setCurrentIndex(index)
def catch_new_level(self):
""" Part of adjustment method 3. adjust level_masl by clicking in plot.
this part selects a y-position from the plot (normally user would select a manual measurement)."""
if self.log_pos is not None:
self.calib_help.setText(ru(QCoreApplication.translate('Calibrlogger', "Select a y position to move to.")))
self.cid.append(self.canvas.mpl_connect('button_press_event', self.set_y_pos_from_y_click))
self.calib_help.setText("")
else:
self.calib_help.setText(ru(QCoreApplication.translate('Calibrlogger', "Something wrong, click \"Current\" and try again.")))
def calcall(self):
obsids = db_utils.sql_load_fr_db("""SELECT DISTINCT obsid FROM w_levels""")[1]
if obsids:
obsids = [x[0] for x in obsids]
self.calc(obsids)
else:
utils.pop_up_info(ru(QCoreApplication.translate('Calclvl',
'Adjustment aborted! No obsids in w_levels.')),
ru(QCoreApplication.translate('Calclvl', 'Error')))
def locations_sublocations_obsids(self):
"""
:return: a list like [[obsid.locationsuffix as location, obsid.locationsuffix.sublocationsuffix as sublocation, obsid), ...]
"""
locations_sublocations_obsids = [('.'.join([x for x in [ru(obsid), ru(self.location_suffix)] if x]),
'.'.join([x for x in [ru(obsid), ru(self.location_suffix), ru(self.sublocation_suffix)] if x]), ru(obsid))
for obsid in set(self._obsid_list.get_all_data())]
return locations_sublocations_obsids
def calculate_median_value(table, column, obsid, dbconnection=None):
"""
Code from https://stackoverflow.com/questions/15763965/how-can-i-calculate-the-median-of-values-in-sqlite
:param table:
:param column:
:param obsid:
:param dbconnection:
:return:
"""
if not isinstance(dbconnection, DbConnectionManager):
dbconnection = DbConnectionManager()
if dbconnection.dbtype == 'spatialite':
data = {'column': column,
'table': table,
'obsid': obsid}
sql = '''SELECT AVG({column})
FROM (SELECT {column}
FROM {table}
WHERE obsid = '{obsid}'
ORDER BY {column}
LIMIT 2 - (SELECT COUNT(*) FROM {table} WHERE obsid = '{obsid}') % 2
OFFSET (SELECT (COUNT(*) - 1) / 2
FROM {table} WHERE obsid = '{obsid}'))'''.format(**data).replace('\n', ' ')
# median value old variant
#sql = r"""SELECT x.obsid, x.""" + column + r""" as median from (select obsid, """ + column + r""" FROM %s WHERE obsid = '"""%table
#sql += obsid
#sql += r"""' and (typeof(""" + column + r""")=typeof(0.01) or typeof(""" + column + r""")=typeof(1))) as x, (select obsid, """ + column + r""" FROM %s WHERE obsid = '"""%table
#sql += obsid
#sql += r"""' and (typeof(""" + column + r""")=typeof(0.01) or typeof(""" + column + r""")=typeof(1))) as y GROUP BY x.""" + column + r""" HAVING SUM(CASE WHEN y.""" + column + r""" <= x.""" + column + r""" THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND SUM(CASE WHEN y.""" + column + r""" >= x.""" + column + r""" THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1"""
ConnectionOK, median_value = sql_load_fr_db(sql, dbconnection)
try:
median_value = median_value[0][0]
except IndexError:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('calculate_median_value',
'Median calculation error, see log message panel')),
log_msg=ru(QCoreApplication.translate('calculate_median_value',
'Sql failed: %s')) % sql)
median_value = None
else:
sql = """SELECT median(u.%s) AS median_value FROM (SELECT %s FROM %s WHERE obsid = '%s') AS u;"""%(column, column, table, obsid)
ConnectionOK, median_value = sql_load_fr_db(sql, dbconnection)
try:
median_value = median_value[0][0]
except IndexError:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('calculate_median_value',
'Median calculation error, see log message panel')),
log_msg=ru(QCoreApplication.translate('calculate_median_value',
'Sql failed: %s')) % sql)
median_value = None
return median_value
def __init__(self, tables_columns_info, file_header, numeric_datatypes):
self.tables_columns_info = tables_columns_info
self.obsids_from_selection = None
self.file_header = file_header
self.db_column = tables_columns_info[1]
self.column_type = tables_columns_info[2]
self.notnull = int(tables_columns_info[3])
pk = int(tables_columns_info[5])
concatted_info = ', '.join([_x for _x in [self.column_type, 'not null' if self.notnull else False,
'primary key' if pk else False] if _x])
label = qgis.PyQt.QtWidgets.QLabel(' '.join(['Column ', self.db_column, '({})'.format(concatted_info)]))
self.column_widgets = [label]
self._all_widgets = [label]
self.combobox = qgis.PyQt.QtWidgets.QComboBox()
self.combobox.setEditable(True)
self.combobox.addItem('')
self.combobox.addItems(sorted(self.file_header, key=lambda s: s.lower()))
if self.db_column == 'obsid':
self.obsids_from_selection = qgis.PyQt.QtWidgets.QCheckBox(ru(QCoreApplication.translate('ColumnEntry', 'Obsid from qgis selection')))
self.obsids_from_selection.setToolTip(ru(QCoreApplication.translate('ColumnEntry', 'Select 1 obsid from obs_points or obs_lines attribute table or map.')))
self.obsids_from_selection.clicked.connect(lambda x: self.obsids_from_selection_checked())
self.obsid_widget = RowEntry()
self.obsid_widget.layout.addWidget(self.obsids_from_selection)
self.obsid_widget.layout.addWidget(self.combobox)
self._all_widgets.extend([self.obsids_from_selection, self.combobox, self.obsid_widget.widget])
self.column_widgets.append(self.obsid_widget.widget)
else:
self.column_widgets.append(self.combobox)
self._all_widgets.extend(self.column_widgets)
self.static_checkbox = qgis.PyQt.QtWidgets.QCheckBox()
self.static_checkbox.setToolTip(ru(QCoreApplication.translate('ColumnEntry', 'The supplied string will be written to the current column name for all\nimported rows instead of being read from file column.')))
self.column_widgets.append(self.static_checkbox)
self._all_widgets.append(self.static_checkbox)
self._factor = qgis.PyQt.QtWidgets.QLineEdit()
self._factor.setText('1')
self._factor.setToolTip(ru(QCoreApplication.translate('ColumnEntry', 'Multiply each imported value in the column with a factor.')))
self._factor.setFixedWidth(40)
self.column_widgets.append(self._factor)
self._all_widgets.append(self._factor)
if self.column_type not in numeric_datatypes:
self._factor.setVisible(False)
self.static_checkbox.clicked.connect(lambda x: self.static_checkbox_checked())
#This line prefills the columns if the header names matches the database column names
self.file_column_name = self.db_column
def __init__(self, tables_columns, parent=None):
qgis.PyQt.QtWidgets.QDialog.__init__(self, parent)
self.setupUi(self) # Required by Qt4 to initialize the UI
#Widgets:
# ------------------------------------------------------------------------------------
#Other widgets in the ui-file
self._input_field_list = ExtendedQPlainTextEdit(keep_sorted=True)
# ------------------------------------------------------------------------------------
self._parameter_table.addItem('')
self._parameter_table.addItems(sorted(tables_columns.keys()))
self._parameter_table.currentIndexChanged.connect(
lambda: self.replace_items(self._parameter_columns, tables_columns.get(self.parameter_table, [])))
self._parameter_columns.currentIndexChanged.connect(
lambda: self.replace_items(self._distinct_parameter, self.get_distinct_values(self.parameter_table, self.parameter_columns)))
self._unit_table.addItem('')
self._unit_table.addItems(sorted(tables_columns.keys()))
self._unit_table.currentIndexChanged.connect(
lambda: self.replace_items(self._unit_columns, tables_columns.get(self.unit_table, [])))
self._unit_columns.currentIndexChanged.connect(
lambda: self.replace_items(self._distinct_unit, self.get_distinct_values(self.unit_table, self.unit_columns)))
self._distinct_parameter.editTextChanged.connect(
lambda: self._combined_name.setText('.'.join([self.distinct_parameter, self.distinct_unit]) if self.distinct_parameter and self.distinct_unit else None))
self._distinct_unit.editTextChanged.connect(
lambda: self._combined_name.setText('.'.join([self.distinct_parameter, self.distinct_unit]) if self.distinct_parameter and self.distinct_unit else None))
self._add_button.clicked.connect(
lambda : self.combine_name(self.combined_name, self.input_type, self.hint))
# ------------------------------------------------------------------------------------
par_unit_tooltip = ru(QCoreApplication.translate('ParameterBrowser' ,
('(optional)\n'
'When both parameter and unit is given, they will be combined to create the input field name.')))
self._distinct_parameter.setToolTip(par_unit_tooltip)
self._distinct_unit.setToolTip(par_unit_tooltip)
self._combined_name.setToolTip(ru(QCoreApplication.translate('ExportToFieldLogger',
'(mandatory)\n'
'Either supply a chosen name directly or use parameter\n'
'and unit boxes to create a name.\n'
'ex: parameter.unit')))
self._input_type.addItem('')
self._input_type.addItems(['numberDecimal|numberSigned', 'text'])
self._input_type.setToolTip(ru(QCoreApplication.translate('ExportToFieldLogger',
'(mandatory)\n'
'Decides the keyboard layout in the Fieldlogger app.\n'
'numberDecimal|numberSigned: Decimals with allowed "-" sign\n'
'text: Text')))
self._hint.setToolTip(ru(QCoreApplication.translate('ParameterBrowser', '(optional)\nHint given to the Fieldlogger user for the parameter. Ex: "depth to water"')))
#------------------------------------------------------------------------------------
self._input_field_list.setToolTip(ru(QCoreApplication.translate('ParameterBrowser', 'Copy input fields to the "Input Fields" boxes using ctrl+c, ctrl+v.')))
self._input_field_list.sizePolicy().setHorizontalPolicy(qgis.PyQt.QtWidgets.QSizePolicy.Expanding)
self._input_field_list.setMinimumWidth(200)
#------------------------------------------------------------------------------------
self.horizontalLayout.addWidget(self._input_field_list)
def __init__(self, parent=None):
qgis.PyQt.QtWidgets.QDialog.__init__(self, parent)
self.resize(qgis.PyQt.QtCore.QSize(500,250))
self.setWindowFlags(qgis.PyQt.QtCore.Qt.Window | qgis.PyQt.QtCore.Qt.WindowMinimizeButtonHint | qgis.PyQt.QtCore.Qt.WindowMaximizeButtonHint | qgis.PyQt.QtCore.Qt.WindowCloseButtonHint);
self.setWindowTitle(ru(QCoreApplication.translate('SurveyDialog', "Identify Results")))
self.layout = qgis.PyQt.QtWidgets.QVBoxLayout(self)
self.layout.setMargin(5)
self.layout2 = qgis.PyQt.QtWidgets.QHBoxLayout()
self.widget = SurveyWidget()
self.layout.addWidget(self.widget)
self.radGeo = qgis.PyQt.QtWidgets.QRadioButton("Geo")
self.radGeo.setChecked(True) #Default is to show colors as per geo
self.layout2.addWidget(self.radGeo)
self.radHydro = qgis.PyQt.QtWidgets.QRadioButton("Hydro")
#self.radHydro.setChecked(False) #Default is NOT to show colors as per hydro
self.layout2.addWidget(self.radHydro)
spacerItem = qgis.PyQt.QtWidgets.QSpacerItem(100,0)
self.layout2.addItem(spacerItem)
self.chkShowDesc = qgis.PyQt.QtWidgets.QCheckBox(ru(QCoreApplication.translate('SurveyDialog', "Show text")))
self.chkShowDesc.setChecked(True)
self.layout2.addWidget(self.chkShowDesc)
self.GeologyOrCommentCBox = qgis.PyQt.QtWidgets.QComboBox(self)
self.GeologyOrCommentCBox.addItem('geology')
self.GeologyOrCommentCBox.addItem('comment')
self.GeologyOrCommentCBox.addItem('geoshort')
self.GeologyOrCommentCBox.addItem('hydro')
self.GeologyOrCommentCBox.addItem('hydro explanation')
self.GeologyOrCommentCBox.addItem('development')
self.layout2.addWidget(self.GeologyOrCommentCBox)
self.btnPrint = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('SurveyDialog', "Print")))
self.layout2.addWidget(self.btnPrint)
self.btnClose = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('SurveyDialog', "Close")))
self.layout2.addWidget(self.btnClose)
self.layout.addLayout(self.layout2)
self.btnClose.clicked.connect(lambda x: self.close())
self.btnPrint.clicked.connect(self.widget.printDiagram)
self.radGeo.toggled.connect( self.typeToggled)
self.radHydro.toggled.connect( self.typeToggled)
self.chkShowDesc.toggled.connect( self.widget.setShowDesc)
self.GeologyOrCommentCBox.currentIndexChanged.connect( partial(self.ComboBoxUpdated))
def write_printlist_to_file(printlist):
filename = utils.get_save_file_name_no_extension(parent=None, caption=ru(QCoreApplication.translate('ExportToFieldLogger', 'Choose a file name')), directory='', filter='csv (*.csv)')
if os.path.splitext(filename)[1] != '.csv':
filename += '.csv'
try:
with open(filename, 'w') as f:
f.write('\n'.join(printlist))
except IOError as e:
utils.pop_up_info(ru(QCoreApplication.translate('ExportToFieldLogger', "Writing of file failed!: %s "))%str(e))
except UnicodeDecodeError as e:
utils.pop_up_info(ru(QCoreApplication.translate('ExportToFieldLogger', "Error writing %s"))%str(printlist))
def ask_and_update_settings(self, objects_with_get_settings, settingskey, msg=''):
old_string = utils.anything_to_string_representation(self.update_stored_settings(objects_with_get_settings))
new_string = qgis.PyQt.QtWidgets.QInputDialog.getText(None, ru(QCoreApplication.translate('ExportToFieldLogger', "Edit settings string")), msg,
qgis.PyQt.QtWidgets.QLineEdit.Normal, old_string)
if not new_string[1]:
return False
new_string_text = ru(new_string[0])
self.update_settings(new_string_text, settingskey)
def get_search_radius(self):
""" Get the period search radius, default to 10 minutes """
if not self.bestFitSearchRadius.text():
search_radius = '10 minutes'
self.bestFitSearchRadius.setText(search_radius)
else:
search_radius = self.bestFitSearchRadius.text()
search_radius_splitted = ru(search_radius).split()
if len(search_radius_splitted) != 2:
utils.pop_up_info(ru(QCoreApplication.translate('Calibrlogger', "Must write time resolution also, ex. %s"))%'10 minutes')
return tuple(search_radius_splitted)
def update_from_stored_settings(self, stored_settings):
if isinstance(stored_settings, dict) and stored_settings:
for attr, val in stored_settings.items():
try:
selfattr = getattr(self, attr)
except:
pass
else:
if isinstance(selfattr, qgis.PyQt.QtWidgets.QPlainTextEdit):
if isinstance(val, (list, tuple)):
val = '\n'.join(val)
selfattr.setPlainText(val)
elif isinstance(selfattr, qgis.PyQt.QtWidgets.QCheckBox):
selfattr.setChecked(val)
elif isinstance(selfattr, qgis.PyQt.QtWidgets.QLineEdit):
selfattr.setText(val)
else:
# Settings:
# --------------
# The order and content of the geographical and general tables will follow general_metadata and geo_metadata list.
# All obs_points columns could appear here except geometry.
# The XY-reference system is added a bit down in the script to the list geo_data. The append has to be commented away
# if it's not wanted.
self.general_metadata.setPlainText('\n'.join(['type',
'h_tocags',
'material',
'diam',
'drillstop',
'screen',
'drilldate']))
self.geo_metadata.setPlainText('\n'.join(['east',
'north',
'ne_accur',
'ne_source',
'h_source',
'h_toc',
'h_accur']))
self.strat_columns.setPlainText('\n'.join(['depth',
'geology',
'geoshort',
'capacity',
'development',
'comment']))
self.general_metadata_header.setText(
ru(QCoreApplication.translate('Drillreport2', 'General information')))
self.geo_metadata_header.setText(
ru(QCoreApplication.translate('Drillreport2', 'Geographical information')))
self.strat_columns_header.setText(ru(QCoreApplication.translate('Drillreport2', 'Stratigraphy')))
self.comment_header.setText(ru(QCoreApplication.translate('Drillreport2', 'Comment')))
def XYTableUpdated(self):
"""This method is called whenever xy table is changed"""
# First, update comboboxes with columns
self.XYColumnsToComboBox(self.ListOfTables_2.currentText()) # For some reason it is not possible to send currentText with the SIGNAL-trigger
# Second, Make sure that column obsid exists
columns = self.LoadColumnsFromTable(self.ListOfTables_2.currentText()) # For some reason it is not possible to send currentText with the SIGNAL-trigger
if 'obsid' in columns:
text = "<font color=green>%s</font>"%ru(QCoreApplication.translate('midvsettingsdialogdock', 'Correct table! obsid column is found.'))
else:
text = "<font color=red>%s</font>"%ru(QCoreApplication.translate('midvsettingsdialogdock', 'Wrong table! obsid is missing.'))
self.InfoTxtXYPlot.setText(text)
self.ms.settingsdict['xytable']=self.ListOfTables_2.currentText()
self.ms.save_settings('xytable')#save this specific setting
def update_parameter_browser_using_stored_settings(stored_settings, parameter_browser):
if not stored_settings or stored_settings is None:
return
for index, attrs in stored_settings:
for attr in attrs:
try:
if hasattr(parameter_browser, ru(attr[0])):
setattr(parameter_browser, ru(attr[0]), ru(attr[1], keep_containers=True))
utils.MessagebarAndLog.warning(log_msg=ru(QCoreApplication.translate('ExportToFieldLogger',
'Tried to load input field fields browser but the variable %s did not exist.')) %
attr[0])
except UnicodeEncodeError:
utils.MessagebarAndLog.warning(log_msg=ru(QCoreApplication.translate('ExportToFieldLogger',
'Tried to load input field fields browser but the variable %s did not exist.')) %
attr[0])
def reformat_date_time(file_data):
try:
colnrs_to_convert = [file_data[0].index(u'date_time')]
except ValueError:
return file_data
else:
if colnrs_to_convert:
num_rows_before = len(file_data)
file_data = [[
date_utils.reformat_date_time(col)
if all([rownr > 0, colnr in colnrs_to_convert]) else col
for colnr, col in enumerate(row)
] for rownr, row in enumerate(file_data) if rownr == 0 or all([
date_utils.reformat_date_time(row[_colnr])
for _colnr in colnrs_to_convert
])]
num_rows_after = len(file_data)
num_removed_rows = num_rows_before - num_rows_after
if num_removed_rows > 0:
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate(
u'GeneralCsvImportGui',
u'%s rows without parsable date_time format skipped during import'
)) % str(num_removed_rows))
return file_data
def translate_and_reorder_file_data(file_data, translation_dict):
new_file_header = [
db_column
for file_column, db_columns in sorted(translation_dict.iteritems())
for db_column in sorted(db_columns)
]
file_column_index = dict([
(file_column, idx) for idx, file_column in enumerate(file_data[0])
])
new_file_data = [new_file_header]
#The loop "for db_column in sorted(db_columns)" is used for cases where one file column is sent to multiple database columns.
try:
new_file_data.extend([[
row[file_column_index[file_column]] for file_column, db_columns
in sorted(translation_dict.iteritems())
for db_column in sorted(db_columns)
] for rownr, row in enumerate(file_data[1:])])
except IndexError as e:
raise IndexError(
ru(
QCoreApplication.translate(
u'GeneralCsvImportGui',
u'Import error on row number %s:\n%s')) %
(str(rownr + 1), u'\n'.join(
[u': '.join(x) for x in zip(file_data[0], row)])))
return new_file_data
def ask_for_CRS(self, set_locale):
# USER MUST SELECT CRS FIRST!!
if set_locale == u'sv_SE':
default_crs = 3006
else:
default_crs = 4326
EPSGID = PyQt4.QtGui.QInputDialog.getInteger(
None, ru(QCoreApplication.translate(u'NewDb', "Select CRS")),
ru(
QCoreApplication.translate(
u'NewDb',
"Give EPSG-ID (integer) corresponding to\nthe CRS you want to use in the database:"
)), default_crs)
if not EPSGID[1]:
raise utils.UserInterruptError()
return EPSGID
def get_selected_lablitteras(self):
selected_lablitteras = [
ru(self.table.item(rownr, 0).text())
for rownr in xrange(self.table.rowCount())
if self.table.item(rownr, 0).isSelected()
]
return selected_lablitteras
def loadSettings(
self
): # settingsdict is a dictionary belonging to instance midvsettings. Must be stored and loaded here.
"""read plugin settings from QgsProject instance"""
self.settingsdict = self.createsettingsdict()
self.readingSettings = True
# map data types to function names
prj = QgsProject.instance()
functions = {
'str': prj.readEntry,
'str': prj.readEntry, # SIP API UPDATE 2.0
'int': prj.readNumEntry,
'float': prj.readDoubleEntry,
'bool': prj.readBoolEntry,
'datetime': prj.
readDoubleEntry, # we converted datetimes to float in writeSetting()
'list': prj.readListEntry, # SIP API UPDATE 2.0
'pyqtWrapperType':
prj.readListEntry # strange name for QStringList
}
output = {}
for (key, value) in self.settingsdict.items():
dataType = type(value).__name__
try:
func = functions[dataType]
output[key] = func("Midvatten", key)
self.settingsdict[key] = output[key][0]
except KeyError:
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate(
u'midvsettings',
u"Settings key %s does not exist in project file. Maybe this file was last used with old Midvatten plugin?"
)) % (str(key)))
self.readingSettings = False
self.settingsareloaded = True
def __init__(self, midvsettingsdialogdock, label_width):
super(PostgisSettings, self).__init__()
self.midvsettingsdialogdock = midvsettingsdialogdock
postgis_connections = db_utils.get_postgis_connections()
self.label = PyQt4.QtGui.QLabel(
ru(QCoreApplication.translate(u'PostgisSettings', u'Connections')))
self.label.setFixedWidth(label_width)
self._connection = PyQt4.QtGui.QComboBox()
self._connection.addItem(u'')
connection_names = [
u'/'.join([
k, u':'.join([v.get(u'host', u''),
v.get(u'port', u'')]),
v.get(u'database', u'')
]) for k, v in postgis_connections.iteritems()
]
self._connection.addItems(sorted(connection_names))
self.midvsettingsdialogdock.connect(
self._connection,
PyQt4.QtCore.SIGNAL("currentIndexChanged(const QString&)"),
self.set_db)
self.layout.addWidget(self.label, 0, 0)
self.layout.addWidget(self._connection, 0, 1)
def get_data_and_make_plot(self):
self.create_parameter_selection()
self.get_selected_observations()
if self.ms.settingsdict['piper_markers']=='type':
self.get_selected_obstypes()#gets unique plt.plottypes and a plt.plot type dictionary
self.create_markers()
elif self.ms.settingsdict['piper_markers']=='obsid' or self.ms.settingsdict['piper_markers']=='obsid but no legend':
self.create_markers()
elif self.ms.settingsdict['piper_markers']=='date_time':
self.get_selected_datetimes()
self.create_markers()
self.get_piper_data()
#here is a simple printout (to python console) to let the user see the piper plt.plot data
try:
print("""obsid, date_time, type, Cl_meqPl, HCO3_meqPl, SO4_meqPl, Na+K_meqPl, Ca_meqPl, Mg_meqPl""")
except:
pass
for row in self.obsrecarray:
#print ','.join([unicode(col).encode('utf-8') for col in row])
try:
# fix_print_with_import
print(','.join([ru(col) for col in row]))
except:
try:
# fix_print_with_import
print("failed printing piper data...")
except:
pass
self.plot_function()
def input_field_group_list(self, value):
value = ru(value, keep_containers=True)
if isinstance(value, (list, tuple)):
self._input_field_group_list.paste_data(paste_list=value)
else:
self._input_field_group_list.paste_data(
paste_list=value.split(u'\n'))
def get_settings(self):
settings = ((u'input_field_group_list', self.input_field_group_list),
(u'location_suffix', self.location_suffix),
(u'sublocation_suffix', self.sublocation_suffix))
settings = tuple((k, v) for k, v in settings if v)
return ru(settings, keep_containers=True)
def update_settings(self, new_string_text, settingskey):
try:
stored_settings = ast.literal_eval(new_string_text)
except SyntaxError, e:
stored_settings = []
utils.MessagebarAndLog.warning(
bar_msg=ru(
QCoreApplication.translate(
u'ExportToFieldLogger',
u'Parsing settings failed, see log message panel')),
log_msg=ru(
QCoreApplication.translate(
u'ExportToFieldLogger',
u'Parsing settings failed using string\n%s\n%s')) %
(new_string_text, str(e)))
return False
def create_parameter_groups_using_stored_settings(stored_settings,
connect):
"""
"""
if not stored_settings or stored_settings is None:
return []
parameter_groups = []
for index, attrs in stored_settings:
parameter_group = ParameterGroup(connect)
attrs_set = False
for attr in attrs:
if hasattr(parameter_group, attr[0].encode(u'utf-8')):
setattr(parameter_group, attr[0].encode(u'utf-8'), attr[1])
attrs_set = True
else:
utils.MessagebarAndLog.warning(log_msg=ru(
QCoreApplication.translate(
u'ExportToFieldLogger',
u'Tried to load input field groups but the variable %s did not exist.'
)) % attr[0])
if attrs_set:
parameter_groups.append(parameter_group)
return parameter_groups
def file_column_name(self, value):
if self.static_checkbox.isChecked():
self.combobox.setEditText(value)
else:
index = self.combobox.findText(ru(value))
if index != -1:
self.combobox.setCurrentIndex(index)
def write_html_table(self,
ReportData,
f,
rowheader_colwidth_percent,
empty_row_between_tables=False,
page_break_between_tables=False,
nr_header_rows=3,
nr_row_header_columns=1):
rpt = """<TABLE WIDTH=100% BORDER=1 CELLPADDING=1 class="no-spacing" CELLSPACING=0 PADDING-BOTTOM=0 PADDING=0>"""
f.write(rpt)
for counter, row in enumerate(ReportData):
row = ru(row, keep_containers=True)
try:
if counter < nr_header_rows:
rpt = "<tr>"
for idx in range(nr_row_header_columns):
rpt += "<TH WIDTH={}%><font size=1>{}</font></th>".format(
str(rowheader_colwidth_percent), row[idx])
data_colwidth = (100.0 -
(float(rowheader_colwidth_percent) *
nr_row_header_columns)) / len(
row[nr_row_header_columns:])
coltext = "<th width ={colwidth}%><font size=1>{data}</font></th>"
rpt += "".join([
coltext.format(**{
"colwidth": str(data_colwidth),
"data": x
}) for x in row[nr_row_header_columns:]
])
rpt += "</tr>\n"
else:
rpt = "<tr>"
for idx in range(nr_row_header_columns):
rpt += """<td align=\"left\"><font size=1>{}</font></td>""".format(
row[idx])
coltext = """<td align=\"right\"><font size=1>{}</font></td>"""
rpt += "".join([
coltext.format(x) for x in row[nr_row_header_columns:]
])
rpt += "</tr>\n"
except:
try:
print("here was an error: %s" % row)
except:
pass
f.write(rpt)
f.write("\n</table><p></p><p></p>")
#All in one table:Wqualreport
if empty_row_between_tables:
f.write("""<p>empty_row_between_tables</p>""")
#Separate tables:
if page_break_between_tables:
f.write("""<p style="page-break-before: always"></p>""")
def __init__(self, midvsettingsdialogdock, gridLayout_db):
self.midvsettingsdialogdock = midvsettingsdialogdock
self.layout = gridLayout_db
self.db_settings_obj = None
self.label_width = self.maximum_label_width()
self._label = PyQt4.QtGui.QLabel(
ru(
QCoreApplication.translate(u'DatabaseSettings',
u'Database type')))
self._label.setFixedWidth(self.label_width)
self._dbtype_combobox = PyQt4.QtGui.QComboBox()
self._dbtype_combobox.addItems([u'', u'spatialite', u'postgis'])
self.grid = gui_utils.RowEntryGrid()
self.grid.layout.addWidget(self._label, 0, 0)
self.grid.layout.addWidget(self._dbtype_combobox, 0, 1)
self.layout.addWidget(self.grid.widget)
self.child_widgets = []
self.midvsettingsdialogdock.connect(
self._dbtype_combobox,
PyQt4.QtCore.SIGNAL("currentIndexChanged(const QString&)"),
self.choose_dbtype)
self.layout.setRowStretch(self.layout.rowCount(), 1)
def maximum_label_width(self):
maximumwidth = 0
for label_name in [
ru(
QCoreApplication.translate('DatabaseSettings',
'Database type')),
ru(QCoreApplication.translate('DatabaseSettings',
'Select db')),
ru(
QCoreApplication.translate('DatabaseSettings',
'Connections'))
]:
testlabel = qgis.PyQt.QtWidgets.QLabel(label_name)
maximumwidth = max(maximumwidth, testlabel.sizeHint().width())
testlabel = None
return maximumwidth
def write_comment_data(self, comment_data, header):
if comment_data:
if header:
rpt = r"""<P><U><B><font size=3>{}</font></B></U></P>""".format(
header)
else:
rpt = r''
rpt += r"""<p style="font-family:'Ubuntu'; font-size:8pt; font-weight:400; font-style:normal;"><font size=1>"""
rpt += r'. '.join(
[ru(x) for x in comment_data if ru(x) not in ['', 'NULL']])
rpt += r"""</font></p>"""
else:
rpt = ''
return rpt
def calculateaveflow(self):
utils.start_waiting_cursor()
date_from = self.FromDateTime.dateTime().toPyDateTime()
date_to = self.ToDateTime.dateTime().toPyDateTime()
#Identify distinct set of obsid and instrumentid with Accvol-data and within the user-defined date_time-interval:
sql= """SELECT DISTINCT obsid, instrumentid FROM (SELECT * FROM w_flow WHERE flowtype = 'Accvol' AND date_time >= '%s' AND date_time <= '%s' AND obsid IN (%s))"""%(date_from,date_to, utils.sql_unicode_list(self.observations))
#utils.pop_up_info(sql)#debug
uniqueset = db_utils.sql_load_fr_db(sql)[1] # The unique set of obsid and instrumentid is kept in uniqueset
negativeflow = False
for pyobsid, pyinstrumentid in uniqueset:
sql= """select date_time, reading from w_flow where flowtype = 'Accvol' and obsid='%s' and instrumentid='%s' and date_time >='%s' and date_time <='%s' order by date_time"""%(pyobsid,pyinstrumentid,date_from,date_to)
recs = db_utils.sql_load_fr_db(sql)[1]
"""Transform data to a numpy.recarray"""
My_format = [('date_time', datetime.datetime), ('values', float)] #Define format with help from function datetime
table = np.array(recs, dtype=My_format) #NDARRAY
table2=table.view(np.recarray) # RECARRAY Makes the two columns into callable objects, i.e. write table2.values
for j, row in enumerate(table2):#This is where Aveflow is calculated for each obs and also written to db
if j>0:#first row is "start-value" for Accvol and there is no Aveflow to be calculated
Volume = (table2.values[j] - table2.values[j-1])*1000#convert to L since Accvol is supposed to be in m3
""" Get help from function datestr2num to get date and time into float"""
DeltaTime = 24*3600*(datestr2num(table2.date_time[j]) - datestr2num(table2.date_time[j-1]))#convert to seconds since numtime is days
Aveflow = Volume/DeltaTime#L/s
if Aveflow<0:
negativeflow = True
sql = """insert or ignore into w_flow(obsid,instrumentid,flowtype,date_time,reading,unit) values('%s','%s','Aveflow','%s','%s','l/s')"""%(pyobsid,pyinstrumentid,table2.date_time[j],Aveflow)
db_utils.sql_alter_db(sql)
if negativeflow:
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('Calcave', "Please notice that negative flow was encountered.")))
utils.stop_waiting_cursor()
self.close()
def export_2_splite(self, target_db, dest_srid):
"""
Exports a datagbase to a new spatialite database file
:param target_db: The name of the new database file
:param dest_srid:
:return:
"""
self.source_dbconnection = db_utils.DbConnectionManager()
self.source_dbconnection.connect2db() #establish connection to the current midv db
self.dest_dbconnection = db_utils.DbConnectionManager(target_db)
self.dest_dbconnection.connect2db()
self.midv_data_importer = midv_data_importer()
self.write_data(self.to_sql, None, defs.get_subset_of_tables_fr_db(category='data_domains'), replace=True)
self.dest_dbconnection.commit()
self.write_data(self.to_sql, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'))
self.dest_dbconnection.commit()
self.write_data(self.to_sql, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'))
self.dest_dbconnection.commit()
db_utils.delete_srids(self.dest_dbconnection.cursor, dest_srid)
self.dest_dbconnection.commit()
#Statistics
statistics = self.get_table_rows_with_differences()
self.dest_dbconnection.cursor.execute('vacuum')
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('ExportData', "Export done, see differences in log message panel")), log_msg=ru(QCoreApplication.translate('ExportData', "Tables with different number of rows:\n%s"))%statistics)
self.dest_dbconnection.commit_and_closedb()
self.source_dbconnection.closedb()
def w_flow_flowtypes_units():
sql = 'select distinct flowtype, unit from w_flow'
connection_ok, result_dict = db_utils.get_sql_result_as_dict(sql)
if not connection_ok:
utils.MessagebarAndLog.warning(
bar_msg=QCoreApplication.translate(
u'w_flow_flowtypes_units',
u"Error, sql failed, see log message panel"),
log_msg=ru(
QCoreApplication.translate(u'w_flow_flowtypes_units',
u'Cannot get data from sql %s')) %
ru(sql))
return {}
return ru(result_dict, keep_containers=True)
def check_and_delete_stratigraphy(self, existing_columns, dbconnection):
if all(['stratid' in existing_columns, 'depthtop' in existing_columns, 'depthbot' in existing_columns]):
skip_obsids = []
obsid_strat = db_utils.get_sql_result_as_dict('select obsid, stratid, depthtop, depthbot from %s' % self.temptable_name, dbconnection)[1]
for obsid, stratid_depthbot_depthtop in obsid_strat.items():
#Turn everything to float
try:
strats = [[float(x) for x in y] for y in stratid_depthbot_depthtop]
except (ValueError, TypeError) as e:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'ValueError: %s. Obsid "%s", stratid: "%s", depthbot: "%s", depthtop: "%s"')) % (str(e), obsid, stratid_depthbot_depthtop[0], stratid_depthbot_depthtop[1], stratid_depthbot_depthtop[2]))
sorted_strats = sorted(strats, key=itemgetter(0))
stratid_idx = 0
depthtop_idx = 1
depthbot_idx = 2
for index in range(len(sorted_strats)):
if index == 0:
continue
#Check that there is no gap in the stratid:
if float(sorted_strats[index][stratid_idx]) - float(sorted_strats[index - 1][stratid_idx]) != 1:
utils.MessagebarAndLog.warning(bar_msg=self.import_error_msg(), log_msg=ru(QCoreApplication.translate('midv_data_importer', 'The obsid %s will not be imported due to gaps in stratid'))%obsid)
skip_obsids.append(obsid)
break
#Check that the current depthtop is equal to the previous depthbot
elif sorted_strats[index][depthtop_idx] != sorted_strats[index - 1][depthbot_idx]:
utils.MessagebarAndLog.warning(bar_msg=self.import_error_msg(), log_msg=ru(QCoreApplication.translate('midv_data_importer', 'The obsid %s will not be imported due to gaps in depthtop/depthbot'))%obsid)
skip_obsids.append(obsid)
break
if skip_obsids:
dbconnection.execute('delete from %s where obsid in (%s)' % (self.temptable_name, ', '.join(["'{}'".format(obsid) for obsid in skip_obsids])))
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_table_info(tablename, dbconnection=None):
if not isinstance(dbconnection, DbConnectionManager):
dbconnection = DbConnectionManager()
if dbconnection.dbtype == 'spatialite':
columns_sql = """PRAGMA table_info ('%s')""" % (tablename)
try:
columns = dbconnection.execute_and_fetchall(columns_sql)
except Exception as e:
utils.MessagebarAndLog.warning(
bar_msg=utils.sql_failed_msg(),
log_msg=ru(
QCoreApplication.translate('get_table_info',
'Sql failed: %s\msg:%s')) %
(columns_sql, str(e)))
return None
else:
columns_sql = "SELECT ordinal_position, column_name, data_type, CASE WHEN is_nullable = 'NO' THEN 1 ELSE 0 END AS notnull, column_default, 0 AS primary_key FROM information_schema.columns WHERE table_schema = '%s' AND table_name = '%s'" % (
dbconnection.schemas(), tablename)
columns = [
list(x) for x in dbconnection.execute_and_fetchall(columns_sql)
]
primary_keys = [
x[0] for x in dbconnection.execute_and_fetchall(
"SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = '%s'::regclass AND i.indisprimary;"
% tablename)
]
for column in columns:
if column[1] in primary_keys:
column[5] = 1
columns = [tuple(column) for column in columns]
return columns
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 and not self._ignore_not_null_checkbox.isChecked():
raise utils.UsageError(ru(QCoreApplication.translate('ColumnEntry', '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('ColumnEntry', 'Import error, the chosen file column for the column %s did not exist in the file header.'))%self.db_column)
else:
return selected
def db_tables_columns_info(table=None, dbconnection=None):
"""Returns a dict like {'tablename': (ordernumber, name, type, notnull, defaultvalue, primarykey)}"""
if not isinstance(dbconnection, DbConnectionManager):
dbconnection = DbConnectionManager()
existing_tablenames = get_tables(dbconnection=dbconnection)
if table is not None:
if table not in existing_tablenames:
return {}
if table is None:
tablenames = existing_tablenames
elif not isinstance(table, (list, tuple)):
tablenames = [table]
else:
tablenames = table
tables_dict = {}
for tablename in tablenames:
columns = get_table_info(tablename, dbconnection=dbconnection)
if columns is None:
utils.MessagebarAndLog.warning(log_msg=ru(
QCoreApplication.translate(
'db_tables_columns_info',
'Getting columns from table %s failed!')) % (tablename))
continue
tables_dict[tablename] = columns
return tables_dict
def __init__(self, tables_columns, file_header=None):
super(ImportTableChooser, self).__init__()
self.tables_columns = tables_columns
self.file_header = file_header
self.columns = []
self.numeric_datatypes = db_utils.numeric_datatypes()
chooser = RowEntry()
self.label = qgis.PyQt.QtWidgets.QLabel(ru(QCoreApplication.translate('ImportTableChooser', 'Import to table')))
self.__import_method = qgis.PyQt.QtWidgets.QComboBox()
self.__import_method.addItem('')
self.__import_method.addItems(sorted(list(tables_columns.keys()), key=lambda s: s.lower()))
self.__import_method.currentIndexChanged.connect( self.choose_method)
for widget in [self.label, self.__import_method]:
chooser.layout.addWidget(widget)
chooser.layout.insertStretch(-1, 5)
self.layout.addWidget(chooser.widget)
self.specific_info_widget = VRowEntry()
self.specific_info_widget.layout.addWidget(get_line())
self.specific_table_info = qgis.PyQt.QtWidgets.QLabel()
self.specific_info_widget.layout.addWidget(self.specific_table_info)
self.specific_info_widget.layout.addWidget(get_line())
self.layout.addWidget(self.specific_info_widget.widget)
def w_qual_field_parameter_units():
sql = 'select distinct parameter, unit from w_qual_field'
connection_ok, result_dict = db_utils.get_sql_result_as_dict(sql)
if not connection_ok:
utils.MessagebarAndLog.warning(
bar_msg=QCoreApplication.translate(
'w_qual_field_parameter_units',
'Error, sql failed, see log message panel'),
log_msg=ru(
QCoreApplication.translate('w_qual_field_parameter_units',
'Cannot get data from sql %s')) %
ru(sql))
return {}
return ru(result_dict, keep_containers=True)
def pandas_how_tooltip():
return ru(
QCoreApplication.translate(
'pandas_how_tooltip',
'How to make the resample, ex. "mean" (default), "first", "last", "sum".\n'
'See pandas pandas.DataFrame.resample documentation for more info\n'
'(though "how" is not explained a lot)'))
def sql_load_fr_db(sql, dbconnection=None):
try:
if not isinstance(dbconnection, DbConnectionManager):
dbconnection = DbConnectionManager()
result = dbconnection.execute_and_fetchall(sql)
except Exception as e:
textstring = ru(
QCoreApplication.translate(
'sql_load_fr_db',
"""DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % (
ru(sql), ru(str(e)))
utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(),
log_msg=textstring,
duration=4)
return False, []
else:
return True, result
def get_distinct_values(tablename, columnname):
if not tablename or not columnname:
return []
sql = '''SELECT DISTINCT %s FROM %s''' % (columnname, tablename)
connection_ok, result = db_utils.sql_load_fr_db(sql)
if not connection_ok:
utils.MessagebarAndLog.critical(
bar_msg=utils.sql_failed_msg(),
log_msg=ru(
QCoreApplication.translate(
u'DistinctValuesBrowser',
u"""Cannot get data from sql %s""")) % ru(sql))
return []
values = [ru(col[0]) for col in result]
return values
