Attribute VB_Name = "modControlesIDR9"
Option Explicit

'====================================================
' LANCEMENT GLOBAL DES CONTROLES
'====================================================

Public Sub LancerTousLesControlesIDR()

    Dim wsAno As Worksheet

    If Not SheetExistsIDR("ANOMALIES_HYPOTHESES_IDR") Then
        CreerOngletAnomaliesHypotheses
    End If

    Set wsAno = ThisWorkbook.Worksheets("ANOMALIES_HYPOTHESES_IDR")

    ' Nettoyage des anciennes anomalies
    wsAno.Rows("2:" & wsAno.Rows.Count).ClearContents

    ControlerHypothesesIDR

    ControlerTableMortalite

    MsgBox "Tous les contrôles sont terminés.", vbInformation

End Sub

'====================================================
' CONTROLE DES HYPOTHESES
'====================================================

Public Sub ControlerHypothesesIDR()

    Dim wsHyp As Worksheet
    Dim wsAno As Worksheet

    Dim lastRow As Long
    Dim i As Long

    If Not SheetExistsIDR("REF_HYPOTHESES_ACTUARIELLES") Then Exit Sub

    Set wsHyp = ThisWorkbook.Worksheets("REF_HYPOTHESES_ACTUARIELLES")
    Set wsAno = ThisWorkbook.Worksheets("ANOMALIES_HYPOTHESES_IDR")

    lastRow = wsHyp.Cells(wsHyp.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        If Trim(CStr(wsHyp.Cells(i, 1).value)) = "" Then

            AddAnomalieIDR _
                wsAno, _
                "REF_HYPOTHESES_ACTUARIELLES", _
                "HYP_001", _
                "", _
                "Code_Hypothese", _
                "", _
                "Bloquant", _
                "Code hypothèse absent"

        End If

        If Trim(CStr(wsHyp.Cells(i, 3).value)) = "" Then

            AddAnomalieIDR _
                wsAno, _
                "REF_HYPOTHESES_ACTUARIELLES", _
                "HYP_002", _
                CStr(wsHyp.Cells(i, 1).value), _
                "Parametre", _
                "", _
                "Bloquant", _
                "Paramètre absent"

        End If

        If Trim(CStr(wsHyp.Cells(i, 4).value)) = "" Then

            AddAnomalieIDR _
                wsAno, _
                "REF_HYPOTHESES_ACTUARIELLES", _
                "HYP_003", _
                CStr(wsHyp.Cells(i, 1).value), _
                "Valeur", _
                "", _
                "Bloquant", _
                "Valeur absente"

        End If

        If UCase(Trim(CStr(wsHyp.Cells(i, 7).value))) <> "OUI" Then

            AddAnomalieIDR _
                wsAno, _
                "REF_HYPOTHESES_ACTUARIELLES", _
                "HYP_030", _
                CStr(wsHyp.Cells(i, 1).value), _
                "Source_Certifiee", _
                wsHyp.Cells(i, 7).Text, _
                "Majeur", _
                "Source non certifiée"

        End If

        If IsDate(wsHyp.Cells(i, 9).value) Then

            If CDate(wsHyp.Cells(i, 9).value) < Date _
               And UCase(Trim(CStr(wsHyp.Cells(i, 10).value))) = "ACTIF" Then

                AddAnomalieIDR _
                    wsAno, _
                    "REF_HYPOTHESES_ACTUARIELLES", _
                    "HYP_021", _
                    CStr(wsHyp.Cells(i, 1).value), _
                    "Date_Fin", _
                    wsHyp.Cells(i, 9).Text, _
                    "Majeur", _
                    "Hypothèse active expirée"

            End If

        End If

    Next i

End Sub

'====================================================
' CONTROLE TABLE MORTALITE
'====================================================

Public Sub ControlerTableMortalite()

    Dim ws As Worksheet
    Dim wsAno As Worksheet

    Dim lastRow As Long
    Dim i As Long

    If Not SheetExistsIDR("TABLE_MORTALITE") Then Exit Sub

    Set ws = ThisWorkbook.Worksheets("TABLE_MORTALITE")
    Set wsAno = ThisWorkbook.Worksheets("ANOMALIES_HYPOTHESES_IDR")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        If Trim(CStr(ws.Cells(i, 1).value)) = "" Then

            AddAnomalieIDR _
                wsAno, _
                "TABLE_MORTALITE", _
                "MOR_001", _
                "Ligne " & i, _
                "Version_Table", _
                "", _
                "Bloquant", _
                "Version de table absente"

        End If

        If Not IsNumeric(ws.Cells(i, 3).value) Then

            AddAnomalieIDR _
                wsAno, _
                "TABLE_MORTALITE", _
                "MOR_002", _
                CStr(ws.Cells(i, 1).value), _
                "Age", _
                ws.Cells(i, 3).Text, _
                "Bloquant", _
                "Age absent ou non numérique"

        End If

        If IsNumeric(ws.Cells(i, 4).value) Then

            If ws.Cells(i, 4).value < 0 _
               Or ws.Cells(i, 4).value > 1 Then

                AddAnomalieIDR _
                    wsAno, _
                    "TABLE_MORTALITE", _
                    "MOR_004", _
                    CStr(ws.Cells(i, 1).value), _
                    "Homme_qx", _
                    ws.Cells(i, 4).Text, _
                    "Bloquant", _
                    "Probabilité décès homme invalide"

            End If

        End If

        If IsNumeric(ws.Cells(i, 5).value) Then

            If ws.Cells(i, 5).value < 0 _
               Or ws.Cells(i, 5).value > 1 Then

                AddAnomalieIDR _
                    wsAno, _
                    "TABLE_MORTALITE", _
                    "MOR_005", _
                    CStr(ws.Cells(i, 1).value), _
                    "Femme_qx", _
                    ws.Cells(i, 5).Text, _
                    "Bloquant", _
                    "Probabilité décès femme invalide"

            End If

        End If

        If Trim(CStr(ws.Cells(i, 9).value)) = "" Then

            AddAnomalieIDR _
                wsAno, _
                "TABLE_MORTALITE", _
                "MOR_013", _
                CStr(ws.Cells(i, 1).value), _
                "Source", _
                "", _
                "Majeur", _
                "Source absente"

        End If

    Next i

End Sub