# -*- coding: utf-8 -*-
"""Moduł exportu danych do bazy"""

from Mapa_PU.swdl.kontroler.database_helper import DatabaseHelper
from Mapa_PU.swdl.kontroler import order_key_generator
from Mapa_PU.swdl.model.tags import TagsLayer
from Mapa_PU.swdl.model.swdl.forest_global_data import Measure, Municipality
from Mapa_PU.swdl.model.swdl.natural_values import NaturalValue
from Mapa_PU.swdl.model.swdl.forest_map_no_sf import ForestMapNoSF
from Mapa_PU.swdl.model.swdl.subarea_data import SubareaData
from Mapa_PU.widok.pasek_wiadomosci import PasekWiadomosc
from qgis.core import QgsTask

"""Eksport danych do bazy taksatora"""
class DatabaseExport():
    
    def __init__(self,DatabaseHelper,Layers,GeometryHelper):
        self.__database=DatabaseHelper
        self.__layers_path=Layers
        self.__geometry_helper=GeometryHelper
    
    def insert_into_database(self,Swdl):
        """Wstawia obiekty z swdl do bazy
        :param Swdl; obiekt Swdl
        """
        try:
#             pasek.setText("Import danych z pliku SWDL")
#             pasek.setProgressValue(10)
            self.__database.open()
#             pasek.setText("Aktualizacja gmin")
            #task.setProgress(20)
            #Aktualizacja gmin
            municipalities=Swdl.forest_global_data.municipalities
            self.update_municipalities(municipalities)
#             pasek.setText("Wstawianie  obrębów ewidencyjnych do bazy")
            #task.setProgress(30)
            
            #Wstawienie  obrębów ewidencyjnych (communities) do bazy
            communities=Swdl.forest_global_data.communities
            self.insert_communities(communities)
#             pasek.setText("Uzupełnianie słownika wskazówek gospodarczych")
            #task.setProgress(40)
            
            #Uzupełnienie słownika wskazówek gosp
            self.update_measures(Swdl.forest_global_data.measures)
#             pasek.setText("Wstawianie danych inwenteryzacyjnych")
            #task.setProgress(50)
            
            #Wstawienie danych inwenteryzacyjnych
            forest_data_desc=Swdl.forest_data_desc
            self.insert_inventory_data(forest_data_desc)
#             pasek.setText("Wstawianie walorów przyrodniczych")
            #task.setProgress(60)
            
            #Wstawienie walorów przyrodniczych
            self.insert_forest_natural_values(Swdl.forest_natural_values)
#             pasek.setText("Wstawianie wydzieleń")
            #task.setProgress(70)
            
            #wstawienie obiektów subarea
            self.insert_subareas(Swdl.subarea_data_list)
#             pasek.setText("Wstawianie danych do warstw")
            #task.setProgress(80)
            #Wstawienie warstw forestMapNoSF
            self.insert_forest_map_no_sf(Swdl.forest_map_no_sf)
        except Exception as e:
            raise e
        finally:
            self.__database.close()

    def insert_subareas(self,subareas):
        """Wstawia do bazy obiekty subarea
        :param Swdl;obiekty subarea
        """
        try:
            dictionary_parcels={}
            parcel_land_use_dictionary=[]
            branch_list=[]
            for subarea in subareas:
#                 print('{} {}'.format(i,subarea.address_forest))
                self.insert_subarea(subarea,branch_list,dictionary_parcels,parcel_land_use_dictionary)
                #progress.setValue(i+1)
#                
            
        except Exception as e:
            raise e
                 
    def insert_subarea(self,subarea:SubareaData,branch_list:[],dictionary_parcels:{},parcel_land_use_dictionary:[]):
        """Wstawia do bazy obiekt subarea
        :param subarea; obiekt subarea
        """
        try:
            arodes_int_num=self.insert_subarea_data(subarea,branch_list)
            self.insert_stand_pecs(arodes_int_num,subarea.arod_stand_pecs)
            self.insert_categories(arodes_int_num,subarea.arod_categories)
            self.insert_cues(arodes_int_num,subarea.arod_cues)
            self.insert_goals(arodes_int_num,subarea.arod_goals)
            self.insert_phenomenas(arodes_int_num,subarea)
            self.insert_soil_specs(arodes_int_num,subarea.arod_soil_specs)
            self.insert_spec_areas(arodes_int_num,subarea)
            self.insert_tree_layers(arodes_int_num,subarea.tree_layers)
            self.insert_parcels(arodes_int_num, subarea.subarea_parcels,dictionary_parcels,parcel_land_use_dictionary)
            self.insert_protect_areas(arodes_int_num,subarea.protect_areas)
        except Exception as e:
            raise e
    
    def insert_protect_areas(self,arodes_int_num,protect_areas):
        """Wstawia obszary chronione do tabeli TEMP_ARODES_SET"""
        try:
            params=[(self.convert_numeric(arodes_int_num),
                     pa.protect_area_cd,
                     pa.protect_area_name) 
                     for pa in protect_areas]
            query="INSERT INTO TEMP_ARODES_SET(ARODES_INT_NUM, PROTEC_AREA_CD, LAND_PROTECT_NAME) VALUES (?, ?, ?)"
            self.__database.run_query_many(query,params)
        except Exception as e:
            raise e
    
    def insert_parcels(self,arodes_int_num, subarea_parcels,dictionary_parcel:{},parcel_land_use_dictionary:[]):
        """Metoda wstawiająca dane o działkach wydzielenia do bazy TPU, do tabel F_PARCEL, F_PARCEL_LAND_USE, F_AROD_LAND_USE i w razie potrzeby F_AREA_USE_DIC
        :param arodes_int_num; adres
        :param subarea_parcels; lista działek
        """
        try:
            for parcel in subarea_parcels:
                #Utworzenie kluczas słownika
                
                key="{}_{}_{}_{}_{}".format(parcel.parcel_nr,parcel.county_cd,parcel.district_cd,parcel.municipality_cd,parcel.community_cd)
                if key not in dictionary_parcel:
                    query="INSERT INTO F_PARCEL(PARCEL_NR, COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, OWNERSHIP_CD, PARCEL_AREA, REG_SHEET_NR2) VALUES ({}, {}, {}, {}, {}, {}, {}, {})".format(
                           self.convert(parcel.parcel_nr),self.convert(parcel.county_cd),self.convert(parcel.district_cd),
                           self.convert(parcel.municipality_cd),self.convert(parcel.community_cd),self.convert(parcel.ownership_cd),
                           self.convert_numeric(parcel.parcel_area),self.convert(parcel.regSheetNr2))
                    parcel_int_num=self.__database.insert_data_identity(query)
                    #Dodanie parcel_int_num do słownika
                    dictionary_parcel.update({key:parcel_int_num})
                
                    #Dodanie danych do warstwy działek
                    if parcel.geometry is not None and self.__layers_path is not None:
                        self.__geometry_helper.set_feature(TagsLayer.SUBAREA_PARCEL.value,parcel.geometry,
                                                           ((TagsLayer.ADMINISTRATIVE_ADDRESS.value,"{}-{}-{}-{}".format(parcel.county_cd,parcel.district_cd,parcel.municipality_cd,parcel.community_cd)),
                                                                                                                                    (TagsLayer.PARCEL_NR.value,parcel.parcel_nr),))
