def __checkLoggingTableExists(self): sql = "SELECT table_name FROM information_schema.tables WHERE table_name = 'postnas_search_logging'"; self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if(query.size() > 0): return True else: return False
def __createLoggingTable(self): file_path = os.path.dirname(os.path.realpath(__file__)) + "/create_loggingtable/create_logging_table.sql" sql = open(file_path).read() self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if(query.lastError().number() == -1): return True else: return False
def __init__(self, plugin): QDialog.__init__(self) self.setupUi(self) self.plugin = plugin s = QSettings("norBIT", "norGIS-ALKIS-Erweiterung") v = QIntValidator() v.setBottom(1) self.leHighlightThreshold.setValidator(v) self.leHighlightThreshold.setText(str(s.value("highlightThreshold", 1000))) (db, conninfo) = self.plugin.opendb() self.db = db qry = QSqlQuery(db) if not qry.exec_("SELECT has_table_privilege('eigner', 'SELECT')") or not qry.next() or not qry.value(0): self.tabWidget.removeTab(self.tabWidget.indexOf(self.tabEigentuemer)) self.replaceButton = self.buttonBox.addButton(u"Ersetzen", QDialogButtonBox.ActionRole) self.addButton = self.buttonBox.addButton(u"Hinzufügen", QDialogButtonBox.ActionRole) self.removeButton = self.buttonBox.addButton(u"Entfernen", QDialogButtonBox.ActionRole) self.clearButton = self.buttonBox.addButton(u"Leeren", QDialogButtonBox.ActionRole) self.replaceButton.clicked.connect(self.replaceClicked) self.addButton.clicked.connect(self.addClicked) self.removeButton.clicked.connect(self.removeClicked) self.clearButton.clicked.connect(self.clearClicked) self.cbxStrassen.setEnabled(False) self.cbxHNR.setEnabled(False) self.pbLabelSearch.clicked.connect(self.evaluate) self.pbOwnerSearch.clicked.connect(self.evaluate) self.pbSearchFSK.clicked.connect(self.evaluate) self.highlighted = set(self.plugin.highlighted()) self.lblResult.setText(u"{} Objekte bereits gewählt.".format(len(self.highlighted)) if len(self.highlighted) > 0 else "") self.restoreGeometry(QSettings("norBIT", "norGIS-ALKIS-Erweiterung").value("searchgeom", QByteArray(), type=QByteArray)) self.tabWidget.setCurrentIndex(s.value("suchmodus", 0, type=int)) self.cbxGemarkung.currentIndexChanged.connect(self.gfzn) self.cbxFlur.currentIndexChanged.connect(self.gfzn) self.cbxFSZ.currentIndexChanged.connect(self.gfzn) self.cbxFSN.currentIndexChanged.connect(self.gfzn) self.gfzn()
def on_pbSearchStr_clicked(self): # qDebug("on_pbSearchStr_clicked: text={}".format(self.leStr.text())) qry = QSqlQuery(self.db) self.cbxStrassen.blockSignals(True) self.cbxStrassen.clear() if qry.exec_(u"SELECT k.schluesselgesamt, k.bezeichnung || coalesce(', ' || g.bezeichnung,'') FROM ax_lagebezeichnungkatalogeintrag k LEFT OUTER JOIN ax_gemeinde g ON k.land=g.land AND k.regierungsbezirk=g.regierungsbezirk AND k.kreis=g.kreis AND k.gemeinde::int=g.gemeinde::int AND g.endet IS NULL WHERE lower(k.bezeichnung) LIKE {0} AND k.endet IS NULL ORDER BY k.bezeichnung || coalesce(', ' || g.bezeichnung,'')".format(quote(self.leStr.text().lower() + '%'))): while qry.next(): self.cbxStrassen.addItem(qry.value(1), qry.value(0)) self.cbxStrassen.blockSignals(False) self.lblResult.setText(u"Keine Straßen gefunden" if self.cbxStrassen.count() == 0 else u"{} Straßen gefunden".format(self.cbxStrassen.count())) self.cbxStrassen.setEnabled(self.cbxStrassen.count() > 0) self.cbxStrassen.setCurrentIndex(0 if self.cbxStrassen.count() == 1 else -1) self.on_cbxStrassen_currentIndexChanged(self.cbxStrassen.currentIndex())
def on_cbxStrassen_currentIndexChanged(self, index): # qDebug(u"on_cbxStrassen_currentIndexChanged: index={} text={}".format(self.cbxStrassen.currentIndex(), self.cbxStrassen.currentText())) qry = QSqlQuery(self.db) schluesselgesamt = self.cbxStrassen.itemData(self.cbxStrassen.currentIndex()) self.cbxHNR.blockSignals(True) self.cbxHNR.clear() if qry.exec_(u"SELECT h.hausnummer FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k ON h.land=k.land AND h.regierungsbezirk=k.regierungsbezirk AND h.kreis=k.kreis AND h.gemeinde=k.gemeinde AND h.lage=k.lage WHERE k.schluesselgesamt={0} ORDER BY NULLIF(regexp_replace(h.hausnummer, E'\\\\D', '', 'g'), '')::int".format(quote(schluesselgesamt))): while qry.next(): self.cbxHNR.addItem(qry.value(0)) if self.cbxHNR.count() > 1: self.cbxHNR.addItem("Alle") self.cbxHNR.blockSignals(False) self.cbxHNR.setEnabled(self.cbxHNR.count() > 0) self.cbxHNR.setCurrentIndex(0 if self.cbxHNR.count() == 1 else -1)
def fetchall(self, db, sql): rows = [] qry = QSqlQuery(db) if qry.exec_(sql): rec = qry.record() while qry.next(): row = {} for i in range(0, rec.count()): v = "%s" % qry.value(i) if v == "NULL": v = '' row[rec.fieldName(i)] = v.strip() rows.append(row) else: qDebug("Exec failed: " + qry.lastError().text()) return rows
def execSQLCommand(self, sql, ignore_errors=False): self.assertTrue(self.conn) query = QSqlQuery(self.conn) self.assertTrue(query.exec_(sql), sql + ': ' + query.lastError().text()) query.finish()
class QtSqlDBCursor(object): def __init__(self, conn): self.qry = QSqlQuery(conn) self.description = None self.rowcount = -1 self.arraysize = 1 def close(self): self.qry.finish() def execute(self, operation, parameters=[]): if len(parameters) == 0: if not self.qry.exec_(operation): raise ExecError(self.qry.lastError().databaseText()) else: if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for i in range(len(parameters)): self.qry.bindValue(i, parameters[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) self.rowcount = self.qry.size() self.description = [] for c in range(self.qry.record().count()): f = self.qry.record().field(c) if f.type() == QVariant.Date: t = Date elif f.type() == QVariant.Time: t = Time elif f.type() == QVariant.DateTime: t = Timestamp elif f.type() == QVariant.Double: t = float elif f.type() == QVariant.Int: t = int elif f.type() == QVariant.String: t = str elif f.type() == QVariant.ByteArray: t = str else: continue self.description.append([ f.name(), # name t, # type_code f.length(), # display_size f.length(), # internal_size f.precision(), # precision None, # scale f.requiredStatus() != QSqlField.Required # null_ok ]) def executemany(self, operation, seq_of_parameters): if len(seq_of_parameters) == 0: return if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for r in seq_of_parameters: for i in range(len(r)): self.qry.bindValue(i, r[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) def scroll(self, row): return self.qry.seek(row) def fetchone(self): if not next(self.qry): return None row = [] for i in range(len(self.description)): value = self.qry.value(i) if (isinstance(value, QDate) or isinstance(value, QTime) or isinstance(value, QDateTime)): value = value.toString() elif isinstance(value, QByteArray): value = u"GEOMETRY" # value = value.toHex() row.append(value) return row def fetchmany(self, size=10): rows = [] while len(rows) < size: row = self.fetchone() if row is None: break rows.append(row) return rows def fetchall(self): rows = [] while True: row = self.fetchone() if row is None: break rows.append(row) return rows def setinputsize(self, sizes): raise ExecError("nyi") def setoutputsize(self, size, column=None): raise ExecError("nyi")
def get_postprocessing_queries(self): """Gets the SQL queries that are stored in the sqlite database for the postprocessing process which is done in postgis. Language support: Everything that is not french or italian will be german. Returns: False: If the queries could not be fetched from the sqlite database. Otherwise a list with the SQL queries. """ path = "modules/%s/postprocessing/postprocessing.db" % self.app_module filename = get_absolute_path(path) self.report_progress("Info: getting postprocessing queries...") try: # This is NOT the project db connection_name = 'postprocessing_' + self.app_module db = open_sqlite_db(filename, connection_name) locale = QSettings().value('locale/userLocale')[0:2] if locale == "fr": lang = locale elif locale == "it": lang = locale else: lang = "de" sql = "SELECT * FROM postprocessing " \ "WHERE (lang = '%s' " \ "OR lang IS NULL) AND apply = 1 " \ "ORDER BY 'order', ogc_fid;" % lang query = db.exec_(sql) if not query.isActive(): message = "Database query not active." raise VerisoError( message, long_message=QSqlQuery.lastError(query).text()) queries = [] record = query.record() while query.next(): sql_query = str(query.value(record.indexOf("sql_query"))) sql_query = sql_query.replace("$$DBSCHEMA", self.db_schema) sql_query = sql_query.replace("$$USER", self.db_user) sql_query = sql_query.replace("$$EPSG", self.epsg) queries.append(sql_query) db.close() del db return queries except Exception as e: message = "Something went wrong while catching postprocessing " \ "queries from sqlite database. You need to delete the " \ "database schema manually." raise VerisoError(message, e)
def read_attributes(self): query = QSqlQuery(self.db) query_str = ( "select cou.remarks, sty.name, mdl.name, cla.name " "from comptages.count as cou " "join comptages.sensor_type as sty on cou.id_sensor_type = sty.id " "join comptages.model as mdl on cou.id_model = mdl.id " "join comptages.class as cla on cou.id_class = cla.id " "where cou.id = {} ".format(self.count_id)) query.exec_(query_str) query.next() self.attributes['remarks'] = query.value(0) self.attributes['sensor_type'] = query.value(1) self.attributes['model'] = query.value(2) self.attributes['class'] = query.value(3) query_str = ( "select sec.owner, sec.road, sec.start_pr, sec.end_pr, " "sec.start_dist, sec.end_dist, sec.place_name, " "lan.direction, lan.direction_desc " "from comptages.section as sec " "inner join comptages.lane as lan on sec.id = lan.id_section " "where sec.id = '{}' ".format(self.section_id)) query.exec_(query_str) while query.next(): self.attributes['owner'] = query.value(0) self.attributes['road'] = query.value(1) self.attributes['start_pr'] = query.value(2) self.attributes['end_pr'] = query.value(3) self.attributes['start_dist'] = query.value(4) self.attributes['end_dist'] = query.value(5) self.attributes['place_name'] = query.value(6) if int(query.value(7)) == 1: self.attributes['dir1'] = query.value(8) elif int(query.value(7)) == 2: self.attributes['dir2'] = query.value(8)
class QtSqlDBCursor: def __init__(self, conn): self.qry = QSqlQuery(conn) self.description = None self.rowcount = -1 self.arraysize = 1 def close(self): self.qry.finish() def execute(self, operation, parameters=[]): if len(parameters) == 0: if not self.qry.exec_(operation): raise ExecError(self.qry.lastError().databaseText()) else: if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for i in range(len(parameters)): self.qry.bindValue(i, parameters[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) self.rowcount = self.qry.size() self.description = [] for c in range(self.qry.record().count()): f = self.qry.record().field(c) if f.type() == QVariant.Date: t = Date elif f.type() == QVariant.Time: t = Time elif f.type() == QVariant.DateTime: t = Timestamp elif f.type() == QVariant.Double: t = float elif f.type() == QVariant.Int: t = int elif f.type() == QVariant.String: t = unicode elif f.type() == QVariant.ByteArray: t = unicode else: continue self.description.append([ f.name(), # name t, # type_code f.length(), # display_size f.length(), # internal_size f.precision(), # precision None, # scale f.requiredStatus() != QSqlField.Required # null_ok ]) def executemany(self, operation, seq_of_parameters): if len(seq_of_parameters) == 0: return if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for r in seq_of_parameters: for i in range(len(r)): self.qry.bindValue(i, r[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) def scroll(self, row): return self.qry.seek(row) def fetchone(self): if not self.qry.next(): return None row = [] for i in range(len(self.description)): value = self.qry.value(i) if (isinstance(value, QDate) or isinstance(value, QTime) or isinstance(value, QDateTime)): value = value.toString() elif isinstance(value, QByteArray): value = u"GEOMETRY" # value = value.toHex() row.append(value) return row def fetchmany(self, size=10): rows = [] while len(rows) < size: row = self.fetchone() if row is None: break rows.append(row) return rows def fetchall(self): rows = [] while True: row = self.fetchone() if row is None: break rows.append(row) return rows def setinputsize(self, sizes): raise ExecError("nyi") def setoutputsize(self, size, column=None): raise ExecError("nyi")
def insertUser(self): if (self.getUsername() != None): self.__openDB() sql = "INSERT INTO postnas_search_access_control (username,name,access) VALUES (:username,:name,:access)" query = QSqlQuery(self.db) query.prepare(sql) query.bindValue(":username", self.getUsername().lower()) query.bindValue(":name", self.name) query.bindValue(":access", self.access) query.exec_() if (query.lastError().number() == -1): return True else: return False else: return False
def getPage(self, fs): (db, conninfo) = self.plugin.opendb() if db is None: return None qry = QSqlQuery(db) if qry.exec_("SELECT 1 FROM information_schema.columns WHERE table_schema={} AND table_name='eignerart' AND column_name='anteil'".format(quote(self.plugin.settings.schema))) and qry.next(): exists_ea_anteil = qry.value(0) == 1 else: exists_ea_anteil = False html = "" for i in range(0, len(fs)): flsnr = fs[i]['flsnr'] best = self.fetchall(db, ( "SELECT " + "ea.bvnr" + ",'' as pz" + ",(SELECT eignerart FROM eign_shl WHERE ea.b=b) as eignerart" + ",%s as anteil" + ",ea.ff_stand AS zhist" + ",b.bestdnr" + ",b.gbbz" + ",b.gbblnr" + ",b.bestfl" + ",b.ff_stand AS bhist" + " FROM eignerart ea" + " JOIN bestand b ON ea.bestdnr = b.bestdnr" + " WHERE ea.flsnr = '%s'" + " ORDER BY zhist,bhist,b") % ("ea.anteil" if exists_ea_anteil else "''", flsnr) ) res = self.fetchall(db, "SELECT f.*,g.gemarkung FROM flurst f LEFT OUTER JOIN gema_shl g ON (f.gemashl=g.gemashl) WHERE f.flsnr='%s' AND f.ff_stand=0" % flsnr) if len(res) == 1: res = res[0] else: QMessageBox.information(None, "Fehler", u"Flurstück %s nicht gefunden.\n[%s]" % (flsnr, repr(fs))) return None res['datum'] = QDate.currentDate().toString("d. MMMM yyyy") res['hist'] = 0 if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['strassen', 'str_shl']])) and qry.next() and qry.value(0): res['str'] = self.fetchall(db, "SELECT sstr.strname,str.hausnr FROM str_shl sstr JOIN strassen str ON str.strshl=sstr.strshl WHERE str.flsnr='%s' AND str.ff_stand=0" % flsnr) if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['nutz_21', 'nutz_shl']])) and qry.next() and qry.value(0): res['nutz'] = self.fetchall(db, "SELECT n21.*, nu.nutzshl, nu.nutzung FROM nutz_21 n21, nutz_shl nu WHERE n21.flsnr='%s' AND n21.nutzsl=nu.nutzshl AND n21.ff_stand=0" % flsnr) if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['klas_3x', 'kls_shl']])) and qry.next() and qry.value(0): res['klas'] = self.fetchall(db, "SELECT sum(fl::int) AS fl, min(kls.klf_text) AS klf_text FROM klas_3x kl, kls_shl kls WHERE kl.flsnr='%s' AND kl.klf=kls.klf AND kl.ff_stand=0 GROUP BY kls.klf" % flsnr) if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['ausfst', 'afst_shl']])) and qry.next() and qry.value(0): res['afst'] = self.fetchall(db, "SELECT au.*, af.afst_txt FROM ausfst au,afst_shl af WHERE au.flsnr='%s' AND au.ausf_st=af.ausf_st AND au.ff_stand=0" % flsnr) if qry.exec_(u"SELECT " + u" AND ".join(["has_table_privilege('{}', 'SELECT')".format(x) for x in ['bestand', 'eignerart', 'eign_shl']])) and qry.next() and qry.value(0): res['best'] = self.fetchall(db, "SELECT ea.bvnr,'' as pz,(SELECT eignerart FROM eign_shl WHERE ea.b = b) as eignerart,%s as anteil,ea.ff_stand AS zhist,b.bestdnr,b.gbbz,b.gbblnr,b.bestfl,b.ff_stand AS bhist FROM eignerart ea JOIN bestand b ON ea.bestdnr = b.bestdnr WHERE ea.flsnr='%s' ORDER BY zhist,bhist,b" % ( "ea.anteil" if exists_ea_anteil else "''", flsnr )) if qry.exec_("SELECT has_table_privilege('eigner', 'SELECT')") and qry.next() and qry.value(0): for b in res['best']: b['bse'] = self.fetchall(db, "SELECT * FROM eigner WHERE bestdnr='%s' AND ff_stand=0" % b['bestdnr']) # for k,v in res.iteritems(): # qDebug( u"%s:%s\n" % ( k, unicode(v) ) ) html = u""" <HTML xmlns="http://www.w3.org/1999/xhtml"> <HEAD> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </HEAD> <BODY> <style> .fls_tab{width:100%%;empty-cells:show} .fls_headline{font-weight:bold;font-size:4em;} .fls_headline_col{background-color:#EEEEEE;width:100%%;height:30px;text-align:left;} .fls_time {background-color:#EEEEEE;font-weight:bold;font-size:4em;text-align:right;width:100%%} .fls_col_names{font-weight:bold;} .fls_col_values{vertical-align:top;} .fls_bst{width:100%%;empty-cells:show} .fls_hr{border:dotted 1px;color:#080808;} .fls_footnote{text-align:center;} </style> <TABLE class="fls_tab" border="0" width="100%%" cellspacing="0"> <TR class="fls_headline"> <TD colspan="3" class="fls_headline_col">Flurstücksnachweis</TD><TD class="fls_time" colspan="4" align="right">%(datum)s</TD></TR> </TR> <TR><TD colspan="7"> </TD></TR> <TR> <TD colspan="7"><h3>Flurstück %(gemashl)s-%(flr)s-%(flsnrk)s<hr style="width:100%%"></h3></TD> </TR> <TR class="fls_col_names"> <TD width="15%%">Gemarkung</TD> <TD width="6%%">Flur</TD> <TD width="15%%">Flurstück</TD> <TD width="20%%">Flurkarte</TD> <TD width="17%%">Entstehung</TD> <TD width="17%%">Fortführung</TD> <TD width="5%%">Fläche</TD> </TR> <TR class="fls_col_values"> <TD>%(gemashl)s<br>%(gemarkung)s</TD> <TD>%(flr)s</TD> <TD>%(flsnrk)s</TD> <TD>%(flurknr)s</TD> <TD>%(entst)s</TD> <TD>%(fortf)s</TD> <TD>%(flsfl)s m²</TD> </TR> </TABLE> """ % res if res['blbnr']: html += u""" <TABLE class="fls_tab" border="0" width="100%%"> <TR class="fls_col_names"> <TD width="21%%"></TD> <TD width="79%%">Baulastenblattnr.</TD> </TR> <TR class="fls_col_values"> <TD></TD> <TD>%(blbnr)s</TD> </TR> </TABLE> """ % res if res['lagebez'] or res['anl_verm']: html += u""" <TABLE class="fls_tab" border="0" width="100%%"> <TR class="fls_col_names"> <TD width="21%%"></TD> <TD width="52%%">Lage</TD> <TD width="27%%">Anliegervermerk</TD> </TR> <TR class="fls_col_values"> <TD></TD> <TD>%(lagebez)s</TD> <TD>%(anl_verm)s</TD> </TR> </TABLE> """ % res if 'str' in res: if res['str']: html += u""" <TABLE border="0" class="fls_tab" width="100%"> <TR class="fls_col_names"> <TD width="21%"></TD> <TD width="52%">Strasse</TD> <TD width="27%">Hausnummer</TD> </TR> """ for strres in res['str']: html += u""" <TR class="fls_col_values"> <TD></TD><TD>%(strname)s</TD><TD>%(hausnr)s</TD></TR> </TR> """ % strres html += u""" </TABLE> """ if 'nutz' in res: html += u""" <TABLE border="0" class="fls_tab" width="100%"> <TR class="fls_col_names"><TD width="21%"></TD><TD width="69%">Nutzung</TD><TD width="10%">Fläche</TD></TR> """ if res['nutz']: for nutz in res['nutz']: html += u""" <TR class="fls_col_values"><TD></TD><TD>21%(nutzshl)s - %(nutzung)s</TD><TD>%(fl)s m²</TD></TR> """ % nutz else: html += u""" <TR class="fls_col_values"><TD></TD><TD colspan=2>Keine</TD></TR> """ html += u""" </TABLE> """ if 'klas' in res: html += u""" <TABLE border="0" class="fls_tab" width="100%"> <TR class="fls_col_names"><TD width="21%"></TD><TD width="69%">Klassifizierung(en)</TD><TD width="10%">Fläche</TD></TR> """ if res['klas']: for klas in res['klas']: html += u""" <TR class="fls_col_values"><TD></TD><TD>%(klf_text)s</TD><TD>%(fl)s m²</TD></TR> """ % klas else: html += u""" <TR class="fls_col_values"><TD></TD><TD colspan=2>Keine</TD></TR> """ html += u""" </TABLE> """ if 'afst' in res: html += u""" <TABLE border="0" class="fls_tab" width="100%"> <TR class="fls_col_names"><TD width="21%"></TD><TD width="79%">Ausführende Stelle(n)</TD></TR> """ if res['afst']: for afst in res['afst']: html += u""" <TR class="fls_col_values"><TD></TD><TD>%(afst_txt)s</TD></TR> """ % afst else: html += u""" <TR class="fls_col_values"><TD></TD><TD colspan=2>Keine</TD></TR> """ html += u""" </TABLE> """ if 'best' in res: if res['best']: html += u""" <br> <TABLE border="0" class="fls_bst" width="100%"> <TR><TD colspan="6"><h3>Bestände<hr style="width:100%"></h3></TD></TR> """ for best in res['best']: html += u""" <TR class="fls_col_names"> <TD>Bestandsnummer</TD> <TD>Grundbuchbezirk</TD> <TD colspan="2">Grundbuchblattnr.</TD> <TD>Anteil</TD> </TR> <TR class="fls_col_values"> <TD>%(bestdnr)s</TD> <TD>%(gbbz)s</TD> <TD colspan="2">%(gbblnr)s</TD> <TD>%(anteil)s</TD> </TR> <TR class="fls_col_names"> <TD></TD> <TD>Buchungskennz.</TD> <TD>BVNR</TD> <TD>PZ</TD> """ % best if res['hist']: html += u""" <TD>Hist. Bestand</TD><TD>Hist. Zuordnung</TD> """ else: html += u""" <TD></TD><TD></TD> """ html += u""" </TR> <TR class="fls_col_values"> <TD></TD> <TD>%(eignerart)s</TD> <TD>%(bvnr)s</TD> <TD>%(pz)s</TD> """ % best html += "<TD>%s</TD>" % ("ja" if res['hist'] and best['bhist'] else "") html += "<TD>%s</TD>" % ("ja" if res['hist'] and best['zhist'] else "") html += u""" </TR> """ if 'bse' in best: if best['bse']: html += u""" <TR class="fls_col_names"><TD>Anteil</TD><TD colspan="5">Namensinformation</TD></TR> """ for bse in best['bse']: html += u""" <TR class="fls_col_values"> <TD>%(antverh)s</TD> <TD colspan="5">%(name1)s %(name2)s<br>%(name3)s<br>%(name4)s</TD> </TR> """ % bse else: html += u""" <p>Kein Eigentümer gefunden.</p> """ html += u""" <TR><TD colspan="6"><hr class="fls_hr"></TD></TR> """ html += u""" """ footnote = self.plugin.settings.footnote if footnote: html += u""" <TR><TD colspan="7" class="fls_footnote">%s</TD></TR> """ % footnote html += u""" </TABLE> </BODY> </HTML> """ return html
def evaluate(self): if not self.plugin.initLayers(): return False if self.tabWidget.currentWidget() == self.tabLabels: text = self.leSuchbegriff.text() if text != "": if self.cbTeiltreffer.isChecked(): # Teiltreffer text = u"lower(text) LIKE %s" % quote("%%%s%%" % text.lower()) else: # Exakter Treffer text = u"text=%s" % quote(text) qry = QSqlQuery(self.db) sql = u"SELECT count(*),st_extent(coalesce(point,line)) FROM po_labels WHERE {0}".format(text) if qry.exec_(sql) and qry.next() and qry.value(0) > 0: self.lblResult.setText("{} Objekte gefunden".format(qry.value(0))) self.plugin.zoomToExtent(qry.value(1), self.plugin.pointMarkerLayer.crs()) else: self.lblResult.setText("Keine Objekte gefunden") return False else: text = "false" self.plugin.pointMarkerLayer.setSubsetString(text) self.plugin.lineMarkerLayer.setSubsetString(text) self.updateButtons() elif self.tabWidget.currentWidget() == self.tabGFF: g = self.cbxGemarkung.itemData(self.cbxGemarkung.currentIndex()) f = self.cbxFlur.itemData(self.cbxFlur.currentIndex()) z = self.cbxFSZ.itemData(self.cbxFSZ.currentIndex()) n = self.cbxFSN.itemData(self.cbxFSN.currentIndex()) flsnr = "" flsnr += ("%" if g is None or g == "" else g) + "-" flsnr += ("%" if f is None or f == "" else f) + "-" flsnr += ("%" if z is None or z == "" else z) + "/" flsnr += ("%" if n is None or n == "" else n) # qDebug(u"flsnr:{}".format(flsnr)) fs = self.plugin.highlight(where=u"EXISTS (SELECT * FROM fs WHERE gml_id=fs_obj AND alb_key LIKE %s)" % quote(flsnr), zoomTo=True) self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden") self.updateButtons(fs) elif self.tabWidget.currentWidget() == self.tabFLSNR: hits = 0 m = re.search("(\\d+)(-\\d+)?-(\\d+)(/\\d+)?", self.leFLSNR.text()) if m: g, f, z, n = int(m.group(1)), m.group(2), int(m.group(3)), m.group(4) f = int(f[1:]) if f else 0 n = int(n[1:]) if n else 0 flsnr = "%06d" % g flsnr += "%03d" % f if f > 0 else "___" flsnr += "%05d" % z flsnr += "%04d" % n if n > 0 else "____" flsnr += "%" fs = self.plugin.highlight(where=u"flurstueckskennzeichen LIKE %s" % quote(flsnr), zoomTo=True) hits = len(fs) self.lblResult.setText(u"{} Flurstücke gefunden".format(hits) if hits > 0 else u"Keine Flurstücke gefunden") self.updateButtons(fs) elif self.tabWidget.currentWidget() == self.tabSTRHNR: text = self.leStr.text() if text != "": m = re.search("^(.*)\\s+(\\d+[a-zA-Z]?)$", text) if m: strasse, ha = m.group(1), m.group(2) fs = self.plugin.highlight(where=u"EXISTS (SELECT * FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k ON h.land=k.land AND h.regierungsbezirk=k.regierungsbezirk AND h.kreis=k.kreis AND h.gemeinde=k.gemeinde AND h.lage=k.lage WHERE ARRAY[h.gml_id] <@ ax_flurstueck.weistauf AND lower(k.bezeichnung) LIKE {0} AND h.hausnummer={1})".format(quote(strasse.lower() + '%'), quote(ha.upper())), zoomTo=True) if len(fs) > 0: self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs))) else: self.lblResult.setText(u"Keine Flurstücke gefunden") self.updateButtons(fs) if self.cbxHNR.isEnabled(): hnr = self.cbxHNR.currentText() sql = u"EXISTS (SELECT * FROM ax_lagebezeichnungmithausnummer h JOIN ax_lagebezeichnungkatalogeintrag k USING (land,regierungsbezirk,kreis,gemeinde,lage) WHERE ARRAY[h.gml_id] <@ ax_flurstueck.weistauf AND k.schluesselgesamt={0}{1})" if hnr == "Alle": sql += u" OR EXISTS (SELECT * FROM ax_lagebezeichnungohnehausnummer h JOIN ax_lagebezeichnungkatalogeintrag k USING (land,regierungsbezirk,kreis,gemeinde,lage) WHERE ARRAY[h.gml_id] <@ ax_flurstueck.zeigtauf AND k.schluesselgesamt={0})" fs = self.plugin.highlight( where=sql.format( quote(self.cbxStrassen.itemData(self.cbxStrassen.currentIndex())), ' AND h.hausnummer={0}'.format(quote(hnr)) if hnr != "Alle" else "" ), zoomTo=True ) self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden") self.updateButtons(fs) elif self.tabWidget.currentWidget() == self.tabEigentuemer: where = [] for e in self.leEigentuemer.text().split(): where.append("lower(name1) LIKE " + quote('%' + e.lower() + '%')) if where: fs = self.plugin.retrieve(u"gml_id IN (SELECT fs_obj FROM fs JOIN eignerart a ON fs.alb_key=a.flsnr JOIN eigner e ON a.bestdnr=e.bestdnr AND %s)" % " AND ".join(where)) if len(fs) == 0: qDebug(u"Kein Flurstück gefunden") self.updateButtons() return False if not self.plugin.logQuery("eigentuemerSuche", self.leEigentuemer.text(), [i['flsnr'] for i in fs]): self.lblResult.setText(u"Flurstücke werden ohne Protokollierung nicht angezeigt.") self.updateButtons() return False fs = self.plugin.highlight(fs=fs, zoomTo=True) self.lblResult.setText(u"{} Flurstücke gefunden".format(len(fs)) if len(fs) > 0 else u"Keine Flurstücke gefunden") self.updateButtons(fs) return True
def gfzn(self): g = self.cbxGemarkung.itemData(self.cbxGemarkung.currentIndex()) if self.cbxGemarkung.currentIndex() >= 0 else None f = self.cbxFlur.itemData(self.cbxFlur.currentIndex()) if self.cbxFlur.currentIndex() >= 0 else None z = self.cbxFSZ.itemData(self.cbxFSZ.currentIndex()) if self.cbxFSZ.currentIndex() >= 0 else None n = self.cbxFSN.itemData(self.cbxFSN.currentIndex()) if self.cbxFSN.currentIndex() >= 0 else None where = [] if g is not None and g != "": where.append("gemashl='%s'" % g) if f is not None and f != "": where.append("flr='%s'" % f) if z is not None and n is not None and z != "" and n != "": where.append("flsnrk='%s/%s'" % (z, n)) elif z is not None and z != "": where.append("flsnrk LIKE '%s/%%'" % z) elif n is not None and n != "": where.append("flsnrk LIKE '%%/%s'" % n) where = u" WHERE {}".format(u" AND ".join(where)) if where else "" qry = QSqlQuery(self.db) # qDebug(u"WHERE:{}".format(where)) for cbx, sql, val in [ [ self.cbxGemarkung, "SELECT {0} FROM gema_shl a LEFT OUTER JOIN gem_shl b USING (gemshl){1} GROUP BY {0} ORDER BY {0}".format( "a.gemashl,a.gemarkung||' ('||a.gemashl||coalesce(', '||b.gemname,'')||')'", u" JOIN flurst c USING (gemashl){0}".format(where) if where != "" else "" ), g, ], [ self.cbxFlur, "SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("flr", where), f, ], [ self.cbxFSZ, "SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',1)", where), z, ], [ self.cbxFSN, "SELECT {0} FROM flurst{1} GROUP BY {0} ORDER BY {0}".format("split_part(flsnrk,'/',2)", where), n, ], ]: cbx.blockSignals(True) cbx.clear() cbx.addItem("Alle", "") # qDebug(u"SQL:{} [{}]".format(sql, val)) if qry.exec_(sql): d = 0 if qry.record().count() == 1 else 1 while qry.next(): cbx.addItem(qry.value(d), qry.value(0)) cbx.setCurrentIndex(cbx.findData(val)) cbx.blockSignals(False) if where == "": return hits = 0 if qry.exec_(u"SELECT count(*) FROM flurst{}".format(where)) and qry.next(): hits = qry.value(0) if hits > 0 and hits < int(self.leHighlightThreshold.text()): self.evaluate() else: self.lblResult.setText(u"{} Flurstücke gefunden".format(hits) if hits > 0 else u"Keine Flurstücke gefunden")
def get_characteristic_speeds(self, count_id, hour, direction, start_timestamp, end_timestamp, section_id): self.init_db_connection() query = QSqlQuery(self.db) result = [] query_str = ("select count(*) from comptages.count_detail as det join " "comptages.lane as lan on det.id_lane = lan.id " "where det.id_count = {} and lan.direction = {} " "and lan.id_section = '{}' " "and date_part('hour', det.timestamp) = {} " "and det.timestamp>='{}' and det.timestamp<'{}';".format( count_id, direction, section_id, hour, start_timestamp, end_timestamp)) query.exec_(query_str) if query.next(): count = query.value(0) else: return [0, 0, 0, 0] percent = [] percent.append(int(count * 0.15)) percent.append(int(count * 0.5)) percent.append(int(count * 0.85)) for i in percent: if i < 0: i = 0 query_str = ( "select det.speed from comptages.count_detail as det join " "comptages.lane as lan on det.id_lane = lan.id " "where det.id_count = {} and lan.direction = {} " "and lan.id_section = '{}' " "and date_part('hour', det.timestamp) = {} " "and det.timestamp>='{}' and det.timestamp<'{}' " "order by speed " "offset ({}-1) rows " "fetch next 1 rows only;".format(count_id, direction, section_id, hour, start_timestamp, end_timestamp, i)) query.exec_(query_str) query.next() if query.value(0) and query.value(0) >= 1: result.append(query.value(0)) else: result.append('NA') query_str = ("select coalesce(avg(det.speed), 0) from " "comptages.count_detail as det join " "comptages.lane as lan on det.id_lane = lan.id " "where det.id_count = {} and lan.direction = {} " "and lan.id_section = '{}' " "and date_part('hour', det.timestamp) = {} " "and det.timestamp>='{}' and det.timestamp<'{}';".format( count_id, direction, section_id, hour, start_timestamp, end_timestamp)) query.exec_(query_str) query.next() if query.value(0) and query.value(0) >= 1: result.append(query.value(0)) else: result.append('NA') return result
def get_projects_from_sqlite(): projects = [] error_message = "Error while reading from projects database." try: db = get_projects_db() sql = "SELECT * FROM projects;" query = db.exec_(sql) if not query.isActive(): QgsMessageLog.logMessage(tr(error_message), "VeriSO", Qgis.Critical) QgsMessageLog.logMessage(str(QSqlQuery.lastError(query).text()), "VeriSO", Qgis.Critical) return record = query.record() while query.next(): project = { "id": str(query.value(record.indexOf("id"))), "displayname": str( query.value(record.indexOf("displayname"))), "dbhost": str(query.value(record.indexOf("dbhost"))), "dbname": str(query.value(record.indexOf("dbname"))), "dbport": str(query.value(record.indexOf("dbport"))), "dbschema": str( query.value(record.indexOf("dbschema"))), "dbuser": str(query.value(record.indexOf("dbuser"))), "dbpwd": str(query.value(record.indexOf("dbpwd"))), "dbadmin": str( query.value(record.indexOf("dbadmin"))), "dbadminpwd": str( query.value(record.indexOf("dbadminpwd"))), "provider": str( query.value(record.indexOf("provider"))), "epsg": str(query.value(record.indexOf("epsg"))), "max_scale": int(query.value(record.indexOf("max_scale"))), "ilimodelname": str( query.value(record.indexOf("ilimodelname"))), "appmodule": str( query.value(record.indexOf("appmodule"))), "appmodulename": str( query.value(record.indexOf("appmodulename"))), "projectrootdir": str( query.value(record.indexOf("projectrootdir"))), "projectdir": str( query.value(record.indexOf("projectdir"))), "datadate": str( query.value(record.indexOf("datadate"))), "importdate": str( query.value(record.indexOf("importdate")))} projects.append(project) db.close() del db except Exception as e: QgsMessageLog.logMessage( tr("Error while reading from projects database."), "VeriSO", Qgis.Critical) QgsMessageLog.logMessage(str(e), "VeriSO", Qgis.Critical) return return projects
def updateUser(self, username_old): if (self.getUsername() != None): self.__openDB() sql = "UPDATE postnas_search_access_control SET username = :username, name = :name, access = :access WHERE username = :username_old" query = QSqlQuery(self.db) query.prepare(sql) query.bindValue(":username", self.getUsername().lower()) query.bindValue(":username_old", username_old) query.bindValue(":name", self.name) query.bindValue(":access", self.access) query.exec_() if (query.lastError().number() == -1): return True else: QgsMessageLog.logMessage( "Datenbankfehler beim Update: " + query.lastError().text(), 'PostNAS-Suche', Qgis.Critical) return False else: return False
def __init__(self, conn): self.qry = QSqlQuery(conn) self.description = None self.rowcount = -1 self.arraysize = 1
def loadUserAccessTable(self): sql = "SELECT lower(username) as username,name,bezeichnung FROM postnas_search_access_control LEFT JOIN postnas_search_accessmode ON postnas_search_access_control.access = postnas_search_accessmode.id" self.__openDB() queryLoadAccessTable = QSqlQuery(self.db) queryLoadAccessTable.prepare(sql) queryLoadAccessTable.exec_() results = [] if (queryLoadAccessTable.size() > 0): while (queryLoadAccessTable.next()): list = { 'username': queryLoadAccessTable.value( queryLoadAccessTable.record().indexOf("username")), 'name': queryLoadAccessTable.value( queryLoadAccessTable.record().indexOf("name")), 'access': queryLoadAccessTable.value( queryLoadAccessTable.record().indexOf("bezeichnung")) } results.append(list) return results
def update_projects_database_pg(self): """Updates the postgres projects database. Returns: False: When there an error occured. Otherswise True. """ error_message = ("Something went wrong while updating projects " "database. You need to delete the database schema " "manually.") try: # Create a new projects database (schema and table on pg) if # there is none table_exists = False schema_exists = False db = get_default_db() sql = "SELECT 1 FROM pg_namespace " \ "WHERE nspname = 'veriso_conf'" query = db.exec_(sql) if query.size() > 0: schema_exists = True sql = """SELECT 1 FROM pg_tables WHERE schemaname = 'veriso_conf' AND tablename = 'project'""" query = db.exec_(sql) if query.size() > 0: table_exists = True if not schema_exists: sql = "CREATE SCHEMA veriso_conf" query = db.exec_(sql) if not table_exists: sql = "CREATE TABLE veriso_conf.project (" \ "ogc_fid serial primary key, " \ "id character varying, " \ "displayname character varying," \ "provider character varying, " \ "epsg integer, " \ "ilimodelname character varying, " \ "appmodule character varying, " \ "appmodulename character varying, " \ "datadate timestamp, " \ "notes character varying, " \ "itf character varying, " \ "max_scale integer default 0 " \ ")" query = db.exec_(sql) values = (self.db_schema, self.db_schema, self.epsg, self.ili, self.app_module, self.app_module_name, self.data_date, self.notes, self.itf, self.max_scale) values = "VALUES ( "\ "'%s', '%s', 'postgres', '%s', '%s', '%s', '%s', '%s', " \ "'%s', '%s', '%s')" % values sql = "INSERT INTO veriso_conf.project (id, displayname, " \ "provider, epsg, ilimodelname, appmodule, appmodulename, " \ "datadate, notes, itf, " \ "max_scale)" + values query = db.exec_(sql) if not query.isActive(): message = "Error while updating projects database." raise VerisoError( message, long_message=QSqlQuery.lastError(query).text()) db.close() self.projectsDatabaseHasChanged.emit() return True except Exception as e: raise VerisoError(error_message, e)
def getAccessModes(self): sql = "SELECT id,bezeichnung FROM postnas_search_accessmode" self.__openDB() queryLoadAccessModes = QSqlQuery(self.db) queryLoadAccessModes.prepare(sql) queryLoadAccessModes.exec_() results = [] if (queryLoadAccessModes.size() > 0): while (queryLoadAccessModes.next()): list = { 'id': queryLoadAccessModes.value( queryLoadAccessModes.record().indexOf("id")), 'bezeichnung': queryLoadAccessModes.value( queryLoadAccessModes.record().indexOf("bezeichnung")) } results.append(list) return results
def setUsername(self, username): self.username = username if (self.checkUserExists()): sql = "SELECT name,access FROM postnas_search_access_control WHERE lower(username) = :username" self.__openDB() queryLoadUserData = QSqlQuery(self.db) queryLoadUserData.prepare(sql) queryLoadUserData.bindValue(":username", self.getUsername()) queryLoadUserData.exec_() if (queryLoadUserData.size() == 1): while (queryLoadUserData.next()): self.setName( queryLoadUserData.value( queryLoadUserData.record().indexOf("name"))) self.setAccess( queryLoadUserData.value( queryLoadUserData.record().indexOf("access")))
def setUp(self): self.db.open() query = QSqlQuery(self.db) query.exec_("DELETE FROM comptages.count;") query.exec_("DELETE FROM comptages.count_detail;") query.exec_("DELETE FROM comptages.count_aggregate;") query.exec_("DELETE FROM comptages.count_aggregate_value_cls;") query.exec_("DELETE FROM comptages.count_aggregate_value_cnt;") query.exec_("DELETE FROM comptages.count_aggregate_value_drn;") query.exec_("DELETE FROM comptages.count_aggregate_value_len;") query.exec_("DELETE FROM comptages.count_aggregate_value_spd;") query.exec_("DELETE FROM comptages.count_aggregate_value_sds;") self.db.close()
def __insertLogEntry(self,requestType,search,result): self.__openDB() sql = "INSERT INTO postnas_search_logging (datum,username,requestType,search,result) VALUES (:datum,:username,:requestType,:search,:result)" query = QSqlQuery(self.db) query.prepare(sql) query.bindValue(":datum",datetime.datetime.now().isoformat()) query.bindValue(":username",self.username) query.bindValue(":requestType",requestType) query.bindValue(":search",search) query.bindValue(":result",str(result).replace("u'","'").replace("\'","\"").replace("[","{").replace("]","}")) query.exec_() if(query.lastError().number() == -1): return True else: return False
def test_data_detail(self): self.db.open() query = QSqlQuery(self.db) query.exec_("SELECT id FROM comptages.installation \ WHERE name = '64080011';") query.next() installation_id = query.value(0) query.exec_("SELECT id FROM comptages.model \ WHERE name = 'M660_LT';") query.next() model_id = query.value(0) query.exec_("select id from comptages.sensor_type \ where name = 'Tube'") query.next() sensor_type_id = query.value(0) query.exec_("select id from comptages.class \ where name = 'SWISS10'") query.next() class_id = query.value(0) query_str = ( "INSERT INTO comptages.count(id, " "start_process_date, end_process_date, start_service_date, " "end_service_date, id_sensor_type, id_model, id_class, " "id_installation) " "VALUES (1, '2018-12-18', '2018-12-20', '2018-12-18', " "'2018-12-20', {}, {}, {}, {});".format( sensor_type_id, model_id, class_id, installation_id)) query.exec_(query_str) task = self.comptages.import_file( os.path.join( self.test_data_path, 'data_loader_simple_detail.V01'), 1) task.waitForFinished() # Let the time to the db to finish the writing time.sleep(1) data_loader = DataLoader( 1, '64080011', self.layers.IMPORT_STATUS_QUARANTINE) count_data = data_loader.load() self.assertEqual( [1, 0, 3, 0, 1, 3, 0, 1, 0, 0, 0, 0, 2], count_data.day_data[0].hour_data[15].direction_data[0].speed_data) self.assertEqual( [1, 1, 2, 1, 1, 1, 1, 1, 1, 1], count_data.day_data[0].hour_data[15].direction_data[0].category_data) self.assertEqual(20, count_data.day_data[0].total()) self.assertEqual(16, count_data.day_data[0].light_vehicles()) self.assertEqual(4, count_data.day_data[0].heavy_vehicles()) self.assertEqual(20.0, count_data.day_data[0].percent_heavy_vehicles())
def loadDatabaseStructure(self, edgvPath): """ Loads the database structure edgvPath: path to the databse sql """ commands = [] hasTemplate = self.abstractDb.checkTemplate(self.version) if hasTemplate: templateDb = self.connectToTemplate(setInnerDb = False) mustUpdateTemplate = templateDb.checkTemplateImplementationVersion() if mustUpdateTemplate: templateName = templateDb.db.databaseName() templateDb.__del__() self.abstractDb.dropDatabase(templateName, dropTemplate = True) hasTemplate = False if not hasTemplate: file = codecs.open(edgvPath, encoding='utf-8', mode="r") sql = file.read() sql = sql.replace('[epsg]', '4674') file.close() commands = [i for i in sql.split('#') if i != ''] # Progress bar steps calculated self.signals.rangeCalculated.emit(len(commands)+4, self.getId()) if not hasTemplate: try: self.abstractDb.createTemplateDatabase(self.version) self.signals.stepProcessed.emit(self.getId()) self.connectToTemplate() self.signals.stepProcessed.emit(self.getId()) except Exception as e: return (0, self.messenger.getProblemFeedbackMessage()+'\n'+':'.join(e.args)) self.db.open() self.db.transaction() query = QSqlQuery(self.db) for command in commands: if not self.stopped[0]: if not query.exec_(command): QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSGTools Plugin", Qgis.Critical) self.db.rollback() self.db.close() self.dropDatabase(self.db) return (0, self.messenger.getProblemFeedbackMessage()) # Updating progress self.signals.stepProcessed.emit(self.getId()) else: self.db.rollback() self.db.close() self.dropDatabase(self.db) QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSGTools Plugin", Qgis.Info) return (-1, self.messenger.getUserCanceledFeedbackMessage()) self.db.commit() if self.version == '2.1.3': sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'cb\',\'complexos\',\'dominios\';' % self.db.databaseName() elif self.version == '2.1.3 Pro': sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'edgv\',\'dominios\';' % self.db.databaseName() elif self.version == 'FTer_2a_Ed': sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'pe\',\'ge\',\'complexos\',\'dominios\';' % self.db.databaseName() elif self.version == '3.0': sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'edgv\',\'complexos\',\'dominios\';' % self.db.databaseName() if sql: if not query.exec_(sql): QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSGTools Plugin", Qgis.Critical) return (0, self.messenger.getProblemFeedbackMessage()) #this commit was missing, so alter database statement was not commited. self.db.commit() self.db.close() self.abstractDb.setDbAsTemplate(self.version) #creates from template if not self.stopped[0]: templateName = self.abstractDb.getTemplateName(self.version) self.abstractDb.createDbFromTemplate(self.dbName, templateName, parentWidget = self.parent) self.signals.stepProcessed.emit(self.getId()) #5. alter spatial structure createdDb = self.dbFactory.createDbFactory(DsgEnums.DriverPostGIS) createdDb.connectDatabaseWithParameters(self.abstractDb.db.hostName(), self.abstractDb.db.port(), self.dbName, self.abstractDb.db.userName(), self.abstractDb.db.password()) errorTuple = createdDb.updateDbSRID(self.epsg, parentWidget = self.parent, threading = True) # if an error occur during the thread we should pass the message to the main thread if errorTuple: QgsMessageLog.logMessage(self.messenger.getProblemMessage(errorTuple[0], errorTuple[1]), "DSGTools Plugin", Qgis.Critical) return (0, self.messenger.getProblemFeedbackMessage()) self.signals.stepProcessed.emit(self.getId()) else: QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSGTools Plugin", Qgis.Info) return (-1, self.messenger.getUserCanceledFeedbackMessage()) QgsMessageLog.logMessage(self.messenger.getSuccessFeedbackMessage(), "DSGTools Plugin", Qgis.Info) return (1, self.messenger.getSuccessFeedbackMessage())
def loadModels(self, error=True): self.settings.servicE = self.leSERVICE.text() self.settings.host = self.leHOST.text() self.settings.port = self.lePORT.text() self.settings.dbname = self.leDBNAME.text() self.settings.schema = self.leSCHEMA.text() self.settings.uid = self.leUID.text() self.settings.pwd = self.lePWD.text() if hasattr(qgis.gui, 'QgsAuthConfigSelect'): self.settings.authcfg = self.authCfgSelect.configId() self.twModellarten.clearContents() self.cbxSignaturkatalog.clear() (db, conninfo) = self.plugin.opendb() if not db: if error: QMessageBox.critical(None, "ALKIS", u"Datenbankverbindung schlug fehl.") self.twModellarten.clearContents() self.twModellarten.setDisabled(True) self.twModellarten.setRowCount(0) self.settings.load() return modelle = self.settings.modellarten if modelle is None: modelle = ['DLKM', 'DKKM1000'] qry = QSqlQuery(db) if qry.exec_("SELECT 1 FROM information_schema.tables WHERE table_schema={} AND table_name='po_modelle'".format(quote(self.plugin.settings.schema))) and qry.next(): sql = "SELECT modell,n FROM po_modelle WHERE modell IS NOT NULL ORDER BY n DESC" else: sql = """ SELECT modell,count(*) FROM ( SELECT unnest(modell) AS modell FROM po_points UNION ALL SELECT unnest(modell) AS modell FROM po_lines UNION ALL SELECT unnest(modell) AS modell FROM po_polygons UNION ALL SELECT unnest(modell) AS modell from po_labels ) AS foo WHERE modell IS NOT NULL GROUP BY modell ORDER BY count(*) DESC """ if qry.exec_(sql): res = {} while qry.next(): res[qry.value(0)] = qry.value(1) self.twModellarten.setRowCount(len(res)) i = 0 for k, n in sorted(iter(list(res.items())), key=operator.itemgetter(1), reverse=True): item = QTableWidgetItem(k) item.setCheckState(Qt.Checked if (item.text() in modelle) else Qt.Unchecked) self.twModellarten.setItem(i, 0, item) item = QTableWidgetItem(str(n)) self.twModellarten.setItem(i, 1, item) i += 1 self.twModellarten.resizeColumnsToContents() self.twModellarten.setEnabled(True) else: self.twModellarten.clearContents() self.twModellarten.setDisabled(True) self.twModellarten.setRowCount(0) if qry.exec_("SELECT id,name FROM alkis_signaturkataloge"): while qry.next(): self.cbxSignaturkatalog.addItem(qry.value(1), int(qry.value(0))) self.cbxSignaturkatalog.setEnabled(True) else: self.cbxSignaturkatalog.addItem(u"Farbe", -1) self.cbxSignaturkatalog.setCurrentIndex(max([0, self.cbxSignaturkatalog.findData(self.settings.signaturkatalog)])) self.settings.load()