2398 lines
85 KiB
Plaintext
2398 lines
85 KiB
Plaintext
*--------------------------------------------------------------------------------------------------------------------------------------------------------
|
||
* (EN) AUTOGENERATED - ATTENTION!! - NOT INTENDED FOR EXECUTION!! USE ONLY FOR MERGING CHANGES AND STORING WITH SCM TOOLS!!
|
||
*--------------------------------------------------------------------------------------------------------------------------------------------------------
|
||
*< FOXBIN2PRG: Version="1.21" SourceFile="frm_initializare_facturi_balanta.scx" CPID="1252" /> (Solo para binarios VFP 9 / Only for VFP 9 binaries)
|
||
*
|
||
*
|
||
#INCLUDE "comun.h"
|
||
|
||
DEFINE CLASS dataenvironment AS dataenvironment
|
||
*< CLASSDATA: Baseclass="dataenvironment" Timestamp="" Scale="" Uniqueid="" ClassIcon="1" />
|
||
|
||
*<PropValue>
|
||
DataSource = .NULL.
|
||
Height = 200
|
||
Left = 1
|
||
Name = "Dataenvironment"
|
||
Top = 220
|
||
Width = 520
|
||
*</PropValue>
|
||
|
||
ENDDEFINE
|
||
|
||
DEFINE CLASS form1 AS form
|
||
*< CLASSDATA: Baseclass="form" Timestamp="" Scale="" Uniqueid="" />
|
||
|
||
*-- OBJECTDATA items order determines ZOrder / El orden de los items OBJECTDATA determina el ZOrder
|
||
*< OBJECTDATA: ObjPath="cmdNoteFacturiBalanta" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="edtInfo" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="cmdTemplate" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="chkModificaNote" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="cmdStergeNote" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="cmdInitBalanta" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="chkParteneriBalantaVerificare" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="optInit" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="cmdInitFacturi" UniqueID="" Timestamp="" />
|
||
*< OBJECTDATA: ObjPath="But_help1" UniqueID="" Timestamp="" />
|
||
|
||
*<DefinedPropArrayMethod>
|
||
*m: completeazaparteneriroa
|
||
*m: connectroa
|
||
*m: creeazanote
|
||
*m: disconnectroa
|
||
*m: exportdate
|
||
*m: initbalanta
|
||
*m: initfacturi
|
||
*m: modificanote
|
||
*m: prelucreazafisierebreeze
|
||
*m: refreshcontrols
|
||
*m: scrienote
|
||
*m: sql_temp_insert
|
||
*m: sql_temp_insert_values
|
||
*m: sterge_import
|
||
*m: templatexlsx
|
||
*m: todo
|
||
*m: trace
|
||
*m: verificaimport
|
||
*p: nidpartnerepartizat
|
||
*p: _memberdata && XML Metadata for customizable properties
|
||
*</DefinedPropArrayMethod>
|
||
|
||
*<PropValue>
|
||
BackColor = 255,255,255
|
||
Caption = "Initializare facturi, balanta parteneri, Registre TVA"
|
||
DoCreate = .T.
|
||
Height = 444
|
||
Icon = ..\grafice\note.ico
|
||
Left = 0
|
||
Name = "Form1"
|
||
nidpartnerepartizat = 0
|
||
ShowWindow = 0
|
||
Top = 0
|
||
Width = 660
|
||
WindowType = 1
|
||
_memberdata = <VFPData>
|
||
<memberdata name="verificaimport" display="VerificaImport"/>
|
||
<memberdata name="modificanote" display="ModificaNote"/>
|
||
<memberdata name="completeazaparteneriroa" display="CompleteazaParteneriROA"/>
|
||
<memberdata name="templatexlsx" display="TemplateXLSX"/>
|
||
<memberdata name="initbalanta" display="InitBalanta"/>
|
||
<memberdata name="initfacturi" display="InitFacturi"/>
|
||
<memberdata name="refreshcontrols" display="RefreshControls"/>
|
||
<memberdata name="todo" display="ToDo"/>
|
||
</VFPData>
|
||
*</PropValue>
|
||
|
||
ADD OBJECT 'But_help1' AS but_help WITH ;
|
||
Anchor = 8, ;
|
||
Left = 616, ;
|
||
Name = "But_help1", ;
|
||
Top = 12
|
||
*< END OBJECT: ClassLib="..\clase\cmd_butoane.vcx" BaseClass="commandbutton" />
|
||
|
||
ADD OBJECT 'chkModificaNote' AS _checkbox WITH ;
|
||
Alignment = 0, ;
|
||
Caption = "Modifica note contabile facturi", ;
|
||
Left = 12, ;
|
||
Name = "chkModificaNote", ;
|
||
TabIndex = 6, ;
|
||
ToolTipText = "La initializarea facturilor se afiseaza formularul de modificare note inainte de salvarea in baza de date", ;
|
||
Top = 60, ;
|
||
Value = 1
|
||
*< END OBJECT: ClassLib="..\clase\_baza.vcx" BaseClass="checkbox" />
|
||
|
||
ADD OBJECT 'chkParteneriBalantaVerificare' AS _checkbox WITH ;
|
||
Alignment = 0, ;
|
||
AutoSize = .T., ;
|
||
Caption = "Initializare balanta/inregistrari parteneri din balanta de verificare", ;
|
||
Height = 18, ;
|
||
Left = 12, ;
|
||
Name = "chkParteneriBalantaVerificare", ;
|
||
TabIndex = 7, ;
|
||
ToolTipText = "Initializare balante si inregistrari parteneri din balanta de verificare 401,4111...", ;
|
||
Top = 84, ;
|
||
Value = 0, ;
|
||
Visible = .F., ;
|
||
Width = 380, ;
|
||
WordWrap = .T.
|
||
*< END OBJECT: ClassLib="..\clase\_baza.vcx" BaseClass="checkbox" />
|
||
|
||
ADD OBJECT 'cmdInitBalanta' AS commandbutton WITH ;
|
||
Anchor = 9, ;
|
||
AutoSize = .F., ;
|
||
BackColor = 255,98,176, ;
|
||
Caption = "Initializare \<balanta", ;
|
||
FontBold = .T., ;
|
||
FontSize = 10, ;
|
||
Height = 36, ;
|
||
Left = 536, ;
|
||
Name = "cmdInitBalanta", ;
|
||
TabIndex = 3, ;
|
||
Top = 108, ;
|
||
Visible = .F., ;
|
||
Width = 111, ;
|
||
WordWrap = .T.
|
||
*< END OBJECT: BaseClass="commandbutton" />
|
||
|
||
ADD OBJECT 'cmdInitFacturi' AS commandbutton WITH ;
|
||
Anchor = 9, ;
|
||
AutoSize = .F., ;
|
||
BackColor = 255,98,176, ;
|
||
Caption = "Initializare \<facturi", ;
|
||
FontBold = .T., ;
|
||
FontSize = 10, ;
|
||
Height = 36, ;
|
||
Left = 422, ;
|
||
Name = "cmdInitFacturi", ;
|
||
TabIndex = 3, ;
|
||
Top = 108, ;
|
||
Visible = .F., ;
|
||
Width = 111, ;
|
||
WordWrap = .T.
|
||
*< END OBJECT: BaseClass="commandbutton" />
|
||
|
||
ADD OBJECT 'cmdNoteFacturiBalanta' AS commandbutton WITH ;
|
||
Anchor = 0, ;
|
||
AutoSize = .F., ;
|
||
BackColor = 255,128,64, ;
|
||
Caption = "\<Import", ;
|
||
FontBold = .T., ;
|
||
FontSize = 10, ;
|
||
Height = 36, ;
|
||
Left = 137, ;
|
||
Name = "cmdNoteFacturiBalanta", ;
|
||
TabIndex = 1, ;
|
||
Top = 108, ;
|
||
Width = 111, ;
|
||
WordWrap = .T.
|
||
*< END OBJECT: BaseClass="commandbutton" />
|
||
|
||
ADD OBJECT 'cmdStergeNote' AS commandbutton WITH ;
|
||
Anchor = 0, ;
|
||
AutoSize = .F., ;
|
||
BackColor = 255,159,159, ;
|
||
Caption = "\<Sterge import", ;
|
||
FontBold = .T., ;
|
||
FontSize = 10, ;
|
||
ForeColor = 0,0,0, ;
|
||
Height = 36, ;
|
||
Left = 261, ;
|
||
Name = "cmdStergeNote", ;
|
||
TabIndex = 4, ;
|
||
TabStop = .F., ;
|
||
ToolTipText = "Sterge note importate, maxim o luna. Pentru mai multe luni trebuie repetata operatia cu alta perioada", ;
|
||
Top = 108, ;
|
||
Width = 111, ;
|
||
WordWrap = .T.
|
||
*< END OBJECT: BaseClass="commandbutton" />
|
||
|
||
ADD OBJECT 'cmdTemplate' AS commandbutton WITH ;
|
||
Anchor = 0, ;
|
||
AutoSize = .F., ;
|
||
BackColor = 74,165,255, ;
|
||
Caption = "Sablon \<xlsx", ;
|
||
FontBold = .T., ;
|
||
FontSize = 10, ;
|
||
Height = 36, ;
|
||
Left = 13, ;
|
||
Name = "cmdTemplate", ;
|
||
TabIndex = 2, ;
|
||
ToolTipText = "Genereaza fisier sablon xlsx pentru import facturi, balanta ", ;
|
||
Top = 108, ;
|
||
Width = 111, ;
|
||
WordWrap = .T.
|
||
*< END OBJECT: BaseClass="commandbutton" />
|
||
|
||
ADD OBJECT 'edtInfo' AS editbox WITH ;
|
||
Anchor = 15, ;
|
||
Height = 277, ;
|
||
Left = 11, ;
|
||
Name = "edtInfo", ;
|
||
TabIndex = 5, ;
|
||
Top = 156, ;
|
||
Width = 636
|
||
*< END OBJECT: BaseClass="editbox" />
|
||
|
||
ADD OBJECT 'optInit' AS _optiongrup WITH ;
|
||
BorderStyle = 0, ;
|
||
Left = 7, ;
|
||
Name = "optInit", ;
|
||
Top = 9, ;
|
||
Option1.AutoSize = .T., ;
|
||
Option1.Caption = "Generare note in Registrul Jurnal pentru initializare facturi si balanta", ;
|
||
Option1.FontSize = 10, ;
|
||
Option1.Left = 5, ;
|
||
Option1.Name = "Option1", ;
|
||
Option1.Top = 5, ;
|
||
Option2.AutoSize = .T., ;
|
||
Option2.Caption = "Initializare directa facturi si balanta", ;
|
||
Option2.FontSize = 10, ;
|
||
Option2.Left = 5, ;
|
||
Option2.Name = "Option2", ;
|
||
Option2.Top = 24
|
||
*< END OBJECT: ClassLib="..\clase\_baza.vcx" BaseClass="optiongroup" />
|
||
|
||
PROCEDURE completeazaparteneriroa
|
||
ENDPROC
|
||
|
||
PROCEDURE connectroa
|
||
ENDPROC
|
||
|
||
PROCEDURE creeazanote
|
||
*********************************************
|
||
*** Generare ID_FACT pentru fiecare factura
|
||
*** Completez id_fact din vireg_parteneri sau generez din secventa
|
||
*********************************************
|
||
Local lcMesaj, lcSql, lcSqlId, lcSqlIdIreg, llSucces, lnAchitat, lnAchitatVal, lnCota, lnFacturat
|
||
Local lnFacturatVal, lnIdSet, lnLinie, lnTVA, lnTotVal, lnTotctva, lnValoare, loFact, ldDataActEmpty
|
||
Local lcAcont4428, lcCont, lcExplicatia, lcPartNerepartizat, lcTip, lcTipDC, lnIdPartNerepartizat
|
||
Local lnSoldFactura, loRec
|
||
|
||
PRIVATE pnCod, pnPerecheC, pnPerecheD, pnSumaVal, pnro11nb, pnro11nt, pnro21nb, pnro21nt
|
||
PRIVATE pcAcont, pcAscc, pcAscd, pcCodFiscal, pcCont, pcDenumire, pcExplicatia, pcFdoc, pcPartC
|
||
PRIVATE pcPartD, pcScc, pcScd, pcSerieAct, pcSucursala, pdDataAct, pdDataIreg, pdDataScad, pnAn
|
||
PRIVATE pnCredit, pnDebit, pnIdFact, pnIdFactC, pnIdFactD, pnIdFdoc, pnIdJtva, pnIdPart, pnIdPartC
|
||
PRIVATE pnIdPartD, pnIdSucursala, pnIdUtil, pnIdValuta, pnLuna, pnNrAct, pnProcTva, pnSuma
|
||
PRIVATE pnTotCtva, pnTotftvatax, pnTottvatax, pnValCredit, pnValDebit, pnro19nb, pnro19nt, pnro20nb
|
||
PRIVATE pnro20nt, pnro24nb, pnro24nt, pnro5nb, pnro5nt, pnro9nb, pnro9nt, ptDataOra
|
||
lnIdSet = 90024 && initializare solduri
|
||
lnIdPartNerepartizat = Thisform.nIdPartNerepartizat
|
||
lcPartNerepartizat = 'NEREPARTIZAT'
|
||
|
||
lcSqlId = [SELECT SEQ_IdFact.NEXTVAL as id_fact FROM DUAL]
|
||
TEXT TO lcSqlIdIreg TEXTMERGE NOSHOW
|
||
SELECT MAX(id_fact) as id_fact FROM ireg_parteneri I JOIN NOM_PARTENERI P ON I.ID_PART = P.ID_PART WHERE I.an = ?pnAn AND I.luna = ?pnLuna AND I.cont = ?pcCont AND I.nract = ?pnNrAct AND I.dataact = ?pdDataAct AND NVL(P.cod_fiscal, 'x') = NVL(?pcCodFiscal, 'x') AND NVL(p.denumire, 'x') = NVL(?pcDenumire, 'x')
|
||
ENDTEXT
|
||
|
||
This.Trace('Verificare si generare Id-uri facturi')
|
||
|
||
Select Cast(0 As I) As id_fact, an, luna, Cont, acont, serie_act, nract, Dataact, datascad, denumire, cod_fiscal, reg_com, id_valuta, Count(*) As nr ;
|
||
From cFacturi ;
|
||
Group By an, luna, Cont, acont, serie_act, nract, Dataact, datascad, denumire, cod_fiscal, reg_com, id_valuta ;
|
||
Into Cursor cFact Readwrite
|
||
|
||
llSucces = .F.
|
||
Select cFact
|
||
Scan
|
||
Scatter Name loFact
|
||
pnAn = an
|
||
pnLuna = luna
|
||
pcCont = Alltrim(Cont)
|
||
pnNrAct = nract
|
||
pdDataAct = Dataact
|
||
pcCodFiscal = Upper(Alltrim(cod_fiscal))
|
||
pcDenumire = Upper(Alltrim(denumire))
|
||
|
||
Wait Window 'Verificare/generare id factura ' + Transform(Recno()) + '/' + Transform(Reccount()) Nowait
|
||
|
||
* Caut factura in vireg_parteneri si intorc id_fact
|
||
pnIdFact = 0
|
||
llSucces = goExecutor.oSelecteaza2Value(m.lcSqlIdIreg, @pnIdFact)
|
||
If !m.llSucces
|
||
lcMesaj = goExecutor.cEroare
|
||
*Messagebox(m.lcMesaj)
|
||
This.Trace(m.lcMesaj)
|
||
Exit
|
||
Endif
|
||
If !Empty(NVL(m.pnIdFact,0))
|
||
loFact.id_fact = m.pnIdFact
|
||
Else
|
||
* generez id_fact din secventa
|
||
llSucces = goExecutor.oSelecteaza2Value(m.lcSqlId, @pnIdFact)
|
||
If !m.llSucces
|
||
lcMesaj = goExecutor.cEroare
|
||
*Messagebox(m.lcMesaj)
|
||
This.Trace(m.lcMesaj)
|
||
Exit
|
||
Endif
|
||
If !Empty(NVL(m.pnIdFact,0))
|
||
loFact.id_fact = m.pnIdFact
|
||
Else
|
||
llSucces = .F.
|
||
This.Trace('Nu s-a generat id_fact!')
|
||
AMessagebox('Nu s-a generat id_fact!', 0 + 48, _Screen.Caption)
|
||
Exit
|
||
Endif
|
||
Endif
|
||
Use In (Select('cIdFact'))
|
||
|
||
If m.llSucces And loFact.id_fact <> 0
|
||
Update cFacturi Set id_fact = loFact.id_fact Where cont = loFact.cont AND acont = loFact.acont AND serie_act = loFact.serie_act And nract = loFact.nract And Dataact = loFact.Dataact And Nvl(id_valuta, 0) = Nvl(loFact.id_valuta, 0) And Nvl(cod_fiscal, '') = Nvl(loFact.cod_fiscal, '') And Nvl(denumire, '') = Nvl(loFact.denumire, '')
|
||
Else
|
||
llSucces = .F.
|
||
AMessagebox('Nu s-a gasit/generat id-ul de factura. Se opreste generarea de id-uri!', 0 + 48, _Screen.Caption)
|
||
Exit
|
||
Endif
|
||
Endscan
|
||
Use In (Select('cFact'))
|
||
|
||
If !m.llSucces
|
||
Return m.llSucces
|
||
Endif
|
||
|
||
*********************************************
|
||
*** Unific facturile duplicate din Facturi cumparari/vanzari si Jurnal TVA Cumparari/Vanzari
|
||
*********************************************
|
||
This.Trace('Unificare facturi Facturi cumparari/vanzari si Jurnal TVA Cumparari/Vanzari')
|
||
* Unific facturile duplicate fara tva neexigibil cu cele cu tva neexigibil, daca exista
|
||
* Pot aparea daca in facturi.xlsx sunt si registrele de tva si facturile cu sold. aceeasi factura poate aparea de 2 ori
|
||
|
||
|
||
* FACTURI FARA TVA NEEXIGIBIL
|
||
Select an, luna, Cont, acont, id_part, id_fact, tip, serie_act, nract, dataact, datascad, denumire, cod_fiscal, explicatia, facturat, achitat, facturatval, achitatval, id_valuta, valuta, valoare, totval, tva, acont4428, baza21, tva21, baza11, tva11, baza19, tva19, baza09, tva09, baza05, tva05, baza24, tva24, baza20, tva20 ;
|
||
from cFacturi Where tip <> 'BALANTA' AND tva = 0 Into Cursor cFacturi1
|
||
|
||
* FACTURI CU TVA NEEXIGIBIL
|
||
Select an, luna, Cont, acont, id_part, id_fact, tip, serie_act, nract, dataact, datascad, denumire, cod_fiscal, explicatia, facturat, achitat, facturatval, achitatval, id_valuta, valuta, valoare, totval, tva, acont4428, baza21, tva21, baza11, tva11, baza19, tva19, baza09, tva09, baza05, tva05, baza24, tva24, baza20, tva20 ;
|
||
from cFacturi Where tip <> 'BALANTA' AND tva <> 0 Into Cursor cFacturi2
|
||
|
||
* SOLDURI BALANTA
|
||
Select an, luna, Cont, acont, 'D' as tipDC, id_fact, id_part, tip, serie_act, nract, dataact, datascad, denumire, cod_fiscal, explicatia, totdeb as valoare ;
|
||
from cFacturi Where tip = 'BALANTA' AND totdeb <> 0 ;
|
||
UNION ;
|
||
Select an, luna, Cont, acont, 'C' as tipDC, id_fact, id_part, tip, serie_act, nract, dataact, datascad, denumire, cod_fiscal, explicatia, totcred as valoare ;
|
||
from cFacturi Where tip = 'BALANTA' AND totcred <> 0 ;
|
||
ORDER BY an, luna, cont, acont, tipDC ;
|
||
Into Cursor cSolduriBalanta READWRITE
|
||
|
||
Select Nvl(f1.an, f2.an) As an, Nvl(f1.luna, f2.luna) As luna, Nvl(f1.Cont, f2.Cont) As Cont, Nvl(f1.acont, f2.acont) As acont, ;
|
||
' ' AS tipdc, ;
|
||
NVL(f1.id_part, f2.id_part) As id_part, Nvl(f1.tip, f2.tip) As tip, Nvl(f1.id_fact, f2.id_fact) As id_fact, ;
|
||
Nvl(f1.serie_act, f2.serie_act) As serie_act, Nvl(f1.nract, f2.nract) As nract, ;
|
||
NVL(f1.Dataact, f2.Dataact) As Dataact, Nvl(f1.datascad, f2.datascad) As datascad, ;
|
||
Nvl(f1.denumire, f2.denumire) As denumire, Nvl(f1.cod_fiscal, f2.cod_fiscal) As cod_fiscal, ;
|
||
f1.explicatia, ;
|
||
NVL(f1.id_valuta, f2.id_valuta) As id_valuta, ;
|
||
NVL(f1.valuta, f2.valuta) As valuta, ;
|
||
CAST(Nvl(f1.facturat, f2.facturat) As N(16, 2)) As facturat, Cast(Nvl(f1.facturatval, f2.facturatval) As N(16, 2)) As facturatval, ;
|
||
CAST(Nvl(f1.achitat, f2.achitat) As N(16, 2)) As achitat, Cast(Nvl(f1.achitatval, f2.achitatval) As N(16, 2)) As achitatval, ;
|
||
CAST(Nvl(f1.valoare, f2.valoare) As N(16, 2)) As valoare, ;
|
||
CAST(Nvl(f1.totval, f2.totval) As N(16, 2)) As totval, ;
|
||
CAST(Nvl(f2.tva, 0.00) As N(16, 2)) As tva, NVL(f2.acont4428, SPACE(4)) as acont4428, ;
|
||
CAST(Nvl(f2.baza21, 0.00) As N(16, 2)) As baza21, Cast(Nvl(f2.tva21, 0.00) As N(16, 2)) As tva21, ;
|
||
CAST(Nvl(f2.baza11, 0.00) As N(16, 2)) As baza11, Cast(Nvl(f2.tva11, 0.00) As N(16, 2)) As tva11, ;
|
||
CAST(Nvl(f2.baza19, 0.00) As N(16, 2)) As baza19, Cast(Nvl(f2.tva19, 0.00) As N(16, 2)) As tva19, ;
|
||
CAST(Nvl(f2.baza09, 0.00) As N(16, 2)) As baza09, Cast(Nvl(f2.tva09, 0.00) As N(16, 2)) As tva09, ;
|
||
CAST(Nvl(f2.baza05, 0.00) As N(16, 2)) As baza05, Cast(Nvl(f2.tva05, 0.00) As N(16, 2)) As tva05, ;
|
||
CAST(Nvl(f2.baza24, 0.00) As N(16, 2)) As baza24, Cast(Nvl(f2.tva24, 0.00) As N(16, 2)) As tva24, ;
|
||
CAST(Nvl(f2.baza20, 0.00) As N(16, 2)) As baza20, Cast(Nvl(f2.tva20, 0.00) As N(16, 2)) As tva20 ;
|
||
from cFacturi1 f1 Full Join cFacturi2 f2 On f1.id_fact = f2.id_fact ;
|
||
ORDER BY 1,2,3,4,11,10 ;
|
||
Into Cursor cFacturiTemp Readwrite
|
||
|
||
UPDATE cFacturiTemp SET tipDC = IIF(INLIST(ALLTRIM(cont), '401', '404', '462', '419', '408', '4551'), 'C', 'D')
|
||
|
||
Use In (Select('cFacturi1'))
|
||
Use In (Select('cFacturi2'))
|
||
|
||
* Scad valoarea facturilor din soldurile din balanta
|
||
SELECT cont, acont, tipDC, SUM(facturat-achitat) as sold, SUM(valoare) as valoare ;
|
||
FROM cFacturiTemp ;
|
||
group by cont, acont, tipDC ;
|
||
INTO CURSOR cSoldFacturiTemp READWRITE
|
||
|
||
* Scad valoarea 4428 tva neexibibil din balanta
|
||
SELECT '4428' as cont, acont4428 as acont, IIF(tipDC = 'D', 'C', 'D') as tipDC, SUM(tva21+tva11+tva19+tva09+tva05+tva24+tva20) as valoare ;
|
||
FROM cFacturiTemp ;
|
||
WHERE tva <> 0 ;
|
||
GROUP BY 1,2,3 ;
|
||
INTO CURSOR cSoldTVATemp
|
||
SELECT cSoldFacturiTemp
|
||
APPEND FROM DBF('cSoldTVATemp')
|
||
USE IN (select('cSoldTVATemp'))
|
||
|
||
|
||
SELECT cSoldFacturiTemp
|
||
SCAN
|
||
SCATTER NAME loRec
|
||
lnSoldFactura = IIF(loRec.sold <> 0, loRec.sold, loRec.valoare)
|
||
lcTipDC = ALLTRIM(tipDC)
|
||
|
||
SELECT cSolduriBalanta
|
||
LOCATE FOR cont = loRec.cont AND acont = loRec.acont AND tipDC = m.lcTipDC
|
||
IF !FOUND()
|
||
* generez id_fact din secventa
|
||
pnIdFact = 0
|
||
llSucces = goExecutor.oSelecteaza2Value(m.lcSqlId, @pnIdFact)
|
||
|
||
SELECT cSolduriBalanta
|
||
APPEND BLANK
|
||
REPLACE an WITH m.gnAn, luna WITH m.gnLuna, cont WITH loRec.cont, acont WITH loRec.acont, tip WITH 'BALANTA', ;
|
||
explicatia WITH 'DIFERENTA FACTURI', id_part WITH Thisform.nIdPartNerepartizat, denumire WITH 'NEREPARTIZAT', id_fact WITH m.pnIdFact, ;
|
||
nract WITH 1, dataact WITH GOMONTH(DATE(m.gnAn, m.gnLuna, 1),1)-1, ;
|
||
tipdc WITH m.lcTipDC
|
||
ELSE
|
||
REPLACE id_part WITH Thisform.nIdPartNerepartizat, denumire WITH 'NEREPARTIZAT'
|
||
ENDIF
|
||
|
||
REPLACE valoare WITH valoare - m.lnSoldFactura
|
||
|
||
ENDSCAN && cSoldFacturiTemp
|
||
USE IN (SELECT('cSoldFacturiTemp'))
|
||
|
||
* Pentru conturile cu balanta parteneri si inregistrari pun partener NEREPARTIZAT in conturile din BALANTA
|
||
* Ca sa creez balanta de parteneri
|
||
lcSql = [select cont, decode(fel_cont, '0', 'D', 'C') as tipdc, cu_inregistrari from CONFIG_CONT_IREG order by cont]
|
||
llSucces = goExecutor.oExecuta(m.lcSql, 'cContIregTemp')
|
||
IF m.llSucces
|
||
SELECT cContIregTemp
|
||
SCAN
|
||
lcCont = ALLTRIM(cont)
|
||
|
||
UPDATE cSolduriBalanta SET id_part = m.lnIdPartNerepartizat, denumire = m.lcPartNerepartizat WHERE ALLTRIM(cont) = m.lcCont
|
||
ENDSCAN
|
||
ENDIF
|
||
|
||
SELECT cFacturiTemp
|
||
APPEND FROM DBF('cSolduriBalanta')
|
||
USE IN (SELECT('cSolduriBalanta'))
|
||
|
||
|
||
|
||
ldDataActEmpty = Gomonth(Date(m.gnAn, m.gnLuna, 1), 1) - 1
|
||
Update cFacturiTemp Set Dataact = m.ldDataActEmpty Where Empty(Dataact)
|
||
Update cFacturiTemp Set datascad = Gomonth(Dataact, 1) Where Empty(datascad)
|
||
|
||
|
||
* Creez actactan
|
||
Create Cursor actactan (id_set I, an N(4), luna N(2), Dataact D, datascad D, dataireg D, serie_act C(20), nract N(20), proc_tva N(5, 2), id_jtva_coloana I NULL, scd C(4), ascd C(4), scc C(4), ascc C(4), ;
|
||
id_partd I, partd C(200), id_partc I, partc C(200), pereched N(14), perechec N(14), id_sucursala I Null, sucursala c(100) null, id_fdoc I, fdoc C(100), id_factd I, id_factc I, id_fact I, ;
|
||
tva_incasare N(1), cod I, suma N(20, 2), id_valuta I, nume_val C(20), suma_val N(20,2), explicatia C(200), id_util I, dataora T)
|
||
|
||
|
||
pnIdFdoc = 0
|
||
pcFdoc = 'FACTURA'
|
||
lcSql = [SELECT ID_FDOC FROM NOM_FDOC WHERE STERS = 0 AND INACTIV = 0 AND FEL_DOCUMENT = 'FACTURA']
|
||
llSucces = goExecutor.oSelecteaza2Value(m.lcSql, @pnIdFdoc)
|
||
|
||
This.Trace('Generare note contabile')
|
||
|
||
pnIdUtil = gnIdUtil
|
||
ptDataOra = Datetime()
|
||
Select cFacturiTemp
|
||
Scan
|
||
Scatter Name loFact
|
||
pnAn = an
|
||
pnLuna = luna
|
||
pcCont = Alltrim(Cont)
|
||
pcAcont = Alltrim(acont)
|
||
lcAcont4428 = ALLTRIM(NVL(acont4428,''))
|
||
lcTipDC = ALLTRIM(tipDC) && D/C
|
||
pcDenumire = Alltrim(denumire)
|
||
pnCod = 0
|
||
pnIdFact = id_fact
|
||
pnIdPart = id_part
|
||
pnIdSucursala = m.gnIdSucursala
|
||
pcSucursala = Iif(!Empty(Nvl(m.pnIdSucursala, 0)), m.gcFirma, '')
|
||
pnIdValuta = id_valuta
|
||
pcSerieAct = Alltrim(Nvl(serie_act, ''))
|
||
pnNrAct = nract
|
||
pdDataAct = Dataact
|
||
pdDataIreg = GOMONTH(DATE(m.gnAn, m.gnLuna, 1), 1) - 1
|
||
pdDataScad = datascad
|
||
lnValoare = valoare
|
||
lnTotVal = totval
|
||
lnTVA = tva
|
||
lnFacturat = facturat
|
||
lnAchitat = achitat
|
||
lnFacturatVal = facturatval
|
||
lnAchitatVal = achitatval
|
||
lcTip = ALLTRIM(tip) && FACTURA/BALANTA
|
||
lcExplicatia = ALLTRIM(NVL(explicatia,''))
|
||
|
||
If m.lnFacturat <> 0 Or m.lnFacturatVal <> 0
|
||
lnTotctva = m.lnFacturat
|
||
Else
|
||
lnTotctva = m.lnValoare
|
||
Endif
|
||
pnIdFdoc = IIF(m.lcTip = 'FACTURA', 42, 69) && 42 = FACTURA, 69 = SOLD
|
||
pnTotCtva = Iif(m.lnTotctva <> 0, m.lnTotctva, baza21 + tva21 + baza11 + tva11 + baza19 + tva19 + baza09 + tva09 + baza05 + tva05 + baza24 + tva24 + baza20 + tva20)
|
||
pnTottvatax = tva21 + tva11 + tva19 + tva09 + tva05 + tva24 + tva20
|
||
pnTotftvatax = m.pnTotCtva - m.pnTottvatax
|
||
pnro21nb = baza21
|
||
pnro21nt = tva21
|
||
pnro11nb = baza11
|
||
pnro11nt = tva11
|
||
pnro19nb = baza19
|
||
pnro19nt = tva19
|
||
pnro9nb = baza09
|
||
pnro9nt = tva09
|
||
pnro5nb = baza05
|
||
pnro5nt = tva05
|
||
pnro24nb = baza24
|
||
pnro24nt = tva24
|
||
pnro20nb = baza20
|
||
pnro20nt = tva20
|
||
|
||
* Adaug linie valoare cu tva / sold si linie achitat, daca exista.
|
||
* Din linia valoare cu tva/sold scad baza si tva neexigibil pe care le adaug distinct, astfel incat sa nu se duplice
|
||
For lnLinie = 1 To 2
|
||
Do Case
|
||
Case m.lnLinie = 1 And (m.lnFacturat <> 0 Or m.lnValoare <> 0)
|
||
* Facturat
|
||
If m.lnFacturat <> 0
|
||
pnSuma = m.lnFacturat
|
||
Else
|
||
pnSuma = m.lnValoare
|
||
ENDIF
|
||
|
||
If m.lnFacturatVal <> 0
|
||
pnSumaVal = m.lnFacturatVal
|
||
Else
|
||
pnSumaVal = m.lnTotVal
|
||
ENDIF
|
||
|
||
* Pun doar diferenta dintre soldul facturii si soldul neexigibil
|
||
pnSuma = pnSuma - (pnro21nb + pnro21nt + pnro11nb + pnro11nt + pnro19nb + pnro19nt + pnro9nb + pnro9nt + pnro5nb + pnro5nt + pnro24nb + pnro24nt + pnro20nb + pnro20nt)
|
||
|
||
* pcExplicatia = IIF(m.lcTip = 'BALANTA', m.lcExplicatia, IIF(m.lnFacturat <> 0, 'FACTURAT', 'SOLD'))
|
||
pcExplicatia = LEFT(m.lcExplicatia, 100)
|
||
|
||
If m.lcTipDC = 'C'
|
||
* x = 401
|
||
pcScd = ''
|
||
pcAscd = ''
|
||
pcScc = m.pcCont
|
||
pcAscc = m.pcAcont
|
||
pnIdPartD = 0
|
||
pnIdPartC = m.pnIdPart
|
||
pcPartD = ''
|
||
pcPartC = m.pcDenumire
|
||
pnIdFactD = 0
|
||
pnIdFactC = 0
|
||
pnPerecheD = 0
|
||
pnPerecheC = 0
|
||
Else
|
||
* 4111 = x
|
||
pcScd = m.pcCont
|
||
pcAscd = m.pcAcont
|
||
pcScc = ''
|
||
pcAscc = ''
|
||
pnIdPartD = m.pnIdPart
|
||
pnIdPartC = 0
|
||
pcPartD = m.pcDenumire
|
||
pcPartC = ''
|
||
pnIdFactD = 0
|
||
pnIdFactC = 0
|
||
pnPerecheD = 0
|
||
pnPerecheC = 0
|
||
ENDIF
|
||
|
||
* la inregistrarile BALANTA, daca am sume debitoare si sume creditoare, pe creditul lui 4111 pun id_factc = id_fact si id_partc = id_part
|
||
IF m.lcTip = 'BALANTA'
|
||
SELECT cContIregTemp
|
||
LOCATE FOR ALLTRIM(cont) = m.pcCont
|
||
IF FOUND()
|
||
DO CASE
|
||
CASE ALLTRIM(tipdc) = 'D' AND NVL(cu_inregistrari,0) = 1 AND m.lcTipDC = 'C' && linia x = 4111
|
||
pnIdFactC = m.pnIdFact
|
||
pnPerecheC = m.pnNrAct
|
||
CASE ALLTRIM(tipdc) = 'C' AND NVL(cu_inregistrari,0) = 1 AND m.lcTipDC = 'D' && linia 401 = x
|
||
pnIdFactD = m.pnIdFact
|
||
pnPerecheD = m.pnNrAct
|
||
ENDCASE
|
||
ENDIF
|
||
ENDIF
|
||
|
||
Case m.lnLinie = 2 And m.lnAchitat <> 0
|
||
* Achitat
|
||
m.pnSuma = m.lnAchitat
|
||
m.pnSumaVal = m.lnAchitatVal
|
||
pcExplicatia = 'ACHITAT'
|
||
If m.lcTipDC = 'C'
|
||
* 401 = x
|
||
pcScd = m.pcCont
|
||
pcAscd = m.pcAcont
|
||
pcScc = ''
|
||
pcAscc = ''
|
||
pnIdPartD = m.pnIdPart
|
||
pnIdPartC = 0
|
||
pcPartD = m.pcDenumire
|
||
pcPartC = ''
|
||
pnIdFactD = m.pnIdFact
|
||
pnIdFactC = 0
|
||
pnPerecheD = m.pnNrAct
|
||
pnPerecheC = 0
|
||
Else
|
||
* x = 4111
|
||
pcScd = ''
|
||
pcAscd = ''
|
||
pcScc = m.pcCont
|
||
pcAscc = m.pcAcont
|
||
pnIdPartD = 0
|
||
pnIdPartC = m.pnIdPart
|
||
pcPartD = ''
|
||
pcPartC = m.pcDenumire
|
||
pnIdFactD = 0
|
||
pnIdFactC = m.pnIdFact
|
||
pnPerecheD = 0
|
||
pnPerecheC = m.pnNrAct
|
||
ENDIF
|
||
Otherwise
|
||
Loop
|
||
Endcase
|
||
|
||
pnProcTva = 0
|
||
pnIdJtva = Null
|
||
|
||
IF m.pnSuma <> 0
|
||
Insert Into actactan(id_set, an, luna, Dataact, datascad, dataireg, serie_act, nract, scd, ascd, scc, ascc, ;
|
||
id_partd, partd, id_partc, partc, id_sucursala, sucursala, id_fdoc, fdoc, id_fact, id_factd, id_factc, pereched, perechec, ;
|
||
cod, suma, explicatia, proc_tva, id_jtva_coloana, id_util, dataora) ;
|
||
VALUES (m.lnIdSet, m.pnAn, m.pnLuna, m.pdDataAct, m.pdDataScad, m.pdDataIreg, m.pcSerieAct, m.pnNrAct, m.pcScd, m.pcAscd, m.pcScc, m.pcAscc, ;
|
||
m.pnIdPartD, m.pcPartD, m.pnIdPartC, m.pcPartC, m.pnIdSucursala, m.pcSucursala, m.pnIdFdoc, m.pcFdoc, ;
|
||
m.pnIdFact, m.pnIdFactD, m.pnIdFactC, m.pnPerecheD, m.pnPerecheC, ;
|
||
m.pnCod, m.pnSuma, m.pcExplicatia, m.pnProcTva, m.pnIdJtva, m.pnIdUtil, m.ptDataOra)
|
||
ENDIF
|
||
Endfor && lnLinie
|
||
|
||
* NOTE TVA NEEXIGIBIL
|
||
IF m.lnTVA <> 0
|
||
For lnLinie = 1 To 2
|
||
* lnLinie = 1 baza
|
||
* lnLinie = 2 tva
|
||
If m.lcTipDC = 'C'
|
||
* 4428 = 401
|
||
pcScd = IIF(m.lnLinie = 1, '', '4428') && linia cu baza ar trebuie sa fie 3xx = 401, nu pun nimic pe debit x = 401
|
||
pcAscd = IIF(m.lnLinie = 1, '', m.lcAcont4428)
|
||
pcScc = m.pcCont
|
||
pcAscc = m.pcAcont
|
||
pnIdPartD = 0
|
||
pnIdPartC = m.pnIdPart
|
||
pcPartD = ''
|
||
pcPartC = m.pcDenumire
|
||
pnIdFactD = 0
|
||
pnIdFactC = 0
|
||
Else
|
||
* 4111 = 4428
|
||
pcScd = m.pcCont
|
||
pcAscd = m.pcAcont
|
||
pcScc = IIF(m.lnLinie = 1, '', '4428')
|
||
pcAscc = IIF(m.lnLinie = 1, '', m.lcAcont4428)
|
||
pnIdPartD = m.pnIdPart
|
||
pnIdPartC = 0
|
||
pcPartD = m.pcDenumire
|
||
pcPartC = ''
|
||
pnIdFactD = 0
|
||
pnIdFactC = 0
|
||
ENDIF
|
||
SET STEP ON
|
||
For lnCota = 1 To 7
|
||
* 21, 11, 5, 9, 19, 20, 24
|
||
Do Case
|
||
Case lnCota = 1
|
||
pnProcTva = 1.21
|
||
pnSuma = Iif(m.lnLinie = 1, m.pnro21nb, m.pnro21nt)
|
||
pnIdJtva = Iif(m.lcTipDC = 'C', Iif(m.lnLinie = 1, 210, 211), Iif(m.lnLinie = 1, 37, 38)) && 210 = ACH. INT. NEEX. 21%, 37 = LIVRARI INT. NEEX. 21%
|
||
Case lnCota = 2
|
||
pnProcTva = 1.11
|
||
pnSuma = Iif(m.lnLinie = 1, m.pnro11nb, m.pnro11nt)
|
||
pnIdJtva = Iif(m.lcTipDC = 'C', Iif(m.lnLinie = 1, 214, 215), Iif(m.lnLinie = 1, 41, 42))
|
||
Case lnCota = 3
|
||
pnProcTva = 1.19
|
||
pnSuma = Iif(m.lnLinie = 1, m.pnro19nb, m.pnro19nt)
|
||
pnIdJtva = Iif(m.lcTipDC = 'C', Iif(m.lnLinie = 1, 188, 189), Iif(m.lnLinie = 1, 31, 32))
|
||
Case lnCota = 4
|
||
pnProcTva = 1.09
|
||
pnSuma = Iif(m.lnLinie = 1, m.pnro9nb, m.pnro9nt)
|
||
pnIdJtva = Iif(m.lcTipDC = 'C', Iif(m.lnLinie = 1, 172, 173), Iif(m.lnLinie = 1, 23, 24))
|
||
Case lnCota = 5
|
||
pnProcTva = 1.05
|
||
pnSuma = Iif(m.lnLinie = 1, m.pnro5nb, m.pnro5nt)
|
||
pnIdJtva = Iif(m.lcTipDC = 'C', Iif(m.lnLinie = 1, 174, 175), Iif(m.lnLinie = 1, 25, 26))
|
||
Case lnCota = 6
|
||
pnProcTva = 1.24
|
||
pnSuma = Iif(m.lnLinie = 1, m.pnro24nb, m.pnro24nt)
|
||
pnIdJtva = Iif(m.lcTipDC = 'C', Iif(m.lnLinie = 1, 170, 171), Iif(m.lnLinie = 1, 21, 22))
|
||
Case lnCota = 7
|
||
pnProcTva = 1.20
|
||
pnSuma = Iif(m.lnLinie = 1, m.pnro20nb, m.pnro20nt)
|
||
pnIdJtva = Iif(m.lcTipDC = 'C', Iif(m.lnLinie = 1, 178, 179), Iif(m.lnLinie = 1, 29, 30))
|
||
|
||
Endcase
|
||
|
||
pcExplicatia = 'NOTE PENTRU EXIGIBILIZARE TVA INCASARE'
|
||
|
||
IF m.pnSuma <> 0
|
||
Insert Into actactan(id_set, an, luna, Dataact, datascad, dataireg, serie_act, nract, scd, ascd, scc, ascc, ;
|
||
id_partd, partd, id_partc, partc, id_sucursala, sucursala, id_fdoc, fdoc, id_fact, id_factd, id_factc, cod, suma, ;
|
||
explicatia, proc_tva, id_jtva_coloana, id_util, dataora) ;
|
||
VALUES (m.lnIdSet, m.pnAn, m.pnLuna, m.pdDataAct, m.pdDataScad, m.pdDataIreg, m.pcSerieAct, m.pnNrAct, m.pcScd, m.pcAscd, m.pcScc, m.pcAscc, ;
|
||
m.pnIdPartD, m.pcPartD, m.pnIdPartC, m.pcPartC, m.pnIdSucursala, m.pcSucursala, m.pnIdFdoc, m.pcFdoc, m.pnIdFact, m.pnIdFactD, m.pnIdFactC, m.pnCod, m.pnSuma, ;
|
||
m.pcExplicatia, m.pnProcTva, m.pnIdJtva, m.pnIdUtil, m.ptDataOra)
|
||
ENDIF
|
||
Endfor && lnCota
|
||
Endfor && lnLinie
|
||
ENDIF && lnTVA
|
||
Endscan && cFacturiTemp
|
||
|
||
USE IN (SELECT('cFacturiTemp'))
|
||
USE IN (SELECT('cContIregTemp'))
|
||
|
||
Return m.llSucces
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE disconnectroa
|
||
ENDPROC
|
||
|
||
PROCEDURE exportdate
|
||
* 24.06.2020
|
||
* + acont4428 in init_facturi.xlsx
|
||
|
||
|
||
Local lcFile, lcFileBinary, llSucces
|
||
LOCAL loEx as Exception
|
||
PRIVATE pcCodFiscal, pnIdPart
|
||
|
||
llSucces = .F.
|
||
AMESSAGEBOX('Alegeti fisierul xlsx initializat cu facturi si/sau sume din balanta.' + CHR(13) + ;
|
||
'Structura fisierului (ordinea coloanelor) trebuie sa fie la fel ca cea a fisierului sablon.',0+64,_screen.Caption)
|
||
|
||
lcFile = Getfile('xlsx','Fisier xlsx cu facturi','Alege')
|
||
llSucces = !Empty(m.lcFile)
|
||
IF !m.llSucces
|
||
RETURN m.llSucces
|
||
Endif
|
||
|
||
* sunt 2 tipuri de inregistrari: FACTURA, BALANTA
|
||
* inregistrarile tip BALANTA au completat SOLDDEB si SOLDCRED
|
||
* inregistrarile tip FACTURA au completat SOLD, SOLTTVANEEX, [FACTURAT, ACHITAT]
|
||
CREATE CURSOR cFacturiTemp(an N(4), luna N(4), cont C(20), acont C(20), tip C(20), serie_act c(10), nract N(20), dataact C(20), datascad c(20), ;
|
||
denumire C(100), cod_fiscal C(20), reg_com C(20), adresa C(200), ;
|
||
explicatie C(100), ;
|
||
totdeb N(18,2), totcred N(18,2), ;
|
||
facturat N(18,2), facturatval N(18,2), achitat N(18,2), achitatval N(18,2), valuta C(20), ;
|
||
valoare N(18,2), totval N(18,2), tva N(18,2), acont4428 C(20), ;
|
||
baza21 N(18,2), tva21 N(18,2), ;
|
||
baza11 N(18,2), tva11 N(18,2), ;
|
||
baza19 N(18,2), tva19 N(18,2), ;
|
||
baza09 N(18,2), tva09 N(18,2), ;
|
||
baza05 N(18,2), tva05 N(18,2), ;
|
||
baza24 N(18,2), tva24 N(18,2), ;
|
||
baza20 N(18,2), tva20 N(18,2))
|
||
|
||
appendfromxlsx(m.lcFile,"cFacturiTemp","",2,"Sheet1", .T.)
|
||
llSucces = .T.
|
||
* Import first sheet
|
||
* importfromxlsx(lcFileName,lnStartRows,lcSheet,llCursor,llEmptyCells,lnHeader,lcTableName)
|
||
*!* importfromxlsx(m.lcFile,2,'Sheet1',.T.,.T.,1,'cfacturitemp')
|
||
*!* llSucces = Used('cfacturitemp')
|
||
IF m.llSucces
|
||
*!* Select Cast(0 As I) As id_fact, Cast(0 As I) As id_part, Int(Val(TRANSFORM(an))) As an, Int(Val(TRANSFORM(luna))) As luna, ;
|
||
*!* Padr(Alltrim(TRANSFORM(Cont)), 4, ' ') As Cont, Padr(Alltrim(TRANSFORM(acont)), 4, ' ') As acont, tip, ;
|
||
*!* TRANSFORM(serie) As serie_act, Int(Val(TRANSFORM(numar))) As nract, IIF(VAL(TRANSFORM(data)) > 3000, DATE(1900,1,1)+VAL(TRANSFORM(data))-2, Ctod(TRANSFORM(Data))) As Dataact, ;
|
||
*!* IIF(VAL(TRANSFORM(datascad)) > 3000, DATE(1900,1,1)+VAL(datascad)-2, Ctod(datascad)) As datascad, ;
|
||
*!* Upper(TRANSFORM(nume)) As denumire, Padr(Alltrim(Upper(TRANSFORM(cod_fiscal))),20, ' ') As cod_fiscal, Padr(Alltrim(Upper(TRANSFORM(reg_com))),30, ' ') As reg_com,;
|
||
*!* Upper(TRANSFORM(adresa)) As adresa, ;
|
||
*!* ROUND(Val(TRANSFORM(facturat)),2) As facturat, Round(Val(TRANSFORM(achitat)),2) As achitat, ;
|
||
*!* ROUND(Val(TRANSFORM(facturatval)),2) As facturatval, Round(Val(TRANSFORM(achitatval)),2) As achitatval, ;
|
||
*!* UPPER(TRANSFORM(valuta)) as valuta, CAST(0 as I) as id_valuta, ;
|
||
*!* ROUND(Val(TRANSFORM(sold)),2) As valoare, Round(Val(TRANSFORM(soldtvaneex)),2) As tva, ;
|
||
*!* ROUND(Val(TRANSFORM(soldval)),2) As totval, ;
|
||
*!* ROUND(Val(Transform(baza24)),2) As baza24, Round(Val(Transform(tva24)),2) As tva24, ;
|
||
*!* ROUND(Val(Transform(baza20)),2) As baza20, Round(Val(Transform(tva20)),2) As tva20, ;
|
||
*!* ROUND(Val(Transform(baza19)),2) As baza19, Round(Val(Transform(tva19)),2) As tva19, ;
|
||
*!* ROUND(Val(Transform(baza09)),2) As baza09, Round(Val(Transform(tva09)),2) As tva09, ;
|
||
*!* ROUND(Val(Transform(baza05)),2) As baza05, Round(Val(Transform(tva05)),2) As tva05 ;
|
||
*!* FROM cfacturitemp ;
|
||
*!* WHERE !Empty(Val(Transform(an))) ;
|
||
*!* INTO Cursor cFacturi Readwrite
|
||
|
||
This.Trace(TRANSFORM(RECCOUNT('cFacturiTemp')) + ' inregistrari in ' + m.lcFile)
|
||
* Verific daca contul are si analitic 401.01
|
||
* Daca da separ contul si analiticul
|
||
SELECT cFacturiTemp
|
||
SCAN
|
||
lcContOriginal = ALLTRIM(NVL(cont,''))
|
||
lcAcontOriginal = ALLTRIM(NVL(acont,''))
|
||
lnLen = AT('.', m.lcContOriginal)
|
||
IF m.lnLen > 0
|
||
lcAcont = ALLTRIM(SUBSTR(m.lcContOriginal, m.lnLen+1))
|
||
lcCont = ALLTRIM(LEFT(m.lcContOriginal, m.lnLen-1))
|
||
REPLACE cont WITH m.lcCont
|
||
IF EMPTY(lcAcontOriginal)
|
||
REPLACE acont WITH m.lcAcont
|
||
ENDIF
|
||
ENDIF
|
||
|
||
ENDSCAN
|
||
|
||
lcColoanaExplicatie = IIF(TYPE('cFacturiTemp.explicatie') <> 'U', [UPPER(explicatie) as explicatie], [space(10) as explicatie])
|
||
* coloana data in format excel number
|
||
UPDATE cFacturiTemp SET dataact = TRANSFORM(ExcelSerialNumber2Date(INT(VAL(dataact)))) WHERE !('.'$dataact OR '/'$dataact)
|
||
UPDATE cFacturiTemp SET datascad = TRANSFORM(ExcelSerialNumber2Date(INT(VAL(datascad)))) WHERE !('.'$datascad OR '/'$datascad)
|
||
|
||
Select CAST(0 AS I) AS id_fact, CAST(0 AS I) AS id_part, an, luna, ;
|
||
PADR(ALLTRIM(cont),4, ' ') as cont, PADR(ALLTRIM(acont),4, ' ') as acont, UPPER(tip) as tip, ;
|
||
UPPER(serie_act) as serie_act, nract, CTOD(dataact) as dataact, CTOD(datascad) as datascad, ;
|
||
IIF(ALLTRIM(tip) = 'BALANTA', SPACE(100), Upper(denumire)) As denumire, UPPER(cod_fiscal) As cod_fiscal, UPPER(reg_com) As reg_com,;
|
||
PADR(IIF(ALLTRIM(tip) = 'BALANTA', UPPER(denumire), 'FACTURA'), 100, ' ') As explicatia, ;
|
||
Upper(adresa) As adresa, ;
|
||
&lcColoanaExplicatie, ;
|
||
totdeb, totcred, ;
|
||
facturat As facturat, achitat As achitat, ;
|
||
facturatval As facturatval, achitatval As achitatval, ;
|
||
UPPER(valuta) as valuta, CAST(0 as I) as id_valuta, ;
|
||
valoare, tva, PADR(ALLTRIM(acont4428),4, ' ') as acont4428, ;
|
||
totval, ;
|
||
baza21 As baza21, tva21 As tva21, ;
|
||
baza11 As baza11, tva11 As tva11, ;
|
||
baza19 As baza19, tva19 As tva19, ;
|
||
baza09 As baza09, tva09 As tva09, ;
|
||
baza05 As baza05, tva05 As tva05, ;
|
||
baza24 As baza24, tva24 As tva24, ;
|
||
baza20 As baza20, tva20 As tva20 ;
|
||
FROM cFacturiTemp ;
|
||
WHERE !Empty(Val(Transform(an))) ;
|
||
INTO Cursor cFacturi Readwrite
|
||
|
||
UPDATE cFacturi SET datascad = GOMONTH(dataact,1) WHERE YEAR(datascad) = 1899 OR EMPTY(NVL(datascad, {}))
|
||
|
||
UPDATE cFacturi SET explicatia = explicatie WHERE ALLTRIM(tip) <> 'BALANTA'
|
||
|
||
USE IN (SELECT('cfacturitemp'))
|
||
|
||
|
||
* inregistrarile tip BALANTA au nract = 1 si dataact = sfarsitul lunii
|
||
UPDATE cFacturi SET nract = 1 WHERE tip = 'BALANTA' AND EMPTY(nract)
|
||
UPDATE cFacturi SET dataact = GOMONTH(DATE(m.gnAn, m.gnLuna, 1),1)-1 WHERE tip = 'BALANTA' AND EMPTY(dataact)
|
||
|
||
SELECT cFacturi
|
||
BROWSE TITLE 'Lista facturi importate din ' + JUSTFNAME(m.lcFile)
|
||
llSucces = (AMESSAGEBOX('Doriti sa continuati?',4+32,_screen.Caption) = 6)
|
||
|
||
IF m.llSucces
|
||
Select Cast(Null As I) As id_part, denumire, Space(100) As nume, Space(100) As prenume, cod_fiscal, reg_com, MAX(adresa) as adresa, MAX(cont) as cont ;
|
||
From cFacturi ;
|
||
WHERE ALLTRIM(tip) = 'FACTURA' ;
|
||
group by denumire, cod_fiscal, reg_com ;
|
||
Into Cursor cParteneriTemp Readwrite
|
||
|
||
* ADAUG PARTENERUL "NEREPARTIZAT" PENTRU DIFERENTELE FATA DE BALANTA DE VERIFICARE
|
||
SELECT cParteneriTemp
|
||
LOCATE FOR ALLTRIM(denumire) = 'NEREPARTIZAT'
|
||
IF !FOUND()
|
||
APPEND BLANK
|
||
REPLACE denumire WITH 'NEREPARTIZAT'
|
||
ENDIF
|
||
|
||
|
||
This.Trace('Verificare ' + TRANSFORM(RECCOUNT('cParteneriTemp')) + ' parteneri')
|
||
|
||
* Completez ID_PART
|
||
TRY
|
||
IF RECCOUNT('cParteneriTemp') > 0
|
||
llSucces = CompleteazaParteneriROA('cParteneriTemp')
|
||
ENDIF
|
||
CATCH TO loEx
|
||
This.Trace(loEx.message)
|
||
AMESSAGEBOX(loEx.Message,0+16,_screen.Caption)
|
||
llSucces = .F.
|
||
ENDTRY
|
||
If m.llSucces
|
||
Select cParteneriTemp
|
||
Scan
|
||
lnIdPart = id_part
|
||
lcCodFiscal = Alltrim(cod_fiscal)
|
||
lcDenumire = ALLTRIM(denumire)
|
||
lcRegCom = ALLTRIM(reg_com)
|
||
IF lcDenumire = 'NEREPARTIZAT'
|
||
Thisform.nIdPartNerepartizat = m.lnIdPart
|
||
ENDIF
|
||
Update cFacturi Set id_part = m.lnIdPart Where cod_fiscal = m.lcCodFiscal AND denumire = m.lcDenumire AND reg_com = m.lcRegCom
|
||
ENDSCAN
|
||
USE IN (SELECT('cParteneriTemp'))
|
||
ENDIF
|
||
ENDIF && llSucces
|
||
|
||
IF m.llSucces
|
||
SELECT distinct CAST(null as I) as id_valuta, valuta ;
|
||
FROM cFacturi ;
|
||
WHERE !EMPTY(NVL(valuta, '')) ;
|
||
INTO CURSOR cValuteTemp READWRITE
|
||
|
||
This.Trace('Verificare ' + TRANSFORM(RECCOUNT('cValuteTemp')) + ' valute')
|
||
|
||
* Completez ID_VALUTA
|
||
TRY
|
||
IF RECCOUNT('cValuteTemp') > 0
|
||
llSucces = CompleteazaValutaROA('cValuteTemp', thisform)
|
||
ENDIF
|
||
CATCH TO loEx
|
||
This.Trace(loEx.message)
|
||
AMESSAGEBOX(loEx.Message,0+16,_screen.Caption)
|
||
llSucces = .F.
|
||
ENDTRY
|
||
ENDIF
|
||
|
||
If m.llSucces
|
||
Select cValuteTemp
|
||
Scan
|
||
lnIdValuta = id_valuta
|
||
lcValuta = Alltrim(valuta)
|
||
|
||
Update cFacturi Set id_valuta = m.lnIdValuta Where valuta = m.lcValuta
|
||
ENDSCAN
|
||
USE IN (SELECT('cValuteTemp'))
|
||
ENDIF && llSucces
|
||
|
||
IF m.llSucces
|
||
* Plan conturi analitic
|
||
This.Trace('Completare plan conturi analitic')
|
||
|
||
Select Distinct an, Cont, acont, CAST(IIF(!EMPTY(NVL(denumire,'')), LEFT(denumire,100), LEFT(explicatia,100)) as C(100)) as denumire, IIF(totdeb-totcred > 0, 'A', 'P') as tip ;
|
||
From cFacturi ;
|
||
Where tip = 'BALANTA' AND !Empty(acont) ;
|
||
Into Cursor cPlan
|
||
|
||
Text To lcSql Noshow
|
||
merge into plcont A
|
||
USING DUAL B
|
||
ON (A.AN = ?an AND CONT = ?ALLTRIM(cont) AND A.ACONT = ?ALLTRIM(acont))
|
||
WHEN NOT MATCHED THEN
|
||
INSERT
|
||
(acont,
|
||
explicatie,
|
||
cont,
|
||
tip_sold,
|
||
an,
|
||
tip_cont,
|
||
id_mod,
|
||
inactiv,
|
||
nefolosit,
|
||
explicaties)
|
||
VALUES (?ALLTRIM(acont),
|
||
?LEFT(ALLTRIM(denumire),100),
|
||
?ALLTRIM(cont),
|
||
?ALLTRIM(tip),
|
||
?an,
|
||
1,
|
||
0,
|
||
0,
|
||
0,
|
||
?LEFT(ALLTRIM(denumire),100))
|
||
|
||
Endtext
|
||
|
||
Select cPlan
|
||
Scan
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !m.llSucces
|
||
This.Trace(goExecutor.cEroare)
|
||
Exit
|
||
Endif
|
||
Endscan
|
||
|
||
ENDIF && llSucces
|
||
|
||
ENDIF && llSucces
|
||
|
||
|
||
Return m.llSucces
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE Init
|
||
DODEFAULT()
|
||
This.RefreshControls()
|
||
ENDPROC
|
||
|
||
PROCEDURE initbalanta
|
||
* Initializeaza direct balanta verificare din balanta.xlsx
|
||
|
||
* Import balanta.xlsx (PRECDEB1, PRECCRED1, RULPRECDEB, RULPRECCRED, PRECDEB, PRECCRED, RULDEB, RULCRED, TODEB, TOTCRED, SOLDDEB, SOLDCRED)
|
||
|
||
|
||
Local lcAcont2, lcBalantaP2IregP, lcCont, lcCont2, lcContAnalitic, lcContSintetic, lcContToate
|
||
Local lcFile, lcIregP2BalantaP, lcListaConturiBalP, lcListaConturiIregP, lcMesaj, lcSql, lcSqlBalP
|
||
Local lcSqlDel, lcSqlId, lcSqlIregP, ldDataI, ldDataP, llParteneriBalanta, llSucces, lnAnI, lnAnP
|
||
Local lnLunaI, lnLunaP, lnNrLuniI, lnNrLuniP, lnPos, loPart
|
||
PRIVATE pdDataAct, pdDataIreg, pdDataScad, pnIdFact, pnIdFdoc, pnNrAct
|
||
|
||
lnLunaI = m.gnLuna
|
||
lnAnI = m.gnAn
|
||
lnNrLuniI = m.lnAnI * 12 + m.lnLunaI
|
||
|
||
ldDataI = Date(m.lnAnI, m.lnLunaI, 1)
|
||
ldDataP = Gomonth(m.ldDataI, -1)
|
||
lnAnP = Year(m.ldDataP)
|
||
lnLunaP = Month(m.ldDataP)
|
||
lnNrLuniP = m.lnAnP * 12 + m.lnLunaP
|
||
|
||
llParteneriBalanta = (This.chkParteneriBalantaVerificare = 1) && creez balante de parteneri din balanta analitica si sterg apoi analiticele din balanta analitica
|
||
SET STEP ON
|
||
TEXT TO lcMesaj NOSHOW
|
||
Alegeti fisierul balanta.xlsx cu structura:
|
||
"CONT, DENUMIRE, PRECDEB1, PRECCRED1, RULPRECDEB, RULPRECCRED, RULDEB, RULCRED, TOTDEB, TOTCRED, SOLDDEB, SOLDCRED"
|
||
sau
|
||
"CONT, DENUMIRE, PRECDEB1, PRECCRED1, PRECDEB, PRECCRED, RULDEB, RULCRED, TOTDEB, TOTCRED, SOLDDEB, SOLDCRED"
|
||
sau
|
||
"CONT, DENUMIRE, PRECDEB, PRECCRED, RULDEB, RULCRED, TOTDEB, TOTCRED, SOLDDEB, SOLDCRED"
|
||
|
||
* PRECDEBI/PRECCRED1 = solduri la 1 ianuarie
|
||
* RULPRECDEB/RULPRECCRED = rulaj precedent anual, fara sold la 1 ianuarie
|
||
* RULDEB/RULCRED = rulaje lunare
|
||
* TOTDEB/TOTCRED = sume totale (sold la 1 ianuarie + rulal precedent anual + rulaj lunar)
|
||
* PRECDEB/PRECCRED = rulaj precedent, inclusiv sold la 1 ianuarie
|
||
ENDTEXT
|
||
AMessagebox(m.lcMesaj, 0 + 64, _Screen.Caption)
|
||
llSucces = .F.
|
||
|
||
lcFile = Getfile('xlsx', 'Fisier Balanta.xlsx', 'Alege')
|
||
llSucces = !EMPTY(m.lcFile)
|
||
IF !m.llSucces
|
||
RETURN m.llSucces
|
||
Endif
|
||
* Import first sheet
|
||
importfromxlsx(m.lcFile, 2, 'Sheet1', .T., .T., 1, 'cBalantaTemp')
|
||
|
||
llSucces = USED('cBalantaTemp')
|
||
|
||
Do Case
|
||
Case Type('cBalantaTemp.precdeb1') <> 'U' And Type('cBalantaTemp.rulprecdeb') <> 'U'
|
||
* sold initial 1 ianuarie + rulaj precedent = precdeb/preccred
|
||
Select Cast(Alltrim(Cont) As C(20)) As Cont, Cast(Upper(Alltrim(denumire)) As C(100)) As denumire, ;
|
||
Cast(Round(Val(Alltrim(Transform(precdeb1))), 2) As N(18, 2)) As precdeb1, ;
|
||
Cast(Round(Val(Alltrim(Transform(preccred1))), 2) As N(18, 2)) As preccred1, ;
|
||
Cast(Round(Val(Alltrim(Transform(precdeb1))), 2) + Round(Val(Alltrim(Transform(rulprecdeb))), 2) As N(18, 2)) As precdeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(preccred1))), 2) + Round(Val(Alltrim(Transform(rulpreccred))), 2) As N(18, 2)) As preccred, ;
|
||
Cast(Round(Val(Alltrim(Transform(ruldeb))), 2) As N(18, 2)) As ruldeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(rulcred))), 2) As N(18, 2)) As rulcred, ;
|
||
Cast(Round(Val(Alltrim(Transform(totdeb))), 2) As N(18, 2)) As totdeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(totcred))), 2) As N(18, 2)) As totcred, ;
|
||
Cast(Round(Val(Alltrim(Transform(solddeb))), 2) As N(18, 2)) As solddeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(soldcred))), 2) As N(18, 2)) As soldcred ;
|
||
From cBalantaTemp ;
|
||
Into Cursor cBalTemp
|
||
Case Type('cBalantaTemp.precdeb1') <> 'U' And Type('cBalantaTemp.precdeb') <> 'U'
|
||
Select Cast(Alltrim(Cont) As C(20)) As Cont, Cast(Upper(Alltrim(denumire)) As C(100)) As denumire, ;
|
||
Cast(Round(Val(Alltrim(Transform(precdeb1))), 2) As N(18, 2)) As precdeb1, ;
|
||
Cast(Round(Val(Alltrim(Transform(preccred1))), 2) As N(18, 2)) As preccred1, ;
|
||
Cast(Round(Val(Alltrim(Transform(precdeb))), 2) As N(18, 2)) As precdeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(preccred))), 2) As N(18, 2)) As preccred, ;
|
||
Cast(Round(Val(Alltrim(Transform(ruldeb))), 2) As N(18, 2)) As ruldeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(rulcred))), 2) As N(18, 2)) As rulcred, ;
|
||
Cast(Round(Val(Alltrim(Transform(totdeb))), 2) As N(18, 2)) As totdeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(totcred))), 2) As N(18, 2)) As totcred, ;
|
||
Cast(Round(Val(Alltrim(Transform(solddeb))), 2) As N(18, 2)) As solddeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(soldcred))), 2) As N(18, 2)) As soldcred ;
|
||
From cBalantaTemp ;
|
||
Into Cursor cBalTemp
|
||
Case Type('cBalantaTemp.precdeb1') = 'U' And Type('cBalantaTemp.precdeb') <> 'U'
|
||
Select Cast(Alltrim(Cont) As C(20)) As Cont, Cast(Upper(Alltrim(denumire)) As C(100)) As denumire, ;
|
||
Cast(Round(Val(Alltrim(Transform(precdeb))), 2) As N(18, 2)) As precdeb1, ;
|
||
Cast(Round(Val(Alltrim(Transform(preccred))), 2) As N(18, 2)) As preccred1, ;
|
||
Cast(Round(Val(Alltrim(Transform(precdeb))), 2) As N(18, 2)) As precdeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(preccred))), 2) As N(18, 2)) As preccred, ;
|
||
Cast(Round(Val(Alltrim(Transform(ruldeb))), 2) As N(18, 2)) As ruldeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(rulcred))), 2) As N(18, 2)) As rulcred, ;
|
||
Cast(Round(Val(Alltrim(Transform(totdeb))), 2) As N(18, 2)) As totdeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(totcred))), 2) As N(18, 2)) As totcred, ;
|
||
Cast(Round(Val(Alltrim(Transform(solddeb))), 2) As N(18, 2)) As solddeb, ;
|
||
Cast(Round(Val(Alltrim(Transform(soldcred))), 2) As N(18, 2)) As soldcred ;
|
||
From cBalantaTemp ;
|
||
Into Cursor cBalTemp
|
||
Otherwise
|
||
Messagebox('Nu recunosc structura de balanta!', 0 + 16, _Screen.Caption)
|
||
llSucces = .F.
|
||
Endcase
|
||
|
||
If !m.llSucces
|
||
Return m.llSucces
|
||
Endif
|
||
|
||
* Conturi cu balanta parteneri
|
||
lcListaConturiBalP = ['401','404','408','409','4091','4092','4093','4111','418','419','461','462','4551','5311','5121','5124']
|
||
|
||
* Conturi cu inregistrari parteneri
|
||
lcListaConturiIregP = ['401','404','408','409','4091','4092','4093','4111','418','419','461','462','4551']
|
||
|
||
|
||
Select Cast(Alltrim(Cont) + '.' + Alltrim(acont) As C(20)) As Cont, cont2, acont2 From conturi_roa Into Cursor cConturiROA
|
||
|
||
Select Cast(Null As I) As id_part, b.Cont, b.denumire, Space(4) As cont2, Space(4) As acont2, b.precdeb1, b.preccred1, b.precdeb, b.preccred, b.ruldeb, b.rulcred, b.totdeb, b.totcred, b.solddeb, b.soldcred, ;
|
||
b.Cont As cod, b.Cont As cod_fiscal, '' As reg_com, '' As judet, '' As adresa, 'ROMANIA' As tara ;
|
||
From cBalTemp b ;
|
||
Order By b.Cont ;
|
||
Into Cursor cBalTemp2 Readwrite
|
||
|
||
* Corectare conturi analitice
|
||
Select cBalTemp2
|
||
Scan
|
||
lcCont = Alltrim(Cont)
|
||
If At('.', m.lcCont) = 0
|
||
lcCont = m.lcCont + '.'
|
||
Endif
|
||
lnPos = At('.', m.lcCont)
|
||
lcContSintetic = Left(m.lcCont, m.lnPos - 1)
|
||
lcContAnalitic = Substr(m.lcCont, m.lnPos + 1)
|
||
lcContToate = m.lcContSintetic + '.TOATE'
|
||
|
||
lcCont2 = Alltrim(m.lcContSintetic)
|
||
lcAcont2 = Alltrim(m.lcContAnalitic)
|
||
|
||
Select cConturiROA
|
||
Locate For Alltrim(Cont) = m.lcCont
|
||
If !Found()
|
||
Locate For Alltrim(Cont) = m.lcContToate
|
||
If Found()
|
||
lcCont2 = Alltrim(cont2)
|
||
lcAcont2 = Alltrim(acont2)
|
||
Endif
|
||
Else
|
||
lcCont2 = Alltrim(cont2)
|
||
lcAcont2 = Alltrim(acont2)
|
||
Endif
|
||
Select cBalTemp2
|
||
Replace cont2 With m.lcCont2, acont2 With m.lcAcont2
|
||
|
||
Endscan
|
||
|
||
|
||
** Balanta sintetica
|
||
Select lnAnI As an, lnLunaI As luna, b.cont2 As Cont, ;
|
||
Sum(b.precdeb1) As precdeb1, Sum(b.preccred1) As preccred1, Sum(b.precdeb) As precdeb, Sum(b.preccred) As preccred, ;
|
||
Sum(b.ruldeb) As ruldeb, Sum(b.rulcred) As rulcred, Sum(b.totdeb) As totdeb, Sum(b.totcred) As totcred, ;
|
||
Sum(b.solddeb) As solddeb, Sum(b.soldcred) As soldcred ;
|
||
From cBalTemp2 b ;
|
||
Where Empty(b.acont2) ;
|
||
Group By 1, 2, 3 ;
|
||
Order By 1, 2, 3 ;
|
||
Into Cursor cBal
|
||
|
||
** Balanta analitica
|
||
** nu initializez analitice pentru conturile cu analitic = partener pentru ca initializez balante analitice de parteneri
|
||
Select lnAnI As an, lnLunaI As luna, b.cont2 As Cont, b.acont2 As acont, ;
|
||
Sum(b.precdeb1) As precdeb1, Sum(b.preccred1) As preccred1, Sum(b.precdeb) As precdeb, Sum(b.preccred) As preccred, ;
|
||
Sum(b.ruldeb) As ruldeb, Sum(b.rulcred) As rulcred, Sum(b.totdeb) As totdeb, Sum(b.totcred) As totcred, ;
|
||
Sum(b.solddeb) As solddeb, Sum(b.soldcred) As soldcred ;
|
||
From cBalTemp2 b ;
|
||
Where !Empty(b.acont2) And (!m.llParteneriBalanta Or (m.llParteneriBalanta And !(['] + Alltrim(cont2) + ['] $ m.lcListaConturiBalP))) ;
|
||
Group By 1, 2, 3, 4 ;
|
||
Order By 1, 2, 3, 4 ;
|
||
Into Cursor cBalana
|
||
|
||
* Plan conturi analitic
|
||
** nu initializez analitice pentru conturile cu analitic = partener pentru ca initializez balante analitice de parteneri
|
||
Select Distinct lnAnI As an, b.cont2 As Cont, b.acont2 As acont, b.denumire ;
|
||
From cBalTemp2 b ;
|
||
Where !Empty(b.acont2) And (!m.llParteneriBalanta Or (m.llParteneriBalanta And !(['] + Alltrim(cont2) + ['] $ m.lcListaConturiBalP))) ;
|
||
Into Cursor cPlan
|
||
|
||
|
||
* Partenerii ii iau din balanta analitica. Nu am alta sursa de parteneri
|
||
** Nomenclator Parteneri
|
||
llSucces = goConn.BeginManualTransaction()
|
||
|
||
If m.llSucces
|
||
Wait Window 'BALANTA SINTETICA...' Nowait
|
||
lcSql = [delete from bal where an = ] + Alltrim(Str(m.lnAnI)) + [ and luna = ] + Alltrim(Str(m.lnLunaI))
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
|
||
If m.llSucces
|
||
lcSql = 'insert into bal (an, luna, cont, precdeb1, preccred1, precdeb, preccred, ruldeb, rulcred) ' + ;
|
||
'VALUES (?an, ?luna, ?ALLTRIM(cont), ?precdeb1, ?preccred1, ?precdeb, ?preccred, ?ruldeb, ?rulcred)'
|
||
Select cBal
|
||
Scan
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Endscan
|
||
Endif && llSucces
|
||
|
||
If m.llSucces
|
||
Wait Window 'PLAN CONTURI...' Nowait
|
||
TEXT To lcSql Noshow
|
||
merge into plcont A
|
||
USING DUAL B
|
||
ON (A.AN = ?an AND CONT = ?ALLTRIM(cont) AND A.ACONT = ?ALLTRIM(acont))
|
||
WHEN NOT MATCHED THEN
|
||
INSERT
|
||
(acont,
|
||
explicatie,
|
||
cont,
|
||
tip_sold,
|
||
an,
|
||
tip_cont,
|
||
id_mod,
|
||
inactiv,
|
||
nefolosit,
|
||
explicaties)
|
||
VALUES (?ALLTRIM(acont),
|
||
?LEFT(ALLTRIM(denumire),100),
|
||
?ALLTRIM(cont),
|
||
'P',
|
||
?an,
|
||
1,
|
||
0,
|
||
0,
|
||
0,
|
||
?LEFT(ALLTRIM(denumire),100))
|
||
|
||
ENDTEXT
|
||
|
||
Select cPlan
|
||
Scan
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Endscan
|
||
Endif
|
||
|
||
If m.llSucces
|
||
Wait Window 'BALANTA ANALITICA...' Nowait
|
||
lcSql = [delete from balana where an = ] + Alltrim(Str(m.lnAnI)) + [ and luna = ] + Alltrim(Str(m.lnLunaI))
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
Endif
|
||
|
||
If m.llSucces
|
||
lcSql = 'insert into balana (an, luna, cont, acont, precdeb1, preccred1, precdeb, preccred, ruldeb, rulcred) ' + ;
|
||
'VALUES (?an, ?luna, ?ALLTRIM(cont), ?ALLTRIM(acont), ?precdeb1, ?preccred1, ?precdeb, ?preccred, ?ruldeb, ?rulcred)'
|
||
Select cBalana
|
||
Scan
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Endscan
|
||
Endif
|
||
Endif && llSucces
|
||
|
||
If m.llParteneriBalanta
|
||
Select Distinct Cont, Upper(denumire) As denumire, cod, cod_fiscal, reg_com, judet, adresa, tara ;
|
||
From cBalTemp2 ;
|
||
Where ['] + Alltrim(cont2) + ['] $ m.lcListaConturiBalP And !Empty(acont2) ;
|
||
Into Cursor cParteneriTemp && nomenclator parteneri
|
||
|
||
Select Distinct Cast(Null As I) As id_part, cod, denumire, Padr(Nvl(cod_fiscal, cod), 20, ' ') As cod_fiscal, reg_com, adresa As localitate ;
|
||
From cParteneriTemp ;
|
||
Into Cursor cParteneri Readwrite
|
||
Use In (Select('cParteneriTemp'))
|
||
|
||
|
||
If m.llSucces
|
||
Wait Window 'PARTENERI...' Nowait
|
||
llSucces = CompleteazaParteneriROA('cParteneri')
|
||
If m.llSucces
|
||
Select cParteneri
|
||
Scan
|
||
Scatter Name loPart
|
||
Update cBalTemp2 Set id_part = loPart.id_part Where cod_fiscal = loPart.cod_fiscal Or denumire = loPart.denumire
|
||
Endscan
|
||
Endif
|
||
Endif
|
||
|
||
* Sterg analiticele partenerilor din balanta analitica
|
||
** Balante Parteneri pentru conturile analitice din balanta de verificare
|
||
Select lnAnI As an, lnLunaI As luna, b.cont2 As Cont, Iif(m.llStergAnalitic, Space(4), b.acont2) As acont, b.denumire, b.cod_fiscal, b.id_part, 0 As id_valuta, ;
|
||
Sum(b.precdeb1) As precdeb1, Sum(b.preccred1) As preccred1, Sum(b.precdeb) As precdeb, Sum(b.preccred) As preccred, ;
|
||
Sum(b.ruldeb) As ruldeb, Sum(b.rulcred) As rulcred, Sum(b.totdeb) As totdeb, Sum(b.totcred) As totcred, ;
|
||
Sum(b.solddeb) As solddeb, Sum(b.soldcred) As soldcred ;
|
||
From cBalTemp2 b ;
|
||
Where ['] + Alltrim(b.cont2) + ['] $ m.lcListaConturiBalP And !Empty(acont2) ;
|
||
Group By 1, 2, 3, 4, 5, 6, 7, 8 ;
|
||
Order By 1, 2, 3, 4, 5, 6, 7, 8 ;
|
||
Into Cursor cBalP
|
||
|
||
If m.llSucces
|
||
Wait Window 'BALANTA PARTENERI, INREGISTRARI PARTENERI...' Nowait
|
||
|
||
lcSqlBalP = 'insert into balanta_parteneri (an, luna, cont, acont, id_part, id_valuta, precdeb1, preccred1, precdeb, preccred, debit, credit) ' + ;
|
||
'VALUES (?an, ?luna, ?ALLTRIM(cont), ?ALLTRIM(acont), ?id_part, ?id_valuta, ?precdeb1, ?preccred1, ?precdeb, ?preccred, ?ruldeb, ?rulcred)'
|
||
|
||
lcSqlId = [SELECT SEQ_IdFact.NEXTVAL as id_fact FROM DUAL]
|
||
|
||
TEXT To lcSqlIregP Textmerge Noshow
|
||
INSERT INTO IREG_PARTENERI (an, luna, cont, acont, id_fact, id_part, id_fdoc, nract, dataact, dataireg, datascad, debit, credit)
|
||
VALUES (?an, ?luna, ?ALLTRIM(Cont), ?ALLTRIM(acont), ?m.pnIdFact, ?id_part, ?pnIdFdoc, ?pnNract, ?pdDataact, ?pdDataIreg, ?pdDatascad, ?totdeb, ?totcred)
|
||
ENDTEXT
|
||
|
||
Select Distinct Cont From cBalP Into Cursor cConturiBalP
|
||
Select cConturiBalP
|
||
Scan
|
||
lcCont = Alltrim(Cont)
|
||
TEXT To lcSqlDel Noshow Textmerge
|
||
begin
|
||
delete from balanta_parteneri where an = <<m.lnAnI>> and luna = <<m.lnLunaI>> and cont = '<<m.lcCont>>';
|
||
delete from ireg_parteneri where an = <<m.lnAnI>> and luna = <<m.lnLunaI>> and cont = '<<m.lcCont>>';
|
||
end;
|
||
ENDTEXT
|
||
|
||
llSucces = goExecutor.oExecuta(m.lcSqlDel)
|
||
|
||
If m.llSucces
|
||
Select cBalP
|
||
Scan For Cont = m.lcCont
|
||
* Balanta Parteneri
|
||
llSucces = goExecutor.oExecuta(m.lcSqlBalP)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
|
||
* Inregistrari Parteneri
|
||
pnIdFact = 0
|
||
llSucces = goExecutor.oSelecteaza2Value(lcSqlId, @pnIdFact)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Select cBalP
|
||
pnNrAct = 1
|
||
pdDataAct = Gomonth(Date(an, luna, 1), 1) - 1
|
||
pdDataIreg = m.pdDataAct
|
||
pdDataScad = pdDataAct
|
||
pnIdFdoc = 69 && SOLD
|
||
llSucces = goExecutor.oExecuta(m.lcSqlIregP)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Endscan
|
||
Endif
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Endscan && cConturiBalP
|
||
Endif && llSucces
|
||
Endif && llParteneriBalanta
|
||
|
||
llSucces = goConn.EndManualTransaction(m.llSucces)
|
||
AMessagebox('Import incheiat ' + Iif(m.llSucces, 'CU', 'FARA') + ' SUCCESS', 0 + 64, _Screen.Caption)
|
||
|
||
Return m.llSucces
|
||
|
||
|
||
TEXT To lcBalantaP2IregP Noshow
|
||
insert into ireg_parteneri (precdeb,preccred, debit, credit, precvaldeb, precvalcred, valdebit, valcredit,
|
||
id_part, cont, acont, an, luna, id_valuta, id_sucursala,
|
||
id_fdoc, dataact, datascad, dataireg, nract, explicatia, id_fact, id_jtva_coloana)
|
||
select precdeb,preccred, debit, credit, precvaldeb, precvalcred, valdebit, valcredit,
|
||
id_part, cont, acont, an, luna, id_valuta, id_sucursala,
|
||
42 as id_fdoc, to_date('30112017', 'ddmmyyyy') as dataact, to_date('30112017', 'ddmmyyyy') as datascad, to_date('30112017', 'ddmmyyyy') as dataireg,
|
||
1 as nract, 'INITIALIZARE SOLD' AS explicatia, seq_idfact.nextval as id_fact, 0 as id_jtva_coloana
|
||
from
|
||
(select id_part, cont, acont, an, luna, id_valuta, id_sucursala, sum(precdeb) as precdeb, sum(preccred)as preccred, sum(debit) as debit, sum(credit) as credit , sum(precvaldeb) as precvaldeb, sum(precvalcred) as precvalcred, sum(valdebit) as valdebit , sum(valcredit) as valcredit
|
||
from
|
||
(select id_part, cont, acont, an, luna, id_valuta, id_sucursala, precdeb, preccred, debit, credit, precvaldeb, precvalcred, valdebit, valcredit
|
||
FROM balanta_parteneri
|
||
where AN = 2018 and luna = 3 and cont not in ('5124','5121')
|
||
union all
|
||
select id_part, cont, acont, an, luna, id_valuta, id_sucursala, -precdeb, -preccred, -debit, -credit, -precvaldeb, -precvalcred, -valdebit, -valcredit
|
||
from ireg_parteneri
|
||
where AN = 2018 and luna = 3 AND CONT IN ('401', '4111'))
|
||
group by id_part, cont, acont, an, luna, id_valuta, id_sucursala);
|
||
|
||
|
||
|
||
ENDTEXT
|
||
|
||
TEXT To lcIregP2BalantaP Noshow
|
||
insert into balanta_parteneri (id_part, cont, acont, an, luna, id_valuta, id_sucursala, precdeb,preccred, debit, credit, precvaldeb, precvalcred, valdebit, valcredit)
|
||
select id_part, cont, acont, an, luna, id_valuta, id_sucursala, sum(precdeb) as precdeb, sum(preccred) as preccred, sum(debit) as debit, sum(credit) as credit , sum(precvaldeb) as precvaldeb, sum(precvalcred) as precvalcred, sum(valdebit) as valdebit , sum(valcredit) as valcredit
|
||
FROM ireg_parteneri
|
||
where AN = 2019 and luna = 3 and cont in ('401','4111')
|
||
group by id_part, cont, acont, an, luna, id_valuta, id_sucursala
|
||
ENDTEXT
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE initfacturi
|
||
* Initializeaza direct ireg_parteneri si jc2007, jv2007 din facturi.xlsx
|
||
|
||
*********************************************
|
||
*** Sterg inregistrarile din Jurnalele TVA
|
||
*********************************************
|
||
Local lcSql, llSucces, lnAchitat, lnAchitatVal, lnFacturat, lnFacturatVal, lnTVA, lnTotVal
|
||
Local lnTotctva, lnValoare, loFact
|
||
PRIVATE pcAcont, pcCont, pcSerieAct, pdDataAct, pdDataIreg, pdDataScad, pnAn, pnCredit, pnDebit
|
||
PRIVATE pnIdFact, pnIdFdoc, pnIdPart, pnIdValuta, pnLuna, pnNrAct, pnTotCtva, pnTotftvatax
|
||
PRIVATE pnTottvatax, pnValCredit, pnValDebit, pnro19nb, pnro19nt, pnro20nb, pnro20nt, pnro24nb
|
||
PRIVATE pnro24nt, pnro5nb, pnro5nt, pnro9nb, pnro9nt
|
||
|
||
TEXT TO lcSql TEXTMERGE noshow
|
||
BEGIN
|
||
DELETE FROM JC2007 WHERE an = ?pnAnI and luna = ?pnLunaI;
|
||
DELETE FROM JV2007 WHERE an = ?pnAnI and luna = ?pnLunaI;
|
||
END;
|
||
ENDTEXT
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !m.llSucces
|
||
Use In (Select('cFacturiTemp'))
|
||
Return m.llSucces
|
||
Endif
|
||
|
||
SET STEP ON
|
||
Select cFacturiTemp
|
||
Scan
|
||
Scatter Name loFact
|
||
pnAn = an
|
||
pnLuna = luna
|
||
pcCont = Alltrim(Cont)
|
||
pcAcont = Alltrim(acont)
|
||
pnIdFact = id_fact
|
||
pnIdPart = id_part
|
||
pnIdValuta = id_valuta
|
||
pcSerieAct = Alltrim(Nvl(serie_act, ''))
|
||
pnNrAct = nract
|
||
pdDataAct = Dataact
|
||
pdDataIreg = Dataact
|
||
pdDataScad = datascad
|
||
lnValoare = valoare
|
||
lnTotVal = totval
|
||
lnTVA = tva
|
||
lnFacturat = facturat
|
||
lnAchitat = achitat
|
||
lnFacturatVal = facturatval
|
||
lnAchitatVal = achitatval
|
||
lnTotVal = totval
|
||
If m.lnFacturat <> 0 Or m.lnFacturatVal <> 0
|
||
lnTotctva = m.lnFacturat
|
||
* Daca sunt completate facturat/achitat
|
||
If Inlist(m.pcCont, '401', '404', '462', '419', '408', '4551','455')
|
||
pnDebit = m.lnAchitat
|
||
pnValDebit = m.lnAchitatVal
|
||
pnCredit = m.lnFacturat
|
||
pnValCredit = m.lnFacturatVal
|
||
Else
|
||
pnDebit = m.lnFacturat
|
||
pnValDebit = m.lnFacturatVal
|
||
pnCredit = m.lnAchitat
|
||
pnValCredit = m.lnAchitatVal
|
||
Endif
|
||
Else
|
||
lnTotctva = m.lnValoare
|
||
* Daca este completat doar soldul
|
||
If Inlist(m.pcCont, '401', '404', '462', '419', '408')
|
||
pnDebit = 0
|
||
pnValDebit = 0
|
||
pnCredit = m.lnValoare
|
||
pnValCredit = m.lnTotVal
|
||
Else
|
||
pnDebit = m.lnValoare
|
||
pnValDebit = m.lnTotVal
|
||
pnCredit = 0
|
||
pnValCredit = 0
|
||
Endif
|
||
Endif
|
||
pnIdFdoc = 42 && FACTURA
|
||
pnTotCtva = Iif(m.lnTotctva <> 0, m.lnTotctva, baza24 + tva24 + baza20 + tva20 + baza19 + tva19 + baza09 + tva09 + baza05 + tva05)
|
||
pnTottvatax = tva24 + tva20 + tva19 + tva09 + tva05
|
||
pnTotftvatax = m.pnTotCtva - m.pnTottvatax
|
||
pnro24nb = baza24
|
||
pnro24nt = tva24
|
||
pnro20nb = baza20
|
||
pnro20nt = tva20
|
||
pnro19nb = baza19
|
||
pnro19nt = tva19
|
||
pnro9nb = baza09
|
||
pnro9nt = tva09
|
||
pnro5nb = baza05
|
||
pnro5nt = tva05
|
||
|
||
If m.lnValoare <> 0
|
||
TEXT TO lcSql TEXTMERGE NOSHOW
|
||
MERGE INTO IREG_PARTENERI A USING (SELECT <<pnAn>> as an, <<pnLuna>> as luna, '<<pcCont>>' as cont, <<pnIdFact>> as id_fact from dual) B ON (A.ID_FACT = B.ID_FACT AND A.AN = B.AN AND A.LUNA = B.LUNA AND A.CONT = B.CONT)
|
||
WHEN NOT MATCHED THEN
|
||
INSERT (an, luna, cont, id_fact, id_part, id_fdoc, nract, dataact, dataireg, datascad, debit, credit) VALUES (?pnAn, ?pnLuna, ?pcCont, ?pnIdFact, ?pnIdPart, ?pnIdFdoc, ?pnNract, ?pdDataact, ?pdDataIreg, ?pdDatascad, ?pnDebit, ?pnCredit)
|
||
WHEN MATCHED THEN
|
||
UPDATE SET debit = ?pnDebit, credit = ?pnCredit
|
||
ENDTEXT
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Endif && m.lnValoare <> 0
|
||
|
||
If m.lnTVA <> 0
|
||
|
||
TEXT TO lcSql TEXTMERGE NOSHOW
|
||
MERGE INTO <<IIF(pcCont = '4111', 'jv2007', 'jc2007')>> A
|
||
USING DUAL B ON (A.AN = ?pnAn and A.LUNA = ?pnLuna and A.ID_FACT = ?pnIdFact)
|
||
WHEN NOT MATCHED THEN
|
||
insert (an, luna, id_fact, id_part, nract, serie_act, dataact, dataireg, id_fdoc, totctva, tottvatax, totftvatax, ro24nb, ro24nt, ro20nb, ro20nt, ro19nb, ro19nt, ro9nb, ro9nt, ro5nb, ro5nt)
|
||
values (?pnAn, ?pnLuna, ?pnIdFact, ?pnIdPart, ?pnNract, ?pcSerieAct, ?pdDataact, ?pdDataIreg, ?pnIdFdoc, ?pnTotctva, ?pnTottvatax, ?pnTotftvatax, ?pnro24nb, ?pnro24nt, ?pnro20nb, ?pnro20nt, ?pnro19nb, ?pnro19nt, ?pnro9nb, ?pnro9nt, ?pnro5nb, ?pnro5nt)
|
||
WHEN MATCHED THEN
|
||
UPDATE SET totctva = ?pnTotctva, tottvatax = ?pnTottvatax, totftvatax = ?pnTotftvatax, ro24nb = ?pnRo24nb, ro24nt = ?pnRo24nt,
|
||
ro20nb = ?pnRo20nb, ro20nt = ?pnRo20nt, ro19nb = ?pnRo19nb, ro19nt = ?pnRo19nt, ro9nb = ?pnRo9nb, ro9nt = ?pnRo9nt, ro5nb = ?pnRo5nb, ro5nt = ?pnRO5nt
|
||
ENDTEXT
|
||
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !m.llSucces
|
||
Exit
|
||
Endif
|
||
Endif && m.lnTVA <> 0
|
||
Endscan && cFacturiTemp
|
||
|
||
* insert into ACT, DOCUMENTE INREGISTRARI DIN JURNALE TVA, NECESARE PENTRU EXIGIBILIZARE TVA INCASARE
|
||
TEXT TO lcSql TEXTMERGE noshow
|
||
BEGIN
|
||
|
||
DELETE FROM ACT WHERE an = ?pnAnI and luna = ?pnLunaI AND id_util = -4;
|
||
DELETE FROM DOCUMENTE WHERE id_util = -4;
|
||
|
||
INSERT INTO ACT
|
||
(AN,
|
||
LUNA,
|
||
ID_SUCURSALA,
|
||
SUMA,
|
||
EXPLICATIA,
|
||
SCD,
|
||
ASCD,
|
||
SCC,
|
||
ASCC,
|
||
PROC_TVA,
|
||
ID_JTVA_COLOANA,
|
||
ID_FACT,
|
||
DATAACT,
|
||
DATAIREG,
|
||
ID_FDOC,
|
||
NRACT,
|
||
ID_UTIL,
|
||
DATAORA)
|
||
select an,
|
||
luna,
|
||
id_sucursala,
|
||
0 as suma,
|
||
'NOTE PENTRU EXIGIBILIZARE TVA INCASARE' AS EXPLICATIA,
|
||
'4428' as scd,
|
||
'' as ascd,
|
||
'401' as scc,
|
||
'' as ascc,
|
||
case
|
||
when ro24nt <> 0 then
|
||
1.24
|
||
when ro20nt <> 0 then
|
||
1.20
|
||
when ro19nt <> 0 then
|
||
1.19
|
||
when ro9nt <> 0 then
|
||
1.09
|
||
when ro5nt <> 0 then
|
||
1.05
|
||
else
|
||
1.20
|
||
end as proc_tva,
|
||
case
|
||
when ro24nt <> 0 then
|
||
171
|
||
when ro20nt <> 0 then
|
||
179
|
||
when ro19nt <> 0 then
|
||
189
|
||
when ro9nt <> 0 then
|
||
173
|
||
when ro5nt <> 0 then
|
||
175
|
||
else
|
||
179
|
||
end as id_jtva_coloana,
|
||
id_fact,
|
||
dataact,
|
||
dataact as dataireg,
|
||
id_fdoc,
|
||
nract,
|
||
-4 as id_util,
|
||
sysdate as dataora
|
||
from jc2007
|
||
where an = ?pnAnI
|
||
and luna = ?pnLunaI;
|
||
|
||
INSERT INTO ACT
|
||
(AN,
|
||
LUNA,
|
||
ID_SUCURSALA,
|
||
SUMA,
|
||
EXPLICATIA,
|
||
SCD,
|
||
ASCD,
|
||
SCC,
|
||
ASCC,
|
||
PROC_TVA,
|
||
ID_JTVA_COLOANA,
|
||
ID_FACT,
|
||
DATAACT,
|
||
DATAIREG,
|
||
ID_FDOC,
|
||
NRACT,
|
||
ID_UTIL,
|
||
DATAORA)
|
||
select an,
|
||
luna,
|
||
id_sucursala,
|
||
0 as suma,
|
||
'NOTE PENTRU EXIGIBILIZARE TVA INCASARE' AS EXPLICATIA,
|
||
'4111' as scd,
|
||
'' as ascd,
|
||
'4428' as scc,
|
||
'' as ascc,
|
||
case
|
||
when ro24nt <> 0 then
|
||
1.24
|
||
when ro20nt <> 0 then
|
||
1.20
|
||
when ro19nt <> 0 then
|
||
1.19
|
||
when ro9nt <> 0 then
|
||
1.09
|
||
when ro5nt <> 0 then
|
||
1.05
|
||
else
|
||
1.20
|
||
end as proc_tva,
|
||
case
|
||
when ro24nt <> 0 then
|
||
22
|
||
when ro20nt <> 0 then
|
||
30
|
||
when ro19nt <> 0 then
|
||
32
|
||
when ro9nt <> 0 then
|
||
24
|
||
when ro5nt <> 0 then
|
||
26
|
||
else
|
||
30
|
||
end as id_jtva_coloana,
|
||
id_fact,
|
||
dataact,
|
||
dataact as dataireg,
|
||
id_fdoc,
|
||
nract,
|
||
-4 as id_util,
|
||
sysdate as dataora
|
||
from jv2007
|
||
where an = ?pnAnI
|
||
and luna = ?pnLunaI;
|
||
|
||
insert into documente (id_doc, dataora, id_util, nract, dataact)
|
||
select id_fact as id_doc,sysdate, -4 as id_util, nract, dataact
|
||
from jc2007
|
||
where an = ?pnAnI
|
||
and luna = ?pnLunaI and id_fact not in (select id_doc from documente);
|
||
|
||
|
||
insert into documente
|
||
(id_doc, dataora, id_util, nract, dataact)
|
||
select id_fact as id_doc, sysdate, -4 as id_util, nract, dataact
|
||
from jv2007
|
||
where an = ?pnAnI
|
||
and luna = ?pnLunaI and id_fact not in (select id_doc from documente);
|
||
END;
|
||
ENDTEXT
|
||
|
||
If m.llSucces
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
Endif
|
||
|
||
* Creez balanta parteneri din ireg_parteneri
|
||
If m.llSucces And AMessagebox('Se regenereaza balanta de parteneri dupa facturi?' + Chr(13) + 'Atentie! Se sterg balantele de parteneri pentru toate conturile!', 4 + 32, _Screen.Caption) = 6
|
||
|
||
TEXT TO lcSql TEXTMERGE noshow
|
||
BEGIN
|
||
delete from balanta_parteneri where AN = ?pnAnI and luna = ?pnLunaI;
|
||
insert into balanta_parteneri
|
||
(precdeb, preccred, debit, credit, precvaldeb, precvalcred, valdebit, valcredit, id_part, cont, acont, an, luna, id_valuta, id_sucursala)
|
||
select
|
||
sum(precdeb) as precdeb, sum(preccred)as preccred, sum(debit) as debit, sum(credit) as credit , sum(precvaldeb) as precvaldeb, sum(precvalcred) as precvalcred, sum(valdebit) as valdebit , sum(valcredit) as valcredit, id_part, cont, acont, an, luna, id_valuta, id_sucursala
|
||
from ireg_parteneri
|
||
where AN = ?pnAnI and luna = ?pnLunaI
|
||
group by id_part, cont, acont, an, luna, id_valuta, id_sucursala;
|
||
END;
|
||
ENDTEXT
|
||
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
|
||
Endif && messagebox
|
||
|
||
Return m.llSucces
|
||
ENDPROC
|
||
|
||
PROCEDURE modificanote
|
||
Private pnCod
|
||
Local loForm As [frm_modific2024]
|
||
Local llMaximize, llNotaNoua, lnIdSet, llSucces
|
||
Local lcSql, ldDataAct, llFurnizorTVAI, lnIdJtva, lnSucces, lnTaxCode
|
||
llSucces = .T.
|
||
update_jtva_coloane("", 'crsExplicatiiTVATemp', 6)
|
||
|
||
lcSql = [SELECT * FROM vact WHERE 1=2]
|
||
llSucces = goExecutor.oExecuta(m.lcSql, 'actactan_temp')
|
||
If m.llSucces
|
||
Select actactan_temp
|
||
If Used('actactan')
|
||
Append From Dbf('actactan')
|
||
Endif
|
||
|
||
Select .F. As ales, Iif(Empty(Nvl(a.id_jtva_coloana,0)), 0, 1) As Cu_tva, Cast(0 As N(2)) As tipnota, a.*, b.denumire As explicatie_tva_2 ;
|
||
From actactan_temp a Left Join crsExplicatiiTVATemp b On a.id_jtva_coloana = b.id_jtva_coloana ;
|
||
Into Cursor tact Readwrite
|
||
|
||
Update tact Set explicatie_tva = explicatie_tva_2
|
||
|
||
Use In (Select('actactan_temp'))
|
||
|
||
* Actualizez taxcode SAFT in functie de furnizori RTVAI
|
||
If m.gl406
|
||
Select tact
|
||
Scan For !Empty(id_jtva_coloana)
|
||
lnIdJtva = id_jtva_coloana
|
||
ldDataAct = dataact
|
||
llFurnizorTVAI = .F.
|
||
lnTaxCode = GetTaxCode(m.gnAn, m.gnLuna, m.ldDataAct, m.lnIdJtva, m.llFurnizorTVAI)
|
||
Select tact
|
||
Replace taxcode With m.lnTaxCode
|
||
Endscan
|
||
|
||
Update tact Set paymentcode = '42' Where Left(scd,3) = '512' Or Left(scc,3) = '512' && plata in contul bancar
|
||
Update tact Set paymentcode = '10' Where scd = '5311' && plata numerar
|
||
|
||
* completez taxcode plati buget salarii, impozit venit/profit, tva de plata
|
||
UPDATE tact SET taxcode = 412000 WHERE scd = '4315' or scc = '4315'
|
||
UPDATE tact SET taxcode = 432000 WHERE scd = '4316' or scc = '4316'
|
||
UPDATE tact SET taxcode = 480000 WHERE scd = '436' or scc = '436'
|
||
UPDATE tact SET taxcode = 602000 WHERE scd = '444' or scc = '444'
|
||
UPDATE tact SET taxcode = 301000 WHERE scd = '4423' or scc = '4423'
|
||
UPDATE tact SET taxcode = 100000 WHERE scd = '4411' or scc = '4411'
|
||
UPDATE tact SET taxcode = 120000 WHERE scd = '4418' or scc = '4418'
|
||
Endif && gl406
|
||
Endif && llSucces
|
||
|
||
update_jtva_coloane("", "crsJtvaTemp", 1)
|
||
|
||
IF m.llSucces
|
||
* Creez cursoare trul, trul_obinv
|
||
lcSql = [select * from vrul_tot where 1=2]
|
||
lcCursor = [v_rul]
|
||
|
||
llSucces = goExecutor.oExecuta(lcSql,lcCursor)
|
||
|
||
If m.llSucces
|
||
Select *, ;
|
||
valoare + valtva As valoarectva, ;
|
||
valoarev + valtvav As valoarevctva ;
|
||
From v_rul ;
|
||
Into Cursor trul READWRITE
|
||
|
||
Use In (SELECT('v_rul'))
|
||
|
||
lcSql = [select * from vrul_obinv_tot where 1=2]
|
||
lcCursor = [v_rul_obinv]
|
||
|
||
llSucces = goExecutor.oExecuta(lcSql,lcCursor)
|
||
Endif
|
||
|
||
If m.llSucces
|
||
Select *, ;
|
||
valoare + valtva As valoarectva, ;
|
||
valoarev + valtvav As valoarevctva ;
|
||
From v_rul_obinv ;
|
||
Into Cursor trul_obinv READWRITE
|
||
|
||
Use In (SELECT('v_rul_obinv'))
|
||
Endif
|
||
ENDIF
|
||
|
||
Use In (Select('crsExplicatiiTVATemp'))
|
||
IF m.llSucces
|
||
lnIdSet = 90024
|
||
Select tact
|
||
Go Top
|
||
llNotaNoua = .T.
|
||
llMaximize = .T.
|
||
loForm = Createobject([frm_modific2024], m.lnIdSet, m.llNotaNoua)
|
||
loForm.lb_titlu_alb_b121.Caption = 'Initializare solduri'
|
||
Select tact
|
||
loForm.Show(1, m.llMaximize)
|
||
llSucces = (buton = 1)
|
||
ENDIF
|
||
|
||
If m.llSucces
|
||
Use In (Select('actactan'))
|
||
|
||
Select tact
|
||
Replace id_jtva_coloana With Null, PROC_TVA With 0 For Cu_tva = 0
|
||
|
||
Select * From tact Into Cursor actactan Readwrite
|
||
|
||
lcSql = [SELECT seq_cod.nextval FROM dual]
|
||
Select Distinct id_fact From actactan Into Cursor cIdFact
|
||
Select cIdFact
|
||
Scan
|
||
Wait Window 'Generez cod pentru factura ' + Transform(Recno()) + '/' + Transform(Reccount()) Nowait
|
||
pnCod = 0
|
||
llSucces = goExecutor.oSelecteaza2Value(m.lcSql, @pnCod)
|
||
If m.llSucces
|
||
Update actactan Set cod = m.pnCod Where id_fact = cIdFact.id_fact
|
||
Endif
|
||
Endscan
|
||
Use In (Select('cIdFact'))
|
||
|
||
Select actactan
|
||
lnSucces = OSCRIE_IN_FISIERE(0, .F., .F., .T.)
|
||
llSucces = (m.lnSucces = 1)
|
||
Endif && buton = 1
|
||
|
||
|
||
Use In (Select('tAct'))
|
||
Use In (Select('actactan'))
|
||
Use In (Select('trul'))
|
||
Use In (Select('rul_temp'))
|
||
Use In (Select('trul_obinv'))
|
||
Use In (Select('rul_temp_obinv'))
|
||
USE IN (SELECT('crsJtvaTemp'))
|
||
|
||
|
||
Return m.llSucces
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE prelucreazafisierebreeze
|
||
ENDPROC
|
||
|
||
PROCEDURE refreshcontrols
|
||
Local lnOptiune
|
||
lnOptiune = This.optInit.Value
|
||
If m.lnOptiune = 1
|
||
This.cmdNoteFacturiBalanta.Enabled = .T.
|
||
This.chkModificaNote.Enabled = .T.
|
||
This.cmdStergeNote.Enabled = .T.
|
||
|
||
This.cmdNoteFacturiBalanta.visible = .T.
|
||
This.chkModificaNote.visible = .T.
|
||
This.cmdStergeNote.visible = .T.
|
||
|
||
******
|
||
|
||
This.chkParteneriBalantaVerificare.Enabled = .F.
|
||
This.cmdInitBalanta.Enabled = .F.
|
||
This.cmdInitFacturi.Enabled = .F.
|
||
|
||
This.chkParteneriBalantaVerificare.visible = .F.
|
||
This.cmdInitBalanta.visible = .F.
|
||
This.cmdInitFacturi.visible = .F.
|
||
|
||
Else
|
||
This.cmdNoteFacturiBalanta.Enabled = .F.
|
||
This.chkModificaNote.Enabled = .F.
|
||
This.cmdStergeNote.Enabled = .F.
|
||
|
||
This.cmdNoteFacturiBalanta.visible = .F.
|
||
This.chkModificaNote.visible = .F.
|
||
This.cmdStergeNote.visible = .F.
|
||
|
||
******
|
||
|
||
This.chkParteneriBalantaVerificare.Enabled = .T.
|
||
This.cmdInitBalanta.Enabled = .T.
|
||
This.cmdInitFacturi.Enabled = .T.
|
||
|
||
This.chkParteneriBalantaVerificare.visible = .T.
|
||
This.cmdInitBalanta.visible = .T.
|
||
This.cmdInitFacturi.visible = .T.
|
||
Endif
|
||
ENDPROC
|
||
|
||
PROCEDURE scrienote
|
||
Local lcMesaj, lcSql, lnHandle, lnSucces, lnSucces2
|
||
Private gnAn, gnIdSucursala, gnIdUtil, gnLuna
|
||
lnSucces = SQLSetprop(m.lnHandle, "Transactions", 2)
|
||
llSucces = (m.lnSucces > 0)
|
||
if m.llSucces
|
||
&& Creez note pentru fiecare luna/an
|
||
Select Distinct an, luna From actactan Order By an, luna Into Cursor cAnLuna
|
||
Select cAnLuna
|
||
Scan
|
||
*gnAn = an
|
||
*gnLuna = luna
|
||
*gnIdUtil = - 3
|
||
*gnIdSucursala = Null
|
||
|
||
if m.llSucces
|
||
lcSql = [begin pack_contafin.init_scriere_act_rul_local(?gnIdUtil,?gnAn,?gnLuna, 1,0,?gnIdSucursala); end;]
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
Endif
|
||
|
||
if m.llSucces
|
||
SELECT * FROM actactan WHERE an = m.gnAn AND luna = m.gnLuna INTO CURSOR cAct NOFILTER
|
||
llSucces = This.sql_temp_insert('cAct', 'ACT_TEMP')
|
||
USE IN (SELECT('cAct'))
|
||
Endif
|
||
|
||
if m.llSucces
|
||
Private pcMesajRefacereNota
|
||
pcMesajRefacereNota = ""
|
||
lcSql = [begin pack_contafin.final_scriere_act_rul_local(?gnIdUtil, ?gnAn, ?gnLuna, NULL, 0, 1, ?@pcMesajRefacereNota); end;]
|
||
llSucces = goExecutor.oExecuta(m.lcSql)
|
||
If !Empty(Nvl(pcMesajRefacereNota, ''))
|
||
This.trace(m.pcMesajRefacereNota)
|
||
*AMESSAGEBOX(m.pcMesajRefacereNota, 0 + 48, 'Atentie')
|
||
Endif
|
||
Release pcMesajRefacereNota
|
||
ELSE
|
||
lcMesaj = goExecutor.cEroare
|
||
This.Trace(m.lcMesaj)
|
||
*AMESSAGEBOX(m.lcMesaj,0+48, _screen.Caption )
|
||
EXIT
|
||
ENDIF && lnSucces
|
||
Endscan && cAnLuna
|
||
|
||
Use In (Select('cAnLuna'))
|
||
|
||
if m.llSucces
|
||
llSucces2 = goExecutor.oExecuta('COMMIT')
|
||
llSucces = m.llSucces2
|
||
Else
|
||
llSucces2 = goExecutor.oExecuta('ROLLBACK')
|
||
Endif
|
||
|
||
If !m.llSucces2
|
||
This.trace(goExecutor.cEroare)
|
||
*AMESSAGEBOX(lcSql + Chr(13) + goExecutor.cEroare, 0 + 16, "Eroare")
|
||
ELSE
|
||
This.trace('Import realizat cu succes!')
|
||
Endif
|
||
|
||
lnSucces3 = SQLSetprop(m.lnHandle, "Transactions", 1)
|
||
|
||
If m.lnSucces3 <> 1
|
||
lcMesaj = 'Programul nu a reusit sa treaca pe tranzactie automata. Iesiti din program si intrati din nou!'
|
||
This.trace(m.lcMesaj)
|
||
AMESSAGEBOX(m.lcMesaj, 0 + 48, 'Atentie!')
|
||
llSucces = .F.
|
||
Endif
|
||
Endif && llSucces
|
||
|
||
Return m.llSucces
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE sql_temp_insert
|
||
Lparameters tcAlias, tcTableName
|
||
|
||
Local lcAlias, lcCursor, llSucces
|
||
Local lcDate, lcFieldName, lcFieldType, lcFieldValue, lcFields, lcInsert, lcMesaj, lcNumber, lcSql
|
||
Local lcString, lcTableName, lcValues, lnCount
|
||
Local lnField
|
||
|
||
Private poIreg, pcTableName
|
||
Local lcVariable
|
||
|
||
If Empty(tcAlias)
|
||
lcAlias = 'ACTACTAN'
|
||
Else
|
||
lcAlias = Upper(Alltrim(tcAlias))
|
||
Endif
|
||
If Empty(tcTableName)
|
||
pcTableName = 'ACT_TEMP'
|
||
Else
|
||
pcTableName = Upper(Alltrim(tcTableName))
|
||
Endif
|
||
|
||
lcCursor = 'crs_Coloane'
|
||
lcSql = [SELECT COLUMN_NAME AS COLOANA from user_tab_columns WHERE table_name = ?pcTableName]
|
||
llSucces = goExecutor.oExecuta(m.lcSql, m.lcCursor)
|
||
If !m.llSucces
|
||
lcMesaj = 'Eroare la selectare coloane din ' + m.lcTableName + Chr(13) + goExecutor.cEroare
|
||
This.trace(m.lcMesaj)
|
||
*AMESSAGEBOX(m.lcMesaj, 0 + 16, 'Eroare')
|
||
Endif
|
||
|
||
If m.llSucces
|
||
Select (lcAlias)
|
||
lnCount = Fcount()
|
||
lcFields = []
|
||
lcValues = []
|
||
lcInsert = []
|
||
For lnField = 1 To lnCount
|
||
Select (lcAlias)
|
||
lcFieldName = Upper(Alltrim(Field(lnField)))
|
||
lcFieldType = Type(lcFieldName)
|
||
lcFieldValue = Evaluate(lcFieldName)
|
||
If lcFieldType = 'C'
|
||
lcVariable = '?ALLTRIM(poIreg.' + lcFieldName + ')'
|
||
Else
|
||
lcVariable = '?poIreg.' + lcFieldName
|
||
Endif
|
||
|
||
Select CRS_COLOANE
|
||
Locate For Upper(Alltrim(COLOANA)) == lcFieldName
|
||
If !Found()
|
||
Loop
|
||
Endif
|
||
lcFields = lcFields + [,] + lcFieldName
|
||
lcValues = lcValues + [,] + lcVariable
|
||
Endfor
|
||
|
||
lcFields = Substr(lcFields, 2)
|
||
lcValues = Substr(lcValues, 2)
|
||
|
||
&& INSERT INTO ACT_TEMP
|
||
lcInsert = [INSERT INTO ] + m.pcTableName + [ (] + m.lcFields + [) VALUES (] + m.lcValues + [)]
|
||
|
||
Select (lcAlias)
|
||
Scan
|
||
Scatter Name poIreg
|
||
IF MOD(RECNO(), 100) = 0
|
||
This.trace(' Inregistrare ' + TRANSFORM(RECNO()) + ' / ' + TRANSFORM(RECCOUNT()) + ' ...')
|
||
ENDIF
|
||
&& FAC NULL ID-URILE CU VALOARE 0
|
||
If TYPE('poReg.id_sucursala') = 'N' AND poIreg.id_sucursala = 0
|
||
poIreg.id_sucursala = Null
|
||
Endif
|
||
|
||
llSucces = goExecutor.oExecuta(m.lcInsert)
|
||
If lnSucces <> 1
|
||
This.trace(goExecutor.cEroare)
|
||
*AMESSAGEBOX(goExecutor.cEroare, 0 + 16, 'Eroare')
|
||
Exit
|
||
Endif
|
||
|
||
Endscan
|
||
Endif
|
||
|
||
Use In (Select('crs_coloane'))
|
||
|
||
Return m.llSucces
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE sql_temp_insert_values
|
||
* daca se doreste insert into act_temp cu valori in loc de ?
|
||
* trebuie decomentata sectiunea cu valori
|
||
Lparameters tcAlias, tcTableName
|
||
|
||
Local lcAlias, lcCursor, llSucces
|
||
Local lcDate, lcFieldName, lcFieldType, lcFieldValue, lcFields, lcInsert, lcMesaj, lcNumber, lcSql
|
||
Local lcString, lcTableName, lcValues, lnCount
|
||
Local lnField
|
||
|
||
Private poIreg, pcTableName
|
||
Local lcVariable
|
||
|
||
If Empty(tcAlias)
|
||
lcAlias = 'ACTACTAN'
|
||
Else
|
||
lcAlias = Upper(Alltrim(tcAlias))
|
||
Endif
|
||
If Empty(tcTableName)
|
||
pcTableName = 'ACT_TEMP'
|
||
Else
|
||
pcTableName = Upper(Alltrim(tcTableName))
|
||
Endif
|
||
|
||
lcCursor = 'crs_Coloane'
|
||
lcSql = [SELECT COLUMN_NAME AS COLOANA from user_tab_columns WHERE table_name = ?pcTableName]
|
||
llSucces = goExecutor.oExecuta(m.lcSql, m.lcCursor)
|
||
If !m.llSucces
|
||
lcMesaj = 'Eroare la selectare coloane din ' + m.pcTableName + Chr(13) + goExecutor.cEroare
|
||
This.trace(m.lcMesaj)
|
||
*AMESSAGEBOX(m.lcMesaj, 0 + 16, 'Eroare')
|
||
Endif
|
||
|
||
If m.llSucces
|
||
Select (lcAlias)
|
||
lnCount = Fcount()
|
||
Scan
|
||
lcFields = []
|
||
lcValues = []
|
||
lcInsert = []
|
||
Scatter Name poIreg
|
||
|
||
For lnField = 1 To lnCount
|
||
Select (lcAlias)
|
||
lcFieldName = Upper(Alltrim(Field(lnField)))
|
||
lcFieldType = Type(lcFieldName)
|
||
lcFieldValue = Evaluate(lcFieldName)
|
||
If lcFieldType = 'C'
|
||
lcVariable = '?ALLTRIM(poIreg.' + lcFieldName + ')'
|
||
Else
|
||
lcVariable = '?poIreg.' + lcFieldName
|
||
Endif
|
||
|
||
Select CRS_COLOANE
|
||
Locate For Upper(Alltrim(COLOANA)) == lcFieldName
|
||
If !Found()
|
||
Loop
|
||
Endif
|
||
lcFields = lcFields + [,] + lcFieldName
|
||
lcNumber = '0'
|
||
lcDate = ''
|
||
lcString = ''
|
||
|
||
&& FAC NULL ID-URILE CU VALOARE 0
|
||
If Inlist(lcFieldName, 'ID_SUCURSALA')
|
||
If poIreg.id_sucursala = 0
|
||
poIreg.id_sucursala = Null
|
||
Endif
|
||
Endif
|
||
lcValues = lcValues + [,] + lcVariable
|
||
|
||
*!* DO CASE
|
||
*!* CASE lcFieldType = 'N'
|
||
*!* IF EMPTY(lcFieldValue) OR ISNULL(lcFieldValue)
|
||
*!* lcNumber = '0'
|
||
*!* ELSE
|
||
*!* lcNumber = ALLTRIM(STR(lcFieldValue,24,4))
|
||
*!* ENDIF
|
||
|
||
*!* lcValues = lcValues + [,] + lcNumber
|
||
*!* CASE lcFieldType = 'C'
|
||
*!* IF EMPTY(lcFieldValue) OR ISNULL(lcFieldValue)
|
||
*!* lcString = ''
|
||
*!* ELSE
|
||
*!* lcString = ALLTRIM(lcFieldValue)
|
||
*!* ENDIF
|
||
|
||
*!* lcValues = lcValues + [,] + ['] + lcString + [']
|
||
*!* CASE INLIST(lcFieldType,'D','T')
|
||
*!* IF EMPTY(lcFieldValue) OR ISNULL(lcFieldValue)
|
||
*!* lcDate = ''
|
||
*!* ELSE
|
||
*!* lcDate = DTOS(lcFieldValue)
|
||
*!* ENDIF
|
||
*!* lcValues = lcValues + [,] + [TO_DATE('] + lcDate + [','YYYY-MM-DD')]
|
||
*!* ENDCASE
|
||
|
||
Endfor
|
||
|
||
lcFields = Substr(lcFields, 2)
|
||
lcValues = Substr(lcValues, 2)
|
||
|
||
&& INSERT INTO ACT_TEMP
|
||
lcInsert = [INSERT INTO ] + m.pcTableName + [ (] + lcFields + [) VALUES (] + lcValues + [)]
|
||
llSucces = goExecutor.oExecuta(m.lcInsert)
|
||
If !m.llSucces
|
||
This.Trace(goExecutor.cEroare)
|
||
*AMESSAGEBOX(goExecutor.cEroare, 0 + 16, 'Eroare')
|
||
Exit
|
||
Endif
|
||
|
||
Endscan
|
||
Endif
|
||
|
||
Use In (SELECT('crs_coloane'))
|
||
|
||
Return m.llSucces
|
||
|
||
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE sterge_import
|
||
Local lcCursor, lcMesaj, lcSql, llContinuare, lnDocumente, lnPercent, lnSucces
|
||
PRIVATE pnCod
|
||
llContinuare = .F.
|
||
lcSql = [select distinct cod from act where an=?gnAn and Luna=?gnLuna and id_set=90024 and sters=0]
|
||
lcCursor = [cCoduri]
|
||
llSucces = goExecutor.oExecuta(m.lcSql, m.lcCursor)
|
||
If m.llSucces
|
||
Select(m.lcCursor)
|
||
lnDocumente = Reccount()
|
||
If m.lnDocumente > 0
|
||
llContinuare = (AMessagebox('Doriti sa stergeti ' + Alltrim(Str(m.lnDocumente)) + ' document(e), tip operatie 90024, din luna ' + Padl(m.gnLuna, 2, '0') + '/' + Alltrim(Str(m.gnAn)) + '?', 4 + 32, _Screen.Caption) = 6)
|
||
Else
|
||
AMessagebox('Nu sunt inregistrari in luna ' + Padl(m.gnLuna, 2, '0') + '/' + Alltrim(Str(m.gnAn)), 0 + 64, _Screen.Caption)
|
||
llContinuare = .F.
|
||
Endif
|
||
Else
|
||
* AMessagebox(goExector.cEroare, 0 + 48, _Screen.Caption )
|
||
This.Trace(goExecutor.cEroare)
|
||
llContinuare = .F.
|
||
Endif
|
||
|
||
If !m.llContinuare
|
||
Use In (Select(m.lcCursor))
|
||
RETURN m.llSucces
|
||
Endif
|
||
|
||
lnSucces = SQLSetprop(m.gnhandle, "Transactions", 2)
|
||
llSucces = (m.lnSucces>0)
|
||
If m.llSucces
|
||
lcSql = [begin pack_contafin.init_scriere_act_rul_local(?gnIdUtil,?gnAn,?gnLuna, 1,0,?gnIdSucursala); end;]
|
||
llSucces = goExecutor.oExecuta(lcSql)
|
||
If m.llSucces
|
||
Select cCoduri
|
||
Scan
|
||
pnCod = cod
|
||
If Mod(Recno(), 10) = 0
|
||
lnPercent = Round(Recno() / Reccount() * 100, 0)
|
||
This.Trace('Stergere ' + Alltrim(Str(m.lnPercent)) + '%')
|
||
Endif
|
||
|
||
|
||
lcSql = [begin pack_contafin.sterge_document(?pnCod); end;]
|
||
lnSucces = goExecutor.oExecute(lcSql)
|
||
If lnSucces < 0
|
||
This.Trace(goExecutor.cEroare)
|
||
Exit
|
||
Endif
|
||
Endscan
|
||
Else
|
||
This.Trace(goExecutor.cEroare)
|
||
Endif
|
||
Else
|
||
This.Trace(goExecutor.cEroare)
|
||
ENDIF && llSucces
|
||
|
||
If !m.llSucces
|
||
goExecutor.oExecuta('ROLLBACK')
|
||
lcMesaj = 'Stergerea nu s-a efectuat!'
|
||
Else
|
||
goExecutor.oExecuta('COMMIT')
|
||
lcMesaj = 'Stergerea s-a efectuat cu succes'
|
||
Endif
|
||
|
||
SQLSetprop(m.gnhandle, "Transactions", 1)
|
||
|
||
This.Trace(m.lcMesaj)
|
||
AMessagebox(m.lcMesaj, 0 + 48, _Screen.Caption)
|
||
|
||
RETURN m.llSucces
|
||
ENDPROC
|
||
|
||
PROCEDURE templatexlsx
|
||
Local lcFile, lcXLSXTemplate
|
||
|
||
lcXLSXTemplate = ADDBS(m.gcAppPath) + "include\init_facturi_balanta_note.xlsx"
|
||
IF !FILE(m.lcXLSXTemplate)
|
||
AMESSAGEBOX('Nu exista fisierul ' + m.lcXLSXTemplate + CHR(13) + CHR(10) + 'Sunati la suportul tehnic',0+48)
|
||
RETURN
|
||
ENDIF
|
||
|
||
lcFile = PUTFILE('Salvati fisierul de initializare', 'init_facturi_balanta_note', 'xlsx')
|
||
|
||
COPY FILE (m.lcXLSXTemplate) TO (m.lcFile)
|
||
|
||
open_default_app(m.lcFile)
|
||
|
||
AMESSAGEBOX('Se va deschide fisierul xlsx cu instructiuni si cateva randuri completate pentru exemplificare.' + CHR(13) + CHR(10) + ;
|
||
'Completati fisierul, salvati-l si apoi importati-l',0+64,_screen.Caption)
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE todo
|
||
TEXT TO lcTodo noshow
|
||
1. OK DE VERIFICAT configurare conturi analitice 4428, pentru notele de tva neexigibil
|
||
ori in facturi.xlsx, ori optiuni la import
|
||
2. TOTAL SUME DEBITOARE/CREDITOARE PENTRU VENITURI SI CHELTUIELI PENTRU F20 BILANT
|
||
|
||
ENDTEXT
|
||
ENDPROC
|
||
|
||
PROCEDURE trace
|
||
LPARAMETERS tcMessage
|
||
|
||
This.edtInfo.Value = This.edtInfo.Value + TRANSFORM(NVL(tcMessage,'')) + CHR(13) + CHR(10)
|
||
this.edtInfo.SelStart = LEN(this.edtInfo.Value)
|
||
This.edtInfo.Refresh()
|
||
ENDPROC
|
||
|
||
PROCEDURE verificaimport
|
||
lcSql = [select COUNT(*) as nr from act where an=?m.gnAn and luna = ?gnLuna and id_set=90024 and sters=0]
|
||
|
||
lcCursor = [cInregistrari]
|
||
lnSucces = goExecutor.oExecute(m.lcSql, m.lcCursor)
|
||
If m.lnSucces > 0
|
||
Select(m.lcCursor)
|
||
lnInregistrari = Nvl(nr, 0)
|
||
ELSE
|
||
AMESSAGEBOX(goExecutor.cEroare)
|
||
This.Trace(goExecutor.cEroare)
|
||
lnInregistrari = 1 && ca sa nu mai continue importul
|
||
Endif
|
||
Use In (Select(m.lcCursor))
|
||
|
||
Return (m.lnInregistrari > 0)
|
||
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE But_help1.Click
|
||
LOCAL lcInfo
|
||
TEXT TO lcInfo noshow
|
||
1. Alegeti optiunea de import:
|
||
- Generare note in Registrul Jurnal pentru initializare facturi si balanta (RECOMANDAT)
|
||
- Initializare directa facturi si balanta
|
||
|
||
|
||
2. Apasati pe butonul "Sablon xlsx".
|
||
Se deschide un fisier sablon tip "xlsx" cu date de exemplificare.
|
||
Completati in fisier datele de initializare si stergeti datele de exemplificare.
|
||
|
||
3. Apasati pe butonul "Importa balanta si facturi (note contabile)".
|
||
ENDTEXT
|
||
|
||
AMESSAGEBOX(m.lcInfo)
|
||
ENDPROC
|
||
|
||
PROCEDURE cmdInitBalanta.Click
|
||
llSucces = Thisform.InitBalanta()
|
||
|
||
Thisform.Trace('Importul s-a incheiat!')
|
||
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE cmdInitFacturi.Click
|
||
Local llSucces
|
||
llSucces = Thisform.ExportDate()
|
||
If m.llSucces
|
||
llSucces = Thisform.InitFacturi()
|
||
Endif
|
||
|
||
Thisform.Trace('Importul s-a incheiat!')
|
||
|
||
AMessagebox('Importul s-a incheiat!', 0 + 64, _Screen.Caption)
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE cmdNoteFacturiBalanta.Click
|
||
Local llSucces
|
||
llSucces = .T.
|
||
If Thisform.VerificaImport()
|
||
llSucces = (AMessagebox('Atentie! Exista deja documente importate (tip operatie 90024) in perioada selectata! Daca continuati, se vor duplica! Doriti sa continuati?', 4 + 32, _Screen.Caption) = 6)
|
||
Endif
|
||
|
||
If m.llSucces
|
||
* import note din sursa externa (dbf, xls)
|
||
llSucces = Thisform.ExportDate()
|
||
|
||
* creez act_temp
|
||
If m.llSucces
|
||
llSucces = Thisform.Creeazanote()
|
||
|
||
* import act_temp
|
||
If used('actactan')
|
||
If Thisform.chkModificaNote.Value = 1
|
||
llSucces = Thisform.ModificaNote()
|
||
Else
|
||
llSucces = Thisform.Scrienote()
|
||
ENDIF
|
||
|
||
Thisform.Trace('Notele ' + IIF(m.llSucces, '', 'nu ') + 's-au scris in Registrul Jurnal')
|
||
|
||
USE IN (SELECT('actactan'))
|
||
Endif
|
||
Endif
|
||
Endif
|
||
Thisform.Trace('Importul s-a incheiat!')
|
||
|
||
AMessagebox('Importul s-a incheiat!',0+64,_Screen.Caption)
|
||
|
||
ENDPROC
|
||
|
||
PROCEDURE cmdStergeNote.Click
|
||
Thisform.sterge_import()
|
||
ENDPROC
|
||
|
||
PROCEDURE cmdTemplate.Click
|
||
Thisform.templatexlsx()
|
||
ENDPROC
|
||
|
||
PROCEDURE optInit.Valid
|
||
Thisform.RefreshControls()
|
||
ENDPROC
|
||
|
||
ENDDEFINE
|