<?php

class ModelSaleCustomer extends Model
{
    public function addCustomer($data)
    {

        $this->load->helper('Ftr_sql');

        if ((!isset($data['data_nascimento'])) || ($data['data_nascimento'] == "")) {
            $dataparts[0] = "00";
            $dataparts[1] = "00";
            $dataparts[2] = "0000";
        } else {
            $dataparts = explode("/", $data['data_nascimento']);
        }

        $data['firstname'] = ucwords(html_entity_decode($data['firstname'], ENT_QUOTES, 'UTF-8'));
        $data['lastname'] = ucwords(html_entity_decode($data['lastname'], ENT_QUOTES, 'UTF-8'));
        $data['email'] = strtr(strtolower($data['email']), "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞß", "àáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ");
        $data['telephone'] = isset($data['telephone']) ? preg_replace('/([^0-9])/i', '', $data['telephone']) : '';
        $data['celular'] = isset($data['celular']) ? preg_replace('/([^0-9])/i', '', $data['celular']) : '';
        $data['fax'] = isset($data['fax']) ? preg_replace('/([^0-9])/i', '', $data['fax']) : '';
        $data['cpf'] = isset($data['cpf']) ? preg_replace('/([^0-9])/i', '', $data['cpf']) : '';
        $data['rg'] = isset($data['rg']) ? preg_replace('/([^0-9])/i', '', $data['rg']) : '';
        $data['razao_social'] = isset($data['razao_social']) ? ucwords(html_entity_decode($data['razao_social'], ENT_QUOTES, 'UTF-8')) : '';

        if (isset($data['cnpj'])) {
            $data['cnpj'] = preg_replace('/([^0-9])/i', '', $data['cnpj']);
        } else {
            $data['cnpj'] = '';
        }

        if (isset($data['perfil'])) {
            $data['perfil'] = $data['perfil'];
        } else {
            $data['perfil'] = '';
        }

        if(isset($data['cnpj']) && trim($data['cnpj']) != ''){
            if (isset($data['contribuinte'])) {
                $data['contribuinte'] = $data['contribuinte'];
            } else {
                $data['contribuinte'] = '';
            }
        } else {
            $data['contribuinte'] = Type_PjContribuoIcms::Tp_NaoContribuinte;
        }

        //se nao tiver a informacao pessoa ele preenche de acordo com o CPF ou CNPJ preenchido
        if(isset($data['pessoa'])){
            if(strtoupper($data['pessoa']) == Type_CadastroPessoa::Tp_Fisico){
                $data['customer_type'] = Type_CadastroPessoa::Tp_Fisico;
            } else {
                $data['customer_type'] = Type_CadastroPessoa::Tp_Juridico;
            }
        } else {
            if(isset($data['cpf']) && trim($data['cpf']) != ''){
                $data['customer_type'] = Type_CadastroPessoa::Tp_Fisico;
            } else {
                $data['customer_type'] = Type_CadastroPessoa::Tp_Juridico;
            }
        }

        if (isset($data['inscricao_estadual'])) {
            $data['inscricao_estadual'] = $data['inscricao_estadual'];
        } else {
            $data['inscricao_estadual'] = '';
        }

        if (isset($data['newsletter'])) {
            $data['newsletter'] = $data['newsletter'];
        } else {
            $data['newsletter'] = 0;
        }

        if (isset($data['fax'])) {
            $data['fax'] = preg_replace('/([^0-9])/i', '', $data['fax']);
        } else {
            $data['fax'] = '';
        }

        if (!isset($data['sexo'])) {
            $data['sexo'] = '';
        }


        $customer_group_id = $data['customer_group_id'];

        if (isset($data['cpf']) && ($data['cpf'] != '') && ftr_geral::isIsset($this->config->get('config_customer_group_id_cpf')) && $this->config->get('config_customer_group_id_cpf') != Type_DefaultValues::Tp_Nenhum) {
            $customer_group_id = $this->config->get('config_customer_group_id_cpf');
        }

        if (isset($data['cnpj']) && ($data['cnpj'] != '') && ftr_geral::isIsset($this->config->get('config_customer_group_id_cnpj')) && $this->config->get('config_customer_group_id_cnpj') != Type_DefaultValues::Tp_Nenhum) {
            $customer_group_id = $this->config->get('config_customer_group_id_cnpj');
        }

        $this->load->dmodel('sale/customer_group');
        $group = $this->dmodel_sale_customer_group->getCustomerGroup($customer_group_id);

        $this->db->query("INSERT INTO " . DB_PREFIX . "customer SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', celular = '" . $this->db->escape($data['celular']) . "', fax = '" . $this->db->escape($data['fax']) . "', newsletter = '" . (int)$data['newsletter'] . "', customer_group_id = '" . (int)$data['customer_group_id'] . "', salt = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "', status = '" . (int)$data['status'] . "', date_added = NOW(), cpf = '" . $this->db->escape($data['cpf']) . "',rg = '" . $this->db->escape($data['rg']) . "',cnpj = '" . $this->db->escape($data['cnpj']) . "', perfil = '" . $this->db->escape($data['perfil']) . "' , contribuinte = '" . $this->db->escape($data['contribuinte']) . "' ,customer_type = '" . $this->db->escape($data['customer_type']) . "' ,razao_social = '" . $this->db->escape($data['razao_social']) . "',inscricao_estadual = '" . $this->db->escape($data['inscricao_estadual']) . "',data_nascimento = '" . $this->db->escape($dataparts[2] . "-" . $dataparts[1] . "-" . $dataparts[0]) . "',sexo = '" . $this->db->escape($data['sexo']) . "', approved = '" . (int)!$group['approval'] . "', token='' ");

        $customer_id = $this->db->getLastId();

        if (isset($data['address'])) {
            foreach ($data['address'] as $address) {
                $dataAdress = array();
                $dataAdress[] = (int)$customer_id;
                $dataAdress[] = (isset($address['firstname']) && $address['firstname'] != '') ? ucwords($address['firstname']) : '';
                $dataAdress[] = (isset($address['lastname']) && $address['lastname'] != '') ? ucwords($address['lastname']) : '';
                $dataAdress[] = '';
                $dataAdress[] = '';
                $dataAdress[] = (isset($address['address_1']) && $address['address_1'] != '') ? ucwords($address['address_1']) : '';
                $dataAdress[] = (isset($address['address_2']) && $address['address_2'] != '') ? ucwords($address['address_2']) : '';
                $dataAdress[] = (isset($address['postcode']) && $address['postcode'] != '') ? preg_replace('/([^0-9])/i', '', $address['postcode']) : '';
                $dataAdress[] = (isset($address['city']) && $address['city'] != '') ? ucwords($address['city']) : '';
                $dataAdress[] = (isset($address['zone_id']) && $address['zone_id'] != '') ? $address['zone_id'] : '';
                $dataAdress[] = (isset($address['country_id']) && $address['country_id'] != '') ? $address['country_id'] : Type_PaisId::Tp_Brasil;
                $dataAdress[] = (isset($address['apelido']) && $address['apelido'] != '') ? ucwords($address['apelido']) : substr(ucwords($address['address_2']), 0, 80);
                $dataAdress[] = preg_replace('/([^0-9])/i', '', $address['numero']);
                $dataAdress[] = (isset($address['complemento']) && $address['complemento'] != '') ? ucwords($address['complemento']) : '';
                $dataAdress[] = (isset($address['tipo_logradouro']) && $address['tipo_logradouro'] != '') ? ucwords($address['tipo_logradouro']) : '';
                $dataAdress[] = (isset($address['logradouro']) && $address['logradouro'] != '') ? ucwords($address['logradouro']) : ucwords($address['address_1']);
                $dataAdress[] = (isset($address['codigo_municipio']) && $address['codigo_municipio'] != '') ? $address['codigo_municipio'] : NULL;

                $sqlInsert = Ftr_sql::generateInsert('oc_address',
                    ['customer_id', 'firstname', 'lastname', 'company', 'company_id', 'address_1', 'address_2',
                        'postcode', 'city', 'zone_id', 'country_id', 'apelido', 'numero', 'complemento', 'tipo_logradouro',
                        'logradouro', 'codigo_municipio']);

                $this->db->executeSql($sqlInsert, $dataAdress);

                if (isset($address['default'])) {
                    $address_id = $this->db->getLastId();

                    $this->db->query("UPDATE " . DB_PREFIX . "customer SET address_id = '" . $address_id . "' WHERE customer_id = '" . (int)$customer_id . "'");
                }
            }
        }
    }

    public function editCustomer($customer_id, $data)
    {

        $this->load->helper('Ftr_sql');

        if ((!isset($data['data_nascimento'])) || ($data['data_nascimento'] == "")) {
            $dataparts[0] = "00";
            $dataparts[1] = "00";
            $dataparts[2] = "0000";
        } else {
            $dataparts = explode("/", $data['data_nascimento']);
        }

        $data['firstname'] = ucwords(html_entity_decode($data['firstname'], ENT_QUOTES, 'UTF-8'));
        $data['lastname'] = ucwords(html_entity_decode($data['lastname'], ENT_QUOTES, 'UTF-8'));
        $data['email'] = strtr(strtolower($data['email']), "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞß", "àáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ");
        $data['telephone'] = isset($data['telephone']) ? preg_replace('/([^0-9])/i', '', $data['telephone']) : '';
        $data['celular'] = isset($data['celular']) ? preg_replace('/([^0-9])/i', '', $data['celular']) : '';
        $data['fax'] = isset($data['fax']) ? preg_replace('/([^0-9])/i', '', $data['fax']) : '';
        $data['cpf'] = isset($data['cpf']) ? preg_replace('/([^0-9])/i', '', $data['cpf']) : '';
        $data['rg'] = isset($data['rg']) ? preg_replace('/([^0-9])/i', '', $data['rg']) : '';
        $data['company'] = '';//variavel removida verificar se irá excluir o campo da base - by Dionis Mendanha
        $data['razao_social'] = isset($data['razao_social']) ? ucwords(html_entity_decode($data['razao_social'], ENT_QUOTES, 'UTF-8')) : '';

        if (isset($data['cnpj'])) {
            $data['cnpj'] = preg_replace('/([^0-9])/i', '', $data['cnpj']);
        } else {
            $data['cnpj'] = '';
        }

        if (isset($data['perfil'])) {
            $data['perfil'] = $data['perfil'];
        } else {
            $data['perfil'] = '';
        }

        if(isset($data['cnpj']) && trim($data['cnpj']) != ''){
            if (isset($data['contribuinte'])) {
                $data['contribuinte'] = $data['contribuinte'];
            } else {
                $data['contribuinte'] = '';
            }
        } else {
            $data['contribuinte'] = Type_PjContribuoIcms::Tp_NaoContribuinte;
        }

        //se nao tiver a informacao pessoa ele preenche de acordo com o CPF ou CNPJ preenchido
        if(isset($data['pessoa'])){
            if(strtoupper($data['pessoa']) == Type_CadastroPessoa::Tp_Fisico){
                $data['customer_type'] = Type_CadastroPessoa::Tp_Fisico;
            } else {
                $data['customer_type'] = Type_CadastroPessoa::Tp_Juridico;
            }
        } else {
            if(isset($data['cpf']) && trim($data['cpf']) != ''){
                $data['customer_type'] = Type_CadastroPessoa::Tp_Fisico;
            } else {
                $data['customer_type'] = Type_CadastroPessoa::Tp_Juridico;
            }
        }

        if (isset($data['inscricao_estadual'])) {
            $data['inscricao_estadual'] = $data['inscricao_estadual'];
        } else {
            $data['inscricao_estadual'] = '';
        }

        if (isset($data['newsletter'])) {
            $data['newsletter'] = $data['newsletter'];
        } else {
            $data['newsletter'] = 0;
        }

        if (isset($data['fax'])) {
            $data['fax'] = preg_replace('/([^0-9])/i', '', $data['fax']);
        } else {
            $data['fax'] = '';
        }

        if (!isset($data['sexo'])) {
            $data['sexo'] = '';
        }


        $this->db->query("UPDATE " . DB_PREFIX . "customer SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', celular = '" . $this->db->escape($data['celular']) . "',cpf = '" . $this->db->escape($data['cpf']) . "',rg = '" . $this->db->escape($data['rg']) . "',cnpj = '" . $this->db->escape($data['cnpj']) . "', perfil = '" . $this->db->escape($data['perfil']) . "' ,contribuinte = '" . $this->db->escape($data['contribuinte']) . "' ,customer_type = '" . $this->db->escape($data['customer_type']) . "' ,razao_social = '" . $this->db->escape($data['razao_social']) . "',inscricao_estadual = '" . $this->db->escape($data['inscricao_estadual']) . "',data_nascimento = '" . $this->db->escape($dataparts[2] . "-" . $dataparts[1] . "-" . $dataparts[0]) . "',sexo = '" . $this->db->escape($data['sexo']) . "', fax = '" . $this->db->escape($data['fax']) . "', newsletter = '" . (int)$data['newsletter'] . "', customer_group_id = '" . (int)$data['customer_group_id'] . "', status = '" . (int)$data['status'] . "' WHERE customer_id = '" . (int)$customer_id . "'");


        if ($data['password']) {
            $this->db->query("UPDATE " . DB_PREFIX . "customer SET salt = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "' WHERE customer_id = '" . (int)$customer_id . "'");
        }

        $this->db->query("DELETE FROM " . DB_PREFIX . "address WHERE customer_id = '" . (int)$customer_id . "'");

        if (isset($data['address'])) {
            foreach ($data['address'] as $address) {
                $dataAdress = array();
                $dataAdress[] = (int)$customer_id;
                $dataAdress[] = (isset($address['firstname']) && $address['firstname'] != '') ? ucwords($address['firstname']) : '';
                $dataAdress[] = (isset($address['lastname']) && $address['lastname'] != '') ? ucwords($address['lastname']) : '';
                $dataAdress[] = '';
                $dataAdress[] = '';
                $dataAdress[] = (isset($address['address_1']) && $address['address_1'] != '') ? ucwords($address['address_1']) : '';
                $dataAdress[] = (isset($address['address_2']) && $address['address_2'] != '') ? ucwords($address['address_2']) : '';
                $dataAdress[] = (isset($address['postcode']) && $address['postcode'] != '') ? preg_replace('/([^0-9])/i', '', $address['postcode']) : '';
                $dataAdress[] = (isset($address['city']) && $address['city'] != '') ? ucwords($address['city']) : '';
                $dataAdress[] = (isset($address['zone_id']) && $address['zone_id'] != '') ? $address['zone_id'] : '';
                $dataAdress[] = (isset($address['country_id']) && $address['country_id'] != '') ? $address['country_id'] : Type_PaisId::Tp_Brasil;
                $dataAdress[] = (isset($address['apelido']) && $address['apelido'] != '') ? ucwords($address['apelido']) : '';
                $dataAdress[] = preg_replace('/([^0-9])/i', '', $address['numero']);
                $dataAdress[] = (isset($address['complemento']) && $address['complemento'] != '') ? ucwords($address['complemento']) : '';
                $dataAdress[] = (isset($address['tipo_logradouro']) && $address['tipo_logradouro'] != '') ? ucwords($address['tipo_logradouro']) : '';
                $dataAdress[] = (isset($address['logradouro']) && $address['logradouro'] != '') ? ucwords($address['logradouro']) : ucwords($address['address_1']);
                $dataAdress[] = (isset($address['codigo_municipio']) && $address['codigo_municipio'] != '') ? $address['codigo_municipio'] : NULL;

                $sqlInsert = Ftr_sql::generateInsert('oc_address',
                    ['customer_id', 'firstname', 'lastname', 'company', 'company_id', 'address_1', 'address_2',
                        'postcode', 'city', 'zone_id', 'country_id', 'apelido', 'numero', 'complemento', 'tipo_logradouro',
                        'logradouro', 'codigo_municipio']);

                $this->db->executeSql($sqlInsert, $dataAdress);


                if (isset($address['default'])) {
                    $address_id = $this->db->getLastId();

                    $this->db->query("UPDATE " . DB_PREFIX . "customer SET address_id = '" . (int)$address_id . "' WHERE customer_id = '" . (int)$customer_id . "'");
                }
            }
        }
    }

    public function editToken($customer_id, $token)
    {
        $this->db->query("UPDATE " . DB_PREFIX . "customer SET token = '" . $this->db->escape($token) . "' WHERE customer_id = '" . (int)$customer_id . "'");
    }

    public function deleteCustomer($customer_id)
    {
        $this->db->query("DELETE FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$customer_id . "'");
        $this->db->query("DELETE FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . (int)$customer_id . "'");
        $this->db->query("DELETE FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . (int)$customer_id . "'");
        $this->db->query("DELETE FROM " . DB_PREFIX . "customer_ip WHERE customer_id = '" . (int)$customer_id . "'");
        $this->db->query("DELETE FROM " . DB_PREFIX . "address WHERE customer_id = '" . (int)$customer_id . "'");
    }

    public function getCustomer($customer_id)
    {
        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->row;
    }

    public function getCustomerByEmail($email)
    {
        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer WHERE LCASE(email) = '" . $this->db->escape(utf8_strtolower($email)) . "'");

        return $query->row;
    }

    public function getCustomers($data = array())
    {

        $this->load->type('tipocomum');

        $sql = "SELECT *, TRIM(CONCAT(TRIM(c.firstname), ' ', TRIM(c.lastname))) AS name, cgd.name AS customer_group
                FROM " . DB_PREFIX . "customer c
                LEFT JOIN " . DB_PREFIX . "customer_group_description cgd ON (c.customer_group_id = cgd.customer_group_id) ";

        if (!empty($data['filter_uf'])) {
            $sql .= " INNER JOIN " . DB_PREFIX . "address a ON (c.address_id = a.address_id) ";
        }

        $sql .= " WHERE cgd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

        $implode = array();

        if (!empty($data['filter_codl'])) {
            $implode[] = "customer_id = '" . (int)$data['filter_codl'] . "'";
        }

        if (!empty($data['filter_codf'])) {
            $implode[] = "id_futura = '" . (int)$data['filter_codf'] . "'";
        }

        if (!empty($data['filter_name'])) {
            $implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
        }

        if (!empty($data['filter_email'])) {
            $implode[] = "c.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
        }

        if (isset($data['filter_newsletter']) && !is_null($data['filter_newsletter'])) {
            $implode[] = "c.newsletter = '" . (int)$data['filter_newsletter'] . "'";
        }

        if (!empty($data['filter_customer_group_id'])) {
            $implode[] = "c.customer_group_id = '" . (int)$data['filter_customer_group_id'] . "'";
        }

        if (!empty($data['filter_ip'])) {
            $implode[] = "c.customer_id IN (SELECT customer_id FROM " . DB_PREFIX . "customer_ip WHERE ip = '" . $this->db->escape($data['filter_ip']) . "')";
        }

        if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
            $implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
        }

        if (isset($data['filter_approved']) && !is_null($data['filter_approved'])) {
            $implode[] = "c.approved = '" . (int)$data['filter_approved'] . "'";
        }

        if (!empty($data['filter_date_added'])) {
            $implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
        }

        if (isset($data['filter_origem_cadastro']) && !is_null($data['filter_origem_cadastro'])) {
            $implode[] = "c.origem_cadastro = '" . (int)$data['filter_origem_cadastro'] . "'";
        }

        if (isset($data['filter_integracao']) && !is_null($data['filter_integracao'])) {
            $implode[] = "c.mkt_integracao_tipo = '" . (int)$data['filter_integracao'] . "'";
        }

        if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace'])) {
            $implode[] = "c.mkt_tipo = '" . (int)$data['filter_marketplace'] . "'";
        }

        if (!empty($data['filter_pessoa']) && ($data['filter_pessoa'] == Type_CadastroPessoa::Tp_Fisico)) {
            $implode[] = "c.cpf != ''";
        }

        if (!empty($data['filter_pessoa']) && ($data['filter_pessoa'] == Type_CadastroPessoa::Tp_Juridico)) {
            $implode[] = "c.cnpj != ''";
        }

        if (!empty($data['filter_cpf_cnpj']) && (strlen(trim($data['filter_cpf_cnpj'])) == 11 )){
            $implode[] = "c.cpf = '" . trim($data['filter_cpf_cnpj']) .  "' ";
        } elseif (!empty($data['filter_cpf_cnpj']) && (strlen(trim($data['filter_cpf_cnpj'])) == 14 )) {
            $implode[] = "c.cnpj = '" . trim($data['filter_cpf_cnpj']) .  "' ";
        }

        if (!empty($data['filter_uf'])) {
            $implode[] = "a.zone_id = '" . (int)$data['filter_uf'] . "'";
        }

        if ($implode) {
            $sql .= " AND " . implode(" AND ", $implode);
        }

        $sort_data = array(
            'name',
            'c.email',
            'customer_group',
            'c.status',
            'c.approved',
            'c.ip',
            'c.date_added',
            'c.origem_cadastro'
        );

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            $sql .= " ORDER BY " . $data['sort'];
        } else {
            $sql .= " ORDER BY name";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC";
        } else {
            $sql .= " ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

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

        return $query->rows;
    }

    public function approve($customer_id)
    {
        $customer_info = $this->getCustomer($customer_id);

        if ($customer_info) {
            $this->db->query("UPDATE " . DB_PREFIX . "customer SET approved = '1' WHERE customer_id = '" . (int)$customer_id . "'");

            $this->language->load('mail/customer');

            $this->load->model('setting/store');

            $store_info = $this->model_setting_store->getStore($customer_info['store_id']);
            $store_name = $this->config->get('config_name');
            $store_url = $this->urlloja->link('account/login');


            $message = sprintf($this->language->get('text_approve_welcome'), $store_name) . "\n\n";
            $message .= $this->language->get('text_approve_login') . "\n";
            $message .= $store_url . "\n\n";
            $message .= $this->language->get('text_approve_services') . "\n\n";
            $message .= $this->language->get('text_approve_thanks') . "\n";
            $message .= $store_name;

            $mail = new Mail();
            $mail->protocol = $this->config->get('config_mail_protocol');
            $mail->parameter = $this->config->get('config_mail_parameter');
            $mail->hostname = $this->config->get('config_smtp_host');
            $mail->username = $this->config->get('config_smtp_username');
            $mail->password = $this->config->get('config_smtp_password');
            $mail->port = $this->config->get('config_smtp_port');
            $mail->timeout = $this->config->get('config_smtp_timeout');
            $mail->setTo($customer_info['email']);
            $mail->setFrom($this->config->get('config_email'));
            $mail->setSender($store_name);
            $mail->setSubject(html_entity_decode(sprintf($this->language->get('text_approve_subject'), $store_name), ENT_QUOTES, 'UTF-8'));
            $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
            $mail->send();
        }
    }

    public function getAddress($address_id)
    {
        $address_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "address WHERE address_id = '" . (int)$address_id . "'");

        if ($address_query->num_rows) {
            $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$address_query->row['country_id'] . "'");

            if ($country_query->num_rows) {
                $country = $country_query->row['name'];
                $iso_code_2 = $country_query->row['iso_code_2'];
                $iso_code_3 = $country_query->row['iso_code_3'];
                $address_format = $country_query->row['address_format'];
            } else {
                $country = '';
                $iso_code_2 = '';
                $iso_code_3 = '';
                $address_format = '';
            }

            $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$address_query->row['zone_id'] . "'");

            if ($zone_query->num_rows) {
                $zone = $zone_query->row['name'];
                $zone_code = $zone_query->row['code'];
            } else {
                $zone = '';
                $zone_code = '';
            }

            return array(
                'address_id' => $address_query->row['address_id'],
                'customer_id' => $address_query->row['customer_id'],
                'apelido' => $address_query->row['apelido'],
                'numero' => $address_query->row['numero'],
                'complemento' => $address_query->row['complemento'],
                'firstname' => $address_query->row['firstname'],
                'lastname' => $address_query->row['lastname'],
                'company' => $address_query->row['company'],
                'company_id' => $address_query->row['company_id'],
                'address_1' => $address_query->row['address_1'],
                'address_2' => $address_query->row['address_2'],
                'postcode' => $address_query->row['postcode'],
                'city' => $address_query->row['city'],
                'zone_id' => $address_query->row['zone_id'],
                'zone' => $zone,
                'zone_code' => $zone_code,
                'country_id' => $address_query->row['country_id'],
                'country' => $country,
                'iso_code_2' => $iso_code_2,
                'iso_code_3' => $iso_code_3,
                'address_format' => $address_format,
                'tipo_logradouro' => $address_query->row['tipo_logradouro'],
                'logradouro' => $address_query->row['logradouro'],
                'codigo_municipio' => $address_query->row['codigo_municipio']
            );
        }
    }

