# -*- coding: utf-8 -*-
"""Metody tworzace obiekty swdl 
"""
from Mapa_PU.swdl.model.swdl.header import Header
from Mapa_PU.swdl.model.swdl.swdl import Swdl
from Mapa_PU.swdl.model.swdl.forest_data_desc import InventoryData
from Mapa_PU.swdl.model.swdl_exception import SwdlException
from Mapa_PU.model.baza_danych import Baza
from Mapa_PU.swdl.model.resources import Message
from Mapa_PU.swdl.model.swdl.subarea_data import SubareaParcel,ParcelLandUse,TreeLayer,TreeSpecies,Goal,Phenomena,SoilSpec,SpecArea,SpeciesSpecArea,SubareaData,ArodStandPec,Cue,ArodCategory,OwnershipShare,LandUseMeasure,ProtectArea
from Mapa_PU.swdl.model.swdl.forest_global_data import County,District,Municipality,Community,ForestGlobalData,Measure
from Mapa_PU.swdl.model.tags import TagsLayer


"""klasa pomocnicza tworzaca obiekt swdl z danych z bazy
"""
class SwdlHelper():
    def __init__(self,database_helper,geometry_helper,path,forest_data_desc_helper,forest_map_no_sf_helper,forest_natural_values_helper,root,swdl_version):
        self.__forest_data_desc_helper=forest_data_desc_helper
        self.__path=path
        self.__database_helper=database_helper
        self.__geometry_helper=geometry_helper
        self.__forest_map_no_sf_helper=forest_map_no_sf_helper
        self.__forest_natural_values_helper=forest_natural_values_helper
        self.__root=root
        self.__swdl=swdl_version
        
        
    
    def create_swdl(self):
        """Tworzy obiekt swdl SWDL do przetwarzania w drzewo XML
        :param header; naglowek
        :return obiekt swdl
        """
        try:
            swdl=Swdl()
            swdl.header=self.create_header(nazwa,adres,stanowisko,telefon,email,imie_nazwisko,data);
            return swdl
        except Exception as e:
            raise e  
             
    def create_forest_natural_values(self,address_forest:str)->[]:
        try:
            return self.__forest_natural_values_helper.create_forest_natural_values(address_forest)
        except Exception as e:
            raise e
               
    def create_forest_map_no_sf(self,address_forest):
        try:
            return self.__forest_map_no_sf_helper.create_forest_map_no_sf(address_forest)
        except Exception as e:
            raise e
               
    def create_forest_global_data(self,address_forest):
        try:
            #sql='SELECT C.COUNTY_CD, C.COUNTY_NAME, D.DISTRICT_CD, D.DISTRICT_NAME, M.MUNICIPALITY_CD, M.MUNICIPALITY_NAME, CO.COMMUNITY_CD, CO.COMMUNITY_NAME FROM F_COUNTY AS C, F_DISTRICT AS D, F_MUNICIPALITY AS M, F_COMMUNITY AS CO WHERE D.COUNTY_CD=C.COUNTY_CD AND M.COUNTY_CD=D.COUNTY_CD AND M.DISTRICT_CD=D.DISTRICT_CD AND CO.COUNTY_CD=M.COUNTY_CD AND CO.DISTRICT_CD=M.DISTRICT_CD AND CO.MUNICIPALITY_CD=M.MUNICIPALITY_CD'
            #normalizacja adresu do obrębu
            if len(address_forest)>10:
                address_forest=address_forest[0:10]
            sql="SELECT F_COUNTY.COUNTY_CD, F_COUNTY.COUNTY_NAME, F_DISTRICT.DISTRICT_CD, F_DISTRICT.DISTRICT_NAME, F_MUNICIPALITY.MUNICIPALITY_CD, F_MUNICIPALITY.MUNICIPALITY_NAME, F_COMMUNITY.COMMUNITY_CD, F_COMMUNITY.COMMUNITY_NAME, F_ETAT.ETAT_OKW, F_ETAT.ETAT_POKW, F_ETAT.ETAT_WSK, F_ETAT.ETAT_P, F_ETAT.ETAT_POZ, F_ETAT.ETAT_MAX FROM (((F_COUNTY INNER JOIN F_DISTRICT ON F_COUNTY.COUNTY_CD = F_DISTRICT.COUNTY_CD) INNER JOIN F_MUNICIPALITY ON (F_DISTRICT.DISTRICT_CD = F_MUNICIPALITY.DISTRICT_CD) AND (F_DISTRICT.COUNTY_CD = F_MUNICIPALITY.COUNTY_CD)) INNER JOIN F_COMMUNITY ON (F_MUNICIPALITY.MUNICIPALITY_CD = F_COMMUNITY.MUNICIPALITY_CD) AND (F_MUNICIPALITY.DISTRICT_CD = F_COMMUNITY.DISTRICT_CD) AND (F_MUNICIPALITY.COUNTY_CD = F_COMMUNITY.COUNTY_CD)) LEFT JOIN F_ETAT ON (F_COMMUNITY.COMMUNITY_NAME = F_ETAT.COMMUNITY_NAME) AND (F_COMMUNITY.COMMUNITY_CD = F_ETAT.COMMUNITY_CD) AND (F_COMMUNITY.MUNICIPALITY_CD = F_ETAT.MUNICIPALITY_CD) AND (F_COMMUNITY.DISTRICT_CD = F_ETAT.DISTRICT_CD) AND (F_COMMUNITY.COUNTY_CD = F_ETAT.COUNTY_CD) WHERE ((([F_COUNTY].[COUNTY_LTR] & [F_DISTRICT].[DISTRICT_CD] & [F_MUNICIPALITY].[MUNICIPALITY_CD] & [F_COMMUNITY].[COMMUNITY_CD]) Like '{}%'))".format(address_forest)
            self.__database_helper.open()
            data=self.__database_helper.get_data(sql)
            
            #wojewodztwa
            counties=[County(COUNTY_CD,COUNTY_NAME) for COUNTY_CD,COUNTY_NAME in set([(COUNTY_CD,COUNTY_NAME) for COUNTY_CD,COUNTY_NAME,*atr in data])]
            
            #powiaty
            districts=[District(COUNTY_CD, DISTRICT_CD, DISTRICT_NAME) for COUNTY_CD, DISTRICT_CD, DISTRICT_NAME in set([(COUNTY_CD, DISTRICT_CD, DISTRICT_NAME) for COUNTY_CD, _, DISTRICT_CD, DISTRICT_NAME,*atr in data])]
            
            #gminy
            municipalities=[Municipality(COUNTY_CD, DISTRICT_CD,MUNICIPALITY_CD, MUNICIPALITY_NAME) for COUNTY_CD,DISTRICT_CD, MUNICIPALITY_CD,MUNICIPALITY_NAME in set([(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, MUNICIPALITY_NAME) for COUNTY_CD, _, DISTRICT_CD, _,MUNICIPALITY_CD, MUNICIPALITY_NAME, *atr in data])]
            
            #obręby
            communities=[Community(COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, COMMUNITY_NAME, ETAT_OKW, ETAT_POKW, ETAT_WSK, ETAT_P, ETAT_POZ, ETAT_MAX) 
                         for COUNTY_CD, _,DISTRICT_CD, _, MUNICIPALITY_CD, _, COMMUNITY_CD, COMMUNITY_NAME, ETAT_OKW, ETAT_POKW, ETAT_WSK, ETAT_P, ETAT_POZ, ETAT_MAX in data]
            
            #słownik wskazówek
            sql='SELECT F_MEASURE.measure_cd, F_MEASURE.measure_nr, F_MEASURE.measure_name, F_MEASURE.cost_centre_cd FROM F_MEASURE;'
            data=self.__database_helper.get_data(sql)
            measures=[Measure(measure_cd,measure_nr,measure_name) for measure_cd, measure_nr, measure_name, _ in data]
           
            return ForestGlobalData(counties,districts,municipalities,communities,measures)
            
        except Exception as e:
            raise e
        finally:
            self.__database_helper.close()
        
    def create_forest_data_desc(self,address_forest,state_date):
        try:
            return self.__forest_data_desc_helper.create_forest_data_desc(address_forest,state_date)
        except Exception as e:
            raise e
           
    def create_header(self,unit_name,unit_address, position, name, phone_number, email):
        """Tworzenie obiektu naglowka
        :param unit_name; nazwa instytucji
        """
        try:
            return Header(unit_name,unit_address, position, name, phone_number, email,self.__root,self.__swdl)
        except Exception as e:
            raise e
        
    def create_subarea_data(self,address_forest):
        """Tworzy liste obiektow SubareaData
        """
        try:
            return self.__database_helper.get_subarea_data(address_forest)
        except Exception as e:
            raise e
        
    def create_tree_layers(self):
        """Lista obiektów TreeLayer
        """
        try:
            #TODO [SP].[INCREMENT_CURRENT]*[S].[SUB_AREA] zamieniono na Round([SP].[INCREMENT_CURRENT]*[S].[SUB_AREA],15) ponieważ pypyodbc nie radzi sobie z tymi polami i zgłasza błąd
            ##Tree species
            sql='SELECT SP.ARODES_INT_NUM, SP.STOREY_CD, SP.SPECIES_CD, SP.VOLUME, SP.PART_CD, SP.BHD, SP.HEIGHT, SP.SITE_CLASS_CD, SP.SPECIES_AGE, SP.SPECIES_RANK_ORDER,  Round(SP.INCREMENT_CURRENT,15) AS INCREMENT_CURRENT, Round([SP].[VOLUME]*[S].[SUB_AREA],15) AS Wyr1, Round([SP].[INCREMENT_CURRENT]*[S].[SUB_AREA],15) AS Wyr2 FROM F_STOREY_SPECIES AS SP INNER JOIN F_SUBAREA AS S ON SP.ARODES_INT_NUM=S.ARODES_INT_NUM'
            species=[(sp[0],sp[1], TreeSpecies(sp[2],sp[3],sp[4],sp[5],sp[6],sp[7],sp[8],sp[9],sp[10],sp[11],sp[12],None)) for sp in self.__database_helper.get_data(sql)]
            
            ##Arod storey
            sql='SELECT ARODES_INT_NUM, STOREY_CD, STANDDENSITY_INDEX, MIXTURE_CD, DENSITY_CD, STOREY_RANK_ORDER FROM F_AROD_STOREY'
            trees=[(layer[0],layer[1],TreeLayer(layer[0],layer[1],layer[2],layer[3],layer[4],layer[5],None,None,None)) for layer in self.__database_helper.get_data(sql)]
              
            for t in trees:
                sp=[s[2] for s in  species if t[0]==s[0] and t[1]==s[1]]
                
                if t[2]!=None and sp!=None:
                    t[2].set_layer_species(sp)
           
            return [t[2] for t in trees]
        except Exception as e:
            raise e
        
    def create_parcels(self):
        """Lista obiektów Subarea Parcel
        """
        try:
            ##Parcel Land Use
            sql='SELECT F_AROD_LAND_USE.ARODES_INT_NUM, F_AROD_LAND_USE.PARCEL_INT_NUM, F_PARCEL_LAND_USE.SHAPE_NR, F_PARCEL_LAND_USE.AREA_USE_CD, F_PARCEL_LAND_USE.SOIL_QUALITY_CD, F_PARCEL_LAND_USE.LAND_USE_AREA, F_AROD_LAND_USE.AROD_LAND_USE_AREA, F_PARCEL.COUNTY_CD, F_PARCEL.DISTRICT_CD, F_PARCEL.MUNICIPALITY_CD, F_PARCEL.COMMUNITY_CD, F_PARCEL.PARCEL_NR,  F_PARCEL_LAND_USE.AFFORESTATION FROM F_PARCEL INNER JOIN(F_PARCEL_LAND_USE INNER JOIN F_AROD_LAND_USE ON (F_PARCEL_LAND_USE.SHAPE_NR = F_AROD_LAND_USE.SHAPE_NR) AND(F_PARCEL_LAND_USE.PARCEL_INT_NUM = F_AROD_LAND_USE.PARCEL_INT_NUM)) ON(F_AROD_LAND_USE.PARCEL_INT_NUM = F_PARCEL.PARCEL_INT_NUM) AND(F_PARCEL.PARCEL_INT_NUM = F_PARCEL_LAND_USE.PARCEL_INT_NUM)'
            geometries_plu=self.__geometry_helper.get_parcel_land_use_geometries(TagsLayer.PARCEL_LAND_USE.value)           
            land_uses= [(land_use[0],land_use[1],land_use[2],'{}-{}-{}-{}'.format(land_use[7],land_use[8],land_use[9],land_use[10]),land_use[11],ParcelLandUse(land_use[3],land_use[4],land_use[5],land_use[6],str(land_use[12]).lower(),land_use[2])) for land_use in self.__database_helper.get_data(sql)]
            sql_use_measure='SELECT TEMP_ARODES_CUE.ARODES_INT_NUM, TEMP_ARODES_CUE.PARCEL_INT_NUM, TEMP_ARODES_CUE.SHAPE_NR, TEMP_ARODES_CUE.CUE_RANK_ORDER, TEMP_ARODES_CUE.MEASURE_CD, TEMP_ARODES_CUE.LARGE_TIMBER_VALUE, TEMP_ARODES_CUE.LARGE_TIMBER_VALUE_NET, TEMP_ARODES_CUE.CUE_AREA_DIV FROM TEMP_ARODES_CUE'
            use_measures=[(um[0], um[1], um[2], LandUseMeasure(um[4],um[5],um[6],um[7],um[3])) for um in self.__database_helper.get_data(sql_use_measure)]
            #Dodanie geometrii i zabiegów do parcel_land_use
            for use in land_uses:
                element=next(filter(lambda g:g[0]==use[3] and g[1]==use[4] and g[2]==use[2], geometries_plu),None)
                if element is not None:
                    use[5].geometry=element[3]          
                use[5].land_use_measures=[lum[3] for lum in use_measures if lum[0]==use[0] and lum[1]==use[1] and lum[2]==use[2]]
            ##Parcel Dictionary
            sql='SELECT DISTINCT A.ARODES_INT_NUM, P.PARCEL_INT_NUM, PU.SHAPE_NR, PARCEL_NR, COUNTY_CD, DISTRICT_CD, MUNICIPALITY_CD, COMMUNITY_CD, OWNERSHIP_CD, PARCEL_AREA, REG_SHEET_NR2 FROM F_PARCEL AS P, F_PARCEL_LAND_USE AS PU, F_AROD_LAND_USE AS A WHERE P.PARCEL_INT_NUM=PU.PARCEL_INT_NUM AND PU.PARCEL_INT_NUM=A.PARCEL_INT_NUM AND PU.SHAPE_NR=A.SHAPE_NR'
            parcels=[(parcel[0],parcel[1],parcel[2],SubareaParcel(parcel[0],parcel[3],parcel[4],parcel[5],parcel[6],parcel[7],parcel[8],parcel[9],parcel[10]))  for parcel in self.__database_helper.get_data(sql)]
            geometries=self.__geometry_helper.get_subarea_parcel_geometries(TagsLayer.SUBAREA_PARCEL.value)
            for p in parcels:
                l=[lu[5] for lu in land_uses if lu[0]==p[0] and lu[1]==p[1] and lu[2]==p[2]]
                if p[3] !=None and l != None:
                    p[3].set_parcel_land_use(l)
                element=next(filter(lambda g:g[0]=='{}-{}-{}-{}'.format(p[3].county_cd,p[3].district_cd,p[3].municipality_cd,p[3].community_cd) and g[1]==p[3].parcel_nr, geometries),None)
                if element is not None:
                    p[3].geometry=element[2]
            return [p[3] for p in parcels]
        except Exception as e:
            raise e
        
    def create_goals(self):
        """Lista obiektow Goal
        """
        try:
            sql='SELECT ARODES_INT_NUM, GOAL_TYPE_FL, SPECIES_CD, GOAL_RANK_ORDER, GOAL_SPECIES_PERC FROM F_AROD_GOAL'
            return [Goal(ARODES_INT_NUM, GOAL_TYPE_FL, SPECIES_CD, GOAL_RANK_ORDER, GOAL_SPECIES_PERC) for ARODES_INT_NUM, GOAL_TYPE_FL, SPECIES_CD, GOAL_RANK_ORDER, GOAL_SPECIES_PERC in self.__database_helper.get_data(sql)]
        except Exception as e:
            raise e
        
    def create_cues(self):
        """Kolekcaj obiektow Cue
        """
        try:
            sql='SELECT F_AROD_CUE.ARODES_INT_NUM, F_AROD_CUE.SITE_NR, F_AROD_CUE.MEASURE_CD, F_AROD_CUE.URGENCY, F_AROD_CUE.CUTTING_AREA, F_AROD_CUE.LARGE_TIMBER_PERC, F_AROD_CUE.LARGE_TIMBER_VALUE, F_AROD_CUE.LARGE_TIMBER_VALUE_NET, F_AROD_CUE.CUE_RANK_ORDER FROM F_AROD_CUE;'
            #TODO W zapytaniu brakuje cuttingNr 
            return [Cue(ARODES_INT_NUM, SITE_NR, MEASURE_CD, URGENCY,None ,CUTTING_AREA, LARGE_TIMBER_PERC, LARGE_TIMBER_VALUE, LARGE_TIMBER_VALUE_NET, CUE_RANK_ORDER) for ARODES_INT_NUM, SITE_NR, MEASURE_CD, URGENCY, CUTTING_AREA, LARGE_TIMBER_PERC, LARGE_TIMBER_VALUE, LARGE_TIMBER_VALUE_NET, CUE_RANK_ORDER in self.__database_helper.get_data(sql)]
        except Exception as e:
            raise e
        
    def create_phenomenas(self):
        """Lista obiektów phenomena
        """
        try:
            ##geometria
