<?php

class Travel_Model_HotelMaster {

    protected $db = NULL;
    public $intId = 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 getHotel($destinationId, $table, $checkInDate, $checkOutDate, $adults, $child, $infent, $refId, $destIndex) {
        $destinationId = trim($destinationId);
        //$destinationId = 6000;
        $response = array();
        $sql = "select * FROM $table A WHERE DestinationsId = $destinationId AND ItemType = 2 AND IsActive = '1' and IsMarkForDel = '0'";
        $res = $this->db->query($sql)->fetchAll();
        if (count($res) > 0) {
            foreach ($res as $row) {
                $longJsonDecode = $row['LongJsonInfo'];
                $longJson = json_decode($longJsonDecode, TRUE);
                $checkPlan = array();
                foreach ($longJson['PriceAdditional'] as $priceVal) {
                    if ($priceVal['CreateDate'] >= $checkInDate && $priceVal['CreateDate'] < $checkOutDate) {
                        $checkPlan['category']['Name'] = $priceVal['Title'];
                        $checkPlan['category']['HotelName'] = $longJson['Title'];
                        $checkPlan['category']['SUB'][$priceVal['EType']][$priceVal['MPType']]['CreateDate'][] = $priceVal['CreateDate'];
                        $checkPlan['category']['SUB'][$priceVal['EType']][$priceVal['MPType']]['Title'] = $priceVal['MPTitle'];
                    }
                }
               // echo "<pre>";print_r($checkPlan);exit;
                $datetime1 = new DateTime($checkInDate);
                $datetime2 = new DateTime($checkOutDate);
                $interval = $datetime1->diff($datetime2);
                $totalNight = (int) $interval->format('%a');
                $countval = array();
                $finalArray = array();
                foreach ($checkPlan['category'] as $checkhotelkey => $checkHotelPrice) {
                    foreach ($checkHotelPrice as $SUBkey => $SUBVal) {
                        $i = 0;
                        foreach ($SUBVal as $keyItem => $checkRNd) {
                            $countval[$i] = count($checkRNd["CreateDate"]);
                            if ($countval[$i] == $totalNight) {
                                $finalArray['category']['Name'] = $checkHotelPrice['Title'];
                                $finalArray['category']['HotelName'] = $longJson['Title'];
                                $finalArray['category']['SUB'][$SUBkey][$keyItem]['CreateDate'][] = $checkRNd['CreateDate'];
                                $finalArray['category']['SUB'][$SUBkey][$keyItem]['Title'] = $checkRNd['Title'];
                            }
                            $i++;
                        }
                    }
                }
                if (!empty($finalArray)) {
                    $response[] = array(
                        'destIndex' => $destIndex,
                        'refId' => $refId,
                        'GTXPkgId' => $row['GTXPkgId'],
                        'DestinationId' => $row['DestinationsId'],
                        'Title' => $longJson['Title'],
                        'Details' => $longJson['Details'],
                        'adults' => $adults,
                        'child' => $child,
                        'infent' => $infent,
                        'Stars' => $longJson['Stars'],
                        'ThumnailImg' => $longJson['ThumnailImg'],
                        'DetailImg' => $longJson['DetailImg'],
                        'PropertyType' => $longJson['PropertyType'],
                        'CheckinTime' => $longJson['CheckinTime'],
                        'Address' => $longJson['Address'],
                        'latitude' => $longJson['latitude'],
                        'longitude' => $longJson['longitude'],
                        'CitySysId' => $longJson['CitySysId'],
                        'ContSysId' => $longJson['ContSysId'],
                        'PinCode' => $longJson['PinCode'],
                        'Location' => $longJson['Location'],
                        'checkPlan' => base64_encode(json_encode($finalArray))
                    );
                }
            }
        }
        return $response;
    }

