<?php

class Travel_Model_TblCountry{ 

   #define class variables
    public $intId = NULL;
    public $intCategoryId  = NULL;
    public $strCountryName = NULL;
    public $strCountryCode = NULL;
    public $strDescription = NULL;
    public $intRegionId    = NULL;
    public $intContinent   = NULL;
    public $strSynonyms    = NULL;
    
    public $strBestTimeToVisit    = NULL;
    public $strHowToReach    = NULL;
    
    public $isBackend = 'Y';
    
    protected $db;
    /*  Common to all models */
    public $strCondition  = NULL;
    
    public $deleted = 'N';
    
    public $status = '1';
        
    public $strSelectedView = '';
    
    public $intQueryOffset = 0;
    
    public $intListPerPage = 10;
    
    public $strOrderBy = 'TB_Master_Geo_Country.Title' ;
    
   /***************************************/
    
      public function __construct(){
      #initialize db adapter
      $this->db = Zend_Db_Table::getDefaultAdapter();
      
     
    }
    
    public function __destruct() {
        $this->db->closeConnection(); 
    }
   
  
	
    public function getCountryList() {
	
        $sql = " SELECT ContId, Title, Code, TBBContId, PhoneCode, TimeZoneUTC,RegionId, Continent, "
                . " Synonyms FROM TB_Master_Geo_Country  WHERE 1 = 1 ";
        
        if($this->strCondition){
             $sql .= $this->strCondition ;
        }
        
        $sql .= " AND TB_Master_Geo_Country.IsActive = '1' AND TB_Master_Geo_Country.IsMarkForDel = '0'";
        
        $sql .= " ORDER BY " . $this->strOrderBy; 
        
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
        public function getCountryListForRegion() {
	
        $sql = " SELECT ContId, Title, Code, TBBContId, PhoneCode, TimeZoneUTC,RegionId, Continent, "
                . " Synonyms FROM TB_Master_Geo_Country  WHERE 1 = 1 ";
        
        if($this->strCondition){
             $sql .= $this->strCondition ;
        }
        
        $sql .= " AND TB_Master_Geo_Country.IsActive = '1' AND TB_Master_Geo_Country.IsMarkForDel = '0' AND TB_Master_Geo_Country.IsApprove = '1'";
        
        $sql .= " ORDER BY " . $this->strOrderBy; 
        
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    public function getCountryListingRecord() {
	
//        $sql = " SELECT Country.ContId, Country.Title, Country.Code, Country.TBBContId, Country.PhoneCode, Country.TimeZoneUTC, Country.IsApprove, Country.IsActive, TB_Master_Geo_Region.Title as RegionName,"
//                . " Country.Synonyms, TB_Master_Geo_Continent.Title as ContinentName FROM TB_Master_Geo_Country as Country "
//                . "LEFT JOIN TB_Master_Geo_Region ON TB_Master_Geo_Region.RegId = Country.RegionId "
//                . "LEFT JOIN TB_Master_Geo_Continent ON TB_Master_Geo_Continent.ContiSysId = Country.Continent WHERE 1=1";
        
//        if($this->strCondition){
//             $sql .= $this->strCondition ;
//        }
        
        $sql = "SELECT t1.ContId,t1.Code, t1.TBBContId, t1.PhoneCode, t1.TimeZoneUTC,t1.IsApprove, t1.IsActive,t1.Synonyms, t4.Title as ContinentName,t1.Title as CountryName,t3.Title as RegionName  FROM [DemoGTXHotel].[dbo].[TB_Master_Geo_Country] t1
                left join TB_Master_Geo_Country_Region_MAP t2 on t1.ContId = t2.ContSysId
                left join TB_Master_Geo_Region t3 on t2.RegCatId = t3.RegId
                left join TB_Master_Geo_Continent t4 on t1.Continent = t4.ContiSysId
                where 1=1 ";
        
        
        if(!empty($this->searchArr)) {
            if($this->searchArr['Title']!=''){
                $sql .= " AND t1.Title LIKE '%".$this->searchArr['Title']."%' ";
            }            
            if($this->searchArr['Code']!=''){
                $sql .= " AND t1.Code LIKE '%".$this->searchArr['Code']."%' ";
            }
            if($this->searchArr['region']!=''){
                $sql .= " AND t2.RegCatId = '".$this->searchArr['region']."' ";
            }
            if($this->searchArr['continent']!=''){
                $sql .= " AND t1.Continent = '".$this->searchArr['continent']."' ";
            }            
            if($this->searchArr['filter1']!=''){
                $sql .= " AND t1.IsActive = '".$this->searchArr['filter1']."' ";
            }            
            if($this->searchArr['filter2']!=''){
                $sql .= " AND t1.IsApprove = '".$this->searchArr['filter2']."' ";
            }
            
        }
      $sql .= "AND t1.IsMarkForDel = '0'";
        //$sql .= "AND t3.IsMarkForDel = '0'";
        
        $sql .= " ORDER BY t1.Title"; 
//        echo $sql; exit;
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    public function getCountryIdByCode($Code) {
        
        $sql = " SELECT ContId FROM TB_Master_Geo_Country  WHERE Code = '" . $Code . "' " ;
        
        $rowset = $this->db->query($sql)->fetchAll();
        
        if(count($rowset) > 0 ) {
            return $rowset[0]['ContId'];
        }
        
        return 0;
    }
	
	public function getCountryNameById($id) {
        
        $sql = " SELECT Title,ContId FROM TB_Master_Geo_Country  WHERE ContId = '" . $id . "' " ;
        
        $rowset = $this->db->query($sql)->fetchAll();
        
        if(count($rowset) > 0 ) {
            return trim($rowset[0]['Title']);
        }
        
        return 'NA';
    }
    public function getCountryFlagCode($id) {
        
        $sql = " SELECT code FROM TB_Master_Geo_Country  WHERE ContId = '" . $id . "' " ;
        
        $rowset = $this->db->query($sql)->fetchAll();
        
        if(count($rowset) > 0 ) {
            return trim($rowset[0]['code']);
        }
        
        return 'NA';
    }
	
	public function getCountryDetailsByName($strName) {
        $sql = " SELECT Top 1 ContId, Title,Code  FROM TB_Master_Geo_Country WHERE Title = '" . $strName."'" ;
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    
    
    public function getAllCountrylist() {
        $sql = " SELECT ContId, Title,Code  FROM TB_Master_Geo_Country " ;
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    

        
        
}