<?php
/**
 * Project module
 * 
 * MySQL queries related to project module
 * 
 * @package linea21\modules\project
 * @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');
}
////////////


function SQL_get_project_tasks_for_reminder() {

    $q =    "SELECT T.*, P.project_name FROM " . T_PROJECT_TASK . " AS T LEFT OUTER JOIN ".T_PROJECT." AS P on T.task_project_id = P.project_id WHERE `task_status` = 'todo' AND task_reminder_freq <> 'NO REMINDER' AND task_deleted ='N';";
    
    return $q;
}

function SQL_get_project_tasks_count($project_id, $status = false) {
    
    if($status) $status_filter = " AND ". sql_status_filter('T.task_status', $status);
    else $status_filter = "";
    
    $q =    "SELECT count(T.task_id) as count FROM " . T_PROJECT_TASK . " AS T WHERE task_project_id = '".$project_id."' AND task_deleted ='N'". $status_filter.";";
    
    return $q;
}

function SQL_get_next_reminder_date($task_id, $interval) {
     
    $q =    "SELECT T.task_id, 
            CASE T.task_reminder_freq
                WHEN 'NO REMINDER' THEN 'noreminder'
                ELSE IF(T.task_reminder_lastdate <> '0001-01-01 00:00:00', DATE_FORMAT(DATE_ADD(T.task_reminder_lastdate, ".$interval."), '".toStringSqlDate()."'), DATE_FORMAT(DATE_ADD(T.task_date_crea, $interval), '".toStringSqlDate()."'))
            END as next_send 
            FROM " . T_PROJECT_TASK . " AS T
            WHERE task_id = '".$task_id."';";
    
    return $q;
}

function SQL_get_project_tasks($project_id, $status = false) {
    
    if($status) $status_filter = " AND ". sql_status_filter('T.task_status', $status);
    else $status_filter = "";
    
    $q =    "SELECT T.task_id, T.task_project_id, T.task_label, T.task_status, T.task_reminder_freq, task_reminder_lastdate, T.task_date_done, user_id, user_login, 
            DATE_FORMAT(T.task_date_crea, '".toStringSqlDate()."') AS task_date_crea_display, 
            DATE_FORMAT(T.task_last_modify, '".toStringSqlDate('long')."') AS task_last_modify_display, 
            DATE_FORMAT(T.task_date_done, '".toStringSqlDate()."') AS task_date_done_display, 
            DATE_FORMAT(T.task_reminder_lastdate, '".toStringSqlDate()."') AS task_reminder_lastdate_display,
            TIMESTAMPDIFF(MONTH,T.task_date_crea,T.task_date_done) AS duration 
            FROM " . T_PROJECT_TASK . " AS T 
            LEFT OUTER JOIN ".T_PROJECT." AS P on T.task_project_id = P.project_id 
            LEFT OUTER JOIN ".T_USER." AS U on T.task_user_id = U.user_id 
            WHERE task_project_id = '".$project_id."' AND task_deleted ='N'". $status_filter." 
            ORDER BY T.task_range ASC, FIELD (T.task_status, 'todo', 'wip', 'done'), T.task_date_crea ASC;";
    
    return $q;
}

function SQL_get_task($id) {

    $q =    "SELECT T.task_id, T.task_project_id, T.task_label, T.task_status, T.task_reminder_freq, task_reminder_lastdate, T.task_date_done, user_id, user_login, 
            DATE_FORMAT(T.task_date_crea, '".toStringSqlDate()."') AS task_date_crea_display, 
            DATE_FORMAT(T.task_last_modify, '".toStringSqlDate('long')."') AS task_last_modify_display, 
            DATE_FORMAT(T.task_date_done, '".toStringSqlDate()."') AS task_date_done_display, 
            DATE_FORMAT(T.task_reminder_lastdate, '".toStringSqlDate()."') AS task_reminder_lastdate_display, 
            TIMESTAMPDIFF(MONTH,T.task_date_crea,T.task_date_done) AS duration 
            FROM " . T_PROJECT_TASK . " AS T 
            LEFT OUTER JOIN ".T_PROJECT." AS P on T.task_project_id = P.project_id
            LEFT OUTER JOIN ".T_USER." AS U on T.task_user_id = U.user_id
            WHERE task_id = '".$id."' AND task_deleted ='N';";
    
    return $q;
}

//$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, scale_root FROM " . T_SCALE . " WHERE scale_statut<>'E' ORDER BY scale_root DESC, scale_range DESC, scale_denomination ASC;";
$req_list_priority = "SELECT priority_id, priority_name FROM " . T_PRIORITY . " ORDER BY priority_id ASC;";

$req_list_workshop = "SELECT workshop_id, workshop_denomination FROM ". T_WORK ." WHERE workshop_statut <> 'E';";
$req_list_sdi = "SELECT sdii_id, sdii_name FROM ". T_SDI_INFO . " WHERE sdii_statut <> 'E';";
$req_quickbox_add = "SELECT project_id, LEFT(project_name, 50) as project_name, project_date_crea, 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, project_last_modify, 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, project_last_modify, 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, project_published_date, 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;";

/**
 * Retrieve last indicator inserted within the given interval
 * @param string $interval
 * @param string $q
 */