#             geom_pkt=self.__geometry_helper.get_pnenomena_geometries('{}\OS_PKT.shp'.format(self.__path),False)
#             geom_pow=self.__geometry_helper.get_pnenomena_geometries('{}\OS_POW.shp'.format(self.__path),True)
            geom_pkt=self.__geometry_helper.get_pnenomena_geometries(TagsLayer.POINT_SINGULARITIES.value,False)
            geom_pow=self.__geometry_helper.get_pnenomena_geometries(TagsLayer.SURFACE_SINGULARITIES.value,True)
            geometries=geom_pkt+geom_pow
            ##Phenomenas
            sql='SELECT P.ARODES_INT_NUM, P.PHENOMENA_CD, P.LOCATION_CD, P.NATURE_MON_FL, P.PHEN_AREA, P.PHEN_NUM, P.PLANT_CD, P.SPECIES_CD, A.ADRESS_FOREST, P.PHEN_RANK_ORDER FROM F_AROD_PHENOMENA AS P, F_ARODES AS A WHERE P.ARODES_INT_NUM=A.ARODES_INT_NUM'
            phenomenas=[('{}_{}'.format(ADRESS_FOREST, PHEN_RANK_ORDER),Phenomena(ARODES_INT_NUM, PHENOMENA_CD, LOCATION_CD,SPECIES_CD,PLANT_CD,PHEN_NUM,PHEN_AREA,NATURE_MON_FL)) for 
                        ARODES_INT_NUM, PHENOMENA_CD, LOCATION_CD, NATURE_MON_FL, PHEN_AREA, PHEN_NUM, PLANT_CD, SPECIES_CD, ADRESS_FOREST, PHEN_RANK_ORDER
                         in self.__database_helper.get_data(sql)]
            for phenomena in phenomenas:
                phenomena[1].set_geometry([geom[1] for geom in geometries if phenomena[0]==geom[0]])
                
            return [p[1] for p in phenomenas]
        except Exception as e:
            raise e
     
    def create_soil_spec(self):
        """Lista obiektow SoilSpec
        """
        try:
            sql='SELECT ARODES_INT_NUM, SOIL_SPECIES_CD, SOIL_LEVEL_CD, SOIL_RANK_ORDER FROM F_AROD_SOIL_SPEC'
            return [SoilSpec(ARODES_INT_NUM, SOIL_SPECIES_CD, SOIL_LEVEL_CD, SOIL_RANK_ORDER) for ARODES_INT_NUM, SOIL_SPECIES_CD, SOIL_LEVEL_CD, SOIL_RANK_ORDER in self.__database_helper.get_data(sql)]
        except Exception as e:
            raise e
        
    def create_spec_areas(self):
        """Lista obiektów SpecArea
        """
        try:
            
            ##geometria
            geom_spec_areas=self.__geometry_helper.get_spec_areas_geometries(TagsLayer.PNSW.value)
            
            ##SpeciesSpecArea
            sql='SELECT ARODES_INT_NUM, AROD_SPAREA_ORDER, SPECIES_CD, SP_AGE FROM F_SPECIES_SPAREA'
            species_spec_area=[('{}_{}'.format(ssa[0],ssa[1]),SpeciesSpecArea(ssa[0],ssa[2],ssa[3])) for ssa in self.__database_helper.get_data(sql)]
            
            ##SpecAreaList
            sql='SELECT F_AROD_SPEC_AREA.ARODES_INT_NUM, F_AROD_SPEC_AREA.AROD_SPAREA_ORDER, F_AROD_SPEC_AREA.SPECIAL_AREA_CD, F_AROD_SPEC_AREA.LOCATION_CD, Round(F_AROD_SPEC_AREA.SPECIAL_AREA,2) AS SPECIAL_AREA, F_AROD_SPEC_AREA.SPECIAL_AREA_NUM, F_AROD_SPEC_AREA.SPECIAL_AREA_YEAR, F_ARODES.ADRESS_FOREST FROM F_ARODES INNER JOIN F_AROD_SPEC_AREA ON F_ARODES.ARODES_INT_NUM = F_AROD_SPEC_AREA.ARODES_INT_NUM;'
            
            spec_areas=[('{}_{}'.format(sa[0],sa[1]),'{}_{}'.format(sa[7],sa[1]),SpecArea(sa[0],sa[2],sa[3],sa[4],sa[5],sa[6],sa[1],sa[7])) for sa in self.__database_helper.get_data(sql)]
            
            for sa in spec_areas:
                sa[2].set_species_spec_areas([ssa[1] for ssa in species_spec_area if sa[0]==ssa[0]])
                sa[2].set_geometry([geom[2] for geom in geom_spec_areas if sa[1]==geom[0]])
                #Ustawinie numeru działki
                parcel_nr_list=[nr for key,nr,geom in geom_spec_areas if sa[1]==key]
                sa[2].parcel_nr=','.join(parcel_nr_list)
            return [sa[2] for sa in spec_areas]
        except Exception as e:
            raise e
        
    def create_categories(self):
        """Lista obiektów ArodCategory
        """
        try:
            sql='SELECT ARODES_INT_NUM, PROT_CATEGORY_CD, PROT_RANK_ORDER FROM F_AROD_CATEGORY'
            return [ArodCategory(id,prot_category,prot_rank_order) for id,prot_category,prot_rank_order in self.__database_helper.get_data(sql)]
        except Exception as e:
            raise e
        
    def create_stand_pecs(self):
        """Lista obiektów StandPec
        """
        try:
            sql='SELECT ARODES_INT_NUM, FOREST_PEC_CD FROM F_AROD_STAND_PEC ORDER BY F_AROD_STAND_PEC.PEC_RANK_ORDER'
            return [ArodStandPec(id,forest_pec_cd) for id,forest_pec_cd in self.__database_helper.get_data(sql)]
        except Exception as e:
            raise e
        
    def create_subareas_geometries(self):
        """Geometrie obiektu Subrea
        """
        try:
            return self.__geometry_helper.get_subareas_geometries(TagsLayer.SURFACE.value)
        except Exception as e:
            raise e
        
    def create_owner_ship(self):
        """Lista obiektów ownerShip
        """
        try:
            #TODO Logika n apodstawie tego komentarza z kodu bibliotehi SWDLConverter  new OwnershipShare(addressForest.Substring(11, 2), "1", subareaData.SubArea)); do wyjaśnienia
            sql='SELECT F_ARODES.ARODES_INT_NUM, Mid([F_ARODES].[ADRESS_FOREST],12,2) AS ownership_category_gus, F_SUBAREA.SUB_AREA FROM F_ARODES INNER JOIN F_SUBAREA ON F_ARODES.ARODES_INT_NUM = F_SUBAREA.ARODES_INT_NUM;'
            data=self.__database_helper.get_data(sql)
            return [OwnershipShare(ARODES_INT_NUM,ownership_category_gus,'1',SUB_AREA) for (ARODES_INT_NUM,ownership_category_gus,SUB_AREA) in data]
        except Exception as e:
            raise e
         
    def create_protect_areas(self):
        """Obszary chronione"""
        try:
            sql='SELECT TEMP_ARODES_SET.ARODES_INT_NUM, TEMP_ARODES_SET.PROTEC_AREA_CD, TEMP_ARODES_SET.LAND_PROTECT_NAME FROM TEMP_ARODES_SET;'
            data=self.__database_helper.get_data(sql)
            return [(arodes_int_num,ProtectArea(area_cd,area_name)) for arodes_int_num,area_cd,area_name in data]
        except Exception as e:
            raise e         
         
    def create_subarea_data_list(self,address_forest):
        """Lista obiektow Subarea
        """
        try:
            sql="SELECT A.ARODES_INT_NUM, A.ADRESS_FOREST, S.DAMAGE_DEGREE_CD, S.CAUSE_CD, S.AREA_TYPE_CD, S.POSITION_CD, S.RELIEF_CD, S.SITE_TYPE_CD, S.DEGRADATION_CD, S.VEG_COVER_CD, S.FOREST_FUNC_CD, S.STAND_STRUCT_CD, S.SLOPE_CD, S.EXPOSURE_CD, S.MOISTURE_CD, S.SOIL_PEC_CD, S.SOIL_SUBTYPE_CD, S.PLANT_COMM_CD, S.ROTATION_AGE, S.SUB_AREA, S.SUBAREA_INFO, C.ALTITUDE_MIN, C.ALTITUDE_MAX FROM (F_ARODES AS A INNER JOIN F_SUBAREA AS S ON A.ARODES_INT_NUM=S.ARODES_INT_NUM) LEFT JOIN F_COMPARTMENT AS C ON A.ARODES_INT_NUM=C.ARODES_INT_NUM WHERE A.ADRESS_VALID='T' AND  Replace([A.ADRESS_FOREST],' ','') LIKE '{}%'".format(address_forest)
            self.__database_helper.open()
            data=self.__database_helper.get_data(sql)
            if data[0]==None:
                raise SwdlException(Message.NO_INVENT_DATA.value)
            #tworze obiekty SubareaData
            subareaList=[SubareaData(id,address_forest,damage_degree,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_sub_type_cd,plant_comm_cd,rotation_age,subarea,
                 subarea_info,altitude_min,altitude_max,accord_cd=None,silviculture_cd=None,reconstr_period=None,seed_base_nr=None,control_unit_cd=None,
                 reconstr_cd=None, phase_cd=None) for id,address_forest,damage_degree,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_sub_type_cd,plant_comm_cd,rotation_age,subarea,
                 subarea_info,altitude_min,altitude_max in data]
            #Aktualizacja obiektow SubareaData o dane dodatkowe
            #standPecs
            stand_pecs=self.create_stand_pecs()
            
            #categories
            categories=self.create_categories()
            
            #cues
            cues=self.create_cues()
            
            #goals
            goals=self.create_goals()
            
            #phenomenas
            phenomenas=self.create_phenomenas()
            
            #soilSpecs
            soil_specs=self.create_soil_spec()
            
            #specAreas
            spec_areas=self.create_spec_areas()
            
            #treeLayers
            tree_layers=self.create_tree_layers()
            
            #parcels
            parcels=self.create_parcels()
            
            #ownership
            owner_ship=self.create_owner_ship()
            
            #geometry
            geometries=self.create_subareas_geometries()
            
            protect_areas=self.create_protect_areas()
            
            #Aktualizacja subarea o dodatkowe informacje
            for sub in subareaList:
                sub.arod_stand_pecs=[sp for sp in stand_pecs if sub.id==sp.id]
                sub.arod_categories=[ac for ac in categories if sub.id==ac.id]
                sub.arod_cues=[c for c in cues if sub.id==c.id]
                sub.arod_goals=[g for g in goals if sub.id==g.id]
                sub.arod_phenomenas=[ap for ap in phenomenas if sub.id==ap.id]
                sub.arod_soil_specs=[ass for ass in soil_specs if sub.id==ass.id]
                sub.arod_spec_areas=[asa for asa in spec_areas if sub.id==asa.id]
                sub.tree_layers=[tl for tl in tree_layers if sub.id==tl.id]
                sub.subarea_parcels=[p for p in parcels if sub.id==p.id]
                sub.geometry=[geometry for address_forest, geometry in geometries if sub.address_forest==address_forest]
                sub.subarea_ownership_structure=[owner for owner in owner_ship if sub.id==owner.id]
                sub.protect_areas=[protect_area for arodes_int_num,protect_area in protect_areas if arodes_int_num==sub.id]
            return subareaList
        except Exception as e:
            raise e
        finally:
            self.__database_helper.close()
        

        