#!/usr/bin/env python3
# -*- coding: UTF-8 -*-
#Nom :  : get_medwet.py
#Description : 
#Copyright : 2021, CEN38
#Auteur    : Colas Geier
#Version : 1.0

import pymedwet as pym
import pandas as pd

if __name__ == "__main__":

  NAME_OUT = '~/Documents/9_PROJETS/1_ZH/inventaire_zh_2021.xlsx'
  lst_fct = {
    'Infos générales': pym.get_SiteInfo,
    'Corine Biotope' : pym.get_cb,
    'Délimitation de la zh': pym.get_Delim_espaceFct,
    'Description de la zh' : pym.get_descrp_zh,
    'Fonctmt de la zh': pym.get_fctmt_zh,
    'Fonctions de la zh' : pym.get_fct_zh,
    'Evaluation de la zh': pym.get_eval_glob,
    'Observations' : pym.get_obs,
    'Bibliographie': pym.get_biblio
  }
  print('INIT récupération des données ...')
  df = {}
  for f,fonction in enumerate(lst_fct):
    name = fonction
    fct  = lst_fct[fonction]
    df1 = fct(pym.db_file1)
    df2 = fct(pym.db_file2)
    if f == 0:
      lst_stcd = df1[df1.SITE_COD.isin(df2.SITE_COD)].SITE_COD
    if fct == pym.get_SiteInfo:
      df2.loc[df2.SITE_COD.isin(lst_stcd), 'OTHER_NAME'] = df1.loc[df1.SITE_COD.isin(lst_stcd),'SITE_NAME'].tolist()
    if isinstance(df1, pd.DataFrame) and isinstance(df2, pd.DataFrame):
      df[f] = pd.concat([df1[~df1.SITE_COD.isin(lst_stcd)], df2])
      df[f].name = name
    elif isinstance(df1, dict) and isinstance(df2, dict):
      df[f] = {}
      df[f]['title'] = name
      for d in df1:
        df[f][d] = pd.concat([df1[d][~df1[d].SITE_COD.isin(lst_stcd)], df2[d]])

  print('INIT écriture du fichier ...')
  # Ecriture des données
  with pd.ExcelWriter(NAME_OUT) as writer:
    for d in df:
      DF = df[d]
      if isinstance(DF, pd.DataFrame):
        DF.to_excel(writer,sheet_name=DF.name,startrow=1 , startcol=0, index=False, header=DF.columns)
        ws = writer.book.active
        writer.sheets[DF.name].cell(1,1,value=DF.name)
        writer.save()
      elif isinstance(DF, dict):
        for i,d in enumerate(DF):
          if d == 'title':
            continue
          if i == 1:
            row = 1
            col = 0
          else:
            col = DF[d].shape[1] + col + 3
          DF[d].to_excel(writer,sheet_name=DF['title'],startrow=row , startcol=col, index=False)
          ws = writer.book.active
          writer.sheets[DF['title']].cell(column=col+1,row=row,value=d)
          writer.save()

  import sys
  sys.exit('END SCRIPT ...')