<?php

/**
 * TblState
 * 
 * This class has been auto-generated by the Doctrine ORM Framework
 * 
 * @package    ##PACKAGE##
 * @subpackage ##SUBPACKAGE##
 * @author     ##NAME## <##EMAIL##>
 * @version    SVN: $Id: Builder.php 7490 2010-03-29 19:53:27Z jwage $
 */
class Travel_Model_TblState {

    public $intId        = NULL;
    
    public $intContSysId = NULL;
    
    public $strStateName = NULL;
    
    public $strDesc      = NULL;
    
    public $strMetaDesc  = NULL;
    
    public $strPageTitle = NULL;
    
    public $strMetaKey   = NULL;
    
    public $intRegionId  = NULL;
    
    protected $db;
     
    public $isBackend = 'Y';
   
    
     /*  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_State.Title' ;
    
    public $synonyms = NULL;
    
    /***************************************/
    
    public function __construct(){
      #initialize db adapter
      $this->db = Zend_Db_Table::getDefaultAdapter();
      
    }
    
    public function __destruct() {
        $this->db->closeConnection(); 
    }
    
    public function getStates(){
        
       $sql = "  SELECT StateId, ContSysId, Title, Code, PIN, IsActive, IsMarkForDelete,
                CreateDate, UpdateDate FROM TB_Master_Geo_State WHERE 1 = 1 " ;
                
        if(!empty($this->intStateId)){
            $sql .= " AND TB_Master_Geo_State.StateId = '".$this->intStateId."'";
        }
        
        if(!empty($this->intContSysId)){
            $sql .= " AND TB_Master_Geo_State.ContSysId = '".$this->intContSysId."'";
        }
        
        if(!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }
         
        $sql .= " AND TB_Master_Geo_State.IsMarkForDelete = '0' ";
         
        $sql .= " ORDER BY " . $this->strOrderBy ;
        
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
        public function getStatesForRegion(){
        
       $sql = "  SELECT StateId, ContSysId, Title, Code, PIN, IsActive, IsMarkForDelete,
                CreateDate, UpdateDate FROM TB_Master_Geo_State WHERE 1 = 1 " ;
                

            $sql .= " AND TB_Master_Geo_State.ContSysId = '101'";

        
         
        $sql .= " AND TB_Master_Geo_State.IsMarkForDelete = '0' AND TB_Master_Geo_State.IsApprove = '1' AND TB_Master_Geo_State.IsActive = '1'";
         
        $sql .= " ORDER BY " . $this->strOrderBy ;
        
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    public function getStateListingRecord(){
        //print_r($this->searchArr);
       $sql = "  SELECT st.StateId, st.RegionId, st.ContSysId, st.Title, st.Code, st.PIN, st.IsActive, st.IsMarkForDelete, TB_Master_Geo_Country.Title as Country,
                st.CreateDate, st.UpdateDate, st.IsApprove,TB_Master_Geo_Region.Title as RegionName FROM TB_Master_Geo_State as st 
                LEFT JOIN TB_Master_Geo_State_Region_MAP ON TB_Master_Geo_State_Region_MAP.StateSysId = st.StateId
                LEFT JOIN TB_Master_Geo_Region ON TB_Master_Geo_Region.RegId = TB_Master_Geo_State_Region_MAP.RegCatId
                LEFT JOIN TB_Master_Geo_Country ON TB_Master_Geo_Country.ContId = st.ContSysId WHERE  1 = 1 AND st.ContSysId = 101 " ;
                
        if(!empty($this->intStateId)){
            $sql .= " AND TB_Master_Geo_State.StateId = '".$this->intStateId."'";
        }
        
        if(!empty($this->searchArr)){
            if($this->searchArr['countryId']!=''){
                $sql .= " AND st.ContSysId = '".$this->searchArr['countryId']."' ";
            }
            
            if($this->searchArr['stateName']!=''){
                $sql .= " AND st.Title LIKE '%".$this->searchArr['stateName']."%' ";
            }
            
            if($this->searchArr['code']!=''){
                $sql .= " AND st.Code LIKE '%".$this->searchArr['code']."%' ";
            }
            
            if($this->searchArr['filter1']!=''){
                $sql .= " AND st.IsActive = '".$this->searchArr['filter1']."' ";
            }
            
            if($this->searchArr['filter2']!=''){
                $sql .= " AND st.IsApprove = '".$this->searchArr['filter2']."' ";
            }
            
            if($this->searchArr['region']!=''){
                $sql .= " AND TB_Master_Geo_State_Region_MAP.RegCatId = '".$this->searchArr['region']."' ";
            }
            
        }
        
        if(!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }
         
        $sql .= " AND st.IsMarkForDelete = '0' ";
         
        $sql .= " ORDER BY st.Title ASC" ;
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    public function save(){
        
        $data = array();
        $data['country']            = $this->intCountryId;
        $data['description']        = $this->strDesc;
        $data['meta_key']           = $this->strMetaKey;
        $data['state_name']         = $this->strStateName;
        $data['page_title']         = $this->strPageTitle;
        $data['meta_description']   = $this->strMetaDesc;
        $data['region']             = $this->intRegionId;
        $this->synonyms             = Zend_Controller_Action_HelperBroker::getStaticHelper('string')->cleanCommaSepString($this->synonyms);
        $data['synonyms']           = $this->synonyms;
  
        if(!$this->intId){
             $this->_db->insert('tbl_state', $data);
             return $this->_db->lastInsertId();
        }else {
             $this->_db->update('tbl_state', $data, 'id = '.$this->intId);
             return $this->intId;
        }
       
    }
    
    public function delete($ids){
         #build query
        $query 		 = "UPDATE tbl_state 
                            SET deleted = 'Y'
                             WHERE id in (".$ids.")";

        #execute query
        $db = Zend_Db_Table::getDefaultAdapter();
        $result   		 = $db->query($query);
        if($result){
            return true;
        }
        
        return false;
        
    }
    
     public function changeStatus($id, $status){
         #build query
        $query 		 = "UPDATE tbl_state 
                              SET status='".$status."'
                            WHERE id = '".$id."'";

        #execute query
        $db = Zend_Db_Table::getDefaultAdapter();
        $result   		 = $db->query($query);
        if($result){
            return true;
        }
        
        return false;
        
    }
    
     public function updatestatus($ids, $status){
         #build query
        $query 		 = "UPDATE tbl_state 
                                SET status = '".$status."'
                            WHERE id in (".$ids.")";
        
        //echo $query;exit;
        #execute query
        $db = Zend_Db_Table::getDefaultAdapter();
        $result   		 = $db->query($query);

        if($result){
            return true;
        }
        
        return false;
      
    }
    
    public function getAllCities() {
       
        
        $objCity = new Travel_Model_TblCity();
        if(!empty($this->intId)) {
             $objCity->strCondition = " AND tbl_city.state_id = " . $this->intId;
        }
        
        if(!empty($this->intCountryId)) {
             $objCity->strCondition = " AND tbl_city.country_id = " . $this->intCountryId;
        }
        return $objCity->getCityList();
    }
    
    public function getStateIdByName($strStateName){
        
        $sql = " SELECT id FROM tbl_state WHERE state_name = '".$strStateName."' AND deleted = 'N' LIMIT 0,1" ;
        $result = $this->db->query($sql)->fetchAll();
        
        if(count($result)>0){
            return $result[0]['id'];
        }
        return '';
    }
 	public function getStateAutoSuggest($keyword) {
        $keyword = trim($keyword);
		$rowset=$this->getStateListWithCountryDetail($keyword);
        $response = array();
        if (count($rowset) > 0) {
            foreach ($rowset as $row) {
                $response[] = array('StateId' => $row['StateId'], 'StateName' => stripslashes($row['stateTitle']), 'label' => $row['stateTitle'].'('.$row['countryTitle'].')', 'ContId' => $row['ContId'],'countryCode' => trim($row['countryCode']),'countryTitle' => trim($row['countryTitle']));
            }
        }
        return $response;
    }
	public function getStateListWithCountryDetail($where){
		$country = array('ContId','Title as countryTitle','Code as countryCode');	
		$stateColumn=array('StateId','Title as stateTitle','Code as stateCode');	
		$select = $this->db->select();
		$select->from(array('tb1' => "TB_Master_Geo_State") ,$stateColumn);
		$select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tb1.ContSysId = tb2.ContId" , $country);
		$select->where($where);
		$select->where("tb1.IsActive = ?", 1 );
		$select->where("tb1.IsApprove = ?", 1 );
		$select->where("tb1.IsMarkForDelete = ?", 0 );
		$select->where("tb2.IsActive = ?", 1);
		$result = $this->db->fetchAll($select);
        return $result;
	}
        
        
    public function getStateList() {
        
        $sql = " SELECT StateId, Title,ContSysId, Description, GSTNo FROM TB_Master_Geo_State WHERE 1 = 1 " ;
                
//        if(!empty($this->intCityId)){
//            $sql .= " AND TB_Master_Geo_State.CityId = '".$this->intCityId."'";
//        }
        
        if(!empty($this->intContSysId)){
            $sql .= " AND TB_Master_Geo_State.ContSysId = '".$this->intContSysId."'";
        }
        
        if(!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }
         
        $sql .= " AND TB_Master_Geo_State.IsMarkForDelete = '0' AND Title!='0' AND IsActive='1' ";
         
        $sql .= " ORDER BY " . $this->strOrderBy ;
        
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    
}