<?php
/**
 * Front-office specific SQL queries
 * 
 * MySQL queries related to front-office
 * This file can be overriden. Discover logic and examples on :
 * @url http://dev.linea21.com/projects/linea21/wiki/Override
 * @url http://dev.linea21.com/projects/linea21/wiki/Personnalisation_de_l%27interface_publique
 * 
 * @package linea21\core\public
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

function SQLget_WorkshopList($nb) {
  $query = 	"SELECT W.* FROM ".T_WORK. " W WHERE workshop_statut='P' ORDER BY workshop_range ASC, workshop_denomination ASC LIMIT 0, ".$nb.";";
  return $query;
}

function SQLget_pastEvents($nb) {
  $query = 	"SELECT DATE_FORMAT(workcal_task_date, '".toStringSqlDate()."') AS task_date, workcal_id, workcal_task, workcal_workshop_id,
				workcal_task_date, workshop_denomination
				FROM ".T_WORK_CAL ."
				LEFT OUTER JOIN  ".T_WORK. " on workcal_workshop_id=workshop_id
				WHERE workcal_task_date < NOW() ORDER BY workcal_task_date DESC LIMIT 0, ".$nb.";";

  return $query;
}

function SQLget_incomingEvents($nb) {
  $query = 	"SELECT DATE_FORMAT(workcal_task_date, '".toStringSqlDate()."') AS task_date, workcal_id, workcal_task, workcal_workshop_id,
				workcal_task_date, workshop_denomination
				FROM ".T_WORK_CAL ."
				LEFT OUTER JOIN  ".T_WORK. " on workcal_workshop_id=workshop_id
				WHERE workcal_task_date >= NOW() ORDER BY workcal_task_date ASC LIMIT 0, ".$nb.";";

  return $query;
}

function SQLget_Events($nb) {
	$query = 	"SELECT DATE_FORMAT(workcal_task_date, '".toStringSqlDate()."') AS task_date, workcal_id, workcal_task, workcal_workshop_id,
	workcal_task_date, workshop_denomination
	FROM ".T_WORK_CAL ."
	LEFT OUTER JOIN  ".T_WORK. " on workcal_workshop_id=workshop_id
	ORDER BY workcal_task_date DESC LIMIT 0, ".$nb.";";

	return $query;
}

function SQL_Get_WorkshopsNames($array = array()) {

  $str='';
  $sep='';
  if(empty($array)) $str = '1 = 1';
  else {
    foreach($array as $v) {
      $str .= $sep. 'workshop_id='.$v;
      $sep = ' OR ';
    }
  }
  $query = "SELECT workshop_denomination, workshop_id, workshop_range, workshop_restricted, workshop_visible FROM ". T_WORK ." WHERE (". $str .") AND workshop_statut='P' ORDER BY workshop_range ASC, workshop_denomination ASC";

  return $query;
}

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

  return $query;
}
function SQL_Get_SdiBoard($nb) {

  $query= "SELECT DISTINCT I.sdii_name, sdiv_id, sdii_id, sdiv_scale, 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, sdiv_scale ORDER BY date_c_max DESC LIMIT 0, ".$nb.";";
  //sdiv_sdi_info,,  sdiv_scale
  return $query;
}

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

function SQL_Get_ScalesNumber($status = array('P')) {
    
    $qstatus = '';
    
    if(isset($status)) {
        for($i=0; $i < count($status); $i++) {
            if($i != 0) $prefix = ' OR '; else $prefix='(';
            $qstatus .= $prefix."scale_statut='" . $status[$i] . "'";
        }
        $qstatus .= ')';
    } else {
        $qstatus .="scale_statut='".$status[0]."'";
    }
    $query= "SELECT count(scale_id) as nb FROM " . T_SCALE . " WHERE ".$qstatus.";";
    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_range ASC, scale_dependencies ASC, scale_denomination ASC;";
  return $query;
}

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

function SQL_getRSS2formatedNews( $tag ) {
    
    if(!is_null($tag) && is_numeric($tag)) $tag_filter = " AND jta_module = 'news' AND jta_tag_id = '".$tag."' ";
    else $tag_filter = '';
    
  $query = 	"SELECT news_id, news_title, news_header, news_body, news_published_date, ".
				"DATE_FORMAT(news_published_date, '".toStringSqlDate()."') AS news_published_date_display,".
				"DATE_FORMAT(news_date_crea, '".toStringSqlDate()."') AS news_date_crea_display, user_login, level_name ".
				"FROM " . T_NEWS . " ".
				"LEFT OUTER JOIN " . T_LEVEL. " ON news_level=level_id ".
				"LEFT OUTER JOIN " . T_USER. " ON news_posted_by=user_id ".
				"LEFT OUTER JOIN ".J_TAG." on news_id = jta_module_id ".
				"WHERE news_statut <> 'E' ".
				"AND news_published_date <> '0001-01-01' ".
				$tag_filter .
				"ORDER BY news_published_date DESC LIMIT 0 , 20;";

  return $query;
}
?>