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 102 103 104 105 106 107 108 109
| Public Sub UpDatePlanning()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim rs4 As DAO.Recordset
Dim IdGroupe As Long
Dim IdPlanning As Long
Dim idStag As Long
Dim LeSQL As String
Dim r As Long
Dim n As Long
Dim dt As Date
Dim ip As Long
Set db = CurrentDb
Set rs1 = db.OpenRecordset("select * from T_Planning where Not IsNull(DateJour) order By IdGroupe, DateJour;")
Set rs3 = db.OpenRecordset("T_Planning", dbOpenDynaset)
Set rs4 = db.OpenRecordset("T_Presence", dbOpenDynaset)
Do Until rs1.EOF
IdGroupe = rs1!IdGroupe
ip = rs1!IdPlanning
dt = rs1!DateJour
n = 10
r = 7
rs1.MoveNext
If Not rs1.EOF Then
Do While (IdGroupe = rs1!IdGroupe) ' on boucle sur les cours.
dt = rs1!DateJour
If Not (rs1!Report) Then
n = n - 1 'on enlève une récurrence au total.
End If
rs1.MoveNext
If (rs1.EOF) Or (n = 0) Then ' sortie.
Exit Do
End If
Loop
End If
dt = dt + r
Do While (n > 0) ' On boucle sur les récurrences restantes à ajouter dans la table.
If (Not EstFerie(dt)) And Not (Weekday(dt) = 1) Then
n = n - 1
' Ajout de l'intervention de même nature à cette date.
rs3.AddNew
rs3!IdGroupe = IdGroupe
rs3!DateJour = dt
rs3.Update
rs3.MoveLast
IdPlanning = rs3!IdPlanning
Set rs2 = db.OpenRecordset("select * from T_Presence where RefPlanning=" & ip & " order by RefStag;")
Do Until rs2.EOF
rs4.AddNew
rs4!RefPlanning = IdPlanning
rs4!RefStag = rs2!RefStag
rs4.Update
rs2.MoveNext
Loop
dt = dt + r ' date de la prochaine récurrence.
Else
dt = dt + r ' jour suivant.
End If
Loop
Loop
rs1.Close
rs2.Close
rs3.Close
rs4.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set rs4 = Nothing
db.Close
Set db = Nothing
End Sub |
Partager