<?php
/**
 * 2007-2022 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-2022 ETS-Soft
 * @license    Valid for 1 website (or project) for each purchase of license
 *  International Registered Trademark & Property of ETS-Soft
 */

class HDDataProvider
{
    public static function findProducts($query, $excludeIds, $excludePackItself = false, $excludeVirtuals = 0, $exclude_packs = 0, Context $context = null)
    {
        if ($excludeIds && !Validate::isArrayWithIds($excludeIds)) {
            return false;
        }
        if ($context == null) {
            $context = Context::getContext();
        }
        if (!$query or $query == '' or Tools::strlen($query) < 1) {
            die();
        }
        if ($pos = strpos($query, ' (ref:')) {
            $query = Tools::substr($query, 0, $pos);
        }
        $sql = '
            SELECT p.`id_product`, pl.`link_rewrite`, p.`reference`, pl.`name`, image_shop.`id_image` id_image, il.`legend`, p.`cache_default_attribute`
            FROM `' . _DB_PREFIX_ . 'product` p
            ' . Shop::addSqlAssociation('product', 'p') . '
            LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.id_product = p.id_product AND pl.id_lang = ' . (int)$context->language->id . Shop::addSqlRestrictionOnLang('pl') . ')
            LEFT JOIN `' . _DB_PREFIX_ . 'image` `i` ON (i.`id_product` = p.`id_product`)
            LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int)$context->language->id . ')
            LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.cover=1 AND image_shop.id_shop=' . (int)$context->shop->id . ')
            WHERE 1 AND (pl.name LIKE \'%' . pSQL($query) . '%\' OR p.reference LIKE \'%' . pSQL($query) . '%\')' .
            (!empty($excludeIds) ? ' AND p.id_product NOT IN (' . $excludeIds . ') ' : ' ') .
            (!empty($excludePackItself) ? ' AND p.id_product <> ' . $excludePackItself . ' ' : ' ') .
            ($excludeVirtuals ? 'AND NOT EXISTS (SELECT 1 FROM `' . _DB_PREFIX_ . 'product_download` pd WHERE (pd.id_product = p.id_product))' : '') .
            ($exclude_packs ? 'AND (p.cache_is_pack IS NULL OR p.cache_is_pack = 0)' : '') .
            ' GROUP BY p.id_product, image_shop.`id_image`
        ';
        return Db::getInstance()->executeS($sql);
    }

    public static function getOrders($id_customer, $order_ref = null, $nb = false, $p = 0, $n = 0, $sort_by = null, $context = null)
    {
        if (!$id_customer || !Validate::isUnsignedInt($id_customer) || $order_ref && !Validate::isReference($order_ref)) {
            return false;
        }
        if ($context === null) {
            $context = Context::getContext();
        }
        $dq = new DbQuery();
        $dq
            ->from('orders', 'a')
            ->leftJoin('customer', 'c', 'c.id_customer = a.id_customer')
            ->leftJoin('order_detail', 'od', 'od.id_order = a.id_order')
            ->leftJoin('product', 'p', 'p.id_product = od.product_id')
            ->leftJoin('product_lang', 'pl', 'p.id_product = pl.id_product AND pl.id_lang=' . (int)$context->language->id)
            ->leftJoin('order_state', 'os', 'os.id_order_state=a.current_state')
            ->leftJoin('order_state_lang', 'osl', 'osl.id_order_state=os.id_order_state AND osl.id_lang=' . (int)$context->language->id)
            ->where('c.id_customer = '.(int)$id_customer);
        if ($order_ref) {
            $dq
                ->where('a.reference=\'' . pSQL($order_ref) . '\'');
        }
        if ($nb) {
            $dq
                ->select('COUNT(a.id_order)');
            return (int)Db::getInstance()->getValue($dq);
        } else
            $dq
                ->select('a.*, pl.name `product`, osl.name `order_status`, os.color `order_status_color`, od.*');
        if (self::tableExist('ets_sl_order_product')) {
            $dq
                ->select('op.shop_name
                    , a.date_add `support_status`
                    , op.license_id,
                    op.date_expried
                ')
                ->leftJoin('ets_sl_order_product', 'op', 'op.id_order = a.id_order AND op.id_product=od.product_id');
            $dq->where('p.state=1');
        }
        if (self::tableExist('ets_czf_product')) {
            $dq
                ->select('czp.`logo` as `product_logo`')
                ->leftJoin('ets_czf_product', 'czp', 'czp.id_product = p.id_product');
        }
        if (null == $sort_by) {
            $sort_by = 'a.date_add DESC';
        }
        if (trim($sort_by) !== '') {
            $dq->orderBy($sort_by);
        }
        if ($p > 0 && $n > 0) {
            $dq->limit($n, $n * ($p - 1));
        }

        return Db::getInstance()->executeS($dq);
    }

    public static function customerVerified($id_customer)
    {
        if (!$id_customer || !Validate::isUnsignedInt($id_customer)) {
            return false;
        }

        if (self::tableExist('ets_fd_verification')) {
            $dq = new DbQuery();
            $dq
                ->select('id_customer')
                ->from('ets_fd_verification')
                ->where('id_customer=' . (int)$id_customer)
                ->where('is_verified = 1');
            return Db::getInstance()->getValue($dq);
        }

        return false;
    }

    public static function tableExist($table)
    {
        return Db::getInstance()->executeS('SHOW TABLES LIKE \'' . _DB_PREFIX_ . bqSQL($table) . '\'');
    }
}