Create PPT

dashboard.create_ppt

Functions

create_ppt(env, date)

Crée une présentation PowerPoint contenant des informations sur la situation hospitalière en matière de lits de réanimation et d'occupation hospitalière à partir de données extraites de la base de données.

Cette fonction enregistre des graphiques, des tableaux et des textes dans un modèle de présentation existant pour générer un rapport visuel.

Paramètres :

env : object L'environnement de connexion nécessaire pour interagir avec la base de données.

str

La date de rapport à traiter, au format 'YYYY-MM-DD'.

Processus :
  1. Chargement et traitement des données depuis la table table_union.
  2. Création d'une présentation en utilisant un modèle PowerPoint existant.
  3. Insertion de graphiques et de cartes pertinents dans la présentation.
  4. Remplissage des zones de texte avec des informations et des données statistiques.
Exceptions :
  • IOError : Si le modèle de présentation ou les fichiers nécessaires ne peuvent pas être trouvés ou chargés.
  • ValueError : Si les données extraites ne respectent pas les formats ou types attendus.

s

Source code in dashboard/create_ppt.py
def create_ppt(env:str, date):
    """
    Crée une présentation PowerPoint contenant des informations sur la situation 
    hospitalière en matière de lits de réanimation et d'occupation hospitalière à partir 
    de données extraites de la base de données. 

    Cette fonction enregistre des graphiques, des tableaux et des textes dans un modèle 
    de présentation existant pour générer un rapport visuel.

    Paramètres :
    ----------
    env : object
        L'environnement de connexion nécessaire pour interagir avec la base de données.

    date : str
        La date de rapport à traiter, au format 'YYYY-MM-DD'. 

    Processus :
    ----------
    1. Chargement et traitement des données depuis la table `table_union`.
    2. Création d'une présentation en utilisant un modèle PowerPoint existant.
    3. Insertion de graphiques et de cartes pertinents dans la présentation.
    4. Remplissage des zones de texte avec des informations et des données statistiques.

    Exceptions :
    ------------
    - IOError : Si le modèle de présentation ou les fichiers nécessaires ne peuvent pas 
      être trouvés ou chargés.
    - ValueError : Si les données extraites ne respectent pas les formats ou types 
      attendus.

    s"""

    from src.constantes_front import ordre_regions, mapping_regions


    # Création d'une instance DateTimeManager
    _dt_manager = DateTimeManager(date)

    # Variables à exporter pour compatibilité avec vos anciens imports
    hour_exec          = _dt_manager.execution_hour
    today              = _dt_manager.today
    today_Ymd_short    = _dt_manager.today_Ymd_short
    today_Ymd_long     = _dt_manager.today_Ymd_long
    today_jmA          = _dt_manager.today_jmA
    prev_day_Ymd_short = _dt_manager.prev_day_Ymd_short
    prev_day_Ymd_long  = _dt_manager.prev_day_Ymd_long
    month_const        = _dt_manager.MONTH_NAMES
    PRINT_TODAY        = _dt_manager.print_today
    PRINT_PREV_DAY     = _dt_manager.print_prev_day
    PREV_FILE_DATE     = _dt_manager.prev_file_date
    FILE_DATE            = _dt_manager.file_date
    PRINT_PPT_DATETIME = _dt_manager.print_ppt_datetime
    start_date = _dt_manager.start_date
    # Import template and csv
    logger.info("Importing recap_brules and template_brules.")


    #%% ----------- Chargement des données ----------
    prs = Presentation(f"{metadata_dir}/Maquette_ppt.pptx")

    df = req2table(f"SELECT * FROM table_union WHERE capacite_extract_datetime::date = '{today_Ymd_long}' AND h_exec='{hour_exec}'", env)
    df["date"] = pd.to_datetime(df["capacite_extract_datetime"].dt.date)
    df = df[~df["lit_capacite"].isnull()]
    df = df[df["sous_categorie_lit"] != "rea_brules"]
    df = df[(df["region"] != "IDF") | (df["lit_capacite"] != 100)]

    df["new_cate"] = np.repeat(np.nan, df.shape[0])
    df.loc[df["categorie_lit"] == "reanimation", "new_cate"] = "Réanimation"
    df.loc[df["categorie_lit"] == "surveillance_continue", "new_cate"] = "Surveillance continue"
    df.loc[df["sous_categorie_lit"] == "usin", "new_cate"] = "USINV"
    df.loc[df["sous_categorie_lit"] == "usic", "new_cate"] = "USIC"
    df.loc[df["sous_categorie_lit"] == "autres_usi", "new_cate"] = "Autres USI"

    loca_finess = pd.read_csv(f'{csv_dir}/GEOLOCALISATION_FINESS_12022021.csv', sep = ';')
    loca_finess = loca_finess.drop_duplicates()
    loca_finess["finess"] = ['{0:0>9}'.format(i) for i in loca_finess["finess"]]
    loca_finess["latitude"] = loca_finess["latitude"].str.replace(",", ".").astype(float)
    loca_finess["longitude"] = loca_finess["longitude"].str.replace(",", ".").astype(float)

    recap_region = pd.read_csv(f"{csv_dir}/recap_region.csv")

    #%% ------------------ Slide réanimation national -----------
    slide = prs.slides[0]
    name_map = 'Espace réservé pour une image\xa0 1'
    name_barplot1 = 'Espace réservé pour une image\xa0 2'
    name_barplot2 = 'Espace réservé pour une image\xa0 4'
    name_source1 = 'Espace réservé du texte 3'
    name_source2 = 'Espace réservé du texte 14'
    name_map_gua = 'Espace réservé pour une image\xa0 5'
    name_map_mar = 'Espace réservé pour une image\xa0 6'
    name_map_guy = 'Espace réservé pour une image\xa0 7'
    name_map_reu = 'Espace réservé pour une image\xa0 8'
    name_map_may = 'Espace réservé pour une image\xa0 9'
    name_legend = 'Espace réservé pour une image\xa0 26'
    name_to1 = 'Espace réservé du texte 21'
    name_to2 = 'Espace réservé du texte 22'
    name_to3 = 'Espace réservé du texte 23'
    name_to4 = 'Espace réservé du texte 24'
    name_to5 = 'Espace réservé du texte 25'
    name_x = 'Espace réservé du texte 29'
    name_fraicheur = 'Espace réservé du texte 30'
    name_legend_title = 'Espace réservé du texte 20'
    name_title_colorbar = 'Espace réservé du texte 19'
    name_colorbar = 'Espace réservé pour une image\xa0 16'
    name_capa1 = 'Espace réservé du texte 18'
    name_capa2 = 'Espace réservé du texte 17'
    name_capa3 = 'Espace réservé du texte 27'
    name_capa4 = 'Espace réservé du texte 28'
    title_map = 'Espace réservé du texte 15'
    title_barplot1 = 'Espace réservé du texte 10'
    title_barplot2 = 'Espace réservé du texte 11'
    # ND_gua = 'Espace réservé du texte 12'
    ND_may = 'Espace réservé du texte 13'


    i = 0
    for shape in slide.shapes:
        if shape.name == name_map:
            id_map = shape.placeholder_format.idx
        elif shape.name == name_barplot1:
            id_barplot1 = shape.placeholder_format.idx
        elif shape.name == name_barplot2:
            id_barplot2 = shape.placeholder_format.idx
        elif shape.name == name_source1:
            id_source1 = i
        elif shape.name == name_source2:
            id_source2 = i
        elif shape.name == name_map_gua:
            id_gua = shape.placeholder_format.idx
        elif shape.name == name_map_mar:
            id_mar = shape.placeholder_format.idx
        elif shape.name == name_map_guy:
            id_guy = shape.placeholder_format.idx
        elif shape.name == name_map_reu:
            id_reu = shape.placeholder_format.idx
        elif shape.name == name_map_may:
            id_may = shape.placeholder_format.idx
        elif shape.name == name_legend:
            id_legend = shape.placeholder_format.idx
        elif shape.name == name_legend_title:
            id_legend_title = i
        elif shape.name == name_to1:
            id_to1 = i
        elif shape.name == name_to2:
            id_to2 = i
        elif shape.name == name_to3:
            id_to3 = i
        elif shape.name == name_to4:
            id_to4 = i
        elif shape.name == name_to5:
            id_to5 = i
        elif shape.name == name_x:
            id_x = i
        elif shape.name == name_fraicheur:
            id_fraicheur = i
        elif shape.name == name_title_colorbar:
            id_title_colorbar = i
        elif shape.name == name_colorbar:
            id_colorbar = shape.placeholder_format.idx
        elif shape.name == name_capa1:
            id_capa1 = i
        elif shape.name == name_capa2:
            id_capa2 = i
        elif shape.name == name_capa3:
            id_capa3 = i
        elif shape.name == name_capa4:
            id_capa4 = i
        elif shape.name == title_map:
            id_title_map = i
        elif shape.name == title_barplot1:
            id_title_barplot1 = i
        elif shape.name == title_barplot2:
            id_title_barplot2 = i
        # elif shape.name == ND_gua:
        #     id_ND_gua = i
        elif shape.name == ND_may:
            id_ND_may = i
        else:
            pass

        i = i + 1

    # -------------- Graphiques -----------------        
    slide.placeholders[id_barplot1].insert_picture(f'{pictures_dir}/barplot_reg_rea.png')
    slide.placeholders[id_barplot2].insert_picture(f'{pictures_dir}/barplot_evol_rea.png')
    slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_rea.png')
    slide.placeholders[id_gua].insert_picture(f'{pictures_dir}/map_rea_GUA.png')
    slide.placeholders[id_mar].insert_picture(f'{pictures_dir}/map_rea_MAR.png')
    slide.placeholders[id_guy].insert_picture(f'{pictures_dir}/map_rea_GUY.png')
    slide.placeholders[id_reu].insert_picture(f'{pictures_dir}/map_rea_REU.png')
    slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_to.png')
    slide.placeholders[id_colorbar].insert_picture(f'{metadata_dir}/plots/legend_capa_rea.png')

    #---------------- empty metadata graphics --------------------------------
    slide.placeholders[id_may].insert_picture(f'{metadata_dir}/plots/carto_mayotte.png')

    # ------------- Zones de texte -------------
    shape = slide.shapes[id_source1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés)  / toutes causes confondues"

    shape = slide.shapes[id_source2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"

    shape = slide.shapes[id_title_colorbar]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Nombre de lits installés à date (/100 000 habitants)"

    shape = slide.shapes[id_capa1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "0"

    shape = slide.shapes[id_capa2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "25"

    shape = slide.shapes[id_capa3]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "10"

    shape = slide.shapes[id_capa4]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "20"

    shape = slide.shapes[id_x]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "X"

    shape = slide.shapes[id_fraicheur]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Défaut de mise à jour"

    shape = slide.shapes[id_legend_title]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Taux d'occupation à date"

    shape = slide.shapes[id_to1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = ">= 95%"

    shape = slide.shapes[id_to2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[90%; 95%["

    shape = slide.shapes[id_to3]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[85%; 90%["

    shape = slide.shapes[id_to4]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[80%; 85%["

    shape = slide.shapes[id_to5]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "< 80%"

    shape = slide.shapes[id_title_map]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Situation dans les départements au {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_title_barplot1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Régions France hors Mayotte – {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_title_barplot2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"France entière hors Mayotte, du {start_date} au {PRINT_PPT_DATETIME}"

    # shape = slide.shapes[id_ND_gua]
    # text_frame = shape.text_frame
    # text_frame.clear()
    # p = text_frame.paragraphs[0]
    # run = p.add_run()
    # run.text = "Non Disponible"

    shape = slide.shapes[id_ND_may]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Non Disponible"
    #%% ------------------ Slide USC national -----------
    slide = prs.slides[1]
    name_map = 'Espace réservé pour une image\xa0 1'
    name_barplot1 = 'Espace réservé pour une image\xa0 2'
    name_barplot2 = 'Espace réservé pour une image\xa0 4'
    name_source1 = 'Espace réservé du texte 3'
    name_source2 = 'Espace réservé du texte 14'
    name_map_gua = 'Espace réservé pour une image\xa0 5'
    name_map_mar = 'Espace réservé pour une image\xa0 6'
    name_map_guy = 'Espace réservé pour une image\xa0 7'
    name_map_reu = 'Espace réservé pour une image\xa0 8'
    name_map_may = 'Espace réservé pour une image\xa0 9'
    name_legend = 'Espace réservé pour une image\xa0 26'
    name_to1 = 'Espace réservé du texte 21'
    name_to2 = 'Espace réservé du texte 22'
    name_to3 = 'Espace réservé du texte 23'
    name_to4 = 'Espace réservé du texte 24'
    name_to5 = 'Espace réservé du texte 25'
    name_x = 'Espace réservé du texte 29'
    name_fraicheur = 'Espace réservé du texte 30'
    name_legend_title = 'Espace réservé du texte 20'
    name_title_colorbar = 'Espace réservé du texte 19'
    name_colorbar = 'Espace réservé pour une image\xa0 16'
    name_capa1 = 'Espace réservé du texte 18'
    name_capa2 = 'Espace réservé du texte 17'
    name_capa3 = 'Espace réservé du texte 27'
    name_capa4 = 'Espace réservé du texte 28'
    title_map = 'Espace réservé du texte 15'
    title_barplot1 = 'Espace réservé du texte 10'
    title_barplot2 = 'Espace réservé du texte 11'
    # ND_gua = 'Espace réservé du texte 12'
    ND_may = 'Espace réservé du texte 13'


    i = 0
    for shape in slide.shapes:
        if shape.name == name_map:
            id_map = shape.placeholder_format.idx
        elif shape.name == name_barplot1:
            id_barplot1 = shape.placeholder_format.idx
        elif shape.name == name_barplot2:
            id_barplot2 = shape.placeholder_format.idx
        elif shape.name == name_source1:
            id_source1 = i
        elif shape.name == name_source2:
            id_source2 = i
        elif shape.name == name_map_gua:
            id_gua = shape.placeholder_format.idx
        elif shape.name == name_map_mar:
            id_mar = shape.placeholder_format.idx
        elif shape.name == name_map_guy:
            id_guy = shape.placeholder_format.idx
        elif shape.name == name_map_reu:
            id_reu = shape.placeholder_format.idx
        elif shape.name == name_map_may:
            id_may = shape.placeholder_format.idx
        elif shape.name == name_legend:
            id_legend = shape.placeholder_format.idx
        elif shape.name == name_legend_title:
            id_legend_title = i
        elif shape.name == name_to1:
            id_to1 = i
        elif shape.name == name_to2:
            id_to2 = i
        elif shape.name == name_to3:
            id_to3 = i
        elif shape.name == name_to4:
            id_to4 = i
        elif shape.name == name_to5:
            id_to5 = i
        elif shape.name == name_x:
            id_x = i
        elif shape.name == name_fraicheur:
            id_fraicheur = i
        elif shape.name == name_title_colorbar:
            id_title_colorbar = i
        elif shape.name == name_colorbar:
            id_colorbar = shape.placeholder_format.idx
        elif shape.name == name_capa1:
            id_capa1 = i
        elif shape.name == name_capa2:
            id_capa2 = i
        elif shape.name == name_capa3:
            id_capa3 = i
        elif shape.name == name_capa4:
            id_capa4 = i
        elif shape.name == title_map:
            id_title_map = i
        elif shape.name == title_barplot1:
            id_title_barplot1 = i
        elif shape.name == title_barplot2:
            id_title_barplot2 = i
        # elif shape.name == ND_gua:
        #     id_ND_gua = i
        elif shape.name == ND_may:
            id_ND_may = i
        else:
            pass

        i = i + 1

    # -------------- Graphiques -----------------        
    slide.placeholders[id_barplot1].insert_picture(f'{pictures_dir}/barplot_reg_usc.png')
    slide.placeholders[id_barplot2].insert_picture(f'{pictures_dir}/barplot_evol_usc.png')
    slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_usc.png')
    slide.placeholders[id_gua].insert_picture(f'{pictures_dir}/map_usc_GUA.png')
    slide.placeholders[id_mar].insert_picture(f'{pictures_dir}/map_usc_MAR.png')
    slide.placeholders[id_guy].insert_picture(f'{pictures_dir}/map_usc_GUY.png')
    slide.placeholders[id_reu].insert_picture(f'{pictures_dir}/map_usc_REU.png')
    slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_to.png')
    slide.placeholders[id_colorbar].insert_picture(f'{metadata_dir}/plots/legend_capa_rea.png')

    # ----- empty mayotte maps
    slide.placeholders[id_may].insert_picture(f'{metadata_dir}/plots/carto_mayotte.png')


    # ------------- Zones de texte -------------
    shape = slide.shapes[id_source1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"

    shape = slide.shapes[id_source2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue (hors unités grands brûlés) / toutes causes confondues"

    shape = slide.shapes[id_title_colorbar]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Nombre de lits installés à date (/100 000 habitants)"

    shape = slide.shapes[id_capa1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "0"

    shape = slide.shapes[id_capa2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "25"

    shape = slide.shapes[id_capa3]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "10"

    shape = slide.shapes[id_capa4]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "20"

    shape = slide.shapes[id_x]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "X"

    shape = slide.shapes[id_fraicheur]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Défaut de mise à jour"

    shape = slide.shapes[id_legend_title]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Taux d'occupation à date"

    shape = slide.shapes[id_to1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = ">= 95%"

    shape = slide.shapes[id_to2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[90%; 95%["

    shape = slide.shapes[id_to3]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[85%; 90%["

    shape = slide.shapes[id_to4]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[80%; 85%["

    shape = slide.shapes[id_to5]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "< 80%"

    shape = slide.shapes[id_title_map]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Situation dans les départements au {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_title_barplot1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Régions France hors Mayotte – {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_title_barplot2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"France entière hors Mayotte, du {start_date} au {PRINT_PPT_DATETIME}"

    # shape = slide.shapes[id_ND_gua]
    # text_frame = shape.text_frame
    # text_frame.clear()
    # p = text_frame.paragraphs[0]
    # run = p.add_run()
    # run.text = "Non Disponible"

    shape = slide.shapes[id_ND_may]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Non Disponible"
    #%% ------------------ Slide IDF reanimation -----------
    slide = prs.slides[2]
    name_map1 = 'Espace réservé pour une image\xa0 8'
    name_map2 = 'Espace réservé pour une image\xa0 7'
    name_barplot = 'Espace réservé pour une image\xa0 2'
    name_source1 = 'Espace réservé du texte 1'
    name_source2 = 'Espace réservé du texte 5'
    name_legend = 'Espace réservé pour une image\xa0 11'
    name_legend_title = 'Espace réservé du texte 9'
    name_to1 = 'Espace réservé du texte 10'
    name_to2 = 'Espace réservé du texte 11'
    name_to3 = 'Espace réservé du texte 12'
    name_to4 = 'Espace réservé du texte 13'
    name_to5 = 'Espace réservé du texte 14'
    title_map = 'Espace réservé du texte 6'
    title_barplot = 'Espace réservé du texte 4'
    name_table = 'Espace réservé du tableau 16'
    name_title_table = 'Espace réservé du texte 3'


    i = 0
    for shape in slide.shapes:
        if shape.name == name_map1:
            id_map1 = shape.placeholder_format.idx
        elif shape.name == name_map2:
            id_map2 = shape.placeholder_format.idx
        elif shape.name == name_barplot:
            id_barplot = shape.placeholder_format.idx
        elif shape.name == name_source1:
            id_source1 = i
        elif shape.name == name_source2:
            id_source2 = i
        elif shape.name == name_colorbar:
            id_colorbar = i
        elif shape.name == title_map:
            id_title_map = i
        elif shape.name == title_barplot:
            id_title_barplot = i
        elif shape.name == name_legend:
            id_legend = shape.placeholder_format.idx
        elif shape.name == name_legend_title:
            id_legend_title = i
        elif shape.name == name_to1:
            id_to1 = i
        elif shape.name == name_to2:
            id_to2 = i
        elif shape.name == name_to3:
            id_to3 = i
        elif shape.name == name_to4:
            id_to4 = i
        elif shape.name == name_to5:
            id_to5 = i
        elif shape.name == name_table:
            id_table = i
        elif shape.name == name_title_table:
            id_title_table = i

        i = i + 1
    # -------------- Graphiques -----------------
    slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_rea_IDF.png')
    slide.placeholders[id_map1].insert_picture(f'{pictures_dir}/map_rea_region_IDF.png')
    slide.placeholders[id_map2].insert_picture(f'{pictures_dir}/map_rea_region_IDF_pcr.png')
    slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_color.png')
    # ------------- Zones de texte -------------
    shape = slide.shapes[id_source1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"

    shape = slide.shapes[id_source2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"

    shape = slide.shapes[id_title_map]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Situation en Île-de-France au {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_title_barplot]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Île-de-France du {start_date} au {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_legend_title]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Taux d'occupation à date"

    shape = slide.shapes[id_to1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = ">= 95%"

    shape = slide.shapes[id_to2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[90%; 95%["

    shape = slide.shapes[id_to3]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[85%; 90%["

    shape = slide.shapes[id_to4]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[80%; 85%["

    shape = slide.shapes[id_to5]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "< 80%"

    to_reg = recap_region.loc[(recap_region["new_cate"] == "Surveillance continue") &\
                            (recap_region["region"] == 'IDF'), 'taux_occ']
    if to_reg.shape[0] == 1 :
        if ~np.isnan(to_reg.iloc[0]):
            to_reg = int(to_reg.iloc[0] * 100)
        else:
            to_reg = ""
    else:
        to_reg = ""
    shape = slide.shapes[id_title_table]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Liste des établissements en Île-de-France par taux d’occupation au {PRINT_PPT_DATETIME}"
    # ----------------- Tableau -------------
    tmp = df[(df["region"] == "IDF")]
    tmp = tmp[tmp["new_cate"] == "Réanimation"]
    tmp_frais = tmp[tmp["dispo_maj_fraicheur"] <= 24]
    tmp_frais = tmp_frais[tmp_frais['lit_capacite'] >= tmp_frais['lit_dispo']]

    recap = tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"]\
        .sum().reset_index(name = 'lit_capacite_tot')
    recap_frais = tmp_frais.groupby(["finess", "etablissement", "departement"], as_index = False)\
        [["lit_capacite", 'lit_dispo']].sum()
    recap = recap.merge(recap_frais, how = 'left', on = ["finess", "etablissement", "departement"])
    fraicheur = (tmp_frais.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum() \
        / tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum())\
        .reset_index(name = 'fraicheur')
    recap = recap.merge(fraicheur, how = 'left', on = ["finess", "etablissement", "departement"])
    recap["taux_occ"] = ((recap["lit_capacite"] - recap["lit_dispo"])\
                                / recap["lit_capacite"]).round(2)
    recap.loc[recap["fraicheur"].isnull(), "fraicheur"] = 0
    recap = recap.merge(loca_finess, on = "finess", how = "left")

    recap = recap[recap["fraicheur"] >= 0.7]
    capa_median = recap["lit_capacite"].median()
    recap = recap[recap["lit_capacite"] >= capa_median]


    top10 = recap.nlargest(10,[ "taux_occ", "lit_capacite"]).\
        sort_values(['taux_occ', 'lit_capacite'], ascending = False)
    top10 = top10.reset_index(drop = True)
    top10 = top10[~top10["taux_occ"].isnull()]
    top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
    top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
    top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
    top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
    top10.loc[top10["ville"].isnull(), "ville"] = "NULL"
    # top10["etablissement"] = [row["etablissement"][0:53] for i, row in top10.iterrows()]
    # top10["ville"] = [row["ville"][0:53] for i, row in top10.iterrows()]



    pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
    table = pl_table.table
    table.columns[0].width = Cm(7.80)
    table.columns[1].width = Cm(4.69)
    table.columns[2].width = Cm(2.46)
    table.columns[3].width = Cm(2.46)

    for i in range(0, 11):
        for j in range(0, 4):
            cond = [j == 0, j == 1, j == 2, j == 3]
            choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
            choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
            col = str(np.select(cond, choices))
            label_col = str(np.select(cond, choices2))


            if i == 0:
                table.cell(i, j).fill.solid()
                table.cell(i, j).fill.fore_color.rgb = RGBColor(183, 184, 217)
                cell = table.rows[i].cells[j]
                paragraph = cell.text_frame.paragraphs[0]
                p = cell.text_frame.paragraphs[0]
                run = p.add_run()
                run.text = label_col
                paragraph.font.size = Pt(7)                
            elif i <= top10.shape[0]:
                text = top10.loc[i - 1, col]   
                table.cell(i, j).fill.solid()
                table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                cell = table.rows[i].cells[j]
                paragraph = cell.text_frame.paragraphs[0]
                p = cell.text_frame.paragraphs[0]
                run = p.add_run()
                run.text = text
                font_size = 6 if len(text) > 50 else 7
                paragraph.font.size = Pt(font_size)
                if (j == 2) | (j == 3):
                    paragraph.alignment = PP_ALIGN.RIGHT
            else:
                table.cell(i, j).fill.solid()
                table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                cell = table.rows[i].cells[j]
                paragraph = cell.text_frame.paragraphs[0]
                p = cell.text_frame.paragraphs[0]
                run = p.add_run()
                run.text = ""
                paragraph.font.size = Pt(7)

        table.rows[i].height = Cm(0.64)

    #%% ------------------ Slide IDF USC -----------
    slide = prs.slides[3]
    name_map1 = 'Espace réservé pour une image\xa0 8'
    name_map2 = 'Espace réservé pour une image\xa0 7'
    name_barplot = 'Espace réservé pour une image\xa0 2'
    name_source1 = 'Espace réservé du texte 1'
    name_source2 = 'Espace réservé du texte 5'
    name_legend = 'Espace réservé pour une image\xa0 11'
    name_legend_title = 'Espace réservé du texte 9'
    name_to1 = 'Espace réservé du texte 10'
    name_to2 = 'Espace réservé du texte 11'
    name_to3 = 'Espace réservé du texte 12'
    name_to4 = 'Espace réservé du texte 13'
    name_to5 = 'Espace réservé du texte 14'
    title_map = 'Espace réservé du texte 6'
    title_barplot = 'Espace réservé du texte 4'
    name_table = 'Espace réservé du tableau 16'
    name_title_table = 'Espace réservé du texte 3'


    i = 0
    for shape in slide.shapes:
        if shape.name == name_map1:
            id_map1 = shape.placeholder_format.idx
        elif shape.name == name_map2:
            id_map2 = shape.placeholder_format.idx
        elif shape.name == name_barplot:
            id_barplot = shape.placeholder_format.idx
        elif shape.name == name_source1:
            id_source1 = i
        elif shape.name == name_source2:
            id_source2 = i
        elif shape.name == name_colorbar:
            id_colorbar = i
        elif shape.name == title_map:
            id_title_map = i
        elif shape.name == title_barplot:
            id_title_barplot = i
        elif shape.name == name_legend:
            id_legend = shape.placeholder_format.idx
        elif shape.name == name_legend_title:
            id_legend_title = i
        elif shape.name == name_to1:
            id_to1 = i
        elif shape.name == name_to2:
            id_to2 = i
        elif shape.name == name_to3:
            id_to3 = i
        elif shape.name == name_to4:
            id_to4 = i
        elif shape.name == name_to5:
            id_to5 = i
        elif shape.name == name_table:
            id_table = i
        elif shape.name == name_title_table:
            id_title_table = i
        i = i + 1
    # -------------- Graphiques -----------------
    slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_usc_IDF.png')
    slide.placeholders[id_map1].insert_picture(f'{pictures_dir}/map_usc_region_IDF.png')
    slide.placeholders[id_map2].insert_picture(f'{pictures_dir}/map_usc_region_IDF_pcr.png')
    slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_color.png')
    # ------------- Zones de texte -------------
    shape = slide.shapes[id_source1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"

    shape = slide.shapes[id_source2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"

    shape = slide.shapes[id_title_map]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Situation en Île-de-France au {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_title_barplot]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Île-de-France du {start_date} au {PRINT_PPT_DATETIME}"

    shape = slide.shapes[id_legend_title]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "Taux d'occupation à date"

    shape = slide.shapes[id_to1]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = ">= 95%"

    shape = slide.shapes[id_to2]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[90%; 95%["

    shape = slide.shapes[id_to3]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[85%; 90%["

    shape = slide.shapes[id_to4]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "[80%; 85%["

    shape = slide.shapes[id_to5]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = "< 80%"

    to_reg = recap_region.loc[(recap_region["new_cate"] == "Surveillance continue") &\
                            (recap_region["region"] == 'IDF'), 'taux_occ']
    if to_reg.shape[0] == 1 :
        if ~np.isnan(to_reg.iloc[0]):
            to_reg = int(to_reg.iloc[0] * 100)
        else:
            to_reg = ""
    else:
        to_reg = ""
    shape = slide.shapes[id_title_table]
    text_frame = shape.text_frame
    text_frame.clear()
    p = text_frame.paragraphs[0]
    run = p.add_run()
    run.text = f"Liste des établissements en Île-de-France par taux d’occupation au {PRINT_PPT_DATETIME}"
    # ----------------- Tableau -------------
    tmp = df[(df["region"] == "IDF")]
    tmp = tmp[tmp["new_cate"] == "Surveillance continue"]
    tmp_frais = tmp[tmp["dispo_maj_fraicheur"] <= 24]
    tmp_frais = tmp_frais[tmp_frais['lit_capacite'] >= tmp_frais['lit_dispo']]

    recap = tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"]\
        .sum().reset_index(name = 'lit_capacite_tot')
    recap_frais = tmp_frais.groupby(["finess", "etablissement", "departement"], as_index = False)\
        [["lit_capacite", 'lit_dispo']].sum()
    recap = recap.merge(recap_frais, how = 'left', on = ["finess", "etablissement", "departement"])
    fraicheur = (tmp_frais.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum() \
        / tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum())\
        .reset_index(name = 'fraicheur')
    recap = recap.merge(fraicheur, how = 'left', on = ["finess", "etablissement", "departement"])
    recap["taux_occ"] = ((recap["lit_capacite"] - recap["lit_dispo"])\
                                / recap["lit_capacite"]).round(2)
    recap.loc[recap["fraicheur"].isnull(), "fraicheur"] = 0
    recap = recap.merge(loca_finess, on = "finess", how = "left")

    recap = recap[recap["fraicheur"] >= 0.7]
    capa_median = recap["lit_capacite"].median()
    recap = recap[recap["lit_capacite"] >= capa_median]


    top10 = recap.nlargest(10, ["taux_occ", "lit_capacite"]).\
        sort_values(['taux_occ', 'lit_capacite'], ascending = False)
    top10 = top10.reset_index(drop = True)
    top10 = top10[~top10["taux_occ"].isnull()]
    top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
    top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
    top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
    top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
    top10.loc[top10["ville"].isnull(), "ville"] = "NULL"
    # top10["etablissement"] = [row["etablissement"][0:53] for i, row in top10.iterrows()]
    # top10["ville"] = [row["ville"][0:53] for i, row in top10.iterrows()]



    pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
    table = pl_table.table
    table.columns[0].width = Cm(7.80)
    table.columns[1].width = Cm(4.69)
    table.columns[2].width = Cm(2.46)
    table.columns[3].width = Cm(2.46)

    for i in range(0, 11):
        for j in range(0, 4):
            cond = [j == 0, j == 1, j == 2, j == 3]
            choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
            choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
            col = str(np.select(cond, choices))
            label_col = str(np.select(cond, choices2))


            if i == 0:
                table.cell(i, j).fill.solid()
                table.cell(i, j).fill.fore_color.rgb = RGBColor(206, 147, 126)
                cell = table.rows[i].cells[j]
                paragraph = cell.text_frame.paragraphs[0]
                p = cell.text_frame.paragraphs[0]
                run = p.add_run()
                run.text = label_col
                paragraph.font.size = Pt(7)                
            elif i <= top10.shape[0]:
                text = top10.loc[i - 1, col]   
                table.cell(i, j).fill.solid()
                table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                cell = table.rows[i].cells[j]
                paragraph = cell.text_frame.paragraphs[0]
                p = cell.text_frame.paragraphs[0]
                run = p.add_run()
                run.text = text
                font_size = 6 if len(text) > 50 else 7
                paragraph.font.size = Pt(font_size)
                if (j == 2) | (j == 3):
                    paragraph.alignment = PP_ALIGN.RIGHT
            else:
                table.cell(i, j).fill.solid()
                table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                cell = table.rows[i].cells[j]
                paragraph = cell.text_frame.paragraphs[0]
                p = cell.text_frame.paragraphs[0]
                run = p.add_run()
                run.text = ""
                paragraph.font.size = Pt(7)

        table.rows[i].height = Cm(0.64)    

    #%% ------------------ Slides region -----------
    ordre_regions = np.array(ordre_regions)[~np.isin(ordre_regions, ['Île-de-France', 'Mayotte'])]

    for reg in ordre_regions:
        tri = list(mapping_regions.keys())[list(mapping_regions.values()).index(reg)]
        # --------------- Réanimation -----------------
        slide_layout = prs.slide_layouts[4]
        slide = prs.slides.add_slide(slide_layout)

        name_map = 'Picture Placeholder 3'
        name_barplot = 'Picture Placeholder 5'
        name_region = 'Text Placeholder 2'
        name_source1 = 'Text Placeholder 4'
        name_source2 = 'Text Placeholder 8'
        title_map = 'Text Placeholder 1'
        title_barplot = 'Text Placeholder 7'
        name_table = 'Table Placeholder 9'
        name_title_table = 'Text Placeholder 6'
        name_to = 'Text Placeholder 10' 


        i = 0
        for shape in slide.shapes:
            if shape.name == name_map:
                id_map = shape.placeholder_format.idx
            elif shape.name == name_barplot:
                id_barplot = shape.placeholder_format.idx
            elif shape.name == name_source1:
                id_source1 = i
            elif shape.name == name_source2:
                id_source2 = i
            elif shape.name == name_region:
                id_region = i
            elif shape.name == title_map:
                id_title_map = i
            elif shape.name == title_barplot:
                id_title_barplot = i
            elif shape.name == name_table:
                id_table = i
            elif shape.name == name_title_table:
                id_title_table = i
            elif shape.name == name_to:
                id_to = i


            i = i + 1

        # ------------- Graphiques ----------------
        if tri!="MAR" :
            slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_rea_{tri}.png')
        slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_rea_region_{tri}.png')
        # ------------- Zones de texte -------------
        shape = slide.shapes[id_source1]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"

        shape = slide.shapes[id_source2]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"

        shape = slide.shapes[id_region]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"{reg.upper()}"

        shape = slide.shapes[id_title_map]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Situation en {reg} au {PRINT_PPT_DATETIME}"

        shape = slide.shapes[id_title_barplot]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"{reg} du {start_date} au {PRINT_PPT_DATETIME}"

        rea_reg = recap_region[(recap_region["new_cate"] == "Réanimation") &\
                                (recap_region["region"] == tri)]

        if rea_reg.shape[0] != 0:
            to_reg = rea_reg["taux_occ"].iloc[0]
            if ~np.isnan(to_reg):
                to_reg = int(to_reg * 100)
            else:
                to_reg = ""
            fraicheur = rea_reg["fraicheur"].iloc[0]
        else:
            to_reg = ""
            fraicheur = np.nan

        shape = slide.shapes[id_to]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Taux d’occupation de la région à date : {to_reg}%"   
        if (fraicheur <= 0.7) | (np.isnan(fraicheur)):
                run.font.color.rgb = RGBColor(166, 166, 166)

        if tri == "REU": 
            word = 'à'
        else:
            word = 'en'
        shape = slide.shapes[id_title_table]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Liste des établissements {word} {reg} par taux d’occupation au {PRINT_PPT_DATETIME}"

        # ----------------- Tableau -------------
        tmp = df[(df["region"] == tri)]
        tmp_rea = tmp[tmp["new_cate"] == "Réanimation"]
        tmp_usc = tmp[tmp["new_cate"] == "Surveillance continue"]
        if np.isin(tri, ["GUY", "REU", "MAR"]):
            tmp_rea_frais = tmp_rea[tmp_rea["dispo_maj_fraicheur"] <= 48]
            tmp_usc_frais = tmp_usc[tmp_usc["dispo_maj_fraicheur"] <= 48]
        else:
            tmp_rea_frais = tmp_rea[tmp_rea["dispo_maj_fraicheur"] <= 24]
            tmp_usc_frais = tmp_usc[tmp_usc["dispo_maj_fraicheur"] <= 24]

        tmp_rea_frais = tmp_rea_frais[tmp_rea_frais['lit_capacite'] >= tmp_rea_frais['lit_dispo']]
        tmp_usc_frais = tmp_usc_frais[tmp_usc_frais['lit_capacite'] >= tmp_usc_frais['lit_dispo']]

        recap_rea = tmp_rea.groupby(["finess", "etablissement"])["lit_capacite"]\
            .sum().reset_index(name = 'lit_capacite_tot')
        recap_frais = tmp_rea_frais.groupby(["finess", "etablissement"], as_index = False)\
            [["lit_capacite", 'lit_dispo']].sum()
        recap_rea = recap_rea.merge(recap_frais, how = 'left', on = ["finess", "etablissement"])
        fraicheur = (tmp_rea_frais.groupby(["finess", "etablissement"])["lit_capacite"].sum() \
            / tmp_rea.groupby(["finess", "etablissement"])["lit_capacite"].sum())\
            .reset_index(name = 'fraicheur')
        recap_rea = recap_rea.merge(fraicheur, how = 'left', on = ["finess", "etablissement"])
        recap_rea["taux_occ"] = ((recap_rea["lit_capacite"] - recap_rea["lit_dispo"])\
                                    / recap_rea["lit_capacite"]).round(2)
        recap_rea.loc[recap_rea["fraicheur"].isnull(), "fraicheur"] = 0
        recap_rea = recap_rea.merge(loca_finess, on = "finess", how = "left")

        recap_rea = recap_rea[recap_rea["fraicheur"] >= 0.7]
        capa_median = recap_rea["lit_capacite"].median()
        recap_rea = recap_rea[recap_rea["lit_capacite"] >= capa_median]

        top10 = recap_rea.nlargest(10,[ "taux_occ", "lit_capacite"]).\
            sort_values(['taux_occ', 'lit_capacite'], ascending = False)
        top10 = top10.reset_index(drop = True)
        top10 = top10[~top10["taux_occ"].isnull()]
        top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
        top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
        top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
        top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
        top10.loc[top10["ville"].isnull(), "ville"] = "NULL"

        pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
        table = pl_table.table
        table.columns[0].width = Cm(7.80)
        table.columns[1].width = Cm(4.69)
        table.columns[2].width = Cm(2.46)
        table.columns[3].width = Cm(2.46)

        for i in range(0, 11):
            for j in range(0, 4):
                cond = [j == 0, j == 1, j == 2, j == 3]
                choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
                choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
                col = str(np.select(cond, choices))
                label_col = str(np.select(cond, choices2))

                if i == 0:
                    table.cell(i, j).fill.solid()
                    table.cell(i, j).fill.fore_color.rgb = RGBColor(183, 184, 217)
                    cell = table.rows[i].cells[j]
                    paragraph = cell.text_frame.paragraphs[0]
                    p = cell.text_frame.paragraphs[0]
                    run = p.add_run()
                    run.text = label_col
                    paragraph.font.size = Pt(7)    
                elif i <= top10.shape[0]:
                    text = top10.loc[i - 1, col]   
                    table.cell(i, j).fill.solid()
                    table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                    cell = table.rows[i].cells[j]
                    paragraph = cell.text_frame.paragraphs[0]
                    p = cell.text_frame.paragraphs[0]
                    run = p.add_run()
                    run.text = text
                    font_size = 6 if len(text) > 50 else 7
                    paragraph.font.size = Pt(font_size)

                    if (j == 2) | (j == 3):
                        paragraph.alignment = PP_ALIGN.RIGHT
                else:
                    table.cell(i, j).fill.solid()
                    table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                    cell = table.rows[i].cells[j]
                    paragraph = cell.text_frame.paragraphs[0]
                    p = cell.text_frame.paragraphs[0]
                    run = p.add_run()
                    run.text = ""
                    paragraph.font.size = Pt(7)

            table.rows[i].height = Cm(0.64)

        # --------------- Soins continues -----------------
        slide_layout = prs.slide_layouts[5]
        slide = prs.slides.add_slide(slide_layout)    
        #------------- Graphiques ----------------
        if tri!="MAR" :
            slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_usc_{tri}.png')
        slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_usc_region_{tri}.png')
        # ------------- Zones de texte -------------   
        shape = slide.shapes[id_source1]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"

        shape = slide.shapes[id_source2]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"

        shape = slide.shapes[id_region]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"{reg.upper()}"

        shape = slide.shapes[id_title_map]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Situation en {reg} au {PRINT_PPT_DATETIME}"

        shape = slide.shapes[id_title_barplot]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"{reg} du {start_date} au {PRINT_PPT_DATETIME}"

        usc_reg = recap_region[(recap_region["new_cate"] == "Surveillance continue") &\
                                (recap_region["region"] == tri)]

        if usc_reg.shape[0] != 0:
            to_reg = usc_reg["taux_occ"].iloc[0]
            if ~np.isnan(to_reg):
                to_reg = int(to_reg * 100)
            else:
                to_reg = ""
            fraicheur = usc_reg["fraicheur"].iloc[0]
        else:
            to_reg = ""
            fraicheur = np.nan

        shape = slide.shapes[id_to]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Taux d’occupation de la région à date : {to_reg}%"
        if fraicheur <= 0.7:
            run.font.color.rgb = RGBColor(166, 166, 166)

        if tri == "REU": 
            word = 'à'
        else:
            word = 'en'
        shape = slide.shapes[id_title_table]
        text_frame = shape.text_frame
        text_frame.clear()
        p = text_frame.paragraphs[0]
        run = p.add_run()
        run.text = f"Liste des établissements {word} {reg} par taux d’occupation au {PRINT_PPT_DATETIME}"

        # ----------------- Tableau -------------
        recap_usc = tmp_usc.groupby(["finess", "etablissement"])["lit_capacite"]\
            .sum().reset_index(name = 'lit_capacite_tot')
        recap_frais = tmp_usc_frais.groupby(["finess", "etablissement"], as_index = False)\
            [["lit_capacite", 'lit_dispo']].sum()
        recap_usc = recap_usc.merge(recap_frais, how = 'left', on = ["finess", "etablissement"])
        fraicheur = (tmp_usc_frais.groupby(["finess", "etablissement"])["lit_capacite"].sum() \
            / tmp_usc.groupby(["finess", "etablissement"])["lit_capacite"].sum())\
            .reset_index(name = 'fraicheur')
        recap_usc = recap_usc.merge(fraicheur, how = 'left', on = ["finess", "etablissement"])
        recap_usc["taux_occ"] = ((recap_usc["lit_capacite"] - recap_usc["lit_dispo"])\
                                    / recap_usc["lit_capacite"]).round(2)
        recap_usc.loc[recap_usc["fraicheur"].isnull(), "fraicheur"] = 0
        recap_usc = recap_usc.merge(loca_finess, on = "finess", how = "left")

        recap_usc = recap_usc[recap_usc["fraicheur"] >= 0.7]
        capa_median = recap_usc["lit_capacite"].median()
        recap_usc = recap_usc[recap_usc["lit_capacite"] >= capa_median]

        top10 = recap_usc.nlargest(10,[ "taux_occ", "lit_capacite"]).\
            sort_values(['taux_occ', 'lit_capacite'], ascending = False)
        top10 = top10.reset_index(drop = True)
        top10 = top10[~top10["taux_occ"].isnull()]
        top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
        top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
        top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
        top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
        top10.loc[top10["ville"].isnull(), "ville"] = "NULL"


        pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
        table = pl_table.table
        table.columns[0].width = Cm(7.80)
        table.columns[1].width = Cm(4.69)
        table.columns[2].width = Cm(2.46)
        table.columns[3].width = Cm(2.46)

        for i in range(0, 11):
            for j in range(0, 4):
                cond = [j == 0, j == 1, j == 2, j == 3]
                choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
                choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
                col = str(np.select(cond, choices))
                label_col = str(np.select(cond, choices2))


                if i == 0:
                    table.cell(i, j).fill.solid()
                    table.cell(i, j).fill.fore_color.rgb = RGBColor(206, 147, 126)
                    cell = table.rows[i].cells[j]
                    paragraph = cell.text_frame.paragraphs[0]
                    p = cell.text_frame.paragraphs[0]
                    run = p.add_run()
                    run.text = label_col
                    paragraph.font.size = Pt(7)                
                elif i <= top10.shape[0]:
                    text = top10.loc[i - 1, col]   
                    table.cell(i, j).fill.solid()
                    table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                    cell = table.rows[i].cells[j]
                    paragraph = cell.text_frame.paragraphs[0]
                    p = cell.text_frame.paragraphs[0]
                    run = p.add_run()
                    run.text = text
                    font_size = 6 if len(text) > 50 else 7
                    paragraph.font.size = Pt(font_size)
                    if (j == 2) | (j == 3):
                        paragraph.alignment = PP_ALIGN.RIGHT
                else:
                    table.cell(i, j).fill.solid()
                    table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
                    cell = table.rows[i].cells[j]
                    paragraph = cell.text_frame.paragraphs[0]
                    p = cell.text_frame.paragraphs[0]
                    run = p.add_run()
                    run.text = ""
                    paragraph.font.size = Pt(7)

            table.rows[i].height = Cm(0.64)

    # %% -------------------- Export ---------------------------

    if os.path.exists(f"{diffusion_dir}/{PREV_FILE_DATE}_TDB_Situation_hospitalière_bulletin_quotidien.pptx"):
        os.remove(f"{diffusion_dir}/{PREV_FILE_DATE}_TDB_Situation_hospitalière_bulletin_quotidien.pptx")

    prs.save(f'{diffusion_dir}/{FILE_DATE}_TDB_Situation_hospitalière_bulletin_quotidien.pptx')
    logger.success("Dashboard powerpoint enregistré")