#                         self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.SUBAREA_PARCEL.value),parcel.geometry,(
#                                                                                                                                     (TagsLayer.ADMINISTRATIVE_ADDRESS.value,"{}-{}-{}-{}".format(parcel.county_cd,parcel.district_cd,parcel.municipality_cd,parcel.community_cd)),
#                                                                                                                                    (TagsLayer.PARCEL_NR.value,parcel.parcel_nr),))
                    self.insert_parcel_land_use(parcel_land_use_dictionary,parcel.parcels_land_use,arodes_int_num ,parcel_int_num,parcel)
                else:
                    parcel_int_num=dictionary_parcel.get(key)
                    self.insert_parcel_land_use(parcel_land_use_dictionary,parcel.parcels_land_use,arodes_int_num ,parcel_int_num,parcel)
                            
        except Exception as e:
            raise e
    
    
    def insert_parcel_land_use(self,parcel_land_use_dictionary:[],parcels_land_use,arodes_int_num,parcel_int_num,parcel):
        """Wstawia dane do tabeli F_PARCEL_LAND_USE, F_AROD_LAND_USE, TEMP_ARODES_CUE"""
        try:
            #wstawienie parcel land use
            for parcel_land in parcels_land_use:
                parcel_land_use_key="{}_{}".format(parcel_int_num,parcel_land.shape_nr)
                if parcel_land_use_key not in parcel_land_use_dictionary:
                    parcel_land_use_dictionary.append(parcel_land_use_key)
                    #aktualizacja tabeli F_AREA_USE_DIC 
                    self.update_area_use_dic(parcel_land.area_use_dic)
                    #wpisanie w tabelę F_PARCEL_LAND_USE
                    query="INSERT INTO F_PARCEL_LAND_USE(PARCEL_INT_NUM, AREA_USE_CD, SOIL_QUALITY_CD, LAND_USE_AREA, SHAPE_NR) VALUES ({}, {}, {}, {}, {})".format(
                        parcel_int_num,self.convert(parcel_land.area_use_dic),self.convert(parcel_land.soil_quality_dic),
                        self.convert_numeric(parcel_land.land_use_area),parcel_land.shape_nr)
                    self.__database.run_queries([query])
                                                
                    #Dodanie danych do warstwy użytków
                    if parcel_land.geometry is not None and self.__layers_path is not None:
                        self.__geometry_helper.set_feature(TagsLayer.PARCEL_LAND_USE.value,parcel_land.geometry,
                                                           ((TagsLayer.ADMINISTRATIVE_ADDRESS.value,"{}-{}-{}-{}".format(parcel.county_cd,parcel.district_cd,parcel.municipality_cd,parcel.community_cd)),
                                                                                                                                 (TagsLayer.PARCEL_NR.value,parcel.parcel_nr),
                                                                                                                                 (TagsLayer.AREA_USE_DIC_CONT_NUM.value,parcel_land.shape_nr),))
