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
|
Dim lstAgentsXml As XmlNodeList
Dim agentXml As XmlNode
Dim periodeXml As XmlNode
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim idAgent As Integer
Dim codeMa As String
Dim codeActivite As String
Dim idjour As Integer
Dim enregistrementTrouve As Boolean = False
Dim ds As DataSet = New DataSet
Dim table As DataTable
Dim row As DataRow
Dim connexionStr As String = ConfigurationSettings.AppSettings("ConnectionString")
Dim requete As String
Dim connection As SqlConnection = New SqlConnection(connexionStr)
connection.Open()
requete = "SELECT P.id_agent, P.id_antenne,"
requete = requete & "P.TableTemps_ID,"
requete = requete & "P.id_ma, P.id_activite"
requete = requete & " FROM PLNG_ANTENNE P JOIN AGENT A ON A.ID_AGENT=P.ID_AGENT "
requete = requete & " LEFT OUTER JOIN TableTemps T ON P.TableTemps_ID=T.TableTemps_ID "
requete = requete & " LEFT OUTER JOIN MOTIF_ABSENCE M ON M.ID_MA=P.ID_MA"
requete = requete & " LEFT OUTER JOIN ACTIVITE AC ON AC.ID_ACTIVITE=P.ID_ACTIVITE"
requete = requete & " LEFT OUTER JOIN agent_antenne B on A.id_agent=B.id_agent"
If idSemaine = 53 Then
requete = requete & " WHERE P.ID_ANTENNE=" & idAntenne.ToString
requete = requete & " AND ((T.TableTemps_SemaineAnnee=" & idSemaine.ToString & " AND T.TableTemps_Annee=" & annee.ToString & ") OR (T.TableTemps_SemaineAnnee=1 AND T.TableTemps_Annee=" & annee.ToString & "+1))"
requete = requete & " AND T.TableTemps_Heure in (8,12,13,14)"
Else
requete = requete & " WHERE P.ID_ANTENNE=" & idAntenne.ToString
requete = requete & " AND T.TableTemps_SemaineAnnee=" & idSemaine.ToString
requete = requete & " AND T.TableTemps_Annee=" & annee.ToString
requete = requete & " AND T.TableTemps_Heure in (8,12,13,14)"
End If
requete = requete & " ORDER BY A.ID_STATUT, A.code_agent, T.TableTemps_DateEntiere, t.TableTemps_Heure"
Dim da As SqlDataAdapter = New SqlDataAdapter(requete, connection)
da.Fill(ds)
table = ds.Tables("Table")
Try
'Parcours le fichier xml
lstAgentsXml = xml.GetElementsByTagName("A")
For i = 0 To lstAgentsXml.Count - 1
agentXml = lstAgentsXml(i)
idAgent = agentXml.Attributes("id_agent").Value
'Parcours des périodes
For j = 0 To agentXml.ChildNodes.Count - 1
periodeXml = agentXml.ChildNodes(j)
If CInt(periodeXml.Attributes("modif").Value) = 1 Then
'Recup du code
If CInt(periodeXml.Attributes("estAbsence").Value) = 1 Then
codeMa = periodeXml.Attributes("code").Value
Else
codeActivite = periodeXml.Attributes("code").Value
End If
'recup de la date
idjour = (New BaseDTOFactory).rechercherIdJour(periodeXml.Attributes("date_entiere").Value)
'Recherche dans le ds
enregistrementTrouve = False
Dim expression As String = "id_agent=" & idAgent.ToString & " AND tabletemps_id=" & idjour
Dim foundRows() As DataRow 'résultat dans des DataRow
foundRows = table.Select(expression)
If foundRows.Length > 0 Then
enregistrementTrouve = True
End If
If enregistrementTrouve Then
'update
table.Rows(k).Item(3) = (New AbsenceDTOFactory).rechercherIdAbsence(codeMa)
table.Rows(k).Item(4) = (New ActiviteDTOFactory).rechercherIdActivite(codeActivite)
Else
'insert
row = ds.Tables("Table").NewRow()
row("id_agent") = idAgent
row("id_antenne") = idAntenne
row("TableTemps_ID") = idjour
row("id_ma") = (New AbsenceDTOFactory).rechercherIdAbsence(codeMa)
row("id_activite") = (New ActiviteDTOFactory).rechercherIdActivite(codeActivite)
End If
End If
Next
Next
da.Update(ds)
Catch ex As Exception
LOGGER.Error("Erreur dans la fonction InsererSemaine :" & ex.Message)
Finally
connection.Close()
End Try |
Partager