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

function SQL_Get_NewsBoard($nb) {
  $query= "SELECT news_id, news_title, to_char(news_published_date, '".toStringSqlDate('long')."') AS news_published_date_display FROM " . T_NEWS . " WHERE news_statut = 'P' ORDER BY news_published_date DESC OFFSET 0 LIMIT ".$nb.";";

  return $query;
}
function SQL_Get_SdiBoard() {

  $query= "SELECT DISTINCT I.sdii_name, V.sdiv_id, I.sdii_id, V.sdiv_scale, V.sdiv_date_crea, MAX(sdiv_date_crea) AS date_c_max
			FROM " . T_SDI_INFO . " as I LEFT OUTER JOIN " . T_SDI_VALUE . " as V ON I.sdii_id=V.sdiv_sdi_info
			WHERE sdiv_statut='P' AND sdii_statut='P' GROUP BY I.sdii_name, I.sdii_id, V.sdiv_scale, V.sdiv_id, V.sdiv_date_crea ORDER BY date_c_max DESC;";

  return $query;
}
function SQL_Get_FilterTheme() {
  $query = "SELECT * FROM " . T_SD_THEME . " ORDER BY sdtheme_range, sdtheme_id;";
  return $query;
}

function SQL_Get_FilterPriority() {
  $query = "SELECT * FROM " . T_PRIORITY . " ORDER BY priority_id;";
  return $query;
}

function SQL_Get_FilterScale($mask=-1) {
  if ($mask<>-1) $mask="OR scale_statut='I'";
  else $mask="";
  $query= "SELECT * FROM " . T_SCALE . " WHERE scale_statut='P' ".$mask." ORDER BY scale_dependencies ASC, scale_denomination ASC;";
  return $query;
}
function SQL_Get_FilterLevel() {
  $query = "SELECT * FROM " . T_LEVEL . " WHERE level_statut='P' ORDER BY level_range DESC;";
  return $query;
}

function SQL_Get_UserWorkshop($login, $pass) {
  $query = "SELECT user_id ,  jwu_workshop_id FROM " . T_USER . " As U LEFT OUTER JOIN " . J_WORK_USERS . " As W ON U.user_id=W.jwu_user_id WHERE lower(user_login) = '".strtolower($login)."' AND user_password='".$pass."' AND user_validity='Y';";
  return $query;
}

function SQL_Get_LastPublications($nb) {
  $query = "SELECT publi_id, publi_title, publi_resume  FROM  " . T_PUBLI . " WHERE publi_statut = 'P' ORDER BY publi_published_date DESC OFFSET 0 LIMIT ".$nb.";";
  return $query;
}

function SQL_getRSSformatedNews() {
  $query = 	"SELECT news_id, news_title, substring(news_header || ' ' || news_body from 0 for 247) || '...' AS news_description, theme_name, ".
				"to_char(news_published_date, 'YYYY-MM-DD') || 'T' || to_char(news_published_date, 'HH24:MI:SS+00:00') AS news_published_date_display, ".
				"to_char(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display, user_login ".
				"FROM " . T_NEWS . " ".
				"LEFT OUTER JOIN " . T_THEME. " ON news_theme=theme_id ".
				"LEFT OUTER JOIN " . T_USER. " ON news_posted_by=user_id ".
				" WHERE news_statut='P' ".
				"ORDER BY news_published_date DESC OFFSET 0 LIMIT 15;";
  return $query;
}

function SQL_getRSS2formatedNews() {

  $query = 	"SELECT news_id, news_title, news_header, news_body, ".
				"to_char(news_published_date, 'YYYY-MM-DD') || 'T' || to_char(news_published_date, 'HH24:MI:SS+00:00') AS news_published_date_display, ".
				"to_char(news_date_crea, 'YYYY-MM-DD') AS news_date_crea_display, user_login, theme_name ".
				"FROM " . T_NEWS . " ".
				"LEFT OUTER JOIN " . T_THEME. " ON news_theme=theme_id ".
				"LEFT OUTER JOIN " . T_USER. " ON news_posted_by=user_id ".
				"WHERE news_statut <> 'E' ".
				"AND news_published_date <> '0001-01-01' ".
				"ORDER BY news_published_date DESC OFFSET 0 LIMIT 15;";
  return $query;
}
?>