<?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 (!class_exists('auth')){
  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, LEFT(news_title, 50) as news_title, DATE_FORMAT(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 0 , 10;";
$req_quickbox_sup = "SELECT news_id,  LEFT(news_title, 50) as news_title, DATE_FORMAT(news_last_modify, '".toStringSqlDate('long')."') AS news_last_modify_display, DATE_FORMAT(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display FROM " . T_NEWS . " WHERE news_statut = 'E' ORDER BY news_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_mod = "SELECT news_id, LEFT(news_title, 50) as news_title, DATE_FORMAT(news_last_modify, '".toStringSqlDate('long')."') AS news_last_modify_display, DATE_FORMAT(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display FROM " . T_NEWS . " WHERE news_statut  <> 'E' ORDER BY news_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_list = "SELECT news_id,  LEFT(news_title, 50) as news_title, DATE_FORMAT(news_published_date, '".toStringSqlDate('long')."') AS news_published_date_display, DATE_FORMAT(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 0 , 10;";

function SQL_getLatestNews() {
	
	$q = "SELECT * FROM  " . T_NEWS . " WHERE news_statut = 'P' ORDER BY news_range ASC, news_published_date DESC LIMIT 0,1;";
	
	return $q;
}

function SQL_getoneCompleteNews($news_id, $force_status = false) {
	
	if($force_status) $force_status = " AND ". sql_status_filter('news_statut', $force_status);
	else $force_status = "";
	
  $q =	"SELECT N.news_title, N.news_header, N.news_body, 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, ".
				"DATE_FORMAT(N.news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display, ".
				"DATE_FORMAT(N.news_last_modify, '".toStringSqlDate('long')."') AS news_last_modify_display, ".
				"DATE_FORMAT(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."'". $force_status.";";

  return $q;
}


function SQL_getNewsList($debut, $limite, $status, $filter=-1, $id_filter=-1) {
  switch ($status) {
    case 'public':
      $mask=" WHERE news_statut = 'P'";
      $orderby="news_range ASC, news_published_date DESC";
      break;
    case 'draft':
      $mask=" WHERE news_statut = 'D'";
      $orderby="news_range ASC, news_date_crea DESC";
      break;
    case 'archives_a':
      $mask=" WHERE news_statut = 'AA'";
      $orderby="news_range ASC, news_date_crea DESC";
      break;
    case 'archives_p':
      $mask=" WHERE news_statut = 'PA'";
      $orderby="news_range ASC, news_date_crea DESC";
      break;
    default:
      $mask=" WHERE news_statut <> 'E'";
      $orderby="news_range ASC, 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;
  }
  $q= "SELECT news_id, LEFT(news_title, 60) AS news_title, news_title AS complete_news_title, ".
			  "news_header, news_body, 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, ".
			  "DATE_FORMAT(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display, ".
			  "DATE_FORMAT(news_published_date, '".toStringSqlDate()."') AS news_published_date_display, ".
			  "DATE_FORMAT(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." LIMIT ".$debut." , ".$limite.";";

  return $q;
}

function SQL_getCountNewsList($status, $filter=-1, $id_filter=-1) {
  switch ($status) {
    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;
  }
  $q=	"SELECT COUNT(news_id) AS num_rows FROM " . T_NEWS ." ";
  if($filter=='THEME') {
    $q.=	"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 ";
  }
  $q.=$mask.";";
  return $q;
}

function SQL_getNewsRangeAndDate($news_id) {
    $q =	"SELECT news_range, news_published_date FROM " . T_NEWS . " WHERE news_id='".$news_id."';";
  return $q;
}

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

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

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

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


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

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

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

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

?>