<?php
/**
 *    This file is part of Mobile Assistant Connector.
 *
 *   Mobile Assistant Connector is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   Mobile Assistant Connector is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with Mobile Assistant Connector.  If not, see <http://www.gnu.org/licenses/>.
 *
 *  @author    eMagicOne <contact@emagicone.com>
 *  @copyright 2014-2017 eMagicOne
 *  @license   http://www.gnu.org/licenses   GNU General Public License
 */

class MobassistantconnectorMobassistantconnectorModuleFrontController extends ModuleFrontController
{
    private $module_name = 'mobassistantconnector';
    private $mob_api_key = 'MOBASSISTANTCONNECTOR_API_KEY';
    private $call_function;
    private $shop_id;
    private $callback;
    private $hash = false;
    private $session_key;
    private $def_currency;
    private $def_lang;
    private $file_logger;
    private $def_shop;
    private $weight_unit;
    private $cart_version;
    private $app_connection_id;
    private $registration_id;
    private $registration_id_old;
    private $api_key;
    private $push_new_order;
    private $push_order_statuses;
    private $push_new_customer;
    private $push_currency_code;
    private $account_email;
    private $device_name;
    private $device_unique_id;
    private $currency_code;
    private $action;
    private $product_id;
    private $statuses;
    private $orders_from;
    private $orders_to;
    private $search_order_id;
    private $page;
    private $show;
    private $get_statuses;
    private $order_id;
    private $only_items;
    private $user_id;
    private $new_status;
    private $is_mail;
    private $carts_from;
    private $carts_to;
    private $cart_id;
    private $order_by;
    private $params;
    private $val;
    private $sort_by;
    private $group_by_product_id;
    private $search_carts;
    private $check_permission;

    const MODULE_VERSION   = '51';
    const CURRENCY_NOT_SET = 'not_set';
    const GUEST            = 'Guest not registered';

    public function init()
    {
        self::checkVersionDefined();
        self::includeFiles();
        parent::init();
        $this->indexAction();
    }

    private function indexAction()
    {
        if (!Module::isEnabled($this->module_name)) {
            $this->generateOutput('module_disabled');
        }

        $this->def_lang     = (int)Configuration::get('PS_LANG_DEFAULT');
        $this->def_currency = (int)Configuration::get('PS_CURRENCY_DEFAULT');
        $this->cart_version = Configuration::get('PS_INSTALL_VERSION');
        $this->weight_unit  = Configuration::get('PS_WEIGHT_UNIT');
        $this->def_shop     = (int)Configuration::get('PS_SHOP_DEFAULT');

        $this->file_logger = new FileLoggerMob();
        $this->file_logger->setFilename(_PS_MODULE_DIR_.$this->module_name.'/log/'.Constants::LOG_FILENAME);

        MobassistantconnectorAccess::clearOldData();

        $this->callback = Tools::getIsset('callback') ? Tools::getValue('callback') : null;
        $this->call_function = Tools::getIsset('call_function') ? Tools::getValue('call_function') : '';
        $this->hash = Tools::getIsset('hash') ? Tools::getValue('hash') : null;
        $this->session_key = Tools::getIsset('key') ? Tools::getValue('key') : null;
        $this->device_unique_id = Tools::getIsset('device_unique_id') ? Tools::getValue('device_unique_id') : null;
        $this->registration_id = Tools::getIsset('registration_id') ? Tools::getValue('registration_id') : null;
        $this->account_email = Tools::getIsset('account_email') ? Tools::getValue('account_email') : null;
        $this->device_name = Tools::getIsset('device_name') ? Tools::getValue('device_name') : null;

        if (Tools::getIsset('hash_only')) {
            $this->generateOutput('You should update PrestaShop Mobile Assistant application');
        }

        if (empty($this->call_function)) {
            $this->runSelfTest();
        }

        $this->mapPushNotificationToDevice();
        $this->updateDeviceLastActivity();

        if ($this->call_function == 'get_qr_code' && $this->hash) {
            $this->getQrCode($this->hash);
        }

        if ($this->call_function == 'get_version') {
            $this->getVersion();
        }

        if ($this->hash) {
            $key = MobassistantconnectorAccess::getSessionKey($this->hash);

            if (!$key) {
                $this->file_logger->logMessageCall('Hash accepted is incorrect', $this->file_logger->level);
                $this->generateOutput('auth_error');
            }

            $this->generateOutput(array('session_key' => $key));
        } elseif ($this->session_key || $this->session_key === '') {
            if (!MobassistantconnectorAccess::checkSessionKey($this->session_key)) {
                $this->file_logger->logMessageCall('Key accepted is incorrect', $this->file_logger->level);
                $this->generateOutput(array('bad_session_key' => true));
            }
        } else {
            MobassistantconnectorAccess::addFailedAttempt();
            $this->file_logger->logMessageCall('Authorization error', $this->file_logger->level);
            $this->generateOutput('auth_error');
        }

        /*if (!$this->call_function)
        {
            $this->file_logger->logMessageCall("Parameter 'call_function' is missing", $this->file_logger->level);
            $this->generateOutput(array('error' => 'call_function is missing'));
        }*/

//        if (!$this->checkAuth() && $this->call_function != 'run_self_test')
//            $this->generateOutput('auth_error');

        $request_params = $_REQUEST;
        $params = $this->validateTypes($request_params, array(
            'show'                => 'INT',
            'page'                => 'INT',
            'is_mail'             => 'INT',
            'search_order_id'     => 'STR',
            'orders_from'         => 'STR',
            'orders_to'           => 'STR',
            'customers_from'      => 'STR',
            'customers_to'        => 'STR',
            'date_from'           => 'STR',
            'date_to'             => 'STR',
            'graph_from'          => 'STR',
            'graph_to'            => 'STR',
            'stats_from'          => 'STR',
            'stats_to'            => 'STR',
            'products_to'         => 'STR',
            'products_from'       => 'STR',
            'order_id'            => 'INT',
            'user_id'             => 'INT',
            'params'              => 'STR',
            'val'                 => 'STR',
            'search_val'          => 'STR',
            'statuses'            => 'STR',
            'sort_by'             => 'STR',
            'last_order_id'       => 'STR',
            'product_id'          => 'INT',
            'get_statuses'        => 'INT',
            'cust_with_orders'    => 'INT',
            'data_for_widget'     => 'INT',
            'registration_id'     => 'STR',
            'registration_id_old' => 'STR',
            'api_key'             => 'STR',
            'push_new_order'      => 'INT',
            'push_order_statuses' => 'STR',
            'account_email'       => 'STR',
            'device_name'         => 'STR',
            'device_unique_id'    => 'STR',
            'push_new_customer'   => 'INT',
            'app_connection_id'   => 'INT',
            'push_currency_code'  => 'INT',
            'tracking_number'     => 'STR',
            'tracking_title'      => 'STR',
            'action'              => 'STR',
            'carrier_code'        => 'STR',
            'custom_period'       => 'INT',
            'shop_id'             => 'INT',
            'cart_id'             => 'INT',
            'new_status'          => 'INT',
            'currency_code'       => 'STR',
            'fc'                  => 'STR',
            'module'              => 'STR',
            'controller'          => 'STR',
            'search_carts'        => 'STR',
            'carts_from'          => 'STR',
            'carts_to'            => 'STR',
            'key'                 => 'STR',
            'only_items'          => 'INT',
            'order_by'            => 'STR',
            'group_by_product_id' => 'INT',
            'check_permission'    => 'STR',
        ));

        foreach ($params as $k => $value) {
            $this->{$k} = $value;

            if ($k == 'currency_code') {
                if ($value == self::CURRENCY_NOT_SET) {
                    $this->{$k} = $value;
                } elseif ($value == 'base_currency' || Currency::getCurrencyInstance((int)$value)->id === null) {
                    $this->{$k} = (int)Configuration::get('PS_CURRENCY_DEFAULT');
                } else {
                    $this->{$k} = (int)$value;
                }
            }
        }

        $this->show = (empty($this->show) || $this->show < 1) ? 25 : $this->show;
        $this->page = (empty($this->page) || $this->page < 1) ? 1 : $this->page;

        if ($this->call_function == 'test_config') {
            $result = array('test' => 1);

            if ($this->check_permission) {
                $this->call_function = $this->check_permission;
                $result['permission_granted'] = $this->isActionAllowed() ? '1' : '0';
            }

            $this->generateOutput($result);
        }

        $this->checkAllowedActions();
        $result = $this->getMethodResult($this->call_function);

        if ($this->call_function == 'get_order_pdf') {
            die($result);
        } else {
            $this->generateOutput($result);
        }
    }

    private function getMethodResult($call_function)
    {
        $result = false;

        switch ($call_function) {
            case 'run_self_test':
                $this->runSelfTest();
                break;
            case 'get_stores':
                $result = $this->getStores();
                break;
            case 'get_currencies':
                $result = $this->getCurrencies();
                break;
            case 'get_store_title':
                $result = $this->getStoreTitle();
                break;
            case 'push_notification_settings':
                $result = $this->pushNotificationSettings();
                break;
            case 'get_orders_statuses':
                $result = $this->getOrdersStatuses();
                break;
            case 'get_store_stats':
                $result = $this->getStoreStats();
                break;
            case 'get_data_graphs':
                $result = $this->getDataGraphs();
                break;
            case 'get_status_stats':
                $result = $this->getStatusStats();
                break;
            case 'search_products':
                $result = $this->searchProducts();
                break;
            case 'search_products_ordered':
                $result = $this->searchProductsOrdered();
                break;
            case 'get_products_info':
                $result = $this->getProductsInfo();
                break;
            case 'get_products_descr':
                $result = $this->getProductsDescr();
                break;
            case 'get_customers':
                $result = $this->getCustomers();
                break;
            case 'get_customers_info':
                $result = $this->getCustomersInfo();
                break;
            case 'get_orders':
                $result = $this->getOrders();
                break;
            case 'get_orders_info':
                $result = $this->getOrdersInfo();
                break;
            case 'get_order_product_list_pickup':
                $result = $this->getOrderProductListPickup();
                break;
            case 'set_order_action':
                $result = $this->setOrderAction();
                break;
            case 'get_carriers':
                $result = $this->getCarriers();
                break;
            case 'get_abandoned_carts_list':
                $result = $this->getAbandonedCarts();
                break;
            case 'get_abandoned_cart_details':
                $result = $this->getAbandonedCartInfo();
                break;
            case 'get_order_pdf':
                $result = $this->getOrderPdf();
                break;
            case 'delete_push_config':
                $result = $this->deletePushConfig();
                break;
            default:
                $this->file_logger->logMessageCall(
                    "Unknown method call ({$this->call_function})",
                    $this->file_logger->level
                );
                $this->generateOutput('old_module');
                break;
        }

        return $result;
    }

    private function validateTypes(&$array, $names)
    {
        foreach ($names as $name => $type) {
            if (isset($array["$name"])) {
                switch ($type) {
                    case 'INT':
                        $array["$name"] = (int)$array["$name"];
                        break;
                    case 'FLOAT':
                        $array["$name"] = (float)$array["$name"];
                        break;
                    case 'STR':
                        $array["$name"] = str_replace(
                            array("\r", "\n"),
                            ' ',
                            addslashes(htmlspecialchars(trim(urldecode($array["$name"]))))
                        );
                        break;
                    case 'STR_HTML':
                        $array["$name"] = addslashes(trim(urldecode($array["$name"])));
                        break;
                    default:
                        $array["$name"] = '';
                }
            } else {
                $array["$name"] = '';
            }
        }

        $array_keys = array_keys($array);

        foreach ($array_keys as $key) {
            if (!isset($names[$key]) && $key != 'call_function' && $key != 'hash') {
                $array[$key] = '';
            }
        }

        return $array;
    }

    private function runSelfTest()
    {
        $html = '<h2>Mobile Assistant Connector (v. '.Module::getInstanceByName($this->module_name)->version.')</h2>
            <div style="margin-top: 15px; font-size: 13px;">Mobile Assistant Connector by
            <a href="http://emagicone.com" target="_blank" style="color: #15428B">eMagicOne</a></div>';

        die($html);
    }

    private function getStores()
    {
        $shop_group_ret = array();
        $shop_groups_tree = Shop::getTree();

        foreach ($shop_groups_tree as $shop_group_tree) {
            $shop_group_ret_shops = array();

            foreach ($shop_group_tree['shops'] as $shop) {
                $shop_group_ret_shops[] = array(
                    'id_shop' => $shop['id_shop'],
                    'id_shop_group' => $shop['id_shop_group'],
                    'name' => $shop['name']
                );
            }

            $shop_group_ret[] = array(
                'group_id' => $shop_group_tree['id'],
                'name' => $shop_group_tree['name'],
                'shops' => $shop_group_ret_shops
            );
        }

        return $shop_group_ret;
    }

    private function getCurrencies()
    {
        $currency_ret = array();
        $currency_ids = array();
        $i = 0;
        $currencies = Currency::getCurrencies();

        foreach ($currencies as $currency) {
            if (!in_array($currency['id_currency'], $currency_ids)) {
                $currency_ret[$i]['code'] = $currency['id_currency'];
                $currency_ret[$i]['symbol'] = $currency['sign'];
                $currency_ret[$i]['name'] = $currency['name'];
                $currency_ids[] = $currency['id_currency'];
                $i++;
            }
        }

        return $currency_ret;
    }

    private function getStoreTitle()
    {
        if (!empty($this->shop_id)) {
            if ($this->shop_id < 1) {
                $this->shop_id = $this->def_shop;
            }

            $shop_info = new Shop($this->shop_id);
        } else {
            $shop_info = new Shop((int)Configuration::get('PS_SHOP_DEFAULT'));
        }

        return array('test' => 1, 'title' => $shop_info->name);
    }

