<?php

/**
 * Class            City API MAP  Model
 * Description 		This model class contains the functions needed for manipulation of CITY API Mapping.
 * @name		    Travel_Model_TblCityApiMap  Model
 * @author		    Shailender Joshi
 * @editor          Shailender Joshi <shailender@catpl.co.in>
 * @version         1.0
 * @copyright       Catabatic Technologies Pvt Ltd
 *
 */
class Travel_Model_TblCityApiMap {

    public $intContSysId = NULL;
    protected $db = NULL;
    public $imageServerUrl = NULL;
    public $baseUrl = NULL;


    /*  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_City.CityId';
    public $intLoginUserId = NULL;
    public $intLoginAgentId = NULL;
    public $strImageFlag = NULL;

    /*     * ************************************ */

    public function __construct() {
        #initialize db adapter
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }

    public function __destruct() {
        $this->db->closeConnection();
    }

    /**
     * updateTable() method is used to update data on table primary key basis
     * @param table name, data array, where condition
     * @return object
     */
    public function updateTable($table, $editData, $where) {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->update($editData, $where);
    }

    // $where       =  array('column'=>value,'column'=>'value',.......);
    // $columnArray =  array('column1','column2','column3',...........);
    public function getRecordListingWhere($table, $columnArray, $where, $orderby = '', $order = "ASC") {
        $select = $this->db->select()->from($table, $columnArray);

        foreach ($where as $col => $val) {
            $select->where("$col =?", $val);
        }

        if (!empty($orderby))
            $select->order("$orderby $order");

        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getGeoRecordListing() {

        $sql = " SELECT CityId, Title, CityCategoryMask, GetLat, GetLong, Synonyms FROM TB_Master_Geo_City WHERE IsApproved = 1 AND IsMarkForDel = 0 AND IsActive = 1 ";

        if (!empty($this->searchArr)) {
            //echo "<pre>"; print_r($this->searchArr); die;

            if (@$this->searchArr['country_id'] != 0) {
                $sql .= " AND ContSysId = '" . $this->searchArr['country_id'] . "' ";
            }
            if (@$this->searchArr['cityTitleGeo'] != '') {
                $sql .= " AND ( Title LIKE '%" . $this->searchArr['cityTitleGeo'] . "%' OR Synonyms LIKE '%" . $this->searchArr['cityTitleGeo'] . "%')";
            }
        }

        $sql .= " ORDER BY Title ";
        //echo $sql; //die();

        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }

    public function getApiRecordListing() {

        $sql = " SELECT APICitySysId, GTXCityId, APICityId, Title FROM TB_API_City WHERE IsApproved = 1 AND IsMarkForDel = 0 AND IsActive = 1 ";

        if (!empty($this->searchArr)) {
            //echo "<pre>"; print_r($this->searchArr); die;

             if (!empty($this->searchArr['country_id'])) {
                $sql .= " AND ContSysId = '" . $this->searchArr['country_id'] . "' ";
            }
            
            if (!empty($this->searchArr['APICitySysId'])) {
                $sql .= " AND APICitySysId = '" . $this->searchArr['APICitySysId'] . "' ";
            }
            
            
             if (!empty($this->searchArr['source_id'])) {
                $sql .= " AND SourceSysId = '" . $this->searchArr['source_id'] . "' ";
            }
             if (!empty($this->searchArr['cityTitleApi'])) {
                $sql .= " AND Title LIKE '%" . $this->searchArr['cityTitleApi'] . "%' ";
            }
            if (@$this->searchArr['filterApi'] != 'All' && @$this->searchArr['filterApi'] != '' && @$this->searchArr['filterApi'] == 0) {
                $sql .= " AND GTXCityId = 0 ";
            }
            if (@$this->searchArr['filterApi'] != 'All' && @$this->searchArr['filterApi'] != '' && @$this->searchArr['filterApi'] != 0) {
                $sql .= " AND GTXCityId != 0 ";
            }
        }

        $sql .= " ORDER BY Title ";
//        echo $sql; //die();

        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }

    # End : functions added by Shailender Joshi on 20 Oct 2016
    
    
        public function getGeoRecordByCityName($cityName,$countryId = NULL) {
        $select = $this->db->select()
                ->from("TB_Master_Geo_City")
                ->where("Title =?", $cityName);
        if(!empty($countryId)){
        $select->where("ContSysId =?", $countryId);
        }
        $result = $this->db->fetchRow($select);
        return $result;
    }
    
          public function getGeoRecordByCityId($cityId) {
        $select = $this->db->select()
                ->from("TB_Master_Geo_City")
                ->where("CityId =?", $cityId);
        $result = $this->db->fetchRow($select);
        return $result;
    }
    
    
     public function addCity($cityDataArray = array()) {
        $cityData = array(
            'TBBCityId' => $cityDataArray['TBBCityId'],
            'Title' => $cityDataArray['Title'],
            'Synonyms' => $cityDataArray['Title'],
            'StateOrZone' => 0,
            'Country' => $cityDataArray['Country'],
            'GetLat' => 0,
            'GetLong' => 0,
            'StateSysId' => 0,
            'ZoneSysId' => 0,
            'ContSysId' => $cityDataArray['CountryId'],
            'PIN' => 0,
            'IsHaveAirPort' => 0,
            'ApproveDate'=>0,
            'CreateDate' => $cityDataArray['currentDate'],//$currentDate ? $currentDate : EMPTY_DATE,
            'UpdateDate' => 0,
            'IsApproved' => 1,
            'IsMarkForDel' => 0,
            'IsActive' => 1
        );
        try {
            $this->db->insert('TB_Master_Geo_City', $cityData);
            return $this->db->lastInsertId('TB_Master_Geo_City');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }
    
    
        public function getApiListingByCityId($cityId, $countryId) {
        $whereCondition = "  TBAC.IsApproved = 1 AND TBAC.IsMarkForDel = 0 AND TBAC.IsActive = 1 ";
        $whereCondition .= " AND GTXCityId = '" . $cityId . "'  AND ContSysId = '" . $countryId . "' ";
        $select = $this->db->select()
                ->from(array("TBAC" => "TB_API_City"), array('APICitySysId', 'GTXCityId', 'APICityId', 'TBAC.Title as CityName','SourceSysId'))
                ->joinLeft(array('TBIS' => 'TB_IC_InfoSources'), 'TBIS.InfoSourceSysId=TBAC.SourceSysId', array('TBIS.Title as sourceName'))
                ->where($whereCondition)
                ->order("TBIS.Title ASC");
//       echo $select.'</br></br>'; 
        $result = $this->db->fetchAll($select);
        return $result;
    }
}