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

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

class Sarbacanedesktop extends Module
{

    public function __construct()
    {
        $this->version = '1.0.15';
        $this->name = 'sarbacanedesktop';
        $this->tab = 'emailing';
        $this->author = 'Sarbacane Software';
        $this->need_instance = 1;
        $this->bootstrap = true;
        parent::__construct();
        $this->displayName = $this->l('Sarbacane');
        $this->description = $this->l('Automatically synchronize clients,');
        $this->description .= ' ' . $this->l('accounts and subscribers to your e-shop newsletter with Sarbacane\'s email marketing software;');
        $this->description .= ' ' . $this->l('create and send stunning newsletters and email marketing campaigns.');
    }

    public function install()
    {
        if ( ! $this->checkPrestashopVersion()) {
            return false;
        }
        $result1 = Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'sd_users`');
        $result2 = Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'sd_updates`');
        $result3 = Db::getInstance()->execute('
        CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'sd_users` (
            `sd_id` varchar(200) NOT NULL,
            `list_id` varchar(50) NOT NULL,
            `newsletter_module_status` varchar(30) NOT NULL,
            `last_call_date` datetime NOT NULL,
            PRIMARY KEY (`sd_id`, `list_id`)
        ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8');
        $result4 = Db::getInstance()->execute('
        CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'sd_updates` (
            `email` varchar(255) NOT NULL,
            `id_shop` int(11) unsigned NOT NULL,
            `id_shop_group` int(11) unsigned NOT NULL,
            `list_type` varchar(10) NOT NULL,
            `action` varchar(5) NOT NULL,
            `update_date` datetime NOT NULL,
            PRIMARY KEY (`email`, `list_type`, `id_shop`, `id_shop_group`)
        ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8');
        $result5 = Configuration::updateGlobalValue('SARBACANEDESKTOP_TOKEN', '');
        $result6 = Configuration::updateGlobalValue('SARBACANEDESKTOP_LIST', '');
        $result7 = Configuration::updateGlobalValue('SARBACANEDESKTOP_IS_USER', '');
        $result8 = Configuration::updateGlobalValue('SARBACANEDESKTOP_LAST_UPDATE', '');
        $result9 = Configuration::updateGlobalValue('SARBACANEDESKTOP_FAILED', '');
        $result10 = Configuration::updateGlobalValue('SARBACANEDESKTOP_TIMEZONE', '');
        if ( ! $result1 || ! $result2 || ! $result3 || ! $result4 || ! $result5
        || ! $result6 || ! $result7 || ! $result8 || ! $result9 || ! $result10) {
            return false;
        }
        if ( ! parent::install()) {
            return false;
        }
        $result = $this->registerHook('actionObjectCustomerUpdateBefore');
        if ( ! $result) {
            return false;
        }
        $result = $this->registerHook('actionObjectCustomerDeleteBefore');
        if ( ! $result) {
            return false;
        }
        $result = $this->registerHook('displayHeader');
        if ( ! $result) {
            return false;
        }
        $result = $this->registerHook('displayBackOfficeHeader');
        if ( ! $result) {
            return false;
        }
        return true;
    }

    public function uninstall()
    {
        if ( ! parent::uninstall()) {
            return false;
        }
        if ( ! Configuration::deleteByName('SARBACANEDESKTOP_TOKEN') || ! Configuration::deleteByName('SARBACANEDESKTOP_LIST')
        || ! Configuration::deleteByName('SARBACANEDESKTOP_IS_USER') || ! Configuration::deleteByName('SARBACANEDESKTOP_LAST_UPDATE')
        || ! Configuration::deleteByName('SARBACANEDESKTOP_FAILED') || ! Configuration::deleteByName('SARBACANEDESKTOP_TIMEZONE')) {
            return false;
        }
        Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'sd_users`');
        Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'sd_updates`');
        return true;
    }

    public function hookActionObjectCustomerUpdateBefore($params)
    {
        try {
            if (isset($params)) {
                if (isset($params['object'])) {
                    $customer = $params['object'];
                    if (isset($customer->id) && isset($customer->email) && isset($customer->id_shop)
                        && (isset($customer->id_shop_group) || isset($customer->id_group_shop))) {
                        $inserts_array = array();
                        $customer_id = $customer->id;
                        $email = $customer->email;
                        $id_shop = $customer->id_shop;
                        if (isset($customer->id_shop_group)) {
                            $id_shop_group = $customer->id_shop_group;
                        } else {
                            $id_shop_group = $customer->id_group_shop;
                        }
                        $rq_sql = '
                        SELECT `email`, `newsletter`
                        FROM `' . _DB_PREFIX_ . 'customer`
                        WHERE `id_customer` = ' . (int) $customer_id;
                        $rq = Db::getInstance()->executeS($rq_sql);
                        if (is_array($rq)) {
                            foreach ($rq as $r) {
                                $old_email = $r['email'];
                                $old_newsletter = $r['newsletter'];
                                if ($old_email != $email) {
                                    if ($old_newsletter == '1') {
                                        $inserts_array[] = array(
                                            'email' => $old_email, 'id_shop' => $id_shop,
                                            'id_shop_group' => 0, 'list_type' => 'N', 'action' => 'U'
                                        );
                                    }
                                    $inserts_array[] = array(
                                        'email' => $old_email, 'id_shop' => $id_shop,
                                        'id_shop_group' => $id_shop_group, 'list_type' => 'C', 'action' => 'U'
                                    );
                                }
                            }
                        }
                        $this->userMultiInsertSdUpdatesIfNecessary($inserts_array);
                    }
                }
            }
        }
        catch (Exception $e) {
        }
    }

    public function hookActionObjectCustomerDeleteBefore($params)
    {
        try {
            if (isset($params)) {
                if (isset($params['object'])) {
                    $customer = $params['object'];
                    if (isset($customer->email) && isset($customer->id_shop)
                        && (isset($customer->id_shop_group) || isset($customer->id_group_shop))
                        && isset($customer->newsletter)) {
                        $inserts_array = array();
                        $email = $customer->email;
                        $id_shop = $customer->id_shop;
                        $newsletter = $customer->newsletter;
                        if (isset($customer->id_shop_group)) {
                            $id_shop_group = $customer->id_shop_group;
                        } else {
                            $id_shop_group = $customer->id_group_shop;
                        }
                        if ($newsletter == '1') {
                            $inserts_array[] = array(
                                'email' => $email, 'id_shop' => $id_shop,
                                'id_shop_group' => 0, 'list_type' => 'N', 'action' => 'U'
                            );
                        }
                        $inserts_array[] = array(
                            'email' => $email, 'id_shop' => $id_shop,
                            'id_shop_group' => $id_shop_group, 'list_type' => 'C', 'action' => 'U'
                        );
                        $this->userMultiInsertSdUpdatesIfNecessary($inserts_array);
                    }
                }
            }
        }
        catch (Exception $e) {
        }
    }

    public function hookDisplayBackOfficeHeader()
    {
        try {
            if (Tools::isSubmit('subscribedmerged')) {
                if (Tools::getIsset('controller') && Tools::getIsset('configure') && Tools::getIsset('id')) {
                    $controller = Tools::getValue('controller');
                    $configure = Tools::getValue('configure');
                    $id = Tools::getValue('id');
                    $newsletter_module_name_array = $this->getNewsletterModulesNames();
                    if ($controller == 'AdminModules' && in_array($configure, $newsletter_module_name_array)) {
                        if (strlen($id) > 1) {
                            if (Tools::substr($id, 0, 1) == 'N') {
                                $id = Tools::substr($id, 1);
                                $newsletter_module = $this->checkNewsletterModuleActive(0);
                                if ($newsletter_module['check_if_newsletter_module']) {
                                    $newsletter_module_table = $newsletter_module['newsletter_module_table'];
                                    $rq_sql = '
                                    SELECT `email`, `id_shop`
                                    FROM `' . _DB_PREFIX_ . $newsletter_module_table . '`
                                    WHERE `id` = ' . (int) $id;
                                    $rq = Db::getInstance()->executeS($rq_sql);
                                    if (is_array($rq)) {
                                        foreach ($rq as $r) {
                                            $email = $r['email'];
                                            $id_shop = $r['id_shop'];
                                            $newsletter_module = $this->checkNewsletterModuleActive($id_shop);
                                            if ($newsletter_module['check_if_newsletter_module']) {
                                                if (Validate::isEmail($email)) {
                                                    $this->userInsertSdUpdatesIfNecessary($email, $id_shop, 0, 'N', 'U');
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception $e) {
        }
    }

    public function hookDisplayHeader()
    {
        try {
            if (Tools::isSubmit('submitNewsletter')) {
                if (Tools::getIsset('email') && Tools::getIsset('action')) {
                    $email = Tools::getValue('email');
                    $action = Tools::getValue('action');
                    if ($action == '1') {
                        if (Validate::isEmail($email)) {
                            $id_shop = $this->context->shop->id;
                            $newsletter_module = $this->checkNewsletterModuleActive($id_shop);
                            if ($newsletter_module['check_if_newsletter_module']) {
                                $this->userInsertSdUpdatesIfNecessary($email, $id_shop, 0, 'N', 'U');
                            }
                        }
                    }
                }
            }
            if (Tools::getIsset('token') && Tools::getIsset('module') && Tools::getIsset('controller')) {
                $token = Tools::getValue('token');
                $module = Tools::getValue('module');
                $controller = Tools::getValue('controller');
                if ($controller == 'verification') {
                    $newsletter_module_name_array = $this->getNewsletterModulesNames();
                    if (in_array($module, $newsletter_module_name_array)) {
                        $newsletter_module = $this->checkNewsletterModuleActive(0);
                        if ($newsletter_module['check_if_newsletter_module']) {
                            $newsletter_module_table = $newsletter_module['newsletter_module_table'];
                            $rq_sql = '
                            SELECT `email`
                            FROM `' . _DB_PREFIX_ . $newsletter_module_table . '`
                            WHERE MD5(CONCAT(`email`, `newsletter_date_add`, \'' . pSQL(Configuration::get('NW_SALT')) . '\')) = \'' . pSQL($token) . '\'';
                            $rq = Db::getInstance()->executeS($rq_sql);
                            if (count($rq) > 0) {
                                $inserts_array = array();
                                foreach ($rq as $r) {
                                    $email = $r['email'];
                                    if (Validate::isEmail($email)) {
                                        $rq_sql = '
                                        SELECT `id_shop`
                                        FROM `' . _DB_PREFIX_ . $newsletter_module_table . '`
                                        WHERE `email` = \'' . pSQL($email) . '\'';
                                        $rq = Db::getInstance()->executeS($rq_sql);
                                        foreach ($rq as $r) {
                                            $id_shop = $r['id_shop'];
                                            $newsletter_module = $this->checkNewsletterModuleActive($id_shop);
                                            if ($newsletter_module['check_if_newsletter_module']) {
                                                $inserts_array[] = array(
                                                    'email' => $email, 'id_shop' => $id_shop,
                                                    'id_shop_group' => 0, 'list_type' => 'N', 'action' => 'S'
                                                );
                                            }
                                        }
                                    }
                                }
                                $this->userMultiInsertSdUpdatesIfNecessary($inserts_array);
                            }
                        }
                    }
                }
            }
        }
        catch (Exception $e) {
        }
    }

    private function checkNeedUserInsertSdUpdates($id_shop, $list_type)
    {
        $sd_list_array = $this->getListConfiguration('array');
        $sd_list_string = implode(',', $sd_list_array);
        if (strpos($sd_list_string, $list_type) === false) {
            return false;
        }
        if ($list_type == 'N') {
            $in_array = in_array($id_shop . 'N0', $sd_list_array);
        } else {
            $in_array = in_array($id_shop . 'C0', $sd_list_array) || in_array($id_shop . 'C1', $sd_list_array);
        }
        if ($in_array) {
            $sd_synchronized_lists_array = $this->getSynchronizedListsArray();
            if (in_array($id_shop . $list_type, $sd_synchronized_lists_array )) {
                return true;
            }
        }
        if ($list_type == 'C') {
            $ids_shops_shared = $this->getIdShopsIfShopShareCustomer($id_shop);
            if ($ids_shops_shared) {
                if (is_array($ids_shops_shared)) {
                    if (count($ids_shops_shared) > 0) {
                        $sd_synchronized_lists_array = $this->getSynchronizedListsArray();
                        foreach ($ids_shops_shared as $id_shop_shared) {
                            if ($id_shop_shared != $id_shop) {
                                if (in_array($id_shop_shared . 'C0', $sd_list_array) || in_array($id_shop_shared . 'C1', $sd_list_array)) {
                                    if (in_array($id_shop_shared . 'C', $sd_synchronized_lists_array )) {
                                        return true;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        return false;
    }

    private function getSynchronizedListsArray() {
        if ( ! isset($this->sd_synchronized_lists_array)) {
            $sd_synchronized_lists_array = array();
            $rq_sql = '
            SELECT `list_id`
            FROM `' . _DB_PREFIX_ . 'sd_users`
            GROUP BY `list_id`';
            $rq = Db::getInstance()->executeS($rq_sql);
            if (is_array($rq)) {
                foreach ($rq as $r) {
                    $sd_synchronized_lists_array[] = $r['list_id'];
                }
            }
            $this->sd_synchronized_lists_array = $sd_synchronized_lists_array;
        }
        return $this->sd_synchronized_lists_array;
    }

    private function userInsertSdUpdatesIfNecessary($email, $id_shop, $id_shop_group, $list_type, $action)
    {
        $inserts_array[] = array(
            'email' => $email, 'id_shop' => $id_shop,
            'id_shop_group' => $id_shop_group, 'list_type' => $list_type, 'action' => $action
        );
        $this->userMultiInsertSdUpdatesIfNecessary($inserts_array);
    }

    private function userMultiInsertSdUpdatesIfNecessary($inserts_array = array())
    {
        if (is_array($inserts_array)) {
            if (count($inserts_array) > 0) {
                $now = gmdate('Y-m-d H:i:s');
                $list_check_array = array();
                $query_array = array();
                foreach ($inserts_array as $insert) {
                    if (isset($insert['email'], $insert['id_shop'], $insert['id_shop_group'], $insert['list_type'], $insert['action'])) {
                        $email = $insert['email'];
                        $id_shop = $insert['id_shop'];
                        $id_shop_group = $insert['id_shop_group'];
                        $list_type = $insert['list_type'];
                        $action = $insert['action'];
                        if (!isset($list_check_array[$id_shop . $list_type])) {
                            $list_check_array[$id_shop . $list_type] = $this->checkNeedUserInsertSdUpdates($id_shop, $list_type);
                        }
                        if ($list_check_array[$id_shop . $list_type]) {
                            if ($list_type == 'N') {
                                $id_shop_group = 0;
                            } else {
                                if ($this->getIdShopGroupIfShopShareCustomer($id_shop)) {
                                    $id_shop = 0;
                                } else {
                                    $id_shop_group = 0;
                                }
                            }
                            $query_array[] = '(\'' . pSQL($email) . '\', ' . (int)$id_shop . ', ' . (int)$id_shop_group . ', \'' . pSQL($list_type) . '\', \'' . pSQL($action) . '\', \'' . pSQL($now) . '\')';
                        }
                    }
                }
                if (count($query_array) > 0) {
                    $query = implode(', ', $query_array);
                    $rq_sql = '
                    INSERT INTO `' . _DB_PREFIX_ . 'sd_updates` VALUES
                    ' . $query . '
                    ON DUPLICATE KEY UPDATE
                    `action` = VALUES(`action`),
                    `update_date` = VALUES(`update_date`)';
                    Db::getInstance()->execute($rq_sql);
                }
            }
        }
    }

    public function initSynchronisation()
    {
        $content = '';
        if (Module::isInstalled($this->name) && Tools::getIsset('stk') && Tools::getIsset('sdid') && $this->checkPrestashopVersion()) {
            $stk = $this->getToken();
            if (Tools::getValue('stk') == $stk && strlen($stk) > 30 && ! $this->checkFailed()) {
                $sdid = Tools::getvalue('sdid');
                if ($sdid != '' && $this->getConfiguration('nb_configured') == 3) {
                    $configuration = $this->getConfiguration('all');
                    if ($configuration['sd_token'] != '' && $configuration['sd_is_user'] != '') {
                        $sd_list_array = $this->getListConfiguration('array');
                        if (is_array($sd_list_array)) {
                            ini_set('max_execution_time', 600);
                            if (Tools::getIsset('list')) {
                                $list = Tools::getValue('list');
                                $id_shop = $this->getIdShopFromList($list);
                                $list_type = $this->getListTypeFromList($list);
                                $list_type_array = $this->getListTypeArray();
                                if (in_array($list_type, $list_type_array)) {
                                    if (($list_type == 'N' && in_array($list . '0', $sd_list_array))
                                    || ($list_type == 'C' && (in_array($list . '0', $sd_list_array) || in_array($list . '1', $sd_list_array)))) {
                                        $now = gmdate('Y-m-d H:i:s');
                                        $content = $this->processNewUnsubcribersAndSubscribers($list_type, $id_shop, $sdid);
                                        $this->saveSdid($sdid, $list_type, $id_shop, $now);
                                        $this->clearHistory($list_type, $id_shop);
                                    } else {
                                        header('HTTP/1.1 404 Not found');
                                        die('FAILED_ID');
                                    }
                                } else {
                                    header('HTTP/1.1 404 Not found');
                                    die('FAILED_ID');
                                }
                            } else {
                                if (Tools::getIsset('action') && Tools::getValue('action') == 'delete') {
                                    $this->deleteSdid($sdid);
                                } else {
                                    $this->setDataToUpdate();
                                    $content = $this->getFormattedContentShops($sdid);
                                }
                            }
                        }
                    }
                }
            } else {
                $this->updateFailed();
                header('HTTP/1.1 403 Forbidden');
                die('FAILED');
            }
        }
        return $content;
    }

    private function checkPrestashopVersion()
    {
        if (version_compare(_PS_VERSION_, '1.5.0.9', '<')) {
            return false;
        }
        return true;
    }

    private function checkFailed()
    {
        if ($this->getConfiguration('sd_nb_failed') < 1000000) {
            return false;
        }
        return true;
    }

    private function updateFailed()
    {
        if ( ! $this->checkFailed()) {
            $this->updateConfiguration('sd_nb_failed', $this->getConfiguration('sd_nb_failed') + 1);
        }
    }

    private function processNewUnsubcribersAndSubscribers($list_type, $id_shop, $sd_id)
    {
        $rq_sql = '
        SELECT `last_call_date`
        FROM `' . _DB_PREFIX_ . 'sd_users`
        WHERE `sd_id` = \'' . pSQL($sd_id) . '\'
        AND `list_id` = \'' . pSQL($id_shop . $list_type) . '\'';
        $last_call_date = Db::getInstance()->getValue($rq_sql);
        if ($last_call_date == null || $last_call_date == '') {
            $last_call_date = false;
        }
        $content = 'email;lastname;firstname';
        if ($list_type == 'C') {
            $content .= ';partners';
            if ($this->checkIfListWithCustomerData('C', $id_shop)) {
                $content .= ';date_first_order;date_last_order;amount_min_order;amount_max_order;amount_avg_order;nb_orders;amount_all_orders';
            }
        }
        $content .= ';action';
        $content .= "\n";
        $content .= $this->processNewUnsubscribers($list_type, $id_shop, $last_call_date);
        $content .= $this->processNewSubscribers($list_type, $id_shop, $last_call_date);
        return $content;
    }

    private function getIdShopFromList($list)
    {
        if (Tools::substr($list, -1) == 'N' || Tools::substr($list, -1) == 'C') {
            return Tools::substr($list, 0, -1);
        } else {
            return Tools::substr($list, 0, -2);
        }
    }

    private function getListTypeFromList($list)
    {
        if (Tools::substr($list, -1) == 'N' || Tools::substr($list, -1) == 'C') {
            return Tools::substr($list, -1);
        } else {
            return Tools::substr($list, -2, 1);
        }
    }

    private function checkIfListWithCustomerData($list_type, $id_shop)
    {
        $sd_list_array = $this->getListConfiguration('array');
        if (in_array($id_shop . $list_type . '1', $sd_list_array)) {
            return true;
        }
        return false;
    }

    private function getFormattedContentShops($sd_id)
    {
        $shops = $this->getShopsArray();
        $content = 'list_id;name;reset;is_updated;type;version' . "\n";
        $sd_list_array = $this->getListConfiguration('array');
        foreach ($sd_list_array as $list) {
            $id_shop = $this->getIdShopFromList($list);
            $list_type = $this->getListTypeFromList($list);
            foreach ($shops as $shop) {
                if ($shop['id_shop'] == $id_shop) {
                    $shop_list = $id_shop . $list_type . ';' . $this->dQuote($shop['name']) . ';';
                    $shop_list .= $this->listStatus($id_shop, $list_type, $sd_id) . ';';
                    $shop_list .= 'Prestashop;' . $this->version . "\n";
                    $content .= $shop_list;
                }
            }
        }
        return $content;
    }

    private function listStatus($id_shop, $list_type, $sd_id)
    {
        $list_is_resetted = 'Y';
        $list_is_updated = 'N';
        $last_call_date = false;
        if ( ! isset($this->sd_used_lists)) {
            $rq_sql = '
            SELECT `sd_id`, `list_id`, `newsletter_module_status`, `last_call_date`
            FROM `' . _DB_PREFIX_ . 'sd_users`';
            $this->sd_used_lists = Db::getInstance()->executeS($rq_sql);
        }
        if (is_array($this->sd_used_lists)) {
            foreach ($this->sd_used_lists as $list) {
                if ($list['sd_id'] == $sd_id && $list['list_id'] == $id_shop . $list_type) {
                    $list_is_resetted = 'N';
                    $last_call_date = $list['last_call_date'];
                    if ($last_call_date == null || $last_call_date == '') {
                        $last_call_date = false;
                    }
                    if ($list_type == 'N') {
                        if ($this->getNewsletterModuleStatus($id_shop) != $list['newsletter_module_status']) {
                            $this->deleteListData('N', $id_shop);
                            $list_is_resetted = 'Y';
                            $last_call_date = false;
                            //unset($this->sd_used_lists);
                        }
                    }
                    break;
                }
            }
        }
        if ($this->processNewUnsubscribers($list_type, $id_shop, $last_call_date, 'is_updated') > 0) {
            $list_is_updated = 'Y';
        } else if ($this->processNewSubscribers($list_type, $id_shop, $last_call_date, 'is_updated') > 0) {
            $list_is_updated = 'Y';
        }
        return $list_is_resetted . ';' . $list_is_updated;
    }

    private function getIdShopGroupColumnName()
    {
        if (version_compare(_PS_VERSION_, '1.5.0.9', '=')) {
            return 'id_group_shop';
        }
        return 'id_shop_group';
    }

    private function setDataToUpdate()
    {
        $this->checkTimezone();
        $now = gmdate('Y-m-d H:i:s');
        if (count($this->getSynchronizedListsArray()) > 0) {
            $last_update_date = $this->getConfiguration('sd_last_update');
            if ($last_update_date != '') {
                $now_local = $this->getLocalDate($now);
                $last_update_date_local = $this->getLocalDate($last_update_date);
                $sd_list_array = $this->getListConfiguration('array');
                if (count($sd_list_array) > 0) {
                    $ids_shops_newsletter_array = array();
                    $ids_shops_newsletter_module_array = array();
                    $ids_shops_customer_array = array();
                    foreach ($sd_list_array as $list) {
                        $id_shop = $this->getIdShopFromList($list);
                        $list_type = $this->getListTypeFromList($list);
                        if ($list_type == 'N') {
                            if ($this->checkNeedUserInsertSdUpdates($id_shop, 'N')) {
                                $ids_shops_newsletter_array[] = (int) $id_shop;
                                $newsletter_module = $this->checkNewsletterModuleActive($id_shop);
                                $newsletter_module_table = $newsletter_module['newsletter_module_table'];
                                 if ($newsletter_module['check_if_newsletter_module']) {
                                    $ids_shops_newsletter_module_array[] = (int) $id_shop;
                                }
                            }
                        } else {
                            if ($this->checkNeedUserInsertSdUpdates($id_shop, 'C')) {
                                $ids_shops_customer_array[] = (int) $id_shop;
                            }
                        }
                    }
                    if (count($ids_shops_newsletter_array) > 0) {
                        $ids_shops_newsletter = implode(', ', $ids_shops_newsletter_array);
                        for ($i = 0; $i < 2; $i++) {
                            if ($i == 0) {
                                $rq_sql_action = 'S';
                                $rq_sql_where = ' AND `newsletter` = 1 AND `deleted` = 0';
                            } else {
                                $rq_sql_action = 'U';
                                $rq_sql_where = ' AND (`newsletter` = 0 OR `deleted` = 1)';
                            }
                            $rq_sql = '
                            INSERT INTO `' . _DB_PREFIX_ . 'sd_updates`
                            SELECT `email`, `id_shop`, 0 AS `id_shop_group`,
                            \'N\' AS `list_type`, \'' . $rq_sql_action . '\' AS `action`, \'' . pSQL($now) . '\' AS `update_date`
                            FROM `' . _DB_PREFIX_ . 'customer`
                            WHERE (
                                (`date_upd` >= \'' . pSQL($last_update_date_local) . '\' AND `date_upd` < \'' . pSQL($now_local) . '\')
                                OR
                                (`newsletter_date_add` >= \'' . pSQL($last_update_date_local) . '\' AND `newsletter_date_add` < \'' . pSQL($now_local) . '\')
                            )
                            AND `id_shop` IN (' . $ids_shops_newsletter . ')' .
                            $rq_sql_where . '
                            ON DUPLICATE KEY UPDATE
                            `action` = VALUES(`action`),
                            `update_date` = VALUES(`update_date`)';
                            Db::getInstance()->execute($rq_sql);
                        }
                        if (count($ids_shops_newsletter_module_array) > 0) {
                            $ids_shops_newsletter_module = implode(', ', $ids_shops_newsletter_module_array);
                            $rq_sql = '
                            INSERT INTO `' . _DB_PREFIX_ . 'sd_updates`
                            SELECT `email`, `id_shop`, 0 AS `id_shop_group`,
                            \'N\' AS `list_type`, \'S\' AS `action`, \'' . pSQL($now) . '\' AS `update_date`
                            FROM `' . _DB_PREFIX_ . $newsletter_module_table . '`
                            WHERE `newsletter_date_add` >= \'' . pSQL($last_update_date_local) . '\'
                            AND `newsletter_date_add` < \'' . pSQL($now_local) . '\'
                            AND `id_shop` IN (' . $ids_shops_newsletter_module . ')
                            AND `active` = 1
                            ON DUPLICATE KEY UPDATE
                            `action` = VALUES(`action`),
                            `update_date` = VALUES(`update_date`)';
                            Db::getInstance()->execute($rq_sql);
                        }
                    }
                    if (count($ids_shops_customer_array) > 0) {
                        $id_shop_group_column_name = $this->getIdShopGroupColumnName();
                        $ids_shops_customer_and_order_array_shared = array();
                        $ids_shops_customer_array_not_shared = array();
                        $ids_shops_order_array_not_shared = array();
                        foreach ($ids_shops_customer_array as $id_shop) {
                            if ($this->getIdShopGroupIfShopShareCustomer($id_shop)) {
                                $ids_shops_customer_and_order_array_shared[] = (int) $id_shop;
                            } else {
                                $ids_shops_customer_array_not_shared[] = (int) $id_shop;
                                if (in_array($id_shop . 'C1', $sd_list_array)) {
                                    $ids_shops_order_array_not_shared[] = (int) $id_shop;
                                }
                            }
                        }
                        $ids_shops_customer_array = array();
                        $ids_shops_order_array = array();
                        if (count($ids_shops_customer_and_order_array_shared) > 0) {
                            $ids_shops_customer_array[] = array(
                                'type' => 'shared',
                                'data' => implode(', ', $ids_shops_customer_and_order_array_shared)
                            );
                            $ids_shops_order_array[] = array(
                                'type' => 'shared',
                                'data' => implode(', ', $ids_shops_customer_and_order_array_shared)
                            );
                        }
                        if (count($ids_shops_customer_array_not_shared) > 0) {
                            $ids_shops_customer_array[] = array(
                                'type' => 'not_shared',
                                'data' => implode(', ', $ids_shops_customer_array_not_shared)
                            );
                        }
                        if (count($ids_shops_order_array_not_shared) > 0) {
                            $ids_shops_order_array[] = array(
                                'type' => 'not_shared',
                                'data' => implode(', ', $ids_shops_order_array_not_shared)
                            );
                        }
                        foreach ($ids_shops_customer_array as $ids_shops_customer) {
                            $id_shop = '`id_shop`';
                            $id_shop_group = '0';
                            if ($ids_shops_customer['type'] == 'shared') {
                                $id_shop = '0';
                                $id_shop_group = '`' . $id_shop_group_column_name . '`';
                            }
                            for ($i = 0; $i < 2; $i++) {
                                if ($i == 0) {
                                    $rq_sql_action = 'S';
                                    $rq_sql_where = '`deleted` = 0';
                                } else {
                                    $rq_sql_action = 'U';
                                    $rq_sql_where = '`deleted` = 1';
                                }
                                $rq_sql = '
                                INSERT INTO `' . _DB_PREFIX_ . 'sd_updates`
                                SELECT `email`, ' . $id_shop . ' AS `id_shop`, ' . $id_shop_group . ' AS `id_shop_group`,
                                \'C\' AS `list_type`, \'' . $rq_sql_action . '\' AS `action`, \'' . pSQL($now) . '\' AS `update_date`
                                FROM `' . _DB_PREFIX_ . 'customer`
                                WHERE `date_upd` >= \'' . pSQL($last_update_date_local) . '\'
                                AND `date_upd` < \'' . pSQL($now_local) . '\'
                                AND `id_shop` IN (' . $ids_shops_customer['data'] . ')
                                AND ' . $rq_sql_where . '
                                ON DUPLICATE KEY UPDATE
                                `action` = VALUES(`action`),
                                `update_date` = VALUES(`update_date`)';
                                Db::getInstance()->execute($rq_sql);
                            }
                        }
                        foreach ($ids_shops_order_array as $ids_shops_order) {
                            $id_shop = 'c.`id_shop`';
                            $id_shop_group = '0';
                            $rq_sql_where = 'o.`date_upd` >= \'' . pSQL($last_update_date_local) . '\' AND o.`date_upd` < \'' . pSQL($now_local) . '\'';
                            if ($ids_shops_order['type'] == 'shared') {
                                $id_shop = '0';
                                $id_shop_group = 'c.`' . $id_shop_group_column_name . '`';
                                $rq_sql_where = '(
                                    (o.`date_upd` >= \'' . pSQL($last_update_date_local) . '\' AND o.`date_upd` < \'' . pSQL($now_local) . '\')
                                    OR
                                    (c.`date_upd` >= \'' . pSQL($last_update_date_local) . '\' AND c.`date_upd` < \'' . pSQL($now_local) . '\')
                                )';
                            }
                            $rq_sql = '
                            INSERT INTO `' . _DB_PREFIX_ . 'sd_updates`
                            SELECT c.`email`, ' . $id_shop . ' AS `id_shop`, ' . $id_shop_group . ' AS `id_shop_group`,
                            \'C\' AS `list_type`, \'S\' AS `action`, \'' . pSQL($now) . '\' AS `update_date`
                            FROM `' . _DB_PREFIX_ . 'orders` o
                            INNER JOIN `' . _DB_PREFIX_ . 'customer` c
                                ON c.`id_customer` = o.`id_customer`
                                AND c.`deleted` = 0
                            WHERE ' . $rq_sql_where . '
                            AND o.`id_shop` IN (' . $ids_shops_order['data'] . ')
                            ON DUPLICATE KEY UPDATE
                            `action` = VALUES(`action`),
                            `update_date` = VALUES(`update_date`)';
                            Db::getInstance()->execute($rq_sql);
                        }
                    }
                }
            }
        }
        $this->updateConfiguration('sd_last_update', $now);
    }

    private function checkTimezone() {
        $ps_timezone = Configuration::getGlobalValue('PS_TIMEZONE');
        $sd_timezone = $this->getConfiguration('sd_timezone');
        if ($sd_timezone == '') {
            $this->updateConfiguration('sd_timezone', $ps_timezone);
        } else if ($ps_timezone != $sd_timezone) {
            $rq_sql = 'TRUNCATE `' . _DB_PREFIX_ . 'sd_updates`';
            Db::getInstance()->execute($rq_sql);
            $rq_sql = 'TRUNCATE `' . _DB_PREFIX_ . 'sd_users`';
            Db::getInstance()->execute($rq_sql);
            $this->updateConfiguration('sd_timezone', $ps_timezone);
        }
    }

    private function getLocalDate($date) {
        $sd_timezone = $this->getConfiguration('sd_timezone');
        $date = DateTime::createFromFormat('Y-m-d H:i:s', $date, new DateTimeZone('UTC'));
        $date->setTimeZone(new DateTimeZone($sd_timezone));
        return $date->format('Y-m-d H:i:s');
    }

    private function getNewsletterModuleStatus($id_shop)
    {
        $newsletter_module = $this->checkNewsletterModuleActive($id_shop);
        if ($newsletter_module['check_if_newsletter_module']) {
            return 'enabled-' . $newsletter_module['newsletter_module_name'];
        } else {
            return 'disabled';
        }
    }

    private function getToken()
    {
        $str = $this->getConfiguration('sd_token');
        $str = explode('-', $str);
        $token = '';
        foreach ($str as $key => $s) {
            if ($key == 0) {
                $token = $s . Tools::substr(Tools::encrypt('SecurityTokenForModule'), 0, 11) . $s;
                $token = Tools::encrypt($token);
            } else {
                $token .= $s;
            }
        }
        return $token;
    }

    private function dQuote($value)
    {
        $value = str_replace('"', '""', $value);
        if (strpos($value, ' ') !== false || strpos($value, ';') !== false) {
            $value = '"' . $value . '"';
        }
        return $value;
    }

    private function getShopsArray()
    {
        $rq_sql = '
        SELECT `id_shop`, `name`
        FROM `' . _DB_PREFIX_ . 'shop`
        ORDER BY `id_shop` ASC';
        $rq = Db::getInstance()->executeS($rq_sql);
        $shops_array = array();
        if (is_array($rq)) {
            foreach ($rq as $r) {
                $shops_array[] = array(
                    'id_shop' => $r['id_shop'],
                    'name'    => $r['name']
                );
            }
        }
        return $shops_array;
    }

    private function getListTypeArray()
    {
        return array('N', 'C');
    }

    private function getNewsletterModulesNames()
    {
        if (version_compare(_PS_VERSION_, '1.7', '<')) {
            return array('blocknewsletter');
        } else {
            return array('ps_emailsubscription', 'blocknewsletter');
        }
    }

    private function checkNewsletterModuleActive($id_shop = 0)
    {
        $result = array(
            'check_if_newsletter_module' => false,
            'newsletter_module_name'     => '',
            'newsletter_module_table'    => ''
        );
        $newsletter_module_name_array = $this->getNewsletterModulesNames();
        foreach ($newsletter_module_name_array as $newsletter_module_name) {
            $newsletter_module = false;
            if ($id_shop != 0) {
                if ( ! isset($this->{'sd_' . $newsletter_module_name . '_shop_active'})) {
                    $rq_sql = '
                    SELECT ms.`id_shop`, COUNT(*) AS `nb`
                    FROM `' . _DB_PREFIX_ . 'module` m,
                    `' . _DB_PREFIX_ . 'module_shop` ms
                    WHERE m.`name` = \'' . $newsletter_module_name . '\'
                    AND m.`active` = 1
                    AND m.`id_module` = ms.`id_module`
                    GROUP BY ms.`id_shop`';
                    $this->{'sd_' . $newsletter_module_name . '_shop_active'} = Db::getInstance()->executeS($rq_sql);
                }
                if (is_array($this->{'sd_' . $newsletter_module_name . '_shop_active'})) {
                    foreach ($this->{'sd_' . $newsletter_module_name . '_shop_active'} as $shop) {
                        if ($shop['id_shop'] == $id_shop) {
                            if ($shop['nb'] == 1) {
                                $newsletter_module = true;
                            }
                            break;
                        }
                    }
                }
            } else {
                if ( ! isset($this->{'sd_' . $newsletter_module_name . '_global_active'})) {
                    $rq_sql = '
                    SELECT COUNT(*) AS `nb`
                    FROM `' . _DB_PREFIX_ . 'module`
                    WHERE `name` = \'' . $newsletter_module_name . '\'
                    AND `active` = 1';
                    $this->{'sd_' . $newsletter_module_name . '_global_active'} = Db::getInstance()->executeS($rq_sql);
                }
                if (is_array($this->{'sd_' . $newsletter_module_name . '_global_active'})) {
                    foreach ($this->{'sd_' . $newsletter_module_name . '_global_active'} as $shop) {
                        if ($shop['nb'] == 1) {
                            $newsletter_module = true;
                        }
                    }
                }
            }
            if ($newsletter_module) {
                if ($newsletter_module_name == 'blocknewsletter') {
                    $newsletter_module_table = 'newsletter';
                } else {
                    $newsletter_module_table = 'emailsubscription';
                }
                if ( ! isset($this->{'sd_' . $newsletter_module_table . '_ok'})) {
                    $this->{'sd_' . $newsletter_module_table . '_ok'} = false;
                    $rq_sql = 'SHOW TABLES LIKE \'' . _DB_PREFIX_ . $newsletter_module_table . '\'';
                    $rq = Db::getInstance()->executeS($rq_sql);
                    if (count($rq) > 0) {
                        $rq_sql = 'SHOW COLUMNS FROM `' . _DB_PREFIX_ . $newsletter_module_table . '`';
                        $rq = Db::getInstance()->executeS($rq_sql);
                        if (is_array($rq)) {
                            $columns = array(
                                'email'               => false,
                                'id_shop'             => false,
                                'active'              => false,
                                'newsletter_date_add' => false
                            );
                            foreach ($rq as $r) {
                                if (isset($r['Field'])) {
                                    if ($r['Field'] == 'email') {
                                        $columns['email'] = true;
                                    } else if ($r['Field'] == 'id_shop') {
                                        $columns['id_shop'] = true;
                                    } else if ($r['Field'] == 'active') {
                                        $columns['active'] = true;
                                    } else if ($r['Field'] == 'newsletter_date_add') {
                                        $columns['newsletter_date_add'] = true;
                                    }
                                }
                            }
                            if ($columns['email'] && $columns['id_shop'] && $columns['active'] && $columns['newsletter_date_add']) {
                                $this->{'sd_' . $newsletter_module_table . '_ok'} = true;
                            }
                        }
                    }
                }
                if ($this->{'sd_' . $newsletter_module_table . '_ok'}) {
                    $result = array(
                        'check_if_newsletter_module' => true,
                        'newsletter_module_name'     => $newsletter_module_name,
                        'newsletter_module_table'    => $newsletter_module_table
                    );
                    break;
                }
            }
        }
        return $result;
    }

    private function getIdShopGroupIfShopShareCustomer($id_shop) {
        $sd_shared_shops = $this->getSharedShops();
        if (is_array($sd_shared_shops)) {
            foreach ($sd_shared_shops as $shop) {
                if ($shop['id_shop'] == $id_shop) {
                    return $shop['id_shop_group'];
                }
            }
        }
        return false;
    }

    private function getIdShopsIfShopShareCustomer($id_shop) {
        $id_shop_group = $this->getIdShopGroupIfShopShareCustomer($id_shop);
        if ($id_shop_group) {
            $sd_shared_shops = $this->getSharedShops();
            if (is_array($sd_shared_shops)) {
                $ids_shops_shared = array();
                foreach ($sd_shared_shops as $shared_shop) {
                    if ($shared_shop['id_shop_group'] == $id_shop_group) {
                        $ids_shops_shared[] = $shared_shop['id_shop'];
                    }
                }
                return $ids_shops_shared;
            }
        }
        return false;
    }

    private function getSharedShops() {
        if ( ! isset($this->sd_shared_shops)) {
            $id_shop_group_column_name = $this->getIdShopGroupColumnName();
            $id_shop_group_table = str_replace('id_', '', $id_shop_group_column_name);
            $rq_sql = '
            SELECT s.`id_shop`, sg.`' . $id_shop_group_column_name . '` AS `id_shop_group`
            FROM `' . _DB_PREFIX_ . 'shop` s,
            `' . _DB_PREFIX_ . $id_shop_group_table . '` sg
            WHERE s.`' . $id_shop_group_column_name . '` = sg.`' . $id_shop_group_column_name . '`
            AND sg.`share_customer` = 1';
            $this->sd_shared_shops = Db::getInstance()->executeS($rq_sql);
        }
        return $this->sd_shared_shops;
    }

    private function processNewSubscribers($list_type, $id_shop, $last_call_date, $type_action = 'display')
    {
        $newsletter_module = $this->checkNewsletterModuleActive($id_shop);
        $check_if_newsletter_module = $newsletter_module['check_if_newsletter_module'];
        $newsletter_module_table = $newsletter_module['newsletter_module_table'];
        $rq_sql_limit = '';
        if ($type_action == 'is_updated') {
            $rq_sql_limit = ' LIMIT 0, 1';
        }
        if ($list_type == 'N') {
            if ( ! $last_call_date) {
                $rq_sql = '';
                if ($check_if_newsletter_module) {
                    $rq_sql .= '
                    (
                        SELECT `email`, \'\' AS `lastname`, \'\' AS `firstname`
                        FROM `' . _DB_PREFIX_ . $newsletter_module_table . '`
                        WHERE `id_shop` = ' . (int) $id_shop . '
                        AND `active` = 1
                    )
                    UNION ALL';
                }
                $rq_sql .= '
                    (
                        SELECT `email`,
                        SUBSTRING_INDEX(GROUP_CONCAT(`lastname` ORDER BY `date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `lastname`,
                        SUBSTRING_INDEX(GROUP_CONCAT(`firstname` ORDER BY `date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `firstname`
                        FROM `' . _DB_PREFIX_ . 'customer`
                        WHERE `id_shop` = ' . (int) $id_shop . '
                        AND `newsletter` = 1
                        AND `deleted` = 0
                        GROUP BY `email`
                    )';
            } else {
                $rq_sql = '';
                if ($check_if_newsletter_module) {
                    $rq_sql .= '
                    (
                        SELECT n.`email` AS `email`, \'\' AS `lastname`, \'\' AS `firstname`
                        FROM `' . _DB_PREFIX_ . 'sd_updates` s
                        INNER JOIN `' . _DB_PREFIX_ . $newsletter_module_table . '` n
                            ON n.`email` = s.`email`
                            AND n.`id_shop` = s.`id_shop`
                            AND n.`active` = 1
                        WHERE s.`id_shop` = ' . (int) $id_shop . '
                        AND s.`list_type` = \'N\'
                        AND s.`update_date` >= \'' . pSQL($last_call_date) . '\'
                    )
                    UNION ALL';
                }
                $rq_sql .= '
                    (
                        SELECT c.`email` AS `email`,
                        SUBSTRING_INDEX(GROUP_CONCAT(c.`lastname` ORDER BY c.`date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `lastname`,
                        SUBSTRING_INDEX(GROUP_CONCAT(c.`firstname` ORDER BY c.`date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `firstname`
                        FROM `' . _DB_PREFIX_ . 'sd_updates` s
                        INNER JOIN `' . _DB_PREFIX_ . 'customer` c
                            ON c.`email` = s.`email`
                            AND c.`id_shop` = s.`id_shop`
                            AND c.`newsletter` = 1
                            AND c.`deleted` = 0
                        WHERE s.`id_shop` = ' . (int) $id_shop . '
                        AND s.`list_type` = \'N\'
                        AND s.`update_date` >= \'' . pSQL($last_call_date) . '\'
                        GROUP BY c.`email`
                    )';
            }
            $rq_sql .= $rq_sql_limit;
        } else if ($list_type == 'C') {
            $id_shop_group = $this->getIdShopGroupIfShopShareCustomer($id_shop);
            if ($id_shop_group) {
                $id_shop_group_column_name = $this->getIdShopGroupColumnName();
            }
            $add_customer_data = $this->checkIfListWithCustomerData('C', $id_shop);
            if ($add_customer_data) {
                if ($id_shop_group) {
                    $rq_sql_select = '
                    SELECT t.`email`,
                    SUBSTRING_INDEX(GROUP_CONCAT(`t`.`lastname` ORDER BY `t`.`priority` ASC SEPARATOR \'||\'), \'||\', 1) AS `lastname`,
                    SUBSTRING_INDEX(GROUP_CONCAT(`t`.`firstname` ORDER BY `t`.`priority` ASC SEPARATOR \'||\'), \'||\', 1) AS `firstname`,
                    MAX(t.`optin`) AS `optin`,
                    MIN(t.`date_first_order`) AS `date_first_order`, MAX(t.`date_last_order`) AS `date_last_order`,
                    MIN(t.`amount_min_order`) AS `amount_min_order`, MAX(t.`amount_max_order`) AS `amount_max_order`,
                    ROUND(SUM(t.`amount_all_orders`)/SUM(t.`nb_orders`), 6) AS `amount_avg_order`, SUM(t.`nb_orders`) AS `nb_orders`,
                    SUM(t.`amount_all_orders`) AS `amount_all_orders`
                    FROM
                    (';
                }
                $rq_sql_sub_select = '
                SELECT c.`email`,
                SUBSTRING_INDEX(GROUP_CONCAT(c.`lastname` ORDER BY c.`date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `lastname`,
                SUBSTRING_INDEX(GROUP_CONCAT(c.`firstname` ORDER BY c.`date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `firstname`,
                MAX(c.`optin`) AS `optin`, MIN(o.`date_add`) AS `date_first_order`, MAX(o.`date_add`) AS `date_last_order`, MIN(o.`total_paid_tax_incl`) AS `amount_min_order`,
                MAX(o.`total_paid_tax_incl`) AS `amount_max_order`, ROUND(SUM(o.`total_paid_tax_incl`)/COUNT(o.`date_add`), 6) AS `amount_avg_order`,
                COUNT(o.`date_add`) AS `nb_orders`, SUM(o.`total_paid_tax_incl`) AS `amount_all_orders`';
            } else {
                if ($id_shop_group) {
                    $rq_sql_select = '
                    SELECT t.`email`,
                    SUBSTRING_INDEX(GROUP_CONCAT(`t`.`lastname` ORDER BY `t`.`priority` ASC SEPARATOR \'||\'), \'||\', 1) AS `lastname`,
                    SUBSTRING_INDEX(GROUP_CONCAT(`t`.`firstname` ORDER BY `t`.`priority` ASC SEPARATOR \'||\'), \'||\', 1) AS `firstname`,
                    MAX(t.`optin`) AS `optin`
                    FROM
                    (';
                }
                $rq_sql_sub_select = '
                SELECT c.`email`,
                SUBSTRING_INDEX(GROUP_CONCAT(c.`lastname` ORDER BY c.`date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `lastname`,
                SUBSTRING_INDEX(GROUP_CONCAT(c.`firstname` ORDER BY c.`date_upd` DESC SEPARATOR \'||\'), \'||\', 1) AS `firstname`,
                MAX(c.`optin`) AS `optin`';
            }
            $rq_sql_group_by = '
            ) AS t
            GROUP BY t.`email`';
            if ( ! $last_call_date) {
                if ( ! $id_shop_group) {
                    $rq_sql =
                    $rq_sql_sub_select . '
                    FROM `' . _DB_PREFIX_ . 'customer` c
                    LEFT JOIN `' . _DB_PREFIX_ . 'orders` o
                        ON o.`id_customer` = c.`id_customer`
                        AND o.`id_shop` = c.`id_shop`
                    WHERE c.`id_shop` = ' . (int)$id_shop . '
                    AND c.`deleted` = 0
                    GROUP BY c.`email`';
                } else {
                    $rq_sql =
                    $rq_sql_select . '
                        (
                            ' . $rq_sql_sub_select . ', 1 AS `priority`
                            FROM `' . _DB_PREFIX_ . 'customer` c
                            LEFT JOIN `' . _DB_PREFIX_ . 'orders` o
                                ON o.`id_customer` = c.`id_customer`
                                AND o.`id_shop` = c.`id_shop`
                            WHERE c.`id_shop` = ' . (int)$id_shop . '
                            AND c.`deleted` = 0
                            GROUP BY c.`email`
                        )
                        UNION ALL
                        (
                            ' . $rq_sql_sub_select . ', 2 AS `priority`
                            FROM `' . _DB_PREFIX_ . 'customer` c
                            INNER JOIN `' . _DB_PREFIX_ . 'orders` o
                                ON o.`id_customer` = c.`id_customer`
                                AND o.`id_shop` = ' . (int) $id_shop . '
                            WHERE c.`' . $id_shop_group_column_name . '` = ' . (int) $id_shop_group . '
                            AND c.`id_shop` != ' . (int) $id_shop . '
                            AND c.`deleted` = 0
                            GROUP BY c.`email`
                        )' .
                    $rq_sql_group_by;
                }
            } else {
                if ( ! $id_shop_group) {
                    $rq_sql =
                    $rq_sql_sub_select . '
                    FROM `' . _DB_PREFIX_ . 'sd_updates` s
                    INNER JOIN `' . _DB_PREFIX_ . 'customer` c
                        ON c.`email` = s.`email`
                        AND c.`id_shop` = s.`id_shop`
                        AND c.`deleted` = 0
                    LEFT JOIN `' . _DB_PREFIX_ . 'orders` o
                        ON o.`id_customer` = c.`id_customer`
                        AND o.`id_shop` = s.`id_shop`
                    WHERE s.`id_shop` = ' . (int) $id_shop . '
                    AND s.`list_type` = \'C\'
                    AND s.`update_date` >= \'' . pSQL($last_call_date) . '\'
                    GROUP BY c.`email`';
                } else {
                    $rq_sql =
                    $rq_sql_select . '
                        (
                            ' . $rq_sql_sub_select . ', 1 AS `priority`
                            FROM `' . _DB_PREFIX_ . 'sd_updates` s
                            INNER JOIN `' . _DB_PREFIX_ . 'customer` c
                                ON c.`email` = s.`email`
                                AND c.`id_shop` = ' . (int) $id_shop . '
                                AND c.`deleted` = 0
                            LEFT JOIN `' . _DB_PREFIX_ . 'orders` o
                                ON o.`id_customer` = c.`id_customer`
                                AND o.`id_shop` = ' . (int) $id_shop . '
                            WHERE s.`id_shop_group` = ' . (int) $id_shop_group . '
                            AND s.`list_type` = \'C\'
                            AND s.`update_date` >= \'' . pSQL($last_call_date) . '\'
                            GROUP BY c.`email`
                        )
                        UNION ALL
                        (
                            ' . $rq_sql_sub_select . ', 2 AS `priority`
                            FROM `' . _DB_PREFIX_ . 'sd_updates` s
                            INNER JOIN `' . _DB_PREFIX_ . 'customer` c
                                ON c.`email` = s.`email`
                                AND c.`' . $id_shop_group_column_name . '` = s.`id_shop_group`
                                AND c.`id_shop` != ' . (int) $id_shop . '
                                AND c.`deleted` = 0
                            INNER JOIN `' . _DB_PREFIX_ . 'orders` o
                                ON o.`id_customer` = c.`id_customer`
                                AND o.`id_shop` = ' . (int) $id_shop . '
                            WHERE s.`id_shop_group` = ' . (int) $id_shop_group . '
                            AND s.`list_type` = \'C\'
                            AND s.`update_date` >= \'' . pSQL($last_call_date) . '\'
                            GROUP BY c.`email`
                        )' .
                    $rq_sql_group_by;
                }
            }
            $rq_sql .= $rq_sql_limit;
        } else {
            if ($type_action == 'is_updated') {
                return 0;
            }
            return '';
        }
        $rq = Db::getInstance()->query($rq_sql);
        $content = '';
        while ($r = Db::getInstance()->nextRow($rq)) {
            if ($type_action == 'is_updated') {
                return 1;
            }
            $content .= $this->dQuote($r['email']) . ';';
            $content .= $this->dQuote($r['lastname']) . ';' . $this->dQuote($r['firstname']);
            if ($list_type == 'C') {
                $content .= ';' . $r['optin'];
                if ($add_customer_data) {
                    $content .= ';' . $this->dQuote($r['date_first_order']) . ';' . $this->dQuote($r['date_last_order']);
                    $content .= ';' . (float) $r['amount_min_order'] . ';' . (float) $r['amount_max_order'] . ';' . (float) $r['amount_avg_order'];
                    $content .= ';' . (int) $r['nb_orders'] . ';' . (float) $r['amount_all_orders'];
                }
            }
            $content .= ';S' . "\n";
        }
        if ($type_action == 'is_updated') {
            return 0;
        }
        return $content;
    }

    private function processNewUnsubscribers($list_type, $id_shop, $last_call_date, $type_action = 'display')
    {
        $rq_sql_limit = '';
        if ($type_action == 'is_updated') {
            $rq_sql_limit = ' LIMIT 0, 1';
        }
        if ($last_call_date && ($list_type == 'N' || $list_type == 'C')) {
            $rq_sql_where = '`id_shop` = ' . (int) $id_shop;
            if ($list_type == 'C') {
                $id_shop_group = $this->getIdShopGroupIfShopShareCustomer($id_shop);
                if ($id_shop_group) {
                    $rq_sql_where = '`' . $this->getIdShopGroupColumnName() . '` = ' . (int) $id_shop_group;
                }
            }
            $rq_sql = '
            SELECT `email`
            FROM `' . _DB_PREFIX_ . 'sd_updates`
            WHERE ' . $rq_sql_where . '
            AND `list_type` = \'' . pSQL($list_type) .'\'
            AND `action` = \'U\'
            AND `update_date` >= \'' . pSQL($last_call_date) . '\'';
            $rq_sql .= $rq_sql_limit;
            $rq = Db::getInstance()->query($rq_sql);
            $content = '';
            while ($r = Db::getInstance()->nextRow($rq)) {
                if ($type_action == 'is_updated') {
                    return 1;
                }
                $content .= $this->dQuote($r['email']) . ';;';
                if ($list_type == 'C') {
                    $content .= ';';
                    if ($this->checkIfListWithCustomerData('C', $id_shop)) {
                        $content .= ';;;;;;;';
                    }
                }
                $content .= ';U' . "\n";
            }
            if ($type_action == 'is_updated') {
                return 0;
            }
            return $content;
        } else {
            if ($type_action == 'is_updated') {
                return 0;
            }
            return '';
        }
    }

    private function getConfiguration($return = 'nb_configured')
    {
        $sd_configuration_loaded = false;
        if (isset($this->sd_configuration)) {
            if (isset($this->sd_configuration['sd_token']) && isset($this->sd_configuration['sd_list'])
            && isset($this->sd_configuration['sd_is_user']) && isset($this->sd_configuration['sd_last_update'])
            && isset($this->sd_configuration['sd_nb_failed']) && isset($this->sd_configuration['sd_timezone'])) {
                $sd_configuration_loaded = true;
            }
        }
        if ( ! $sd_configuration_loaded) {
            $this->sd_configuration = array(
                'sd_token'       => Configuration::getGlobalValue('SARBACANEDESKTOP_TOKEN'),
                'sd_list'        => Configuration::getGlobalValue('SARBACANEDESKTOP_LIST'),
                'sd_is_user'     => Configuration::getGlobalValue('SARBACANEDESKTOP_IS_USER'),
                'sd_last_update' => Configuration::getGlobalValue('SARBACANEDESKTOP_LAST_UPDATE'),
                'sd_nb_failed'   => (int) Configuration::getGlobalValue('SARBACANEDESKTOP_FAILED'),
                'sd_timezone'    => Configuration::getGlobalValue('SARBACANEDESKTOP_TIMEZONE')
            );
        }
        if ($return == 'sd_token' || $return == 'sd_list' || $return == 'sd_is_user'
        || $return == 'sd_last_update' || $return == 'sd_nb_failed' || $return == 'sd_timezone') {
            return $this->sd_configuration[$return];
        } else {
            if ($return == 'all') {
                return $this->sd_configuration;
            } else {
                $nb_configured = 0;
                if ($this->sd_configuration['sd_token'] != '') {
                    $nb_configured = 3;
                } else {
                    if ($this->sd_configuration['sd_list'] != '') {
                        $nb_configured++;
                    }
                    if ($this->sd_configuration['sd_is_user'] != '') {
                        $nb_configured++;
                    }
                }
                return $nb_configured;
            }
        }
    }

    private function updateConfiguration($name, $value) {
        $bdd_name = '';
        if ( $name == 'sd_token') {
            $bdd_name = 'SARBACANEDESKTOP_TOKEN';
        } else if ( $name == 'sd_list') {
            $bdd_name = 'SARBACANEDESKTOP_LIST';
        } else if ( $name == 'sd_is_user') {
            $bdd_name = 'SARBACANEDESKTOP_IS_USER';
        } else if ( $name == 'sd_last_update') {
            $bdd_name = 'SARBACANEDESKTOP_LAST_UPDATE';
        } else if ( $name == 'sd_nb_failed') {
            $bdd_name = 'SARBACANEDESKTOP_FAILED';
        } else if ( $name == 'sd_timezone') {
            $bdd_name = 'SARBACANEDESKTOP_TIMEZONE';
        }
        if ($bdd_name != '') {
            Configuration::updateGlobalValue($bdd_name, $value);
        }
        unset($this->sd_configuration);
    }

    private function saveSdid($sdid, $list_type, $id_shop, $now)
    {
        $newsletter_module_status = '';
        if ($list_type == 'N') {
            $newsletter_module_status = $this->getNewsletterModuleStatus($id_shop);
        }
        $rq_sql = '
        INSERT INTO `' . _DB_PREFIX_ . 'sd_users` (`sd_id`, `list_id`, `newsletter_module_status`, `last_call_date`)
        VALUES(\'' . pSQL($sdid) . '\', \'' . pSQL($id_shop . $list_type) . '\', \'' . pSQL($newsletter_module_status) . '\', \'' . pSQL($now) . '\')
        ON DUPLICATE KEY UPDATE
        `newsletter_module_status` = VALUES(`newsletter_module_status`),
        `last_call_date` = VALUES(`last_call_date`)';
        Db::getInstance()->execute($rq_sql);
    }

    private function saveSdIsUser()
    {
        if (Tools::getIsset('sd_is_user')) {
            $sd_is_user = Tools::getValue('sd_is_user');
            $this->updateConfiguration('sd_is_user', $sd_is_user);
        }
    }

    private function deleteSdid($sd_id)
    {
        $rq_sql = '
        DELETE
        FROM `' . _DB_PREFIX_ . 'sd_users`
        WHERE `sd_id` = \'' . pSQL($sd_id) . '\'';
        Db::getInstance()->execute($rq_sql);
    }

    private function saveTokenParameterConfiguration()
    {
        $this->updateConfiguration('sd_nb_failed', '');
        $rq_sql = 'TRUNCATE `' . _DB_PREFIX_ . 'sd_updates`';
        Db::getInstance()->execute($rq_sql);
        $rq_sql = 'TRUNCATE `' . _DB_PREFIX_ . 'sd_users`';
        Db::getInstance()->execute($rq_sql);
        $token_parameter = rand(100000, 999999) . time() . '-';
        $characters = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
        $nb_characters = strlen($characters);
        for ($i = 0; $i < 30; $i++) {
            $token_parameter .= Tools::substr($characters, mt_rand(0, $nb_characters - 1), 1);
        }
        $this->updateConfiguration('sd_token', $token_parameter);
    }

    private function getListConfiguration($return = 'string')
    {
        $sd_list = $this->getConfiguration('sd_list');
        if ($return == 'string') {
            return $sd_list;
        } else {
            if (Tools::strlen($sd_list) != 0) {
                return explode(',', $sd_list);
            }
            return array();
        }
    }

    private function getKeyForSynchronisation()
    {
        return str_rot13('modules/sarbacanedesktop/sd.php?stk=' . $this->getToken());
    }

    private function saveListConfiguration()
    {
        $shops = '';
        $ids_shops = array();
        if (Tools::getIsset('id_shop')) {
            $ids_shops = Tools::getValue('id_shop');
        }
        if (is_array($ids_shops)) {
            $sd_list_array = $this->getListConfiguration('array');
            foreach ($sd_list_array as $sd_list) {
                if ( ! in_array($sd_list, $ids_shops)) {
                    $id_shop = $this->getIdShopFromList($sd_list);
                    $list_type = $this->getListTypeFromList($sd_list);
                    $this->deleteListData($list_type, $id_shop);
                }
            }
            $shops = implode(',', $ids_shops);
        }
        $this->updateConfiguration('sd_list', $shops);
    }

    private function clearHistory($list_type, $id_shop)
    {
        $sql_parameters = $this->getDataForDeleteRequest($list_type, $id_shop);
        $rq_sql = '
        DELETE
        FROM `' . _DB_PREFIX_ . 'sd_updates`
        WHERE `id_shop` = ' . (int) $sql_parameters['id_shop'] . '
        AND `list_type` = \'' . pSQL($list_type) . '\'
        AND `id_shop_group` = ' . (int) $sql_parameters['id_shop_group'] . '
        AND `update_date` <= (
            SELECT MIN(`last_call_date`)
            FROM `' . _DB_PREFIX_ . 'sd_users`
            WHERE `list_id` IN (' . $sql_parameters['lists_ids_sql'] . ')
        )';
        Db::getInstance()->execute($rq_sql);
        $rq_sql = '
        DELETE
        FROM `' . _DB_PREFIX_ . 'sd_updates`
        WHERE `update_date` <= (
            SELECT MIN(`last_call_date`)
            FROM `' . _DB_PREFIX_ . 'sd_users`
        )';
        Db::getInstance()->execute($rq_sql);
    }

    private function getDataForDeleteRequest($list_type, $id_shop) {
        $share_customers = false;
        if ($list_type == 'C') {
            $id_shop_group = $this->getIdShopGroupIfShopShareCustomer($id_shop);
            if ($id_shop_group) {
                $ids_shops_shared = $this->getIdShopsIfShopShareCustomer($id_shop);
                if ($ids_shops_shared) {
                    if (is_array($ids_shops_shared)) {
                        if (count($ids_shops_shared) > 0) {
                            $share_customers = true;
                            $id_shop = 0;
                            $lists_ids = array();
                            foreach ($ids_shops_shared as $id_shop_shared) {
                                $lists_ids[] = $id_shop_shared . 'C';
                            }
                        }
                    }
                }
            }
        }
        if ( ! $share_customers) {
            $id_shop_group = 0;
            $lists_ids = array($id_shop . $list_type);
        }
        $lists_ids_sql = array();
        foreach ($lists_ids as $list_id) {
            $lists_ids_sql[] = '\'' . pSQL($list_id) . '\'';
        }
        $lists_ids_sql = implode(', ', $lists_ids_sql);
        return array(
            'id_shop'       => (int) $id_shop,
            'id_shop_group' => (int) $id_shop_group,
            'lists_ids_sql' => $lists_ids_sql
        );
    }

    private function deleteListData($list_type, $id_shop)
    {
        $sql_parameters = $this->getDataForDeleteRequest($list_type, $id_shop);
        $rq_sql = '
        DELETE
        FROM `' . _DB_PREFIX_ . 'sd_updates`
        WHERE `id_shop` = ' . (int) $sql_parameters['id_shop'] . '
        AND `id_shop_group` = ' . (int) $sql_parameters['id_shop_group'] . '
        AND `list_type` = \'' . pSQL($list_type) . '\'';
        Db::getInstance()->execute($rq_sql);
        $rq_sql = '
        DELETE
        FROM `' . _DB_PREFIX_ . 'sd_users`
        WHERE `list_id` IN (' . $sql_parameters['lists_ids_sql'] . ')';
        Db::getInstance()->execute($rq_sql);
    }

    private function getSdFormKey()
    {
        return Tools::substr(Tools::encrypt('SarbacaneDesktopForm'), 0, 15);
    }

    private function getWebsitesUrlsArray()
    {
        $url = Tools::getHttpHost(true) . __PS_BASE_URI__;
        $is_https = strpos($url, 'https://') !== false ? true: false;
        $rq_sql = '
        SELECT `domain`, `domain_ssl`, `physical_uri`, `virtual_uri`
        FROM `' . _DB_PREFIX_ . 'shop_url`
        WHERE `active` = 1
        AND `main` = 1
        ORDER BY `id_shop` ASC';
        $rq = Db::getInstance()->executeS($rq_sql);
        $shops_urls = array();
        $shops_urls[] = $url;
        if (is_array($rq)) {
            foreach ($rq as $key => $r) {
                if ($is_https) {
                    $shop_url = 'https://' . $r['domain_ssl'] . $r['physical_uri'] . $r['virtual_uri'];
                } else {
                    $shop_url = 'http://' . $r['domain'] . $r['physical_uri'] . $r['virtual_uri'];
                }
                $shops_urls[] = $shop_url;
                if ($shop_url == $url) {
                    return array($shop_url);
                }
            }
        }
        return $shops_urls;
    }

    public function getContent()
    {
        $general_configuration = $this->getConfiguration('nb_configured');
        $displayed_step = 1;
        if ($general_configuration == 1) {
            $displayed_step = 2;
        } else if ($general_configuration == 2 || $general_configuration == 3) {
            $displayed_step = 3;
        }
        if (Tools::isSubmit('submit_is_user') || Tools::isSubmit('submit_configuration') || Tools::isSubmit('submit_parameter_key')) {
            if (Tools::getIsset('sd_form_key')) {
                if (Tools::getValue('sd_form_key') == $this->getSdFormKey()) {
                    if (Tools::isSubmit('submit_is_user')) {
                        $this->saveSdIsUser();
                        $general_configuration = $this->getConfiguration('nb_configured');
                        $displayed_step = 2;
                    } else if (Tools::isSubmit('submit_configuration')) {
                        $this->saveListConfiguration();
                        if ($this->getConfiguration('sd_token') == '') {
                            $this->saveTokenParameterConfiguration();
                        }
                        $general_configuration = $this->getConfiguration('nb_configured');
                        $displayed_step = 3;
                    } else if (Tools::isSubmit('submit_parameter_key')) {
                        $this->saveTokenParameterConfiguration();
                    }
                }
            }
        }
        $sd_submit_url = 'index.php?controller=' . Tools::safeOutput(Tools::getValue('controller')) . '&token=' . Tools::safeOutput(Tools::getValue('token'));
        $sd_submit_url .= '&configure=' . $this->name . '&tab_module=' . $this->tab . '&module_name=' . $this->name . '#sd_step';
        $this->context->smarty->assign(array(
            'sd_submit_url'           => $sd_submit_url,
            'sd_form_key'             => $this->getSdFormKey(),
            'key_for_synchronisation' => $this->getKeyForSynchronisation(),
            'check_failed'            => $this->checkFailed(),
            'list_configuration'      => $this->getListConfiguration('array'),
            'general_configuration'   => $general_configuration,
            'sd_is_user'              => $this->getConfiguration('sd_is_user'),
            'displayed_step'          => $displayed_step,
            'shops_array'             => $this->getShopsArray(),
            'websites_urls_array'     => $this->getWebsitesUrlsArray(),
            'css_url'                 => $this->_path . 'views/css/sarbacanedesktop.css?v=1.0.15',
            'js_url'                  => $this->_path . 'views/js/sarbacanedesktop.js?v=1.0.15'
        ));
        return $this->context->smarty->fetch($this->local_path . 'views/templates/admin/sarbacanedesktop.tpl');
    }

}