<?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 (!class_exists('auth')){
  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, LEFT(workshop_denomination, 50) as workshop_denomination, DATE_FORMAT(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 LIMIT 0 , 10;";
$req_quickbox_mod = "SELECT workshop_id, LEFT(workshop_denomination, 50) as workshop_denomination, DATE_FORMAT(workshop_last_modify, '".toStringSqlDate('long')."') AS workshop_last_modify_display, DATE_FORMAT(workshop_date_crea, '".toStringSqlDate()."') AS workshop_date_crea_display FROM " . T_WORK . " WHERE workshop_statut  <> 'E' ORDER BY workshop_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_add_noadmin = "SELECT workshop_id, LEFT(workshop_denomination, 50) as workshop_denomination, DATE_FORMAT(workshop_date_crea, '".toStringSqlDate('long')."') AS workshop_date_crea_display FROM " . T_WORK . " WHERE workshop_statut  = 'P'  ORDER BY workshop_date_crea DESC LIMIT 0 , 10;";
$req_quickbox_mod_noadmin = "SELECT workshop_id, LEFT(workshop_denomination, 50) as workshop_denomination, DATE_FORMAT(workshop_last_modify, '".toStringSqlDate('long')."') AS workshop_last_modify_display, DATE_FORMAT(workshop_date_crea, '".toStringSqlDate()."') AS workshop_date_crea_display FROM " . T_WORK . " WHERE workshop_statut  = 'P' ORDER BY workshop_last_modify DESC LIMIT 0 , 10;";

function SQL_getWorkshopList($debut, $limite = SELECT_LIMIT, $filter= -1, $id_filter=-1) {
  if ($debut<>-1) $mask_limit="LIMIT ".$debut." , ".$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 = 'PA'";
      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_restricted, W.workshop_statut, DATE_FORMAT(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_range ASC, 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="LIMIT ".$debut." , ".$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, DATE_FORMAT(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(DATE_FORMAT(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, P.profile_avatar, P.profile_firstname, profile_lastname, W.workcom_user_id, W.workcom_statut, W.workcom_body ,  DATE_FORMAT(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 LEFT OUTER JOIN ". T_PROFILE. " AS P on U.user_profile=P.profile_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, P.profile_avatar, P.profile_firstname, profile_lastname, W.workcom_user_id, W.workcom_statut, W.workcom_body ,  DATE_FORMAT(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 LEFT OUTER JOIN ". T_PROFILE. " AS P on U.user_profile=P.profile_id".
  $mask." ORDER BY workcom_date_crea ASC LIMIT ".$debut." , ".$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 = 'PA'";
      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, W.workshop_restricted  ".
				"FROM " . T_WORK . " AS  W ".
				"WHERE workshop_id='".$workshop_id."';";
  return $query;
}

function SQL_getoneCompleteWorkshop($workshop_id, $force_status = false) {
  
	if($force_status) $force_status = " AND ". sql_status_filter('workshop_statut', $force_status);
	else $force_status = "";
	
  $query =	"SELECT W.workshop_denomination, W.workshop_resume, W.workshop_theme,SD.sdtheme_id, SD.sdtheme_name, W.workshop_restricted, W.workshop_level, W.workshop_comment, W.workshop_statut, ".
				"T.theme_name, L.level_name, ".
				"DATE_FORMAT(W.workshop_date_crea, '".toStringSqlDate()."') AS workshop_date_crea_display, ".
				"DATE_FORMAT(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."'". $force_status.";";
  return $query;
}

function SQL_getWorkshopReportList($workshop_id, $statut=-1) {
  if ($statut=='P') {
    $mask="AND workrep_statut='P'";
  }
  if ($statut=='PA') {
    $mask="AND workrep_statut='PA'";
  }
  if ($statut==-1) {
    $mask="AND workrep_statut<>'E'";
  }

  $query =	"SELECT WR.workrep_id, WR.workrep_title, WR.workrep_resume, WR.workrep_comment, WR.workrep_published_date,
			DATE_FORMAT(WR.workrep_published_date, '".toStringSqlDate()."') As workrep_published_date_display, DATE_FORMAT(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 ASC, workrep_published_date DESC;";
  return $query;
}

function SQL_getoneListCalWorkshop($workshop_id, $nb=-1) {
  if ($nb<>-1) {
    if ($nb=="ALL") $mask="";
    else  $mask="LIMIT 0, ".$nb;
  }
  else $mask="";
  $query =	"SELECT workcal_id, workcal_task, workcal_task_details,	U.user_login, workcal_posted_by, workcal_workshop_id,
			DATE_FORMAT(workcal_task_date, '".toStringSqlDate()."') AS workcal_task_date_display,
		    DATE_FORMAT(workcal_date_crea, '".toStringSqlDate()."') AS workcal_date_crea_display, DATE_FORMAT(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." OR workcal_workshop_id=999999999)
			AND workcal_validity='Y' ORDER BY  workcal_task_date DESC ".$mask." ;";
  return $query;
}

if(!function_exists('SQL_getPublicationParts')) {
	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_range ASC, PC.publicon_date_crea ASC;";
	  return $query;
	}
}

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

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

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

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

/**
 * SQL_getAllWorkshopUsersforNotification
 * Enter description here ...
 * @param string $type should take value 'file' | 'topic' | 'post'
 */
function SQL_getAllWorkshopUsersforNotification($type)
{
	$query = "SELECT DISTINCT jwu_id, jwu_user_id, jwu_user_right, user_login, 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_PROFILE . " P ON U.user_profile=P.profile_id "
	. "AND jwu_user_id NOT IN (SELECT user_id FROM ".T_WORK_NOTIFY ." WHERE type='".$type."' ) "
	. "GROUP BY jwu_user_id ORDER BY jwu_user_right ASC;";

	return $query;
}

/**
 * SQL_getWorkshopUsersforNotification
 * Enter description here ...
 * @param integer $work_id
 * @param string $type should take value 'file' | 'topic' | 'post'
 */
function SQL_getWorkshopUsersforNotification($work_id, $type)
{
	if (!is_numeric($work_id)) return false;
	$query = "SELECT jwu_id, jwu_user_id, jwu_user_right, user_login, 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_PROFILE . " P ON U.user_profile=P.profile_id "
	. "WHERE JW.jwu_workshop_id=" . $work_id ." " 
	. "AND jwu_user_id NOT IN (SELECT user_id FROM ".T_WORK_NOTIFY ." WHERE workshop_id=".$work_id." AND type='".$type."' ) "
	. "GROUP BY jwu_user_id ORDER BY jwu_user_right ASC;";

	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.*  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 DATE_FORMAT(workcal_task_date, '".toStringSqlDate()."' ) AS date_t, workcal_task, workcal_task_details, workcal_workshop_id, DATE_FORMAT(workcal_date_crea, '".toStringSqlDate()."' ) AS date_c, DATE_FORMAT(workcal_last_modify,'".toStringSqlDate()."') AS date_m
		FROM ". T_WORK_CAL ."
		WHERE workcal_id=" . $task_id . ";";
  //echo $query;

  return $query;
}

function SQL_getOrganizersList() {
	
  $query=	"SELECT U.user_login, P.profile_email, P.profile_firstname, P.profile_lastname FROM ". T_USER." as U
  			LEFT OUTER JOIN " . T_PROFILE . " as P ON U.user_profile=P.profile_id
  			LEFT OUTER JOIN " . T_RIGHT . " R ON U.user_rights=R.rights_id
  			WHERE U.user_validity<>'N' AND R.rights_workshop <> 'U' ORDER BY user_login ASC;";

  return $query;
}

function SQL_getAllUsersList() {
	
  $query=	"SELECT U.user_login, P.profile_email, P.profile_firstname, P.profile_lastname FROM ". T_USER." as U
  			LEFT OUTER JOIN " . T_PROFILE . " as P ON U.user_profile=P.profile_id
  			WHERE U.user_validity<>'N' ORDER BY user_login ASC;";

  return $query;
}

function SQL_getWorkgroupUsersList($work_id)
{

	$query = "SELECT DISTINCT U.user_login, P.profile_email, P.profile_firstname, P.profile_lastname 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 . " AND U.user_validity<>'N' ORDER BY user_login ASC;";

	return $query;
}

?>