<?php
/*************************************************************************************************************
* Copyright (C) 2007 Paul Bringetto
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
*
**************************************************************************************************************/
class geoitem {
var $navresultset = Array();
var $itemresultset = Array();
var $breadcrumb;
var $displaytitle;
var $ID;
var $geoID;
var $root;
var $rooturl;
var $region;
var $subregion;
var $item;
var $country;
var $adm1;
var $locality;
var $LATITUDE;
var $LONGITUDE;
var $MAXLATITUDE;
var $MAXLONGITUDE;
var $MINLATITUDE;
var $MINLONGITUDE;
var $maptype;
var $item_blurb;
var $item_desc;
var $item_id;
var $url;
var $item_geodata_table;
var $item_geodata_table_UFI;
var $item_geodata_table_UNI;
var $item_geodata_table_id;
var $item_geodata_table_display_id;
var $item_geodata_table_lat;
var $item_geodata_table_lng;
var $item_data_table;
var $item_data_table_id;
var $item_data_table_name;
var $item_data_table_type;
var $item_data_table_description;
var $item_data_table_inactive;
var $item_data_table_inactive_value;
var $item_data_table_fax;
var $item_data_table_address;
var $item_data_table_postal;
var $item_data_table_phone;
var $item_data_table_email;
var $item_data_table_url;
var $item_fax;
var $item_address;
var $item_postal;
var $item_phone;
var $item_email;
var $item_url;
var $item_limit;
var $item_path;
function sel_items_by_bounds() {
global $db;
$sql = "SELECT g.".$this->item_data_table_id." AS item_id,
g.".$this->item_data_table_name." AS item_name,a.ID,
g.".$this->item_data_table_type." AS item_type,
g.".$this->item_data_table_description." AS description,
f.".$this->item_geodata_table_display_id." AS itemID,
f.".$this->item_geodata_table_lat." AS LATITUDE,
f.".$this->item_geodata_table_lng." AS LONGITUDE,
a.ID AS REGIONID,a.REGION_NAME,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
AND (((f.".$this->item_geodata_table_lat." > (".$this->MINLATITUDE.")) AND (f.".$this->item_geodata_table_lat." < (".$this->MAXLATITUDE.")))
AND ((f.".$this->item_geodata_table_lng." > (".$this->MINLONGITUDE.")) AND (f.".$this->item_geodata_table_lng." < (".$this->MAXLONGITUDE."))))
ORDER BY RAND()
LIMIT 0,".$this->item_limit."; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_items by bounds\n".$sql);
$this->itemresultset = $db->query($sql);
}
function sel_items() {
global $db;
$sql = "SELECT g.".$this->item_data_table_id." AS item_id,
g.".$this->item_data_table_name." AS item_name,a.ID,
g.".$this->item_data_table_type." AS item_type,
f.".$this->item_geodata_table_display_id." AS itemID,
a.ID AS REGIONID,a.REGION_NAME,a.LATITUDE,a.LONGITUDE,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
ORDER BY RAND()
LIMIT 0,".$this->item_limit."; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_items\n".$sql);
$this->itemresultset = $db->query($sql);
}
function sel_item() {
global $db;
$sql = "SELECT g.".$this->item_data_table_id." AS item_id,
g.".$this->item_data_table_name." AS item_name,
g.".$this->item_data_table_description." AS item_desc,
g.".$this->item_data_table_type." AS item_type,
g.".$this->item_data_table_fax." AS item_fax,
g.".$this->item_data_table_address." AS item_address,
g.".$this->item_data_table_postal." AS item_postal,
g.".$this->item_data_table_phone." AS item_phone,
g.".$this->item_data_table_email." AS item_email,
g.".$this->item_data_table_url." AS item_url,
a.ID AS REGIONID,a.REGION_NAME,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND,
f.".$this->item_geodata_table_lat." AS LATITUDE,f.".$this->item_geodata_table_lng." AS LONGITUDE,
a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE,e.SORT_NAME AS ID
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
AND f.".$this->item_geodata_table_display_id." = '".$this->item."'; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_item\n".$sql);
$result = $db->query($sql);
$this->ID = $result[0]->ID;
$this->country = $result[0]->CC_FULL_NAME;
$this->adm1 = $result[0]->ADM1_FULL_NAME_ND1;
$this->locality = $result[0]->FULL_NAME_ND;
$this->LATITUDE = $result[0]->LATITUDE;
$this->LONGITUDE = $result[0]->LONGITUDE;
$this->MAXLATITUDE = $result[0]->MAXLATITUDE;
$this->MAXLONGITUDE = $result[0]->MAXLONGITUDE;
$this->MINLATITUDE = $result[0]->MINLATITUDE;
$this->MINLONGITUDE = $result[0]->MINLONGITUDE;
$this->displaytitle = ucwords(strtolower($result[0]->item_name));
$this->item_blurb = $result[0]->item_blurb;
$this->item_desc = $result[0]->item_desc;
$this->item_type = $result[0]->item_type;
$this->item_id = $result[0]->item_id;
$this->url = $result[0]->url;
$this->item_fax = $result[0]->item_fax;
$this->item_address = $result[0]->item_address;
$this->item_postal = $result[0]->item_postal;
$this->item_phone = $result[0]->item_phone;
$this->item_email = $result[0]->item_email;
$this->item_url = $result[0]->item_url;
$this->breadcrumb = "<a href='".$this->rooturl."'>".$this->root."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."'>".$result[0]->REGION_NAME."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."'>".$result[0]->SUBREGION_NAME."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."/".$result[0]->CCID."'>".ucwords(strtolower($result[0]->CC_FULL_NAME))."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."/".$result[0]->CCID."/".$result[0]->ADM1ID."'>".$result[0]->ADM1_FULL_NAME_ND."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."/".$result[0]->CCID."/".$result[0]->ADM1ID."/".$result[0]->cityID."'>".$result[0]->FULL_NAME_ND."</a>";
return $result;
}
}
class geoitemlocality {
function sel_items_by_locality() {
global $db;
$sql = "SELECT f.".$this->item_geodata_table_display_id." AS ID,
g.".$this->item_data_table_name." AS item_name,
g.".$this->item_data_table_type." AS item_type,
f.".$this->item_geodata_table_display_id." AS itemID,
a.ID AS REGIONID,a.REGION_NAME,
f.".$this->item_geodata_table_lat." AS LATITUDE,f.".$this->item_geodata_table_lng." AS LONGITUDE,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND,
CONCAT('".$this->item_path."','/',g.".$this->item_data_table_type.",'/',f.".$this->item_geodata_table_display_id.") AS PATH
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE c.ID = '".$this->country."'
AND d.ID = '".$this->adm1."'
AND NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
AND e.SORT_NAME = '".$this->locality."'; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_items_by_locality\n".$sql);
$this->itemresultset = $db->query($sql);
}
function sel_locality() {
global $db;
$sql = "SELECT h.ID AS REGIONID,h.REGION_NAME,
g.ID AS SUBREGIONID,g.SUBREGION_NAME,
d.ID AS CCID,d.CC_FULL_NAME,
c.ID AS ADM1ID,c.ADM1_FULL_NAME_ND,
a.SORT_NAME AS ID,a.FULL_NAME_ND,
a.LATITUDE,a.LONGITUDE, a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE
FROM geo_cities a
INNER JOIN geo_adm1 c ON a.CC_FIPS = c.CC_FIPS AND c.ADM1 = a.ADM1
INNER JOIN geo_country d ON d.CC_FIPS = a.CC_FIPS
INNER JOIN geo_subregion g ON g.SUBREGION = d.SUBREGION
INNER JOIN geo_region h ON h.REGION = g.REGION
INNER JOIN ".$this->item_geodata_table." f ON a.UFI = f.".$this->item_geodata_table_UFI." AND a.UNI = f.".$this->item_geodata_table_UNI."
WHERE d.ID = '".$this->country."'
AND c.ID = '".$this->adm1."'
AND a.SORT_NAME = '".$this->locality."'; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_locality\n".$sql);
$result = $db->query($sql);
$this->LATITUDE = $result[0]->LATITUDE;
$this->LONGITUDE = $result[0]->LONGITUDE;
$this->MAXLATITUDE = $result[0]->MAXLATITUDE;
$this->MINLATITUDE = $result[0]->MINLATITUDE;
$this->MAXLONGITUDE = $result[0]->MAXLONGITUDE;
$this->MINLONGITUDE = $result[0]->MINLONGITUDE;
$this->displaytitle = ucwords(strtolower($result[0]->FULL_NAME_ND));
$this->breadcrumb = "<a href='".$this->rooturl."'>".$this->root."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."'>".$result[0]->REGION_NAME."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."'>".$result[0]->SUBREGION_NAME."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."/".$result[0]->CCID."'>".ucwords(strtolower($result[0]->CC_FULL_NAME))."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."/".$result[0]->CCID."/".$result[0]->ADM1ID."'>".$result[0]->ADM1_FULL_NAME_ND."</a>"; return $result;
}
function sel_localities() {
global $db;
$sql = "SELECT a.UFI,a.UNI,a.FULL_NAME_ND AS NAME,a.ADM2_FULL_NAME_ND,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE,
h.REGION_NAME,g.SUBREGION_NAME,COUNT(b.".$this->item_geodata_table_id.") AS c,d.CC_FULL_NAME,c.ADM1_FULL_NAME_ND,LCASE(a.SORT_NAME) AS ID,CONCAT(h.ID,'/',g.ID,'/',d.ID,'/',c.ID,'/',LCASE(a.SORT_NAME)) AS PATH
FROM geo_cities a
INNER JOIN ".$this->item_geodata_table." b ON a.UFI = b.".$this->item_geodata_table_UFI." AND a.UNI = b.".$this->item_geodata_table_UNI."
INNER JOIN geo_adm1 c ON a.CC_FIPS = c.CC_FIPS AND c.ADM1 = a.ADM1
INNER JOIN geo_country d ON d.CC_FIPS = a.CC_FIPS
INNER JOIN geo_subregion g ON g.SUBREGION = d.SUBREGION
INNER JOIN geo_region h ON h.REGION = g.REGION
WHERE d.ID = '".$this->country."'
AND c.ID = '".$this->adm1."'
GROUP BY a.UFI,a.UNI,a.FULL_NAME_ND,a.ADM2_FULL_NAME_ND,a.LATITUDE,a.LONGITUDE; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_localities\n".$sql);
$this->navresultset = $db->query($sql);
}
}
class geoitemadm1 {
function sel_items_by_adm1() {
global $db;
$sql = "SELECT g.".$this->item_data_table_id." AS item_id,
g.".$this->item_data_table_name." AS item_name,LCASE(e.SORT_NAME) AS ID,
g.".$this->item_data_table_type." AS item_type,
f.".$this->item_geodata_table_display_id." AS itemID,
a.ID AS REGIONID,a.REGION_NAME,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND,
f.".$this->item_geodata_table_lat." AS LATITUDE,f.".$this->item_geodata_table_lng." AS LONGITUDE
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE c.ID = '".$this->country."'
AND d.ID = '".$this->adm1."'
AND NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
ORDER BY RAND()
LIMIT 0,".$this->item_limit."; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_items_by_adm1\n".$sql);
$this->itemresultset = $db->query($sql);
}
function sel_adm1() {
global $db;
$sql = "SELECT a.ID,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE,a.ADM1_FULL_NAME_ND,
h.ID AS REGIONID,h.REGION_NAME,
g.ID AS SUBREGIONID,g.SUBREGION_NAME,
b.ID AS CCID,b.CC_FULL_NAME
FROM geo_adm1 a
INNER JOIN geo_cities e ON a.CC_FIPS = e.CC_FIPS AND a.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN geo_country b ON b.CC_FIPS = a.CC_FIPS
INNER JOIN geo_subregion g ON g.SUBREGION = b.SUBREGION
INNER JOIN geo_region h ON h.REGION = g.REGION
WHERE b.ID = '".$this->country."'
AND a.ID = '".$this->adm1."'; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_adm1\n".$sql);
$result = $db->query($sql);
$this->LATITUDE = $result[0]->LATITUDE;
$this->LONGITUDE = $result[0]->LONGITUDE;
$this->MAXLATITUDE = $result[0]->MAXLATITUDE;
$this->MAXLONGITUDE = $result[0]->MAXLONGITUDE;
$this->MINLATITUDE = $result[0]->MINLATITUDE;
$this->MINLONGITUDE = $result[0]->MINLONGITUDE;
$this->displaytitle = ucwords(strtolower($result[0]->ADM1_FULL_NAME_ND));
$this->breadcrumb = "<a href='".$this->rooturl."'>".$this->root."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."'>".$result[0]->REGION_NAME."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."'>".$result[0]->SUBREGION_NAME."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."/".$result[0]->CCID."'>".ucwords(strtolower($result[0]->CC_FULL_NAME))."</a>";
}
function sel_adm1s() {
global $db;
$sql = "SELECT a.ADM1_FULL_NAME_ND AS NAME,a.LATITUDE,a.LONGITUDE,COUNT(f.".$this->item_geodata_table_id.") AS c,
b.CC_FULL_NAME,a.ID,CONCAT(h.ID,'/',g.ID,'/',b.ID,'/',a.ID) AS PATH
FROM geo_adm1 a
INNER JOIN geo_cities e ON a.CC_FIPS = e.CC_FIPS AND a.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN geo_country b ON b.CC_FIPS = a.CC_FIPS
INNER JOIN geo_subregion g ON g.SUBREGION = b.SUBREGION
INNER JOIN geo_region h ON h.REGION = g.REGION
WHERE b.ID = '".$this->country."'
GROUP BY a.ID,a.ADM1_FULL_NAME_ND,a.LATITUDE,a.LONGITUDE; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_adm1s\n".$sql);
$this->navresultset = $db->query($sql);
}
}
class geoitemcountry {
function sel_items_by_country() {
global $db;
$sql = "SELECT g.".$this->item_data_table_id." AS item_id,
g.".$this->item_data_table_name." AS item_name,d.ID,
g.".$this->item_data_table_type." AS item_type,
f.".$this->item_geodata_table_display_id." AS itemID,
a.ID AS REGIONID,a.REGION_NAME,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND,
d.LATITUDE,d.LONGITUDE
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE c.ID = '".$this->country."'
AND NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
LIMIT 0,".$this->item_limit."; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_items_by_country\n".$sql);
$this->itemresultset = $db->query($sql);
}
function sel_country() {
global $db;
$sql = "SELECT a.CC_FIPS,a.CC_FULL_NAME,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE,
h.ID AS REGIONID,h.REGION_NAME,
g.ID AS SUBREGIONID,g.SUBREGION_NAME,a.ID
FROM geo_country a
INNER JOIN geo_adm1 d ON a.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN geo_subregion g ON g.SUBREGION = a.SUBREGION
INNER JOIN geo_region h ON h.REGION = g.REGION
WHERE a.ID = '".$this->country."'; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_country\n".$sql);
$result = $db->query($sql);
$this->LATITUDE = $result[0]->LATITUDE;
$this->LONGITUDE = $result[0]->LONGITUDE;
$this->MAXLATITUDE = $result[0]->MAXLATITUDE;
$this->MAXLONGITUDE = $result[0]->MAXLONGITUDE;
$this->MINLATITUDE = $result[0]->MINLATITUDE;
$this->MINLONGITUDE = $result[0]->MINLONGITUDE;
$this->displaytitle = ucwords(strtolower($result[0]->CC_FULL_NAME));
$this->breadcrumb = "<a href='".$this->rooturl."'>".$this->root."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."'>".$result[0]->REGION_NAME."</a> >
<a href='".$this->rooturl."/".$result[0]->REGIONID."/".$result[0]->SUBREGIONID."'>".$result[0]->SUBREGION_NAME."</a>";
}
function sel_countries() {
global $db;
$sql = "SELECT a.CC_FIPS,a.CC_FULL_NAME AS NAME,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE,
COUNT(f.".$this->item_geodata_table_id.") AS c,a.ID,CONCAT(h.ID,'/',g.ID,'/',a.ID) AS PATH
FROM geo_country a
INNER JOIN geo_adm1 d ON a.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN geo_subregion g ON g.SUBREGION = a.SUBREGION
INNER JOIN geo_region h ON h.REGION = g.REGION
WHERE g.ID = '".$this->subregion."'
GROUP BY a.CC_FIPS,a.CC_FULL_NAME,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_countries\n".$sql);
$this->navresultset = $db->query($sql);
}
}
class geoitemsubregion {
function sel_items_by_subregion() {
global $db;
$sql = "SELECT g.".$this->item_data_table_id." AS item_id,
g.".$this->item_data_table_name." AS item_name,c.ID,
g.".$this->item_data_table_type." AS item_type,
f.".$this->item_geodata_table_display_id." AS itemID,
a.ID AS REGIONID,a.REGION_NAME,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND,
c.LATITUDE,c.LONGITUDE
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE b.ID = '".$this->subregion."'
AND NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
ORDER BY RAND()
LIMIT 0,".$this->item_limit."; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_items_by_subregion\n".$sql);
$this->itemresultset = $db->query($sql);
}
function sel_subregion() {
global $db;
$sql = "SELECT a.SUBREGION_NAME,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE,
g.REGION_NAME,g.ID AS REGIONID,a.ID
FROM geo_subregion a
INNER JOIN geo_country c ON a.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN geo_region g ON g.REGION = a.REGION
WHERE REPLACE(LCASE(a.SUBREGION_NAME),' ','') = '".$this->subregion."'; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_subregion\n".$sql);
$result = $db->query($sql);
$this->LATITUDE = $result[0]->LATITUDE;
$this->LONGITUDE = $result[0]->LONGITUDE;
$this->MAXLATITUDE = $result[0]->MAXLATITUDE;
$this->MAXLONGITUDE = $result[0]->MAXLONGITUDE;
$this->MINLATITUDE = $result[0]->MINLATITUDE;
$this->MINLONGITUDE = $result[0]->MINLONGITUDE;
$this->displaytitle = $result[0]->SUBREGION_NAME;
$this->breadcrumb = "<a href='".$this->rooturl."'>".$this->root."</a> > <a href='".$this->rooturl."/".$result[0]->REGIONID."'>".$result[0]->REGION_NAME."</a>";
}
function sel_subregions() {
global $db;
$sql = "SELECT a.SUBREGION_NAME AS NAME,a.LATITUDE,a.LONGITUDE,COUNT(f.".$this->item_geodata_table_id.") AS c,a.ID,CONCAT(g.ID,'/',a.ID) AS PATH
FROM geo_subregion a
INNER JOIN geo_country c ON a.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN geo_region g ON g.REGION = a.REGION
WHERE g.REGION_NAME = '".$this->region."'
GROUP BY a.SUBREGION,a.SUBREGION_NAME,a.LATITUDE,a.LONGITUDE,g.REGION_NAME; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_subregions\n".$sql);
$this->navresultset = $db->query($sql);
}
}
class geoitemregion {
function sel_items_by_region() {
global $db;
$sql = "SELECT g.".$this->item_data_table_id." AS item_id,
g.".$this->item_data_table_name." AS item_name,b.ID,
g.".$this->item_data_table_type." AS item_type,
f.".$this->item_geodata_table_display_id." AS itemID,
a.ID AS REGIONID,a.REGION_NAME,
b.ID AS SUBREGIONID,b.SUBREGION_NAME,
c.ID AS CCID,c.CC_FULL_NAME,
d.ID AS ADM1ID,d.ADM1_FULL_NAME_ND,
LCASE(e.SORT_NAME) AS cityID,e.FULL_NAME_ND,
b.LATITUDE,b.LONGITUDE
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
INNER JOIN ".$this->item_data_table." g ON g.".$this->item_data_table_id." = f.".$this->item_geodata_table_id."
WHERE a.REGION_NAME = '".$this->region."'
AND NOT(g.".$this->item_data_table_inactive." = '".$this->item_data_table_inactive_value."')
ORDER BY RAND()
LIMIT 0,".$this->item_limit."; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_items_by_region\n".$sql);
$this->itemresultset = $db->query($sql);
}
function sel_region() {
global $db;
$sql = "SELECT a.REGION_NAME,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,
a.MAXLONGITUDE,a.MINLONGITUDE,a.ID
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
WHERE a.REGION_NAME = '".$this->region."'; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_region\n".$sql);
$result = $db->query($sql);
$this->LATITUDE = $result[0]->LATITUDE;
$this->LONGITUDE = $result[0]->LONGITUDE;
$this->MAXLATITUDE = $result[0]->MAXLATITUDE;
$this->MAXLONGITUDE = $result[0]->MAXLONGITUDE;
$this->MINLATITUDE = $result[0]->MINLATITUDE;
$this->MINLONGITUDE = $result[0]->MINLONGITUDE;
$this->displaytitle = $result[0]->REGION_NAME;
$this->breadcrumb = "<a href='".$this->rooturl."'>".$this->root."</a>";
return $result;
}
function sel_regions() {
global $db;
$sql = "SELECT a.REGION,a.REGION_NAME AS NAME,COUNT(f.".$this->item_geodata_table_id.") AS c,a.ID,a.ID AS PATH,a.LATITUDE,a.LONGITUDE
FROM geo_region a
INNER JOIN geo_subregion b ON a.REGION = b.REGION
INNER JOIN geo_country c ON b.SUBREGION = c.SUBREGION
INNER JOIN geo_adm1 d ON c.CC_FIPS = d.CC_FIPS
INNER JOIN geo_cities e ON d.CC_FIPS = e.CC_FIPS AND d.ADM1 = e.ADM1
INNER JOIN ".$this->item_geodata_table." f ON e.UFI = f.".$this->item_geodata_table_UFI." AND e.UNI = f.".$this->item_geodata_table_UNI."
GROUP BY a.REGION,a.REGION_NAME,a.LATITUDE,a.LONGITUDE,a.MAXLATITUDE,a.MINLATITUDE,a.MAXLONGITUDE,a.MINLONGITUDE; ";
upd_log($_SERVER['DOCUMENT_ROOT']."/lib/php/sql.log","sel_regions\n".$sql);
$this->breadcrumb = "<a href='".$this->rooturl."'>".$this->root."</a>";
$this->LATITUDE = 0;
$this->LONGITUDE = 33;
$this->MAXLATITUDE = 0;
$this->MAXLONGITUDE = 0;
$this->MINLATITUDE = 0;
$this->MINLONGITUDE = 0;
$this->navresultset = $db->query($sql);
}
}
class geoitemdisplay {
function displaydata() {
if(strlen($this->region)<1) { $this->maptype = 1; } else { $this->maptype = 0;}
if($this->subregion) { unset($this->region); }
if($this->country) { unset($this->subregion); }
if($this->region) {
geoitemregion::sel_region();
geoitemsubregion::sel_subregions();
geoitemregion::sel_items_by_region();
} elseif ($this->subregion) {
geoitemsubregion::sel_subregion();
geoitemcountry::sel_countries();
geoitemsubregion::sel_items_by_subregion();
} elseif ($this->country && !$this->adm1) {
geoitemcountry::sel_country();
geoitemadm1::sel_adm1s();
geoitemcountry::sel_items_by_country();
} elseif ($this->country && $this->adm1 && !$this->locality) {
geoitemadm1::sel_adm1();
geoitemlocality::sel_localities();
geoitemadm1::sel_items_by_adm1();
} elseif ($this->country && $this->adm1 && $this->locality && !$this->item) {
geoitemlocality::sel_locality();
geoitemlocality::sel_items_by_locality();
} elseif (!$this->country && !$this->adm1 && !$this->locality && $this->item) {
geoitem::sel_item();
$this->maptype = 2;
} elseif ($this->MAXLATITUDE && $this->MAXLATITUDE && $this->MINLONGITUDE && $this->MAXLONGITUDE) {
geoitem::sel_items_by_bounds();
$this->maptype = 2;
} else {
geoitemregion::sel_regions();
geoitem::sel_items();
}
}
}
?>