    function getHotelDetail($hotelRefSysId) {

        $select = $this->db->select()->from(array("t1" => "tb_travelplan_hotel"));
        $select->joinLeft(array('t2' => 'tb_tbb2c_packages_master'), 't1.HotelId = t2.GTXPkgId', array("LongJsonInfo"));
        $select->joinLeft(array('t3' => 'tb_master_geo_city'), 't1.DestinationId = t3.CityId', array("Title"));
        $select->joinLeft(array('t4' => 'tb_traavelplan_destination'), 't1.destsysId = t4.Id', array("StartDate", "EndDate"));
        $select->where("t1.ID IN(?)", $hotelRefSysId);
        $select->order("t4.Id ASC");
        $getZsontoArray = $this->db->fetchAll($select);
//       echo "<pre>";print_r($getZsontoArray);die;
        $selectionHotelByOption = array();
        $i = 0;
        $TSys = array();
        foreach ($getZsontoArray as $key => $val) {
            $TPSysId = (int) trim($val['TpsysId']);
           $ROOM_TYPE_DATA = unserialize(ROOM_TYPE_DATA);
          
            foreach ($ROOM_TYPE_DATA as $RTKey => $RTValue) {
                if($val['RoomTypeId'] == $RTKey){
                    $RoomTypeIdNew = $RTValue;
                }
            }
            $MEAL_TYPE = unserialize(MEAL_TYPE);
            foreach ($MEAL_TYPE as $MTKey => $MTValue) {
                if($val['MealPlanId'] == $MTKey){
                    $MealTypeIdNew = $MTValue;
                }
            }
            
            $keyVal = $val['DestinationId'] . "_" . $val['destSeq'];

            $selectionHotelByOption[$keyVal]['Title'] = $val['Title'];
            $longJson = json_decode($val['LongJsonInfo'], TRUE);
            
            $selectionHotelByOption[$keyVal]['Hotel'][$val['HotelId']]['Title'] = $longJson['Title'];
            $selectionHotelByOption[$keyVal]['Hotel'][$val['HotelId']]['RoomTypeId'][] = $RoomTypeIdNew;
            $selectionHotelByOption[$keyVal]['Hotel'][$val['HotelId']]['MealPlanId'][] = $MealTypeIdNew;
            $selectionHotelByOption[$keyVal]['StartDate'] = $val['StartDate'];
            $selectionHotelByOption[$keyVal]['EndDate'] = $val['EndDate'];
//            $selectionHotelByOption[$keyVal]['RoomTypeId'][] = $val['RoomTypeId'];
//            $selectionHotelByOption[$keyVal]['MealPlanId'][] = $val['MealPlanId'];
            $TSys['TPSysId'] = $TPSysId;
            // $selectionHotelByOption[$val['DestinationId']]['Hotel'][$val['HotelId']]['Price'][] = $getPrice;
            $i++;
        }

        return $selectionHotelByOption = array(
            "TPSysId" => $TSys,
            "selectionHotelByOption" => $selectionHotelByOption,
        );
    }

