*!* 04.08.2011 *!* marius.mutu *!* PrepareRptSitOp *!* soldul initial se ia din max(luna de initializare, luna de inceput) in loc de luna de inceput care putea sa nu aiba sume *!* #3086 *!* 10.11.2011 *!* marius.mutu *!* PrepareRptSitOp *!* luna de initializare este (precdeb <> 0 sau preccred <> 0) sau (debit <> 0 sau credit <> 0) *!* nu detecta corect luna de initializare pentru clienti cu sume in luna *!* 02.07.2019 *!* marius.mutu *!* PrepareRptSitOp - corectie grupare la !"defalcare dupa documente pereche" ********************************************************************************** PROCEDURE PrepareRptBalantaFC LPARAMETERS tcCont,tcTitlu,tcNumeRaport,tleActiv ** daca nu este precizat contul => va fi cerut STORE "" TO lctitlu,lcNumeRaport,lcCont STORE .F. TO eActiv lcNumeRaport = tcNumeRaport eActiv = tleActiv IF EMPTY(tcTitlu) lctitlu = "Jurnal" ELSE lctitlu = tcTitlu ENDIF IF EMPTY(tcCont) && cere cont, titlu DO Alege_Cont_Titlu WITH lcCont,eActiv,lctitlu ELSE lcCont = tcCont ** cere & propune [doar] titlu raport: DO ceretitlu_raport_v2 WITH 'Titlul Raportului','lcTitlu' ENDIF **preluare ora de pe oracle server: *!* STORE 0 TO pnsucces *!* pcexec = [select to_char(SYSdate,'dd/mm/yyyy hh24:mi:ss') 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 *!* pcDataOra = get_ora(2) If glEMama pcexec = [select sum(precdeb1) as precdeb1,sum(preccred1) as preccred1,] + ; [sum(precdeb) as precdeb,sum(preccred) as preccred,] + ; [sum(debit) as debit,sum(credit) as credit,] + ; [sum(totdeb) as totdeb,sum(totcred) as totcred,] + ; [sum(solddeb) as solddeb,sum(soldcred) as soldcred,] + ; [sum(precvaldeb1) as precvaldeb1,sum(precvalcred1) as precvalcred1,] + ; [sum(precvaldeb) as precvaldeb,sum(precvalcred) as precvalcred,] + ; [sum(valdebit) as valdebit,sum(valcredit) as valcredit,] + ; [sum(totvaldeb) as totvaldeb,sum(totvalcred) as totvalcred,] + ; [sum(soldvaldeb) as soldvaldeb,sum(soldvalcred) as soldvalcred,] + ; [id_part,nume,cod_fiscal,cont,acont,an,luna,id_valuta,] + ; [nume_val,moneda_nationala ] + ; [from ] + gcS + [.vbalanta_parteneri ] + ; [WHERE cont = ] + Alltrim(lcCont) + [ and luna = ] + Alltrim(Str(gnluna)) + ; [ and an = ] + Alltrim(Str(gnan)) + ; [ group by id_part,nume,cod_fiscal,cont,acont,an,luna,] + ; [id_valuta,nume_val,moneda_nationala order by nume] Else pcexec = [SELECT * from ] + gcS + [.vbalanta_parteneri ] + ; [WHERE cont = ] + Alltrim(lcCont) + [ and luna = ] + Alltrim(Str(gnluna)) + ; [ and an = ] + Alltrim(Str(gnan)) + gcCondSucursala + [ order by nume] Endif pcCursor = [curent_bal_cursor] pnsucces = goExecutor.oExecute(pcexec,pcCursor) If (pnsucces != 0) *!* If reccount(pcCursor) > 0 *!* Select (pcCursor) *!* Report Format &lcNumeRaport To Printer Prompt Noconsole Preview *!* Use In (pcCursor) *!* Else *!* amessagebox("Nu exista inregistrari pentru listare!",48,"Atentie") *!* Endif goExport.export2frx(pcCursor,lcNumeRaport) Endif Endproc **************************************************************************************** ******************************************************************************************** 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 ******************************************************************************************** PROCEDURE ceretitlu_raport_v2 PARAMETERS pc_tit,pc_var obj1=CREATEOBJECT("frm_cere_titlu") obj1.clb_tx_simplu1.lb_simplu1.CAPTION = pc_tit obj1.clb_tx_simplu1.text_simplu1.CONTROLSOURCE = pc_var obj1.SHOW(1) ******************************************************************************************** PROCEDURE Alege_Cont_Titlu LPARAMETERS tcCont,tleActiv,tcTitlu 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_titlu") 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 tcCont = "0" USE IN clista RETURN ELSE tcCont = loCont.CONT tleActiv = eActiv ENDIF ENDPROC ******************************************************************************************** **************************************************************************************** ** daca NU primeste cont si activ => va alege utilizatoru pt. ce cont vrea situatia, ** altfel va fi blocat pe contul primit ca parametru ** daca primeste cont si activ => trebuie sa primeasca si ceilalti parametrii:tlCauta_alfa_Cu_Toti,tlPerioada_Blocata,tcRaport_sau_Vizualizare,tc_NumeForma **altfel nu este nevoie de ei PROCEDURE PrepareRptSitOp LPARAMETERS tcCont,tlActiv,tcTitlu,tlCauta_alfa_Cu_Toti,tlPerioada_Blocata,tcRaport_sau_Vizualizare,tc_NumeForma, tcLuna_sau_Perioada **rol parametrii: **___________________ **tcCont : contul **tlActiv : e true daca contu e activ, false dac e pasiv **tcTitlu : titlul raportului **tlCauta_alfa_Cu_Toti: true <=> sa apara "" in cauta_alfa, false <=> sa nu apara **tlPerioada_Blocata : true <=> utilizatorul are blocata perioada, este predefinita, false <=> utilizatorul poate selecta o perioada **tcRaport_sau_Vizualizare: "Vizualizare" <=> e vizulaizare, lasat gol (sau lipsa) <=>direct raport **tc_NumeForma : numele clasei dupa care va fi facuta forma de vizualizare (cum e aceeasi in toate cazurile, ar putea lipsi...) Private plDefalcatDocumentPereche, pnSemn, pnId_part, pcCont, pcPerioada, pcAnalitic, plActiv Private lctitlu,lcPerioada,lcEntitateAleasa,lnId_part,lnFel_raport, llFormRelease, lcluna1, lcluna2, lclunaAn1, lclunaAn2, lcAn1, lcAn2 PRIVATE lnSold_Final_Total, lnSold_Initial_Total Local lnNrLuni1, lnNrLuni2, lcConditieAnaliticCredit, lcConditieAnaliticDebit, lcRaport, lcSelect, lcSql Local lnId_part_curent, lnSoldInit, lnSucces, j plDefalcatDocumentPereche = .T. && daca documentele de incasare/plata se defalca dupa documentele pereche STORE "" TO lctitlu,lcPerioada,lcEntitateAleasa, pcAnalitic STORE 0 TO lnId_part,lnFel_raport STORE .F. TO plActiv STORE .F. TO llFormRelease pcCont = IIF(EMPTY(tcCont),[],tcCont) DECLARE laId_part(1,1) && stocare id-uri parteneri laId_part[1,1] = 0 IF EMPTY(tcRaport_sau_Vizualizare) tcRaport_sau_Vizualizare = "Raport" ENDIF IF EMPTY(tcTitlu) lctitlu = "Situatii Operative "+pcCont && titlu default ELSE lctitlu = tcTitlu ENDIF ** daca primeste parametrii de restrictie => creaza forma de introducere cont si partener cu acesti parametrii: **daca NU => o creaza cu totul selectabil: IF !EMPTY(tcCont) OsitOp = CREATEOBJECT('frm_lans_sit_op',tcCont,tlActiv,tlCauta_alfa_Cu_Toti,tlPerioada_Blocata,tcLuna_sau_Perioada) ELSE && elsu asta deocamdata nu-l face deloc... *!* lctitlu = "Situatie Operativa Entitati" *!* DO ceretitlu_raport_v2 WITH 'Titlul Raportului','lcTitlu' OsitOp = CREATEOBJECT('frm_lans_sit_op') ENDIF OsitOp.SHOW(1) IF !llFormRelease RETURN ENDIF IF (AT("-",lcPerioada)!=0) lclunaAn1 = SUBSTR(lcPerioada,1,7) lclunaAn2 = SUBSTR(lcPerioada,9) lcluna1=SUBSTR(lclunaAn1,1,2) lcAn1 = SUBSTR(lclunaAn1,4) lcluna2=SUBSTR(lclunaAn2,1,2) lcAn2 = SUBSTR(lclunaAn2,4) ELSE lcluna1=SUBSTR(PADL(lcPerioada,7,"0"),1,2) lcAn1 = SUBSTR(PADL(lcPerioada,7,"0"),4) lcluna2 = lcluna1 lcAn2 = lcAn1 ENDIF IF lcEntitateAleasa = "" lcEntitateAleasa = "Toate Entitatile" ENDIF pnSemn = IIF(plActiv = .T., 1, -1) pnId_part = lnId_part *!* modificare v 2.0.85 *!* pcCont = tcCont *!* modificare v 2.0.85 ^ pcPerioada = lcPerioada pcAnalitic=ALLTRIM(pcAnalitic) *MESSAGEBOX(pcAnalitic) ** restrictii: ** totzi partenerii => doar luna curenta, (simplu=NEgrupat sau grupat) ** singur partener => raport simplu=NEgrupat (pe o luna sau pe un interval de luni) *** creare structura cursor: CREATE CURSOR cursor_raport (nrord c(50) NULL, contract c(30) NULL, fel_document c(30) NULL, PART c(100) NULL, partX c(100) NULL, serie_act C(10) NULL, nract N(20), dataact d NULL,datascad d NULL, ; debit N(20,4), credit N(20,4), soldi N(20,4), soldf N(20,4), dataireg d NULL, id_lucrare N(10), id_fdoc N(10), an N(4), luna N(2), ; id_part N(10), id_partX N(10), id_fact N(10), id_factX N(10), perecheX N(14), contX c(4)) *** sold initial: *** luna pentru soldurile initiale = max(luna de initializare solduri, luna de inceput) *!* 04.08.2011 LOCAL lnAnLunaInitializare, lnAnInitializare, lnLunaInitializare lnAnLunaInitializare = 0 lnLunaInitializare = VAL(m.lcLuna1) lnAnInitializare = VAL(m.lcAn1) lnNrLuni1 = VAL(m.lcAn1)*12 + VAL(m.lcLuna1) lnNrLuni2 = VAL(m.lcAn2)*12 + VAL(m.lcLuna2) lcSql = [SELECT MIN(an*12 + luna) as luna_initializare FROM balanta_parteneri WHERE cont = ?pcCont and (precdeb <> 0 OR preccred <> 0 or debit <> 0 or credit <> 0) and an*12+luna BETWEEN ] + ; ALLTRIM(STR(lnNrLuni1)) + [ and ] + ALLTRIM(STR(lnNrLuni2)) + IIF(pnId_part <> 0,[ and id_part = ?pnId_part],[]) lnSucces = goExecutor.oSelect2Value(lcSql, @lnAnLunaInitializare) IF lnSucces < 0 aMESSAGEBOX(goExecutor.cEroare, 0+48, _screen.Caption) ENDIF lnAnLunaInitializare = MAX(NVL(m.lnAnLunaInitializare,0), lnAnInitializare * 12 + lnLunaInitializare ) NrLuni2AnLuna(lnAnLunaInitializare, @lnAnInitializare, @lnLunaInitializare) lcAn1 = ALLTRIM(STR(INT(lnAnInitializare))) lcLuna1 = PADL(INT(lnLunaInitializare), 2, '0') *!* 04.08.2011 ^ *!* IF (lnId_part != 0) && simplu=NEgrupat <=> un singur partener pcexec = [SELECT ?pnSemn*sum(precdeb-preccred) as sold_init from ] + ; gcS + [.balanta_parteneri WHERE ] + IIF(pnId_part <> 0,[id_part =?pnId_part],[1=1]) + ; [ and luna=] + ALLTRIM(STR(lnLunaInitializare)) + [ and an=] + ALLTRIM(STR(lnAnInitializare)) + [ and cont = ?pcCont] +IIF(EMPTY(pcAnalitic),[],[ and acont=?pcAnalitic ])+ gcCondSucursala *!* ELSE *!* pcexec = [SELECT ?pnSemn*(precdeb-preccred) as sold_init from ] + gcS + [.bal WHERE luna = ] + ALLTRIM(lcluna1) + [ and an=] + ALLTRIM(lcAn1) + [ and cont=] + ALLTRIM(lcCont) *!* ENDIF pcCursor = [sold_cursor] pnsucces = goExecutor.oExecute(pcexec,pcCursor) SELECT sold_cursor GO TOP IF ISNULL(sold_init) lnSoldInit = 0 ELSE lnSoldInit = sold_init ENDIF USE IN sold_cursor *** inserare prima linie cu sold initial: SELECT cursor_raport IF (pnSemn = 1) && activ INSERT INTO cursor_raport(nrord,debit,an,luna) VALUES("SOLD INITIAL "+lcluna1+"/"+lcAn1,lnSoldInit,VAL(lcAn1),VAL(lcluna1)) ELSE INSERT INTO cursor_raport(nrord,credit,an,luna) VALUES("SOLD INITIAL "+lcluna1+"/"+lcAn1,lnSoldInit,VAL(lcAn1),VAL(lcluna1)) ENDIF *** umplere cursor_raport cu datele din act: If pcAnalitic #"" lcConditieAnaliticDebit=[ and ascd = ?pcAnalitic] lcConditieAnaliticCredit=[ and ascc = ?pcAnalitic] Else lcConditieAnaliticDebit=[] lcConditieAnaliticCredit=[] EndIf IF (lnId_part != 0) && simplu=NEgrupat <=> un singur partener pcexec = [select l.nrord, c.contract, f.fel_document, p.nume as part, px.nume as partX,a.serie_act,a.nract,a.dataact,a.datascad, ] +; [SUM(a.debit) as debit, SUM(a.credit) as credit,a.dataireg,a.id_lucrare,a.id_ctr, a.id_fdoc,a.an,a.luna,a.id_part,a.id_partX, a.id_fact, a.id_factX, a.perecheX, MAX(a.contX) as contX from ] +; [(select (case when scd = ?pcCont then id_partd when scc = ?pcCont then id_partc else 00000000000000000 end) as id_part,]+; [(case when scd = ?pcCont then id_partc when scc = ?pcCont then id_partd else 00000000000000000 end) as id_partX,]+; Iif(!plDefalcatDocumentPereche, ; [00000000000000000 as perecheX,], ; [(case when scd = ?pcCont then perecheD when scc = ?pcCont then perecheC else 00000000000000000 end) as perecheX,]) + ; Iif(!plDefalcatDocumentPereche, ; [00000000000000000 as id_factX,], ; [(case when scd = ?pcCont then id_factD when scc = ?pcCont then id_factC else 00000000000000000 end) as id_factX,]) + ; [(case when scd = ?pcCont and ?pnSemn = -1 then scc when scc = ?pcCont and ?pnSemn = 1 then scd else ' ' end) as contX,]+; [(case when (scd = ?pcCont and NVL(e.exceptie,0)=0) or (scc = ?pcCont and ?pnSemn=1 and NVL(e.exceptie,0)=1) then CASE WHEN NVL(E.EXCEPTIE,0) = 1 THEN -SUMA ELSE SUMA END else 0.0000 end) as debit,]+; [(case when (scc = ?pcCont and NVL(e.exceptie,0)=0 ) or (scd = ?pcCont and ?pnSemn=-1 and NVL(e.exceptie,0)=1) then CASE WHEN NVL(E.EXCEPTIE,0) = 1 THEN -SUMA ELSE SUMA END else 0.0000 end) as credit,]+; [id_fact, serie_act, nract, dataact,datascad, dataireg, id_lucrare, id_ctr, id_fdoc, an, luna ] +; [ from act ] +; [LEFT JOIN (SELECT DISTINCT 1 AS EXCEPTIE,]+; [DECODE(DEBIT, 1, CONT_C, CONT) AS SCDe,]+; [DECODE(DEBIT, 1, CONT, CONT_C) AS SCCe ]+; [FROM EXCEPTII_IREG ]+; [WHERE INVERS = 1 ]+; [AND CONT = ']+pcCont+[') E ON Act.SCD = E.SCDe ]+; [AND Act.SCC = E.SCCe ]+; [where sters = 0 and ((scd = ?pcCont and id_partd = ?pnId_part]+lcConditieAnaliticDebit+[) or (scc = ?pcCont and id_partc = ?pnId_part]+lcConditieAnaliticCredit+[))] + ; gcCondSucursala + ; [ and an*12+luna between ] + ALLTRIM(lcAn1) + [*12+] + ALLTRIM(lcluna1) + [ and ] + ALLTRIM(lcAn2) + [*12+] + ALLTRIM(lcluna2) + [) a ] +; [left join vnom_lucrari l on a.id_lucrare = l.id_lucrare ] +; [left join vcontracte c on a.id_ctr = c.id_ctr ] +; [left join nom_fdoc f on a.id_fdoc = f.id_fdoc ] +; [left join nom_parteneri p on a.id_part = p.id_part ] +; [left join nom_parteneri px on a.id_partX = px.id_part ] +; [group by l.nrord, c.contract, f.fel_document, p.nume, px.nume,a.serie_act, a.nract,a.dataact,a.datascad,a.dataireg,a.id_lucrare,a.id_ctr,a.id_fdoc,a.an,a.luna,a.id_part,a.id_partX, a.id_fact ] + ; [,a.id_factX, a.perecheX ] + ; [order by a.dataact, a.nract] ELSE pcexec = [select l.nrord, c.contract, f.fel_document, p.nume as part, px.nume as partX,a.serie_act,a.nract,a.dataact,a.datascad, ] +; [SUM(a.debit) as debit, SUM(a.credit) as credit,a.dataireg,a.id_lucrare,a.id_ctr,a.id_fdoc,a.an,a.luna,a.id_part,a.id_partX, a.id_fact, a.id_factX, a.perecheX, MAX(a.contX) from ] +; [(select (case when scd = ?pcCont then id_partd when scc = ?pcCont then id_partc else 00000000000000000 end) as id_part,]+; [(case when scd = ?pcCont then id_partc when scc = ?pcCont then id_partd else 00000000000000000 end) as id_partX,]+; Iif(!plDefalcatDocumentPereche, [00000000000000000 as perecheX,], ; [(case when scd = ?pcCont then perecheD when scc = ?pcCont then perecheC else 00000000000000000 end) as perecheX,]) + ; Iif(!plDefalcatDocumentPereche, [00000000000000000 as id_factX,], ; [(case when scd = ?pcCont then id_factD when scc = ?pcCont then id_factC else 00000000000000000 end) as id_factX,]) + ; [(case when scd = ?pcCont and ?pnSemn = -1 then scc when scc = ?pcCont and ?pnSemn = 1 then scd else ' ' end) as contX,]+; [(case when (scd = ?pcCont and NVL(e.exceptie,0)=0) or (scc = ?pcCont and ?pnSemn=1 and NVL(e.exceptie,0)=1) then CASE WHEN NVL(E.EXCEPTIE,0) = 1 THEN -SUMA ELSE SUMA END else 0.0000 end) as debit,]+; [(case when (scc = ?pcCont and NVL(e.exceptie,0)=0) or (scd = ?pcCont and ?pnSemn=-1 and NVL(e.exceptie,0)=1) then CASE WHEN NVL(E.EXCEPTIE,0) = 1 THEN -SUMA ELSE SUMA END else 0.0000 end) as credit,]+; [id_fact, serie_act, nract, dataact,datascad, dataireg, id_lucrare, id_ctr,id_fdoc, an, luna ]+; [ from act ] +; [LEFT JOIN (SELECT DISTINCT 1 AS EXCEPTIE,]+; [DECODE(DEBIT, 1, CONT_C, CONT) AS SCDe,]+; [DECODE(DEBIT, 1, CONT, CONT_C) AS SCCe ]+; [FROM EXCEPTII_IREG ]+; [WHERE INVERS = 1 ]+; [AND CONT = ']+pcCont+[') E ON Act.SCD = E.SCDe ]+; [AND Act.SCC = E.SCCe ]+; [where sters = 0 and ((scd = ?pcCont]+lcConditieAnaliticDebit+[) or (scc = ?pcCont ]+lcConditieAnaliticCredit+[))] + ; gcCondSucursala + ; [ and an*12+luna between ] + ALLTRIM(lcAn1) + [*12+] + ALLTRIM(lcluna1) + [ and ] + ALLTRIM(lcAn2) + [*12+] + ALLTRIM(lcluna2) + [) a ] +; [left join vnom_lucrari l on a.id_lucrare = l.id_lucrare ] +; [left join vcontracte c on a.id_ctr = c.id_ctr ] +; [left join nom_fdoc f on a.id_fdoc = f.id_fdoc ] +; [left join nom_parteneri p on a.id_part = p.id_part ] +; [left join nom_parteneri px on a.id_partX = px.id_part ] +; [group by l.nrord, c.contract, f.fel_document, p.nume, px.nume,a.serie_act,a.nract,a.dataact,a.datascad,a.dataireg,a.id_lucrare,a.id_ctr,a.id_fdoc,a.an,a.luna,a.id_part,a.id_partX, a.id_fact ] + ; [,a.id_factX, a.perecheX ] + ; [order by ] + IIF(lnFel_raport = 1,[p.nume,a.id_part,],[]) + [a.dataact, a.nract] ENDIF pcCursor = [cursor_act] pnsucces = goExecutor.oExecute(pcexec,pcCursor) IF pnsucces < 0 AMESSAGEBOX('Eroare la cursor_act : ' + goExecutor.cEroare) RETURN ENDIF SELECT cursor_raport APPEND FROM DBF('cursor_act') ** calculare si inserare sold cumulat: **[prinde in calcul si soldul initial, acesta fiind trecut pe coloana debit sau credit, pe prima linie] *---------------- UN FEL DE DENOMINARE daca data in care am intrat este mai mare decat 7/2005 IF (gnan*12 + gnluna >= gnAnRon*12 + gnLunaRon) AND (VAL(lcluna1) + VAL(lcAn1)*12 < gnAnRon*12 + gnLunaRon) SELECT cursor_raport REPLACE ALL debit WITH debit/10000 FOR an *12 + luna < gnAnRon*12 + gnLunaRon REPLACE ALL credit WITH credit/10000 FOR an *12 + luna < gnAnRon*12 + gnLunaRon ENDIF *---------------- END UN FEL DE DENOMINARE IF !(lnFel_raport = 1) && NEgrupat STORE 0 TO lnSold_Initial_Total STORE 0 TO lnSold_Final_Total STORE 0 TO sold_cumulat SELECT cursor_raport LOCATE lnSold_Initial_Total = lnSold_Initial_Total + pnSemn*(debit - credit) SCAN sold_cumulat = sold_cumulat + pnSemn*(debit - credit) REPLACE soldf WITH sold_cumulat ENDSCAN lnSold_Final_Total = sold_cumulat ELSE ** incarcare vector cu solduri initiale pe fiecare partener: *** sold initial/partener: pcexec = [SELECT id_part, nume as part, sum(?pnSemn*(precdeb-preccred)) as sold_init, 0 as ales ] + ; [from vbalanta_parteneri WHERE luna=] + ALLTRIM(STR(lnLunaInitializare)) + [ and an=] + ALLTRIM(STR(lnAnInitializare)) + [ and cont = ?pcCont ] +; gcCondSucursala + ; [ group by id_part, nume order by nume ] *!* 04.08.2011 ^ pcCursor = [sold_cursor] pnsucces = goExecutor.oExecute(pcexec,pcCursor) ** calculare si inserare sold initial,sold final/fiece partener: STORE 0 TO lnSold_Initial_Total STORE 0 TO lnSold_Final_Total STORE 1 TO j STORE 0 TO sold_final, sold_ini STORE 0 TO lnId_part_curent SELECT sold_cursor SCAN SCATTER NAME osold lnSold_Initial_Total = lnSold_Initial_Total + osold.sold_init lnSold_Final_Total = lnSold_Final_Total + osold.sold_init sold_final = osold.sold_init SELECT cursor_raport SCAN FOR id_part = osold.id_part sold_final = sold_final + pnSemn*(debit - credit) lnSold_Final_Total = lnSold_Final_Total + pnSemn*(debit - credit) REPLACE soldf WITH sold_final REPLACE soldi WITH osold.sold_init osold.ales = 1 SELECT cursor_raport ENDSCAN IF osold.ales = 0 AND osold.sold_init <> 0 SELECT cursor_raport APPEND BLANK REPLACE id_part WITH osold.id_part, PART WITH osold.PART, soldi WITH osold.sold_init, soldf WITH osold.sold_init ENDIF SELECT sold_cursor ENDSCAN IF USED('sold_cursor') USE IN sold_cursor ENDIF ****** cu array *!* SELECT DISTINCT id_part, sold_init FROM sold_cursor INTO ARRAY laId_part *!* SELECT cursor_raport *!* LOCATE *!* *sold_final = pnSemn*(debit - credit)&& sa calculeze in functie de soldu init *!* IF RECCOUNT()>1 *!* GOTO 2 && pe prima e soldu init, nu partener *!* lnId_part_curent = id_part *!* lnNrRand = ALEN(laId_part,1) *!* lnElem = ASCAN(laId_part, lnId_part_curent,1,lnNrRand,1) *!* lnLinie = ASUBSCRIPT(laId_part, lnElem, 1) *!* sold_ini = laId_part[lnLinie,2] *!* sold_final = sold_ini *!* lnSold_Initial_Total = lnSold_Initial_Total + sold_ini *!* lnSold_Final_Total = lnSold_Final_Total + sold_ini *!* *!* SCAN FOR id_part <> 0 && prima linie = cea cu soldu init total=> are id_part = 0 =>fara ea *!* *** sold final calculat: e sold final deabia pe ultima linie corespunzatoare fiecarui partener, in rest (pana acolo) e sold_cumulat *!* IF (id_part != lnId_part_curent) *!* lnId_part_curent = id_part *!* lnElem = ASCAN(laId_part, lnId_part_curent,1,lnNrRand,1) *!* lnLinie = ASUBSCRIPT(laId_part, lnElem, 1) *!* sold_ini = laId_part[lnLinie,2] *!* sold_final = sold_ini *!* lnSold_Initial_Total = lnSold_Initial_Total + sold_ini *!* lnSold_Final_Total = lnSold_Final_Total + sold_ini *!* ENDIF *!* sold_final = sold_final + pnSemn*(debit - credit) *!* lnSold_Final_Total = lnSold_Final_Total + pnSemn*(debit - credit) *!* REPLACE soldf WITH sold_final *!* *REPLACE soldi WITH laId_part[lnLinie,2] *!* REPLACE soldi WITH sold_ini *!* ENDSCAN *!* ENDIF ENDIF **afisare raport sau forma de vizualizare: If tcRaport_sau_Vizualizare == "Vizualizare" op=Createobject(tc_NumeForma,laId_part) op.Lb_titlu_alb_b121.Caption = 'SITUATIE OPERATIVA ' + pcCont + ' - ' +lcEntitateAleasa op.Show(1) Else *!* modificare v 2.0.84 *!* If Reccount('cursor_raport')>0 If Reccount('cursor_raport')>1 && are intotdeauna o linie (soldul initial) Private pnOptiune Local lnNrOptiuni,loFrmOpt pnOptiune = 1 lnNrOptiuni=2 Do While gnButon = 1 loFrmOpt=Createobject("frm_optiune",[Vizualizare raport],[Alegeti modalitatea de vizualizare:],lnNrOptiuni,[Listare la imprimanta;Export in Excel;]) loFrmOpt.Show(1) Release loFrmOpt If gnButon = 1 If pnOptiune = 1 *!* modificare v 2.0.84 ^ pctitlu = lcTitlu *!* pcdataora = get_ora(2) *!* Select cursor_raport *!* IF (lnId_part != 0) *!* REPORT FORMAT sit_oper_sing_rpt1 TO PRINTER PROMPT NOCONSOLE PREVIEW *!* ELSE Select cursor_raport Set Filter To ID_PART <> 0 If (lnFel_raport = 1) && grupat *!* Report Format sit_oper_multi_gr_rpt1 To Printer Prompt Noconsole Preview lcRaport = [sit_oper_multi_gr_rpt1] Else && NEgrupat *!* Report Format sit_oper_multi_NEgr_rpt1 To Printer Prompt Noconsole Preview lcRaport = [sit_oper_multi_NEgr_rpt1] Endif goExport.export2frx([cursor_raport],lcRaport,, , , , , .T.) *!* ENDIF *!* modificare v 2.0.84 ELSE If Used('cursor_excel') Use In cursor_excel Endif lcSelect = [Select Cast(fel_document As c(100)) As fel_document,dataireg as data_inreg,dataact as data_act,] + ; [CAST((Alltrim(Alltrim(Nvl(serie_act,''))+' '+Alltrim(Str(nract,20)))) As c(100)) As nr_act,] + ; [CAST(part as c(100)) as partener,CAST(allt(nvl(nrord, ''))+IIF(!EMPTY(NVL(contract,'')),';', '')+allt(nvl(contract,'')) As c(100)) As lucrare_contract,] + ; [CAST(credit As N(14,gnPa)) As credit,Cast(debit As N(14,gnPa)) As debit,] + ; [CAST(soldi As N(14,gnPa)) As soldi,Cast(soldf As N(14,gnPa)) As soldf] + ; IIF(plDefalcatDocumentPereche,[,CAST(ALLTRIM(STR(perechex)) As c(50)) As doc_pereche],[]) + [ From cursor_raport ] + ; [ORDER BY ] + IIF(lnFel_raport = 1,[5,],[]) + [3,4 Into Cursor cursor_excel] &lcSelect goExport.export2xlsx([cursor_excel]) Use In cursor_excel Endif Endif Enddo *!* modificare v 2.0.84 ^ Else amessagebox("Nu exista inregistrari pentru listare/export!",48,"Atentie") Endif Use In cursor_raport Endif ENDPROC ********************************************************************************************