<?php

class Travel_Model_TblFlightProposal
{
    #declare class vairables
    public $intId        = NULL;
    public $TPSysId        = NULL;
    private $db          = NULL;
    public $strCondition = '';
    /**
     * Class Constructors
     */
    public function __construct()
    {

        #initialize db adapter
        $this->db = Zend_Db_Table::getDefaultAdapter();
        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $registry = Zend_Registry::getInstance();
        $this->imageServerUrl = @$registry->imageServerURL;
    }


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


    public function getFlightProposalDetails($MasterTPSysId, $TPSysId = NULL, $AirInvenSysId = NULL)
    {

        $arrTravelPlan_Air = array(
            'InvnItemSysId',
            'Cost',
            'TotalCost',
            'CurrencyType',
            'Discount',
            'XServiceTaxAmount',
            'GTXServiceTaxAmount',
            'AgentServiceTaxAmount',
            'AgentsCustomServiceTaxAmount',
            'MarkUp',
            'AgencyMarkUp',
            'GTXMarkup',
            'VersionId',
            'Taxes',
            'TripType as route'
        );
        $arrTrans_API_Air = array(
            'TBQAirInvenSysId as AirInvenSysId',
            'FlightNumber',
            'AirlineSysId',
            'FlyingMinutes as FlightDuration',
            'SourceAirportCode',
            'JourneyType',
            'DestAirportCode',
            'IsInternational',
            'CurrencyType',
            'IsRefundable',
            'FareClass',
            'ApiResultIndex',
            'apiTraceId',
            'FromUTCTime as ArrivalTime',
            'ToUTCTime as DepartureTime',
            'StopCount',
            'TripType',
            'IsRefundable',
            'NoOfSeatAvailable',
        );
        //$arrTrans_API_Air_Fare = array('PublishedFare','CommissionEarned','PLBEarned','IncentiveEarned','OfferedFare'); CurrencyType
        $arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
        $arrCurrency = array('Symbol as Symbol');



        $select = $this->db->select();
        $select->from(array('t1' => 'TB_Agency_Customer_TravelPlan'), array('TPSysId', 'AgencySysId', 'AgentSysId', 'CustomerSysId', 'IsPlanViewed', 'RoomInfoJson', 'StatusType', 'EconomyMask', 'PackSpecType', 'EmailText', 'MasterTPSysId'));
        $select->joinLeft(array('t2' => 'TB_Agency_Customer_TravelPlan_Itenary'), 't2.TPSysId = t1.TPSysId', array(''));
        $select->joinLeft(array('t3' => 'TB_Agency_Customer_TravelPlan_Air'), 't3.TPIntSysId = t2.TPIntSysId', $arrTravelPlan_Air);
        $select->joinLeft(array('t4' => 'TB_IC_Trans_API_Air'), 't4.TBQAirInvenSysId = t3.InvnItemSysId', $arrTrans_API_Air);
        //$select->joinLeft(array('t6'=>'TB_IC_Trans_API_Air_Fare'),'t6.TBOAirInvenAPISysId = t4.TBQAirInvenSysId',$arrTrans_API_Air_Fare);
        $select->joinLeft(array('t7' => 'TB_Master_Airline'), 't7.AirlineSysId = t4.AirlineSysId ', $arrAirLine);
        $select->joinLeft(array('t8' => 'TB_Master_Currency'), 't8.CurrencyType = t3.CurrencyType ', $arrCurrency);

        if (!empty($MasterTPSysId)) {
            $select->where('t1.MasterTPSysId = ?', $MasterTPSysId);
        }
        if (!empty($TPSysId)) {
            $select->where('t1.TPSysId = ?', $TPSysId);
        }
        if (!empty($AirInvenSysId)) {
            $select->where('t3.InvnItemSysId = ?', $AirInvenSysId);
        }
        $result = $this->db->fetchAll($select);
        //echo "<pre>";print_r($result);
        return $result;
    }


    public function getProposalAgencyDetails($TPintSysId, $type = '')
    {
        $arrTravelPlan = array('AgencySysId', 'AgentSysId', 'CustomerSysId', 'RoomInfoJson', 'SourcePlaces', 'DestinationPlaces', 'MasterTPSysId');
        $arrAgency = array('AgencySysId', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo');
        $arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
        $arrGC = array('Title AS CityTitle', 'Country');


        $select = $this->db->select();

        if ($type == 'query') {
            $select->from(array('TP' => 'TB_Agency_Customer_TravelPlan_Query'), $arrTravelPlan);
        } else {
            $select->from(array('TP' => 'TB_Agency_Customer_TravelPlan'), $arrTravelPlan);
        }
        $select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
        $select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
        $select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);

        if (!empty($TPintSysId)) {
            $select->where('TP.TPSysId = ?', $TPintSysId);
        }
        $result = $this->db->fetchRow($select);
        //echo "<pre>";print_r($result);
        return $result;
    }

