<?php
/**
 * Directory module
 * 
 * MySQL queries related to directory module
 * 
 * @package linea21\modules\yellowpages
 * @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_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, DATE_FORMAT(yellowp_last_modify, '".toStringSqlDate('long')."') AS yellowp_last_modify, yellowp_last_modify AS yellowp_last_modify2, DATE_FORMAT(yellowp_date_crea, '".toStringSqlDate()."') AS yellowp_date_crea
FROM " . T_YELLOWPAGES . "
WHERE yellowp_statut <> 'E'
ORDER BY yellowp_last_modify2 DESC
LIMIT 0 , 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(LEFT(yellowp_name, 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, LEFT(Y.yellowp_name, 60) AS yellowp_name, Y.yellowp_statut, DATE_FORMAT(Y.yellowp_date_crea, '".toStringSqlDate('long')."') AS yellowp_date_crea_display FROM " . T_YELLOWPAGES." AS Y ".
  $mask." ORDER BY Y.yellowp_name LIMIT ".$debut." , ".$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, LEFT(Y.yellowp_name, 60) AS yellowp_name, T.theme_name, yellowp_activity, SD.sdtheme_name, SD.sdtheme_id, Y.yellowp_statut, DATE_FORMAT(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 LIMIT ".$debut." , ".$limite.";";
  return $requete;
}
function SQL_get_req_alphabetical(){
  $req_alphabetical="SELECT DISTINCT UCASE(LEFT(yellowp_name, 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, $force_status = false) {

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

  $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, yellowp_latitude, yellowp_longitude, DATE_FORMAT(yellowp_last_modify, '".toStringSqlDate('long')."') AS yellowp_last_modify, DATE_FORMAT(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 ."'". $force_status.";";
  return $req_det;
}

function SQL_getlistyp($debut, $limite) {
  $mask="";
  if ($limite==true) {
    $mask= "LIMIT $debut , ". 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= "LIMIT $debut , ". 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= "LIMIT $debut , ". 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= "LIMIT $debut , ". SELECT_LIMIT;
  }
  $req_liststatut= "SELECT * FROM " . T_YELLOWPAGES . " WHERE yellowp_statut='$statut' ORDER BY yellowp_name $mask ;";
  return $req_liststatut;
}

?>