<?php
/**
 * Workshop module
 * 
 * MySQL queries related to workshop module
 * 
 * @package linea21\modules\workshop
 * @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(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_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, workshop_date_crea, 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, workshop_last_modify, 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, workshop_date_crea, 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, workshop_last_modify, 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 'LEVEL':
     	if(is_array($id_filter)) {
     		$mask=" WHERE workshop_statut = 'P' AND ". sql_status_filter('workshop_level', $id_filter);
     	} else {
     		$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, L.level_name, L.level_id, W.workshop_resume, W.workshop_restricted, W.workshop_visible, W.workshop_statut, workshop_range, workshop_date_crea, DATE_FORMAT(W.workshop_date_crea, '".toStringSqlDate('long')."') AS workshop_date_crea_display, DATE_FORMAT(W.workshop_date_crea, '".toStringSqlDate()."') AS workshop_date_crea_display_short  FROM " . T_WORK." AS W".
	 " 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_get_all_workshop() {
	$q = "SELECT workshop_id, workshop_denomination FROM ". T_WORK ." WHERE workshop_statut <> 'E';";
	return $q;
}

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($id, $debut, $limite = SELECT_LIMIT) {

  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, W.workcom_viewed, U.user_login, U.user_validity, W.workcom_user_id, W.workcom_statut, workcom_last_modify, 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 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, U.user_validity, P.profile_avatar, P.profile_firstname, profile_lastname, W.workcom_user_id, W.workcom_statut, W.workcom_body, W.workcom_date_crea, 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 W.workcom_date_crea ASC;";

  return $query;

}

function SQL_getLatestMessage($id, $com_id) {
	
	$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, U.user_validity, P.profile_avatar, P.profile_firstname, profile_lastname, W.workcom_user_id, W.workcom_statut, W.workcom_body, W.workcom_date_crea, 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 W.workcom_date_crea DESC LIMIT 0, 1;";
			
			return $query;
			
}

function SQL_get_post_position($id, $parent_id) {
    
    $q = "SELECT count(*) as position, workcom_date_crea  FROM ". T_WORK_COM." WHERE  workcom_id < ". $id . " AND workcom_parent = " . $parent_id . " AND workcom_statut = 'P' ORDER BY workcom_date_crea ; ";
    return $q;
}

function SQL_getOnetopicWorkcom2($id, $com_id, $debut, $limite = SELECT_LIMIT, $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, U.user_validity, P.profile_avatar, P.profile_firstname, profile_lastname, W.workcom_user_id, W.workcom_statut, W.workcom_body, W.workcom_date_crea, 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 W.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 U.user_id, U.user_login, P.profile_email FROM " . T_USER . " AS U LEFT OUTER JOIN " . T_PROFILE . " AS P ON  U.user_profile=P.profile_id 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_level, L.level_id, L.level_name, W.workshop_restricted, W.workshop_visible, W.workshop_comment, W.workshop_statut, ".
				"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_LEVEL." AS L on W.workshop_level=L.level_id ".
				"WHERE workshop_id='".$workshop_id."'". $force_status.";";
  return $query;
}

function SQL_getAssociatedProjects($workshop_id, $force_status = false) {

	if($force_status) $force_status = " AND ". sql_status_filter('P.project_statut', $force_status);
	else $force_status = "";

  $query =	"SELECT P.project_id, P.project_name FROM " . T_PROJECT . " AS  P ".
				"WHERE P.project_workshop_id='".$workshop_id."'". $force_status.";";
  #echo($query);
  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_visible, 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, workcal_task_date, 
			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, PC.publicon_range, PC.publicon_date_crea 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_getonescaleDenomination')) {
	function SQL_getonescaleDenomination($scale_id) {
	  $query="SELECT scale_denomination FROM " . T_SCALE . " WHERE scale_id='".$scale_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, $user_right = -1)
{
  if (!is_numeric($work_id)) return false;
  
  if($user_right == -1) $filter = '';
  else $filter = ' AND '. sql_status_filter('jwu_user_right', $user_right);
  
  $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 . $filter . " ORDER BY jwu_user_right, user_login 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 = 'O' OR R.rights_workshop = 'A') 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;
}

function SQL_getWGUserNotifyExceptionsSettings($id, $wg_id) {
    $q = 	"SELECT CONCAT_WS('-',type,workshop_id) as exception FROM ". T_WORK_NOTIFY .	" WHERE user_id = '".$id."' AND workshop_id = '".$wg_id."';";
    
    return $q;
}

?>