#                         self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.PARCEL_LAND_USE.value),parcel_land.geometry,(
#                                                                                                                             (TagsLayer.ADMINISTRATIVE_ADDRESS.value,"{}-{}-{}-{}".format(parcel.county_cd,parcel.district_cd,parcel.municipality_cd,parcel.community_cd)),
#                                                                                                                                 (TagsLayer.PARCEL_NR.value,parcel.parcel_nr),
#                                                                                                                                 (TagsLayer.AREA_USE_DIC_CONT_NUM.value,parcel_land.shape_nr),))
                        
                #Wpisanie w tabelę F_AROD_LAND_USE
                query="INSERT INTO F_AROD_LAND_USE(ARODES_INT_NUM, PARCEL_INT_NUM, SHAPE_NR, AROD_LAND_USE_AREA) VALUES ({}, {}, {}, {})".format(
                    arodes_int_num,parcel_int_num,parcel_land.shape_nr,self.convert_numeric(parcel_land.arod_land_use_area))
                self.__database.run_queries([query])        
                
                #Wypełnienie tabeli TEMP_ARODES_CUE
                if len(parcel_land.land_use_measures)>0:
                    params=[(self.convert_numeric_null(arodes_int_num),
                        self.convert_numeric_null(parcel_int_num),
                        self.convert_numeric_null(parcel_land.shape_nr),
                        self.convert_numeric_null(land_use_measure.cue_rank_order),
                        land_use_measure.measure_cd,
                        self.convert_numeric_null(land_use_measure.large_timber),
                        self.convert_numeric_null(land_use_measure.large_timber_netto),
                        self.convert_numeric_null(land_use_measure.cue_area_div)) 
                        for land_use_measure in parcel_land.land_use_measures]
                    query="INSERT INTO TEMP_ARODES_CUE(ARODES_INT_NUM, PARCEL_INT_NUM, SHAPE_NR, CUE_RANK_ORDER, MEASURE_CD, LARGE_TIMBER_VALUE, LARGE_TIMBER_VALUE_NET, CUE_AREA_DIV) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
                    self.__database.run_query_many(query,params)
        except Exception as e:
            raise e
                    
    def update_area_use_dic(self,area_use_dic):
        """Aktualizacja tabeli F_AREA_USE_DIC
        :param area_use_dic; kod słownika
        """
        try:
            max_nr=self.__database.get_data('SELECT MAX(AREA_USE_NR) FROM F_AREA_USE_DIC')
            max_nr=max_nr[0][0]+1
            #sprawdzenie czy w słowniku już istnieje taki kod
            code=self.__database.get_data("SELECT F_AREA_USE_DIC.AREA_USE_CD FROM F_AREA_USE_DIC WHERE (((F_AREA_USE_DIC.AREA_USE_CD)='{}'))".format(area_use_dic))
            #jeżelie nie ma takiego kodu to dodajemy do tabeli
            if len(code)==0:
                query="INSERT INTO F_AREA_USE_DIC(AREA_USE_CD, AREA_USE_NR) VALUES ('{}', {})".format(area_use_dic,max_nr)
                self.__database.run_queries([query])
        except Exception as e:
            raise e
    
    def insert_tree_layers(self,arodes_int_num,tree_layers):
        """Metoda wstawiająca dane o warstwach i gatunkach w wydzieleniu do bazy TPU, do tabel F_AROD_STOREY i F_STOREY_SPECIES
        :param arodes_int_num; adres leśny
        :param tree_layers; warstwy
        """
        try:
            for tree in tree_layers:
                query="INSERT INTO F_AROD_STOREY(ARODES_INT_NUM, STOREY_CD, STANDDENSITY_INDEX, MIXTURE_CD, DENSITY_CD, STOREY_RANK_ORDER) VALUES ({}, {}, {}, {}, {}, {})".format(
                    arodes_int_num,self.convert(tree.storey_cd),self.convert_numeric(tree.stand_density_index),
                    self.convert(tree.mixture_cd),self.convert(tree.density_cd),self.convert_numeric(tree.storey_rank_order))
                #TODO brak danych self.location_cd=location_cd, self.tree_stock_cd=tree_stock_cd self.silv_quality_cd=silv_quality_cd
                self.__database.run_queries([query])
                #dodanie gatunków
                for species in tree.layer_species:
                    query="INSERT INTO F_STOREY_SPECIES(ARODES_INT_NUM, STOREY_CD, SPECIES_CD, VOLUME, PART_CD, BHD, HEIGHT, SITE_CLASS_CD, SPECIES_AGE, SPECIES_RANK_ORDER, INCREMENT_CURRENT) VALUES ({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {})".format(
                        arodes_int_num,self.convert(tree.storey_cd),self.convert(species.species_cd),self.convert_numeric(species.volume_ha),
                        self.convert(species.part_cd),self.convert_numeric(species.dbh),self.convert_numeric(species.height),
                        self.convert(species.site_class_cd),self.convert_numeric(species.species_age),self.convert(species.species_rank_order),
                        self.convert_numeric(species.increment_ha))
                    self.__database.run_queries([query])
        except Exception as e:
            raise e
    
    def insert_spec_areas(self,arodes_int_num,subarea):
        """Metoda wstawiająca dane o pnsw w wydzieleniu do bazy TPU, do tabel F_AROD_SPEC_AREA i F_SPECIES_SPAREA oraz do warstwy pnsw
        :param arodes_int_num; adres
        :param subarea; wydielenia
        """
        try:
                        
            sparea_order=1
            for spec_area in subarea.arod_spec_areas:
                query="INSERT INTO F_AROD_SPEC_AREA(ARODES_INT_NUM, AROD_SPAREA_ORDER, SPECIAL_AREA_CD, LOCATION_CD, SPECIAL_AREA, SPECIAL_AREA_NUM, SPECIAL_AREA_YEAR) VALUES({}, {}, {}, {}, {}, {}, {})".format(
                    arodes_int_num,sparea_order,
                    self.convert(spec_area.special_area_cd),self.convert(spec_area.location_cd),self.convert_numeric(spec_area.special_area),
                    self.convert_numeric(spec_area.special_area_num),self.convert_numeric(spec_area.special_area_year))
                
                self.__database.run_queries([query])
                #wstawienie geometrii, gatunku
                if spec_area.geometry is not None and len(spec_area.geometry)>0:
                    for geo in spec_area.geometry:
                        self.__geometry_helper.set_feature(TagsLayer.PNSW.value,geo,((TagsLayer.ADDRESS.value,subarea.address_forest),(TagsLayer.NR_PNSW.value,sparea_order),(TagsLayer.CODE_PNSW.value,spec_area.special_area_cd),(TagsLayer.PARCEL_NR.value,spec_area.parcel_nr),(TagsLayer.ADMINISTRATIVE_ADDRESS.value,self.administrative_address(subarea.address_forest)),))
