<?php
/**
 * Level module
 * 
 * MySQL queries related to level module
 * 
 * @package linea21\core\level
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

// ////////// Check Inclusion de pages ////////////
if (! class_exists ( 'auth' )) {
	include_once ("../lib/lib_common.php");
	ReloadIndex ( 'admin' );
}
// //////////


function SQL_getLevelListQ($action = 'mod') {
	if ($action == 'del') {
		$filter = "level_status  = 'E'";
		$orderby = "level_last_modify DESC";
	}
	if ($action == 'add') {
		$filter = "level_status  <> 'E'";
		$orderby = "level_date_crea DESC";
	}
	if ($action == 'mod') {
		$filter = "level_status  <> 'E'";
		$orderby = "level_last_modify DESC";
	}
	
	$q = "SELECT level_id, level_name, level_code, level_label, level_date_crea, level_last_modify, DATE_FORMAT(level_date_crea, '" . toStringSqlDate () . "') AS level_date_crea_display, DATE_FORMAT(level_last_modify, '" . toStringSqlDate ( 'long' ) . "') AS level_last_modify_display FROM " . T_LEVEL . " WHERE " . $filter . " ORDER BY " . $orderby . " LIMIT 0 , 10;";
	
	return $q;
}


function SQL_getLevelsList( $only_first_level = false) {
	
	$only_first_level ? $filter = 'AND level_parent=0' : $filter = '';
	
	$q = sprintf("SELECT * FROM " . T_LEVEL . " WHERE level_status<>'E' %s ORDER BY level_parent DESC, level_range DESC, level_name ASC;", $filter);
	
	return $q;
}

/**
 * Display Parents with children
 * DO not remove
 *
 * @return string
 */
function SQL_getLevelParents2() {
	return "SELECT level_id, level_name, level_code, level_label, @pv:=level_parent as 'level_parent' from " . T_LEVEL . " JOIN (SELECT @pv:=1)tmp WHERE level_id=@pv ORDER BY level_parent DESC, level_range DESC, level_name ASC;";
}

function SQL_getLevelParent($id) {
	return "SELECT level_id, level_name, level_code, level_label from " . T_LEVEL . " WHERE level_id=".$id.";";
}

function SQL_getoneLevel($item_id) {
	$q = "SELECT T.level_id, T.level_name, T.level_code, T.level_label, T.level_desc_project, T.level_desc_dashboard, T.level_desc_publication, T.level_desc_news, T.level_desc_workshop, T.level_status, DATE_FORMAT(T.level_date_crea, '" . toStringSqlDate () . "') AS level_date_crea, DATE_FORMAT(T.level_last_modify, '" . toStringSqlDate ( 'long' ) . "') AS level_last_modify, SDT.level_name as parent_name, SDT.level_label as parent_label, SDT.level_id as parent_id FROM " . T_LEVEL . " AS T LEFT OUTER JOIN " . T_LEVEL . " AS SDT ON  T.level_parent = SDT.level_id WHERE T.level_id='" . $item_id . "' LIMIT 1;";
	return $q;
}


function SQL_getNewsByLevel($level_id)
{
	$q = 'SELECT * FROM '. T_NEWS .' WHERE  news_statut <> \'E\' AND news_level = '.$level_id.' ORDER BY news_range ASC, news_published_date DESC';
	return $q;
}

function SQL_getProjectsByLevel($level_id)
{
	$q = 'SELECT * FROM '. T_PROJECT .' WHERE project_statut <> \'E\' AND project_level_id = '.$level_id.' ORDER BY project_range ASC, project_published_date DESC';
	return $q;
}

function SQL_getPublicationsByLevel($level_id)
{
	$q = 'SELECT * FROM '. T_PUBLI .' WHERE publi_statut <> \'E\' AND publi_level = '.$level_id.' ORDER BY publi_range ASC, publi_published_date DESC';
	return $q;
}

function SQL_getWorkshopsByLevel($level_id)
{
	$q = 'SELECT * FROM '. T_WORK .' WHERE workshop_statut <> \'E\' AND workshop_level = '.$level_id.' ORDER BY workshop_range ASC, workshop_date_crea DESC';
	return $q;
}

function SQL_getIndicatorsByLevel($level_id)
{
	$q = 'SELECT * FROM '. T_SDI_INFO .' WHERE sdii_statut <> \'E\' AND sdii_level = '.$level_id.' ORDER BY sdii_name ASC, sdii_date_crea DESC';
	return $q;
}

function SQL_getDirectoryEntriesByLevel($level_id)
{
	$q = 'SELECT * FROM '. T_YELLOWPAGES .' WHERE yellowp_statut <> \'E\' AND yellowp_level = '.$level_id.' ORDER BY yellowp_name ASC, yellowp_date_crea DESC';
	return $q;
}

?>