|
QR code
Aug 29, 2020 16:09:25 GMT -5
Post by tenochtitlanuk on Aug 29, 2020 16:09:25 GMT -5
This ( with rather careless column formatting and alignment) works fine. Do you have a file which has the variations of 'ZZ' and 'EA' to test?
start$ ="<cac:InvoiceLine>" lenS =len(start$) finish$ ="</cac:InvoiceLine>" lenF =len( finish$)
comma$ =chr$( 34)
open "UBLTest.xml" for input as #fIn file$ =input$( #fIn, lof( #fIn)) close #fIn
current = 1
print "Name"; tab( 30); "Taxable amount Tax amount Invoiced quantity Percent Price" PRINT ""
[look] pointer =instr( file$, start$, current) if pointer <>0 then endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 30); print dataBetween$( "<cbc:TaxableAmount currencyID=" +comma$ +"EUR" +comma$ +">", "</cbc:TaxableAmount>", data$),, print dataBetween$( "<cbc:TaxAmount currencyID=" +comma$ +"EUR" +comma$ +">", "</cbc:TaxAmount>", data$), print dataBetween$( "<cbc:InvoicedQuantity unitCode=" +comma$ +"ZZ" +comma$ +">", "</cbc:InvoicedQuantity>", data$); tab( 90); print dataBetween$( "<cbc:Percent>" , "</cbc:Percent>", data$), print dataBetween$( "<cbc:PriceAmount currencyID=" +comma$ +"EUR" +comma$ +">", "</cbc:PriceAmount>", data$) else e=nd end if
scan goto [look]
wait ' ___________________________________________________
function dataBetween$( start$, finish$, source$) S =instr( source$, start$) +len( start$) rightHand$ =mid$( source$, S) d$ =upto$( rightHand$, finish$) dataBetween$ =d$ end function
Output is..
Name Taxable amount Tax amount Invoiced quantity Percent Price
Artikel - Een - Hoog 100.00 21.00 1.00 21.00 100.00 Artikel - Twee - Hoog 200.00 42.00 1.00 21.00 200.00 Artikel - Drie - Hoog 300.00 63.00 1.00 21.00 300.00 Artikel - Vier - Hoog 400.00 84.00 1.00 21.00 400.00
|
|
|
QR code
Aug 30, 2020 5:31:59 GMT -5
Post by tenochtitlanuk on Aug 30, 2020 5:31:59 GMT -5
I think the following code correctly finds the two cases. But are other possibilities than ZZ or EA possible?
start$ ="<cac:InvoiceLine>" lenS =len(start$) finish$ ="</cac:InvoiceLine>" lenF =len( finish$)
quotes$ =chr$( 34)
open "UBLTestEAZZ.xml" for input as #fIn file$ =input$( #fIn, lof( #fIn)) close #fIn
current = 1
print "Name"; tab( 30); "Taxable amount Tax amount Invoiced quantity Percent Price" PRINT ""
[look] pointer =instr( file$, start$, current) if pointer <>0 then endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 30);
print dataBetween$( "<cbc:TaxableAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxableAmount>", data$),,
print dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>", data$),
invoiceCode$ =dataBetween$( "<cbc:InvoicedQuantity unitCode=" +quotes$, quotes$ +">1.00</cbc:InvoicedQuantity>", data$) if invoiceCode$ ="ZZ" then print dataBetween$( "<cbc:InvoicedQuantity unitCode=" +quotes$ +"ZZ" +quotes$ +">", "</cbc:InvoicedQuantity>", data$); tab( 90); else print dataBetween$( "<cbc:InvoicedQuantity unitCode=" +quotes$ +"EA" +quotes$ +">", "</cbc:InvoicedQuantity>", data$); tab( 90); end if
print dataBetween$( "<cbc:Percent>" , "</cbc:Percent>", data$), print dataBetween$( "<cbc:PriceAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:PriceAmount>", data$) else e=nd end if
scan goto [look]
wait ' ___________________________________________________
function dataBetween$( start$, finish$, source$) S =instr( source$, start$) +len( start$) rightHand$ =mid$( source$, S) d$ =upto$( rightHand$, finish$) dataBetween$ =d$ end function
|
|
konijn
Junior Member
Posts: 96
|
QR code
Aug 30, 2020 11:11:41 GMT -5
Post by konijn on Aug 30, 2020 11:11:41 GMT -5
Hello tenochtitlanuk, yes we still working on the project. I hope with this we can make a step forward. I think its ZZ or EA in the file, I think it is not mixed. But that would not a problem with this solution. With the last code I get a error on this line:
print dataBetween$( "<cbc:Percent>"
I can not figure it out what wend wrong?
If we get al lines out off the file can I give it a number ? Like data$(i)? Because the data off each colum has to be written in a new line on almost the same way like this
<AdedLevering> <A702078Regelnummer>10</A702078Regelnummer> <A702164DatumLevering>2020-08-28</A702164DatumLevering> <A300225BonnummerLeveringsbon>10</A300225BonnummerLeveringsbon> <A702165Leveringsbonregelnummer>10</A702165Leveringsbonregelnummer> <A702827OrderNummerLeverancier>uw ref.: Jack</A702827OrderNummerLeverancier> <A203811BedragBrutoExBtw>0.30</A203811BedragBrutoExBtw> <A203862BedragNettoExBtw>0.30</A203862BedragNettoExBtw> <A203822HoeveelheidProdukt>1.00</A203822HoeveelheidProdukt> <A204652Eenheid>ST</A204652Eenheid> <AdedProdukt> <A203819Artikelcode>ZY.BS04C</A203819Artikelcode> <A401013PrijsBruto>0.30</A401013PrijsBruto> <A203820OmschrijvingProdukt>BONDED SEAL 1/4 INCH CENTR.</A203820OmschrijvingProdukt> <AdedBtw> <A202637Btw>21.00</A202637Btw> <A702004BedragBtw>0.06</A702004BedragBtw> </AdedBtw> </AdedProdukt> </AdedLevering> Also the adres of the seller and buyer has to taken out, but maybe I can manage that. Thats always one item. the invoicelines is more difficult.
|
|
|
QR code
Aug 30, 2020 16:41:12 GMT -5
Post by tenochtitlanuk on Aug 30, 2020 16:41:12 GMT -5
Sorry- an extra 'return' seems to have crept in. The old laptop /Linux I'm using has a dodgy touchpad, and it does odd things when I'm editing data. I just cut from LB and pasted to the forum, so not sure how it happened!! The following are all one line- the spaces were just to align ( in a mono-spaced editor font) to emphasize that all the lines were basically similar.
print dataBetween$( "<cbc:Percent>" , "</cbc:Percent>", data$),
|
|
konijn
Junior Member
Posts: 96
|
QR code
Aug 31, 2020 6:04:55 GMT -5
Post by konijn on Aug 31, 2020 6:04:55 GMT -5
No problem. Can happen.. I can downloaded it. I have tested with a ubl with "ZZ" This is the result
File entry # Name Taxable amount Tax amount Invoiced quantity Percent Price
1 H.D. SLANG GEMAAKT 24.00 5.04 voicedQuantity unitCode="ZZ">3.0021.00 8.00 2 SLANG DIN 2SC DN08 KOMPACT L=4500mm 75.60 15.88 InvoicedQuantity unitCode="ZZ">9.0021.00 8.40 3 KOPPELING 14.08 2.96 InvoicedQuantity unitCode="ZZ">2.0021.00 7.04 4 KOPPELING 7.56 1.59 InvoicedQuantity unitCode="ZZ">2.0021.00 3.78 5 PERSHULS 1-2 INLAGE 5/16'' 6.44 1.35 InvoicedQuantity unitCode="ZZ">4.0021.00 1.61 6 SLANG DIN 2SC DN10 KOMPACT L= 2090mm 19.60 4.12 InvoicedQuantity unitCode="ZZ">2.0921.00 9.38 7 KOPPELING 3.92 0.82 1.0021.00 3.92 8 KOPPELING 2.00 0.42 1.0021.00 2.00 9 PERSHULS 1-2 INLAGE 3/8'' 2.80 0.59 InvoicedQuantity unitCode="ZZ">2.0021.00 1.40 10 BONDED SEAL 1/4 INCH CENTR. 0.30 0.06 1.0021.00 0.30 11 BONDED SEAL 1/2 INCH CENTR. 0.66 0.14 :InvoicedQuantity unitCode="ZZ">2.0021.00 0.33 12 SNELKOPPELING FASTER 13.26 2.78 :InvoicedQuantity unitCode="ZZ">2.0021.00 6.63 13 RECHTE INSCHROEFKOPP. 1.49 0.31 1.0021.00 1.49 14 RECHTE INSCHROEFKOPP.+WD 3.91 0.82 1.0021.00 3.91 15 AFSLUITSTOP 2.04 0.43 :InvoicedQuantity unitCode="ZZ">2.0021.00 1.02 16 SNIJRINGMOER 0.88 0.18 :InvoicedQuantity unitCode="ZZ">2.0021.00 0.44 17 AFSLUITKAP BINNENKONUS 4.98 1.05 :InvoicedQuantity unitCode="ZZ">2.0021.00 2.49 18 Danfoss OMR315/EPRMS 547.95 115.07 1.0021.00 547.95
With "EA"works good but with "ZZ" not all correct. I found a UBL who has this in: <cbc:InvoicedQuantity unitCodeListID="UNECERec20" unitCode="EA">1.00</cbc:InvoicedQuantity> Used a diffrent code
How must I now go on to place the data in the new XML scheme?
Thanks
Jack
|
|
|
QR code
Sept 1, 2020 16:39:37 GMT -5
Post by tenochtitlanuk on Sept 1, 2020 16:39:37 GMT -5
Can you try this variant? I THINK it covers the EA/ZZ and InvoicedQuantity versions. Added number count, and the code EA/ZZ. You'd be storing values in your arrays, not printing, of course. Change file name appropriately.. I know NOTHING of xml. But LB will happily write values into a text file called jack.xml if you DO understand it!
start$ ="<cac:InvoiceLine>" lenS =len(start$) finish$ ="</cac:InvoiceLine>" lenF =len( finish$)
quotes$ =chr$( 34)
itemNumber =0
open "UBLTestEAZZb.xml" for input as #fIn file$ =input$( #fIn, lof( #fIn)) close #fIn
current = 1
print "File entry # Name"; tab( 38); "Taxable amount Tax amount Invoiced quantity Percent Price" PRINT ""
[look] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 40);
print dataBetween$( "<cbc:TaxableAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxableAmount>", data$),
print dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>", data$),
' _______________________________________________________________________________________________________________________________ 'PROBLEM old syntax <cbc:InvoicedQuantity unitCode="ZZ">1.00</cbc:InvoicedQuantity> with EA or ZZ ' ^ ^ ' but alternative syntax <cbc:InvoicedQuantity unitCodeListID="UNECERec20" unitCode="EA">1.00</cbc:InvoicedQuantity> ' ^ ^ Code$ =dataBetween$( "<cbc:InvoicedQuantity ", quotes$ +">", data$) invoiceCode$ =right$( Code$, 2)
I$ = dataBetween$( quotes$ +">", "</", dataBetween$( "<cbc:InvoicedQuantity ", "</cbc:InvoicedQuantity>", data$)) 'I$ = print I$; tab( 90);
' _______________________________________________________________________________________________________________________________
print dataBetween$( "<cbc:Percent>" , "</cbc:Percent>", data$), print dataBetween$( "<cbc:PriceAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:PriceAmount>", data$), print invoiceCode$ else end end if
scan goto [look]
wait ' ___________________________________________________
function dataBetween$( start$, finish$, source$) S =instr( source$, start$) +len( start$) rightHand$ =mid$( source$, S) d$ =upto$( rightHand$, finish$) dataBetween$ =d$ end function
|
|
konijn
Junior Member
Posts: 96
|
QR code
Sept 2, 2020 8:05:24 GMT -5
Post by konijn on Sept 2, 2020 8:05:24 GMT -5
Yes it seems working. I have some ubls who give problems with this line:
[code] <cbc:TaxAmount currencyID="EUR">8.30</cbc:TaxAmount> [/code]
There is a enter in the line, so LB is noth finding it. How to do?
Yes I write the values with LB as file .xml. But how can I write:
quantity(i) name(i) withouttax(i) tax(i) next i
?
|
|
|
QR code
Sept 2, 2020 8:43:43 GMT -5
Post by tenochtitlanuk on Sept 2, 2020 8:43:43 GMT -5
I'm sure we can cope for an added line feed. Can you load the file in a hex editor and find whether it is a CR, a LF, a CRLF pair, and are there extra spaces? I'm worried that we are 'reverse engineering' files. We don't know what the file specification is, so there may be further surprises in store! Writing a version of an existing xml file with new values is in itself easy. Code below has been provided with code snippets numbers 1 2 and 3, and inserts text- or numeric changed to text string form- between them. NB not real xml code.
name$ ="GIF" '"JPG" offset =0 '666
open "newXml.xml" for output as #fOut open "xmlSamplePart1.xml" for input as #fIn i$( 1) =input$( #fIn, lof( #fIn)) close #fIn
open "xmlSamplePart2.xml" for input as #fIn i$( 2) =input$( #fIn, lof( #fIn)) close #fIn
open "xmlSamplePart3.xml" for input as #fIn i$( 3) =input$( #fIn, lof( #fIn)) close #fIn
whole$ =i$( 1) +name$ +i$( 2) +str$( offset) +i$( 3) #fOut whole$; print whole$
close #fOut
Produces <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE magicmap>
<magicmap> <!-- <magic name="GIF" offset="0" target="GIF8"/> --> </magicmap>
... and altering the two data items, file now has JPG and 666 replacing GIF and 0.
|
|
konijn
Junior Member
Posts: 96
|
QR code
Sept 3, 2020 5:51:10 GMT -5
Post by konijn on Sept 3, 2020 5:51:10 GMT -5
I made a screenshot of it. I hope this is also the same in some other xml's. Or something with a wilcard as you did with with the tax code. linkIts a CR and LF with spaces. I want on the end off the file go on with write the new xml structure, how can I then split the data$ for each item? I am now working with the other data off the ubl xml, like adres supplier and customer. I wil post the code if it worked. Sure I made mistakes :: ))
|
|
konijn
Junior Member
Posts: 96
|
QR code
Sept 3, 2020 11:03:35 GMT -5
Post by konijn on Sept 3, 2020 11:03:35 GMT -5
Here I made a start with more items rom the xml and a start with the new xml.
In the new xml I start first with the invoicenumber. is that correct this way?
start$ ="<cac:AccountingSupplierParty>" lenS =len(start$) finish$ ="</cac:AccountingSupplierParty>" lenF =len( finish$)
quotes$ =chr$( 34)
itemNumber =0
open "c:\factuur\sepa\sepabestanden\ubl.xml" for input as #fIn file$ =input$( #fIn, lof( #fIn)) close #fIn
current = 1
' print "File entry # Name"; tab( 38); "Taxable amount Tax amount Invoiced quantity Percent Price" ' PRINT ""
[look] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 40);
print dataBetween$( "<cbc:StreetName>", "</cbc:StreetName>", data$),
print dataBetween$( "<cbc:CityName>", "</cbc:CityName>", data$),
print dataBetween$( "<cbc:PostalZone>", "</cbc:PostalZone>", data$),
print dataBetween$( "<cbc:IdentificationCode listAgencyID=" +quotes$ +"6" +quotes$ +" listID=" +quotes$ +"ISO3166-1:Alpha2" +quotes$ +">", "</cbc:IdentificationCode>", data$), print dataBetween$( "<cbc:CompanyID schemeAgencyID=" +quotes$ +"ZZZ" +quotes$ +" schemeID="+quotes$ +"NL:VAT" +quotes$ +">", "</cbc:CompanyID>", data$),
else goto [tweede] end if
scan goto [look]
wait ' ___________________________________________________
[tweede]
start$ ="<cac:AccountingCustomerParty>" lenS =len(start$) finish$ ="</cac:AccountingCustomerParty>" lenF =len( finish$)
' quotes$ =chr$( 34)
' itemNumber =0
' open "c:\factuur\sepa\sepabestanden\ubl.xml" for input as #fIn ' file$ =input$( #fIn, lof( #fIn)) ' close #fIn
current = 1
' print "File entry # Name"; tab( 38); "Taxable amount Tax amount Invoiced quantity Percent Price" ' PRINT ""
[look1] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 40);
print dataBetween$( "<cbc:StreetName>", "</cbc:StreetName>", data$),
print dataBetween$( "<cbc:CityName>", "</cbc:CityName>", data$),
print dataBetween$( "<cbc:PostalZone>", "</cbc:PostalZone>", data$),
print dataBetween$( "<cbc:IdentificationCode listAgencyID=" +quotes$ +"6" +quotes$ +" listID=" +quotes$ +"ISO3166-1:Alpha2" +quotes$ +">", "</cbc:IdentificationCode>", data$), print dataBetween$( "<cbc:CompanyID schemeAgencyID=" +quotes$ +"ZZZ" +quotes$ +" schemeID="+quotes$ +"NL:VAT" +quotes$ +">", "</cbc:CompanyID>", data$),
else goto [derde] end if
scan goto [look1]
wait ' ___________________________________________________
[derde]
start$ ="<cac:InvoiceLine>" lenS =len(start$) finish$ ="</cac:InvoiceLine>" lenF =len( finish$)
' quotes$ =chr$( 34)
' itemNumber =0
' open "c:\factuur\sepa\sepabestanden\ubl.xml" for input as #fIn ' file$ =input$( #fIn, lof( #fIn)) ' close #fIn
' current = 1
' print "File entry # Name"; tab( 38); "Taxable amount Tax amount Invoiced quantity Percent Price" ' PRINT ""
[look2] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 40);
print dataBetween$( "<cbc:TaxableAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxableAmount>", data$),
print dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>", data$),
' _______________________________________________________________________________________________________________________________ 'PROBLEM old syntax <cbc:InvoicedQuantity unitCode="ZZ">1.00</cbc:InvoicedQuantity> with EA or ZZ ' ^ ^ ' but alternative syntax <cbc:InvoicedQuantity unitCodeListID="UNECERec20" unitCode="EA">1.00</cbc:InvoicedQuantity> ' ^ ^ Code$ =dataBetween$( "<cbc:InvoicedQuantity ", quotes$ +">", data$) invoiceCode$ =right$( Code$, 2)
I$ = dataBetween$( quotes$ +">", "</", dataBetween$( "<cbc:InvoicedQuantity ", "</cbc:InvoicedQuantity>", data$)) 'I$ = print I$; tab( 90);
' _______________________________________________________________________________________________________________________________
print dataBetween$( "<cbc:Percent>" , "</cbc:Percent>", data$), print dataBetween$( "<cbc:PriceAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:PriceAmount>", data$), print invoiceCode$ else
goto [vierde] end if
scan goto [look2]
wait ' ___________________________________________________
[vierde]
start$ ="<cac:AdditionalDocumentReference>" lenS =len(start$) finish$ ="</cac:AdditionalDocumentReference>" lenF =len( finish$)
current = 1
[look3] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:ID>", "</cbc:ID>", data$);
else goto [write] end if
scan goto [look3]
wait ' ___________________________________________________
function dataBetween$( start$, finish$, source$) S =instr( source$, start$) +len( start$) rightHand$ =mid$( source$, S) d$ =upto$( rightHand$, finish$) dataBetween$ =d$ end function
[write] open "c:\factuur\sepa\sepabestanden\agro.xml" for output as #xml 'open "c:\melkteller\inleesdelpro\out.dat" for output as #2
print #xml, "<?xml version=" +quotes$ +"1.0" +quotes$ +"encoding=" +quotes$ +"UTF-8" +quotes$ +"?>" print #xml, "<!--V1 Konijn Automatisering Per 27-08-2020-->" print #xml, "<AdedTransaction>" print #xml, "<A402617CodeMuntsoort>EUR</A402617CodeMuntsoort>" print #xml, "<A201029SoortOverzicht>380</A201029SoortOverzicht>" print #xml, "<A201136Faktuurnummer>" +dataBetween$( "<cbc:ID>", "</cbc:ID>", data$) +"</A201136Faktuurnummer>"
close #xml end '
|
|
konijn
Junior Member
Posts: 96
|
QR code
Sept 3, 2020 11:52:15 GMT -5
Post by konijn on Sept 3, 2020 11:52:15 GMT -5
And this one?
start$ ="<cac:AccountingSupplierParty>" lenS =len(start$) finish$ ="</cac:AccountingSupplierParty>" lenF =len( finish$)
quotes$ =chr$( 34)
itemNumber =0
open "c:\factuur\sepa\sepabestanden\ubl.xml" for input as #fIn file$ =input$( #fIn, lof( #fIn)) close #fIn
current = 1
' print "File entry # Name"; tab( 38); "Taxable amount Tax amount Invoiced quantity Percent Price" ' PRINT ""
[look] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 40);
print dataBetween$( "<cbc:StreetName>", "</cbc:StreetName>", data$),
print dataBetween$( "<cbc:CityName>", "</cbc:CityName>", data$),
print dataBetween$( "<cbc:PostalZone>", "</cbc:PostalZone>", data$),
print dataBetween$( "<cbc:IdentificationCode listAgencyID=" +quotes$ +"6" +quotes$ +" listID=" +quotes$ +"ISO3166-1:Alpha2" +quotes$ +">", "</cbc:IdentificationCode>", data$), print dataBetween$( "<cbc:CompanyID schemeAgencyID=" +quotes$ +"ZZZ" +quotes$ +" schemeID="+quotes$ +"NL:VAT" +quotes$ +">", "</cbc:CompanyID>", data$),
else goto [tweede] end if
scan goto [look]
wait ' ___________________________________________________
[tweede]
start$ ="<cac:AccountingCustomerParty>" lenS =len(start$) finish$ ="</cac:AccountingCustomerParty>" lenF =len( finish$)
' quotes$ =chr$( 34)
' itemNumber =0
' open "c:\factuur\sepa\sepabestanden\ubl.xml" for input as #fIn ' file$ =input$( #fIn, lof( #fIn)) ' close #fIn
current = 1
' print "File entry # Name"; tab( 38); "Taxable amount Tax amount Invoiced quantity Percent Price" ' PRINT ""
[look1] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 40);
print dataBetween$( "<cbc:StreetName>", "</cbc:StreetName>", data$),
print dataBetween$( "<cbc:CityName>", "</cbc:CityName>", data$),
print dataBetween$( "<cbc:PostalZone>", "</cbc:PostalZone>", data$),
print dataBetween$( "<cbc:IdentificationCode listAgencyID=" +quotes$ +"6" +quotes$ +" listID=" +quotes$ +"ISO3166-1:Alpha2" +quotes$ +">", "</cbc:IdentificationCode>", data$), print dataBetween$( "<cbc:CompanyID schemeAgencyID=" +quotes$ +"ZZZ" +quotes$ +" schemeID="+quotes$ +"NL:VAT" +quotes$ +">", "</cbc:CompanyID>", data$),
else goto [derde] end if
scan goto [look1]
wait ' ___________________________________________________
[derde]
start$ ="<cac:InvoiceLine>" lenS =len(start$) finish$ ="</cac:InvoiceLine>" lenF =len( finish$)
' quotes$ =chr$( 34)
' itemNumber =0
' open "c:\factuur\sepa\sepabestanden\ubl.xml" for input as #fIn ' file$ =input$( #fIn, lof( #fIn)) ' close #fIn
' current = 1
' print "File entry # Name"; tab( 38); "Taxable amount Tax amount Invoiced quantity Percent Price" ' PRINT ""
[look2] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:Name>", "</cbc:Name>", data$); tab( 40);
print dataBetween$( "<cbc:TaxableAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxableAmount>", data$),
print dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>", data$),
' _______________________________________________________________________________________________________________________________ 'PROBLEM old syntax <cbc:InvoicedQuantity unitCode="ZZ">1.00</cbc:InvoicedQuantity> with EA or ZZ ' ^ ^ ' but alternative syntax <cbc:InvoicedQuantity unitCodeListID="UNECERec20" unitCode="EA">1.00</cbc:InvoicedQuantity> ' ^ ^ Code$ =dataBetween$( "<cbc:InvoicedQuantity ", quotes$ +">", data$) invoiceCode$ =right$( Code$, 2)
I$ = dataBetween$( quotes$ +">", "</", dataBetween$( "<cbc:InvoicedQuantity ", "</cbc:InvoicedQuantity>", data$)) 'I$ = print I$; tab( 90);
' _______________________________________________________________________________________________________________________________
print dataBetween$( "<cbc:Percent>" , "</cbc:Percent>", data$), print dataBetween$( "<cbc:PriceAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:PriceAmount>", data$), print invoiceCode$ else
goto [vierde] end if
scan goto [look2]
wait ' ___________________________________________________
[vierde]
start$ ="<cac:AdditionalDocumentReference>" lenS =len(start$) finish$ ="</cac:AdditionalDocumentReference>" lenF =len( finish$)
current = 1
[look3] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print dataBetween$( "<cbc:ID>", "</cbc:ID>", data$);
else goto [write] end if
scan goto [look3]
wait ' ___________________________________________________
function dataBetween$( start$, finish$, source$) S =instr( source$, start$) +len( start$) rightHand$ =mid$( source$, S) d$ =upto$( rightHand$, finish$) dataBetween$ =d$ end function
[write] open "c:\factuur\sepa\sepabestanden\agro.xml" for output as #xml 'open "c:\melkteller\inleesdelpro\out.dat" for output as #2
print #xml, "<?xml version=" +quotes$ +"1.0" +quotes$ +"encoding=" +quotes$ +"UTF-8" +quotes$ +"?>" print #xml, "<!--V1 Konijn Automatisering Per 27-08-2020-->" print #xml, "<AdedTransaction>" print #xml, "<A402617CodeMuntsoort>EUR</A402617CodeMuntsoort>" print #xml, "<A201029SoortOverzicht>380</A201029SoortOverzicht>" print #xml, "<A201136Faktuurnummer>" +dataBetween$( "<cbc:ID>","</cbc:ID>", data$) +"</A201136Faktuurnummer>" start$ ="<cac:InvoiceLine>" lenS =len(start$) finish$ ="</cac:InvoiceLine>" lenF =len( finish$) [lo] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
print right$( " " +str$( itemNumber), 4),
print #xml, "<A203820OmschrijvingProdukt>" +dataBetween$( "<cbc:Name>","</cbc:Name>",data$) +"</A203820OmschrijvingProdukt>" print #xml, "<AdedBtw>" print #xml, "<A202637Btw>" +dataBetween$( "<cbc:Percent>", "</cbc:Percent>", data$) +"</A202637Btw>" print #xml, "<A702004BedragBtw>" +dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>",data$) +"</A702004BedragBtw>" else
goto [verder] end if
scan goto [lo] [verder] close #xml end '
|
|
konijn
Junior Member
Posts: 96
|
QR code
Sept 25, 2020 11:11:36 GMT -5
Post by konijn on Sept 25, 2020 11:11:36 GMT -5
This is my final code. It seems to working. I use a UBL file 2.0. Maybe with othet versions I need something to change. Next step to made. And I dont know yet how -Select the pfd invoice. -Copy the pdf to a special directory -Ask from who the sender is -Run the convert pdf/ubl program with the input data from who is the sender - Run the code below - Name the new xml and the pdf the same. - select both files and mail to the cloud (I have that already) dim btwex(3000), btwex$(3000), btwpr$(3000), btwpr(3000), btw$(3000), btw(3000)
start$ ="<cac:AccountingSupplierParty>" lenS =len(start$) finish$ ="</cac:AccountingSupplierParty>" lenF =len( finish$)
quotes$ =chr$( 34) enter$ =chr$( 13) enter2$ =chr$( 10) itemNumber =0 r = 1 open "c:\factuur\sepa\sepabestanden\ubl.xml" for input as #fIn file$ =input$( #fIn, lof( #fIn)) close #fIn
current = 1
[write] open "c:\factuur\sepa\sepabestanden\agro.xml" for output as #xml 'open "c:\melkteller\inleesdelpro\out.dat" for output as #2
print #xml, "<?xml version=" +quotes$ +"1.0" +quotes$ +" encoding=" +quotes$ +"UTF-8" +quotes$ +"?>" print #xml, "<!--V1 Konijn Automatisering Per 27-08-2020-->" print #xml, "<AdedTransaction>"
start$ ="<cbc:UBLVersionID>" lenS =len(start$) finish$ ="<cac:AdditionalDocumentReference>" lenF =len( finish$)
current = 1
[lo1] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
' print right$( " " +str$( itemNumber), 4),
print #xml, " <A402617CodeMuntsoort>" +dataBetween$( "<cbc:DocumentCurrencyCode>","</cbc:DocumentCurrencyCode>", data$) +"</A402617CodeMuntsoort>" print #xml, " <A201029SoortOverzicht>" +dataBetween$("<cbc:InvoiceTypeCode>", "</cbc:InvoiceTypeCode>", data$) +"</A201029SoortOverzicht>" print #xml, " <A201136Faktuurnummer>" +dataBetween$( "<cbc:ID>", "</cbc:ID>", data$) +"</A201136Faktuurnummer>" print #xml, " <A201134Faktuurdatum>" +dataBetween$( "<cbc:IssueDate>", "</cbc:IssueDate>", data$) +"</A201134Faktuurdatum>" 'print #xml, " <A203817DatumIncasso>" +dataBetween$( "<cbc:IssueDate>", "</cbc:IssueDate>", data$) +"</A203817DatumIncasso>" factuurdatum$ = dataBetween$("<cbc:IssueDate>", "</cbc:IssueDate>", data$) print #xml, " <A800004UniekBerichtNummer>/" +dataBetween$( "<cbc:ID>", "</cbc:ID>", data$) +"</A800004UniekBerichtNummer>" print #xml, " <A800006AssociationAssignedCode>EVF2.2</A800006AssociationAssignedCode>" print #xml, " <A204654Toelichting> / " +dataBetween$( "<cbc:ID>", "</cbc:ID>", data$) +"</A204654Toelichting>"
else goto [supplier] end if
scan goto [lo1]
[supplier] start$ ="<cac:AccountingSupplierParty>" lenS =len(start$) finish$ ="</cac:AccountingSupplierParty>" lenF =len( finish$) current = 1 [lo2] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
' print right$( " " +str$( itemNumber), 4), print #xml, " <AdedLeverancier>" print #xml, " <A700129Naam>" +dataBetween$( "<cbc:Name>","</cbc:Name>" , data$) +"</A700129Naam>" print #xml, " <A700766PostadresStraat>" +dataBetween$( "<cbc:StreetName>","</cbc:StreetName>", data$) +"</A700766PostadresStraat>" print #xml, " <A700767PostadresHuisnummer />" print #xml, " <A700768PostadresPlaats>" +dataBetween$( "<cbc:CityName>","</cbc:CityName>", data$) +"</A700768PostadresPlaats>" print #xml, " <A204587PostadresPostcode>" +dataBetween$( "<cbc:PostalZone>","</cbc:PostalZone>", data$) +"</A204587PostadresPostcode>" print #xml, " <A700770PostadresLand>" +dataBetween$( "<cbc:IdentificationCode>","</cbc:IdentificationCode>", data$) +"</A700770PostadresLand>" btwnrsup$ = dataBetween$( "<cbc:CompanyID>","</cbc:CompanyID>", data$) debiteur$ = dataBetween$( "<cbc:Telephone>","</cbc:Telephone>", data$) 'print #xml, "<A202637Btw>" +dataBetween$( "<cbc:Percent>", "</cbc:Percent>", data$) +"</A202637Btw>" 'print #xml, "<A702004BedragBtw>" +dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>",data$) +"</A702004BedragBtw>" 'print #xml, "</AdedBtw>" else
goto [financial] end if
scan goto [lo2]
[financial] start$ ="<cac:PaymentMeans>" lenS =len(start$) finish$ ="</cac:PaymentMeans>" lenF =len( finish$) current = 1 [lo3] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
' print right$( " " +str$( itemNumber), 4),
print #xml, " <A204581Iban>" +dataBetween$( "<cbc:ID>", "</cbc:ID>", data$) +"</A204581Iban>" 'print #xml, " <A204582Bic>" +dataBetween$( "<cbc:ID schemeName=" +quotes$ +"BIC" +quotes$ +">", "</cbc:ID>", data$) +"</A204582Bic>" print #xml, " <A204651VatNumberInternational>" +btwnrsup$ +"</A204651VatNumberInternational>" print #xml, " </AdedLeverancier>" else
goto [klant] end if
scan goto [lo3]
[klant] start$ ="<cac:AccountingCustomerParty>" lenS =len(start$) finish$ ="</cac:AccountingCustomerParty>" lenF =len( finish$) current = 1 [lo4] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
' print right$( " " +str$( itemNumber), 4),
print #xml, " <AdedAfnemer>" print #xml, " <A700129Naam>" +dataBetween$( "<cbc:Name>","</cbc:Name>" , data$) +"</A700129Naam>" print #xml, " <A700766PostadresStraat>" +dataBetween$( "<cbc:StreetName>","</cbc:StreetName>", data$) +"</A700766PostadresStraat>" print #xml, " <A700767PostadresHuisnummer />" print #xml, " <A700768PostadresPlaats>" +dataBetween$( "<cbc:CityName>","</cbc:CityName>", data$) +"</A700768PostadresPlaats>" print #xml, " <A204587PostadresPostcode>" +dataBetween$( "<cbc:PostalZone>","</cbc:PostalZone>", data$) +"</A204587PostadresPostcode>" print #xml, " <A700770PostadresLand>" +dataBetween$( "<cbc:IdentificationCode>","</cbc:IdentificationCode>", data$) +"</A700770PostadresLand>" print #xml, " <A203813Relatienummer>"+debiteur$ +"</A203813Relatienummer>" print #xml, " <A203838CodeBetaling>1</A203838CodeBetaling>" print #xml, " </AdedAfnemer>" else
goto [invoiceline] end if
scan goto [lo4]
[invoiceline] start$ ="<cac:InvoiceLine>" lenS =len(start$) finish$ ="</cac:InvoiceLine>" lenF =len( finish$) current = 1 [lo5] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
' print right$( " " +str$( itemNumber), 4),
print #xml, " <AdedLevering>" print #xml, " <A702078Regelnummer>"; r; "</A702078Regelnummer>" print #xml, " <A702164DatumLevering>" +factuurdatum$ +"</A702164DatumLevering>" print #xml, " <A300225BonnummerLeveringsbon>"; r; "</A300225BonnummerLeveringsbon>" print #xml, " <A702165Leveringsbonregelnummer>"; r; "</A702165Leveringsbonregelnummer>" print #xml, " <A203811BedragBrutoExBtw>" +dataBetween$( "<cbc:TaxableAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxableAmount>", data$) +"</A203811BedragBrutoExBtw>" btwex$(r) = dataBetween$( "<cbc:TaxableAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxableAmount>", data$) btwex(r) = val(btwex$(r)) print #xml, " <A203862BedragNettoExBtw>" +dataBetween$( "<cbc:TaxableAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxableAmount>", data$) +"</A203862BedragNettoExBtw>" Code$ =dataBetween$( "<cbc:InvoicedQuantity ", quotes$ +">", data$) invoiceCode$ =right$( Code$, 2)
I$ = dataBetween$( quotes$ +">", "</", dataBetween$( "<cbc:InvoicedQuantity ", "</cbc:InvoicedQuantity>", data$)) print #xml, " <A203822HoeveelheidProdukt>"+I$ +"</A203822HoeveelheidProdukt>" print #xml, " <A204652Eenheid>ST</A204652Eenheid>" print #xml, " <AdedProdukt>" print #xml, " <A203819Artikelcode>" +dataBetween$( "<cac:SellersItemIdentification>"+enter$+enter2$+"<cbc:ID>","</cbc:ID>",data$) +"</A203819Artikelcode>" print #xml, " <A401013PrijsBruto>" +dataBetween$( "<cbc:PriceAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:PriceAmount>",data$) +"</A401013PrijsBruto>" print #xml, " <A203820OmschrijvingProdukt>" +dataBetween$( "<cbc:Name>","</cbc:Name>",data$) +"</A203820OmschrijvingProdukt>" print #xml, " <AdedBtw>" print #xml, " <A202637Btw>" +dataBetween$( "<cbc:Percent>", "</cbc:Percent>", data$) +"</A202637Btw>" btwpr$(r) = dataBetween$( "<cbc:Percent>", "</cbc:Percent>", data$) btwpr(r) = val(btwpr$(r)) print #xml, " <A702004BedragBtw>" +dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>",data$) +"</A702004BedragBtw>" btw$(r) = dataBetween$( "<cbc:TaxAmount currencyID=" +quotes$ +"EUR" +quotes$ +">", "</cbc:TaxAmount>",data$) btw(r) = val(btw$(r)) if btwpr(r) = 0 then totbtw0 = btw(r) +totbtw0 if btwpr(r) = 9 then totbtw9 = btw(r) +totbtw9 if btwpr(r) = 21 then totbtw21 = btw(r) +totbtw21 print "jack:",totbtw21 if btwpr(r) = 0 then totex0 = btwex(r) +totex0 if btwpr(r) = 9 then totex9 = btwex(r) +totex9 if btwpr(r) = 21 then totex21 = btwex(r) +totex21 print btw(r) print #xml, " </AdedBtw>" print #xml, " </AdedProdukt>" print #xml, " </AdedLevering>" else
goto [btw0] end if
scan r= r +1 goto [lo5]
[btw0]
print #xml, " <AdedBtw1>" print #xml, " <A202637Btw>0.00</A202637Btw>" print #xml, " <A203836BtwGrondslag>";totex0;"</A203836BtwGrondslag>"; print #xml, " <A702004BedragBtw>";totbtw0;"</A702004BedragBtw>"; print #xml, " </AdedBtw1>"
goto [btw9]
[btw9]
print #xml, " <AdedBtw1>" print #xml, " <A202637Btw>9.00</A202637Btw>" print #xml, " <A203836BtwGrondslag>";totex9;"</A203836BtwGrondslag>"; print #xml, " <A702004BedragBtw>";totbtw9;"</A702004BedragBtw>"; print #xml, " </AdedBtw1>"
goto [btw21]
[btw21]
print #xml, " <AdedBtw1>" print #xml, " <A202637Btw>21.00</A202637Btw>" print #xml, " <A203836BtwGrondslag>";totex21;"</A203836BtwGrondslag>"; print #xml, " <A702004BedragBtw>";totbtw21;"</A702004BedragBtw>"; print #xml, " </AdedBtw1>"
goto [totaal]
[totaal] start$ = "<cac:LegalMonetaryTotal>" lenS =len(start$) finish$ = "</cac:LegalMonetaryTotal>" lenF =len( finish$) current = 1 [lo8] pointer =instr( file$, start$, current) if pointer <>0 then itemNumber =itemNumber +1
endItem =instr( file$, finish$, pointer +2) data$ =mid$( file$, pointer +lenS , endItem -pointer -lenF) current =endItem
totaal=totbtw0+totbtw9+totbtw21+totex0+totex9+totex21 print #xml, " <AdedTotalen>" print #xml, " <A700154Bedrag>";totaal;"</A700154Bedrag>"; print #xml, " <A204655Bedragqualifier>86</A204655Bedragqualifier>" print #xml, " </AdedTotalen>" else
goto [verder] end if
scan goto [lo8]
[verder]
print #xml, "</AdedTransaction>" close #xml
end '
function dataBetween$( start$, finish$, source$) S =instr( source$, start$) +len( start$) rightHand$ =mid$( source$, S) d$ =upto$( rightHand$, finish$) dataBetween$ =d$ end function
|
|
|
QR code
Sept 27, 2020 15:47:16 GMT -5
Post by tenochtitlanuk on Sept 27, 2020 15:47:16 GMT -5
Congratulations on getting so far with a program that does something commercially useful to you! Don't have much time at present to look at the code or what you want to do next- but keep us in touch with developments!
|
|