<?php

/*
 * Catabatic Technology Pvt. Ltd.
 * File Name :TblCRMCustomer.php
 * File Description :Manage to Customer Operation
 * Created By : Shobhit Srivastav 
 * Created Date: 03-Aug-2016
 */

class Travel_Model_Package_PackageModel
{

    protected $_dbObj;
    protected $_pkgmodelObj;

    public function __construct()
    {

        $this->_pkgmodelObj = new Travel_Model_TblPackageModel();
        $this->_dbObj = Zend_Db_Table::getDefaultAdapter();
    }
    public function addTravelPlan(array $data)
    {
        $this->_pkgmodelObj->insert($data);
        $lastID = $this->_dbObj->lastInsertId();
        return $lastID;
    }

    public function GetTravelPlan($tpsysID)
    {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan"));
        $select->where('tbl.TPSysId = ?', $tpsysID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
    public function GetTravelPlanAllCat($tpsysID)
    {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan"));
        $select->where('tbl.TPSysId = ?', $tpsysID);
        $result = $this->_dbObj->fetchAll($select);
        if (!empty($result)) {
            $msaterTpSysId = isset($result[0]['MasterRefId']) ? $result[0]['MasterRefId'] : 0;
            if ($msaterTpSysId > 0) {
                $select = $this->_dbObj->select();
                $select->from(array('tbl' => "TB_TravelPlan"));
                $select->where('tbl.TPSysId = ?', $msaterTpSysId);
                $select->orWhere('tbl.MASTERREFID = ?', $msaterTpSysId);
                $select->order('tbl.TPSysId');
                $result = $this->_dbObj->fetchAll($select);
            }
        }
        return $result;
    }
    public function GetTravelPlanItenary($tpsysID)
    {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary"));
        $select->where('tbl.TPSysId = ?', $tpsysID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetTravelPlanItenaryAccom($tpsysID)
    {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary_Accom"));
        $select->where('tbl.TPSysId = ?', $tpsysID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetItenaryWiseSelectedHotel($tpinttSysID)
    {
        //echo $tpinttSysID;
        $travelplanitenary = array('*');
        $icaccomodation = array('*');
        $country = array('Title as CountryName');
        $cityname = array('Title as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary_Accom"), $travelplanitenary);
        $select->joinLeft(array('tb2' => "TB_IC_Accomdation"), "tbl.AccoSysId = tb2.AccomSysId", $icaccomodation);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb2.ContSysId = tb3.ContId", $country);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb2.CitySysId = tb4.CityId", $cityname);
        $select->joinLeft(array('tb5' => "TB_Master_RoomType"), "tbl.RoomType = tb5.RoomType", array('Title as RoomTitle'));
        $select->joinLeft(array('tb6' => "TB_Master_Accom_MealType"), "tbl.MealPlanType = tb6.MealTypeId", array('Title as RoomMealType'));
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.TPIntSysId = ?', $tpinttSysID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetItenaryWiseSelectedCustomerHotel($tpinttSysID, $seq = null)
    {
        //echo $tpinttSysID;
        $travelplanitenary = array('*');
        $icaccomodation = array('*');
        $country = array('Title as CountryName');
        $cityname = array('Title as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Accom"), $travelplanitenary);
        $select->joinLeft(array('tb2' => "TB_IC_Accomdation"), "tbl.AccoSysId = tb2.AccomSysId", $icaccomodation);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tbl.XRefCityId = tb4.CityId", $cityname);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb4.ContSysId = tb3.ContId", $country);
        $select->joinLeft(array('tb5' => "TB_Master_RoomType"), "tbl.RoomType = tb5.RoomType", array('Title as RoomTitle'));
        $select->joinLeft(array('tb6' => "TB_Master_Accom_MealType"), "tbl.MealPlanType = tb6.MealTypeId", array('Title as RoomMealType'));
        $select->where('tbl.TPIntSysId = ?', $tpinttSysID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        if (!empty($seq)) {
            $select->where('tbl.SeqId = ?', $seq);
        }
        $select->order('tbl.VersionId ASC');
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetItenaryWiseSelectedHotelBySeq($tpinttSysID, $seq)
    {
        //echo $tpinttSysID;
        $travelplanitenary = array('*');
        $icaccomodation = array('*');
        $country = array('Title as CountryName');
        $cityname = array('Title as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary_Accom"), $travelplanitenary);
        $select->joinLeft(array('tb2' => "TB_IC_Accomdation"), "tbl.AccoSysId = tb2.AccomSysId", $icaccomodation);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb2.ContSysId = tb3.ContId", $country);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb2.CitySysId = tb4.CityId", $cityname);
        $select->where('tbl.TPIntSysId = ?', $tpinttSysID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.SeqId = ?', $seq);
        //echo $select;
        //die;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetItenaryWiseSelectedHotelCustBySeq($tpinttSysID, $seq)
    {
        //echo $tpinttSysID;
        $travelplanitenary = array('*');
        $icaccomodation = array('*');
        $country = array('Title as CountryName');
        $cityname = array('Title as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Accom"), $travelplanitenary);
        $select->joinLeft(array('tb2' => "TB_IC_Accomdation"), "tbl.AccoSysId = tb2.AccomSysId", $icaccomodation);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb2.ContSysId = tb3.ContId", $country);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb2.CitySysId = tb4.CityId", $cityname);
        $select->where('tbl.TPIntSysId = ?', $tpinttSysID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.SeqId = ?', $seq);
        //  echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }


    public function getSelectedHotel($tpintsysID)
    {

        $travelplanitenary = array('*');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary_Accom"), $travelplanitenary);
        $select->where('tbl.TPIntSysId = ?', $tpintsysID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.IsSelectedOnPkg = ?', 1);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }


    public function getSelectedHotelByCustomer($tpintsysID)
    {

        $travelplanitenary = array('*');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Accom"), $travelplanitenary);
        $select->where('tbl.TPIntSysId = ?', $tpintsysID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.IsSelectedOnPkg = ?', 1);
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetItenaryWiseSelectedHotelByCustomer($tpinttSysID)
    {
        //echo $tpinttSysID;
        $travelplanitenary = array('*');
        $icaccomodation = array('*');
        $country = array('Title as CountryName');
        $cityname = array('Title as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Accom"), $travelplanitenary);
        $select->joinLeft(array('tb2' => "TB_IC_Accomdation"), "tbl.AccoSysId = tb2.AccomSysId", $icaccomodation);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb2.ContSysId = tb3.ContId", $country);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb2.CitySysId = tb4.CityId", $cityname);
        $select->where('tbl.TPIntSysId = ?', $tpinttSysID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }



    public function GetPackageMask($tpsysID)
    {
        $travelplanitenary = array('AgencySysId', '');
        $icaccomodation = array('*');
        $country = array('Title as CountryName');
        $cityname = array('Title as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Accom"), $travelplanitenary);
        $select->joinLeft(array('tb2' => "TB_IC_Accomdation"), "tbl.AccoSysId = tb2.AccomSysId", $icaccomodation);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb2.ContSysId = tb3.ContId", $country);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb2.CitySysId = tb4.CityId", $cityname);
        $select->where('tbl.TPIntSysId = ?', $tpinttSysID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }



    public function GetItenrayID($tpID)
    {
        $travelplanitenary = array('TPIntSysId');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary"), $travelplanitenary);
        $select->where('tbl.TPSysId = ?', $tpID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetItenrayIDByo($tpID)
    {
        $travelplanitenary = array('TPIntSysId', 'Sequence', 'Cities');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary"), $travelplanitenary);
        $select->where('tbl.TPSysId = ?', $tpID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        // echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function deleteItenary($where, $updateData)
    {
        $this->_dbObj->update('TB_Agency_Customer_TravelPlan_Itenary', $updateData, $where);
    }

    public function deleteItenarybyo($where, $updateData)
    {
        $this->_dbObj->update('TB_TravelPlan_Itenary', $updateData, $where);
    }

    public function GetItenrayIDWithoutDelete($tpID)
    {
        $travelplanitenary = array('TPIntSysId');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary"), $travelplanitenary);
        $select->where('tbl.TPSysId = ?', $tpID);
        $select->where('tbl.IsMarkForDel = ?', '0');
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetItenrayIDWithoutDeleteBYO($tpID)
    {
        $travelplanitenary = array('TPIntSysId');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary"), $travelplanitenary);
        $select->where('tbl.TPSysId = ?', $tpID);
        $select->where('tbl.IsMarkForDel = ?', '0');
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetInnerItenrayID($tpID)
    {
        $travelplanitenary = array('TPIntSysId');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_TravelPlan_Itenary"), $travelplanitenary);
        $select->where('tbl.TPSysId = ?', $tpID);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    /* public function GetItenaryWiseSelectedCustomerHotels($packageId, $cityId, $startSequence, $endSequence, $byoPackType = NULL,$mID = NULL) {
             $cond =  !empty($byoPackType) ? " AND TBTPIA.BYOPackType = $byoPackType" : '';  
             $cond1 =  !empty($mID) ? " AND TBTPIA.MealPlanType = $mID" : '';  
        $whereCondition = " TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBICA.CitySysId = '" . $cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBMAMT.IsActive = 1 AND TBMAMT.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 $cond $cond1";
        $travelplanitenary = array('TPSysId','Sequence','Cities as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary"), $travelplanitenary);
        $select->joinLeft(array('TBTPIA' => "TB_Agency_Customer_TravelPlan_Accom"), "TBTPI.TPIntSysId = TBTPIA.TPIntSysId", array('XRefCityId as CitySysId','Title','AccoSysId','TPIntSysId','VersionId','InvnItemSysId','SeqId','TotalCost'));
        $select->joinLeft(array('TBTP' => "TB_Agency_Customer_TravelPlan"), "TBTP.TPSysId = TBTPI.TPSysId", array('SupplierSysId','NetPrice'));
        $select->joinLeft(array('TIS' => "TB_IC_Supplier"), "TIS.SupplierSysId = TBTPIA.SupplierSysId", array('SupplierSysId','SupplierName','Salution as SupplierSalution','FirstName as SupplierFirstName','LastName as SupplierLastName','EmailId as SupplierEmailId'));
        $select->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBTPIA.AccoSysId = TBICA.AccomSysId", array('Stars','Rating','AccoAminitiesMask'));
        $select->joinLeft(array('TBMRT' => "TB_Master_RoomType"), "TBTPIA.RoomType = TBMRT.RoomType", array('Title as RoomTitle'));
        $select->joinLeft(array('TBMAMT' => "TB_Master_Accom_MealType"), "TBTPIA.MealPlanType = TBMAMT.MealTypeId", array('Title as RoomMealType'));
        $select->joinLeft(array('TBRSC' => "TB_Agency_Customer_ReqSupplierOnCall"), "TBTPIA.AccoSysId = TBRSC.ItemSysId AND TBTP.TPSysId = TBRSC.TPSysId", array('StatusSysId'));
        $select->where($whereCondition);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }*/
    public function GetItenaryWiseSelectedCustomerHotels($packageId, $cityId, $startSequence, $endSequence, $byoPackType = NULL, $mID = NULL)
    {
        $cond =  !empty($byoPackType) ? " AND TBTPIA.BYOPackType = $byoPackType" : '';
        $cond1 =  !empty($mID) ? " AND TBTPIA.MealPlanType = $mID" : '';
        $whereCondition = " TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBICA.CitySysId = '" . $cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBMAMT.IsActive = 1 AND TBMAMT.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 $cond $cond1";
        $travelplanitenary = array('TPSysId', 'Sequence', 'Cities as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary"), $travelplanitenary);
        $select->joinLeft(array('TBTPIA' => "TB_Agency_Customer_TravelPlan_Accom"), "TBTPI.TPIntSysId = TBTPIA.TPIntSysId", array('XRefCityId as CitySysId', 'AccoSysId as AccomSysId', 'TPIntSysId', 'VersionId', 'InvnItemSysId', 'SeqId', 'TotalCost', 'FromDate', 'ToDate', 'TotalNights', 'AccoSimilarHotel', 'RoomTypeStr',"REPLACE(TBTPIA.Title,'(Internal)','') as Title",'RoomNumber','APIBookingRes'));
        $select->joinLeft(array('TBTP' => "TB_Agency_Customer_TravelPlan"), "TBTP.TPSysId = TBTPI.TPSysId", array('SupplierSysId', 'NetPrice'));
        $select->joinLeft(array('TIS' => "TB_IC_Supplier"), "TIS.SupplierSysId = TBTPIA.SupplierSysId", array('SupplierSysId', 'SupplierName', 'Salution as SupplierSalution', 'FirstName as SupplierFirstName', 'LastName as SupplierLastName', 'EmailId as SupplierEmailId'));
        $select->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBTPIA.AccoSysId = TBICA.AccomSysId", array('Stars', 'Rating', 'AccoAminitiesMask', 'IsAgentSpec', 'ICSourceSysId', 'DetailImg', 'Brief', 'Address','Title as HotelName'));
        $select->joinLeft(array('TBMRT' => "TB_Master_RoomType"), "TBTPIA.RoomType = TBMRT.RoomType", array('Title as RoomTitle'));
        $select->joinLeft(array('TBMAMT' => "TB_Master_Accom_MealType"), "TBTPIA.MealPlanType = TBMAMT.MealTypeId", array('Title as RoomMealType'));
        $select->joinLeft(array('TBRSC' => "TB_Agency_Customer_ReqSupplierOnCall"), "TBTPIA.AccoSysId = TBRSC.ItemSysId AND TBTP.TPSysId = TBRSC.TPSysId", array('StatusSysId'));
        $select->where($whereCondition);
        //echo $select; exit;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
    public function GetItenaryWiseReadymadeSelectedCustomerHotels($packageId, $cityId, $startSequence, $endSequence,$TotalMinDays = 0)
    {
        $whereCondition = " TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBTPIA.XRefCityId = '" . $cityId . "' AND TBTPI.TotalMinDays = '" . $TotalMinDays . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 ";
        $travelplanitenary = array('TPSysId', 'Sequence', 'Cities as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary"), $travelplanitenary);
        $select->joinLeft(array('TBTPIA' => "TB_Agency_Customer_TravelPlan_Accom"), "TBTPI.TPIntSysId = TBTPIA.TPIntSysId", array('TPIntSysId', 'VersionId', 'InvnItemSysId', 'SeqId', 'TotalCost', 'FromDate', 'ToDate', 'TotalNights', 'AccoSimilarHotel', 'RoomTypeStr',"REPLACE(TBTPIA.Title,'(Internal)','') as Title", 'NetCost','CurrencyType','RoomNumber','APIBookingRes'));
        $select->joinLeft(array('TBTP' => "TB_Agency_Customer_TravelPlan"), "TBTP.TPSysId = TBTPI.TPSysId", array('SupplierSysId', 'NetPrice','XServiceTaxAmount','AgentServiceTaxAmount','AgentsCustomServiceTaxAmount','ServiceTaxOnCustomDiscount','ExtraMarkUp'));
        $select->joinLeft(array('TIS' => "TB_IC_Supplier"), "TIS.SupplierSysId = TBTPIA.SupplierSysId", array('SupplierSysId', 'SupplierName', 'Salution as SupplierSalution', 'FirstName as SupplierFirstName', 'LastName as SupplierLastName', 'EmailId as SupplierEmailId'));
        $select->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBTPIA.AccoSysId = TBICA.AccomSysId", array('CitySysId', 'AccomSysId', 'Stars', 'Rating', 'AccoAminitiesMask', 'IsAgentSpec', 'ICSourceSysId', 'DetailImg', 'Brief', 'Address','Title as HotelName'));
        $select->joinLeft(array('TBMRT' => "TB_Master_RoomType"), "TBTPIA.RoomType = TBMRT.RoomType", array('RoomType', 'Title as RoomTitle'));
        $select->joinLeft(array('TBMAMT' => "TB_Master_Accom_MealType"), "TBTPIA.MealPlanType = TBMAMT.MealTypeId", array('MealTypeId', 'Title as RoomMealType'));
        $select->joinLeft(array('TMC' => 'TB_Master_Currency'), 'TMC.CurrencyType = TBTPIA.CurrencyType', array('Symbol'));
        $select->where($whereCondition);

//         echo $select; exit;

        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getCustomerPackageCities($packageId)
    {

        $whereCondition = " TBTPI.IsActive = '1' AND TBTPI.IsMarkForDel = '0' AND TBTPI.TPSysId = '" . @$packageId . "' ";
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary"));
        $select->joinLeft(array('TBTPIP' => "TB_Agency_Customer_TravelPlan_Itenary_Places"), "TBTPI.TPIntSysId = TBTPIP.TPIntSysId AND TBTPIP.TPIntSysId IN ( SELECT TPIntSysId from TB_Agency_Customer_TravelPlan_Itenary where  TPSYSID = $packageId) ", array('TPIntSysId'));
        $select->joinLeft(array('TBMGC' => "TB_Master_Geo_City"), "TBMGC.CityId = TBTPIP.PlaceSysId", array('CityId', 'Title AS CityName'));
        $select->where($whereCondition);
        $select->order('TBTPI.Sequence ASC');
        $select->order('TBTPI.TPIntSysId ASC');
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getCustomerPackageCitiesFullFillment($packageId)
    {

        $whereCondition = " TBTPI.IsActive = '1' AND TBTPI.IsMarkForDel = '0' AND TBTPI.TPSysId = '" . @$packageId . "' ";
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary_Fullfillment"));
        $select->joinLeft(array('TBTPIP' => "TB_Agency_Customer_TravelPlan_Itenary_Places_Fullfillment"), "TBTPI.TPIntSysId = TBTPIP.TPIntSysId", array('TPIntSysId'));
        $select->joinLeft(array('TBMGC' => "TB_Master_Geo_City"), "TBMGC.CityId = TBTPIP.PlaceSysId", array('CityId', 'Title AS CityName'));
        $select->where($whereCondition);
        $select->order('TBTPI.Sequence ASC');
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
    public function GetItenaryWiseSelectedCustomerHotelsDetail($packageId, $cityId, $startSequence, $endSequence)
    {
        $whereCondition = " TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBICA.CitySysId = '" . $cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 ";
        $travelplanitenary = array('TPSysId', 'Sequence', 'Cities as CityName');
        $arrAccomRoomInfo = array('RoomNumber as ACCOMROOMRoomNumber', 'OtherDetails as ACCOMROOMOtherDetails', 'Title as ACCOMROOMTitle', 'HotelNorms as ACCOMROOMHotelNorms', 'Amenities as ACCOMROOMAmenities');
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary"), $travelplanitenary);
        $select->joinLeft(array('TBTPIA' => "TB_Agency_Customer_TravelPlan_Accom"), "TBTPI.TPIntSysId = TBTPIA.TPIntSysId", array('TPIntSysId', 'AccoBookingId', 'XRefBookingId'));
        $select->joinLeft(array('TBTP' => "TB_Agency_Customer_TravelPlan"), "TBTP.TPSysId = TBTPI.TPSysId", array('SupplierSysId'));
        $select->joinLeft(array('TIS' => "TB_IC_Supplier"), "TIS.SupplierSysId = TBTP.SupplierSysId", array('SupplierName', 'Salution as SupplierSalution', 'FirstName as SupplierFirstName', 'LastName as SupplierLastName', 'EmailId as SupplierEmailId'));
        $select->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBTPIA.AccoSysId = TBICA.AccomSysId", array('CitySysId', 'Title', 'AccomSysId', 'Stars', 'Rating', 'AccoAminitiesMask', 'Address'));
        $select->joinLeft(array('TBICAR' => "TB_IC_Accomdation_Room"), "TBICAR.AccomSysId = TBTPIA.AccoSysId AND TBICAR.RoomIndex = TBTPIA.RoomIndex AND TBICAR.RoomIndex = TBTPIA.RoomIndex AND TBICAR.RoomNumber = TBTPIA.RoomNumber", $arrAccomRoomInfo);
        $select->joinLeft(array('TBRSC' => "TB_Agency_Customer_ReqSupplierOnCall"), "TBTPIA.AccoSysId = TBRSC.ItemSysId", array('StatusSysId'));
        $select->where($whereCondition);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetCustomerItenrayIDWithoutDelete($tpID)
    {
        $travelplanitenary = array('TPIntSysId', 'Sequence', 'CitySysId', 'Cities');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary"), $travelplanitenary);
        $select->where('tbl.TPSysId = ?', $tpID);
        $select->where('tbl.IsMarkForDel = ?', '0');
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    // get hotel details for hotels for fullfillment
    public function GetItenaryWiseReadymadeSelectedCustomerHotelsFullFill($packageId, $cityId, $startSequence, $endSequence)
    {
        $whereCondition = " TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBICA.CitySysId = '" . $cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 ";
        $travelplanitenary = array('TPSysId', 'Sequence', 'Cities as CityName', 'ExtraServices');
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary_Fullfillment"), $travelplanitenary);
        $select->joinLeft(array('TBTPIA' => "TB_Agency_Customer_TravelPlan_Accom_Fullfillment"), "TBTPI.TPIntSysId = TBTPIA.TPIntSysId", array('TPIntSysId', 'VersionId', 'InvnItemSysId', 'SeqId', 'TotalCost', 'StatusSysId', 'RoomType as RoomTypeId', 'MealPlanType as MealPlanTypeId', 'RoomMemInfoJson', 'FromDate', 'ToDate', 'TotalNights', 'PickUpLocation', 'DropLocation', 'PickUpDateTime', 'DropDateTime', 'IsEarlyCheckIn', 'Notes', 'TermsConditions'));
        $select->joinLeft(array('TBTP' => "TB_Agency_Customer_TravelPlan_Fullfillment"), "TBTP.TPSysId = TBTPI.TPSysId", array('SupplierSysId', 'NetPrice'));
        $select->joinLeft(array('TIS' => "TB_IC_Supplier"), "TIS.SupplierSysId = TBTPIA.SupplierSysId", array('SupplierSysId', 'SupplierName', 'Salution as SupplierSalution', 'FirstName as SupplierFirstName', 'LastName as SupplierLastName', 'EmailId as SupplierEmailId', 'StateId as SupplierStateId'));
        $select->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBTPIA.AccoSysId = TBICA.AccomSysId", array('CitySysId', 'Title', 'AccomSysId', 'Stars', 'Rating', 'AccoAminitiesMask', 'IsAgentSpec', 'ICSourceSysId'));
        $select->joinLeft(array('TBMRT' => "TB_Master_RoomType"), "TBTPIA.RoomType = TBMRT.RoomType", array('Title as RoomTitle'));
        $select->joinLeft(array('TBMAMT' => "TB_Master_Accom_MealType"), "TBTPIA.MealPlanType = TBMAMT.MealTypeId", array('Title as RoomMealType'));
        $select->joinLeft(array('TBCT' => "TB_Master_Geo_City"), "TBTPI.CitySysId = TBCT.CityId", array('ContSysId'));
        $select->joinLeft(array('TBST' => "TB_Master_Geo_State"), "TIS.StateId = TBST.StateId", array('Title as SupplierstateName'));
        $select->where($whereCondition);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
    // get hotel details for hotels for fullfillment
    public function GetItenaryWiseSelectedCustomerHotelsFullFill($packageId, $cityId, $startSequence, $endSequence, $byoPackType = NULL, $mID = NULL)
    {
        $cond =  !empty($byoPackType) ? " AND TBTPIA.BYOPackType = $byoPackType" : '';
        $cond1 =  !empty($mID) ? " AND TBTPIA.MealPlanType = $mID" : '';
        $whereCondition = " TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBICA.CitySysId = '" . $cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBMAMT.IsActive = 1 AND TBMAMT.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 $cond $cond1";
        $travelplanitenary = array('TPSysId', 'Sequence', 'Cities as CityName', 'ExtraServices');
        $select = $this->_dbObj->select();
        $select->from(array('TBTPI' => "TB_Agency_Customer_TravelPlan_Itenary_Fullfillment"), $travelplanitenary);
        $select->joinLeft(array('TBTPIA' => "TB_Agency_Customer_TravelPlan_Accom_Fullfillment"), "TBTPI.TPIntSysId = TBTPIA.TPIntSysId", array('XRefCityId as CitySysId', 'Title', 'AccoSysId as AccomSysId', 'TPIntSysId', 'VersionId', 'InvnItemSysId', 'SeqId', 'TotalCost', 'RoomType as RoomTypeId', 'MealPlanType as MealPlanTypeId', 'RoomMemInfoJson', 'FromDate', 'ToDate', 'TotalNights', 'PickUpLocation', 'DropLocation', 'PickUpDateTime', 'DropDateTime', 'IsEarlyCheckIn', 'Notes', 'TermsConditions'));
        $select->joinLeft(array('TBTP' => "TB_Agency_Customer_TravelPlan_Fullfillment"), "TBTP.TPSysId = TBTPI.TPSysId", array('SupplierSysId', 'NetPrice'));
        $select->joinLeft(array('TIS' => "TB_IC_Supplier"), "TIS.SupplierSysId = TBTPIA.SupplierSysId", array('SupplierSysId', 'SupplierName', 'Salution as SupplierSalution', 'FirstName as SupplierFirstName', 'LastName as SupplierLastName', 'EmailId as SupplierEmailId', 'StateId as SupplierStateId'));
        $select->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBTPIA.AccoSysId = TBICA.AccomSysId", array('Stars', 'Rating', 'AccoAminitiesMask', 'IsAgentSpec', 'ICSourceSysId'));
        $select->joinLeft(array('TBMRT' => "TB_Master_RoomType"), "TBTPIA.RoomType = TBMRT.RoomType", array('Title as RoomTitle'));
        $select->joinLeft(array('TBMAMT' => "TB_Master_Accom_MealType"), "TBTPIA.MealPlanType = TBMAMT.MealTypeId", array('Title as RoomMealType'));
        $select->joinLeft(array('TBRSC' => "TB_Agency_Customer_ReqSupplierOnCall"), "TBTPIA.AccoSysId = TBRSC.ItemSysId AND TBTP.TPSysId = TBRSC.TPSysId", array('StatusSysId'));
        $select->joinLeft(array('TBCT' => "TB_Master_Geo_City"), "TBTPI.CitySysId = TBCT.CityId", array('ContSysId'));
        $select->joinLeft(array('TBST' => "TB_Master_Geo_State"), "TIS.StateId = TBST.StateId", array('Title as SupplierstateName'));
        $select->where($whereCondition);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
}