1253 lines
49 KiB
Plaintext
1253 lines
49 KiB
Plaintext
#Define TIP_TRANZIT 'TRANZIT'
|
|
#Define TIP_CHEIAJ 'CHEIAJ'
|
|
#Define TIP_APA 'APA'
|
|
#Define TIP_CHIRII 'CHIRII'
|
|
#Define TIP_PILOTAJ 'PILOTAJ'
|
|
#Define TIP_ALTE 'ALTE'
|
|
#Define TIP_PENALITATI 'PENALITATI'
|
|
#Define TIP_DIVERSE 'DIVERSE'
|
|
|
|
#Define NTIP_TRANZIT 0
|
|
#Define NTIP_CHEIAJ 1
|
|
#Define NTIP_APA 2
|
|
#Define NTIP_CHIRII 3
|
|
#Define NTIP_PILOTAJ 4
|
|
#Define NTIP_ALTE 5
|
|
#Define NTIP_PENALITATI 6
|
|
#Define NTIP_DIVERSE 9
|
|
|
|
*!* 08.07.2020
|
|
*!* Raport1_Concordanta se verifica diferentele registru documente pe locatii - contabilitate la nivel de document si conturi
|
|
|
|
*!* 24.11.2022
|
|
*!* GET_DATE_VENITURI - situatie venituri tranzit. la facturile cu valoare -, scad si indicatorii fizici.
|
|
|
|
*!* 19.08.2024
|
|
*!* GET_DATE_VENITURI - situatie venituri cheiaj - corectare UM LBD/LBP
|
|
|
|
*******************************************************************************
|
|
*** concordanta facturi comercial-contabilitate zilnic (numar facturi, valoare)
|
|
*******************************************************************************
|
|
Procedure Raport1_Concordanta
|
|
Local lcAn, lcLuna, lcRaport, lcSqlAct, lcSqlComercial, lcSqlNotInAct, lcSqlNotInComercial
|
|
Local lcSqlTVAAct, lcSqlTVAComercial, lcSqlVanzariSterse, lcdata, ldDataI, llSucces, lnAn, lnLuna
|
|
Local lnNrfact2, lnUltimazi, lnZi, lnvalftvaE_tot, lnvalftvaI_tot, lnvalftva_tot
|
|
Local lnvaltva_tot, lnvalftvacont_tot, lnvaltvacont_tot, loCom
|
|
Private pdData, pdData1, pdData2, pnTVA, pnTVARJ
|
|
|
|
lnAn = Int(m.gnAn)
|
|
lnLuna = Int(m.gnLuna)
|
|
lcAn = Alltrim(Str(m.lnAn))
|
|
lcLuna = Padl(m.lnLuna, 2, '0')
|
|
|
|
lcdata = '01/' + lcLuna + '/' + lcAn
|
|
ldDataI = Date(m.lnAn, m.lnLuna, 1)
|
|
pdData = m.ldDataI
|
|
lnUltimazi = Day(Gomonth(ldDataI, 1) - 1)
|
|
pdData1 = Date(m.gnAn, m.gnLuna, 1)
|
|
pdData2 = Gomonth(m.pdData1, 1) - 1
|
|
|
|
lnvalftva_tot = 0
|
|
lnvalftvaI_tot = 0
|
|
lnvalftvaE_tot = 0
|
|
lnvaltva_tot = 0
|
|
lnvalftvacont_tot = 0
|
|
lnvaltvacont_tot = 0
|
|
|
|
lcRaport = gcTempPath + [raport_] + Sys(2) + [.txt]
|
|
lcRaportXls = Forceext(m.lcRaport, 'xls')
|
|
|
|
CREATE CURSOR cFacturiTemp (ddata T NULL, nrfact1 I NULL, nrfact2 I NULL, ;
|
|
valftvai1 N(20,2) NULL, valftvae1 N(20,2) NULL, ;
|
|
valftva1 N(20,2) NULL, valftva2 N(20,2) NULL, difvalftva N(20,2) NULL, ;
|
|
valtva1 N(20,2) NULL, valtva2 N(20,2) NULL, difvaltva N(20,2) NULL, ;
|
|
nract I NULL, nract1 I NULL, nract2 I NULL, dataact1 D NULL, dataact2 D NULL, ;
|
|
client C(100) NULL, tip_factura C(100) NULL, ;
|
|
cont C(4) NULL, acont C(4) NULL, suma1 N(20,2) NULL, suma2 N(20,2) NULL, difcont N(20,2) NULL)
|
|
|
|
* Facturi din Vanzari si din Act
|
|
TEXT TO lcSqlVanzariAct TEXTMERGE NOSHOW
|
|
select nvl(v.ddata, a.ddata) as ddata,
|
|
nvl(v.nract, a.nract) as nract,
|
|
v.client,
|
|
v.ddata as dataact1,
|
|
a.ddata as dataact2,
|
|
v.nract as nract1,
|
|
a.nract as nract2,
|
|
v.valftvai1,
|
|
v.valftvae1,
|
|
v.valftva1,
|
|
a.valftva2,
|
|
nvl(v.valftva1,0) - nvl(a.valftva2,0) as difvalftva,
|
|
v.valtva1,
|
|
a.valtva2,
|
|
nvl(v.valtva1,0) - nvl(a.valtva2,0) as difvaltva from
|
|
(Select numar_act as nract, data_act as ddata, p.denumire as client,
|
|
Sum(total_fara_tva) As valftva1,
|
|
Sum(total_tva) As valtva1,
|
|
SUM(case
|
|
when in_valuta = 0 then
|
|
total_fara_tva
|
|
else
|
|
0
|
|
end) As valftvai1,
|
|
Sum(case
|
|
when in_valuta = 1 then
|
|
total_fara_tva
|
|
else
|
|
0
|
|
end) As valftvae1
|
|
FROM vanzari vz join nom_parteneri p on vz.id_part = p.id_part
|
|
WHERE vz.data_act between ?pdData1 and ?pdData2
|
|
and vz.sters = 0
|
|
GROUP by vz.numar_act, vz.data_act, p.denumire) v
|
|
full join (select nract, ddata, sum(baza) as valftva2, sum(tva) as valtva2
|
|
from (Select dataact as ddata, nract, sum(decode(scc, '4427', 0, suma)) as baza, sum(decode(scc, '4427', suma, 0)) as tva
|
|
FROM act
|
|
WHERE an = ?gnAn
|
|
and luna = ?gnLuna
|
|
and sters = 0
|
|
and id_set = 50200
|
|
and to_char(nract) || '/' ||
|
|
to_char(dataact, 'yyyymmdd') In
|
|
(Select to_char(numar_act) || '/' || to_char(data_act, 'yyyymmdd')
|
|
FROM vanzari
|
|
WHERE data_act between ?pdData1 and ?pdData2
|
|
and sters = 0)
|
|
group by dataact, nract)
|
|
group by nract, ddata) a
|
|
on v.ddata = a.ddata and v.nract = a.nract
|
|
order by 1,2
|
|
ENDTEXT
|
|
|
|
* Selectie din Vanzari si Act pe zile
|
|
Wait Window 'Selectie facturi si valori...' Nowait
|
|
llSucces = goExecutor.oExecuta(m.lcSqlVanzariAct, 'cFacturiTemp2')
|
|
|
|
|
|
* Centralizez valorile pe zile
|
|
Select ddata, ;
|
|
SUM(Iif(!Empty(Nvl(nract1, 0)), 1, 0)) As nrfact1, ;
|
|
SUM(Iif(!Empty(Nvl(nract2, 0)), 1, 0)) As nrfact2, ;
|
|
SUM(valftvai1) As valftvai1, ;
|
|
SUM(valftvae1) As valftvae1, ;
|
|
SUM(valftva1) As valftva1, ;
|
|
SUM(valftva2) As valftva2, ;
|
|
SUM(difvalftva) As difvalftva, ;
|
|
SUM(valtva1) As valtva1, ;
|
|
SUM(valtva2) As valtva2, ;
|
|
SUM(difvaltva) As difvaltva, ;
|
|
CAST(Null As N(20)) As nract, ;
|
|
CAST(Null As N(20)) As nract1, ;
|
|
CAST(Null As N(20)) As nract2, ;
|
|
CAST(Null As D) As dataact1, ;
|
|
CAST(Null As D) As dataact2, ;
|
|
CAST(Null As C(100)) As client, ;
|
|
CAST(Null As C(100)) As tip_factura ;
|
|
from cFacturiTemp2 ;
|
|
group By 1 ;
|
|
order By 1 ;
|
|
into Cursor cFacturiTemp3
|
|
|
|
SELECT cFacturiTemp
|
|
APPEND FROM DBF('cFacturiTemp3')
|
|
USE IN (SELECT('cFacturiTemp3'))
|
|
|
|
* Selectez facturile cu diferente comercial-contabilitate
|
|
Select C.*, 'Diferente Comercial - Contabilitate' As tip_factura ;
|
|
FROM cFacturiTemp2 C ;
|
|
WHERE Nvl(valftva1, 0) <> Nvl(valftva2, 0) Or Nvl(valftva2, 0) <> Nvl(valftva2, 0) ;
|
|
ORDER By ddata, nract ;
|
|
INTO Cursor cFacturiDifTemp
|
|
|
|
Select cFacturiTemp
|
|
Append From Dbf('cFacturiDifTemp')
|
|
Use In (Select('cFacturiDifTemp'))
|
|
Use In (Select('cFacturiTemp2'))
|
|
|
|
*** Facturi sterse din Vanzari, care nu sunt refacute cu acelasi numar si data
|
|
TEXT TO lcSqlVanzariSterse TEXTMERGE NOSHOW
|
|
Select distinct v.numar_act as nract,
|
|
'Sterse' as tip_factura
|
|
FROM vanzari v
|
|
WHERE v.data_act between ?pdData1 and ?pdData2 And v.sters = 1
|
|
and v.numar_act Not In
|
|
(Select numar_act
|
|
FROM vanzari
|
|
WHERE data_act between ?pdData1 and ?pdData2 And sters = 0)
|
|
order by v.numar_act
|
|
ENDTEXT
|
|
|
|
Wait Window 'Selectie facturi sterse din comercial...' Nowait
|
|
llSucces = goExecutor.oExecuta(m.lcSqlVanzariSterse, 'cFacturiSterseTemp')
|
|
|
|
Select cFacturiTemp
|
|
Append From Dbf('cFacturiSterseTemp')
|
|
Use In (Select('cFacturiSterseTemp'))
|
|
|
|
|
|
*** facturi cu diferente pe conturi din Regdoc fata de contabilitate
|
|
TEXT TO lcSqlRegdocAct TEXTMERGE NOSHOW
|
|
select nvl(a1.dataact, a2.dataact) as ddata,
|
|
nvl(a1.nract, a2.nract) as nract,
|
|
nvl(a1.denumire, p2.denumire) as client,
|
|
NVL(a1.cont, a2.cont) as cont,
|
|
NVL(a1.acont, a2.acont) as acont,
|
|
a1.suma as suma1,
|
|
a2.suma as suma2,
|
|
nvl(a2.suma, 0) - nvl(a1.suma, 0) as difcont,
|
|
'Diferente Reg. Documente - Contabilitate' as tip_factura
|
|
from (select a11.id_fact,
|
|
a11.nract,
|
|
a11.dataact,
|
|
p.denumire,
|
|
decode(e1.exceptie, 1, a11.scd, a11.scc) as cont,
|
|
decode(e1.exceptie, 1, a11.ascd, a11.ascc) as acont,
|
|
sum(decode(e1.exceptie, 1, -a11.suma, a11.suma)) as suma
|
|
from (select v.id_fact,
|
|
v.id_part,
|
|
v.numar_act as nract,
|
|
v.data_act as dataact,
|
|
trim(decode(r.tip, 6, pl.ctpideb, pl.ctdebitor)) as scd,
|
|
trim(decode(r.tip,
|
|
6,
|
|
pl.ctapideb,
|
|
decode(v.in_valuta,
|
|
1,
|
|
pl.ctaedeb,
|
|
pl.ctaideb))) as ascd,
|
|
trim(decode(r.tip, 6, pl.ctpicred, pl.ctcreditor)) as scc,
|
|
trim(decode(r.tip,
|
|
6,
|
|
pl.ctapicred,
|
|
decode(v.in_valuta,
|
|
1,
|
|
pl.ctaecred,
|
|
pl.ctaicred))) as ascc,
|
|
r.valftva as suma
|
|
from ips_regdoc r
|
|
join vanzari v
|
|
on r.id_vanzare = v.id_vanzare
|
|
left join ips_prestatii_locatii pl
|
|
on r.id_articol = pl.id_articol
|
|
and r.id_locatia = pl.id_locatia
|
|
where extract(year from v.data_act) = ?gnAn
|
|
and extract(month from v.data_act) = ?gnLuna
|
|
and v.sters = 0) a11
|
|
LEFT JOIN (SELECT DISTINCT 1 AS EXCEPTIE,
|
|
DECODE(DEBIT, 1, CONT_C, CONT) AS SCD,
|
|
DECODE(DEBIT, 1, CONT, CONT_C) AS SCC
|
|
FROM EXCEPTII_IREG
|
|
WHERE INVERS = 1) e1
|
|
ON trim(a11.scd) = trim(e1.scd)
|
|
AND trim(a11.scc) = trim(e1.scc)
|
|
left join nom_parteneri p
|
|
on a11.id_part = p.id_part
|
|
group by a11.id_fact,
|
|
a11.nract,
|
|
a11.dataact,
|
|
p.denumire,
|
|
decode(e1.exceptie, 1, a11.scd, a11.scc),
|
|
decode(e1.exceptie, 1, a11.ascd, a11.ascc)) a1
|
|
full join (select a.id_fact,
|
|
a.nract,
|
|
a.dataact,
|
|
decode(e.exceptie, 1, a.id_partc, a.id_partd) as id_part,
|
|
decode(e.exceptie, 1, a.scd, a.scc) as cont,
|
|
decode(e.exceptie, 1, a.ascd, a.ascc) as acont,
|
|
sum(decode(e.exceptie, 1, -a.suma, a.suma)) as suma
|
|
from act a
|
|
LEFT JOIN (SELECT DISTINCT 1 AS EXCEPTIE,
|
|
DECODE(DEBIT, 1, CONT_C, CONT) AS SCD,
|
|
DECODE(DEBIT, 1, CONT, CONT_C) AS SCC
|
|
FROM EXCEPTII_IREG
|
|
WHERE INVERS = 1) E
|
|
ON trim(A.SCD) = trim(E.SCD)
|
|
AND trim(A.SCC) = trim(E.SCC)
|
|
where a.an = ?gnAn
|
|
and a.luna = ?gnLuna
|
|
and a.sters = 0
|
|
and a.id_Set = 50200
|
|
AND a.scc not in ('4427', '4428')
|
|
group by a.id_fact,
|
|
a.nract,
|
|
a.dataact,
|
|
decode(e.exceptie, 1, a.id_partc, a.id_partd),
|
|
decode(e.exceptie, 1, a.scd, a.scc),
|
|
decode(e.exceptie, 1, a.ascd, a.ascc)) a2
|
|
on (a1.id_fact = a2.id_fact and a1.nract = a2.nract and
|
|
a1.dataact = a2.dataact and trim(a1.cont) = trim(a2.cont) and
|
|
trim(nvl(a1.acont, 'x')) = trim(nvl(a2.acont, 'x')))
|
|
left join nom_parteneri p2
|
|
on a2.id_part = p2.id_part
|
|
where abs(nvl(a1.suma, 0) - nvl(a2.suma, 0)) > 0.05
|
|
order by 1, 2
|
|
ENDTEXT
|
|
|
|
* Selectie din Regdoc si Act pe zile si conturi
|
|
Wait Window 'Selectie Reg. Documente si valori...' Nowait
|
|
llSucces = goExecutor.oExecuta(m.lcSqlRegdocAct, 'cFacturiTemp4')
|
|
|
|
Select cFacturiTemp
|
|
Append From Dbf('cFacturiTemp4')
|
|
USE IN (SELECT('cFacturiTemp4'))
|
|
|
|
***********************************
|
|
Set Textmerge On To (lcRaport) Noshow
|
|
Select cFacturiTemp
|
|
Scan For Isnull(tip_factura)
|
|
pdData = Ttod(ddata)
|
|
|
|
lnvalftva_tot = lnvalftva_tot + valftva1
|
|
lnvalftvaI_tot = lnvalftvaI_tot + valftvai1
|
|
lnvalftvaE_tot = lnvalftvaE_tot + valftvae1
|
|
lnvaltva_tot = lnvaltva_tot + valtva1
|
|
lnvalftvacont_tot = lnvalftvacont_tot + valftva2
|
|
lnvaltvacont_tot = lnvaltvacont_tot + valtva2
|
|
|
|
\zi: <<m.pdData>> <<IIF(nrfact1 # nrfact2, "ATENTIE!!!!!!", "")>>
|
|
\Nr. fact. comercial : <<INT(nrfact1)>>
|
|
\Nr. fact. contabilitate : <<INT(nrfact2)>>
|
|
\Val. fara TVA comercial INTERN : <<TRANSFORM(valftvaI1,'999 999 999 999.99')>>
|
|
\Val. fara TVA comercial EXTERN : <<TRANSFORM(valftvaE1,'999 999 999 999.99')>>
|
|
\Val. fara TVA comercial TOTAL : <<TRANSFORM(valftva1,'999 999 999 999.99')>>
|
|
\Val. fara TVA contabilitate TOTAL : <<TRANSFORM(valftva2,'999 999 999 999.99')>> <<IIF(valftva1 # valftva2, "ATENTIE!!!!!!", "")>>
|
|
\Val. TVA comercial : <<TRANSFORM(valtva1,'999 999 999 999.99')>>
|
|
\Val. TVA contabilitate : <<TRANSFORM(valtva2,'999 999 999 999.99')>> <<IIF(valtva1 # valtva2, "ATENTIE!!!!!!", "")>>
|
|
\-------------------------------------------------------------------------
|
|
Endscan
|
|
\
|
|
\
|
|
\TOTAL PERIOADA
|
|
\Val. fara TVA comercial INTERN : <<TRANSFORM(lnvalftvaI_tot ,'999 999 999 999.99')>>
|
|
\Val. fara TVA comercial EXTERN : <<TRANSFORM(lnvalftvaE_tot ,'999 999 999 999.99')>>
|
|
\Val. fara TVA comercial TOTAL : <<TRANSFORM(lnvalftva_tot,'999 999 999 999.99')>>
|
|
\Val. fara TVA contabilitate TOTAL : <<TRANSFORM(lnvalftvacont_tot,'999 999 999 999.99')>> <<IIF(m.lnvalftva_tot # m.lnvalftvacont_tot, "ATENTIE!!!!!!", "")>>
|
|
\Val. TVA comercial : <<TRANSFORM(lnvaltva_tot ,'999 999 999 999.99')>>
|
|
\Val. TVA contabilitate : <<TRANSFORM(lnvaltvacont_tot ,'999 999 999 999.99')>> <<IIF(m.lnvaltva_tot # m.lnvaltvacont_tot , "ATENTIE!!!!!!", "")>>
|
|
\-------------------------------------------------------------------------
|
|
|
|
* Facturile cu diferente din Vanzari care nu sunt in Registrul Jurnal
|
|
Wait Window 'Facturi cu diferente Comercial - Contabilitate...' Nowait
|
|
\
|
|
\Facturi cu diferente in comercial fata de contabilitate in perioada <<m.pdData1>> - <<m.pdData2>>
|
|
Sele cFacturiTemp
|
|
Scan For Alltrim(tip_factura) = 'Diferente Comercial - Contabilitate'
|
|
\Nr. fact. <<nract>> Data fact. <<TTOD(ddata)>> Client <<client>>
|
|
\Val. fara TVA comercial : <<TRANSFORM(valftva1,'999 999 999 999.99')>>
|
|
\Val. fara TVA contabilitate : <<TRANSFORM(valftva2,'999 999 999 999.99')>> <<IIF(valftva1 # valftva2, "ATENTIE!!!!!!", "")>>
|
|
\Val. TVA comercial : <<TRANSFORM(valtva1,'999 999 999 999.99')>>
|
|
\Val. TVA contabilitate : <<TRANSFORM(valtva2,'999 999 999 999.99')>> <<IIF(valtva1 # valtva2, "ATENTIE!!!!!!", "")>>
|
|
Endscan
|
|
|
|
* Facturi sterse
|
|
\
|
|
\
|
|
\Numere de facturi sterse in comercial si nereutilizate in perioada <<m.pdData1>> - <<m.pdData2>>
|
|
Sele cFacturiTemp
|
|
Scan For Alltrim(tip_factura) = 'Sterse'
|
|
\Nr. fact. <<nract>>
|
|
Endscan
|
|
|
|
* Facturile cu diferente Reg. documente pe locatii fata de Contabilitate pe conturi
|
|
Wait Window 'Facturi cu diferente Reg. Documente pe locatii - Contabilitate...' Nowait
|
|
\
|
|
\Facturi cu diferente Reg. Documente pe locatii fata de contabilitate in perioada <<m.pdData1>> - <<m.pdData2>>
|
|
Sele cFacturiTemp
|
|
Scan For Alltrim(tip_factura) = 'Diferente Reg. Documente - Contabilitate'
|
|
\Nr. fact: <<PADL(nract,15, ' ')>> Data fact: <<DTOC(TTOD(ddata))>> Client: <<PADR(ALLTRIM(client),50, ' ')>>
|
|
\\ Cont: <<PADR(ALLTRIM(cont) + IIF(!EMPTY(NVL(acont,'')), '.' + ALLTRIM(acont), ''), 10, ' ')>> ; Contabilitate: <<ALLTRIM(TRANSFORM(NVL(suma2,0),'999 999 999 999.99'))>>
|
|
\\ - Reg. Doc: <<ALLTRIM(TRANSFORM(NVL(suma1,0),'999 999 999 999.99'))>>
|
|
\\ = Diferenta: <<ALLTRIM(TRANSFORM(NVL(difcont,0),'999 999 999 999.99'))>>
|
|
Endscan
|
|
|
|
Set Textmerge To
|
|
|
|
Select Ttod(ddata) As Data, ;
|
|
nrfact1 As nr_facturi_com, ;
|
|
nrfact2 As nr_facturi_cont, ;
|
|
valftvai1 As valoare_fara_tva_int_com, ;
|
|
valftvae1 As valoare_fara_tva_ext_com, ;
|
|
valftva1 As valoare_fara_tva_com, ;
|
|
valftva2 As valoare_fara_tva_cont, ;
|
|
difvalftva As diferenta_valoare_fara_tva, ;
|
|
valtva1 As valoare_tva_com, ;
|
|
valtva2 As valoare_tva_cont, ;
|
|
difvaltva As diferenta_valoare_tva, ;
|
|
PADR(ALLTRIM(NVL(cont, '')) + IIF(!EMPTY(NVL(acont,'')), '.' + ALLTRIM(acont), ''), 10, ' ') as cont, ;
|
|
suma1 as regdoc_suma, ;
|
|
suma2 as conta_suma, ;
|
|
difcont as diferenta_regdoc_conta, ;
|
|
nract, Ttod(ddata) As dataact, client, tip_factura ;
|
|
FROM cFacturiTemp ;
|
|
INTO Cursor cFacturiXLS
|
|
|
|
Select cFacturiXLS
|
|
Copy To (lcRaportXls) Type Xl5
|
|
|
|
Use In (Select('cFacturiTemp'))
|
|
Use In (Select('cFacturiXLS'))
|
|
|
|
|
|
open_default_app(m.lcRaport)
|
|
open_default_app(m.lcRaportXls)
|
|
|
|
*!* lcRun = [run /N notepad.exe "] + lcRaport + ["]
|
|
*!* &lcRun
|
|
|
|
Endproc
|
|
&& --SFARSIT: Raport1_Concordanta --
|
|
|
|
*********************************
|
|
*** Lanseaza formularul frm_situatie_venituri
|
|
*********************************
|
|
Procedure situatii_venituri
|
|
loSituatieVenituri = Createobject("frm_situatii_venituri")
|
|
loSituatieVenituri.Show(1)
|
|
Endproc && situatii_venituri
|
|
|
|
Procedure GET_DATE_VENITURI
|
|
Parameters toDate, tcTabel, tcColoane
|
|
* tcTabel OUT: numele cursorului rezultat
|
|
* tcColoane OUT: lista coloane din cursorul rezultat, separate prin |
|
|
|
|
* tnTip: 0 = TRANZIT, 1 = CHEIAJ, 2 = ALTE (NU SE MAI FOLOSESTE), 3 = CHIRII, 4 = PILOTAJ (NU SE MAI FOLOSESTE), 5 = APA, 6 = PENALITATI, 9 = DIVERSE
|
|
* toDate.nNrRaport: numarul situatiei de venituri
|
|
* toDate.dData1, toDate.dData2: perioada pentru raport
|
|
* toDate.nIdClient, toDate.nIdCtrClient (OPTIONAL): id contract client, daca se doreste raportul pentru un singur contract
|
|
|
|
Local lcPrestatia, lcSql, lcValfTVA, ldData1, llSucces, lnNrRaport, lnTip, lnTotalCumulat, lnvaloare
|
|
*:Global B, I, LCFIELD, LPRESTATIA, NVENITURI, OClient, OTOTAL, T, X, pdData1, pdData2, pnIdClient
|
|
*:Global pnIdCtrClient, taHeader[1]
|
|
lnTip = toDate.nTip
|
|
lnNrRaport = toDate.nNrRaport
|
|
pdData1 = toDate.dData1
|
|
pdData2 = toDate.dData2
|
|
pnIdClient = Nvl(toDate.nIdClient, 0)
|
|
pnIdCtrClient = Nvl(toDate.nIdCtrClient, 0)
|
|
|
|
Do Case
|
|
Case m.lnTip = NTIP_TRANZIT And m.lnNrRaport = 3 && TRANZIT: CLIENT,INT/EXT, CAP., TRN, CP, LBP
|
|
|
|
TEXT TO lcSql TEXTMERGE NOSHOW
|
|
SELECT p1.denumire As client,
|
|
c.numar as contract,
|
|
p2.denumire as beneficiar,
|
|
decode(v.in_valuta, 1, 0, 1) as intern,
|
|
vmv.um as ct,
|
|
ROUND(sum(SIGN(vmv.valftva)*vmv.quantity),0) as cargo,
|
|
ROUND(sum(case when vmv.um = 'TON' then SIGN(vmv.valftva)*vmv.tcap else 0 end),0) as tcap,
|
|
ROUND(sum(case when vmv.um = 'TRN' then SIGN(vmv.valftva)*vmv.trn else 0 end),0) as trn,
|
|
ROUND(sum(case when vmv.um = 'LBD' then SIGN(vmv.valftva)*vmv.lbd else 0 end),0) as lbd,
|
|
ROUND(sum(case when vmv.um = 'HP' then SIGN(vmv.valftva)*vmv.hp else 0 end),0) as hp,
|
|
ROUND(sum(vmv.valval),0) as valval,
|
|
ROUND(sum(vmv.valftva),0) as valftva
|
|
From vanzari v
|
|
join ips_voyages_vanzari vv
|
|
on v.id_vanzare = vv.vz_id
|
|
join ips_voyage_members_vanzari vmv on vv.id = vmv.vv_id
|
|
join nom_parteneri p1
|
|
on v.id_part = p1.id_part
|
|
left join nom_parteneri p2
|
|
on v.id_beneficiar = p2.id_part
|
|
left join contracte c
|
|
on v.id_ctr = c.id_ctr
|
|
Where v.data_act between ?pdData1 and ?pdData2 and v.tip_factura = <<m.lnTip>> <<IIF(!EMPTY(m.pnIdClient), 'and v.id_part = ?pnIdClient', '')>> <<IIF(!EMPTY(m.pnIdCtrClient), 'and v.id_ctr = ?pnIdCtrClient', '')>>
|
|
Group By p1.denumire, c.numar, p2.denumire, v.in_valuta, vmv.um
|
|
Order By 1, 2, 3, 4, 5
|
|
ENDTEXT
|
|
|
|
tcTabel = "cRaportTranzit"
|
|
tcColoane = [Client|Client ctr.|Beneficiar|Intern|UM|Cargo|CAP|TRN|LBD|HP|Valoare valuta|Valoare lei]
|
|
llSucces = goExecutor.oExecuta(m.lcSql, m.tcTabel)
|
|
|
|
Case m.lnTip = NTIP_CHEIAJ And m.lnNrRaport = 5 && CHEIAJ: 3 = Clienti - venituri pe locatii (lei)
|
|
TEXT TO lcSql TEXTMERGE NOSHOW
|
|
SELECT p1.denumire As client, v.id_part as client_id,
|
|
Round(Sum(CASE WHEN r.id_locatia = 5 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P1,
|
|
Round(Sum(CASE WHEN r.id_locatia = 5 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P2,
|
|
Round(Sum(CASE WHEN r.id_locatia = 1 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P3,
|
|
Round(Sum(CASE WHEN r.id_locatia = 1 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P4,
|
|
Round(Sum(CASE WHEN r.id_locatia = 4 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P5,
|
|
Round(Sum(CASE WHEN r.id_locatia = 4 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P6,
|
|
Round(Sum(CASE WHEN r.id_locatia = 2 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P7,
|
|
Round(Sum(CASE WHEN r.id_locatia = 2 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P8,
|
|
Round(Sum(CASE WHEN r.id_locatia = 3 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P9,
|
|
Round(Sum(CASE WHEN r.id_locatia = 3 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P10,
|
|
Round(Sum(CASE WHEN r.id_locatia = 6 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P11,
|
|
Round(Sum(CASE WHEN r.id_locatia = 6 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P12,
|
|
Round(Sum(CASE WHEN r.id_locatia = 7 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P13,
|
|
Round(Sum(CASE WHEN r.id_locatia = 7 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P14,
|
|
Round(Sum(CASE WHEN r.id_locatia = 23 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P15,
|
|
Round(Sum(CASE WHEN r.id_locatia = 23 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P16,
|
|
Round(Sum(CASE WHEN r.id_locatia = 26 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P17,
|
|
Round(Sum(CASE WHEN r.id_locatia = 26 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P18,
|
|
Round(Sum(r.valftva), 0) As TOTAL, CAST(0 as NUMBER(20)) AS TOTALCUMUL, 0 AS TIP
|
|
From ips_regdoc r Join vanzari v on r.id_vanzare = v.id_vanzare
|
|
join nom_parteneri p1 on v.id_part = p1.id_part
|
|
Where v.data_act between ?pdData1 and ?pdData2 and v.tip_factura = <<m.lnTip>> <<IIF(!EMPTY(m.pnIdClient), 'and v.id_part = ?pnIdClient', '')>> <<IIF(!EMPTY(m.pnIdCtrClient), 'and v.id_ctr = ?pnIdCtrClient', '')>>
|
|
Group By p1.denumire, v.id_part
|
|
ENDTEXT
|
|
|
|
llSucces = goExecutor.oExecuta(m.lcSql, "xTabx")
|
|
|
|
If m.llSucces
|
|
*!* TOTAL CUMULAT DE LA INCEPUTUL ANULUI
|
|
ldData1 = m.pdData1
|
|
pdData1 = Date(Year(m.ldData1), 1, 1)
|
|
llSucces = goExecutor.oExecuta(m.lcSql, "xTabCumulat")
|
|
pdData1 = m.ldData1
|
|
Endif
|
|
|
|
IF m.llSucces
|
|
* Totaluri cumulat de la inceputul anului pe linii (clienti)
|
|
Sele xTabCumulat
|
|
Scan
|
|
Scatter Name loClient
|
|
With loClient
|
|
Select xTabx
|
|
Locate For client_id = .client_id
|
|
If !Found()
|
|
Insert Into xTabx (client, client_id, totalcumul) Values (.client, .client_id, .total)
|
|
Else
|
|
Replace totalcumul With .total
|
|
Endif
|
|
Endwith
|
|
Sele xTabCumulat
|
|
ENDSCAN
|
|
|
|
* Totaluri perioada pe coloane (locatii)
|
|
SELECT "TOTAL" as client, 0 as client_id, 1 as tip, 0 as totalcumul, SUM(total) as total, ;
|
|
SUM(p1) as p1,SUM(p2) as p2,SUM(p3) as p3,SUM(p4) as p4,SUM(p5) as p5,SUM(p6) as p6, ;
|
|
SUM(p7) as p7,SUM(p8) as p8,SUM(p9) as p9,SUM(p10) as p10,SUM(p11) as p11,SUM(p12) as p12, ;
|
|
SUM(p13) as p13,SUM(p14) as p14,SUM(p15) as p15,SUM(p16) as p16,SUM(p17) as p17,SUM(p18) as p18 ;
|
|
from xTabx ;
|
|
into cursor cTabPerioada
|
|
SELECT cTabPerioada
|
|
SCATTER NAME loTabPerioada
|
|
INSERT INTO xTabx FROM NAME loTabPerioada
|
|
USE IN (SELECT('cTabPerioada'))
|
|
|
|
* Totaluri cumulat de la inceputul anului pe coloane (locatii)
|
|
SELECT "TOTAL CUMULAT" as client, 0 as client_id, 2 as tip, SUM(total) as totalcumul, 0 as total, ;
|
|
SUM(p1) as p1,SUM(p2) as p2,SUM(p3) as p3,SUM(p4) as p4,SUM(p5) as p5,SUM(p6) as p6, ;
|
|
SUM(p7) as p7,SUM(p8) as p8,SUM(p9) as p9,SUM(p10) as p10,SUM(p11) as p11,SUM(p12) as p12, ;
|
|
SUM(p13) as p13,SUM(p14) as p14,SUM(p15) as p15,SUM(p16) as p16,SUM(p17) as p17,SUM(p18) as p18 ;
|
|
from xTabCumulat ;
|
|
into cursor cTabCumulat
|
|
SELECT cTabCumulat
|
|
SCATTER NAME loTabCumulat
|
|
INSERT INTO xTabx FROM NAME loTabCumulat
|
|
USE IN (SELECT('cTabCumulat'))
|
|
|
|
ENDIF && llSuccess
|
|
|
|
SELECT * FROM xTabx ORDER BY tip, client INTO CURSOR xTaby
|
|
USE IN (SELECT('xTabx'))
|
|
tcTabel = "XTABY"
|
|
tcColoane = [Client|Client Id|Agigea extern|Agigea intern|Basarabi extern|Basarabi intern|Luminita extern|Luminita intern|Medgidia extern|Medgidia intern|Ovidiu extern|Ovidiu intern|] + ;
|
|
[Cernavoda extern|Cernavoda intern|Navodari extern|Navodari intern|Front asteptare extern|Front asteptare intern|Senal extern|Senal intern|] + ;
|
|
[Total perioada|Total cumulat de la inceputul anului|Tip]
|
|
|
|
Case m.lnTip = NTIP_TRANZIT And m.lnNrRaport = 13 && TRANZIT: CLIENT, TCAP TRANSE PLIN, TCAP TRANSE GOL, TCAP TOTAL, TON MARFA
|
|
|
|
TEXT TO lcSql TEXTMERGE NOSHOW
|
|
SELECT client, contract, beneficiar, SUM(cargo) as cantitate_marfa,
|
|
SUM(CASE WHEN cargo <> 0 AND tcap < 2500 THEN tcap ELSE 0 END) as tcap_plin_0_2499,
|
|
SUM(CASE WHEN cargo <> 0 AND tcap between 2500 and 5999 THEN tcap ELSE 0 END) as tcap_plin_2500_5999,
|
|
SUM(CASE WHEN cargo <> 0 AND tcap between 6000 and 8999 THEN tcap ELSE 0 END) as tcap_plin_6000_8999,
|
|
SUM(CASE WHEN cargo <> 0 AND tcap between 9000 and 11999 THEN tcap ELSE 0 END) as tcap_plin_9000_11999,
|
|
SUM(CASE WHEN cargo <> 0 AND tcap >= 12000 THEN tcap ELSE 0 END) as tcap_plin_12000_99000,
|
|
SUM(CASE WHEN cargo = 0 AND tcap < 2500 THEN tcap ELSE 0 END) as tcap_plin_0_2499,
|
|
SUM(CASE WHEN cargo = 0 AND tcap between 2500 and 5999 THEN tcap ELSE 0 END) as tcap_plin_2500_5999,
|
|
SUM(CASE WHEN cargo = 0 AND tcap between 6000 and 8999 THEN tcap ELSE 0 END) as tcap_plin_6000_8999,
|
|
SUM(CASE WHEN cargo = 0 AND tcap between 9000 and 11999 THEN tcap ELSE 0 END) as tcap_plin_9000_11999,
|
|
SUM(CASE WHEN cargo = 0 AND tcap >= 12000 THEN tcap ELSE 0 END) as tcap_plin_12000_99000,
|
|
SUM(tcap) as total_tone_capacitate
|
|
FROM (SELECT p1.denumire As client,
|
|
c.numar as contract,
|
|
p2.denumire as beneficiar,
|
|
vmv.vye_id,
|
|
ROUND(sum(vmv.quantity),2) as cargo,
|
|
ROUND(sum(case when vmv.um = 'TON' then vmv.tcap else 0 end),2) as tcap
|
|
From vanzari v
|
|
join ips_voyages_vanzari vv
|
|
on v.id_vanzare = vv.vz_id
|
|
join ips_voyage_members_vanzari vmv on vv.id = vmv.vv_id
|
|
join nom_parteneri p1
|
|
on v.id_part = p1.id_part
|
|
left join nom_parteneri p2
|
|
on v.id_beneficiar = p2.id_part
|
|
left join contracte c
|
|
on v.id_ctr = c.id_ctr
|
|
Where v.data_act between ?pdData1 and ?pdData2 and v.tip_factura = <<m.lnTip>> <<IIF(!EMPTY(m.pnIdClient), 'and v.id_part = ?pnIdClient', '')>> <<IIF(!EMPTY(m.pnIdCtrClient), 'and v.id_ctr = ?pnIdCtrClient', '')>>
|
|
Group By p1.denumire, c.numar, p2.denumire, vmv.vye_id)
|
|
WHERE tcap <> 0
|
|
GROUP BY client, contract, beneficiar
|
|
Order By client, contract, beneficiar
|
|
ENDTEXT
|
|
|
|
tcTabel = "cRaportTranzit"
|
|
tcColoane = [Client|Client ctr.|Beneficiar|Cantitate marfa|Plin 0-2499|Plin 2500-5999|Plin 6000-8999|Plin 9000-11999|Plin 12000-99000|Gol 0-2499|Gol 2500-5999|Gol 6000-8999|Gol 9000-11999|Gol 12000-99000|Total tone capacitate]
|
|
llSucces = goExecutor.oExecuta(m.lcSql, m.tcTabel)
|
|
|
|
Case m.lnTip = NTIP_CHEIAJ And m.lnNrRaport = 5 && CHEIAJ: 3 = Clienti - venituri pe locatii (lei)
|
|
TEXT TO lcSql TEXTMERGE NOSHOW
|
|
SELECT p1.denumire As client, v.id_part as client_id,
|
|
Round(Sum(CASE WHEN r.id_locatia = 5 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P1,
|
|
Round(Sum(CASE WHEN r.id_locatia = 5 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P2,
|
|
Round(Sum(CASE WHEN r.id_locatia = 1 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P3,
|
|
Round(Sum(CASE WHEN r.id_locatia = 1 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P4,
|
|
Round(Sum(CASE WHEN r.id_locatia = 4 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P5,
|
|
Round(Sum(CASE WHEN r.id_locatia = 4 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P6,
|
|
Round(Sum(CASE WHEN r.id_locatia = 2 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P7,
|
|
Round(Sum(CASE WHEN r.id_locatia = 2 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P8,
|
|
Round(Sum(CASE WHEN r.id_locatia = 3 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P9,
|
|
Round(Sum(CASE WHEN r.id_locatia = 3 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P10,
|
|
Round(Sum(CASE WHEN r.id_locatia = 6 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P11,
|
|
Round(Sum(CASE WHEN r.id_locatia = 6 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P12,
|
|
Round(Sum(CASE WHEN r.id_locatia = 7 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P13,
|
|
Round(Sum(CASE WHEN r.id_locatia = 7 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P14,
|
|
Round(Sum(CASE WHEN r.id_locatia = 23 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P15,
|
|
Round(Sum(CASE WHEN r.id_locatia = 23 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P16,
|
|
Round(Sum(CASE WHEN r.id_locatia = 26 and v.in_valuta = 1 THEN r.valftva ELSE 0 END), 0) As P17,
|
|
Round(Sum(CASE WHEN r.id_locatia = 26 and v.in_valuta = 0 THEN r.valftva ELSE 0 END), 0) As P18,
|
|
Round(Sum(r.valftva), 0) As TOTAL, CAST(0 as NUMBER(20)) AS TOTALCUMUL, 0 AS TIP
|
|
From ips_regdoc r Join vanzari v on r.id_vanzare = v.id_vanzare
|
|
join nom_parteneri p1 on v.id_part = p1.id_part
|
|
Where v.data_act between ?pdData1 and ?pdData2 and v.tip_factura = <<m.lnTip>> <<IIF(!EMPTY(m.pnIdClient), 'and v.id_part = ?pnIdClient', '')>> <<IIF(!EMPTY(m.pnIdCtrClient), 'and v.id_ctr = ?pnIdCtrClient', '')>>
|
|
Group By p1.denumire, v.id_part
|
|
ENDTEXT
|
|
|
|
llSucces = goExecutor.oExecuta(m.lcSql, "xTabx")
|
|
|
|
If m.llSucces
|
|
*!* TOTAL CUMULAT DE LA INCEPUTUL ANULUI
|
|
ldData1 = m.pdData1
|
|
pdData1 = Date(Year(m.ldData1), 1, 1)
|
|
llSucces = goExecutor.oExecuta(m.lcSql, "xTabCumulat")
|
|
pdData1 = m.ldData1
|
|
Endif
|
|
|
|
IF m.llSucces
|
|
* Totaluri cumulat de la inceputul anului pe linii (clienti)
|
|
Sele xTabCumulat
|
|
Scan
|
|
Scatter Name loClient
|
|
With loClient
|
|
Select xTabx
|
|
Locate For client_id = .client_id
|
|
If !Found()
|
|
Insert Into xTabx (client, client_id, totalcumul) Values (.client, .client_id, .total)
|
|
Else
|
|
Replace totalcumul With .total
|
|
Endif
|
|
Endwith
|
|
Sele xTabCumulat
|
|
ENDSCAN
|
|
|
|
* Totaluri perioada pe coloane (locatii)
|
|
SELECT "TOTAL" as client, 0 as client_id, 1 as tip, 0 as totalcumul, SUM(total) as total, ;
|
|
SUM(p1) as p1,SUM(p2) as p2,SUM(p3) as p3,SUM(p4) as p4,SUM(p5) as p5,SUM(p6) as p6, ;
|
|
SUM(p7) as p7,SUM(p8) as p8,SUM(p9) as p9,SUM(p10) as p10,SUM(p11) as p11,SUM(p12) as p12, ;
|
|
SUM(p13) as p13,SUM(p14) as p14,SUM(p15) as p15,SUM(p16) as p16,SUM(p17) as p17,SUM(p18) as p18 ;
|
|
from xTabx ;
|
|
into cursor cTabPerioada
|
|
SELECT cTabPerioada
|
|
SCATTER NAME loTabPerioada
|
|
INSERT INTO xTabx FROM NAME loTabPerioada
|
|
USE IN (SELECT('cTabPerioada'))
|
|
|
|
* Totaluri cumulat de la inceputul anului pe coloane (locatii)
|
|
SELECT "TOTAL CUMULAT" as client, 0 as client_id, 2 as tip, SUM(total) as totalcumul, 0 as total, ;
|
|
SUM(p1) as p1,SUM(p2) as p2,SUM(p3) as p3,SUM(p4) as p4,SUM(p5) as p5,SUM(p6) as p6, ;
|
|
SUM(p7) as p7,SUM(p8) as p8,SUM(p9) as p9,SUM(p10) as p10,SUM(p11) as p11,SUM(p12) as p12, ;
|
|
SUM(p13) as p13,SUM(p14) as p14,SUM(p15) as p15,SUM(p16) as p16,SUM(p17) as p17,SUM(p18) as p18 ;
|
|
from xTabCumulat ;
|
|
into cursor cTabCumulat
|
|
SELECT cTabCumulat
|
|
SCATTER NAME loTabCumulat
|
|
INSERT INTO xTabx FROM NAME loTabCumulat
|
|
USE IN (SELECT('cTabCumulat'))
|
|
|
|
ENDIF && llSuccess
|
|
|
|
SELECT * FROM xTabx ORDER BY tip, client INTO CURSOR xTaby
|
|
USE IN (SELECT('xTabx'))
|
|
tcTabel = "XTABY"
|
|
tcColoane = [Client|Client Id|Agigea extern|Agigea intern|Basarabi extern|Basarabi intern|Luminita extern|Luminita intern|Medgidia extern|Medgidia intern|Ovidiu extern|Ovidiu intern|] + ;
|
|
[Cernavoda extern|Cernavoda intern|Navodari extern|Navodari intern|Front asteptare extern|Front asteptare intern|Senal extern|Senal intern|] + ;
|
|
[Total perioada|Total cumulat de la inceputul anului|Tip]
|
|
|
|
Case m.lnTip = NTIP_CHEIAJ And m.lnNrRaport = 4 && CHEIAJ: CLIENT,PORT, CAP., TRN, CP, LBP
|
|
TEXT TO lcSql TEXTMERGE NOSHOW
|
|
SELECT p1.denumire As client, MAX(TRIM(c.numar)) as contract, p2.denumire as beneficiar, l.denumire As port,
|
|
Round(Sum(CASE WHEN bd.um like '%TC%' then bv.CAP else 0 end), 0) As CAP,
|
|
Round(Sum(CASE WHEN bd.um like '%TRN%' then bv.TRN else 0 end), 0) As TRN,
|
|
Round(Sum(CASE WHEN bd.um like '%ML%' then bv.LUNG else 0 end), 0) As LUNG,
|
|
Round(Sum(CASE WHEN bd.um like '%CP%' then bv.HP else 0 end), 0) As CP,
|
|
Round(Sum(CASE WHEN bd.um like '%LBD%' or bd.um like '%LBP%' then bv.LBD else 0 end), 0) As LBD,
|
|
Round(Sum(CASE WHEN TRIM(bd.um) = 'ZI' then 1 else 0 end), 0) As ZI,
|
|
Round(Sum(CASE WHEN bd.um like '%TC%' then bd.valftva else 0 end), 0) As cap_val,
|
|
Round(Sum(CASE WHEN bd.um like '%TRN%' then bd.valftva else 0 end), 0) As trn_val,
|
|
Round(Sum(CASE WHEN bd.um like '%ML%' then bd.valftva else 0 end), 0) As lung_val,
|
|
Round(Sum(CASE WHEN bd.um like '%CP%' then bd.valftva else 0 end), 0) As cp_val,
|
|
Round(Sum(CASE WHEN bd.um like '%LBD%' or bd.um like '%LBP%' then bd.valftva else 0 end), 0) As lbd_val,
|
|
Round(Sum(CASE WHEN TRIM(bd.um) = 'ZI' then bd.valftva else 0 end), 0) As zi_val
|
|
From vanzari v join ips_voyages_vanzari vv on v.id_vanzare = vv.vz_id
|
|
join ips_berthings_vanzari bv on vv.id = bv.vv_id
|
|
join ips_berthing_details_vanzari bd on bv.id = bd.bv_id
|
|
join nom_parteneri p1 on v.id_part = p1.id_part
|
|
left join nom_parteneri p2 on v.id_beneficiar = p2.id_part
|
|
left join contracte c on v.id_ctr = c.id_ctr
|
|
left join nom_locatii l on bv.id_locatia = l.id
|
|
Where v.data_act between ?pdData1 and ?pdData2 and v.tip_factura = <<m.lnTip>> <<IIF(!EMPTY(m.pnIdClient), 'and v.id_part = ?pnIdClient', '')>> <<IIF(!EMPTY(m.pnIdCtrClient), 'and v.id_ctr = ?pnIdCtrClient', '')>>
|
|
Group By p1.denumire, p2.denumire, l.denumire
|
|
Order By 1, 2, 3, 4
|
|
ENDTEXT
|
|
|
|
tcTabel = "cRaportCheiaj"
|
|
tcColoane = [Client|Client ctr.|Beneficiar|Port|CAP|TRN|LUNG|CP|LBD|ZI|Valoare CAP|Valoare TRN|Valoare LUNG|Valoare CP|Valoare LBD|Valoare ZI]
|
|
llSucces = goExecutor.oExecuta(m.lcSql, m.tcTabel)
|
|
|
|
|
|
Case Inlist(m.lnTip, NTIP_APA, NTIP_CHIRII, NTIP_ALTE, NTIP_DIVERSE) And m.lnNrRaport = 1 && valori facturate pe client/prestatii (clienti pe randuri, prestatii pe coloane)
|
|
|
|
TEXT TO lcSql TEXTMERGE NOSHOW
|
|
Select p1.denumire As client, c.numar as contract, 'P' || TO_CHAR(r.id_articol) As id_articol, a.denumire As prestatia,
|
|
ROUND(Sum(r.valftva),0) As valtot_lei, Round(SUM(decode(v.in_valuta,1, r.valval, 0)), 0) As val_val
|
|
From ips_regdoc r Join vanzari v On r.id_vanzare = v.id_vanzare
|
|
join nom_parteneri p1 on v.id_part = p1.id_part
|
|
left join nom_parteneri p2 on v.id_beneficiar = p2.id_part
|
|
left join contracte c on v.id_ctr = c.id_ctr
|
|
Join nom_articole a On r.id_articol = a.id_articol
|
|
Where v.data_act between ?pdData1 and ?pdData2 and v.tip_factura = <<m.lnTip>> <<IIF(!EMPTY(m.pnIdClient), 'and v.id_part = ?pnIdClient', '')>> <<IIF(!EMPTY(m.pnIdCtrClient), 'and v.id_ctr = ?pnIdCtrClient', '')>>
|
|
Group By p1.denumire, c.numar, 'P' || TO_CHAR(r.id_articol), a.denumire
|
|
Order By 2, 3, 4
|
|
ENDTEXT
|
|
|
|
llSucces = goExecutor.oExecuta(m.lcSql, "cRaportVenituri1")
|
|
If m.llSucces
|
|
*!* TOTAL CUMULAT DE LA INCEPUTUL ANULUI
|
|
ldData1 = m.pdData1
|
|
pdData1 = Date(Year(m.ldData1), 1, 1)
|
|
llSucces = goExecutor.oExecuta(m.lcSql, "cRaportVenituri2")
|
|
pdData1 = m.ldData1
|
|
Endif
|
|
|
|
If m.llSucces
|
|
Select client, contract, Sum(valtot_lei) As valoare ;
|
|
From cRaportVenituri2 ;
|
|
Group By client, contract ;
|
|
Into Cursor totclient
|
|
|
|
Select id_articol, prestatia, Sum(valtot_lei) As valoare ;
|
|
From cRaportVenituri2 ;
|
|
Group By id_articol, prestatia ;
|
|
Into Cursor totprestatie
|
|
|
|
*!* lc_outfile, lu_struct, ll_closein, ll_therm, ln_rowfld, ;
|
|
*!* ln_colhead, ln_cellfld, ll_xtotal, ln_extfld1, ln_extfld2, ;
|
|
*!* ln_extfld3, ln_extfld4, ll_cnt, ll_avg, ll_min, ll_max, ;
|
|
*!* la_uniqcol, ln_rowcol, ln_colsort, ln_rowsort
|
|
|
|
Sele cRaportVenituri1
|
|
X = MATXTAB("XTABX", 2, .F., .F., 1, 3, 5, .T., 2)
|
|
Use In (Select('cRaportVenituri1'))
|
|
Use In (Select('cRaportVenituri2'))
|
|
Endif && llSuccess
|
|
|
|
If Used('XTABX')
|
|
*!* MARESC TOATE COLOANELE DE TIP NUMERIC LA N(16,2)
|
|
ALTER_TABLE_INCREASE_NUM('XTABX', 'N(16,2)')
|
|
|
|
Sele TOTPRESTATIE
|
|
Scan
|
|
lcField = Allt(TOTPRESTATIE.id_articol)
|
|
Select xtabx
|
|
If TYPE('xtabx.' + m.lcField) = 'U'
|
|
Alter Table xtabx Add Column &lcField N(16, 2)
|
|
Replace All &lcField With 0
|
|
Endif
|
|
Sele TOTPRESTATIE
|
|
Endscan
|
|
|
|
|
|
*!* TOTAL CUMULAT PE CLIENTI
|
|
Select xtabx
|
|
Alter Table xtabx Add Column totalcumul N(20, 2) Add Column tip N(1)
|
|
Sele totclient
|
|
Scan
|
|
Scatter Name OClient
|
|
With OClient
|
|
Select xtabx
|
|
Locate For Alltrim(client) = Alltrim(OClient.client) AND ALLTRIM(contract) = ALLTRIM(oclient.contract)
|
|
If !Found()
|
|
Insert Into xtabx (client, contract, totalcumul) Values (.client, .contract, .valoare)
|
|
Else
|
|
Replace totalcumul With .valoare
|
|
Endif
|
|
Endwith
|
|
Sele totclient
|
|
Endscan
|
|
|
|
|
|
*!* TOTAL PRESTATII DIN PERIOADA DATA
|
|
Sele xtabx
|
|
Scat Name OTOTAL Blank
|
|
For I = 3 To Fcount()
|
|
T = Field(I)
|
|
Calc Sum(&T)To OTOTAL.&T
|
|
Endfor
|
|
Appe Blank
|
|
Gath Name OTOTAL
|
|
Repl client With "TOTAL", totalcumul With 0, tip With 1
|
|
|
|
|
|
*!* TOTAL CUMULAT DE LA INCEPUTUL ANULUI PE PRESTATII
|
|
Select xtabx
|
|
Append Blank
|
|
Replace client With "TOTAL CUMULAT", tip With 2
|
|
|
|
Select totprestatie
|
|
lnTotalCumulat = 0
|
|
Scan
|
|
lnvaloare = valoare
|
|
lnTotalCumulat = lnTotalCumulat + lnvaloare
|
|
lcField = 'xtabx.' + Alltrim(id_articol)
|
|
Replace &lcField With m.lnValoare In xtabx
|
|
Endscan
|
|
Replace totalcumul With lnTotalCumulat In xtabx
|
|
|
|
tcColoane = ''
|
|
|
|
Sele xtabx
|
|
For I = 1 To Fcount()
|
|
LCFIELD = Uppe(Allt(Field(I)))
|
|
LPRESTATIA = LCFIELD
|
|
Sele TOTPRESTATIE
|
|
Loca For id_articol = LPRESTATIA
|
|
If Found()
|
|
LPRESTATIA = Alltrim(prestatia)
|
|
Else
|
|
If LCFIELD = 'XTOT'
|
|
LPRESTATIA = 'TOTAL PERIOADA'
|
|
Endif
|
|
If LCFIELD = 'TOTALCUMUL'
|
|
LPRESTATIA = 'TOTAL CUMULAT DE LA INCEPUTUL ANULUI'
|
|
Endif
|
|
Endif
|
|
tcColoane = tcColoane + Iif(I > 1, '|', '') + Alltrim(m.LPRESTATIA)
|
|
Sele xtabx
|
|
Endfor
|
|
|
|
tcTabel = "XTABY"
|
|
Select * From xtabx Order By tip, client Into Cursor XTABY
|
|
|
|
Use In (Select('XTABX'))
|
|
Use In (Select('TVENITURI'))
|
|
Use In (Select('TOTPRESTATIE'))
|
|
Use In (SELECT('totclient'))
|
|
Endif
|
|
|
|
|
|
|
|
|
|
Otherwise
|
|
AMESSAGEBOX("Nu este inca implementat.", 0 + 64, _Screen.Caption)
|
|
Endcase
|
|
|
|
* RETURN taHeader
|
|
Endproc && GET_DATE_VENITURI
|
|
|
|
Procedure DECO_LIST_VENITURI
|
|
Parameters tnTip, tnNrRaport, TEXCEL, TTITLU
|
|
&& tip prestatie, nr. situatie,daca e in excel, data1, data2, titlu
|
|
lcPerioada = GET_PERIOADA()
|
|
ldData1 = Ctod(Substr(lcPerioada, 1, 10))
|
|
ldData2 = Ctod(Substr(lcPerioada, At(";", lcPerioada) + 1))
|
|
|
|
lnIdCtrClient = 0
|
|
lcClient = ''
|
|
|
|
TTITLU = Iif(Empty(TTITLU), "SITUATIE VENITURI", TTITLU) + " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Do Case
|
|
Case tnTip = 0
|
|
If Inlist(tnNrRaport, 2, 3, 4, 5, 8, 9, 10, 11, 12) && TRANZIT > 2 DETALIU UN CLIENT, 3. TOTI CLIENTII, 4 PLATITOR/GRUPA MARFA, 5 PLATITOR/RELATIE/GRUPA MARFA, 8 PLATITOR/MARFA, 10 SELECTIE CONVOAIE XLS, 11 Indicatori fizici pe \<clienti (fara MIXT), 12 Indicatori fizici pe contracte, pe beneficiari (baza raport 11)
|
|
loCauta = caut_client_contract_activ()
|
|
lcClient = loCauta.denumire
|
|
lnIdClient = loCauta.id_part
|
|
lcContractClient = Alltrim(loCauta.numar)
|
|
lnIdCtrClient = loCauta.id_ctr
|
|
|
|
TTITLU = "SITUATIE PLATITOR " + lcClient + " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
If tnNrRaport = 10
|
|
TTITLU = "SELECTIE CONVOAIE " + lcClient + " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Endif
|
|
Endif
|
|
|
|
Case tnTip = 1
|
|
If tnNrRaport = 4 && CHEIAJ > INDICATORI,VALORI
|
|
loCauta = caut_client_contract_activ()
|
|
lcClient = loCauta.denumire
|
|
lnIdClient = loCauta.id_part
|
|
lcContractClient = Alltrim(loCauta.numar)
|
|
lnIdCtrClient = loCauta.id_ctr
|
|
|
|
TTITLU = "SITUATIE INDICATORI SI VALORI CHEIAJ " + lcClient + " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Endif
|
|
|
|
Case Inlist(tnTip, 2, 3, 5, 9)
|
|
If Inlist(m.tnNrRaport, 2, 3, 4) && raport facturat pe locatii, ani, doar anumite categorii de prestatii
|
|
ALEG_PRESTATII(m.tnTip) && creeaza cursor crsNomPrest
|
|
Endif
|
|
Endcase
|
|
|
|
Dimension LAHEADER[1] && HEADERUL CARE CONTINE NUMELE TABELULUI SI TITLURILE COLOANELOR PT AFISAREA IN EXCEL
|
|
= GET_DATE_VENITURI(tnTip, tnNrRaport, ldData1, ldData2, lnIdCtrClient, @LAHEADER)
|
|
lcTabel = LAHEADER[1]
|
|
|
|
If TEXCEL
|
|
If Used(m.lcTabel)
|
|
If m.tnTip = 0 && tranzit
|
|
lnReturn = LIST_VENITURI_EXCEL_XLSX(m.tnTip, m.lcTabel, @LAHEADER, m.TTITLU)
|
|
If m.lnReturn = 0
|
|
= LIST_VENITURI_EXCEL(m.tnTip, m.lcTabel, @LAHEADER, m.TTITLU)
|
|
Endif
|
|
Else
|
|
= LIST_VENITURI_EXCEL(tnTip, m.lcTabel, @LAHEADER, TTITLU)
|
|
Endif
|
|
Endif
|
|
Else
|
|
Do Case
|
|
Case tnTip = 0
|
|
Do Case
|
|
Case tnNrRaport = 2
|
|
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE PLATITOR "] + lcClient + ["]
|
|
PTITLU2 = " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Set Cent Off
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
Set Order To Ct
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_TRANZIT2")
|
|
Set Cent On
|
|
Release PTITLU1, PTITLU2
|
|
Endif
|
|
Case tnNrRaport = 3
|
|
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE VENITURI TRANZIT]
|
|
PTITLU2 = "DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Set Cent Off
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_TRANZIT3")
|
|
|
|
Set Cent On
|
|
Release PTITLU1, PTITLU2
|
|
Endif
|
|
Case Inlist(tnNrRaport, 4, 8)
|
|
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE PLATITORI - MARFA]
|
|
PTITLU2 = "DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Set Cent Off
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_TRANZIT4")
|
|
|
|
Set Cent On
|
|
Release PTITLU1, PTITLU2
|
|
Endif
|
|
Case Inlist(tnNrRaport, 5, 9)
|
|
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE PLATITORI - MARFA - RELATIA]
|
|
PTITLU2 = "DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Set Cent Off
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_TRANZIT5")
|
|
|
|
Set Cent On
|
|
Release PTITLU1, PTITLU2
|
|
Endif
|
|
|
|
Case tnNrRaport = 7
|
|
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE PLATITORI - RELATIE - TONE CAPACITATE]
|
|
PTITLU2 = "DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Set Cent Off
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_TRANZIT7")
|
|
|
|
Set Cent On
|
|
Release PTITLU1, PTITLU2
|
|
Endif
|
|
|
|
Case tnNrRaport = 11
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE VENITURI TRANZIT]
|
|
PTITLU2 = "DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Set Cent Off
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_TRANZIT11")
|
|
Set Cent On
|
|
Release PTITLU1, PTITLU2
|
|
Endif
|
|
|
|
Case tnNrRaport = 12
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE VENITURI TRANZIT]
|
|
PTITLU2 = "DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
Set Cent Off
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_TRANZIT12")
|
|
Set Cent On
|
|
Release PTITLU1, PTITLU2
|
|
Endif
|
|
|
|
|
|
Other
|
|
*
|
|
Endcase
|
|
Case tnTip = 1
|
|
|
|
Do Case
|
|
Case tnNrRaport = 4
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE INDICATORI SI VALORI CHEIAJ ] + + lcClient
|
|
PTITLU2 = " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_CHEIAJ_INDICATORI")
|
|
Endif
|
|
Case Inlist(tnNrRaport, 1, 2, 3)
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE VENITURI DIN CHEIAJ]
|
|
PTITLU2 = " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_CHEIAJ2")
|
|
Endif
|
|
Case tnNrRaport = 5
|
|
If !Empty(m.lcTabel)
|
|
Public PTITLU1, PTITLU2
|
|
PTITLU1 = [SITUATIE VENITURI DIN CHEIAJ]
|
|
PTITLU2 = " DE LA " + Dtoc(ldData1) + " LA " + Dtoc(ldData2)
|
|
|
|
Keyboard'{CTRL+F10}'
|
|
Sele (m.lcTabel)
|
|
= LISTAREUSERREPORT(m.lcTabel, "FRX", "SIT_CHEIAJ5")
|
|
|
|
Endif
|
|
|
|
Other
|
|
*
|
|
Endcase
|
|
Endcase
|
|
Endif
|
|
If Used(m.lcTabel)
|
|
Use In (m.lcTabel)
|
|
Endif
|
|
Endproc && DECO_LIST_VENITURI
|
|
|
|
***-----------------------------------------------------------------------------------------------
|
|
Procedure DECO_LIST_VENITURI_EXCEL
|
|
&& perioada pe care vreau veniturile
|
|
lcPerioada = GET_PERIOADA()
|
|
ldData1 = Ctod(Left(lcPerioada, At(";", lcPerioada) - 1))
|
|
ldData2 = Ctod(Right(lcPerioada, At(";", lcPerioada) - 1))
|
|
|
|
OPRESTATIA = GET_PRESTATIA() && 0,1,2,3,4,5
|
|
LITIP = OPRESTATIA.tip
|
|
lcTitlu = "VENITURI " + Allt(OPRESTATIA.prestatia) + " " + Strtran(lcPerioada, ";", " - ")
|
|
LNRRAP = 1
|
|
|
|
Dime LAHEADER[1]
|
|
LNRAP = 1 && tipul 1 de situatie
|
|
= GET_DATE_VENITURI(LITIP, LNRRAP, ldData1, ldData2, 0, @LAHEADER)
|
|
|
|
lcTabel = LAHEADER[1]
|
|
|
|
|
|
Release OPRESTATIA
|
|
If !Empty(m.lcTabel)
|
|
* list_venituri_excel_xlsx nu face xlsx pentru ca nu gaseste driverul x86 si intra pe ramura copy to xls5 fara header
|
|
* nu pune headerul in fisier
|
|
*!* lnReturn = LIST_VENITURI_EXCEL_XLSX(LITIP, m.lcTabel, @LAHEADER, lcTitlu)
|
|
*!* IF m.lnReturn = 0
|
|
= LIST_VENITURI_EXCEL(LITIP, m.lcTabel, @LAHEADER, lcTitlu)
|
|
*!* Endif
|
|
Endif
|
|
|
|
If Used(m.lcTabel)
|
|
Use In (m.lcTabel)
|
|
Endif
|
|
|
|
Endproc && DECO_LIST_VENITURI_EXCEL
|
|
|
|
Procedure LIST_VENITURI_EXCEL
|
|
Param tnTip, TTABEL, taHeader, TTITLU
|
|
|
|
Local I, laFields[1], LCFIELD, lcFormat, lcType, lnDecimals, lnField, lnFields, lnSize, luValue
|
|
*:Global I, LCANTET, LCFIS, LCHEADER, LDATE, NRCOL, TTITLU
|
|
LCFIS = Allt(LOC) + "\" + Allt(NFSCURT) + "\TEMPO\VENITURI_" + Sys(2) + ".XLS"
|
|
LCFIS = Strtran(LCFIS, '\\', '\')
|
|
|
|
Set Textmerge On To (LCFIS) Noshow
|
|
If Empty(TTITLU)
|
|
TTITLU = ""
|
|
Endif
|
|
|
|
LCANTET = Uppe(Allt(TTITLU)) + CRLF
|
|
\
|
|
\\<<LCANTET>>
|
|
\
|
|
LCHEADER = ""
|
|
NRCOL = 0
|
|
If !Empty(taHeader)
|
|
NRCOL = Alen(taHeader, 1)
|
|
Endif
|
|
Sele (TTABEL)
|
|
If NRCOL - 1 < Fcount()
|
|
|
|
For I = 1 To Fcount()
|
|
LCHEADER = LCHEADER + Upper(Allt(Field(I))) + Tab
|
|
Endfor
|
|
Else
|
|
|
|
For I = 2 To NRCOL
|
|
LCHEADER = LCHEADER + Uppe(Allt(taHeader[I])) + Tab
|
|
Endfor
|
|
Endif
|
|
LCHEADER = LCHEADER + CRLF
|
|
|
|
\\<<LCHEADER>>
|
|
|
|
Sele (TTABEL)
|
|
lnFields = Afields(laFields, m.TTABEL)
|
|
Scan
|
|
LDATE = ""
|
|
For lnField = 1 To m.lnFields
|
|
LCFIELD = laFields[m.lnField, 1]
|
|
luValue = Evaluate(m.LCFIELD)
|
|
lcType = laFields[m.lnField, 2]
|
|
lnSize = laFields[m.lnField, 3]
|
|
lnDecimals = laFields[m.lnField, 4]
|
|
Do Case
|
|
Case lcType = 'C'
|
|
LDATE = LDATE + m.luValue + Tab
|
|
Case lcType = 'N'
|
|
LDATE = LDATE + Str(m.luValue, m.lnSize, m.lnDecimals) + Tab
|
|
Otherwise
|
|
LDATE = LDATE + Transform(m.luValue, "") + Tab
|
|
Endcase
|
|
Endfor
|
|
LDATE = LDATE + CRLF
|
|
\\<<LDATE>>
|
|
Endscan
|
|
Set Textmerge To
|
|
|
|
Wait Window "Se deschide Excel..." Nowait
|
|
|
|
|
|
open_default_app(m.LCFIS)
|
|
|
|
Endproc && list_venituri_excel
|
|
|
|
|
|
************************************************************************************************
|
|
Procedure GET_PERIOADA
|
|
Local LD1, LD2
|
|
|
|
LRET = ";"
|
|
LD1 = Date() - 7
|
|
LD2 = Date()
|
|
OBD = Crea("frm_GET_perioada")
|
|
With OBD
|
|
.TXTDATA1.ControlSource = "LD1"
|
|
.TXTDATA2.ControlSource = "LD2"
|
|
Endwith
|
|
OBD.Show(1)
|
|
|
|
LRET = Dtoc(LD1) + ";" + Dtoc(LD2)
|
|
|
|
Return LRET
|
|
|
|
Endproc && get_perioada
|
|
|
|
************************************************************************************************
|
|
Procedure GET_PRESTATIA
|
|
|
|
Create Cursor TPRESTATIA (tip I(6), prestatia C(30))
|
|
Insert Into TPRESTATIA (tip, prestatia) Values (0, "Tranzit")
|
|
|
|
Insert Into TPRESTATIA (tip, prestatia) Values(1, "Cheiaj nave")
|
|
Insert Into TPRESTATIA (tip, prestatia) Values(2, "Diverse")
|
|
Insert Into TPRESTATIA (tip, prestatia) Values(3, "Chirii si utilitati")
|
|
*!* INSERT INTO CURSOR VALUES(4,"Pilotaj")
|
|
Insert Into TPRESTATIA (tip, prestatia) Values(5, "Furnizare apa")
|
|
*WAIT WIND '1'
|
|
Sele TPRESTATIA
|
|
Go Top
|
|
*BROW
|
|
|
|
OBP = Crea("FRM_GET_PRESTATIA")
|
|
With OBP.GRID1
|
|
.RecordSource = "tprestatia"
|
|
.CPRESTATIA.ControlSource = "prestatia"
|
|
Endwith
|
|
OBP.Show(1)
|
|
|
|
*WAIT WIND '2'
|
|
Sele TPRESTATIA
|
|
Scat Name OPRESTATIA
|
|
LTIP = tip
|
|
Use In TPRESTATIA
|
|
|
|
Return OPRESTATIA && tipul prestatiei alese
|
|
Endproc && get_prestatia
|
|
|
|
|
|
*!* MARESC TOATE COLOANELE DE TIP NUMERIC LA N(16,2)
|
|
*!* MARIUS MUTU
|
|
*!* 04.05.2006
|
|
Procedure ALTER_TABLE_INCREASE_NUM
|
|
Lparameters tcAlias, tcPrecision
|
|
|
|
Local lnFields, I, LCFIELD, lcAlter
|
|
Declare laFields[1, 1]
|
|
|
|
If Used(tcAlias)
|
|
lnFields = Afields(laFields, tcAlias)
|
|
For I = 1 To lnFields
|
|
If Inlist(laFields[i, 2], 'N', 'F', 'I', 'B', 'Y')
|
|
LCFIELD = laFields[i, 1]
|
|
lcAlter = [alter table ] + tcAlias + [ alter column ] + LCFIELD + + [ ] + tcPrecision
|
|
&lcAlter
|
|
Endif
|
|
Endfor
|
|
Endif
|
|
|
|
Endproc && ALTER_TABLE_INCREASE_NUM |