<?php
/**
 * User module
 * 
 * MySQL queries related to user module
 * 
 * @package linea21\core\user
 * @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_quickbox_add = "SELECT user_id, user_login, profile_firstname, profile_lastname, user_date_crea, DATE_FORMAT(user_last_modify, '".toStringSqlDate('long')."') AS user_last_modify_display, DATE_FORMAT(user_date_crea, '".toStringSqlDate()."') AS user_date_crea_display FROM " . T_USER . " LEFT OUTER JOIN ".T_PROFILE." ON user_profile=profile_id  WHERE user_validity <> 'N' ORDER BY user_date_crea DESC, user_login ASC LIMIT 0 , 10;";
$req_quickbox_list = "SELECT user_id, user_login, profile_firstname, profile_lastname, user_last_modify, DATE_FORMAT(user_last_modify, '".toStringSqlDate('long')."') AS user_last_modify_display, DATE_FORMAT(user_date_crea, '".toStringSqlDate()."') AS user_date_crea_display FROM " . T_USER . " LEFT OUTER JOIN ".T_PROFILE." ON user_profile=profile_id  WHERE user_validity <> 'N' ORDER BY user_last_modify DESC, user_login ASC LIMIT 0 , 10;";
$req_quickbox_sup = "SELECT user_id, user_login, profile_firstname, profile_lastname, user_last_modify, DATE_FORMAT(user_last_modify, '".toStringSqlDate('long')."') AS user_last_modify_display, DATE_FORMAT(user_date_crea, '".toStringSqlDate()."') AS user_date_crea_display FROM " . T_USER . " LEFT OUTER JOIN ".T_PROFILE." ON user_profile=profile_id  WHERE user_validity = 'N' ORDER BY user_last_modify DESC, user_login ASC LIMIT 0 , 10;";

$req_list_group="SELECT catus_id, catus_name FROM " . T_CAT_USER . ";";
$req_alphabetical="SELECT DISTINCT UPPER(LEFT( user_login, 1)) AS first_letter FROM ". T_USER." WHERE user_validity<>'N' ORDER BY first_letter ASC;";
$req_nb_inscrit="SELECT COUNT(user_id) AS nb_inscrit FROM ". T_USER." WHERE user_validity<>'N'";

function SQL_getUserLogin($user_id) {
  $q="SELECT user_login, user_password FROM ". T_USER." WHERE user_validity<>'N' AND user_id='".$user_id."'";
  return $q;
}

function SQL_getAllUserInfo($user_id) {
  $q="SELECT U.user_login, U.user_password, U.user_old_logins, U.user_validity, U.user_category, R.*, P.*, DATE_FORMAT(profile_birthdate, '".toStringSqlDate()."') AS profile_birthdate, DATE_FORMAT(profile_last_modify, '".toStringSqlDate('long')."') AS profile_last_modify_display, DATE_FORMAT(user_last_con, '".toStringSqlDate('long')."') AS user_last_con_display, DATE_FORMAT(profile_date_crea, '".toStringSqlDate()."') AS profile_date_crea_display, C.catus_id, C.catus_name FROM ". T_USER." AS U LEFT OUTER JOIN ".T_RIGHT." AS R on R.rights_id=U.user_rights LEFT OUTER JOIN ".T_PROFILE." AS P on P.profile_id=U.user_profile LEFT OUTER JOIN ".T_CAT_USER." AS C on C.catus_id=U.user_category WHERE user_validity <> 'N' AND user_id='".$user_id."'";
  return $q;
}

function SQL_get_user_projects($user_id) {
    
    $q = "SELECT * FROM ". T_PROJECT ."
      		LEFT OUTER JOIN " . J_PROJECT_MANAGER . " ON project_id = jpm_project_id
      		WHERE jpm_manager_id ='".$user_id. "' AND project_statut <> 'E' ORDER BY project_published_date DESC;";
    
    return $q;
}

function SQL_get_projects_by_ids($array) {
    
    $filter = '';
    $sep = '';
    foreach($array as $el) {
        $filter .= $sep . 'project_id='.$el; 
        $sep = ' OR ';
    }
    $q = "SELECT * FROM ". T_PROJECT ."	WHERE 1 = 1 AND (".$filter. ");";

    return $q;
}

function SQL_getlistuser($debut, $limite = SELECT_LIMIT, $filter='', $search='' ) {
  if($search!='') {
    switch ($filter) {
      case 'ALPHA':
        $mask=" AND lower(user_login) LIKE lower('\\".$search."%') ";
        break;
      case 'GROUP':
        $mask=" AND user_category='".$search."' ";
        break;
      default:
        $mask='';
        break;
    }
  } else $mask='';
  $q= "SELECT U.user_login, U.user_password, U.user_id, P.profile_firstname, P.profile_lastname, P.profile_email, user_date_crea, DATE_FORMAT(user_date_crea, '".toStringSqlDate()."') AS user_date_crea_display, C.catus_name FROM " . T_USER . " AS U LEFT OUTER JOIN ".T_CAT_USER." AS C ON U.user_category=C.catus_id LEFT OUTER JOIN ".T_PROFILE." AS P ON U.user_profile=P.profile_id WHERE user_validity<>'N'".$mask." ORDER BY user_date_crea DESC, user_login ASC LIMIT ".$debut." , ".$limite.";";
  return $q;
}

function SQLCount_getlistuser($filter='', $search='' ) {

  if($search!='') {
    switch ($filter) {
      case 'ALPHA':
        $mask=" AND lower(user_login) LIKE lower('\\".$search."%') ";
        break;
      case 'GROUP':
        $mask=" AND user_category='".$search."' ";
        break;
      default:
        $mask='';
        break;
    }
  } else $mask='';
  $q= "SELECT COUNT(user_login) AS num_rows FROM " . T_USER . " WHERE user_validity<>'N'".$mask.";";
  return $q;
}

function SQL_Get_WorkshopsDenomination($array) {

  $str='';
  $sep='';
  foreach($array as $v) {
    $str .= $sep. 'workshop_id='.$v;
    $sep = ' OR ';
  }
  $query = "SELECT workshop_denomination, workshop_id, workshop_statut, workshop_range FROM ". T_WORK ." WHERE (". $str .") AND workshop_statut <> 'E' ORDER BY workshop_range DESC, workshop_id DESC";

  return $query;
}

function SQL_getWorkgroupsList($force_status = false) {
	
  if($force_status) $force_status = " AND ". sql_status_filter('workshop_statut', $force_status);
  else $force_status = "";
	
  $q = "SELECT workshop_id as id, workshop_denomination as name, workshop_statut, workshop_range FROM ". T_WORK ." WHERE 1=1". $force_status." ORDER BY workshop_range ASC;"; // , workshop_statut ='P' DESC, workshop_statut ='PA' DESC, workshop_statut ='AA' DESC
  return $q;
}

function SQL_getUserWorkgroups($id) {
	$q = 	"SELECT workshop_id as id, workshop_denomination as name, workshop_range FROM ". J_WORK_USERS .
	        " LEFT OUTER JOIN " . T_WORK . " ON jwu_workshop_id = workshop_id " .
			" WHERE jwu_user_id = '".$id."' AND jwu_user_right != 'A' AND workshop_statut='P' ORDER BY workshop_range ASC, workshop_denomination ASC;";

	return $q;
}

function SQL_getUserNotifyExceptionsSettings($id) {
  $q = 	"SELECT CONCAT_WS('-',type,workshop_id) as exception FROM ". T_WORK_NOTIFY .	" WHERE user_id = '".$id."';";

  return $q;
}


if(!function_exists('SQL_getProjectsList')) {
    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_indic_order, ".
            "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;
    }
}
?>