<?php
//////////// Check Inclusion de pages //////////// 
if (!function_exists('AuthenthificationProcess')){ 
include_once("../lib/lib_common.php");
ReloadIndex('admin');
}
////////////

$req_add = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut<> 'E' AND scale_dependencies<".(MAX_SCALE_LEVEL-1)." ORDER BY scale_id;";

$req_quickbox_list = "SELECT yellowp_id, yellowp_name, to_char(yellowp_last_modify, 'DD-MM-YYYY à HH24:MI:SS') AS yellowp_last_modify, yellowp_last_modify AS yellowp_last_modify2, to_char(yellowp_date_crea, 'DD-MM-YYYY') AS yellowp_date_crea
FROM " . T_YELLOWPAGES . "
WHERE yellowp_statut <> 'E'
ORDER BY yellowp_last_modify2 DESC
OFFSET 0 LIMIT 10;";
$req_count_yp = "SELECT COUNT(yellowp_id) as num_rows FROM " . T_YELLOWPAGES . " WHERE yellowp_statut<>'E';";
$req_list_theme="SELECT T.theme_id, T.theme_name, T.theme_statut, SDT.sdtheme_name, SDT.sdtheme_id FROM " . T_THEME . " AS T LEFT OUTER JOIN ". T_SD_THEME ." AS SDT ON SDT.sdtheme_id=T.theme_sd_theme WHERE T.theme_statut  <> 'E' ORDER BY sdtheme_range DESC, sdtheme_name ASC, theme_name ASC;";
$req_alphabetical="SELECT DISTINCT upper(substring(yellowp_name from 1 for 1)) AS first_letter FROM ". T_YELLOWPAGES." WHERE yellowp_statut <> 'E' ORDER BY first_letter ASC;";

function SQL_getCountypList($filter= -1, $search= -1) {
	switch ($filter) {
		case 'P':
			$mask=" WHERE yellowp_statut = 'P'";
			break;
		case 'D':
			$mask=" WHERE yellowp_statut = 'D'";
			break;
		case 'AA':
			$mask=" WHERE yellowp_statut = 'AA'";
			break;
		case 'ALPHA':
		$mask=" WHERE yellowp_statut <> 'E' AND lower(yellowp_name) LIKE '\\".strtolower($search)."%' ";
		break;
		default:
			$mask=" WHERE yellowp_statut <> 'E'";
			break;
		}
		if($filter== -1) $mask=" WHERE yellowp_statut <> 'E'";
	$requete= "SELECT COUNT(yellowp_id) AS num_rows FROM " . T_YELLOWPAGES . $mask.";";
	return $requete;
}

function SQL_getypList($debut, $limite = SELECT_LIMIT, $filter= -1, $search= -1) {
	switch ($filter) {
		case 'P':
			$mask=" WHERE yellowp_statut = 'P'";
			break;
		case 'D':
			$mask=" WHERE yellowp_statut = 'D'";
			break;
		case 'AA':
			$mask=" WHERE yellowp_statut = 'AA'";
			break;
		case 'ALPHA':
		$mask=" WHERE yellowp_statut <> 'E' AND lower(yellowp_name) LIKE '\\".strtolower($search)."%' ";
		break;
		default:
			$mask=" WHERE yellowp_statut <> 'E'";
			break;
		}
		if($filter== -1) $mask=" WHERE yellowp_statut <> 'E'";
	$requete=	"SELECT Y.yellowp_id, substring(Y.yellowp_name from 0 for 60) AS yellowp_name, Y.yellowp_statut, to_char(Y.yellowp_date_crea, 'DD-MM-YYYY à HH24:MI:SS') AS yellowp_date_crea_display FROM " . T_YELLOWPAGES." AS Y ".
			 	$mask." ORDER BY Y.yellowp_name OFFSET ".$debut." LIMIT ".$limite.";";
	return $requete;
}

function SQL_get_Display_CountypList($filter= -1, $search= -1) {
	switch ($filter) {
		case 'theme':
			$mask=" WHERE yellowp_statut = 'P' AND SD.sdtheme_id =".$search;
			break;
			
		case 'alpha':
		$mask=" WHERE yellowp_statut ='P' AND lower(yellowp_name) LIKE '\\".strtolower($search)."%' ";
		break;
		default:
			$mask=" WHERE yellowp_statut ='P'";
			break;
		}
		if($filter== -1) $mask=" WHERE yellowp_statut ='P'";
	$requete= "SELECT COUNT(yellowp_id) AS num_rows FROM " . T_YELLOWPAGES." AS Y ".
	 " LEFT OUTER JOIN ". T_THEME. " AS T on Y.yellowp_theme=T.theme_id".
	 " LEFT OUTER JOIN ". T_SD_THEME. " AS SD on T.theme_sd_theme=SD.sdtheme_id"
	  . $mask.";";
	return $requete;
}

