Attribute VB_Name = "modReferentielsIDR10"
Option Explicit

'====================================================
' CONTROLES HYPOTHESES
'====================================================

Public Sub CreerOngletControlesHypotheses()

    Dim ws As Worksheet

    Set ws = ResetSheetIDR("CONTROLES_HYPOTHESES_IDR")

    ws.Cells(1, 1).value = "Code_Controle"
    ws.Cells(1, 2).value = "Famille"
    ws.Cells(1, 3).value = "Champ_Controle"
    ws.Cells(1, 4).value = "Regle"
    ws.Cells(1, 5).value = "Gravite"
    ws.Cells(1, 6).value = "Message_Anomalie"
    ws.Cells(1, 7).value = "Statut"

    AjouterControle ws, 2, "HYP_001", "Complétude", "Code_Hypothese", "Non vide", "Bloquant", "Code hypothèse absent"
    AjouterControle ws, 3, "HYP_002", "Complétude", "Parametre", "Non vide", "Bloquant", "Paramètre absent"
    AjouterControle ws, 4, "HYP_003", "Complétude", "Valeur", "Non vide", "Bloquant", "Valeur absente"
    AjouterControle ws, 5, "HYP_005", "Source", "Code_Source", "Existe dans REFERENTIEL_SOURCES_CERTIFIEES", "Bloquant", "Source inexistante"
    AjouterControle ws, 6, "HYP_010", "Cohérence", "Taux actualisation", "> 0", "Bloquant", "Taux actualisation invalide"
    AjouterControle ws, 7, "HYP_014", "Cohérence", "Charges patronales", "Entre 0 et 100", "Bloquant", "Taux charges invalide"
    AjouterControle ws, 8, "HYP_018", "Référence", "Table mortalité", "Existe dans TABLE_MORTALITE", "Bloquant", "Table mortalité inexistante"
    AjouterControle ws, 9, "HYP_021", "Validité", "Statut", "ACTIF non expiré", "Majeur", "Hypothèse active expirée"
    AjouterControle ws, 10, "HYP_030", "Source", "Source_Certifiee", "OUI", "Majeur", "Source non certifiée"

    FormatReferentielIDR ws, "A1:G1"

End Sub

'====================================================
' CONTROLES TABLE MORTALITE
'====================================================

Public Sub CreerOngletControlesTableMortalite()

    Dim ws As Worksheet

    Set ws = ResetSheetIDR("CONTROLES_TABLE_MORTALITE")

    ws.Cells(1, 1).value = "Code_Controle"
    ws.Cells(1, 2).value = "Type_Controle"
    ws.Cells(1, 3).value = "Champ_Controle"
    ws.Cells(1, 4).value = "Regle"
    ws.Cells(1, 5).value = "Gravite"
    ws.Cells(1, 6).value = "Message_Anomalie"
    ws.Cells(1, 7).value = "Statut"

    AjouterControle ws, 2, "MOR_001", "Complétude", "Version_Table", "Non vide", "Bloquant", "Version de table absente"
    AjouterControle ws, 3, "MOR_002", "Complétude", "Age", "Non vide", "Bloquant", "Age absent"
    AjouterControle ws, 4, "MOR_003", "Cohérence", "Age", "Entre 0 et 120", "Bloquant", "Age hors plage"
    AjouterControle ws, 5, "MOR_004", "Cohérence", "Homme_qx", "Entre 0 et 1", "Bloquant", "Probabilité décès homme invalide"
    AjouterControle ws, 6, "MOR_005", "Cohérence", "Femme_qx", "Entre 0 et 1", "Bloquant", "Probabilité décès femme invalide"
    AjouterControle ws, 7, "MOR_006", "Cohérence", "Homme_px", "Entre 0 et 1", "Bloquant", "Probabilité survie homme invalide"
    AjouterControle ws, 8, "MOR_007", "Cohérence", "Femme_px", "Entre 0 et 1", "Bloquant", "Probabilité survie femme invalide"
    AjouterControle ws, 9, "MOR_008", "Calcul", "Homme_px", "Homme_px = 1 - Homme_qx", "Majeur", "Incohérence qx/px homme"
    AjouterControle ws, 10, "MOR_009", "Calcul", "Femme_px", "Femme_px = 1 - Femme_qx", "Majeur", "Incohérence qx/px femme"
    AjouterControle ws, 11, "MOR_010", "Doublon", "Version_Table + Age", "Unique", "Majeur", "Doublon âge/version"
    AjouterControle ws, 12, "MOR_011", "Statut", "Statut", "ACTIF ou OBSOLETE", "Majeur", "Statut invalide"

    FormatReferentielIDR ws, "A1:G1"

