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
110
111
112
113
114
115
116
117
118
119
|
Public Sub integrerFichier(ByVal s As String) ' Fonction qui me permet de traiter un fichier
Dim myLines() As String
Dim continuFichier As Boolean = True
Dim continuFile As Boolean = True
Dim codeUNI As String
Dim i As Int32
Dim nombre As Int64
Dim sqlSelect As String
Dim ligne As String
Dim newLigne As String
Dim l As Int32 = 0
Dim ElementBis() As String
Try
Dim fi As FileInfo
fi = New FileInfo(s)
Dim Element() As String = fi.Name.Split(".")
codeUNI = Element(2)
myLines = File.ReadAllLines(s)
If (myLines.Length > 1) Then
nbLigne = nbLigne + 1
ligne = myLines(1)
If (ligne.Length <= 40) Then
i = 1
Do
ligne = myLines(i)
integrerLigne(ligne, codeUNI)
myLines(i - 1) = ligne
i = i + 1
Loop While (i < myLines.Length)
Dim sw As StreamWriter
sw = New StreamWriter(s)
Do
newLigne = myLines(l)
sw.WriteLine(newLigne)
l = l + 1
Loop While (l < myLines.Length - 1)
sw.Close()
End If
End If
If (myLines(0).Length < 50) Then
ElementBis = myLines(0).Split(";")
sqlSelect = "SELECT count(*) FROM ligne where codeUNI = '" & codeUNI & "' and datepart(yy,dateTransaction) = datepart(yy, '" & Date.Parse(ElementBis(1)) & "') and datepart(mm,dateTransaction) = datepart(mm,'" & Date.Parse(ElementBis(1)) & "') and datepart(dd,dateTransaction) = datepart(dd,'" & Date.Parse(ElementBis(1)) & "') and IdMachine = " & CInt(ElementBis(0)) & " and nbDossiers = " & CInt(ElementBis(5)) & " and datepart(mi,heure) = datepart(mi,'" & Date.Parse(ElementBis(2)) & "') and datepart(hh,heure) = datepart(hh,'" & Date.Parse(ElementBis(2)) & "') and decision = '" & ElementBis(3) & "' and codeModePaiement = '" & ElementBis(4) & "' and sommeTransaction = " & ElementBis(6)
Dim CommandSQL As New SqlCommand(sqlSelect, con)
nombre = CommandSQL.ExecuteScalar
If (nombre = 0) Then
Dim sqlString As String = "BULK INSERT ligne FROM " & "'" & s & "'" & " WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n');"
Dim com As New SqlCommand(sqlString, con)
com.ExecuteNonQuery()
End If
End If
Catch ex As Exception
MsgBox("bulk: " & ex.Message)
End Try
End Sub
Public Function integrerLigne(ByRef maligne As String, ByVal pointVente As String) ' Fonction qui permet de modifier une ligne de mon fichier original
Dim continuLigne As Boolean = True
Dim p As Char = "."
Dim v As Char = ","
Dim pv As Char = ";"
Dim e As Char = " "
Dim th As Int16
Dim tm As Int16
Try
Dim ElementBis() As String = maligne.Split(";")
If (ElementBis(2).Length = 8) Then
If (ElementBis(6).Contains(v)) Then
ElementBis(6).Replace(v, p)
End If
Dim h As String = ElementBis(2).Substring(4, 4).Insert(2, ":")
th = trancheHoraire(h)
tm = typeMachine(ElementBis(0))
maligne = ElementBis(0) & ";" & ElementBis(1) & ";" & h & ";" & ElementBis(3) & ";" & ElementBis(4) & ";" & ElementBis(5) & ";" & ElementBis(6) & ";" & pointVente & ";" & th.ToString & ";" & tm.ToString
End If
Catch ex As Exception
MsgBox("ligne non valide" + " " + pointVente + " " & nbLigne)
End Try
End Function |