#                         self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.PNSW.value),geo,((TagsLayer.ADDRESS.value,subarea.address_forest),(TagsLayer.NR_PNSW.value,sparea_order),(TagsLayer.CODE_PNSW.value,spec_area.special_area_cd),(TagsLayer.PARCEL_NR.value,spec_area.parcel_nr),(TagsLayer.ADMINISTRATIVE_ADDRESS.value,self.administrative_address(subarea.address_forest)),))
                #wstawienie gatunków na pnsw
                rank_order=1
                for ssa in spec_area.species_spec_areas:
                    query="INSERT INTO F_SPECIES_SPAREA(ARODES_INT_NUM, AROD_SPAREA_ORDER, SPECIES_CD, SP_AGE, SP_RANK_ORDER) VALUES ({}, {}, {}, {}, {})".format(
                        arodes_int_num,
                        sparea_order,
                        self.convert(ssa.species_cd),
                        self.convert_numeric(ssa.species_age),
                        rank_order)
                    self.__database.run_queries([query])
                    rank_order+=1
                sparea_order+=1
        except Exception as e:
            raise e
    
    def insert_soil_specs(self,arodes_int_num,arod_soil_specs):
        """Metoda wstawiająca dane o glebach w wydzieleniu do bazy TPU, do tabeli F_AROD_SOIL_SPEC
        :param arodes_int_num; adres leśny
        :parm arod_soil_specs; lista gleb
        """
        try:
            queries=["INSERT INTO F_AROD_SOIL_SPEC(ARODES_INT_NUM, SOIL_SPECIES_CD, SOIL_LEVEL_CD, SOIL_RANK_ORDER) VALUES({}, {},{}, {})".format(
                arodes_int_num,
                self.convert(ss.soil_species_cd),
                self.convert(ss.soil_level_dic_cd),
                self.convert_numeric(ss.soil_rank_order)
                ) for ss in arod_soil_specs]
            self.__database.run_queries(queries)
        except Exception as e:
            raise e
            

    def insert_phenomenas(self,arodes_int_num,subarea):
        """Metoda wstawiająca dane o osobliwościach w wydzieleniu do bazy TPU, do tabeli F_AROD_PHENOMENA oraz do warstwy poligonowej lub punktowej osobliwości
        :param arodes_int_num; klucz
        :param arod_phenomenas; osobliwości
        """
        try:
            queries=[]
            rank_order=1
            for ph in subarea.arod_phenomenas:
                query="INSERT INTO F_AROD_PHENOMENA(ARODES_INT_NUM, PHENOMENA_CD, LOCATION_CD, NATURE_MON_FL, PHEN_AREA, PHEN_NUM, PLANT_CD, SPECIES_CD, PHEN_RANK_ORDER) VALUES({}, {}, {}, {}, {}, {}, {}, {}, {})".format(
                    arodes_int_num,self.convert(ph.phenomena_cd),self.convert(ph.location_cd),self.convert(ph.nature_mon_fl),
                    self.convert_numeric(ph.phen_area),self.convert_numeric(ph.phen_num),self.convert(ph.plant_cd),self.convert(ph.species_cd),rank_order)
                self.__database.run_queries([query])
                #wstawienie geometrii
                if ph.geometry is not None:
                    self.insert_phenomena_geometry(ph,rank_order,subarea.address_forest)
                rank_order+=1
        except Exception as e:
            raise e
                 
    def insert_phenomena_geometry(self,ph,rank_order,address_forest):
        """Wstawia geometrię do warstw osobliwosci
        :param gemetry; geometria
        :param rank_order; kolejnosc sortowania
        :param address_forest; adres leśny
        """
        try:
            if 'point' in ph.geometry.decode("utf-8").lower():
                #obiekt punktowy
                self.__geometry_helper.set_feature(TagsLayer.POINT_SINGULARITIES.value,ph.geometry,((TagsLayer.ADDRESS.value,address_forest),(TagsLayer.NR_SINGULARITY.value,rank_order),(TagsLayer.SINGULARITY_CODE.value,ph.phenomena_cd),))
                #self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.POINT_SINGULARITIES.value),ph.geometry,((TagsLayer.ADDRESS.value,address_forest),(TagsLayer.NR_SINGULARITY.value,rank_order),(TagsLayer.SINGULARITY_CODE.value,ph.phenomena_cd),))
            else:
                #obiekt poligonowy
                self.__geometry_helper.set_feature(TagsLayer.SURFACE_SINGULARITIES.value,ph.geometry,((TagsLayer.ADDRESS.value,address_forest),(TagsLayer.NR_SINGULARITY.value,rank_order),(TagsLayer.SINGULARITY_CODE.value,ph.phenomena_cd),(TagsLayer.SINGULARITY_AREA.value,ph.phen_area),))
                #self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.SURFACE_SINGULARITIES.value),ph.geometry,((TagsLayer.ADDRESS.value,address_forest),(TagsLayer.NR_SINGULARITY.value,rank_order),(TagsLayer.SINGULARITY_CODE.value,ph.phenomena_cd),(TagsLayer.SINGULARITY_AREA.value,ph.phen_area),))
        except Exception as e:
            raise e

    def insert_goals(self,arodes_int_num,goals):
        """Metoda wstawiająca dane o GTD w wydzieleniu do bazy TPU, do tabeli F_AROD_GOAL
        :param arodes_int_num; klucz
        :param goals; gospodarcze typy drzewostanu
        """
        try:
            queries=["INSERT INTO F_AROD_GOAL(ARODES_INT_NUM, GOAL_TYPE_FL, SPECIES_CD, GOAL_SPECIES_PERC, GOAL_RANK_ORDER) VALUES ({}, {}, {}, {}, {})".format(
                arodes_int_num,self.convert(goal.goal_type_fl),self.convert(goal.species_cd),
                self.convert_numeric(goal.goal_species_perc),self.convert_numeric(goal.goal_rank_order)
                ) for goal in goals]
            self.__database.run_queries(queries)
        except Exception as e:
            raise e

    def insert_cues(self,arodes_int_num,arod_cues):
        """Metoda wstawiająca dane o wskazówkach w wydzieleniu do bazy TPU, do tabeli F_AROD_CUE
        :param arodes_int_num; klucz
        :param arod_cues; lista wskazówek
        """
        try:
            queries=["INSERT INTO F_AROD_CUE(ARODES_INT_NUM, SITE_NR, MEASURE_CD, URGENCY, CUTTING_AREA, LARGE_TIMBER_PERC,LARGE_TIMBER_VALUE,LARGE_TIMBER_VALUE_NET,CUE_RANK_ORDER) VALUES ({}, {}, {}, {}, {}, {}, {}, {}, {})".format(
                    arodes_int_num,self.convert_numeric(ac.site_nr),self.convert(ac.measure_cd),self.convert(ac.urgency),
                    self.convert_numeric(ac.cutting_area),self.convert_numeric(ac.large_timber_perc), self.convert_numeric(ac.large_timber), self.convert_numeric(ac.large_timber_netto), self.convert_numeric(ac.cue_rank_order) ) 
                for ac in arod_cues]
            self.__database.run_queries(queries)
        except Exception as e:
            raise e

    def insert_categories(self,arodes_int_num,arod_categories):
        """Metoda wstawiająca dane o kategoriach ochronności w wydzieleniu do bazy TPU, do tabeli F_AROD_CATEGORY
        :param arodes_int_num; klucz
        :param arod_categories; kategorie
        """
        try:
            queries=["INSERT INTO F_AROD_CATEGORY(ARODES_INT_NUM, PROT_CATEGORY_CD, PROT_RANK_ORDER) VALUES ({}, {}, {})".format(
                arodes_int_num,self.convert(ac.category_cd),self.convert_numeric(ac.category_rank_order)) for ac in arod_categories]
            self.__database.run_queries(queries)
        except Exception as e:
            raise e

    def insert_stand_pecs(self,arodes_int_num,arod_stand_pecs):
        """Metoda wstawiająca dane o cechach drzewostanu w wydzieleniu do bazy TPU, do tabeli F_AROD_STAND_PEC
        :param arodes_int_num; klucz 
        :param arod_stand_pecs; dane 
        """
        try:
            pec_rank_order=1
            queries=[]
            for stand_pec in arod_stand_pecs:
                query="INSERT INTO F_AROD_STAND_PEC(ARODES_INT_NUM, FOREST_PEC_CD, PEC_RANK_ORDER) VALUES ({}, {}, {})".format(arodes_int_num,self.convert(stand_pec.forest_pec_cd),pec_rank_order) 
                pec_rank_order+=1
                queries.append(query)
            self.__database.run_queries(queries)
        except Exception as e:
            raise e

    def insert_subarea_data(self,subarea,branch_list:[]):
        """Metoda wstawiająca dane wydzielenia do bazy TPU, do tabel F_ARODES, F_COMPARTMENT, F_SUBAREA
        :param subarea; obiekt subarea
        """
        try:
            #Województwa z bazy
            counties=self.__database.get_data('SELECT F_COUNTY.COUNTY_CD, F_COUNTY.COUNTY_LTR FROM F_COUNTY;')
            order_key=order_key_generator.build_order_key(subarea.address_forest,counties)
            query="INSERT INTO F_ARODES(ADRESS_FOREST, ARODES_TYP_CD, ORDER_KEY, ADRESS_VALID) VALUES ('{}', 'WYDZIEL', '{}', 'T')".format(subarea.address_forest,order_key)
            #1. wstawiamy wiersz wydzielenia w F_ARODES i dostajemy klucz arodes_int_num
            arodes_int_num=self.__database.insert_data_identity(query) 
            #2. wstawiamy oddział do F_ARODES
            adres_forest_branch=self.address_branch(subarea.address_forest)
            order_key_branch=order_key_generator.build_order_key(adres_forest_branch,counties)
            query="INSERT INTO F_ARODES(ADRESS_FOREST, ARODES_TYP_CD, ORDER_KEY, ADRESS_VALID) VALUES ('{}', 'ODDZ', '{}', 'T')".format(adres_forest_branch,order_key_branch)
            if adres_forest_branch not in branch_list:
                branch_list.append(adres_forest_branch)    
                self.__database.insert_data_identity(query)
            #wstawienie wiersza do F_COMPARTMENT
            if subarea.altitude_max is not None or subarea.altitude_min is not None:
                query="INSERT INTO F_COMPARTMENT(ARODES_INT_NUM, COMPARTMENT_CD, ALTITUDE_MAX, ALTITUDE_MIN) VALUES ({}, '{}', {}, {})".format(arodes_int_num,subarea.address_forest[13:4],self.convert(subarea.altitude_max),self.convert(subarea.altitude_min))
                self.__database.run_queries([query])
            #wstawienie wiersza w F_SUBAREA
            query="INSERT INTO F_SUBAREA (ARODES_INT_NUM, DAMAGE_DEGREE_CD, CAUSE_CD, AREA_TYPE_CD, POSITION_CD, RELIEF_CD, SITE_TYPE_CD, DEGRADATION_CD, VEG_COVER_CD, FOREST_FUNC_CD, STAND_STRUCT_CD, SLOPE_CD, EXPOSURE_CD, MOISTURE_CD, SOIL_PEC_CD, SOIL_SUBTYPE_CD, PLANT_COMM_CD, ROTATION_AGE, SUB_AREA, SUBAREA_INFO) VALUES ({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {})".format(
                arodes_int_num,self.convert(subarea.damage_degree),self.convert(subarea.cause_cd),self.convert(subarea.area_type_cd),
                self.convert(subarea.position_cd),self.convert(subarea.relief_cd),self.convert(subarea.site_type_cd),self.convert(subarea.degradation_cd),
                self.convert(subarea.veg_cover_cd),self.convert(subarea.forest_func_cd),self.convert(subarea.stand_struct_cd),self.convert(subarea.slope_cd),
                self.convert(subarea.exposure_cd),self.convert(subarea.moisture_cd),self.convert(subarea.soil_pec_cd),self.convert(subarea.soil_sub_type_cd),
                self.convert(subarea.plant_comm_cd),self.convert_numeric(subarea.rotation_age),self.convert_numeric(subarea.subarea),self.convert(subarea.subarea_info))
            self.__database.run_queries([query])
            #Wstawienie geometrii
            if subarea.geometry is not None and self.__layers_path is not None:
                self.__geometry_helper.set_feature(TagsLayer.SURFACE.value,subarea.geometry,((TagsLayer.ADDRESS.value,subarea.address_forest),))
                #self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.SURFACE.value),subarea.geometry,((TagsLayer.ADDRESS.value,subarea.address_forest),))
            return arodes_int_num            
            
        except Exception as e:
            raise e

    def insert_communities(self,communities):
        """Wstawia obreby do bazy i etaty
        :param communities; lista obiektów obrębów
        """
        try:
            params=[(self.convert_numeric(com.county_cd),
                     self.convert_numeric(com.district_cd),
                     self.convert_numeric(com.municipality_cd),
                     self.convert_numeric(com.community_cd),
                     self.convert_numeric(com.community_name), 
                     )
                     for com in communities]
            query="INSERT INTO F_COMMUNITY(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, COMMUNITY_NAME) VALUES (?, ?, ?, ?, ?)"
            self.__database.run_query_many(query,params)
            #Wstawienie etatów
            params=[(self.convert_numeric(com.county_cd),
                     self.convert_numeric(com.district_cd),
                     self.convert_numeric(com.municipality_cd),
                     self.convert_numeric(com.community_cd),
                     self.convert_numeric(com.community_name), 
                     self.convert_numeric_null(com.allow_cut_l_c_a),
                     self.convert_numeric_null(com.allow_cut_p_c_a),
                     self.convert_numeric_null(com.allow_cut_c_n),
                     self.convert_numeric_null(com.allow_cut_adopted),
                     self.convert_numeric_null(com.allow_cut_other_cut),
                     self.convert_numeric_null(com.allow_cut_max_vol)
                     )
                     for com in communities]
            query="INSERT INTO F_ETAT(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, COMMUNITY_NAME, ETAT_OKW, ETAT_POKW, ETAT_WSK, ETAT_P, ETAT_POZ, ETAT_MAX) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            self.__database.run_query_many(query,params)