    private function pushNotificationSettings()
    {
        if ((int)$this->app_connection_id < 1) {
            return false;
        }

        if (!$this->registration_id && !$this->api_key && !$this->device_unique_id) {
            return array('error' => 'missing_parameters');
        }

        /*if (Shop::isFeatureActive()) {
            Shop::setContext(Shop::CONTEXT_ALL);
        }*/

        // Update old registration id
        if ($this->registration_id_old) {
            Db::getInstance()->update(
                'mobassistantconnector_push_notifications',
                array('device_id' => $this->registration_id),
                "`device_id` = '".pSQL($this->registration_id_old)."'"
            );
        }

        $this->shop_id = (int)$this->shop_id;
        $result        = array('success' => 'true');
        $id_shop       = $this->shop_id > 0 ? $this->shop_id : -1;
        $device_name   = $this->device_name ? $this->device_name : '';
        $date          = date('Y-m-d H:i:s');
        $account_id    = $this->getAccountIdByEmail((string)$this->account_email);
        $query2 = $query3 = $query4 = true;

        $currency_code  = $this->def_currency;
        if ($this->push_currency_code) {
            $currency_code = $this->push_currency_code;
        } elseif ($this->currency_code) {
            $currency_code = (int)$this->currency_code;
        }

        $query1 = Db::getInstance()->execute(
            'INSERT INTO `'._DB_PREFIX_."mobassistantconnector_devices` (`device_unique`, `account_id`,
            `device_name`, `last_activity`) VALUES ('".pSQL($this->device_unique_id)."',".(int)$account_id.",'"
            .pSQL($device_name)."','".pSQL($date)."') ON DUPLICATE KEY UPDATE `device_name` = '".pSQL($device_name)
            ."', `last_activity` = '".pSQL($date)."'"
        );
        $id = Db::getInstance()->getValue(
            'SELECT `device_unique_id` FROM `'._DB_PREFIX_
            ."mobassistantconnector_devices` WHERE `device_unique` = '".pSQL($this->device_unique_id)
            ."' AND `account_id` = ".(int)$account_id
        );

        $user_id = (int)MobassistantconnectorAccess::getUserIdBySessionKey($this->session_key);
        $user_actions = MobassistantconnectorAccess::getAllowedActionsByUserId($user_id);

        // Delete empty record
        if ($this->push_new_order == 0 && empty($this->push_order_statuses) && $this->push_new_customer == 0) {
            $query2 = Db::getInstance()->delete(
                'mobassistantconnector_push_notifications',
                "`device_id` = '".pSQL($this->registration_id)."' AND `app_connection_id` = "
                .(int)$this->app_connection_id
            );
            deleteEmptyDevices();
            deleteEmptyAccounts();
        } elseif (
            !empty($user_actions) && (in_array('push_notification_settings_new_order', $user_actions)
                    || in_array('push_notification_settings_new_customer', $user_actions)
                    || in_array('push_notification_settings_order_statuses', $user_actions))
        ) {
            $id_exists = Db::getInstance()->getValue(
                'SELECT `id` FROM `'._DB_PREFIX_."mobassistantconnector_push_notifications` WHERE `device_id` = '"
                .pSQL($this->registration_id)."' AND `app_connection_id` = ".(int)$this->app_connection_id
            );
            $data = array(
                'device_unique_id' => (int)$id,
                'id_shop'          => $id_shop,
                'currency_code'    => (int)$currency_code,
                'user_id'          => $user_id,
            );

            if (in_array('push_notification_settings_new_order', $user_actions)) {
                $data['new_order'] = (int)$this->push_new_order;
            }

            if (in_array('push_notification_settings_new_customer', $user_actions)) {
                $data['new_customer'] = (int)$this->push_new_customer;
            }

            if (in_array('push_notification_settings_order_statuses', $user_actions)) {
                $data['order_statuses'] = (string)$this->push_order_statuses;
            }

            if ($id_exists) {
                $query3 = Db::getInstance()->update(
                    'mobassistantconnector_push_notifications',
                    $data,
                    '`id` = '.(int)$id_exists
                );
            } else {
                $data['device_id']         = $this->registration_id;
                $data['app_connection_id'] = (int)$this->app_connection_id;
                $query3 = Db::getInstance()->insert('mobassistantconnector_push_notifications', $data);
            }
        }

        Configuration::updateGlobalValue($this->mob_api_key, $this->api_key);

        if (!$query1 || !$query2 || !$query3 || !$query4) {
            $result = array('error' => 'could_not_update_data');
        }

        return $result;
    }

    private function deletePushConfig()
    {
        if (!$this->app_connection_id && !$this->registration_id) {
            return array('error' => 'missing_parameters');
        }

        $result = Db::getInstance()->delete(
            'mobassistantconnector_push_notifications',
            "`device_id` = '".pSQL($this->registration_id)."' AND `app_connection_id` = "
            .(int)$this->app_connection_id
        );

        if ($result) {
            $ret = array('success' => 'true');
        } else {
            $ret = array('error' => 'could_not_delete_data');
        }

        deleteEmptyDevices();
        deleteEmptyAccounts();

        return $ret;
    }

    private function getOrdersStatuses()
    {
        $statuses = OrderState::getOrderStates($this->def_lang);
        $statuses = array_map(array($this, 'statusProcess'), $statuses);

        return $statuses;
    }

