<?php
/**
 * SQL queries
 * 
 * Main SQL queries - embedded in both admin / public UIs
 * 
 * @package linea21\core\admin
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

function SQL_Get_UserInfo4Auth($login, $pass) {
  $q = "SELECT U.user_id, U.user_login,	U.user_password, U.user_last_con, R.*, P.profile_firstname, P.profile_lastname  FROM " . T_USER . " AS U LEFT OUTER JOIN " . T_RIGHT . " AS R ON U.user_rights =R.rights_id LEFT OUTER JOIN " . T_PROFILE . " AS P ON  U.user_profile=P.profile_id WHERE lower(user_login) = '".strtolower($login)."' AND user_password='".$pass."' AND user_validity='Y';";
  return $q;
}

function SQL_Get_UserFromRememberMe($cookiehash, $login) {
	$q = "SELECT U.user_id, U.user_login,	U.user_password, U.user_last_con, R.*, P.profile_firstname, P.profile_lastname  FROM " . T_USER . " AS U LEFT OUTER JOIN " . T_RIGHT . " AS R ON U.user_rights =R.rights_id LEFT OUTER JOIN " . T_PROFILE . " AS P ON  U.user_profile=P.profile_id WHERE user_cookiehash = '".$cookiehash."' AND lower(user_login) = '".strtolower($login)."' AND user_validity='Y';";
	return $q;
}

function SQL_Get_isWorkgroupUser($id) {
  $q = "SELECT user_id ,  jwu_workshop_id FROM " . T_USER . " As U LEFT OUTER JOIN " . J_WORK_USERS . " As W ON U.user_id=W.jwu_user_id WHERE user_id='".$id."' AND (jwu_user_right = 'O' OR jwu_user_right = 'U')  AND user_validity='Y';";
  return $q;
}

function SQL_Get_isWorkgroupPendingUser($id) {
	$q = "SELECT user_id ,  jwu_workshop_id FROM " . T_USER . " As U LEFT OUTER JOIN " . J_WORK_USERS . " As W ON U.user_id=W.jwu_user_id WHERE user_id='".$id."' AND (jwu_user_right = 'A')  AND user_validity='Y';";
	return $q;
}

function SQL_Get_isWorkgroupOrganiser($id) {
	$q = "SELECT user_id ,  jwu_workshop_id FROM " . T_USER . " As U LEFT OUTER JOIN " . J_WORK_USERS . " As W ON U.user_id=W.jwu_user_id WHERE user_id='".$id."' AND user_validity='Y' AND jwu_user_right='O';";
	return $q;
}

function SQL_Get_isProjectManager($id) {
	$q = "SELECT user_id ,  jpm_project_id FROM " . T_USER . " As U LEFT OUTER JOIN " . J_PROJECT_MANAGER . " As P ON U.user_id=P.jpm_manager_id WHERE user_id='".$id."' AND user_validity='Y';";
	return $q;
}

function SQL_get_UserInfo($login) {
  $q = "SELECT U.user_id, U.user_login, U.user_password, SUBSTRING(U.user_password, 1, 2) AS salt, P.profile_email FROM " . T_USER . " AS U LEFT OUTER JOIN " . T_PROFILE . " AS P ON  U.user_profile=P.profile_id WHERE lower(user_login) = '".strtolower($login)."' AND user_validity='Y';";
  return $q;
}

function SQL_get_UserInfoPK($passkey) {
  $q = "SELECT U.user_id, U.user_login, P.profile_email FROM " . T_USER . " AS U LEFT OUTER JOIN " . T_PROFILE . " AS P ON  U.user_profile=P.profile_id WHERE user_forget_passkey = '".$passkey."' AND user_validity='Y';";
  return $q;
}

function SQL_Get_UserWorkshop($login, $pass) {
	$query = "SELECT user_id ,  jwu_workshop_id FROM " . T_USER . " As U LEFT OUTER JOIN " . J_WORK_USERS . " As W ON U.user_id=W.jwu_user_id WHERE lower(user_login) = '".strtolower($login)."' AND user_password='".$pass."' AND user_validity='Y' AND jwu_user_right <> 'A';";
	return $query;
}

function SQL_Get_UserProject($login, $pass) {
	$query = "SELECT user_id ,  jpm_project_id FROM " . T_USER . " As U INNER JOIN " . J_PROJECT_MANAGER . " As P ON U.user_id=P.jpm_manager_id WHERE lower(user_login) = '".strtolower($login)."' AND user_password='".$pass."' AND user_validity='Y';";
	return $query;
}

function SQL_Get_ProjectIndicators($array) {
	
	$clause = '';
	$sep = '';
	
	foreach ($array as $el) {
		$clause .= $sep. 'jps_project_id = '. $el ;
		$sep = ' || ';
	}
	$query = "SELECT DISTINCT jps_sdi_id FROM " . J_PROJECT_SDI . " WHERE ". $clause . ";"; 

	return $query;
}


function SQL_Get_LatestConnectedUsers($limit) {
	$query = "SELECT user_id, user_login, user_last_con, DATE_FORMAT(user_last_con, '".  toStringSqlDate('long')."') AS user_last_con_display FROM " . T_USER . " WHERE user_last_con > NOW( ) - INTERVAL 15 MINUTE ORDER BY user_last_con DESC LIMIT 0 , ".$limit . ";";
	return $query;
}

function SQL_getTagsList() {
	$query=	"SELECT tag_id, tag_name FROM ". T_TAG.";";
	return $query;
}

function SQL_getSuperAdmins() {
	$q = "SELECT U.user_id, U.user_login,	P.profile_email, P.profile_firstname, P.profile_lastname  FROM " . T_USER . " AS U LEFT OUTER JOIN " . T_PROFILE . " AS P ON  U.user_profile=P.profile_id WHERE U.user_category = 1 AND user_validity='Y';";
	return $q;
}

function SQL_getEmailQueue($limit) {
	$query=	"SELECT * FROM ". T_EMAIL_QUEUE." WHERE eq_status = 'unsent' ORDER BY eq_priority ASC, eq_id ASC LIMIT 0 , " .$limit . ";";
	return $query;
}

?>