SELECT 
                ss.code_elastic as Distributeur, p.ordre as Ordre, m.commercial_code as Code_commercial, CONCAT(u.nom," ", u.prenom) as Commercial, 
                p.code as Code_produit, p.libelle as Produit, p.colisage as Colisage, p.marge as Marge,
               MAX(DATE_FORMAT(m.date, '%Y-%m-%d')) as Date_mvt,
                (SELECT mpr.prix_ttc 
                          FROM mouvements as mpr 
                          WHERE mpr.produit_code = p.code and mpr.commercial_code = m.commercial_code and 
                                DATE_FORMAT(mpr.date, '%Y-%m-%d') =  MAX(DATE_FORMAT(m.date, '%Y-%m-%d')) 
                          order by mpr.type ASC
                          LIMIT 1)  as Prix_TTC,
                p.prix_ttc as Prix_TTC_instantane,
                SUM(IF(m.type='chargement', m.quantite,'0')) as Chargement,
                SUM(IF(m.type='retour' OR m.type='retourI', m.quantite,'0')) as Retour,
                SUM(IF(m.type='casse', m.quantite,'0')) as Casse,
                SUM(IF(m.type='dlc', m.quantite,'0')) as Dlc,
                SUM(IF(m.type='gratuiteC', m.quantite,'0')) as gratuite,

                -- Vente reel
                SUM(IF(m.type='chargement', m.quantite,-m.quantite)) as Vente_Reel,
                SUM(IF(m.type='chargement', m.quantite * m.prix_ttc,- m.quantite * m.prix_ttc)) as Chiffre_Vente_Reel,

                SUM(IF(m.type='chargement', m.quantite * p.marge,-m.quantite * p.marge)) as Commission,

                ROUND(SUM(IF(m.type='retour' OR m.type='retourI', m.quantite,'0')) * 100 / SUM(IF(m.type='chargement', m.quantite,'0')),0) as RC,

                SUM(IF(m.type='chargement', m.quantite * m.prix_ttc,'0')) as Chiffre_Chargement,
                SUM(IF(m.type='retour' OR m.type='retourI', m.quantite * m.prix_ttc,'0')) as Chiffre_retour,
                SUM(IF(m.type='casse', m.quantite * m.prix_ttc,'0')) as Chiffre_casse,
                SUM(IF(m.type='dlc', m.quantite * m.prix_ttc,'0')) as Chiffre_dlc,
                SUM(IF(m.type='gratuiteC', m.quantite * m.prix_ttc,'0')) as Chiffre_gratuite
            FROM produits as p
            LEFT JOIN mouvements as m ON m.produit_code = p.code
            LEFT JOIN soussocietes as ss ON m.soussociete_code = ss.code
            LEFT JOIN users as u ON m.commercial_code = u.code
            WHERE  m.etat <> 'refused' and m.type in ('chargement', 'retour', 'retourI', 'casse', 'dlc', 'gratuiteC')
            GROUP BY (DATE_FORMAT(m.date, '%Y-%m-%d')), p.code, m.commercial_code
            ORDER BY MAX(DATE_FORMAT(m.date, '%Y-%m-%d')) DESC, p.ordre ASC