    public function getAddresses($customer_id)
    {
        $address_data = array();

        $query = $this->db->query("SELECT address_id FROM " . DB_PREFIX . "address WHERE customer_id = '" . (int)$customer_id . "'");

        foreach ($query->rows as $result) {
            $address_info = $this->getAddress($result['address_id']);

            if ($address_info) {
                $address_data[$result['address_id']] = $address_info;
            }
        }

        return $address_data;
    }

    public function getTotalCustomers($data = array())
    {

        $this->load->type('tipocomum');

        $sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer c";

        if (!empty($data['filter_uf'])) {
            $sql .= " INNER JOIN " . DB_PREFIX . "address a ON (c.address_id = a.address_id) ";
        }

        $implode = array();

        if (!empty($data['filter_codl'])) {
            $implode[] = "c.customer_id = '" . (int)$data['filter_codl'] . "'";
        }

        if (!empty($data['filter_codf'])) {
            $implode[] = "c.id_futura = '" . (int)$data['filter_codf'] . "'";
        }

        if (!empty($data['filter_name'])) {
            $implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
        }

        if (!empty($data['filter_email'])) {
            $implode[] = "c.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
        }

        if (isset($data['filter_newsletter']) && !is_null($data['filter_newsletter'])) {
            $implode[] = "c.newsletter = '" . (int)$data['filter_newsletter'] . "'";
        }

        if (!empty($data['filter_customer_group_id'])) {
            $implode[] = "c.customer_group_id = '" . (int)$data['filter_customer_group_id'] . "'";
        }

        if (!empty($data['filter_ip'])) {
            $implode[] = "c.customer_id IN (SELECT customer_id FROM " . DB_PREFIX . "customer_ip WHERE ip = '" . $this->db->escape($data['filter_ip']) . "')";
        }

        if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
            $implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
        }

