def backup_db(dbconnection=None):
if not isinstance(dbconnection, DbConnectionManager):
dbconnection = DbConnectionManager()
if dbconnection.dbtype == u'spatialite':
curs = dbconnection.cursor
curs.execute("begin immediate")
bkupname = dbconnection.dbpath + datetime.datetime.now().strftime(
'%Y%m%dT%H%M') + '.zip'
zf = zipfile.ZipFile(bkupname, mode='w')
zf.write(dbconnection.dbpath, compress_type=compression
) # compression will depend on if zlib is found or not
zf.close()
dbconnection.conn.rollback()
utils.MessagebarAndLog.info(bar_msg=utils.returnunicode(
QCoreApplication.translate(
"backup_db", "Database backup was written to %s ")) % bkupname,
duration=15)
else:
utils.MessagebarAndLog.info(bar_msg=utils.returnunicode(
QCoreApplication.translate(
"backup_db",
u"Backup of PostGIS database not supported yet!")),
duration=15)
dbconnection.closedb()
def create_test_string(anything):
ur""" Turns anything into a string used for testing
:param anything: just about anything
:return: A unicode string
>>> create_test_string(u'123')
u'123'
>>> create_test_string([1, 2, 3])
u'[1, 2, 3]'
>>> create_test_string({3: 'a', 2: 'b', 1: ('c', 'd')})
u'{1: (c, d), 2: b, 3: a}'
"""
if isinstance(anything, dict):
aunicode = u''.join([u'{', u', '.join([u': '.join([create_test_string(k), create_test_string(v)]) for k, v in sorted(anything.iteritems())]), u'}'])
elif isinstance(anything, list):
aunicode = u''.join([u'[', u', '.join([create_test_string(x) for x in anything]), u']'])
elif isinstance(anything, tuple):
aunicode = u''.join([u'(', u', '.join([create_test_string(x) for x in anything]), u')'])
elif isinstance(anything, (basestring, float, int)):
aunicode = utils.returnunicode(anything)
elif isinstance(anything, PyQt4.QtCore.QVariant):
print("Was varaint")
aunicode = utils.returnunicode(anything.toString().data())
else:
aunicode = utils.returnunicode(str(anything))
return aunicode
def create_test_string(anything=None):
ur""" Turns anything into a string used for testing
:param anything: just about anything
:return: A unicode string
>>> create_test_string(u'123')
u'123'
>>> create_test_string([1, 2, 3])
u'[1, 2, 3]'
>>> create_test_string({3: 'a', 2: 'b', 1: ('c', 'd')})
u'{1: (c, d), 2: b, 3: a}'
"""
if isinstance(anything, dict):
aunicode = u''.join([u'{', u', '.join([u': '.join([create_test_string(k), create_test_string(v)]) for k, v in sorted(anything.iteritems())]), u'}'])
elif isinstance(anything, list):
aunicode = u''.join([u'[', u', '.join([create_test_string(x) for x in anything]), u']'])
elif isinstance(anything, tuple):
aunicode = u''.join([u'(', u', '.join([create_test_string(x) for x in anything]), u')'])
elif isinstance(anything, (basestring, float, int)):
aunicode = utils.returnunicode(anything)
elif isinstance(anything, PyQt4.QtCore.QVariant):
print("Was variant")
aunicode = utils.returnunicode(anything.toString().data())
else:
aunicode = utils.returnunicode(str(anything))
return aunicode
def get_spatialite_db_path_from_dbsettings_string(db_settings):
if isinstance(db_settings, basestring):
# Test if the db_setting is an old database
if os.path.isfile(db_settings):
return db_settings
else:
try:
db_settings = ast.literal_eval(db_settings)
except Exception as e:
try:
msg = str(e)
except:
msg = utils.returnunicode(
QCoreApplication.translate(
u'get_spatialite_db_path_from_dbsettings_string',
u'Error message failed! Could not be converted to string!'
))
utils.MessagebarAndLog.info(log_msg=utils.returnunicode(
QCoreApplication.translate(
u'get_spatialite_db_path_from_dbsettings_string',
u'%s error msg from db_settings string "%s": %s')) % (
u'get_spatialite_db_path_from_dbsettings_string',
db_settings, msg))
return u''
else:
return db_settings.get(u'spatialite', {}).get(u'dbpath', u'')
elif isinstance(db_settings, dict):
return db_settings.get(u'spatialite', {}).get(u'dbpath', u'')
else:
return u''
def set_combobox(combobox, value):
index = combobox.findText(returnunicode(value))
if index != -1:
combobox.setCurrentIndex(index)
else:
combobox.addItem(returnunicode(value))
index = combobox.findText(returnunicode(value))
combobox.setCurrentIndex(index)
def create_temporary_table_for_import(self,
temptable_name,
fieldnames_types,
geometry_colname_type_srid=None):
if not temptable_name.startswith(u'temp_'):
temptable_name = u'temp_%s' % temptable_name
existing_names = tables_columns(dbconnection=self).keys()
while temptable_name in existing_names: #this should only be needed if an earlier import failed. if so, propose to rename the temporary import-table
reponse = PyQt4.QtGui.QMessageBox.question(
None,
utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u"Warning - Table name confusion!")),
utils.returnunicode(
QCoreApplication.translate(
u'midv_data_importer',
u'''The temporary import table '%s' already exists in the current DataBase. This could indicate a failure during last import. Please verify that your table contains all expected data and then remove '%s'.\n\nMeanwhile, do you want to go on with this import, creating a temporary table '%s_2' in database?'''
)) % (self.temptable_name, self.temptable_name,
self.temptable_name),
PyQt4.QtGui.QMessageBox.Yes | PyQt4.QtGui.QMessageBox.No)
if reponse == PyQt4.QtGui.QMessageBox.Yes:
self.temptable_name = '%s_2' % self.temptable_name
else:
raise utils.UserInterruptError()
if self.dbtype == u'spatialite':
temptable_name = u'mem.' + temptable_name
self.execute(u"""ATTACH DATABASE ':memory:' AS mem""")
if geometry_colname_type_srid is not None:
geom_column = geometry_colname_type_srid[0]
geom_type = geometry_colname_type_srid[1]
srid = geometry_colname_type_srid[2]
fieldnames_types.append(u'geometry %s' %
geometry_colname_type_srid[0])
sql = u"""CREATE table %s (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, %s)""" % (
temptable_name, u', '.join(fieldnames_types))
self.execute(sql)
sql = u"""SELECT RecoverGeometryColumn('%s','%s',%s,'%s',2) from %s AS a""" % (
temptable_name, geom_column, srid, geom_type,
temptable_name)
self.execute(sql)
else:
sql = u"""CREATE table %s (%s)""" % (
temptable_name, u', '.join(fieldnames_types))
self.execute(sql)
else:
self.execute(u"""CREATE TEMPORARY table %s (%s)""" %
(temptable_name, u', '.join(fieldnames_types)))
if geometry_colname_type_srid is not None:
geom_column = geometry_colname_type_srid[0]
geom_type = geometry_colname_type_srid[1]
srid = geometry_colname_type_srid[2]
sql = u"""ALTER TABLE %s ADD COLUMN %s geometry(%s, %s);""" % (
temptable_name, geom_column, geom_type, srid)
self.execute(sql)
return temptable_name
def transform_geometries(tname, column_names, old_table_column_srid_dict, new_table_column_srid_dict):
ur"""
Transform geometry columns to new chosen SRID
The transformation is only done if the chosen srid is not the same as the old srid,
and if the geometry column in question exists in both the old and the new database
:param tname: The table name
:param column_names: a list of columns that will be copied from the old table/database to the new table/database
:param old_table_column_srid_dict: a dict like {u'testt': {u'geom': 3006}}. A dict with tables that have geometries. The inner dict is the column and the srid.
:param new_table_column_srid_dict: a dict like {u'testt': {u'geom': 3006}}. A dict with tables that have geometries. The inner dict is the column and the srid.
:return: A list of columns where the geometry columns are Transformed.
>>> ExportData.transform_geometries(u'testt', [u'notgeom', u'geom'], {u'testt': {u'geom': 3006}}, {u'testt': {u'geom': 3006}})
[u'notgeom', u'geom']
>>> ExportData.transform_geometries(u'testt', [u'notgeom', u'geom'], {u'testt': {u'geom': 3006}}, {u'testt': {u'geom': 3010}})
[u'notgeom', u'Transform(geom, 3010)']
>>> ExportData.transform_geometries(u'obs_points', [u'obsid', u'east', u'north', u'geom'], {u'obs_points': {u'geom': 3006}}, {u'obs_points': {u'geom': 3010}})
[u'obsid', u'X(Transform(geom, 3010))', u'Y(Transform(geom, 3010))', u'Transform(geom, 3010)']
"""
transformed = False
#Make a transformation for column names that are geometries
if tname in new_table_column_srid_dict and tname in old_table_column_srid_dict:
transformed_column_names = []
for column in column_names:
new_srid = new_table_column_srid_dict.get(tname, {}).get(column, None)
old_srid = old_table_column_srid_dict.get(tname, {}).get(column, None)
if old_srid is not None and new_srid is not None and old_srid != new_srid:
transformed_column_names.append(u'Transform(' + column + u', ' + utils.returnunicode(new_srid) + u')')
utils.MessagebarAndLog.info(log_msg=u'Transformation for table "' + tname + u'" column "' + column + u'" from ' + str(old_srid) + u" to " + str(new_srid))
transformed = True
else:
transformed_column_names.append(column)
else:
transformed_column_names = column_names
#Special case for obs_points because of the very special columns east/north
if tname == u'obs_points' and transformed:
old_geocol_srids = [(k, v) for k, v in old_table_column_srid_dict.get(tname, {}).iteritems()]
new_geocol_srids = [(k, v) for k, v in new_table_column_srid_dict.get(tname, {}).iteritems()]
if len(old_geocol_srids) != 1 and len(new_geocol_srids) != 1:
utils.MessagebarAndLog.critical(bar_msg=u'Export warning!, see Log Message Panel', log_msg=u'Transformation of east/north for table obs_points failed! The number of geometry columns was not == 1!')
else:
new_srid = new_geocol_srids[0][1]
old_geometry_column = old_geocol_srids[0][0]
res = []
for column in transformed_column_names:
if column == u'east':
res.append(u'X(Transform(%s, %s))'%(old_geometry_column, new_srid))
elif column == u'north':
res.append(u'Y(Transform(%s, %s))'%(old_geometry_column, new_srid))
else:
res.append(column)
transformed_column_names = res
utils.MessagebarAndLog.info(log_msg=u'transformed_column_names:\n' + utils.returnunicode(transformed_column_names))
return transformed_column_names
def paste_data(self, paste_list=None):
if paste_list is None:
paste_list = PyQt4.QtGui.QApplication.clipboard().text().split(u'\n')
old_text = [returnunicode(self.item(i).text()) for i in xrange(self.count())]
new_items = set()
new_items.update([returnunicode(x) for x in paste_list])
new_items.update(old_text)
self.clear()
self.addItems(list(sorted(new_items)))
def locations_sublocations_obsids(self):
"""
:return: a list like [[obsid.locationsuffix as location, obsid.locationsuffix.sublocationsuffix as sublocation, obsid), ...]
"""
locations_sublocations_obsids = [(u'.'.join([x for x in [returnunicode(obsid), returnunicode(self.location_suffix)] if x]),
u'.'.join([x for x in [returnunicode(obsid), returnunicode(self.location_suffix), returnunicode(self.sublocation_suffix)] if x]), returnunicode(obsid))
for obsid in self._obsid_list.get_all_data()]
return locations_sublocations_obsids
def execute_and_fetchall(self, sql):
try:
self.cursor.execute(sql)
except Exception as e:
textstring = utils.returnunicode(
QCoreApplication.translate(
u'sql_load_fr_db',
u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""")
) % (utils.returnunicode(sql), utils.returnunicode(str(e)))
utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(),
log_msg=textstring)
raise
return self.cursor.fetchall()
def dict_to_sorted_list(adict):
"""
Creates a list of a dict of dicts
:param adict: a dict that may contain more dicts
:return:
>>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, 6)})
['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', '6']
>>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, {'k': 8, 'i': 9})})
['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', 'i', '9', 'k', '8']
>>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, {'k': 8, 'i': (9, 29)})})
['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', 'i', '9', 29, 'k', '8']
"""
result_list = []
if isinstance(adict, dict):
for k, v in sorted(adict.iteritems()):
result_list.extend(dict_to_sorted_list(k))
result_list.extend(dict_to_sorted_list(v))
elif isinstance(adict, list) or isinstance(adict, tuple):
for k in adict:
result_list.extend(dict_to_sorted_list(k))
else:
result_list.append(utils.returnunicode(adict).encode('utf-8'))
return result_list
def dict_to_sorted_list(adict):
"""
Creates a list of a dict of dicts
:param adict: a dict that may contain more dicts
:return:
>>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, 6)})
['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', '6']
>>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, {'k': 8, 'i': 9})})
['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', 'i', '9', 'k', '8']
>>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, {'k': 8, 'i': (9, 29)})})
['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', 'i', '9', 29, 'k', '8']
"""
result_list = []
if isinstance(adict, dict):
for k, v in sorted(adict.iteritems()):
result_list.extend(dict_to_sorted_list(k))
result_list.extend(dict_to_sorted_list(v))
elif isinstance(adict, list) or isinstance(adict, tuple):
for k in adict:
result_list.extend(dict_to_sorted_list(k))
else:
result_list.append(utils.returnunicode(adict).encode('utf-8'))
return result_list
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:
print ','.join([utils.returnunicode(col) for col in row])
except:
try:
print "failed printing piper data..."
except:
pass
self.make_the_plot()
def test_stratigraphy(self, mock_skippopup, mock_messagebar):
"""
:param mock_skippopup:
:param mock_messagebar:
:return:
"""
db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))''')
db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('2', 10, ST_GeomFromText('POINT(6720727 016568)', 3006))''')
db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('3', 20, ST_GeomFromText('POINT(6720728 016569)', 3006))''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''')
self.create_and_select_vlayer()
#print(str(self.vlayer.isValid()))
#print(str(db_utils.sql_load_fr_db('select * from obs_points')))
#print(str(db_utils.sql_load_fr_db('select * from stratigraphy')))
dlg = Stratigraphy(self.iface, self.vlayer, self.ms.settingsdict)
#print(str(mock_messagebar.mock_calls))
#print(str(mock_skippopup.mock_calls))
dlg.showSurvey()
test = utils.anything_to_string_representation(dlg.data)
test_survey = utils.anything_to_string_representation(repr(dlg.data['1']))
test_strata = utils.anything_to_string_representation(utils.returnunicode(dlg.data['1'].strata, keep_containers=True))
assert len(mock_skippopup.mock_calls) == 0
print(str(mock_messagebar.mock_calls))
assert len(mock_messagebar.mock_calls) == 0
assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')}"""
assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"'''
print("test_strata: " + test_strata)
assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
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 = utils.returnunicode(
QCoreApplication.translate(
u'sql_load_fr_db',
u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % (
utils.returnunicode(sql), utils.returnunicode(str(e)))
utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(),
log_msg=textstring,
duration=4)
return False, []
else:
return True, result
def get_last_used_quality_instruments():
"""
Returns quality instrumentids
:return: A tuple with instrument ids from w_qual_field
"""
sql = "select parameter, unit, instrument, staff, max(date_time) from w_qual_field group by parameter, unit, instrument, staff"
connection_ok, result_dict = utils.get_sql_result_as_dict(sql)
return returnunicode(result_dict, True)
def check_db_is_locked(self):
if self.dbtype == u'spatialite':
if os.path.exists(u'%s-journal' % self.dbpath):
raise DatabaseLockedError(
utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u"Error, The database is already in use (a journal-file was found)"
)))
def rpt_lower_left(self, GeneralData):
rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">"""
if utils.returnunicode(GeneralData[0][24]) not in ['','NULL'] and utils.returnunicode(GeneralData[0][25]) not in ['','NULL']:
rpt += utils.returnunicode(GeneralData[0][24])
rpt += utils.returnunicode(GeneralData[0][25])
elif utils.returnunicode(GeneralData[0][24]) not in ['','NULL']:
rpt += utils.returnunicode(GeneralData[0][24])
elif utils.returnunicode(GeneralData[0][25]) not in ['','NULL']:
rpt += utils.returnunicode(GeneralData[0][25])
rpt += r"""</p>"""
return rpt
def get_postgis_connections():
qs = QSettings()
postgresql_connections = {}
for k in sorted(qs.allKeys()):
k = utils.returnunicode(k)
if k.startswith(u'PostgreSQL'):
cols = k.split(u'/')
conn_name = cols[2]
try:
setting = cols[3]
except IndexError:
#utils.MessagebarAndLog.info(log_msg=u'Postgresql connection info: Setting ' + str(k) + u" couldn't be read")
continue
value = qs.value(k)
postgresql_connections.setdefault(conn_name, {})[setting] = value
postgresql_connections = utils.returnunicode(postgresql_connections,
keep_containers=True)
return postgresql_connections
def rpt_lower_right(self, statistics,meas_or_level_masl):
if meas_or_level_masl == 'meas':
unit = u' m below toc<br>'
else:
unit = u' m above sea level<br>'
rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">"""
if utils.returnunicode(statistics[2]) != '' and utils.returnunicode(statistics[2]) != '0':
rpt += u'Number of water level measurements: ' + utils.returnunicode(statistics[2]) + u'<br>'
if utils.returnunicode(statistics[0]) != '':
rpt += u'Highest measured water level: ' + utils.returnunicode(statistics[0]) + unit
if utils.returnunicode(statistics[1]) != '':
rpt += u'Median water level: ' + utils.returnunicode(statistics[1]) + unit
if utils.returnunicode(statistics[3]) != '':
rpt += u'Lowest measured water level: ' + utils.returnunicode(statistics[3]) + unit
rpt += r"""</p>"""
return rpt
def rpt_lower_right_sv(self, statistics,meas_or_level_masl):
if meas_or_level_masl == 'meas':
unit = u' m u rök<br>'
else:
unit = u' m ö h<br>'
rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">"""
if utils.returnunicode(statistics[2]) != '' and utils.returnunicode(statistics[2]) != '0':
rpt += u'Antal nivåmätningar: ' + utils.returnunicode(statistics[2]) + u'<br>'
if utils.returnunicode(statistics[0]) != '':
rpt += u'Högsta uppmätta nivå: ' + utils.returnunicode(statistics[0]) + unit
if utils.returnunicode(statistics[1]) != '':
rpt += u'Medianvärde för nivå: ' + utils.returnunicode(statistics[1]) + unit
if utils.returnunicode(statistics[3]) != '':
rpt += u'Lägsta uppmätta nivå: ' + utils.returnunicode(statistics[3]) + unit
rpt += r"""</p>"""
return rpt
def sql_alter_db(sql, dbconnection=None, all_args=None):
if not isinstance(dbconnection, DbConnectionManager):
dbconnection = DbConnectionManager()
if dbconnection.dbtype == u'spatialite':
try:
dbconnection.execute(u'PRAGMA foreign_keys = ON')
except:
pass
try:
dbconnection.execute_and_commit(sql, all_args=all_args)
except Exception as e:
textstring = utils.returnunicode(
QCoreApplication.translate(
u'sql_alter_db',
u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % (
utils.returnunicode(sql), utils.returnunicode(str(e)))
utils.MessagebarAndLog.warning(bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'sql_alter_db',
u'Some sql failure, see log for additional info.')),
log_msg=textstring,
duration=4)
def find_date_format(datestring):
"""
Parses a string and returns the found working dateformat string
:param datestring: A string representing a date, ex: '2015-01-01 12:00'
:return: The dateformat of the string, ex: '%Y-%m-%d %H:%M'
Can only parse a list of preconfigured datestrings. See the code.
>>> find_date_format(u'2015-01-01 01:01:01')
u'%Y-%m-%d %H:%M:%S'
>>> find_date_format(u'01-01-2015 01:01:01')
u'%d-%m-%Y %H:%M:%S'
>>> find_date_format(u'01:01:01')
u'%H:%M:%S'
>>> find_date_format(u'2015-01-01')
u'%Y-%m-%d'
>>> print(find_date_format(u'abc'))
None
"""
datestring = str(datestring)
date_formats_to_try = [
u'%Y/%m/%d %H:%M:%S', u'%Y-%m-%d %H:%M:%S', u'%Y%m%d %H:%M:%S',
u'%Y-%m-%d %H:%M', u'%Y%m%d', u'%Y-%m-%d', u'%d-%m-%Y', u'%H:%M:%S',
u'%d-%m-%Y %H:%M:%S', u'%d-%m-%Y %H:%M', u'%d-%m-%Y %H',
u'%Y/%m/%d %H:%M', u'%Y/%m/%d %H', u'%Y%m%d %H%M%S', u'%Y%m%d %H%M',
u'%Y%m%d %H'
]
found_format = None
for dateformat in date_formats_to_try:
try:
datetime.datetime.strptime(datestring, dateformat)
except ValueError:
continue
else:
found_format = dateformat
break
if found_format is None:
utils.MessagebarAndLog.critical(
bar_msg=QCoreApplication.translate(
u'find_date_format',
u'Date parsing failed, see log message panel'),
log_msg=ru(
QCoreApplication.translate(
u'find_date_format',
u'Could not find the date format for string "%s"\nSupported date formats:\n%s'
)) %
(utils.returnunicode(datestring), u'\n'.join(date_formats_to_try)))
return found_format
def paste_data(self, paste_list=None):
if paste_list is None:
paste_list = PyQt4.QtGui.QApplication.clipboard().text().split(u'\n')
#Use lists to keep the data ordering (the reason set() is not used
old_text = self.get_all_data()
new_items = []
for alist in [old_text, paste_list]:
for x in alist:
if x not in new_items:
new_items.append(returnunicode(x))
self.clear()
if self.keep_sorted:
self.addItems(list(sorted(new_items)))
else:
self.addItems(list(new_items))
def test_stratigraphy(self, mock_skippopup, mock_messagebar):
"""
:param mock_skippopup:
:param mock_messagebar:
:return:
"""
db_utils.sql_alter_db(
'''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))'''
)
db_utils.sql_alter_db(
'''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('2', 10, ST_GeomFromText('POINT(6720727 016568)', 3006))'''
)
db_utils.sql_alter_db(
'''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('3', 20, ST_GeomFromText('POINT(6720728 016569)', 3006))'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')'''
)
self.create_and_select_vlayer()
#print(str(self.vlayer.isValid()))
#print(str(db_utils.sql_load_fr_db('select * from obs_points')))
#print(str(db_utils.sql_load_fr_db('select * from stratigraphy')))
dlg = Stratigraphy(self.iface, self.vlayer,
self.midvatten.ms.settingsdict)
#print(str(mock_messagebar.mock_calls))
#print(str(mock_skippopup.mock_calls))
dlg.showSurvey()
test = utils.anything_to_string_representation(dlg.data)
test_survey = utils.anything_to_string_representation(
repr(dlg.data['1']))
test_strata = utils.anything_to_string_representation(
utils.returnunicode(dlg.data['1'].strata, keep_containers=True))
assert len(mock_skippopup.mock_calls) == 0
print(str(mock_messagebar.mock_calls))
assert len(mock_messagebar.mock_calls) == 0
assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')}"""
assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"'''
print("test_strata: " + test_strata)
assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
def delete_srids(execute_able_object, keep_epsg_code):
if isinstance(execute_able_object, DbConnectionManager):
if not execute_able_object.dbtype == u'spatialite':
return None
delete_srid_sql_aux = r"""delete from spatial_ref_sys_aux where srid NOT IN ('%s', '4326');""" % keep_epsg_code
try:
execute_able_object.execute(delete_srid_sql_aux)
except:
pass
delete_srid_sql = r"""delete from spatial_ref_sys where srid NOT IN ('%s', '4326');""" % keep_epsg_code
try:
execute_able_object.execute(delete_srid_sql)
except:
utils.MessagebarAndLog.info(log_msg=utils.returnunicode(
QCoreApplication.translate(u'delete_srids',
u'Removing srids failed using: %s')) %
str(delete_srid_sql))
def rpt_upper_right(self, StratData):
rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">"""
if len(StratData) > 0:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=15%><P><u>""" + u'level (m b gs)</P></u></TD>'
rpt += r"""<TD WIDTH=27%><P><u>""" + u'geology, full text' + '</P></u></TD>'
rpt += r"""<TD WIDTH=17%><P><u>""" + u'geology, short' + '</P></u></TD>'
rpt += r"""<TD WIDTH=9%><P><u>""" + u'capacity' + '</P></u></TD>'
rpt += r"""<TD WIDTH=13%><P><u>""" + u'development' + '</P></u></TD>'
rpt += r"""<TD WIDTH=21%><P><u>""" + u'comment' + '</P></u></TD></TR>'
for row in StratData:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=15%><P>"""
rpt += utils.returnunicode(row[2]) + ' - ' + utils.returnunicode(row[3]) + '</P></TD>'
rpt += r"""<TD WIDTH=27%><P>""" + utils.returnunicode(row[4]) + '</P></TD>'
rpt += r"""<TD WIDTH=17%><P>""" + utils.returnunicode(row[5]) + '</P></TD>'
rpt += r"""<TD WIDTH=9%><P>""" + utils.returnunicode(row[6]) + '</P></TD>'
rpt += r"""<TD WIDTH=13%><P>""" + utils.returnunicode(row[7]) + '</P></TD>'
rpt += r"""<TD WIDTH=21%><P>""" + utils.returnunicode(row[8]) + '</P></TD></TR>'
rpt += r"""</p>"""
return rpt
def select_file(self):
""" Open a dialog to locate the sqlite file and some more..."""
dbpath = QFileDialog.getOpenFileName(None, str("Select database:"),
"*.sqlite")
if dbpath: # Only get new db name if not cancelling the FileDialog
self.dbpath = dbpath
self.midvsettingsdialogdock.ms.settingsdict[
'database'] = utils.anything_to_string_representation(
{u'spatialite': {
u'dbpath': dbpath
}})
self.midvsettingsdialogdock.ms.save_settings('database')
self.midvsettingsdialogdock.load_plot_settings()
#self.midvsettingsdialogdock.LoadAndSelectLastSettings()
else: # debug
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
u'SpatialiteSettings',
u"DB selection cancelled and still using database path %s")
) % utils.returnunicode(
self.midvsettingsdialogdock.ms.settingsdict['database']))
def rpt_upper_right_sv(self, StratData):
rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">"""
if len(StratData) > 0:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=17%><P><u>""" + u'nivå (mumy)</P></u></TD>'
rpt += r"""<TD WIDTH=27%><P><u>""" + u'jordart, fullst beskrivn' + '</P></u></TD>'
rpt += r"""<TD WIDTH=17%><P><u>""" + u'huvudfraktion' + '</P></u></TD>'
rpt += r"""<TD WIDTH=5%><P><u>""" + u'vg' + '</P></u></TD>'
rpt += r"""<TD WIDTH=9%><P><u>""" + u'stänger?' + '</P></u></TD>'
rpt += r"""<TD WIDTH=27%><P><u>""" + u'kommentar' + '</P></u></TD></TR>'
for row in StratData:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=17%><P>"""
rpt += utils.returnunicode(row[2]) + ' - ' + utils.returnunicode(row[3]) + '</P></TD>'
rpt += r"""<TD WIDTH=27%><P>""" + utils.returnunicode(row[4]) + '</P></TD>'
rpt += r"""<TD WIDTH=17%><P>""" + utils.returnunicode(row[5]) + '</P></TD>'
rpt += r"""<TD WIDTH=5%><P>""" + utils.returnunicode(row[6]) + '</P></TD>'
rpt += r"""<TD WIDTH=9%><P>""" + utils.returnunicode(row[7]) + '</P></TD>'
rpt += r"""<TD WIDTH=27%><P>""" + utils.returnunicode(row[8]) + '</P></TD></TR>'
rpt += r"""</p>"""
return rpt
def __init__(self,msettings,activelayer):
self.ms = msettings
self.activelayer = activelayer
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
print """obsid, date_time, type, Cl_meqPl, HCO3_meqPl, SO4_meqPl, Na+K_meqPl, Ca_meqPl, Mg_meqPl"""
for row in self.obsrecarray:
#print ','.join([unicode(col).encode('utf-8') for col in row])
try:
print ','.join([utils.returnunicode(col) for col in row])
except:
print "failed printing piper data..."
self.make_the_plot()
def get_table_info(tablename, dbconnection=None):
if not isinstance(dbconnection, DbConnectionManager):
dbconnection = DbConnectionManager()
if dbconnection.dbtype == u'spatialite':
columns_sql = """PRAGMA table_info (%s)""" % tablename
try:
columns = dbconnection.execute_and_fetchall(columns_sql)
except Exception as e:
if dbconnection.dbtype == u'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=utils.returnunicode(
QCoreApplication.translate(
u'get_table_info', u'Sql failed: %s\msg:%s')) %
(columns_sql, str(e)))
return None
else:
columns_sql = u"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(
u"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 test_stratigraphy_missing_h_gs(self, mock_skippopup, mock_messagebar):
"""
:param mock_skippopup:
:param mock_messagebar:
:return:
"""
db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))''')
db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, h_toc, geometry) VALUES ('2', NULL, 10, ST_GeomFromText('POINT(6720727 016568)', 3006))''')
db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, geometry) VALUES ('3', ST_GeomFromText('POINT(6720728 016569)', 3006))''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 1, 0, 1, 'sand', 'sand', '3', 'j')''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 1, 0, 2, 'sand', 'sand', '3', 'j')''')
db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 2, 2, 6, 'morän', 'morän', '3', 'j')''')
self.create_and_select_vlayer()
dlg = Stratigraphy(self.iface, self.vlayer, self.ms.settingsdict)
dlg.showSurvey()
test = utils.anything_to_string_representation(dlg.data)
test_survey = utils.anything_to_string_representation(repr(dlg.data['1']))
test_strata = utils.anything_to_string_representation(utils.returnunicode(dlg.data['1'].strata, keep_containers=True))
assert len(mock_skippopup.mock_calls) == 1
print(str(mock_skippopup.mock_calls))
print(str(mock_messagebar.mock_calls))
assert mock_skippopup.mock_calls == [mock.call('Warning, h_gs is missing. See messagebar.')]
assert mock_messagebar.mock_calls == [mock.call.warning(bar_msg="Obsid 2: using h_gs '' failed, using 'h_toc' instead.", duration=90, log_msg='False'),
mock.call.warning(bar_msg="Obsid 3: using h_gs '' failed, using '-1' instead.", duration=90, log_msg='False')]
print("test: " + test)
assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>'), "2": SURVEY('2', 10.000000, '<QgsPointXY: POINT(6720727 16568)>'), "3": SURVEY('3', -1.000000, '<QgsPointXY: POINT(6720728 16569)>')}"""
print("test_survey " + test_survey)
assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"'''
print("Test strata " + test_strata)
assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
def dbpath(self, value):
self._dbpath.setText(utils.returnunicode(value))
def dbpath(self):
return utils.returnunicode(self._dbpath.text())
def dbtype_combobox(self, value):
index = self._dbtype_combobox.findText(utils.returnunicode(value))
if index != -1:
self._dbtype_combobox.setCurrentIndex(index)
def dbtype_combobox(self):
return utils.returnunicode(self._dbtype_combobox.currentText())
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, basestring):
sql = [sql]
elif not isinstance(sql, (list, tuple)):
raise TypeError(
utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'DbConnectionManager.execute: sql must be type string or a list/tuple of strings. Was %s'
)) % utils.returnunicode(type(sql)))
for idx, line in enumerate(sql):
if all_args is None:
try:
self.cursor.execute(line)
except Exception as e:
textstring = utils.returnunicode(
QCoreApplication.translate(
u'sql_load_fr_db',
u"""DB error!\n SQL causing this error:%s\nMsg:\n%s"""
)) % (utils.returnunicode(line),
utils.returnunicode(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 = utils.returnunicode(
QCoreApplication.translate(
u'sql_load_fr_db',
u"""DB error!\n SQL causing this error:%s\nusing args %s\nMsg:\n%s"""
)) % (utils.returnunicode(line),
utils.returnunicode(args),
utils.returnunicode(str(e)))
utils.MessagebarAndLog.warning(
bar_msg=utils.sql_failed_msg(), log_msg=textstring)
raise
else:
raise TypeError(
utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'DbConnectionManager.execute: all_args must be a list/tuple. Was %s'
)) % utils.returnunicode(type(all_args)))
def create_export_printlist(export_objects):
"""
Creates a result list with FieldLogger format from selected obsids and parameters
:return: a list with result lines to export to file
"""
latlons = utils.get_latlon_for_all_obsids()
parameters_inputtypes_hints = OrderedDict()
sublocations_locations = OrderedDict()
sublocations_lat_lon = OrderedDict()
sublocations_parameters = OrderedDict()
for export_object in export_objects:
parameter = export_object.final_parameter_name
if not parameter:
utils.MessagebarAndLog.critical(
bar_msg=u"Critical: Parameter " + parameter + u' error. See log message panel',
log_msg=u'Parameter name not given.')
continue
input_type = export_object.input_type
if not input_type:
utils.MessagebarAndLog.critical(
bar_msg=u"Critical: Parameter " + parameter + u' error. See log message panel',
log_msg=u'Input type not given.')
continue
if parameter in parameters_inputtypes_hints:
utils.MessagebarAndLog.warning(bar_msg=u"Warning: Parameter " + parameter + u' error. See log message panel', log_msg=u'The parameter ' + parameter + u' already exists. Only the first occurence one will be written to file.')
continue
parameters_inputtypes_hints[parameter] = (input_type, export_object.hint)
for location, sublocation, obsid in export_object.locations_sublocations_obsids:
location_exists = sublocations_locations.get(sublocation, None)
if location != location_exists and location_exists is not None:
utils.MessagebarAndLog.warning(bar_msg=u'Warning: sublocation ' + sublocation + u' error, see log message panel', log_msg=u'sublocation ' + sublocation + u' already existed for location ' + location_exists + u' and is duplicated by location ' + location + u'. It will be skipped.')
continue
if sublocation not in sublocations_lat_lon:
lat, lon = latlons.get(obsid, [None, None])
if any([lat is None, not lat, lon is None, not lon]):
utils.MessagebarAndLog.critical(bar_msg=u'Critical: Obsid ' + u' did not have lat-lon coordinates. Check obs_points table')
continue
sublocations_lat_lon[sublocation] = (returnunicode(lat), returnunicode(lon))
sublocations_locations[sublocation] = location
sublocations_parameters.setdefault(sublocation, []).append(parameter)
comments = [par for par in parameters_inputtypes_hints.keys() if u'comment' in par]
if not comments:
utils.MessagebarAndLog.warning(bar_msg=u'Warning: No comment parameter found. Is it forgotten?')
#Make a flat set of used parameters
#used_parameters = [item for v in sublocations_parameters.values() for item in v]
#Remove unused parameters
#parameters_inputtypes_hints = OrderedDict([(k, v) for k, v in parameters_inputtypes_hints.iteritems() if k in used_parameters])
printlist = []
printlist.append(u"FileVersion 1;" + str(len(parameters_inputtypes_hints)))
printlist.append(u"NAME;INPUTTYPE;HINT")
printlist.extend([u';'.join([returnunicode(par),
returnunicode(i_h[0]) if i_h[0] else u'',
returnunicode(i_h[1]) if i_h[1] else u''])
for par, i_h in parameters_inputtypes_hints.iteritems()])
printlist.append(u'NAME;sublocation;LAT;LON;INPUTFIELD')
printlist.extend([u';'.join([returnunicode(location),
returnunicode(sublocation),
returnunicode(sublocations_lat_lon[sublocation][0]),
returnunicode(sublocations_lat_lon[sublocation][1]),
u'|'.join(returnunicode(sublocations_parameters[sublocation], keep_containers=True))])
for sublocation, location in sorted(sublocations_locations.iteritems())])
return printlist
def connection(self, value):
index = self._connection.findText(utils.returnunicode(value))
if index != -1:
self._connection.setCurrentIndex(index)
def test_stratigraphy_missing_h_gs(self, mock_skippopup, mock_messagebar):
"""
:param mock_skippopup:
:param mock_messagebar:
:return:
"""
db_utils.sql_alter_db(
'''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))'''
)
db_utils.sql_alter_db(
'''INSERT INTO obs_points (obsid, h_gs, h_toc, geometry) VALUES ('2', NULL, 10, ST_GeomFromText('POINT(6720727 016568)', 3006))'''
)
db_utils.sql_alter_db(
'''INSERT INTO obs_points (obsid, geometry) VALUES ('3', ST_GeomFromText('POINT(6720728 016569)', 3006))'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 1, 0, 1, 'sand', 'sand', '3', 'j')'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 2, 1, 4.5, 'morän', 'morän', '3', 'j')'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 1, 0, 2, 'sand', 'sand', '3', 'j')'''
)
db_utils.sql_alter_db(
'''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 2, 2, 6, 'morän', 'morän', '3', 'j')'''
)
self.create_and_select_vlayer()
dlg = Stratigraphy(self.iface, self.vlayer, self.ms.settingsdict)
dlg.showSurvey()
test = utils.anything_to_string_representation(dlg.data)
test_survey = utils.anything_to_string_representation(
repr(dlg.data['1']))
test_strata = utils.anything_to_string_representation(
utils.returnunicode(dlg.data['1'].strata, keep_containers=True))
assert len(mock_skippopup.mock_calls) == 1
print(str(mock_skippopup.mock_calls))
print(str(mock_messagebar.mock_calls))
assert mock_skippopup.mock_calls == [
mock.call('Warning, h_gs is missing. See messagebar.')
]
assert mock_messagebar.mock_calls == [
mock.call.warning(
bar_msg="Obsid 2: using h_gs '' failed, using 'h_toc' instead.",
duration=90,
log_msg='False'),
mock.call.warning(
bar_msg="Obsid 3: using h_gs '' failed, using '-1' instead.",
duration=90,
log_msg='False')
]
print("test: " + test)
assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>'), "2": SURVEY('2', 10.000000, '<QgsPointXY: POINT(6720727 16568)>'), "3": SURVEY('3', -1.000000, '<QgsPointXY: POINT(6720728 16569)>')}"""
print("test_survey " + test_survey)
assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"'''
print("Test strata " + test_strata)
assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
def rpt_upper_left_sv(self, GeneralData, CRS='', CRSname=''):
rpt = r"""<p style="font-family:'arial'; font-size:8pt; font-weight:400; font-style:normal;">"""
if utils.returnunicode(GeneralData[0][1]) != '' and utils.returnunicode(GeneralData[0][1]) != 'NULL' and utils.returnunicode(GeneralData[0][1]) != utils.returnunicode(GeneralData[0][0]):
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'originalbenämning' + r"""</TD><TD WIDTH=67%>""" + utils.returnunicode(GeneralData[0][1]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][3]) != '' and utils.returnunicode(GeneralData[0][3]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'obstyp' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][3]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][4]) != '' and utils.returnunicode(GeneralData[0][4]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'djup (m fr my t botten)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][4]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][17]) != '' and utils.returnunicode(GeneralData[0][17]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'röröverkant (möh)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][17])
if utils.returnunicode(GeneralData[0][21]) != '':
rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')'
rpt += '</TD></TR>'
if utils.returnunicode(GeneralData[0][18]) != '' and utils.returnunicode(GeneralData[0][18]) != 'NULL' and utils.returnunicode(GeneralData[0][18]) != '0' and utils.returnunicode(GeneralData[0][18]) != '0.0':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'rörövermått (m ö my)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][18]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][19]) != '' and utils.returnunicode(GeneralData[0][19]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'markytans nivå, my (möh)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][19])
if utils.returnunicode(GeneralData[0][21]) != '':
rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')'
rpt += '</TD></TR>'
if utils.returnunicode(GeneralData[0][20]) != '' and utils.returnunicode(GeneralData[0][20]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'onoggrannhet i höjd, avser rök (m)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][20]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][13]) != '' and utils.returnunicode(GeneralData[0][13]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'östlig koordinat' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][13]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>'
if utils.returnunicode(GeneralData[0][14]) != '' and utils.returnunicode(GeneralData[0][14]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'nordlig koordinat' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][14]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>'
if utils.returnunicode(GeneralData[0][13]) != '' and utils.returnunicode(GeneralData[0][13]) != 'NULL' and utils.returnunicode(GeneralData[0][14]) != '' and utils.returnunicode(GeneralData[0][15]) != '':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'lägesonoggrannhet' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][15]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][7]) != '' and utils.returnunicode(GeneralData[0][7]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'material' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][7]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][6]) != '' and utils.returnunicode(GeneralData[0][6]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'innerdiameter (mm)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][6]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][5]) != '' and utils.returnunicode(GeneralData[0][5]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'borrningens avslut' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][5]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][8]) != '' and utils.returnunicode(GeneralData[0][8]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'filter/spets' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][8]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][10]) != '' and utils.returnunicode(GeneralData[0][10]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'borrningen avslutades' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][10]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][9]) != '' and utils.returnunicode(GeneralData[0][9]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'kapacitet/vg på spetsnivå' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][9]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][2]) != '' and utils.returnunicode(GeneralData[0][2]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'fastighet/plats' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][2]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][23]) != '' and utils.returnunicode(GeneralData[0][23]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'referens' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][23]) + '</TD></TR>'
rpt += r"""</p>"""
if utils.returnunicode(GeneralData[0][16]) != '' and utils.returnunicode(GeneralData[0][16]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'lägesangivelsens ursprung' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][16]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][22]) != '' and utils.returnunicode(GeneralData[0][22]) != 'NULL':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'höjdangivelsens ursprung' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][22]) + '</TD></TR>'
return rpt
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 == u'spatialite':
data = {u'column': column, u'table': table, u'obsid': obsid}
sql = u'''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(u'\n', u' ')
# 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=utils.returnunicode(
QCoreApplication.translate(
u'calculate_median_value',
u'Median calculation error, see log message panel')),
log_msg=utils.returnunicode(
QCoreApplication.translate(u'calculate_median_value',
u'Sql failed: %s')) % sql)
median_value = None
else:
sql = u"""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=utils.returnunicode(
QCoreApplication.translate(
u'calculate_median_value',
u'Median calculation error, see log message panel')),
log_msg=utils.returnunicode(
QCoreApplication.translate(u'calculate_median_value',
u'Sql failed: %s')) % sql)
median_value = None
return median_value
def dbtype_combobox(self):
return utils.returnunicode(self._dbtype_combobox.currentText())
def locations_sublocations_obsids(self):
locations_sublocations_obsids = [(u'.'.join([returnunicode(obsid), returnunicode(self.location_suffix)]),
u'.'.join([returnunicode(obsid), returnunicode(self.sublocation_suffix)]), returnunicode(obsid))
for obsid in self.obsid_list.get_all_data()]
return locations_sublocations_obsids
def select_file(self):
""" Open a dialog to locate the sqlite file and some more..."""
dbpath, __ = QFileDialog.getOpenFileName(None, str("Select database:"), "*.sqlite")
if dbpath: # Only get new db name if not cancelling the FileDialog
self.dbpath = dbpath
self.midvsettingsdialogdock.ms.settingsdict['database'] = utils.anything_to_string_representation({'spatialite': {'dbpath': dbpath}})
self.midvsettingsdialogdock.ms.save_settings('database')
self.midvsettingsdialogdock.load_plot_settings()
warn_about_old_database()
else: # debug
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('SpatialiteSettings', "DB selection cancelled and still using database path %s"))%utils.returnunicode(self.midvsettingsdialogdock.ms.settingsdict['database']))
def dbpath(self):
return utils.returnunicode(self._dbpath.text())
def connection(self):
return utils.returnunicode(self._connection.currentText())
def dbpath(self, value):
self._dbpath.setText(utils.returnunicode(value))
def __init__(self, obsid='', settingsdict = {}):
#open connection to report file
reportfolder = os.path.join(QDir.tempPath(), 'midvatten_reports')
if not os.path.exists(reportfolder):
os.makedirs(reportfolder)
reportpath = os.path.join(reportfolder, "drill_report.html")
logopath = os.path.join(os.sep,os.path.dirname(__file__),"..","about","midvatten_logga.png")
imgpath = os.path.join(os.sep,os.path.dirname(__file__),"..","reports")
f = codecs.open(reportpath, "wb", "utf-8")
#write some initiating html, header and also
rpt = r"""<meta http-equiv="content-type" content="text/html; charset=utf-8" />"""
rpt += r"""<head><title>%s General report from Midvatten plugin for QGIS</title></head>"""%obsid
rpt += r"""<html><TABLE WIDTH=100% BORDER=0 CELLPADDING=1 CELLSPACING=1><TR VALIGN=TOP><TD WIDTH=15%><h3 style="font-family:'arial';font-size:18pt; font-weight:600">"""
rpt += obsid
if utils.getcurrentlocale() == 'sv_SE':
rpt += ''.join([r'''</h3><img src="''', os.path.join(imgpath, 'for_general_report_sv.png'), r'''" /><br><img src=''', r"""'"""])
#rpt += r"""</h3><img src="for_general_report_sv.png" /><br><img src='"""
else:
rpt += ''.join([r'''</h3><img src="''', os.path.join(imgpath, 'for_general_report.png'), r'''" /><br><img src=''', r"""'"""])
#rpt += r"""</h3><img src="for_general_report.png" /><br><img src='"""
rpt += logopath
rpt +="""' /></TD><TD WIDTH=85%><TABLE WIDTH=100% BORDER=1 CELLPADDING=4 CELLSPACING=3><TR VALIGN=TOP><TD WIDTH=50%><P><U><B>"""
if utils.getcurrentlocale() == 'sv_SE':
rpt += u'Allmän information'
else:
rpt += u'General information'
rpt += r"""</B></U></P><TABLE style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;" WIDTH=100% BORDER=0 CELLPADDING=0 CELLSPACING=1><COL WIDTH=43*><COL WIDTH=43*>"""
f.write(rpt)
# GENERAL DATA UPPER LEFT QUADRANT
ConnectionOK, GeneralData = self.GetData(obsid, 'obs_points', 'n')#MacOSX fix1
#utils.pop_up_info(str(ConnectionOK))#debug
if ConnectionOK==True:
result2 = (utils.sql_load_fr_db(r"""SELECT srid FROM geometry_columns where f_table_name = 'obs_points'""")[1])[0][0]
CRS = utils.returnunicode(result2) #1st we need crs
result3 = (utils.sql_load_fr_db(r"""SELECT ref_sys_name FROM spatial_ref_sys where srid =""" + CRS)[1])[0][0]
CRSname = utils.returnunicode(result3) # and crs name
if utils.getcurrentlocale() == 'sv_SE':
reportdata_1 = self.rpt_upper_left_sv(GeneralData, CRS, CRSname)
else:
reportdata_1 = self.rpt_upper_left(GeneralData, CRS, CRSname)
f.write(reportdata_1)
rpt = r"""</TABLE></TD><TD WIDTH=50%><P><U><B>"""
if utils.getcurrentlocale() == 'sv_SE':
rpt += u'Lagerföljd'
else:
rpt += u'Stratigraphy'
rpt += r"""</B></U></P><TABLE style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;" WIDTH=100% BORDER=0 CELLPADDING=0 CELLSPACING=1><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*>"""
f.write(rpt)
# STRATIGRAPHY DATA UPPER RIGHT QUADRANT
StratData = self.GetData(obsid, 'stratigraphy', 'n')[1] #MacOSX fix1
if utils.getcurrentlocale() == 'sv_SE':
reportdata_2 = self.rpt_upper_right_sv(StratData)
else:
reportdata_2 = self.rpt_upper_right(StratData)
f.write(reportdata_2)
rpt = r"""</TABLE></TD></TR><TR VALIGN=TOP><TD WIDTH=50%><P><U><B>"""
if utils.getcurrentlocale() == 'sv_SE':
rpt += u'Kommentarer'
else:
rpt += u'Comments'
rpt += r"""</B></U></P>"""
f.write(rpt)
# COMMENTS LOWER LEFT QUADRANT
reportdata_3 = self.rpt_lower_left(GeneralData)
f.write(reportdata_3)
rpt = r"""</TD><TD WIDTH=50%><P><U><B>"""
if utils.getcurrentlocale() == 'sv_SE':
rpt += u'Vattennivåer'
else:
rpt += u'Water levels'
rpt += r"""</B></U></P>"""
f.write(rpt)
# WATER LEVEL STATISTICS LOWER RIGHT QUADRANT
meas_or_level_masl, statistics = GetStatistics(obsid)#MacOSX fix1
if utils.getcurrentlocale() == 'sv_SE':
reportdata_4 = self.rpt_lower_right_sv(statistics,meas_or_level_masl)
else:
reportdata_4 = self.rpt_lower_right(statistics,meas_or_level_masl)
f.write(reportdata_4)
f.write(r"""</TD></TR></TABLE></TD></TR></TABLE>""")
f.write("\n</p></body></html>")
f.close()
#print reportpath#debug
QDesktopServices.openUrl(QUrl.fromLocalFile(reportpath))
def connection(self, value):
index = self._connection.findText(utils.returnunicode(value))
if index != -1:
self._connection.setCurrentIndex(index)
def rpt_upper_left(self, GeneralData, CRS='', CRSname=''):
rpt = r"""<p style="font-family:'arial'; font-size:8pt; font-weight:400; font-style:normal;">"""
if utils.returnunicode(GeneralData[0][1]) != '' and utils.returnunicode(GeneralData[0][1]) != 'NULL' and utils.returnunicode(GeneralData[0][1]) != utils.returnunicode(GeneralData[0][0]):
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'original name' + r"""</TD><TD WIDTH=67%>""" + utils.returnunicode(GeneralData[0][1]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][3]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'obs type' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][3]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][4]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'depth (m fr gs to bottom)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][4]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][17]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'top of casing, toc (masl)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][17])
if utils.returnunicode(GeneralData[0][21]) != '':
rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')'
rpt += '</TD></TR>'
if utils.returnunicode(GeneralData[0][18]) not in ['','NULL'] and utils.returnunicode(GeneralData[0][18]) != '0' and utils.returnunicode(GeneralData[0][18]) != '0.0':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'distance toc-gs, tocags (mags)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][18]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][19]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'ground surface level, gs (masl)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][19])
if utils.returnunicode(GeneralData[0][21]) != '':
rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')'
rpt += '</TD></TR>'
if utils.returnunicode(GeneralData[0][20]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'elevation accuracy (m)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][20]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][13]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'eastern coordinate' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][13]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>'
if utils.returnunicode(GeneralData[0][14]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'northern coordinate' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][14]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>'
if utils.returnunicode(GeneralData[0][13]) not in ['','NULL'] and utils.returnunicode(GeneralData[0][14]) != '' and utils.returnunicode(GeneralData[0][15]) != '':
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'position accuracy' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][15]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][7]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'material' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][7]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][6]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'inner diameter (mm)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][6]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][5]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'drill stop' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][5]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][8]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'screen type' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][8]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][10]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'drill date' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][10]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][9]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'capacity' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][9]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][2]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'place' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][2]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][23]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'reference' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][23]) + '</TD></TR>'
rpt += r"""</p>"""
if utils.returnunicode(GeneralData[0][16]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'source for position' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][16]) + '</TD></TR>'
if utils.returnunicode(GeneralData[0][22]) not in ['','NULL']:
rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'source for elevation' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][22]) + '</TD></TR>'
return rpt
def format_obsids(self, obsids):
formatted_obsids = u''.join([u'(', u', '.join([u"'{}'".format(k) for k in utils.returnunicode(obsids, True)]), u')']).encode('utf-8')
return formatted_obsids
def GetData(self, dbPath='', obsid = ''): # GetData method that returns a table with water quality data
#conn = sqlite.connect(dbPath,detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
myconnection = utils.dbconnection()
myconnection.connect2db()
# skapa en cursor
curs = myconnection.conn.cursor()
# Load all water quality parameters stored in two result columns: parameter, unit
if not(unicode(self.settingsdict['wqual_unitcolumn']) ==''): #If there is a a given column for unit
sql =r"""select distinct parameter, """
sql += self.settingsdict['wqual_unitcolumn']
sql +=r""" from """
else: # IF no specific column exist for unit
sql =r"""select distinct parameter, parameter from """ # The twice selection of parameter is a dummy to keep same structure (2 cols) of sql-answer as if a unit column exists
sql += self.settingsdict['wqualtable']
sql += r""" where obsid = '"""
sql += obsid
sql += r"""' ORDER BY parameter"""
parameters_cursor = curs.execute(sql) #Send SQL-syntax to cursor
print(sql)#debug
parameters = parameters_cursor.fetchall()
if not parameters:
qgis.utils.iface.messageBar().pushMessage("Debug","Something is wrong, no parameters are found in table w_qual_lab for "+ obsid, 0 ,duration=10)#DEBUG
return False
print('parameters for ' + obsid + ' is loaded at time: ' + str(time.time()))#debug
# Load all date_times, stored in two result columns: reportnr, date_time
if not (self.settingsdict['wqual_sortingcolumn'] == ''): #If there is a a specific sorting column
sql =r"""select distinct """
sql += self.settingsdict['wqual_sortingcolumn']
sql += r""", date_time from """ #including parameters
else: # IF no specific column exist for sorting
sql =r"""select distinct date_time, date_time from """ # The twice selection of date_time is a dummy to keep same structure (2 cols) of sql-answer as if reportnr exists
sql += self.settingsdict['wqualtable']
sql += """ where obsid = '"""
sql += obsid
sql += """' ORDER BY date_time"""
#sql2 = unicode(sql) #To get back to unicode-string
date_times_cursor = curs.execute(sql) #Send SQL-syntax to cursor,
date_times = date_times_cursor.fetchall()
print (sql)#debug
print('loaded distinct date_time for the parameters for ' + obsid + ' at time: ' + str(time.time()))#debug
if not date_times:
qgis.utils.iface.messageBar().pushMessage("Debug","Something is wrong, no parameters are found in table w_qual_lab for "+ obsid, 0 ,duration=10)#DEBUG
return
ReportTable = ['']*(len(parameters)+2) # Define size of ReportTable
for i in range(len(parameters)+2): # Fill the table with ''
ReportTable[i] = [''] * (len(date_times)+1)
#Populate First 'column' w parameters
parametercounter = 2 #First two rows are for obsid and date_time
for p, u in parameters:
if not(self.settingsdict['wqual_unitcolumn']==''):
if u:
#ReportTable[parametercounter][0] = p.encode(locale.getdefaultlocale()[1]) + ", " + u.encode(locale.getdefaultlocale()[1])
ReportTable[parametercounter][0] = p + ", " + u
else:
#ReportTable[parametercounter][0] = p.encode(locale.getdefaultlocale()[1])
ReportTable[parametercounter][0] = p
else:
#ReportTable[parametercounter][0] = p.encode(locale.getdefaultlocale()[1])
ReportTable[parametercounter][0] = p
parametercounter = parametercounter + 1
print('now go for each parameter value for ' + obsid + ', at time: ' + str(time.time()))#debug
#Populate First 'row' w obsid
datecounter = 1 #First col is 'parametercolumn'
for r, d in date_times: #date_times includes both report and date_time (or possibly date_time and date_time if there is no reportnr)
ReportTable[0][datecounter]=obsid
datecounter += 1
datecounter=1 # first 'column' is for parameter names
for k, v in date_times: # Loop through all report
parametercounter = 1 # first row is for obsid
ReportTable[parametercounter][datecounter] = v # v is date_time
for p, u in parameters:
parametercounter = parametercounter + 1 # one 'row' down after date was stored
sql =r"""SELECT """
sql += self.settingsdict['wqual_valuecolumn']
sql += r""" from """
sql += self.settingsdict['wqualtable']
sql += """ where obsid = '"""
sql += obsid
sql += """' and date_time = '"""
sql += v
if not(self.settingsdict['wqual_unitcolumn'] == '') and u:
sql += """' and parameter = '"""
sql += p
sql += """' and """
sql += self.settingsdict['wqual_unitcolumn']
sql += """ = '"""
sql += u
sql += """'"""
else:
sql += """' and parameter = '"""
sql += p
sql += """'"""
rs = curs.execute(sql) #Send SQL-syntax to cursor, NOTE: here we send sql which was utf-8 already from interpreting it
#print (sql)#debug
#print('time: ' + str(time.time()))#debug
recs = rs.fetchall() # All data are stored in recs
#each value must be in unicode or string to be written as html report
if recs:
try:
ReportTable[parametercounter][datecounter] = utils.returnunicode(recs[0][0])
except:
ReportTable[parametercounter][datecounter]=''
qgis.utils.iface.messageBar().pushMessage("Note!","The value for %s [%s] at %s, %s was not readable. Check your data!"%(p,u,k,v),0,duration=15)
else:
ReportTable[parametercounter][datecounter] =' '
datecounter = datecounter + 1
self.htmlcols = datecounter + 1 # to be able to set a relevant width to the table
parameters_cursor.close()
date_times_cursor.close()
rs.close()
#conn.close()
myconnection.closedb()
return ReportTable
def dbtype_combobox(self, value):
index = self._dbtype_combobox.findText(utils.returnunicode(value))
if index != -1:
self._dbtype_combobox.setCurrentIndex(index)
def connection(self):
return utils.returnunicode(self._connection.currentText())
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
if db_settings is None:
db_settings = QgsProject.instance().readEntry(
"Midvatten", "database")[0]
if isinstance(db_settings, basestring):
#Test if the db_setting is an old database
if os.path.isfile(db_settings):
db_settings = {u'spatialite': {u'dbpath': db_settings}}
else:
if not db_settings:
# TODO: Something feels off here. It should not return None, as that will just cause other hard to solve errors.
# TODO An exception feels better but is uglier for the user.
utils.MessagebarAndLog.critical(bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'Database not chosen correctly. Check DB tab in Midvatten settings.'
)))
return None
else:
try:
db_settings = ast.literal_eval(db_settings)
except:
#TODO: Something feels off here. It should not return None, as that will just cause other hard to solve errors.
#TODO An exception feels better but is uglier for the user.
utils.MessagebarAndLog.critical(
bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'Database connection failed. Try reset settings.'
)))
return None
elif isinstance(db_settings, dict):
pass
else:
raise Exception(
utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u"DbConnectionManager error: db_settings must be either a dict like {u'spatialite': {u'dbpath': u'x'} or a string representation of it. Was: %s"
)) % utils.returnunicode(db_settings))
db_settings = utils.returnunicode(db_settings, keep_containers=True)
self.db_settings = db_settings
self.dbtype = self.db_settings.keys()[0]
self.connection_settings = self.db_settings.values()[0]
self.uri = QgsDataSourceURI()
if self.dbtype == u'spatialite':
self.dbpath = utils.returnunicode(
self.connection_settings[u'dbpath'])
self.check_db_is_locked()
#Create the database if it's not existing
self.uri.setDatabase(self.dbpath)
try:
self.conn = sqlite.connect(self.dbpath,
detect_types=sqlite.PARSE_DECLTYPES
| sqlite.PARSE_COLNAMES)
except:
utils.MessagebarAndLog.critical(bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'Connecting to spatialite db %s failed! Check that the file or path exists.'
)) % self.dbpath)
self.conn = None
else:
try:
self.connector = spatialite_connector.SpatiaLiteDBConnector(
self.uri)
except:
pass
self.cursor = self.conn.cursor()
elif self.dbtype == u'postgis':
connection_name = self.connection_settings[u'connection'].split(
u'/')[0]
self.postgis_settings = get_postgis_connections()[connection_name]
self.uri.setConnection(self.postgis_settings[u'host'],
self.postgis_settings[u'port'],
self.postgis_settings[u'database'],
self.postgis_settings[u'username'],
self.postgis_settings[u'password'])
try:
self.connector = postgis_connector.PostGisDBConnector(self.uri)
except Exception as e:
if u'no password supplied' in str(e):
utils.MessagebarAndLog.warning(bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'No password supplied for postgis connection')))
raise utils.UserInterruptError()
else:
raise
self.conn = self.connector.connection
self.cursor = self.connector._get_cursor()