How to export statistics (best products) into excel?

[Housy]

Hi,

is it possible to export best products into excel?

Thank you,
Housy

[Housy]

I have found the bestproducts file but i have no idea how this query works to be honest, because this php file has classes involved and complex queries, so i'm gonna need some help.

I just want to create one php file and get all the data for best products.

How to combine these two queries into one and print out all data for best products?

public function getTotalCount($dateBetween)
   {
       $result = Db::getInstance()->getRow('
       SELECT COUNT(DISTINCT p.`id_product`) totalCount
       FROM `'._DB_PREFIX_.'product` p
       LEFT JOIN '._DB_PREFIX_.'order_detail od ON od.product_id = p.id_product
       LEFT JOIN '._DB_PREFIX_.'orders o ON od.id_order = o.id_order
       WHERE p.active = 1 AND o.valid = 1
       AND o.invoice_date BETWEEN '.$dateBetween);
       return $result['totalCount'];
   }

   public function getData()
   {
       $dateBetween = $this->getDate();
       $arrayDateBetween = explode(' AND ', $dateBetween);
       $this->_totalCount = $this->getTotalCount($dateBetween);

       $this->_query = '
       SELECT p.reference, p.id_product, pl.name, ROUND(AVG(od.product_price / c.conversion_rate), 2) as avgPriceSold, 
           IFNULL((SELECT SUM(pa.quantity) FROM '._DB_PREFIX_.'product_attribute pa WHERE pa.id_product = p.id_product GROUP BY pa.id_product), p.quantity) as quantity,
           IFNULL(SUM(od.product_quantity), 0) AS totalQuantitySold,
           ROUND(IFNULL(IFNULL(SUM(od.product_quantity), 0) / (1 + LEAST(TO_DAYS('.$arrayDateBetween[1].'), TO_DAYS(NOW())) - GREATEST(TO_DAYS('.$arrayDateBetween[0].'), TO_DAYS(p.date_add))), 0), 2) as averageQuantitySold,
           ROUND(IFNULL(SUM((od.product_price * od.product_quantity) / c.conversion_rate), 0), 2) AS totalPriceSold,
           (
               SELECT IFNULL(SUM(pv.counter), 0)
               FROM '._DB_PREFIX_.'page pa
               LEFT JOIN '._DB_PREFIX_.'page_viewed pv ON pa.id_page = pv.id_page
               LEFT JOIN '._DB_PREFIX_.'date_range dr ON pv.id_date_range = dr.id_date_range
               WHERE pa.id_object = p.id_product AND pa.id_page_type = 1
               AND dr.time_start BETWEEN '.$dateBetween.'
               AND dr.time_end BETWEEN '.$dateBetween.'
           ) AS totalPageViewed
       FROM '._DB_PREFIX_.'product p
       LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = '.intval($this->getLang()).')
       LEFT JOIN '._DB_PREFIX_.'order_detail od ON od.product_id = p.id_product
       LEFT JOIN '._DB_PREFIX_.'orders o ON od.id_order = o.id_order
       LEFT JOIN '._DB_PREFIX_.'currency c ON o.id_currency = c.id_currency
       WHERE p.active = 1 AND o.valid = 1
       AND o.invoice_date BETWEEN '.$dateBetween.'
       GROUP BY od.product_id';

I would be grateful for any help, because i'm not good in php code and mysql and i really need this, it's urgent.

Thank you,
Housy

[perusi]

did you managed to do it?