<?php

class DModelListaEscolarListaEscolar extends Model
{

    public function getEscolas($idEcmEscola = false)
    {
        $filterEmpresa = $this->config->get('futura_id_empresa') ? ' AND pl.company_id_futura = ' . $this->config->get('futura_id_empresa') : '';
        $filterEscola = $idEcmEscola ? ' and s.school_id = ' . $idEcmEscola : '';

        $sql = "SELECT DISTINCT s.id_futura as id, s.school_id as id_ecommerce, s.DESCRICAO as descricao, s.image as logo, s.URL_AMIGAVEL as url_amigavel,
		concat(s.tipo_logradouro ,' ',s.logradouro,', ',s.numero,', ',s.cidade,', ',s.uf) endereco
		FROM oc_school s
		INNER JOIN oc_product_list pl ON pl.school_id = s.school_id
		WHERE EXISTS(
		    SELECT * FROM oc_product_list_item pli
    		INNER JOIN oc_product p ON pli.product_id = p.product_id
    		WHERE pli.product_list_id = pl.product_list_id
		    LIMIT 1
		) AND  pl.status = 0 AND pl.site_utiliza = 'S' " . $filterEmpresa . $filterEscola . "
		ORDER BY s.id_futura, pl.id_futura, pl.school_grade_id";

        $results = $this->db->query($sql);
        return $results->rows;
    }


    public function getEscola($idEscola)
    {
        $sql = "SELECT DISTINCT s.*,
                concat(s.tipo_logradouro ,' ',s.logradouro,', ',s.numero,', ',s.cidade,', ',s.uf) endereco
		        FROM oc_school s
		        WHERE pl.s.id_futura = " . $idEscola;

        $results = $this->db->query($sql);
        return $results->row;
    }

    public function getInfosLista($idLista)
    {
        $sql = "SELECT DISTINCT
                    pl.description lista_escolar,
                    s.descricao escola,
                    s.id_futura id_escola,
                    sg.description serie,
                    pl.school_year ano,
                    s.image logo,
                    pl.observation observacao,
                    pl.customer_group_id id_tabela_preco,
                    concat(s.tipo_logradouro ,' ',s.logradouro,', ',s.numero,', ',s.cidade,', ',s.uf) endereco
		        FROM oc_product_list pl
		        INNER JOIN oc_school s on s.school_id = pl.school_id
		        INNER JOIN oc_school_grade sg on sg.school_grade_id = pl.school_grade_id
		        WHERE pl.id_futura = " . $idLista;

        $results = $this->db->query($sql);
        return $results->row;
    }

    public function getListas($idEscola)
    {
        $filterEmpresa = $this->config->get('futura_id_empresa') ? ' AND pl.company_id_futura = ' . $this->config->get('futura_id_empresa') : '';

        $sql = "SELECT
                    pl.product_list_id id_ecommerce,
                    pl.customer_group_id fk_tabela_preco,
                    pl.school_grade_id fk_serie,
                    pl.company_id_futura fk_empresa,
                    pl.description descricao,
                    pl.observation observacao,
                    pl.status status,
                    pl.school_year ano,
                    pl.type tipo,
                    pl.id_futura id,
                    sg.description serie
		        FROM oc_product_list pl
		        INNER JOIN oc_school s on s.school_id = pl.school_id
		        INNER JOIN oc_school_grade sg on sg.school_grade_id = pl.school_grade_id
		        WHERE EXISTS(
                        SELECT * FROM oc_product_list_item pli
                        INNER JOIN oc_product p ON pli.product_id = p.product_id
                        WHERE pli.product_list_id = pl.product_list_id
                        LIMIT 1
                    )
		            AND pl.status = 0 AND pl.site_utiliza = 'S' AND s.id_futura = " . $idEscola . $filterEmpresa . "
		        ORDER BY pl.school_year DESC, pl.school_grade_id ASC";

        $results = $this->db->query($sql);
        return $results->rows;
    }


    public function getDescontoLista($idProduto, $idListaFutura)
    {
        $sql = "SELECT discount_percentage
                FROM " . DB_PREFIX . "product_list_item li
                INNER JOIN " . DB_PREFIX . "product_list l on li.product_list_id = l.product_list_id
                WHERE product_id = " . (int)$idProduto . " AND l.id_futura = $idListaFutura
                ORDER BY discount_percentage DESC
                LIMIT 1";

        $results = $this->db->query($sql);

        return ($results->num_rows > 0 ? $results->row['discount_percentage'] : 0);
    }

