select 
        p.code, p.code_erp as code_erp_produit, p.libelle as produit, p.code_a_barre, g.libelle as gamme, 
        m.libelle as marque, f.libelle as famille, sf.libelle as sousfamille, en.type, 
        ROUND((lc.total - ((lc.remise * lc.total)/100)) - (lc.quantite * lc.prix_achat_ttc), 3) as total_marge, 
        lc.total - ((lc.remise * lc.total)/100) as total_net, lc.pu_ht * lc.quantite as total_ht, 
        (lc.pu_ht * lc.quantite) - ((lc.remise * (lc.pu_ht * lc.quantite))/100) as total_ht_net, 
        ((lc.remise * lc.total)/100) as remise, lc.pu_ht as pu_ht, lc.pu_ttc as pu_ttc, 
        ((lc.taux_tva * lc.pu_ttc)/100) as p_tva, (lc.quantite / p.colisage) as Carton, lc.taux_tva, 
        lc.type as type_ligne, p.colisage, lc.total, lc.entetecommercial_code, lc.quantite, 
        DATE_FORMAT(en.date, "%Y-%m-%d") as date_creation, CONCAT(lc.remise, " ","%") as taux_remise, 
        c.adresse_facturation, c.rc, c.matricule_fiscale, c.code_a_barre as cab_client, 
        en.remise_commande, en.remise_pied, CONCAT(u.nom," ",u.prenom) as  commercial, 
        CONCAT(ucv.nom," ",ucv.prenom) as  user_validate_cadeaux, en.commentaire, c.code as code_client, 
        u.code as code_commercial, lc.prix_achat_ttc, c.code_erp as code_erp_client, 
        CONCAT(c.nom," ",c.prenom) as  client, c.magasin, reg.libelle as region, gv.libelle as gouvernorat, 
        cat.libelle as categorie, act.libelle as activite, four.libelle as fournisseur, c.presentoire, 
        en.longitude, en.latitude, en.type_cadeaux, c.longitude as longitudeClient, 
        c.latitude as latitudeClient, zn.libelle as zone, ss.nom as soussociete, ss.code_elastic 

from lignecommercials as lc 
left join entetecommercials as en on en.code = lc.entetecommercial_code 
left join users as u on u.code = en.commercial_code 
left join users as ucv on ucv.code = en.user_cadeaux_valide 
left join clients as c on c.code = en.client_code 
left join soussocietes as ss on ss.code = en.soussociete_code 
left join categoriecommercials as cat on cat.code = c.categoriecommercial_code 
left join activites as act on act.code = c.activite_code 
left join regions as reg on reg.code = c.region 
left join gouvernorats as gv on gv.code = c.gouvernorat 
left join zones as zn on zn.code = c.zone 
left join produits as p on lc.produit_code = p.code 
left join gammes as g on p.gamme_code = g.code 
left join marques as m on p.marque_code = m.code 
left join familles as f on p.famille_code = f.code 
left join fournisseurs as four on p.fournisseur_code = four.code 
left join sousfamilles as sf on p.sousfamille_code = sf.code 

where en.type in ('bl', 'avoir', 'cadeaux') and date(en.date)>= '2024-09-07' and date(en.date)<= "2025-08-31" order by lc.date_creation desc