def _get_fsn_from_gml_id(self, feature_list):
"""
Ruft die Eigentümerinformaionen anhand der gml_id der Flurstücke aus der ALKIS-DB ab.
:param features: Liste der Features, deren Eigentümerinformationen abgerufen werden sollen
:type list
:return: Eigentümerinformationen
:rtype list
"""
db = Alkis()
query = """
SELECT *
FROM
(SELECT
gml_id,
(land ||
gemarkungsnummer ||
'-' ||
trim(to_char(flurnummer, '000')) ||
'-' || trim(to_char(zaehler, '00000')) ||
'/000') AS flsnr
FROM ax_flurstueck) flur
WHERE flur.gml_id = %s
"""
return db.query(query, (feature_list,))
def _build_html(self, fs):
"""
Weitestgehend aus dem ALKIS-Plugin der Firma norBIT übernommen und auf die eigene Datenbankverbindung
umgeschriebene Methode zum Abruf der Flurstücksinformationen, wenn die Daten aus der Datenbank ausgespielt
worden sind.
Credits to jef-n (https://github.com/jef-n)
:param fs: Flurstücksnummer des betroffenen Flurstücks
:type String
:return: HTML-Repräsentation der Flurstücksinformationen
:rtype String
"""
fs = fs.rstrip(' ')
db = Alkis()
res = db.query("SELECT 1 FROM pg_attribute WHERE attrelid=(SELECT oid FROM pg_class WHERE "
"relname='eignerart') AND attname='anteil'", None)
if res:
exists_ea_anteil = (res[0]['?column?'] == 1)
else:
exists_ea_anteil = False
html = ""
qry = """
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 "''", fs)
best = db.query(qry, None)
res = db.query(
"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" % fs, None)
if len(res) == 1:
res = res[0]
else:
QMessageBox.information(None, "Fehler", u"Flurstück %s nicht gefunden.\n[%s]" % (fs, repr(fs)))
return
res['datum'] = QDate.currentDate().toString("d. MMMM yyyy")
res['hist'] = 0
res['str'] = db.query(
"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" % fs, None)
res['nutz'] = db.query(
"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" % fs, None)
res['klas'] = db.query(
"SELECT kl.*, kls.klf_text FROM klas_3x kl, kls_shl kls WHERE kl.flsnr='%s' AND "
"kl.klf=kls.klf AND kl.ff_stand=0" % fs, None)
res['afst'] = db.query(
"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" % fs, None)
res['best'] = db.query(
"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 "''", fs), None)
for b in res['best']:
b['bse'] = db.query("SELECT * FROM eigner WHERE bestdnr='%s' AND ff_stand=0" % b['bestdnr'], None)
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_time{text-align:right;width:100%%}
.fls_headline_col{background-color:#EEEEEE;width:100%%;height:30px;text-align:center;}
.fls_headline{font-weight:bold;font-size:24px;}
.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;}
</style>
<TABLE class="fls_tab" border="0">
<TR><TD>Flurstücksnachweis</TD><TD class="fls_time" colspan="6"><span>%(datum)s</TD></TR>
<TR><TD colspan="7"><hr style="width:100%%"></TD></TR>
<TR class="fls_headline_col">
<TD colspan="7"><span class="fls_headline">Flurstücksnachweis<span></TD>
</TR>
<TR><TD colspan="7"> </TD></TR>
<TR>
<TD colspan="7"><h3>Flurstück<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 nowrap>%(flsfl)s m²</TD>
</TR>
</TABLE>
""" % res
if res['blbnr']:
html += """
<TABLE border="0" class="fls_tab">
<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 += """
<TABLE border="0" class="fls_tab">
<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 res['str']:
html += """
<TABLE border="0" class="fls_tab">
<TR class="fls_col_names">
<TD></TD><TD>Strasse</TD><TD>Hausnummer</TD>
</TR>
"""
for strres in res['str']:
html += """
<TR class="fls_col_values">
<TD></TD><TD>%(strname)s</TD><TD>%(hausnr)s</TD></TR>
</TR>
""" % strres
html += """
</TABLE>
"""
if res['nutz']:
html += """
<TABLE border="0" class="fls_tab">
<TR class="fls_col_names"><TD width="21%%"></TD><TD width="69%%">Nutzung</TD><TD width="10%%">Fläche</TD></TR>
"""
for nutz in res['nutz']:
html += """
<TR class="fls_col_values"><TD></TD><TD>21%(nutzshl)s - %(nutzung)s</TD><TD nowrap>%(fl)s m²</TD></TR>
""" % nutz
html += """
</TABLE>
"""
else:
html += """
<p>Keine Nutzungen.</p>
"""
if res['klas']:
html += """
<TABLE border="0" class="fls_tab">
<TR class="fls_col_names"><TD width="21%%"></TD><TD width="69%%">Klassifizierung</TD><TD width="10%%">Fläche</TD></TR>
"""
for klas in res['klas']:
html += """
<TR class="fls_col_values"><TD></TD><TD>%(klf_text)s</TD><TD nowrap>%(fl)s m²</TD></TR>
""" % klas
html += """
</TABLE>
"""
else:
html += """
<p>Keine Klassifizierungen.</p>
"""
if res['afst']:
html += """
<TABLE border="0" class="fls_tab">
<TR class="fls_col_names"><TD width="21%%"></TD><TD width="79%%">Ausführende Stelle</TD></TR>
"""
for afst in res['afst']:
html += """
<TR class="fls_col_values"><TD></TD><TD>%(afst_txt)s</TD></TR>
""" % afst
html += """
</TABLE>
"""
else:
html += """
<p>Keine ausführenden Stellen.</p>
"""
if res['best']:
html += """
<TABLE border="0" class="fls_bst">
<TR><TD colspan="6"><h3>Bestände<hr style="width:100%%"></h3></TD></TR>
"""
for best in res['best']:
html += """
<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 += """
<TD>Hist. Bestand</TD><TD>Hist. Zuordnung</TD>
"""
else:
html += """
<TD></TD><TD></TD>
"""
html += """
</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 += """
</TR>
"""
if best['bse']:
html += """
<TR class="fls_col_names"><TD>Anteil</TD><TD colspan="5">Namensinformation</TD></TR>
"""
for bse in best['bse']:
html += """
<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 += """
<p>Keine Eigner gefunden.</p>
"""
html += """
<TR><TD colspan="6"><hr class="fls_hr"></TD></TR>
"""
html += """
</TABLE>
</BODY>
</HTML>
"""
return html
