<?php
/***************************************************************************
 *                                                                          *
 * Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
 *                                                                          *
 * This  is  commercial  software,  only  users  who have purchased a valid *
 * license  and  accept  to the terms of the  License Agreement can install *
 * and use this program.                                                    *
 *                                                                          *
 ****************************************************************************
 * PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
 * "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE.            *
 ****************************************************************************/ //
//$Id: CityModel.php, v 1.0 2013/03/14 14:14:14
class CityModel extends Model {
    //Function to save city detils
    public function saveCityDetails($languageIDs, $POST, $cityID = 0, $cityCampaignListID = "") {
        if ($cityID)
            $languageID = $languageIDs['Current'];
        else
            $languageID = $languageIDs['Default'];
        $cityStatus = $this->escape($POST['cityStatus']);
        $cityName   = $this->escape($POST['cityName']);
        $province   = $this->escape($POST['province']);
        $cityKeyID  = $this->escape($POST['cityKeyID']);
        $cityLat    = $this->escape($POST['cityLatitude']);
        $cityLng    = $this->escape($POST['cityLongitude']);
        if ($POST['citylogoImage']) {
            $citylogoImage = $cityKeyID . substr($POST['citylogoImage'], strpos($POST['citylogoImage'], "."));
        } else {
            $citylogoImage = '';
        }
        if ($POST['citybgImage']) {
            $citybgImage = $cityKeyID . substr($POST['citybgImage'], strpos($POST['citybgImage'], "."));
        } else {
            $citybgImage = '';
        }
        // For city based charities on 09/03/2012
        if ($POST['cityID']) {
            //NOW DEAL - add city lat & lng			
            $sql = "
			UPDATE 
				" . DB_PREFIX . "cities 
			SET 
				countryID='" . $POST['countryID'] . "',";
            if ($POST['stateSelectedType'] == 'state') {
                $sql .= "stateID='" . $POST['stateID'] . "'";
            } else if ($POST['stateSelectedType'] == 'province') {
                $sql .= "stateID=''";
            }
            $sql .= ",
				timeZoneID='" . $POST['timezoneID'] . "',
				cityKeyID='" . $cityKeyID . "',
				cityLat='" . $cityLat . "',
				cityLng='" . $cityLng . "', 
				cityStatus='" . $cityStatus . "', 
				citylogoImage='" . $citylogoImage . "',
				cityDefCurrencyID='" . $POST['cityDefCurrencyID'] . "',
				citybgImage='" . $citybgImage . "' 
			WHERE 
				cityID='" . $POST['cityID'] . "' ";
            $updateCity = $this->ObjDatabase->setQuery($sql);
            //updte city details				
            if ($this->isCityWithLanguageIDExists($languageID, $POST['cityID'])) {
                $sql                = "
				UPDATE 
					`" . DB_PREFIX . "city_details` 
				SET 
					cityName='" . $cityName . "',
					province='" . $province . "' 
				WHERE 
					cityID='" . $POST['cityID'] . "' 
					AND languageID='" . $languageID . "'";
                $updatedCityDetails = $this->ObjDatabase->setQuery($sql);
            } else {
                $sql                  = "
				INSERT INTO 
					`" . DB_PREFIX . "city_details`(
						`cityID`, 
						`languageID`, 
						`cityName`, 
						`province`
					)VALUES(
						'" . $POST['cityID'] . "',
						'" . $languageID . "',
						'" . $cityName . "','" . $province . "'
					)";
                $resInsertCityDetails = $this->ObjDatabase->setQuery($sql);
            }
            return true;
        } else {
            //NOW DEAL - add city lat & lng			
            $sql          = "
			 INSERT INTO 
			 	" . DB_PREFIX . "cities( 
					`countryID`, 
					`stateID`, 
					`cityProximity`,
					`timeZoneID`,
					`cityKeyID`,
					`cityLat`,
					`cityLng`, 
					`cityStatus`,
					`cityCampaignListID`, 
					`citylogoImage`, 
					`citybgImage`,
					`cityDefCurrencyID`
				)VALUES(
					'" . $POST['countryID'] . "',
					'" . $POST['stateID'] . "',
					'" . $POST['cityProximity'] . "',
					'" . $POST['timezoneID'] . "',
					'" . $cityKeyID . "',
					'" . $cityLat . "',
					'" . $cityLng . "',
					'" . $cityStatus . "', 
					'" . $cityCampaignListID . "',
					'" . $citylogoImage . "',
					'" . $citybgImage . "',
					'" . $POST['cityDefCurrencyID'] . "'
				)";
            $insertCities = $this->ObjDatabase->setQuery($sql);
            $cityID       = $this->ObjDatabase->getInsertId();
            if ($cityID) {
                $listLanguages = $languageIDs['AllList'];
                foreach ($listLanguages as $language) {
                    $sql                  = "
					INSERT INTO 
						`" . DB_PREFIX . "city_details`(
							`cityID`, 
							`languageID`, 
							`cityName`, 
							`province`
						)VALUES(
							'" . $cityID . "',
							'" . $language . "',
							'" . $cityName . "',
							'" . $province . "'
						)";
                    $resInsertCityDetails = $this->ObjDatabase->setQuery($sql);
                }
                return $cityID;
            }
        }
        return 0;
    }
    //End of function
    public function getCityIDFromCityKey($cityKeyID) {
        $sql = "
				SELECT 
					cityID
				FROM 
					" . DB_PREFIX . "cities 
				WHERE 
					cityKeyID ='" . $cityKeyID . "' ";
        return $this->ObjDatabase->readValue($sql);
    }
    //function to check the nationla proximity exists or not
    function checkNationalExists($POST) {
        if ($_POST['cityID'] != '')
            $cityID = $_POST['cityID'];
        else
            $cityID = 0;
        $sql = " 
		SELECT 
			EXISTS(
				SELECT 
					* 
				FROM 
					" . DB_PREFIX . "cities 
				WHERE cityID !='" . $cityID . "' 
					AND countryID ='" . $_POST['countryID'] . "' 
					AND cityProximity='National' 
					AND cityStatus!='Deleted'
				)";
        $res = $this->ObjDatabase->readValue($sql);
        return $res[0];
    }
    //End : function
    //function to check the nationla proximity exists or not
    function checkNational($cityID) {
        $sql = "
		SELECT 
			EXISTS(
				SELECT 
					* 
				FROM 
					" . DB_PREFIX . "cities 
				WHERE 
					cityID ='" . $cityID . "' 
					AND cityProximity='National'
				)";
        $res = $this->ObjDatabase->readValue($sql);
        return $res[0];
    }
    //End : function
    //Function to check city with language exists
    function isCityWithLanguageIDExists($languageID, $cityID) {
        $sql              = "
			SELECT 
				* 
			FROM 
				`" . DB_PREFIX . "city_details` 
			WHERE 
				`languageID` = '" . $languageID . "' 
				AND `cityID` = '" . $cityID . "'";
        $resSelectProduct = $this->ObjDatabase->numberOfRecords($sql);
        if ($resSelectProduct)
            return true;
        else
            return false;
    }
    //End of function
    //Function to list cities
    public function listCities($languageIDs, $limitFrom = 0, $limitTo = "", $countryID = 0, $stateID = 0, $cityID = 0, $status = '', $keywords = '', $column = '', $order = '', $province = '', $from = '') {
        $fieldsArr = array(
            'cityName' => 'cityID',
            'cname' => 'cityName',
            'cityKeyID' => 'cityKeyID',
            'stateName' => 'stateName',
            'locationCount' => 'locationCount',
            'timeZoneName' => 'timeZoneName'
        );
        $orderArr  = array(
            'asc' => 'asc',
            'desc' => 'desc'
        );
        $sql       = "SELECT ";
        //IF PAGINATION REQUIRED						 
        if ($limitTo) {
            $sql .= " SQL_CALC_FOUND_ROWS ";
        }
        //NOW DEAL - add city lat & lng			
        $sql .= "
				
				citDet.cityName,
				cur.currencyCode";
        if (!$from) {
            $sql .= ", 
				cit.cityProximity AS cityprox,
				cit.cityLat AS cityLatitude,
				cit.cityLng AS cityLongitude,
	 			cit.*,
				COUNT(citLoc.cityLocID) AS locationCount,
				citDet.province,
				timz.timeZoneName,
				timz.timeZoneID,
				staDet.stateName,
				cntryDet.countryName";
        }
        $sql .= " 
		   FROM 
		   	`" . DB_PREFIX . "cities` cit 
			LEFT JOIN 
				`" . DB_PREFIX . "city_details` citDet 
				ON
					(
						cit.cityID=citDet.cityID 
						AND citDet.languageID = '" . $languageIDs['Current'] . "' 
					)";
        if (!$from) {
            $sql .= " 
				
                LEFT JOIN 
					`" . DB_PREFIX . "state_details`  staDet
					ON
					(
						staDet.stateID=cit.stateID 
						AND staDet.languageID = '" . $languageIDs['Current'] . "' 
					)	
				LEFT JOIN 
					`" . DB_PREFIX . "country_details` cntryDet 
					ON
					(
						cntryDet.countryID=cit.countryID 
						AND cntryDet.languageID = '" . $languageIDs['Current'] . "' 
					)
				LEFT JOIN 
					`" . DB_PREFIX . "city_locations` citLoc 
					ON
					( 
						citLoc.cityID=cit.cityID 
						AND citLoc.cityLocStatus!='Deleted'
					)
				LEFT JOIN 
					" . DB_PREFIX . "timezones  timz
					ON cit.timeZoneID=timz.timeZoneID	
					";
        }
        $sql .= " 
			LEFT JOIN 
				`" . DB_PREFIX . "currencies`  cur
				ON(cur.currencyID=cit.cityDefCurrencyID)
			WHERE cit.cityStatus!='Deleted' ";
        if ($status != '') {
            $sql .= " AND cit.cityStatus='" . $status . "' ";
        }
        /*City Based Privilages*/
        if ($_SESSION['ADMIN_ACCED_CITIES']) {
            $sql .= " AND cit.cityID IN (" . $_SESSION['ADMIN_ACCED_CITIES'] . ") ";
        }
        /*City Based Privilages*/
        if ($countryID > 0) {
            $sql .= " AND  cit.countryID='" . $countryID . "' ";
        }
        if (($stateID) > 0) {
            $sql .= "  AND cit.stateID='" . $stateID . "' ";
        }
        if ($province) {
            $sql .= "  AND citDet.province='" . $this->escape($province, false) . "' ";
        }
        if ($cityID > 0) {
            $sql .= "  AND cit.cityID='" . $cityID . "' ";
            $cityDetails = $this->ObjDatabase->readValue($sql);
        } else {
            $sqlGroupBy = " GROUP BY cit.cityID";
            $sql .= $sqlGroupBy;
            if (array_key_exists($column, $fieldsArr) && array_key_exists($order, $orderArr)) {
                $sql .= " ORDER BY " . $fieldsArr[$column] . " " . $orderArr[$order];
            } else {
                $sql .= " ORDER BY citDet.cityName ASC ";
            }
            //IF PAGINATION REQUIRED
            if ($limitTo) {
                $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
            }
            $cityDetails = $this->ObjDatabase->readValues($sql);
        }
        return $cityDetails;
    }
    //End of function
    //Function to list city location
    public function listCityLocations($languageIDs, $cityID) {
        $sql    = "
			SELECT 
				citLoc.*,
				citLocDet.*,
				citDet.*
			FROM 
				`" . DB_PREFIX . "city_locations` citLoc 
			LEFT JOIN  
				`" . DB_PREFIX . "city_location_details` citLocDet 
				ON
				(
					citLoc.cityLocID=citLocDet.cityLocID 
					AND citLocDet.languageID = '" . $languageIDs['Current'] . "'
				) 
			LEFT JOIN 
				" . DB_PREFIX . "city_details citDet 
				ON
				( 
					citLoc.cityID=citDet.cityID
					AND citDet.languageID = '" . $languageIDs['Current'] . "'
				)
			LEFT JOIN 
				" . DB_PREFIX . "cities cit 
				ON cit.cityID=citLoc.cityID
			WHERE  
				citLoc.cityLocStatus='Active' 
				AND cit.cityStatus='Active' 
				AND citLoc.cityID='" . $cityID . "'
			ORDER BY citLocDet.cityLocName ASC	  ";
        $result = $this->ObjDatabase->readValues($sql);
        return $result;
    }
    //End of function
    //function to change city status
    public function changeCityStatus($POST, $cityID, $deleteprivilege = '') {
        if ($POST['changeStatus'] == 'Deleted' && !$deleteprivilege)
            $POST['changeStatus'] = 'Active';
        if ($POST['changeStatus'] != '') {
            $sql    = "
				UPDATE 
					`" . DB_PREFIX . "cities` 
				SET 
					cityStatus='" . $POST['changeStatus'] . "' 
				WHERE 
					cityID='" . $cityID . "' ";
            $result = $this->ObjDatabase->setQuery($sql);
            return $result;
        }
        return false;
    }
    //End of function
    //function to check cityname exists
    public function isCityNameExists($languageIDs, $stateID, $cityName, $cityID) {
        $sql   = "
			SELECT 
				COUNT(cit.cityID) AS cityCount 
            FROM 
				`" . DB_PREFIX . "cities` cit
			LEFT JOIN 
				`" . DB_PREFIX . "city_details` citDet
                 ON citDet.cityID=cit.cityID";
        $where = " 
			WHERE 
				citDet.languageID='" . $languageIDs['Current'] . "' 
				AND citDet.cityName='" . $this->escape($cityName) . "' 
				AND cit.cityStatus!='Deleted' ";
        if ($stateID > 0) {
            $where .= " AND cit.stateID='" . $stateID . "' ";
        }
        if ($cityID > 0) {
            $where .= " AND cit.cityID!='" . $cityID . "' ";
        }
        $cityCount = $this->ObjDatabase->readValue($sql . $where);
        if ($cityCount['cityCount'] > 0) {
            echo "1";
        } else {
            echo "0";
        }
    }
    //End of function
    //function to check citylocation name exists
    public function isCityLocNameExists($languageIDs, $cityLocID, $cityLocName, $cityID) {
        $cityLocName = $this->escape($cityLocName);
        $sql         = "SELECT 
							COUNT(citLoc.cityLocID) AS cityLocCount 
						FROM 
							`" . DB_PREFIX . "city_locations` citLoc
						LEFT JOIN 
							`" . DB_PREFIX . "city_location_details` citLocDet
                            ON citLocDet.cityLocID=citLoc.cityLocID";
        $where       = " 
			WHERE 
				citLocDet.languageID='" . $languageIDs['Default'] . "' 
				AND citLocDet.cityLocName='" . $cityLocName . "' 
				AND citLoc.cityLocStatus!='Deleted' ";
        if ($cityID > 0) {
            $where .= " AND citLoc.cityID='" . $cityID . "' ";
        }
        if ($cityLocID > 0) {
            $where .= " AND citLoc.cityLocID!='" . $cityLocID . "' ";
        }
        $cityCount = $this->ObjDatabase->readValue($sql . $where);
        if ($cityCount['cityLocCount'] > 0) {
            echo "1";
        } else {
            echo "0";
        }
    }
    //End of function
    //Function to check citylocation name exists
    public function getCityByLocID($locID, $languageID) {
        $sql    = "	SELECT 
							citDet.* 
						FROM 
							`" . DB_PREFIX . "city_locations` citLoc,
							`" . DB_PREFIX . "cities` cit,
							`" . DB_PREFIX . "city_details` citDet 
						WHERE 
							cit.cityID=citDet.cityID 
							AND cit.cityID=citLoc.cityID 
							AND citDet.languageID='" . $languageID . "' 
							AND citLoc.cityLocID='" . $locID . "'";
        $result = $this->ObjDatabase->readValues($sql);
        return $result;
    }
    //End of function
    //Function to check location details
    public function saveLocationDetails($POST, $languageIDs) {
        $cityID        = $POST['cityID'];
        $cityLocID     = $POST['cityLocID'];
        $cityLocStatus = $this->escape($POST['cityLocStatus']);
        $cityLocName   = $this->escape($POST['cityLocName']);
        $cityLocTips   = $this->escape($POST['cityLocTips']);
        $cityLatitude  = $this->escape($POST['cityLatitude']);
        $cityLongitude = $this->escape($POST['cityLongitude']);
        if (!$cityLocID) {
            $sql                = "
				 INSERT INTO 
				 	" . DB_PREFIX . "city_locations(
						`cityID`, 
						`cityLocLat`,
						`cityLocLng`, 
						`cityLocStatus`
					)VALUES(
						'" . $cityID . "',
						'" . $cityLatitude . "',
						'" . $cityLongitude . "',
						'" . $cityLocStatus . "'
					) ";
            $insertCityLocation = $this->ObjDatabase->setQuery($sql);
            $cityLocID          = $this->ObjDatabase->getInsertId();
            if ($cityLocID) {
                $listLanguages = $languageIDs['AllList'];
                foreach ($listLanguages as $language) {
                    $languageID           = $language;
                    $sql                  = "
						INSERT INTO 
							`" . DB_PREFIX . "city_location_details` ( 
								`cityLocID`, 
								`languageID`, 
								`cityLocName`, 
								`cityLocTips`
							)VALUES(
								'" . $cityLocID . "',
								'" . $languageID . "',
								'" . $cityLocName . "',
								'" . $cityLocTips . "'
							)";
                    $insertCityLocDetails = $this->ObjDatabase->setQuery($sql);
                }
            }
            return $cityLocID;
        }
        if ($POST['cityLocID']) {
            $languageID = $languageIDs['Current'];
        } else {
            $languageID = $languageIDs['Default'];
        }
        $sql               = "
			UPDATE  
				" . DB_PREFIX . "city_locations 
			SET 
				`cityLocLat` = '" . $cityLatitude . "', 
				`cityLocLng` = '" . $cityLongitude . "', 
				`cityLocStatus` = '" . $cityLocStatus . "' 
			WHERE 
				`cityLocID` ='" . $cityLocID . "'";
        $resUpdateLocation = $this->ObjDatabase->setQuery($sql);
        if ($this->isCityLocationWithLanguageIDExists($languageID, $cityLocID)) {
            $sql                      = "UPDATE  
					" . DB_PREFIX . "city_location_details 
				SET 
					`cityLocName` = '$cityLocName',
					`cityLocTips` = '" . $cityLocTips . "'
				WHERE 
					`cityLocID` ='" . $cityLocID . "' 
					AND languageID='" . $languageID . "' ";
            $resUpdateLocationDetails = $this->ObjDatabase->setQuery($sql);
        } else {
            $sql                  = "
				INSERT INTO 
					`" . DB_PREFIX . "city_location_details`( 
						`cityLocID`, 
						`languageID`, 
						`cityLocName`, 
						`cityLocTips`
					)VALUES(
						'" . $cityLocID . "',
						'" . $languageID . "',
						'" . $cityLocName . "',
						'" . $cityLocTips . "'
					)";
            $insertCityLocDetails = $this->ObjDatabase->setQuery($sql);
        }
    }
    //End of function
    //Function to check city location exists
    function isCityLocationWithLanguageIDExists($languageID, $cityLocID) {
        $sql    = "
		SELECT 
			* 
		FROM 
			`" . DB_PREFIX . "city_location_details` 
		WHERE 
			`languageID` = '" . $languageID . "' 
			AND `cityLocID` = '" . $cityLocID . "'";
        $result = $this->ObjDatabase->numberOfRecords($sql);
        if ($result)
            return true;
        else
            return false;
    }
    //End of function
    function getCityLocation($languageIDs, $cityLocID) {
        $sql = "
		SELECT 
			* 
		FROM 
			`" . DB_PREFIX . "city_location_details` citLocDet,
			" . DB_PREFIX . "city_locations citLoc 
		WHERE  
			citLocDet.cityLocID='" . $cityLocID . "' 
			AND languageID='" . $languageIDs['Current'] . "' 
			AND citLocDet.cityLocID=citLoc.cityLocID";
        return $this->ObjDatabase->readValue($sql);
    }
    //Function to list locations
    public function listLocations($languageIDs, $limitFrom = 0, $limitTo = "", $status = '', $cityID = '') {
        $sql = "
		SELECT 
			SQL_CALC_FOUND_ROWS 
			* 
		FROM 
			" . DB_PREFIX . "city_locations citLoc,
			" . DB_PREFIX . "city_location_details citLocDet
		WHERE 
			citLocDet.cityLocID=citLoc.cityLocID 
			AND citLoc.cityLocStatus!='Deleted' 
			AND citLocDet.languageID = '" . $languageIDs['Current'] . "'";
        if ($status != '')
            $sql .= " AND citLoc.cityLocStatus='" . $status . "'";
        if ($cityID != '')
            $sql .= " AND citLoc.cityID='" . $cityID . "'";
        $sql .= " ORDER BY citLocDet.cityLocName ASC";
        //IF PAGINATION REQUIRED
        if ($limitTo)
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        $result = $this->ObjDatabase->readValues($sql);
        return $result;
    }
    //End of function
    //Function to update location status
    public function updateLocationsStatus() {
        if ($_POST['changeStatus'] != '') {
            if (count($_POST['locationBulkStatus']) > 0) {
                for ($i = 0; $i < count($_POST['locationBulkStatus']); $i++) {
                    $sql    = "
					UPDATE  
						" . DB_PREFIX . "city_locations 
					SET 
						`cityLocStatus` = '" . $_POST['changeStatus'] . "' 
					WHERE 
						`cityLocID` ='" . $_POST['locationBulkStatus'][$i] . "';";
                    $result = $this->ObjDatabase->setQuery($sql);
                }
            } else {
                $sql    = "
				UPDATE  
					" . DB_PREFIX . "city_locations 
				SET 
					`cityLocStatus` = '" . $_POST['changeStatus'] . "' 
				WHERE 
					`cityLocID` ='" . $_POST['cityLocID'] . "'";
                $result = $this->ObjDatabase->setQuery($sql);
            }
            return true;
        }
        return false;
    }
    //End of function
    //Function to destroy city location
    public function destroyCityLocation($cityLocID) {
        $sql    = "
			DELETE 
			FROM  
				" . DB_PREFIX . "city_locations 
			WHERE 
				`cityLocID` ='" . $cityLocID . "'";
        $result = $this->ObjDatabase->setQuery($sql);
        return $result;
    }
    //End of function
    //Function to check cities with different timezone
    public function isCitiesWithDifferentTimeZone($cityIdString) {
        $sql    = "
			SELECT 
				* 
			FROM 
				" . DB_PREFIX . "cities 
			WHERE 
				`cityID` IN (" . $cityIdString . ") 
			GROUP BY `timeZoneID`";
        $result = $this->ObjDatabase->readValues($sql);
        return count($result) . '~*' . $result[0]['timeZoneID'];
    }
    //End of function
    //Function to get city details
    public function getCityDetails($languageIDs, $cityID = 0, $cityName = '', $status = '') {
        $sql .= " 
			SELECT 
				cit.cityID,
				citDet.cityName,
				cit.*,
				timz.* 
			FROM 
				`" . DB_PREFIX . "cities` cit 
			LEFT JOIN 
				`" . DB_PREFIX . "city_details` citDet
				 ON
				 ( 
				 	citDet.cityID=cit.cityID 
					AND citDet.languageID = '" . $languageIDs['Current'] . "'
				 )
			LEFT JOIN 
				" . DB_PREFIX . "timezones  timz
				ON timz.timeZoneID=cit.timeZoneID  ";
        $sql .= " WHERE  cit.cityStatus!='Deleted' ";
        if ($status != '') {
            $sql .= " AND  cit.cityStatus='" . $status . "' ";
        }
        if ($cityName != '') {
            $sql .= " AND  citDet.cityName='" . $cityName . "' ";
        }
        if ($cityID > 0) {
            $sql .= "  AND cit.cityID='" . $cityID . "' ";
        }
        $result = $this->ObjDatabase->readValue($sql);
        return $result;
    }
    //End of function
    //Function to get city options
    function getCityListWithProximity($languageIDs) {
        $sql = "
		SELECT 
			*,
			COALESCE(
			(
				SELECT 
					countryName 
				FROM 
					" . DB_PREFIX . "country_details  
				WHERE 
					countryID=cit.countryID 
					AND languageID='" . $languageIDs['Current'] . "' 
			),
			(
				SELECT 
					countryName  
				FROM 
					" . DB_PREFIX . "country_details  
				WHERE 
					countryID=cit.countryID 
					AND languageID='" . $languageIDs['Default'] . "' 
			)
		) AS countryName
		FROM 
			" . DB_PREFIX . "cities cit,
			" . DB_PREFIX . "city_details citDet 
		WHERE 
			cit.cityID=citDet.cityID 
			AND languageID=(
				SELECT 
					CASE WHEN 
					(
						SELECT EXISTS(
							SELECT 
								* 
							FROM 
								" . DB_PREFIX . "city_details 
							WHERE 
								cityID=citDet.cityID 
								AND languageID='" . $languageIDs['Current'] . "')
					) 
					THEN 
						" . $languageIDs['Current'] . " 
					ELSE 
						" . $languageIDs['Default'] . " 
					END 
					) 
			AND cityStatus!='Deleted'";
        /*City Based Privilages*/
        if ($_SESSION['ADMIN_ACCED_CITIES']) {
            $sql .= " AND  cit.cityID IN (" . $_SESSION['ADMIN_ACCED_CITIES'] . ") ";
        }
        /*City Based Privilages*/
        $sql .= " ORDER BY 
					countryID,
					cityProximity DESC,
					cityName ASC";
        $res     = $this->ObjDatabase->readValues($sql);
        $arr     = array();
        $country = $res[0]['countryID'];
        foreach ($res as $key => $result) {
            if ($country != $result['countryID'])
                $country = $result['countryID'];
            $arr[$result['countryName']][$result['cityID']] = array(
                $result['countryName'],
                $result['cityID'],
                $result['cityName'],
                $result['cityProximity']
            );
        }
        return $arr;
    }
    //End of function
    //Function to check campaignlist exists  
    public function checkCampaignListExist($cityKeyID) {
        $sql    = "
		SELECT 
			cityCampaignListID 
		FROM 
			" . DB_PREFIX . "cities 
		WHERE 
			cityKeyID='" . $cityKeyID . "'
			AND cityStatus = 'Deleted'
			AND cityCampaignListID != ''";
        $result = $this->ObjDatabase->readValue($sql);
        return $result;
    }
    //End of function
    //Function to update deleted city id
    public function updateDeletedCityListID($cityKeyID) {
        $sql    = "
		UPDATE 
			" . DB_PREFIX . "cities 
		SET 
			cityCampaignListID = ''
		WHERE 
			cityKeyID='" . $cityKeyID . "'
			AND cityStatus = 'Deleted'
			AND cityCampaignListID != ''";
        $result = $this->ObjDatabase->setQuery($sql);
        return $result;
    }
    //End of function
    // For deleting city images on ajax delete
    public function deleteCityImages($fieldName, $cityKey) {
        $sql = "
	   UPDATE 
			" . DB_PREFIX . "cities 
		SET ";
        if ($fieldName == 'citylogoImage')
            $sql .= " citylogoImage = '' ";
        if ($fieldName == 'citybgImage')
            $sql .= " citybgImage = '' ";
        $sql .= " WHERE cityKeyID='" . $cityKey . "'";
        $result = $this->ObjDatabase->setQuery($sql);
        return $result;
    }
    //Function to get city details
    public function getAllCityDetails($languageIDs, $status = '') {
        $sql = " 
		SELECT 
			cit.cityID AS cityID,
			citDet.cityName,
			cit.*,
			timz.* 
		FROM 
			`" . DB_PREFIX . "cities` cit 
		LEFT JOIN 
			`" . DB_PREFIX . "city_details`  citDet
			ON
			(
				citDet.cityID=cit.cityID 
				AND citDet.languageID = '" . $languageIDs['Current'] . "'
			)
		LEFT JOIN 
			" . DB_PREFIX . "timezones  timz
			ON timz.timeZoneID=cit.timeZoneID  ";
        if ($status != '')
            $sql .= " WHERE  cit.cityStatus='Active' ";
        else
            $sql .= " WHERE  cit.cityStatus!='Deleted' ";
        $result = $this->ObjDatabase->readValues($sql);
        return $result;
    }
    //Function to get city details
    public function getCityLocDetails($languageIDs, $cityID = '', $cityLocID = '') {
        $sql    = " 
		SELECT 
			* 
		FROM 
			`" . DB_PREFIX . "city_locations` citLoc 
		LEFT JOIN 
			`" . DB_PREFIX . "city_location_details` citLocDet
			ON
			(
				citLoc.cityLocID=citLocDet.cityLocID 
				AND citLocDet.languageID = '" . $languageIDs['Current'] . "'
			) 
			WHERE  
				citLoc.cityLocStatus!='Deleted' 
				AND citLoc.cityID='" . $cityID . "' 
				AND citLoc.cityLocID='" . $cityLocID . "'";
        $result = $this->ObjDatabase->readValue($sql);
        return $result;
    }
    //Function to check National City exists  
    public function checkProxExist($countryID) {
        $sql = "
		 SELECT 
		 	COUNT(cityID) AS Cnt,
			cityID 
		FROM 
			" . DB_PREFIX . "cities 
		WHERE 
			countryID='" . $countryID . "'
			AND cityStatus = 'Active'
			AND cityProximity = 'National'";
        /*City Based Privilages*/
        if ($_SESSION['ADMIN_ACCED_CITIES']) {
            $sql .= " AND cityID IN (" . $_SESSION['ADMIN_ACCED_CITIES'] . ") ";
        }
        /*City Based Privilages*/
        $resgetListID = $this->ObjDatabase->readValue($sql);
        if ($resgetListID['Cnt'] > 0)
            return $resgetListID['cityID'];
        else
            return 0;
    }
    //End of function
    //for MIC
    public function getCityCurrencies($cityID = 0) {
        $sql = "
		SELECT 
			cityID,
			cityDefCurrencyID 
		FROM 
			" . DB_PREFIX . "cities cit  
		INNER JOIN 
			" . DB_PREFIX . "currencies cur 
			ON
			(
				cur.currencyID=cit.cityDefCurrencyID 
				AND cur.currencyStatus != 'Delete'
			) 
		WHERE  cityStatus = 'Active' ";
        /*City Based Privilages*/
        if ($_SESSION['ADMIN_ACCED_CITIES']) {
            $sql .= " AND cityID IN (" . $_SESSION['ADMIN_ACCED_CITIES'] . ")";
        }
        /*City Based Privilages*/
        if ($cityID > 0)
            $sql .= " AND cityID = '" . $cityID . "'";
        $rescity = $this->ObjDatabase->readValues($sql);
        if ($cityID > 0)
            return $rescity[0];
        else {
            if (count($rescity) > 0) {
                foreach ($rescity as $cities) {
                    $returnArray[$cities['cityID']] = $cities['cityDefCurrencyID'];
                }
            }
            return $returnArray;
        }
    }
    //MIC
    // For Drop Down
    public function listCitiesForSearch($languageIDs, $status = "", $purpose = '') {
        $sql = "
					SELECT  
						cit.cityID, ";
        if ($purpose != '')
            $sql .= " 
						citDet.cityName AS cityName ";
        else
            $sql .= " 
						IF
							(
								cit.cityProximity='National',
								CONCAT(citDet.cityName,'[',cntryDet.countryName,']'),
								citDet.cityName
							)
						AS cityName ";
        $sql .= "
					FROM  
					   " . DB_PREFIX . "cities cit  
					LEFT JOIN  
						" . DB_PREFIX . "city_details citDet  
						ON 
							( 
								cit.cityID=citDet.cityID  
								AND citDet.languageID = " . $languageIDs['Current'] . " 
							) 
					LEFT JOIN  
						" . DB_PREFIX . "country_details cntryDet  
						ON 
						( 
							cntryDet.countryID=cit.countryID  
							AND cntryDet.languageID = " . $languageIDs['Current'] . " 
						)";
        $sql .= "
					WHERE
						cit.cityStatus!='Deleted'";
        if ($status != '') {
            $sql .= " 
						AND cit.cityStatus='" . $status . "' ";
        }
        /*City Based Privilages*/
        if ($_SESSION['ADMIN_ACCED_CITIES']) {
            $sql .= " 
						AND cit.cityID IN (" . $_SESSION['ADMIN_ACCED_CITIES'] . ") ";
        }
        /*City Based Privilages*/
        $sql .= "
					ORDER BY
						citDet.cityName ASC ";
        return $this->ObjDatabase->readValues($sql);
    }
	
    //List all cities for the selected country
    public function listCitiesFrontend($countryID) {
        $sql = "	SELECT 
						cityID, 
						cityName, 
						countryID
					FROM 
							`" . DB_PREFIX . "cities` 
					WHERE 
						countryID =" . (int) $countryID . "
						ORDER BY
						cityName ASC";
        return $this->ObjDatabase->readValues($sql);
    }	
	
	
}
