<?php
/*
* 2007-2016 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Academic Free License (AFL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/afl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to license@prestashop.com so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <contact@prestashop.com>
*  @copyright  2007-2016 PrestaShop SA
*  @license    http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
*  International Registered Trademark & Property of PrestaShop SA
*/

if (!defined('_PS_VERSION_')) {
    exit;
}

class StatsForecast extends Module
{
    private $html = '';
    private $t1 = 0;
    private $t2 = 0;
    private $t3 = 0;
    private $t4 = 0;
    private $t5 = 0;
    private $t6 = 0;
    private $t7 = 0;
    private $t8 = 0;
    private $t9 = 0;

    public function __construct()
    {
        $this->name = 'statsforecast';
        $this->tab = 'analytics_stats';
        $this->version = '1.4.5';
        $this->author = 'PrestaShop';
        $this->need_instance = 0;

        parent::__construct();

        $this->displayName = $this->l('Stats Dashboard');
        $this->description = $this->l('This is the main module for the Stats dashboard. It displays a summary of all your current statistics.');
        $this->ps_versions_compliancy = array('min' => '1.6', 'max' => '1.7.0.99');
    }

    public function install()
    {
        return parent::install()
            && $this->registerHook('AdminStatsModules')
            && $this->registerHook('actionAdminControllerSetMedia');
    }

    public function getContent()
    {
        Tools::redirectAdmin('index.php?controller=AdminStats&module=statsforecast&token='.Tools::getAdminTokenLite('AdminStats'));
    }

    public function hookActionAdminControllerSetMedia()
    {
        if (Tools::getValue('controller') == 'AdminStats'
            && (Tools::getValue('module') == false || Tools::getValue('module') == 'statsforecast')
        ) {
            $this->context->controller->addCSS($this->getPathUri().'views/css/conversion-graph.css');
            $this->context->controller->addJS($this->getPathUri().'views/js/conversion-graph.js');
        }
    }

