def export_2_csv(self,exportfolder):
database = utils.dbconnection()
database.connect2db() #establish connection to the current midv db
curs = database.conn.cursor()#get a cursor
#--------First export selected obs_points and corresponding data---------------------------
if len(self.ID_obs_points)>0:#only if there are any obs_points selected at all
ptabs = ['obs_points', 'w_levels', 'w_levels_logger', 'w_flow', 'w_qual_lab', 'w_qual_field', 'stratigraphy', 'meteo']
for tname in ptabs:
# problems with string replacement and tuples (no success with parameter substitution)
# and therefore different sql_clause depending on number of obs
if len(self.ID_obs_points)==1:
sql_clause = r"""select count(obsid) from %s where obsid in ('%s')""" %(tname, self.ID_obs_points[0])
elif len(self.ID_obs_points)>1:
sql_clause = r"select count(obsid) from %s where obsid in %s" %(tname, self.ID_obs_points)
no_of_obs_cursor = curs.execute(sql_clause)
no_of_obs = no_of_obs_cursor.fetchall()
if no_of_obs[0][0] > 0:#only go on if there are any observations for this obsid
some_data = 0
if len(self.ID_obs_points)==1:#problems to combine parameter substitution and string replacement in same sql clause, should be fixed
sql_clause = r"""select * from %s where obsid in ('%s')""" %(tname, self.ID_obs_points[0])
some_data = 1
elif len(self.ID_obs_points)>1:
sql_clause = r"select * from %s where obsid in %s" %(tname, self.ID_obs_points)
some_data = 1
if some_data !=0:
output = UnicodeWriter(file(os.path.join(exportfolder,tname + ".csv"), 'w'))
curs.execute(sql_clause)
output.writerow([col[0] for col in curs.description])
filter(None, (output.writerow(row) for row in curs))
#--------Then export selected obs_lines with corresponding data---------------------------
if len(self.ID_obs_lines)>0:#only if there are any obs_points selected at all
ptabs = ['obs_lines', 'vlf_data', 'seismic_data']
for tname in ptabs:
# problems with string replacement and tuples (no success with parameter substitution)
# and therefore different sql_clause depending on number of obs
if len(self.ID_obs_lines)==1:
sql_clause = r"""select count(obsid) from %s where obsid in ('%s')""" %(tname, self.ID_obs_lines[0])
elif len(self.ID_obs_lines)>1:
sql_clause = r"select count(obsid) from %s where obsid in %s" %(tname, self.ID_obs_lines)
no_of_obs_cursor = curs.execute(sql_clause)
no_of_obs = no_of_obs_cursor.fetchall()
if no_of_obs[0][0] > 0:#only go on if there are any observations for this obsid
some_data = 0
if len(self.ID_obs_lines)==1:#problems to combine parameter substitution and string replacement in same sql clause, should be fixed
sql_clause = r"""select * from %s where obsid in ('%s')""" %(tname, self.ID_obs_lines[0])
some_data = 1
elif len(self.ID_obs_lines)>1:
sql_clause = r"select * from %s where obsid in %s" %(tname, self.ID_obs_lines)
some_data = 1
if some_data !=0:
output = UnicodeWriter(file(os.path.join(exportfolder,tname + ".csv"), 'w'))
curs.execute(sql_clause)
output.writerow([col[0] for col in curs.description])
filter(None, (output.writerow(row) for row in curs))
database.closedb()
def _getDataStep2(self, surveys):
""" STEP 2: get strata information for every point """
myconnection = utils.dbconnection()
if myconnection.connect2db() == True:
# create a cursor
curs = myconnection.conn.cursor()
for (obsid, survey) in surveys.iteritems():
sql =r"""SELECT stratid, depthtop, depthbot, geology, geoshort, capacity, comment, development FROM """
sql += self.stratitable #MacOSX fix1
sql += r""" WHERE obsid = '"""
sql += str(obsid) # THIS IS WHERE THE KEY IS GIVEN TO LOAD STRATIGRAPHY FOR CHOOSEN obsid
sql += """' ORDER BY stratid"""
rs = curs.execute(sql) #Send SQL-syntax to cursor
recs = rs.fetchall() # All data are stored in recs
# parse attributes
for record in recs:
if utils.isinteger(record[0]) and utils.isfloat(record[1]) and utils.isfloat(record[2]):
stratigaphy_id = record[0] # Stratigraphy layer no
depthtotop = record[1] # depth to top of stratrigraphy layer
depthtobot = record[2] # depth to bottom of stratrigraphy layer
else:
raise DataSanityError(str(obsid), "Something bad with stratid, depthtop or depthbot!")
stratigaphy_id = 1 # when something went wrong, put it into first layer
depthtotop = 0
depthtobot = 999#default value when something went wrong
if record[3]: # Must check since it is not possible to print null values as text in qt widget
geology = record[3] # Geology full text
else:
geology = " "
geo_short_txt = record[4] # geo_short might contain national special characters
if geo_short_txt: # Must not try to encode an empty field
geo_short = unicodedata.normalize('NFKD', geo_short_txt).encode('ascii','ignore') # geo_short normalized for symbols and color
else: # If the field is empty, then store an empty string
geo_short = ''
hydro = record[5] # waterloss (hydrogeo parameter) for color
if record[6]: # Must check since it is not possible to print null values as text in qt widget
comment = record[6] #
else:
comment = " "
if record[7]: # Must check since it is not possible to print null values as text in qt widget
development = record[7] #
else:
development = " "
st = StrataInfo(stratigaphy_id, depthtotop, depthtobot, geology, geo_short, hydro, comment, development)
# add strata information (in right order)
insertAt = 0
for a in survey.strata:
if a.stratid > stratigaphy_id:
break
insertAt += 1
survey.strata.insert(insertAt, st)
""" Close SQLite-connections """
rs.close() # First close the table
myconnection.closedb()# then close the database
DataLoadingStatus = True
return DataLoadingStatus, surveys
else:
return False, surveys
def export_2_csv(self,exportfolder):
database = utils.dbconnection()
database.connect2db() #establish connection to the current midv db
self.curs = database.conn.cursor()#get a cursor
self.exportfolder = exportfolder
self.write_data(self.to_csv, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'), utils.verify_table_exists)
self.write_data(self.to_csv, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'), utils.verify_table_exists)
self.write_data(self.zz_to_csv, u'no_obsids', defs.get_subset_of_tables_fr_db(category='data_domains'), utils.verify_table_exists)
database.closedb()
def export_2_csv(self,exportfolder):
database = utils.dbconnection()
database.connect2db() #establish connection to the current midv db
self.curs = database.conn.cursor()#get a cursor
self.exportfolder = exportfolder
self.write_data(self.to_csv, self.ID_obs_points, ['obs_points', 'comments', 'w_levels', 'w_levels_logger', 'w_flow', 'w_qual_lab', 'w_qual_field', 'stratigraphy', 'meteo'], utils.verify_table_exists)
self.write_data(self.to_csv, self.ID_obs_lines, ['obs_lines', 'vlf_data', 'seismic_data'], utils.verify_table_exists)
self.write_data(self.zz_to_csv, u'no_obsids', ['zz_flowtype', 'zz_meteoparam', 'zz_staff', 'zz_strat', 'zz_capacity', 'zz_w_qual_field_parameters'], utils.verify_table_exists)
database.closedb()
def load_obsid_from_db(self):
self.combobox_obsid.clear()
myconnection = utils.dbconnection()
if myconnection.connect2db() == True:
# skapa en cursor
curs = myconnection.conn.cursor()
rs=curs.execute("""select distinct obsid from w_levels_logger order by obsid""")
self.combobox_obsid.addItem('')
for row in curs:
self.combobox_obsid.addItem(row[0])
rs.close()
myconnection.closedb()
def LoadColumnsFromTable(self, table=''):
""" This method returns a list with all the columns in the table"""
if len(table)>0: # Should not be needed since the function never should be called without existing table...
myconnection = utils.dbconnection()
if myconnection.connect2db() == True:
# skapa en cursor
curs = myconnection.conn.cursor()
sql = r"""SELECT * FROM '"""
sql += unicode(table)
sql += """'"""
rs=curs.execute(sql)
columns = {}
columns = [tuple[0] for tuple in curs.description]
rs.close()
myconnection.closedb()
else:
#QMessageBox.information(None,"info","no table is loaded") # DEBUGGING
columns = {}
return columns # This method returns a list with all the columns in the table
def LoadTablesFromDB( self ): # Open the SpatiaLite file to extract info about tables
self.table_ComboBox_1.clear()
self.table_ComboBox_2.clear()
self.table_ComboBox_3.clear()
for i in range (1,3):
self.clearthings(i)
myconnection = utils.dbconnection()
if myconnection.connect2db() == True:
# skapa en cursor
curs = myconnection.conn.cursor()
rs=curs.execute(r"""SELECT tbl_name FROM sqlite_master WHERE (type='table' or type='view') and not (name in""" + midvatten_defs.SQLiteInternalTables() + r""") ORDER BY tbl_name""") #SQL statement to get the relevant tables in the spatialite database
#self.dbTables = {}
self.table_ComboBox_1.addItem('')
self.table_ComboBox_2.addItem('')
self.table_ComboBox_3.addItem('')
for row in curs:
self.table_ComboBox_1.addItem(row[0])
self.table_ComboBox_2.addItem(row[0])
self.table_ComboBox_3.addItem(row[0])
rs.close()
myconnection.closedb()
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 drawPlot(self):
QtGui.QApplication.setOverrideCursor(QtGui.QCursor(QtCore.Qt.WaitCursor))#show the user this may take a long time...
self.axes.clear()
My_format = [('date_time', datetime.datetime), ('values', float)] #Define (with help from function datetime) a good format for numpy array
myconnection = utils.dbconnection()
myconnection.connect2db()
curs = myconnection.conn.cursor()
i = 0
nop=0# nop=number of plots
self.p=[]
self.plabels=[]
if not (self.table_ComboBox_1.currentText() == '' or self.table_ComboBox_1.currentText()==' ') and not (self.xcol_ComboBox_1.currentText()== '' or self.xcol_ComboBox_1.currentText()==' ') and not (self.ycol_ComboBox_1.currentText()== '' or self.ycol_ComboBox_1.currentText()==' '): #if anything is to be plotted from tab 1
self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots
plottable1='y'
filter1 = unicode(self.Filter1_ComboBox_1.currentText())
filter1list = []
filter2list = []
filter1list = self.Filter1_QListWidget_1.selectedItems()
filter2 = unicode(self.Filter2_ComboBox_1.currentText())
filter2list= self.Filter2_QListWidget_1.selectedItems()
nop += max(len(filter1list),1)*max(len(filter2list),1)
#self.p= [None]*nop#list for plot objects
self.p.extend([None]*nop)#list for plot objects
self.plabels.extend([None]*nop)# List for plot labels
while i < len(self.p):
if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]):
for item1 in filter1list:
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText())
i += 1
elif not (filter1 == '' or filter1==' ' or filter1list==[]):
for item1 in filter1list:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(item1.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText())
i += 1
elif not (filter2 == '' or filter2==' ' or filter2list==[]):
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText())
i += 1
else:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(self.ycol_ComboBox_1.currentText())+""", """+unicode(self.table_ComboBox_1.currentText())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText())
i += 1
if not (self.table_ComboBox_2.currentText() == '' or self.table_ComboBox_2.currentText()==' ') and not (self.xcol_ComboBox_2.currentText()== '' or self.xcol_ComboBox_2.currentText()==' ') and not (self.ycol_ComboBox_2.currentText()== '' or self.ycol_ComboBox_2.currentText()==' '):#if anything is to be plotted from tab 2
self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots
plottable2='y'
filter1 = unicode(self.Filter1_ComboBox_2.currentText())
filter1list = []
filter2list = []
filter1list = self.Filter1_QListWidget_2.selectedItems()
filter2 = unicode(self.Filter2_ComboBox_2.currentText())
filter2list= self.Filter2_QListWidget_2.selectedItems()
nop =+ max(len(filter1list),1)*max(len(filter2list),1)
self.p.extend([None]*nop)#list for plot objects
self.plabels.extend([None]*nop)# List for plot labels
while i < len(self.p):
if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]):
for item1 in filter1list:
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText())
i += 1
elif not (filter1 == '' or filter1==' ' or filter1list==[]):
for item1 in filter1list:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(item1.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText())
i += 1
elif not (filter2 == '' or filter2==' ' or filter2list==[]):
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText())
i += 1
else:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(self.ycol2)+""", """+unicode(self.table_ComboBox_2.currentText())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText())
i += 1
if not (self.table_ComboBox_3.currentText() == '' or self.table_ComboBox_3.currentText()==' ') and not (self.xcol_ComboBox_3.currentText()== '' or self.xcol_ComboBox_3.currentText()==' ') and not (self.ycol_ComboBox_3.currentText()== '' or self.ycol_ComboBox_3.currentText()==' '):#if anything is to be plotted from tab 3
self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots
plottable3='y'
filter1 = unicode(self.Filter1_ComboBox_3.currentText())
filter1list = []
filter2list = []
filter1list = self.Filter1_QListWidget_3.selectedItems()
filter2 = unicode(self.Filter2_ComboBox_3.currentText())
filter2list= self.Filter2_QListWidget_3.selectedItems()
nop =+ max(len(filter1list),1)*max(len(filter2list),1)
self.p.extend([None]*nop)#list for plot objects
self.plabels.extend([None]*nop)# List for plot labels
while i < len(self.p):
if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]):
for item1 in filter1list:
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText())
i += 1
elif not (filter1 == '' or filter1==' ' or filter1list==[]):
for item1 in filter1list:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(item1.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText())
i += 1
elif not (filter2 == '' or filter2==' ' or filter2list==[]):
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText())
i += 1
else:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(self.ycol_ComboBox_3.currentText())+""", """+unicode(self.table_ComboBox_3.currentText())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText())
i += 1
#rs.close() # close the cursor
myconnection.closedb() # close the database
self.refreshPlot()
QtGui.QApplication.restoreOverrideCursor()#now this long process is done and the cursor is back as normal
def drawPlot(self):
QtGui.QApplication.setOverrideCursor(QtGui.QCursor(QtCore.Qt.WaitCursor))#show the user this may take a long time...
self.axes.clear()
self.axes.legend_ = None
My_format = [('date_time', datetime.datetime), ('values', float)] #Define (with help from function datetime) a good format for numpy array
myconnection = utils.dbconnection()
myconnection.connect2db()
curs = myconnection.conn.cursor()
i = 0
nop=0# nop=number of plots
self.p=[]
self.plabels=[]
if not (self.table_ComboBox_1.currentText() == '' or self.table_ComboBox_1.currentText()==' ') and not (self.xcol_ComboBox_1.currentText()== '' or self.xcol_ComboBox_1.currentText()==' ') and not (self.ycol_ComboBox_1.currentText()== '' or self.ycol_ComboBox_1.currentText()==' '): #if anything is to be plotted from tab 1
self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots
plottable1='y'
filter1 = unicode(self.Filter1_ComboBox_1.currentText())
filter1list = []
filter2list = []
filter1list = self.Filter1_QListWidget_1.selectedItems()
filter2 = unicode(self.Filter2_ComboBox_1.currentText())
filter2list= self.Filter2_QListWidget_1.selectedItems()
nop += max(len(filter1list),1)*max(len(filter2list),1)
#self.p= [None]*nop#list for plot objects
self.p.extend([None]*nop)#list for plot objects
self.plabels.extend([None]*nop)# List for plot labels
try:
factor1 = float(self.LineEditFactor1.text().replace(',','.'))
except ValueError:
factor1 = 1.0
try:
offset1 = float(self.LineEditOffset1.text().replace(',','.'))
except ValueError:
offset1 = 0.0
remove_mean1 = self.checkBox_remove_mean1.isChecked()
while i < len(self.p):
if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]):
for item1 in filter1list:
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1)
i += 1
elif not (filter1 == '' or filter1==' ' or filter1list==[]):
for item1 in filter1list:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(item1.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1)
i += 1
elif not (filter2 == '' or filter2==' ' or filter2list==[]):
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1)
i += 1
else:
sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_1.currentText())
self.plabels[i] = unicode(self.ycol_ComboBox_1.currentText())+""", """+unicode(self.table_ComboBox_1.currentText())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1)
i += 1
if not (self.table_ComboBox_2.currentText() == '' or self.table_ComboBox_2.currentText()==' ') and not (self.xcol_ComboBox_2.currentText()== '' or self.xcol_ComboBox_2.currentText()==' ') and not (self.ycol_ComboBox_2.currentText()== '' or self.ycol_ComboBox_2.currentText()==' '):#if anything is to be plotted from tab 2
self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots
plottable2='y'
filter1 = unicode(self.Filter1_ComboBox_2.currentText())
filter1list = []
filter2list = []
filter1list = self.Filter1_QListWidget_2.selectedItems()
filter2 = unicode(self.Filter2_ComboBox_2.currentText())
filter2list= self.Filter2_QListWidget_2.selectedItems()
nop =+ max(len(filter1list),1)*max(len(filter2list),1)
self.p.extend([None]*nop)#list for plot objects
self.plabels.extend([None]*nop)# List for plot labels
try:
factor2 = float(self.LineEditFactor2.text().replace(',','.'))
except ValueError:
factor2 = 1.0
try:
offset2 = float(self.LineEditOffset2.text().replace(',','.'))
except ValueError:
offset2 = 0.0
remove_mean2 = self.checkBox_remove_mean2.isChecked()
while i < len(self.p):
if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]):
for item1 in filter1list:
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2)
i += 1
elif not (filter1 == '' or filter1==' ' or filter1list==[]):
for item1 in filter1list:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(item1.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2)
i += 1
elif not (filter2 == '' or filter2==' ' or filter2list==[]):
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2)
i += 1
else:
sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_2.currentText())
self.plabels[i] = unicode(self.ycol2)+""", """+unicode(self.table_ComboBox_2.currentText())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2)
i += 1
if not (self.table_ComboBox_3.currentText() == '' or self.table_ComboBox_3.currentText()==' ') and not (self.xcol_ComboBox_3.currentText()== '' or self.xcol_ComboBox_3.currentText()==' ') and not (self.ycol_ComboBox_3.currentText()== '' or self.ycol_ComboBox_3.currentText()==' '):#if anything is to be plotted from tab 3
self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots
plottable3='y'
filter1 = unicode(self.Filter1_ComboBox_3.currentText())
filter1list = []
filter2list = []
filter1list = self.Filter1_QListWidget_3.selectedItems()
filter2 = unicode(self.Filter2_ComboBox_3.currentText())
filter2list= self.Filter2_QListWidget_3.selectedItems()
nop =+ max(len(filter1list),1)*max(len(filter2list),1)
self.p.extend([None]*nop)#list for plot objects
self.plabels.extend([None]*nop)# List for plot labels
try:
factor3 = float(self.LineEditFactor3.text().replace(',','.'))
except ValueError:
factor3 = 1.0
try:
offset3 = float(self.LineEditOffset3.text().replace(',','.'))
except ValueError:
offset3 = 0.0
remove_mean3 = self.checkBox_remove_mean3.isChecked()
while i < len(self.p):
if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]):
for item1 in filter1list:
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3)
i += 1
elif not (filter1 == '' or filter1==' ' or filter1list==[]):
for item1 in filter1list:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(item1.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3)
i += 1
elif not (filter2 == '' or filter2==' ' or filter2list==[]):
for item2 in filter2list:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(item2.text())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3)
i += 1
else:
sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_3.currentText())
self.plabels[i] = unicode(self.ycol_ComboBox_3.currentText())+""", """+unicode(self.table_ComboBox_3.currentText())
self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3)
i += 1
#rs.close() # close the cursor
myconnection.closedb() # close the database
self.xaxis_formatters = (self.axes.xaxis.get_major_formatter(), self.axes.xaxis.get_major_locator())
self.axes.set_title(self.ms.settingsdict['custplot_title'])
self.axes.set_xlabel(self.ms.settingsdict['custplot_xtitle'])
self.axes.set_ylabel(self.ms.settingsdict['custplot_ytitle'])
self.drawn = True
self.refreshPlot()
QtGui.QApplication.restoreOverrideCursor()#now this long process is done and the cursor is back as normal
def showtheplot(self, layer): # PlotTS method that, at the moment, performs all the real work
provider = layer.dataProvider() #Something with OGR
kolumnindex = provider.fieldNameIndex('obsid') # To find the column named 'obsid'
if kolumnindex == -1:
kolumnindex = provider.fieldNameIndex('OBSID') # backwards compatibility
if(layer):
nF = layer.selectedFeatureCount()
if (nF > 0):
myconnection = utils.dbconnection()
if myconnection.connect2db() == True:
# skapa en cursor
curs = myconnection.conn.cursor()
# Load all selected observation points
ob = layer.selectedFeatures()
# Create a plot window with one single subplot
fig = plt.figure() # causes conflict with plugins "statist" and "chartmaker"
ax = fig.add_subplot(111)
p=[None]*nF # List for plot objects
plabel=[None]*nF # List for label strings
i=0
for k in ob: # Loop through all selected objects, a plot is added for each one of the observation points (i.e. selected objects)
obsid = unicode(ob[i][kolumnindex])
# Load all observations (full time series) for the object [i] (i.e. selected observation point no i)
sql =r"""SELECT date_time as 'date [datetime]', """
sql += unicode(self.settingsdict['tscolumn']) #MacOSX fix1
sql += """ FROM """
sql += unicode(self.settingsdict['tstable']) #MacOSX fix1
sql += r""" WHERE obsid = '"""
sql += obsid
sql += """' ORDER BY date_time """
rs = curs.execute(sql) #Send SQL-syntax to cursor
recs = rs.fetchall() # All data are stored in recs
"""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 inte callable objects, i.e. write table2.values
""" Get help from function datestr2num to get date and time into float"""
myTimestring = [] #LIST
j = 0
for row in table2:
myTimestring.append(table2.date_time[j])
j = j + 1
numtime=datestr2num(myTimestring) #conv list of strings to numpy.ndarray of floats
if self.settingsdict['tsdotmarkers']==2: # If the checkbox is checked - markers will be plotted #MacOSX fix1
if self.settingsdict['tsstepplot']==2: # If the checkbox is checked - draw a step plot #MacOSX fix1
p[i], = ax.plot_date(numtime, table2.values, marker = 'o', linestyle = '-', drawstyle='steps-pre', label=obsid) # PLOT!!
else:
p[i], = ax.plot_date(numtime, table2.values, 'o-', label=obsid)
else: # NO markers wil be plotted, , just a line
if self.settingsdict['tsstepplot']==2: # If the checkbox is checked - draw a step plot #MacOSX fix1
p[i], = ax.plot_date(numtime, table2.values, marker = 'None', linestyle = '-', drawstyle='steps-pre', label=obsid) # PLOT!!
else:
p[i], = ax.plot_date(numtime, table2.values, '-', label=obsid)
plabel[i] = obsid # Label for the plot
i = i+1
""" Close SQLite-connections """
rs.close() # First close the table
myconnection.closedb()# then close the database
""" Finish plot """
ax.grid(True)
ax.yaxis.set_major_formatter(tick.ScalarFormatter(useOffset=False, useMathText=False))
fig.autofmt_xdate()
ax.set_ylabel(self.settingsdict['tscolumn']) #MacOSX fix1
ax.set_title(self.settingsdict['tstable'])#MacOSX fix1
leg = fig.legend(p, plabel, loc=0)#leg = fig.legend(p, plabel, 'right')
leg.draggable(state=True)
frame = leg.get_frame() # the matplotlib.patches.Rectangle instance surrounding the legend
frame.set_facecolor('0.80') # set the frame face color to light gray
frame.set_fill(False) # set the frame face color transparent
for t in leg.get_texts():
t.set_fontsize(10) # the legend text fontsize
for label in ax.xaxis.get_ticklabels():
label.set_fontsize(10)
for label in ax.yaxis.get_ticklabels():
label.set_fontsize(10)
#plt.ion()#force interactivity to prevent the plot window from blocking the qgis app
plt.show()
#plt.draw()
else:
utils.pop_up_info("Please select at least one point with time series data")
else:
utils.pop_up_info("Please select a layer with time series observation points")
