<?php

/**
 * Class TblCity  Model
 * Description 		This model class contains the functions needed for manipulation of City.
 * @name		TblCity  Model
 * @author		Ravi Khare
 * @editor              Ranvir singh <ranvir@catpl.co.in>
 * @version             1.0
 * @copyright           Catabatic Technologies Pvt Ltd
 *
 */
class Travel_Model_TblCity
{

    public $intCityId = NULL;
    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.Title';
    public $intLoginUserId = NULL;
    public $intLoginAgentId = NULL;
    public $strImageFlag = NULL;
    public $intGTXCityId = NULL;
    public $intICSourceSysId = NULL;

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

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

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

    public function getCityList()
    {

        $sql = " SELECT CityId, TBBCityId, Title, Alias, StateOrZone, Country,Synonyms, GetLat, GetLong
                ,StateSysId, ZoneSysId,CityCategoryMask, ContSysId, PIN, IsHaveAirPort, CreateDate, UpdateDate
                ,IsMarkForDel, IsApproved, IsActive FROM TB_Master_Geo_City WHERE 1 = 1 ";

        if (!empty($this->intCityId)) {
            $sql .= " AND TB_Master_Geo_City.CityId = '" . $this->intCityId . "'";
        }

        if (!empty($this->intContSysId)) {
            $sql .= " AND TB_Master_Geo_City.ContSysId = '" . $this->intContSysId . "'";
        }

        if (!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }

        $sql .= " AND TB_Master_Geo_City.IsMarkForDel = '0' AND Title!='0' AND IsActive='1' ";

        $sql .= " ORDER BY " . $this->strOrderBy;

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

    public function getCityListCondition($keyword)
    {

        $sql = " SELECT TBBCityId,CityId, Title  FROM TB_Master_Geo_City WHERE 1 = 1 AND IsActive='1' AND Title like '" . $keyword . "%'";

        $sql .= " ORDER BY " . $this->strOrderBy;

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

    public function getCityNameById($intId)
    {
        $sql = " SELECT CityId, Title  FROM TB_Master_Geo_City WHERE CityId = " . $intId;
        $rowset = $this->db->query($sql)->fetch();
        return $rowset['Title'];
    }

    
     public function getCityIdByName($title)
    {
        $sql = " SELECT CityId,ContSysId FROM TB_Master_Geo_City WHERE IsMarkForDel = 0 AND isActive = 1 AND Title = '" . $title."'";
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    
    public function getCityDetailsById($intId)
    {
        $sql = " SELECT CityId, Title,Code,Country,TBBCityId,StateSysId,ContSysId  FROM TB_Master_Geo_City WHERE CityId = " . $intId;
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    public function getAutoSuggestForSupplier($keyword)
    {
        $keyword = trim($keyword);
        $response = array();
        $sql = " SELECT CityId, Title FROM TB_Master_Geo_City WHERE 1 = 1 AND IsApproved = 1 AND IsMarkForDel = 0 AND isActive = 1";
        $sql .= " AND TB_Master_Geo_City.Title = '$keyword'";
        if (!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }
        //echo $sql; exit;
        $rowset = $this->db->query($sql)->fetchAll();
        $response = array();
        if (count($rowset) > 0) {
            foreach ($rowset as $row) {
                $response[] = array('value' => $row['CityId'], 'label' => $row['Title']);
            }
        }
        return $response;
    }
    
    
    public function getAutoSuggest($keyword)
    {
        $keyword = trim($keyword);
        $response = array();


        $sql = " SELECT CityId, Title FROM TB_Master_Geo_City WHERE 1 = 1 AND IsApproved = 1 AND IsMarkForDel = 0 AND isActive = 1";

        $sql .= " AND TB_Master_Geo_City.Title LIKE '" . $keyword . "%' ";

        if (!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }


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

        $response = array();

        if (count($rowset) > 0) {
            foreach ($rowset as $row) {
                $response[] = array('value' => $row['CityId'], 'label' => $row['Title']);
            }
        }

        return $response;
    }

    # Start : functions added by Ranvir Singh on 13 Sep 2016

    /**
     * getDetailsByUniqueId() method is used to get details on primary key
     * @param table name, key name, key value
     * @return object 
     */
    public function getDetailsByUniqueId($table, $columnArray, $uniqueId, $uniqueIdVal)
    {
        $select = $this->db->select()
            ->from("$table", $columnArray)
            ->where("$uniqueId =?", $uniqueIdVal);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    # getDetailsByUniqueId

    public function getRecordListing($table, $columnArray, $uniqueId, $uniqueIdVal, $orderby = '', $order = "ASC", $markForDel = '')
    {
        //        echo"<pre>";print_r($columnArray[2]);
        $select = $this->db->select()->from("$table", $columnArray)
            ->where("$uniqueId =?", $uniqueIdVal)
            ->where("IsActive =?", 1);

        if ($markForDel != '') {
            $select->where("IsMarkForDel =?", $markForDel);
        }

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

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

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

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

        if (isset($searchArr)) {
            $count = 0;
            foreach ($searchArr as $col => $val) {
                if ($count == 0)
                    $select->where("$col LIKE ?", "%" . $val . "%");
                else
                    $select->ORwhere("$col LIKE ?", "%" . $val . "%");

                $count++;
            }
        }

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

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

    # getRecordListing

    public function getRecordListingWhereWithSearch($table, $columnArray, $where, $orderby = '', $order = "ASC")
    {
        // $searchInColumn = array('column1','column2','column3'......); // apply search on the following columns

        $select = $this->db->select()->from($table, $columnArray);

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


        if (!empty($this->searchArr)) {
            //echo "<pre>"; print_r($this->searchArr); die;
            if ($this->searchArr['search'] != '') {
                $count = 0;
                foreach ($columnArray as $col) {
                    if ($count == 0)
                        $select->where("$col LIKE ?", "%" . $this->searchArr['search'] . "%");
                    else
                        $select->ORwhere("$col LIKE ?", "%" . $this->searchArr['search'] . "%");

                    $count++;
                }
            }
        }

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

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

    # getRecordListing
    # to get records from 2 tables

    public function getRecordFromTwoTablesById($t1, $colsArr1, $t2, $colsArr2, $joinCol1, $joinCol2, $whereArr, $orderby = '', $order = "ASC")
    {
        $select = $this->db->select();
        $select->from(array("t1" => "$t1"), $colsArr1);
        $select->joinLeft(array('t2' => "$t2"), "t1.$joinCol1 = t2.$joinCol2", $colsArr2);

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

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

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

    # getRecordListingFromTwoTablesWhere
    # to get records from 3 tables

    public function getRecordFromThreeTablesById($t1, $colsArr1, $t2, $colsArr2, $t3, $colsArr3, $joinCols1, $joinCols2, $whereArr, $orderby = '', $order = "ASC")
    {
        $select = $this->db->select();
        $select->from(array("t1" => "$t1"), $colsArr1);
        $select->joinLeft(array('t2' => "$t2"), "t1." . $joinCols1[0] . "= t2." . $joinCols1[1], $colsArr2);

        $select->joinLeft(array('t3' => "$t3"), "t2." . $joinCols2[0] . "= t3." . $joinCols2[1], $colsArr3);

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

        if (!empty($orderby))
            $select->order("$orderby $order");
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    # getRecordListingFromTwoTablesWhere
    # to get records listings from 2 tables

    public function getRecordListingFromTwoTablesWhere($t1, $colsArr1, $t2, $colsArr2, $joinGlueArr, $whereArr, $whereFromTbl = 't1', $orderby = '', $order = "ASC")
    {
        $select = $this->db->select();
        $select->from(array("t1" => "$t1"), $colsArr1);
        $select->joinLeft(array('t2' => "$t2"), " t1.$joinGlueArr[0] = t2.$joinGlueArr[1] ", $colsArr2);

        $strWhereFromTbl = ($whereFromTbl == 2) ? 't2' : 't1';
        foreach ($whereArr as $col => $val) {
            $select->where("$strWhereFromTbl.$col =?", $val);
        }

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

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

    # getRecordListingFromTwoTablesWhere

    public function getCityListingRecord()
    {
        $sql = " SELECT citytbl.CityId, citytbl.Title, citytbl.Synonyms, TB_Master_Geo_State.Title as State, TB_Master_Geo_Country.Title as Country, citytbl.IsHaveAirPort, citytbl.IsApproved, citytbl.IsActive, citytbl.CreateDate, citytbl.UpdateDate
			FROM TB_Master_Geo_City as citytbl 
			  LEFT JOIN TB_Master_Geo_Country ON TB_Master_Geo_Country.ContId = citytbl.ContSysId
			  LEFT JOIN TB_Master_Geo_State ON TB_Master_Geo_State.StateId = citytbl.StateSysId
			WHERE citytbl.IsMarkForDel =0 ";

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

            if ($this->searchArr['dateFrom'] != '' && $this->searchArr['dateTo'] != '') {
                $sql .= " AND citytbl.CreateDate >= '" . $this->searchArr['dateFrom'] . "' AND  citytbl.CreateDate <= '" . $this->searchArr['dateTo'] . "' ";
            }
            if ($this->searchArr['Synonyms'] != '') {
                $sql .= " AND citytbl.Synonyms LIKE '%" . $this->searchArr['Synonyms'] . "%' ";
            }
            if ($this->searchArr['cityName'] != '') {
                $sql .= " AND citytbl.Title LIKE '%" . $this->searchArr['cityName'] . "%' ";
            }
            if ($this->searchArr['stateId'] != 0) {
                $sql .= " AND TB_Master_Geo_State.StateId = '" . $this->searchArr['stateId'] . "' AND citytbl.StateSysId = '" . $this->searchArr['stateId'] . "'";
            }
            if ($this->searchArr['countryId'] != 0) {
                $sql .= " AND citytbl.ContSysId = '" . $this->searchArr['countryId'] . "' ";
            }
            if ($this->searchArr['filter1'] != 'All' && $this->searchArr['filter1'] != '') {
                $sql .= " AND citytbl.IsActive = '" . $this->searchArr['filter1'] . "' ";
            }
            if ($this->searchArr['filter2'] != 'All' && $this->searchArr['filter2'] != '') {
                $sql .= " AND citytbl.IsApproved = '" . $this->searchArr['filter2'] . "' ";
            }
        }

        $sql .= " ORDER BY citytbl.CityId DESC ";
        //echo $sql; //die();

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

    public function insertTable($table, $data)
    {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->insert($data);
    }

    /**
     * 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);
    }

    # End : functions added by Ranvir Singh on 13 Sep 2016

    public function getAutoSuggestWithCountryName($keyword)
    {
        $keyword = trim($keyword);
        $response = array();

        if ($keyword != '') {
            $where = " tbl.Title LIKE '" . $keyword . "%' ";
        }
        $rowset = $this->getCityListWithCountryDetail($where);
        //echo "<pre>"; print_r($rowset);die;

        if (count($rowset) > 0) {
            foreach ($rowset as $row) {
                $city_title = $row['cityTitle'] . '  (' . $row['countryTitle'] . ')';
                $response[] = array('CityId' => $row['CityId'], 'CityKeyName' => ucfirst(strtolower($row['cityTitle'])), 'CountryId' => $row['ContId'], 'CountryTitle' => $row['countryTitle'], 'value' => $city_title, 'label' => $city_title);
            }
        }

        return $response;
    }

    public function GetCountryCode($cityID)
    {

        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Geo_City"));
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.ContSysId = tb2.ContId");
        $select->where("tbl.CityId = ?", $cityID);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsApproved = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        $select->where("tb2.IsActive = ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    //function for fetching city record with country details created by Er Amit Kumar Dubey on 16 september 2016 on 1:42 PM
    public function getCityListWithCountryDetail($where)
    {
        $country = array('ContId', 'Title as countryTitle', 'Code as countryCode');
        $city = array('CityId', 'TBBCityId', 'Title as cityTitle', 'Alias as cityAlias', 'Code as cityCode');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Geo_City"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.ContSysId = tb2.ContId", $country);
        $select->where($where);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsApproved = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        $select->where("tb2.IsActive = ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }
    
    
    public function getCityIdWithCountryDetail($cityName, $countryName)
    {
        $city = array('CityId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Geo_City"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.ContSysId = tb2.ContId");
        $select->where("tbl.Title = ?", $cityName);
        $select->where("tb2.Title = ?", $countryName);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsApproved = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        $select->where("tb2.IsActive = ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getCityListWithStateDetail($where)
    {
        $country = array('ContId', 'Title as countryTitle', 'Code as countryCode');
        $state = array('StateId', 'Title as stateTitle', 'Code as stateCode');
        $city = array('CityId', 'TBBCityId', 'Title as cityTitle', 'Alias as cityAlias', 'Code as cityCode');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Geo_City"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_State"), "tbl.StateSysId = tb2.StateId", $state);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tbl.ContSysId = tb3.ContId", $country);
        $select->where($where);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsApproved = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        $select->where("tb3.IsActive = ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function addCity(array $data)
    {
        $this->_cityObj = new Travel_Model_TblCity();
        $this->_cityObj->insert($data);
        $lastID = $this->db->lastInsertId();
        return $lastID;
    }

    public function getCityImagesDetailsById($cityId, $ImgType, $IsPrimary)
    {
        $cityImages = array('ImgPath');
        $city = array('CityId', 'TBBCityId', 'LongDesc', 'Title as cityTitle', 'Alias as cityAlias', 'Code as cityCode');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_Geo_City"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City_Images"), "tb1.CityId = tb2.CitySysId", $cityImages);
        $select->where("tb1.CityId =?", $cityId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->where("tb2.IsPrimary = ?", $IsPrimary);
        $select->where("tb2.IsMarkForDel = ?", 0);
        $select->where("tb2.IsActive = ?", 1);
        $select->where("tb2.ImgType = ?", $ImgType);
        $result = $this->db->fetchAll($select);
        // echo $select;print_r($result);die('ppp');
        return $result;
    }

    public function getApiCityDetails()
    {

        $arrCity = array('APICityId');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_API_City"), $arrCity);
        if (!empty($this->intGTXCityId)) {
            $select->where("GTXCityId = ? ", $this->intGTXCityId);
        }
        if (!empty($this->intICSourceSysId)) {
            $select->where("SourceSysId = ? ", $this->intICSourceSysId);
        }
        $select->where("IsActive = ? ", 1);
        $select->limit(1);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        //echo "<pre>"; print_r($result); exit;
        if (count($result) > 0) {
            return $result[0]['APICityId'];
        } else {
            return '';
        }
        //return $result[0]['APICityId'];
    }

    public function getCityDetailsByMultipleId($CityIds)
    {
            $resultPackageActivity = array();
            $CityIds = ltrim(rtrim(trim($CityIds),','),',');
            $ssIdArrayWithZero = explode(',', $CityIds);
            $ssIdArray = array();
            if(!empty($ssIdArrayWithZero)) {
                foreach($ssIdArrayWithZero as $cityIds) {
                    if((int)$cityIds > 0) {
                       $ssIdArray[] = $cityIds;
                } 
            }
            }
            if (!empty($ssIdArray)) {
                $indexVal = 1;
                $condSition = '';
                $countVal = count($ssIdArray);
                foreach ($ssIdArray as $values) {
                    if((int)$values > 0){
                       $condSition .= '(' . $values . ',' . $indexVal . ')';
                    }
                    if ($countVal != $indexVal) {
                        $condSition .= ', ';
                    }
                    $indexVal++;
                }
                $condSition = ltrim(rtrim($condSition,','),','); 
                if($condSition != ''){
                    $sqlPackageActivity = "SELECT CityId,Title,Country
                    FROM (VALUES $condSition) AS orderTbl(orderKey, orderIdx) 
                    LEFT JOIN TB_Master_Geo_City ON orderTbl.orderKey=TB_Master_Geo_City.CityId
                    ORDER BY orderTbl.orderIdx";
                    //echo $sqlPackageActivity; exit;
                     $resultPackageActivity = $this->db->query($sqlPackageActivity)->fetchAll();
                }


                return $resultPackageActivity;
            }
    }

    public function getCityListWithCountryDetailtest($where, $agencySysId)
    {
        $select = $this->db->select();
        $select->from(array('tbl4' => "TB_TBB2C_Packages_Master"), array('PackageSearchString as SearchString'));
        $select->where($where);
        $select->where("IsActive = ?", 1);
       // $select->where("PackageSearchString IS NOT NULL");
        $select->where("ItemType = ?", 1);
        $select->where("AgencySysId IN (?)", $agencySysId);
        $select->where("IsMarkForDel = ?", 0);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getCityListWithCountryDetailtest1($cityid)
    {
        $country = array('ContId', 'Title as countryTitle', 'Code as countryCode');
        $city = array('CityId', 'TBBCityId', 'Title as cityTitle', 'Alias as cityAlias', 'Code as cityCode', 'Synonyms');
        $arrCity = array('APICityId', 'SourceSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Geo_City"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.ContSysId = tb2.ContId", $country);
        $select->joinLeft(array('tb3' => "TB_API_City"), "tbl.CityId = tb3.GTXCityId", $arrCity);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_State"), "tbl.StateSysId = tb4.StateId", array("Title as StateTitle"));
        $select->where("tbl.CityId = ?", $cityid);
        //$select->where("tb3.SourceSysId IN(3,4)");
        $select->order("tb3.SourceSysId DESC");
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getCityListWithCountryDetailtest2($cityid)
    {
        $country = array('ContId', 'Title as countryTitle', 'Code as countryCode');
        $city = array('TypeId', 'Title as cityTitle', 'TypeTitle');
        $arrCity = array('APICityId', 'SourceSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_MASTER_VIEW_PKGSEARCH_STRING"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.CountryId = tb2.ContId", $country);
        $select->joinLeft(array('tb3' => "TB_API_City"), "tbl.TypeId = tb3.GTXCityId AND tbl.TypeTitle = 'city' AND tb3.SourceSysId = '4'", $arrCity);
        $select->where("tbl.Title IN (?)", $cityid);
        $select->order("tbl.ID DESC");
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getStateListWithCountryDetailtest2($cityid)
    {
        $country = array('ContId', 'Title as countryTitle', 'Code as countryCode');
        $city = array('TypeId', 'Title as cityTitle', 'TypeTitle');

        $select = $this->db->select();
        $select->from(array('tbl' => "TB_MASTER_VIEW_PKGSEARCH_STRING"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.CountryId = tb2.ContId", $country);
        //$select->joinLeft(array('tb3' => "TB_API_City"), "tbl.TypeId = tb3.GTXCityId AND tbl.TypeTitle = 'city' AND tb3.SourceSysId = '4'" , $arrCity);
        $select->where("tbl.Title IN (?)", $cityid);
        $select->where("tbl.CountryId = (?)", 101);
        $select->order("tbl.ID DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getCityListWithCountryDetailForWebsite($cityid, $countryIds)
    {
        $country = array('ContId', 'Title as countryTitle', 'Code as countryCode');
        $city = array('TypeId', 'Title as cityTitle', 'TypeTitle');
        $arrCity = array('APICityId', 'SourceSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_MASTER_VIEW_PKGSEARCH_STRING"), $city);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.CountryId = tb2.ContId", $country);
        $select->joinLeft(array('tb3' => "TB_API_City"), "tbl.TypeId = tb3.GTXCityId AND tbl.TypeTitle = 'city' AND tb3.SourceSysId = '4'", $arrCity);
        $select->where("tbl.Title IN (?)", $cityid);
        $select->where("tbl.TypeTitle = 'city'");
        $select->where("tbl.CountryId IN (?)", $countryIds);
        $select->order("tbl.ID DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }
}