#             queries=["INSERT INTO F_COMMUNITY(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, COMMUNITY_NAME) VALUES ({}, {}, {}, {}, {})".format(self.convert(com.county_cd),self.convert(com.district_cd),self.convert(com.municipality_cd),self.convert(com.community_cd),self.convert(com.community_name))
#                      for com in communities]
#             for q in queries:
#                 print(q)
#                 self.__database.run_queries(q)
        except Exception as e:
            raise e
        
    def update_municipalities(self,municipalities):
        """Aktualizacja bazy o ogminy"""
        try:
            #pobranie gmin z bazy
            data=self.__database.get_data('SELECT F_MUNICIPALITY.COUNTY_CD, F_MUNICIPALITY.DISTRICT_CD, F_MUNICIPALITY.MUNICIPALITY_CD FROM F_MUNICIPALITY;')
            municipalities_orginal=[Municipality(county,district,municipality,None) for county,district,municipality in data]
            #porównanie z danymi z sdl-a
            difference=[]
            for m in municipalities:
                value=True
                for mo in municipalities_orginal:
                    if mo.county_cd==m.county_cd and mo.district_cd==m.district_cd and mo.municipality_cd==m.municipality_cd:
                        value=False
                #value=next(mo for mo in municipalities_orginal if mo.county_cd==m.county_cd and mo.district_cd==m.district_cd and mo.municipality_cd==m.municipality_cd)
                if value==True:
                    difference.append(m)
