*_____________________________________________________*
*                                                     *
*  proceduri utilizate pentru generarea rapoartelor:  *
*  Trezorerie->Casa                                   *
*            ->Banca                                  *
*            ->Altele                                 *
*_____________________________________________________*

* PROCEDURE listare_reg_casabanca
* Date : 04/11/2004, 11:42:49
* author : lavinia.viziru
* description: 

****** PARAMETER BLOCK **************
* Parameters : 0
* 
********************************* INCEPUT:listare_reg_casabanca *****************************
Procedure listare_reg_casabanca
	Parameters tcCont   && 5311 - casa, 5121 - banca

	Local lcTitlu, lcCont, loCasa, lnId_part, lcNume
	Store '' To lcTitlu, lcCont, loCasa
	Store 0 To lnId_part

	Private  pnsoldi
	Store 0 To pnsoldi

	lcCont  = Alltrim(tcCont)

	lcTitlu = Iif(lcCont = '5311', [Alegeti numele casei], [Alegeti numele bancii])
	loCasa = caut_parteneri(lcCont, lcTitlu)
	&& rezultatele cautarii: loCasa.nume, loCasa.id_part,loCasa.cod_fiscal
	If Empty(loCasa.denumire)
		Return
	Endif

	lnId_part = loCasa.id_part
	lcNume = loCasa.denumire

	Private pcexec, pcselect, pcschema, pcfiltru, pcorder, pobancasa, pnprecdeb, pnpreccred
	Store 0 To pnprecdeb, pnpreccred
	Store "" To pobancasa

	*pcexec=[begin cbancasaproc(']+ lcCont +[',]+Alltrim(Str(gnluna+gnan*12))+[); end;]
	pcexec = [begin pack_sesiune.setcont('] + lcCont + ['); end;]
	lnSucces = goExecutor.oExecute(pcexec)
	If lnSucces < 0
		aMESSAGEBOX(goExecutor.cEroare, 0 + 16, 'Eroare')
	Endif

	If Used('casban')
		Use In casban
	Endif
	*!*	modificare	v 2.0.117 : am adaugat id_fdoc,id_nume
	pcschema = ['nrcrt n(1),fdoc c(30),dataact d,dataireg d,nract N(20),serie_act c(15),bancasa c(50),nume c(50), cod_fiscal c(50),explicatia c(100),'+] + ;
		['incasari n(20,4),plati n(20,4),scd c(4),scc c(4),ascd c(4),ascc c(4),COD N(20),NRORD C(30),nresp c(50),sucursala c(100),id_fdoc n(20),id_nume N(20)']
	pcselect = ['select nrcrt, fdoc, dataact, dataireg, nract,serie_act, bancasa, nume, cod_fiscal, explicatia, incasari,] + ;
		[plati, scd, scc, ascd, ascc,COD, NRORD, nresp,sucursala,id_fdoc,id_nume from ] + gcS + [.vbancasa_] + Alltrim(tcCont) + [ where  2=2 ']


	pcfiltru = [UPPER(TRIM(bancasa)) = '] + Upper(Alltrim(lcNume)) + ['] + gcCondSucursala
	pcorder = Iif(lcCont = '5311', [dataireg,dataact,cod], [dataact,cod])
	llAfisare = .F.
	gencursor('pobancasa', 'casban', pcselect, pcfiltru, pcschema, pcorder, llAfisare)
	pobancasa.ca_baza1.afisare()

	&&preluare ora de pe oracle server:
	*!*	STORE 0 to pnsucces
	*!*	pcexec = [select to_char(SYSdate,'dd/mm/yyyy hh:mi:ss AM') as dataora from dual]
	*!*	pcCursor = [dataora_cursor]
	*!*	pnsucces = goExecutor.oExecute(pcexec,pcCursor)
	*!*	IF (pnsucces != 0)
	*!*		SELECT dataora_cursor
	*!*		pcdataora = dataora_cursor.dataora
	*!*		USE IN dataora_cursor
	*!*	ENDIF

	Select casban
	Calculate Sum(incasari), Sum(plati) For nrcrt = 1 To  pnprecdeb, pnpreccred
	pnsoldi = pnprecdeb - pnpreccred
	*!*	modificare	v 2.0.117
	*!*	Select * from casban where nrcrt = 2 into cursor casban_ordonat READWRITE
	If raport_regcasa_restrans(1, lcCont = [5311], [casban], [casban_ordonat])
		*!*	modificare	v 2.0.117	^

		*!* 29.05.2008
		*!* daca nu am rulaje in luna vreau sa listez soldul initial = soldul final fara inregistrari
		*!* si trebuie sa pun o linie goala in tabel pt listare
		If Reccount('casban_ordonat') = 0
			Select bancasa From casban Where nrcrt = 1 Into Cursor cSolduriTemp
			Select casban_ordonat
			Append From Dbf('cSolduriTemp')
			Use In (Select('cSolduriTemp'))
		Endif
		*!* 29.05.2008 ^

		lcRaport = Iif(lcCont = '5311', [registru_casa_rpt1], [registru_banca_rpt1])
		goExport.export2frx([casban_ordonat], lcRaport)
		*!*	modificare	v 2.0.117
	Endif
	*!*	modificare	v 2.0.117	^
	Use In (Select('casban'))
	Use In (Select('casban_ordonat'))

Endproc
********************************* SFARSIT:listare_reg_casabanca *****************************
********************************* INCEPUT: listare_reg_casacum *****************************
Procedure listare_reg_casacum
	Local lcTitlu, lcCont, loCasa, lnId_part, lcNume
	Store '' To lcTitlu, lcCont, loCasa
	Store 0 To lnId_part

	Private  pnsoldi, pdDataI, pdDataF
	Store 0 To pnsoldi
	Store {} To pdDataI, pdDataF
	***	alegere perioada
	ofrm = Createobject("frm_perioada_zzllaaaa")
	ofrm.Show(1)
	If gnButon = 2 Or Isnull(pdDataI) Or Isnull(pdDataF)
		Return
	Endif

	***	cautare partener
	lcCont  = [5311]
	lcTitlu = [Registrul de Casa]
	llToateIreg = .F.
	lcXMLParteneri = caut_parteneri_xml(lcCont, lcTitlu, llToateIreg)
	If !Empty(lcXMLParteneri) And gnButon = 1
		Xmltocursor(lcXMLParteneri, "crsCasaTemp")
		If Reccount('crsCasaTemp') = 0
			aMESSAGEBOX('Nu ati selectat vreo casa!', 0 + 48, 'Atentie')
			Return
		Endif
		lcSirIdPart = cursor2lista("crsCasaTemp", "id_part", ",")
		lcSirNume = cursor2lista("crsCasaTemp", "denumire", ",")
		Use In crsCasaTemp
	Else
		aMESSAGEBOX("Nu ati selectat vreo casa!", 0 + 48, "Atentie")
		Return
	Endif

	Private pcexec, pcselect, pcschema, pcfiltru, pcorder, pobancasa, pnprecdeb, pnpreccred, pcSubtitlu
	pcSubtitlu = [Casa :] + Alltrim(lcSirNume) + [; Data inreg. intre ] + Dtoc(pdDataI) + [ si ] + Dtoc(pdDataF)
	Store 0 To pnprecdeb, pnpreccred
	Store "" To pobancasa
	pcexec = [begin pack_sesiune.setcont('] + lcCont + ['); end;]
	lnSucces = goExecutor.oExecute(pcexec)
	If lnSucces < 0
		aMESSAGEBOX(goExecutor.cEroare, 0 + 16, 'Eroare')
	Endif

	If Used('casban')
		Use In casban
	Endif

	lcSql = [select sum(incasari) as precdeb,sum(plati) as preccred from ] + gcS + [.vbancasa_] + Alltrim(lcCont) + ;
		[ where (nrcrt = 1 or NVL(dataireg,to_date('01011900','DDMMYYYY')) < ?pdDataI)] + gcCondSucursala
	lnSucces = goExecutor.oExecute(lcSql, [casban])
	If lnSucces < 0
		aMESSAGEBOX(goExecutor.cEroare, 16, 'Eroare')
		Return
	Endif
	Select casban
	pnprecdeb = precdeb
	pnpreccred = preccred
	pnsoldi = pnprecdeb - pnpreccred
	Use In (Select('casban'))

	*!*	modificare	v 2.0.117 : am adaugat id_fdoc, id_nume
	pcschema = ['nrcrt n(1),fdoc c(30),dataact d,dataireg d,nract N(20),serie_act c(15),bancasa c(50),nume c(50), cod_fiscal c(50),explicatia c(100),'+] + ;
		['incasari n(20,4),plati n(20,4),scd c(4),scc c(4),ascd c(4),ascc c(4),COD N(20),NRORD C(30),nresp c(50),sucursala c(100),id_fdoc N(20),id_nume N(20)']
	pcselect = ['select nrcrt, fdoc, dataact, dataireg, nract,serie_act, bancasa, nume, cod_fiscal, explicatia, incasari,] + ;
		[plati, scd, scc, ascd, ascc,COD, NRORD, nresp,sucursala,id_fdoc,id_nume from ] + gcS + [.vbancasa_] + Alltrim(lcCont) + [ where  2=2 ']
	pcfiltru = [id_bancasa in (] + Alltrim(lcSirIdPart) + [) and ] + ;
		[(NVL(dataireg,to_date('01011900','DDMMYYYY')) between ?pdDataI and ?pdDataF or dataireg is null) ] + gcCondSucursala
	pcorder = [dataireg,bancasa,dataact,cod]
	llAfisare = .F.
	gencursor('pobancasa', 'casban', pcselect, pcfiltru, pcschema, pcorder, llAfisare)
	pobancasa.ca_baza1.afisare()

	*!*	modificare	v 2.0.117
	*!*	Select * from casban where nrcrt = 2 into cursor casban_ordonat READWRITE
	If raport_regcasa_restrans(2, .T., [casban], [casban_ordonat])
		*!*	modificare	v 2.0.117	^

		If Reccount('casban_ordonat') = 0
			Select bancasa From casban Where nrcrt = 1 Into Cursor cSolduriTemp
			Select casban_ordonat
			Append From Dbf('cSolduriTemp')
			Use In (Select('cSolduriTemp'))
		Endif

		lcRaport = [registru_casa_rpt3]
		goExport.export2frx([casban_ordonat], lcRaport)
		*!*	modificare	v 2.0.117
	Endif
	*!*	modificare	v 2.0.117	^
	Use In (Select('casban'))
	Use In (Select('casban_ordonat'))

Endproc
********************************* SFARSIT: listare_reg_casacum ****************************
* PROCEDURE listare_reg_casabanca_val
* Date : 05/11/2004, 13:03:15
* author : lavinia.viziru
* description: 

****** PARAMETER BLOCK **************
* Parameters : 0
* 
***************************** INCEPUT:listare_reg_casabanca_val *****************************
Procedure listare_reg_casabanca_val
	Parameters tcCont, tlBancasaCum 
	* tcCont: 5314 - casa in valuta, 5124 - banca in valuta
	* tlBancasaCum: registru cumulat pe documente pereche / defalcat pe documente pereche
	
	Local lcTitlu, lcCont, loCasa, lnId_part, lcNume, llBanCasaCum
	Store '' To lcTitlu, lcCont, loCasa
	Store 0 To lnId_part

	Private  pnsoldi, pnsoldival, pcTiltlu, pcBancasa, pcTitlugrup, pcCampgrup, pnprecdebval, pnpreccredval
	Store 0 To pnsoldi, pnsoldival, pnprecdebval, pnpreccredval
	Store '' To pcTiltlu, pcBancasa, pcTitlugrup, pcCampgrup

	lcCont  = Alltrim(tcCont)
	lcTitlu = Iif(lcCont = '5314', [Alegeti numele casei], [Alegeti numele bancii])
	llBanCasaCum = IIF(PCOUNT() = 1, .T., m.tlBancasaCum)
	
	loCasa = caut_parteneri(lcCont, lcTitlu)
	&& rezultatele cautarii: loCasa.nume, loCasa.id_part,loCasa.cod_fiscal
	If Empty(loCasa.denumire)
		Return
	Endif

	lnId_part = loCasa.id_part
	lcNume = loCasa.denumire

	Private pcexec, pcselect, pcschema, pcfiltru, pcorder, pobancasaval, pnprecdeb, pnpreccred
	Store 0 To pnprecdeb, pnpreccred
	Store "" To pobancasaval

	*pcexec=[begin cbancasaproc('] + lcCont + [',]+Alltrim(Str(gnluna+gnan*12))+[); end;]
	pcexec = [begin pack_sesiune.setcont('] + lcCont + ['); end;]
	lnSucces = goExecutor.oExecute(pcexec)
	If lnSucces < 0
		aMESSAGEBOX(goExecutor.cEroare, 0 + 16, 'Eroare')
	Endif

	Use In (SELECT('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), 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)]), [])

	lcSelect = [select nrcrt,fdoc,dataact,dataireg,nract,serie_act,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], []) + ;
		[ from vbancasa_] + Alltrim(m.tcCont) + IIF(m.llBancasaCum,[_CUM],[])

	lcFiltru = [UPPER(TRIM(bancasa)) = '] + Upper(Alltrim(lcNume)) + ['] + gcCondSucursala
	lcOrder = Iif(lcCont = '5314', [dataireg,dataact,cod], [dataact,cod])
	llAfisare = .F.
	lcGroup = ""
	llModParam = .T.
	lcFiltruOriginal = ""
	gencursor('pobancasaval','casban', lcSelect, lcFiltru, lcSchema, lcOrder, llAfisare, lcGroup, llModParam, lcFiltruOriginal)
	pobancasaval.ca_baza1.afisare()

	Calculate Sum(incasari), Sum(incasval), Sum(plati), Sum(platival) For nrcrt = 1 To ;
		pnprecdeb, pnprecdebval, pnpreccred, pnpreccredval
	pnsoldi = pnprecdeb - pnpreccred
	pnsoldival = pnprecdebval - pnpreccredval
	Select * From casban Where nrcrt = 2 Into Cursor casban_ordonat Readwrite

	*!* 29.05.2008
	*!* daca nu am rulaje in luna vreau sa listez soldul initial = soldul final fara inregistrari 
	*!* si trebuie sa pun o linie goala in tabel pt listare
	If Reccount('casban_ordonat') = 0
		Select bancasa From casban Where nrcrt = 1 Into Cursor cSolduriTemp
		Select casban_ordonat
		Append From Dbf('cSolduriTemp')
		Use In (Select('cSolduriTemp'))
	Endif
	*!* 29.05.2008 ^

	pcTitlu = Iif(lcCont = '5314', 'REGISTRUL DE CASA IN VALUTA', 'REGISTRUL DE BANCA IN VALUTA')
	pcBancasa = Iif(lcCont = '5314', 'Casa: ', 'Banca: ')
	pcTitlugrup = Iif(lcCont = '5314', [Data inregistrarii: ], [Data actului: ])
	pcCampgrup = Iif(lcCont = '5314', [dataireg], [dataact])

	goExport.export2frx([casban_ordonat], [registru_casabanca_valuta_rpt1])

	Use In (Select('casban'))
	Use In (Select('casban_ordonat'))

Endproc
***************************** SFARSIT: listare_registru_casaval *****************************


* PROCEDURE jurnal_plati_incasari
* Date : 10/11/2004, 09:16:01
* author : lavinia.viziru
* description: 

****** PARAMETER BLOCK **************
* Parameters : 1
* Parameter 1: tlresp
*
****************************** INCEPUT:jurnal_plati_incasari ********************************
Procedure jurnal_plati_incasari
	Parameters tlresp, tlplati

	Private pdData1, pdData2, pdinit1, pdinit2
	Store {} To pdData1, pdData2, pdinit1, pdinit2
	Private pocauta
	Store "" To pocauta

	pdinit1 = Ctod('01/' + Alltrim(Str(gnLuna)) + '/' + Alltrim(Str(gnAn)))
	pdinit2 = Gomonth(pdinit1, 1) - 1

	pdData1 = pdinit1
	pdData2 = pdinit2

	ofrmjur = Createobject("frm_jurnal_plati_incasari") && in orapoarte_cont.vcx
	ofrmjur.txtdata1.ControlSource = "pdData1"
	ofrmjur.txtdata2.ControlSource = "pdData2"
	ofrmjur.opt_afis.Value = Iif(tlplati, 1, 2)
	*ofrmjur.opt_recap.enabled = IIF(tlplati,.f.,.t.)
	ofrmjur.opt_recap.Value = Iif(tlresp, 2, 1)
	ofrmjur.Show(1)

Endproc
****************************** SFARSIT: jurnal_plati_incasari *******************************



Procedure listare_reg_incasariplati
	Lparameters llExcel

	Local lcCondAct, lcConturi, lnCont
	Local lcCuvCurent, lcFile, lcPerioada, lcSql, lncount, loPerioada
	PRIVATE pcDataOra, pcTitlu, pcSubtitlu
	pcdataora = get_ora(2)
	lcCondAct = ".T."
	
	&& citesc din optiuni_firma lista de conturi care nu intra in selectie
	lcConturi = ALLTRIM(Upper(Nvl(citeste_optiune('conturi_regincaspl'), '')))
	IF EMPTY(m.lcConturi)
		lcConturi = '455,591,581'
	ENDIF 
	If Right(lcConturi, 1) = ','
		lcConturi = Left(lcConturi, Len(lcConturi) - 1)
	Endif
	If Left(lcConturi, 1) = ','
		lcConturi = Substr(lcConturi, 2)
	Endif

	&& confirm lista conturi care nu intra in selectie
	lcConturi = Inputbox("Conturi", "Conturi care nu intra in selectie", lcConturi)

	&& salvez lista de conturi care nu intra in selectie
	If !Empty(lcConturi)
		scrie_optiune('conturi_regincaspl', m.lcConturi, 'Conturi Reg. Incasari Plati PFA')
	Endif
	lcCondAct = ""
	If !Empty(lcConturi)
		lncount = Getwordcount(lcConturi, ",")
		For lnCont = 1 To lncount
			lcCuvCurent = Getwordnum(lcConturi, m.lnCont, ",")
			If At(".", lcCuvCurent) > 0

				lcCondAct = lcCondAct + " and NOT(TRIM(scd)+'.'+TRIM(ascd) = '" + lcCuvCurent + "'" + ;
					" or TRIM(scc)+'.'+ TRIM(ascc) = '" + TRIM(lcCuvCurent) + "')"
			Else
				lcCondAct = lcCondAct + " and NOT(TRIM(scd) = '" + lcCuvCurent + "'" + ;
					" or TRIM(scc) = '" + lcCuvCurent + "')"
			Endif
		Endfor
	Endif

	loPerioada = AlegePerioada() && oproceduri_comune.prg
	If loPerioada.an1 * 12 + loPerioada.luna1 = loPerioada.an2 * 12 + loPerioada.luna2
		lcPerioada = [ and an = ] + Alltrim(Str(loPerioada.an1)) + [ and luna = ] + Alltrim(Str(loPerioada.luna1))
	Else
		lcPerioada = [ and an*12+luna between ] + Alltrim(Str(loPerioada.an1 * 12 + loPerioada.luna1)) + [ and ] + Alltrim(Str(loPerioada.an2 * 12 + loPerioada.luna2))
	Endif

	Text To lcSql Textmerge Noshow
	Select an, luna, cod, Max(FDOC) As FDOC,nract,dataact,dataireg, Max(EXPLICATIA) As EXPLICATIA, Max(nrord) As nrord, Max(dst_chlt) As dst_chlt,
	    SUM(decode(scd, '5311', suma, '5314', suma, 0.00)) As incasaric,
	    SUM(decode(scc , '5311', suma, '5314', suma, 0.00)) As platic,
	    SUM(decode(scd,'5121', suma, '5124', suma, 0.00)) As incasarib,
	    SUM(decode(scc,'5121', suma, '5124', suma, 0.00)) As platib
	    FROM vact
		WHERE (scd In('5311','5121','5124') Or scc In('5311','5121','5124')) And id_set <> 77777 <<m.lcPerioada>> <<m.lcCondAct>>
		GROUP By an,luna,cod,nract,dataact,dataireg
		ORDER By an, luna, dataact
	Endtext
	
	llSucces = goExecutor.oExecuta(m.lcSql, 'crsRegCasaIncasPl')

	IF m.llSucces
		IF RECCOUNT('crsRegCasaIncasPl') = 0
			AMESSAGEBOX('Nu exista inregistrari in perioada aleasa!',0+64,_screen.Caption)
		ELSE 	
			If m.llExcel
				lcFile = Addbs(gcTempPath) + "TempTrezorerie.Xls"
				Copy To (lcFile) Type Xl5
				OPEN_DEFAULT_APP(lcFile)
			Else
				pcTitlu = 'REGISTRU JURNAL DE INCASARI SI PLATI'
				pcSubtitlu = loPerioada.perioada
				listareuserreport("crsRegCasaIncasPl", "FRX", "rap_reg_incaspl")
			ENDIF
		ENDIF 
	ENDIF 
	Use In (Select('crsRegCasaIncasPl'))

Endproc && listare_reg_incasariplati