<?php

class ModelAccountCustomer extends Model
{

  public function addCustomer($data)
  {

    $this->load->helper('ftr_geral');
    $this->load->helper('Ftr_sql');
    $this->load->type('tipocomum');

    if (isset($data['tbp']) && ($data['tbp'] != '')) {
      $customer_group_id = $data['tbp'];
      $this->load->model('account/customer_group');
      $customer_group_info = $this->model_account_customer_group->getCustomerGroup($customer_group_id);
    }

    if (!isset($customer_group_info) || (isset($customer_group_info) && $customer_group_info['hab_url_cadastro'] <> 1)) {
      $customer_group_id = $this->config->get('config_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->model('account/customer_group');

      $customer_group_info = $this->model_account_customer_group->getCustomerGroup($customer_group_id);
    }


    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']), "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞß", "àáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ");

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

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

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

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

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

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

    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['razao_social'])) {
      $data['razao_social'] = ucwords(html_entity_decode($data['razao_social'], ENT_QUOTES, 'UTF-8'));
    } else {
      $data['razao_social'] = '';
    }

    if(isset($data['indicator_id'])){
      $indicator_id = $this->db->escape($data['indicator_id']);
    } else {
      $indicator_id = 'null';
    }

    if (isset($data['origem_cadastro'])) {
        $origem_cadastro =  $this->db->escape($data['origem_cadastro']);
    } else {
          $origem_cadastro = 0;
    }