    public function checkIsproposalBooked($TPintSysId)
    {
        $arrTravelPlan = array('AgencySysId', 'AgentSysId', 'RoomInfoJson', 'SourcePlaces', 'DestinationPlaces');
        $select = $this->db->select();
        $select->from(array('TP' => 'TB_Agency_Customer_TravelPlan'), $arrTravelPlan);
        if (!empty($TPintSysId)) {
            $select->where('TP.MasterTPSysId = ?', $TPintSysId);
            $select->where('TP.StatusType != ?', 17);
        }
        $result = $this->db->fetchRow($select);
        return $result;
    }


    public function getAirPortByAirportCode($airPortCode)
    {

        $arrAirPort = array('AirportCode', 'AirportTitle', 'CityId as AirportCityId', 'CountryCode');
        $arrCity = array('CityId', 'TBBCityId', 'Title as cityTitle', 'Alias as cityAlias', 'Code as cityCode');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Geo_City"), $arrCity);
        $select->joinInner(array('tb2' => "TB_Master_Airport"), "tbl.CityId = tb2.CityId", $arrAirPort);
        if (!empty($airPortCode)) {
            $select->where("tb2.AirportCode = ?", $airPortCode);
        }
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsApproved = ?", 1);
        $select->where("tb2.IsActive = ?", 1);
        $select->where("tb2.IsMarkForDel = ?", 0);
        // echo $select;
        $result = $this->db->fetchRow($select);
        if (count($result) > 0) {
            if (!empty(trim($result['CountryCode'])) && !empty(trim($result['AirportCode']))) {
                $response = array('AirportCode' => $result['AirportCode'],  'CityID' => $result['CityId'], 'label' => trim($result['cityTitle']) . ", " . trim($result['CountryCode']) . " - " . trim($result['AirportTitle']) . " (" . trim($result['AirportCode']) . ")");
            } else {
                $response = array('AirportCode' => $result['AirportCode'], 'CityID' => $result['CityId'], 'label' => trim($result['cityTitle']) . ", " . trim($result['AirportTitle']) . " (" . trim($result['AirportCode']) . ")");
            }
        }
        return $response;
    }



    public function GetFlightProposalAgencyCustomerDetails($id)
    {

        $agenyCustomer = array('Title', 'Relation', 'Contacts', 'EmailId', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId');
        $arrGeoCity = array('Title as CityTitle');
        $arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode', 'ContId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), $agenyCustomer);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tbl.CitySysId", $arrGeoCity);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb3.ContId = tb2.ContSysId", $arrGeoCountry);
        $select->where("tbl.CustomerSysId = ?", $id);
        $select->limit(1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getVersionIdByTPSysId($TPSysId, $InvnItemSysId = null)
    {

        $travel = array('VersionId');
        $select = $this->db->select();
        $select->from(array('t1' => 'TB_Agency_Customer_TravelPlan'), array(''));
        $select->joinLeft(array('t2' => 'TB_Agency_Customer_TravelPlan_Itenary'), 't2.TPSysId = t1.TPSysId', array(''));
        $select->joinLeft(array('t3' => 'TB_Agency_Customer_TravelPlan_Air'), 't3.TPIntSysId = t2.TPIntSysId', $travel);
        $select->where('t1.TPSysId = ?', $TPSysId);
        if (!empty($InvnItemSysId)) {
            $select->where('t3.InvnItemSysId = ?', $InvnItemSysId);
        }
        $result = $this->db->fetchRow($select);
        return $result['VersionId'];
    }


    public function getFlightProposalDetailsByAirInvenSysId($AirInvenSysId)
    {
        $select = $this->db->select();
        $select->from(array('t1' => 'TB_Agency_Customer_TravelPlan_Air'));
        $select->where('t1.InvnItemSysId = ?', $AirInvenSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }




    public function getVersionIdByTPSysIds($TPSysId)
    {

        $travel = array('VersionId');
        $select = $this->db->select();
        $select->from(array('t1' => 'TB_Agency_Customer_TravelPlan'), array('TPSysId', 'MasterTPSysId', 'RoomInfoJson', 'AgentSysId'));
        $select->joinLeft(array('t2' => 'TB_Agency_Customer_TravelPlan_Itenary'), 't2.TPSysId = t1.TPSysId', array(''));
        $select->joinLeft(array('t3' => 'TB_Agency_Customer_TravelPlan_Air'), 't3.TPIntSysId = t2.TPIntSysId', $travel);
        $select->where('t1.TPSysId = ?', $TPSysId);
        if (!empty($this->strCondition)) {
            $select->where($this->strCondition);
        }
        //echo $select;exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }
}