726 lines
28 KiB
Plaintext
726 lines
28 KiB
Plaintext
*!* 25.03.2022
|
|
*!* viz_frm_facturi > situatie_facturi se afiseaza si facturile cu total cu tva = 0
|
|
|
|
*_________________________________________________________*
|
|
* *
|
|
* proceduri utilizate pentru generarea rapoartelor din: *
|
|
* Terti->Furnizori *
|
|
* ->Clienti *
|
|
* ->Alti *
|
|
*_________________________________________________________*
|
|
|
|
* PROCEDURE viz_frm_facturi( tntip )
|
|
* Date : 19/11/2004, 14:36:36
|
|
* author : lavinia.viziru
|
|
* description:
|
|
|
|
****** PARAMETER BLOCK **************
|
|
* Parameters : 1
|
|
* Parameter 1:
|
|
*
|
|
******************************************* INCEPUT:viz_frm_facturi *******************************************
|
|
Procedure viz_frm_facturi
|
|
Parameters tntip, tccont, tnId_part, tcNume
|
|
|
|
&& tnId_part, tcNume - din programul de contracte, ca sa intru deja pe partenerul pe care sunt pozitionat in lista de contracte
|
|
|
|
|
|
Private pocauta, pnlunai, pnani, pnlunaf, pnanf, pccont, plemise, pcperioada, pctitlu, pcpartener
|
|
Store '' To pocauta, pcperioada, pctitlu, pcpartener
|
|
Store 0 To pnlunai, pnani, pnlunaf, pnanf
|
|
|
|
plemise = Iif(tntip = 2, .T., .F.)
|
|
plActiv = .T.
|
|
pccont = Iif(!Empty(tccont), tccont, [])
|
|
If !Empty(pccont)
|
|
pcexec = [select NVL(fel_cont,0) as fel_cont from ] + gcs + [.vcoresp_tip_cont where cont=?pcCont]
|
|
pcCursor = [felCont_cursor]
|
|
pnsucces = goExecutor.oExecute(pcexec, pcCursor)
|
|
If pnsucces > 0 And _Tally > 0
|
|
Select felCont_cursor
|
|
Locate
|
|
plActiv = Iif(felCont_cursor.fel_cont = 0, .T., .F.)
|
|
Use In felCont_cursor
|
|
Endif
|
|
Endif
|
|
|
|
gnButon = 1
|
|
Do While gnButon = 1
|
|
ofrm = Createobject('frm_rap_facturi')
|
|
With ofrm
|
|
If !Empty(tnId_part)
|
|
.lnIdPart = tnId_part
|
|
.ncu_contract = 1
|
|
.ckContract.Value = 1
|
|
*.clb_tx_simplu3.text_simplu1.Value = [<TOATE INREGISTRARILE>]
|
|
Endif
|
|
If !Empty(tcNume)
|
|
.clb_tx_simplu2.text_simplu1.Value = tcNume
|
|
Endif
|
|
.lcCont = pccont
|
|
.llActiv = plActiv
|
|
.clb_tx_simplu1.text_simplu1.Value = pccont
|
|
.lb_titlu_alb_b121.Caption = [Facturi ] + Iif(!Empty(pccont), pccont, [])
|
|
.opt_perioada.Value = tntip
|
|
.lhide = .T.
|
|
Endwith
|
|
ofrm.Show(1)
|
|
pccont = ofrm.lcCont
|
|
plemise = Iif(ofrm.opt_perioada.Value = 1, .F., .T.)
|
|
pnIdPart = ofrm.lnIdPart
|
|
plActiv = ofrm.llActiv
|
|
pnani = ofrm.Caut_anluna1.nan
|
|
pnanf = ofrm.Caut_anluna2.nan
|
|
pnlunai = ofrm.Caut_anluna1.nluna
|
|
pnlunaf = ofrm.Caut_anluna2.nluna
|
|
pnCuAnalitic = ofrm.ncu_analitic
|
|
pcAnalitic = ofrm.lcAcont
|
|
pnCuValuta = ofrm.ncu_valuta
|
|
pnIdValuta = ofrm.lnIdValuta
|
|
pnCuVechime = ofrm.ncu_vechime
|
|
pcValuta = ofrm.txt_valuta.Value
|
|
pnCuContract = ofrm.ncu_contract
|
|
pnIdCtr = ofrm.lnIdCtr
|
|
ofrm.Release
|
|
|
|
If gnButon = 1
|
|
pcperioada = [Perioada: ] + Alltrim(Str(pnlunai)) + [/] + Alltrim(Str(pnani)) + ;
|
|
[ - ] + Alltrim(Str(pnlunaf)) + [/] + Alltrim(Str(pnanf))
|
|
|
|
|
|
* lcCursor = situatie_facturi(pccont,plActiv,plemise,pnIdPart,pnani,pnlunai,pnanf,pnlunaf,pnCuAnalitic,pcAnalitic,pnCuValuta,pnIdValuta,pnCuVechime)
|
|
lcCursor = situatie_facturi(Alltrim(pccont), plActiv, plemise, pnIdPart, pnani, pnlunai, pnanf, pnlunaf, pnCuAnalitic, pcAnalitic, pnCuValuta, pnIdValuta, pnCuVechime, pnCuContract, pnIdCtr)
|
|
|
|
*!* Do Case
|
|
*!* Case Empty(lcCursor)
|
|
*!* amessagebox("Nu a fost generata selectia pentru listare!",0+48,"Atentie")
|
|
*!* Case Reccount(lcCursor) = 0
|
|
*!* amessagebox("Nu exista inregistrari pentru listare!",0+48,"Atentie")
|
|
*!* Use In (lcCursor)
|
|
*!* Otherwise
|
|
If Alltrim(pccont) = [401]
|
|
pcpartener = [Furnizor: ]
|
|
lctitlu = [Situatia facturilor de cumparari]
|
|
Else && pccont = [411]
|
|
pcpartener = [Client: ]
|
|
lctitlu = [Situatia facturilor de vanzari]
|
|
Endif
|
|
If pnCuVechime = 0
|
|
lcRaport = [rap_facturi]
|
|
Else
|
|
lcRaport = [rap_facturi_vechime]
|
|
Endif
|
|
|
|
If pnCuAnalitic = 0
|
|
pcGrupAcont = [EOF()]
|
|
Else
|
|
pcGrupAcont = [acont]
|
|
Endif
|
|
If !Empty(pnIdValuta)
|
|
lctitlu = lctitlu + Chr(13) + [Valuta ] + Alltrim(pcValuta)
|
|
Endif
|
|
*!* pctitlu = ceretitlu_rap('Titlul Raportului',lctitlu)
|
|
*!* pcDataOra = Get_Ora(2)
|
|
*!* Select (lcCursor)
|
|
*!* Report Form &lcRaport To Printer Prompt Preview
|
|
pctitlu = lctitlu
|
|
goExport.export2frx(lcCursor, lcRaport, .T., , , , , .T., , )
|
|
|
|
If Messagebox('Doriti sa exportati in XLS?', 4 + 32, _Screen.Caption) = 6
|
|
lcFileXLS = Putfile("Salvati fisierul XLS", "Facturi.xls", "xls")
|
|
If !Empty(lcFileXLS)
|
|
Select (lcCursor)
|
|
Copy To (lcFileXLS) Type Xl5
|
|
open_default_app(lcFileXLS)
|
|
Endif
|
|
Endif
|
|
Use In (lcCursor)
|
|
*!* Endcase
|
|
Endif
|
|
Enddo
|
|
Endproc
|
|
****************************************** SFARSIT: viz_frm_facturi ****************************
|
|
* PROCEDURE situatie_facturi
|
|
* Date : 10/04/2006, 16:10:03
|
|
* author : georgiana.voicu
|
|
* description: preluare facturi pentru conturi cu inregistrari
|
|
|
|
****** PARAMETER BLOCK **************
|
|
* Parameters : 3
|
|
* Parameter 1: tlemise
|
|
* Description: tipul facturilor preluate
|
|
* (.t.-facturi emise intr-o perioada determinata
|
|
* .f.-facturi cu sold dintr-o perioada determinata)
|
|
* Parameter 2: tccont
|
|
* Description: contul partenerului(411-client, 401- furnizor)
|
|
* Parameter 3: topartener
|
|
* Description: obiectul care contine datele partenerului(client/furnizor) selectat:nume + id_part
|
|
* (daca numele partenerului=<TOATE INREGISTRARILE> atunci se iau in considerare
|
|
* toti partenerii- clienti sau furnizori)
|
|
* Parameter 4,5,6,7 : perioada
|
|
******************************************* INCEPUT:situatie_facturi *******************************************
|
|
Procedure situatie_facturi
|
|
Parameters tccont, tlActiv, tlemise, tnIdPart, tnAnI, tnLunaI, tnAnF, tnLunaF, tnCuAnalitic, tcAnalitic, tnCuValuta, tnIdValuta, tnCuVechime, tnCuContract, tnIdCtr
|
|
|
|
Private lnrlunii, lnrlunif, lnNrPrimaL
|
|
|
|
Local lcAchitat, lcAcontIncPl, lcCondAnalitic, lcCondData, lcCondExclud, lcCondId_ctr, lcCondId_part
|
|
Local lcCondPerioada, lcCondPrimaLuna, lcConditieSupl, lcCondvaluta, lcContIncPl, lcCursor
|
|
Local lcCursor1, lcExceptii, lcIdPartener, lcIdPereche, lcOrder, lcPartener, lcPereche, lcSelect
|
|
Local lcSelect1, lcSoldPrec, lcSql, lcTotctva, lcTotctvaPrec, ldDataFactura, ldDataVechime, lnIdFact
|
|
Local lnIdPart, lnNrRand, lnNrZile, lnSoldFact, lnSoldPart, lnSucces, lnSucces1
|
|
PRIVATE lnnrlunii, lnnrlunif
|
|
PRIVATE oComplet, oInc, objFactInc, osold, pcExplicatie
|
|
oComplet = NULL
|
|
oInc = NULL
|
|
objFactInc = NULL
|
|
osold = NULL
|
|
pcExplicatie = ''
|
|
lnnrlunii = tnLunaI + 12 * tnAnI
|
|
lnnrlunif = tnLunaF + 12 * tnAnF
|
|
|
|
*!* IF tlemise
|
|
lnNrPrimaL = lnnrlunii
|
|
*!* ELSE
|
|
*!* lnNrPrimaL = lnnrlunii -1
|
|
*!* ENDIF
|
|
|
|
Local lnId_ctr, lcContract
|
|
Store 0 To lnId_ctr
|
|
Store '' To lcContract
|
|
|
|
If tnIdPart = 0
|
|
lcCondId_part = [1=1]
|
|
Else
|
|
lcCondId_part = [id_part = ] + Alltrim(Str(tnIdPart))
|
|
Endif
|
|
|
|
If tnIdCtr = 0
|
|
lcCondId_ctr = [1=1]
|
|
Else
|
|
lcCondId_ctr = [id_ctr = ] + Alltrim(Str(tnIdCtr))
|
|
Endif
|
|
|
|
lcSql = [select * from ] + gcs + [.exceptii_ireg where invers = 1 and debit = ] + Iif(tlActiv, [1], [0]) + [ and cont = ] + tccont
|
|
lcCursor = [ex_debit]
|
|
lnSucces = goExecutor.oExecute(lcSql, lcCursor)
|
|
lcExceptii = [-1]
|
|
If lnSucces > 0
|
|
Select ex_debit
|
|
Scan
|
|
lcExceptii = lcExceptii + ',' + Alltrim(cont_c)
|
|
Endscan
|
|
Use In ex_debit
|
|
Endif
|
|
|
|
If !tlActiv && - furnizori
|
|
lcTotctva = Iif(tnCuValuta = 0, [credit + preccred], [valcredit + precvalcred])
|
|
*!* modificare v 2.0.46
|
|
lcTotctvaPrec = Iif(tnCuValuta = 0, [preccred], [precvalcred]) && am nevoie pentru calcularea soldului prec.
|
|
*!* modificare v 2.0.46 ^
|
|
lcSoldPrec = Iif(tnCuValuta = 0, [preccred - precdeb], [precvalcred - precvaldeb])
|
|
*lcAchitat = [debit + precdeb]
|
|
lcAchitat = Iif(tnCuValuta = 0, [precdeb], [precvaldeb])
|
|
lcPereche = [pereched]
|
|
lcIdPereche = [id_factd]
|
|
lcPartener = [partd]
|
|
lcIdPartener = [id_partd]
|
|
lcContIncPl = [scd]
|
|
lcAcontIncPl = [ascd]
|
|
lcCondExclud = [(to_number(scc) not in (] + lcExceptii + [))]
|
|
Else && - clienti
|
|
lcTotctva = Iif(tnCuValuta = 0, [debit + precdeb], [valdebit + precvaldeb])
|
|
*!* modificare v 2.0.46
|
|
lcTotctvaPrec = Iif(tnCuValuta = 0, [precdeb], [precvaldeb])
|
|
*!* modificare v 2.0.46 ^
|
|
lcSoldPrec = Iif(tnCuValuta = 0, [precdeb - preccred], [precvaldeb - precvalcred])
|
|
*lcAchitat = [credit + preccred]
|
|
lcAchitat = Iif(tnCuValuta = 0, [preccred], [precvalcred])
|
|
lcPereche = [perechec]
|
|
lcIdPereche = [id_factc]
|
|
lcPartener = [partc]
|
|
lcIdPartener = [id_partc]
|
|
lcContIncPl = [scc]
|
|
lcAcontIncPl = [ascc]
|
|
lcCondExclud = [(to_number(scd) not in (] + lcExceptii + [))]
|
|
Endif
|
|
|
|
lcCondPerioada = [((luna + 12*an) between ?lnNrPrimaL and ?lnnrlunif)]
|
|
lcCondData = [(to_number(to_char(dataireg,'MM'),'99') + 12*to_number(to_char(dataireg,'YYYY'),'9999'))]
|
|
lcCondPrimaLuna = [(luna + 12*an = ?lnNrPrimaL)]
|
|
If tnCuAnalitic = 1
|
|
lcCondAnalitic = Iif(!Empty(tcAnalitic), [acont = ?tcAnalitic], [1=1])
|
|
Else
|
|
lcCondAnalitic = [1=1]
|
|
Endif
|
|
If tnCuValuta = 1
|
|
lcCondvaluta = Iif(!Empty(tnIdValuta), [id_valuta = ?tnIdValuta], [id_valuta<>0])
|
|
Else
|
|
lcCondvaluta = [1=1]
|
|
Endif
|
|
pcExplicatie = [Din Precedent]
|
|
|
|
|
|
*!* lcSelect = [select an, luna, id_fact, id_part, nume, acont, dataact, nract,] + lcTotctva + [ as totctva,?pcExplicatie as fdoc,] + ;
|
|
*!* IIF(tlemise,[0],[(case when ] + lcCondPrimaLuna +[ then ] + lcAchitat + [ else 0 end)]) + [ as achitat] + ;
|
|
*!* [ from ] + gcS + [.vireg_parteneri ] + ;
|
|
*!* [where cont = ?tccont and ] + lcCondId_part + [ and ] + lcCondPerioada + ;
|
|
*!* [ and (luna + 12*an =] + IIF(tlemise,lcCondData,[(case when ] + lcCondPrimaLuna +;
|
|
*!* [ then luna + 12*an else ] + lcCondData + [ end)]) + [)] + ;
|
|
*!* [ order by an, luna, nume, dataact, nract]
|
|
|
|
lcSelect = [select an, luna, id_fact, id_part, nume, cod_fiscal, ] + ;
|
|
Iif(tnCuContract = 1, [id_ctr], [0]) + [ as id_ctr, ] + ;
|
|
Iif(tnCuContract = 1, [contract], ['xxxxxxxxxxxxxxxxxxxx']) + [ as contract, ] + ;
|
|
Iif(tnCuAnalitic = 1, [acont], ['xxxx']) + [ as acont, serie_act, dataact, nract,] + ;
|
|
Iif(tnCuValuta = 1, [id_valuta,nume_val,], []) + ;
|
|
[(case when precdeb <> 0 or preccred <> 0 then 1 else 0 end) as precedent,SUM(] + lcTotctva + [) as totctva, SUM(] + m.lcSoldPrec + [) as soldprec, ?pcExplicatie as fdoc,] + ;
|
|
[SUM(] + Iif(tlemise, [0], [(case when ] + lcCondPrimaLuna + [ then ] + lcAchitat + [ else 0 end)]) + [) as achitat] + ;
|
|
[ from ] + gcs + [.vireg_parteneri ] + ;
|
|
[ where cont = ?tccont and ] + lcCondId_part + [ and ] + lcCondId_ctr + [ and ] + lcCondPerioada + [ and ] + lcCondAnalitic + [ and ] + lcCondvaluta + m.gcCondSucursala + ;
|
|
[ and (luna + 12*an =] + Iif(tlemise, lcCondData, [(case when ] + lcCondPrimaLuna + ;
|
|
[ then luna + 12*an else ] + lcCondData + [ end)]) + [)] + ;
|
|
[ group by an, luna, id_fact, id_part, nume, cod_fiscal, ] + Iif(tnCuContract = 1, [id_ctr, contract,], []) + Iif(tnCuAnalitic = 1, [acont,], []) + [ dataact, serie_act, nract] + Iif(tnCuValuta = 1, [,id_valuta,nume_val], []) + [,(case when precdeb <> 0 or preccred <> 0 then 1 else 0 end)] + ;
|
|
[ order by ] + Iif(tnCuContract = 1, [contract,], []) + [ an, luna, nume, cod_fiscal, ] + Iif(tnCuContract = 1, [contract,], []) + Iif(tnCuValuta = 1, [id_valuta,], []) + Iif(tnCuAnalitic = 1, [acont,], []) + [dataact, serie_act, nract]
|
|
|
|
lcCursor = [cFacturi]
|
|
|
|
If Used(lcCursor)
|
|
Use In (lcCursor)
|
|
Endif
|
|
|
|
lnSucces = goExecutor.oExecute(lcSelect, lcCursor)
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare, 0 + 16, 'Eroare')
|
|
Return ''
|
|
Endif
|
|
|
|
Select (lcCursor)
|
|
Replace All dataact With {} For Isnull(dataact)
|
|
|
|
&& 27.05.2008
|
|
&& soldurile initiale pentru facturile emise in perioada nu se pot obtine din facturile din cFacturi (nu am precdeb/preccred <> 0)
|
|
&& trebuie sa le selectez din ireg_parteneri separat
|
|
If tlemise
|
|
lcSelect = [select an, luna, id_part, cont, acont, Sum(] + lcSoldPrec + [) as soldprec ] + ;
|
|
[ from ireg_parteneri ] + ;
|
|
[ where cont = ?tccont and ] + lcCondId_part + [ and ] + lcCondAnalitic + ;
|
|
[ and ] + lcCondvaluta + gcCondSucursala + ;
|
|
[ and ] + lcCondPrimaLuna + ;
|
|
[ group by an, luna, id_part, cont, acont]
|
|
|
|
lcCursor = [cSoldParteneri]
|
|
|
|
If Used(lcCursor)
|
|
Use In (lcCursor)
|
|
Endif
|
|
|
|
lnSucces = goExecutor.oExecute(lcSelect, lcCursor)
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare, 0 + 16, 'Eroare')
|
|
Return ''
|
|
Endif
|
|
Endif
|
|
&& soldurile initiale pentru facturile emise in perioada ^
|
|
|
|
&& iau in considerare doar incasarile inregistrate in perioada data
|
|
|
|
lcCondPerioada = [((luna + 12*an) between ?lnNrLuniI and ?lnNrLuniF)]
|
|
If tnIdPart = 0
|
|
lcCondId_part = [1=1]
|
|
Else
|
|
lcCondId_part = lcIdPartener + [ = ] + Alltrim(Str(tnIdPart))
|
|
Endif
|
|
If tnCuAnalitic = 1
|
|
lcCondAnalitic = Iif(!Empty(tcAnalitic), lcAcontIncPl + [ = ?tcAnalitic], [1=1])
|
|
Else
|
|
lcCondAnalitic = [1=1]
|
|
Endif
|
|
If tnCuValuta = 1
|
|
lcCondvaluta = Iif(!Empty(tnIdValuta), [id_valuta = ?tnIdValuta and suma_val<>0], [id_valuta<>0 and suma_val<>0])
|
|
Else
|
|
lcCondvaluta = [1=1]
|
|
Endif
|
|
|
|
*!* modificare v 2.0.46
|
|
If tccont = '4111'
|
|
lcConditieSupl = [ OR (SCD = '4111' ] + Iif(!Empty(tcAnalitic), [AND ASCD = ?tcAnalitic ], []) + ;
|
|
[AND SCC = '4427' ] + Iif(tnIdPart = 0, [], [AND ID_PARTD = ] + Alltrim(Str(tnIdPart)) + [ ]) + ;
|
|
[AND (NVL(PERECHED,0) <> 0 OR NVL(ID_FACTD,0)<>0))]
|
|
lcSelect1 = [Select (case when scc = '4427' then id_partd else id_partc end) as id_part,] + ;
|
|
[(case when scc = '4427' then partd else partc end) as nume,fdoc,dataact as datadoc,] + ;
|
|
[serie_act as serie_doc,nract as nrdoc,] + ;
|
|
Iif(tnCuAnalitic = 1, [(case when scc='4427' then ascd else ascc end)], ['xxxx']) + [ as acont,] + ;
|
|
[(case when scc = '4427' then pereched else perechec end) as pereche,] + ;
|
|
[(case when scc = '4427' then id_factd else id_factc end) as id_fact,] + ;
|
|
[(case when scc = '4427' then (-1)*] + Iif(tnCuValuta = 0, [suma else suma], [suma_val else suma_val]) + [ end) as suma,] + ;
|
|
[ id_valuta, luna as luna_inc, an as an_inc ] + ;
|
|
[ from ] + gcs + [.vact ] + ;
|
|
[ where ((] + lcContIncPl + [ = ] + tccont + [ and ] + lcCondId_part + [ and ] + lcCondAnalitic + [ and (] + lcPereche + [ <> 0 or ] + lcIdPereche + [ <>0))] + ;
|
|
lcConditieSupl + [)] + ;
|
|
[ and ] + lcCondExclud + [ and ] + lcCondPerioada + ;
|
|
[ and ] + lcCondvaluta + gcCondSucursala + ;
|
|
[ order by serie_act, nract, dataact]
|
|
Else
|
|
lcSelect1 = [select ] + lcIdPartener + [ as id_part,] + lcPartener + [ as nume,fdoc,dataact as datadoc,serie_act as serie_doc,nract as nrdoc,] + ;
|
|
Iif(tnCuAnalitic = 1, lcAcontIncPl, ['xxxx']) + [ as acont,] + lcPereche + [ as pereche,] + lcIdPereche + [ as id_fact,] + ;
|
|
Iif(tnCuValuta = 0, [suma], [suma_val]) + [ as suma,] + ;
|
|
[ id_valuta, luna as luna_inc, an as an_inc ] + ;
|
|
[ from ] + gcs + [.vact ] + ;
|
|
[ where ] + lcContIncPl + [ = ] + tccont + [ and ] + lcCondId_part + [ and ] + lcCondvaluta + [ and ] + lcCondAnalitic + ;
|
|
[ and ] + lcCondExclud + [ and (] + lcPereche + [ <> 0 or ] + lcIdPereche + [ <>0) and ] + lcCondPerioada + ;
|
|
gcCondSucursala + ;
|
|
[ order by serie_act, nract, dataact]
|
|
Endif
|
|
*!* modificare v 2.0.46 ^
|
|
lcCursor1 = [cIncasari_temp]
|
|
|
|
If Used(lcCursor1)
|
|
Use In (lcCursor1)
|
|
Endif
|
|
|
|
lnSucces1 = goExecutor.oExecute(lcSelect1, lcCursor1)
|
|
If lnSucces1 < 0
|
|
amessagebox(goExecutor.cEroare, 0 + 16, 'Eroare')
|
|
Return ''
|
|
Endif
|
|
|
|
Select cIncasari_temp
|
|
Replace All datadoc With {} For Isnull(datadoc)
|
|
|
|
Select an, luna, id_fact, id_part, nume, cod_fiscal, acont, Nvl(Ttod(dataact), {}) As datafact, Nvl(serie_act, []) As serie_fact, ;
|
|
id_ctr, contract, ;
|
|
nract As nrfact, totctva, ;
|
|
Padr(fdoc, 30, ' ') As fdoc, {//} As datadoc, 00000000000000 As nrdoc, precedent, ;
|
|
achitat As suma, 00000000000000000000.0000 As sold, ;
|
|
00000000000000000000.0000 As soldcumulat, 0 As tip, 00 As luna_inc, 0000 As an_inc, 0 As sold_final, ;
|
|
00000000000000000000.0000 As vechime_1, 00000000000000000000.0000 As vechime_2, 00000000000000000000.0000 As vechime_3, 00000000000000000000.0000 As vechime_4 ;
|
|
From cFacturi ;
|
|
Where totctva - achitat <> 0 OR totctva = 0 ;
|
|
Order By nume, id_part, acont, dataact, serie_act, nract ;
|
|
Into Cursor cFactInc Readwrite
|
|
|
|
Select cIncasari_temp
|
|
Scan
|
|
Scatter Name oInc
|
|
Select cFactInc
|
|
Locate For id_fact = oInc.id_fact And id_part = oInc.id_part And Nvl(acont, [xxxx]) = Nvl(oInc.acont, [xxxx])
|
|
Do Case
|
|
Case !Found() && nu exista factura pentru incasare
|
|
*!* modificare v 2.0.50 ^
|
|
*!* modificare v 2.0.46
|
|
*!* Append blank
|
|
*!* caut incasarea neimperecheata
|
|
If Used('crstempireg')
|
|
Use In crstempireg
|
|
Endif
|
|
lcSql = [select 1 as precedent,id_fact,id_part,serie_act as serie_fact,nract as nrfact,dataact as datafact,] + ;
|
|
[pack_sesiune.suma_ron((-1)*] + ;
|
|
Iif(!tlActiv, Iif(tnCuValuta = 0, [debit], [valdebit]), Iif(tnCuValuta = 0, [credit], [valcredit])) + ;
|
|
[,Nvl(dataact,to_date(an||luna,'YYYYMM'))) as totctvaprec ] + ;
|
|
[from ireg_parteneri ] + ;
|
|
[where id_fact = ] + Alltrim(Str(oInc.id_fact)) + [ and id_part = ] + Alltrim(Str(oInc.id_part)) + [ ] + ;
|
|
[and ] + Iif(tnCuValuta = 0, [precdeb=0 and preccred=0], [precvaldeb=0 and precvalcred=0]) + [ and ] + ;
|
|
Iif(!tlActiv, Iif(tnCuValuta = 0, [credit], [valcredit]), Iif(tnCuValuta = 0, [debit], [valdebit])) + [=0]
|
|
lnSucces = goExecutor.oExecute(lcSql, [crstempireg])
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare, 16, "Eroare")
|
|
Return
|
|
Else
|
|
If Reccount('crstempireg') > 0
|
|
Select crstempireg
|
|
Scatter Name oComplet
|
|
AddProperty(oComplet, "totctva", oComplet.totctvaprec)
|
|
Use In crstempireg
|
|
Select cFacturi
|
|
Locate For id_fact = oComplet.id_fact && verificare daca mai exista deja
|
|
If !Found()
|
|
Append Blank
|
|
Gather Name oComplet Fields id_part, totctva, totctvaprec, precedent
|
|
Select cFactInc
|
|
Append Blank
|
|
Gather Name oComplet
|
|
Else
|
|
Select cFactInc
|
|
Append Blank
|
|
Endif
|
|
Else
|
|
Select cFactInc
|
|
Append Blank
|
|
Endif
|
|
Endif
|
|
*!* modificare v 2.0.50
|
|
Case Found() And ((tip = 1) Or (tip = 0 And suma <> 0)) && daca mai exista deja o incasare pe factura sau are achitat din precedent
|
|
Scatter Name objFactInc
|
|
lnId_ctr = id_ctr
|
|
lcContract = contract
|
|
Append Blank
|
|
Gather Name objFactInc
|
|
Replace totctva With 0
|
|
Case Found() And tip = 0 && daca nu mai exista incasare pe factura
|
|
&& completez dupa case
|
|
lnId_ctr = id_ctr
|
|
lcContract = contract
|
|
Otherwise && adaug incasarea
|
|
Append Blank
|
|
Endcase
|
|
*!* Endif
|
|
*!* modificare v 2.0.50 ^
|
|
*!* modificare v 2.0.46 ^
|
|
Gather Name oInc
|
|
Replace id_ctr With lnId_ctr, contract With lcContract && las contractul de pe factura (f. probabil sa nu-l am in precedent pe incasare )
|
|
Replace tip With 1
|
|
Select cIncasari_temp
|
|
Endscan
|
|
|
|
* calculez soldurile (initial, cumulat, pe factura)
|
|
* ordonez datele pentru grupare
|
|
*!* SELECT id_part, SUM(totctva-achitat) as soldi_part FROM cFacturi WHERE an*12 + luna = lnNrPrimaL INTO CURSOR cSoldiPart GROUP BY id_part
|
|
*!* SELECT id_part,acont, SUM(totctva-achitat) as soldi_ana FROM cFacturi WHERE an*12 + luna = lnNrPrimaL INTO CURSOR cSoldiAna GROUP BY id_part, acont
|
|
|
|
&& 27.05.2008
|
|
&& soldurile initiale pentru facturile emise in perioada nu se pot obtine din facturile din cFacturi (nu am precdeb/preccred <> 0)
|
|
If tlemise
|
|
Select id_part, Sum(soldprec) As soldi_part From cSoldParteneri Into Cursor cSoldiPart Group By id_part
|
|
Select id_part, Nvl(acont, Space(4)) As acont, Sum(soldprec) As soldi_ana From cSoldParteneri Into Cursor cSoldiAna Group By id_part, acont Readwrite
|
|
Else
|
|
*!* modificare v 2.0.46
|
|
*!* Select id_part, Sum(totctva-achitat) As soldi_part From cFacturi Where precedent = 1 Into Cursor cSoldiPart Group By id_part
|
|
*!* Select id_part,Nvl(acont,Space(4)) As acont, Sum(totctva-achitat) As soldi_ana From cFacturi Where precedent = 1 Into Cursor cSoldiAna Group By id_part, acont Readwrite
|
|
Select id_part, Sum(soldprec) As soldi_part From cFacturi Where precedent = 1 Into Cursor cSoldiPart Group By id_part
|
|
Select id_part, Nvl(acont, Space(4)) As acont, Sum(soldprec) As soldi_ana From cFacturi Where precedent = 1 Into Cursor cSoldiAna Group By id_part, acont Readwrite
|
|
*!* modificare v 2.0.46 ^
|
|
Endif
|
|
&& 27.05.2008 ^
|
|
|
|
If tnCuContract = 1
|
|
lcOrder = [c.nume, c.id_part, c.id_ctr, c.acont, c.datafact, c.serie_fact, c.nrfact, c.id_fact]
|
|
Else
|
|
lcOrder = [c.nume, c.id_part, c.acont, c.datafact, c.serie_fact, c.nrfact, c.id_fact]
|
|
Endif
|
|
|
|
* SELECT * FROM cFactInc INTO TABLE c:\factinc.dbf
|
|
|
|
Select c.*, p.soldi_part, a.soldi_ana ;
|
|
From cFactInc c ;
|
|
Left Join cSoldiPart p On c.id_part = p.id_part ;
|
|
Left Join cSoldiAna a On c.id_part = a.id_part And Nvl(c.acont, '') = Nvl(a.acont, '') ;
|
|
Into Cursor cFactIncO ;
|
|
Order By &lcOrder Readwrite
|
|
|
|
If Used('cSoldiPart')
|
|
Use In cSoldiPart
|
|
Endif
|
|
If Used('cSoldiAna')
|
|
Use In cSoldiAna
|
|
Endif
|
|
* soldul cumulat
|
|
**** pt vechime (temporar) aflu ultima zi a perioadei , consider data de referinta
|
|
ldDataVechime = Gomonth(Date(tnAnF, tnLunaF, 1), 1) - 1
|
|
****
|
|
|
|
Select cFactIncO
|
|
Locate
|
|
lnIdFact = id_fact
|
|
lnIdPart = id_part
|
|
lnSoldFact = Nvl(totctva, 0)
|
|
lnSoldPart = Nvl(soldi_part, 0)
|
|
*ldDataFactura = NVL(TTOD(datafact),{})
|
|
ldDataFactura = Nvl(datafact, {})
|
|
Scan
|
|
Scatter Name osold
|
|
lnNrRand = Recno()
|
|
If lnIdFact <> osold.id_fact
|
|
Select cFactIncO
|
|
If tnCuVechime = 1
|
|
Go lnNrRand - 1
|
|
Replace sold_final With 1
|
|
lnNrZile = ldDataVechime - ldDataFactura
|
|
If Empty(ldDataFactura)
|
|
lnNrZile = 400 && peste 1 An
|
|
Else
|
|
lnNrZile = ldDataVechime - ldDataFactura
|
|
Endif
|
|
Do Case
|
|
Case Between(lnNrZile, 0, 30)
|
|
Replace vechime_1 With sold
|
|
Case Between(lnNrZile, 31, 90)
|
|
Replace vechime_2 With sold
|
|
Case Between(lnNrZile, 91, 365)
|
|
Replace vechime_3 With sold
|
|
Otherwise
|
|
Replace vechime_4 With sold
|
|
Endcase
|
|
|
|
Select cFactIncO
|
|
Go lnNrRand
|
|
Endif
|
|
*ldDataFactura = NVL(TTOD(datafact),{})
|
|
ldDataFactura = Nvl(datafact, {})
|
|
lnIdFact = osold.id_fact
|
|
lnSoldFact = Nvl(osold.totctva, 0)
|
|
Endif
|
|
If lnIdPart <> osold.id_part
|
|
lnIdPart = osold.id_part
|
|
lnSoldPart = Nvl(osold.soldi_part, 0)
|
|
Endif
|
|
lnSoldFact = lnSoldFact - Nvl(osold.suma, 0)
|
|
* lnSoldPart = lnSoldPart + IIF(osold.an*12+osold.luna >=lnnrlunii, NVL(osold.totctva,0), 0) - IIF(osold.an_inc*12+osold.luna_inc >=lnnrlunii, NVL(osold.suma,0), 0)
|
|
lnSoldPart = lnSoldPart + Iif(osold.precedent = 0, Nvl(osold.totctva, 0), 0) - Iif(osold.an_inc * 12 + osold.luna_inc >= lnnrlunii, Nvl(osold.suma, 0), 0)
|
|
Replace sold With lnSoldFact, soldcumulat With lnSoldPart
|
|
Select cFactIncO
|
|
Endscan
|
|
|
|
If tnCuVechime = 1
|
|
Select cFactIncO
|
|
Go Bottom
|
|
Replace sold_final With 1
|
|
*ldDataFactura = NVL(TTOD(datafact),{})
|
|
ldDataFactura = Nvl(datafact, {})
|
|
If Empty(ldDataFactura)
|
|
lnNrZile = 400 && peste 1 An
|
|
Else
|
|
lnNrZile = ldDataVechime - ldDataFactura
|
|
Endif
|
|
Do Case
|
|
Case Between(lnNrZile, 0, 30)
|
|
Replace vechime_1 With sold
|
|
Case Between(lnNrZile, 31, 90)
|
|
Replace vechime_2 With sold
|
|
Case Between(lnNrZile, 91, 365)
|
|
Replace vechime_3 With sold
|
|
Otherwise
|
|
Replace vechime_4 With sold
|
|
Endcase
|
|
Endif
|
|
|
|
*!* SELECT cFactIncO
|
|
*!* DELETE from cFactIncO WHERE id_fact IN (select id_fact from cFactIncO where sold_final = 1 AND NVL(vechime_1,0) = 0 AND NVL(vechime_2,0) = 0 AND NVL(vechime_3,0) = 0 AND NVL(vechime_4,0) = 0)
|
|
|
|
If Used('cFacturi')
|
|
Use In cFacturi
|
|
Endif
|
|
If Used('cIncasari_temp')
|
|
Use In cIncasari_temp
|
|
Endif
|
|
If Used('cFactInc')
|
|
Use In cFactInc
|
|
Endif
|
|
|
|
Return "cFactIncO"
|
|
Endproc
|
|
******************************************* SFARSIT: situatie_facturi *******************************************
|
|
|
|
Procedure Alege_Cont
|
|
Lparameters tccont, tleActiv
|
|
|
|
Private polista, pcschema1, pcselect1, pcfiltru1, pcorder1
|
|
Store "" To polista
|
|
pcschema1 = ['cont c(4),acont c(4),explicatie c(50)']
|
|
pcselect1 = ['select distinct cont,rpad(CHR(32),4,CHR(32)) as acont,explicatie from ] + gcs + [.vcoresp_tip_cont where 1=2']
|
|
pcfiltru1 = [2=2]
|
|
pcorder1 = [cont]
|
|
llAfisare = .F.
|
|
gencursor('polista', 'clista', pcselect1, pcfiltru1, pcschema1, pcorder1, llAfisare)
|
|
polista.ca_baza1.afisare()
|
|
|
|
|
|
Select clista
|
|
loCont = myscatter('blank')
|
|
|
|
Private eActiv
|
|
Store .T. To eActiv
|
|
|
|
Ol = Createobject("frm_cere_cont")
|
|
With Ol
|
|
*.titlufrumos1.CAPTION= 'Selectati contul'
|
|
If Empty(.cboCont.RowSource)
|
|
.cboCont.RowSource = "clista.cont,acont"
|
|
Endif
|
|
.ocont = loCont
|
|
If Empty(.cAlias)
|
|
.cAlias = Left(.cboCont.RowSource, At(".", .cboCont.RowSource) - 1)
|
|
Endif
|
|
.Height = 190
|
|
Endwith
|
|
Ol.Show(1)
|
|
|
|
If buton = 2
|
|
Use In clista
|
|
Return
|
|
Endif
|
|
|
|
tccont = loCont.Cont
|
|
tleActiv = eActiv
|
|
Endproc
|
|
|
|
********************************************************************************************
|
|
********************************* INCEPUT:incasari_facturi_lunac ****************************
|
|
Procedure incasari_facturi_lunac
|
|
Parameters tlemitere
|
|
|
|
Private pctitlu, pddata_incasi, pddata_incasf && pcDataOra,
|
|
Store '' To pctitlu && ,pcDataOra
|
|
|
|
pddata_incasi = Ctod('01/' + Alltrim(Str(gnLuna)) + '/' + Alltrim(Str(gnAn)))
|
|
If Month(Date()) = gnLuna And Year(Date()) = gnAn
|
|
pddata_incasf = Date()
|
|
Else
|
|
pddata_incasf = Gomonth(pddata_incasi, 1) - 1
|
|
Endif
|
|
|
|
Do Form frm_filtru_numar Name ofrm_filtru Linked With [a.dataact], [Data incasarii], 1, 1, 'D', [], 0 To lcFiltruData Noshow
|
|
*ofrm_filtru.text1.ResetToDefault([lostfocus])
|
|
*ofrm_filtru.text1.lostfocus()
|
|
ofrm_filtru.text2.SetFocus()
|
|
ofrm_filtru.text1.ControlSource = [pddata_incasi]
|
|
ofrm_filtru.text2.ControlSource = [pddata_incasf]
|
|
ofrm_filtru.Show(1)
|
|
|
|
pcperioada = [Perioada: ] + Alltrim(Dtoc(pddata_incasi)) + [-] + Alltrim(Dtoc(pddata_incasf))
|
|
|
|
If tlemitere
|
|
lcOrdonare = [data_fact,data_incas]
|
|
Else
|
|
lcOrdonare = [data_incas,data_fact]
|
|
Endif
|
|
|
|
lcSelect = [select a.dataact as data_incas,a.nract as nr_doc, a.serie_act, f.fel_document as fdoc, a.suma as incasat,] + ;
|
|
[a.scd as tip_incas, d.denumire as nume, c.nract as nr_fact, e.nrord, c.dataact as data_fact, ] + ;
|
|
[ c.datascad, c.debit + c.precdeb as total_fact] + Iif(glEMama, [,g.firma as sucursala], []) + [ ] + ;
|
|
[from ] + gcs + [.act a join ] + gcs + [.ireg_parteneri c on ] + ;
|
|
[a.perechec = c.nract and a.id_partc = c.id_part and NVL(a.id_sucursala,-99) = NVL(c.id_sucursala,-99) ] + ;
|
|
[left join ] + gcs + [.nom_parteneri d on c.id_part = d.id_part ] + ;
|
|
[left join ] + gcs + [.vnom_lucrari e on c.id_lucrare = e.id_lucrare ] + ;
|
|
[left join ] + gcs + [.nom_fdoc f on a.id_fdoc = f.id_fdoc ] + ;
|
|
Iif(glEMama, [left join syn_nom_firme g on a.id_sucursala = g.id_firma ], []) + ;
|
|
[where a.sters = 0 and SUBSTR(a.scc,1,4) = '4111' and SUBSTR(a.scd,1,1) = '5' and c.cont = '4111'] + ;
|
|
[ and a.an = ] + Alltrim(Str(gnAn)) + [ and a.luna = ] + Alltrim(Str(gnLuna)) + ;
|
|
[ and c.an = ] + Alltrim(Str(gnAn)) + [ and c.luna = ] + Alltrim(Str(gnLuna)) + ;
|
|
Strtran(gcCondSucursala, [id_sucursala], [a.id_sucursala]) + ;
|
|
[ ] + lcFiltruData + [ order by ] + lcOrdonare
|
|
lcCursor = [cIncasari]
|
|
|
|
lnSucces = goExecutor.oExecute(lcSelect, lcCursor)
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare, 0 + 16, 'Eroare')
|
|
Return
|
|
Endif
|
|
|
|
*!* If Reccount(lcCursor)>0
|
|
pctitlu = [Situatia incasarilor ]
|
|
goExport.export2frx(lcCursor, [rap_incasari_facturi], .T.)
|
|
*!* pctitlu = ceretitlu_rap('Titlul Raportului',lctitlu)
|
|
*!* pcDataOra= Get_Ora(2)
|
|
*!* Select (lcCursor)
|
|
*!* Report Form rap_incasari_facturi To Printer Prompt Preview
|
|
*!* Else
|
|
*!* amessagebox("Nu exista inregistrari pentru listare!",48,"Atentie")
|
|
*!* Endif
|
|
|
|
Use In (lcCursor)
|
|
Release pctitlu, pddata_incasi, pddata_incasf
|
|
Endproc
|
|
********************************* SFARSIT:incasari_facturi_lunac ****************************
|
|
|