SELECT 
    p.ref AS `Ref.`,
    s.nom AS `Terti`,
    d.nom AS `Judet`,
    DATE_FORMAT(p.datep, '%Y-%m-%d') AS `Data oferta`,
    r.label AS `Canal de vanzare`,
    pextra.categorieprodus AS `Categorie de produs`,
    FORMAT(p.total_ht, 2, 'ro_RO') AS `Suma (fara taxe)`,
    FORMAT(p.total_ttc, 2, 'ro_RO') AS `Suma (cu TVA)`,
    CONCAT(u.firstname, ' ', u.lastname) AS `Rep. Vanzari`,
    FORMAT(COALESCE(SUM(CASE WHEN c.fk_statut = 1 THEN c.total_ht ELSE 0 END), 0), 2, 'ro_RO') AS `Comandat (fara TVA)`,
    FORMAT(COALESCE(SUM(CASE WHEN c.fk_statut = 1 THEN c.total_ttc ELSE 0 END), 0), 2, 'ro_RO') AS `Comandat (cu TVA)`,
    GROUP_CONCAT(DISTINCT CASE WHEN c.fk_statut = 1 THEN c.ref ELSE NULL END SEPARATOR ', ') AS `Referinte Comenzi`,
    FORMAT(COALESCE(SUM(CASE WHEN f.fk_statut != 0 THEN f.total_ht ELSE 0 END), 0), 2, 'ro_RO') AS `Facturat (fara TVA)`,
    FORMAT(COALESCE(SUM(CASE WHEN f.fk_statut != 0 THEN f.total_ttc ELSE 0 END), 0), 2, 'ro_RO') AS `Facturat (cu TVA)`,
    FORMAT(COALESCE(SUM(pf.amount), 0), 2, 'ro_RO') AS `Platit`,
    FORMAT(COALESCE(SUM(CASE WHEN c.fk_statut = 1 THEN c.total_ttc ELSE 0 END) - SUM(CASE WHEN f.fk_statut != 0 THEN f.total_ttc ELSE 0 END), 0), 2, 'ro_RO') AS `Ramas de facturat`,
    FORMAT(COALESCE(SUM(CASE WHEN f.fk_statut != 0 THEN f.total_ttc ELSE 0 END) - SUM(pf.amount), 0), 2, 'ro_RO') AS `Ramas de plata`,
    GROUP_CONCAT(DISTINCT f.ref SEPARATOR ', ') AS `Referinte Facturi`,
    CASE 
        WHEN p.fk_statut = 0 THEN 'Brouillon (Draft)'
        WHEN p.fk_statut = 1 THEN 'Validata'
        WHEN p.fk_statut = 2 THEN 'Semnata'
        WHEN p.fk_statut = 3 THEN 'Facturata'
        ELSE 'Necunoscut'
    END AS `Status`

FROM
    llx_propal p
INNER JOIN
    llx_societe s ON p.fk_soc = s.rowid
LEFT JOIN
    llx_c_departements d ON s.fk_departement = d.rowid
INNER JOIN
    llx_user u ON p.fk_user_author = u.rowid
LEFT JOIN
    llx_c_input_reason r ON p.fk_input_reason = r.rowid
LEFT JOIN
    llx_propal_extrafields pextra ON p.rowid = pextra.fk_object
LEFT JOIN
    llx_element_element ee_cmd ON ee_cmd.fk_source = p.rowid AND ee_cmd.sourcetype = 'propal' AND ee_cmd.targettype = 'commande'
LEFT JOIN
    llx_commande c ON ee_cmd.fk_target = c.rowid AND c.fk_statut = 1
LEFT JOIN
    llx_element_element ee_fact ON ee_fact.fk_source = c.rowid AND ee_fact.sourcetype = 'commande' AND ee_fact.targettype = 'facture'
LEFT JOIN
    llx_facture f ON ee_fact.fk_target = f.rowid AND f.fk_statut != 0
LEFT JOIN
    llx_paiement_facture pf ON f.rowid = pf.fk_facture
LEFT JOIN
    llx_paiement pmt ON pf.fk_paiement = pmt.rowid

WHERE
    YEAR(p.datep) IN (2025)
    AND p.ref NOT LIKE '(PROV%'

GROUP BY
    p.ref, s.nom, d.nom, p.datep, p.total_ht, p.total_ttc, r.label, u.firstname, u.lastname, p.fk_statut, pextra.categorieprodus

ORDER BY
    p.ref;
