*!* 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