#             difference=[m for m in municipalities if m.county_cd not in [cd.county_cd for cd in municipalities_orginal] 
#                        and m.district_cd not in [cd.district_cd for cd in municipalities_orginal]
#                        and m.municipality not in [cd.municipality for cd in municipalities_orginal]]
            params=[(m.county_cd,m.district_cd,m.municipality_cd,m.municipality_name) for m in difference]
            #Aktualizacja tabeli F_MUNICIPALITY
            query='INSERT INTO F_MUNICIPALITY(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, MUNICIPALITY_NAME) VALUES (?, ?, ?, ?)'
            self.__database.run_query_many(query,params)
        except Exception as e:
            raise e
        
    def update_measures(self,measures):
        """Aktualizuje słownik wskazówek o nowe rekordy
        :param measures; słownik wskazówek
        """
        try:
            data=self.__database.get_data('SELECT F_MEASURE.measure_cd, F_MEASURE.measure_nr, F_MEASURE.measure_name, F_MEASURE.cost_centre_cd FROM F_MEASURE;')
            measures_original=[Measure(measure_cd, measure_nr, measure_name) for measure_cd, measure_nr, measure_name, _ in data]
            difference=[m for m in measures if m.measure_cd.strip() not in [cd.measure_cd.strip() for cd in measures_original]]
            params=[(self.convert_numeric(m.measure_cd),
                     self.convert_numeric(m.measure_nr),
                     self.convert_space(m.measure_name)
                     ) 
                     for m in difference]
            query="INSERT INTO F_MEASURE(MEASURE_CD, MEASURE_NR, MEASURE_NAME) VALUES (?, ?, ?)"
            self.__database.run_query_many(query,params)
        except Exception as e:
            raise e
        
    def insert_inventory_data(self,forest_data_desc):
        """wstawia dane inwentaryzacyjne
        :param forest_data_desc; dane inwentaryzacyjne
        """
        try:
            query="INSERT INTO F_PARAMETER (ObjectFullName, TAXATION_YEAR, S_OrgName, S_Street, S_City, S_Code, S_Country, S_Phone, S_Fax, S_Email, DS_OrgName, DS_Street, DS_City, DS_Code, DS_Country, DS_Phone, DS_Fax, DS_Email) VALUES ({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {})".format(
                self.convert(forest_data_desc.inventory_data.inventory_object_name),
                forest_data_desc.inventory_data.invent_year,
                self.convert(forest_data_desc.inventory_data.supervisor.organization_name),
                self.convert(forest_data_desc.inventory_data.supervisor.street),
                self.convert(forest_data_desc.inventory_data.supervisor.city),
                self.convert(forest_data_desc.inventory_data.supervisor.postal_code),
                self.convert(forest_data_desc.inventory_data.supervisor.country),
                self.convert(forest_data_desc.inventory_data.supervisor.phone),
                self.convert(forest_data_desc.inventory_data.supervisor.fax),
                self.convert(forest_data_desc.inventory_data.supervisor.email),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.organization_name),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.street),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.city),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.postal_code),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.country),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.phone),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.fax),
                self.convert(forest_data_desc.inventory_data.delegate_supervisor.email)
                )
                
            self.__database.run_queries([query])
        except Exception as e:
            raise e

    def insert_forest_natural_values(self,forest_natural_values:[]):
        """"Wstawia walory przyrodnicze
        :param forest_natural_values; walory przyrodnicze
        """
        try:
            query="INSERT INTO F_ENV_PROTECT(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, PROTEC_AREA_CD, ENV_COUNT, TOTAL_AREA, PUL_AREA, FOREST_AREA, NFOREST_AREA, FOREST_AREA_P, NFOREST_AREA_P) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            params=[(nv.county,
                    nv.district,
                    nv.municipality,
                    nv.community,
                    nv.natural_value_cd,
                    self.convert_numeric_null(nv.env_count),
                    self.convert_numeric_null(nv.total_area),
                    self.convert_numeric_null(nv.pul_area),
                    self.convert_numeric_null(nv.forest_area),
                    self.convert_numeric_null(nv.nforest_area),
                    self.convert_numeric_null(nv.forest_area_p),
                    self.convert_numeric_null(nv.nforest_area_p)
                    ) for nv in forest_natural_values]
