<?PHP

class menus_SQL extends SqlRequest{

	var $table = 'menus';

	function menus($add_sql = '', $execute = false){

		$this->LINE = __FILE__.' '.__LINE__;
		return $this->Select("select * from ".$this->table." ".
			$add_sql,
			$execute
		);
	}

	function admin_menus(){

		$this->LINE = __FILE__.' '.__LINE__;
		return $this->Select("select p1.*, p2.name as model, p2.page as page from ".$this->table." as p1 ".
			"join templates as p2 on p2.id = p1.model_id ".
			"join permissions as p3 on p3.table_name = 'templates' and p3.table_id = p2.id ".
			"where p1.is_enabled = 1 and p1.is_restricted = 1 and p3.allow_access = 1 and p1.parent_id = 0 and p3.role_id = ".($_SESSION['UserData']['role_id'])." ".
			"group by p1.id ".
			"order by p1.queue "
		);
	}

	function parent_menu($where){

		$this->LINE = __FILE__.' '.__LINE__;
		return $this->Select("select p1.*, p2.name as model, p2.page as page from ".$this->table." as p1 ".
			"join templates as p2 on p2.id = p1.model_id ".
			"join permissions as p3 on p3.table_name = 'templates' and p3.table_id = p2.id ".
			"where p1.is_enabled = 1 and p1.is_restricted = 1 and p3.allow_access = 1 ".($where ? " AND ".$where : '')." ".
			"group by p1.id ".
			"order by p1.queue "
		);
	}

	function recursive_menu($add_sql = '', $execute = false){

		$this->LINE = __FILE__.' '.__LINE__;
		return $this->Select(
			"SELECT p2.*, p1.*, DATE_FORMAT(p1.active_at, '%d.%m.%Y') AS active_at, DATE_FORMAT(p1.un_active_at, '%d.%m.%Y') AS un_active_at, CONCAT(p2.name, ' - ', page) AS model,
			CASE WHEN LENGTH(p1.position) > 0 THEN p1.position ELSE (SELECT px.position FROM menus AS px WHERE px.id = p1.parent_id LIMIT 1) END AS menu_position,
			p2.name AS model_name, p2.position as content_position,
			CASE WHEN
				p1.queue = (select min(queue) from menus where parent_id=p1.parent_id and is_public =1 and language='".$_SESSION['language']."' group by parent_id) THEN
				1 ELSE
				0 END as is_first,
			CASE WHEN
				p1.queue = (select max(queue) from menus where parent_id=p1.parent_id and is_public =1 and language='".$_SESSION['language']."' group by parent_id) THEN
				1 ELSE
				0 END as is_last,
			CASE WHEN
				(select count(id) from menus where parent_id=p1.parent_id and is_public=1 and language='".$_SESSION['language']."' group by parent_id) > 1 THEN
				0 ELSE
				1 END as is_only,
			CASE WHEN p1.url THEN p1.url ELSE (

				SELECT CASE WHEN p2_1.parent_id > 0 THEN CONCAT('?model=', p2_2.name, '&page=', p2_2.page) ELSE CONCAT('?model=pages', '&page=', p2_1.name) END AS url FROM `templates` as p2_1
				LEFT JOIN `templates` as p2_2 on p2_2.id = p2_1.parent_id
				WHERE p2_1.id = p1.id

			) END as base_url
			FROM menus as p1 ".
			"JOIN templates AS p2 ON CASE WHEN p1.model_id > 1 THEN p2.id = p1.model_id ELSE p2.id = p1.template_id END ".
		$add_sql,
		$execute
		);
	}

	function public_menu($position, $where='', $order){

		$this->LINE = __FILE__.' '.__LINE__;
		return $this->Select("SELECT p1.*,
		CASE WHEN
			p1.to_next_menu = 1 AND (SELECT id FROM menus WHERE parent_id = p1.id ORDER BY queue DESC LIMIT 1 ) IS NOT NULL THEN
			(SELECT id FROM menus WHERE parent_id = p1.id ORDER BY queue DESC LIMIT 1 ) ELSE
			p1.id
		END AS menu_id,
		CASE WHEN
			p1.to_next_menu = 1 AND (SELECT id FROM menus WHERE parent_id = p1.id ORDER BY queue DESC LIMIT 1 ) IS NOT NULL THEN
			(SELECT name FROM menus WHERE parent_id = p1.id ORDER BY queue DESC LIMIT 1 ) ELSE
			p1.name
		END AS url_menu_name,
		p4.name as model,
		p4.page AS page,
		p4.is_first_page
		FROM ".$this->table." p1 ".
			"LEFT JOIN `templates` as p4 on CASE WHEN p1.model_id > 1 THEN p4.id = p1.model_id ELSE p4.id = p1.template_id END ".
			"LEFT JOIN permissions as p5 on p5.table_name='templates' and p5.table_id=p4.id ".
			"WHERE p1.is_public = 1 and p1.is_enabled = 1 and p1.language = '".$_SESSION['language']."' ".($position ? "and p1.position = '".$position."' " : "")." and
			CASE WHEN
				p1.template_id > 0 THEN
				p5.role_id = ".(is_numeric($_SESSION['UserData']['role_id']) ? $_SESSION['UserData']['role_id'] : 1)." ELSE
				p1.template_id = 0
			END AND
			CASE WHEN
				p1.active_at IS NOT NULL THEN
				p1.active_at >= NOW() ELSE
				p1.active_at IS NULL
			END AND
			CASE WHEN
				p1.un_active_at IS NOT NULL THEN
				p1.un_active_at <= NOW() ELSE
				p1.un_active_at IS NULL
			END ".
			($where ? " AND ".$where." " : "").
			"ORDER BY p1.queue ".$order);
	}

	function menu_and_other_language($add_sql = '', $execute = false){

		$this->LINE = __FILE__.' '.__LINE__;
		return $this->Select(
			"SELECT p1.* FROM menus  p1 ".
			"JOIN menus p2 ON p2.name = p1.name ".
		$add_sql,
		$execute
		);
	}
}
?>