function SQL_get_Display_ypList($debut, $limite = SELECT_LIMIT, $filter= -1, $search= -1) {
	switch ($filter) {
		case 'theme':
			$mask=" WHERE yellowp_statut = 'P' AND SD.sdtheme_id =".$search;
			break;
			
		case 'alpha':
		$mask=" WHERE yellowp_statut ='P' AND lower(yellowp_name) LIKE '\\".strtolower($search)."%' ";
		break;
		default:
			$mask=" WHERE yellowp_statut ='P'";
			break;
		}
		if($filter== -1) $mask=" WHERE yellowp_statut ='P'";
	$requete=	"SELECT Y.yellowp_id, substring(Y.yellowp_name from 0 for 60) AS yellowp_name, T.theme_name, yellowp_activity, SD.sdtheme_name, SD.sdtheme_id, Y.yellowp_statut, to_char(Y.yellowp_date_crea, 'DD-MM-YYYY à HH24:MI:SS') AS yellowp_date_crea_display FROM " . T_YELLOWPAGES." AS Y ". 
		 " LEFT OUTER JOIN ". T_THEME. " AS T on Y.yellowp_theme=T.theme_id".
	 " LEFT OUTER JOIN ". T_SD_THEME. " AS SD on T.theme_sd_theme=SD.sdtheme_id".
	 $mask." ORDER BY Y.yellowp_name OFFSET ".$debut." LIMIT ".$limite.";";
	return $requete;
}
function SQL_get_req_alphabetical(){
$req_alphabetical="SELECT DISTINCT initcap(substring(yellowp_name from 0 for 1)) AS first_letter FROM ". T_YELLOWPAGES." WHERE yellowp_statut = 'P' ORDER BY first_letter ASC;";
return $req_alphabetical;
}
function SQL_get_req_list_theme(){
$req_list_theme="SELECT T.theme_id, T.theme_name, T.theme_statut, SDT.sdtheme_name, SDT.sdtheme_id FROM " . T_THEME . " AS T LEFT OUTER JOIN ". T_SD_THEME ." AS SDT ON SDT.sdtheme_id=T.theme_sd_theme WHERE T.theme_statut  ='P' ORDER BY sdtheme_range DESC, sdtheme_name ASC, theme_name ASC;";
return $req_list_theme;
}
function SQL_getdetyp($yp_id) {
$req_det="
SELECT yellowp_name, yellowp_activity, t.theme_name, yellowp_theme, yellowp_street, SD.sdtheme_name, SD.sdtheme_id, yellowp_postal_code, yellowp_city,  yellowp_country,  yellowp_phone,  yellowp_fax, yellowp_email,   yellowp_website, to_char(yellowp_last_modify, 'DD-MM-YYYY à HH24:MI:SS') AS yellowp_last_modify, to_char(yellowp_date_crea, 'DD-MM-YYYY') AS yellowp_date_crea,   yellowp_statut
FROM " . T_YELLOWPAGES . " 
LEFT  OUTER  JOIN " . T_THEME . " t ON yellowp_theme = t.theme_id 
LEFT OUTER JOIN ". T_SD_THEME. " AS SD on t.theme_sd_theme=SD.sdtheme_id
WHERE yellowp_id =". $yp_id .";";
return $req_det;
}

function SQL_getlistyp($debut, $limite) {
 $mask="";
if ($limite==true) {
 $mask= "OFFSET $debut LIMIT ". SELECT_LIMIT;
 }  
$req_listpre= "SELECT * FROM " . T_YELLOWPAGES . " WHERE yellowp_statut<>'E' ORDER BY yellowp_name $mask ;";
return $req_listpre;
}

function SQL_getlistyppre($debut, $limite) {
 $mask="";
if ($limite==true) {
 $mask= "OFFSET $debut LIMIT ". SELECT_LIMIT;
 }  
 $req_list2= "SELECT * FROM " . T_YELLOWPAGES . " WHERE yellowp_statut='D' ORDER BY yellowp_name $mask ;";
 return $req_list2;
}

function SQL_getlistyptheme($theme,$debut, $limite) {
 $mask="";
if ($limite==true) {
 $mask= "OFFSET $debut LIMIT ". SELECT_LIMIT;
 }  
	$req_listtheme= "SELECT * FROM " . T_YELLOWPAGES . " WHERE yellowp_theme=$theme ORDER BY yellowp_name $mask ;";
	return $req_listtheme;
}
function SQL_getOneyptheme($theme_id) { 
	$req_Onetheme= "SELECT theme_name FROM " . T_THEME . " WHERE theme_id=$theme_id ;";
	return $req_Onetheme;
}

function SQL_getlistypstatut($statut, $debut, $limite) {
 $mask="";
if ($limite==true) {
 $mask= "OFFSET $debut LIMIT ". SELECT_LIMIT;
 }  
	$req_liststatut= "SELECT * FROM " . T_YELLOWPAGES . " WHERE yellowp_statut='$statut' ORDER BY yellowp_name $mask ;";
	return $req_liststatut;
}

?>