SELECT
    c.ref AS `Ref.`,
    s.nom AS `Terti`,
    d.nom AS `Judet`,
    DATE_FORMAT(c.date_creation, '%Y-%m-%d') AS `Data comanda`,
    r.label AS `Canal de vanzare`,
    cextra.categorieprodus AS `Categorie de produs`,
    FORMAT(c.total_ht, 2, 'ro_RO') AS `Suma (fara taxe)`,
    FORMAT(c.total_ttc, 2, 'ro_RO') AS `Suma (cu TVA)`,
    CONCAT(u.firstname, ' ', u.lastname) AS `Rep. Vanzari`,
    FORMAT(COALESCE(SUM(f.total_ht), 0), 2, 'ro_RO') AS `Facturat (fara TVA)`,
    FORMAT(COALESCE(SUM(f.total_ttc), 0), 2, 'ro_RO') AS `Facturat (cu TVA)`,
    FORMAT(COALESCE(SUM(pf.amount), 0), 2, 'ro_RO') AS `Platit`,
    FORMAT(COALESCE(SUM(f.total_ttc) - SUM(pf.amount), 0), 2, 'ro_RO') AS `Ramas de plata`,
    GROUP_CONCAT(DISTINCT f.ref SEPARATOR ', ') AS `Referinte Facturi`,
    CASE 
        WHEN c.fk_statut = 1 THEN 'Validat'
        WHEN c.fk_statut = 2 THEN 'In curs'
        WHEN c.fk_statut = 3 THEN 'Livrate'
        WHEN c.fk_statut = 4 THEN 'Anulata'
        ELSE 'Necunoscut'
    END AS `Status`

FROM
    llx_commande c
INNER JOIN
    llx_societe s ON c.fk_soc = s.rowid
LEFT JOIN
    llx_c_departements d ON s.fk_departement = d.rowid
INNER JOIN
    llx_user u ON c.fk_user_author = u.rowid
LEFT JOIN
    llx_c_input_reason r ON c.fk_input_reason = r.rowid
LEFT JOIN
    llx_commande_extrafields cextra ON c.rowid = cextra.fk_object
LEFT JOIN
    llx_element_element ee ON ee.fk_source = c.rowid AND ee.sourcetype = 'commande' AND ee.targettype = 'facture'
LEFT JOIN
    llx_facture f ON ee.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 p ON pf.fk_paiement = p.rowid

WHERE
    YEAR(c.date_valid) = 2025
    AND c.ref NOT LIKE '(PROV%'

GROUP BY
    c.ref, s.nom, d.nom, c.date_creation, c.total_ht, c.total_ttc, r.label, u.firstname, u.lastname, c.fk_statut, cextra.categorieprodus

ORDER BY
    c.ref;
