# -*- coding: utf-8 -*-
"""
/***************************************************************************
 huntcore.py core class of the plugin functionality

                                 Ein QGIS plugin
 Dieses Plugin kann in Kombination mit dem alkisplugin zur Erstellung eines
 Jagdkatasters verwendet werden. Die Datengrundlage sind die in Deutschland
 üblichen ALKIS-Daten (NAS-Format)

 Generated by Plugin Builder: http://g-sherman.github.io/Qgis-Plugin-Builder/
                              -------------------
        begin                : 2020-03-26
        git sha              : $Format:%H$
        copyright            : (C) 2020 by Simon Hodrus
        email                : s.hodrus@gmx.de
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
"""

from qgis.PyQt.QtCore import Qt, QEvent, QSettings, QPoint, QDate, QThread
from qgis.PyQt.QtWidgets import QApplication, QDialog, QDialogButtonBox, QMessageBox, QFileDialog, QProgressBar
from qgis.PyQt.QtPrintSupport import QPrintDialog, QPrinter
from qgis.PyQt.QtSql import QSqlQuery
from qgis.PyQt import uic

from qgis.core import QgsProject, QgsMessageLog, Qgis, QgsCoordinateTransform
from qgis.gui import QgsMapTool, QgsAuthConfigSelect, QgsRubberBand

import os
import re
import numpy as np

from .huntuiclasses import UtilizationsUI, ParcelsCertsUI, SelectUtilisationUI, CalculatePayoffUI, HuntRegUI
from .huntuiclasses import ParcelShare, ParcelsOwner


