<?php
/**
 * News module
 * 
 * MySQL queries related to news module
 * 
 * @package linea21\modules\news
 * @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');
}
////////////


$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_scale = "SELECT scale_id, scale_denomination, scale_root FROM " . T_SCALE . " WHERE scale_statut<>'E' ORDER BY scale_root DESC, scale_range DESC, scale_denomination ASC;";

$req_quickbox_add = "SELECT news_id, news_date_crea, 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, news_last_modify, 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, news_last_modify, 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, news_published_date, 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_sticky, N.news_posted_by, ".
				"N.news_level, N.news_scale, L.level_name, L.level_id, L.level_label, S.scale_denomination, 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, ".
				"DATE_FORMAT(N.news_published_date, '".toStringSqlDate('long')."') AS news_published_date_display_long ".
				"FROM " . T_NEWS . " AS  N ".
				"LEFT OUTER JOIN ".T_LEVEL." AS L on N.news_level= L.level_id ".
				"LEFT OUTER JOIN ".T_SCALE." AS S on N.news_scale=S.scale_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_sticky DESC, news_range ASC, news_published_date DESC";
      break;
    case 'draft':
      $mask=" WHERE news_statut = 'D'";
      $orderby="news_sticky DESC, news_range ASC, news_date_crea DESC";
      break;
    case 'archives_a':
      $mask=" WHERE news_statut = 'AA'";
      $orderby="news_sticky DESC, news_range ASC, news_date_crea DESC";
      break;
    case 'archives_p':
      $mask=" WHERE news_statut = 'PA'";
      $orderby="news_sticky DESC, news_range ASC, news_date_crea DESC";
      break;
    default:
      $mask=" WHERE news_statut <> 'E'";
      $orderby="news_sticky DESC, news_range ASC, news_date_crea DESC";
      break;
  }
  switch ($filter) {
  	case 'LEVEL':
    	if(is_array($id_filter)) {
     		$mask.=" AND ". sql_status_filter('news_level', $id_filter);
    	} else {
    		$mask.=" AND news_level = " . $id_filter;
    	}
    	break;
    case 'SCALE':
      $mask.=" AND news_scale = '".$id_filter."'";
      break;
    case 'TAG':
      	$mask.=" AND jta_module = 'news' AND jta_tag_id = '".$id_filter."'";
      	break;
    default:
      $mask.="";
      break;
  }
  $q= "SELECT DISTINCT(news_id), LEFT(news_title, 60) AS news_title, news_title AS complete_news_title, ".
			  "news_header, news_body, news_scale, news_sticky, news_statut, L.level_id, L.level_name, ".
			  "news_scale, news_range, news_date_crea, news_published_date, scale_denomination, 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_published_date, '".toStringSqlDate('long')."') AS news_published_date_display_long, ".
			  "DATE_FORMAT(news_last_modify, '".toStringSqlDate()."') AS news_last_modify_display ".
			  "FROM " . T_NEWS ." ".
			  "LEFT OUTER JOIN ".T_LEVEL." AS L on news_level=L.level_id ".
			  "LEFT OUTER JOIN ".T_SCALE." AS S on news_scale=S.scale_id ".
			  "LEFT OUTER JOIN ".T_USER." AS U on news_posted_by=U.user_id ".
			  "LEFT OUTER JOIN ".J_TAG." on news_id = jta_module_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 'LEVEL':
    	if(is_array($id_filter)) {
     		$mask.=" AND ". sql_status_filter('news_level', $id_filter);
    	} else {
    		$mask.=" AND news_level = " . $id_filter;
    	}
    	break;
    case 'SCALE':
      $mask.=" AND news_scale = '".$id_filter."'";
      break;
    default:
      $mask.="";
      break;
  }
  $q=	"SELECT COUNT(news_id) AS num_rows FROM " . T_NEWS ." ";
  if($filter=='LEVEL') {
    $q.=	"LEFT OUTER JOIN ".T_LEVEL." AS L on news_level=L.level_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, news_range, news_published_date 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, news_range, news_published_date 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, news_published_date 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, news_published_date 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_getonescaleDenomination($scale_id) {
  $q="SELECT scale_denomination FROM " . T_SCALE . " WHERE scale_id='".$scale_id."';";
  return $q;
}

?>