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 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()
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 __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
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()