End Sub

'====================================================
' ANOMALIES
'====================================================

Public Sub CreerOngletAnomaliesHypotheses()

    Dim ws As Worksheet

    Set ws = ResetSheetIDR("ANOMALIES_HYPOTHESES_IDR")

    ws.Cells(1, 1).value = "ID_Anomalie"
    ws.Cells(1, 2).value = "Date_Controle"
    ws.Cells(1, 3).value = "Onglet_Source"
    ws.Cells(1, 4).value = "Code_Controle"
    ws.Cells(1, 5).value = "Code_Element"
    ws.Cells(1, 6).value = "Champ"
    ws.Cells(1, 7).value = "Valeur"
    ws.Cells(1, 8).value = "Gravite"
    ws.Cells(1, 9).value = "Message"
    ws.Cells(1, 10).value = "Statut"

    FormatReferentielIDR ws, "A1:J1"

End Sub

'====================================================
' KPI
'====================================================

Public Sub CreerOngletKPIHypothesesIDR()

    Dim ws As Worksheet

    Set ws = ResetSheetIDR("KPI_HYPOTHESES_IDR")

    ws.Cells(1, 1).value = "Bloc"
    ws.Cells(1, 2).value = "KPI"
    ws.Cells(1, 3).value = "Formule"
    ws.Cells(1, 4).value = "Valeur"

    ws.Cells(2, 1).value = "Qualité"
    ws.Cells(2, 2).value = "Nombre hypothèses actives"

    ws.Cells(3, 1).value = "Qualité"
    ws.Cells(3, 2).value = "Hypothèses expirées"

    ws.Cells(4, 1).value = "Sources"
    ws.Cells(4, 2).value = "Taux sources certifiées"

    ws.Cells(5, 1).value = "Contrôles"
    ws.Cells(5, 2).value = "Anomalies bloquantes"

    ws.Cells(6, 1).value = "Mortalité"
    ws.Cells(6, 2).value = "Table mortalité active"

    FormatReferentielIDR ws, "A1:D1"

End Sub

'====================================================
' DASHBOARD
'====================================================

Public Sub CreerOngletDashboardHypothesesIDRVide()

    Dim ws As Worksheet

    Set ws = ResetSheetIDR("DASHBOARD_HYPOTHESES_IDR")

    ws.Cells(1, 1).value = "Dashboard à construire"

    FormatReferentielIDR ws, "A1:A1"

End Sub

'====================================================
' OUTIL INTERNE
'====================================================

Private Sub AjouterControle( _
    ByVal ws As Worksheet, _
    ByVal ligne As Long, _
    ByVal CodeControle As String, _
    ByVal TypeControle As String, _
    ByVal Champ As String, _
    ByVal Regle As String, _
    ByVal Gravite As String, _
    ByVal Message As String)

    ws.Cells(ligne, 1).value = CodeControle
    ws.Cells(ligne, 2).value = TypeControle
    ws.Cells(ligne, 3).value = Champ
    ws.Cells(ligne, 4).value = Regle
    ws.Cells(ligne, 5).value = Gravite
    ws.Cells(ligne, 6).value = Message
    ws.Cells(ligne, 7).value = "ACTIF"

End Sub