    $this->db->query("INSERT INTO " . DB_PREFIX . "customer SET
                        store_id = '" . (int)$this->config->get('config_store_id') . "',
                        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']) . "',
                        salt = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "',
                        password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "',
                        newsletter = '" . (isset($data['newsletter']) ? (int)$data['newsletter'] : 0) . "',
                        customer_group_id = '" . (int)$customer_group_id . "',
                        ip = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "',
                        status = '1',
                        approved = '" . (int)!$customer_group_info['approval'] . "',
                        date_added = NOW(),
                        cpf = '" . $this->db->escape($data['cpf']) . "',
                        rg = '" . $this->db->escape($data['rg']) . "',
                        cnpj = '" . $this->db->escape($data['cnpj']) . "',
                        customer_type = '" . $this->db->escape($data['customer_type']) . "',
                        perfil = '" . $this->db->escape($data['perfil']) . "',
                        contribuinte = '" . $this->db->escape($data['contribuinte']) . "',
                        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']) . "',
                        interests_remember = '0',
                        interests_date = '" . date('Y-m-d') . "',
                        indicator_id = " . $indicator_id.",
                        feedback_whatsapp_number = '".$this->db->escape($data['feedback_whatsapp_number'])."',
                        feedback_whatsapp_status = '".$this->db->escape($data['feedback_whatsapp_status'])."',
                        origem_cadastro = ". $origem_cadastro);

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

    if (isset($data['categories'])) {
      $data['categories']['customer_id'] = $customer_id;
      $this->addInterests($data['categories']);
    }

    $dataAdress = array();
    $dataAdress[] = (int)$customer_id;
    $dataAdress[] = ucwords($data['firstname']);
    $dataAdress[] = ucwords($data['lastname']);
    $dataAdress[] = '';
    $dataAdress[] = '';
    $dataAdress[] = ucwords($data['address_1']);
    $dataAdress[] = substr(ucwords($data['address_2']), 0, 300);
    $dataAdress[] = preg_replace('/([^0-9])/i', '', $data['postcode']);
    $dataAdress[] = ucwords($data['city']);
    $dataAdress[] = $data['zone_id'];
    $dataAdress[] = (isset($data['country_id']) && $data['country_id'] != '') ? $data['country_id'] : Type_PaisId::Tp_Brasil;
    $dataAdress[] = ($data['apelido'] != '') ? ucwords($data['apelido']) : substr(ucwords($data['address_2']), 0, 300);
    $dataAdress[] = preg_replace('/([^0-9])/i', '', $data['numero']);
    $dataAdress[] = ucwords($data['complemento']);
    $dataAdress[] = ucwords($data['tipo_logradouro']);
    $dataAdress[] = (isset($data['logradouro']) && $data['logradouro'] != '') ? ucwords($data['logradouro']) : ucwords($data['address_1']);
    $dataAdress[] = (isset($data['codigo_municipio']) && $data['codigo_municipio'] != '') ? $data['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);

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

    $this->db->query("UPDATE " . DB_PREFIX . "customer SET address_id = '" . (int)$address_id . "', time_lock = '".Crypto::criptografarAES256(json_encode(['customer_id' => (int)$customer_id, 'date_lock'=>'0']), Crypto::CAES256)."' WHERE customer_id = '" . (int)$customer_id . "'");

  }

  public function updateImgCustomer($IDCliente, $img){
    $this->db->query("UPDATE " . DB_PREFIX . "customer SET picture  = '" . $img ."' WHERE customer_id = '" . (int)$IDCliente."'");
  }

  public function deleteCustomer($IDCliente){

      $sql= " update oc_customer set
                    `firstname` = 'LGPD - DADOS EXCLUIDOS' ,
                    `lastname` = 'LGPD - DADOS EXCLUIDOS',
                    `email` = 'exclusao@lgpd.com',
                    `telephone` = '19999999999' ,
                    `fax` = '1999999999',
                    `cpf` = '',
                    `celular` = '19999999999' ,
                    `cnpj` = '',
                    `rg` = '',
                    `inscricao_estadual` = '' ,
                    `razao_social` = 'LGPD - DADOS EXCLUIDOS',
                    `ip`= '127.1.1.1' ,
                    `status` = 0,
                    `approved` = 0,
                    `email_facebook` = 'exclusao@lgpd.com' ,
                    `email_gmail` = 'exclusao@lgpd.com' ,
                    `mkt_buyer_id` = null ,
                    `feedback_whatsapp_number` = '19999999999',
                    `facebook_user_id` = '',
                    `gmail_user_id` = ''
                    where customer_id = {$IDCliente}";
      $result = $this->db->query($sql);

      if($result){
          $sql= "  update `oc_address` set
                        `firstname` = 'LGPD - SOLICITADO EXCLUSÃO',
                        `lastname` = 'LGPD',
                        `apelido` = 'LGPD',
                        `company` = '',
                        `address_1` = 'LGPD - DADOS EXCLUIDOS',
                        `numero` = 1,
                        `address_2` = 'LGPD - DADOS EXCLUIDOS',
                        `complemento` = 'LGPD - DADOS EXCLUIDOS',
                        `city` = 'LGPD - DADOS EXCLUIDOS',
                        `postcode` = 00000000,
                        `logradouro` = 'LGPD - DADOS EXCLUIDOS',
                        `codigo_municipio` = 0
                    where customer_id = {$IDCliente}";

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

        $sql= "UPDATE  oc_order c set
						  c.firstname = 'LGPD - SOLICITADO EXCLUSÃO',
						  c.lastname = 'LGPD',
						  c.email = 'exclusao@lgpd.com',
						  c.telephone = 0,
              c.fax = 0,
						  c.cpf = '',
              c.rg = '',
              c.cnpj = '',
              c.inscricao_estadual = '',
              c.razao_social = '',
						  c.data_nascimento = '0',
						  c.sexo = '',
						  c.payment_apelido = 'LGPD - DADOS EXCLUIDOS',
						  c.payment_firstname = 'LGPD - SOLICITADO EXCLUSÃO',
						  c.payment_lastname = 'LGPD',
						  c.payment_address_1 = 'LGPD - DADOS EXCLUIDOS',
						  c.payment_numero = 0,
						  c.payment_address_2 = 'LGPD - DADOS EXCLUIDOS',
						  c.payment_city = 'LGPD - DADOS EXCLUIDOS',
						  c.payment_postcode = '',
						  c.payment_country = '',
						  c.payment_country_id = '',
						  c.payment_zone = '',
						  c.payment_zone_id = '',
						  c.shipping_apelido = 'LGPD - DADOS EXCLUIDOS',
						  c.shipping_firstname = 'LGPD - SOLICITADO EXCLUSÃO',
						  c.shipping_lastname = 'LGPD',
						  c.shipping_company = 'LGPD - DADOS EXCLUIDOS',
						  c.shipping_address_1 = 'LGPD - DADOS EXCLUIDOS',
						  c.shipping_numero = 0,
						  c.shipping_address_2 = 'LGPD - DADOS EXCLUIDOS',
						  c.shipping_complemento = '',
						  c.shipping_city = 'LGPD - DADOS EXCLUIDOS',
						  c.shipping_postcode = '',
						  c.shipping_country = '',
						  c.shipping_country_id = 0,
						  c.shipping_zone = '',
						  c.shipping_zone_id = '',
						  c.celular = '',
						  c.shipping_logradouro = 'LGPD - DADOS EXCLUIDOS',
						  c.shipping_tipo_logradouro = 'LGPD - DADOS EXCLUIDOS',
						  c.payment_logradouro = 'LGPD - DADOS EXCLUIDOS',
						  c.payment_tipo_logradouro = 'LGPD - DADOS EXCLUIDOS'
               where  customer_id = {$IDCliente}";

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

      }
      return  $result;
  }

  public function addInterests($data)
  {

    $customer_id = (int)$data['customer_id'];
    unset($data['customer_id']);

    $this->removeInterests($customer_id);

    $query = "INSERT INTO " . DB_PREFIX . "cadastro_interesses (customer_id, tipo, category_id, subcategory_id) VALUES ";
    foreach ($data as $category_id => $value) {
      if (isset($value['status']) && $value['status']) {
        $query .= "(" . $customer_id . ", 0, " . (int)$category_id . ", null),";
      } else {
        if (is_array($value)) {
          foreach ($value as $subcategory_id => $value) {
            if ($subcategory_id != 'status' && $subcategory_id != 'customer_id') {
              $query .= "(" . $customer_id . ", 1, null, " . (int)$subcategory_id . "),";
            }
          }
        }
      }
    }

    $query = substr($query, 0, strlen($query) - 1);

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

    return $result;
  }

  public function removeInterests($customer_id = '')
  {
    if (!$customer_id) {
      $customer_id = (int)$this->customer->getId();
    }

    $query = "DELETE FROM " . DB_PREFIX . "cadastro_interesses WHERE customer_id = " . $customer_id;
    $result = $this->db->query($query);
    return $result;
  }

  public function getInterests($customer_id = '')
  {
    $customerId = ($customer_id) ? $customer_id : (int)$this->customer->getId();

    $query = "SELECT * FROM " . DB_PREFIX . "cadastro_interesses WHERE customer_id = " . $customerId;
    $result = $this->db->query($query);
    return $result->rows;
  }

  public function getInterestsCustomer($customer_id)
  {
    $query = "select case when ISNULL(ci.subcategory_id) then ci.category_id else ci.subcategory_id end as category_id from oc_cadastro_interesses ci where ci.customer_id = " . (int)$customer_id . " order by ci.tipo";
    $result = $this->db->query($query);
    return $result->rows;
  }

  public function setInterestsRemember($data, $customer_id = '')
  {

    if (!$customer_id) {
      $customer_id = (int)$this->customer->getId();
    }
    $result = $this->db->query("UPDATE " . DB_PREFIX . "customer SET interests_remember = '" . $data . "', interests_date = '" . date('Y-m-d') . "' WHERE customer_id = " . $customer_id);

    return $result;
  }

  public function editCustomer($data)
  {

    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'] = trim(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']), "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞß", "àáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ");

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

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

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

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

    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['razao_social'])) {
      $data['razao_social'] = ucwords(html_entity_decode($data['razao_social'], ENT_QUOTES, 'UTF-8'));
    } else {
      $data['razao_social'] = '';
    }

    if (isset($data['customerml'])) {

      $customerid = $data['customerml'];

      $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']) . "', fax = '" . $this->db->escape($data['fax']) . "', 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']) . "'  , razao_social = '" . $this->db->escape($data['razao_social']) . "', salt = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "', 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']) . "', cadastro_atualizado_ml=1 WHERE customer_id = '" . (int)$customerid . "'");
    } else {
      $customerid = $this->customer->getId();

      $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']) . "',
                            fax = '" . $this->db->escape($data['fax']) . "',
                            cpf = '" . $this->db->escape($data['cpf']) . "',
                            rg = '" . $this->db->escape($data['rg']) . "',
                            cnpj = '" . $this->db->escape($data['cnpj']) . "',
                            customer_type = '" . $this->db->escape($data['customer_type']) . "',
                            perfil = '" . $this->db->escape($data['perfil']) . "',
                            contribuinte = '" . $this->db->escape($data['contribuinte']) . "' ,
                            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']) . "',
                            feedback_whatsapp_number = '".$this->db->escape($data['feedback_whatsapp_number'])."'
                        WHERE customer_id = '" . (int)$customerid . "'");
    }


  }

  public function editPassword($email, $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($password)))) . "' WHERE LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "'");
  }

  public function editNewsletter($newsletter)
  {
    $this->db->query("UPDATE " . DB_PREFIX . "customer SET newsletter = '" . (int)$newsletter . "' WHERE customer_id = '" . (int)$this->customer->getId() . "'");
  }

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

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

    return $query->row;
  }

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

  public function getCustomerByToken($token)
  {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE token = '" . $this->db->escape($token) . "' AND token != ''");

    $this->db->query("UPDATE " . DB_PREFIX . "customer SET token = '' WHERE token = '" . $this->db->escape($token) . "' AND token != ''");

    return $query->row;
  }

  public function getCustomers($data = array())
  {
    $sql = "SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cg.name AS customer_group FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_group cg ON (c.customer_group_id = cg.customer_group_id) ";

    $implode = array();

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

    if (isset($data['filter_email']) && !is_null($data['filter_email'])) {
      $implode[] = "LCASE(c.email) = '" . $this->db->escape(utf8_strtolower($data['filter_email'])) . "'";
    }

    if (isset($data['filter_customer_group_id']) && !is_null($data['filter_customer_group_id'])) {
      $implode[] = "cg.customer_group_id = '" . $this->db->escape($data['filter_customer_group_id']) . "'";
    }

    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 (isset($data['filter_ip']) && !is_null($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_date_added']) && !is_null($data['filter_date_added'])) {
      $implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
    }

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

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

    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 getTotalCustomersByEmail($email)
  {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "'");

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

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

    return $query->rows;
  }

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

    return $query->num_rows;
  }

  public function getValidaPassword($password)
  {

    $customer_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE LOWER(email) = '" . $this->db->escape(utf8_strtolower($this->customer->getEmail())) . "' AND (password = SHA1(CONCAT(salt, SHA1(CONCAT(salt, SHA1('" . $this->db->escape($password) . "'))))) OR password = '" . $this->db->escape(md5($password)) . "') ");

    if ($customer_query->num_rows) {
      return true;
    } else {
      return false;
    }

  }

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

      if($data){

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

          $this->db->query("UPDATE " . DB_PREFIX . "customer
          SET celular = ".$this->db->escape($data['celular'])."
          WHERE customer_id = ".$this->customer->getId());
      }

  }

}
