<?php
/**
 * @package linea21.modules
 * @subpackage yellowpages
 * @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_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, '".toStringSqlDate('long')."') AS yellowp_last_modify, yellowp_last_modify AS yellowp_last_modify2, to_char(yellowp_date_crea, '".toStringSqlDate()."') 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(substring(Y.yellowp_name from 1 for 1)) = '".strtolower($search)."' ";
      break;
    default:
      $mask=" WHERE yellowp_statut <> 'E'";
      break;
  }
  if($filter== -1) $mask=" WHERE yellowp_statut <> 'E'";
  $query= "SELECT COUNT(yellowp_id) AS num_rows FROM " . T_YELLOWPAGES . $mask.";";
  return $query;
}

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(substring(Y.yellowp_name from 1 for 1)) = LIKE '".strtolower($search)."' ";
      break;
    default:
      $mask=" WHERE yellowp_statut <> 'E'";
      break;
  }
  if($filter== -1) $mask=" WHERE yellowp_statut <> 'E'";
  $query=	"SELECT Y.yellowp_id, substring(Y.yellowp_name from 0 for 60) AS yellowp_name, Y.yellowp_statut, to_char(Y.yellowp_date_crea, '".toStringSqlDate('long')."') AS yellowp_date_crea_display FROM " . T_YELLOWPAGES." AS Y ".
  $mask." ORDER BY Y.yellowp_name OFFSET ".$debut." LIMIT ".$limite.";";
  return $query;
}

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(substring(Y.yellowp_name from 1 for 1)) = '".strtolower($search)."' ";
      break;
    default:
      $mask=" WHERE yellowp_statut ='P'";
      break;
  }
  if($filter== -1) $mask=" WHERE yellowp_statut ='P'";
  $query= "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 $query;
}

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(substring(Y.yellowp_name from 1 for 1)) = '".strtolower($search)."' ";
      break;
    default:
      $mask=" WHERE yellowp_statut ='P'";
      break;
  }
  if($filter== -1) $mask=" WHERE yellowp_statut ='P'";
  $query=	"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, '".toStringSqlDate('long')."') 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 $query;
}
function SQL_get_req_alphabetical(){
  $req_alphabetical="SELECT DISTINCT upper(substring(yellowp_name from 1 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_getOneYpName($id) {
  $q =	"SELECT yellowp_name
      	FROM " . T_YELLOWPAGES . " 
      	WHERE yellowp_id =". $id .";";
  return $q;
}

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, '".toStringSqlDate('long')."') AS yellowp_last_modify, to_char(yellowp_date_crea, '".toStringSqlDate()."') 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;
}

?>
