Stats do not show orders and carts correctly TB 1.0.4

[rubben1985]

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

[rubben1985]

Any idea?

[Adik]

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($datefrom, $dateto, $granularity = false) { if ($granularity == 'day') { $sales = array(); $result = Db::getInstance(PSUSESQLSLAVE)->ExecuteS(' SELECT LEFT(date_add, 10) as date, SUM(totalpaidtaxexcl / o.conversionrate) as sales FROM '._DB_PREFIX_.'orders o LEFT JOIN '._DB_PREFIX_.'order_state os ON o.currentstate = os.idorderstate WHERE date_add BETWEEN "'.pSQL($datefrom).' 00:00:00" AND "'.pSQL($dateto).' 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(PSUSESQLSLAVE)->ExecuteS(' SELECT LEFT(date_add, 7) as date, SUM(totalpaidtaxexcl / o.conversionrate) as sales FROM '._DB_PREFIX_.'orders o LEFT JOIN '._DB_PREFIX_.'order_state os ON o.currentstate = os.idorderstate WHERE date_add BETWEEN "'.pSQL($datefrom).' 00:00:00" AND "'.pSQL($dateto).' 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(PSUSESQLSLAVE)->getValue(' SELECT SUM(totalpaidtaxexcl / o.conversionrate) FROM '._DB_PREFIX_.'orders o LEFT JOIN '._DB_PREFIX_.'order_state os ON o.currentstate = os.idorderstate WHERE date_add BETWEEN "'.pSQL($datefrom).' 00:00:00" AND "'.pSQL($dateto).' 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;
}

} ```

[Traumflug]

https://github.com/thirtybees/thirtybees/issues/511

[Traumflug]

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

Opinions?

[RaptorX]

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.

[rubben1985]

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.

[rubben1985]

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

[DaoKakao]

@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

[rubben1985]

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 :)