<?php
/**
 * @package linea21.modules
 * @subpackage workshop
 * @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(publi_id) AS maxid FROM " . T_WORK . ";";

$req_count_workshop = "SELECT COUNT(workshop_id) as num_rows FROM " . T_WORK . " WHERE workshop_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 workshop_id, substring(workshop_denomination from 1 for 50) as workshop_denomination, to_char(workshop_date_crea, '".toStringSqlDate('long')."') AS workshop_date_crea_display FROM " . T_WORK . " WHERE workshop_statut  = 'P' OR workshop_statut = 'D'  ORDER BY workshop_date_crea DESC OFFSET 0 LIMIT 10;";
$req_quickbox_mod = "SELECT workshop_id, substring(workshop_denomination from 1 for 50) as workshop_denomination, to_char(workshop_last_modify, '".toStringSqlDate('long')."') AS workshop_last_modify_display, to_char(workshop_date_crea, '".toStringSqlDate()."') AS workshop_date_crea_display FROM " . T_WORK . " WHERE workshop_statut  <> 'E' ORDER BY workshop_last_modify DESC OFFSET 0 LIMIT 10;";
$req_quickbox_add_noadmin = "SELECT workshop_id, substring(workshop_denomination from 1 for 50) as workshop_denomination, to_char(workshop_date_crea, '".toStringSqlDate('long')."') AS workshop_date_crea_display FROM " . T_WORK . " WHERE workshop_statut  = 'P'  ORDER BY workshop_date_crea DESC OFFSET 0 LIMIT 10;";
$req_quickbox_mod_noadmin = "SELECT workshop_id, substring(workshop_denomination from 1 for 50) as workshop_denomination, to_char(workshop_last_modify, '".toStringSqlDate('long')."') AS workshop_last_modify_display, to_char(workshop_date_crea, '".toStringSqlDate()."') AS workshop_date_crea_display FROM " . T_WORK . " WHERE workshop_statut  = 'P' ORDER BY workshop_last_modify DESC OFFSET 0 LIMIT 10;";

function SQL_getWorkshopList($debut, $limite = SELECT_LIMIT, $filter= -1, $id_filter=-1) {
  if ($debut<>-1) $mask_limit="OFFSET ".$debut." LIMIT ".$limite;
  else $mask_limit="";
  switch ($filter) {
    case 'public':
      $mask=" WHERE workshop_statut = 'P'";
      break;
    case 'draft':
      $mask=" WHERE workshop_statut = 'D'";
      break;
    case 'archives_a':
      $mask=" WHERE workshop_statut = 'AA'";
      break;
    case 'archives_p':
      $mask=" WHERE workshop_statut = 'AP'";
      break;
    case 'theme':
      $mask=" WHERE workshop_statut = 'P' AND SD.sdtheme_id =".$id_filter;
      break;
    case 'niveau':
      $mask=" WHERE workshop_statut = 'P' AND workshop_level=".$id_filter;
      break;
    default:
      $mask=" WHERE workshop_statut <> 'E'";
      break;
  }
  //if($filter== -1) $mask=" WHERE workshop_statut <> 'E'";
  $query=	"SELECT W.workshop_id, W.workshop_denomination, SD.sdtheme_name, SD.sdtheme_id, L.level_name, workshop_level, W.workshop_resume, W.workshop_statut, to_char(W.workshop_date_crea, '".toStringSqlDate('long')."') AS workshop_date_crea_display FROM " . T_WORK." AS W".
	 " LEFT OUTER JOIN ". T_THEME. " AS T on W.workshop_theme=T.theme_id"
	 ." LEFT OUTER JOIN ". T_SD_THEME. " AS SD on T.theme_sd_theme=SD.sdtheme_id"
	 ." LEFT OUTER JOIN ". T_LEVEL. " AS L on W.workshop_level=L.level_id"
	 .$mask." ORDER BY workshop_date_crea DESC ".$mask_limit." ";
	 return $query;
}

function SQL_getcountnbcom($id) {
  $query= "SELECT COUNT(workcom_id) as num_rows FROM " . T_WORK_COM . " WHERE workcom_statut<>'E' AND workcom_parent=0 AND workcom_workshop_id=$id;";
  return $query;
}

function SQL_getWorkcomList($debut, $limite = SELECT_LIMIT, $id) {

  if ($debut<>-1) $mask_limit="OFFSET ".$debut." LIMIT ".$limite;
  else $mask_limit="";

  $mask=" WHERE workcom_statut <> 'E' AND workcom_parent=0 AND workcom_workshop_id=$id";
  $query=	"SELECT W.workcom_id, W.workcom_subject, U.user_login, W.workcom_user_id, W.workcom_statut, to_char(W.workcom_last_modify, '".toStringSqlDate('long')."') AS workcom_date_crea_display FROM "
  . T_WORK_COM." AS W LEFT OUTER JOIN ". T_USER. " AS U on W.workcom_user_id=U.user_id ".
  $mask." ORDER BY workcom_last_modify DESC ".$mask_limit." ";
  return $query;
}


function SQL_getWorkcom_detList($id) {
  $mask=" WHERE workcom_statut <> 'E'";
  $query=	"SELECT COUNT(W.workcom_id) AS nb_rep, MAX(to_char(W.workcom_date_crea, '".toStringSqlDate('long')."')) AS workcom_date_crea_display FROM " . T_WORK_COM." AS W ".
  $mask."
  AND W.workcom_parent=$id
  GROUP BY workcom_parent;";
  //echo $query;
  return $query;
}

function SQL_getOnetopicWorkcom($id, $com_id, $root= -1){

  if ($root<>-1) $mask=" WHERE workcom_statut <> 'E' AND workcom_id=$com_id  AND workcom_workshop_id=$id";
  else $mask=" WHERE workcom_statut <> 'E' AND workcom_parent=$com_id  AND workcom_workshop_id=$id";

  $query=	"SELECT W.workcom_id, W.workcom_subject, U.user_login, W.workcom_user_id, W.workcom_statut, W.workcom_body ,  to_char(W.workcom_date_crea, '".toStringSqlDate('long')."') AS workcom_date_crea_display FROM "
  . T_WORK_COM." AS W LEFT OUTER JOIN ". T_USER. " AS U on W.workcom_user_id=U.user_id".
  $mask." ORDER BY workcom_date_crea ASC;";

  return $query;

}

function SQL_getOnetopicWorkcom2($id, $com_id, $root= -1, $debut, $limite = SELECT_LIMIT){

  if ($root<>-1) $mask=" WHERE workcom_statut <> 'E' AND workcom_id=$com_id  AND workcom_workshop_id=$id";
  else $mask=" WHERE workcom_statut <> 'E' AND workcom_parent=$com_id  AND workcom_workshop_id=$id";

  $query=	"SELECT W.workcom_id, W.workcom_subject, U.user_login, W.workcom_user_id, W.workcom_statut, W.workcom_body ,  to_char(W.workcom_date_crea, '".toStringSqlDate('long')."') AS workcom_date_crea_display FROM "
  . T_WORK_COM." AS W LEFT OUTER JOIN ". T_USER. " AS U on W.workcom_user_id=U.user_id".
  $mask." ORDER BY workcom_date_crea ASC OFFSET ".$debut." LIMIT ".$limite.";";

  return $query;

}

function SQL_getCountWorkshopList($filter= -1) {
  switch ($filter) {
    case 'public':
      $mask=" WHERE workshop_statut = 'P'";
      break;
    case 'draft':
      $mask=" WHERE workshop_statut = 'D'";
      break;
    case 'archives_a':
      $mask=" WHERE workshop_statut = 'AA'";
      break;
    case 'archives_p':
      $mask=" WHERE workshop_statut = 'AP'";
      break;
    default:
      $mask=" WHERE workshop_statut <> 'E'";
      break;
  }
  if($filter== -1) $mask=" WHERE workshop_statut <> 'E'";
  $query= "SELECT COUNT(workshop_id) AS num_rows FROM " . T_WORK . $mask.";";
  return $query;
}
function SQL_getoneUser($user_id) {
  $query = "SELECT user_login FROM " . T_USER . " WHERE user_id='" . $user_id . "' AND user_validity='Y';";
  return $query;
}

function SQL_getWorkshopDenomination($workshop_id) {
  $query =	"SELECT W.workshop_denomination ".
				"FROM " . T_WORK . " AS  W ".
				"WHERE workshop_id='".$workshop_id."';";
  return $query;
}

function SQL_getoneCompleteWorkshop($workshop_id) {
  $query =	"SELECT W.workshop_denomination, W.workshop_resume, W.workshop_theme,SD.sdtheme_id, SD.sdtheme_name, W.workshop_level, W.workshop_comment, W.workshop_statut, ".
				"T.theme_name, L.level_name, ".
				"to_char(W.workshop_date_crea, '".toStringSqlDate()."') AS workshop_date_crea_display, ".
				"to_char(W.workshop_last_modify, '".toStringSqlDate('long')."') AS workshop_last_modify_display ".
				"FROM " . T_WORK . " AS  W ".
				"LEFT OUTER JOIN ".T_THEME." AS T on W.workshop_theme=T.theme_id ".
				"LEFT OUTER JOIN ". T_SD_THEME. " AS SD on T.theme_sd_theme=SD.sdtheme_id ".
				"LEFT OUTER JOIN ".T_LEVEL." AS L on W.workshop_level=L.level_id ".
				"WHERE workshop_id='".$workshop_id."' AND workshop_statut<>'E';";
  return $query;
}
function SQL_getWorkshopReportList($workshop_id, $statut=-1) {
  if ($statut=='P') {
    $mask="AND workrep_statut='P'";
  }
  else $mask="AND workrep_statut<>'E'";

  $query =	"SELECT WR.workrep_id, WR.workrep_title, WR.workrep_resume, WR.workrep_comment,
			to_char(WR.workrep_published_date, '".toStringSqlDate()."') As workrep_published_date, to_char(WR.workrep_date_crea, '".toStringSqlDate()."') AS workrep_date_crea_display, W.workshop_denomination, WR.workrep_statut FROM ". T_WORK_REP ." WR 
			LEFT OUTER JOIN " . T_WORK . " W ON W.workshop_id = WR.workrep_workshop_id WHERE WR.workrep_workshop_id 
			='".$workshop_id."' ".$mask." ORDER BY workrep_statut DESC, workrep_published_date DESC ;";
  return $query;
}

function SQL_getoneListCalWorkshop($workshop_id, $nb=-1) {
  if ($nb<>-1) {
    if ($nb=="ALL") $mask="";
    else  $mask="OFFSET 0 LIMIT ".$nb;
  }
  else $mask="";
  $query =	"SELECT workcal_id, workcal_task, workcal_task_details,	U.user_login, workcal_posted_by,
			to_char(workcal_task_date, '".toStringSqlDate()."') AS workcal_task_date_display,
		    to_char(workcal_date_crea, '".toStringSqlDate()."') AS workcal_date_crea_display, to_char(workcal_last_modify, '".toStringSqlDate()."') AS workcal_last_modify_display FROM ". T_WORK_CAL ." 
			LEFT OUTER JOIN ". T_USER. " AS U on workcal_posted_by=U.user_id 
			WHERE workcal_workshop_id=".$workshop_id."
			AND workcal_validity='Y' ORDER BY  workcal_task_date DESC ".$mask." ;";
  return $query;
}

function SQL_getPublicationParts($publi_id) {
  $query =	"SELECT PC.publicon_id, PC.publicon_title FROM " . J_PUBLI_PARTS ." AS JP ".
				"LEFT OUTER JOIN ".T_PUBLI_CONT." AS PC ON  JP.jpp_contents_id=PC.publicon_id ".
				"WHERE  JP.jpp_publication_id='".$publi_id."' ORDER BY PC.publicon_date_crea ASC;";
  return $query;
}

function SQL_getPublicationTitle($publi_id) {
  $query =	"SELECT N.publi_title FROM " . T_PUBLI . " AS  N WHERE publi_id='".$publi_id."';";
  return $query;
}

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

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

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

function SQL_getWorkshopUserList($work_id)
{
  if (!is_numeric($work_id)) return false;
  $query = "SELECT DISTINCT jwu_id, jwu_user_id, jwu_user_right, user_login, C.catus_name, U.user_category, P.profile_email  FROM "
  . J_WORK_USERS . " AS JW "
  . "LEFT OUTER JOIN " . T_USER . " U ON JW.jwu_user_id=U.user_id "
  . "LEFT OUTER JOIN " . T_CAT_USER . " C ON U.user_category=C.catus_id "
  . "LEFT OUTER JOIN " . T_PROFILE . " P ON U.user_profile=P.profile_id "
  . "WHERE JW.jwu_workshop_id=" . $work_id . " ORDER BY jwu_user_right ASC;";

  return $query;
}

function SQL_getOneTask($task_id)
{

  $query = "SELECT to_char(workcal_task_date, '".toStringSqlDate()."') AS date_t, workcal_task, workcal_task_details, workcal_workshop_id, to_char(workcal_date_crea, '".toStringSqlDate()."' ) AS date_c, to_char(workcal_last_modify,'".toStringSqlDate()."') AS date_m
		FROM ". T_WORK_CAL ."
		WHERE workcal_id=" . $task_id . ";";
  //echo $query;

  return $query;
}

?>