function SQL_get_last_inserted_indicator($interval = '20 MINUTE') {
	$q = 'SELECT * FROM ' . T_SDI_INFO . ' WHERE  sdii_date_crea >= DATE_SUB(NOW(),INTERVAL '.$interval.') AND sdii_statut <> "E" ORDER BY sdii_date_crea DESC LIMIT 0, 1;';
	return $q;
}


function SQL_getTotalProjectCompletion($statut) {
    
    switch ($statut) {
        case 'public':
            $mask=" WHERE P.project_statut = 'P'";
            break;
        case 'draft':
            $mask=" WHERE P.project_statut = 'D'";
            break;
        case 'archives_a':
            $mask=" WHERE P.project_statut = 'AA'";
            break;
        case 'archives_p':
            $mask=" WHERE P.project_statut = 'PA'";
            break;
        default:
            $mask=" WHERE P.project_statut <> 'E'";
            break;
    }
    
    $query =	"SELECT (SUM(project_completed) / COUNT(project_id)) as completion,  COUNT(project_id) as number FROM " . T_PROJECT . " AS P ".$mask;

    return $query;
}

function SQL_getoneCompleteProject($project_id, $force_status = false) {

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

  $query =	"SELECT P.project_name, P.project_goal, P.project_opgoal, P.project_description, P.project_body, P.project_cal_comment, P.project_budget, P.project_budget_comment, P.project_elected, P.project_team, P.project_comment, ".
                "P.project_statut, P.project_posted_by, PP.project_name AS parent_name, P.project_parent_id AS project_parent_id, P.project_completed, P.project_completed_sync, ".
                "P.project_posted_by, P.project_scale_id, user_login, P.project_priority_id, priority_name AS project_priority, S.scale_denomination, ".
                "W.workshop_denomination, W.workshop_id AS project_workshop_id, L.level_name, L.level_label, P.project_level_id, ".
				"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, ".
  				"DATE_FORMAT(P.project_published_date, '".toStringSqlDate('long')."') AS project_published_date_display_long ".
				"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_LEVEL." AS L on P.project_level_id=L.level_id ".
                "LEFT OUTER JOIN ".T_WORK." AS W on P.project_workshop_id=W.workshop_id ".
                "LEFT OUTER JOIN ".T_PRIORITY." AS O on P.project_priority_id=O.priority_id ".
				"WHERE P.project_id='".$project_id."'". $force_status.";";
  #echo($query);
  return $query;
}

// /////////////////////////////////
// Pour la génération de rapports
// /////////////////////////////////
function GetAllProjectsInfo ($status, $type, $project_array = -1)
{
	$mask = '';
	// @todo remove - see https://dev.linea21.com/issues/1155
// 	if ($status == 'ALL') $mask .= 'WHERE P.project_statut <> \'E\'';
// 	else $mask .= 'WHERE P.project_statut = \'P\'';
	$mask .= 'WHERE P.project_statut <> \'E\'';
	if ($type != 'ALL') {
		$sep = ' AND ';
		for($i = 0; $i < count($project_array); $i++) {
			$mask .= $sep . 'P.project_id = ' . $project_array[$i] . '';
			$sep = ' OR ';
		}
	}
	$q =  "SELECT P.*, U.user_login , O.priority_name , PP.project_name AS parent_project_name, L.*, S.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 ".
				"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 ".
		    "LEFT OUTER JOIN ".T_LEVEL." AS L on P.project_level_id=L.level_id ";
	$q .= $mask;
	$q .= " ORDER  BY P.project_range ASC, P.project_name ASC";

	return $q;
}


