protected function getData($date_from, $date_to)
{
// We need the following figures to calculate our stats
$tmp_data = array('visits' => array(), 'orders' => array(), 'total_paid_tax_excl' => array(), 'total_purchases' => array(), 'total_expenses' => array());
if (Configuration::get('PS_DASHBOARD_SIMULATION')) {
$from = strtotime($date_from . ' 00:00:00');
$to = min(time(), strtotime($date_to . ' 23:59:59'));
for ($date = $from; $date <= $to; $date = strtotime('+1 day', $date)) {
$tmp_data['visits'][$date] = round(rand(2000, 20000));
$tmp_data['conversion_rate'][$date] = rand(80, 250) / 100;
$tmp_data['average_cart_value'][$date] = round(rand(60, 200), 2);
$tmp_data['orders'][$date] = round($tmp_data['visits'][$date] * $tmp_data['conversion_rate'][$date] / 100);
$tmp_data['total_paid_tax_excl'][$date] = $tmp_data['orders'][$date] * $tmp_data['average_cart_value'][$date];
$tmp_data['total_purchases'][$date] = $tmp_data['total_paid_tax_excl'][$date] * rand(50, 70) / 100;
$tmp_data['total_expenses'][$date] = $tmp_data['total_paid_tax_excl'][$date] * rand(0, 10) / 100;
}
} else {
$tmp_data['visits'] = AdminStatsController::getVisits(false, $date_from, $date_to, 'day');
$tmp_data['orders'] = AdminStatsController::getOrders($date_from, $date_to, 'day');
$tmp_data['total_paid_tax_excl'] = AdminStatsController::getTotalSales($date_from, $date_to, 'day');
$tmp_data['total_purchases'] = AdminStatsController::getPurchases($date_from, $date_to, 'day');
$tmp_data['total_expenses'] = AdminStatsController::getExpenses($date_from, $date_to, 'day');
}
return $tmp_data;
}
public function displayAjaxGetKpi()
{
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
switch (Tools::getValue('kpi')) {
case 'conversion_rate':
$visitors = AdminStatsController::getVisits(true, date('Y-m-d', strtotime('-31 day')), date('Y-m-d', strtotime('-1 day')), false);
$orders = AdminStatsController::getOrders(date('Y-m-d', strtotime('-31 day')), date('Y-m-d', strtotime('-1 day')), false);
// $data = array();
// $from = strtotime(date('Y-m-d 00:00:00', strtotime('-31 day')));
// $to = strtotime(date('Y-m-d 23:59:59', strtotime('-1 day')));
// for ($date = $from; $date <= $to; $date = strtotime('+1 day', $date))
// if (isset($visitors[$date]) && $visitors[$date])
// $data[$date] = round(100 * ((isset($orders[$date]) && $orders[$date]) ? $orders[$date] : 0) / $visitors[$date], 2);
// else
// $data[$date] = 0;
$visits_sum = $visitors;
//array_sum($visitors);
$orders_sum = $orders;
//array_sum($orders);
if ($visits_sum) {
$value = round(100 * $orders_sum / $visits_sum, 2);
} elseif ($orders_sum) {
$value = '∞';
} else {
$value = 0;
}
$value .= '%';
// ConfigurationKPI::updateValue('CONVERSION_RATE_CHART', Tools::jsonEncode($data));
ConfigurationKPI::updateValue('CONVERSION_RATE', $value);
ConfigurationKPI::updateValue('CONVERSION_RATE_EXPIRE', strtotime(date('Y-m-d 00:00:00', strtotime('+1 day'))));
break;
case 'abandoned_cart':
$value = AdminStatsController::getAbandonedCarts(date('Y-m-d H:i:s', strtotime('-2 day')), date('Y-m-d H:i:s', strtotime('-1 day')));
ConfigurationKPI::updateValue('ABANDONED_CARTS', $value);
ConfigurationKPI::updateValue('ABANDONED_CARTS_EXPIRE', strtotime('+1 hour'));
break;
case 'installed_modules':
$value = AdminStatsController::getInstalledModules();
ConfigurationKPI::updateValue('INSTALLED_MODULES', $value);
ConfigurationKPI::updateValue('INSTALLED_MODULES_EXPIRE', strtotime('+2 min'));
break;
case 'disabled_modules':
$value = AdminStatsController::getDisabledModules();
ConfigurationKPI::updateValue('DISABLED_MODULES', $value);
ConfigurationKPI::updateValue('DISABLED_MODULES_EXPIRE', strtotime('+2 min'));
break;
case 'update_modules':
$value = AdminStatsController::getModulesToUpdate();
ConfigurationKPI::updateValue('UPDATE_MODULES', $value);
ConfigurationKPI::updateValue('UPDATE_MODULES_EXPIRE', strtotime('+2 min'));
break;
case 'percent_product_stock':
$value = AdminStatsController::getPercentProductStock();
ConfigurationKPI::updateValue('PERCENT_PRODUCT_STOCK', $value);
ConfigurationKPI::updateValue('PERCENT_PRODUCT_STOCK_EXPIRE', strtotime('+4 hour'));
break;
case 'product_avg_gross_margin':
$value = AdminStatsController::getProductAverageGrossMargin();
ConfigurationKPI::updateValue('PRODUCT_AVG_GROSS_MARGIN', $value);
ConfigurationKPI::updateValue('PRODUCT_AVG_GROSS_MARGIN_EXPIRE', strtotime('+6 hour'));
break;
case 'disabled_categories':
$value = AdminStatsController::getDisabledCategories();
ConfigurationKPI::updateValue('DISABLED_CATEGORIES', $value);
ConfigurationKPI::updateValue('DISABLED_CATEGORIES_EXPIRE', strtotime('+2 hour'));
break;
case 'disabled_products':
$value = AdminStatsController::getDisabledProducts();
ConfigurationKPI::updateValue('DISABLED_PRODUCTS', $value);
ConfigurationKPI::updateValue('DISABLED_PRODUCTS_EXPIRE', strtotime('+2 hour'));
break;
case '8020_sales_catalog':
$value = AdminStatsController::get8020SalesCatalog(date('Y-m-d', strtotime('-1 month')), date('Y-m-d'));
$value = sprintf($this->l('%d%% of your Catalog'), $value);
ConfigurationKPI::updateValue('8020_SALES_CATALOG', $value);
ConfigurationKPI::updateValue('8020_SALES_CATALOG_EXPIRE', strtotime('+12 hour'));
break;
case 'empty_categories':
$value = AdminStatsController::getEmptyCategories();
ConfigurationKPI::updateValue('EMPTY_CATEGORIES', $value);
ConfigurationKPI::updateValue('EMPTY_CATEGORIES_EXPIRE', strtotime('+2 hour'));
break;
case 'customer_main_gender':
$value = AdminStatsController::getCustomerMainGender();
if ($value === false) {
$value = $this->l('No customers', null, null, false);
} elseif ($value['type'] == 'female') {
$value = sprintf($this->l('%d%% Women Customers', null, null, false), $value['value']);
} elseif ($value['type'] == 'male') {
$value = sprintf($this->l('%d%% Men Customers', null, null, false), $value['value']);
} else {
$value = sprintf($this->l('%d%% Neutral Customers', null, null, false), $value['value']);
}
ConfigurationKPI::updateValue('CUSTOMER_MAIN_GENDER', array($this->context->language->id => $value));
ConfigurationKPI::updateValue('CUSTOMER_MAIN_GENDER_EXPIRE', array($this->context->language->id => strtotime('+1 day')));
break;
case 'avg_customer_age':
$value = sprintf($this->l('%d years', null, null, false), AdminStatsController::getAverageCustomerAge(), 1);
ConfigurationKPI::updateValue('AVG_CUSTOMER_AGE', $value);
ConfigurationKPI::updateValue('AVG_CUSTOMER_AGE_EXPIRE', strtotime('+1 day'));
break;
case 'pending_messages':
$value = (int) AdminStatsController::getPendingMessages();
ConfigurationKPI::updateValue('PENDING_MESSAGES', $value);
ConfigurationKPI::updateValue('PENDING_MESSAGES_EXPIRE', strtotime('+5 min'));
break;
case 'avg_msg_response_time':
$value = sprintf($this->l('%.1f hours', null, null, false), AdminStatsController::getAverageMessageResponseTime(date('Y-m-d', strtotime('-31 day')), date('Y-m-d', strtotime('-1 day'))));
ConfigurationKPI::updateValue('AVG_MSG_RESPONSE_TIME', $value);
ConfigurationKPI::updateValue('AVG_MSG_RESPONSE_TIME_EXPIRE', strtotime('+4 hour'));
break;
case 'messages_per_thread':
$value = round(AdminStatsController::getMessagesPerThread(date('Y-m-d', strtotime('-31 day')), date('Y-m-d', strtotime('-1 day'))), 1);
ConfigurationKPI::updateValue('MESSAGES_PER_THREAD', $value);
ConfigurationKPI::updateValue('MESSAGES_PER_THREAD_EXPIRE', strtotime('+12 hour'));
break;
case 'newsletter_registrations':
$value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer`
WHERE newsletter = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
if (Module::isInstalled('blocknewsletter')) {
$value += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'newsletter`
WHERE active = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
}
ConfigurationKPI::updateValue('NEWSLETTER_REGISTRATIONS', $value);
ConfigurationKPI::updateValue('NEWSLETTER_REGISTRATIONS_EXPIRE', strtotime('+6 hour'));
break;
case 'enabled_languages':
$value = Language::countActiveLanguages();
ConfigurationKPI::updateValue('ENABLED_LANGUAGES', $value);
ConfigurationKPI::updateValue('ENABLED_LANGUAGES_EXPIRE', strtotime('+1 min'));
break;
case 'frontoffice_translations':
$themes = Theme::getThemes();
$languages = Language::getLanguages();
$total = $translated = 0;
foreach ($themes as $theme) {
foreach ($languages as $language) {
$kpi_key = substr(strtoupper($theme->name . '_' . $language['iso_code']), 0, 16);
$total += ConfigurationKPI::get('TRANSLATE_TOTAL_' . $kpi_key);
$translated += ConfigurationKPI::get('TRANSLATE_DONE_' . $kpi_key);
}
}
$value = 0;
if ($translated) {
$value = round(100 * $translated / $total, 1);
}
$value .= '%';
ConfigurationKPI::updateValue('FRONTOFFICE_TRANSLATIONS', $value);
ConfigurationKPI::updateValue('FRONTOFFICE_TRANSLATIONS_EXPIRE', strtotime('+2 min'));
break;
case 'main_country':
if (!($row = AdminStatsController::getMainCountry(date('Y-m-d', strtotime('-30 day')), date('Y-m-d')))) {
$value = $this->l('No orders', null, null, false);
} else {
$country = new Country($row['id_country'], $this->context->language->id);
$value = sprintf($this->l('%d%% %s', null, null, false), $row['orders'], $country->name);
}
ConfigurationKPI::updateValue('MAIN_COUNTRY', array($this->context->language->id => $value));
ConfigurationKPI::updateValue('MAIN_COUNTRY_EXPIRE', array($this->context->language->id => strtotime('+1 day')));
break;
case 'orders_per_customer':
$value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer` c
WHERE active = 1
' . Shop::addSqlRestriction());
if ($value) {
$orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'orders` o
WHERE valid = 1
' . Shop::addSqlRestriction());
$value = round($orders / $value, 2);
}
ConfigurationKPI::updateValue('ORDERS_PER_CUSTOMER', $value);
ConfigurationKPI::updateValue('ORDERS_PER_CUSTOMER_EXPIRE', strtotime('+1 day'));
break;
case 'average_order_value':
$row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT
COUNT(`id_order`) as orders,
SUM(`total_paid_tax_excl` / `conversion_rate`) as total_paid_tax_excl
FROM `' . _DB_PREFIX_ . 'orders`
WHERE `invoice_date` BETWEEN "' . pSQL(date('Y-m-d', strtotime('-31 day'))) . ' 00:00:00" AND "' . pSQL(date('Y-m-d', strtotime('-1 day'))) . ' 23:59:59"
' . Shop::addSqlRestriction());
$value = Tools::displayPrice($row['orders'] ? $row['total_paid_tax_excl'] / $row['orders'] : 0, $currency);
ConfigurationKPI::updateValue('AVG_ORDER_VALUE', $value);
ConfigurationKPI::updateValue('AVG_ORDER_VALUE_EXPIRE', strtotime(date('Y-m-d 00:00:00', strtotime('+1 day'))));
break;
case 'netprofit_visitor':
$date_from = date('Y-m-d', strtotime('-31 day'));
$date_to = date('Y-m-d', strtotime('-1 day'));
$total_visitors = AdminStatsController::getVisits(true, $date_from, $date_to);
$net_profits = AdminStatsController::getTotalSales($date_from, $date_to);
$net_profits -= AdminStatsController::getExpenses($date_from, $date_to);
$net_profits -= AdminStatsController::getPurchases($date_from, $date_to);
if ($total_visitors) {
$value = Tools::displayPrice($net_profits / $total_visitors, $currency);
} elseif ($net_profits) {
$value = '∞';
} else {
$value = Tools::displayPrice(0, $currency);
}
ConfigurationKPI::updateValue('NETPROFIT_VISITOR', $value);
ConfigurationKPI::updateValue('NETPROFIT_VISITOR_EXPIRE', strtotime(date('Y-m-d 00:00:00', strtotime('+1 day'))));
break;
case 'products_per_category':
$products = AdminStatsController::getTotalProducts();
$categories = AdminStatsController::getTotalCategories();
$value = round($products / $categories);
ConfigurationKPI::updateValue('PRODUCTS_PER_CATEGORY', $value);
ConfigurationKPI::updateValue('PRODUCTS_PER_CATEGORY_EXPIRE', strtotime('+1 hour'));
break;
case 'top_category':
if (!($id_category = AdminStatsController::getBestCategory(date('Y-m-d', strtotime('-1 month')), date('Y-m-d')))) {
$value = $this->l('No category', null, null, false);
} else {
$category = new Category($id_category, $this->context->language->id);
$value = $category->name;
}
ConfigurationKPI::updateValue('TOP_CATEGORY', array($this->context->language->id => $value));
ConfigurationKPI::updateValue('TOP_CATEGORY_EXPIRE', array($this->context->language->id => strtotime('+1 day')));
break;
default:
$value = false;
}
if ($value !== false) {
$array = array('value' => $value);
if (isset($data)) {
$array['data'] = $data;
}
die(Tools::jsonEncode($array));
}
die(Tools::jsonEncode(array('has_errors' => true)));
}
public function hookDashboardData($params)
{
if (Tools::strlen($params['date_from']) == 10) {
$params['date_from'] .= ' 00:00:00';
}
if (Tools::strlen($params['date_to']) == 10) {
$params['date_to'] .= ' 23:59:59';
}
if (Configuration::get('PS_DASHBOARD_SIMULATION')) {
$days = (strtotime($params['date_to']) - strtotime($params['date_from'])) / 3600 / 24;
$online_visitor = rand(10, 50);
$visits = rand(200, 2000) * $days;
return array('data_value' => array('pending_orders' => round(rand(0, 5)), 'return_exchanges' => round(rand(0, 5)), 'abandoned_cart' => round(rand(5, 50)), 'products_out_of_stock' => round(rand(1, 10)), 'new_messages' => round(rand(1, 10) * $days), 'product_reviews' => round(rand(5, 50) * $days), 'new_customers' => round(rand(1, 5) * $days), 'online_visitor' => round($online_visitor), 'active_shopping_cart' => round($online_visitor / 10), 'new_registrations' => round(rand(1, 5) * $days), 'total_suscribers' => round(rand(200, 2000)), 'visits' => round($visits), 'unique_visitors' => round($visits * 0.6)), 'data_trends' => array('orders_trends' => array('way' => 'down', 'value' => 0.42)), 'data_list_small' => array('dash_traffic_source' => array('<i class="icon-circle" style="color:' . self::$colors[0] . '"></i> prestashop.com' => round($visits / 2), '<i class="icon-circle" style="color:' . self::$colors[1] . '"></i> google.com' => round($visits / 3), '<i class="icon-circle" style="color:' . self::$colors[2] . '"></i> Direct Traffic' => round($visits / 4))), 'data_chart' => array('dash_trends_chart1' => array('chart_type' => 'pie_chart_trends', 'data' => array(array('key' => 'prestashop.com', 'y' => round($visits / 2), 'color' => self::$colors[0]), array('key' => 'google.com', 'y' => round($visits / 3), 'color' => self::$colors[1]), array('key' => 'Direct Traffic', 'y' => round($visits / 4), 'color' => self::$colors[2])))));
}
$gapi = Module::isInstalled('gapi') ? Module::getInstanceByName('gapi') : false;
if (Validate::isLoadedObject($gapi) && $gapi->isConfigured()) {
$visits = $unique_visitors = $online_visitor = 0;
if ($result = $gapi->requestReportData('', 'ga:visits,ga:visitors', substr($params['date_from'], 0, 10), substr($params['date_to'], 0, 10), null, null, 1, 1)) {
$visits = $result[0]['metrics']['visits'];
$unique_visitors = $result[0]['metrics']['visitors'];
}
} else {
$row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT COUNT(*) as visits, COUNT(DISTINCT `id_guest`) as unique_visitors
FROM `' . _DB_PREFIX_ . 'connections`
WHERE `date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(false));
extract($row);
}
// Online visitors is only available with Analytics Real Time still in private beta at this time (October 18th, 2013).
// if ($result = $gapi->requestReportData('', 'ga:activeVisitors', null, null, null, null, 1, 1))
// $online_visitor = $result[0]['metrics']['activeVisitors'];
if ($maintenance_ips = Configuration::get('PS_MAINTENANCE_IP')) {
$maintenance_ips = implode(',', array_map('ip2long', array_map('trim', explode(',', $maintenance_ips))));
}
if (Configuration::get('PS_STATSDATA_CUSTOMER_PAGESVIEWS')) {
$sql = 'SELECT c.id_guest, c.ip_address, c.date_add, c.http_referer, pt.name as page
FROM `' . _DB_PREFIX_ . 'connections` c
LEFT JOIN `' . _DB_PREFIX_ . 'connections_page` cp ON c.id_connections = cp.id_connections
LEFT JOIN `' . _DB_PREFIX_ . 'page` p ON p.id_page = cp.id_page
LEFT JOIN `' . _DB_PREFIX_ . 'page_type` pt ON p.id_page_type = pt.id_page_type
INNER JOIN `' . _DB_PREFIX_ . 'guest` g ON c.id_guest = g.id_guest
WHERE (g.id_customer IS NULL OR g.id_customer = 0)
' . Shop::addSqlRestriction(false, 'c') . '
AND cp.`time_end` IS NULL
AND TIME_TO_SEC(TIMEDIFF(\'' . pSQL(date('Y-m-d H:i:00', time())) . '\', cp.`time_start`)) < 900
' . ($maintenance_ips ? 'AND c.ip_address NOT IN (' . preg_replace('/[^,0-9]/', '', $maintenance_ips) . ')' : '') . '
GROUP BY c.id_connections
ORDER BY c.date_add DESC';
} else {
$sql = 'SELECT c.id_guest, c.ip_address, c.date_add, c.http_referer, "-" as page
FROM `' . _DB_PREFIX_ . 'connections` c
INNER JOIN `' . _DB_PREFIX_ . 'guest` g ON c.id_guest = g.id_guest
WHERE (g.id_customer IS NULL OR g.id_customer = 0)
' . Shop::addSqlRestriction(false, 'c') . '
AND TIME_TO_SEC(TIMEDIFF(\'' . pSQL(date('Y-m-d H:i:00', time())) . '\', c.`date_add`)) < 900
' . ($maintenance_ips ? 'AND c.ip_address NOT IN (' . preg_replace('/[^,0-9]/', '', $maintenance_ips) . ')' : '') . '
ORDER BY c.date_add DESC';
}
$results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
$online_visitor = Db::getInstance()->NumRows();
$pending_orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON (o.current_state = os.id_order_state)
WHERE os.paid = 1 AND os.shipped = 0
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$abandoned_cart = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'cart`
WHERE `date_upd` BETWEEN "' . pSQL(date('Y-m-d H:i:s', strtotime('-' . (int) Configuration::get('DASHACTIVITY_CART_ABANDONED_MAX') . ' MIN'))) . '" AND "' . pSQL(date('Y-m-d H:i:s', strtotime('-' . (int) Configuration::get('DASHACTIVITY_CART_ABANDONED_MIN') . ' MIN'))) . '"
AND id_cart NOT IN (SELECT id_cart FROM `' . _DB_PREFIX_ . 'orders`)
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$return_exchanges = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_return` or2 ON o.id_order = or2.id_order
WHERE or2.`date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o'));
$products_out_of_stock = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT SUM(IF(IFNULL(stock.quantity, 0) > 0, 0, 1))
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON p.id_product = pa.id_product
' . Product::sqlStock('p', 'pa'));
$new_messages = AdminStatsController::getPendingMessages();
$active_shopping_cart = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'cart`
WHERE date_upd > "' . pSQL(date('Y-m-d H:i:s', strtotime('-' . (int) Configuration::get('DASHACTIVITY_CART_ACTIVE') . ' MIN'))) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$new_customers = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer`
WHERE `date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$new_registrations = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer`
WHERE `newsletter_date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
AND newsletter = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$total_suscribers = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer`
WHERE newsletter = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
if (Module::isInstalled('blocknewsletter')) {
$new_registrations += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'newsletter`
WHERE active = 1
AND `newsletter_date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$total_suscribers += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'newsletter`
WHERE active = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
}
$product_reviews = 0;
if (Module::isInstalled('productcomments')) {
$product_reviews += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'product_comment` pc
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (pc.id_product = p.id_product)
' . Shop::addSqlAssociation('product', 'p') . '
WHERE pc.deleted = 0
AND pc.`date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
}
return array('data_value' => array('pending_orders' => (int) $pending_orders, 'return_exchanges' => (int) $return_exchanges, 'abandoned_cart' => (int) $abandoned_cart, 'products_out_of_stock' => (int) $products_out_of_stock, 'new_messages' => (int) $new_messages, 'product_reviews' => (int) $product_reviews, 'new_customers' => (int) $new_customers, 'online_visitor' => (int) $online_visitor, 'active_shopping_cart' => (int) $active_shopping_cart, 'new_registrations' => (int) $new_registrations, 'total_suscribers' => (int) $total_suscribers, 'visits' => (int) $visits, 'unique_visitors' => (int) $unique_visitors), 'data_trends' => array('orders_trends' => array('way' => 'down', 'value' => 0.42)), 'data_list_small' => array('dash_traffic_source' => $this->getTrafficSources($params['date_from'], $params['date_to'])), 'data_chart' => array('dash_trends_chart1' => $this->getChartTrafficSource($params['date_from'], $params['date_to'])));
}
public function getChartData($year)
{
// There are stream types (different charts) and for each types there are 3 available zones (one color for the goal, one if you over perform and one if you under perfom)
$stream_types = array(array('type' => 'traffic', 'title' => $this->l('Traffic'), 'unit_text' => $this->l('visits')), array('type' => 'conversion', 'title' => $this->l('Conversion'), 'unit_text' => ''), array('type' => 'avg_cart_value', 'title' => $this->l('Average cart value'), 'unit_text' => ''), array('type' => 'sales', 'title' => $this->l('Sales'), 'unit_text' => ''));
$stream_zones = array(array('zone' => 'real', 'text' => ''), array('zone' => 'more', 'text' => $this->l('Goal exceeded')), array('zone' => 'less', 'text' => $this->l('Goal not reached')));
// We initialize all the streams types for all the zones
$streams = array();
$average_goals = array();
foreach ($stream_types as $key => $stream_type) {
$streams[$stream_type['type']] = array();
foreach ($stream_zones as $stream_zone) {
$streams[$stream_type['type']][$stream_zone['zone']] = array('key' => $stream_type['type'] . '_' . $stream_zone['zone'], 'title' => $stream_type['title'], 'unit_text' => $stream_type['unit_text'], 'zone_text' => $stream_zone['text'], 'color' => $stream_zone['zone'] == 'more' ? self::$more_color[$key] : ($stream_zone['zone'] == 'less' ? self::$less_color[$key] : self::$real_color[$key]), 'values' => array(), 'disabled' => isset($stream_type['type']) && $stream_type['type'] == 'sales' ? false : true);
}
if (isset($stream_type['type'])) {
$average_goals[$stream_type['type']] = 0;
}
}
if (Configuration::get('PS_DASHBOARD_SIMULATION')) {
$visits = $orders = $sales = array();
$from = strtotime(date('Y-01-01 00:00:00'));
$to = strtotime(date('Y-12-31 00:00:00'));
for ($date = $from; $date <= $to; $date = strtotime('+1 day', $date)) {
$visits[$date] = round(rand(2000, 5000));
$orders[$date] = round(rand(40, 100));
$sales[$date] = round(rand(3000, 9000), 2);
}
// We need to calculate the average value of each goals for the year, this will be the base rate for "100%"
for ($i = '01'; $i <= 12; $i = sprintf('%02d', $i + 1)) {
$average_goals['traffic'] += $this->fakeConfigurationKPI_get('DASHGOALS_TRAFFIC_' . $i . '_' . $year);
$average_goals['conversion'] += $this->fakeConfigurationKPI_get('DASHGOALS_CONVERSION_' . $i . '_' . $year);
$average_goals['avg_cart_value'] += $this->fakeConfigurationKPI_get('DASHGOALS_AVG_CART_VALUE_' . $i . '_' . $year);
}
foreach ($average_goals as &$average_goal) {
$average_goal /= 12;
}
$average_goals['sales'] = $average_goals['traffic'] * $average_goals['conversion'] / 100 * $average_goals['avg_cart_value'];
// Now we can calculate the value for every months
for ($i = '01'; $i <= 12; $i = sprintf('%02d', $i + 1)) {
$timestamp = strtotime($year . '-' . $i . '-01');
$month_goal = $this->fakeConfigurationKPI_get('DASHGOALS_TRAFFIC_' . $i . '_' . $year);
$value = isset($visits[$timestamp]) ? $visits[$timestamp] : 0;
$stream_values = $this->getValuesFromGoals($average_goals['traffic'], $month_goal, $value, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['traffic'] = $value;
$stream_values['real']['goal'] = $month_goal;
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['traffic'] = $value;
$stream_values['more']['traffic'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = $goal_diff;
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = $goal_diff;
}
if ($value == 0) {
$streams['traffic']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['traffic'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
$month_goal = $this->fakeConfigurationKPI_get('DASHGOALS_CONVERSION_' . $i . '_' . $year);
$value = 100 * (isset($visits[$timestamp]) && $visits[$timestamp] && isset($orders[$timestamp]) && $orders[$timestamp] ? $orders[$timestamp] / $visits[$timestamp] : 0);
$stream_values = $this->getValuesFromGoals($average_goals['conversion'], $month_goal, $value, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['conversion'] = round($value, 2);
$stream_values['real']['goal'] = round($month_goal, 2);
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['conversion'] = $value;
$stream_values['more']['conversion'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
if ($value == 0) {
$streams['conversion']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['conversion'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
$month_goal = $this->fakeConfigurationKPI_get('DASHGOALS_AVG_CART_VALUE_' . $i . '_' . $year);
$value = isset($orders[$timestamp]) && $orders[$timestamp] && isset($sales[$timestamp]) && $sales[$timestamp] ? $sales[$timestamp] / $orders[$timestamp] : 0;
$stream_values = $this->getValuesFromGoals($average_goals['avg_cart_value'], $month_goal, $value, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['avg_cart_value'] = $value;
$stream_values['real']['goal'] = $month_goal;
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['avg_cart_value'] = $value;
$stream_values['more']['avg_cart_value'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = $goal_diff;
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = $goal_diff;
}
if ($value == 0) {
$streams['avg_cart_value']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['avg_cart_value'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
$month_goal = $this->fakeConfigurationKPI_get('DASHGOALS_TRAFFIC_' . $i . '_' . $year) * $this->fakeConfigurationKPI_get('DASHGOALS_CONVERSION_' . $i . '_' . $year) / 100 * $this->fakeConfigurationKPI_get('DASHGOALS_AVG_CART_VALUE_' . $i . '_' . $year);
$value = isset($sales[$timestamp]) ? $sales[$timestamp] : 0;
$stream_values = $this->getValuesFromGoals($average_goals['sales'], $month_goal, $value, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['sales'] = $value;
$stream_values['real']['goal'] = $month_goal;
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['sales'] = $value;
$stream_values['more']['sales'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = $goal_diff;
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = $goal_diff;
}
if ($value == 0) {
$streams['sales']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['sales'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
}
} else {
// Retrieve gross data from AdminStatsController
$visits = AdminStatsController::getVisits(false, $year . date('-01-01'), $year . date('-12-31'), 'month');
$orders = AdminStatsController::getOrders($year . date('-01-01'), $year . date('-12-31'), 'month');
$sales = AdminStatsController::getTotalSales($year . date('-01-01'), $year . date('-12-31'), 'month');
// We need to calculate the average value of each goals for the year, this will be the base rate for "100%"
for ($i = '01'; $i <= 12; $i = sprintf('%02d', $i + 1)) {
$average_goals['traffic'] += ConfigurationKPI::get('DASHGOALS_TRAFFIC_' . $i . '_' . $year);
$average_goals['conversion'] += ConfigurationKPI::get('DASHGOALS_CONVERSION_' . $i . '_' . $year) / 100;
$average_goals['avg_cart_value'] += ConfigurationKPI::get('DASHGOALS_AVG_CART_VALUE_' . $i . '_' . $year);
}
foreach ($average_goals as &$average_goal) {
$average_goal /= 12;
}
$average_goals['sales'] = $average_goals['traffic'] * $average_goals['conversion'] * $average_goals['avg_cart_value'];
// Now we can calculate the value for every months
for ($i = '01'; $i <= 12; $i = sprintf('%02d', $i + 1)) {
$timestamp = strtotime($year . '-' . $i . '-01');
$month_goal = ConfigurationKPI::get('DASHGOALS_TRAFFIC_' . $i . '_' . $year);
$value = isset($visits[$timestamp]) ? $visits[$timestamp] : 0;
$stream_values = $this->getValuesFromGoals($average_goals['traffic'], $month_goal, $value, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['traffic'] = $value;
$stream_values['real']['goal'] = $month_goal;
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['traffic'] = $value;
$stream_values['more']['traffic'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = $goal_diff;
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = $goal_diff;
}
if ($value == 0) {
$streams['traffic']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['traffic'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
$month_goal = ConfigurationKPI::get('DASHGOALS_CONVERSION_' . $i . '_' . $year);
$value = 100 * (isset($visits[$timestamp]) && $visits[$timestamp] && isset($orders[$timestamp]) && $orders[$timestamp] ? $orders[$timestamp] / $visits[$timestamp] : 0);
$stream_values = $this->getValuesFromGoals($average_goals['conversion'] * 100, $month_goal, $value, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['conversion'] = round($value, 2);
$stream_values['real']['goal'] = round($month_goal, 2);
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['conversion'] = $value;
$stream_values['more']['conversion'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
if ($value == 0) {
$streams['conversion']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['conversion'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
$month_goal = ConfigurationKPI::get('DASHGOALS_AVG_CART_VALUE_' . $i . '_' . $year);
$value = isset($orders[$timestamp]) && $orders[$timestamp] && isset($sales[$timestamp]) && $sales[$timestamp] ? $sales[$timestamp] / $orders[$timestamp] : 0;
$stream_values = $this->getValuesFromGoals($average_goals['avg_cart_value'], $month_goal, $value, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['avg_cart_value'] = $value;
$stream_values['real']['goal'] = $month_goal;
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['avg_cart_value'] = $value;
$stream_values['more']['avg_cart_value'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = $goal_diff;
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = $goal_diff;
}
if ($value == 0) {
$streams['avg_cart_value']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['avg_cart_value'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
$month_goal = ConfigurationKPI::get('DASHGOALS_TRAFFIC_' . $i . '_' . $year) * ConfigurationKPI::get('DASHGOALS_CONVERSION_' . $i . '_' . $year) / 100 * ConfigurationKPI::get('DASHGOALS_AVG_CART_VALUE_' . $i . '_' . $year);
$value = isset($sales[$timestamp]) && $sales[$timestamp] ? $sales[$timestamp] : 0;
$stream_values = $this->getValuesFromGoals($average_goals['sales'], $month_goal, isset($sales[$timestamp]) ? $sales[$timestamp] : 0, Dashgoals::$month_labels[$i]);
$goal_diff = $value - $month_goal;
$stream_values['real']['sales'] = $value;
$stream_values['real']['goal'] = $month_goal;
if ($value > 0) {
$stream_values['real']['goal_diff'] = round($goal_diff * 100 / ($month_goal > 0 ? $month_goal : 1), 2);
}
$stream_values['less']['sales'] = $value;
$stream_values['more']['sales'] = $value;
if ($value > 0 && $value < $month_goal) {
$stream_values['less']['goal_diff'] = $goal_diff;
} elseif ($value > 0) {
$stream_values['more']['goal_diff'] = $goal_diff;
}
if ($value == 0) {
$streams['sales']['less']['zone_text'] = $this->l('Goal set:');
$stream_values['less']['goal'] = $month_goal;
}
foreach ($stream_zones as $stream_zone) {
$streams['sales'][$stream_zone['zone']]['values'][] = $stream_values[$stream_zone['zone']];
}
}
}
// Merge all the streams before sending
$all_streams = array();
foreach ($stream_types as $stream_type) {
foreach ($stream_zones as $stream_zone) {
$all_streams[] = $streams[$stream_type['type']][$stream_zone['zone']];
}
}
return array('chart_type' => 'bar_chart_goals', 'data' => $all_streams);
}
public function postProcess()
{
if ($this->enableCalendar()) {
// Warning, instantiating a controller here changes the controller in the Context...
$calendar_tab = new AdminStatsController();
$calendar_tab->postProcess();
// ...so we set it back to the correct one here
$this->context->controller = $this;
}
if (Tools::isSubmit('submitSettings')) {
if ($this->tabAccess['edit'] === '1') {
if (Configuration::updateValue('TRACKING_DIRECT_TRAFFIC', (int) Tools::getValue('tracking_dt'))) {
Tools::redirectAdmin(self::$currentIndex . '&conf=4&token=' . Tools::getValue('token'));
}
}
}
if (ModuleGraph::getDateBetween() != Configuration::get('PS_REFERRERS_CACHE_LIKE') || Tools::isSubmit('submitRefreshCache')) {
Referrer::refreshCache();
}
if (Tools::isSubmit('submitRefreshIndex')) {
Referrer::refreshIndex();
}
return parent::postProcess();
}