1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
|
Option Compare Database
Option Explicit
Sub CreerCalendrier(ByVal pnMachineCE As Long, ByVal pdDebut As Date, ByVal pdFin As Date, ByVal pnFrequence As Integer)
Dim vdMaDate As Date 'Déclaration des variables
Dim NbJ As Integer
vdMaDate = pdDebut 'Récupère la date du premier entretien
While JourNT(vdMaDate) 'Tant que ma date est un jour non ouvré
vdMaDate = vdMaDate + 1
Wend
While vdMaDate <= pdFin 'boucle jusqu'a la date de fin de l'entretien ou du calcul du planning
CurrentDb.Execute "Insert Into Intervention (CE_Machine,Date_Intervention) Values (" & pnMachineCE & ",#" & Format(vdMaDate, "mm/dd/yyyy") & "#)"
NbJ = 1
While NbJ <= pnFrequence 'Tant que le jour sur lequel on tombe est non ouvré
vdMaDate = vdMaDate + 1 'On rajoute un jour
If Not JourNT(vdMaDate) Then NbJ = NbJ + 1 'Si on tombe sur un jour ouvré, on arrête
Wend
Wend
End Sub
Sub CreerCalendrierTous()
Dim MesInter As Recordset
Set MesInter = CurrentDb.OpenRecordset("R1", dbOpenSnapshot) 'Utilisation requête
With MesInter
While Not .EOF 'Tant qu'il n'y a pas de dates (Table entretien vide pour un point)
CreerCalendrier !CE_Machine, !DD_Intervention, #12/31/2040#, !Recurrence
.MoveNext 'Point suivant
Wend
.Close 'Une fois que tous les points ont au mois une date d'entretien
End With
MsgBox "Génération du calendrier terminé !", vbInformation
End Sub
Public Function JourNT(ByVal pdDate As Date) As Boolean 'Fonction Oui/Non
Select Case Format(pdDate, "w", vbMonday) 'Définition du Format
Case 7 ' dimanche
JourNT = True
Case Else 'Autres jour de la semaine
If Ferier(pdDate) Then 'Regroupe les jours fériés dans cette fonction
JourNT = True
Else
JourNT = False
End If
End Select
End Function
Public Function Ferier(ByVal DateX As Date) As Boolean
Dim anneeDate As Integer
Dim joursFeries(1 To 11) As Date
Dim i As Integer
anneeDate = Year(DateX)
joursFeries(1) = DateSerial(anneeDate, 1, 1)
joursFeries(2) = DateSerial(anneeDate, 5, 1)
joursFeries(3) = DateSerial(anneeDate, 5, 8)
joursFeries(4) = DateSerial(anneeDate, 7, 14)
joursFeries(5) = DateSerial(anneeDate, 8, 15)
joursFeries(6) = DateSerial(anneeDate, 11, 1)
joursFeries(7) = DateSerial(anneeDate, 11, 11)
joursFeries(8) = DateSerial(anneeDate, 12, 25)
joursFeries(9) = fLundiPaques(anneeDate)
joursFeries(10) = joursFeries(9) + 38 ' Ascension = lundi de Pâques + 38
joursFeries(11) = joursFeries(9) + 49 ' Lundi de pentecote = lundi de Pâques + 49
For i = 1 To 11
If DateX = joursFeries(i) Then
Ferier = True
Exit For
End If
Next
End Function
Private Function fLundiPaques(ByVal Iyear As Integer) As Date
Dim l(6) As Long, Lj As Long, Lm As Long
l(1) = Iyear Mod 19: l(2) = Iyear Mod 4: l(3) = Iyear Mod 7
l(4) = (19 * l(1) + 24) Mod 30
l(5) = ((2 * l(2)) + (4 * l(3)) + (6 * l(4)) + 5) Mod 7
l(6) = 22 + l(4) + l(5)
If l(6) > 31 Then
Lj = l(6) - 31
Lm = 4
Else
Lj = l(6)
Lm = 3
End If
' Lundi de Pâques = Pâques + 1 jour
fLundiPaques = DateAdd("d", 1, (Lj & "/" & Lm & "/" & Iyear))
End Function |
Partager