class HuntCore():
    """core class of the Huntregister plugin functionality"""

    def __init__(self, plugin):
        """Constructor.

        :param plugin: The HuntRegsiter main plugin
            which provides access to alkis and qgis iface functionality
        :type plugin: HuntRegister
        """
        self.plugin = plugin
        self.iface = plugin.iface
        self.alkis = plugin.alkisplugin
        self.hlayer = None

        # alkis holding types
        procBestAlkis0 = [1100, 1101, 1102, 1301, 1302, 1401, 1402, 1501, 1502, 5101]                                                                   # alkis0 owner holdings that must be processed for hunt register all others can be dropped
        procBestAlkis1 = [1100, 1210, 1220, 1310, 1320, 1410, 1420, 1510, 1520]                                                                         # alkis1 owner holdings that must be processed for hunt register all others can be dropped
        self.procBestAlkis = np.unique(np.array(procBestAlkis0 + procBestAlkis1))                                                                       # get unique list of processable holding types

        self.nSupBestAlkis01 = [1401, 1402, 1501, 1502, 1410, 1420, 1510, 1520]                                                                         # alkis0 and alkis1 owner holding types that can not be processed by this version, example data needed
        self.genBestAlkis0 = [1101, 1102]                                                                                                               # alkis0 generic(category) owner types, in case such a category is part of the owner types all other non ignorable owner types must be value +200
        self.genBestAlkis1 = [1210, 1220]                                                                                                               # alkis  generic(category) owner types, in case such a category is part of the owner types all other non ignorable owner types must be value +100

    def quote(self, x, prefix='E'):
        """quote used to generate search terms"""

        if type(x) == str:
            x.replace("'", "''")
            x.replace("\\", "\\\\")
            if x.find("\\") < 0:
                return u"'%s'" % x
            else:
                return u"%s'%s'" % (prefix, x)
        elif type(x) == str and x.find(u"\\"):
            x.replace(u"\\", u"\\\\")
            return u"%s'%s'" % (prefix, str(x))
        else:
            return u"'%s'" % str(x)

    def debugMessage(self, msg, level=Qgis.Critical):
        """plugin message logging"""
        QgsMessageLog.logMessage(msg, u'HUNT', level)

    def getHLayer(self):
        """get the plugins layer"""
        if(self.hlayer is not None):
            return self.hlayer
        else:
            (layerId, res) = QgsProject.instance().readEntry("hunt", "/areaMarkerLayer")
            if res and layerId:
                self.hlayer = QgsProject.instance().mapLayer(layerId)
        return self.hlayer

    def openDB(self, conninfo=None):
        """get Database connection info from alkisplugin."""
        if self.alkis is not None:
            return self.alkis.opendb(conninfo)
        else:
            self.debugMessage("ALKIS opendb() failed to retrive database")
            return (None, None)

    def getEPSG(self):
        """getEPGS database information"""
        (db, conninfo) = self.openDB()
        if db is None:
            return None

        qry = QSqlQuery(db)
        if qry.exec_("SELECT find_srid('','po_points', 'point')") and qry.next():
            return qry.value(0)
        else:
            self.debugMessage("ALKIS QRY failed to get EPSG from database")
            return None

    def test(self, layer):
        """quick test"""
        # (db, conninfo) = self.openDB()
        # if db is None:
        #     return None
        # #qryTest = u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['nutz_21', 'nutz_shl']))
        # strq = u"SELECT gml_id,alkis_flsnr(ax_flurstueck) FROM ax_flurstueck WHERE endet IS NULL AND st_contains(wkb_geometry,st_geomfromtext('POINT(574486.182 5285038.143)'::text,25832))"

        # qry = QSqlQuery(db)
        # if qry.exec_(strq):
        #     while qry.next():
        #         #val = (qry.value(0), qry.value(1))
        #         pass
        # else:
        #     pass
        return

    def retrieve(self, where):
        """retrive parcel flsnr and gml_id from ax_flurstueck based on where clause"""
        if not isinstance(where, list):                                                                                                             # in case where is not a list place where in list. Otherwise join() will fragment the string
            where = [where]

        fs = []

        (db, conninfo) = self.openDB()
        if db is None:
            return fs

        qry = QSqlQuery(db)

        if not qry.exec_(
            u"SELECT "
            u"gml_id"
            u",alkis_flsnr(ax_flurstueck)"
            u" FROM ax_flurstueck"
            u" WHERE endet IS NULL"
            u" AND (%s)" % u" AND ".join(where)
        ):
            QMessageBox.critical(None, "Fehler", u"Konnte Abfrage nicht ausführen.\nSQL:%s\nFehler:%s" % (qry.lastQuery(), qry.lastError().text()))
            return fs

        while qry.next():
            fs.append({'gmlid': qry.value(0), 'flsnr': qry.value(1)})
        return fs

    def retrieveFromUtilGem(self, selGemList, selUtilList):
        """retrive parcel flsnr and gml_id based on (Nutzung) and (Gemarkung)

        :param selGemList [str..]: list of (Gemarkung) numbers as strings
        :param selUtilList [{..}..]: list of (Nutzung) dictionaries
        """

        fs = []

        (db, conninfo) = self.openDB()
        if db is None:
            return fs

        qry = QSqlQuery(db)

        if not qry.exec_(
            u"SELECT "
            u"gml_id"
            u",alkis_flsnr(ax_flurstueck)"
            u" FROM ax_flurstueck, flurst, nutz_21, nutz_shl"
            u" WHERE endet IS NULL"
            u" AND alkis_flsnr(ax_flurstueck)=flurst.flsnr AND alkis_flsnr(ax_flurstueck)=nutz_21.flsnr"
            u" AND flurst.gemashl IN (" + ','.join(self.quote(n) for n in selGemList) + ")"
            u" AND nutz_21.nutzsl=nutz_shl.nutzshl AND nutz_21.ff_stand=0 AND nutz_shl.nutzshl IN (" + ','.join(self.quote(n["nutzshl"]) for n in selUtilList) + ')'
        ):
            QMessageBox.critical(None, "Fehler", u"Konnte Abfrage nicht ausführen.\nSQL:%s\nFehler:%s" % (qry.lastQuery(), qry.lastError().text()))
            return fs

        while qry.next():
            fs.append({'gmlid': qry.value(0), 'flsnr': qry.value(1)})
        return fs

    def highlight(self, fs, layer=None, zoomTo=False, click=False, add=False):
        """highlight parcels in fs
           add parcels in fs to highlighted parcels if add=True otherwise clear previously highlighted
           toggle highlighted state if click=True
        """
        if layer is None:                                                                                                                           # use standard plugin layer in case no optional layer is provided
            layer = self.getHLayer()

        # openDB not needed in case zoom is always unused
        (db, conninfo) = self.openDB()
        if db is None or layer is None or not fs:
            return

        gmlids = set()
        for e in fs:
            gmlids.add(e['gmlid'])                                                                                                                  # get unique gmlids from parcel list
        if add:
            m = re.search("layer='ax_flurstueck' AND gml_id IN \\('(.*)'\\)", layer.subsetString())                                                 # search highlighted gmlids in layer subsetstring
            if m:
                gmlidsLoaded = set(m.group(1).split("','"))                                                                                         # get set of highlighted gmlids
                gmlidsToRemove = set()

                if(click):                                                                                                                          # add to remove set if click=True and gmlid is already highlighted
                    for e in gmlids:
                        if e in gmlidsLoaded:
                            gmlidsToRemove.add(e)

                gmlids = gmlids | gmlidsLoaded                                                                                                      # join sets of already loaded and gmlids and new gmlids
                gmlids = gmlids.difference(gmlidsToRemove)                                                                                          # remove the gmlids in the remove set

        layer.setSubsetString("layer='ax_flurstueck' AND gml_id IN ('" + "','".join(gmlids) + "')")                                                 # reset layer subsetstring with new data

        # maybe the refresh could be triggered for the specific layer only
        self.iface.mapCanvas().refresh()                                                                                                            # refresh map canvas

        #if zoomTo and qry.exec_( u"SELECT st_extent(wkb_geometry) FROM ax_flurstueck WHERE gml_id IN ('" + "','".join( gmlids ) + "')" ) and qry.next():
        #    self.zoomToExtent( qry.value(0), self.areaMarkerLayer.crs() )
        return

    def hitOnHighlight(self, e, layer=None):
        """highlight or remove highlighting of a single parcel based on map canvas mouse click event

        :param e: mouse click event args
        """
        if layer is None:                                                                                                                           # use standard plugin layer in case no optional layer is provided
            layer = self.getHLayer()

        if layer is None or e is None:
            self.debugMessage("Layer is None or mouse event is None")
            return

        point = self.iface.mapCanvas().getCoordinateTransform().toMapCoordinates(e.x(), e.y())                                                      # get clicl point in map coordinates

        src = QgsProject.instance().crs()
        dst = layer.crs()

        if src and dst and src != dst:                                                                                                              # transform in case source and destination differ
            t = QgsCoordinateTransform(src, dst, QgsProject.instance())
            point.transform(t)

        p = "POINT(%.3lf %.3lf)" % (point.x(), point.y())                                                                                           # get string representation of coordinates for query

        try:
            QApplication.setOverrideCursor(Qt.WaitCursor)
            where = u"st_contains(wkb_geometry,st_geomfromtext('{}'::text,{}))".format(p, self.getEPSG())
            fs = self.retrieve(where)                                                                                                               # retrive parcels where coordinate is in parcel geometry

            if not fs:
                QApplication.restoreOverrideCursor()
                QMessageBox.information(None, "Fehler", "Kein Flurstück gefunden")
                return
            else:
                self.highlight(fs, click=True, add=True)
        finally:
            QApplication.restoreOverrideCursor()
        return fs

    def clearHighlight(self, layer=None):
        """clear all highlighted parcels in layer"""
        if layer is None:                                                                                                                           # use standard plugin layer in case no optional layer is provided
            layer = self.getHLayer()
        if layer is None:
            return

        layer.setSubsetString("false")                                                                                                              # clear layer subsetString
        self.iface.mapCanvas().refresh()                                                                                                            # refresh map canvas

    def swapAlkisToHunt(self, layer=None):
        """copy highlighted parcels from alkisplugin to layer"""
        (layerId, res) = QgsProject.instance().readEntry("alkis", "/areaMarkerLayer")                                                               # search for entry of alkisplugin selection layer
        if res and layerId:
            alkisSelectAreaLayer = QgsProject.instance().mapLayer(layerId)
        else:                                                                                                                                       # swap can not be processed becuase of missing layer
            self.iface.messageBar().pushMessage(
                "Fehler", "'Flächenmarkierung' fehlt in ALKIS 'Markierungen' Gruppe.", level=Qgis.Critical, duration=10)
            return
        if layer is None:                                                                                                                           # use standard plugin layer in case no optional layer is provided
            layer = self.getHLayer()
        if layer is None:                                                                                                                           # try layer restore on runtime in case no target layer was found
            self.iface.messageBar().pushMessage(
                "Fehler", "'Jagd-Flächenmarkierung' fehlt in 'Markierungen' Gruppe. Reparaturversuch gestartet", level=Qgis.Critical, duration=10)
            self.plugin.initLayers()
            return

        alkisGmldata = set(self.getGMLData(alkisSelectAreaLayer))                                                                                   # get gmlids from alkisplugin layer
        huntGmldata = set(self.getGMLData(layer))                                                                                                   # get gmlids from huntplugin layer
        gmldata = huntGmldata | alkisGmldata                                                                                                        # join gmlids

        if not gmldata:
            return

        layer.setSubsetString("layer='ax_flurstueck' AND gml_id IN ('" + "','".join(gmldata) + "')")                                                # set joined gmlids to layer
        self.iface.mapCanvas().refresh()

    def showSummaryDialog(self, layer=None):
        """ show pacel info summary as html content"""
        if layer is None:                                                                                                                           # use standard plugin layer in case no optional layer is provided
            layer = self.getHLayer()
        if layer is None:
            self.iface.messageBar().pushMessage(
                "Fehler", "'Jagd-Flächenmarkierung' fehlt in 'Markierungen' Gruppe. Reparaturversuch gestartet", level=Qgis.Critical, duration=10)
            self.plugin.initLayers()
            return

        gmldata = self.getGMLData(layer)                                                                                                            # get gmlids of all selected parcels in layer
        if(gmldata):
            where = u"ax_flurstueck.gml_id IN (" + ','.join(self.quote(n) for n in gmldata) + ")"
            fs = self.retrieve(where)                                                                                                               # get related database gmlid and flsnr
            dialog = None

            parcelcount = len(fs)
            QApplication.setOverrideCursor(Qt.WaitCursor)
            try:
                utils = self.getUtilizations(fs)                                                                                                    # get utilization database dictionaries for all selected parcels, get

                if not utils and parcelcount > 0:
                    QMessageBox.critical(None, "Fehler", u"Keine Flurstücksnutzung gefunden")
                else:
                    dialog = UtilizationsUI((utils, parcelcount))
            finally:
                QApplication.restoreOverrideCursor()
            if dialog is not None:
                dialog.exec_()
        else:
            QMessageBox.information(None, "Information", u"Keine Flurstücke ausgewählt")

    def showListSelection(self):
        """show dialog that provides parcel selection based on pacel attributes"""
        data = self.getExstUtilGem()                                                                                                                # returns dictionary {'gemarkung', 'nutzung'} containing the user selected values
        if not data:                                                                                                                                # empty dictonary was returend from dialog
            self.debugMessage("No data returned from SelectUtilisationUI dialog")
            return

        dialog = SelectUtilisationUI(data)
        result = dialog.exec_()
        if(result):
            fs = self.retrieveFromUtilGem(dialog.selGemarkungText, dialog.selUtilisationData)                                                       # retrive parcel gmlids and flsnr based on user selected parcel attributes of (Gemarkund) and (Nutzung)
            if fs:
                self.highlight(fs, add=True)

    def showParcelCerts(self, layer=None):
        """show window containing html list of parcel certificates"""
        if layer is None:                                                                                                                           # use standard plugin layer in case no optional layer is provided
            layer = self.getHLayer()
        if layer is None:
            self.iface.messageBar().pushMessage(
                "Fehler", "'Jagd-Flächenmarkierung' fehlt in 'Markierungen' Gruppe. Reparaturversuch gestartet", level=Qgis.Critical, duration=10)
            self.plugin.initLayers()
            return

        gmldata = self.getGMLData(layer)                                                                                                            # get gmlids of all selected parcels
        if(gmldata):
            where = u"ax_flurstueck.gml_id IN (" + ','.join(self.quote(n) for n in gmldata) + ")"
            fs = self.retrieve(where)                                                                                                               # get database parcel information

            if(len(fs) > 500):                                                                                                                      # info that processing and data showing might take a while
                opendiag = QMessageBox.information(None, "Information",
                                                   (u"Das Anzeigen von {} Flurstücksnachweisen kann einige Sekunden benötigen.\n".format(len(fs)) +
                                                    "Eventuell kommt es zu einem Anzeigefehler beim Fortschrittsbalken.\n" +
                                                    "Trotzdem fortfahren?"), buttons=QMessageBox.Apply | QMessageBox.Abort)          # create a dialog with apply and abort button
                if opendiag != QMessageBox.Apply:
                    return

            dialog = None

            QApplication.setOverrideCursor(Qt.WaitCursor)
            try:
                certs = self.getCertData(fs)
                if certs:
                    dialog = ParcelsCertsUI(certs)
            finally:
                QApplication.restoreOverrideCursor()
            if dialog is not None:
                dialog.exec_()
        else:
            QMessageBox.information(None, "Information", u"Keine Flurstücke ausgewählt")

    def showHuntReg(self, layer=None):
        """show window containing the hunt register as html content"""
        if layer is None:                                                                                                                           # use standard plugin layer in case no optional layer is provided
            layer = self.getHLayer()
        if layer is None:
            self.iface.messageBar().pushMessage(
                "Fehler", "'Jagd-Flächenmarkierung' fehlt in 'Markierungen' Gruppe. Reparaturversuch gestartet", level=Qgis.Critical, duration=10)
            self.plugin.initLayers()
            return

        gmldata = self.getGMLData(layer)                                                                                                            # get gmlids of all selected parcels
        if(gmldata):
            where = u"ax_flurstueck.gml_id IN (" + ','.join(self.quote(n) for n in gmldata) + ")"
            fs = self.retrieve(where)                                                                                                               # get database parcel information

            if fs:
                utils = self.getExstUtil(fs)                                                                                                        # get all utilizations of the selected parcels
                dialog = CalculatePayoffUI(utils)                                                                                                   # create dialog for choosing the utilizations to be excluded
                result = dialog.exec_()
                info = None
                if result:
                    QApplication.setOverrideCursor(Qt.WaitCursor)

                    try:
                        huntreg = self.getHuntReg([n['flsnr'] for n in fs], dialog.exUtilisationData)                                               # get hunt register dictonary, the methode used the parcel numbers and the utilizations to be exclude
                        huntreg['entryData'] = dialog.entryData                                                                                     # add formular data to the dictonaty

                        holdEmpty = huntreg['errLists']['ehl']                                                                                      # empty holdings list, no processable holding found for the parcel
                        holdnSupp = huntreg['errLists']['hnl']                                                                                      # holding not supported list, the holding type can not be processed by the current version of this program
                        holdMulti = huntreg['errLists']['mhl']                                                                                      # multiple holdings list, multiple non matching holding types for one parcel
                        holdInSha = huntreg['errLists']['snl']                                                                                      # shares not valid list, all shares do not add up to 1

                        # show parcles without valid owners
                        if holdEmpty:
                            msg = u"Grundstückseigentümer für folgende Flurstücke nicht vorhanden:\n" + '\n'.join(str(n) for n in holdEmpty)
                            self.flsnrMessage(msg, holdEmpty, u"Grundstückseigentümer nicht vorhanden")
                        # show parcles with unsupported holdings
                        if holdnSupp:
                            msg = u"Härtefall, Bestandstyp nicht verarbeitbar. Folgende Flurstücke müssen manuell berechnet werden:\n" + '\n'.join(str(n) for n in holdnSupp)
                            self.flsnrMessage(msg, holdnSupp, u"Härtefälle")
                        # show parcles without valid holdings
                        if holdMulti:
                            msg = u"Bestandsdaten für folgende Flurstücke fehlerhaft:\n" + '\n'.join(str(n) for n in holdMulti)
                            self.flsnrMessage(msg, holdMulti, u"Bestandsdaten fehlerhaft")
                        # show parcles without valid shares
                        if holdInSha:
                            msg = u"Bestandsanteile für folgende Flurstücke fehlerhaft:\n" + '\n'.join(str(n) for n in holdInSha)
                            self.flsnrMessage(msg, holdInSha, u"Bestandsanteile fehlerhaft")

                        info = HuntRegUI(huntreg)
                    finally:
                        QApplication.restoreOverrideCursor()
                    if info is not None:
                        info.exec_()
        else:
            QMessageBox.information(None, "Information", u"Keine Flurstücke ausgewählt")

    def getGMLData(self, layer):
        """get the gmlids of the layer"""
        gIds = []

        #idx = layer.fields().indexFromName("gml_id")
        for feat in layer.getFeatures():
            gIds.append(feat.attribute("gml_id"))
        return gIds

    def getUtilizations(self, fs):
        """get utilizations of a list of parcels (fs)"""
        utils = []

        (db, conninfo) = self.openDB()
        if db is None or not fs:
            return []

        qry = QSqlQuery(db)
        if not (qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['nutz_21', 'nutz_shl']))) and qry.next() and qry.value(0)):            # check if needed tables are missing
            self.iface.messageBar().pushMessage(
                "Fehler", "ALKIS Datenbanktabellen fehlerhaft", level=Qgis.Critical, duration=10)                                                   # the database does not contain the needed tables
            return []

        parcelcount = len(fs)
        progBar = self.createProgressMessage("Vearbeite Nutzungen von {} Flurstücken".format(parcelcount))                                                  # create a progress bar in the qgis message bar
        for n, e in enumerate(fs):                                                                                                                  # iterate parcels
            progBar.setValue(int(n / float(parcelcount) * 100))
            res = 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" % e['flsnr'])
            if not res:
                self.iface.messageBar().pushMessage(
                    "Error", "Flurstücksnutzung für %s nicht vorhanden => Prozess fehlgeschlagen" % e['flsnr'])
                return []
            for util in res:                                                                                                                        # append every parcel utilization to the list
                utils.append(util)
        self.iface.messageBar().clearWidgets()                                                                                                      # delete progress message
        return utils

    def getExstUtilGem(self):
        """Get (Gemarkungen) and (Nutzungen) for all existing parcels"""
        res = {}                                                                                                                                    # return a dictonary containing 'nutzung' list of dictonaries and 'gemarkung' list of strings

        (db, conninfo) = self.openDB()
        if db is None:
            return res

        qry = QSqlQuery(db)
        if not (qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['ax_flurstueck', 'flurst', 'nutz_21', 'nutz_shl']))) and qry.next() and qry.value(0)):
            self.iface.messageBar().pushMessage(
                "Fehler", "ALKIS Datenbanktabellen fehlerhaft", level=Qgis.Critical, duration=10)                                                   # database tables are missing
            return

        where = u"'true'"
        fs = self.retrieve(where)                                                                                                                   # return all parcel database basic information by utilizing retrieve function
        fsnr = ["'{}'".format(n['flsnr']) for n in fs]                                                                                              # get 'flsnr' of all parcels and quote the numbers for query string

        # get distinct list of all utilizations in list of fs
        res['nutzung'] = self.fetchall(db, "SELECT DISTINCT nu.nutzshl, nu.nutzung FROM nutz_21 n21, nutz_shl nu WHERE n21.nutzsl=nu.nutzshl AND n21.ff_stand=0 AND n21.flsnr IN (%s)" % ", ".join(fsnr) + " ORDER BY nu.nutzshl")
        # get list of (Gemarkung) strings
        res["gemarkung"] = self.fetchall(db, "SELECT DISTINCT gemashl FROM flurst")
        return res

    def getExstUtil(self, fs):
        """Get utilization (Nutzungen) for all existing parcels"""
        res = {}

        (db, conninfo) = self.openDB()
        if db is None or not fs:
            return res

        qry = QSqlQuery(db)
        if not (qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['ax_flurstueck', 'nutz_21', 'nutz_shl']))) and qry.next() and qry.value(0)):
            self.iface.messageBar().pushMessage(
                "Fehler", "ALKIS Datenbanktabellen fehlerhaft", level=Qgis.Critical, duration=10)                                                   # database tables are missing
            return
        fsnr = ["'{}'".format(n['flsnr']) for n in fs]
        # get distinct list of all utilizations in parcel list of fs
        res = self.fetchall(db, "SELECT DISTINCT nu.nutzshl, nu.nutzung FROM nutz_21 n21, nutz_shl nu WHERE n21.nutzsl=nu.nutzshl AND n21.ff_stand=0 AND n21.flsnr IN (%s)" % ", ".join(fsnr) + " ORDER BY nu.nutzshl")
        return res

    def fetchall(self, db, sql):
        """fetch all results from sql query"""
        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:
            self.debugMessage("SQL query execution failed: " + qry.lastError().text())
        return rows

    def getCertData(self, fs):
        """get list of certification data to create html list of parcel certificates"""
        certs = []

        (db, conninfo) = self.openDB()
        if db is None or not fs:
            return certs

        qry = QSqlQuery(db)
        if qry.exec_("SELECT 1 FROM pg_attribute WHERE attrelid=(SELECT oid FROM pg_class WHERE relname='eignerart') AND attname='anteil'") and qry.next():
            exists_ea_anteil = qry.value(0) == 1                                                                                                        # does the database contain owner parcel share information
        else:
            exists_ea_anteil = False

        parcelcount = len(fs)
        progBar = self.createProgressMessage("Vearbeitung von {} Flurstücken".format(parcelcount))                                                      # create a progress bar in the qgis message bar
        for n, e in enumerate(fs):                                                                                                                      # iterate all parcels in fs
            progBar.setValue(int(n / float(parcelcount) * 100))

            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" % e['flsnr'])
            if len(res) == 1:
                res = res[0]
            else:
                QMessageBox.information(None, "Fehler", u"Flurstückinformation für %s nicht gefunden" % (e['flsnr']))
                return certs

            res['date'] = QDate.currentDate().toString("d. MMMM yyyy")
            res['hist'] = 0

            if qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['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" % e['flsnr'])

            if qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['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" % e['flsnr'])

            if qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['klas_3x', 'kls_shl']))) and qry.next() and qry.value(0):
                res['klas'] = self.fetchall(db, "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" % e['flsnr'])

            if qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['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" % e['flsnr'])

            if qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['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 "''", e['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'])

            certs.append(res)                                                                                                                           # return certificate data as list of tuples
        self.iface.messageBar().clearWidgets()                                                                                                          # delete progress message

        certs.sort(key=lambda c: c['flsnr'], reverse=False)                                                                                             # order the certificate data by the parcel number 'flsnr'
        return (certs)

    def getHuntReg(self, fllist, utilExldata):
        """get dictionary of pacel owner data and additional data to create a html hunt register"""
        (db, conninfo) = self.openDB()
        if db is None:
            return None

        qry = QSqlQuery(db)
        if qry.exec_("SELECT 1 FROM pg_attribute WHERE attrelid=(SELECT oid FROM pg_class WHERE relname='eignerart') AND attname='anteil'") and qry.next():
            exists_ea_anteil = qry.value(0) == 1
        else:
            exists_ea_anteil = False

        parcelOwnerList = []                                                                                                                            # list of ParcelsOwner instances each owner instance cotains a list of parcels and the respective parcel share for this owner
        nameIdentifierList = []                                                                                                                         # list of owner ids this list is used to check if the owner instance already exists without looping the owner instances
        utilExlList = [n['nutzshl'] for n in utilExldata]                                                                                               # list of utilizations that should not be included in the huntregister

        ret = {}                                                                                                                                        # return dictionary
        procLists = {}
        procLists['pol'] = parcelOwnerList
        procLists['nil'] = nameIdentifierList
        procLists['uel'] = utilExlList

        errLists = {}                                                                                                                                   # dictionary of flsnr lists that could not be processed as intended
        errLists['ehl'] = []                                                                                                                            # empty holdings list, no processable holding found for the parcel
        errLists['hnl'] = []                                                                                                                            # holding not supported list, the holding type can not be processed by the current version of this program
        errLists['mhl'] = []                                                                                                                            # multiple holdings list, multiple non matching holding types for one parcel
        errLists['snl'] = []                                                                                                                            # shares not valid list, all shares do not add up to 1

        parcelcount = len(fllist)
        progBar = self.createProgressMessage("Vearbeitung von {} Flurstücken".format(parcelcount))                                                      # create a progress bar in the qgis message bar
        for n, flsnr in enumerate(fllist):                                                                                                              # enumerate all selected parcels
            progBar.setValue(int(n / float(parcelcount) * 100))

            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.critical(None, "Error", u"Parcel %s not found." % flsnr)
                return

            if qry.exec_(u"SELECT " + u" AND ".join(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['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(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['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(map(lambda x: "has_table_privilege('{}', 'SELECT')".format(x), ['bestand', 'eignerart', 'eign_shl']))) and qry.next() and qry.value(0):
                res['best'] = self.fetchall(db, "SELECT ea.bvnr, ea.b,'' 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'])

            parcelData = {}                                                                                                                             # parcel data dictionary
            parcelData['flsnr'] = res['flsnr']                                                                                                          # 'flsnr' database id
            parcelData['tSur'] = res['amtlflsfl']                                                                                                       # official area
            parcelData['utilSur'] = [dict([('util', n['nutzshl']), ('sur', n['fl'])]) for n in res['nutz']]                                             # list of dictionaries, one dictionary for each utilization containing the utilization key 'nutzshl' and the associated area

            if "str" in res:                                                                                                                            # address information of the parcel
                strname = ""
                strs = [n['strname'] for n in res['str'] if len(n) > 0]                                                                                 # get street names out of the address information, big parcels border multiple parcels or the same strret with multiple numbers
                uniquestrs = np.unique(np.array(strs))                                                                                                  # remove duplicate street names
                strname += ", ".join(uniquestrs)                                                                                                        # join all streets of the parcel to a singel streetnames string
                parcelData['str'] = strname

            if 'best' in res:                                                                                                                           # process owner holdings
                holdings = self.processHoldings(flsnr, res['best'], errLists)                                                                           # list of valid and processable holdings

                if all(['bse' in h for h in holdings]):                                                                                                 # iterate only in case of all holdings have valid owner data
                    for best in holdings:                                                                                                               # iterate all parcel holdings
                        share = best['anteil']                                                                                                          # get the holding share of the parcel
                        (owners, ownersType) = self.processOwnerType(flsnr, best['bse'])                                                                # get owners and owners type owner type is None for separate natural persons

                        if ownersType is None:                                                                                                          # the legal entity is a single owner, most of the time a natural person or organisation
                            for bse in best['bse']:
                                nameList = [(u"%s;%s;%s;%s" % (bse['anrede'], bse['ak_grade'], bse['name1'], bse['name2']))]                            # joins the name information to a single string                                                                                # gets the owner share for the current parcel
                                self.processOwner(procLists, nameList, ownersType, share, bse['antverh'], parcelData)                                   # adds the parcel share to each owner
                        else:
                            nameList = []
                            bsel = sorted(best['bse'], key=lambda n: n['name1'])
                            for bse in bsel:
                                nameList.append(u"%s;%s;%s;%s" % (bse['anrede'], bse['ak_grade'], bse['name1'], bse['name2']))                          # generate a name list of all owners
                            self.processOwner(procLists, nameList, ownersType, share, '1/1', parcelData)                                                # a legal entity with multiple individuals with non explicit defined shares is owner of the complete parcel
                else:
                    errLists['ehl'].append(flsnr)                                                                                                       # parcel has no holding or owners => add to error list

        totalUnPacSur = 0
        for ownData in parcelOwnerList:                                                                                                                 # calculate the total huntable area
            totalUnPacSur += ownData.gSurUnPacOwn

        self.iface.messageBar().clearWidgets()                                                                                                          # delete progress message
        ret['procLists'] = procLists                                                                                                                    # add the processed list to the return dictionary
        ret['errLists'] = errLists                                                                                                                      # add the holding and owner error list to the return dictionary
        ret['totalUnPacSur'] = totalUnPacSur                                                                                                            # add the total huntable area to the return dictionary
        return ret

    def processHoldings(self, flsnr, holdings, errLists):
        """process the holdings of one parcel
        holdings: list of holdings
        errLists: dictionary with different error lists"""

        allProcHoldings = [h for h in holdings if int(h['b']) in self.procBestAlkis]
        procHoldings = [h for h in allProcHoldings if int(h['b']) not in self.genBestAlkis0 + self.genBestAlkis1]                                       # all non generic holdings
        uniqueGenHoldingB = np.unique(np.array([int(h['b']) for h in allProcHoldings if int(h['b']) in self.genBestAlkis0 + self.genBestAlkis1]))       # unique list of processable generic holding types

        if len(procHoldings) == 0:                                                                                                                      # no holding left for processing add flsnr to matching error list
            errLists['ehl'].append(flsnr)
            return []

        if [True for h in allProcHoldings if int(h['b']) in self.nSupBestAlkis01]:                                                                      # contains one or multiple holdings that are not processable by the current version of the program
            errLists['hnl'].append(flsnr)
            return []

        if uniqueGenHoldingB:                                                                                                                           # there are generic holdings
            if len(uniqueGenHoldingB) > 1:                                                                                                              # the current supported generics do not support multiple different gerneic types in the same holding list
                errLists['mhl'].append(flsnr)
            else:                                                                                                                                       # all non generic holdinhs must be of the same target holding type
                genHType = uniqueGenHoldingB[0]
                targetHType = genHType + 200 if genHType in self.genBestAlkis0 else genHType + 100                                                      # get the target type according to alkis0 or alkis1
                if [True for h in procHoldings if int(h['b']) != targetHType]:                                                                          # check for a holding that does mot match the target type
                    errLists['mhl'].append(flsnr)
                    return []

        (isExplicit, isValid) = ParcelShare.SharesCheckStatic([ParcelShare.StringFractionStatic(h['anteil']) for h in procHoldings])                    # check that the sum of all holdings shares is 1 or all shares are not explicitly set
        if not isValid:
            errLists['snl'].append(flsnr)
            return []

        return procHoldings                                                                                                                             # return all holdings that can be processed

    def processOwnerType(self, flsnr, entries):
        """get the owner type for a single holding by its owner entries"""
        ownersType = None
        ownerTypes = set()                                                                                                                              # set of unique owner types
        owners = []                                                                                                                                     # list of owners

        for ent in entries:                                                                                                                             # iterata all owner entries
            name1 = ent['name1']
            if len(name1) > 0:                                                                                                                          # ignore empty entries
                if name1[0] == '(' and name1[-1] == ')':                                                                                                # filter type entries
                    ownerTypes.add(name1)
                else:
                    owners.append(ent)                                                                                                                  # filter valid entries

        if len(ownerTypes) > 1:                                                                                                                         # multiple legal entity types found, this is a error case
            self.flsnrMessage(
                u"Mehrere Ids zur Feststellung des Eignertyps für Bestandsnummer %s in Flurstück %s\n Bestand in Kalkulation nicht berücksichtigt" % entries[0]['bestdnr'], flsnr,
                [flsnr], u"Bestandsfehler in Flurstück")
            return [], None

        (isExplicit, isValid) = ParcelShare.SharesCheckStatic([ParcelShare.StringFractionStatic(b['antverh']) for b in owners])                         # check validity of all shares
        if not isValid:
            self.flsnrMessage(
                u"Die Summe der Anteile für Bestandsnummer %s in Flurstück %s is kleiner als 1\n Bestand in Kalkulation nicht berücksichtigt" % entries[0]['bestdnr'], flsnr,
                [flsnr], u"Bestandsfehler in Flurstück")
            return [], None

        if not isExplicit and len(ownerTypes) == 1:                                                                                                     # the owners are a single legal entity in case a owner type was found and the share sum is not explicit
            ownersType = ownerTypes.pop()
        elif not isExplicit:                                                                                                                            # non explicit but no owners type was found
            ownersType = "(Unbekannt (Angabe fehlt in Datensatz))"

        return (owners, ownersType)

    def flsnrMessage(self, msg, flsnrs, diagtitle=None):
        """dialog for parcles, the user can directly open the parcel certificates"""
        opendiag = QMessageBox.critical(None, "Fehler", msg, buttons=QMessageBox.Open | QMessageBox.Close)                                              # create a dialog with open and close option
        if opendiag == QMessageBox.Open:                                                                                                                # in case the dialog result is open => get parcel certificates for all parcel numbers in flsnrs
            dialog = None
            QApplication.setOverrideCursor(Qt.WaitCursor)
            try:
                fs = [dict([('flsnr', n)]) for n in flsnrs]
                certs = self.getCertData(fs)
                if certs:
                    dialog = ParcelsCertsUI(certs, title=diagtitle)
            finally:
                QApplication.restoreOverrideCursor()
            if dialog is not None:
                dialog.exec_()

    def processOwner(self, pLists, names, nameType, holdShare, ownerShare, parcelData):                                                                 # parcelData is the parcel data for the current owner
        """assign each parcel data to the corresponding owner"""

        # names must have been sorted otherwise the identifier is different on different compositions
        nameIdentifier = hash(",".join(names))                                                                                                          # get an id by calculation the hash of the owner name fragments
        parcelS = ParcelShare(parcelData, pLists['uel'], holdShare, ownerShare)                                                                         # calculate the parcel share by excluding the utilizations from the utilization exclusion list (uel)

        if not parcelS.valid:                                                                                                                           # show an error in case the calculation does not add up
            self.iface.messageBar().pushMessage(
                "Fehler", "Brechnungsergebnis für Flurstück %s nicht stimmig" % parcelS.flsnr, level=Qgis.Critical)

        if nameIdentifier in pLists['nil']:                                                                                                             # check if the name identifier list (nil) already contains this owner
            ind = pLists['nil'].index(nameIdentifier)                                                                                                   # get the owners list index
        else:
            pLists['pol'].append(ParcelsOwner(names, nameType))                                                                                         # add owner to the parcel owner list (pol)
            pLists['nil'].append(nameIdentifier)                                                                                                        # add the owners name identified to the list of existing owner identifiers
            ind = -1                                                                                                                                    # index to the latest added owner

        pLists['pol'][ind].sharedParcelsHolds.append(parcelS)                                                                                           # add the parcel share to the owner based pn the index

    def createProgressMessage(self, msg):
        """place message and progressbar widget in the qgis message bar"""
        progressMessageBar = self.iface.messageBar().createMessage(msg)
        progress = QProgressBar()
        progress.setAlignment(Qt.AlignLeft | Qt.AlignVCenter)
        progressMessageBar.layout().addWidget(progress)
        self.iface.messageBar().pushWidget(progressMessageBar, Qgis.Info)
        return progress
