<?php

class Travel_Model_TblPackageSearch {

    public $intId = NULL;
    private $db = NULL;

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

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

    public function searchPackage($AgencySysId, $getDataFinal, $filterData = array()) {
        // echo "<pre>";print_r($filterData);echo "</pre>";
        $IsGTXNetWork = isset($getDataFinal['IsGTXNetWork']) ? $getDataFinal['IsGTXNetWork'] : false;
        if ($getDataFinal['search_specific_date'] && $getDataFinal['search_specific_date'] != '__/__/____') {
            $tripDate = $getDataFinal['search_specific_date'];
        } else {
            $tripDate = date('d/m/Y');
        }
        $travelDate = date("Y-m-d", strtotime(str_replace('/', '-', $tripDate)));
        $seachMultiData = $getDataFinal['search_going_to_city'];
        $whereCondition = '';
        if (trim($seachMultiData)) {
            $expodeDataSet = explode(',', str_replace(",,", "", $seachMultiData));
            if (!empty($expodeDataSet)) {
                $whereCondition .= " AND ( ";
                for ($i = 0; $i < count($expodeDataSet); $i++) {
                    $whereCondition .= "  TBTP.SearchString LIKE '%" . trim($expodeDataSet[$i]) . "%' ";
                    if (count($expodeDataSet) > $i + 1) {
                        $whereCondition .= " OR ";
                    }
                }
                $whereCondition .= " ) ";
            }
        }
        if ($IsGTXNetWork == true) {
            $whereCondition .= " AND TBTP.IsGTXNetwork = 1 AND TBTP.AgencySysId != $AgencySysId";
            $select = $this->db->select()->from(array("TBTP" => "TB_TravelPlan"), array("PackSpecType", "IsFixedDeparturePackage", "TPSysId", "Title", "InclMask", "MasterRefId", "EconomyMask", '(select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId AND TPTI.IsMarkForDel = 0 group by TPSysId) AS days', "SourcePlaces", "DestinationPlaces", "AgencySysId", "bookingvalidityDay", "IsCommonFlightPackage"))
                    ->joinLeft(array('TB' => "TB_Agency"), "TBTP.AgencySysId = TB.AgencySysId", array(""))
                    ->joinLeft(array('TBICS' => "TB_IC_Supplier"), "TBTP.AgencySysId = TBICS.SupplierAgencySysId", array("SupplierName", "Rating as SupplierRating", "SupplierSysId"))
                    ->where("TBTP.IsActive = 1 AND TBTP.IsCustEdit = '0' AND TBTP.IsMarkForDel = 0 
AND TBTP.IsAprooved = 1 
AND TBTP.MasterRefId = 0 
AND TB.IsGTXNetworkWeb = 1
AND TB.IsActive = 1
AND '$travelDate' <= CAST(TBTP.PkgSellValidTill AS DATE) 
$whereCondition
AND TBTP.MinPax <= CASE WHEN (TBTP.MinPax != '') THEN 2 ELSE 0 END");
            //  echo $whereCondition;die;
        } else {
            $whereCondition .= " AND TBTP.AgencySysId = $AgencySysId";
            $select = $this->db->select()->from(array("TBTP" => "TB_TravelPlan"), array("PackSpecType", "IsFixedDeparturePackage", "TPSysId", "Title", "InclMask", "MasterRefId", "EconomyMask", "SupplierSysId", '(select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId AND TPTI.IsMarkForDel = 0 group by TPSysId) AS days', "SourcePlaces", "DestinationPlaces", "AgencySysId", "bookingvalidityDay", "IsCommonFlightPackage"))
                    ->joinLeft(array('TBICS' => "TB_IC_Supplier"), "TBTP.SupplierSysId = TBICS.SupplierSysId", array("SupplierName", "Rating as SupplierRating"))
                    ->where("TBTP.IsActive = 1 AND TBTP.IsCustEdit = '0' AND TBTP.IsMarkForDel = 0 
AND TBTP.IsAprooved = 1 
AND TBTP.MasterRefId = 0 
AND '$travelDate' <= CAST(TBTP.PkgSellValidTill AS DATE) 
$whereCondition
AND TBTP.MinPax <= CASE WHEN (TBTP.MinPax != '') THEN 2 ELSE 0 END");
        }


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

    public function searchPackageWithAllNetwork($AgencySysId, $getDataFinal, $filterData = array()) {
        $IsGTXNetWork = isset($getDataFinal['IsGTXNetWork']) ? $getDataFinal['IsGTXNetWork'] : false;
        if ($getDataFinal['search_specific_date'] && $getDataFinal['search_specific_date'] != '__/__/____') {
            $tripDate = $getDataFinal['search_specific_date'];
        } else {
            $tripDate = date('d/m/Y');
        }
        $travelDate = date("Y-m-d", strtotime(str_replace('/', '-', $tripDate)));
        $seachMultiData = $getDataFinal['search_going_to_city'];
        $whereCondition = '';
        if (trim($seachMultiData)) {
            $expodeDataSet = explode(',', str_replace(",,", "", $seachMultiData));
            if (!empty($expodeDataSet)) {
                $whereCondition .= " ( ";
                for ($i = 0; $i < count($expodeDataSet); $i++) {
                    $whereCondition .= "  TBTP.SearchString LIKE '%" . trim($expodeDataSet[$i]) . "%' ";
                    if (count($expodeDataSet) > $i + 1) {
                        $whereCondition .= " OR ";
                    }
                }
                $whereCondition .= " ) ";
            }
        }
        $select = $this->db->select();
        $subqueryDays = $this->db->select()
                ->from('TB_TravelPlan_Itenary', ['MAX(sequence)'])
                ->where('TB_TravelPlan_Itenary.TPSysId = TBTP.TPSysId')
                ->where('TB_TravelPlan_Itenary.IsMarkForDel = ?', 0)
                ->group('TB_TravelPlan_Itenary.TPSysId');
        $select->from(['TBTP' => 'TB_TravelPlan'], [
            'PackSpecType',
            'IsFixedDeparturePackage',
            'TPSysId',
            'Title',
            'InclMask',
            'MasterRefId',
            'EconomyMask',
            'days' => new Zend_Db_Expr("($subqueryDays)"),
            'SourcePlaces',
            'DestinationPlaces',
            'AgencySysId',
            'bookingvalidityDay',
            'IsCommonFlightPackage',
            'IsGTXNetwork' => new Zend_Db_Expr("CASE WHEN $AgencySysId = TBTP.AgencySysId THEN 0 ELSE 1 END"),
            //'TBTP.IsGTXNetwork',
            'SupplierSysId' => new Zend_Db_Expr('COALESCE(TBICS1.SupplierSysId, TBICS2.SupplierSysId)'),
            'SupplierName' => new Zend_Db_Expr('COALESCE(TBICS1.SupplierName, TBICS2.SupplierName)'),
            'SupplierRating' => new Zend_Db_Expr('COALESCE(TBICS1.Rating, TBICS2.Rating)')
        ]);
        $select->joinLeft(
        ['TBICS1' => 'TB_IC_Supplier'],
        "CASE WHEN $AgencySysId = TBTP.AgencySysId THEN 0 ELSE 1 END = 1 AND TBTP.AgencySysId = TBICS1.SupplierAgencySysId",
        []
    )
    ->joinLeft(
        ['TBICS2' => 'TB_IC_Supplier'],
        "CASE WHEN $AgencySysId = TBTP.AgencySysId THEN 0 ELSE 1 END != 1 AND TBTP.SupplierSysId = TBICS2.SupplierSysId",
        []
    );
        $select->joinLeft(
                        ['TB' => 'TB_Agency'],
                        'TBTP.AgencySysId = TB.AgencySysId',
                        []
                )
                ->where('TBTP.IsActive = ?', 1)
                ->where('TBTP.IsCustEdit = ?', 0)
                ->where('TBTP.IsMarkForDel = ?', 0)
                ->where('TBTP.IsAprooved = ?', 1)
                ->where('TBTP.MasterRefId = ?', 0)
                ->where('CAST(TBTP.PkgSellValidTill AS DATE) >= ?', $travelDate)
                ->where($whereCondition)
                ->where('TBTP.MinPax <= ?', new Zend_Db_Expr("CASE WHEN (TBTP.MinPax != '') THEN 2 ELSE 0 END"))
                ->where(
                        '(TBTP.AgencySysId = ' . $AgencySysId . ' OR (TB.IsGTXNetworkWeb = 1 AND TB.IsActive = 1 AND TBTP.IsGTXNetworkWeb = 1 AND TBTP.AgencySysId != ' . $AgencySysId . '))'
        );
        $select->order("IsGTXNetwork ASC");
        //echo $select; exit;
        $result = $this->db->query($select)->fetchAll();
        return $result;
    }

}