<?php

class Travel_Model_TblTransportsRoutes {

    private $db = NULL;
    public $strCondition = NULL;

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

    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 updateData($table, $data, $where) {
        $this->db->update($table, $data, $where);
    }

    public function insertData($table, $data) {
        //echo "<pre>";print_r($data);exit;
        $this->db->insert($table, $data);
        return $this->db->lastInsertId();
    }

    public function getAllVehicleTypeList() {

        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_VehicleType"), array('VehicleTypeSysId', 'Title'));
        $select->where("tbl.IsActive = ?", 1);
        $select->order("tbl.Title");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getVehicleList($vehicleTypeSysId, $intLoggedinUserAgencySysId = null) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_Vehicle"), array('VehSysId','VehTypeSysId', 'Capacity', 'VehName', new Zend_Db_Expr("CASE WHEN IsAC = 1 then 'AC' else 'NON-AC' END as IsAC")));
        if (!empty($vehicleTypeSysId)) {
            $select->where("tbl.VehTypeSysId = ?", $vehicleTypeSysId);
        }
        if ($intLoggedinUserAgencySysId != null) {
            $agencyArr = array(0, $intLoggedinUserAgencySysId);
            if($intLoggedinUserAgencySysId == 139360){
                $select->where("tbl.AgencySysId = ?", $intLoggedinUserAgencySysId);
            }else{
                $select->where("tbl.AgencySysId IN(?)", $agencyArr);
            }
            
        }
        $select->where("tbl.IsActive = ?", 1);
        $select->order("tbl.VehName");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function addTransportsRoute($data) {
        $this->db->insert('TB_Agency_Transport_Route', $data);
        return $this->db->lastInsertId();
    }

    public function editTransportsRoute($id, $data) {
        $where = array('RouteSysId = ? ' => $id);
        $this->db->update('TB_Agency_Transport_Route', $data, $where);
    }

    public function editTransportsRouteData($id, $data) {
        $where = array('RouteSysId = ? ' => $id);
        $result = $this->db->update('TB_Agency_Transport_Route', $data, $where);
        return $result;
    }

    public function addTransportsRoutesCityIncluded($data) {
        $this->db->insert('TB_Agency_Transport_Route_Destinations', $data);
        return $this->db->lastInsertId();
    }

    public function deleteTransportsRoutesCityIncluded($id) {
        $where = array('RouteSysId = ? ' => $id);
        $this->db->delete('TB_Agency_Transport_Route_Destinations', $where);
    }

    public function addRoutesVehicles($data) {
        $this->db->insert('TB_Agency_Transport_Route_Vehicle', $data);
        return $this->db->lastInsertId();
    }

    public function transportsRoutesList() {

        $arrTransportRoute = array('RouteSysId', 'RouteName', 'RouteDestinationStr', 'IsActive', 'Noofnights', 'IsWebsite', 'TransportType', 'TransportSubType');

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route"), $arrTransportRoute);
        $select->joinLeft(array('tb2' => "TB_IC_Supplier"), "tb2.SupplierSysId = tb1.SupplierSysId", array('SupplierName'));
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tb1.StartCitySysId", array('Title as CityName'));
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb4.CityId = tb1.EndCitySysId", array('Title as EndCityName'));
        $select->joinLeft(array('tb5' => "TB_Master_Geo_City"), "tb5.CityId = tb1.DestinationCitySysId", array('Title as DestinationCityName'));
        $select->joinLeft(array('tb6' => "TB_Master_Geo_State"), "tb6.StateId = tb1.StateSysId", array('Title as StateName'));
        $select->joinLeft(array('tb7' => "TB_Master_Geo_Country"), "tb7.ContId = tb1.CountrySysId", array('Title as CountryName'));
        if (!empty($this->strConditionPick)) {
            $select->joinInner(array('tb7' => "TB_Agency_Transport_Area"), "tb7.RouteId = tb1.RouteSysId", array(''));
        }

        if (!empty($this->strCondition)) {
            $select->where($this->strCondition);
        }
        if (!empty($this->strConditionPick)) {
            $select->where($this->strConditionPick);
        }
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.RouteSysId DESC");
//        echo $select;die;
        return $select;
    }

    public function transportsRoutesListSearch() {

        $arrTransportRoute = array('RouteSysId', 'RouteName', 'RouteDestinationStr', 'IsActive', 'Noofnights', 'IsWebsite', 'StartCitySysId', 'EndCitySysId', 'DestinationCitySysId', 'TransportType', 'TransportSubType');

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route_Vehicle"));
        $select->joinLeft(array('tb4' => "TB_Agency_Transport_Route"), "tb1.RouteSysId = tb4.RouteSysId", $arrTransportRoute);
        $select->joinLeft(array('tb2' => "TB_IC_Supplier"), "tb2.SupplierSysId = tb1.SupplierSysId", array('SupplierName'));
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tb4.StartCitySysId", array('Title as CityName'));
        $select->joinLeft(array('tb6' => "TB_Master_Geo_City"), "tb6.CityId = tb4.EndCitySysId", array('Title as EndCityName'));
        $select->joinLeft(array('tb7' => "TB_Master_Geo_City"), "tb7.CityId = tb4.DestinationCitySysId", array('Title as DestinationCityName'));
        $select->joinLeft(array('tb5' => "TB_Master_Vehicle"), "tb5.VehSysId = tb1.VehicleSysId", array('VehName', 'IsAC', 'VehSysId'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_State"), "tb8.StateId = tb4.StateSysId", array('Title as StateName'));
        $select->joinLeft(array('tb9' => "TB_Master_Currency"), "tb9.CurrencyType = tb1.CurrencyType", array('Symbol as CurrencySymbol'));
        if (!empty($this->strConditionsear)) {
            $select->where($this->strConditionsear);
        }
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->where("tb4.IsActive = ?", 1);
        $select->where("tb4.IsMarkForDel = ?", 0);
        $select->order("tb1.RouteSysId DESC");
//        echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTransportsRouteDetailsById($id) {

        $arrTransportRoute = array('RouteSysId', 'RouteName', 'SupplierSysId', 'Description', 'StartCitySysId', 'Noofnights', 'EndCitySysId', 'Iswebsite', 'TransportType', 'TransportSubType', 'DestinationCitySysId', 'StateSysId', 'CountrySysId');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route"), $arrTransportRoute);
        $select->joinLeft(array('tb2' => "TB_IC_Supplier"), "tb2.SupplierSysId = tb1.SupplierSysId", array('SupplierName'));
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tb1.StartCitySysId", array('Title as CityName'));
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb4.CityId = tb1.EndCitySysId", array('Title as EndCityName'));
        $select->joinLeft(array('tb5' => "TB_Master_Geo_City"), "tb5.CityId = tb1.DestinationCitySysId", array('Title as DestinationCityName'));
        $select->joinLeft(array('tb6' => "TB_Master_Geo_State"), "tb6.StateId = tb1.StateSysId", array('Title as StateName'));
        $select->joinLeft(array('tb7' => "TB_Master_Geo_Country"), "tb7.ContId = tb1.CountrySysId", array('Title as CountryName'));
        $select->where("tb1.RouteSysId = ?", $id);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTransportsRoutesCityIncluded($id,$CheckSightseeing = 0) {

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route_Destinations"), array('DestSysId', 'nights', 'DestinationCovered', 'destSequence'));

        $select->joinLeft(array('tb3' => "TB_Agency_Transport_Route_Destinations_SightSeeings"), "tb3.DestSysId = tb1.DestSysId AND tb3.RouteDestSysId = tb1.RouteDestSysId AND tb3.RouteSysId = $id", array('SightSeeingName', 'SSSysId', 'Sequence'));
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tb1.DestSysId", array('Title as CityName'));
        $select->where("tb1.RouteSysId = ?", $id);
        if($CheckSightseeing == 1){
            $select->where("tb3.SSSysId > ?", 0);
        }
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.DestSysId != ?", 0);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.RouteDestSysId ASC");
//        echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTransportsRoutesVehiclesIncluded($id, $RouteVechSysId = 0) {

        $arrRouteVehicles = array('RouteSysId','RouteVechSysId', 'VehicleSysId', 'VehicleTypeSysId', 'Cost', 'ChildCost', 'ChildAge', 'Capacity', 'CurrencyType', 'vehicleCostType', 'vehicleKM', 'vehiclePerKMCost', 'vehicleDriverPerDayCost', 'vehicleDriverPerNightCost', 'vehicleTollTax', 'vehicleOtherCost', 'SupplierSysId', 'TotalCost', 'TotalChildCost', 'MarkupType', 'Markup', 'FromDate', 'ToDate', 'MarketPlaceId','B2BMarkUp','B2BTotalCost','B2BChildTotalCost');

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route_Vehicle"), $arrRouteVehicles);
        $select->joinLeft(array('tb2' => "TB_IC_Supplier"), "tb2.SupplierSysId = tb1.SupplierSysId", array('SupplierName'));
        $select->joinLeft(array('tb3' => "TB_Master_Vehicle"), "tb3.VehSysId = tb1.VehicleSysId", array('VehName', 'IsAC'));
        $select->joinLeft(array('tb4' => "TB_Master_VehicleType"), "tb4.VehicleTypeSysId = tb1.VehicleTypeSysId", array('Title as VehicleTypeName'));
        $select->joinLeft(array('tb5' => "TB_Master_Currency"), "tb5.CurrencyType = tb1.CurrencyType", array('Symbol as CurrencySymbol'));
        $select->where("tb1.RouteSysId = ?", $id);
        if ($RouteVechSysId > 0) {
            $select->where("tb1.RouteVechSysId = ?", $RouteVechSysId);
        }
        $select->where("tb1.IsActive = ?", 1);
        $select->order("tb1.FromDate ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function deleteTransportRoute($intId, $data) {
        $strWhereCondition = array('RouteSysId = ?' => $intId);
        $this->db->update('TB_Agency_Transport_Route', $data, $strWhereCondition);
    }

    public function deleteTransportsRoutesVehiclesIncluded($intId) {
        if(is_array($intId)){
            $where = array('RouteVechSysId IN(?) ' => $intId);
            $this->db->delete('TB_Agency_Transport_Route_Vehicle', $where);
        }else{
            if($intId > 0){
                $where = array('RouteVechSysId = ? ' => $intId);
                $this->db->delete('TB_Agency_Transport_Route_Vehicle', $where);
            } 
        }
        
    }

    public function selectCityCovered($routesCityIds) {
        $routesCityIds = explode(',', $routesCityIds);
        $arrRouteVehicles = array('CityId', 'Title', 'IsActive', 'Country');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_Geo_City"), $arrRouteVehicles);
        $select->where("tb1.CityId IN(?)", $routesCityIds);
        $select->where("tb1.IsActive = ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function busTransportsRoutesList($AgencySysId) {

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Bus_Route"));
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.BusRouteSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function addBusTransportRoute($data) {
        return $this->db->insert('TB_Agency_Transport_bus_Route', $data);
    }

    public function getBusTransportRouteDataById($AgencySysId, $BusRouteSysId) {

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Bus_Route"));
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.BusRouteSysId = ?", $BusRouteSysId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", '0');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getBusPickupLocationTimeDataById($AgencySysId, $BusRouteSysId) {

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_bus_Pickup_Time"));
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.BusRouteSysId = ?", $BusRouteSysId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", '0');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getBusPickupLocationTimeDataByMasterId($AgencySysId, $MasterId) {

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_bus_Pickup_Time"));
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.MasterRefTPSysID = ?", $MasterId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", '0');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function updateBusTransportsRouteData($id, $data) {
        $where = array('BusRouteSysId = ? ' => $id);
        $result = $this->db->update('TB_Agency_Transport_Bus_Route', $data, $where);
        return $result;
    }

    public function deleteBusTransportRoute($intId, $data) {
        $strWhereCondition = array('BusRouteSysId = ?' => $intId);
        $this->db->update('TB_Agency_Transport_Bus_Route', $data, $strWhereCondition);
    }

    public function getPickUpLocationsByStartEndCityId($AgencySysId, $startCityId, $EndCityId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Bus_Route"));
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.StartCitySysId = ?", $startCityId);
        $select->where("tb1.EndCitySysId = ?", $EndCityId);

        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", '0');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function deleteTransportsRoutesCityIncludedSightSeeing($id) {
        $where = array('RouteSysId = ? ' => $id);
        $this->db->delete('TB_Agency_Transport_Route_Destinations_SightSeeings', $where);
    }

    public function getAlltransportRouteSearchList() {

        $arrTransportRoute = array('RouteSysId', 'RouteName', 'RouteDestinationStr', 'IsActive', 'Noofnights', 'IsWebsite');

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route", $arrTransportRoute));
        $select->joinLeft(array('tb2' => "TB_Agency_Transport_Route_Vehicle"), "tb1.RouteSysId = tb2.RouteSysId");
        $select->joinLeft(array('tb3' => "TB_IC_Supplier"), "tb3.SupplierSysId = tb2.SupplierSysId", array('SupplierName'));
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb4.CityId = tb1.StartCitySysId", array('Title as CityName'));
        $select->joinLeft(array('tb5' => "TB_Master_Vehicle"), "tb5.VehSysId = tb2.VehicleSysId", array('VehSysId', 'Capacity', 'VehName', 'IsAC', new Zend_Db_Expr("CASE WHEN IsAC = 1 then 'AC' else 'NON-AC' END as IsAC")));
        $select->joinLeft(array('tb6' => "TB_Agency_Transport_Route_Destinations_SightSeeings"), "tb6.RouteSysId = tb1.RouteSysId", array('SSSysId', 'SightSeeingName'));
        $select->joinLeft(array('tb7' => "TB_IC_SightSeeing"), "tb7.SSSysId = tb6.SSSysId", array('CitySysId', 'ContSysId'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb8.CityId = tb7.CitySysId", array('Title as SightSeeingCityName'));
        $select->joinLeft(array('tb9' => "TB_Master_Geo_Country"), "tb9.ContId = tb7.ContSysId", array('Title as SightSeeingCountryName'));
        $select->joinLeft(array('tb10' => "TB_Master_Geo_City"), "tb10.CityId = tb1.EndCitySysId", array('Title as EndCityName'));
        if (!empty($this->strConditionsear)) {
            $select->where($this->strConditionsear);
        }
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", '0');
        $select->where("tb2.IsActive = ?", 1);
        $select->where("tb2.IsMarkForDel = ?", '0');
        $select->order("tb1.RouteSysId DESC");
//        echo $select;die;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getpickupPointAutoSuggest($term, $AgencySysId = 0, $CityId) {
        $select = $this->db->select();

        $select->from(array('tb1' => "TB_Agency_Area_Master"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);

        if ($CityId > 0) {
            $select->where("tb1.CityId =?", $CityId);
        }
        $select->where('Area LIKE ?', '%' . $term . '%');
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb1.IsActive =?", 1);
        $select->order(array('Area ASC'));
        $result = $this->db->fetchAll($select);

        if (count($result) > 0) {
            foreach ($result as $row) {
                $response[] = array('AreaSysId' => $row['AreaSysId'], 'Area' => $row['Area'], 'CityName' => $row['CityName'], 'label' => $row['Area']);
            }
        }
//            else {
//                $response[] = array('AreaSysId' => 0, 'Area' => 'Add New Item','CityName' => 0);
//            }
        return $response;
    }

    public function getAreaSysIdByName($AreaName, $AgencySysId, $cityId) {
        if (!empty($AreaName)) {
            $select = $this->db->select();
            $select->from(array('tb1' => "TB_Agency_Area_Master"),array('AreaSysId'));
            $select->where("tb1.AgencySysId = ?", $AgencySysId);
            if ($cityId > 0) {
                $select->where("tb1.CityId = ?", $cityId);
            }
            $select->where("tb1.Area = ?", trim($AreaName));
            $select->where("tb1.IsMarkForDel = ?", 0);
            $select->where("tb1.IsActive = ?", 1);
            
            $result = $this->db->fetchRow($select);
            return $result;
        } else {
            return null;
        }
    }
    

    public function getTransportsRoutesAreas($AgencySysId, $RouteSysId) {

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Area"));
        $select->joinLeft(array('tb2' => "TB_Agency_Area_Master"), "tb2.AreaSysId = tb1.pickupAreaId AND tb2.IsMarkForDel = '0'", array('Area as pickupArea'));
        $select->joinLeft(array('tb3' => "TB_Agency_Area_Master"), "tb3.AreaSysId = tb1.dropAreaId AND tb3.IsMarkForDel = '0'", array('Area as dropArea'));
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.RouteId = ?", $RouteSysId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", '0');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function deleteTransportsRoutesAreas($AgencySysId, $id) {
        $where = array('RouteId = ? ' => $id, 'AgencySysId = ? ' => $AgencySysId);
        $this->db->delete('TB_Agency_Transport_Area', $where);
    }

    public function getTransportsRoutesSightseeingIncluded($id) {

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route_Destinations_SightSeeings"), array('SightSeeingName', 'SSSysId', 'Sequence', 'RouteSysId', 'DestSysId', 'RouteSightSysId', 'RouteDestSysId'));
        $select->joinLeft(array('tb2' => "TB_Agency_Transport_Route"), "tb2.RouteSysId = tb1.RouteSysId AND tb2.IsMarkForDel = '0'", array('TransportSubType'));
        $select->where("tb1.RouteSysId IN(?) ", $id);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.RouteSightSysId ASC");
        $result = $this->db->fetchAll($select);

        $select1 = $this->db->select();
        $select1->from(array('tb1' => "TB_Agency_Transport_Route_Destinations"), array('RouteDestSysId', 'DestSysId', 'nights', 'DestinationCovered', 'destSequence', 'RouteSysId'));
        $select1->joinLeft(array('tb2' => "TB_Agency_Transport_Route"), "tb2.RouteSysId = tb1.RouteSysId AND tb2.IsMarkForDel = '0'", array('TransportSubType'));
        $select1->where("tb1.RouteSysId IN(?) ", $id);
        $select1->where("tb1.IsActive = ?", 1);
        $select1->where("tb1.IsMarkForDel = ?", 0);
        $select1->order("tb1.RouteDestSysId ASC");
        $result1 = $this->db->fetchAll($select1);
        return array('destination' => $result1, 'sightseeing' => $result);
    }

    public function getSightEntryFeesById($SSSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_MP_Inventory_SightSeeing"), array('AdultEntryFee', 'ChildEntryFee', 'XRef', 'CostCurrency'));
        $select->where("tb1.XRef IN (?)", $SSSysId);
        $select->where("tb1.IsSaleStart = ?", 1);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.InvnItemSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }
    public function CheckTransportRouteCostSeason($RouteSysId, $VehicleSysId, $VehicleTypeSysId, $SeasonFromDate, $SeasonToDate) {
        $whereCondition = " RouteSysId = '" . $RouteSysId . "' AND VehicleSysId = '" . $VehicleSysId . "' AND VehicleTypeSysId = '" . $VehicleTypeSysId . "' AND IsMarkForDel = '0' AND IsActive = '1' AND " 
                . "(
                    ('$SeasonFromDate' BETWEEN CAST(TBATRV.FromDate AS DATE) AND CAST(TBATRV.ToDate AS DATE)  OR '$SeasonToDate' BETWEEN CAST(TBATRV.FromDate AS DATE) AND CAST(TBATRV.ToDate AS DATE))
                    OR
                    (CAST(TBATRV.FromDate AS DATE) BETWEEN '$SeasonFromDate' AND '$SeasonToDate'  OR CAST(TBATRV.ToDate AS DATE) BETWEEN '$SeasonFromDate' AND '$SeasonToDate')
                   )";
        $select = $this->db->select()
                ->from(array("TBATRV" => "TB_Agency_Transport_Route_Vehicle"),array('RouteVechSysId'))
                ->where($whereCondition);
        //echo $select; die;
        $result = $this->db->fetchRow($select);
        return $result;
    }
    public function checkTransportsRoutesVehiclesIncluded($id, $RouteVechSysId = 0) {

        $arrRouteVehicles = array('RouteSysId','RouteVechSysId','VehicleSysId','VehicleTypeSysId','FromDate','ToDate');

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Transport_Route_Vehicle"), $arrRouteVehicles);
        $select->where("tb1.RouteSysId = ?", $id);
        if ($RouteVechSysId > 0) {
            $select->where("tb1.RouteVechSysId = ?", $RouteVechSysId);
        }
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.FromDate ASC");
        $result = $this->db->fetchRow($select);
        return $result;
    }
}