<?php
class ModelAventuras extends Model {
	public function addAventura($data) {
	$timestamp=time();
	$query=$this->db->query("INSERT INTO " . DB_PREFIX ."");
	return $query;
}
	
public function getAventuras(){
	$query=$this->db->query("
	SELECT node,recorrido 
	FROM " . DB_PREFIX . " aventuras 
	WHERE state=1");
	return $query->rows;
}
	
public function getAventura($id){
	$query=$this->db->query("
	SELECT aventuras.node,aventuras.recorrido as recorrido, aventuras.imagen as imagen,
	aventuras.ubicacion as place, aventuras.propiedades as propiedades,
  aventuras.descripcion as descripcion, temporada.season as season, grado.grade as grade,
	duracion.time as time 
  FROM aventuras
  INNER JOIN
  temporada
  ON aventuras.temporada=temporada.id
  INNER JOIN
  grado
  ON
  aventuras.grado=grado.id
  INNER JOIN
  duracion
  ON aventuras.id_duracion=duracion.id
  WHERE aventuras.node='$id'
  ");
		
		return $query->row;
}

public function searchAventuras($search){
	$query=$this->db->query("
	SELECT a.node,a.recorrido, a.imagen,
	a.ubicacion as place, a.propiedades,
	a.descripcion, t.season, g.grade,
	d.time, ta.tipo 
	FROM aventuras a
	JOIN
	tipo_aventura ta
	ON a.id_tipo=ta.id
	INNER JOIN
	temporada t
	ON a.temporada=t.id
	INNER JOIN
	grado g
	ON a.grado=g.id
	INNER JOIN
	duracion d
	ON a.id_duracion=d.id
	WHERE a.descripcion like '%$search%' || a.recorrido like '%$search%'
	");
	
	return $query->rows;
}

public function getFavoritas(){
	$query=$this->db->query("
	SELECT node,recorrido,descripcion,imagen
	FROM " . DB_PREFIX . "  aventuras WHERE favorito=1
	");
	
	return $query->rows;
}

public function getAventurasTipo($id){
	$query=$this->db->query("
	SELECT a.node,a.recorrido, a.imagen,
	a.ubicacion as place, a.propiedades,
	a.descripcion, t.season, g.grade,
	d.time, ta.tipo 
	FROM
	aventuras a
	JOIN
	tipo_aventura ta
	ON a.id_tipo=ta.id
	INNER JOIN
	temporada t
	ON a.temporada=t.id
	INNER JOIN
	grado g
	ON a.grado=g.id
	INNER JOIN
	duracion d
	ON a.id_duracion=d.id
	WHERE a.id_tipo='$id'
	");
	return $query->rows;
}

public function getTipos(){
	$query=$this->db->query("
	SELECT id,tipo FROM tipo_aventura
	");
	return $query->rows;
}
	public function addAddress($data){
		$query=$this->db->query("
		INSERT INTO " . DB_PREFIX . " customer_address SET
		customer_id 	= '" . $this->db->escape($data['customer_id']) . "',
		firstname 	= '" . $this->db->escape($data['firstname']) . "',
    lastname 		= '" . $this->db->escape($data['lastname']) . "',
    address_1 	= '" . $this->db->escape($data['address_1']) . "',
    address_2 	= '" . $this->db->escape($data['address_2']) . "',
    state 			= '" . $this->db->escape($data['state']) . "',
    city 				= '" . $this->db->escape($data['city']) . "',
    zip 				= '" . $this->db->escape($data['zip']) . "',
    phone 				= '" . $this->db->escape($data['phone']) . "',
    country_id 	= '" . (int)$this->db->escape($data['country_id']) . "'
		");
		
		return $query;
	}
	
	public function getCountries(){
		$query=$this->db->query("
		SELECT country_id,name FROM " . DB_PREFIX . " address_country WHERE status=1
		");
		
		return $query->rows;
	}
		public function getCountry($id){
		$query=$this->db->query("
		SELECT country_id,name FROM " . DB_PREFIX . " address_country WHERE country_id='$id'
		");
		return $query->row;
	}		
	public function editCustomer($data) {
		$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']) . "', fax = '" . $this->db->escape($data['fax']) . "' WHERE customer_id = '" . (int)$this->customer->getId() . "'");
	}

	public function editPassword($email, $password) {
      	$this->db->query("UPDATE " . DB_PREFIX . "customer SET password = '" . $this->db->escape(md5($password)) . "' WHERE email = '" . $this->db->escape($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 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 email = '" . $this->db->escape($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 = ''");
		
		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[] = "c.email = '" . $this->db->escape($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(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 isBlacklisted($ip) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_ip_blacklist` WHERE ip = '" . $this->db->escape($ip) . "'");
		
		return $query->num_rows;
	}	
}
?>