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
| '13.9b création des fichiers XML de télérèglement (un par banque)
Private Sub sepa_Click()
If Not Mode_debug Then On Error GoTo err:
Dim rsc As New adodb.Recordset, rsv As New adodb.Recordset, rsl As New adodb.Recordset, rsm As New adodb.Recordset
Dim n As Integer, emetteur As String, motif As String, quand As String, cd As Long, r As Double
Dim nfic As Integer, Vfic As Double, Dlot As Date, nlot As Integer, Vlot As Double, compteur As Integer, lib As String
Dim FSO As New Scripting.FileSystemObject, F1 As Scripting.TextStream
'nombre de règlements de type SEPA en attente de transfert
100 n = DCount("*", "reglt", "code3='EUR' and nature='S' and montant>0.005 and isnull(cd_tele)")
102 If n = 0 Then MsgBox "Il n'y a aucun règlement en attente de transfert.", vbInformation, "": Exit Sub
104 If MsgBox("Générer le fichier SEPA pour " & n & " virement(s) en attente ?", vbYesNo, "Confirmation") <> vbYes Then Exit Sub
110 emetteur = MajAcc(Get_fnom(1), 2) 'nom de l'émetteur sans caractère interdit
111 quand = Format(Now, "yyyy-mm-ddThh:mm:ss")
'liste des comptes en banque avec virements transférables puis création d'un fichier par compte
120 rsc.Open "SELECT c.compte, c.libelle, c.iban, c.bic, count(*) as nf, sum(montant) as vf " _
& "FROM (reglt r inner join tiers t on r.cd_tiers=t.cd_tiers) inner join comptes c on c.compte=r.compte " _
& "WHERE r.code3='EUR' and r.nature='S' and r.montant>0.005 and cd_tele is null and not t.iban is null and not t.bic is null and not c.iban is null and not c.bic is null " _
& "GROUP BY c.compte, c.libelle, c.iban, c.bic ORDER BY 1;", cnx, adOpenStatic
121 While Not rsc.EOF
122 r = SysCmd(1, rsc!libelle, rsc!nf)
123 nfic = 0: Vfic = 0 'nb de lignes et cumul des règlements pour controle du fichier
124 lib = MajAcc(rsc!libelle, 2)
126 cd = Get_next("reglt", "cd_tele", 2) 'un par banque
127 compteur = 0 'compteur de lot (par banque)
'création et entete du fichier SEPA
130 Set F1 = FSO.OpenTextFile(Client_path & lib & "_" & Left(quand, 13) & ".xml", ForWriting, True)
132 F1.WriteLine "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""no"" ?>"
134 F1.WriteLine "<Document xmlns=""urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"">"
136 F1.WriteLine "<CstmrCdtTrfInitn><GrpHdr>"
138 F1.WriteLine "<MsgId>" & lib & " No " & cd & "</MsgId>"
140 F1.WriteLine "<CreDtTm>" & quand & "</CreDtTm>"
142 F1.WriteLine "<NbOfTxs>" & rsc!nf & "</NbOfTxs>"
144 F1.WriteLine "<CtrlSum>" & Num(rsc!Vf, 2) & "</CtrlSum>"
146 F1.WriteLine "<InitgPty><Nm>" & emetteur & "</Nm></InitgPty>"
148 F1.WriteLine "</GrpHdr>"
'virements à transférer pour la banque rsc!compte
150 rsv.Open "SELECT int(r.date_reg) as D, r.cd_reg, r.montant, t.fnom, t.iban, t.bic " _
& "FROM reglt r inner join tiers t on r.cd_tiers=t.cd_tiers " _
& "WHERE r.code3='EUR' and r.nature='S' and cd_tele is null and not t.iban is null and not t.bic is null and r.compte='" & rsc!compte & "' ORDER BY 1;", cnx, adOpenStatic
152 Dlot = 0 'détecteur de changement de lot (un lot par jour)
154 While Not rsv.EOF
160 If Dlot <> rsv!d Then 'entete de lot
161 compteur = compteur + 1
162 If Dlot > 0 Then 'on termine d'abord le lot précédent
164 F1.WriteLine "</PmtInf>"
166 If rsl!nl <> nlot Then Call message("Erreur en création de lot SEPA n° " & cd & " : lignes attendues " & rsl!nl & " trouvées " & nlot): Exit Sub
167 If rsl!vl <> Vlot Then Call message("Erreur en création de lot SEPA n° " & cd & " : valeur attendue " & rsl!vl & " trouvée " & Vlot): Exit Sub
168 rsl.Close
End If
'nombre de lignes et montant attendu de chaque lot
170 rsl.Open "SELECT count(*) as nl, sum(montant) as vl FROM reglt r inner join tiers t on r.cd_tiers=t.cd_tiers " _
& "WHERE r.code3='EUR' and r.nature='S' and cd_tele is null and not t.iban is null and not t.bic is null " _
& "and r.compte='" & rsc!compte & "' and " & IIf(msql, "cast(r.date_reg as date)=", "int(r.date_reg)=cdate") & "('" & rsv!d & "') ORDER BY 1;", cnx, adOpenStatic
172 If rsl.EOF Then Call message("Erreur en création de fichier SEPA n° " & cd & " : données incomplètes compte " & rsc!compte & " le " & rsc!d): Exit Sub
174 nlot = 0 'compteur de lignes du lot
176 Vlot = 0 'montant du lot
180 F1.WriteLine "<PmtInf><PmtInfId>Remise " & cd & " lot " & compteur & "</PmtInfId><PmtMtd>TRF</PmtMtd>"
182 F1.WriteLine "<NbOfTxs>" & rsl!nl & "</NbOfTxs>"
184 F1.WriteLine "<CtrlSum>" & Num(rsl!vl, 2) & "</CtrlSum>"
186 F1.WriteLine "<PmtTpInf><SvcLvl><Cd>SEPA</Cd></SvcLvl></PmtTpInf>"
188 F1.WriteLine "<ReqdExctnDt>" & Format(rsv!d, "yyyy-mm-dd") & "</ReqdExctnDt>"
190 F1.WriteLine "<Dbtr><Nm>" & emetteur & "</Nm></Dbtr>"
192 F1.WriteLine "<DbtrAcct><Id><IBAN>" & rsc!iban & "</IBAN></Id></DbtrAcct>"
194 F1.WriteLine "<DbtrAgt><FinInstnId><BIC>" & rsc!bic & "</BIC></FinInstnId></DbtrAgt>"
196 Dlot = rsv!d
End If 'fin entete de lot (sans balise car elle est après les virements du lot)
'virements
200 F1.WriteLine "<CdtTrfTxInf><PmtId><EndToEndId>R" & rsv!cd_reg & "</EndToEndId></PmtId>"
202 F1.WriteLine "<Amt><InstdAmt Ccy=""EUR"">" & Num(rsv!montant, 2) & "</InstdAmt></Amt>"
204 If Left(rsv!iban, 2) <> "FR" Then F1.WriteLine "<CdtrAgt><FinInstnId><BIC>" & rsv!bic & "</BIC></FinInstnId></CdtrAgt>" 'optionnel en france ?
206 F1.WriteLine "<Cdtr><Nm>" & MajAcc(rsv!Fnom, 2) & "</Nm></Cdtr>"
208 F1.WriteLine "<CdtrAcct><Id><IBAN>" & rsv!iban & "</IBAN></Id></CdtrAcct>"
'constitution du motif = liste des factures réglées + (n° du fournisseur)
210 motif = "R" & rsv!cd_reg
212 rsm.Open "SELECT date_fac, cd_ff, f_four FROM FF WHERE cd_reg=" & rsv!cd_reg & " ORDER BY 1,2;", cnx, adOpenStatic
214 While Not rsm.EOF: motif = motif & " - " & rsm!f_four & " " & Format(rsm!date_fac, "dd/mm/yy"): rsm.MoveNext: Wend
216 motif = Left(MajAcc(motif, 2), 140)
218 F1.WriteLine "<RmtInf><Ustrd>" & motif & "</Ustrd></RmtInf></CdtTrfTxInf>"
220 nfic = nfic + 1: nlot = nlot + 1
222 Vfic = Vfic + rsv!montant: Vlot = Vlot + rsv!montant
224 r = SysCmd(2, nfic)
226 rsv.MoveNext
Wend
'controle et termine le dernier lot
300 If rsl!nl <> nlot Then Call message("Erreur en création de lot SEPA n° " & cd & " : lignes attendues " & rsl!nl & " trouvées " & nlot): Exit Sub
302 If rsl!vl <> Vlot Then Call message("Erreur en création de lot SEPA n° " & cd & " : valeur attendue " & rsl!vl & " trouvée " & Vlot): Exit Sub
304 F1.WriteLine "</PmtInf>"
306 rsv.Close: rsl.Close
'controle et termine le fichier, marque les règlements télétransférés
310 If rsc!nf <> nfic Then Call message("Erreur en création de fichier SEPA n° " & cd & " : lignes attendues " & rsc!nf & " trouvées " & nfic): Exit Sub
312 If rsc!Vf <> Vfic Then Call message("Erreur en création de fichier SEPA n° " & cd & " : valeur attendue " & rsc!Vf & " trouvée " & Vfic): Exit Sub
314 Sr = "UPDATE reglt r inner join tiers t on r.cd_tiers=t.cd_tiers SET cd_tele=" & cd & ", date_tele=" & IIf(msql, "", "cdate") & "('" & Date _
& "') WHERE r.code3='EUR' and r.nature='S' and cd_tele is null and not t.iban is null and not t.bic is null " _
& "and r.compte='" & rsc!compte & "';"
316 cnx.Execute Sr, dbFailOnError
320 F1.WriteLine "</CstmrCdtTrfInitn></Document>" 'termine le fichier
322 F1.Close: Set F1 = Nothing
324 MsgBox "Télérèglement " & cd & " (" & nfic & " lignes pour " & Round(Vfic, 2) & ") terminé dans le fichier " _
& Client_path & rsc!libelle & "_" & Left(quand, 13) & ".xml", vbInformation, ""
326 rsc.MoveNext
Wend
350 rsc.Close
400 Set FSO = Nothing
402 r = SysCmd(3)
Exit Sub
err: Call message("Erreur " & err.Number & "/" & Erl & " dans RF_cree.sepa : " & err.description)
End Sub |
Partager