    public function getProdutosLista($idLista)
    {

        switch ($this->config->get('listaescolar_ordenacao')) {
            case Type_OrdenacaoListaEscolar::Tp_Sequencia :
                $ordenacao = 'PLI.sequence';
                break;
            case Type_OrdenacaoListaEscolar::Tp_Descricao :
                $ordenacao = 'PD.description';
                break;
            default:
                $ordenacao = 'PLI.sequence';
        }

        $futuraListas = $this->db->query(
            "SELECT
                P.id_futura id_produto_futura,
                P.product_id id_produto_ecommerce,
                PLI.quantity quantidade,
                PLI.discount_percentage percentual_desconto,
                tp.id_futura id_tabela_preco_futura,
                tp.customer_group_id id_tabela_preco_ecommerce
            FROM oc_product_list_item PLI
            INNER JOIN oc_product_list PL ON PL.product_list_id = PLI.product_list_id
            INNER JOIN oc_product P ON (PLI.product_id = P.product_id AND P.status = " . Type_Status::Tp_Habilitado . ")
            LEFT JOIN oc_product_description PD ON (P.product_id = PD.product_id)
            LEFT  JOIN oc_customer_group tp on tp.customer_group_id = PL.customer_group_id
            WHERE PL.id_futura = " . $idLista . " order by " . $ordenacao
        )->rows;

        if ($futuraListas) {
            $this->load->type('tipocomum');
            $this->load->helper('ftr_geral');

            $data_language = $this->config->get('config_data_language');
            if ((!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) || ($this->ftrgeralregistry->isAdmin())) {
                unset($data_language);
            }

            $data = array();
            foreach ($futuraListas as $key => $productFutura) {
                $quantity = $productFutura['quantidade'];
                $product_id = $productFutura['id_produto_ecommerce'];
                $stock = true;

                if ($this->config->get('futuragrade_status')) {
                    $language = $this->language->load('module/futuragrade');
                    $title_specification = (ftr_geral::isIsset($this->config->get('futuragrade_title_specification')) && $this->config->get('futuragrade_title_specification') != "") ? $this->config->get('futuragrade_title_specification') : $this->language->get('heading_title_specification');
                    $title_size = $this->language->get('heading_title_size');
                    $title_cor = $this->language->get('heading_title_cor');
                    $sql = "SELECT p.*,";
                    $sql .= " pd.*,";
                    $sql .= isset($data_language) ? "coalesce(pt.descricao_ecommerce, pd.name) as name," : "pd.name as name,";
                    $sql .= " (pc.description)  color_description,"
                        . " (ps.description)  size_description,"
                        . " (psp.description) specification_description,"
                        . " (COALESCE(pgt.descricao_cor,'$title_cor')) color_template_description,"
                        . " (COALESCE(pgt.descricao_tamanho,'$title_size')) size_template_description,"
                        . " (COALESCE(pgt.descricao_especificacao,'$title_specification')) specification_template_description"
                        . " FROM " . DB_PREFIX . "product p";
                    $sql .= " LEFT JOIN " . DB_PREFIX . "product_description   pd  ON (p.product_id = pd.product_id)";
                    $sql .= isset($data_language) ? " LEFT JOIN " . DB_PREFIX . "product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
                    $sql .= " LEFT JOIN " . DB_PREFIX . "product_color         pc  ON (pc.productcolor_id = p.product_color_id)"
                        . " LEFT JOIN " . DB_PREFIX . "product_size          ps  ON (ps.productsize_id = p.product_size_id)"
                        . " LEFT JOIN " . DB_PREFIX . "product_specification psp ON (psp.productspecification_id = p.product_specification_id)"
                        . " LEFT JOIN oc_product_grid_detail pgd ON pgd.product_id = p.product_id"
                        . " LEFT JOIN oc_product_grid pg ON pg.product_grid_id = pgd.product_grid_id"
                        . " LEFT JOIN oc_product_grid_template pgt ON pg.product_grid_template_id = pgt.product_grid_template_id"
                        . " WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'";
                    $product_query = $this->db->query($sql);
                } else {
                    $sql = " SELECT *,";
                    $sql .= isset($data_language) ? "coalesce(pt.descricao_ecommerce, pd.name) as name" : "pd.name as name";
                    $sql .= " FROM " . DB_PREFIX . "product p";
                    $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)";
                    $sql .= isset($data_language) ? " LEFT JOIN oc_product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
                    $sql .= " WHERE p.product_id = '" . (int)$product_id . "'";
                    $sql .= " AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
					          AND p.date_available <= NOW() AND p.status = '1'";
                    $product_query = $this->db->query($sql);
                }
                if ($product_query->num_rows) {
                    if (isset($productFutura['id_tabela_preco_ecommerce']) && $productFutura['id_tabela_preco_ecommerce'] > 0) {
                        $customer_group_id = $productFutura['id_tabela_preco_ecommerce'];
                    } else if ($this->customer->isLogged()) {
                        $customer_group_id = $this->customer->getCustomerGroupId();
                    } else {
                        $customer_group_id = $this->config->get('config_customer_group_id');
                    }
                    $price = $product_query->row['price'];

                    $product_discount_query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity <= '" . (int)$quantity . "' AND (quantity <> 0) AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity DESC, priority ASC, price ASC LIMIT 1");
                    if ($product_discount_query->num_rows) {
                        $price = $product_discount_query->row['price'];
                    }

                    $product_special_query = $this->db->query("select coalesce(perc_discount, 0) perc_discount from " . DB_PREFIX . "order_discount  where (product_id = '" . (int)$product_id . "') and (tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") and (status = " . Type_PromocaoStatus::Tp_Ativo . ") and ((date_start = '0000-00-00') or (date_start <= CURDATE())) and ((date_end = '0000-00-00') or (date_end >= CURDATE())) ORDER BY perc_discount DESC LIMIT 1");
                    if (($product_special_query->num_rows) && ($product_special_query->row['perc_discount'] > 0)) {
                        $price = ($price - (($product_special_query->row['perc_discount'] * 0.01) * $price));
                    }


                    if (!$product_query->row['quantity'] || ($product_query->row['quantity'] < $quantity)) {
                        $stock = false;
                    }
                    $type = ftr_geral::isIsset($this->config->get('config_product_code')) ? $this->config->get('config_product_code') : Type_ListCodProduct::Tp_CodReferencia;
                    switch ($type) {
                        case Type_ListCodProduct::Tp_CodReferencia:
                            $model = $product_query->row['model'];
                            break;
                        case Type_ListCodProduct::Tp_CodProdEcommerce:
                            $model = $product_query->row['product_id'];
                            break;
                        case Type_ListCodProduct::Tp_CodProdFuturaServer:
                            $model = $product_query->row['id_futura'];
                            break;
                        case Type_ListCodProduct::Tp_CodBarras:
                            $model = $product_query->row['codigo_barra'];
                            break;
                        default:
                            $model = $product_query->row['model'];
                    }
                    $data[$key] = array(
                        'key' => $key,
                        'product_id' => $product_query->row['product_id'],
                        'id_futura' => $product_query->row['id_futura'],
                        'name' => $product_query->row['name'],
                        'model' => $model,
                        'shipping' => $product_query->row['shipping'],
                        'image' => $product_query->row['image'],
                        'data_atualizacao_replicacao' => $product_query->row['data_atualizacao_replicacao'],
                        'quantity' => $quantity,
                        'fator_multiplicador' => ($this->config->get('config_fator_multiplicador') && ((int)$product_query->row['quantity_unit'] != 0)) ? (int)$product_query->row['quantity_unit'] : 1,
                        'minimum' => $product_query->row['minimum'],
                        'subtract' => $product_query->row['subtract'],
                        'stock' => $stock,
                        'color_description' => ($this->config->get('futuragrade_status')) ? $product_query->row['color_description'] : '',
                        'size_description' => ($this->config->get('futuragrade_status')) ? $product_query->row['size_description'] : '',
                        'specification_description' => ($this->config->get('futuragrade_status')) ? $product_query->row['specification_description'] : '',
                        'color_template_description' => ($this->config->get('futuragrade_status')) ? $product_query->row['color_template_description'] : '',
                        'size_template_description' => ($this->config->get('futuragrade_status')) ? $product_query->row['size_template_description'] : '',
                        'specification_template_description' => ($this->config->get('futuragrade_status')) ? $product_query->row['specification_template_description'] : '',
                        'price' => ($price),
                        'total' => ($price) * $quantity,
                        'weight' => ($product_query->row['weight']) * $quantity,
                        'weight_class_id' => $product_query->row['weight_class_id'],
                        'length' => $product_query->row['length'],
                        'width' => $product_query->row['width'],
                        'height' => $product_query->row['height'],
                        'sku' => $product_query->row['sku'],
                        'tag' => $product_query->row['tag'],
                        'category_id' => $product_query->row['category_id'],
                        'percentual_desconto' => $productFutura['percentual_desconto'],
                        'id_customer_group' => (isset($productFutura['id_tabela_preco_ecommerce'])) ? $productFutura['id_tabela_preco_ecommerce'] : 0
                    );
                }
            }
        }
        return $data;
    }


    public function getProdutosSimilares($category_id, $product_id, $tags = '', $customer_group_id = false)
    {
        if (!$this->config->get('listaescolar_pesquisa_gruposubgrupo') && trim($tags) == '') {
            return array();
        } else {
            $language = $this->language->load('module/futuragrade');
            $title_specification = (ftr_geral::isIsset($this->config->get('futuragrade_title_specification')) && $this->config->get('futuragrade_title_specification') != "") ? $this->config->get('futuragrade_title_specification') : $this->language->get('heading_title_specification');
            $title_size = $this->language->get('heading_title_size');
            $title_cor = $this->language->get('heading_title_cor');

            $data_language = $this->config->get('config_data_language');
            if ((!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) || ($this->ftrgeralregistry->isAdmin())) {
                unset($data_language);
            }

            $sql = "SELECT DISTINCT
		  p.*,
		  pd.*,";
            $sql .= isset($data_language) ? "coalesce(pt.descricao_ecommerce, pd.name) as name," : "pd.name as name,";
            $sql .= $customer_group_id !== false ? "dis.price as price_table," : "";
            $sql .= "(pc.description)  color_description,
		  (ps.description)  size_description,
		  (psp.description) specification_description,
		  (COALESCE(pgt.descricao_cor,'$title_cor')) color_template_description,
		  (COALESCE(pgt.descricao_tamanho,'$title_size')) size_template_description,
		  (COALESCE(pgt.descricao_especificacao,'$title_specification')) specification_template_description
		  FROM " . DB_PREFIX . "product p
		  LEFT JOIN " . DB_PREFIX . "product_description   pd  ON (p.product_id = pd.product_id)";
            $sql .= isset($data_language) ? " LEFT JOIN " . DB_PREFIX . "product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
            $sql .= $customer_group_id !== false ? " LEFT JOIN " . DB_PREFIX . "product_discount dis ON (p.product_id = dis.product_id and dis.customer_group_id = " . $customer_group_id . ")" : "";
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_color         pc  ON (pc.productcolor_id = p.product_color_id)
		  LEFT JOIN " . DB_PREFIX . "product_size          ps  ON (ps.productsize_id = p.product_size_id)
		  LEFT JOIN " . DB_PREFIX . "product_specification psp ON (psp.productspecification_id = p.product_specification_id)
		  LEFT JOIN " . DB_PREFIX . "category p2c ON (p.category_id = p2c.category_id)
		  LEFT JOIN oc_product_grid_detail pgd ON pgd.product_id = p.product_id
		  LEFT JOIN oc_product_grid pg ON pg.product_grid_id = pgd.product_grid_id
		  LEFT JOIN oc_product_grid_template pgt ON pg.product_grid_template_id = pgt.product_grid_template_id
		  WHERE p.quantity > 0 AND p.status = '1' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'  AND p.product_id <> '" . (int)$product_id . "' ";

            if ($this->config->get('listaescolar_pesquisa_gruposubgrupo')) {
                $sql .= " AND p2c.category_id = '" . (int)$category_id . "'";
            }

            if ($this->config->get('listaescolar_pesquisa_tag') && trim($tags) != '') {
                $tags = strtoupper(str_replace(array(' ,', ', ', ','), '|', $tags));
                $sql .= " AND upper(pd.tag) REGEXP '" . $tags . "'";
            }

            $sql .= " ORDER BY pd.name ASC LIMIT 20";

            $query = $this->db->query($sql);

            return $query->rows;
        }
    }
}