    private function getStoreStats()
    {
        $this->shop_id = (int)$this->shop_id;
        $data_graphs = '';
        $query_where_parts = '';
        $order_status_stats = array();
        $store_stats = array(
            'count_orders'    => '0',
            'total_sales'     => '0',
            'count_customers' => '0',
            'count_products'  => '0',
            'last_order_id'   => '0',
            'new_orders'      => '0'
        );
        $today = date('Y-m-d', time());
        $date_from = $date_to = $today;

        if (!empty($this->stats_from)) {
            $date_from = $this->stats_from;
        }

        if (!empty($this->stats_to)) {
            $date_to = $this->stats_to;
        }

        if (isset($this->custom_period) && Tools::strlen($this->custom_period) > 0) {
            $custom_period = $this->getCustomPeriod($this->custom_period);

            $date_from = $custom_period['start_date'];
            $date_to = $custom_period['end_date'];
        }

        if (!empty($date_from)) {
            $query_where_parts[] = "o.date_add >= '".date('Y-m-d H:i:s', strtotime($date_from.' 00:00:00'))."'";
        }

        if (!empty($date_to)) {
            $query_where_parts[] = "o.date_add <= '".date('Y-m-d H:i:s', strtotime($date_to.' 23:59:59'))."'";
        }

        if ($this->shop_id > 0) {
            $query_where_parts[] = 'o.id_shop = '.(int)$this->shop_id;
        }

        if (!empty($this->statuses)) {
            $statuses = str_replace('|', ',', $this->statuses);
            $query_where_parts[] = 'osl.id_order_state IN ('.pSQL($statuses).')';
        }

        // Get count of orders
        $orders_count_obj = new DbQuery();
        $orders_count_obj->select('
            COUNT(o.id_order) AS count_orders,
            IFNULL(SUM(o.total_paid_tax_excl/o.conversion_rate), 0) AS total_sales
        ');
        $orders_count_obj->from('orders', 'o');
        $orders_count_obj->leftJoin(
            'order_state_lang',
            'osl',
            'osl.id_order_state = o.current_state AND osl.id_lang = '.(int)$this->def_lang
        );
        if (!empty($query_where_parts)) {
            $orders_count_obj->where(implode(' AND ', $query_where_parts));
        }
        $orders_count_sql = $orders_count_obj->build();
        $orders_count = Db::getInstance()->executeS($orders_count_sql);
        $orders_count = array_shift($orders_count);

        // Convert price
        if ($this->currency_code != $this->def_currency) {
            $orders_count['total_sales'] = $this->convertPrice($orders_count['total_sales'], $this->def_currency);
        }

        // Form price
        $orders_count['total_sales'] = $this->displayPrice($orders_count['total_sales'], $this->currency_code, true);

        // Add count and sum of orders into return array
        $store_stats['count_orders'] = $orders_count['count_orders'];
        $store_stats['total_sales'] = $orders_count['total_sales'];

        // Get count of products
        $products_count_obj = new DbQuery();
        $products_count_obj->select('COUNT(od.product_id) AS count_products');
        $products_count_obj->from('orders', 'o');
        $products_count_obj->leftJoin('order_detail', 'od', 'od.id_order = o.id_order');
        $products_count_obj->leftJoin(
            'order_state_lang',
            'osl',
            'osl.id_order_state = o.current_state AND osl.id_lang = '.(int)$this->def_lang
        );
        if (!empty($query_where_parts)) {
            $products_count_obj->where(implode(' AND ', $query_where_parts));
        }
        $products_count_sql = $products_count_obj->build();
        $products_count = Db::getInstance()->executeS($products_count_sql);
        $products_count = array_shift($products_count);

        // Add count and sum of products into return array
        $store_stats['count_products'] = $products_count['count_products'];

        // Get last_order_id and count of new orders
        if ($this->last_order_id != '') {
            $order_last_obj = new DbQuery();
            $order_last_obj->select('
                COUNT(o.id_order) AS count_orders,
                MAX(o.id_order) AS last_order_id
            ');
            $order_last_obj->from('orders', 'o');
            $order_last_obj->leftJoin(
                'order_state_lang',
                'osl',
                'osl.id_order_state = o.current_state AND osl.id_lang = '.(int)$this->def_lang
            );
            if (!empty($query_where_parts)) {
                $order_last_obj->where(
                    implode(' AND ', $query_where_parts).' AND o.id_order > '.(int)$this->last_order_id
                );
            } else {
                $order_last_obj->where('o.id_order > '.(int)$this->last_order_id);
            }
            $order_last_sql = $order_last_obj->build();
            $order_last = Db::getInstance()->executeS($order_last_sql);
            $order_last = array_shift($order_last);

            // Add last_order_id and count of new orders into return array
            if ($order_last['last_order_id'] > (int)$this->last_order_id) {
                $store_stats['last_order_id'] = $order_last['last_order_id'];
            }
            $store_stats['new_orders'] = $order_last['count_orders'];
        }

        unset($query_where_parts);

        if (!empty($date_from)) {
            $query_where_parts[] = "date_add >= '".date('Y-m-d H:i:s', (strtotime($date_from.' 00:00:00')))."'";
        }

        if (!empty($date_to)) {
            $query_where_parts[] = "date_add <= '".date('Y-m-d H:i:s', (strtotime($date_to.' 23:59:59')))."'";
        }

        if ($this->shop_id > 0) {
            $query_where_parts[] = 'id_shop = '.(int)$this->shop_id;
        }

        // Get count of customers
        $customers_count_obj = new DbQuery();
        $customers_count_obj->select('COUNT(id_customer) AS count_customers');
        $customers_count_obj->from('customer');
        if (!empty($query_where_parts)) {
            $customers_count_obj->where(implode(' AND ', $query_where_parts));
        }
        $customers_count_sql = $customers_count_obj->build();
        $customers_count = Db::getInstance()->executeS($customers_count_sql);
        $customers_count = array_shift($customers_count);

        // Add count of customers into return array
        $store_stats['count_customers'] = $customers_count['count_customers'];

        if (!isset($this->data_for_widget) || empty($this->data_for_widget) || $this->data_for_widget != 1) {
            $data_graphs = $this->getDataGraphs();
        }

        if (!isset($this->data_for_widget) || $this->data_for_widget != 1) {
            $order_status_stats = $this->getStatusStats();
        }

        $result = array_merge(
            $store_stats,
            array('data_graphs' => $data_graphs),
            array('order_status_stats' => $order_status_stats)
        );

        return $result;
    }

    private function getDataGraphs()
    {
        $orders = array();
        $customers = array();
        $where_statuses = '';
        $where_id_shop = '';
        $average = array(
            'avg_sum_orders' => 0,
            'sum_orders'     => 0,
            'tot_orders'     => 0,
            'tot_customers'  => 0,
            'avg_orders'     => 0,
            'avg_customers'  => 0,
            'avg_cust_order' => '0.00',
        );

        if (empty($this->graph_from)) {
            $this->graph_from = date('Y-m-d', mktime(0, 0, 0, date('m'), date('d') - 7, date('Y')));
        }

        if (empty($this->graph_to)) {
            if (!empty($this->stats_to)) {
                $this->graph_to = $this->stats_to;
            } else {
                $this->graph_to = date('Y-m-d', time());
            }
        }

        if ((int)$this->shop_id > 0) {
            $where_id_shop = ' AND id_shop = '.(int)$this->shop_id;
        }

        $start_date = $this->graph_from.' 00:00:00';
        $end_date = $this->graph_to.' 23:59:59';
        $plus_date = '+1 day';

        if (isset($this->custom_period) && Tools::strlen($this->custom_period) > 0) {
            $custom_period = $this->getCustomPeriod($this->custom_period);

            if ($this->custom_period == 3) {
                $plus_date = '+3 day';
            } elseif ($this->custom_period == 4 || $this->custom_period == 8) {
                $plus_date = '+1 week';
            } elseif ($this->custom_period == 5 || $this->custom_period == 6 || $this->custom_period == 7) {
                $plus_date = '+1 month';
            }

            if ($this->custom_period == 7) {
                $dates_obj = new DbQuery();
                $dates_obj->select('
                    MIN(date_add) AS min_date_add,
                    MAX(date_add) AS max_date_add
                ');
                $dates_obj->from('orders');
                $dates_sql = $dates_obj->build();
                $dates = Db::getInstance()->executeS($dates_sql);

                $dates = array_shift($dates);
                $start_date = $dates['min_date_add'];
                $end_date = $dates['max_date_add'];
            } else {
                $start_date = $custom_period['start_date'].' 00:00:00';
                $end_date = $custom_period['end_date'].' 23:59:59';
            }
        }

        $start_date = strtotime($start_date);
        $end_date = strtotime($end_date);
        $date = $start_date;
        $d = 0;

        if (!empty($this->statuses)) {
            $statuses = str_replace('|', ',', $this->statuses);
            $where_statuses = 'osl.id_order_state IN ('.pSQL($statuses).') AND ';
        }

        while ($date <= $end_date) {
            $d++;
            $date_str = date('Y-m-d H:i:s', ($date));
            $where_orders_date_add = "o.date_add >= '{$date_str}' AND o.date_add < '"
                .date('Y-m-d H:i:s', (strtotime($plus_date, $date)))."'";

            // Get orders
            $orders_tot_obj = new DbQuery();
            $orders_tot_obj->select('
                o.date_add,
                SUM(o.total_paid_tax_excl/o.conversion_rate) AS value,
                COUNT(o.id_order) AS tot_orders
            ');
            $orders_tot_obj->from('orders', 'o');
            $orders_tot_obj->leftJoin(
                'order_state_lang',
                'osl',
                'osl.id_order_state = o.current_state AND osl.id_lang = '.(int)$this->def_lang
            );
            $orders_tot_obj->where($where_statuses.$where_orders_date_add.$where_id_shop);
            $orders_tot_obj->groupBy('DATE(o.date_add)');
            $orders_tot_obj->orderBy('o.date_add');
            $orders_tot_sql = $orders_tot_obj->build();
            $orders_tot = Db::getInstance()->executeS($orders_tot_sql);

            $total_order_per_day = 0;

            // Get total of orders per date
            foreach ($orders_tot as $order) {
                $total_order_per_day += $order['value'];
                $average['tot_orders'] += $order['tot_orders'];
                $average['sum_orders'] += $order['value'];
            }

            // Convert price
            if ($this->currency_code != $this->def_currency) {
                $total_order_per_day = $this->convertPrice($total_order_per_day, $this->def_currency);
            }

            $orders[] = array($date * 1000, $total_order_per_day);
            $where_customers_date_add = "date_add >= '{$date_str}' AND date_add < '"
                .date('Y-m-d H:i:s', strtotime($plus_date, $date))."'";

            // Get customers
            $customers_tot_obj = new DbQuery();
            $customers_tot_obj->select('
                COUNT(id_customer) AS tot_customers,
                date_add
            ');
            $customers_tot_obj->from('customer');
            $customers_tot_obj->where($where_customers_date_add.$where_id_shop);
            $customers_tot_obj->groupBy('DATE(date_add)');
            $customers_tot_obj->orderBy('date_add');
            $customers_tot_sql = $customers_tot_obj->build();
            $customers_tot = Db::getInstance()->executeS($customers_tot_sql);

            $total_customer_per_day = 0;

            // Get total of customers per date
            foreach ($customers_tot as $customer) {
                $total_customer_per_day += $customer['tot_customers'];
                $average['tot_customers'] += $customer['tot_customers'];
            }

            $customers[] = array($date * 1000, $total_customer_per_day);
            $date = strtotime($plus_date, $date);
        }

        // Add 2 additional element into array of orders for graph in mobile application
        if (count($orders) == 1) {
            $orders_tmp = $orders[0];
            $orders = array();
            $orders[0][] = strtotime(date('Y-m-d', $orders_tmp[0] / 1000).'-1 month') * 1000;
            $orders[0][] = 0;
            $orders[1] = $orders_tmp;
            $orders[2][] = strtotime(date('Y-m-d', $orders_tmp[0] / 1000).'+1 month') * 1000;
            $orders[2][] = 0;
        }

        // Add 2 additional element into array of customers for graph in mobile application
        if (count($customers) == 1) {
            $customers_tmp = $customers[0];
            $customers = array();
            $customers[0][] = strtotime(date('Y-m-d', $customers_tmp[0] / 1000).'-1 month') * 1000;
            $customers[0][] = 0;
            $customers[1] = $customers_tmp;
            $customers[2][] = strtotime(date('Y-m-d', $customers_tmp[0] / 1000).'+1 month') * 1000;
            $customers[2][] = 0;
        }

        if ($d <= 0) {
            $d = 1;
        }

//        $average['avg_sum_orders'] = number_format($average['sum_orders'] / $d, 2, '.', ' ');
        $average['avg_sum_orders'] = $this->displayPrice($average['sum_orders'] / $d, $this->currency_code);
        $average['avg_orders']     = number_format($average['tot_orders'] / $d, 1, '.', ' ');
        $average['avg_customers']  = number_format($average['tot_customers'] / $d, 1, '.', ' ');

        if ($average['tot_customers'] > 0) {
            $average['avg_cust_order'] = $average['sum_orders'] / $average['tot_customers'];
        }

        $average['avg_cust_order'] = $this->displayPrice($average['avg_cust_order'], $this->currency_code);
//        $average['sum_orders']    = number_format($average['sum_orders'], 2, '.', ' ');
        $average['sum_orders']    = $this->displayPrice($average['sum_orders'], $this->currency_code);
        $average['tot_customers'] = number_format($average['tot_customers'], 1, '.', ' ');
        $average['tot_orders']    = number_format($average['tot_orders'], 1, '.', ' ');

        if ($this->currency_code != self::CURRENCY_NOT_SET) {
            $currency = new Currency($this->currency_code);
        } else {
            $currency = new Currency($this->def_currency);
        }

        return array(
            'orders' => $orders,
            'customers' => $customers,
            'currency_sign' => $currency->sign,
            'average' => $average
        );
    }

    private function getStatusStats()
    {
        $order_statuses_ret = array();
        $query_where_parts = array();
        $today = date('Y-m-d', time());
        $date_from = $date_to = $today;

        if (!empty($this->stats_from)) {
            $date_from = $this->stats_from;
        }

        if (!empty($this->stats_to)) {
            $date_to = $this->stats_to;
        }

        if (isset($this->custom_period) && Tools::strlen($this->custom_period) > 0) {
            $custom_period = $this->getCustomPeriod($this->custom_period);

            $date_from = $custom_period['start_date'];
            $date_to = $custom_period['end_date'];
        }

        if (!empty($date_from)) {
            $date_from = $date_from.' 00:00:00';
        }

        if (!empty($date_to)) {
            $date_to = $date_to.' 23:59:59';
        }

        if ((int)$this->shop_id > 0) {
            $query_where_parts[] = 'o.id_shop = '.(int)$this->shop_id;
        }

        if (!empty($date_from) && !empty($date_to)) {
            $query_where_parts[] = "o.date_add >= '".date('Y-m-d H:i:s', strtotime($date_from))
                ."' AND o.date_add <= '".date('Y-m-d H:i:s', strtotime($date_to))."'";
        }

        // Get count of orders by status
        $order_statuses_obj = new DbQuery();
        $order_statuses_obj->select('
            COUNT(o.id_order) AS count,
            SUM(o.total_paid_tax_excl/o.conversion_rate) AS total,
            osl.name,
            o.current_state AS code
        ');
        $order_statuses_obj->from('orders', 'o');
        $order_statuses_obj->leftJoin(
            'order_state_lang',
            'osl',
            'osl.id_order_state = o.current_state AND osl.id_lang = '.(int)$this->def_lang
        );
        if (!empty($query_where_parts)) {
            $order_statuses_obj->where(implode(' AND ', $query_where_parts));
        }
        $order_statuses_obj->groupBy('o.current_state');
        $order_statuses_obj->orderBy('count DESC');
        $order_statuses_sql = $order_statuses_obj->build();
        $order_statuses = Db::getInstance()->executeS($order_statuses_sql);

        foreach ($order_statuses as $order_status) {
            if ($this->currency_code != $this->def_currency) {
                $order_status['total'] = $this->convertPrice($order_status['total'], $this->def_currency);
            }

            $order_status['total'] = $this->displayPrice($order_status['total'], $this->currency_code, true);
            $order_statuses_ret[] = $order_status;
        }

        return $order_statuses_ret;
    }

    private function searchProducts()
    {
        $query_where_parts = array();
        $products = array();
        $order_by = '';
        $id_shop = (int)$this->def_shop;
        $this->shop_id = (int)$this->shop_id;

        if ($this->shop_id > 0) {
            $where_id_shop = $this->shop_id;
            $id_shop = $this->shop_id;
        } else {
            $where_id_shop = (int)$this->def_shop;
        }

        if (empty($this->sort_by)) {
            $this->sort_by = 'id';
        }

        switch ($this->sort_by) {
            case 'id':
                $order_by = 'p.id_product '.$this->getSortDirection('DESC');
                break;
            case 'name':
                $order_by = 'pl.name '.$this->getSortDirection('ASC');
                break;
            case 'qty':
                $order_by = 'sa.quantity '.$this->getSortDirection('DESC');
                break;
            case 'price':
                $order_by = 'ps.price '.$this->getSortDirection('DESC');
                break;
            case 'status':
                $order_by = 'status_code '.$this->getSortDirection('DESC');
                break;
        }

        if (!empty($this->params) && !empty($this->val)) {
            $this->params = explode('|', $this->params);
            $count_params = count($this->params);

            for ($i = 0; $i < $count_params; $i++) {
                switch ($this->params[$i]) {
                    case 'pr_id':
                        if (!empty($this->val) && preg_match('/^\d+(?:,\d+)*$/', $this->val)) {
                            $query_where_parts[] = 'p.id_product IN ('.pSQL($this->val).')';
                        } else {
                            $query_where_parts[] = 'p.id_product = '.(int)$this->val;
                        }

                        break;
                    case 'pr_sku':
                        $query_where_parts[] = "p.reference = '".pSQL($this->val)."'";
                        break;
                    case 'pr_name':
                        $query_where_parts[] = "pl.name LIKE '%".pSQL($this->val)."%'";
                        break;
                    case 'pr_desc':
                        $query_where_parts[] = "pl.description LIKE '%".pSQL($this->val)."%'";
                        break;
                    case 'pr_short_desc':
                        $query_where_parts[] = "pl.description_short LIKE '%".pSQL($this->val)."%'";
                        break;
                    default:
                        $values = explode('|', $this->val);
                        $count  = count($values);
                        for ($k = 0; $k < $count; $k++) {
                            if (empty($values[$k])) {
                                unset($values[$k]);
                            }
                        }
                        $values = "'".implode("','", $values)."'";

                        $params = explode('|', $this->params[$i]);
                        $count = count($params);
                        for ($k = 0; $k < $count; $k++) {
                            if (empty($params[$k])) {
                                continue;
                            }

                            $query_where_parts[] = "p.{$params[$k]} IN ($values)";
                        }
                }
            }
        }

        // Get products
        $products_obj = new DbQuery();
        $products_obj->select('
            p.id_product AS main_id,
            p.id_product AS product_id,
            pl.name,
            pl.link_rewrite,
            p.price AS default_price,
            p.wholesale_price AS default_wholesale_price,
            p.reference AS sku,
            p.ean13,
            p.upc,
            sa.quantity,
            ps.price,
            ps.wholesale_price,
            ps.active AS status_code,
            s.name AS shop_name
        ');
        $products_obj->from('product', 'p');
        $products_obj->leftJoin(
            'product_lang',
            'pl',
            'p.id_product = pl.id_product AND pl.id_lang = '.(int)$this->def_lang
        );
        $products_obj->leftJoin(
            'stock_available',
            'sa',
            'p.id_product = sa.id_product AND sa.id_product_attribute = 0'
        );
        $products_obj->innerJoin('product_shop', 'ps', 'ps.id_product = p.id_product AND ps.id_shop = '.(int)$id_shop);
        $products_obj->leftJoin('shop', 's', 's.id_shop = '.(int)$id_shop);

        if (!empty($query_where_parts)) {
            $products_obj->where(implode(' OR ', $query_where_parts));
        }

        $products_obj->groupBy('p.id_product');
        $products_obj->orderBy($order_by);
        $products_obj->limit($this->show, ($this->page - 1) * $this->show);
        $products_sql = $products_obj->build();
        $products_res = Db::getInstance()->executeS($products_sql);

        foreach ($products_res as $product) {
            if ($this->currency_code != $this->def_currency) {
                $product['price'] = $this->convertPrice($product['price'], $this->def_currency);
                $product['wholesale_price'] = $this->convertPrice($product['wholesale_price'], $this->def_currency);
            }

            $product['price'] = $this->displayPrice($product['price'], $this->currency_code, true);
            $product['wholesale_price'] = $this->displayPrice($product['wholesale_price'], $this->currency_code, true);

            // Get url of product image
            $image_url = $this->getProductCoverImageUrl($product['product_id'], $product['link_rewrite']);
            if ($image_url) {
                $product['product_image'] = $image_url;
            }

            $products[] = $product;
        }

        // Get count of products
        $products_count_obj = new DbQuery();
        $products_count_obj->select('COUNT(p.id_product) AS count_prods');
        $products_count_obj->from('product', 'p');
        $products_count_obj->leftJoin(
            'product_lang',
            'pl',
            'p.id_product = pl.id_product AND pl.id_lang = '.(int)$this->def_lang.' AND id_shop = '.$where_id_shop
        );

        if ($this->shop_id > 0) {
            $products_count_obj->innerJoin(
                'product_shop',
                'ps',
                'p.id_product = ps.id_product AND ps.id_shop = '.(int)$this->shop_id
            );
        }

        if (!empty($query_where_parts)) {
            $products_count_obj->where(implode(' OR ', $query_where_parts));
        }

        $products_count_sql = $products_count_obj->build();
        $products_count = Db::getInstance()->executeS($products_count_sql);
        $products_count = array_shift($products_count);

        return array('products_count' => (int)$products_count['count_prods'], 'products' => $products);
    }

    private function searchProductsOrdered()
    {
        $query_where_parts = array();
        $query_where_parts_search = array();
        $products = array();
        $order_by = '';
        $this->shop_id = (int)$this->shop_id;

        if (!empty($this->params) && !empty($this->val)) {
            $this->params = explode('|', $this->params);
            $count_params = count($this->params);

            for ($i = 0; $i < $count_params; $i++) {
                switch ($this->params[$i]) {
                    case 'pr_id':
                        if (!empty($this->val) && preg_match('/^\d+(?:,\d+)*$/', $this->val)) {
                            $query_where_parts_search[] = 'od.product_id IN ('.pSQL($this->val).')';
                        } else {
                            $query_where_parts_search[] = 'od.product_id = '.(int)$this->val;
                        }

                        break;
                    case 'pr_sku':
                        $query_where_parts_search[] = "od.product_reference = '".pSQL($this->val)."'";
                        break;
                    case 'pr_name':
                        $query_where_parts_search[] = "od.product_name LIKE '%".pSQL($this->val)."%'";
                        break;
                    case 'order_id':
                        $query_where_parts_search[] = 'order_id = '.(int)$this->val;
                        break;
                }
            }
        }

        if (!empty($query_where_parts_search)) {
            $query_where_parts[] = implode(' OR ', $query_where_parts_search);
        }

        if ($this->shop_id > 0) {
            $query_where_parts[] = 'o.id_shop = '.(int)$this->shop_id;
        }

        if (!empty($this->products_from)) {
            $query_where_parts[] = "o.date_add >= '".date('Y-m-d H:i:s', strtotime($this->products_from.' 00:00:00'))
                ."'";
        }

        if (!empty($this->products_to)) {
            $query_where_parts[] = "o.date_add <= '".date('Y-m-d H:i:s', strtotime($this->products_to.' 23:59:59'))
                ."'";
        }

        if (!empty($this->statuses)) {
            $statuses = str_replace('|', ',', $this->statuses);
            $query_where_parts[] = 'o.current_state IN ('.pSQL($statuses).')';
        }

        if (empty($this->sort_by)) {
            $this->sort_by = 'id';
        }

        switch ($this->sort_by) {
            case 'id':
                $order_by = 'od.product_id '.$this->getSortDirection('DESC');
                break;
            case 'name':
                $order_by = 'pl.name '.$this->getSortDirection('ASC');
                break;
            case 'qty':
                $order_by = 'quantity '.$this->getSortDirection('DESC');
                break;
            case 'total':
                $order_by = 'price '.$this->getSortDirection('DESC');
                break;
        }

        // Get ordered products
        $products_ordered_obj = new DbQuery();
        $products_ordered_obj->from('order_detail', 'od');
        $products_ordered_obj->leftJoin('orders', 'o', 'od.id_order = o.id_order');
        $products_ordered_obj->leftJoin('product', 'p', 'p.id_product = od.product_id');
        $products_ordered_obj->leftJoin(
            'product_shop',
            'ps',
            'ps.id_product = od.product_id AND ps.id_shop = '
            .(int)($this->shop_id > 0 ? $this->shop_id : $this->def_shop)
        );
        $products_ordered_obj->leftJoin(
            'product_lang',
            'pl',
            'od.product_id = pl.id_product AND o.id_lang = pl.id_lang AND od.id_shop = pl.id_shop'
        );
        if (!empty($query_where_parts)) {
            $products_ordered_obj->where(implode(' AND ', $query_where_parts));
        }
        $products_ordered_obj->orderBy($order_by);
        $products_ordered_obj->limit($this->show, ($this->page - 1) * $this->show);

        if ((int)$this->group_by_product_id == 1) {
            $products_ordered_obj->select('
                od.id_order AS main_id,
                od.id_order AS order_id,
                od.product_id,
                od.product_reference AS sku,
                SUM(od.product_price) AS price,
                od.id_shop,
                SUM(od.product_quantity) AS quantity,
                pl.name,
                pl.link_rewrite,
                ps.price AS current_price,
                p.wholesale_price,
                p.ean13,
                p.upc,
                od.id_order_detail AS id_order_product
            ');
            $products_ordered_obj->groupBy('od.product_id');
        } else {
            $products_ordered_obj->select('
                od.id_order AS main_id,
                od.id_order AS order_id,
                od.product_id,
                pl.name,
                pl.link_rewrite,
                od.product_reference AS sku,
                od.product_price AS price,
                ps.price AS current_price,
                p.wholesale_price,
                p.ean13,
                p.upc,
                od.product_quantity AS quantity,
                osl.name AS `status`,
                od.id_order_detail AS id_order_product,
                od.id_shop,
                o.date_add,
                o.current_state AS status_code
            ');
            $products_ordered_obj->leftJoin(
                'order_state_lang',
                'osl',
                'osl.id_order_state = o.current_state AND osl.id_lang = o.id_lang'
            );
        }

        $products_ordered_sql = $products_ordered_obj->build();
        $products_ordered_res = Db::getInstance()->executeS($products_ordered_sql);

        foreach ($products_ordered_res as $product_ordered) {
            if ($this->currency_code != $this->def_currency) {
                $product_ordered['price'] = $this->convertPrice($product_ordered['price'], $this->def_currency);
                $product_ordered['current_price'] = $this->convertPrice(
                    $product_ordered['current_price'],
                    $this->def_currency
                );
                $product_ordered['wholesale_price'] = $this->convertPrice(
                    $product_ordered['wholesale_price'],
                    $this->def_currency
                );
            }

            $product_ordered['price'] = $this->displayPrice($product_ordered['price'], $this->currency_code, true);
            $product_ordered['current_price'] = $this->displayPrice(
                $product_ordered['current_price'],
                $this->currency_code,
                true
            );

            // Get url of product image
            $image_url = $this->getProductCoverImageUrl(
                $product_ordered['product_id'],
                $product_ordered['link_rewrite']
            );
            if ($image_url) {
                $product_ordered['product_image'] = $image_url;
            }

            $products[] = $product_ordered;
        }

        // Get count of ordered products
        $products_ordered_count_obj = new DbQuery();
        if ((int)$this->group_by_product_id == 1) {
            $products_ordered_count_obj->select('
                COUNT(DISTINCT od.product_id) AS count_prods,
                MAX(o.date_add) AS max_date,
                MIN(o.date_add) AS min_date
            ');
        } else {
            $products_ordered_count_obj->select('
                COUNT(od.product_id) AS count_prods,
                MAX(o.date_add) AS max_date,
                MIN(o.date_add) AS min_date
            ');
        }
        $products_ordered_count_obj->from('order_detail', 'od');
        $products_ordered_count_obj->leftJoin('orders', 'o', 'od.id_order = o.id_order');
        if (!empty($query_where_parts)) {
            $products_ordered_count_obj->where(implode(' AND ', $query_where_parts));
        }
        $products_ordered_count_sql = $products_ordered_count_obj->build();
        $products_ordered_count = Db::getInstance()->executeS($products_ordered_count_sql);
        $products_ordered_count = array_shift($products_ordered_count);

        return array(
            'products_count' => (int)$products_ordered_count['count_prods'],
            'products'       => $products,
            'max_date'       => $products_ordered_count['max_date'],
            'min_date'       => $products_ordered_count['min_date'],
        );
    }

    private function getProductsInfo()
    {
        $this->product_id = (int)$this->product_id;
        $this->shop_id = (int)$this->shop_id;

        if ($this->product_id < 1) {
            return false;
        }

        $product_obj = new Product(
            $this->product_id,
            false,
            $this->def_lang,
            $this->shop_id > 0 ? $this->shop_id : null
        );

        if (!$product_obj->id) {
            return false;
        }

        $id_shop = $this->shop_id > 0 ? $this->shop_id : $this->def_shop;
        $product_obj_query = new DbQuery();
        $product_obj_query->select('
			sa.quantity,
			ps.price AS price,
			ps.wholesale_price
		');
        $product_obj_query->from('product', 'p');
        $product_obj_query->leftJoin(
            'stock_available',
            'sa',
            'p.id_product = sa.id_product AND sa.id_product_attribute = 0'
        );
        $product_obj_query->leftJoin(
            'product_shop',
            'ps',
            'ps.id_product = p.id_product AND ps.id_shop = '.(int)$id_shop
        );
        $product_obj_query->where('p.id_product = '.(int)$this->product_id);
        $product_obj_query->groupBy('p.id_product');
        $product_sql = $product_obj_query->build();
        $product_sql_res = Db::getInstance()->executeS($product_sql);

        $product_obj->quantity = $product_sql_res[0]['quantity'];
        $product_obj->base_price = $product_sql_res[0]['price'];
        $product_obj->wholesale_price = $product_sql_res[0]['wholesale_price'];

        $images = $product_obj->getImages($this->def_lang);
        $image_cover = array();
        $image_simple = array();
        $processed_image_ids = array();
        $count_images = count($images);
        for ($i = 0; $i < $count_images; $i++) {
            if (in_array($images[$i]['id_image'], $processed_image_ids)) {
                continue;
            }

            $image = array(
                'small' => $this->getProductImageUrl($product_obj->link_rewrite, $images[$i]['id_image'], 'large'),
                'large' => $this->getProductImageUrl($product_obj->link_rewrite, $images[$i]['id_image'], 'thickbox')
            );

            if ((int)$images[$i]['cover'] == 1) {
                $image_cover[] = $image;
            } else {
                $image_simple[] = $image;
            }

            $processed_image_ids[] = $images[$i]['id_image'];
        }

        $where = array("product_id = $this->product_id");

        if ($this->shop_id > 0) {
            $where[] = "id_shop = $this->shop_id";
        }

        $total_ordered = Db::getInstance()->getValue(
            'SELECT SUM(product_quantity) FROM '._DB_PREFIX_.'order_detail WHERE '.implode(' AND ', $where)
        );

        $product = array(
            'id_product'              => $product_obj->id,
            'name'                    => $product_obj->name,
            'default_price'           => $product_obj->base_price,
            'default_wholesale_price' => $product_obj->wholesale_price,
            'quantity'                => $product_obj->quantity,
            'sku'                     => $product_obj->reference,
            'ean13'                   => $product_obj->ean13,
            'upc'                     => $product_obj->upc,
            'active'                  => $product_obj->active == 1 ? 'Enabled' : 'Disabled',
            'images'                  => array_merge($image_cover, $image_simple),
            'total_ordered'           => (int)$total_ordered,
            'price' => $this->currency_code != $this->def_currency
                ? $this->convertPrice($product_obj->base_price, $this->def_currency)
                : $product_obj->base_price,
            'wholesale_price' => $this->currency_code != $this->def_currency
                ? $this->convertPrice($product_obj->wholesale_price, $this->def_currency)
                : $product_obj->wholesale_price
        );

        $product['price'] = $this->displayPrice($product['price'], $this->currency_code, true);
        $product['wholesale_price'] = $this->displayPrice($product['wholesale_price'], $this->currency_code, true);

        return $product;
    }

    private function getProductsDescr()
    {
        $this->product_id = (int)$this->product_id;
        $this->shop_id = (int)$this->shop_id;

        if ($this->product_id < 1) {
            return false;
        }

        $product_obj = new Product(
            $this->product_id,
            false,
            $this->def_lang,
            $this->shop_id > 0 ? $this->shop_id : null
        );

        if (!$product_obj->id) {
            return false;
        }

        return array(
            'descr'       => $product_obj->description,
            'short_descr' => $product_obj->description_short
        );
    }

    private function getCustomers()
    {
        $customers = array();
        $order_by = '';
        $query_where_parts = '';

        if (!empty($this->customers_from)) {
            $query_where_parts[] = "c.date_add >= '".date('Y-m-d H:i:s', strtotime($this->customers_from.' 00:00:00'))
                ."'";
        }

        if (!empty($this->customers_to)) {
            $query_where_parts[] = "c.date_add <= '".date('Y-m-d H:i:s', strtotime($this->customers_to.' 23:59:59'))
                ."'";
        }

        if (!empty($this->search_val) && preg_match('/^\d+(?:,\d+)*$/', $this->search_val)) {
            $query_where_parts[] = 'c.id_customer IN ('.pSQL($this->search_val).')';
        } elseif (!empty($this->search_val)) {
            $query_where_parts[] = "(c.email LIKE '%".pSQL($this->search_val)
                ."%' OR CONCAT(c.firstname, ' ', c.lastname) LIKE '%".pSQL($this->search_val)."%')";
        }

        if (!empty($this->cust_with_orders)) {
            $query_where_parts[] = 'tot.total_orders > 0';
        }

        if ((int)$this->shop_id > 0) {
            $query_where_parts[] = 'c.id_shop = '.(int)$this->shop_id;
        }

        if (empty($this->sort_by)) {
            $this->sort_by = 'id';
        }

        switch ($this->sort_by) {
            case 'id':
                $order_by = 'c.id_customer '.$this->getSortDirection('DESC');
                break;
            case 'date':
                $order_by = 'c.date_add '.$this->getSortDirection('DESC');
                break;
            case 'name':
                $order_by = 'full_name '.$this->getSortDirection('ASC');
                break;
            case 'qty':
                $order_by = 'total_orders '.$this->getSortDirection('DESC');
                break;
        }

        // Get customers
        $customers_obj = new DbQuery();
        $customers_obj->select("
            c.id_customer,
            c.firstname,
            c.lastname,
            CONCAT(c.firstname, ' ', c.lastname) AS full_name,
            c.email,
            c.date_add,
            c.id_shop,
            IFNULL(tot.total_orders, 0) AS total_orders
        ");
        $customers_obj->from(
            'customer',
            'c LEFT OUTER JOIN (SELECT COUNT(id_order) AS total_orders, id_customer FROM '._DB_PREFIX_
            .'orders GROUP BY id_customer) AS tot ON tot.id_customer = c.id_customer'
        );
        if (!empty($query_where_parts)) {
            $customers_obj->where(implode(' AND ', $query_where_parts));
        }
        $customers_obj->orderBy($order_by);
        $customers_obj->limit($this->show, ($this->page - 1) * $this->show);
        $customers_sql = $customers_obj->build();
        $customers_res = Db::getInstance()->executeS($customers_sql);

        foreach ($customers_res as $customer) {
            $date = explode(' ', $customer['date_add']);
            $customer['date_add'] = $date[0];
            $customer['total_orders'] = (int)$customer['total_orders'];
            $customers[] = $customer;
        }

        // Get count of customers
        $customers_count_obj = new DbQuery();
        $customers_count_obj->select('
            COUNT(c.id_customer) AS count_custs,
            MAX(c.date_add) AS max_date,
            MIN(c.date_add) AS min_date
        ');
        $customers_count_obj->from(
            'customer',
            'c LEFT OUTER JOIN (SELECT COUNT(id_order) AS total_orders, id_customer FROM '._DB_PREFIX_
            .'orders GROUP BY id_customer) AS tot ON tot.id_customer = c.id_customer'
        );
        if (!empty($query_where_parts)) {
            $customers_count_obj->where(implode(' AND ', $query_where_parts));
        }
        $customers_count_sql = $customers_count_obj->build();
        $customers_count = Db::getInstance()->executeS($customers_count_sql);
        $customers_count = array_shift($customers_count);

        return array(
            'customers_count' => (int)$customers_count['count_custs'],
            'customers'       => $customers,
        );
    }

    private function getCustomersInfo()
    {
        if ((int)$this->user_id < 1) {
            return false;
        }

        $customer = new Customer((int)$this->user_id);

        if (!$customer->id) {
            return false;
        }

        if ((int)$this->only_items == 1) {
            return array('customer_orders' => $this->getCustomerItems());
        }

        // Get all customer addresses
        $customer_addresses = array();
        $addresses = $customer->getAddresses($this->def_lang);
        foreach ($addresses as $address) {
            $customer_addresses[] = array(
                'dni'          => $address['dni'],
                'alias'        => $address['alias'],
                'first_name'   => $address['firstname'],
                'last_name'    => $address['lastname'],
                'company'      => $address['company'],
                'vat_number'   => $address['vat_number'],
                'address1'     => $address['address1'],
                'address2'     => $address['address2'],
                'postcode'     => $address['postcode'],
                'city'         => $address['city'],
                'country'      => $address['country'],
                'state'        => $address['state'],
                'phone'        => $address['phone'],
                'phone_mobile' => $address['phone_mobile'],
                'other'        => $address['other'],
            );
        }

        // Get customer general information
        $customer_info = array(
            'id_customer' => $customer->id,
            'firstname'   => $customer->firstname,
            'lastname'    => $customer->lastname,
            'date_add'    => $customer->date_add,
            'email'       => $customer->email,
        );

        // Get count of orders for customer
        $customer_orders_count_obj = new DbQuery();
        $customer_orders_count_obj->select('
            COUNT(o.id_order) AS count_ords,
            SUM(o.total_paid/o.conversion_rate) AS sum_ords
        ');
        $customer_orders_count_obj->from('orders', 'o');
        $customer_orders_count_obj->where('o.id_customer = '.(int)$this->user_id);
        $customer_orders_count_sql = $customer_orders_count_obj->build();
        $customer_orders_count_res = Db::getInstance()->executeS($customer_orders_count_sql);
        $customer_orders_count = array_shift($customer_orders_count_res);

        if ($this->currency_code != $this->def_currency) {
            $customer_orders_count['sum_ords'] = $this->convertPrice(
                $customer_orders_count['sum_ords'],
                $this->def_currency
            );
        }

        $customer_orders_count['sum_ords'] = $this->displayPrice(
            $customer_orders_count['sum_ords'],
            $this->currency_code,
            true
        );

        return array(
            'user_info'       => $customer_info,
            'addresses'       => $customer_addresses,
            'customer_orders' => $this->getCustomerItems(),
            'c_orders_count'  => (int)$customer_orders_count['count_ords'],
            'sum_ords'        => $customer_orders_count['sum_ords']
        );
    }

    private function getCustomerItems()
    {
        $customer_orders_obj = new DbQuery();
        $customer_orders_obj->select('
            o.id_order,
            o.date_add,
            o.total_paid,
            o.id_currency,
            o.current_state AS ord_status_code,
            osl.name AS ord_status,
            c.iso_code,
            SUM(od.product_quantity) AS pr_qty
        ');
        $customer_orders_obj->from('orders', 'o');
        $customer_orders_obj->leftJoin('currency', 'c', 'o.id_currency = c.id_currency');
        $customer_orders_obj->leftJoin('order_detail', 'od', 'od.id_order = o.id_order');
        $customer_orders_obj->leftJoin(
            'order_state_lang',
            'osl',
            'osl.id_order_state = o.current_state AND osl.id_lang = '.(int)$this->def_lang
        );
        $customer_orders_obj->where('o.id_customer = '.(int)$this->user_id);
        $customer_orders_obj->groupBy('o.id_order');
        $customer_orders_obj->orderBy('o.id_order DESC');
        $customer_orders_obj->limit($this->show, ($this->page - 1) * $this->show);
        $customer_orders_sql = $customer_orders_obj->build();
        $customer_orders = Db::getInstance()->executeS($customer_orders_sql);
        $count = count($customer_orders);

        for ($i = 0; $i < $count; $i++) {
            if ($this->currency_code != $customer_orders[$i]['id_currency']) {
                $customer_orders[$i]['total_paid'] = $this->convertPrice(
                    $customer_orders[$i]['total_paid'],
                    $customer_orders[$i]['id_currency']
                );
            }

            $customer_orders[$i]['total_paid'] = $this->displayPrice(
                $customer_orders[$i]['total_paid'],
                $customer_orders[$i]['id_currency']
            );
            unset($customer_orders[$i]['id_currency']);
        }

        return $customer_orders;
    }

    private function getOrders()
    {
        $query_where_parts = array();
        $orders = array();

        if ((int)$this->shop_id > 0) {
            $query_where_parts[] = 'o.id_shop = '.(int)$this->shop_id;
        }

        if (!empty($this->statuses)) {
            $query_where_parts[] = 'o.current_state IN ('.pSQL(str_replace('|', ',', $this->statuses)).')';
        }

        if (!empty($this->search_order_id) && preg_match('/^\d+(?:,\d+)*$/', $this->search_order_id)) {
            $query_where_parts[] = 'o.id_order IN ('.pSQL($this->search_order_id).')';
        } elseif (!empty($this->search_order_id)) {
            $query_where_parts[] = "(CONCAT(cus.firstname, ' ', cus.lastname) LIKE '%".pSQL($this->search_order_id)
                ."%' OR o.reference LIKE '%".pSQL($this->search_order_id)."%')";
        }

        if (!empty($this->orders_from)) {
            $query_where_parts[] = "o.date_add >= '".date('Y-m-d H:i:s', strtotime($this->orders_from.' 00:00:00'))
                ."'";
        }

        if (!empty($this->orders_to)) {
            $query_where_parts[] = "o.date_add <= '".date('Y-m-d H:i:s', strtotime($this->orders_to.' 23:59:59'))."'";
        }

        if (empty($this->sort_by)) {
            $this->sort_by = 'id';
        }

        switch ($this->sort_by) {
            case 'id':
                $order_by = 'o.id_order '.$this->getSortDirection('DESC');
                break;
            case 'date':
                $order_by = 'o.date_add '.$this->getSortDirection('DESC');
                break;
            case 'name':
                $order_by = 'customer '.$this->getSortDirection('ASC');
                break;
            case 'total':
                $order_by = 'o.total_paid '.$this->getSortDirection('DESC');
                break;
            case 'qty':
                $order_by = 'count_prods '.$this->getSortDirection('DESC');
                break;
            default:
                $order_by = '';
                break;
        }

        // Get orders
        $orders_obj = new DbQuery();
        $orders_obj->select(
            "o.id_order,
            o.date_add,
            o.total_paid,
            o.id_currency,
            o.current_state AS status_code,
            c.iso_code,
            CONCAT(cus.firstname, ' ', cus.lastname) AS customer,
            osl.name AS ord_status,
            s.name AS shop_name,
            o.id_shop,
            (SELECT SUM(od.product_quantity) FROM `"._DB_PREFIX_
            .'order_detail` AS od WHERE od.id_order = o.id_order) AS count_prods'
        )
            ->from('orders', 'o')
            ->leftJoin('currency', 'c', 'o.id_currency = c.id_currency')
            ->leftJoin('customer', 'cus', 'o.id_customer = cus.id_customer')
            ->leftJoin(
                'order_state_lang',
                'osl',
                'o.current_state = osl.id_order_state AND osl.id_lang = '.(int)$this->def_lang
            )
            ->leftJoin('shop', 's', 's.id_shop = o.id_shop')
            ->orderBy($order_by)
            ->limit($this->show, ($this->page - 1) * $this->show);

        if (!empty($query_where_parts)) {
            $orders_obj->where(implode(' AND ', $query_where_parts));
        }

        $orders_sql = $orders_obj->build();
        $orders_res = Db::getInstance()->executeS($orders_sql);

        // Convert price data
        foreach ($orders_res as $order) {
            if ($this->currency_code != $order['id_currency']) {
                $order['total_paid'] = $this->convertPrice($order['total_paid'], $order['id_currency']);
            }

            $order['total_paid'] = $this->displayPrice($order['total_paid'], $order['id_currency']);
            unset($order['id_currency']);
            $orders[] = $order;
        }

        // Get orders statistics
        $orders_stats_obj = new DbQuery();
        $orders_stats_obj->select('
            COUNT(o.id_order) AS count_ords,
            SUM(o.total_paid/o.conversion_rate) AS total_paid
        ');
        $orders_stats_obj->from('orders', 'o');
        $orders_stats_obj->leftJoin('currency', 'c', 'o.id_currency = c.id_currency');
        $orders_stats_obj->leftJoin('customer', 'cus', 'o.id_customer = cus.id_customer');
        if (!empty($query_where_parts)) {
            $orders_stats_obj->where(implode(' AND ', $query_where_parts));
        }
        $orders_stats_sql = $orders_stats_obj->build();
        $orders_stats = Db::getInstance()->executeS($orders_stats_sql);
        $orders_stats = array_shift($orders_stats);

        if ($this->currency_code != $this->def_currency) {
            $orders_stats['total_paid'] = $this->convertPrice($orders_stats['total_paid'], $this->def_currency);
        }

        $orders_total = $this->displayPrice($orders_stats['total_paid'], $this->currency_code, true);

        // Get all order statuses
        $orders_status = null;
        if ((int)$this->get_statuses == 1) {
            $orders_status = $this->getOrdersStatuses();
        }

        return array(
            'orders'        => $orders,
            'orders_count'  => (int)$orders_stats['count_ords'],
            'orders_total'  => $orders_total,
            'orders_status' => $orders_status,
        );
    }

    private function getOrdersInfo()
    {
        if ((int)$this->order_id < 1) {
            return false;
        }

        $order = new Order((int)$this->order_id);

        if (!$order->id) {
            return false;
        }

        if ((int)$this->only_items == 1) {
            return array('order_products' => $this->getOrderItems());
        }

        $currency_obj = new Currency($order->id_currency);
        $shop_obj = new Shop($order->id_shop);
        $customer_obj = $order->getCustomer();
        $status = $order->getCurrentStateFull($this->def_lang);
        $address_delivery_obj = new Address($order->id_address_delivery);
        $address_invoice_obj = $order->id_address_delivery != $order->id_address_invoice
            ? new Address($order->id_address_invoice)
            : $address_delivery_obj;

        $order_info = array(
            'id_order'          => $order->id,
            'date_add'          => $order->date_add,
            'id_customer'       => $order->id_customer,
            'id_currency'       => $order->id_currency,
            'total_paid'        => $order->total_paid,
            'total_paid_real'   => $order->total_paid_real,
            'total_discounts'   => $order->total_discounts,
            'total_products'    => $order->total_products,
            'total_products_wt' => $order->total_products_wt,
            'total_shipping'    => $order->total_shipping,
            'total_wrapping'    => $order->total_wrapping,
            'status_code'       => $order->current_state,
            'status'            => isset($status['name']) ? $status['name'] : '',
            'iso_code'          => $currency_obj->iso_code,
            'format'            => $currency_obj->format,
            'customer'          => "$customer_obj->firstname $customer_obj->lastname",
            'email'             => $customer_obj->email,
            'd_name'            => "$address_delivery_obj->firstname $address_delivery_obj->lastname",
            'd_company'         => $address_delivery_obj->company,
            'd_address1'        => $address_delivery_obj->address1,
            'd_address2'        => $address_delivery_obj->address2,
            'd_city'            => $address_delivery_obj->city,
            'd_state'           => State::getNameById($address_delivery_obj->id_state),
            'd_postcode'        => $address_delivery_obj->postcode,
            'd_country'         => $address_delivery_obj->country,
            'd_other'           => $address_delivery_obj->other,
            'd_phone'           => $address_delivery_obj->phone,
            'd_phone_mobile'    => $address_delivery_obj->phone_mobile,
            'i_name'            => "$address_invoice_obj->firstname $address_invoice_obj->lastname",
            'i_company'         => $address_invoice_obj->company,
            'i_address1'        => $address_invoice_obj->address1,
            'i_address2'        => $address_invoice_obj->address2,
            'i_city'            => $address_invoice_obj->city,
            'i_state'           => State::getNameById($address_invoice_obj->id_state),
            'i_postcode'        => $address_invoice_obj->postcode,
            'i_country'         => $address_invoice_obj->country,
            'i_other'           => $address_invoice_obj->other,
            'i_phone'           => $address_invoice_obj->phone,
            'i_phone_mobile'    => $address_invoice_obj->phone_mobile,
            'id_shop'           => $order->id_shop,
            'shop_name'         => $shop_obj->name,
            'p_method'          => $order->payment,
        );

        // Convert and format prices
        $elements_to_format = array(
            'total_paid',
            'total_products',
            'total_products_wt',
            'total_discounts',
            'total_shipping',
            'total_wrapping',
            'total_paid_real'
        );
        $count = count($elements_to_format);
        for ($i = 0; $i < $count; $i++) {
            if ($this->currency_code != $order->id_currency) {
                $order_info[$elements_to_format[$i]] = $this->convertPrice(
                    $order_info[$elements_to_format[$i]],
                    $order->id_currency
                );
            }

            $order_info[$elements_to_format[$i]] = $this->displayPrice(
                $order_info[$elements_to_format[$i]],
                $order_info['id_currency']
            );
        }

        // Get number of products in order
        $order_products_count = Db::getInstance()->getValue(
            'SELECT COUNT(product_id) FROM '._DB_PREFIX_.'order_detail WHERE id_order = '.(int)$this->order_id
        );

        $id_order_carrier = Db::getInstance()->getValue(
            'SELECT `id_order_carrier` FROM `'._DB_PREFIX_.'order_carrier` WHERE `id_order` = '.(int)$order->id
        );

//        $order_carrier_obj = new OrderCarrier($order_obj->getIdOrderCarrier());
        $order_carrier_obj = new OrderCarrier((int)$id_order_carrier);
        $carrier_obj = new Carrier($order_carrier_obj->id_carrier);
        $order_carrier = array(
            'date_add'        => $order_carrier_obj->date_add,
            'carrier_name'    => $carrier_obj->name,
            'track_url'       => str_replace('@', $order_carrier_obj->tracking_number, $carrier_obj->url),
            'weight'          => number_format($order_carrier_obj->weight, 3).' '.$this->weight_unit,
            'shipping_cost'   => $order_carrier_obj->shipping_cost_tax_excl,
            'tracking_number' => $order_carrier_obj->tracking_number,
            'id_currency'     => $order->id_currency
        );

        if ($this->currency_code != $order->id_currency) {
            $order_carrier['shipping_cost'] = $this->convertPrice(
                $order_carrier['shipping_cost'],
                $order->id_currency
            );
        }
        $order_carrier['shipping_cost'] = $this->displayPrice(
            $order_carrier['shipping_cost'],
            $order->id_currency
        );

        if (strpos($order_carrier['track_url'], 'tools.usps.com') !== false) {
            $date_time  = explode(' ', $order_carrier['date_add']);
            $date_parts = explode('-', $date_time[0]);
            $order_carrier['track_url'] .= '&qtc_senddate1='.$date_parts[1].'%2F'.$date_parts[2].'%2F'.$date_parts[0];
        }

        return array(
            'order_info'       => $order_info,
            'order_products'   => $this->getOrderItems(),
            'o_products_count' => (int)$order_products_count,
            'order_tracking'   => array($order_carrier)
        );
    }

    private function getOrderItems()
    {
        $order_products_obj = new DbQuery();
        $order_products_obj->select('
            od.id_order_detail AS id_order_product,
            od.id_order,
            od.product_id,
            od.product_name,
            od.product_reference AS sku,
            od.total_price_tax_excl AS product_price,
            od.product_quantity,
            od.product_ean13 AS ean13,
            od.product_upc AS upc,
            ps.wholesale_price,
            pl.link_rewrite,
            o.id_currency,
            w.`name` AS warehouse_name
        ')
            ->from('order_detail', 'od')
            ->leftJoin('orders', 'o', 'o.id_order = od.id_order')
            ->leftJoin(
                'product_shop',
                'ps',
                'ps.id_product = od.product_id AND ps.id_shop = od.id_shop'
            )
            ->leftJoin(
                'product_lang',
                'pl',
                'ps.id_product = pl.id_product AND pl.id_lang = '.(int)$this->def_lang.' AND pl.id_shop = o.id_shop'
            )
            ->leftJoin('product', 'p', 'p.id_product = od.product_id')
            ->leftJoin('warehouse', 'w', 'w.id_warehouse = od.id_warehouse')
            ->where('od.id_order = '.(int)$this->order_id);

        if ('get_order_product_list_pickup' != $this->call_function) {
            $order_products_obj->limit($this->show, ($this->page - 1) * $this->show);
        }

        if (version_compare(_PS_VERSION_, '1.7', '>=')) {
            $order_products_obj->select('od.product_isbn AS isbn');
        }

        $order_products_sql = $order_products_obj->build();
        $order_products = Db::getInstance()->executeS($order_products_sql);
        $count_products = count($order_products);

        // Convert and form price data
        for ($i = 0; $i < $count_products; $i++) {
            if ($this->currency_code != $order_products[$i]['id_currency']) {
                $order_products[$i]['product_price'] = $this->convertPrice(
                    $order_products[$i]['product_price'],
                    $order_products[$i]['id_currency']
                );
            }

            if ($this->currency_code != $this->def_currency) {
                $order_products[$i]['wholesale_price'] = $this->convertPrice(
                    $order_products[$i]['wholesale_price'],
                    $this->def_currency
                );
            }

            $order_products[$i]['product_price'] = $this->displayPrice(
                $order_products[$i]['product_price'],
                $order_products[$i]['id_currency']
            );
            $order_products[$i]['wholesale_price'] = $this->displayPrice(
                $order_products[$i]['wholesale_price'],
                $order_products[$i]['id_currency']
            );

            // Get url of product image
            $image_url = $this->getProductCoverImageUrl(
                $order_products[$i]['product_id'],
                $order_products[$i]['link_rewrite']
            );
            if ($image_url) {
                $order_products[$i]['product_image'] = $image_url;
            }
        }

        return $order_products;
    }

    private function getOrderProductListPickup()
    {
        return $this->getOrdersInfo();
    }

    private function setOrderAction()
    {
        $this->order_id = (int)$this->order_id;

        if ($this->order_id < 1) {
            return array('error' => 'Order id cannot be empty!');
        }

        if (empty($this->action)) {
            return array('error' => 'Action is not set!');
        }

        $order = new Order($this->order_id);

        if (!$order->id) {
            return array('error' => 'No order was found!');
        }

        $result = array('success' => false);

        switch ($this->action) {
            case 'change_status':
                if ((int)$this->new_status > 0) {
                    $result = $this->changeStatusOrder();
                } else {
                    $result = array('error' => 'New order status is not set!');
                }

                break;
            case 'update_track_number':
                if (isset($this->tracking_number)) {
                    $result = $this->updateTrackNumber();
                } else {
                    $result = array('error' => 'New order tracking number is not set!');
                }

                break;
        }

        return $result;
    }

    private function updateTrackNumber()
    {
        $order       = new Order((int)$this->order_id);
        $lang_id     = $order->id_lang;
        $lang_id_all = Configuration::get('MOBASSISTANTCONNECTOR_TN_LNG');

        if (method_exists('Order', 'setWsShippingNumber')) {
            $is_updated = $order->setWsShippingNumber($this->tracking_number);
        } else {
            $is_updated = $this->setWsShippingNumberOwn($this->tracking_number);
        }

        $result = array('success' => ($is_updated === true) ? 'true' : 'false');

        if ($this->is_mail != 1 || $is_updated !== true) {
            return $result;
        }

        $customer = new Customer($order->id_customer);

        if (Configuration::get('PS_LOGO_MAIL') !== false
            && file_exists(_PS_IMG_DIR_.Configuration::get('PS_LOGO_MAIL', null, null, $order->id_shop))) {
            $logo = _PS_BASE_URL_._PS_IMG_.Configuration::get('PS_LOGO_MAIL', null, null, $order->id_shop);
        } else {
            if (file_exists(_PS_IMG_DIR_.Configuration::get('PS_LOGO', null, null, $order->id_shop))) {
                $logo = _PS_BASE_URL_._PS_IMG_.Configuration::get('PS_LOGO', null, null, $order->id_shop);
            } else {
                $logo = '';
            }
        }

        $data = array(
            '{order_name}' => $order->reference,
            '{firstname}' => $customer->firstname,
            '{lastname}' => $customer->lastname,
            '{tracking_number}' => $this->tracking_number,
            '{shop_name}' => Tools::safeOutput(Configuration::get('PS_SHOP_NAME', null, null, $order->id_shop)),
            '{shop_url}' => Context::getContext()->link->getPageLink(
                'index',
                true,
                Context::getContext()->language->id,
                null,
                false,
                $order->id_shop
            ),
            '{shop_logo}' => $logo,
        );

        if ($lang_id_all != 0) {
            $lang_id = $lang_id_all;
        }

        $mail_body = Tools::file_get_contents(
            _PS_MODULE_DIR_.'mobassistantconnector/mail/common/tracking_number_header.txt'
        );
        $mail_body .= str_replace(
            "\r\n",
            '<br>',
            Configuration::get('MOBASSISTANTCONNECTOR_TN_TEXT', (int)$lang_id)
        );
        $mail_body .= Tools::file_get_contents(
            _PS_MODULE_DIR_.'mobassistantconnector/mail/common/tracking_number_footer.txt'
        );

        foreach ($data as $key => $value) {
            $mail_body = str_replace($key, $value, $mail_body);
        }

        $send = $this->sendMail(
            $customer->email,
            $customer->firstname.' '.$customer->lastname,
            $mail_body,
            'Order Tracking Number',
            $order->id_shop
        );

        if ($send !== true) {
            $result = array('mail_sent_error' => $send);
        }

        return $result;
    }

    private function changeStatusOrder()
    {
        $this->order_id = (int)$this->order_id;

        if ($this->order_id < 1) {
            return false;
        }

        try {
            $order = new Order($this->order_id);
            Context::getContext()->cart = new Cart($order->id_cart); // to avoid conflict with giftcard module
            $order->setCurrentState((int)$this->new_status);
        } catch (Exception $e) {
            $this->file_logger->logMessageCall(
                'change_status_order: exception = '.$e->getMessage(),
                $this->file_logger->level
            );
            return array('error' => $e->getMessage());
        }

        return array('success' => 'true');
    }

    private function getCarriers()
    {
        $carriers_ret = array();
        $carries = Carrier::getCarriers($this->def_lang);

        foreach ($carries as $key => $carrier) {
            $carriers_ret[$key]['id_carrier'] = $carrier['id_carrier'];
            $carriers_ret[$key]['name'] = $carrier['name'];
        }

        return $carriers_ret;
    }

    private function getAbandonedCarts()
    {
        $query_where_parts = array('o.id_order IS NULL');
        $carts             = array();
        $carts_date        = array();

        if ((int)$this->shop_id > 0) {
            $query_where_parts[] = 'c.id_shop = '.(int)$this->shop_id;
        }

        if (!empty($this->search_carts) && preg_match('/^\d+(?:,\d+)*$/', $this->search_carts)) {
            $query_where_parts[] = 'c.id_cart IN ('.pSQL($this->search_carts).')';
        } elseif (!empty($this->search_carts)) {
            $query_where_parts[] = "( COALESCE(CONCAT(cus.firstname, ' ', cus.lastname), 'Guest not registered') LIKE
                '%".pSQL($this->search_carts)."%')";
        }

        if (!empty($this->carts_from)) {
            $query_where_parts[] = "c.date_add >= '".date('Y-m-d H:i:s', strtotime($this->carts_from.' 00:00:00'))."'";
        }

        if (!empty($this->carts_to)) {
            $query_where_parts[] = "c.date_add <= '".date('Y-m-d H:i:s', strtotime($this->carts_to.' 23:59:59'))."'";
        }

        if (empty($this->sort_by)) {
            $this->sort_by = 'id';
        }

        switch ($this->sort_by) {
            case 'id':
                $order_by = 'c.id_cart '.$this->getSortDirection('DESC');
                break;
            case 'date':
                $order_by = 'c.date_add '.$this->getSortDirection('DESC');
                break;
            case 'name':
                $order_by = 'customer '.$this->getSortDirection('ASC');
                break;
            case 'total':
                $order_by = 'cart_total '.$this->getSortDirection('DESC');
                break;
            case 'qty':
                $order_by = 'cart_count_products '.$this->getSortDirection('DESC');
                break;
            default:
                $order_by = '';
                break;
        }

        // Get abandoned carts
        $carts_obj = new DbQuery();
        $carts_obj->select("
            c.id_cart,
            c.date_add,
            c.id_shop,
            c.id_currency,
            c.id_customer,
            cus.email,
            COALESCE(CONCAT(cus.firstname, ' ', cus.lastname), 'Guest not registered') AS customer,
            s.name AS shop_name,
            car.name AS carrier_name,
            SUM((ps.price + pas.price) * cp.quantity) AS cart_total,
            COUNT(cp.id_cart) AS cart_count_products
        ");
        $carts_obj->from('cart', 'c');
        $carts_obj->innerJoin('cart_product', 'cp', 'cp.id_cart = c.id_cart');
        $carts_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop');
        $carts_obj->leftJoin(
            'product_attribute_shop',
            'pas',
            'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop'
        );
        $carts_obj->leftJoin('customer', 'cus', 'c.id_customer = cus.id_customer');
        $carts_obj->leftJoin('orders', 'o', 'o.id_cart = c.id_cart');
        $carts_obj->leftJoin('shop', 's', 's.id_shop = c.id_shop');
        $carts_obj->leftJoin('carrier', 'car', 'car.id_carrier = c.id_carrier');
        $carts_obj->where(implode(' AND ', $query_where_parts));
        $carts_obj->groupBy('c.id_cart');
        $carts_obj->orderBy($order_by);
        $carts_obj->limit($this->show, ($this->page - 1) * $this->show);
        $carts_sql = $carts_obj->build();
        $carts_res = Db::getInstance()->executeS($carts_sql);

        // Convert price data
        foreach ($carts_res as $cart) {
            if ($this->currency_code != $cart['id_currency']) {
                $cart['cart_total'] = $this->convertPrice(
                    $cart['cart_total'],
                    $this->def_currency,
                    $cart['id_currency']
                );
            }

            $carts_date[] = strtotime($cart['date_add']);
            $cart['cart_total'] = $this->displayPrice($cart['cart_total'], $cart['id_currency']);
            $carts[] = $cart;
        }

        // Get abandoned carts total
        $cart_total_obj = new DbQuery();
        $cart_total_obj->select('
            SUM((ps.price + pas.price) * cp.quantity) AS total_sum,
            COUNT(DISTINCT cp.id_cart) AS total_count
        ');
        $cart_total_obj->from('cart', 'c');
        $cart_total_obj->innerJoin('cart_product', 'cp', 'cp.id_cart = c.id_cart');
        $cart_total_obj->leftJoin('orders', 'o', 'o.id_cart = c.id_cart');
        $cart_total_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop');
        $cart_total_obj->leftJoin(
            'product_attribute_shop',
            'pas',
            'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop'
        );
        $cart_total_obj->leftJoin('customer', 'cus', 'c.id_customer = cus.id_customer');
        $cart_total_obj->where(implode(' AND ', $query_where_parts));
        $cart_total_sql = $cart_total_obj->build();
        $cart_total_res = Db::getInstance()->executeS($cart_total_sql);
        $cart_total_res = array_shift($cart_total_res);

        // Convert price data
        if ($this->currency_code != $this->def_currency) {
            $cart_total_res['total_sum'] = $this->convertPrice(
                $cart_total_res['total_sum'],
                $this->def_currency,
                $this->currency_code
            );
        }
        $cart_total_res['total_sum'] = $this->displayPrice($cart_total_res['total_sum'], $this->currency_code, true);

        return array(
            'abandoned_carts'       => $carts,
            'abandoned_carts_count' => $cart_total_res['total_count'],
            'abandoned_carts_total' => $cart_total_res['total_sum'],
        );
    }

    private function getAbandonedCartInfo()
    {
        $this->cart_id = (int)$this->cart_id;

        if ($this->cart_id < 1) {
            return false;
        }

        $cart_obj = new Cart($this->cart_id);

        if (!$cart_obj->id) {
            return false;
        }

        if ((int)$this->only_items == 1) {
            return array('cart_products' => $this->getCartItems());
        }

        // Get cart information
        $cart_info_obj = new DbQuery();
        $cart_info_obj->select("
            c.id_cart,
            c.date_add,
            c.id_currency,
            c.id_customer,
            cus.date_add AS account_registered,
            cus.email,
            a.phone,
            CONCAT(cus.firstname, ' ', cus.lastname) AS customer,
            s.name AS shop_name,
            car.name AS carrier_name,
            SUM((ps.price + pas.price) * cp.quantity) AS cart_total
        ");
        $cart_info_obj->from('cart', 'c');
        $cart_info_obj->innerJoin('cart_product', 'cp', 'cp.id_cart = c.id_cart');
        $cart_info_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop');
        $cart_info_obj->leftJoin(
            'product_attribute_shop',
            'pas',
            'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop'
        );
        $cart_info_obj->leftJoin('customer', 'cus', 'c.id_customer = cus.id_customer');
        $cart_info_obj->leftJoin('address', 'a', 'a.id_customer = cus.id_customer');
        $cart_info_obj->leftJoin('shop', 's', 's.id_shop = c.id_shop');
        $cart_info_obj->leftJoin('carrier', 'car', 'car.id_carrier = c.id_carrier');
        $cart_info_obj->where('c.id_cart = '.(int)$this->cart_id);
        $cart_info_obj->groupBy('c.id_cart');
        $cart_info_sql = $cart_info_obj->build();
        $cart_info = Db::getInstance()->executeS($cart_info_sql);
        $cart_info = array_shift($cart_info);

        if (trim($cart_info['customer']) == '') {
            $cart_info['customer'] = self::GUEST;
        }

        // Convert and format price data
        if ($this->currency_code != $cart_info['id_currency']) {
            $cart_info['cart_total'] = $this->convertPrice(
                $cart_info['cart_total'],
                $this->def_currency,
                $cart_info['id_currency']
            );
        }
        $cart_info['cart_total'] = $this->displayPrice($cart_info['cart_total'], $cart_info['id_currency']);

        // Get cart products count
        $cart_products_count = Db::getInstance()->getValue(
            'SELECT COUNT(id_product) FROM '._DB_PREFIX_.'cart_product WHERE id_cart = '.(int)$this->cart_id
        );

        return array(
            'cart_info'           => $cart_info,
            'cart_products'       => $this->getCartItems(),
            'cart_products_count' => (int)$cart_products_count,
        );
    }

    private function getCartItems()
    {
        $cart_products_obj = new DbQuery();
        $cart_products_obj->select('
            cp.id_product,
            cp.id_product_attribute,
            cp.quantity AS product_quantity,
            p.reference AS sku,
            (ps.price + pas.price) AS product_price,
            (ps.wholesale_price + pas.wholesale_price) AS wholesale_price,
            c.id_currency,
            pl.name AS product_name,
            pl.link_rewrite
        ');
        $cart_products_obj->from('cart_product', 'cp');
        $cart_products_obj->leftJoin(
            'product_shop',
            'ps',
            'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop'
        );
        $cart_products_obj->leftJoin(
            'product_attribute_shop',
            'pas',
            'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop'
        );
        $cart_products_obj->leftJoin('product', 'p', 'p.id_product = cp.id_product');
        $cart_products_obj->leftJoin('cart', 'c', 'c.id_cart = cp.id_cart');
        $cart_products_obj->leftJoin(
            'product_lang',
            'pl',
            'pl.id_product = cp.id_product AND pl.id_shop = cp.id_shop AND pl.id_lang = '.(int)$this->def_lang
        );
        $cart_products_obj->where('cp.id_cart = '.(int)$this->cart_id);
        $cart_products_obj->limit($this->show, ($this->page - 1) * $this->show);
        $cart_products_sql = $cart_products_obj->build();
        $cart_products = Db::getInstance()->executeS($cart_products_sql);
        $count = count($cart_products);

        // Get attribute values
        for ($i = 0; $i < $count; $i++) {
            $product_attributes_obj = new DbQuery();
            $product_attributes_obj->select('
                al.name AS attribute_value,
                agl.public_name AS attribute_name
            ');
            $product_attributes_obj->from('product_attribute_combination', 'pac');
            $product_attributes_obj->leftJoin(
                'attribute_lang',
                'al',
                'al.id_attribute = pac.id_attribute AND al.id_lang = '.(int)$this->def_lang
            );
            $product_attributes_obj->leftJoin('attribute', 'a', 'a.id_attribute = pac.id_attribute');
            $product_attributes_obj->leftJoin(
                'attribute_group_lang',
                'agl',
                'agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = '.(int)$this->def_lang
            );
            $product_attributes_obj->where(
                'pac.id_product_attribute = '.(int)$cart_products[$i]['id_product_attribute']
            );
            $product_attributes_obj->orderBy('attribute_name');
            $product_attributes_sql = $product_attributes_obj->build();
            $product_attributes = Db::getInstance()->executeS($product_attributes_sql);

            $product_attr = array();
            foreach ($product_attributes as $product_attribute) {
                $product_attr[] = $product_attribute['attribute_name'].' : '.$product_attribute['attribute_value'];
            }

            $cart_products[$i]['combination'] = !empty($product_attr) ? implode(', ', $product_attr) : '';

            // Convert and form price data
            if ($this->currency_code != $this->def_currency) {
                $cart_products[$i]['product_price'] = $this->convertPrice(
                    $cart_products[$i]['product_price'],
                    $this->def_currency,
                    $cart_products[$i]['id_currency']
                );
                $cart_products[$i]['wholesale_price'] = $this->convertPrice(
                    $cart_products[$i]['wholesale_price'],
                    $this->def_currency,
                    $cart_products[$i]['id_currency']
                );
            }
            $cart_products[$i]['product_price'] = $this->displayPrice(
                $cart_products[$i]['product_price'],
                $cart_products[$i]['id_currency']
            );
            $cart_products[$i]['wholesale_price'] = $this->displayPrice(
                $cart_products[$i]['wholesale_price'],
                $cart_products[$i]['id_currency']
            );

            // Get url of product image
            $image_url = $this->getProductCoverImageUrl(
                $cart_products[$i]['id_product'],
                $cart_products[$i]['link_rewrite']
            );
            if ($image_url) {
                $cart_products[$i]['product_image'] = $image_url;
            }
        }

        return $cart_products;
    }

    private function getOrderPdf()
    {
        if ((int)$this->order_id < 1) {
            return false;
        }

        $order = new Order((int)$this->order_id);

        if (!Validate::isLoadedObject($order)) {
            return false;
        }

        $order_invoices = $order->getInvoicesCollection();

        if ($order_invoices->count()) {
            $pdf = new PDFCore($order_invoices, PDFCore::TEMPLATE_INVOICE, Context::getContext()->smarty);
        } else {
            $pdf = new PDFMob($order, 'OrderPdf', Context::getContext()->smarty);
            $lang = new Language($order->id_lang);
            $pdf->lang_iso_code = $lang->iso_code;
        }

        return $pdf->render();
    }

    private function statusProcess($status)
    {
        return array(
            'st_id'   => $status['id_order_state'],
            'st_name' => $status['name']
        );
    }

    private function generateOutput($data)
    {
        $add_bridge_version = false;

        if (
            in_array(
                $this->call_function,
                array('test_config', 'get_store_title', 'get_store_stats', 'get_data_graphs', 'get_version')
            )
        ) {
            if (is_array($data) && $data != 'auth_error' && $data != 'connection_error' && $data != 'old_bridge') {
                $add_bridge_version = true;
            }
        }

        if (!is_array($data)) {
            $data = array($data);
        } else {
            $data['module_response'] = '1';
        }

        if (is_array($data)) {
            array_walk_recursive($data, array($this, 'resetNull'));
        }

        if ($add_bridge_version) {
            $data['module_version'] = self::MODULE_VERSION;
            $data['cart_version'] = _PS_VERSION_;
        }

        $data = Tools::jsonEncode($data);

        header('Content-Type: text/javascript;charset=utf-8');
        die($data);
    }

    private function resetNull(&$item)
    {
        if (empty($item) && $item != 0) {
            $item = '';
        }

        $item = trim($item);
    }

    private function getSortDirection($direction_default)
    {
        return !empty($this->order_by) ? $this->order_by : $direction_default;
    }

    private function convertPrice($data, $id_currency, $to_id_currency = false)
    {
        if ($this->currency_code == self::CURRENCY_NOT_SET && !$to_id_currency) {
            return $data;
        } elseif ($this->currency_code == self::CURRENCY_NOT_SET) {
            $currency_to = Currency::getCurrencyInstance($to_id_currency);
        } else {
            $currency_to = Currency::getCurrencyInstance($this->currency_code);
        }

        $currency_from = Currency::getCurrencyInstance($id_currency);

        return Tools::convertPriceFull($data, $currency_from, $currency_to);
    }

    private function displayPrice($data, $id_currency, $resume = false)
    {
        $currency = $id_currency;

        if ($resume !== false) {
            if ($id_currency == self::CURRENCY_NOT_SET) {
                $currency = $this->def_currency;
            }
        } elseif ($this->currency_code != self::CURRENCY_NOT_SET) {
            $currency = $this->currency_code;
        }

        return Tools::displayPrice($data, Currency::getCurrencyInstance($currency));
    }

    private function getCustomPeriod($period = 0)
    {
        $custom_period = array('start_date' => '', 'end_date' => '');
        $format = 'm/d/Y';

        switch ($period) {
            case 0: //3 days
                $custom_period['start_date'] = date($format, mktime(0, 0, 0, date('m'), date('d') - 2, date('Y')));
                $custom_period['end_date'] = date($format, mktime(23, 59, 59, date('m'), date('d'), date('Y')));
                break;
            case 1: //7 days
                $custom_period['start_date'] = date($format, mktime(0, 0, 0, date('m'), date('d') - 6, date('Y')));
                $custom_period['end_date'] = date($format, mktime(23, 59, 59, date('m'), date('d'), date('Y')));
                break;
            case 2: //Prev week
                $custom_period['start_date'] = date(
                    $format,
                    mktime(0, 0, 0, date('N'), date('j') - 6, date('Y')) - ((date('N')) * 3600 * 24)
                );
                $custom_period['end_date'] = date(
                    $format,
                    mktime(23, 59, 59, date('N'), date('j'), date('Y')) - ((date('N')) * 3600 * 24)
                );
                break;
            case 3: //Prev month
                $custom_period['start_date'] = date($format, mktime(0, 0, 0, date('m') - 1, 1, date('Y')));
                $custom_period['end_date'] = date(
                    $format,
                    mktime(23, 59, 59, date('m'), date('d') - date('j'), date('Y'))
                );
                break;
            case 4: //This quarter
                $m = date('n');
                $start_m = 1;
                $end_m = 3;

                if ($m <= 3) {
                    $start_m = 1;
                    $end_m = 3;
                } elseif ($m >= 4 && $m <= 6) {
                    $start_m = 4;
                    $end_m = 6;
                } elseif ($m >= 7 && $m <= 9) {
                    $start_m = 7;
                    $end_m = 9;
                } elseif ($m >= 10) {
                    $start_m = 10;
                    $end_m = 12;
                }

                $custom_period['start_date'] = date($format, mktime(0, 0, 0, $start_m, 1, date('Y')));
                $custom_period['end_date'] = date($format, mktime(23, 59, 59, $end_m + 1, date(1) - 1, date('Y')));
                break;
            case 5: //This year
                $custom_period['start_date'] = date($format, mktime(0, 0, 0, date(1), date(1), date('Y')));
                $custom_period['end_date'] = date($format, mktime(23, 59, 59, date(1), date(1) - 1, date('Y') + 1));
                break;
            case 6: //Last year
                $custom_period['start_date'] = date($format, mktime(0, 0, 0, date(1), date(1), date('Y') - 1));
                $custom_period['end_date'] = date($format, mktime(23, 59, 59, date(1), date(1) - 1, date('Y')));
                break;
            case 8: //Previous quarter
                $m = date('n');
                $start_m = 1;
                $end_m = 3;

                if ($m <= 3) {
                    $start_m = 10;
                    $end_m = 12;
                } elseif ($m >= 4 && $m <= 6) {
                    $start_m = 1;
                    $end_m = 3;
                } elseif ($m >= 7 && $m <= 9) {
                    $start_m = 4;
                    $end_m = 6;
                } elseif ($m >= 10) {
                    $start_m = 7;
                    $end_m = 9;
                }

                $custom_period['start_date'] = date($format, mktime(0, 0, 0, $start_m, 1, date('Y')));
                $custom_period['end_date'] = date($format, mktime(23, 59, 59, $end_m + 1, date(1) - 1, date('Y')));
                break;
        }

        return $custom_period;
    }

    private function setWsShippingNumberOwn($shipping_number)
    {
        $id_order_carrier = Db::getInstance()->getValue(
            'SELECT `id_order_carrier` FROM `'._DB_PREFIX_.'order_carrier` WHERE `id_order` = '.(int)$this->order_id
        );

        if ($id_order_carrier) {
            $order_carrier = new OrderCarrier($id_order_carrier);
            $order_carrier->tracking_number = $shipping_number;
            $order_carrier->update();
        } else {
            $this->shipping_number = $shipping_number;
        }

        return true;
    }

    private function getProductCoverImageUrl($product_id, $link_rewrite, $image_name = 'home')
    {
        $image = Product::getCover($product_id);

        return $this->getProductImageUrl($link_rewrite, $image['id_image'], $image_name);
    }

    private function getProductImageUrl($link_rewrite, $id_image, $image_name)
    {
        $image_url = false;
        $id_image = (int)$id_image;
        $link_rewrite = (string)$link_rewrite;
        $image_type = method_exists('ImageType', 'getFormatedName')
            ? ImageType::getFormatedName($image_name)
            : "{$image_name}_default";

        if (!empty($link_rewrite) && $id_image > 0) {
            $image_url = Context::getContext()->link->getImageLink($link_rewrite, $id_image, $image_type);
        }

        return $image_url;
    }

    private function getAccountIdByEmail($account_email)
    {
        if (empty($account_email)) {
            return false;
        }

        $account_id = Db::getInstance()->getValue(
            'SELECT `id` FROM `'._DB_PREFIX_."mobassistantconnector_accounts` WHERE `account_email` = '"
            .pSQL($account_email)."'"
        );

        if (!$account_id) {
            $insert = Db::getInstance()->execute(
                'INSERT INTO `'._DB_PREFIX_."mobassistantconnector_accounts` (`account_email`, `status`) VALUES ('"
                .pSQL($account_email)."', 1)"
            );

            if ($insert) {
                $account_id = Db::getInstance()->getValue(
                    'SELECT `id` FROM `'._DB_PREFIX_."mobassistantconnector_accounts` WHERE `account_email` = '"
                    .pSQL($account_email)."'"
                );
            }
        }

        return $account_id;
    }

    private function mapPushNotificationToDevice()
    {
        if (!$this->registration_id || !$this->device_unique_id || $this->call_function == 'delete_push_config') {
            return;
        }

        $date       = date('Y-m-d H:i:s');
        $account_id = $this->getAccountIdByEmail((string)$this->account_email);
//        $account_id = $this->getAccountIdByEmail((string)Tools::getValue('account_email'));

        $sql = 'INSERT INTO `'._DB_PREFIX_."mobassistantconnector_devices` (`device_unique`, `account_id`,
            `device_name`, `last_activity`) VALUES ('".$this->device_unique_id."',".(int)$account_id.",'"
            .pSQL($this->device_name)."','".pSQL($date)."') ON DUPLICATE KEY UPDATE `device_name` = '"
            .pSQL($this->device_name)."', `last_activity` = '".pSQL($date)."'";

        if (Db::getInstance()->execute($sql)) {
            $id = Db::getInstance()->getValue(
                'SELECT `device_unique_id` FROM `'._DB_PREFIX_
                ."mobassistantconnector_devices` WHERE `device_unique` = '".pSQL($this->device_unique_id)
                ."' AND `account_id` = ".(int)$account_id
            );

            if ($id) {
                Db::getInstance()->update(
                    'mobassistantconnector_push_notifications',
                    array('device_unique_id' => (int)$id),
                    "`device_id` = '".pSQL($this->registration_id)."'"
                );
            }
        }
    }

    private function updateDeviceLastActivity()
    {
        if (!$this->device_unique_id) {
            return;
        }

        $account_id = $this->getAccountIdByEmail((string)$this->account_email);
//        $account_id = $this->getAccountIdByEmail((string)Tools::getValue('account_email'));
        Db::getInstance()->update(
            'mobassistantconnector_devices',
            array('last_activity' => date('Y-m-d H:i:s')),
            "`device_unique` = '".pSQL($this->device_unique_id)."' AND `account_id` = ".(int)$account_id
        );
    }

    private function sendMail($customer_email, $customer_name, $body, $subject, $id_shop)
    {
        $method = (int)Configuration::get('PS_MAIL_METHOD');

        if ($method == 3) {
            return true;
        }

        if (version_compare(_PS_VERSION_, '1.6.1.5', '<')) {
            return $this->sendMailOnCartVersionLower1615($customer_email, $body, $subject, $id_shop, $method);
        } else {
            return $this->sendMailOnCartVersionHigher1614($customer_email, $customer_name, $body, $subject, $method);
        }
    }

    private function sendMailOnCartVersionLower1615($customer_email, $body, $subject, $id_shop, $method)
    {
        try {
            if ($method == 2) {
                $server     = Configuration::get('PS_MAIL_SERVER');
                $port       = Configuration::get('PS_MAIL_SMTP_PORT');
                $encryption = Configuration::get('PS_MAIL_SMTP_ENCRYPTION');
                $user       = Configuration::get('PS_MAIL_USER');
                $password   = Configuration::get('PS_MAIL_PASSWD');

                if (empty($server) || empty($port)) {
                    return 205;
                }

                $connection = new Swift_Connection_SMTP(
                    $server,
                    $port,
                    ($encryption == 'ssl')
                    ? Swift_Connection_SMTP::ENC_SSL
                    : (($encryption == 'tls') ? Swift_Connection_SMTP::ENC_TLS : Swift_Connection_SMTP::ENC_OFF)
                );
                $connection->setTimeout(4);

                if (!$connection) {
                    return 206;
                }

                if (!empty($user)) {
                    $connection->setUsername($user);
                }

                if (!empty($password)) {
                    $connection->setPassword($password);
                }
            } else {
                $connection = new Swift_Connection_NativeMail();

                if (!$connection) {
                    return 207;
                }
            }

            $swift = new Swift($connection, Configuration::get('PS_MAIL_DOMAIN', null, null, $id_shop));
            $message = new Swift_Message('['.Configuration::get('PS_SHOP_NAME', null, null, $id_shop).'] '.$subject);
            $message->setCharset('utf-8');
            $message->headers->setEncoding('Q');

            if ((Context::getContext()->link instanceof Link) === false) {
                Context::getContext()->link = new Link();
            }

            $message->attach(new Swift_Message_Part($body, 'text/html', '8bit', 'utf-8'));

            /* Send mail */
            $swift->send(
                $message,
                $customer_email,
                new Swift_Address(
                    Configuration::get('PS_SHOP_EMAIL'),
                    Configuration::get('PS_SHOP_NAME', null, null, $id_shop)
                )
            );
            $swift->disconnect();

            return true;
        } catch (Swift_Exception $e) {
            return 208;
        }
    }

    private function sendMailOnCartVersionHigher1614($customer_email, $customer_name, $body, $subject, $method)
    {
        try {
            if ($method == 2) {
                $server     = Configuration::get('PS_MAIL_SERVER');
                $port       = Configuration::get('PS_MAIL_SMTP_PORT');
                $encryption = Configuration::get('PS_MAIL_SMTP_ENCRYPTION');
                $user       = Configuration::get('PS_MAIL_USER');
                $password   = Configuration::get('PS_MAIL_PASSWD');

                if (empty($server) || empty($port)) {
                    return 205;
                }

                $connection = Swift_SmtpTransport::newInstance($server, $port, $encryption)
                    ->setUsername($user)
                    ->setPassword($password);
                $connection->setTimeout(4);

                if (!$connection) {
                    return 206;
                }
            } else {
                $connection = Swift_MailTransport::newInstance();

                if (!$connection) {
                    return 207;
                }
            }

            $swift = Swift_Mailer::newInstance($connection);
            $message = Swift_Message::newInstance();
            $message->addTo($customer_email, $customer_name)
                ->setCharset('utf-8')
                ->setSubject($subject);

            if ((Context::getContext()->link instanceof Link) === false) {
                Context::getContext()->link = new Link();
            }

            // Define and set content type
            $ps_mail_type = Configuration::get('PS_MAIL_TYPE');
            $content_type = null;
            if ($ps_mail_type == Mail::TYPE_BOTH || $ps_mail_type == Mail::TYPE_TEXT) {
                $content_type = 'text/plain';
            }
            if ($ps_mail_type == Mail::TYPE_BOTH || $ps_mail_type == Mail::TYPE_HTML) {
                $content_type = 'text/html';
            }
            $message->addPart($body, $content_type, 'utf-8');

            // Send mail
            $message->setFrom(array(Configuration::get('PS_SHOP_EMAIL') => Configuration::get('PS_SHOP_NAME')));
            $swift->send($message);

            return true;
        } catch (Swift_SwiftException $e) {
            return 208;
        }
    }

    private function getQrCode($hash)
    {
        $result = Db::getInstance()->executeS(
            'SELECT `username`, `password` FROM `'._DB_PREFIX_
            ."mobassistantconnector_users` WHERE `qr_code_hash` = '".pSQL($hash)."' AND `status` = 1"
        );

        if ($result) {
            $array = array();
            $id_shop_default = (int)Configuration::get('PS_SHOP_DEFAULT');
            $shop_info = new Shop($id_shop_default);
            $shop_url = $shop_info->getBaseURL($id_shop_default);
            $store_url = str_replace('http://', '', $shop_url);
            $store_url = str_replace('https://', '', $store_url);
            preg_replace('/\/*$/i', '', $store_url);
            $array['url'] = $store_url;
            $array['login'] = $result[0]['username'];
            $array['password'] = $result[0]['password'];
            $data_to_qr = call_user_func('base64_encode', Tools::jsonEncode($array));

            $shop_url = new ShopUrl($id_shop_default);
            $base_url = $shop_url->getURL(Configuration::get('PS_SSL_ENABLED'));

            include _PS_MODULE_DIR_.'/mobassistantconnector/views/qr_code.phtml';

            echo '<script type="text/javascript">
                        (function() {
                            var qrcode = new QRCode(document.getElementById("mobassistantconnector_qrcode_img"), {
                                width : 300,
                                height : 300
                            });

                            qrcode.makeCode("'.$data_to_qr.'");
                })();
                document.getElementById("mobassistantconnector_base_url_hidden").value="'.$base_url.'"
                </script>';
        } else {
            $this->generateOutput('auth_error');
        }

        die();
    }

    private function getVersion()
    {
        $session_key = '';

        if ($this->hash) {
            $user_data = MobassistantconnectorAccess::checkAuth($this->hash, $this->file_logger);

            if ($user_data) {
                if ($this->session_key) {
                    if (MobassistantconnectorAccess::checkSessionKey($this->session_key, $user_data['user_id'])) {
                        $session_key = $this->session_key;
                    } else {
                        $session_key = MobassistantconnectorAccess::getSessionKey($this->hash, $user_data['user_id']);
                    }
                } else {
                    $session_key = MobassistantconnectorAccess::getSessionKey($this->hash, $user_data['user_id']);
                }
            } else {
                $this->generateOutput('auth_error');
            }
        } elseif ($this->session_key && MobassistantconnectorAccess::checkSessionKey($this->session_key)) {
            $session_key = $this->session_key;
        }

        $this->generateOutput(array('session_key' => $session_key));
    }

    private function checkAllowedActions()
    {
        if (!$this->isActionAllowed()) {
            $this->generateOutput('action_forbidden');
        }
    }

    private function isActionAllowed()
    {
        $allowed_functions_always = getAlwaysAllowedFunctions();

        if (in_array($this->call_function, $allowed_functions_always)) {
            return true;
        }

        $allowed_actions = MobassistantconnectorAccess::getAllowedActionsBySessionKey($this->session_key);
        $restricted_actions_to_functions = getRestrictedActionsToFunctions();
        $is_allowed = false;

        if ($this->call_function == 'set_order_action') {
            if ($this->action == 'change_status' && in_array('update_order_status', $allowed_actions)) {
                $is_allowed = true;
            } elseif ($this->action == 'update_track_number'
                && in_array('update_order_tracking_number', $allowed_actions)) {
                $is_allowed = true;
            }
        } else {
            foreach ($restricted_actions_to_functions as $key => $values) {
                if (in_array($this->call_function, $values)) {
                    if (in_array($key, $allowed_actions)) {
                        $is_allowed = true;
                    }

                    break;
                }
            }
        }

        return $is_allowed;
    }

    private static function checkVersionDefined()
    {
        if (!defined('_PS_VERSION_')) {
            die();
        }
    }

    private static function includeFiles()
    {
        include_once _PS_ROOT_DIR_.'/classes/Mail.php';
//        include_once _PS_ROOT_DIR_.'/tools/swift/Swift.php';
//        include_once _PS_ROOT_DIR_.'/tools/swift/Swift/Connection/SMTP.php';
//        include_once _PS_ROOT_DIR_.'/tools/swift/Swift/Connection/NativeMail.php';
        include_once _PS_MODULE_DIR_.'/mobassistantconnector/classes/FileLoggerMob.php';
        include_once _PS_MODULE_DIR_.'/mobassistantconnector/classes/PDF.php';
        include_once _PS_MODULE_DIR_.'/mobassistantconnector/classes/HTMLTemplateOrderPdf.php';
        include_once _PS_MODULE_DIR_.'/mobassistantconnector/classes/Access.php';
        include_once _PS_MODULE_DIR_.'/mobassistantconnector/functions/functions.php';
        include_once _PS_MODULE_DIR_.'/mobassistantconnector/classes/Constants.php';
    }
}
