330 lines
13 KiB
Plaintext
330 lines
13 KiB
Plaintext
*!* ocasaban.prg
|
|
*!* 27.04.2009
|
|
*!* marius.mutu
|
|
*!* am mutat viz_bancasa din roacont\programe\ocont2003.prg in comun\programe\ pentru folosire in ROACASA
|
|
|
|
*!* 27.08.2018
|
|
*!* marius.mutu
|
|
*!* viz_bancasa - test_casa si pentru 5314 reg. casa in valuta (trebuia atentionare pentru sold zilnic negativ
|
|
|
|
*********************************************************************************************
|
|
* PROCEDURE viz_bancasa(tcCont)
|
|
* Date : 29/11/2005, 12:00:20
|
|
* author : marius.mutu
|
|
* description:
|
|
******************************************* INCEPUT:viz_bancasa *******************************************
|
|
Procedure viz_bancasa
|
|
Lparameters tcCont, tlArataInregOperator
|
|
|
|
*!* adaugat 08.05.2009
|
|
*!* alex.lepadatu
|
|
*!* tlArataInregOperator - daca = .t. atunci se arata doar inregistrarile operatorului
|
|
*!* 08.05.2009 ^^
|
|
|
|
Local lccSelect, lnPrecDeb, lnPrecCred, lnPrecDebval, lnPrecCredval, lnSold
|
|
Local lcCursor, lcSqlCount, llAfisareTot, llSucces, lnInregistrariAfisare, llValuta
|
|
Local lcExec, lcSelect, lcSchema, lcFiltru, lcOrder, lnSucces
|
|
Local lcClasa, lcTitlu, lcck_bancasa_caption, lcck_bancasa_titlu, lcTip
|
|
LOCAL oreg
|
|
PRIVATE pnRecCnt, pobancasa
|
|
Store 0 To lnPrecDeb, lnPrecCred, lnPrecDebval, lnPrecCredval
|
|
Store "" To pobancasa
|
|
lccSelect = SELECT()
|
|
|
|
If Empty(tcCont) Or !Inlist(tcCont, '5311', '5121', '5314', '5124')
|
|
lcCont = '5311'
|
|
lcClasa = "frm_casaban"
|
|
lcTitlu = "REGISTRUL DE CASA"
|
|
lcck_bancasa_caption = "Casa"
|
|
lcck_bancasa_titlu = "Alegeti casa"
|
|
lcTip = "Casa"
|
|
Else
|
|
Local lcCont
|
|
lcCont = Alltrim(tcCont)
|
|
Endif
|
|
llValuta = Inlist(m.lcCont, '5314', '5124')
|
|
|
|
*** SETARE CONTEXT BANCASA PENTRU VBANCASA SI VBANCASA_VAL
|
|
|
|
*lcExec = [begin cbancasaproc('] + lcCont + [', ]+ALLTRIM(STR(gnluna+gnan*12))+[); end;]
|
|
lcExec = [begin pack_sesiune.setcont('] + lcCont + ['); end;]
|
|
|
|
lnSucces = goExecutor.oExecute(lcExec)
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare, 0 + 16, 'Eroare')
|
|
Return
|
|
Endif
|
|
|
|
|
|
*** VERIFICARI CU BALANTA DE VERIFICARE DUPA CONT
|
|
Wait Window 'VERIFICARE SOLDURI...' Nowait
|
|
Do inainte With "REGCASA", lcCont In oinainte_de.prg
|
|
|
|
*** VERIFICARE SOLDURI CASA LEI
|
|
If Inlist(lcCont, '5311', '5314')
|
|
Wait Window 'TEST CASA...' Nowait
|
|
Do test_casa With lcCont In oinainte_de.prg
|
|
Endif
|
|
|
|
* Calculez numarul de inregistrari. Daca numarul este mai mic de min(300, gnInregistrariAfisare) afisez toate inregistrarile in loc sa intru cu 1=2
|
|
lnInregistrariAfisare = Iif(Type('gnInregistrariAfisare') = 'N', m.gnInregistrariAfisare, 300)
|
|
|
|
lcSqlCount = [SELECT COUNT(*) as nr FROM balanta_parteneri where an=?gnAn and luna=?gnLuna and CONT in ('] + Alltrim(Strtran(m.lcCont, [,], [','])) + [') ]
|
|
pnRecCnt = 0
|
|
llSucces = goExecutor.oSelecteaza2Value(m.lcSqlCount, @pnRecCnt)
|
|
llAfisareTot = (Nvl(m.pnRecCnt, 0) <= m.lnInregistrariAfisare)
|
|
|
|
*** CALCULEZ PRECEDENTE DIN BALANTA DE PARTENERI PENTRU SOLDURI INITIALE
|
|
lcSelect = [select SUM(precdeb) as precdeb, SUM(preccred) as preccred, ] + ;
|
|
[SUM(precvaldeb) as precvaldeb, SUM(precvalcred) as precvalcred ] + ;
|
|
[from ] + gcs + [.balanta_parteneri where cont = '] + lcCont + [' ] + ;
|
|
[and an = ?gnAn and luna = ?gnLuna ] + gcCondSucursala
|
|
|
|
lcCursor = [crsSold]
|
|
lnSucces = goExecutor.oExecute(lcSelect, lcCursor)
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare, 0 + 16, 'Eroare')
|
|
Else
|
|
Select crsSold
|
|
Go Top
|
|
lnPrecDeb = precdeb
|
|
lnPrecCred = preccred
|
|
lnPrecDebval = precvaldeb
|
|
lnPrecCredval = precvalcred
|
|
Use In crsSold
|
|
Endif
|
|
|
|
*!* modificare v 2.5.40
|
|
creeaza_cursor_bancasa(m.lcCont, m.glBancasaCum)
|
|
creeaza_cursor_bancasalista(m.lcCont, .T.)
|
|
|
|
Select casban
|
|
Do Case
|
|
Case lcCont = '5311'
|
|
lcClasa = "frm_casaban"
|
|
lcTitlu = "REGISTRUL DE CASA"
|
|
lcck_bancasa_caption = "Casa"
|
|
lcck_bancasa_titlu = "Alegeti casa"
|
|
lcTip = "Casa"
|
|
|
|
Case lcCont = '5121'
|
|
lcClasa = "frm_casaban"
|
|
lcTitlu = "REGISTRUL DE BANCA"
|
|
lcck_bancasa_caption = "Banca"
|
|
lcck_bancasa_titlu = "Alegeti banca"
|
|
lcTip = "Banca"
|
|
|
|
Case lcCont = '5314'
|
|
lcClasa = "frm_casaban_val"
|
|
lcTitlu = "REGISTRUL DE CASA IN VALUTA"
|
|
lcck_bancasa_caption = "Casa"
|
|
lcck_bancasa_titlu = "Alegeti casa"
|
|
lcTip = "Casa"
|
|
|
|
Case lcCont = '5124'
|
|
lcClasa = "frm_casaban_val"
|
|
lcTitlu = "REGISTRUL DE BANCA IN VALUTA"
|
|
lcck_bancasa_caption = "Banca"
|
|
lcck_bancasa_titlu = "Alegeti banca"
|
|
lcTip = "Banca"
|
|
|
|
Endcase
|
|
Clear Class (lcClasa)
|
|
|
|
*oreg=Createobject(lcClasa,lcCont)
|
|
oreg = Createobject(lcClasa, lcCont, tlArataInregOperator, m.llAfisareTot)
|
|
|
|
With oreg
|
|
.Lb_titlu_alb_b121.Caption = lcTitlu
|
|
.grid1.cbancasa.header1.Caption = lcck_bancasa_caption
|
|
.ck_bancasa.Caption = lcck_bancasa_caption
|
|
.ck_bancasa.titlu = lcck_bancasa_titlu
|
|
.nrcont = lcCont
|
|
.tip = lcTip
|
|
.nprecdebI = lnPrecDeb
|
|
.npreccredI = lnPrecCred
|
|
.nprecdebvalI = lnPrecDebval
|
|
.npreccredvalI = lnPrecCredval
|
|
Endwith
|
|
oreg.Show(1)
|
|
|
|
Use In (SELECT('casban'))
|
|
USE IN (SELECT('cBancasaLista'))
|
|
|
|
Endproc && viz_bancasa
|
|
******************************************* SFARSIT: viz_bancasa *******************************************
|
|
********************************* INCEPUT: raport_regcasa_restrans ****************************
|
|
Function raport_regcasa_restrans
|
|
Lparameters tnTip, tlCasa, tcCursorSursa, tcCursorDest
|
|
Private pnDocAles, pnPartAles
|
|
Store 0 To pnDocAles, pnPartAles
|
|
Local llReturn
|
|
llReturn = .F.
|
|
|
|
Select Distinct 0 As ales, fdoc As fel_document, id_fdoc From (tcCursorSursa) ;
|
|
Where id_fdoc <> 0 ;
|
|
Order By fel_document Into Cursor crsdocumente Readwrite
|
|
|
|
Select Distinct 0 As ales, id_nume As id_part, nume As denumire, cod_fiscal From (tcCursorSursa) ;
|
|
Where Nvl(id_nume, 0) <> 0 ;
|
|
Order By denumire, cod_fiscal Into Cursor crsparteneri Readwrite
|
|
|
|
If Reccount('crsdocumente') <> 0 And Reccount('crsparteneri') <> 0
|
|
ofrmcriterii = Createobject('frm_casaban_restrans')
|
|
ofrmcriterii.Show(1)
|
|
Else
|
|
gnButon = 1
|
|
Endif
|
|
If gnButon = 1
|
|
llReturn = .T.
|
|
If pnDocAles = 0 And pnPartAles = 0
|
|
If tnTip = 1
|
|
If tlCasa
|
|
Select * From (tcCursorSursa) Where nrcrt = 2 Order By dataireg, dataact, cod Into Cursor (tcCursorDest) Readwrite
|
|
Else
|
|
Select * From (tcCursorSursa) Where nrcrt = 2 Order By dataact, cod Into Cursor (tcCursorDest) Readwrite
|
|
Endif
|
|
Else
|
|
Select * From (tcCursorSursa) Where nrcrt = 2 Order By dataireg, bancasa, dataact, cod Into Cursor (tcCursorDest) Readwrite
|
|
Endif
|
|
Else
|
|
If tnTip = 1
|
|
If tlCasa
|
|
Select dataireg, [] As serie_act, 0 As nract, ;
|
|
nume, explicatia, nrord, Sum(incasari) As incasari, Sum(plati) As plati, ;
|
|
scd, ascd, scc, ascc, dataact, Min(cod) As cod, bancasa From (tcCursorSursa) ;
|
|
Where nrcrt = 2 And id_fdoc In (Select id_fdoc From crsdocumente Where ales = 1 ) And ;
|
|
id_nume In (Select id_part From crsparteneri Where ales = 1 ) ;
|
|
Group By 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 15 ;
|
|
Union All ;
|
|
Select dataireg, serie_act, nract, ;
|
|
nume, explicatia, nrord, incasari, plati, ;
|
|
scd, ascd, scc, ascc, dataact, cod, bancasa From (tcCursorSursa) ;
|
|
Where nrcrt = 2 And Not (id_fdoc In (Select id_fdoc From crsdocumente Where ales = 1 ) And ;
|
|
id_nume In (Select id_part From crsparteneri Where ales = 1)) ;
|
|
Order By 1, 13, 14 Into Cursor (tcCursorDest) Readwrite
|
|
Else
|
|
Select dataireg, [] As serie_act, 0 As nract, ;
|
|
nume, explicatia, nrord, Sum(incasari) As incasari, Sum(plati) As plati, ;
|
|
scd, ascd, scc, ascc, dataact, Min(cod) As cod, bancasa From (tcCursorSursa) ;
|
|
Where nrcrt = 2 And id_fdoc In (Select id_fdoc From crsdocumente Where ales = 1 ) And ;
|
|
id_nume In (Select id_part From crsparteneri Where ales = 1 ) ;
|
|
Group By 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 15 ;
|
|
Union All ;
|
|
Select dataireg, serie_act, nract, ;
|
|
nume, explicatia, nrord, incasari, plati, ;
|
|
scd, ascd, scc, ascc, dataact, cod, bancasa From (tcCursorSursa) ;
|
|
Where nrcrt = 2 And Not (id_fdoc In (Select id_fdoc From crsdocumente Where ales = 1 ) And ;
|
|
id_nume In (Select id_part From crsparteneri Where ales = 1)) ;
|
|
Order By 13, 14 Into Cursor (tcCursorDest) Readwrite
|
|
Endif
|
|
Else
|
|
Select dataireg, Iif(id_fdoc = gnID_FDOC_BONFISCAL, [], serie_act) As serie_act, ;
|
|
Iif(id_fdoc = gnID_FDOC_BONFISCAL, 0, nract) As nract, ;
|
|
nume, explicatia, nrord, Sum(incasari) As incasari, Sum(plati) As plati, ;
|
|
scd, ascd, scc, ascc, dataact, Min(cod) As cod, bancasa From (tcCursorSursa) ;
|
|
Where nrcrt = 2 And id_fdoc In (Select id_fdoc From crsdocumente Where ales = 1 ) And ;
|
|
id_nume In (Select id_part From crsparteneri Where ales = 1 ) ;
|
|
Group By 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 15 ;
|
|
Union All ;
|
|
Select dataireg, serie_act, nract, ;
|
|
nume, explicatia, nrord, incasari, plati, ;
|
|
scd, ascd, scc, ascc, dataact, cod, bancasa From (tcCursorSursa) ;
|
|
Where nrcrt = 2 And Not (id_fdoc In (Select id_fdoc From crsdocumente Where ales = 1 ) And ;
|
|
id_nume In (Select id_part From crsparteneri Where ales = 1)) ;
|
|
Order By 1, 15, 13, 14 Into Cursor (tcCursorDest) Readwrite
|
|
Endif
|
|
Endif
|
|
Endif
|
|
If Used('crsdocumente')
|
|
Use In crsdocumente
|
|
Endif
|
|
|
|
If Used('crsparteneri')
|
|
Use In crsparteneri
|
|
Endif
|
|
|
|
Release ofrmcriterii, pnDocAles, pnPartAles
|
|
Return llReturn
|
|
Endfunc && raport_regcasa_restrans
|
|
********************************* SFARSIT: raport_regcasa_restrans ****************************
|
|
Procedure creeaza_cursor_bancasa
|
|
Lparameters tcCont, tlBancasaCum
|
|
Local lcCursor
|
|
|
|
lcCursor = [casban]
|
|
*** SELECTIE SOLDURI DIN BALANTA PARTENERI SI INREGISTRARI DIN REGISTRUL JURNAL (VBANCASA SI VBANCASA_VAL)
|
|
lcSchema = [nrcrt n(1),fdoc c(30),dataact d,dataireg d,nract N(20),serie_act c(10),bancasa c(50), id_bancasa N(20), ] + ;
|
|
[nume c(50), cod_fiscal c(50), reg_comert c(50), explicatia c(100), ] + ;
|
|
[scd c(4),scc c(4),ascd c(4), ascc c(4),COD N(20),NRORD C(30),NRESP C(30),SUCURSALA C(100),id_nume N(20),id_fdoc N(20),] + ;
|
|
[incasari n(19,gnPa),plati n(19,gnPa)] + Iif(Inlist(m.tcCont, '5314', '5124'), [,incasval n(19,gnPa),platival n(19,gnPa),numeval C(10), ] + Iif(m.tlBancasaCum, [curs C(100)], [curs N(10,4)]), []) + [,soldcum N(19,gnPa)]
|
|
|
|
lcSelect = [select nrcrt,fdoc,dataact,dataireg,nract,serie_act,bancasa,id_bancasa,nume,cod_fiscal,reg_comert,explicatia,] + ;
|
|
[scd,scc,ascd,ascc,COD,NRORD,NRESP,sucursala,id_nume,id_fdoc,] + ;
|
|
[incasari,plati] + Iif(Inlist(m.tcCont, '5314', '5124'), [,incasval,platival,numeval,curs], []) + [,CAST(0 as Number(20,4)) as soldcum] + ;
|
|
[ from vbancasa_] + Alltrim(m.tcCont) + Iif(m.tlBancasaCum, [_CUM], [])
|
|
|
|
lcFiltru = [1 = 2]
|
|
lcOrder = [nrcrt,dataireg,bancasa,cod]
|
|
llAfisare = .F.
|
|
lcGroup = ""
|
|
llModParam = .T.
|
|
lcFiltruOriginal = ""
|
|
If Used(lcCursor)
|
|
Use In (lcCursor)
|
|
Endif
|
|
gencursor('pobancasa', lcCursor, lcSelect, lcFiltru, lcSchema, lcOrder, llAfisare, lcGroup, llModParam, lcFiltruOriginal)
|
|
pobancasa.ca_baza1.afisare()
|
|
Endproc
|
|
|
|
* Cursor lista banci si solduri pentru filtrare in formular frm_casban
|
|
PROCEDURE creeaza_cursor_bancasalista
|
|
LPARAMETERS tcCont, tlEmpty
|
|
LOCAL lcSelect, lcSql, lnIdMax
|
|
Local llSucces, llValuta
|
|
lcSelect = SELECT()
|
|
|
|
* Creez cursorul banci si solduri pentru filtrare
|
|
CREATE CURSOR cBancasaLista (bancasa C(250) null, id_bancasa N(20) null, numeval C(10) null, sold N(20,4) null, ord N(1) null)
|
|
* La prima apelare vreau doar cursorul gol
|
|
IF m.tlEmpty
|
|
SELECT (m.lcSelect)
|
|
RETURN
|
|
ENDIF
|
|
|
|
llValuta = Inlist(m.tcCont, '5314', '5124')
|
|
lcSql = [select bancasa,id_bancasa,SUM(incasari) as incasari,SUM(plati) as plati,CAST(0 as Number(20,4)) as sold] + Iif(m.llValuta, [,SUM(incasval) as incasval,SUM(platival) as platival,numeval], []) + ;
|
|
[ from vbancasa_] + Alltrim(m.tcCont) + ;
|
|
[ group by bancasa,id_bancasa] + Iif(m.llValuta, [,numeval], [])
|
|
|
|
llSucces = goExecutor.oExecuta(m.lcSql, 'cSoldTemp')
|
|
|
|
IF m.llValuta
|
|
select 1 AS ord, PADR('TOATE', 250, ' ') as bancasa, 0 as id_bancasa, 'LEI' as numeval, sum(incasari-plati) as sold from cSoldTemp ;
|
|
UNION ALL ;
|
|
select 2 AS ord, PADR(numeval, 250, ' ') as bancasa, -1 as id_bancasa, numeval, sum(incasval-platival) as sold from cSoldTemp group by numeval ;
|
|
UNION ALL ;
|
|
select 3 AS ord, bancasa, id_bancasa, numeval, sum(incasval-platival) as sold from cSoldTemp group by bancasa, id_bancasa, numeval ;
|
|
order by 1,3 ;
|
|
INTO CURSOR cBancaTemp READWRITE
|
|
ELSE
|
|
select 1 AS ord, PADR('TOATE', 250, ' ') as bancasa, 0 as id_bancasa, 'LEI' as numeval, sum(incasari-plati) as sold from cSoldTemp ;
|
|
UNION ALL ;
|
|
select 3 AS ord, bancasa, id_bancasa, 'LEI' as numeval, sum(incasari-plati) as sold from cSoldTemp group by bancasa, id_bancasa ;
|
|
order by 1,2 ;
|
|
INTO CURSOR cBancaTemp READWRITE
|
|
ENDIF
|
|
|
|
CALCULATE MAX(id_bancasa) TO lnIdMax IN cBancaTemp
|
|
|
|
* creez un id_bancasa unic pentru fiecare valuta
|
|
UPDATE cBancaTemp SET id_bancasa = m.lnIdMax + RECNO() WHERE id_bancasa = -1
|
|
|
|
USE IN (SELECT('cSoldTemp'))
|
|
|
|
SELECT cBancasaLista
|
|
APPEND FROM DBF('cBancaTemp')
|
|
USE IN (SELECT('cBancaTemp'))
|
|
|
|
SELECT (m.lcSelect)
|
|
ENDPROC && creeaza_cursor_bancasalista |