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
|
Private Sub RecupResult(ByRef Resultat As DataTable, ByVal SemDate As Date)
Debug.Print("Début de requête : " & System.DateTime.Now.ToLongTimeString & ":" & System.DateTime.Now.Millisecond)
Dim sSQL As String
Dim rs As New Recordset
Dim Adapt As New OleDbDataAdapter
'Pour i variant sur toute la semaine courante
For I As Integer = -SemDate.DayOfWeek + 1 To -SemDate.DayOfWeek + 5
'On récupère (pour chaque département) le nombre de valeur ajoutée à 1 pour le jour donné
sSQL = "SELECT "
For j As Integer = 1 To DGV_STATUS.Columns.Count - 1
If j = DGV_STATUS.Columns.Count - 1 Then
sSQL += "COUNT(R" & j & "1" & ".RES_NoRes), COUNT (R" & j & "2" & ".RES_NoRes) "
Else
sSQL += "COUNT(R" & j & "1" & ".RES_NoRes), COUNT (R" & j & "2" & ".RES_NoRes), "
End If
Next
sSQL += "FROM "
For j As Integer = 1 To DGV_STATUS.Columns.Count - 1
If j = DGV_STATUS.Columns.Count - 1 Then
sSQL += "RESULTAT R" & j & "1, RESULTAT R" & j & "2" & ", EMPLOYE E" & j & "1, EMPLOYE E" & j & "2 "
Else
sSQL += "RESULTAT R" & j & "1, RESULTAT R" & j & "2" & ", EMPLOYE E" & j & "1, EMPLOYE E" & j & "2, "
End If
Next
sSQL += "WHERE "
For j As Integer = 1 To DGV_STATUS.Columns.Count - 1
If j = DGV_STATUS.Columns.Count - 1 Then
sSQL += "R" & j & "1" & ".RES_NoEmp = E" & j & "1.EMP_NoEmp" & " AND E" & j & "1.EMP_NumDep = " & DGV_STATUS.Columns(j).ToolTipText & " AND FORMAT(R" & j & "1" & ".RES_DateRes, ""yyyy/mm/dd"") LIKE '" & SemDate.AddDays(I).ToString("yyyy/MM/dd") & "' AND R" & j & "1" & ".RES_IsVA = 1 AND " & _
"R" & j & "2" & ".RES_NoEmp = E" & j & "2.EMP_NoEmp" & " AND E" & j & "2.EMP_NumDep = " & DGV_STATUS.Columns(j).ToolTipText & " AND FORMAT(R" & j & "1" & ".RES_DateRes, ""yyyy/mm/dd"") LIKE '" & SemDate.AddDays(I).ToString("yyyy/MM/dd") & "'"
Else
sSQL += "R" & j & "1" & ".RES_NoEmp = E" & j & "1.EMP_NoEmp" & " AND E" & j & "1.EMP_NumDep = " & DGV_STATUS.Columns(j).ToolTipText & " AND FORMAT(R" & j & "1" & ".RES_DateRes, ""yyyy/mm/dd"") LIKE '" & SemDate.AddDays(I).ToString("yyyy/MM/dd") & "' AND R" & j & "1" & ".RES_IsVA = 1 AND " & _
"R" & j & "2" & ".RES_NoEmp = E" & j & "2.EMP_NoEmp" & " AND E" & j & "2.EMP_NumDep = " & DGV_STATUS.Columns(j).ToolTipText & " AND FORMAT(R" & j & "1" & ".RES_DateRes, ""yyyy/mm/dd"") LIKE '" & SemDate.AddDays(I).ToString("yyyy/MM/dd") & "' AND "
End If
Next
Debug.Print(sSQL)
rs.Open(sSQL, conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, -1)
Adapt.Fill(Resultat, rs)
rs.Close()
Next
Debug.Print("Fin de requête : " & System.DateTime.Now.ToLongTimeString & ":" & System.DateTime.Now.Millisecond)
End Sub |
Partager