<?php
defined('BASEPATH') or exit('No direct script access allowed');

class ProductModel extends CI_Model
{

	public function getProduct($category, $sub_category)
	{
		$this->db->select("sha1(a.product_id) as product_id, a.`product_name`, a.`product_model`, ,a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,a.brand_id,a.url,c.url as sub_url");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where('b.url', $category);
		$this->db->where('c.url', $sub_category);
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->order_by("a.product_id", "DESC");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->result_array();
	}
	public function getAllProduct()
	{
		$this->db->select("sha1(a.product_id) as product_id, a.`product_name`, a.`product_model`, a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,c.url as sub_url,a.url,c.url as sub_url,a.brand_id");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->order_by("a.product_id", "DESC");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function getFilterProduct($product)
	{
		$this->db->select("sha1(a.product_id) as product_id, a.url,a.`product_name`, a.`product_model`, a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,a.brand_id,c.url as sub_url");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->like('a.product_name', $product);
		$this->db->or_like('c.sub_category_name', $product);
		$this->db->or_like('b.category_name', $product);
		$this->db->or_like('d.brand_name', $product);
		$this->db->order_by("a.product_id", "DESC");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function getFeatureProduct()
	{
		$this->db->select("sha1(a.product_id) as product_id, a.`product_name`, a.`product_model`, a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,a.brand_id,a.url,c.url as sub_url");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->where("find_in_set(1, a.presentation_status)");
		$this->db->order_by("a.product_id", "DESC");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->result_array();
	}
	public function topSaleProduct()
	{
		$this->db->select("sha1(a.product_id) as product_id, a.`product_name`, a.`product_model`, a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,a.brand_id,a.url,c.url as sub_url");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->where("find_in_set(2, a.presentation_status)");
		$this->db->order_by("a.product_id", "DESC");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function getSingleProduct($sub_category, $product)
	{

		$this->db->select("sha1(a.product_id) as product_id, a.tag, a.meta_description, a.`product_name`,a.url, a.`product_model`, a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,a.brand_id,c.url as sub_url");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where('a.url', $product);
		$this->db->where('c.url', $sub_category);
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->order_by("a.product_id", "DESC");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->row();
	}

	public function relatedProduct($product_info)
	{
		$tagArray=explode(',',$product_info->tag);
		$raw_query="";
		$numItems = count($tagArray);
		$i = 0;

		foreach ($tagArray as $data){
			if(++$i === $numItems) {
				$raw_query.="Lower(a.`tag`) like '%".trim(strtolower($data))."%' ";
			}
			else{
				$raw_query.="Lower(a.`tag`) like '%".trim(strtolower($data))."%' OR ";
			}

		}

		$this->db->select("sha1(a.product_id) as product_id, a.`product_name`, a.`product_model`, a.url,a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,a.brand_id,c.url as sub_url");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where_not_in('sha1(a.product_id)', $product_info->product_id);
		$this->db->where($raw_query);
		$this->db->or_where('sha1(a.sub_category_id)', $product_info->sub_category_id);
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->order_by("a.product_id", "DESC");
		$this->db->limit("5");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->result_array();
	}


	public function getBrandProduct($brand)
	{
		$this->db->select("sha1(a.product_id) as product_id, a.`product_name`,a.url, a.`product_model`, a.`size`, a.`description`, a.`presentation_status`, a.`status`, a.`availability`, a.`publish_status`,a.picture,b.category_name,c.sub_category_name,d.brand_name,a.category_id,sha1(a.sub_category_id) as sub_category_id,a.brand_id,c.url as sub_url");
		$this->db->join('category as b', 'a.category_id = b.category_id', 'left');
		$this->db->join('sub_category as c', 'a.sub_category_id = c.sub_category_id AND c.category_id=b.category_id', 'left');
		$this->db->join('brand as d', 'a.brand_id = d.brand_id', 'left');
		$this->db->where('d.url', $brand);
		$this->db->where('a.status', 'Active');
		$this->db->where('a.availability', 'Available');
		$this->db->where('a.publish_status', 'Published');
		$this->db->order_by("a.product_id", "DESC");
		$this->db->from('product as a');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function getInfoByID($col, $table, $val)
	{
		$this->db->select("*");
		$this->db->from($table);
		$this->db->where($col, $val);
		$query = $this->db->get();
		return $query->row();
	}
	public function getCategoryWiseProduct(){
		$this->db->select("c.category_name,c.url,count(p.product_id) as total ");
		$this->db->where('c.`category_id` = p.category_id');
		$this->db->where('p.status', 'Active');
		$this->db->where('p.availability', 'Available');
		$this->db->where('p.publish_status', 'Published');
		$this->db->order_by("c.category_name", "ASC");
		$this->db->from('`category` c,product p');
		$this->db->group_by("c.category_name,c.url");
		$query = $this->db->get();
		return $query->result_array();
	}
}
