<?php
/**
 * Handle content for publication and workgroup report modules
 * 
 * MySQL queries related to content module
 * 
 * @package linea21\modules\content
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 * 'Content' module is used by 'Publication' and 'Workshop' modules
 */

//////////// Check Inclusion de pages ////////////
if (!class_exists('auth')) {
  include_once("../lib/lib_common.php");
  ReloadIndex('admin');
}
////////////

$req_max_publi_content_id = "SELECT MAX(publicon_id) AS maxid FROM " . T_PUBLI_CONT . ";";

function SQL_getPublicationParts($publi_id, $orderby='CREA', $statut='Y', $limit=-1) {
  $q =	"SELECT PC.publicon_id, PC.publicon_title, PC.publicon_range, PC.publicon_range, PC.publicon_last_modify, DATE_FORMAT(PC.publicon_date_crea, '".toStringSqlDate()."') AS publicon_date_crea_display, DATE_FORMAT(PC.publicon_last_modify, '".toStringSqlDate('long')."') AS publicon_last_modify_display 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 PC.publicon_validity='".$statut."' ";
  if($orderby=='CREA')	$q.="ORDER BY PC.publicon_range ASC, PC.publicon_date_crea ASC";
  if($orderby=='MODIFY')	$q.="ORDER BY PC.publicon_last_modify DESC";
  if($limit!=-1) $q.= " LIMIT 0 , ".$limit;
  $q.=";";
  return $q;
}


function SQL_getWorkshopParts($workrep_id, $orderby='CREA', $statut='Y', $limit=-1) {
  $q =	"SELECT W.workrepcon_id , W.workrepcon_title, W.workrepcon_range, W.workrepcon_date_crea, W.workrepcon_last_modify, DATE_FORMAT(W.workrepcon_date_crea, '".toStringSqlDate()."') AS workrepcon_date_crea_display, DATE_FORMAT(W.workrepcon_last_modify, '".toStringSqlDate('long')."') AS workrepcon_last_modify_display FROM " . J_PARTS ." AS JW ".
				"LEFT OUTER JOIN ".T_WORK_REP_CONT." AS W ON  JW.j_parts_id=W.workrepcon_id ".
				"WHERE  JW.j_root_id='".$workrep_id."' ".
				"AND JW.j_type='W' AND W.workrepcon_validity='".$statut."' ";
  if($orderby=='CREA')	$q.="ORDER BY W.workrepcon_range ASC, W.workrepcon_date_crea ASC";
  if($orderby=='MODIFY')	$q.="ORDER BY W.workrepcon_last_modify DESC";
  if($limit!=-1) $q.= " LIMIT 0 , ".$limit;
  $q.=";";
  #echo $q;
  return $q;
}


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

function SQL_getWorkshopRepTitleAndGroup($workrep_id) {
  $q =	"SELECT workrep_title, workrep_visible, workrep_id, workshop_denomination, workshop_id, workrep_workshop_id  FROM " . T_WORK_REP . " ".
				"LEFT OUTER JOIN ".T_WORK." on workshop_id=workrep_workshop_id ".
				"WHERE workrep_id='".$workrep_id."' LIMIT 1;";
  return $q;
}

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

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

  $q =	"SELECT PC.publicon_title, PC.publicon_body, DATE_FORMAT(PC.publicon_date_crea, '".toStringSqlDate()."') AS publicon_date_crea_display, DATE_FORMAT(PC.publicon_last_modify, '".toStringSqlDate('long')."') AS publicon_last_modify_display FROM " . T_PUBLI_CONT." AS PC ".
				"WHERE  PC.publicon_id='".$publi_id."'". $force_status." LIMIT 1;";
  return $q;
}

function SQL_getOneWorkshopParts($workrep_id, $force_status = false) {

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

  $q =	"SELECT WC.workrepcon_title, WC.workrepcon_body, DATE_FORMAT( WC.workrepcon_date_crea, '".toStringSqlDate()."') AS  workrepcon_date_crea_display, DATE_FORMAT(WC.workrepcon_last_modify, '".toStringSqlDate('long')."') AS  workrepcon_last_modify_display FROM " . T_WORK_REP_CONT." AS WC ".
				"WHERE  WC.workrepcon_id='".$workrep_id."'". $force_status." LIMIT 1;";
  return $q;
}

function SQL_getPreviousWorkshopRepPartsByDate($id, $parentid, $date) {
  $q =	"SELECT workrepcon_id AS part_id, workrepcon_title AS part_title, workrepcon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_WORK_REP_CONT." ON workrepcon_id=j_parts_id ".
		"WHERE workrepcon_id <> '".$id."' AND workrepcon_date_crea < '".$date."' AND j_root_id='".$parentid."' AND j_type='W' AND workrepcon_validity = 'Y' ORDER BY workrepcon_date_crea DESC LIMIT 0,1;";
  #echo $q;
  return $q;
}

