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 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234
| Private Sub BtnVerifExport_Click()
'Traitement des doublons :
'######## Suppression des doublons ########
'Déclaration des variables
Dim objConn As ADODB.Connection
Dim objVoipProvidersTable As ADODB.Recordset
Dim query As String
Set objConn = New ADODB.Connection
Dim objVOIPProvidersList As Recordset
'Déclaration des informations
Dim dialprefix As String
Dim Destination As String
Dim rateinitial As String
Dim buyrate As String
Dim buyrateinitblock As String
Dim buyrateincrement As String
Dim billingblock As String
Dim connectcharge As String
Dim disconnectcharge As String
Dim stepchargea As String
Dim chargea As String
Dim timechargea As String
Dim billingblocka As String
Dim stepchargeb As String
Dim chargeb As String
Dim timechargeb As String
Dim billingblockb As String
Dim stepchargec As String
Dim chargec As String
Dim timechargec As String
Dim billingblockc As String
Dim startdate As String
Dim stopdate As String
Dim starttime As String
Dim endtime As String
Dim objTmp As Recordset
'Instanciation des variables
Set objVOIPProvidersList = CurrentDb.OpenRecordset("VoipProvidersList")
Set objUpdate = New ADODB.Recordset
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=C:\Documents and Settings\Bequet Jonathan\Bureau\tarifs Jo 29 aout version 1.6.mdb"
objVOIPProvidersList.MoveLast
objVOIPProvidersList.MoveFirst
While Not objVOIPProvidersList.EOF
objConn.Open
NameProvider = objVOIPProvidersList.Fields("Nom")
TableNameR = objVOIPProvidersList.Fields("TableA2Billing")
TableNameI = objVOIPProvidersList.Fields("TableImport")
'Vide Verif_Tmp
query = "DELETE * FROM Verif_Tmp"
Set objVoipProvidersTable = objConn.Execute(query)
'Copie les prefix sans les doublons dans Verif_Tmp
query = "INSERT INTO Verif_Tmp (dialprefix)SELECT DISTINCT dialprefix FROM " & TableNameR & ""
Set objVoipProvidersTable = objConn.Execute(query)
objConn.Close
objConn.Open
Set objTmp = CurrentDb.OpenRecordset("Verif_Tmp")
objTmp.MoveLast
objTmp.MoveFirst
MsgBox ("Vérification de la pertinence de la table " + NameProvider)
While Not objTmp.EOF
dialprefix = objTmp("dialprefix")
objUpdate.Open ("SELECT * FROM " & TableNameR & " WHERE dialprefix = '" & dialprefix & "'"), objConn
If (Not objUpdate.EOF) Then
Destination = objUpdate.Fields("destination")
rateinitial = objUpdate.Fields("rateinitial")
buyrate = objUpdate.Fields("buyrate")
buyrateinitblock = objUpdate.Fields("buyrateinitblock")
buyrateincrement = objUpdate.Fields("buyrateincrement")
initblock = objUpdate.Fields("initblock")
billingblock = objUpdate.Fields("billingblock")
connectcharge = objUpdate.Fields("connectcharge")
disconnectcharge = objUpdate.Fields("disconnectcharge")
stepchargea = objUpdate.Fields("stepchargea")
chargea = objUpdate.Fields("chargea")
timechargea = objUpdate.Fields("timechargea")
billingblocka = objUpdate.Fields("billingblocka")
stepchargeb = objUpdate.Fields("stepchargeb")
chargeb = objUpdate.Fields("chargeb")
timechargeb = objUpdate.Fields("timechargeb")
billingblockb = objUpdate.Fields("billingblockb")
stepchargec = objUpdate.Fields("stepchargec")
chargec = objUpdate.Fields("chargec")
timechargec = objUpdate.Fields("timechargec")
billingblockc = objUpdate.Fields("billingblockc")
startdate = objUpdate.Fields("startdate")
stopdate = objUpdate.Fields("stopdate")
starttime = objUpdate.Fields("starttime")
endtime = objUpdate.Fields("endtime")
End If
objTmp.Edit
objTmp("destination") = dialprefix 'Destination
objTmp.Update
objTmp.Edit
objTmp("buyrate") = rateinitial
objTmp.Update
objTmp.Edit
objTmp("buyrateinitblock") = buyrateinitblock
objTmp.Update
objTmp.Edit
objTmp("buyrateincrement") = buyrateincrement
objTmp.Update
objTmp.Edit
objTmp("rateinitial") = rateinitial
objTmp.Update
objTmp.Edit
objTmp("initblock") = initblock
objTmp.Update
objTmp.Edit
objTmp("billingblock") = billingblock
objTmp.Update
objTmp.Edit
objTmp("connectcharge") = connectcharge
objTmp.Update
objTmp.Edit
objTmp("disconnectcharge") = disconnectcharge
objTmp.Update
objTmp.Edit
objTmp("stepchargea") = stepchargea
objTmp.Update
objTmp.Edit
objTmp("chargea") = chargea
objTmp.Update
objTmp.Edit
objTmp("timechargea") = timechargea
objTmp.Update
objTmp.Edit
objTmp("billingblocka") = billingblocka
objTmp.Update
objTmp.Edit
objTmp("stepchargeb") = stepchargeb
objTmp.Update
objTmp.Edit
objTmp("chargeb") = chargeb
objTmp.Update
objTmp.Edit
objTmp("timechargeb") = timechargeb
objTmp.Update
objTmp.Edit
objTmp("billingblockb") = billingblockb
objTmp.Update
objTmp.Edit
objTmp("stepchargec") = stepchargec
objTmp.Update
objTmp.Edit
objTmp("chargec") = chargec
objTmp.Update
objTmp.Edit
objTmp("timechargec") = timechargec
objTmp.Update
objTmp.Edit
objTmp("billingblockc") = billingblockc
objTmp.Update
objTmp.Edit
objTmp("startdate") = startdate
objTmp.Update
objTmp.Edit
objTmp("stopdate") = stopdate
objTmp.Update
objTmp.Edit
objTmp("starttime") = starttime
objTmp.Update
objTmp.Edit
objTmp("endtime") = endtime
objTmp.Update
objUpdate.Close
objTmp.MoveNext
Wend
objTmp.Close
objConn.Close
objConn.Open
'Vide la table A2Billing
query = "DELETE * FROM " & TableNameR & ""
Set objVoipProvidersTable = objConn.Execute(query)
'Copie la table Verif_Tmp vers la table A2Billing
query = "INSERT INTO " & TableNameR & " (dialprefix, destination, rateinitial, buyrate, buyrateinitblock, buyrateincrement, initblock, billingblock, connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb, timechargeb, billingblockb, stepchargec, chargec, timechargec, billingblockc, startdate, stopdate, starttime, endtime)SELECT * FROM Verif_Tmp"
Set objVoipProvidersTable = objConn.Execute(query)
'Vide Verif_Tmp
'query = "DELETE * FROM Verif_Tmp"
'Set objVoipProvidersTable = objConn.Execute(query)
objVOIPProvidersList.MoveNext
objConn.Close
Wend
objVOIPProvidersList.Close
BtnVerifExport.Value = False
MsgBox ("Vérification terminée. Cependant certains coûts peuvent être vides, veuillez vérifier les tables Ready2Export. Merci.")
End Sub |
Partager