        if (isset($data['filter_approved']) && !is_null($data['filter_approved'])) {
            $implode[] = "c.approved = '" . (int)$data['filter_approved'] . "'";
        }

        if (!empty($data['filter_date_added'])) {
            $implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
        }

        if (!empty($data['filter_date_added_between'])) {
            $implode[] = "DATE(c.date_added) BETWEEN MAKEDATE(YEAR(current_timestamp), 1) AND DATE(NOW())";
        }

        if (isset($data['filter_origem_cadastro']) && !is_null($data['filter_origem_cadastro'])) {
            $implode[] = "c.origem_cadastro = '" . (int)$data['filter_origem_cadastro'] . "'";
        }

        if (isset($data['filter_integracao']) && !is_null($data['filter_integracao'])) {
            $implode[] = "c.mkt_integracao_tipo = '" . (int)$data['filter_integracao'] . "'";
        }

        if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace'])) {
            $implode[] = "c.mkt_tipo = '" . (int)$data['filter_marketplace'] . "'";
        }

        if (!empty($data['filter_pessoa']) && ($data['filter_pessoa'] == Type_CadastroPessoa::Tp_Fisico)) {
            $implode[] = "c.cpf != ''";
        }

        if (!empty($data['filter_pessoa']) && ($data['filter_pessoa'] == Type_CadastroPessoa::Tp_Juridico)) {
            $implode[] = "c.cnpj != ''";
        }

        if (!empty($data['filter_cpf_cnpj']) && (strlen(trim($data['filter_cpf_cnpj'])) == 11 )){
            $implode[] = "c.cpf = '" . trim($data['filter_cpf_cnpj']) .  "' ";
        } elseif (!empty($data['filter_cpf_cnpj']) && (strlen(trim($data['filter_cpf_cnpj'])) == 14 )) {
            $implode[] = "c.cnpj = '" . trim($data['filter_cpf_cnpj']) .  "' ";
        }

        if (!empty($data['filter_uf'])) {
            $implode[] = "a.zone_id = '" . (int)$data['filter_uf'] . "'";
        }

        if ($implode) {
            $sql .= " WHERE " . implode(" AND ", $implode);
        }

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

        return $query->row['total'];
    }

    public function getTotalCustomersAwaitingApproval()
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE status = '0' OR approved = '0'");

        return $query->row['total'];
    }

    public function getTotalAddressesByCustomerId($customer_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "address WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->row['total'];
    }

    public function getTotalAddressesByCountryId($country_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "address WHERE country_id = '" . (int)$country_id . "'");

        return $query->row['total'];
    }

    public function getTotalAddressesByZoneId($zone_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "address WHERE zone_id = '" . (int)$zone_id . "'");

        return $query->row['total'];
    }

    public function getTotalCustomersByCustomerGroupId($customer_group_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE customer_group_id = '" . (int)$customer_group_id . "'");

        return $query->row['total'];
    }

    public function addHistory($customer_id, $comment)
    {
        $this->db->query("INSERT INTO " . DB_PREFIX . "customer_history SET customer_id = '" . (int)$customer_id . "', comment = '" . $this->db->escape(strip_tags($comment)) . "', date_added = NOW()");
    }

    public function getHistories($customer_id, $start = 0, $limit = 10)
    {
        if ($start < 0) {
            $start = 0;
        }

        if ($limit < 1) {
            $limit = 10;
        }

        $query = $this->db->query("SELECT comment, date_added FROM " . DB_PREFIX . "customer_history WHERE customer_id = '" . (int)$customer_id . "' ORDER BY date_added DESC LIMIT " . (int)$start . "," . (int)$limit);

        return $query->rows;
    }

    public function getTotalHistories($customer_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_history WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->row['total'];
    }

    public function addTransaction($customer_id, $description = '', $amount = '', $order_id = 0)
    {
        $customer_info = $this->getCustomer($customer_id);

        if ($customer_info) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "customer_transaction SET customer_id = '" . (int)$customer_id . "', order_id = '" . (int)$order_id . "', description = '" . $this->db->escape($description) . "', amount = '" . (float)$amount . "', date_added = NOW()");

            $this->language->load('mail/customer');

            if ($customer_info['store_id']) {
                $this->load->model('setting/store');

                $store_info = $this->model_setting_store->getStore($customer_info['store_id']);

                if ($store_info) {
                    $store_name = $store_info['name'];
                } else {
                    $store_name = $this->config->get('config_name');
                }
            } else {
                $store_name = $this->config->get('config_name');
            }

            $message = sprintf($this->language->get('text_transaction_received'), $this->currency->format($amount, $this->config->get('config_currency'))) . "\n\n";
            $message .= sprintf($this->language->get('text_transaction_total'), $this->currency->format($this->getTransactionTotal($customer_id)));

            $mail = new Mail();
            $mail->protocol = $this->config->get('config_mail_protocol');
            $mail->parameter = $this->config->get('config_mail_parameter');
            $mail->hostname = $this->config->get('config_smtp_host');
            $mail->username = $this->config->get('config_smtp_username');
            $mail->password = $this->config->get('config_smtp_password');
            $mail->port = $this->config->get('config_smtp_port');
            $mail->timeout = $this->config->get('config_smtp_timeout');
            $mail->setTo($customer_info['email']);
            $mail->setFrom($this->config->get('config_email'));
            $mail->setSender($store_name);
            $mail->setSubject(html_entity_decode(sprintf($this->language->get('text_transaction_subject'), $this->config->get('config_name')), ENT_QUOTES, 'UTF-8'));
            $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
            $mail->send();
        }
    }

    public function deleteTransaction($order_id)
    {
        $this->db->query("DELETE FROM " . DB_PREFIX . "customer_transaction WHERE order_id = '" . (int)$order_id . "'");
    }

    public function getTransactions($customer_id, $start = 0, $limit = 10)
    {
        if ($start < 0) {
            $start = 0;
        }

        if ($limit < 1) {
            $limit = 10;
        }

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . (int)$customer_id . "' ORDER BY date_added DESC LIMIT " . (int)$start . "," . (int)$limit);

        return $query->rows;
    }

    public function getTotalTransactions($customer_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total  FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->row['total'];
    }

    public function getTransactionTotal($customer_id)
    {
        $query = $this->db->query("SELECT SUM(amount) AS total FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->row['total'];
    }

    public function getTotalTransactionsByOrderId($order_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_transaction WHERE order_id = '" . (int)$order_id . "'");

        return $query->row['total'];
    }

    public function addReward($customer_id, $description = '', $points = '', $order_id = 0)
    {
        $customer_info = $this->getCustomer($customer_id);

        if ($customer_info) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "customer_reward SET customer_id = '" . (int)$customer_id . "', order_id = '" . (int)$order_id . "', points = '" . (int)$points . "', description = '" . $this->db->escape($description) . "', date_added = NOW()");

            $this->language->load('mail/customer');

            if ($order_id) {
                $this->load->model('sale/order');

                $order_info = $this->model_sale_order->getOrder($order_id);

                if ($order_info) {
                    $store_name = $order_info['store_name'];
                } else {
                    $store_name = $this->config->get('config_name');
                }
            } else {
                $store_name = $this->config->get('config_name');
            }

            $message = sprintf($this->language->get('text_reward_received'), $points) . "\n\n";
            $message .= sprintf($this->language->get('text_reward_total'), $this->getRewardTotal($customer_id));

            $mail = new Mail();
            $mail->protocol = $this->config->get('config_mail_protocol');
            $mail->parameter = $this->config->get('config_mail_parameter');
            $mail->hostname = $this->config->get('config_smtp_host');
            $mail->username = $this->config->get('config_smtp_username');
            $mail->password = $this->config->get('config_smtp_password');
            $mail->port = $this->config->get('config_smtp_port');
            $mail->timeout = $this->config->get('config_smtp_timeout');
            $mail->setTo($customer_info['email']);
            $mail->setFrom($this->config->get('config_email'));
            $mail->setSender($store_name);
            $mail->setSubject(html_entity_decode(sprintf($this->language->get('text_reward_subject'), $store_name), ENT_QUOTES, 'UTF-8'));
            $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
            $mail->send();
        }
    }

    public function deleteReward($order_id)
    {
        $this->db->query("DELETE FROM " . DB_PREFIX . "customer_reward WHERE order_id = '" . (int)$order_id . "'");
    }

    public function getRewards($customer_id, $start = 0, $limit = 10)
    {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . (int)$customer_id . "' ORDER BY date_added DESC LIMIT " . (int)$start . "," . (int)$limit);

        return $query->rows;
    }

    public function getTotalRewards($customer_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->row['total'];
    }

    public function getRewardTotal($customer_id)
    {
        $query = $this->db->query("SELECT SUM(points) AS total FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->row['total'];
    }

    public function getTotalCustomerRewardsByOrderId($order_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_reward WHERE order_id = '" . (int)$order_id . "'");

        return $query->row['total'];
    }

    public function getIpsByCustomerId($customer_id)
    {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer_ip WHERE customer_id = '" . (int)$customer_id . "'");

        return $query->rows;
    }

    public function getTotalCustomersByIp($ip)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_ip WHERE ip = '" . $this->db->escape($ip) . "'");

        return $query->row['total'];
    }

    public function addBanIp($ip)
    {
        $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_ban_ip` SET `ip` = '" . $this->db->escape($ip) . "'");
    }

    public function removeBanIp($ip)
    {
        $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_ban_ip` WHERE `ip` = '" . $this->db->escape($ip) . "'");
    }

    public function getTotalBanIpsByIp($ip)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "customer_ban_ip` WHERE `ip` = '" . $this->db->escape($ip) . "'");

        return $query->row['total'];
    }

    public function getProductDiscountByCustomerGroupId($product_id, $customer_group_id){

        $sql = "SELECT * ".
            "  FROM ". DB_PREFIX ."product_discount ".
            " WHERE product_id = '" . $product_id . "'".
            " AND customer_group_id = '" . $customer_group_id . "' " .
            " AND (quantity = 1) 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";

        $query = $this->db->query($sql);
        return $query->row; //retorna apenas o primeiro registro pois tem um LIMIT 1
    }

    public function getProductsCartByCustomerID($customer_ID)
    {

        $productInfo = Array();
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE `CUSTOMER_ID` = '" . $this->db->escape($customer_ID) . "' ");
        $cart = unserialize($query->row['cart']);

        if (!empty($cart)) {

            $this->load->dmodel('account/customer');
            $customer = $this->dmodel_account_customer->getCustomer($customer_ID);

            if(isset($customer['customer_group_id']) && $customer['customer_group_id']){
                $customer_group_id = $customer['customer_group_id'];
            } else {

                if(isset($customer['cpf']) && (trim($customer['cpf']) != '')){
                    $customer_group_id = $this->config->get('config_customer_group_id_cpf');
                }else if (isset($customer['cnpj']) && (trim($customer['cnpj']) != '')){
                    $customer_group_id = $this->config->get('config_customer_group_id_cnpj');
                }else
                    $customer_group_id = $this->config->get('config_customer_group_id');
            }

            foreach (unserialize($query->row['cart']) as $key => $cartvalue) {
                $quantity = $cartvalue['quantity'];
                $product_id = (int)str_replace('::', '', $key);
                if ($this->config->get('futuragrade_status')) {
                    $product_query = $this->db->query(
                        "SELECT "
                        . " p.*,"
                        . "     (pd.name)         name, "
                        . "     pkit.product_kit_id AS product_kit_id,"
                        . " 	(pc.description)  color_description,"
                        . " 	(ps.description)  size_description,"
                        . " 	(psp.description) specification_description"
                        . " FROM " . DB_PREFIX . "product p"
                        . " LEFT JOIN " . DB_PREFIX . "product_description   pd  ON (p.product_id                = pd.product_id) "
                        . " 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_kit pkit on pkit.product_id = p.product_id"
                        . " WHERE p.product_id = '" . $product_id . "'"
                    );
                } else {
                    $product_query = $this->db->query(
                        "SELECT "
                        . "p.*, "
                        . "pkit.product_kit_id AS product_kit_id,"
                        . "(pd.name)name "
                        . "FROM " . DB_PREFIX . "product p"
                        . " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) "
                        . " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id"
                        . " WHERE p.product_id = '" . $product_id . "'");
                }

                $price = null;
                $product_discount = $this->getProductDiscountByCustomerGroupId($product_id, $customer_group_id);
                if (isset($product_discount['price']) && $product_discount['price']) {
                    $price = $product_discount['price'];
                }

                $productInfo[] = Array(
                    'product_id' => $product_query->row['product_id'],
                    'name' => $product_query->row['name'],
                    'model' => $product_query->row['model'],
                    'quantity' => $quantity,
                    'price' => ( $price ? $price : $product_query->row['price']),
                    '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'] : '',
                    'image' => $product_query->row['image'],
                    'lista_escolar_id' => (isset($cartvalue['id_listaescolar'])) ? $cartvalue['id_listaescolar'] : 0,
                    'listaescolar_descricao' => (isset($cartvalue['listaescolar_descricao'])) ? $cartvalue['listaescolar_descricao'] : '',
                    'listaescolar_escola' => (isset($cartvalue['listaescolar_escola'])) ? $cartvalue['listaescolar_escola'] : '',
                    'listaescolar_serie' => (isset($cartvalue['listaescolar_serie'])) ? $cartvalue['listaescolar_serie'] : '',
                    'product_kit_id' => $product_query->row['product_kit_id']
                );

            }
        }

        return $productInfo;
    }

    public function getCustomerTotalDateByHour($hour)
    {

        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE DATE(date_added) = DATE(NOW()) AND HOUR(date_added) = '" . (int)$hour . "' GROUP BY HOUR(date_added) ORDER BY date_added ASC");
        return ($query->num_rows) ? (int)$query->row['total'] : 0;

    }

    public function getCustomerTotalDateByWeek($date)
    {

        $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "customer` WHERE DATE(date_added) = '" . $this->db->escape($date) . "' GROUP BY DATE(date_added)");
        return ($query->num_rows) ? (int)$query->row['total'] : 0;

    }

    public function getCustomerTotalDateByMonth($date)
    {

        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE DATE(date_added) = '" . $this->db->escape($date) . "' GROUP BY DAY(date_added)");
        return ($query->num_rows) ? (int)$query->row['total'] : 0;

    }

    public function getCustomerTotalDateByYear($year)
    {

        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE YEAR(date_added) = '" . date('Y') . "' AND MONTH(date_added) = '" . $year . "' GROUP BY MONTH(date_added)");
        return ($query->num_rows) ? (int)$query->row['total'] : 0;

    }

}
