<?php
/**
 * 2007-2021 ETS-Soft
 *
 * NOTICE OF LICENSE
 *
 * This file is not open source! Each license that you purchased is only available for 1 wesite only.
 * If you want to use this file on more websites (or projects), you need to purchase additional licenses.
 * You are not allowed to redistribute, resell, lease, license, sub-license or offer our resources to any third party.
 *
 * 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 contact us for extra customization service at an affordable price
 *
 * @author ETS-Soft <etssoft.jsc@gmail.com>
 * @copyright  2007-2021 ETS-Soft
 * @license    Valid for 1 website (or project) for each purchase of license
 *  International Registered Trademark & Property of ETS-Soft
 */

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

class AdminContactFormUltimateDashboardController extends AdminModuleAdapterController
{
    public $_html;
    public $filters = array();
    public function __construct()
    {
        $this->bootstrap = true;
        parent::__construct();
        $this->filters = array(
            'mon' => array(
                'label' => $this->l('This month'),
                'id' => 'month',
            ),
            'yea' => array(
                'label' => $this->l('This year'),
                'id' => 'year',
            ),
            'all' => array(
                'label' => $this->l('All time'),
                'id' => 'all',
            ),
        );
        $this->charts = array(
            'mes' => array(
                'label' => $this->l('Messages'),
            ),
            'vie' => array(
                'label' => $this->l('Views'),
            ),
            'rep' => array(
                'label' => $this->l('Replies'),
            ),
            'use' => array(
                'label' => $this->l('Users'),
            ),
        );
    }
    public function initContent()
    {
        parent::initContent();
        $this->getCharts(Tools::getValue('ajax'));
    }
    public function renderList()
    {
        $assigns = $this->getCharts();
        $contacts = Db::getInstance()->executeS('
            SELECT * FROM ' . _DB_PREFIX_ . 'ets_cfu_contact c, ' . _DB_PREFIX_ . 'ets_cfu_contact_lang cl 
            WHERE c.id_contact=cl.id_contact AND cl.id_lang=' . (int)$this->context->language->id
        );
        $sql = "SELECT l.* FROM " . _DB_PREFIX_ . "ets_cfu_log l 
            INNER JOIN " . _DB_PREFIX_ . "ets_cfu_contact c ON (l.id_contact=c.id_contact)
            LEFT JOIN " . _DB_PREFIX_ . "ets_cfu_contact_lang cl ON (c.id_contact=cl.id_contact AND cl.id_lang='" . (int)$this->context->language->id . "')
            LEFT JOIN " . _DB_PREFIX_ . "customer cu ON (l.id_customer=cu.id_customer)
            GROUP BY l.ip, l.id_contact, l.datetime_added 
            ORDER BY l.datetime_added DESC LIMIT 0, 6";
        if (($logs = Db::getInstance()->executeS($sql))) {
            $black_list = explode("\n", Configuration::get('ETS_CFU_IP_BLACK_LIST'));
            foreach ($logs as &$log) {
                if (in_array($log['ip'], $black_list))
                    $log['black_list'] = true;
                else
                    $log['black_list'] = false;
                $browser = explode(' ', $log['browser']);
                if (isset($browser[0]))
                    $log['class'] = Tools::strtolower($browser[0]);
                else
                    $log['class'] = 'default';
            }
        }
        /*new*/
        $assigns = array_merge($assigns, array(
            'ets_cfu_month' => (string)Tools::getValue('ets_cfu_months', date('m')),
            'action' => $this->context->link->getAdminLink('AdminContactFormUltimateDashboard'),
            'ets_cfu_contacts' => $contacts,
            'ets_cfu_year' => Tools::getValue('years', date('Y')),
            'ets_cfu_cfu_contact' => (int)Tools::getValue('id_contact'),
            'ets_cfu_js_dir_path' => $this->module->getPathUri() . 'views/js/',
            'ets_cfu_img_dir_path' => $this->module->getPathUri() . 'views/img/',
            'ets_cfu_logs' => $logs,
            'cfu_tab_ets' => Tools::getValue('cfu_tab_ets', 'chart'),
            'ets_cfu_show_reset' => Tools::isSubmit('etsCfuSubmitFilterChart'),
            'ets_cfu_link' => $this->context->link,
            'ets_cfu_total_message' => $this->module->getCountUnreadMessage(),
            'ets_cfu_stats' => $this->getDashboardStats(),
            'filters' => $this->filters
        ));
        $this->context->smarty->assign($assigns);
        $this->_html .= $this->module->display($this->module->getLocalPath(), 'dashboard.tpl');
        return $this->_html;
    }
    public function getDashboardStats()
    {
        $stats = array();
        //1.Messages received
        $sql = 'SELECT COUNT(%1$s) FROM ' . _DB_PREFIX_ . 'ets_cfu_contact%3$s b
            LEFT JOIN ' . _DB_PREFIX_ . 'ets_cfu_contact%2$s_shop a ON (a.id_contact%2$s = b.id_contact%2$s)
            WHERE a.id_contact%2$s is NOT NULL AND a.id_shop = ' . (int)$this->context->shop->id;

        $sql_forms = sprintf($sql, '*', '', '');
        $total_forms = (int)Db::getInstance()->getValue($sql_forms);

        $query_msg = sprintf($sql, '*', '_message', '_message');
        $total_msg = (int)Db::getInstance()->getValue($query_msg);

        $query_forms = sprintf($sql, 'DISTINCT a.id_contact', '', '_message');
        $msg_in_forms = (int)Db::getInstance()->getValue($query_forms);

        $stats[] = array(
            'label' => $this->l('Messages received'),
            'color' => 'blue',
            'icon' => 'sign-in',
            'value1' => $total_msg,
            'value2' => sprintf(($total_forms > 1? $this->l('From %s forms') : $this->l('From %s form')), $msg_in_forms),
            'percent' => Tools::ps_round(($total_forms > 0 ? $msg_in_forms / $total_forms : 0) * 100, 0),
            'link' => $this->context->link->getAdminLink('AdminContactFormUltimateMessage', true),
        );
        //2.Replied messages
        $query_reps = '
            SELECT COUNT(DISTINCT m.id_contact_message)
            FROM ' . _DB_PREFIX_ . 'ets_cfu_contact_message m
            INNER JOIN ' . _DB_PREFIX_ . 'ets_cfu_contact_message_shop ms ON (ms.id_contact_message = m.id_contact_message AND ms.id_shop = 1)
            LEFT JOIN ' . _DB_PREFIX_ . 'ets_cfu_message_reply r ON (r.id_contact_message = m.id_contact_message)
            WHERE r.id_ets_cfu_message_reply is NOT NULL AND r.id_ets_cfu_message_reply != 0
        ';
        $total_reps = (int)Db::getInstance()->getValue($query_reps);
        $stats[] = array(
            'label' => $this->l('Replied messages'),
            'color' => 'green',
            'icon' => 'share',
            'value1' => $total_reps,
            'value2' => Tools::ps_round(($percent = ($total_msg > 0 ? $total_reps / $total_msg : 0) * 100), 0) . '%',
            'percent' => $percent,
            'link' => $this->context->link->getAdminLink('AdminContactFormUltimateMessage', true),
        );
        //3.Unread messages
        $query_reads = $query_msg . ' AND b.readed <= 0';
        $total_reads = (int)Db::getInstance()->getValue($query_reads);
        $stats[] = array(
            'label' => $this->l('Unread messages'),
            'color' => 'brown',
            'icon' => 'envelope',
            'value1' => $total_reads,
            'value2' => Tools::ps_round(($percent = ($total_msg > 0 ? $total_reads / $total_msg : 0) * 100), 0) . '%',
            'percent' => $percent,
            'link' => $this->context->link->getAdminLink('AdminContactFormUltimateMessage', true),
        );
        //4.Users contacted
        $contact_is_reg = (int)Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT m.id_customer) FROM ' . _DB_PREFIX_ . 'ets_cfu_contact_message m
            INNER JOIN ' . _DB_PREFIX_ . 'ets_cfu_contact_message_shop ms ON (m.id_contact_message = ms.id_contact_message)
            WHERE id_customer is NOT NULL AND id_customer != 0 
            GROUP BY id_customer
        ');
        $total_contacts = (int)Db::getInstance()->getValue('
            SELECT COUNT(m.id_customer) FROM (
              SELECT * FROM ' . _DB_PREFIX_ . 'ets_cfu_contact_message WHERE id_customer is NOT NULL AND id_customer != 0 GROUP BY id_customer
              UNION
              SELECT * FROM ' . _DB_PREFIX_ . 'ets_cfu_contact_message WHERE id_customer is NULL OR id_customer = 0) as `m` 
            INNER JOIN ' . _DB_PREFIX_ . 'ets_cfu_contact_message_shop ms ON (m.id_contact_message = ms.id_contact_message)
            WHERE ms.id_shop = ' . (int)$this->context->shop->id . '
        ');
        $stats[] = array(
            'label' => $this->l('Users contacted'),
            'color' => 'yellow',
            'icon' => 'users',
            'value1' => $total_contacts,
            'value2' => sprintf($this->l('%s registered'), $contact_is_reg),
            'percent' => Tools::ps_round(($total_contacts > 0 ? $contact_is_reg / $total_contacts : 0) * 100, 0),
            'link' => $this->context->link->getAdminLink('AdminCustomers', true),
        );
        //5.
        $sql_form_enabled = $sql_forms . ' AND b.active > 0';
        $total_form_enabled = (int)Db::getInstance()->getValue($sql_form_enabled);
        $stats[] = array(
            'label' => $this->l('Contact forms'),
            'color' => 'pink',
            'icon' => 'th-large',
            'value1' => $total_forms,
            'value2' => sprintf($this->l('%s enabled'), ($total_form_enabled != $total_forms ? $total_form_enabled : $this->l('All'))),
            'percent' => Tools::ps_round(($total_forms > 0 ? $total_form_enabled / $total_forms : 0) * 100, 0),
            'link' => $this->context->link->getAdminLink('AdminContactFormUltimateContactForm', true),
        );
        return $stats;
    }
}