<?php

class Travel_Model_TblAirport
{

    private $db = NULL;

    public $intId = NULL;

    public $strAirportCode = NULL;

    public $baseUrl;

    public function __construct()
    {
        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }

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

    public function getAirportAutoSearchList($strAirport)
    {
        $sql = "Select AirportCode, AirportTitle as label, AirportTitle AS value, AirportTitle From TB_Master_Airport where 1 = 1 AND IsActive='1' ";

        if (!empty($strAirport)) {
            $sql .= " AND AirportTitle LIKE '$strAirport%' ";
        }

        $sql .= " ORDER BY AirportTitle ";

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


    public function getAirportList()
    {
        $sql = " SELECT AirportCode, AirportTitle,TB_Master_Airport.CountryCode,TB_Master_Airport.CityId,TB_Master_Geo_City.Country as CountryName, TB_Master_Geo_City.Title as CityName
                FROM TB_Master_Airport INNER JOIN TB_Master_Geo_City
                ON TB_Master_Airport.CityId = TB_Master_Geo_City.CityId
                WHERE 1=1
                ";

        if (!empty($this->strAirportCode)) {
            $sql .= " AND TB_Master_Airport.AirportCode = '" . $this->strAirportCode . "'";
        }

        $sql .= " ORDER BY AirportTitle ";

        return $this->db->query($sql)->fetchAll();
    }
    public function getMultipleAirportList($strAirportCode)
    {
        if(!empty($strAirportCode)){
            $sql = " SELECT AirportCode, AirportTitle,TB_Master_Airport.CountryCode,TB_Master_Airport.CityId,TB_Master_Geo_City.Country as CountryName, TB_Master_Geo_City.Title as CityName
                FROM TB_Master_Airport INNER JOIN TB_Master_Geo_City
                ON TB_Master_Airport.CityId = TB_Master_Geo_City.CityId
                WHERE 1=1
                ";
            $sql .= " AND TB_Master_Airport.AirportCode IN(" . $strAirportCode . ")";


            $sql .= " ORDER BY AirportTitle ";

            return $this->db->query($sql)->fetchAll();
        }
        
    }
    //this function used to fetch airport detail with city and country. created by Amit Kumar Dubey on 3 nov 2016 at 3:48 PM
    public function getAirportListWithCountryCode()
    {
        $sql = " SELECT AirportCode, AirportTitle,TB_Master_Airport.CityId, TB_Master_Geo_City.Title as CityName,
                TB_Master_Geo_Country.Code as countrycode FROM TB_Master_Airport INNER JOIN TB_Master_Geo_City
                ON TB_Master_Airport.CityId = TB_Master_Geo_City.CityId INNER JOIN TB_Master_Geo_Country 
                on TB_Master_Geo_Country.ContId=TB_Master_Airport.CountrySysId
                WHERE 1=1";

        if (!empty($this->strAirportCode)) {
            $sql .= " AND TB_Master_Airport.AirportCode = '" . $this->strAirportCode . "'";
        }

        $sql .= " ORDER BY AirportTitle ";

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


    public function getAirportListingRecord()
    {
        //echo "<pre>";        print_r($this->searchArr); die;

        $whereCondition = "tbl.IsMarkForDel = '0' ";
        if ($this->searchArr['Title'] != '') {
            $Title = $this->searchArr['Title'];
            $whereCondition .= " AND ( AirportTitle = '$Title' OR AirportTitle LIKE '$Title%' )";
        }
        if ($this->searchArr['Code'] != '') {
            $Code = $this->searchArr['Code'];
            $whereCondition .= " AND ( AirportCode = '$Code' OR AirportCode LIKE '$Code%' )";
        }
        if ($this->searchArr['CityId'] != '') {
            $CityId = $this->searchArr['CityId'];
            $whereCondition .= " AND ( tbl.CityId = '$CityId' OR tbl.CityId LIKE '$CityId%' )";
        }
        if ($this->searchArr['CountryId'] != '') {
            $CountryId = $this->searchArr['CountryId'];
            $whereCondition .= " AND ( tbl.CountrySysId = '$CountryId' OR tbl.CountrySysId LIKE '$CountryId%' )";
        }
        if ($this->searchArr['filter1'] != '') {
            $filter1 = $this->searchArr['filter1'];
            $whereCondition .= " AND ( tbl.IsActive = '$filter1' )";
        }

        //echo $tpinttSysID;
        $travel_airport = array('ISO', 'AirportCode', 'AirportTitle', 'IsActive');
        $country = array('Title as CountryName');
        $cityname = array('Title as CityName');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Airport"), $travel_airport);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tbl.CountrySysId = tb2.ContId", $country);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tbl.CityId = tb3.CityId", $cityname);
        $select->where($whereCondition);
        $select->order('RTRIM(LTRIM(tbl.AirportTitle))');
        $result = $this->db->fetchAll($select);
        return $result;
    }


    public function getDetailsByUniqueId($airport_id)
    {
        //echo $tpinttSysID;
        $travel_airport = array('*');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Airport"), $travel_airport);
        $select->where('tbl.ISO =?', $airport_id);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getRecordListingWhere($CountrySysId)
    {

        //echo $tpinttSysID;
        $cityname = array('CityId', 'Title');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_Geo_City"), $cityname);
        $select->where('tb1.ContSysId =?', $CountrySysId);
        $select->order('tb1.Title');
        //echo $select; die;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getRecordListingWhere2($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 updateTable($editData, $where)
    {
        return $this->db->update('TB_Master_Airport', $editData, $where);
    }



    //Added BY Pardeep Panchal...


    public function getCityIdsAndAirPortCodeList()
    {

        $arrAirPort = array('AirportCode', 'CityId');

        $select = $this->db->select();
        $select->from(array("t1" => "TB_Master_Airport"), $arrAirPort);
        $select->where("IsActive = ?", 1);
        $select->where("CityId != ?", 0);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        $arrAirPortList = array();
        if (count($result) > 0) {
            foreach ($result as $res) {
                $arrAirPortList[trim($res['AirportCode'])] = trim($res['CityId']);
            }
        }
        return $arrAirPortList;
    }

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

    //Added BY Pardeep Panchal...



}