function SQL_getNextWorkshopRepPartsByDate($id, $parentid, $date) {
  $q =	"SELECT workrepcon_id AS part_id, workrepcon_title AS part_title, workrepcon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_WORK_REP_CONT." ON workrepcon_id=j_parts_id ".
		"WHERE workrepcon_id <> '".$id."' AND workrepcon_date_crea > '".$date."' AND j_root_id='".$parentid."' AND j_type='W' AND workrepcon_validity = 'Y' ORDER BY workrepcon_date_crea ASC LIMIT 0,1;";
  #echo $q;
  return $q;
}
function SQL_getNextWorkshopRepPartsByRange($id, $parentid, $range) {
  $q =	"SELECT workrepcon_id AS part_id, workrepcon_title AS part_title, workrepcon_range, workrepcon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_WORK_REP_CONT." ON workrepcon_id=j_parts_id ".
		"WHERE workrepcon_id <> '".$id."' AND workrepcon_range >= '".$range."' AND j_root_id='".$parentid."' AND j_type='W' AND workrepcon_validity = 'Y' ORDER BY workrepcon_range ASC, workrepcon_date_crea ASC LIMIT 0,1;";
  #echo $q;
  return $q;
}
function SQL_getPreviousWorkshopRepPartsByRange($id, $parentid, $range) {
  $q ="SELECT workrepcon_id AS part_id, workrepcon_title AS part_title , workrepcon_range, workrepcon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_WORK_REP_CONT." ON workrepcon_id=j_parts_id ".
		"WHERE workrepcon_id <> '".$id."' AND workrepcon_range <= '".$range."' AND j_root_id='".$parentid."' AND j_type='W' AND workrepcon_validity = 'Y' ORDER BY workrepcon_range DESC, workrepcon_date_crea ASC LIMIT 0,1;";
  #echo $q;
  return $q;
}

function SQL_getPreviousPublicationPartsByDate($id, $parentid, $date) {
  $q ="SELECT publicon_id AS part_id, publicon_title AS part_title, publicon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_PUBLI_CONT." ON publicon_id=j_parts_id ".
		"WHERE publicon_id <>'".$id."' AND publicon_date_crea  < '".$date."' AND j_root_id='".$parentid."' AND j_type='P' AND publicon_validity = 'Y' ORDER BY publicon_date_crea DESC LIMIT 0,1;";
  #echo $q;
  return $q;
}

function SQL_getNextPublicationPartsByDate($id, $parentid, $date) {
  $q =	"SELECT publicon_id AS part_id, publicon_title AS part_title, publicon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_PUBLI_CONT." ON publicon_id=j_parts_id ".
		"WHERE publicon_id <>'".$id."' AND publicon_date_crea  > '".$date."' AND j_root_id='".$parentid."' AND j_type='P' AND publicon_validity = 'Y' ORDER BY publicon_date_crea ASC LIMIT 0,1;";
  #echo $q;
  return $q;
}

function SQL_getPreviousPublicationPartsByRange($id, $parentid, $range) {
  $q ="SELECT publicon_id AS part_id, publicon_title AS part_title, publicon_range, publicon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_PUBLI_CONT." ON publicon_id=j_parts_id ".
		"WHERE publicon_id <>'".$id."' AND publicon_range  <= '".$range."' AND j_root_id='".$parentid."' AND j_type='P' AND publicon_validity = 'Y' ORDER BY publicon_range DESC, publicon_date_crea ASC LIMIT 0,1;";
  #echo $q;
  return $q;
}

function SQL_getNextPublicationPartsByRange($id, $parentid, $range) {
  $q ="SELECT publicon_id AS part_id, publicon_title AS part_title, publicon_range, publicon_date_crea FROM " . J_PARTS . " ".
		"LEFT OUTER JOIN ".T_PUBLI_CONT." ON publicon_id=j_parts_id ".
		"WHERE publicon_id <>'".$id."' AND publicon_range  >= '".$range."' AND j_root_id='".$parentid."' AND j_type='P' AND publicon_validity = 'Y' ORDER BY publicon_range ASC, publicon_date_crea ASC LIMIT 0,1;";
  #echo $q;
  return $q;
}

function SQL_getWorkshopRepRangeAndDate($id) {
  $q =	"SELECT workrepcon_range as item_range, workrepcon_date_crea as item_date_crea FROM " . T_WORK_REP_CONT . " WHERE workrepcon_id = ".$id.";";
  #echo $q;
  return $q;
}

function SQL_getPublicationRangeAndDate($id) {
  $q =	"SELECT publicon_range as item_range, publicon_date_crea as item_date_crea FROM " . T_PUBLI_CONT . " WHERE publicon_id = ".$id.";";
  #echo $q;
  return $q;
}

?>