Stats do not show orders and carts correctly TB 1.0.4



  • Stats only show correctly visits to the page, orders are 0 and average cart value is 0 too



  • Any idea?



  • Hello,

    I think only orders that have invoice counts. Please check this solutions:

    touch file /override/controllers/admin/AdminStatsController.php with below code. After this delete /cache/class_index.php.

    <?php
    class AdminStatsController extends AdminStatsControllerCore
    {
    		public static function getTotalSales($date_from, $date_to, $granularity = false)
        {
            if ($granularity == 'day') {
                $sales = array();
                $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
    			SELECT LEFT(`date_add`, 10) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o').'
    			GROUP BY LEFT(`date_add`, 10)');
                foreach ($result as $row) {
                    $sales[strtotime($row['date'])] = $row['sales'];
                }
                return $sales;
            } elseif ($granularity == 'month') {
                $sales = array();
                $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
    			SELECT LEFT(`date_add`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o').'
    			GROUP BY LEFT(`date_add`, 7)');
                foreach ($result as $row) {
                    $sales[strtotime($row['date'].'-01')] = $row['sales'];
                }
                return $sales;
            } else {
                return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
    			SELECT SUM(total_paid_tax_excl / o.conversion_rate)
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o'));
            }
        }
        
        public static function getOrders($date_from, $date_to, $granularity = false)
        {
            if ($granularity == 'day') {
                $orders = array();
                $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
    			SELECT LEFT(`date_add`, 10) as date, COUNT(*) as orders
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o').'
    			GROUP BY LEFT(`date_add`, 10)');
                foreach ($result as $row) {
                    $orders[strtotime($row['date'])] = $row['orders'];
                }
                return $orders;
            } elseif ($granularity == 'month') {
                $orders = array();
                $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
    			SELECT LEFT(`date_add`, 7) as date, COUNT(*) as orders
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o').'
    			GROUP BY LEFT(`date_add`, 7)');
                foreach ($result as $row) {
                    $orders[strtotime($row['date'].'-01')] = $row['orders'];
                }
                return $orders;
            } else {
                $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
    			SELECT COUNT(*) as orders
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o'));
            }
    
            return $orders;
        }
        
        public static function getPurchases($date_from, $date_to, $granularity = false)
        {
            if ($granularity == 'day') {
                $purchases = array();
                $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
    			SELECT
    				LEFT(`date_add`, 10) as date,
    				SUM(od.`product_quantity` * IF(
    					od.`purchase_supplier_price` > 0,
    					od.`purchase_supplier_price` / `conversion_rate`,
    					od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
    				)) as total_purchase_price
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o').'
    			GROUP BY LEFT(`date_add`, 10)');
                foreach ($result as $row) {
                    $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
                }
                return $purchases;
            } else {
                return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
    			SELECT SUM(od.`product_quantity` * IF(
    				od.`purchase_supplier_price` > 0,
    				od.`purchase_supplier_price` / `conversion_rate`,
    				od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
    			)) as total_purchase_price
    			FROM `'._DB_PREFIX_.'orders` o
    			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
    			LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    			WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    			'.Shop::addSqlRestriction(false, 'o'));
            }
        }
    
        public static function getExpenses($date_from, $date_to, $granularity = false)
        {
            $expenses = ($granularity == 'day' ? array() : 0);
    
            $orders = Db::getInstance()->ExecuteS('
    		SELECT
    			LEFT(`date_add`, 10) as date,
    			total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl,
    			total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl,
    			o.module,
    			a.id_country,
    			o.id_currency,
    			c.id_reference as carrier_reference
    		FROM `'._DB_PREFIX_.'orders` o
    		LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address
    		LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier
    		LEFT JOIN `'._DB_PREFIX_.'order_state` os ON o.current_state = os.id_order_state
    		WHERE `date_add` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
    		'.Shop::addSqlRestriction(false, 'o'));
            foreach ($orders as $order) {
                // Add flat fees for this order
                $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                    $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                        ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED')
                        : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN')
                    );
    
                // Add variable fees for this order
                $var_fees = $order['total_paid_tax_incl'] * (
                    $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                        ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR')
                        : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN')
                    ) / 100;
    
                // Add shipping fees for this order
                $shipping_fees = $order['total_shipping_tax_excl'] * (
                    $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                        ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP')
                        : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS')
                    ) / 100;
    
                // Tally up these fees
                if ($granularity == 'day') {
                    if (!isset($expenses[strtotime($order['date'])])) {
                        $expenses[strtotime($order['date'])] = 0;
                    }
                    $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
                } else {
                    $expenses += $flat_fees + $var_fees + $shipping_fees;
                }
            }
            return $expenses;
        }
    }
    




  • It’s probably a matter of taste what one wants to count. All orders recorded or only orders with an invoice ( = completed orders).

    Opinions?



  • I would personally just count completed orders.

    In my situation many customers create the online order but don’t complete the payment for whatever reason. I wouldn’t count those “phantom” orders.



  • In our case, we prefer without invoice. Because in our previous business we found quite interesting not to create invoice until the order is sent. Why? Because numerous times clients cancel some orders so we need to create another document or “negative invoice” to cancel the initial invoice. The solution? To create the invoice when the order is sent. If client cancel before, we just cancel the order and it is not necessary to create the “negative invoice” because the invoice was never created.

    A common solution would be to count orders that have been paid, with or without an invoice.



  • @adik Thanks for your help, this makes to count paid orders or orders with invoices?



  • @traumflug said in Stats do not show orders and carts correctly TB 1.0.4:

    It’s probably a matter of taste what one wants to count. All orders recorded or only orders with an invoice ( = completed orders).

    Opinions?

    Wouldn’t be difficult to give a choise for merchant - whether to include only completed orders or alltogether? This could be done as an option in module’s settings



  • But anyway it demands work to make it possible. As I said, a common solution would be to count orders that have been paid, with or without an invoice. Everyones is happy 🙂


 

Looks like your connection to thirty bees forum was lost, please wait while we try to reconnect.