252 lines
8.3 KiB
Plaintext
252 lines
8.3 KiB
Plaintext
*!* 04.03.2011
|
|
*!* marius.mutu
|
|
*!* alege_raport - arat doar rapoartele pe care are drepturi utilizatorul curent
|
|
|
|
Procedure alege_raport
|
|
Lparameters tnIdFormularRaport, tlDrepturi
|
|
|
|
Private pnIdRaport
|
|
Local lcSql, lnSucces, loFrmAlegeRaport,loFrmRaport,lcForm
|
|
|
|
pnIdRaport = 0
|
|
|
|
|
|
lcForm = [frm_date_rapoarte_rulaje]
|
|
Do Case
|
|
Case tnIdFormularRaport = 1 && rapoarte bazate pe rulaje
|
|
lcForm = [frm_date_rapoarte_rulaje]
|
|
Case tnIdFormularRaport = 2 && rapoarte dinamice sql
|
|
lcForm = [frm_date_rapoarte_sql]
|
|
Case tnIdFormularRaport = 3 && rapoarte bazate pe balanta de parteneri
|
|
lcForm = [frm_date_rapoarte_balpart]
|
|
Otherwise
|
|
amessagebox("Nu exista configurari pentru acest tip de raport!",16,_screen.Caption)
|
|
Return
|
|
Endcase
|
|
|
|
If Used('crsrapoarte')
|
|
Use In crsrapoarte
|
|
Endif
|
|
|
|
IF tlDrepturi
|
|
lcSql = [select r.id_raport,Substr(denumire,1,254) as denumire,titlu from vrapoarte r join rapoarte_utilizatori ru on r.id_raport = ru.id_raport ] + ;
|
|
[where r.id_formular_raport = ] + Alltrim(Str(m.tnIdFormularRaport)) + [ and ru.id_utilizator = ] + ALLTRIM(STR(m.gnIdUtil)) + [ order by denumire]
|
|
ELSE
|
|
lcSql = [select r.id_raport,Substr(denumire,1,254) as denumire,titlu from vrapoarte r ] + ;
|
|
[where r.id_formular_raport = ] + Alltrim(Str(tnIdFormularRaport)) + [ order by denumire]
|
|
ENDIF
|
|
lnSucces = goExecutor.oExecute(lcSql,[crsrapoarte])
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare,16,"Eroare")
|
|
Return
|
|
ELSE
|
|
lcMenu = [Raport nou;\-]
|
|
SELECT crsRapoarte
|
|
SCAN
|
|
lcMenu = lcMenu + [;] + ALLTRIM(denumire)
|
|
ENDSCAN
|
|
lnMenu = xmenu(lcMenu)
|
|
*!* If Reccount('crsrapoarte') > 0
|
|
*!* loFrmAlegeRaport = Createobject('frm_optiuni_rapoarte')
|
|
*!* loFrmAlegeRaport.Show(1)
|
|
*!* Else
|
|
*!* gnButon = 1
|
|
*!* Endif
|
|
pnIdRaport = 0
|
|
IF lnMenu > 0
|
|
IF lnMenu = 1 && raport nou
|
|
pnIdRaport = 0
|
|
ELSE
|
|
SELECT crsRapoarte
|
|
IF RECCOUNT()>= lnMenu-2
|
|
GOTO lnMenu - 2
|
|
pnIdRaport = id_raport
|
|
ENDIF
|
|
ENDIF
|
|
|
|
|
|
loFrmRaport = Createobject(lcForm, m.pnIdRaport, m.tlDrepturi)
|
|
loFrmRaport.Show(1)
|
|
|
|
ENDIF && lnMenu > 0
|
|
If Used('crsrapoarte')
|
|
Use In crsrapoarte
|
|
Endif
|
|
ENDIF && lnSucces < 0
|
|
|
|
|
|
|
|
Release loFrmAlegeRaport,loFrmRaport
|
|
Endproc
|
|
|
|
*===========================================================
|
|
* lanseaza formularul de editare raport cu un raport incarcat (fara sa aleg raportul)
|
|
* folosesc daca stiu ID-ul raportului (ex. rapoarte sql predefinite)
|
|
*===========================================================
|
|
PROCEDURE executa_raport
|
|
LPARAMETERS tnIdFormularRaport, tnIdRaport
|
|
|
|
llDrepturi = .T.
|
|
lcForm = []
|
|
Do Case
|
|
Case tnIdFormularRaport = 1 && rapoarte bazate pe rulaje
|
|
lcForm = [frm_date_rapoarte_rulaje]
|
|
Case tnIdFormularRaport = 2 && rapoarte dinamice sql
|
|
lcForm = [frm_date_rapoarte_sql]
|
|
Case tnIdFormularRaport = 3 && rapoarte bazate pe balanta de parteneri
|
|
lcForm = [frm_date_rapoarte_balpart]
|
|
ENDCASE
|
|
IF EMPTY(m.lcForm)
|
|
amessagebox("Nu exista configurari pentru acest tip de raport!",16,_screen.Caption)
|
|
Return
|
|
ENDIF
|
|
|
|
loFrmRaport = Createobject(m.lcForm, m.tnIdRaport, m.llDrepturi)
|
|
loFrmRaport.Show(1)
|
|
ENDPROC
|
|
|
|
*===========================================================
|
|
* creez cursorul cu parametri din tcSql, il completez cu definitiile salvate in rapoarte.csql_parametri
|
|
* salvez definitia parametrilor (titlu, tip ...) in rapoarte
|
|
* salvez filtrul cu parametri in rapoarte_filtre (titlu,camp,semn,valoare)
|
|
*===========================================================
|
|
Procedure parametri_rapoarte_sql
|
|
Parameters tcSql, tnIdRaport
|
|
|
|
Private pcParametriXML, pcFiltru
|
|
|
|
Local lcParametriXML, lnSucces, lcText, lcValue, lnAtPos1, lnAtPos2, lcSelect
|
|
Local lcParametri, lcParametru, loFrmAlegeRaport, lcTitlu, lcCamp, lcTipCamp
|
|
Local lcPozitieSemn, lcValoare
|
|
|
|
pcParametriXML = ""
|
|
pcFiltru = ""
|
|
|
|
lcSelect = Select()
|
|
lcText = tcSql
|
|
lcParametri = ""
|
|
|
|
|
|
lnSucces = goExecutor.oSelect2Value([select csql_parametri from rapoarte where id_raport = ?tnIdRaport], @lcParametriXML)
|
|
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare,16,"Eroare")
|
|
Return
|
|
Endif
|
|
|
|
Create Cursor crsParametriTemp(parametru C(250))
|
|
|
|
Create Cursor crsParametriXML(parametru C(250), titlu C(100), CAMP M, TIPCAMP C(10), CAMPID C(100), CAMPCAUTARE C(100))
|
|
If !Empty(Nvl(m.lcParametriXML,''))
|
|
Xmltocursor(m.lcParametriXML, "crsParametriXMLTemp")
|
|
Select crsParametriXML
|
|
Append From Dbf("crsParametriXMLTemp")
|
|
Use In (Select("crsParametriXMLTemp"))
|
|
Endif
|
|
|
|
*** Lista de parametri din sql dupa separatori <%=...%>
|
|
*** ex: select * from act where an = <%=tnAn%> and luna = <%=tnLuna%>
|
|
|
|
Do While Atcc('<%=', lcText) > 0
|
|
* Get the start and end position of the next expression
|
|
lnAtPos1 = Atcc('<%=', lcText)
|
|
lnAtPos2 = Atcc('%>', lcText)
|
|
|
|
* Extract the next expression
|
|
lcParametru = Substr(m.lcText, lnAtPos1 + 3, lnAtPos2 - lnAtPos1 - 3)
|
|
* Remove any cariage returns from the expression as they could be inserted by an HTML editor
|
|
lcParametru = Upper(Alltrim(Chrtran(m.lcParametru, Chr(13) + Chr(10), '')))
|
|
* Adaug o singura aparitie a unui parametru (sa nu ii cer de mai multe ori data curenta, daca in sql este nevoie de ea de mai multe ori)
|
|
SELECT crsParametriTemp
|
|
LOCATE FOR parametru = lcParametru
|
|
IF !FOUND()
|
|
Insert Into crsParametriTemp (parametru) Values (m.lcParametru)
|
|
ENDIF
|
|
* Evaluate it
|
|
*!* lcValue = TRANSFORM(EVALUATE(lcEval))
|
|
lcValue = 'x'
|
|
* Stuff the result back instead of the original expression
|
|
lcText = Stuffc(lcText, lnAtPos1, lnAtPos2 - lnAtPos1 + 2, lcValue)
|
|
Enddo
|
|
***
|
|
|
|
*!* completare parametri cu definitia salvata in baza de date
|
|
Select p.parametru, x.titlu, p.parametru as CAMP, x.TIPCAMP, x.CAMPID, x.CAMPCAUTARE ;
|
|
FROM crsParametriTemp p Left Join crsParametriXML x On p.parametru = x.parametru ;
|
|
INTO Cursor crsParametri Readwrite
|
|
Use In (Select('crsParametriTemp'))
|
|
Use In (Select('crsParametriXML'))
|
|
Select crsParametri
|
|
|
|
loFrmAlegeRaport = Createobject('frm_optiuni_rapoarte_sql')
|
|
loFrmAlegeRaport.Show(1)
|
|
|
|
If gnButon = 1
|
|
Cursortoxml("crsParametri", "pcParametriXML",1,0+2,0,"")
|
|
lnSucces = goExecutor.oExecute("update rapoarte set csql_parametri = ?pcParametriXML where id_raport = ?tnIdRaport")
|
|
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare,16,"Eroare")
|
|
ELSE
|
|
*** salvez filtrul in rapoarte_filtre
|
|
pcFiltru = ""
|
|
Select crsParametri
|
|
Scan
|
|
lcTitlu = Alltrim(titlu)
|
|
lcCamp = Alltrim(parametru)
|
|
lcTipCamp = Upper(Alltrim(TIPCAMP))
|
|
lcCamp = Iif(lcTipCamp = 'D1', 'AN*12+LUNA', lcCamp)
|
|
lcPozitieSemn = Iif(lcTipCamp = 'N', '1', Iif(lcTipCamp = 'C', '2', Iif(lcTipCamp = 'D', '1', Iif(lcTipCamp = 'D1', '1', '1'))))
|
|
lcValoare = Iif(lcTipCamp = 'N', '0', Iif(lcTipCamp = 'C', '', Iif(lcTipCamp = 'D', '{//}', Iif(lcTipCamp = 'D1', Padl(Alltrim(Str(gnLuna)),2,[0]) + [/] + Alltrim(Str(gnAn)), '0'))))
|
|
|
|
pcFiltru = pcFiltru + lcTitlu + [;] + lcCamp + [;] + lcPozitieSemn + [;;] + lcValoare + [|]
|
|
*!* IESIRI;CANTE;2;;0.000|SI|TIP RULAJ;NONE;0;0;INTRARE/IESIRE REALA|SI|PERIOADA;AN*12+LUNA;1;;LUNA_CURENTA|
|
|
lnSucces = goExecutor.oExecute("begin pack_rapoarte.salveaza_filtru(?tnIdRaport, ?pcFiltru); end;")
|
|
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare,16,"Eroare")
|
|
ENDIF
|
|
Endscan
|
|
|
|
|
|
Endif
|
|
Endif
|
|
|
|
|
|
Use In (Select('crsParametri'))
|
|
Select (lcSelect)
|
|
RETURN gnButon
|
|
|
|
Endproc
|
|
|
|
|
|
*===========================================================
|
|
* creeaza cursorul crsParametriXML cu criteriile de selectie (an, luna etc.)
|
|
*===========================================================
|
|
Procedure criterii_rapoarte_sql
|
|
Parameters tnIdRaport
|
|
|
|
Local lcParametriXML, lnSucces, lcSelect
|
|
|
|
|
|
lcSelect = Select()
|
|
|
|
|
|
Create Cursor crsParametriXML(parametru C(250), titlu C(100), CAMP M, TIPCAMP C(10), CAMPID C(100), CAMPCAUTARE C(100) )
|
|
|
|
lnSucces = goExecutor.oSelect2Value([select csql_parametri from rapoarte where id_raport = ?tnIdRaport], @lcParametriXML)
|
|
|
|
If lnSucces < 0
|
|
amessagebox(goExecutor.cEroare,16,"Eroare")
|
|
Else
|
|
If !Empty(Nvl(m.lcParametriXML,''))
|
|
Xmltocursor(m.lcParametriXML, "crsParametriXMLTemp")
|
|
Select crsParametriXML
|
|
Append From Dbf("crsParametriXMLTemp")
|
|
Use In (Select("crsParametriXMLTemp"))
|
|
Endif
|
|
Endif
|
|
|
|
Select (lcSelect)
|
|
Endproc && criterii_rapoarte_sql
|