data/dataframes.py

from typing import List
import pandas as pd


def merge_clients(data):
    mnr_df = data['MNR_Client']
    mnr_df['Prestataire'] = 'MNR'

    stm_df = data['STM_Client']
    stm_df['Prestataire'] = 'STM'

    valm_df = data['VALM_Client']
    valm_df['Prestataire'] = 'VALM'

    data['Clients'] = pd.concat([mnr_df, stm_df, valm_df], ignore_index=True)
    data['Clients'].rename(columns=dict(zip(data['Clients'].columns,['Code client', 'Type client', 'Nom client', 'Dist STE', 'Duration STE', 'Dist VAL', 'Duration VAL', 'Prestataire'])), inplace=True)
    data['Clients'] = data['Clients'][data['Clients']['Type client'] == 'Client']

    data['Clients']['Dist STE'] = data['Clients']['Dist STE'].fillna(0)
    data['Clients']['Duration STE'] = data['Clients']['Duration STE'].fillna(0)
    data['Clients']['Dist VAL'] = data['Clients']['Dist VAL'].fillna(0)
    data['Clients']['Duration VAL'] = data['Clients']['Duration VAL'].fillna(0)

    del data['MNR_Client']
    del data['STM_Client']
    del data['VALM_Client']


def add_motifs_absences(df: pd.DataFrame, motifs: List[str]):
    filtered_df = df[df['Nom client'].isin(['SECMI'])]
    intervenants = df['Code intervenant (Identifiant du document de référence -> Document de vente)'].unique()
    
    for intervenant in intervenants:
        rows = []
        for motif in motifs['Libellé GTP2023'].tolist():
            rows.append({
                'Code intervenant (Identifiant du document de référence -> Document de vente)': intervenant,
                'Code client': filtered_df.iloc[0]['Code client'] if filtered_df.shape[0] > 0 else 'SECMI',
                'Nom client': filtered_df.iloc[0]['Nom client'] if filtered_df.shape[0] > 0 else 'SECMI',
                'Code chantier': '000000',
                'Libellé': motif

            })
        df = pd.concat([df, pd.DataFrame(rows)], ignore_index=True)
    return df


def merge_affaires(data):
    mnr_df = data['MNR_Affaire']
    mnr_df = add_motifs_absences(mnr_df, data['absences'])
    mnr_df['Prestataire'] = 'MNR'

    stm_df = data['STM_Affaire']
    stm_df = add_motifs_absences(stm_df, data['absences'])
    stm_df['Prestataire'] = 'STM'

    valm_df = data['VALM_Affaire']
    valm_df = add_motifs_absences(valm_df, data['absences'])
    valm_df['Prestataire'] = 'VALM'

    data['affaire'] = pd.concat([mnr_df, stm_df, valm_df], ignore_index=True)
    data['affaire']['Code affaire'] = data['affaire']['Code chantier'] + ' - ' + data['affaire']['Libellé']
    del data['affaire']['Code chantier']
    del data['affaire']['Libellé']
    data['affaire'].rename(columns=dict(zip(data['affaire'].columns,['Code intervenant', 'Code client', 'Nom client', 'prestataire', 'Code affaire'])), inplace=True)
    del data['MNR_Affaire']
    del data['STM_Affaire']
    del data['VALM_Affaire']

def merge_intervenants_affaires(data):
    data['all'] = data['affaire'].merge(data['intervenants'], on='Code intervenant', how='left')
    data['all'].rename(columns=dict(zip(['Code intervenant', 'Code affaire', 'Nom client', 'Code secteur géographique', 'Veh_Affecte', 'Durée Hebdo', 'Coût horaire'],['intervenant', 'affaire', 'client', 'secteur', 'vehicule affecte', 'contrat heures', 'cout horaire'])), inplace=True)
    new_row  = pd.DataFrame([['-'] * (data['all'].shape[1])], columns=data['all'].columns)
    data['all'] = pd.concat([new_row, data['all']], ignore_index=True)
    data['all']['secteur'] = data['all']['secteur'].fillna('STE')
    data['all']['contrat heures'] = data['all']['contrat heures'].fillna(0)
    data['all']['cout horaire'] = data['all']['cout horaire'].fillna(0)
    data['all'] = data['all'].dropna(subset=['intervenant', 'client', 'affaire'])

def complete_vehicules(data):
    data['vehicules']['vehicules'] = 'Collectif - ' + data['vehicules']['IMMAT'] + ' - ' + data['vehicules']['Description']
    data['vehicules'].loc[len(data['vehicules'])] = [None, None, 'Perso']
    data['vehicules'].loc[len(data['vehicules'])] = [None, None, 'Service affecté']

def complete_supplements(data):
    data['supplements'].rename(columns=dict(zip(data['supplements'].columns,['supplements'])), inplace=True)
    data['supplements']['supplements'] = data['supplements']['supplements'].str.replace('_x000D_', ' ')
    new_row  = pd.DataFrame([['-'] * (data['supplements'].shape[1])], columns=data['supplements'].columns)
    data['supplements'] = pd.concat([new_row, data['supplements']], ignore_index=True)
    data['supplements'] = data['supplements'][~data['supplements']['supplements'].isin([' H Peinture ', ' H  Qualif. Électrique ', ' H Soudure '])]

def complete_affaires(data):
    data['affaire'].rename(columns=dict(zip(['Code intervenant', 'Code affaire', 'Nom client'],['intervenant', 'affaire', 'client'])), inplace=True)
    new_row  = pd.DataFrame([['-'] * (data['affaire'].shape[1])], columns=data['affaire'].columns)
    data['affaire'] = pd.concat([new_row, data['affaire']], ignore_index=True)

def complete_intervenants(data):
    data['intervenants'].rename(columns=dict(zip(['Code intervenant',  'Code secteur géographique', 'Veh_Affecte', 'Durée Hebdo', 'Coût horaire'],['intervenant', 'secteur', 'vehicule affecte', 'contrat heures', 'cout horaire'])), inplace=True)
    data['intervenants']['Employeur'] = data['intervenants']['Employeur'].apply(lambda x: x.split()[1] if pd.notna(x) else None)
    data['intervenants']['Employeur'] = data['intervenants']['Employeur'].fillna('SECMI')
    new_row  = pd.DataFrame([['-'] * (data['intervenants'].shape[1])], columns=data['intervenants'].columns)
    data['intervenants'] = pd.concat([new_row, data['intervenants']], ignore_index=True)
    #TODO to remove, for test only. Fill with 0
    data['intervenants']['contrat heures'] = data['intervenants']['contrat heures'].fillna(35)

def complete_arrets(data):
    data['arrets'].rename(columns=dict(zip(data['arrets'].columns,['code_absence', 'H-35.0', 'H-37.0', 'H-37.5', 'H-39.0'])), inplace=True)
    data['arrets']['code_absence'] = data['arrets']['code_absence'].str.replace('_x000D_', ' ')
    data['arrets'].fillna(0, inplace=True)

def cast_specifics_to_str(data):
    data['all'] = data['all'].astype({'intervenant': str, 'affaire': str, 'client': str, 'prestataire': str})
    data['vehicules'] = data['vehicules'].astype({'IMMAT': str, 'Description': str})
    # print(data['affaire'])
    data['affaire'] = data['affaire'].astype({'intervenant': str, 'affaire': str, 'client': str, 'prestataire': str})
    data['intervenants'] = data['intervenants'].astype({'intervenant': str, 'secteur': str, 'vehicule affecte': str})