Attribute VB_Name = "modBaremesIDR5"
Option Explicit

Public Sub CreerBaremesIDR()
    Dim ws As Worksheet
    Set ws = ResetSheetIDR("BAREMES_IDR")

    EcrireEntetesBaremes ws
    AjouterBareme ws, 2, 1, "ACCORD", "ACCORD_GROUPE", "TOUS", 10, 15, 1
    AjouterBareme ws, 3, 1, "ACCORD", "ACCORD_GROUPE", "TOUS", 15, 20, 2
    AjouterBareme ws, 4, 1, "ACCORD", "ACCORD_GROUPE", "TOUS", 20, 30, 3
    AjouterBareme ws, 5, 1, "ACCORD", "ACCORD_GROUPE", "TOUS", 30, 999, 4
    AjouterBareme ws, 6, 2, "CONVENTION", "SYNTEC", "CADRE", 10, 15, 0.5
    AjouterBareme ws, 7, 2, "CONVENTION", "SYNTEC", "CADRE", 15, 20, 1
    AjouterBareme ws, 8, 2, "CONVENTION", "SYNTEC", "CADRE", 20, 30, 1.5
    AjouterBareme ws, 9, 2, "CONVENTION", "SYNTEC", "CADRE", 30, 999, 2
    AjouterBareme ws, 10, 2, "CONVENTION", "BTP", "TOUS", 10, 15, 1
    AjouterBareme ws, 11, 2, "CONVENTION", "BTP", "TOUS", 15, 20, 1.5
    AjouterBareme ws, 12, 2, "CONVENTION", "BTP", "TOUS", 20, 30, 2
    AjouterBareme ws, 13, 2, "CONVENTION", "BTP", "TOUS", 30, 999, 3
    AjouterBareme ws, 14, 3, "LEGAL", "LEGAL", "TOUS", 10, 15, 0.5
    AjouterBareme ws, 15, 3, "LEGAL", "LEGAL", "TOUS", 15, 20, 1
    AjouterBareme ws, 16, 3, "LEGAL", "LEGAL", "TOUS", 20, 30, 1.5
    AjouterBareme ws, 17, 3, "LEGAL", "LEGAL", "TOUS", 30, 999, 2

    FormatReferentielIDR ws, "A1:G1"
    MsgBox "BAREMES_IDR cree.", vbInformation
End Sub

Private Sub EcrireEntetesBaremes(ByVal ws As Worksheet)
    ws.Cells(1, 1).value = "Priorite"
    ws.Cells(1, 2).value = "Type_Regle"
    ws.Cells(1, 3).value = "Regle"
    ws.Cells(1, 4).value = "CSP"
    ws.Cells(1, 5).value = "Anciennete_Min"
    ws.Cells(1, 6).value = "Anciennete_Max"
    ws.Cells(1, 7).value = "Mois_Indemnite"
End Sub

Private Sub AjouterBareme(ByVal ws As Worksheet, ByVal r As Long, ByVal priorite As Long, ByVal typeRegle As String, ByVal Regle As String, ByVal csp As String, ByVal ancienneteMin As Double, ByVal ancienneteMax As Double, ByVal mois As Double)
    ws.Cells(r, 1).value = priorite
    ws.Cells(r, 2).value = typeRegle
    ws.Cells(r, 3).value = Regle
    ws.Cells(r, 4).value = csp
    ws.Cells(r, 5).value = ancienneteMin
    ws.Cells(r, 6).value = ancienneteMax
    ws.Cells(r, 7).value = mois
End Sub

Public Function GetMoisIndemniteIDR(ByVal accordEntreprise As String, ByVal convention As String, ByVal csp As String, ByVal ancienneteProjetee As Double) As Double
    Dim mois As Double
    mois = ChercherBaremeIDR("ACCORD", accordEntreprise, csp, ancienneteProjetee)
    If mois >= 0 Then
        GetMoisIndemniteIDR = mois
        Exit Function
    End If

    mois = ChercherBaremeIDR("CONVENTION", convention, csp, ancienneteProjetee)
    If mois >= 0 Then
        GetMoisIndemniteIDR = mois
        Exit Function
    End If

    mois = ChercherBaremeIDR("LEGAL", "LEGAL", "TOUS", ancienneteProjetee)
    If mois >= 0 Then
        GetMoisIndemniteIDR = mois
    Else
        GetMoisIndemniteIDR = 0
    End If
End Function

Private Function ChercherBaremeIDR(ByVal typeRegle As String, ByVal regleRecherchee As String, ByVal cspRecherche As String, ByVal ancienneteProjetee As Double) As Double
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim typeLigne As String
    Dim regleLigne As String
    Dim cspLigne As String

    ChercherBaremeIDR = -1
    If Not SheetExistsIDR("BAREMES_IDR") Then Exit Function
    If Trim(regleRecherchee) = "" Or UCase(Trim(regleRecherchee)) = "AUCUN" Then Exit Function

    Set ws = Worksheets("BAREMES_IDR")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        typeLigne = UCase(Trim(CStr(ws.Cells(i, 2).value)))
        regleLigne = UCase(Trim(CStr(ws.Cells(i, 3).value)))
        cspLigne = UCase(Trim(CStr(ws.Cells(i, 4).value)))

        If typeLigne = UCase(typeRegle) And regleLigne = UCase(Trim(regleRecherchee)) Then
            If cspLigne = "TOUS" Or cspLigne = UCase(Trim(cspRecherche)) Then
                If ancienneteProjetee >= ToDoubleIDR(ws.Cells(i, 5).value) And ancienneteProjetee < ToDoubleIDR(ws.Cells(i, 6).value) Then
                    ChercherBaremeIDR = ToDoubleIDR(ws.Cells(i, 7).value)
                    Exit Function
                End If
            End If
        End If
    Next i
End Function