#             for p in params:
#                 q="INSERT INTO F_ENV_PROTECT(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, PROTEC_AREA_CD, ENV_COUNT, TOTAL_AREA, PUL_AREA, FOREST_AREA, NFOREST_AREA, FOREST_AREA_P, NFOREST_AREA_P) VALUES {}".format(p)
#                 print(q)
#                 self.__database.run_queries(q)
            self.__database.run_query_many(query,params)
        except Exception as e:
            raise e
       
#     def convert_empty(self,value):
#         try:
#             if value==None:
#                 return ''
#             return "{}".format(value)
#         except Exception as e:
#             raise e
    def convert_numeric_null(self,value):
        """Konwertuje wartość do int, double lub NULL"""
        try:
            if value==None:
                return None
            ret="{}".format(value)
            ret=ret.replace('.',',',)
            return ret
        except Exception as e:
            raise e
    
    def convert_space(self,value):
        try:
            if value==None:
                return ' '
            return "{}".format(value)
        except Exception as e:
            raise e
        
    def convert_numeric(self,value):
        """Konwertuje wartośc na double,int itd lub null
        :param value; wartośc
        """
        try:
            if value==None:
                return 'NULL'
            return "{}".format(value)
        except Exception as e:
            raise e

    def convert(self,value):
        """Konwertuje wartośc na string lub null
        :parm null; wartośc
        """
        try:
            if value==None:
                return 'NULL'
            return "'{}'".format(value.strip())
        except Exception as e:
            raise e
        
    def administrative_address(self,value):
        """Zwraca adres administracyjny
        :param value;Adres
        """
        try:
            sql='SELECT F_COUNTY.COUNTY_CD, F_COUNTY.COUNTY_LTR FROM F_COUNTY;'
            codes=self.__database.get_data(sql)
            county=''
            for code,ltr in codes:
                if ltr==value[0:1]:
                    county=code
            #county=[code in code, ltr in codes if ltr==value[0:1]][0]
            return county+'-'+value[1:3]+'-'+value[3:6]+'-'+value[6:10]
        except Exception as e:
            raise e
    
    def insert_forest_map_no_sf(self,forest_map_no_sf:ForestMapNoSF):
        """Wstawia dane do warstw shp"""
        try:
            #Wstawienie danych do warstwy oddziałów countyLayer
            if forest_map_no_sf.county_layer is not None and self.__layers_path is not None:
                for county in forest_map_no_sf.county_layer:
                    #1. Wstawienie geometrii oddziałów
                    self.__geometry_helper.set_feature(TagsLayer.COUNTY.value,county.geometry,((TagsLayer.ADDRESS.value,county.address_forest),)) 
                    #self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.COUNTY.value),county.geometry,((TagsLayer.ADDRESS.value,county.address_forest),))
                    
            #Wstawienie danych warstwy obiektów podstawowych
            if forest_map_no_sf.basic_objects_layer is not None and self.__layers_path is not None:
                #Pobranie adresów administracyjnych
                sql='SELECT F_COUNTY.COUNTY_CD, F_COUNTY.COUNTY_LTR FROM F_COUNTY'
                data=self.__database.get_data(sql)
                for bo in forest_map_no_sf.basic_objects_layer:
                    county_number=[COUNTY_CD for COUNTY_CD, COUNTY_LTR in data if bo.address_forest[0:1]==COUNTY_LTR]
                    number='99'
                    if len(county_number)>0:
                        number=county_number[0]
                    self.__geometry_helper.set_feature(TagsLayer.BASIC_OBJECTS.value,bo.geometry,((TagsLayer.ADDRESS.value,bo.address_forest),
                                                                                                                                      (TagsLayer.ADMINISTRATIVE_ADDRESS.value,"{}-{}-{}-{}".format(
                                                                                                                                          number,bo.address_forest[1:3],bo.address_forest[3:6],bo.address_forest[6:10])),
                                                                                                                             (TagsLayer.PARCEL_NR.value,bo.parcel_nr),
                                                                                                                             (TagsLayer.AREA_USE_DIC_CONT_NUM.value,bo.area_use_dic_count_num),))