function SQL_getProjectsList($debut, $limite, $statut, $filter=-1, $id_filter=-1, $extra_filter=-1, $workshop_filter = -1) {
  switch ($statut) {
    case 'public':
      $mask=" WHERE P.project_statut = 'P'";
      $orderby="P.project_range ASC, P.project_name ASC";
      break;
    case 'draft':
      $mask=" WHERE P.project_statut = 'D'";
      $orderby="P.project_range ASC, P.project_date_crea ASC";
      break;
    case 'archives_a':
      $mask=" WHERE P.project_statut = 'AA'";
      $orderby="P.project_range ASC, P.project_date_crea ASC";
      break;
    case 'archives_p':
      $mask=" WHERE P.project_statut = 'PA'";
      $orderby="P.project_range ASC, P.project_name ASC";
      break;
    default:
      $mask=" WHERE P.project_statut <> 'E'";
      $orderby="P.project_range ASC, 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;
    case 'LEVEL':
    	if(is_array($id_filter)) {
     		$mask.=" AND ". sql_status_filter('P.project_level_id', $id_filter);
    	} else {
    		$mask.=" AND P.project_level_id = " . $id_filter;
    	}
    	$orderby="P.project_range ASC, P.project_name ASC";
     	break;
    case 'FINALITY':
     		$mask.=" AND jpf_finality_id= '".$id_filter."'";
     		$orderby="P.project_range ASC, P.project_name ASC";
     		break;
    case 'TAG':
     	$mask.=" AND jta_module = 'project' AND jta_tag_id = '".$id_filter."'";
     	break;
    case 'LEVEL_TAG':
    	if(is_array($id_filter)) {
    		$mask.=" AND ". sql_status_filter('P.project_level_id', $id_filter)." AND jta_module = 'project' AND jta_tag_id = '".$extra_filter."'";
     	} else {
     		$mask.=" AND P.project_level_id = " . $id_filter. " AND jta_module = 'project' AND jta_tag_id = '".$extra_filter."'";
     	}
    default:
      $mask.="";
      break;
  }
  if($workshop_filter != -1) $mask .= " AND P.project_workshop_id = '" . $workshop_filter . "'";

  $query= "SELECT DISTINCT(P.project_id), LEFT(P.project_name, 120) AS project_name, P.project_range, P.project_workshop_id, P.project_parent_id, P.project_name AS complete_project_name, ".
			  "P.project_description, P.project_body, P.project_completed, PP.project_name AS parent_project_name, ".
		      "P.project_posted_by, P.project_date_crea, user_login, P.project_statut, O.priority_name, O.priority_id, ".
			  "S.scale_id AS project_scale_id, S.scale_denomination, ".
		      "L.level_name, P.project_level_id, ".
		      "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_published_date, '".toStringSqlDate('long')."') AS project_published_date_display_long, ".
			  "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 ".
		    "LEFT OUTER JOIN ".T_LEVEL." AS L on P.project_level_id=L.level_id ".
        "LEFT OUTER JOIN ".J_TAG." on P.project_id = jta_module_id ";
  
  if(defined('PROJECT_FINALITIES') && PROJECT_FINALITIES == 1) $query .= "LEFT OUTER JOIN ".J_PROJECT_FINALITY." on P.project_id = jpf_project_id ";

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

function SQL_getCountProjectsList($statut, $filter=-1, $id_filter=-1, $workshop_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;
    case 'LEVEL':
     	$mask.=" AND ". sql_status_filter('P.project_level_id', $id_filter);
      break;
    default:
      $mask.="";
      break;
  }
  
  if($workshop_filter != -1) $mask .= " AND P.project_workshop_id = '" . $workshop_filter . "'";
  
  $query=	"SELECT COUNT(project_id) AS num_rows FROM " . T_PROJECT ." ";
  if($filter=='LEVEL') {
    $query  .=	"LEFT OUTER JOIN ".T_LEVEL." AS L on project_level_id = L.level_id ";
  }
  $query.=$mask.";";
  return $query;
}

