<?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, 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 = '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, 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_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, 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, 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 = '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, ".
				"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."' 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, 
			DATE_FORMAT(WR.workrep_published_date, '".toStringSqlDate()."') As workrep_published_date, 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 DESC, 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,
			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."
			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 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;
    }

?>