    function hotelPriceCalculation($hotelRefSysId, $TPSysId) {
        $selectionHotelByOption = array();
        $select = $this->db->select()->from(array("t1" => "tb_travelplan_hotel"));
        $select->joinLeft(array('t2' => 'tb_tbb2c_packages_master'), 't1.HotelId = t2.GTXPkgId', array("LongJsonInfo"));
        $select->joinLeft(array('t3' => 'tb_master_geo_city'), 't1.DestinationId = t3.CityId', array("Title"));
        $select->joinLeft(array('t4' => 'tb_traavelplan_destination'), 't1.destsysId = t4.Id');
        $select->where("t1.HotelId IN(?)", $hotelRefSysId);
        $select->where("t2.IsActive = ? ", 1);
        $select->where("t1.IsMarkForDel = ? ", '0');
        //$select->where("t1.HotelId IN(?)", $hotelRefSysId);
        $select->where("t1.TpsysId = ?", $TPSysId);
        $getZsontoArray = $this->db->fetchAll($select);
        //echo "<pre>";print_r($getZsontoArray);exit;
        foreach ($getZsontoArray as $key => $val) {
            $longJson = json_decode($val['LongJsonInfo'], TRUE);
            $checkInDate = $val['StartDate'];
            $checkOutDate = $val['EndDate'];
            $RoomTypeId = (int) trim($val['RoomTypeId']);
            $MealPlanId = (int) trim($val['MealPlanId']);
            $Adult = (int) trim($val['Adult']);
            $AdultExtraBed = (int) trim($val['AdultExtraBed']);
            $ChildWithBed = (int) trim($val['ChildWithBed']);
            $ChildWithOutBed = (int) trim($val['ChildWithOutBed']);
            $Infent = (int) trim($val['Infent']);
            $totalAdult = (int) $Adult + (int) $AdultExtraBed + (int) $ChildWithBed + (int) $ChildWithOutBed;
            $getPrice = array();
//            echo "<pre>";
//            print_r($longJson['PriceAdditional']);
//            exit;
            foreach ($longJson['PriceAdditional'] as $priceVal) {
                if ((trim($priceVal['EType']) == $RoomTypeId && trim($priceVal['MPType']) == $MealPlanId) && ($priceVal['CreateDate'] >= $checkInDate && $priceVal['CreateDate'] < $checkOutDate)) {
                    switch ($totalAdult) {
                        case 1 : {
                        if((int)$priceVal['NSOC'] > 0){
                                $getPrice[] = (float) $priceVal['NSOC'];
                        } else {
                              $getPrice[] = (float) $priceVal['NDOC'];
                        }
                                break;
                            }
                        case 2 : {
                                if ($Adult == 2) {
                                    $getPrice[] = (float) $priceVal['NDOC'];
                                } else if ($ChildWithOutBed == 2) {
                                    $getPrice[] = (float) $priceVal['NDOC'];
                                } else if (($Adult + $ChildWithOutBed) == 2) {
                                    $getPrice[] = (float) $priceVal['NDOC'];
                                } else {
                                    $getPrice[] = (float) $priceVal['NDOC'] + (float) $priceVal['NEBC'];
                                }
                                break;
                            }
                        case 3 : {
                                if ($Adult == 3) {
                                    $getPrice[] = (float) $priceVal['NDOC']+(float) $priceVal['NEWOBC'];
                                } else if ($ChildWithOutBed == 3) {
                                    $getPrice[] = (float) $priceVal['NDOC']+(float) $priceVal['NEWOBC'];
                                } else {
                                    $getPrice[] = (float) $priceVal['NDOC'] + (float) $priceVal['NEBC'];
                                }
                                break;
                            }
                        case 4 : {
                                if ($ChildWithOutBed + $Adult == 4) {
                                    $getPrice[] = (float) $priceVal['NDOC']+(float) $priceVal['NEWOBC']+(float) $priceVal['NEWOBC'];
                                } else {
                                    $getPrice[] = (float) $priceVal['NDOC']+(float) $priceVal['NEWOBC']+ (float) $priceVal['NEBC'];
                                }
                                break;
                            }
                        default : {
                                $getPrice[] = (float) $priceVal['NDOC']+(float) $priceVal['NEWOBC']+ (float) $priceVal['EWOBC'];
                                break;
                            }
                    }
                }
            }
        
            $selectionHotelByOption['Hotel'][$val['HotelId']]['Price'][] = $getPrice;
        }
        $finalval = 0;
        foreach ($selectionHotelByOption['Hotel'] as $priceVal) {
            foreach ($priceVal['Price'] as $hPrice) {
                for ($p = 0; $p < count($hPrice); $p++) {
                    $finalval = (float) $finalval + (float) $hPrice[$p];
                }
                //echo "<pre>";print_r($hPrice);exit; 
            }
            // echo "<pre>";print_r($priceVal);exit;
            //$finalval = 
        }
        return $finalval;
        // echo "<pre>";print_r($selectionHotelByOption);exit;
    }

