File Source for pgsql.inc.php

<?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 = '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_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_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="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, P.profile_avatar, 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 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 , 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 LEFT OUTER JOIN ". T_PROFILE. " AS P on U.user_profile=P.profile_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 = '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 ".
"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'";
}
if ($statut=='PA') {
$mask="AND workrep_statut='PA'";
}
if ($statut==-1) {
$mask="AND workrep_statut<>'E'";
}
else $mask="AND workrep_statut<>'E'";
$query = "SELECT WR.workrep_id, WR.workrep_title, WR.workrep_resume, WR.workrep_comment, WR.workrep_published_date,
to_char(WR.workrep_published_date, '".toStringSqlDate()."') As workrep_published_date_display, 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 ASC, 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, workcal_workshop_id,
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." OR workcal_workshop_id=999999999)
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_range ASC, PC.publicon_date_crea ASC;";
return $query;
}
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;
}
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;
}
/**
* 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 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 "
. "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."' ) "
. "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 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;
}
function SQL_getOrganizersList() {
$query= "SELECT 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
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 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;
}
?>

Source for file pgsql.inc.php