<?php

/*
 * Catabatic Technology Pvt. Ltd.
 * File Name :Tbltbb2Cuser.php
 * File Description :
 * Created By : Harpreet
 * Created Date: 26-July-2016
 */

class Travel_Model_Tbltbbcuser {

    private $db = 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 addGuest($guestData = array()) {
        $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
        $now = date("Y-m-d H:i:s");
        $chkout= date('Y-m-d H:i:s', strtotime($_SESSION['chekOutDate']));
        $chkIN = date('Y-m-d H:i:s', strtotime($_SESSION['chekInDate']));
        if (!empty($guestData)) {

            $sqlMAX = "SELECT TOP 1 CustomerSysId FROM TB_Agency_Customer order by CustomerSysId Desc";
            $maxId = $this->db->query($sqlMAX)->fetchAll();
            foreach ($maxId as $masIDS) {
                $maxUserID = $masIDS['CustomerSysId'];
                $maxUserID++;
            }

            $sql = "SELECT EmailId,Contacts FROM TB_Agency_Customer";
            $rowset = $this->db->query($sql)->fetchAll();
            $emailMess = '';
            foreach ($rowset as $val) {
                if ($val['EmailId'] == $guestData['emailId']) {
                    $sqlEmail = "SELECT * FROM TB_Agency_Customer WHERE EmailId='" . $guestData['emailId'] . "' ";
                    $rowset = $this->db->query($sqlEmail)->fetchAll();
                    $emailMess = $guestData['emailId'] . ' Allready Exist';
                    $_SESSION['USER_DETAILS_HTL'] = $rowset;
                } else if ($val['Contacts'] == $guestData['mobilNo']) {
                    $emailMess = $guestData['mobilNo'] . ' Allready Exist';
                    $sqlMobil = "SELECT * FROM TB_Agency_Customer WHERE Contacts='" . $guestData['mobilNo'] . "' ";
                    $rowset = $this->db->query($sqlMobil)->fetchAll();
                    $_SESSION['USER_DETAILS_HTL'] = $rowset;
                }
            }
            //echo $maxId;
            if ($emailMess != '') {
                //*************insert Data   TB_Agency_Customer_Members ********/   
                $meID = '1';
                $MemberSql = "select  top 1 MemberSysId from TB_Agency_Customer_Members 
where CustomerSysId='" . $_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'] . "' order by MemberSysId DESC";
                $rowsetMem = $this->db->query($MemberSql)->fetchAll();
                foreach ($rowsetMem as $memId) {
                    $meID = $memId['MemberSysId'];
                    $meID++;
                }

                $arrCustMem = array('AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
                    'CustomerSysId' => $_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'],
                    'MemberSysId' => $meID, 'EmailId' => $guestData['emailId'],
                    'Title' => $guestData['initial'], 'Logo' => ' ', 'IsfromSM' => '0', 'SMTypeId' => '0', 'SMId' => '0', 'UserId' => '0', 'IsEmployee' => '0',
                    'Contacts' => $guestData['mobilNo'], 'JoinDate' => $chkIN, 'ExitDate' => $chkout, 'UserName' => ' ',
                    'FirstName' => $guestData['firstName'], 'LastName' => $guestData['lastName'], 'Designation' => ' ', 'Password' => ' ', 'RandStr' => ' ',
                    'PasswordExpiryDate' => EMPTY_DATE, 'IsPswExpire' => '0', 'OtherEmail' => ' ',
                    'SecondaryEmail' => ' ', 'LastLoginTime' => EMPTY_DATE, 'IsLogInNow' => '0', 'CreatedByUserSysId' => $sessionLogin_user->intLoggedinUserId
                    , 'RandomCode' => ' ', 'Gender' => '0',
                    'UserNickName' => ' ', 'UserPicPath' => '', 'ContactNo1' => '0', 'ContactNo2' => '0', 'HomePhone' => '0',
                    'SecondaryPhone' => '0', 'UserDetails' => ' ', 'Address' => ' ', 'CitySysId' => '0', 'StateOrZoneSysId' => '0',
                    'CountrySysId' => '0',
                    'PinCode' => '0', 'SecondaryAddress' => ' ', 'Fax' => '0', 'MacIp' => '0', 'SpeakingLangSysId' => '0',
                    'Signature' => ' ', 'UpdateDate' => EMPTY_DATE, 'RegisterDate' => EMPTY_DATE, 'IsActivated' => '0', 'IsApproved' => '0',
                    'IsActive' => '0', 'IsMarkForDelete' => '0');
                // echo 'TB_Agency_Customer_Members';print_r($arrCustMem);
                try {


                    $this->db->insert('TB_Agency_Customer_Members', $arrCustMem);
                    $lastIDCustMem = $this->db->lastInsertId('TB_Agency_Customer_Members');
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }


                //*************insert Data   TB_Agency_Customer_TravelPlan ********/ 

                $plancode = 'Q/' . date('y');
                $arrTravelPln = array('PlanBookingId' => $plancode, 'AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
                    'StatusType' => '0',
                    'CustomerSysId' => $_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'], 
                    'PlanType' => '2',
                    'StartDate' => $now, 'ValidTill' => EMPTY_DATE,
                    'CreateDate' => $now, 'UpdateDate' => $now, 'ApproveDate' => $now,
                    'IsMarkForDel' => '0', 'IsActive' => '1', 'IsAprooved' => '0');
                //echo 'TB_Agency_Customer_TravelPlan';print_r($arrTravelPln);
                try {

                    $this->db->insert('TB_Agency_Customer_TravelPlan', $arrTravelPln);
                    $lastIDTvlPln = $this->db->lastInsertId('TB_Agency_Customer_TravelPlan');
                    $_SESSION['TPSysId'] = $lastIDTvlPln;
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }


                //*************insert Data   TB_Agency_Customer_TravelPlan_Itenary ********/ 

                $arrTvlItnry = array('TPSysId' => $lastIDTvlPln, 'AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
                    'PlanType' => '2', 'StartDate' => $now, 'EndDate' => EMPTY_DATE, 'CreateDate' => $now,
                    'UpdateDate' => $now);
                try {

                    $this->db->insert('TB_Agency_Customer_TravelPlan_Itenary', $arrTvlItnry);
                    $lastIDTvlITnry = $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Itenary');
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }
                //*************************TB_Agency_Customer_TravelPlan_Accom*********************//
                 if(isset($_SESSION['BOOK_ROM_INVENTORY'])){
                    $romType=$_SESSION['BOOK_ROM_INVENTORY'][0]['Title'];
                }if(isset($_SESSION['BOOK_ROM_INVENTORY'])){
                    $romType=$_SESSION['blkRoom']['BlockRoomResult']['HotelRoomsDetails'][0]['RoomTypeName'];
                }
                try {
                   
                    $booking = 'HTL-' . Zend_Controller_Action_HelperBroker::getStaticHelper('General')->generateRandomString(5);
                    $memCnt = count($_SESSION['sessionHotelDirectSearchData']['params']['memberDetails']);
                    $memArr = $_SESSION['sessionHotelDirectSearchData']['params']['memberDetails'];
                    $j = 0; $K=1;
                    for ($i = 0; $i < $memCnt; $i++) {
                        $sqlMAX = "SELECT TOP 1 SeqId FROM TB_Agency_Customer_TravelPlan_Accom order by SeqId Desc";
                        $maxId = $this->db->query($sqlMAX)->fetchAll();
                        foreach ($maxId as $masIDS) {
                            $maxUserID = $masIDS['SeqId'];
                            $maxUserID++;
                        }
                        $insertTravelPlan_Accom = array("TPIntSysId" => $lastIDTvlITnry, "SeqId" => $K,
                            "AccoBookingId" => $booking, "XRefBookingId" => ' '
                            , "AgencySysId" => $sessionLogin_user->intLoggedinUserAgencySysId,
                            "AccoSysId" => $_SESSION['SELECTED_HTL_ID'], 'RoomType' => '0', 'RoomNumber'=> $_SESSION['SEARCH']['selectRoom'], "Title" => $_SESSION['SEARCH']['Hotel_name_city'],
                            "SpecialRequest" => $guestData['special_req'],
                            'CheckInTime' => $chkIN, 'CheckOutTime' => $chkout, 'XRefCityId' => $_SESSION['SEARCH']['hidden_selected_hotel_id'], "FromDate" => $chkIN
                            , "ToDate" => $chkout, "AdultPax" => $memArr[$j]['AdultPax'], "ChildPax" => $memArr[$j]['ChildPax'], "InfantPax" => '0',
                            "PetPax" => '0', 'ChildsAge' => $memArr[$j]['ChildsAge'], "TotalPax" => $memCnt, "StarRating" => '0', "CurrencyType" => '0',
                            "TotalCost" => '0', "Discounts" => '0'
                            , "Taxes" => '0', "NetCost" => '0', "CreateDate" => $now, "UpdateDate" => $now, "IsMarkForDel" => '0', "IsActive" => 1);
//echo '<pre>';
//print_r($insertTravelPlan_Accom);
                        $this->db->insert('TB_Agency_Customer_TravelPlan_Accom', $insertTravelPlan_Accom);
//print_r($insertTravelPlan_Accom);exit;

                        $j++; $K++;
                    }
                    $lastPlanID = $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Accom');
                    $_SESSION['SeqId'] = $maxUserID;

                    // print_r($_SESSION['SeqId']);exit; 
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }
                return 1;
            } else {


                //Zend_Session::rememberMe(60 * 60 * 24 * 7);
                //*************insert Data   TB_CRM_CustomerAccount ********/
                $arAdCustomerCRM = array('EmailId' => $guestData['emailId'], 'Salutation' => $guestData['initial'], 'PrimaryContactNumber' => $guestData['mobilNo'],
                    'FullName' => $guestData['firstName'] . ' ' . $guestData['lastName'], 'FirstName' => $guestData['firstName'], 'LastName' => $guestData['lastName'], 'IsActivated' => '1',
                    'ActiveDate' => EMPTY_DATE, 'IsApproved' => '1', 'IsActive' => '1', 'IsMarkForDelete' => '0');

                try {
                    $this->db->insert('TB_CRM_CustomerAccount', $arAdCustomerCRM);
                    $lastIDCRM = $this->db->lastInsertId('TB_CRM_CustomerAccount');
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }


                //*************insert Data   TB_Agency_Customer ********/   
                $now = date("Y-m-d H:i:s");

                $arrCustomer = array('AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId, 'CRMCustSysId' => $lastIDCRM, 'EmailId' => $guestData['emailId'], 'Title' => $guestData['initial'],
                    'Logo' => '', 'IsfromSM' => '0', 'SMTypeId' => '0', 'SMId' => '', 'UserId' => '0', 'IsEmployee' => '0',
                    'Contacts' => $guestData['mobilNo'], 'ExitDate' => EMPTY_DATE, 'UserName' => '', 'FirstName' => $guestData['firstName'], 'LastName' => $guestData['lastName'],
                    'Designation' => '', 'Password' => '', 'RandStr' => '', 'PasswordExpiryDate' => EMPTY_DATE, 'IsPswExpire' => 0,
                    'OtherEmail' => '', 'SecondaryEmail' => '', 'LastLoginTime' => EMPTY_DATE, 'IsLogInNow' => 0, 'CreatedByUserSysId' => $sessionLogin_user->intLoggedinUserId,
                    'RandomCode' => '', 'Gender' => '', 'UserNickName' => '', 'UserPicPath' => '', 'ContactNo1' => '', 'ContactNo2' => '',
                    'HomePhone' => '', 'SecondaryPhone' => '', 'UserDetails' => '', 'Address' => '', 'CitySysId' => 0,
                    'StateOrZoneSysId' => 0, 'CountrySysId' => 0, 'PinCode' => '', 'SecondaryAddress' => '', 'Fax' => '',
                    'MacIp' => '', 'SpeakingLangSysId' => 0, 'Signature' => '', 'UpdateDate' => EMPTY_DATE,
                    'RegisterDate' => EMPTY_DATE, 'SpecialRequest' => $guestData['special_req'], 'IsActivated' => '0', 'IsApproved' => 0, 'IsActive' => 0, 'IsMarkForDelete' => '0'
                );

                try {
                    $this->db->insert('TB_Agency_Customer', $arrCustomer);
                    $lastUserIDCustomAgency = $this->db->lastInsertId('TB_Agency_Customer');
                    $sqlLastInsert = "SELECT * FROM TB_Agency_Customer WHERE CustomerSysId='" . $lastUserIDCustomAgency . "'";
                    $rowset = $this->db->query($sqlLastInsert)->fetchAll();
                    $_SESSION['USER_DETAILS_HTL'] = $rowset;
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }
                //*************insert Data   TB_Agency_Customer_Members ********/   

                $arrCustMem = array('AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
                    'CustomerSysId' => $lastUserIDCustomAgency,
                    'MemberSysId' => '1', 'EmailId' => $guestData['emailId'],
                    'Title' => $guestData['initial'], 'Logo' => ' ', 'IsfromSM' => '0', 'SMTypeId' => '0', 'SMId' => ' ', 'UserId' => '0', 'IsEmployee' => '0',
                    'Contacts' => $guestData['mobilNo'], 'JoinDate' => $chkIN, 'ExitDate' => $chkout, 'UserName' => ' ',
                    'FirstName' => $guestData['firstName'], 'LastName' => $guestData['lastName'], 'Designation' => ' ', 'Password' => ' ', 'RandStr' => ' ',
                    'PasswordExpiryDate' => EMPTY_DATE, 'IsPswExpire' => '0', 'OtherEmail' => ' ',
                    'SecondaryEmail' => ' ', 'LastLoginTime' => EMPTY_DATE, 'IsLogInNow' => '0', 'CreatedByUserSysId' => $sessionLogin_user->intLoggedinUserId
                    , 'RandomCode' => ' ', 'Gender' => '0',
                    'UserNickName' => ' ', 'UserPicPath' => ' ', 'ContactNo1' => ' ', 'ContactNo2' => ' ', 'HomePhone' => ' ',
                    'SecondaryPhone' => ' ', 'UserDetails' => ' ', 'Address' => ' ', 'CitySysId' => '0', 'StateOrZoneSysId' => '0',
                    'CountrySysId' => '0',
                    'PinCode' => ' ', 'SecondaryAddress' => ' ', 'Fax' => ' ', 'MacIp' => ' ', 'SpeakingLangSysId' => '0',
                    'Signature' => ' ', 'UpdateDate' => EMPTY_DATE, 'RegisterDate' => EMPTY_DATE, 'IsActivated' => '0', 'IsApproved' => '0',
                    'IsActive' => '0', 'IsMarkForDelete' => '0');
                try {

                    $this->db->insert('TB_Agency_Customer_Members', $arrCustMem);
                    $lastIDCustMem = $this->db->lastInsertId('TB_Agency_Customer_Members');
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }


                //*************insert Data   TB_Agency_Customer_TravelPlan ********/ 
                $now = date("Y-m-d H:i:s");
                $plancode = 'Q/' . date('y');
                $arrTravelPln = array('PlanBookingId' => $plancode, 'AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
                    'StatusType' => '0',
                    'CustomerSysId' => $lastUserIDCustomAgency, 'PlanType' => '2','StartDate' => $now, 'ValidTill' => EMPTY_DATE,
                    'CreateDate' => $now, 'UpdateDate' => $now, 'ApproveDate' => $now,
                    'IsMarkForDel' => '0', 'IsActive' => '1', 'IsAprooved' => '0');
                try {

                    $this->db->insert('TB_Agency_Customer_TravelPlan', $arrTravelPln);
                    $lastIDTvlPln = $this->db->lastInsertId('TB_Agency_Customer_TravelPlan');
                    $_SESSION['TPSysId'] = $lastIDTvlPln;
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }


                //*************insert Data   TB_Agency_Customer_TravelPlan_Itenary ********/ 

                $arrTvlItnry = array('TPSysId' => $lastIDTvlPln, 'AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
                    'PlanType' => '2', 'StartDate' => $now, 'EndDate' => EMPTY_DATE, 'CreateDate' => $now,
                    'UpdateDate' => $now);
                try {

                    $this->db->insert('TB_Agency_Customer_TravelPlan_Itenary', $arrTvlItnry);
                    $lastIDTvlITnry = $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Itenary');
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }


                //****************************TB_Agency_Customer_TravelPlan_Accom*****************//////
                if(isset($_SESSION['BOOK_ROM_INVENTORY'])){
                    $romType=$_SESSION['BOOK_ROM_INVENTORY'][0]['Title'];
                }if(isset($_SESSION['BOOK_ROM_INVENTORY'])){
                    $romType=$_SESSION['blkRoom']['BlockRoomResult']['HotelRoomsDetails'][0]['RoomTypeName'];
                }
                try {
                    $booking = 'HTL-' . Zend_Controller_Action_HelperBroker::getStaticHelper('General')->generateRandomString(5);
                    $memCnt = count($_SESSION['sessionHotelDirectSearchData']['params']['memberDetails']);
                    $memArr = $_SESSION['sessionHotelDirectSearchData']['params']['memberDetails'];
                    $j = 0; $K=1;
                    for ($i = 0; $i < $memCnt; $i++) {
//                        $sqlMAX = "SELECT TOP 1 SeqId FROM TB_Agency_Customer_TravelPlan_Accom order by SeqId Desc";
//                        $maxId = $this->db->query($sqlMAX)->fetchAll();
//                        foreach ($maxId as $masIDS) {
//                            $maxUserID = $masIDS['SeqId'];
//                            $maxUserID++;
//                        }
                        $insertTravelPlan_Accom = array("TPIntSysId" => $lastIDTvlITnry, "SeqId" => $K,
                            "AccoBookingId" => ' ', "XRefBookingId" => '0'
                            , "AgencySysId" => $sessionLogin_user->intLoggedinUserAgencySysId,
                            "AccoSysId" => $_SESSION['SELECTED_HTL_ID'], 'RoomType' => '0', 'RoomNumber' => $_SESSION['SEARCH']['selectRoom'], "Title" => $_SESSION['SEARCH']['Hotel_name_city'],
                            "SpecialRequest" => $guestData['special_req'], 'CheckInTime' => $chkIN, 'CheckOutTime' => $chkout, 'XRefCityId' =>$_SESSION['SEARCH']['hidden_selected_hotel_id'], "FromDate" => $chkIN
                            , "ToDate" => $chkout, "AdultPax" => $memArr[$j]['AdultPax'], "ChildPax" => $memArr[$j]['ChildPax'], "InfantPax" => '0',
                            "PetPax" => '0', 'ChildsAge' => $memArr[$j]['ChildsAge'], "TotalPax" => $memCnt, "StarRating" => '0', "CurrencyType" => '0',
                            "TotalCost" => '0', "Discounts" => '0'
                            , "Taxes" => '0', "NetCost" => '0', "CreateDate" => $now, "UpdateDate" => $now, "IsMarkForDel" => '0', "IsActive" => 1);
//print_r($insertTravelPlan_Accom);

                        $this->db->insert('TB_Agency_Customer_TravelPlan_Accom', $insertTravelPlan_Accom);
//print_r($insertTravelPlan_Accom);exit;
                        $j++;$K++;
                    }
                    $lastPlanID = $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Accom');
                    $_SESSION['SeqId'] = $maxUserID;
                } catch (Exception $e) {
                    die('There has been an error. ' . $e->getMessage());
                }
                return 1;
            }
        }
    }
    
    public function getDataForBookingHotelList() {
        $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
        $sql = "SELECT DISTINCT AC.FirstName,AC.LastName,AC.EmailId,AC.Contacts,A.Title AS HOTELNAME,ACTP.TPSysId,
        ACX.XRefTrxNo,AG.Title,AG.PrimaryContactName,
        ACX.TrxDate,ACX.TrxType,ACX.TrxSysId,ACX.PaidAmount,ACTI.TPSysId,ACTA.AccoSysId,A.Title,
        ACTA.CheckInTime,ACTA.XRefCityId,
        GC.Title AS CITYNAME,ACTI.TPIntSysId, AC.CustomerSysId,ACTA.XRefCityId  FROM 
        TB_IC_Accomdation A 
        LEFT OUTER JOIN TB_Agency_Customer_TravelPlan_Accom ACTA ON A.AccomSysId=ACTA.AccoSysId 
        LEFT OUTER JOIN TB_Agency_Customer_TravelPlan ACTP ON A.AccomSysId=ACTA.AccoSysId 
        LEFT OUTER JOIN TB_Agency_Customer_TravelPlan_Itenary ACTI ON ACTI.TPSysId=ACTP.TPSysId 
        LEFT OUTER JOIN TB_Agency_Customer_Trx ACX ON ACTP.TPSysId= ACX.TPSysId
        LEFT OUTER JOIN TB_Agency_Customer AC ON AC.CustomerSysId=ACX.CustomerSysId
        LEFT OUTER JOIN TB_Agency AG ON AG.AgencySysId=AC.AgencySysId 
        LEFT OUTER JOIN TB_Master_Geo_City GC ON GC.TBBCityId=ACTA.XRefCityId
        WHERE AG.AgencySysId='".$sessionLogin_user->intLoggedinUserAgencySysId."' AND ACX.TrxType='H' AND ACX.TrxStatus='1' AND
         ACTI.TPSysId=ACX.TPSysId AND ACTI.TPIntSysId=ACTA.TPIntSysId
         GROUP BY    AC.FirstName,AC.LastName,AC.EmailId,AC.Contacts,A.Title ,ACTP.TPSysId,
ACX.XRefTrxNo,AG.Title,AG.PrimaryContactName,
        ACX.TrxDate,ACX.TrxType,ACX.PaidAmount,ACTI.TPSysId,ACTA.AccoSysId,A.Title,ACX.TrxSysId,
        ACTA.CheckInTime,ACTA.XRefCityId,GC.Title,ACTI.TPIntSysId, AC.CustomerSysId,ACTA.XRefCityId";

        
        $res = $this->db->query($sql)->fetchAll();
        return $res;
    }
    public function getGuestDetailVocherBookingHotelList($custID,$TPSysId,$TPIntSysId) {
        $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
     $sql = "SELECT DISTINCT A.Title as HOTELNAME,A.Stars, A.Address AS HOTELADD,A.PrimaryContact AS HOTELCONT, ACTI.TPIntSysId,AC.FirstName,AC.LastName,AC.EmailId,
          AC.Contacts,ACTP.TPSysId,ACX.XRefTrxNo,AG.Title,AG.PrimaryContactName,ACX.TrxDate,ACX.TrxType,ACX.PaidAmount,ACTI.TPSysId,
        ACTA.AccoSysId,ACTA.FromDate AS CheckInTime,ACTA.ToDate AS CheckOutTime,ACTA.XRefCityId,
        GC.Title AS CITYNAME,ACTA.RoomType,ACX.XRefTrxNo,ACX.TrxSysId,ACTA.RoomNumber,ACTA.TotalPax,ACTA.TotalNights,ACTA.AccoBookingId,ACTA.TotalCost,ACTA.Discounts,
        ACTA.Taxes,ACTA.AdultPax,ACTA.ChildPax,ACTA.TotalNights,ACTA.SpecialRequest FROM TB_Agency_Customer_TravelPlan_Accom ACTA 
        LEFT OUTER JOIN TB_IC_Accomdation A ON A.AccomSysId=ACTA.AccoSysId        
        LEFT OUTER JOIN TB_Agency_Customer_TravelPlan_Itenary ACTI ON ACTI.TPIntSysId=ACTA.TPIntSysId
        LEFT OUTER JOIN TB_Agency_Customer_TravelPlan ACTP ON ACTI.TPSysId=ACTP.TPSysId  
        LEFT OUTER JOIN TB_Agency_Customer_Trx ACX ON ACTP.TPSysId= ACX.TPSysId
        LEFT OUTER JOIN TB_Agency_Customer AC ON AC.CustomerSysId=ACX.CustomerSysId
        LEFT OUTER JOIN TB_Agency AG ON AG.AgencySysId=AC.AgencySysId 
        LEFT OUTER JOIN TB_Master_Geo_City GC ON GC.TBBCityId=ACTA.XRefCityId
        WHERE AG.AgencySysId='".$sessionLogin_user->intLoggedinUserAgencySysId."' AND 
            GC.TBBCityId=ACTA.XRefCityId AND ACX.TrxType='H' AND
            ACX.TrxStatus='1' AND  ACX.CustomerSysId='".$custID."'  AND ACTP.TPSysId='".$TPSysId."' ";
        $res = $this->db->query($sql)->fetchAll();
        $_SESSION['voucer_view']=$res;
       // print_r();
       return ;
    }
    
    public function agencydetail(){
          $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
           $sql=  "SELECT ABD.BankName,ABD.AccountName,ABD.AccountNo,ABD.Branch,ABD.IFSCCode,
              A.Title,A.Url,A.Logo,A.ServiceTaxNo,A.PanCardNo,A.PrimaryEmail,
              A.PrimaryContactName,A.ContactInfo,A.Address,A.CreateDate,AD.CitySysId,AD.Pincode,
              AD.Address AS ADDRESSTB, C.Title as CITYNAME , CI.Title as BANKCITY,CU.Title AS COUNTRY
              FROM TB_Agency_BankDetails ABD LEFT OUTER JOIN TB_Agency  A ON A.AgencySysId=ABD.AgencySysId
              LEFT OUTER JOIN TB_Agency_Addresses AD ON AD.AgencySysId=ABD.AgencySysId
              LEFT OUTER JOIN TB_Master_Geo_City C ON C.CityId=AD.CitySysId 
              LEFT OUTER JOIN TB_Master_Geo_City CI ON CI.CityId=ABD.CitySysId
              LEFT OUTER JOIN TB_Master_Geo_Country CU ON CU.ContId=ABD.CountrySysId
              WHERE A.AgencySysId='".$sessionLogin_user->intLoggedinUserAgencySysId."'
              GROUP BY 
              ABD.BankName,ABD.AccountName,ABD.AccountNo,ABD.IFSCCode,
              A.Title,A.Logo,A.ServiceTaxNo,A.PanCardNo,A.PrimaryEmail,
              A.PrimaryContactName,A.ContactInfo,A.Address,A.CreateDate,AD.CitySysId,
              AD.Pincode,AD.Address,C.Title,ABD.Branch,CI.Title,CU.Title";
              $res = $this->db->query($sql)->fetchAll();
        return $res;
    }
    
    
    

}