#                    self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.BASIC_OBJECTS.value),bo.geometry,((TagsLayer.ADDRESS.value,bo.address_forest),
#                                                                                                                                      (TagsLayer.ADMINISTRATIVE_ADDRESS.value,"{}-{}-{}-{}".format(
#                                                                                                                                          number,bo.address_forest[1:3],bo.address_forest[3:6],bo.address_forest[6:10])),
#                                                                                                                             (TagsLayer.PARCEL_NR.value,bo.parcel_nr),
#                                                                                                                             (TagsLayer.AREA_USE_DIC_CONT_NUM.value,bo.area_use_dic_count_num),))
            #Wstawienie danych warstwy opisów pododdziałów
            if forest_map_no_sf.subarea_labels_layer is not None and self.__layers_path is not None:
                for sll in forest_map_no_sf.subarea_labels_layer:
                    self.__geometry_helper.set_feature(TagsLayer.SUBAREA_LABELS.value,sll.geometry,((TagsLayer.ADDRESS.value,sll.address_forest),))
#                     self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.SUBAREA_LABELS.value),sll.geometry,((TagsLayer.ADDRESS.value,sll.address_forest),))
            #Wstawienie danych do warstwy opisów oddziałów
            if forest_map_no_sf.county_labels_layer is not None and self.__layers_path is not None:
                for cll in forest_map_no_sf.county_labels_layer:
                    self.__geometry_helper.set_feature(TagsLayer.COUNTY_LABELS.value,cll.geometry,((TagsLayer.ADDRESS.value,cll.address_forest),))
#                     self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.COUNTY_LABELS.value),cll.geometry,((TagsLayer.ADDRESS.value,cll.address_forest),))
            #Wstawienie danych do warstwy Lini
            if forest_map_no_sf.line_layer is not None and self.__layers_path is not None:
                for line in forest_map_no_sf.line_layer:
                    self.__geometry_helper.set_feature(TagsLayer.LINES.value,line.geometry,((TagsLayer.TYPE_CD.value,line.type_cd),
                                                                                                                                (TagsLayer.WIDTH.value,line.width),))
#                     self.__geometry_helper.set_geometry(self.__layers_path+'\\{}'.format(TagsLayer.LINES.value),line.geometry,((TagsLayer.TYPE_CD.value,line.type_cd),
#                                                                                                                                (TagsLayer.WIDTH.value,line.width),))
            
        except Exception as e:
            raise e    
        
    def address_branch(self,address_forest:str):
        """Zwraca adres oddziału"""
        try:
            return address_forest[0:18]+'    -'
        except Exception as e:
            raise e