    public function getTotalDays($TPSysId, $startDate) {
        $select = $this->db->select()->from(array("t1" => "tb_traavelplan_destination"), array("Sum(NoDay) as TotalNight"));

        $select->where("t1.TPSysId = ?", $TPSysId);
        //  echo $select;exit;
        $getZsontoArray = $this->db->fetchRow($select);

        $totalDays = (int) $getZsontoArray["TotalNight"] + 1;


        $datetime = new DateTime($startDate);

        $datetime->modify("+$totalDays days");
        $EndData = $datetime->format('Y-m-d');

        $returnArray = array(
            "startDate" => $startDate,
            "endDate" => $EndData,
            "totalDays" => $totalDays,
        );

        return $returnArray;
    }

    public function getItnaryDays($TPSysId) {
        $select = $this->db->select()->from(array("t1" => "tb_traavelplan"), array("EndCityId", "EndCity", "StartCity", "TotalAdult", "TotalChild", "TotalInfant", "StartDate","EndContId","StartCityId"));
        $select->joinLeft(array('t2' => 'tb_traavelplan_destination'), 't1.TPSysId = t2.TPSysId', array("CitySysId", "NoDay"));
        $select->joinLeft(array('t3' => 'tb_master_geo_city'), 't2.CitySysId = t3.CityId', array("Title", "ContSysId"));
        $select->where("t1.TPSysId = ?", $TPSysId);
        $select->order("t2.StartDate ASC");
        //  echo $select;exit;
        $getZsontoArray = $this->db->fetchAll($select);
//        echo "<pre>";print_r($getZsontoArray);die;
        $totalDays = 0;
        $arraycity = array();
        $i = 0;
        foreach ($getZsontoArray as $key => $val) {
            $days = (int) $val['NoDay'];
            $totalDays = $totalDays + $days;
            $arraycity['CitySysId'][$i]['days'] = (int) $days;
            $arraycity['CitySysId'][$i]['cityId'] = (int) $val['CitySysId'];
            $arraycity['CitySysId'][$i]['cityIdName'] = $val['Title'];
            $arraycity['CitySysId'][$i]['ContSysId'] = $val['ContSysId'];
            $arraycity['EndCityId'] = (int) $val['EndCityId'];
            $arraycity['EndContId'] = (int) $val['EndContId'];
            $arraycity['EndCity'] = $val['EndCity'];
            $arraycity['StartCity'] = $val['StartCity'];
            $arraycity['StartCityId'] = $val['StartCityId'];
            $arraycity['TotalAdult'] = (int) $val['TotalAdult'];
            $arraycity['TotalChild'] = (int) $val['TotalChild'];
            $arraycity['TotalInfant'] = (int) $val['TotalInfant'];
            $arraycity['StartDate'] = $val['StartDate'];

            $i++;
        }
        $returnArray = array();
        $d = 0;
        foreach ($arraycity['CitySysId'] as $valueCity) {
            for ($k = 0; $k < $valueCity['days']; $k++) {
                $returnArray[$d]['destSysId'] = $valueCity['cityId'];
                $returnArray[$d]['CityName'] = $valueCity['cityIdName'];
                $returnArray[$d]['ContSysId'] = $valueCity['ContSysId'];
                $d++;
            }
        }
        $returnArray[$d]['destSysId'] = $arraycity['EndCityId'];
        $arraycityEndCity = explode('(',$arraycity['EndCity']);
        $returnArray[$d]['CityName'] = $arraycityEndCity[0];
        $returnArray[$d]['ContSysId'] = $arraycity['EndContId'];

        $returnAr = array(
            "arraycity" => $arraycity,
            "returnArray" => $returnArray
        );


        return $returnAr;
    }

    public function getTravelPlanHistory($TPSysId) {
        $select = $this->db->select()->from(array("t1" => "tb_traavelplan"), array("EndCityId", "EndCity", "StartCity", "TotalAdult", "TotalChild", "TotalInfant", "StartDate", "StartCityId"));
        $select->where("t1.TPSysId = ?", $TPSysId);
        $getZsontoArray = $this->db->fetchAll($select);
        return $getZsontoArray;
        //$select->order("t.StartDate ASC");
    }

