<?php
class ModelCatalogCategory extends Model {
	public function getCategory($category_id) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '1'");

		return $query->row;
	}

	public function getCategories($parent_id = 0) {
		$query = $this->db->query("SELECT c.category_id as category_id, 
                                          cd.name as name, 
                                          cd.description, 
                                          c.image, 
                                          c.top, 
                                          c.sort_order as sort_order, 
                                          1 as category 
                                    FROM " . DB_PREFIX . "category c 
                                    LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) 
                                    LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) 
                                    WHERE c.parent_id = '" . $parent_id . "' 
                                    AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
                                    AND c2s.store_id = '0' 
                                    AND c.status = '1' 
                               UNION 
                                    SELECT 0-i.information_id, 
                                           id.title, 
										   '',
                                           i.image, 
                                           i.top, 
                                           i.sort_order, 
                                           0 as category 
                                    FROM " . DB_PREFIX . "information i 
                                    LEFT JOIN " . DB_PREFIX . "information_description id ON(i.information_id = id.information_id) 
                               WHERE status = '1' 
                                    AND id.language_id = '" . (int)$this->config->get('config_language_id') . "' 
                                    AND i.parent_id = '" . (0-$parent_id) . "' 
                                    ORDER BY sort_order, LCASE(name)");

		return $query->rows;
	}

	public function getCategoryFilters($category_id) {
		$implode = array();

		$query = $this->db->query("SELECT filter_id FROM " . DB_PREFIX . "category_filter WHERE category_id = '" . (int)$category_id . "'");

		foreach ($query->rows as $result) {
			$implode[] = (int)$result['filter_id'];
		}

		$filter_group_data = array();

		if ($implode) {
			$filter_group_query = $this->db->query("SELECT DISTINCT f.filter_group_id, fgd.name, fg.sort_order FROM " . DB_PREFIX . "filter f LEFT JOIN " . DB_PREFIX . "filter_group fg ON (f.filter_group_id = fg.filter_group_id) LEFT JOIN " . DB_PREFIX . "filter_group_description fgd ON (fg.filter_group_id = fgd.filter_group_id) WHERE f.filter_id IN (" . implode(',', $implode) . ") AND fgd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY f.filter_group_id ORDER BY fg.sort_order, LCASE(fgd.name)");

			foreach ($filter_group_query->rows as $filter_group) {
				$filter_data = array();

				$filter_query = $this->db->query("SELECT DISTINCT f.filter_id, fd.name FROM " . DB_PREFIX . "filter f LEFT JOIN " . DB_PREFIX . "filter_description fd ON (f.filter_id = fd.filter_id) WHERE f.filter_id IN (" . implode(',', $implode) . ") AND f.filter_group_id = '" . (int)$filter_group['filter_group_id'] . "' AND fd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY f.sort_order, LCASE(fd.name)");

				foreach ($filter_query->rows as $filter) {
					$filter_data[] = array(
						'filter_id' => $filter['filter_id'],
						'name'      => $filter['name']
					);
				}

				if ($filter_data) {
					$filter_group_data[] = array(
						'filter_group_id' => $filter_group['filter_group_id'],
						'name'            => $filter_group['name'],
						'filter'          => $filter_data
					);
				}
			}
		}

		return $filter_group_data;
	}

	public function getCategoryLayoutId($category_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category_to_layout WHERE category_id = '" . (int)$category_id . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "'");

		if ($query->num_rows) {
			return $query->row['layout_id'];
		} else {
			return 0;
		}
	}

	public function getTotalCategoriesByCategoryId($parent_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '1'");

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

	public function getThreeInfo() {
        $query = $this->db->query("SELECT i.information_id, id.title, id.description, i.image, i.top, id.short_description, i.sort_order FROM " . DB_PREFIX . "information i LEFT JOIN " . DB_PREFIX . "information_description id ON(i.information_id = id.information_id) WHERE status = '1' AND bottom = '1' AND parent_id = '0' AND id.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY sort_order LIMIT 3");

        return $query->rows;
    }

    public function getCategoryProducts($category_id) {
        $products = array();

        $query = $this->db->query("SELECT p.product_id, pd.name FROM " . DB_PREFIX . "product_to_category p2c LEFT JOIN " . DB_PREFIX . "product p ON(p2c.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON(p.product_id = pd.product_id) WHERE p.status = 1 AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '" . $category_id . "' ORDER BY p.sort_order");

        if($query->rows) {
            foreach($query->rows as $result) {
                $products[] = array(
                  'product_id' => $result['product_id'],
                  'name'       => $result['name'],
                  'href'       => $this->url->link('product/product', '&product_id='.$result['product_id'])
                );
            }
        }

        return $products;
    }

    public function getCatId($product_id) {
        $query = $this->db->query("SELECT category_id FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");

        if($query->row['category_id'] && $query->row['category_id'] > 0) {
            return $query->row['category_id'];
        } else {
            return 0;
        }
    }

    public function getLostPath($category_id) {
        $query = $this->db->query("SELECT path_id FROM " . DB_PREFIX . "category_path WHERE category_id = '" . $category_id . "' AND path_id != '" . $category_id . "'");

        return $query->row['path_id'];
    }
	
	public function getBooking($product_id) {  
        $query = $this->db->query("SELECT booking_date FROM " . DB_PREFIX . "order_product WHERE product_id = '" . $product_id . "'");
        return $query->rows;
    }
	
    public  function  getCategoryHome($category_calendar,$date){

        $sql="SELECT p.sort_order as sort_order,
            p.price as price,
            p.product_id as product_id,
            pd.name AS name, ";
        $yy=[];
        for ($i=0; $i<=6;$i++) {
            $booking_date=date('Y-m-d',strtotime("$date +$i day"));
            $yy[] = " (
                        SELECT 
                            p.quantity-if(SUM(op.quantity) IS NULL,0,SUM(op.quantity)) 
                        FROM oc_order_product op WHERE op.product_id=p2c.product_id 
                            AND op.booking_date='".$this->db->escape($booking_date)."' 
                      ) AS day".$i;
        }

        for ($i=0; $i<=6;$i++) {
            $booking_date=date('Y-m-d',strtotime("$date +$i day"));
           $yy[]="(
           SELECT price
		FROM " . DB_PREFIX . "product_special ps
		WHERE ps.product_id = p.product_id
            AND ps.customer_group_id = 1
            AND (
                (
                    ps.date_start = '0000-00-00'
                    OR ps.date_start <= '".$this->db->escape($booking_date)."'
                )
                AND (
                    ps.date_end = '0000-00-00'
                    OR ps.date_end >= '".$this->db->escape($booking_date)."'
                )
                AND ps.disable_day" .($i+1). " = '0'
				)
		ORDER BY ps.priority ASC
			,ps.price ASC LIMIT 1
		) AS special".$i;
        }

        $sql .= implode(', ',$yy);

        $sql.="   
FROM
	".DB_PREFIX."product_to_category p2c 
    LEFT JOIN ".DB_PREFIX."product p ON p.product_id=p2c.product_id
    LEFT JOIN ".DB_PREFIX."product_description pd ON pd.product_id=p2c.product_id
    LEFT JOIN ".DB_PREFIX."category_path cp ON cp.category_id=p2c.category_id
WHERE 
	pd.language_id=". (int)$this->config->get('config_language_id') ." AND
	p.status=1
  AND p.category_calendar=".(int)$category_calendar."
  AND cp.level=0
  AND p.quantity>0
    ORDER BY sort_order ASC
    ";
        $query=$this->db->query($sql)->rows;
        return $query;

    }
}