Attribute VB_Name = "modTableMortaliteIDR4"
Option Explicit

Public Sub CreerTableMortalite()

    Dim ws As Worksheet

    Set ws = ResetSheetIDR("TABLE_MORTALITE")

    ws.Cells(1, 1).value = "Version_Table"
    ws.Cells(1, 2).value = "Type_Table"
    ws.Cells(1, 3).value = "Age"
    ws.Cells(1, 4).value = "Homme_qx"
    ws.Cells(1, 5).value = "Femme_qx"
    ws.Cells(1, 6).value = "Homme_px"
    ws.Cells(1, 7).value = "Femme_px"
    ws.Cells(1, 8).value = "Date_Effet"
    ws.Cells(1, 9).value = "Source"
    ws.Cells(1, 10).value = "Statut"

    FormatReferentielIDR ws, "A1:J1"

End Sub

Public Sub ChargerTableMortaliteDemo()

    Dim ws As Worksheet
    Dim age As Long
    Dim r As Long

    If Not SheetExistsIDR("TABLE_MORTALITE") Then
        CreerTableMortalite
    End If

    Set ws = Worksheets("TABLE_MORTALITE")

    ' On conserve les en-têtes et on vide les anciennes lignes
    ws.Rows("2:" & ws.Rows.Count).ClearContents

    r = 2

    For age = 18 To 100

        ws.Cells(r, 1).value = "DEMO"
        ws.Cells(r, 2).value = "TEST"
        ws.Cells(r, 3).value = age

        ws.Cells(r, 4).value = 0.001 + ((age - 18) * 0.0001)
        ws.Cells(r, 5).value = 0.0008 + ((age - 18) * 0.00008)

        ws.Cells(r, 6).value = 1 - ws.Cells(r, 4).value
        ws.Cells(r, 7).value = 1 - ws.Cells(r, 5).value

        ws.Cells(r, 8).value = DateSerial(2026, 1, 1)
        ws.Cells(r, 9).value = "SRC007"
        ws.Cells(r, 10).value = "ACTIVE"

        r = r + 1

    Next age

    ws.Range("D:G").NumberFormat = "0.00000"

    FormatReferentielIDR ws, "A1:J1"

    MsgBox "TABLE_MORTALITE chargée avec la table de démonstration.", vbInformation

End Sub

Public Sub ImporterTableMortaliteCSV()

    Dim ws As Worksheet
    Dim fichierCSV As Variant
    Dim qt As QueryTable

    Set ws = ResetSheetIDR("TABLE_MORTALITE")

    fichierCSV = Application.GetOpenFilename( _
        "Fichiers CSV (*.csv),*.csv")

    If fichierCSV = False Then Exit Sub

    Set qt = ws.QueryTables.Add( _
        Connection:="TEXT;" & fichierCSV, _
        Destination:=ws.Range("A1"))

    With qt
        .TextFileParseType = xlDelimited
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFilePlatform = 65001
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
    End With

    ws.Columns.AutoFit

    If Not VerifierStructureTableMortalite(ws) Then
        MsgBox "Import annulé : la structure du CSV TGH/TGF n'est pas conforme.", vbCritical
        Exit Sub
    End If

    VerifierCoherenceTableMortalite

    MsgBox "Table TGH/TGF importée dans TABLE_MORTALITE.", vbInformation

End Sub

Private Function VerifierStructureTableMortalite(ByVal ws As Worksheet) As Boolean

    VerifierStructureTableMortalite = False

    If GetColumnByHeaderIDR(ws, "Version_Table") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Type_Table") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Age") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Homme_qx") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Femme_qx") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Homme_px") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Femme_px") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Date_Effet") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Source") = 0 Then Exit Function
    If GetColumnByHeaderIDR(ws, "Statut") = 0 Then Exit Function

    VerifierStructureTableMortalite = True

End Function

Public Sub VerifierCoherenceTableMortalite()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Dim qxH As Double
    Dim qxF As Double
    Dim pxH As Double
    Dim pxF As Double

    Set ws = Worksheets("TABLE_MORTALITE")

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

    If lastRow < 2 Then
        MsgBox "TABLE_MORTALITE ne contient aucune donnée.", vbExclamation
        Exit Sub
    End If

    For i = 2 To lastRow

        If Not IsNumeric(ws.Cells(i, 3).value) Then
            MsgBox "Age non numérique ligne " & i, vbCritical
            Exit Sub
        End If

        If Not IsNumeric(ws.Cells(i, 4).value) Then
            MsgBox "Homme_qx non numérique ligne " & i, vbCritical
            Exit Sub
        End If

        If Not IsNumeric(ws.Cells(i, 5).value) Then
            MsgBox "Femme_qx non numérique ligne " & i, vbCritical
            Exit Sub
        End If

        If Not IsNumeric(ws.Cells(i, 6).value) Then
            MsgBox "Homme_px non numérique ligne " & i, vbCritical
            Exit Sub
        End If

        If Not IsNumeric(ws.Cells(i, 7).value) Then
            MsgBox "Femme_px non numérique ligne " & i, vbCritical
            Exit Sub
        End If

        qxH = CDbl(ws.Cells(i, 4).value)
        qxF = CDbl(ws.Cells(i, 5).value)
        pxH = CDbl(ws.