    public function getTransportRoute($TPSysId,$StartSysId,$totaldays,$endCityID){
        $select = $this->db->select()->from(array("t1" => "tb_transport_route"));
        $select->where("t1.StartCitySysId = ?", $StartSysId);
        $select->where("t1.EndCitySysId = ?", $endCityID);
        $select->where("t1.Noofnights = ?", $totaldays);
        $select->where("t1.IsActive = ?", 1);
        $select->where("t1.IsMarkForDel = '0'");
       // echo $select;
        $getZsontoArray = $this->db->fetchAll($select);
        $select1 = $this->db->select()->from(array("t2" => "tb_traavelplan_destination"),array("CitySysId"));
        $select1->where("t2.TPSysId = ?", $TPSysId);
        $CitySysIdArray = $this->db->fetchAll($select1);
//        echo "<pre>";print_r($CitySysIdArray);
//        
//        echo "<br>";
//        
//        echo "<pre>";print_r($getZsontoArray);exit;
        
        $arrCitySysId = array();
       // $arrCitySysId[0] = 0;
        foreach ($CitySysIdArray as $cityIdKey => $cityIdValue) {
            $arrCitySysId[] = $cityIdValue['CitySysId'];
        }
      // echo "<pre>";print_r($arrCitySysId);
      // echo "<br>";
         $finalArray = array(); 
        if(!empty($getZsontoArray)) {
            
          
            foreach($getZsontoArray as $key => $routeName){
                 $category = json_decode($routeName['category'], TRUE);
                 $cityIncludes = json_decode($routeName['cityIncludes'], TRUE);
            //   echo "<pre>";print_r($category); 
              // echo "<br>";
                    $arrdesId = array();
                    $testResult = 0;
                    
                    foreach ($cityIncludes as $ciKey => $ciValue) {
                        
                         if($ciValue['DestSysId'] == $arrCitySysId[$ciKey]){
                             $testResult++;
                         }
                    }
                   
                    
                 $i = 0;
                    if((count($cityIncludes) == count($arrCitySysId)) && (count($cityIncludes) == $testResult)) {
                        
                    foreach ($category as $catKey => $catValue) {
                    
                     $finalArray[$i] = [
                         'transport_route_id' => $routeName['transport_route_id'],
                         'RouteSysId' => $routeName['RouteSysId'],
                         'RouteName' => $routeName['RouteName'],
                         'SupplierSysId' => $routeName['SupplierSysId'],
                         'Title' => $routeName['Title'],
                         'Description' => $routeName['Description'],
                         'StartCitySysId' => $routeName['StartCitySysId'],
                         'Noofnights' => $routeName['Noofnights'],
                         'CityName' => $routeName['CityName'],
                         'EndCityName' => $routeName['EndCityName'],
                         'RouteVechSysId' => $catValue['RouteVechSysId'],
                         'VehicleSysId' => $catValue['VehicleSysId'],
                         'VehicleTypeSysId' => $catValue['VehicleTypeSysId'],
                         'Cost' => $catValue['Cost'],
                         'Capacity' => $catValue['Capacity'],
                         'CurrencyType' => $catValue['CurrencyType'],
                         'SupplierName' => $catValue['SupplierName'],
                         'VehName' => $catValue['VehName'],
                         'VehicleTypeName' => $catValue['VehicleTypeName'],
                         'CurrencySymbol' => $catValue['CurrencySymbol'],
                         'cityIncludes' => $cityIncludes,
                        
                     ];
                     $i++;
                      
                 }
                    
                 } 
                 
                 

            }
           // exit;
           // echo "<pre>";print_r($finalArray);exit;
        }

        return $finalArray;
        //$select->order("t2.StartDate ASC");
    }

    public function getTncMasterData() {
        $select = $this->db->select()->from(array("t1" => "tb_traavelplan_tnc_master"), '*');
        
        $tncDataArray = $this->db->fetchAll($select);
        return $tncDataArray;
        //$select->order("t.StartDate ASC");
    }
}