<?php
/**
 * @package linea21.modules
 * @subpackage project
 * @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_project_id = "SELECT MAX(project_id) AS maxid FROM " . T_PROJECT . ";";

$req_count_projects = "SELECT COUNT(project_id) as num_rows FROM " . T_PROJECT . " WHERE project_statut<>'E';";

$req_list_scale = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' ORDER BY scale_denomination ASC;";
$req_list_priority = "SELECT priority_id, priority_name FROM " . T_PRIORITY . " ORDER BY priority_id ASC;";

$req_quickbox_add = "SELECT project_id, LEFT(project_name, 50) as project_name, DATE_FORMAT(project_date_crea, '".toStringSqlDate('long')."') AS project_date_crea_display FROM " . T_PROJECT . " WHERE project_statut  = 'P' OR project_statut = 'D'  ORDER BY project_date_crea DESC LIMIT 0 , 10;";
$req_quickbox_sup = "SELECT project_id,  LEFT(project_name, 50) as project_name, DATE_FORMAT(project_last_modify, '".toStringSqlDate('long')."') AS project_last_modify_display, DATE_FORMAT(project_date_crea, '".toStringSqlDate()."') AS project_date_crea_display FROM " . T_PROJECT . " WHERE project_statut = 'E' ORDER BY project_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_mod = "SELECT project_id, LEFT(project_name, 50) as project_name, DATE_FORMAT(project_last_modify, '".toStringSqlDate('long')."') AS project_last_modify_display, DATE_FORMAT(project_date_crea, '".toStringSqlDate()."') AS project_date_crea_display FROM " . T_PROJECT . " WHERE project_statut  <> 'E' ORDER BY project_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_list = "SELECT project_id,  LEFT(project_name, 50) as project_name, DATE_FORMAT(project_published_date, '".toStringSqlDate('long')."') AS project_published_date_display, DATE_FORMAT(project_date_crea, '".toStringSqlDate()."') AS project_date_crea_display FROM " . T_PROJECT . " WHERE project_statut <> 'E' AND  project_published_date <> '0001-01-01' ORDER BY project_published_date DESC LIMIT 0 , 10;";


function SQL_getoneCompleteProject($project_id) {

  $query =	"SELECT P.project_name, P.project_description, P.project_body, ".
                "P.project_statut, P.project_posted_by, PP.project_name AS parent_name, P.project_parent_id AS project_parent_id, ".
                "P.project_posted_by, P.project_scale_id, user_login, P.project_priority_id, priority_name AS project_priority, S.scale_denomination, ".
				"DATE_FORMAT(P.project_date_crea, '".toStringSqlDate()."') AS project_date_crea_display, ".
				"DATE_FORMAT(P.project_last_modify, '".toStringSqlDate('long')."') AS project_last_modify_display, ".
				"DATE_FORMAT(P.project_begin_date, '".toStringSqlDate()."') AS project_begin_date_display, ".
  				"DATE_FORMAT(P.project_estimated_date, '".toStringSqlDate()."') AS project_estimated_date_display, ".
                "DATE_FORMAT(P.project_end_date, '".toStringSqlDate()."') AS project_end_date_display, ".
  				"DATE_FORMAT(P.project_published_date, '".toStringSqlDate()."') AS project_published_date_display ".
				"FROM " . T_PROJECT . " AS  P ".
                "LEFT OUTER JOIN ".T_PROJECT." AS PP on P.project_parent_id=PP.project_id ".
			    "LEFT OUTER JOIN ".T_USER." AS U on P.project_posted_by=U.user_id ".
                "LEFT OUTER JOIN ".T_SCALE." AS S on P.project_scale_id=S.scale_id ".
                "LEFT OUTER JOIN ".T_PRIORITY." AS O on P.project_priority_id=O.priority_id ".
				"WHERE P.project_id='".$project_id."' AND P.project_statut<>'E';";
				#echo($query);
	return $query;
}


function SQL_getProjectsList($debut, $limite, $statut, $filter=-1, $id_filter=-1) {
	switch ($statut) {
		case 'public':
			$mask=" WHERE P.project_statut = 'P'";
			$orderby="P.project_date_crea DESC";
			break;
		case 'draft':
			$mask=" WHERE P.project_statut = 'D'";
			$orderby="P.project_date_crea DESC";
			break;
		case 'archives_a':
			$mask=" WHERE P.project_statut = 'AA'";
			$orderby="P.project_date_crea DESC";
			break;
		case 'archives_p':
			$mask=" WHERE P.project_statut = 'PA'";
			$orderby="P.project_date_crea DESC";
			break;
		default:
			$mask=" WHERE P.project_statut <> 'E'";
			$orderby="P.project_date_crea DESC";
			break;
		}
	switch ($filter) {
		case 'SCALE':
			$mask.=" AND P.project_scale_id = '".$id_filter."'";
			break;
		case 'PRIORITY':
			$mask.=" AND P.project_priority_id = '".$id_filter."'";
			break;
		default:
			$mask.="";
			break;
		}
		
		$query= "SELECT P.project_id, LEFT(P.project_name, 60) AS project_name, P.project_name AS complete_project_name, ".
			  "P.project_description, P.project_body, PP.project_name AS parent_project_name, ".
		      "P.project_posted_by, user_login, P.project_statut, O.priority_name, O.priority_id, ".
			  "S.scale_id AS project_scale_id, S.scale_denomination, ".
		      "DATE_FORMAT(P.project_begin_date, '".toStringSqlDate()."') AS project_begin_date_display, ".
			  "DATE_FORMAT(P.project_estimated_date, '".toStringSqlDate()."') AS project_estimated_date_display, ".
			  "DATE_FORMAT(P.project_end_date, '".toStringSqlDate()."') AS project_end_date_display, ".
	          "DATE_FORMAT(P.project_published_date, '".toStringSqlDate()."') AS project_published_date_display, ".
			  "DATE_FORMAT(P.project_date_crea, '".toStringSqlDate()."') AS project_date_crea_display, ".
			  "DATE_FORMAT(P.project_last_modify, '".toStringSqlDate()."') AS project_last_modify_display ".
			  "FROM " . T_PROJECT ." AS P ".
			  "LEFT OUTER JOIN ".T_PROJECT." AS PP on P.project_parent_id=PP.project_id ".
			  "LEFT OUTER JOIN ".T_USER." AS U on P.project_posted_by=U.user_id ".
		      "LEFT OUTER JOIN ".T_PRIORITY." AS O on P.project_priority_id=O.priority_id ".
		      "LEFT OUTER JOIN ".T_SCALE." AS S on P.project_scale_id=S.scale_id ".
		
			  $mask." ORDER BY ".$orderby." LIMIT ".$debut." , ".$limite.";";
	#echo $query;
	return $query;
}

function SQL_getCountProjectsList($statut, $filter=-1, $id_filter=-1) {
	switch ($statut) {
		case 'public':
			$mask="WHERE project_statut = 'P'";
			break;
		case 'draft':
			$mask="WHERE project_statut = 'D'";
			break;
		case 'archives_a':
			$mask="WHERE project_statut = 'AA'";
			break;
		case 'archives_p':
			$mask="WHERE project_statut = 'PA'";
			break;
		default:
			$mask="WHERE project_statut <> 'E'";
			break;
		}
	switch ($filter) {
		case 'SCALE':
			$mask.=" AND project_scale_id = '".$id_filter."'";
			break;
		case 'PRIORITY':
			$mask.=" AND project_priority_id = '".$id_filter."'";
			break;
		default:
			$mask.="";
			break;
		}
	$query=	"SELECT COUNT(project_id) AS num_rows FROM " . T_PROJECT ." ";

	$query.=$mask.";";
	return $query;
}



function SQL_getProjectName($project_id) {
	$query =	"SELECT P.project_name, P.project_posted_by FROM " . T_PROJECT . " AS  P WHERE project_id='".$project_id."';";
	return $query;
}

function SQL_getProjectChildren($project_id) {
	$query =	"SELECT P.project_name, P.project_id FROM " . T_PROJECT . " AS  P WHERE project_parent_id='".$project_id."' AND project_statut = 'P' ;";
	return $query;
}

function SQL_getonescaleDenomination($scale_id) {
	$query="SELECT scale_denomination FROM " . T_SCALE . " WHERE scale_id='".$scale_id."';";
	return $query;
}

function SQL_getonepriorityName($priority_id) {
	$query="SELECT priority_name FROM " . T_PRIORITY . " WHERE priority_id='".$priority_id."';";
	return $query;
}

?>