307 lines
13 KiB
Plaintext
307 lines
13 KiB
Plaintext
*!* 11.12.2018
|
|
*!* am folosit o schema XSD la importul din xml facturi saga
|
|
|
|
Set Century On
|
|
Set Ansi On
|
|
Set Exact On
|
|
Set Deleted On
|
|
Set Date Dmy
|
|
Set Safety Off
|
|
Set Exclusive Off
|
|
Close Databases All
|
|
Set Default To Justpath(Sys(16,0))
|
|
SET PATH TO 'd:\roa\roacont\comun\programe;' + ;
|
|
'd:\roa\roacont\comun\utile\excel;' ADDITIVE
|
|
|
|
SET PROCEDURE TO regex.prg ADDITIVE
|
|
Set Procedure To importfromxlsx.prg Additive
|
|
Set Classlib To vfpxworkbookxlsx.vcx Additive
|
|
|
|
_Screen.Caption = "SmartBill2ROA"
|
|
|
|
Messagebox('Pentru import din SmartBill sunt necesare fisierele: ' + Chr(13) + Chr(10) + ;
|
|
'1. facturi export saga (format xml) ex: F_CUI_multiple_zz.ll.aaaa.xml' + Chr(13) + Chr(10) + Chr(13) + Chr(10) + ;
|
|
'2. facturi export format xlsx ex: Facturi_zz_ll_aaaa.xlsx' + Chr(13) + Chr(10) + ;
|
|
'* Primul rand din fisierul xlsx trebuie sa fie completat cu denumirile coloanelor',0+64,_Screen.Caption)
|
|
|
|
|
|
*****************************************************************
|
|
* xml saga - nu are judet/localitate pentru clienti
|
|
lcFileFacturi = Getfile("xml","Facturi SAGA","Alege", 0, "Alege fisierul xml cu exportul de facturi in format SAGA") && "E:\pass\F_RO16148853_multiple_01.08.2018.xml"
|
|
If Empty(m.lcFileFacturi)
|
|
Return
|
|
Endif
|
|
If Lower(Justext(m.lcFileFacturi)) <> 'xml'
|
|
Messagebox('Fisierul trebuie sa fie de tip xml!',0+48,_Screen.Caption)
|
|
Return
|
|
Endif
|
|
|
|
lcFileFacturiXlsx = Getfile("xlsx","Facturi excel","Alege", 0, "Alege fisierul xlsx cu exportul de facturi in format excel") && 'e:\pass\Facturi_12_09_2018.xlsx'
|
|
If Empty(m.lcFileFacturiXlsx)
|
|
Return
|
|
Endif
|
|
If Lower(Justext(m.lcFileFacturiXlsx)) <> 'xlsx'
|
|
Messagebox('Fisierul trebuie sa fie de tip xlsx!',0+48,_Screen.Caption)
|
|
Return
|
|
Endif
|
|
*****************************************************************
|
|
|
|
*!* lcFileClienti = 'e:\pass\cli_11_09_2018.xml'
|
|
lcText = Filetostr(m.lcFileFacturi)
|
|
|
|
* ANTETE
|
|
lcAntete = ""
|
|
lnAntete = Occurs("<antet>", Lower(m.lcText))
|
|
For m.lnAntet = 1 To m.lnAntete
|
|
lcAntet = Strextract(m.lcText, "<antet>", "</antet>", lnAntet,1+4)
|
|
lcAntete = m.lcAntete + lcAntet + Chr(13) + Chr(10)
|
|
Endfor
|
|
|
|
lcAntete = "<vfpdata>" + m.lcAntete + "</vfpdata>"
|
|
|
|
Xmltocursor(m.lcAntete,"cAntetTemp")
|
|
Select Cast(0 As I) As nAntet, * From cAntetTemp Into Cursor cAntet Readwrite
|
|
Use In (Select('cAntetTemp'))
|
|
Replace All nAntet With Recno() In cAntet
|
|
|
|
|
|
***********
|
|
* Detalii
|
|
create cursor cDetalii ;
|
|
(NANTET I, ;
|
|
CANTITATE N(10,3), ;
|
|
CODARTICOLCLIENT C(20), ;
|
|
CODARTICOLFURNIZOR C(20), ;
|
|
CODBARE C(20), ;
|
|
DESCRIERE C(100), ;
|
|
GESTIUNE C(20), ;
|
|
INFORMATIISUPLIMENTARE C(100), ;
|
|
LINIENRCRT I, ;
|
|
PRET N(20,4), ;
|
|
PROCTVA N(5,2), ;
|
|
UM C(10), ;
|
|
VALOARE N(20,4), ;
|
|
TVA N(20,4))
|
|
|
|
TEXT TO lcXSD NOSHOW
|
|
<xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
|
|
<xsd:element name="VFPData" msdata:IsDataSet="true">
|
|
<xsd:complexType>
|
|
<xsd:choice maxOccurs="unbounded">
|
|
<xsd:element name="cdetalii" minOccurs="0" maxOccurs="unbounded">
|
|
<xsd:complexType>
|
|
<xsd:sequence>
|
|
<xsd:element name="cantitate">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:decimal">
|
|
<xsd:totalDigits value="9"/>
|
|
<xsd:fractionDigits value="3"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="codarticolclient">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:string">
|
|
<xsd:maxLength value="20"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="codarticolfurnizor">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:string">
|
|
<xsd:maxLength value="20"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="codbare">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:string">
|
|
<xsd:maxLength value="20"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="descriere">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:string">
|
|
<xsd:maxLength value="100"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="gestiune">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:string">
|
|
<xsd:maxLength value="20"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="informatiisuplimentare">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:string">
|
|
<xsd:maxLength value="100"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="linienrcrt" type="xsd:int"/>
|
|
<xsd:element name="pret">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:decimal">
|
|
<xsd:totalDigits value="19"/>
|
|
<xsd:fractionDigits value="4"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="proctva">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:decimal">
|
|
<xsd:totalDigits value="4"/>
|
|
<xsd:fractionDigits value="2"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="um">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:string">
|
|
<xsd:maxLength value="10"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="valoare">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:decimal">
|
|
<xsd:totalDigits value="19"/>
|
|
<xsd:fractionDigits value="4"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
<xsd:element name="tva">
|
|
<xsd:simpleType>
|
|
<xsd:restriction base="xsd:decimal">
|
|
<xsd:totalDigits value="19"/>
|
|
<xsd:fractionDigits value="4"/>
|
|
</xsd:restriction>
|
|
</xsd:simpleType>
|
|
</xsd:element>
|
|
</xsd:sequence>
|
|
</xsd:complexType>
|
|
</xsd:element>
|
|
</xsd:choice>
|
|
<xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace" processContents="lax"/>
|
|
</xsd:complexType>
|
|
</xsd:element>
|
|
</xsd:schema>
|
|
ENDTEXT
|
|
|
|
lcDetalii = ""
|
|
lnDetalii = Occurs("<continut>", Lower(m.lcText))
|
|
For m.lnDetaliu = 1 To m.lnDetalii
|
|
lcDetaliu = Strextract(m.lcText, "<continut>", "</continut>", lnDetaliu,1+4)
|
|
lcDetaliu = STRTRAN(m.lcDetaliu, "<continut>", "<continut>" + CHR(13) + CHR(10) + m.lcXSD + CHR(13) + CHR(10),1,1,1) && adaug schema XSD
|
|
lcDetaliu = STRTRAN(m.lcDetaliu, "</continut>", "</continut>",1,1,1) && modific tag-ul de sfarsit tot cu litere mici
|
|
Xmltocursor(m.lcDetaliu,"cDetaliu")
|
|
Select m.lnDetaliu As nAntet, * From cDetaliu Into Cursor cDetaliiTemp Readwrite
|
|
Select cDetalii
|
|
Append From Dbf('cDetaliiTemp')
|
|
|
|
Use In (Select("cDetaliu"))
|
|
Use In (Select("cDetaliiTemp"))
|
|
|
|
Endfor
|
|
|
|
Select Padr(Upper(Alltrim(a.clientadresa)), 250, ' ') As adresa, a.clientbanca As banca, Padr(Strtran(Upper(a.clientcif), '-', ''), 20, ' ') As cod_fiscal, a.clientiban As cont_banca, a.clientnrregcom As reg_com, Upper(a.clientnume) As denumire, Ctod(a.facturadata) As dataact, a.facturanumar As nrfact, Ctod(a.facturascadenta) As datascad, ;
|
|
ROUND(b.cantitate,3) As cantitate, Upper(b.descriere) As articol, b.proctva as proctva, ;
|
|
ROUND(b.valoare,2) As valftva, ;
|
|
ROUND(b.tva,2) As valtva, ;
|
|
CAST(0 as N(20,2)) as valctva, ;
|
|
UPPER(a.facturatvaincasare) As tvaincasare, Upper(a.facturataxareinversa) As taxareinversa ;
|
|
From cAntet a Left Join cDetalii b On a.nAntet = b.nAntet ;
|
|
Into Cursor cFacturiSaga READWRITE
|
|
|
|
UPDATE cFacturiSaga SET valctva = valftva + valtva
|
|
|
|
Use In (Select('cAntet'))
|
|
Use In (Select('cDetalii'))
|
|
|
|
*********
|
|
* Clienti
|
|
* cod, denumire, cod_fiscal, reg_com, tara, judet, adresa, cont_banca, banca, tel, email, discount, informatii
|
|
* "cod","Elena Tomescu","-","reg_com","RO","B","Loc. Bucuresti,Calea Serban Voda nr 133 Corp A , etaj 1, receptie Provident, Cod Postal 40204", "cont_banca", "banca", "tel", "email", "discount", "informatii"
|
|
* Xmltocursor(m.lcFileClienti,'cClienti',512)
|
|
|
|
* Import first sheet
|
|
|
|
importfromxlsx(m.lcFileFacturiXlsx,2,'sheet 1',.T.,.T.,1,'facturitemp')
|
|
|
|
*!* * sunt fisiere doar cu (valoaretotala), altele cu (valoarefaratva, valoaretva, valoaretotala)
|
|
*!* IF TYPE('facturitemp.valoarefaratva') <> 'U'
|
|
*!* Select Padr(Upper(client), 100, ' ') As denumire, Padr(Strtran(Upper(cif), '-', ''), 20, ' ') As cod_fiscal, Padr(Upper(Alltrim(adresa)), 250, ' ') As adresa, Padr(Alltrim(factura), 20, ' ') As nrfact, Ctod(Alltrim(dataemiterii)) As datafact, Ctod(Alltrim(datascadentei)) As datascad, ;
|
|
*!* Round(VAL(TRANSFORM(valoarefaratva)), 2) As valftva, ;
|
|
*!* Round(VAL(TRANSFORM(valoaretva)), 2) As valtva, ;
|
|
*!* Round(VAL(TRANSFORM(valoaretotala)), 2) As valctva ;
|
|
*!* From facturitemp ;
|
|
*!* Into Cursor cFacturiXls
|
|
*!* ELSE
|
|
*!* Select Padr(Upper(client), 100, ' ') As denumire, Padr(Strtran(Upper(cif), '-', ''), 20, ' ') As cod_fiscal, Padr(Upper(Alltrim(adresa)), 250, ' ') As adresa, Padr(Alltrim(factura), 20, ' ') As nrfact, Ctod(Alltrim(dataemiterii)) As datafact, Ctod(Alltrim(datascadentei)) As datascad, ;
|
|
*!* 0 As valftva, ;
|
|
*!* 0 As valtva, ;
|
|
*!* Round(VAL(TRANSFORM(valoaretotala)), 2) As valctva ;
|
|
*!* From facturitemp ;
|
|
*!* Into Cursor cFacturiXls
|
|
*!* ENDIF
|
|
|
|
* Nu am nevoie de valori din fisierul facturitemp
|
|
Select Padr(Upper(client), 100, ' ') As denumire, Padr(Strtran(Upper(cif), '-', ''), 20, ' ') As cod_fiscal, Padr(Upper(Alltrim(adresa)), 250, ' ') As adresa, ;
|
|
Padr(Alltrim(factura), 50, ' ') As nrfact, Ctod(Alltrim(dataemiterii)) As datafact, Ctod(Alltrim(datascadentei)) As datascad ;
|
|
From facturitemp ;
|
|
Into Cursor cFacturiXls
|
|
|
|
Use In (Select('facturitemp'))
|
|
|
|
|
|
*!* lcFile = 'e:\pass\Vanzari_Produse_12_09_2018.xlsx'
|
|
*!* importfromxlsx(m.lcFile,2,'sheet 1',.T.,.T.,1,'vanzari')
|
|
|
|
*!* SELECT UPPER(produs) as articol, PADR(UPPER(client), 100, ' ') as denumire, PADR(STRTRAN(UPPER(cifclient), '-', ''), 20, ' ') as cod_fiscal, PADR(UPPER(ALLTRIM(judetclient)), 100, ' ') as judet, PADR(ALLTRIM(document), 20, ' ') as nrfact, CTOD(ALLTRIM(data)) as datafact, ROUND(valoarefaratva, 2) as valftva, ROUND(valoarecutva,2)-ROUND(valoarefaratva, 2) as valtva, ROUND(valoarecutva, 2) as valctva FROM vanzari INTO CURSOR cVanzarix
|
|
|
|
Select a.articol, a.denumire, a.cod_fiscal, a.reg_com, a.nrfact, b.adresa, SPACE(20) as serie_act, CAST(0 as N(14)) As nract, a.dataact, a.datascad, a.proctva, a.valftva, a.valtva, a.valctva, '707' As cont7xx, '' As acont7xx, '' As acont4111, Iif(a.tvaincasare = 'DA', '4428', '4427') As cont442x, Iif(a.tvaincasare = 'DA', '2', ' ') As acont442x, ;
|
|
'NU' As incasat, '' As casa, '' As cont5xx, '' As acont5xx, ;
|
|
a.tvaincasare, a.taxareinversa ;
|
|
FROM cFacturiSaga a Left Join cFacturiXls b On a.nrfact = b.nrfact ;
|
|
ORDER By a.dataact, a.nrfact ;
|
|
INTO Cursor cFacturi Readwrite
|
|
|
|
* nract = ultime
|
|
SELECT cFacturi
|
|
SCAN
|
|
lcNrAct = Alltrim(Right(Alltrim(GetRegExp(ALLTRIM(nrfact), "[0-9]+$",1)),14))
|
|
lnNrAct = Int(Val(m.lcNrAct))
|
|
lcSerieAct = ALLTRIM(STRTRAN(nrfact, m.lcNrAct, '',1,1,1))
|
|
REPLACE serie_act WITH m.lcSerieAct, nract WITH m.lnNrAct
|
|
ENDSCAN
|
|
|
|
|
|
USE IN (SELECT('cFacturiSaga'))
|
|
USE IN (SELECT('cFacturiXls'))
|
|
|
|
Update cFacturi Set datascad = dataact Where Empty(datascad)
|
|
Update cFacturi Set cont7xx = '704' Where 'GRAVURA'$articol
|
|
Update cFacturi Set cont7xx = '708' Where 'CURIER'$articol OR 'COURIER'$articol OR 'TRANSPORT'$articol
|
|
Update cFacturi Set cont7xx = '667' Where 'DISCOUNT'$articol
|
|
Update cFacturi Set cont7xx = '419' Where 'GIFT CARD'$articol
|
|
*lcTable = ADDBS(JUSTPATH(m.lcFile)) + 'facturi.dbf'
|
|
Select denumire, cod_fiscal, reg_com, adresa, serie_act, nract, dataact, datascad, proctva, cont7xx, acont7xx, acont4111, cont442x, acont442x, incasat, casa, cont5xx, acont5xx, tvaincasare, taxareinversa,;
|
|
SUM(valftva) As valftva, Sum(valtva) As valtva ;
|
|
GROUP By denumire, cod_fiscal, reg_com, adresa, serie_act, nract, dataact, datascad, proctva, cont7xx, acont7xx, acont4111, cont442x, acont442x, incasat, casa, cont5xx, acont5xx, tvaincasare, taxareinversa ;
|
|
ORDER By dataact, nract ;
|
|
FROM cFacturi Into Cursor Facturi && (m.lcTable)
|
|
|
|
USE IN (SELECT('cFacturi'))
|
|
|
|
* EXPORT XLSX
|
|
lcExcel = Addbs(Justpath(m.lcFileFacturiXlsx)) + 'facturi_roa.xlsx'
|
|
|
|
loExcel = Newobject("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
|
|
loExcel.SaveTabletoWorkbook('facturi', lcExcel, .T., .T., 'Sheet1')
|
|
Messagebox('S-a salvat fisierul ' + m.lcExcel + CHR(13) + CHR(10) + 'Lansati ROACONT > Import > Note facturi clienti',0+64, _Screen.Caption)
|
|
|
|
USE IN (SELECT('Facturi'))
|