function SQL_getWorkshopName($id) {
  $query ="SELECT workshop_denomination FROM " . T_WORK . " WHERE workshop_id='".$id."';";
  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;
}

if(!function_exists('SQL_getonescaleDenomination')) {
	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;
}

function SQL_getSdiName($id) {
  $query = "SELECT sdii_name FROM " . T_SDI_INFO . " WHERE sdii_id='".$id."';";
  return $query;
}

function SQL_getProjectSdi($id) {
  $query = 	"SELECT sdii_name, sdii_id FROM " . J_PROJECT_SDI . " AS J ".
    		 		"LEFT OUTER JOIN ".T_SDI_INFO." AS S on S.sdii_id=J.jps_sdi_id ".
            "WHERE jps_project_id='".$id."';";
  return $query;
}

function SQL_getActorsList($filter = false ) {

    if($filter) $query = "SELECT DISTINCT(actor_id), actor_name, jpa_type FROM ". T_ACTOR." LEFT OUTER JOIN  ". J_PROJECT_ACTOR." ON actor_id = jpa_actor_id WHERE jpa_type = '".$filter."';";
    else $query = "SELECT DISTINCT(actor_id), actor_name FROM ". T_ACTOR." ORDER BY actor_name ASC;";

	return $query;
}

function SQL_getActorsListWithOccurrences() {
    
    $query = "SELECT actor_id, actor_name, COUNT(*) as actor_occ FROM ". T_ACTOR." LEFT OUTER JOIN  ". J_PROJECT_ACTOR." ON actor_id = jpa_actor_id GROUP BY actor_id ORDER BY actor_name ASC;";
    
    return $query;
}

function SQL_getSCales() {

	$query=	"SELECT scale_denomination as scale_name, scale_id FROM ". T_SCALE ." WHERE scale_statut = 'P';";

	return $query;
}

function SQL_getPriorities() {
    
    $query=	"SELECT priority_id, priority_name FROM ". T_PRIORITY .";";
    
    return $query;
}

function SQL_getFinalities($id = false) {

	if(is_numeric($id)) {
		$query=	"SELECT *, IF(jpf_value IS NULL, 'N', jpf_value) jpf_value_setted FROM ". T_FINALITY." AS F LEFT OUTER JOIN ". J_PROJECT_FINALITY." ON F.finality_id = jpf_finality_id WHERE finality_status = 'P' AND jpf_project_id=" . $id . " ORDER BY finality_id;";
	} else {
		$query=	"SELECT * FROM ". T_FINALITY." WHERE finality_status = 'P';";
		// $query=	"SELECT *, IF(jpf_value IS NULL, 'N', jpf_value) jpf_value_setted FROM ". T_FINALITY." AS F LEFT OUTER JOIN ". J_PROJECT_FINALITY." ON F.finality_id = jpf_finality_id WHERE finality_status = 'P' ORDER BY finality_id;";
	}

	return $query;
}

function SQL_getAssociatedActors($id, $type) {
	$query = 	"SELECT A.actor_id, A.actor_name, J.jpa_id FROM " . J_PROJECT_ACTOR . " AS J ".
						"LEFT OUTER JOIN ".T_ACTOR." AS A on A.actor_id=J.jpa_actor_id ".
						"WHERE jpa_project_id=" . $id . " AND jpa_type='" . $type . "'  ORDER BY J.jpa_id ASC;";
						
	return $query;
}

function SQL_getAssociatedManagers($id) {
	$query = 	"SELECT U.user_id, U.user_login, J.jpm_id, P.profile_firstname, P.profile_lastname, P.profile_email  FROM " . J_PROJECT_MANAGER . " AS J ".
			"LEFT OUTER JOIN ".T_USER." AS U on U.user_id = J.jpm_manager_id ".
			"LEFT OUTER JOIN ".T_PROFILE." AS P on P.profile_id = U.user_id ".
			"WHERE jpm_project_id=" . $id . " ORDER BY J.jpm_id ASC;";
	
	return $query;
}

function SQL_getProjectManagersList()
{

  $query=	"SELECT U.user_login, P.profile_email, P.profile_firstname, P.profile_lastname, U.user_login 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_project = 'O' OR R.rights_project = 'A') ORDER BY U.user_login ASC;";

  return $query;
}

?>