select
sc_.id_fisa_sursa,
ffur_.serie_factura, no_crypt_value( fpj_.nume,fpj_.crypt,'${decryptKey}') as furnizor,
'''' || fm_furnizor.time_document as "data receptie",
fm_furnizor.document as "document receptie",
sum(fm_furnizor.intrata) as "cantitate receptionata",
fm_furnizor.buy_price as "pret receptie fara tva",
sum(fm_furnizor.intrata) * fm_furnizor.buy_price as "valoare receptie fara tva",
'''' || o_.closedate::date  as "data vanzare",
'''' || o_.closedate::time  as "ora vanzare",
'Comanda ' || o_.human_orderid as "document vanzare",
id_product,
p_.label as "nume produs",
cota_tva,
sum(quantity_float) as "cantitate iesita",
ppu_original as "pret vanzare intreg cu tva" ,
ppu_original / (1+cota_tva) as "pret vanzare intreg fara tva" ,
sum(quantity_float) * ppu_original / (1+cota_tva) as "valoare vanzare intreaga fara tva" ,
ppu as "pret vanzare efectiv (dupa discount) cu tva",
ppu / (1+cota_tva) as "pret vanzare efectiv (dupa discount) fara tva",
ppu * sum(quantity_float) as "valoare vanzare efectiva (dupa discount) cu tva",
ppu * sum(quantity_float)/(1+cota_tva) as "valoare vanzare efectiva (dupa discount) fara tva",
sum(quantity_float) * discount as "valoare discount cu tva",
(discount * sum(quantity_float))/ (ppu_original * sum(quantity_float)) * 100 as "procent discount efectiv",
sum(fm_.val_iesita_pa) as "gestiune val iesita pa",
sum(fm_.val_iesita_pv) as "gestiune val iesita pv" ,
ppu * sum(quantity_float) / (1+cota_tva) - sum(fm_.val_iesita_pa) as "valoare adaos fara tva" ,
no_crypt_value( pj_.nume,pj_.crypt,'${decryptKey}') as "nume client"
from orders o_ inner join sales s_ on o_.id = s_.id_order
inner join products p_ on s_.id_product = p_.id
inner join documente d_ on d_.id_order = o_.id
left outer join fisa_magazie fm_ on (o_.id = fm_.id_document and fm_.type_document = 3 and s_.id = fm_.id_document_item )
left outer join stock_consum sc_ on (fm_.id = sc_.id_fisa_consum)
left outer join fisa_magazie fm_furnizor on (sc_.id_fisa_sursa = fm_furnizor.id and fm_.id_stock = fm_furnizor.id_stock )
left outer join facturi_emise_orders feo_ on o_.id = feo_.id_order
left outer join facturi_emise fe_ on feo_.id_factura = fe_.id
left outer join clienti c_ on fe_.id_client = c_.id
left outer join persoane_juridice pj_ on c_.idpersjuridica = pj_.id
left outer join documente di_ on fm_furnizor.id_document = di_.id_factura
left outer join facturi ffur_ on di_.id_factura = ffur_.id
left outer join clienti f_ on ffur_.id_furnizor = f_.id
left outer join persoane_juridice fpj_ on f_.idpersjuridica = fpj_.id
where 1=1
group by o_.id, o_.human_orderid, id_product , ppu, discount, ppu_original, cota_tva, p_.label, o_.closedate, fm_furnizor.time_document, fm_furnizor.document, fm_furnizor.buy_price, fm_furnizor.id_stock, sc_.id,
feo_.id_Factura, c_.id, pj_.id, no_crypt_value( pj_.nume,pj_.crypt,'${decryptKey}') , ffur_.serie_factura, no_crypt_value( fpj_.nume,fpj_.crypt,'${decryptKey}') , sc_.id_fisa_sursa
order by o_.human_orderid
