<?php
/**
 * Publication module
 * 
 * MySQL queries related to publication module
 * 
 * @package linea21\modules\publication
 * @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_list_scale = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut='P' OR scale_statut='D' ORDER BY scale_denomination ASC;";

$req_quickbox_add = "SELECT publi_id, LEFT(publi_title, 50) as publi_title, publi_date_crea, DATE_FORMAT(publi_date_crea, '".toStringSqlDate('long')."') AS publi_date_crea_display FROM " . T_PUBLI . " WHERE publi_statut  = 'P' OR publi_statut = 'D'  ORDER BY publi_date_crea DESC LIMIT 0 , 10;";
$req_quickbox_sup = "SELECT publi_id,  LEFT(publi_title, 50) as publi_title, publi_last_modify, DATE_FORMAT(publi_last_modify, '".toStringSqlDate('long')."') AS publi_last_modify_display, DATE_FORMAT(publi_date_crea, '".toStringSqlDate()."') AS publi_date_crea_display FROM " . T_PUBLI . " WHERE publi_statut = 'E' ORDER BY publi_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_mod = "SELECT publi_id, LEFT(publi_title, 50) as publi_title, publi_last_modify, DATE_FORMAT(publi_last_modify, '".toStringSqlDate('long')."') AS publi_last_modify_display, DATE_FORMAT(publi_date_crea, '".toStringSqlDate()."') AS publi_date_crea_display FROM " . T_PUBLI . " WHERE publi_statut  <> 'E' ORDER BY publi_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_list = "SELECT publi_id,  LEFT(publi_title, 50) as publi_title, publi_published_date, DATE_FORMAT(publi_published_date, '".toStringSqlDate('long')."') AS publi_published_date_display, DATE_FORMAT(publi_date_crea, '".toStringSqlDate()."') AS publi_date_crea_display FROM " . T_PUBLI . " WHERE publi_statut <> 'E' AND  publi_published_date <> '0001-01-01' ORDER BY publi_published_date DESC LIMIT 0 , 10;";


function SQL_getPublicationList($debut, $limite = SELECT_LIMIT, $statut=-1, $filter= -1, $id_filter=-1) {
  switch ($statut) {
    case 'public':
      $mask=" WHERE publi_statut = 'P'";
      $orderby="publi_range ASC, publi_published_date DESC";
      break;
    case 'draft':
      $mask=" WHERE publi_statut = 'D'";
      $orderby="publi_range ASC, publi_date_crea DESC";
      break;
    case 'archives_a':
      $mask=" WHERE publi_statut = 'AA'";
      $orderby="publi_range ASC, publi_last_modify DESC";
      break;
    case 'archives_p':
      $mask=" WHERE publi_statut = 'PA'";
      $orderby="publi_range ASC, publi_last_modify DESC";
      break;
    default:
      $mask=" WHERE publi_statut <> 'E'";
      $orderby="publi_range ASC, publi_date_crea DESC";
      break;
  }
  switch ($filter) {
  case 'LEVEL':
    	if(is_array($id_filter)) {
     		$mask.=" AND ". sql_status_filter('publi_level', $id_filter);
    	} else {
    		$mask.=" AND publi_level = " . $id_filter;
    	}
    	break;
    case 'SCALE':
      $mask.=" AND publi_scale = '".$id_filter."'";
      break;
    case 'TAG':
     	$mask.=" AND jta_module = 'publication' AND jta_tag_id = '".$id_filter."'";
     	break;
    default:
      $mask.="";
      break;
  }
  $q=	"SELECT DISTINCT(P.publi_id), U.user_login, LEFT(P.publi_title, 60) AS publi_title, ".
				"P.publi_title AS complete_publi_title, P.publi_statut, ".
				"publi_resume, publi_range, publi_date_crea, publi_last_modify, publi_published_date, publi_scale, publi_statut, L.level_id, L.level_name, ".
			  	"scale_denomination, publi_posted_by, ".
				"DATE_FORMAT(P.publi_date_crea, '".toStringSqlDate()."') AS publi_date_crea_display, ".
			  	"DATE_FORMAT(P.publi_published_date, '".toStringSqlDate()."') AS publi_published_date_display, ".
			  	"DATE_FORMAT(P.publi_published_date, '".toStringSqlDate('long')."') AS publi_published_date_display_long, ".
			  	"DATE_FORMAT(P.publi_last_modify, '".toStringSqlDate()."') AS publi_last_modify_display ".
				"FROM " . T_PUBLI." AS P ".
				"LEFT OUTER JOIN ".T_LEVEL." AS L on P.publi_level=L.level_id ".
				"LEFT OUTER JOIN ".T_SCALE." AS S on publi_scale=S.scale_id ".
			 	"LEFT OUTER JOIN ".T_USER." AS U on P.publi_posted_by=U.user_id ".
			 	"LEFT OUTER JOIN ".J_TAG." on publi_id = jta_module_id ".

  $mask." ORDER BY ".$orderby." LIMIT ".$debut." , ".$limite.";";
  #echo $q;
  return $q;
}

function SQL_getCountPublicationList($statut=-1, $filter=-1, $id_filter=-1) {
  switch ($statut) {
    case 'public':
      $mask=" WHERE publi_statut = 'P'";
      break;
    case 'draft':
      $mask=" WHERE publi_statut = 'D'";
      break;
    case 'archives_a':
      $mask=" WHERE publi_statut = 'AA'";
      break;
    case 'archives_p':
      $mask=" WHERE publi_statut = 'PA'";
      break;
    default:
      $mask=" WHERE publi_statut <> 'E'";
      break;
  }
  switch ($filter) {
  	case 'LEVEL':
    	if(is_array($id_filter)) {
     		$mask.=" AND ". sql_status_filter('publi_level', $id_filter);
    	} else {
    		$mask.=" AND publi_level = " . $id_filter;
    	}
      break;
    case 'SCALE':
      $mask.=" AND publi_scale = '".$id_filter."'";
      break;
    default:
      $mask.="";
      break;
  }
  $q= "SELECT COUNT(publi_id) AS num_rows FROM " . T_PUBLI ." ";
  if($filter=='LEVEL') {
    $q.=	"LEFT OUTER JOIN ".T_LEVEL." AS L on publi_LEVEL=L.level_id ";
  }
  $q.=$mask.";";

  return $q;
}

function SQL_getoneCompletePublication($publi_id, $force_status = false) {

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

  $q =	"SELECT P.publi_title, P.publi_resume, P.publi_level, P.publi_scale, P.publi_posted_by, P.publi_comment, P.publi_statut, ".
				"L.level_name, S.scale_denomination, U.user_login, L.level_id, L.level_name, ".
				"DATE_FORMAT(P.publi_date_crea, '".toStringSqlDate()."') AS publi_date_crea_display, ".
				"DATE_FORMAT(P.publi_last_modify, '".toStringSqlDate('long')."') AS publi_last_modify_display, ".
				"DATE_FORMAT(P.publi_published_date, '".toStringSqlDate()."') AS publi_published_date_display, ".
				"DATE_FORMAT(P.publi_published_date, '".toStringSqlDate('long')."') AS publi_published_date_display_long ".
				"FROM " . T_PUBLI . " AS  P ".
				"LEFT OUTER JOIN ".T_LEVEL." AS L on P.publi_level=L.level_id ".
				"LEFT OUTER JOIN ".T_SCALE." AS S on P.publi_scale=S.scale_id ".
				"LEFT OUTER JOIN ".T_USER." AS U on P.publi_posted_by=U.user_id ".
				"WHERE publi_id='".$publi_id."'". $force_status.";";
  #echo $q;
  return $q;
}

if(!function_exists('SQL_getPublicationParts')) {
  function SQL_getPublicationParts($publi_id) {
    $q =	"SELECT PC.publicon_id, PC.publicon_title, PC.publicon_range, PC.publicon_date_crea FROM " . J_PARTS ." AS JP ".
				"LEFT OUTER JOIN ".T_PUBLI_CONT." AS PC ON  JP.j_parts_id=PC.publicon_id ".
				"WHERE  JP.j_root_id='".$publi_id."' AND JP.j_type='P' AND publicon_validity='Y' ORDER BY PC.publicon_range ASC, PC.publicon_date_crea ASC;";
    return $q;
  }
}

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

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

// Copie conforme de celle qui se trouve dans workshoprep
function SQL_GetResources($ID, $type, $ressource, $sql_object)
{
  switch ($ressource) {
    case 'BIBLIO':
      $TDB_LIAISON = J_BIBLIO;
      $ACTIVE_TABLE = T_BIBLIO_RES;
      $join_active="bibliores";
      $champ2="j_biblio_id";
      break;
    case 'LINK':
      $TDB_LIAISON = J_LINK;
      $ACTIVE_TABLE = T_LINK_RES;
      $join_active="linkres";
      $champ2="j_link_id";
      break;
    case 'MULTI':
      $TDB_LIAISON = J_MULTI;
      $ACTIVE_TABLE = T_MULTI_RES;
      $join_active="multires";
      $champ2="j_multi_id";
      break;
    default:
      return false;
  }

  $q = "SELECT * FROM " . $ACTIVE_TABLE . " LEFT OUTER JOIN ".$TDB_LIAISON." ON ".$champ2."=".$join_active."_id WHERE j_root_id='".$ID."' AND j_type='".substr($type, 0, 1)."' AND ".$join_active."_validity='Y' ORDER BY ".$join_active."_range ASC, ".$join_active."_date_crea ASC;";
  //echo $q;
  $result = $sql_object -> DBSelect ($q);

  return $result;
}


?>