<?php
/**
 * @package linea21.modules
 * @subpackage news
 * @author linea21 <info@linea21.com>
 * @version $id SVN
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

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


$req_max_news_id = "SELECT MAX(news_id) AS maxid FROM " . T_NEWS . ";";

$req_count_news = "SELECT COUNT(news_id) as num_rows FROM " . T_NEWS . " WHERE news_statut<>'E';";


$req_list_theme = "SELECT T.theme_id, T.theme_name, T.theme_statut, SDT.sdtheme_name, SDT.sdtheme_id FROM " . T_THEME . " AS T LEFT OUTER JOIN ". T_SD_THEME ." AS SDT ON SDT.sdtheme_id=T.theme_sd_theme WHERE T.theme_statut  <> 'E' ORDER BY sdtheme_range DESC, sdtheme_name DESC, theme_name ASC;";
$req_list_level = "SELECT level_id, level_name FROM " . T_LEVEL. " WHERE level_statut<>'E' ORDER BY level_range DESC;";
$req_list_scale = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' ORDER BY scale_denomination ASC;";

$req_quickbox_add = "SELECT news_id, substring(news_title from 0 for 50) as news_title, to_char(news_date_crea, '".toStringSqlDate('long')."') AS news_date_crea_display FROM " . T_NEWS . " WHERE news_statut  = 'P' OR news_statut = 'D'  ORDER BY news_date_crea DESC LIMIT 10 OFFSET 0;";
$req_quickbox_sup = "SELECT news_id,  substring(news_title from 0 for 50) as news_title, to_char(news_last_modify, '".toStringSqlDate('long')."') AS news_last_modify_display, to_char(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display FROM " . T_NEWS . " WHERE news_statut = 'E' ORDER BY news_last_modify DESC LIMIT 10 OFFSET 0;";
$req_quickbox_mod = "SELECT news_id, substring(news_title from 0 for 50) as news_title, to_char(news_last_modify, '".toStringSqlDate('long')."') AS news_last_modify_display, to_char(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display FROM " . T_NEWS . " WHERE news_statut  <> 'E' ORDER BY news_last_modify DESC LIMIT 10 OFFSET 0;";
$req_quickbox_list = "SELECT news_id,  substring(news_title from 0 for 50) as news_title, to_char(news_published_date, '".toStringSqlDate('long')."') AS news_published_date_display, to_char(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display FROM " . T_NEWS . " WHERE news_statut <> 'E' AND  news_published_date <> '0001-01-01' ORDER BY news_published_date DESC LIMIT 10 OFFSET 0;";


function SQL_getoneCompleteNews($news_id) {
	$requete =	"SELECT N.news_title, N.news_header, N.news_body, N.news_template, N.news_photo_uri, N.news_statut, N.news_posted_by, ".
				"N.news_theme, N.news_scale, N.news_level, T.theme_name, T.theme_sd_theme, SDT.sdtheme_name, S.scale_denomination, L.level_name, U.user_login, ".
				"to_char(N.news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display, ".
				"to_char(N.news_last_modify, '".toStringSqlDate('long')."') AS news_last_modify_display, ".
				"to_char(N.news_published_date, '".toStringSqlDate()."') AS news_published_date_display ".
				"FROM " . T_NEWS . " AS  N ".
				"LEFT OUTER JOIN ".T_THEME." AS T on N.news_theme=T.theme_id ".
				"LEFT OUTER JOIN ".T_SD_THEME." AS SDT on T.theme_sd_theme=SDT.sdtheme_id ".
				"LEFT OUTER JOIN ".T_SCALE." AS S on N.news_scale=S.scale_id ".
				"LEFT OUTER JOIN ".T_LEVEL." AS L on N.news_level=L.level_id ".
				"LEFT OUTER JOIN ".T_USER." AS U on N.news_posted_by=U.user_id ".
				"WHERE news_id='".$news_id."' AND news_statut<>'N';";

	return $requete;
}


function SQL_getNewsList($debut, $limite, $statut, $filter=-1, $id_filter=-1) {
	switch ($statut) {
		case 'public':
			$mask=" WHERE news_statut = 'P'";
			$orderby="news_published_date DESC";
			break;
		case 'draft':
			$mask=" WHERE news_statut = 'D'";
			$orderby="news_date_crea DESC";
			break;
		case 'archives_a':
			$mask=" WHERE news_statut = 'AA'";
			$orderby="news_date_crea DESC";
			break;
		case 'archives_p':
			$mask=" WHERE news_statut = 'PA'";
			$orderby="news_date_crea DESC";
			break;
		default:
			$mask=" WHERE news_statut <> 'E'";
			$orderby="news_date_crea DESC";
			break;
		}
	switch ($filter) {
		case 'THEME':
			$mask.=" AND theme_sd_theme = '".$id_filter."'";
			break;
		case 'SCALE':
			$mask.=" AND news_scale = '".$id_filter."'";
			break;
		case 'LEVEL':
			$mask.=" AND news_level = '".$id_filter."'";
			break;
		default:
			$mask.="";
			break;
		}
	$requete= "SELECT news_id, substring(news_title from 0 for 60) AS news_title, news_title AS complete_news_title, ".
			  "news_header, news_level, news_scale, news_statut, SDT.sdtheme_id, SDT.sdtheme_name, ".
			  "news_scale, scale_denomination, news_level, level_name, news_posted_by, user_login, ".
			  "to_char(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display, ".
			  "to_char(news_published_date, '".toStringSqlDate()."') AS news_published_date_display, ".
			  "to_char(news_last_modify, '".toStringSqlDate()."') AS news_last_modify_display ".
			  "FROM " . T_NEWS ." ".
			  "LEFT OUTER JOIN ".T_THEME." AS T on news_theme=T.theme_id ".
			  "LEFT OUTER JOIN ".T_SD_THEME." AS SDT on theme_sd_theme=SDT.sdtheme_id ".
			  "LEFT OUTER JOIN ".T_SCALE." AS S on news_scale=S.scale_id ".
			  "LEFT OUTER JOIN ".T_LEVEL." AS L on news_level=L.level_id ".
			  "LEFT OUTER JOIN ".T_USER." AS U on news_posted_by=U.user_id ".
			  
			  $mask." ORDER BY ".$orderby." OFFSET ".$debut." LIMIT ".$limite.";";

	return $requete;
}

function SQL_getCountNewsList($statut, $filter=-1, $id_filter=-1) {
	switch ($statut) {
		case 'public':
			$mask="WHERE news_statut = 'P'";
			break;
		case 'draft':
			$mask="WHERE news_statut = 'D'";
			break;
		case 'archives_a':
			$mask="WHERE news_statut = 'AA'";
			break;
		case 'archives_p':
			$mask="WHERE news_statut = 'PA'";
			break;
		default:
			$mask="WHERE news_statut <> 'E'";
			break;
		}
	switch ($filter) {
		case 'THEME':
			$mask.=" AND theme_sd_theme = '".$id_filter."'";
			break;
		case 'SCALE':
			$mask.=" AND news_scale = '".$id_filter."'";
			break;
		case 'LEVEL':
			$mask.=" AND news_level = '".$id_filter."'";
			break;
		default:
			$mask.="";
			break;
		}
	$requete=	"SELECT COUNT(news_id) AS num_rows FROM " . T_NEWS ." ";
	if($filter=='THEME') {
		$requete.=	"LEFT OUTER JOIN ".T_THEME." AS T on news_theme=T.theme_id ".
					"LEFT OUTER JOIN ".T_SD_THEME." AS SDT on theme_sd_theme=SDT.sdtheme_id ";
	}
	$requete.=$mask.";";
	return $requete;
}

function SQL_getPreviousNews($news_id, $statut) {
	switch ($statut) {
		case 'public':
			$statut='P';
			$orderby="news_published_date DESC";
			break;
		case 'draft':
			$statut='D';
			$orderby="news_published_date DESC";
			break;
		case 'archives_a':
			$statut='AA';
			$orderby="news_published_date DESC";
			break;
		case 'archives_p':
			$statut='PA';
			$orderby="news_published_date DESC";
			break;
		default:
			$statut='P';
			$orderby="news_published_date DESC";
			break;
	}
	$requete =	"SELECT news_title, news_id FROM " . T_NEWS . " WHERE news_id<'".$news_id."' AND news_statut = '".$statut."' ORDER BY ".$orderby." LIMIT 1 OFFSET 0;";
	return $requete;
}

function SQL_getNextNews($news_id, $statut) {
	switch ($statut) {
		case 'public':
			$statut='P';
			$orderby="news_published_date ASC";
			break;
		case 'draft':
			$statut='D';
			$orderby="news_published_date DESC";
			break;
		case 'archives_a':
			$statut='AA';
			$orderby="news_published_date DESC";
			break;
		case 'archives_p':
			$statut='PA';
			$orderby="news_published_date DESC";
			break;
		default:
			$statut='P';
			$orderby="news_published_date DESC";
			break;
	}
	$requete =	"SELECT news_title, news_id FROM " . T_NEWS . " WHERE news_id>'".$news_id."' AND news_statut = '".$statut."' ORDER BY ".$orderby." LIMIT 1 OFFSET 0;";
	return $requete;
}


function SQL_getNewsTitle($news_id) {
	$requete =	"SELECT N.news_title, N.news_posted_by FROM " . T_NEWS . " AS  N WHERE news_id='".$news_id."';";
	return $requete;
}

function SQL_getonethemeName($theme_id) {
	$requete ="SELECT theme_name FROM " . T_THEME . " WHERE theme_id='".$theme_id."';";
	return $requete;
}

function SQL_getonescaleDenomination($scale_id) {
	$requete="SELECT scale_denomination FROM " . T_SCALE . " WHERE scale_id='".$scale_id."';";
	return $requete;
}

function SQL_getonelevelName($level_id) {
	$requete="SELECT level_name FROM " . T_LEVEL . " WHERE level_id='".$level_id."';";
	return $requete;
}

?>