romfast/vfp_roaauto
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
vfp_roaauto/COMUN/programe/ocompensari.prg

603 lines
23 KiB
Plaintext
Raw Permalink Blame History

*!* 27.09.2011
*!* marius.mutu
*!* create cursor calculcompens in loc de create table
*!* #3146 Steaua Nordului
*!* 31.01.2013
*!* marius.mutu
*!* calculcompens - tratare facturi cu tva_incasare: calculcompens.tva_incasare_a, id_fact (ex: plata 401 = 542)
*!* 19.04.2013
*!* marius.mutu
*!* calculcompens - tva incasare pentru ambele facturi din compensare. ex: 401 = 4111
*** proceduri introducere in actactan, nu merge pentru sume negative !!!!
*!* 25.12.2025
*!* calculcompens - tratare compensare 5121 = 419 cu cote TVA diferite (proc_tva, id_jtvacol)
*!* 07.01.206
*!* calculcompens_part - tratare compensare 419 lei 5121 = 419 cu cote TVA diferite (proc_tva, id_jtvacol)
******************************************* INCEPUT: calculcompens *******************************************
***
PROCEDURE calculcompens
PARAMETERS tcFisD, tcFisC, tnTipOrdonare, tnTipOprire
*!* modificare v 2.5.27 : tnTipOrdonare, tnTipOprire
*!* tnTipOrdonare - 0/implicit = ordonare descrescator dupa suma
*!* 1 = ordonare dupa data
*!* tnTipOprire - 0/implicit = cand sum(sumaachi) pentru ambele tabele este 0 ( initial, sum(sumaachi) dintr-un tabel este egala cu sum(sumaachi) din celalalt )
*!* 1 = cand sum(sumaachi) pentru unul dintre tabele este 0
PRIVATE pcCont
STORE '' TO pcCont
LOCAL max1, max2, nract1, nract2, lcFis1, lcFis2, id_fact1, id_fact2, llDefalcTVA, lnMaxTvai, lcOrdine
STORE 1 TO max1, max2
STORE 0 TO nract1, nract2, id_fact1, id_fact2
lnMaxTvai = 0
lcFisD = ALLTRIM(tcFisD)
lcFisC = ALLTRIM(tcFisC)
*!* modificare v 2.5.26
*!* modificare v 2.5.27 : am adaugat lcOrdine
*!* SELECT * FROM &lcFisD INTO CURSOR tperecheD READWRITE
*!* SELECT * FROM &lcFisC INTO CURSOR tperecheC READWRITE
lcOrdine = IIF(EVL(tnTipOrdonare,0)=1,[dataact,nract],[sumaachi desc])
SELECT * FROM &lcFisD WHERE ales AND sumaachi <> 0 ORDER BY &lcOrdine INTO CURSOR tperecheD READWRITE
SELECT * FROM &lcFisC WHERE ales AND sumaachi <> 0 ORDER BY &lcOrdine INTO CURSOR tperecheC READWRITE
*!* modificare v 2.5.26 ^
*** Aflu daca exista bifa TVA_INCASARE_A intr-unul din fisiere (ex: 401 = 542)
*** pentru a putea pune in cursorul rezultat id_fact din fisierul corespunzator
*** in cazul compensarilor 401 = 4111, pot fi amandoua facturile cu TVA incasare
llTvaIncasareD = .F.
llTvaIncasareC = .F.
select tPerecheD
locate for nvl(tva_incasare_a,0) = 1
if found()
llTvaIncasareD = .T.
endif
select tPerecheC
locate for nvl(tva_incasare_a,0) = 1
if found()
llTvaIncasareC = .T.
endif
llTVAIncasare = m.llTvaIncasareD or m.llTvaIncasareC
CREATE cursor calculcompensare (nr_doc_d N(14), nr_doc_c N(14), id_factd N(14), id_factc N(14),;
ascc c(4), ascd c(4), sumaachi N(20,4), ales l, proc_tva N(5,2), id_jtvacol N(10), proc_tvaD N(5,2), id_jtvacolD N(10), proc_tvaC N(5,2), id_jtvacolC N(10), ;
tva_incasare_a N(1), tva_incasare_d N(1), tva_incasare_dtip C(1),tva_incasare_c N(1), tva_incasare_ctip C(1))
lnRecnoD = 1
lnReccountD = Reccount("tPerecheD")
Select tperecheD
Go Top
lnSumaD = sumaachi
pcCont = Alltrim(Cont)
llDefalcTVAD = .F.
lcSelect = [select count(*) as defalcTVA from config_cont_ireg t where cont = ?pcCont and cu_proc_tva = 1]
If goExecutor.oExecuta(lcSelect, "crsDefalcTva")
Select crsDefalcTVA
llDefalcTVAD = defalcTVA >0
Use In crsDefalcTVA
Endif
lnRecnoC = 1
lnReccountC = Reccount("tPerecheC")
Select tperecheC
Go Top
lnSumaC = sumaachi
pcCont = Alltrim(Cont)
llDefalcTVAC = .F.
lcSelect = [select count(*) as defalcTVA from config_cont_ireg t where cont = ?pcCont and cu_proc_tva = 1]
If goExecutor.oExecuta(lcSelect, "crsDefalcTva")
Select crsDefalcTVA
llDefalcTVAC = defalcTVA >0
Use In crsDefalcTVA
Endif
lnSumaCompensata = 0
lcTipMaxUrm = []
lnSemnSumaD = SIGN(tperecheD.sumaachi)
*!* am ordonat documentele pereche de pe credit si de pe debit descrescator dupa sume
*!* pana ajung la 0 pe ultima inregistrare de pe una dintre tabele, aleg MIN
*!* cand am ajuns la 0 pe ultima inregistrare intr-una dintre tabele, iau doar valorile din tabelul celalalt
*!* modificare v 2.5.27
*!* DO WHILE !(lnRecnoD = lnReccountD AND lnRecnoC = lnReccountC AND lnSumaC = 0 AND lnSumaD = 0)
DO WHILE (EVL(tnTipOprire,0) = 0 AND !(lnRecnoD = lnReccountD AND lnRecnoC = lnReccountC AND lnSumaC = 0 AND lnSumaD = 0)) ;
OR (EVL(tnTipOprire,0) = 1 AND EMPTY(lcTipMaxUrm))
*!* modificare v 2.5.27 ^
lcTipMax = lcTipMaxUrm
lnSumaCompensata = IIF(lcTipMax=[C],lnSumaD,IIF(lcTipMax=[D],lnSumaC,IIF(EVL(tnTipOprire,0)=1,MIN(ABS(lnSumaC),ABS(lnSumaD)),MIN(lnSumaC,lnSumaD))))
lnProc_tva = 0.00
lnIdJtvaColoana = 0
lnProc_tvaD = 0.00
lnIdJtvaColoanaD = 0
lnProc_tvaC = 0.00
lnIdJtvaColoanaC = 0
***-------------------------
SELECT tperecheD
STORE nract TO nract1
id_fact1 = id_fact
lcascd = NVL(acont,'')
IF cu_tva = 1 AND TYPE('tPerecheD.proc_tva') # 'U' AND llDefalcTVAD
lnProc_tva = proc_tva
lnIdJtvaColoana = NVL(id_jtva_coloana,0)
lnProc_tvaD = proc_tva
lnIdJtvaColoanaD = NVL(id_jtva_coloana,0)
ENDIF
*!* cand tnTipOprire = 1, atunci trebuie sa diminuez sumaachi cu lnSumaCompensata
replace sumaachi WITH sumaachi - IIF(EVL(tnTipOprire,0)=1,lnSemnSumaD,1) * lnSumaCompensata
lnSumaD = sumaachi
Do Case
Case lnSumaD = 0 And lnRecNoD <> lnReccountD
Skip
lnSumaD = sumaachi
lnRecNoD = Recno()
Case lnSumaD = 0 And lnRecNoD = lnReccountD And EMPTY(lcTipMax)
*!* daca am ajuns la ultima inregistrare si am restul 0, atunci trebuie sa iau valorile doar din celalalt tabel
*!* pentru urmatoarele note, nu mai fac exigibilizarea de TVA pentru Debit
lcTipMaxUrm = [D]
Endcase
***-------------------------
SELECT tperecheC
STORE nract TO nract2
id_fact2 = id_fact
lcascc = NVL(acont,'')
IF cu_tva = 1 AND TYPE('tPerecheC.proc_tva') # 'U' AND llDefalcTVAC
lnProc_tva = proc_tva
lnIdJtvaColoana = NVL(id_jtva_coloana,0)
lnProc_tvaC = proc_tva
lnIdJtvaColoanaC = NVL(id_jtva_coloana,0)
ENDIF
*!* cand tnTipOprire = 1, atunci trebuie sa diminuez sumaachi cu lnSumaCompensata
replace sumaachi WITH sumaachi - IIF(EVL(tnTipOprire,0)=1,lnSemnSumaD*(-1),1) * lnSumaCompensata
lnSumaC = sumaachi
Do Case
Case lnSumaC = 0 And lnRecNoC <> lnReccountC
Skip
lnSumaC = sumaachi
lnRecNoC = Recno()
Case lnSumaC = 0 And lnRecNoC = lnReccountC And EMPTY(lcTipMax)
*!* daca am ajuns la ultima inregistrare si am restul 0, atunci trebuie sa iau valorile doar din celalalt tabel
*!* pentru urmatoarele note, nu mai fac exigibilizarea de TVA pentru Credit
lcTipMaxUrm = [C]
Endcase
SELECT calculcompensare
IF lnSumaCompensata <> 0
APPEND BLANK
REPLACE nr_doc_c WITH nract2, ascc WITH lcascc, id_factc WITH id_fact2
REPLACE nr_doc_d WITH nract1, ascd WITH lcascd, id_factd WITH id_fact1
REPLACE proc_tva WITH lnProc_tva,id_jtvacol WITH lnIdJtvaColoana
REPLACE proc_tvaD WITH lnProc_tvaD,id_jtvacolD WITH lnIdJtvaColoanaD
REPLACE proc_tvaC WITH lnProc_tvaC,id_jtvacolC WITH lnIdJtvaColoanaC
REPLACE sumaachi WITH IIF(EVL(tnTipOprire,0)=1,lnSemnSumaD,1)*lnSumaCompensata
REPLACE ales WITH .T.
if m.llTvaIncasare
replace tva_incasare_a with 1
endif
if m.llTvaIncasareD && AND lcTipMax<>[D] : pusesem conditia ca sa nu mai genereze note de TVA la incasare cand ajungeam pe 0 la ultima inregistrare din cursor
replace tva_incasare_d with 1, tva_incasare_dtip with 'D'
endif
if m.llTvaIncasareC && AND lcTipMax<>[C] : pusesem conditia ca sa nu mai genereze note de TVA la incasare cand ajungeam pe 0 la ultima inregistrare din cursor
replace tva_incasare_c with 1, tva_incasare_ctip with 'C'
endif
ENDIF
ENDDO
*!* modificare v 2.5.26 ^
USE IN tperecheD
USE IN tperecheC
RETURN
***
******************************************* SFARSIT: calculcompens in lei *******************************************
******************************************* INCEPUT: calculcompens in valuta - fara sume negative *******************************************
***
PROCEDURE calculcompens_valuta
PARAMETERS tcFisD, tcFisC
LOCAL max1, max2, nract1, nract2, lcFis1, lcFis2, id_fact1, id_fact2, llDefalcTVA
STORE 1 TO max1,max2
STORE 0 TO nract1, nract2, id_fact1, id_fact2
PRIVATE pcCont
pcCont = ''
llDefalcTVA = .F.
lcFisD = ALLTRIM(tcFisD)
lcFisC = ALLTRIM(tcFisC)
SELECT * FROM &lcFisD INTO CURSOR tperecheD READWRITE
SELECT * FROM &lcFisC INTO CURSOR tperecheC READWRITE
CREATE CURSOR calculcompensare (nr_doc_d N(14), nr_doc_c N(14),id_factd N(14), id_factc N(14),;
ascc c(4), ascd c(4), sumaachi2 N(20,4), achilei N(20,4), id_jtvacol N(10), proc_tva N(5,2), ;
sumaachid N(20,4), sumaachic N(20,4), cursd N(12,4), cursc N(12,4), difplus N(20,4), ;
difminus N(20,4), cursval N(12,4), cursdif N(12,4), ales l)
DO WHILE max1!=0 AND max2!=0
lnProc_tva = 0.00
lnIdJtvaColoana = 0
SELECT tperecheC
CALCULATE MAX(sumaachi2) FOR ales TO max2
*WAIT WINDOW 'max2'+STR(max2)
SELECT tperecheD
CALCULATE MAX(sumaachi2) FOR ales TO max1
*WAIT WINDOW 'max1'+STR(max1)
***-------------------------
SELECT tperecheD
GO TOP
pcCont = ALLTRIM(CONT)
llDefalcTVA = .F.
lcSelect = [select count(*) as defalcTVA from config_cont_ireg t where cont = ?pcCont and cu_proc_tva = 1]
lnSucces = goExecutor.oExecute(lcSelect, "crsDefalcTva")
IF lnSucces < 0
AMESSAGEBOX(goExecutor.cEroare,0+16,'Eroare')
ELSE
SELECT crsDefalcTVA
llDefalcTVA = defalcTVA >0
IF USED('crsDefalcTVA')
USE IN crsDefalcTVA
ENDIF
ENDIF
***-------------------------
SELECT tperecheD
LOCATE FOR sumaachi2 = max1 AND ales
nract1 = nract
id_fact1 = id_fact
lcascd = NVL(acont,'')
lnCursD = cursschimb
IF TYPE('tPerecheD.proc_tva') # 'U' AND llDefalcTVA
lnProc_tva = proc_tva
lnIdJtvaColoana = NVL(id_jtva_coloana,0)
ENDIF
IF max1 <= max2
lnSumaleiD = sumaachi
REPLACE sumaachi2 WITH 0
REPLACE ales WITH .F.
ELSE
lnSumaleiD = ROUND(cursschimb * max2, gnPC)
IF lnSumaleiD > sumaachi
lnSumaleiD = sumaachi
ENDIF
REPLACE sumaachi2 WITH max1 - max2
REPLACE sumaachi WITH sumaachi - lnSumaleiD
ENDIF
***-------------------------
SELECT tperecheC
GO TOP
pcCont = ALLTRIM(CONT)
llDefalcTVA = .F.
lcSelect = [select count(*) as defalcTVA from config_cont_ireg t where cont = ?pcCont and cu_proc_tva = 1]
lnSucces = goExecutor.oExecute(lcSelect, "crsDefalcTva")
IF lnSucces < 0
AMESSAGEBOX(goExecutor.cEroare,0+16,'Eroare')
ELSE
SELECT crsDefalcTVA
llDefalcTVA = defalcTVA >0
IF USED('crsDefalcTVA')
USE IN crsDefalcTVA
ENDIF
ENDIF
***-------------------------
SELECT tperecheC
LOCATE FOR sumaachi2 = max2 AND ales
nract2 = nract
id_fact2 = id_fact
lcascc = NVL(acont,'')
lnCursC = cursschimb
IF TYPE('tPerecheC.proc_tva') # 'U' AND llDefalcTVA
lnProc_tva = proc_tva
lnIdJtvaColoana = NVL(id_jtva_coloana,0)
ENDIF
IF max2 <= max1
lnSumaleiC = sumaachi
REPLACE sumaachi2 WITH 0
REPLACE ales WITH .F.
ELSE
lnSumaleiC = ROUND(cursschimb * max1, gnPC)
IF lnSumaleiC > sumaachi
lnSumaleiC = sumaachi
ENDIF
REPLACE sumaachi2 WITH max2 - max1
REPLACE sumaachi WITH sumaachi - lnSumaleiC
ENDIF
SELECT calculcompensare
IF MIN(max1, max2) <> 0
APPEND BLANK
REPLACE nr_doc_c WITH nract2, id_factc WITH id_fact2, ascc WITH lcascc, sumaachic WITH lnSumaleiC, cursc WITH lnCursC
REPLACE nr_doc_d WITH nract1, id_factd WITH id_fact1,ascd WITH lcascd, sumaachid WITH lnSumaleiD, cursd WITH lnCursD
REPLACE sumaachi2 WITH MIN(max1, max2),proc_tva WITH lnProc_tva,id_jtvacol WITH lnIdJtvaColoana
REPLACE ales WITH .T.
ENDIF
ENDDO
SELECT calculcompensare
SCAN
IF sumaachid > sumaachic
REPLACE difplus WITH sumaachid - sumaachic, achilei WITH sumaachic, cursval WITH cursc, cursdif WITH cursd
ELSE
REPLACE difminus WITH sumaachic - sumaachid, achilei WITH sumaachid, cursval WITH cursd, cursdif WITH cursc
ENDIF
ENDSCAN
USE IN tperecheD
USE IN tperecheC
RETURN
******************************************* SFARSIT: calculcompens - fara sume negative *******************************************
***
*!* PROCEDURE calculcompens_valuta
*!* PARAMETERS tcFisD, tcFisC
*!* LOCAL max1, max2, nract1, nract2, lcFis1, lcFis2
*!* STORE 1 TO max1,max2
*!* STORE 0 TO nract1,nract2
*!* lcFisD = ALLTRIM(tcFisD)
*!* lcFisC = ALLTRIM(tcFisC)
*!* SELECT * from &lcfisd INTO CURSOR tperecheD READWRITE
*!* SELECT * from &lcfisc INTO CURSOR tperecheC READWRITE
*!* CREATE TABLE &gcTempPath\calculcompensare.dbf (nr_doc_d n(14), nr_doc_c n(14), ascc c(4), ascd c(4), sumaachi2 n(16,2), achilei n(16,2), ;
*!* sumaachid n(16,2), sumaachic n(16,2), cursd n(8,2), cursc n(8,2), difplus n(16,2), difminus n(16,2), cursval n(8,2), cursdif n(8,2), ales l)
*!*
*!* DO WHILE max1!=0 OR max2!=0
*!* SELECT tperecheC
*!* CALCULATE MAX(sumaachi2) FOR ales AND sumaachi2 <> 0 TO max2
*!* *WAIT WINDOW 'max2'+STR(max2)
*!* SELECT tperecheD
*!* CALCULATE MAX(sumaachi2) FOR ales AND sumaachi2 <> 0 TO max1
*!* *WAIT WINDOW 'max1'+STR(max1)
*!* SELECT tperecheD
*!* LOCATE FOR sumaachi2 = max1 AND ales
*!* nract1 = nract
*!* lcascd = acont
*!* lnCursD = cursschimb
*!* if (max1 <= max2 OR max2 = 0) AND max1 <> 0
*!* lnSumaleiD = sumaachi
*!* REPLACE sumaachi2 WITH 0
*!* * REPLACE ales WITH .f.
*!*
*!* ELSE
*!* lnSumaLeiD = ROUND(cursschimb * max2, 0)
*!* IF lnSumaLeiD > sumaachi
*!* lnSumaLeiD = sumaachi
*!* ENDIF
*!* REPLACE sumaachi2 WITH max1 - max2
*!* REPLACE sumaachi WITH sumaachi - lnSumaLeiD
*!* ENDIF
*!*
*!* SELECT tperecheC
*!* LOCATE FOR sumaachi2 = max2 AND ales
*!* nract2 = nract
*!* lcascc = acont
*!* lnCursC = cursschimb
*!*
*!* if (max2 <= max1 OR max1 = 0) AND max2 <> 0
*!* lnSumaleiC = sumaachi
*!* REPLACE sumaachi2 WITH 0
*!* * REPLACE ales WITH .f.
*!*
*!* ELSE
*!* lnSumaLeiC = ROUND(cursschimb * max1, 0)
*!* IF lnSumaLeiC > sumaachi
*!* lnSumaLeiC = sumaachi
*!* ENDIF
*!* REPLACE sumaachi2 WITH max2 - max1
*!* REPLACE sumaachi WITH sumaachi - lnSumaLeiC
*!* ENDIF
*!*
*!*
*!* SELECT calculcompensare
*!* IF max1 <> 0 OR max2 <> 0
*!* APPEND BLANK
*!* REPLACE nr_doc_c WITH nract2, ascc WITH lcAscc, sumaAchiC WITH lnSumaleiC, cursC WITH lnCursC
*!* REPLACE nr_doc_d WITH nract1, ascd WITH lcAscd, sumaAchiD WITH lnSumaleiD, cursD WITH lnCursD
*!* IF MIN(max1, max2) <> 0
*!* REPLACE sumaachi2 WITH MIN(max1, max2)
*!* ELSE
*!* REPLACE sumaachi2 WITH MAX(max1, max2)
*!* ENDIF
*!* REPLACE ales WITH .t.
*!* ENDIF
*!* ENDDO
*!* SELECT calculcompensare
*!* SCAN
*!* IF sumaAchiD > sumaAchiC
*!* REPLACE difplus WITH sumaAchiD - sumaAchiC, achilei WITH sumaAchiC, cursval WITH cursC, cursdif WITH cursD
*!* ELSE
*!* REPLACE difminus WITH sumaAchiC - sumaAchiD, achilei WITH sumaAchiD, cursval WITH cursD, cursdif WITH cursC
*!* ENDIF
*!* ENDSCAN
*!* USE IN tperecheD
*!* USE IN tperecheC
*!* RETURN
*********************************************** SFARSIT calculcompens in valuta ******************************************
Function calculcompens_part
Lparameters tcCont, tnProcTva, tlValuta, tnIdValuta, tnIdValuta2, tcNumeVal2
&& partea in valuta nu este testata
Local llReturn, lnTip, lcCursorIregPart, lcCursorSumeNegative, lcCursorSumePozitive, lcCursorCalculCompensare, ;
lcCursorCalculCompensareTemp, lcCont, lcCond, lnIdPart, ;
lnFelCont, lnPozitie, lcSumaAchi, lcSelectie, lcSelectie1, lcSelectie2
lcCursorIregPart = Sys(2015)
lcCursorSumeNegative = Sys(2015)
lcCursorSumePozitive = Sys(2015)
lcCursorCalculCompensare = [calculcompensare]
lcCursorCalculCompensareTemp = SYS(2015)
lcCont = Alltrim(tcCont)
lcCond = [ an = ] + Alltrim(Str(gnAn)) + [ and luna = ] + Alltrim(Str(gnluna))+ Iif(glEMama,' and NVL(id_sucursala,0) = 0 ',gcCondSucursala)
llReturn = .T.
If tlValuta
lcCond= lcCond + [ and ABS(i.precvaldeb + i.valdebit - i.precvalcred - i.valcredit)>0 and i.id_valuta ] + ;
Iif(!EMPTY(tnIdValuta2), [in ( ] + Alltrim(Str(tnIdValuta)) + [,] + Alltrim(Str(Nvl(tnIdValuta2,0))) + [)], [ = ]+Alltrim(Str(tnIdValuta)))
Else
lcCond= lcCond + [ and (i.precvaldeb + i.valdebit - i.precvalcred - i.valcredit = 0) ]
Endif
Select xcont
Locate For Val(contpart) = Val(lcCont)
lnIdPart = idpart
lnFelCont = fel_cont
lnPozitie = pozitie
pcselect = ['select i.* FROM vireg_parteneri i where 2=2 ']
pcfiltru = [ i.id_part = ] + Alltrim(Str(lnIdPart)) + [ AND ABS(i.precdeb + i.debit - i.preccred - i.credit)<>0 AND i.cont = ']+ lcCont + [' and ] + lcCond
Private podateintrod
Store '' To podateintrod
gencursor('podateintrod',lcCursorIregPart,pcselect,pcfiltru,[''],[''])
Do Case
Case m.lnFelCont = 0 Or (m.lnFelCont = 2 And m.lnPozitie = 1)
lnTip = 1
lcSumaAchi = [ROUND(precdeb + debit,gnPc) - ROUND(preccred+credit,gnPc)]
lcSelectie = [SELECT *, ACONT AS ASCC, nume_val as numeval, curs as cursschimb, precdeb + debit as totctva, preccred+credit as achitat, credit as achitatl, ] +;
[precvaldeb + valdebit AS sumaval, precVALcred+VALcredit as achitatval, ] + Iif(tnProcTva = 1, [1], [0]) + [ as cu_tva, ] +;
[.T. as ales, ] + lcSumaAchi + [ as sumaachi, 000000000000.0000 as sumaachi2, 00000000000000.0000 as achilei, 00000000000000.0000 as difplus, ]+;
[ 00000000000000.0000 as difminus, 000000.0000 as cursval, 000000.0000 as cursdif, id_jtva_coloana as id_jtvacol, ] +;
[CAST(0 as n(10)) as id_valuta2, CAST('' as c(30)) as nume_val2, CAST(0 as n(15,2)) as sumaachi22,tva_incasare ] + ;
[FROM ] + lcCursorIregPart
Otherwise
lnTip = 2
lcSumaAchi = [ROUND(preccred + credit,gnPc) - ROUND(precdeb+debit,gnPc)]
lcSelectie = [SELECT *, ACONT AS ASCD, nume_val as numeval, curs as cursschimb, preccred + credit as totctva, precdeb+debit as achitat, debit as achitatl,] + ;
[precvalcred + valcredit as sumaval, precVALdeb+VALdebit as achitatval, ] + Iif(tnProcTva = 1, [1], [0]) + [ as cu_tva, ] +;
[.T. as ales, ] + lcSumaAchi + [ as sumaachi, 00000000000000.0000 as sumaachi2, 00000000000000.0000 as achilei, 00000000000000.0000 as difplus, ]+;
[ 00000000000000.0000 as difminus, 000000.0000 as cursval, 000000.0000 as cursdif, id_jtva_coloana as id_jtvacol, ] + ;
[CAST(0 as n(10)) as id_valuta2, CAST('' as c(30)) as nume_val2, CAST(0 as n(15,2)) as sumaachi22,tva_incasare ] + ;
[FROM ] + lcCursorIregPart
Endcase
lcSelectie1 = lcSelectie + [ WHERE ] + lcSumaAchi + [< 0 INTO CURSOR ] + lcCursorSumeNegative + [ READWRITE]
lcSelectie2 = lcSelectie + [ WHERE ] + lcSumaAchi + [> 0 INTO CURSOR ] + lcCursorSumePozitive + [ READWRITE]
&lcSelectie1
&lcSelectie2
If !Empty(tcNumeVal2) And !Empty(Nvl(tnIdValuta2,0))
Update (lcCursorSumeNegative) Set id_valuta2 = tnIdValuta2, nume_Val2 = tcNumeVal2
Update (lcCursorSumePozitive) Set id_valuta2 = tnIdValuta2, nume_Val2 = tcNumeVal2
ENDIF
*!* ROUND(sumaval-achitatval,gnPval)
*!* && sumaachi2 = valoarea in valuta facturii (euro)
*!* && sumaachi22 = valoarea in valuta de incasare(usd)
*!* && m.suma_32 = cursul usd
*!* && m.suma_3 = cursul euro
*!* Local lncurs
*!* If ales
*!* lncurs = Iif(id_valuta = id_valuta2, poact.suma_32, poact.suma_3)
*!* * lncurs = Iif(id_valuta = poact.id_valuta2, poact.suma_32, poact.suma_3)
*!* Repl sumaachi2 With Round(sumaval - achitatval,gnPval)
*!* IF poAct.suma_32 > 0
*!* replace sumaachi22 WITH ROUND(sumaachi2 * m.lncurs / poact.suma_32,2)
*!* ENDIF
*!* If sumaachi2=0
*!* Do mesaj With "Bifati doar inregistrarile","care au sold in valuta!"
*!* Replace ales With .F.
*!* Endif
*!* Else
*!* Repl sumaachi2 With 0, sumaachi22 With 0
*!* Endif
If Reccount(lcCursorSumeNegative)>0 And Reccount(lcCursorSumePozitive)>0
Do calculcompens With lcCursorSumeNegative, lcCursorSumePozitive, 1,1
*!* mut toate facturile pe coloana corecta si le cumulez dupa id_fact
copiaza_structura_cursor(lcCursorCalculCompensare,lcCursorCalculCompensareTemp)
INSERT INTO (lcCursorCalculCompensareTemp) SELECT * FROM (lcCursorCalculCompensare)
DELETE from (lcCursorCalculCompensare)
IF lnTip = 1
Insert Into (lcCursorCalculCompensare) (nr_doc_d,nr_doc_c,id_factd,id_factc,ascd,ascc,sumaachi,ales,proc_tva,;
id_jtvacol,tva_incasare_a,tva_incasare_d,tva_incasare_dtip,tva_incasare_c,tva_incasare_ctip) ;
SELECT 0 As nr_doc_d, nr_doc_d As nr_doc_c, 0 As id_factd, id_factd As id_factc, '' As ascd,ascd As ascc,;
Sum(sumaachi) As sumaachi,ales,;
proc_tvaD as proc_tva,id_jtvacol as id_jtvacolC,;
tva_incasare_a,0 As tva_incasare_d,'' As tva_incasare_dtip,tva_incasare_d As tva_incasare_c,;
IIF(tva_incasare_dtip='D','C','') As tva_incasare_ctip ;
From (lcCursorCalculCompensareTemp) ;
Group By 1,2,3,4,5,6,8,9,10,11,12,13,14,15 ;
Union All ;
SELECT 0 As nr_doc_d,nr_doc_c,0 As id_factd,id_factc,'' As ascd,ascc,;
Sum(sumaachi)*(-1) As sumaachi,ales,;
proc_tvaC as proc_tva, id_jtvacolC as id_jtvacol,;
tva_incasare_a,0 As tva_incasare_d,'' As tva_incasare_dtip,tva_incasare_c,tva_incasare_ctip ;
From (lcCursorCalculCompensareTemp) ;
Group By 1,2,3,4,5,6,8,9,10,11,12,13,14,15
ELSE
Insert Into (lcCursorCalculCompensare) (nr_doc_d,nr_doc_c,id_factd,id_factc,ascd,ascc,sumaachi,ales,proc_tva,;
id_jtvacol,tva_incasare_a,tva_incasare_d,tva_incasare_dtip,tva_incasare_c,tva_incasare_ctip) ;
SELECT nr_doc_c As nr_doc_d, 0 As nr_doc_c, id_factc As id_factd, 0 As id_factc, ascc As ascd, '' As ascc,;
Sum(sumaachi)*(-1) As sumaachi,ales,;
proc_tvaC as proc_tva, id_jtvacolC as id_jtvacol,;
tva_incasare_a,tva_incasare_c As tva_incasare_d,;
IIF(tva_incasare_ctip='C','D','') as tva_incasare_dtip,;
0 As tva_incasare_c,'' As tva_incasare_ctip ;
From (lcCursorCalculCompensareTemp) ;
Group By 1,2,3,4,5,6,8,9,10,11,12,13,14,15 ;
Union All ;
SELECT nr_doc_d,0 As nr_doc_c,id_factd,0 As id_factc,ascd,'' As ascc,;
Sum(sumaachi) As sumaachi,ales,;
proc_tvaD as proc_tva, id_jtvacolD as id_jtvacol, ;
tva_incasare_a,tva_incasare_d,tva_incasare_dtip,0 As tva_incasare_c,'' As tva_incasare_ctip ;
From (lcCursorCalculCompensareTemp) ;
Group By 1,2,3,4,5,6,8,9,10,11,12,13,14,15
ENDIF
USE IN (lcCursorCalculCompensareTemp)
Else
llReturn = .F.
amessagebox("Nu se pot face compensari intrucat nu exista facturi cu semn diferit!",48,"Atentie")
Endif
Use In (lcCursorIregPart)
Use In (lcCursorSumeNegative)
Use In (lcCursorSumePozitive)
Release lnTip, lcCursorIregPart, lcCursorSumeNegative, lcCursorSumePozitive, lcCursorCalculCompensare, ;
lcCursorCalculCompensareTemp, lcCont, lcCond, lnIdPart, ;
lnFelCont, lnPozitie, lcSumaAchi, lcSelectie, lcSelectie1, lcSelectie2
RETURN llReturn
Endproc
Reference in New Issue View Git Blame Copy Permalink