<?php

class Gtxwebservices_Model_HotelProposal extends Zend_Db_Table_Abstract
{

    protected $_dbObj = '';
    function __construct()
    {
        $this->_dbObj = Zend_Db_Table::getDefaultAdapter();
    }

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

    public function HotelVoucher($post)
    {
        try {
            $customhelper = Zend_Controller_Action_HelperBroker::getStaticHelper('Custom');
            $MasterTPSysId = isset($post['MasterTPSysId']) ? (int) $post['MasterTPSysId'] : 0;
            $TPSysId = isset($post['TPSysId']) ? (int) $post['TPSysId'] : 0;
            $TrxType = 'H';
            $select = $this->_dbObj->select();
            $travelplanarray = array('TPSysId', 'MasterTPSysId', 'IsCardChrgToCustomer', 'StatusType', 'PaymentStatus', 'BookingStatus as CurrentStatus', 'ExchangeCurrencyRate', 'SupplierSysId', 'LeadSourceSysId', 'AgencySysId', 'AgentSysId', 'ChangeRequestId', 'IsB2BProposal', 'CustomerSysId', 'RoomInfoJson', 'AdditionalReq', 'Cities', 'Countries', 'Price', 'NetPrice', 'CreatorSysId');
            $customerarray = array('CustomerSysId', 'EmailId', 'Contacts', 'FirstName', 'Title as Salutation', 'LastName');
            $travel_itenary = array('TPIntSysId as itenaryID');
            $travel_accom = array(
                'TPIntSysId',
                'AccoSysId',
                'APIBookingid',
                'BookingRefNo',
                'ConfirmationNo',
                'Title as hotelCityTitle',
                'FullFillmentInfo',
                'FromDate as CheckInDate',
                'ToDate as CheckOutDate',
                'AccoSysId',
                'AdultPax',
                'TotalNights',
                'ChildPax',
                'StarRating',
                'ChildsAge',
                'XrefCityId as hotelXrefCityId',
                'VersionId',
                'XServiceTaxAmount',
                'GTXServiceTaxAmount',
                'AgentServiceTaxAmount',
                'AgentsCustomServiceTaxAmount',
                'NetCost',
                'TotalCost',
                'MarkUp as hotelMarkUp',
                'GTXMarkup',
                'AgencyMarkUp',
                'SourceSysId as ICSourceSysId',
                'Taxes',
                'TotalPax',
                'Discounts',
                'XrefBookingId',
                'SpecialRequest',
                'ErrorMessage',
                'CreateDate as bookingDate'
            );
            $accom_details = array(
                'Title as hotelName',
                'ShortName',
                'ResultIndex',
                'TokenId',
                'TraceId',
                'MinPublishedFare',
                'Address as hotelAddress',
                'Rating as AccomRating',
                'Stars',
                'PinCode',
                'Location',
                'GTXComment',
                'GeoLat',
                'GeoLong',
                'Brief',
                'DetailImg as HotelPicture',
                'PrimaryContact as hotelPrimaryContact'
            );
            $arrAgencyCustomerTrx = array('XRefTrxNo as CTRXXRefTrxNo', 'TrxSysId');
            $arrTBTrx = array('CurrencyType');
            $arrAgency = array('AgencySysId', 'DisplayName', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo', 'countrycode', 'IsB2bSiteurl', 'ServiceTaxNo as AgencyGst', 'countrycode as Mastercountrycode');
            $arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
            $LeadSource = array('Title AS LeadSource', 'LeadSourceSysId');
            $arrCurrency = array('Symbol AS CurrencySymbol', 'Title as CurrencyTitle');
            $arrGC = array('Title AS CityTitle', 'Country');
            $arrHGC = array('Title AS HotelCountry');

            $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan"), $travelplanarray);
            $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_Itenary"), "tb1.TPSysId = tb2.TPSysId", $travel_itenary);
            $select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan_Accom"), "tb2.TPIntSysId = tb3.TPIntSysId", $travel_accom);
            $select->joinLeft(array('tb4' => "TB_Agency_Customer"), "tb1.CustomerSysId = tb4.CustomerSysId", $customerarray);
            $select->joinLeft(array('CU' => "TB_Master_Currency"), "CU.CurrencyType = tb3.CurrencyType", $arrCurrency);
            $select->joinLeft(array('tb5' => "TB_IC_Accomdation_API"), "tb5.AccomSysId = tb3.AccoSysId", $accom_details);
            $select->joinLeft(array('HGC' => "TB_Master_Geo_Country"), "HGC.ContId = tb5.ContSysId", $arrHGC);
            $select->joinLeft(array('tb7' => "TB_Agency_Customer_Trx"), "tb7.TPSysId = tb1.TPSysId", $arrAgencyCustomerTrx);
            $select->joinLeft(array('tb8' => "TB_Trx"), "tb8.TrxSysId = tb7.TrxSysId", $arrTBTrx);
            $select->joinLeft(array('tb9' => "TB_Agency"), "tb1.AgencySysId = tb9.AgencySysId", $arrAgency);
            $select->joinLeft(array('tb10' => "TB_Agency_Addresses"), "tb9.AgencySysId = tb10.AgencySysId", $arrAgencyAddress);
            $select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = tb9.CitySysId", $arrGC);
            $select->joinLeft(array('TAU' => "TB_Agency_User"), "TAU.UserSysId = tb4.CreatedByUserSysId", array('FirstName as agentName', 'EmailId as AgentEmailId', 'AgencyName as AgentAgencyName', 'UserPicPath', 'AgencySysId as B2BAgencySysId', 'UserSysId'));
            $select->joinLeft(array('TAUU' => "TB_Agency_User"), "tb9.PrimaryUserSysId = TAUU.UserSysId", array('ContactNo2', 'countrycode2', 'SecondaryEmail'));
            $select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = tb1.LeadSourceSysId ', $LeadSource);
            $select->joinLeft(array('INV' => "TB_Agency_Miscellaneous"), "tb1.TPSysId = INV.TPSysId", array('InvoiceNumber', 'CreateDate as InvoiceDate'));


            $select->where('tb1.TPSysId = ?', $TPSysId);
            $select->where('tb8.TrxType = ?', $TrxType);
            // $select->where('tb8.TrxStatus = ?', 1);
            $result = $this->_dbObj->fetchRow($select);

            // echo "<pre>";
            // print_r($result);
            // exit;
            if ($result) {
                $InvoiceDate = (array)$result['InvoiceDate'];
                $CheckInDate = (array)$result['CheckInDate'];
                $CheckOutDate = (array)$result['CheckOutDate'];
                $bookingDate = (array)$result['bookingDate'];
                $instruction = (isset($result['SpecialRequest']) && !empty($result['SpecialRequest'])) ? json_decode($result['SpecialRequest'], 1) : [];
                $facility = (isset($result['FullFillmentInfo']) && !empty($result['FullFillmentInfo'])) ? json_decode($result['FullFillmentInfo'], 1) : [];

                $VersionId = $result['VersionId'];
                $result['CheckInDate'] = date('Y-m-d', strtotime($CheckInDate['date']));
                $result['CheckOutDate'] = date('Y-m-d', strtotime($CheckOutDate['date']));
                $result['CreateDate'] = $bookingDate['date'];
                $result['InvoiceDate'] = $InvoiceDate['date'];
                $result['ShortName'] = trim($result['ShortName']);
                $result['Country'] = trim($result['Country']);
                $result['instruction'] = $instruction;
                $result['facility'] = $facility;
                $result['BookingStatus'] = $customhelper->GetTravelPlanStatusName(2, $result['StatusType']);
                unset($result['SpecialRequest']);
                unset($result['FullFillmentInfo']);
                unset($result['RoomInfoJson']);
                $select2 = $this->_dbObj->select();
                $AddonServices = array('AdultCount as totaladult', 'ChildCount as totalchild', 'Comment', 'CurrencyType', 'Markup as servicefee', 'Cost as amount', 'CreateDate as fromdate', 'UpdateDate as todate');
                $select2->from(array('tb1' => "TB_Agency_Customer_TravelPlan_AddonServices"), $AddonServices);
                $select2->where('tb1.TPSysId = ?', $TPSysId);
                $select2->where('tb1.PlanType = ?', 12);
                $Cancellation = $this->_dbObj->fetchAll($select2);
                $CancellationArray = [];
                if ($Cancellation) {
                    foreach ($Cancellation as $key => $value) {
                        $fromdate = (array)$value['fromdate'];
                        $todate = (array)$value['todate'];
                        $Comment = (isset($value['Comment']) && !empty($value['Comment'])) ? json_decode($value['Comment'], 1) : [];

                        $CancellationArray[$key] = $value;
                        $CancellationArray[$key]['fromdate'] = date('Y-m-d H:i:s', strtotime($fromdate['date']));
                        $CancellationArray[$key]['todate'] = date('Y-m-d H:i:s', strtotime($todate['date']));
                        $CancellationArray[$key]['Currency'] = isset($Comment['Currency']) ? $Comment['Currency'] : '';
                        $CancellationArray[$key]['LastCancellationDate'] = isset($Comment['LastCancellationDate']) ? $Comment['LastCancellationDate'] : '';
                        $CancellationArray[$key]['Isfullrefundallowed'] = isset($Comment['Isfullrefundallowed']) ? $Comment['Isfullrefundallowed'] : false;
                    }
                }

                $select3 = $this->_dbObj->select();
                // 'CONVERT(varchar(11), LastCancellationDate, 111) as LastCancellationDate',
                $Accom_Room = array(
                    'VersionId',
                    'RoomTypeName',
                    'RoomIndex',
                    'RoomTypeCode',
                    'RatePlanName',
                    'MealPlanType',
                    'RatePlan',
                    'BedTypeCode',
                    'Currency',
                    'AdultPax',
                    'ChildPax',
                    'InfantPax',
                    'CurrencyType',
                    'NetCost',
                    'RoomPrice',
                    'Discounts',
                    'TotalPrice',
                    'Taxes',
                    'AgencyMarkUp',
                    'GSTOnAgencyMarkUp',
                    'AgentMarkup',
                    'GSTOnAgentMarkup',
                    'GTXMarkup',
                    'Commission',
                    'DiscountMsg',
                    'LastCancellationDate',
                    'LastVoucherDate',
                    'IsMarkForDel'
                );
                $select3->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Accom_Room"), $Accom_Room);
                $select3->where('tb1.TPSysId = ?', $TPSysId);
                $select3->where('tb1.TPAccomSysId = ?', $VersionId);
                $select3->where('tb1.IsMarkForDel = ?', 0);
                $Accom_Room = $this->_dbObj->fetchAll($select3);
                $Accom_RoomArray = [];
                if ($Accom_Room) {
                    foreach ($Accom_Room as $key => $value) {
                        $select4 = $this->_dbObj->select();
                        $select4->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Pax"), ['CustomerSysId', 'panno', 'MemberSysId', 'IsLeadPax', 'paxType', 'DOB', 'GstNumber', 'GstCompany', 'GstEmail', 'GstPhone', 'GstAddress', 'FirstName', 'LastName', 'Salutation', 'passportnoexpiry', 'passporIssue', 'PassportNationality', 'passportno']);
                        $select4->where('tb1.FareBreakdownSysId = ?', $value['VersionId']);
                        $select4->where('tb1.TPSysId = ?', $TPSysId);
                        $Customer = $this->_dbObj->fetchAll($select4);
                        $LastCancellationDate = (array)$value['LastCancellationDate'];
                        $LastVoucherDate = (array)$value['LastVoucherDate'];
                        $Accom_RoomArray[$key] = $value;
                        $Accom_RoomArray[$key]['LastCancellationDate'] = date('Y-m-d H:i:s', strtotime($LastCancellationDate['date']));
                        $Accom_RoomArray[$key]['LastVoucherDate'] = $LastVoucherDate['date'];
                        $ARR_SALUTIONTBO = unserialize(ARR_SALUTIONTBO);
                        $ARR_SALUTION = unserialize(ARR_SALUTION_TJ);
                        $ARR_SALUTION_CHILD = unserialize(ARR_SALUTION_CHILD);
                        $CustomerArray = [];
                        if ($Customer) {
                            foreach ($Customer as $k => $pax) {
                                $paxtype = $pax['paxType'];
                                $Title = (isset($pax['Salutation']) && !empty($pax['Salutation'])) ? $pax['Salutation'] : $pax['Title'];
                                if ($paxtype == 1) {
                                    $paxtypeName = 'AD';
                                    $Salutation = $ARR_SALUTIONTBO[$Title];
                                } else if ($paxtype == 2) {
                                    $paxtypeName = 'CH';
                                    $Salutation = $ARR_SALUTIONTBO[$Title];
                                }
                                $CustomerArray[$k] = $pax;
                                $CustomerArray[$k]['Salutation'] = $Salutation;
                                $CustomerArray[$k]['paxtypeName'] = $paxtypeName;
                            }
                        }
                        if ($Customer) {
                            $Accom_RoomArray[$key]['customer'] = $CustomerArray;
                        }
                    }
                }


                $select5 = $this->_dbObj->select();
                $select5->from(array('tb1' => "TB_IC_Accomdation_Images"), ['Details as ImageURL']);
                $select5->where('tb1.AccomSysId = ?', $result['AccoSysId']);
                $Images = $this->_dbObj->fetchAll($select5);
                $result['images'] = $Images;
                return ['HotelData' => $result, 'cancellation' => $CancellationArray, 'accom_room' => $Accom_RoomArray];
            } else {
                return ['HotelData' => [], 'cancellation' => [], 'accom_room' => []];
            }
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }
    public function HotelVoucherOffline($post)
    {
        try {
            $customhelper = Zend_Controller_Action_HelperBroker::getStaticHelper('Custom');
            $MasterTPSysId = isset($post['MasterTPSysId']) ? (int) $post['MasterTPSysId'] : 0;
            $TPSysId = isset($post['TPSysId']) ? (int) $post['TPSysId'] : 0;
            $TrxType = 'H';
            $select = $this->_dbObj->select();
            $travelplanarray = array('TPSysId', 'MasterTPSysId', 'IsCardChrgToCustomer', 'StatusType', 'PaymentStatus', 'BookingStatus as CurrentStatus', 'ExchangeCurrencyRate', 'SupplierSysId', 'LeadSourceSysId', 'AgencySysId', 'AgentSysId', 'ChangeRequestId', 'IsB2BProposal', 'CustomerSysId', 'RoomInfoJson', 'AdditionalReq', 'Cities', 'Countries', 'Price', 'NetPrice', 'CreatorSysId');
            $customerarray = array('CustomerSysId', 'EmailId', 'Contacts', 'FirstName', 'Title as Salutation', 'LastName');
            $travel_itenary = array('TPIntSysId as itenaryID');
            $travel_accom = array(
                'TPIntSysId',
                'AccoSysId',
                'APIBookingid',
                'BookingRefNo',
                'ConfirmationNo',
                'Title as hotelCityTitle',
                'FullFillmentInfo',
                'FromDate as CheckInDate',
                'ToDate as CheckOutDate',
                'AccoSysId',
                'AdultPax',
                'TotalNights',
                'ChildPax',
                'StarRating',
                'ChildsAge',
                'XrefCityId as hotelXrefCityId',
                'VersionId',
                'XServiceTaxAmount',
                'GTXServiceTaxAmount',
                'AgentServiceTaxAmount',
                'AgentsCustomServiceTaxAmount',
                'NetCost',
                'TotalCost',
                'MarkUp as hotelMarkUp',
                'GTXMarkup',
                'AgencyMarkUp',
                'SourceSysId as ICSourceSysId',
                'Taxes',
                'TotalPax',
                'Discounts',
                'XrefBookingId',
                'SpecialRequest',
                'ErrorMessage',
                'AdjustmentsAmount',
                'CreateDate as bookingDate'
            );
            $accom_details = array(
                'Title as hotelName',
                'ShortName',
                'ResultIndex',
                'TokenId',
                'TraceId',
                'MinPublishedFare',
                'Address as hotelAddress',
                'Rating as AccomRating',
                'Stars',
                'PinCode',
                'Location',
                'GTXComment',
                'GeoLat',
                'GeoLong',
                'Brief',
                'DetailImg as HotelPicture',
                'PrimaryContact as hotelPrimaryContact'
            );
            $arrAgencyCustomerTrx = array('XRefTrxNo as CTRXXRefTrxNo', 'TrxSysId');
            $arrTBTrx = array('CurrencyType');
            $arrAgency = array('AgencySysId', 'DisplayName', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo', 'countrycode', 'IsB2bSiteurl', 'ServiceTaxNo as AgencyGst', 'countrycode as Mastercountrycode');
            $arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
            $LeadSource = array('Title AS LeadSource', 'LeadSourceSysId');
            $arrCurrency = array('Symbol AS CurrencySymbol', 'Title as CurrencyTitle');
            $arrGC = array('Title AS CityTitle', 'Country');
            $arrHGC = array('Title AS HotelCountry');

            $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan"), $travelplanarray);
            $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_Itenary"), "tb1.TPSysId = tb2.TPSysId", $travel_itenary);
            $select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan_Accom"), "tb2.TPIntSysId = tb3.TPIntSysId", $travel_accom);
            $select->joinLeft(array('tb4' => "TB_Agency_Customer"), "tb1.CustomerSysId = tb4.CustomerSysId", $customerarray);
            $select->joinLeft(array('CU' => "TB_Master_Currency"), "CU.CurrencyType = tb3.CurrencyType", $arrCurrency);
            $select->joinLeft(array('tb5' => "TB_IC_Accomdation_API"), "tb5.AccomSysId = tb3.AccoSysId", $accom_details);
            $select->joinLeft(array('HGC' => "TB_Master_Geo_Country"), "HGC.ContId = tb5.ContSysId", $arrHGC);
            $select->joinLeft(array('tb7' => "TB_Agency_Customer_Trx"), "tb7.TPSysId = tb1.TPSysId", $arrAgencyCustomerTrx);
            $select->joinLeft(array('tb8' => "TB_Trx"), "tb8.TrxSysId = tb7.TrxSysId", $arrTBTrx);
            $select->joinLeft(array('tb9' => "TB_Agency"), "tb1.AgencySysId = tb9.AgencySysId", $arrAgency);
            $select->joinLeft(array('tb10' => "TB_Agency_Addresses"), "tb9.AgencySysId = tb10.AgencySysId", $arrAgencyAddress);
            $select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = tb9.CitySysId", $arrGC);
            $select->joinLeft(array('TAU' => "TB_Agency_User"), "TAU.UserSysId = tb4.CreatedByUserSysId", array('FirstName as agentName', 'EmailId as AgentEmailId', 'AgencyName as AgentAgencyName', 'UserPicPath', 'AgencySysId as B2BAgencySysId', 'UserSysId'));
            $select->joinLeft(array('TAUU' => "TB_Agency_User"), "tb9.PrimaryUserSysId = TAUU.UserSysId", array('ContactNo2', 'countrycode2', 'SecondaryEmail'));
            $select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = tb1.LeadSourceSysId ', $LeadSource);
            $select->joinLeft(array('INV' => "TB_Agency_Miscellaneous"), "tb1.TPSysId = INV.TPSysId", array('InvoiceNumber', 'CreateDate as InvoiceDate'));


            $select->where('tb1.TPSysId = ?', $TPSysId);
            //$select->where('tb8.TrxType = ?', $TrxType);CurrencyType
            // $select->where('tb8.TrxStatus = ?', 1);
            $result = $this->_dbObj->fetchRow($select);

            // echo '<pre>';
            // print_r($result);
            // die;

            if ($result) {
                $InvoiceDate = (array)$result['InvoiceDate'];
                $CheckInDate = (array)$result['CheckInDate'];
                $CheckOutDate = (array)$result['CheckOutDate'];
                $bookingDate = (array)$result['bookingDate'];
                $instruction = (isset($result['SpecialRequest']) && !empty($result['SpecialRequest'])) ? json_decode($result['SpecialRequest'], 1) : [];
                $facility = (isset($result['FullFillmentInfo']) && !empty($result['FullFillmentInfo'])) ? json_decode($result['FullFillmentInfo'], 1) : [];

                $VersionId = $result['VersionId'];
                $result['CheckInDate'] = date('Y-m-d', strtotime($CheckInDate['date']));
                $result['CheckOutDate'] = date('Y-m-d', strtotime($CheckOutDate['date']));
                $result['CreateDate'] = $bookingDate['date'];
                $result['InvoiceDate'] = $InvoiceDate['date'];
                $result['ShortName'] = trim($result['ShortName']);
                $result['Country'] = trim($result['Country']);
                $result['instruction'] = $instruction;
                $result['facility'] = $facility;
                $result['BookingStatus'] = $customhelper->GetTravelPlanStatusName(2, $result['StatusType']);
                unset($result['SpecialRequest']);
                unset($result['FullFillmentInfo']);
                unset($result['RoomInfoJson']);


                $select2 = $this->_dbObj->select();
                $AddonServices = array('AdultCount as totaladult', 'ChildCount as totalchild', 'Comment', 'CurrencyType', 'Markup as servicefee', 'Cost as amount', 'CreateDate as fromdate', 'UpdateDate as todate');
                $select2->from(array('tb1' => "TB_Agency_Customer_TravelPlan_AddonServices"), $AddonServices);
                $select2->where('tb1.TPSysId = ?', $TPSysId);
                $select2->where('tb1.PlanType = ?', 12);
                $Cancellation = $this->_dbObj->fetchAll($select2);

                $CancellationArray = [];
                if ($Cancellation) {
                    foreach ($Cancellation as $key => $value) {
                        $fromdate = (array)$value['fromdate'];
                        $todate = (array)$value['todate'];
                        $Comment = (isset($value['Comment']) && !empty($value['Comment'])) ? json_decode($value['Comment'], 1) : [];

                        $CancellationArray[$key] = $value;
                        $CancellationArray[$key]['fromdate'] = date('Y-m-d H:i:s', strtotime($fromdate['date']));
                        $CancellationArray[$key]['todate'] = date('Y-m-d H:i:s', strtotime($todate['date']));
                        $CancellationArray[$key]['Currency'] = isset($Comment['Currency']) ? $Comment['Currency'] : '';
                        $CancellationArray[$key]['LastCancellationDate'] = isset($Comment['LastCancellationDate']) ? $Comment['LastCancellationDate'] : '';
                        $CancellationArray[$key]['Isfullrefundallowed'] = isset($Comment['Isfullrefundallowed']) ? $Comment['Isfullrefundallowed'] : false;
                    }
                }


                $select3 = $this->_dbObj->select();
                // 'CONVERT(varchar(11), LastCancellationDate, 111) as LastCancellationDate',
                $Accom_Room = array(
                    'VersionId',
                    'RoomTypeName',
                    'RoomIndex',
                    'RoomTypeCode',
                    'RatePlanName',
                    'MealPlanType',
                    'RatePlan',
                    'BedTypeCode',
                    'Currency',
                    'AdultPax',
                    'ChildPax',
                    'InfantPax',
                    'CurrencyType',
                    'NetCost',
                    'RoomPrice',
                    'Discounts',
                    'TotalPrice',
                    'Taxes',
                    'AgencyMarkUp',
                    'GSTOnAgencyMarkUp',
                    'AgentMarkup',
                    'GSTOnAgentMarkup',
                    'GTXMarkup',
                    'Commission',
                    'DiscountMsg',
                    'LastCancellationDate',
                    'LastVoucherDate',
                    'IsMarkForDel'
                );
                $select3->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Accom_Room"), $Accom_Room);
                $select3->where('tb1.TPSysId = ?', $TPSysId);
                $select3->where('tb1.TPAccomSysId = ?', $VersionId);

                $Accom_Room = $this->_dbObj->fetchAll($select3);
                // echo '<pre>';
                // print_r($Accom_Room);
                // die;
                $Accom_RoomArray = [];
                $RoomOptionArray = [];
                if ($Accom_Room) {
                    foreach ($Accom_Room as $key => $value) {
                        $select4 = $this->_dbObj->select();
                        $select4->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Pax"), ['TPPaxSysId', 'panno', 'CustomerSysId', 'MemberSysId', 'IsLeadPax', 'paxType', 'DOB', 'GstNumber', 'GstCompany', 'GstEmail', 'GstPhone', 'GstAddress', 'FirstName', 'LastName', 'Salutation', 'passportnoexpiry', 'passporIssue', 'PassportNationality', 'passportno']);
                        $select4->where('tb1.FareBreakdownSysId = ?', $value['VersionId']);
                        $select4->where('tb1.TPSysId = ?', $TPSysId);
                        $Customer = $this->_dbObj->fetchAll($select4);
                        $LastCancellationDate = (array)$value['LastCancellationDate'];
                        $LastVoucherDate = (array)$value['LastVoucherDate'];
                        $RoomOptionArray[$value['RoomIndex']] = $value['RoomTypeName'] . ' (' . $value['MealPlanType'] . ')';
                        $Accom_RoomArray[$key] = $value;
                        $Accom_RoomArray[$key]['LastCancellationDate'] = date('Y-m-d H:i:s', strtotime($LastCancellationDate['date']));
                        $Accom_RoomArray[$key]['LastVoucherDate'] = $LastVoucherDate['date'];
                        $ARR_SALUTIONTBO = unserialize(ARR_SALUTIONTBO);
                        $ARR_SALUTION = unserialize(ARR_SALUTION_TJ);
                        $ARR_SALUTION_CHILD = unserialize(ARR_SALUTION_CHILD);
                        $CustomerArray = [];
                        if ($Customer) {
                            foreach ($Customer as $k => $pax) {
                                $paxtype = $pax['paxType'];
                                $Title = (isset($pax['Salutation']) && !empty($pax['Salutation'])) ? $pax['Salutation'] : $pax['Title'];
                                if ($paxtype == 1) {
                                    $paxtypeName = 'AD';
                                    $Salutation = !empty($ARR_SALUTIONTBO[$Title]) ? $ARR_SALUTIONTBO[$Title] : '';
                                } else if ($paxtype == 2) {
                                    $paxtypeName = 'CH';
                                    $Salutation = !empty($ARR_SALUTIONTBO[$Title]) ? $ARR_SALUTIONTBO[$Title] : '';
                                }
                                $CustomerArray[$k] = $pax;
                                $CustomerArray[$k]['Salutation'] = $Salutation;
                                $CustomerArray[$k]['paxtypeName'] = $paxtypeName;
                            }
                        }
                        if ($Customer) {
                            $Accom_RoomArray[$key]['customer'] = $CustomerArray;
                        }
                    }
                }


                $select5 = $this->_dbObj->select();
                $select5->from(array('tb1' => "TB_IC_Accomdation_Images"), ['Details as ImageURL']);
                $select5->where('tb1.AccomSysId = ?', $result['AccoSysId']);
                $Images = $this->_dbObj->fetchAll($select5);
                $result['images'] = $Images;
                return ['HotelData' => $result, 'cancellation' => $CancellationArray, 'accom_room' => $Accom_RoomArray, 'RoomOptionArray' => $RoomOptionArray];
            } else {
                return ['HotelData' => [], 'cancellation' => [], 'accom_room' => []];
            }
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }
}