    public function hookAdminStatsModules()
    {
        Context::getContext()->controller->addJqueryPlugin('connections');

        $ru = AdminController::$currentIndex.'&module='.$this->name.'&token='.Tools::getValue('token');

        $db = Db::getInstance();

        if (!isset($this->context->cookie->stats_granularity)) {
            $this->context->cookie->stats_granularity = 10;
        }
        if (Tools::isSubmit('submitIdZone')) {
            $this->context->cookie->stats_id_zone = (int)Tools::getValue('stats_id_zone');
            Tools::redirectAdmin($ru);
        }
        if (Tools::isSubmit('submitGranularity')) {
            $this->context->cookie->stats_granularity = Tools::getValue('stats_granularity');
            Tools::redirectAdmin($ru);
        }

        $currency = $this->context->currency;
        $employee = $this->context->employee;

        $from = max((_PS_CREATION_DATE_.' 00:00:00'), ($employee->stats_date_from.' 00:00:00'));
		$to = date('Y-m-d H:i:s', strtotime('+1 day', strtotime($employee->stats_date_to.' 23:59:59')));
        $to2 = min(date('Y-m-d H:i:s', strtotime('+1 day')), $to);
		$interval =  HotelHelper::getNumberOfDays($from, $to);
		$interval2 =  HotelHelper::getNumberOfDays($from, $to2);
        $prop30 = $interval / $interval2;
		$to = strtotime($to);
		$to2 = strtotime($to2);
		$from = strtotime($from);

        if ($this->context->cookie->stats_granularity == 7) {
            $interval_avg = $interval2 / 30;
        }
        if ($this->context->cookie->stats_granularity == 4) {
            $interval_avg = $interval2 / 365;
        }
        if ($this->context->cookie->stats_granularity == 10) {
            $interval_avg = $interval2;
        }
        if ($this->context->cookie->stats_granularity == 42) {
            $interval_avg = $interval2 / 7;
        }

        $data_table = array();
        if ($this->context->cookie->stats_granularity == 10) {
            for ($i = $from; $i <= $to2; $i = strtotime('+1 day', $i)) {
                $data_table[date('Y-m-d', $i)] = array(
                    'fix_date' => date('Y-m-d', $i),
                    'countOrders' => 0,
                    'totalRoomsBooked' => 0,
                    'totalSales' => 0,
                    'totalOperatingCost' => 0,
                );
            }
        }

        $date_from_gadd = ($this->context->cookie->stats_granularity != 42
            ? 'LEFT(date_add, '.(int)$this->context->cookie->stats_granularity.')'
            : 'IFNULL(MAKEDATE(YEAR(date_add),DAYOFYEAR(date_add)-WEEKDAY(date_add)), CONCAT(YEAR(date_add),"-01-01*"))');

        $date_from_ginvoice = ($this->context->cookie->stats_granularity != 42
            ? 'LEFT(invoice_date, '.(int)$this->context->cookie->stats_granularity.')'
            : 'IFNULL(MAKEDATE(YEAR(invoice_date),DAYOFYEAR(invoice_date)-WEEKDAY(invoice_date)), CONCAT(YEAR(invoice_date),"-01-01*"))');

        $date_to_ginvoice = '';
        if ($this->context->cookie->stats_granularity == 42) {
            $date_to_ginvoice = 'DATE_ADD('.$date_from_ginvoice.', INTERVAL 6 DAY) as end_date,';
        }

        $result = $db->query(
            'SELECT
            '.$date_from_ginvoice.' AS fix_date,
            '.($this->context->cookie->stats_granularity == 42 ? $date_to_ginvoice : '').'
            COUNT(DISTINCT(o.`id_order`)) AS countOrders,
            SUM((SELECT IFNULL(SUM(DATEDIFF(hbd.`date_to`, hbd.`date_from`)), 0) FROM `'._DB_PREFIX_.'htl_booking_detail` hbd WHERE o.`id_order` = hbd.`id_order`)) AS totalRoomsBooked,
			SUM(o.`total_paid_tax_excl` / o.`conversion_rate`) AS totalSales,
            SUM((
                SELECT SUM(ROUND(IFNULL(DATEDIFF(hbd.`date_to`, hbd.`date_from`), 1) * (
                    CASE
                        WHEN od.`original_wholesale_price` <> "0.000000" THEN od.`original_wholesale_price`
                        WHEN p.`wholesale_price` <> "0.000000" THEN p.`wholesale_price`
                        ELSE 0
                    END
                ), 2))
                FROM `'._DB_PREFIX_.'order_detail` od
                LEFT JOIN `'._DB_PREFIX_.'product` p
                ON (p.`id_product` = od.`product_id`)
				LEFT JOIN `'._DB_PREFIX_.'htl_booking_detail` hbd
                ON (hbd.`id_product` = od.`product_id` AND hbd.`id_order` = od.`id_order`)
                WHERE od.`id_order` = o.`id_order`
            )) AS totalOperatingCost
            FROM '._DB_PREFIX_.'orders o
            WHERE o.`valid` = 1
            AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().'
            AND (
                EXISTS (
                    SELECT 1
                    FROM `'._DB_PREFIX_.'htl_booking_detail` hbd
                    WHERE hbd.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false).'
                ) OR EXISTS (
                    SELECT 1
                    FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                    WHERE spod.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false, 'spod').'
                ) OR EXISTS (
                    SELECT 1
                    FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                    WHERE spod.`id_order` = o.`id_order` AND spod.`id_hotel` = 0 AND spod.`id_htl_booking_detail` = 0
                )
            )
			'.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').'
            GROUP BY '.$date_from_ginvoice
        );

        while ($row = $db->nextRow($result)) {
            $data_table[$row['fix_date']] = $row;
        }

        $this->html .= '<div>
			<div class="panel-heading"><i class="icon-dashboard"></i> '.$this->displayName.'</div>
			<div class="alert alert-info">'.$this->l('The listed amounts do not include tax.').'</div>
			<form id="granularity" action="'.Tools::safeOutput($ru).'" method="post" class="form-horizontal">
				<div class="row row-margin-bottom">
					<label class="control-label col-lg-3">
						'.$this->l('Time frame').'
					</label>
					<div class="col-lg-2">
						<input type="hidden" name="submitGranularity" value="1" />
						<select name="stats_granularity" onchange="this.form.submit();">
							<option value="10">'.$this->l('Daily').'</option>
							<option value="42" '.($this->context->cookie->stats_granularity == '42' ? 'selected="selected"' : '').'>'.$this->l('Weekly').'</option>
							<option value="7" '.($this->context->cookie->stats_granularity == '7' ? 'selected="selected"' : '').'>'.$this->l('Monthly').'</option>
							<option value="4" '.($this->context->cookie->stats_granularity == '4' ? 'selected="selected"' : '').'>'.$this->l('Yearly').'</option>
						</select>
					</div>
				</div>
			</form>

			<table class="table">
				<thead>
					<tr>
						<th></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Visits').'</span></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Registrations').'</span></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Orders placed').'</span></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Booked rooms').'</span></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Percentage of registrations').'</span></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Conversion rate').'</span></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Revenue').'</span></th>
						<th class="text-left"><span class="title_box active">'.$this->l('Margin').'</span></th>
					</tr>
				</thead>';

        $visit_array = array();
        $sql = 'SELECT '.$date_from_gadd.' as fix_date, COUNT(*) as visits
        FROM '._DB_PREFIX_.'connections c
        WHERE c.`date_add` BETWEEN '.ModuleGraph::getDateBetween().'
        '.Shop::addSqlRestriction(false, 'c').'
        GROUP BY '.$date_from_gadd;
        $visits = Db::getInstance()->query($sql);
        while ($row = $db->nextRow($visits)) {
            $visit_array[$row['fix_date']] = $row['visits'];
        }

        foreach ($data_table as $row) {
            $visits_today = (int)(isset($visit_array[$row['fix_date']]) ? $visit_array[$row['fix_date']] : 0);

            $date_from_greg = ($this->context->cookie->stats_granularity != 42
                ? 'LIKE \''.$row['fix_date'].'%\''
                : 'BETWEEN \''.Tools::substr($row['fix_date'], 0, 10).' 00:00:00\' AND DATE_ADD(\''.Tools::substr($row['fix_date'], 0, 8).Tools::substr($row['fix_date'], 8, 2).' 23:59:59\', INTERVAL 7 DAY)');
            $row['registrations'] = Db::getInstance()->getValue('
			SELECT COUNT(*) FROM '._DB_PREFIX_.'customer
			WHERE date_add BETWEEN '.ModuleGraph::getDateBetween().'
			AND date_add '.$date_from_greg
                .Shop::addSqlRestriction(Shop::SHARE_CUSTOMER));

            $this->html .= '
			<tr>
				<td class="fixed-width-sm">'.($this->context->cookie->stats_granularity == 42 ? (Tools::displayDate($row['fix_date']).' - '.Tools::displayDate($row['end_date'])) : $row['fix_date']).'</td>
				<td class="text-left">'.$visits_today.'</td>
				<td class="text-left">'.(int)$row['registrations'].'</td>
				<td class="text-left">'.(int)$row['countOrders'].'</td>
				<td class="text-left">'.(int)$row['totalRoomsBooked'].'</td>
				<td class="text-left">'.sprintf('%0.2f', $visits_today ? ($row['registrations'] / $visits_today) * 100 : 0).' %'.'</td>
				<td class="text-left">'.sprintf('%0.2f', $visits_today ? ($row['countOrders'] / $visits_today) * 100 : 0).' %'.'</td>
				<td class="text-left">'.Tools::displayPrice($row['totalSales'], $currency).'</td>
				<td class="text-left">'.Tools::displayPrice(($row['totalSales'] - $row['totalOperatingCost']), $currency).'</td>
			</tr>';

            $this->t1 += $visits_today;
            $this->t2 += (int)$row['registrations'];
            $this->t3 += (int)$row['countOrders'];
            $this->t4 += (int)$row['totalRoomsBooked'];
            $this->t8 += $row['totalSales'];
            $this->t9 += ($row['totalSales'] - $row['totalOperatingCost']);
        }

        $this->html .= '
				<tr>
					<th></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Visits').'</span></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Registrations').'</span></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Orders placed').'</span></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Booked rooms').'</span></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Percentage of registrations').'</span></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Conversion rate').'</span></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Revenue').'</span></th>
                    <th class="text-left"><span class="title_box active">'.$this->l('Margin').'</span></th>
				</tr>
				<tr>
					<td>'.$this->l('Total').'</td>
					<td class="text-left">'.(int)$this->t1.'</td>
					<td class="text-left">'.(int)$this->t2.'</td>
					<td class="text-left">'.(int)$this->t3.'</td>
					<td class="text-left">'.(int)$this->t4.'</td>
					<td class="text-left">--</td>
					<td class="text-left">--</td>
					<td class="text-left">'.Tools::displayPrice($this->t8, $currency).'</td>
					<td class="text-left">'.Tools::displayPrice($this->t9, $currency).'</td>
				</tr>
				<tr>
					<td>'.$this->l('Average').'</td>
					<td class="text-left">'.sprintf('%0.2f', $this->t1 / $interval_avg).'</td>
					<td class="text-left">'.sprintf('%0.2f', $this->t2 / $interval_avg).'</td>
					<td class="text-left">'.sprintf('%0.2f', $this->t3 / $interval_avg).'</td>
					<td class="text-left">'.sprintf('%0.2f', $this->t4 / $interval_avg).'</td>
					<td class="text-left">'.sprintf('%0.2f', ($this->t1 ? ($this->t2 / $this->t1) * 100 : 0)).' %</td>
					<td class="text-left">'.sprintf('%0.2f', ($this->t1 ? ($this->t3 / $this->t1) * 100 : 0)).' %</td>
					<td class="text-left">'.Tools::displayPrice($this->t8 / $interval_avg, $currency).'</td>
					<td class="text-left">'.Tools::displayPrice($this->t9 / $interval_avg, $currency).'</td>
				</tr>
				<tr>
					<td>'.$this->l('Forecast').'</td>
					<td class="text-left">'.(int)($this->t1 * $prop30).'</td>
					<td class="text-left">'.(int)($this->t2 * $prop30).'</td>
					<td class="text-left">'.(int)($this->t3 * $prop30).'</td>
					<td class="text-left">'.(int)($this->t4 * $prop30).'</td>
					<td class="text-left">--</td>
					<td class="text-left">--</td>
					<td class="text-left">'.Tools::displayPrice($this->t8 * $prop30, $currency).'</td>
					<td class="text-left">'.Tools::displayPrice($this->t9 * $prop30, $currency).'</td>
				</tr>
			</table>
		</div>';

        $ca = $this->getRealCA();

        $sql = 'SELECT COUNT(DISTINCT c.id_guest)
        FROM '._DB_PREFIX_.'connections c
        WHERE c.date_add BETWEEN '.ModuleGraph::getDateBetween();
        $visitors = Db::getInstance()->getValue($sql);

        $sql = 'SELECT COUNT(DISTINCT g.`id_customer`)
        FROM '._DB_PREFIX_.'connections c
        INNER JOIN '._DB_PREFIX_.'guest g ON c.id_guest = g.id_guest
        WHERE g.id_customer != 0
        AND c.date_add BETWEEN '.ModuleGraph::getDateBetween();
        $customers = Db::getInstance()->getValue($sql);

        $sql = 'SELECT COUNT(DISTINCT g.`id_guest`)
        FROM '._DB_PREFIX_.'connections c
        INNER JOIN '._DB_PREFIX_.'guest g ON c.`id_guest` = g.`id_guest`
        WHERE g.`id_customer` = 0
        AND c.`date_add` BETWEEN '.ModuleGraph::getDateBetween();
        $guests = Db::getInstance()->getValue($sql);

        $sql = 'SELECT COUNT(DISTINCT c.`id_cart`)
        FROM '._DB_PREFIX_.'cart c
        WHERE c.`id_customer` != 0
        AND (
            EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'htl_cart_booking_data` hcbd
                WHERE hcbd.`id_cart` = c.`id_cart`' . HotelBranchInformation::addHotelRestriction(false, 'hcbd').'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_cart_detail` spcd
                WHERE spcd.`id_cart` = c.`id_cart`' . HotelBranchInformation::addHotelRestriction(false, 'spcd').'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_cart_detail` spcd
                WHERE spcd.`id_cart` = c.`id_cart` AND spcd.`id_hotel` = 0 AND spcd.`htl_cart_booking_id` = 0
            )
        )
        AND (c.`date_add` BETWEEN '.ModuleGraph::getDateBetween().' OR c.`date_upd` BETWEEN '.ModuleGraph::getDateBetween().')';
        $carts_registered = Db::getInstance()->getValue($sql);

        $sql = 'SELECT COUNT(DISTINCT c.`id_cart`)
        FROM '._DB_PREFIX_.'cart c
		INNER JOIN `'._DB_PREFIX_.'htl_cart_booking_data` hcbd ON (hcbd.`id_cart` = c.`id_cart`)
        WHERE c.`id_customer` = 0
		AND (
            EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'htl_cart_booking_data` hcbd
                WHERE hcbd.`id_cart` = c.`id_cart`' . HotelBranchInformation::addHotelRestriction(false, 'hcbd').'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_cart_detail` spcd
                WHERE spcd.`id_cart` = c.`id_cart`' . HotelBranchInformation::addHotelRestriction(false, 'spcd').'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_cart_detail` spcd
                WHERE spcd.`id_cart` = c.`id_cart` AND spcd.`id_hotel` = 0 AND spcd.`htl_cart_booking_id` = 0
            )
        )
        AND (c.`date_add` BETWEEN '.ModuleGraph::getDateBetween().' OR c.`date_upd` BETWEEN '.ModuleGraph::getDateBetween().')';
        $carts_unregistered = Db::getInstance()->getValue($sql);

        $sql = 'SELECT COUNT(DISTINCT(o.`id_order`))
        FROM '._DB_PREFIX_.'orders o
        WHERE o.`valid` = 1
		AND (
            EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'htl_booking_detail` hbd
                WHERE hbd.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false).'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                WHERE spod.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false, 'spod').'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                WHERE spod.`id_order` = o.`id_order` AND spod.`id_hotel` = 0 AND spod.`id_htl_booking_detail` = 0
            )
        )
		AND o.`date_add` BETWEEN '.ModuleGraph::getDateBetween();
        $orders = Db::getInstance()->getValue($sql);

        $sql = 'SELECT COUNT(DISTINCT(o.`id_order`))
        FROM '._DB_PREFIX_.'orders o
        INNER JOIN '._DB_PREFIX_.'customer c ON c.`id_customer` = o.`id_customer`
        WHERE o.`valid` = 1 AND c.`is_guest` = 0
		AND (
            EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'htl_booking_detail` hbd
                WHERE hbd.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false).'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                WHERE spod.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false, 'spod').'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                WHERE spod.`id_order` = o.`id_order` AND spod.`id_hotel` = 0 AND spod.`id_htl_booking_detail` = 0
            )
        )
        AND o.`date_add` BETWEEN '.ModuleGraph::getDateBetween();
        $orders_registered = Db::getInstance()->getValue($sql);

        $sql = 'SELECT COUNT(DISTINCT(o.`id_order`))
        FROM '._DB_PREFIX_.'orders o
        INNER JOIN '._DB_PREFIX_.'customer c ON c.`id_customer` = o.`id_customer`
        WHERE o.`valid` = 1 AND c.`is_guest` = 1
		AND (
            EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'htl_booking_detail` hbd
                WHERE hbd.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false).'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                WHERE spod.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false, 'spod').'
            ) OR EXISTS (
                SELECT 1
                FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                WHERE spod.`id_order` = o.`id_order` AND spod.`id_hotel` = 0 AND spod.`id_htl_booking_detail` = 0
            )
        )
        AND o.`date_add` BETWEEN '.ModuleGraph::getDateBetween();
        $orders_unregistered = Db::getInstance()->getValue($sql);

        $this->context->smarty->assign(array(
            'conversion_graph_data' => array(
                'values' => array(
                    'visitors' => $visitors,
                    'visitors_registered' => $customers,
                    'visitors_unregistered' => $guests,
                    'carts_registered' => $carts_registered,
                    'carts_unregistered' => $carts_unregistered,
                    'orders' => $orders,
                    'orders_registered' => $orders_registered,
                    'orders_unregistered' => $orders_unregistered,
                ),
                'percentages' => array(
                    'visitors_registered' => sprintf('%0.2f', ($visitors ? $customers / $visitors : 0) * 100),
                    'visitors_unregistered' => sprintf('%0.2f', ($visitors ? $guests / $visitors : 0) * 100),
                    'orders' => sprintf('%0.2f', ($orders ? $carts_registered / $orders : 0) * 100),
                    'orders_top_registered' => sprintf('%0.2f', ($carts_registered ? $orders_registered / $carts_registered : 0) * 100),
                    'orders_top_unregistered' => sprintf('%0.2f', ($carts_unregistered ? $orders_unregistered / $carts_unregistered : 0) * 100),
                    'orders_bottom_registered' => sprintf('%0.2f', ($orders ? $orders_registered / $orders : 0) * 100),
                    'orders_bottom_unregistered' => sprintf('%0.2f', ($orders ? $orders_unregistered / $orders : 0) * 100),
                ),
            ),
        ));

        $this->html .= '
		<div class="row row-margin-bottom">
			<h4><i class="icon-filter"></i> '.$this->l('Conversion').'</h4>
		</div>
		<div class="row row-margin-bottom">'.
            $this->context->smarty->fetch($this->getLocalPath().'views/templates/conversion.tpl').'
		</div>
		<div class="alert alert-info">
			<p>'.$this->l('A simple statistical calculation lets you know the monetary value of your visitors:').'</p>
			<p>'.$this->l('On average, each visitor places an order for this amount:').' <b>'.Tools::displayPrice($ca['ventil']['total'] / max(1, $visitors), $currency).'.</b></p>
			<p>'.$this->l('On average, each registered visitor places an order for this amount:').' <b>'.Tools::displayPrice($ca['ventil']['total'] / max(1, $customers), $currency).'</b>.</p>
		</div>';

        $from = strtotime($employee->stats_date_from.' 00:00:00');
        $to = strtotime($employee->stats_date_to.' 23:59:59');
        $interval = ($to - $from) / 60 / 60 / 24;

        $this->html .= '
			<div class="row row-margin-bottom">
				<h4><i class="icon-money"></i> '.$this->l('Payment distribution').'</h4>
				<div class="alert alert-info">'
            .$this->l('The amounts in this section include taxes, so you can get an estimation of the commission due to the payment method.').'
				</div>
				<form id="cat" action="'.Tools::safeOutput($ru).'#payment" method="post" class="form-horizontal">
					<div class="row row-margin-bottom">
						<label class="control-label col-lg-3">
							'.$this->l('Zone:').'
						</label>
						<div class="col-lg-3">
							<input type="hidden" name="submitIdZone" value="1" />
							<select name="stats_id_zone" onchange="this.form.submit();">
								<option value="0">'.$this->l('-- No filter --').'</option>';
        foreach (Zone::getZones() as $zone) {
            $this->html .= '<option value="'.(int)$zone['id_zone'].'" '.($this->context->cookie->stats_id_zone == $zone['id_zone'] ? 'selected="selected"' : '').'>'.$zone['name'].'</option>';
        }
        $this->html .= '
							</select>
						</div>
					</div>
				</form>
				<table class="table">
					<thead>
						<tr>
							<th class="text-left"><span class="title_box active">'.$this->l('Module').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Orders').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Payment').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Percentage of orders').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Percentage of revenue').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Average payment value').'</span></th>
						</tr>
					</thead>
					<tbody>';
		if (is_array($ca['payment']) && count($ca['payment'])) {
			foreach ($ca['payment'] as $payment) {
				$this->html .= '
						<tr>
							<td class="text-left">'.$payment['payment_method'].'</td>
							<td class="text-left">'.(int)$payment['nb'].'</td>
							<td class="text-left">'.Tools::displayPrice($payment['total'], $currency).'</td>
							<td class="text-left">'.sprintf('%0.2f', ($ca['ventil']['nb'] ? ($payment['nb'] / $ca['ventil']['nb']) * 100 : 0)).'%</td>
							<td class="text-left">'.sprintf('%0.2f', ($ca['ventil']['total_incl'] > 0 ? ($payment['total'] / $ca['ventil']['total_incl']) * 100 : 0)).'%</td>
							<td class="text-left">'.Tools::displayPrice($payment['total'] / (int)$payment['nb'], $currency).'</td>
						</tr>';
			}
		} else {
			$this->html .= '
				<tr>
					<td colspan="6" class="text-center">'.$this->l('No data available.').'</td>
				</tr>';
		}
        $this->html .= '
					</tbody>
				</table>
			</div>
			<div class="row row-margin-bottom">
				<h4><i class="icon-flag"></i> '.$this->l('Language distribution').'</h4>
				<table class="table">
					<thead>
						<tr>
							<th class="text-left"><span class="title_box active">'.$this->l('Language').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Revenue').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Percentage of revenue').'</span></th>
						</tr>
					</thead>
					<tbody>';
		if (is_array($ca['lang']) && count($ca['lang'])) {
			foreach ($ca['lang'] as $ophone => $amount) {
				$this->html .= '
						<tr>
							<td class="text-left">'.$ophone.'</td>
							<td class="text-left">'.Tools::displayPrice($amount, $currency).'</td>
							<td class="text-left">'.sprintf('%0.2f', ($ca['ventil']['total'] > 0 ? ($amount / $ca['ventil']['total']) * 100 : 0)).'%</td>
						</tr>';
			}
		} else {
			$this->html .= '
				<tr>
					<td colspan="5" class="text-center">'.$this->l('No data available.').'</td>
				</tr>';
		}
        $this->html .= '
					</tbody>
				</table>
			</div>
			<div class="row row-margin-bottom">
				<h4><i class="icon-map-marker"></i> '.$this->l('Zone distribution').'</h4>
				<table class="table">
					<thead>
						<tr>
							<th class="text-left"><span class="title_box active">'.$this->l('Zone').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Orders').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Revenue').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Percentage of orders').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Percentage of revenue').'</span></th>
						</tr>
					</thead>
					<tbody>';
		if (is_array($ca['zones']) && count($ca['zones'])) {
			foreach ($ca['zones'] as $zone) {
				$this->html .= '
						<tr>
							<td class="text-left">'.(isset($zone['name']) ? $zone['name'] : $this->l('Undefined')).'</td>
							<td class="text-left">'.(int)($zone['nb']).'</td>
							<td class="text-left">'.Tools::displayPrice($zone['total'], $currency).'</td>
							<td class="text-left">'.sprintf('%0.2f', $ca['ventil']['nb'] ? ($zone['nb'] / $ca['ventil']['nb']) * 100 : 0).'%</td>
							<td class="text-left">'.sprintf('%0.2f', $ca['ventil']['total'] > 0 ? ($zone['total'] / $ca['ventil']['total']) * 100 : 0).'%</td>
						</tr>';
			}
		} else {
			$this->html .= '
				<tr>
					<td colspan="5" class="text-center">'.$this->l('No data available.').'</td>
				</tr>';
		}
        $this->html .= '
					</tbody>
				</table>
			</div>
			<div class="row row-margin-bottom">
				<h4><i class="icon-money"></i> '.$this->l('Currency distribution').'</h4>
				<form id="cat_2" action="'.Tools::safeOutput($ru).'#currencies" method="post" class="form-horizontal">
					<div class="row row-margin-bottom">
						<label class="control-label col-lg-3">
							'.$this->l('Zone:').'
						</label>
						<div class="col-lg-3">
							<input type="hidden" name="submitIdZone" value="1" />
							<select name="stats_id_zone" onchange="this.form.submit();">
								<option value="0">'.$this->l('-- No filter --').'</option>';
        foreach (Zone::getZones() as $zone) {
            $this->html .= '<option value="'.(int)$zone['id_zone'].'" '.($this->context->cookie->stats_id_zone == $zone['id_zone'] ? 'selected="selected"' : '').'>'.$zone['name'].'</option>';
        }
        $this->html .= '
							</select>
						</div>
					</div>
				</form>
				<table class="table">
					<thead>
						<tr>
							<th class="text-left"><span class="title_box active">'.$this->l('Currency').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Orders').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Revenue (converted)').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Percentage of orders').'</span></th>
							<th class="text-left"><span class="title_box active">'.$this->l('Percentage of revenue').'</span></th>
						</tr>
					</thead>
					<tbody>';
		if (is_array($ca['currencies']) && count($ca['currencies'])) {
			foreach ($ca['currencies'] as $currency_row) {
				$this->html .= '
							<tr>
								<td class="text-left">'.$currency_row['name'].'</td>
								<td class="text-left">'.(int)($currency_row['nb']).'</td>
								<td class="text-left">'.Tools::displayPrice($currency_row['total'], $currency).'</td>
								<td class="text-left">'.sprintf('%0.2f', $ca['ventil']['nb'] ? ($currency_row['nb'] / $ca['ventil']['nb']) * 100 : 0).'%</td>
								<td class="text-left">'.sprintf('%0.2f', $ca['ventil']['total'] > 0 ? ($currency_row['total'] / $ca['ventil']['total']) * 100 : 0).'%</td>
							</tr>';
			}
		} else {
			$this->html .= '
				<tr>
					<td colspan="5" class="text-center">'.$this->l('No data available.').'</td>
				</tr>';
		}
        $this->html .= '
					</tbody>
				</table>
			</div>';

        return $this->html;
    }

    private function getRealCA()
    {
        $employee = $this->context->employee;
        $ca = array();

        $where = $join = '';
        if ((int)$this->context->cookie->stats_id_zone) {
            $join = ' LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_invoice = a.id_address LEFT JOIN `'._DB_PREFIX_.'country` co ON co.id_country = a.id_country';
            $where = ' AND co.id_zone = '.(int)$this->context->cookie->stats_id_zone.' ';
        }

		if (!$idOrders = Db::getInstance()->getValue('
			SELECT GROUP_CONCAT(DISTINCT o.`id_order`)
			FROM `'._DB_PREFIX_.'orders` o
			WHERE  o.valid = 1 AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().'
            AND (
                EXISTS (
                    SELECT 1
                    FROM `'._DB_PREFIX_.'htl_booking_detail` hbd
                    WHERE hbd.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false).'
                ) OR EXISTS (
                    SELECT 1
                    FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                    WHERE spod.`id_order` = o.`id_order`' . HotelBranchInformation::addHotelRestriction(false, 'spod').'
                ) OR EXISTS (
                    SELECT 1
                    FROM `'._DB_PREFIX_.'service_product_order_detail` spod
                    WHERE spod.`id_order` = o.`id_order` AND spod.`id_hotel` = 0 AND spod.`id_htl_booking_detail` = 0
                )
            )
			'.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o')
		)) {
			$idOrders = 0;
		}

        $lang_values = '';
        $sql = 'SELECT l.id_lang, l.iso_code
				FROM `'._DB_PREFIX_.'lang` l
				'.Shop::addSqlAssociation('lang', 'l').'
				WHERE l.active = 1';
        $languages = Db::getInstance()->executeS($sql);
        foreach ($languages as $language) {
            $lang_values .= 'SUM(IF(o.id_lang = '.(int)$language['id_lang'].', total_paid_tax_excl / o.conversion_rate, 0)) as '.pSQL($language['iso_code']).',';
        }
        $lang_values = rtrim($lang_values, ',');

        if ($lang_values) {
            $sql = 'SELECT '.$lang_values.'
					FROM `'._DB_PREFIX_.'orders` o
					WHERE o.`id_order` IN ('.pSQL($idOrders).')';
            $ca['lang'] = Db::getInstance()->getRow($sql);
            arsort($ca['lang']);
        } else {
            $ca['lang'] = array();
        }

        $sql = 'SELECT id_order
					FROM `'._DB_PREFIX_.'orders` o
					'.$join.'
					WHERE o.`id_order` IN ('.pSQL($idOrders).')
					'.$where;
        $result = Db::getInstance()->executeS($sql);
        if (count($result)) {
            $orderIdPayment = array();
            foreach ($result as $r) {
                $orderIdPayment[] = $r['id_order'];
            }
            $sql = 'SELECT op.`payment_method`, SUM(opd.`amount` / op.`conversion_rate`) as total, COUNT(DISTINCT opd.`id_order`) as nb
					FROM `'._DB_PREFIX_.'order_payment` op
					INNER JOIN `'._DB_PREFIX_.'order_payment_detail` opd ON (op.`id_order_payment` = opd.`id_order_payment`)
					WHERE opd.`id_order` IN ('.implode(',', $orderIdPayment).')
					GROUP BY op.payment_method
					ORDER BY total DESC';
            $ca['payment'] = Db::getInstance()->executeS($sql);
        } else {
            $ca['payment'] = array();
        }

        $sql = 'SELECT z.name, SUM(o.`total_paid_tax_excl` / o.`conversion_rate`) as total, COUNT(*) as nb
				FROM `'._DB_PREFIX_.'orders` o
				LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_invoice = a.id_address
				LEFT JOIN `'._DB_PREFIX_.'country` c ON c.id_country = a.id_country
				LEFT JOIN `'._DB_PREFIX_.'zone` z ON z.id_zone = c.id_zone
				WHERE o.valid = 1
					'.(isset($idOrders) ? ' AND o.`id_order` IN ('.pSQL($idOrders).')':'').'
					AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().'
					'.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').'
				GROUP BY c.id_zone
				ORDER BY total DESC';
        $ca['zones'] = Db::getInstance()->executeS($sql);

        $sql = 'SELECT cu.name, SUM(o.`total_paid_tax_excl` / o.`conversion_rate`) as total, COUNT(*) as nb
				FROM `'._DB_PREFIX_.'orders` o
				LEFT JOIN `'._DB_PREFIX_.'currency` cu ON o.id_currency = cu.id_currency
				'.$join.'
				WHERE o.`id_order` IN ('.pSQL($idOrders).')
				'.$where.'
				GROUP BY o.id_currency
				ORDER BY total DESC';
        $ca['currencies'] = Db::getInstance()->executeS($sql);

        $sql = 'SELECT SUM(total_paid_tax_excl / o.`conversion_rate`) as total,
			SUM(total_paid_tax_incl / o.`conversion_rate`) as total_incl, COUNT(*) AS nb
				FROM `'._DB_PREFIX_.'orders` o
				WHERE o.`id_order` IN ('.pSQL($idOrders).')';
        $ca['ventil'] = Db::getInstance()->getRow($sql);

        $sql = 'SELECT /*pac.id_attribute,*/ agl.name as gname, al.name as aname, COUNT(*) as total
				FROM '._DB_PREFIX_.'orders o
				LEFT JOIN '._DB_PREFIX_.'order_detail od ON o.id_order = od.id_order
				INNER JOIN '._DB_PREFIX_.'product_attribute_combination pac ON od.product_attribute_id = pac.id_product_attribute
				INNER JOIN '._DB_PREFIX_.'attribute a ON pac.id_attribute = a.id_attribute
				INNER JOIN '._DB_PREFIX_.'attribute_group_lang agl ON (a.id_attribute_group = agl.id_attribute_group AND agl.id_lang = '.(int)$this->context->language->id.')
				INNER JOIN '._DB_PREFIX_.'attribute_lang al ON (a.id_attribute = al.id_attribute AND al.id_lang = '.(int)$this->context->language->id.')
				WHERE o.valid = 1
					AND o.`invoice_date` BETWEEN '.ModuleGraph::getDateBetween().'
					'.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').'
				GROUP BY pac.id_attribute';
        $ca['attributes'] = Db::getInstance()->executeS($sql);

        return $ca;
    }
}
