#!/usr/bin/env python
# -*- coding: UTF-8 -*-

from os import getlogin
import pandas as pd
import geopandas as gpd
from sqlalchemy.engine import URL
from sqlalchemy import create_engine
from pycen import con_bdcen as con, con_fon


annee = 2023
path_source = '/media/{log}/SRV/FICHIERS/OUTILS/BASES DE DONNEES/BILAN_FEDE_CEN/{an}/Docs_SIG_joints{an}/Docs_SIG_joints{an}/'.format(log=getlogin(),an=annee)
dico_data = 'Dico_DATA_sites_CEN_v%i_v1.xlsx' % annee
sheet_par = 'parcelles_cen_XX_%i' % annee
sheet_sit = 'sites_cen_XX_%i' % annee


###### Récupération des données conservatoires
def get_sites_cen():
    sqlsit = '''SELECT * FROM %s.%s WHERE date_fin is NULL OR date_fin >= '%i-01-01' ;'''%('sites','c_sites_zonages',annee)
    dfsite = gpd.read_postgis(sql=sqlsit,geom_col='geom',con=con)

    # Isolation de tous les sites CEN (gestion/assist/mili)
    return dfsite[
        ~dfsite.code_site.duplicated()|\
        (dfsite.code_site.duplicated() & dfsite.type_zonage == 'ZI')
    ]

def get_parcelles_cen():
    return