<?php

class Travel_Model_TblPackageBooking {
    #declare class vairables

    public $intId = NULL;
    public $TPSysId = NULL;
    private $db = NULL;
    public $strCondition = '';
    public $baseUrl;
    public $imageServerUrl;

    /**
     * Class Constructors
     */
    public function __construct() {

        #initialize db adapter
        $this->db = Zend_Db_Table::getDefaultAdapter();

        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();

        $registry = Zend_Registry::getInstance();
        $this->imageServerUrl = @$registry->imageServerURL;
    }

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

    /* Added By Pardeep Panchal */

    public function getPackageTravelPlanDetails() {

        /*
          SELECT *  FROM TB_Agency_Customer_TravelPlan where TPSysId = 2
          SELECT *  FROM TB_Agency_Customer_TravelPlan_Itenary where TPSysId = 2
          SELECT *  FROM TB_Agency_Customer_TravelPlan_Itenary_Places where TPintSysId in (1,3,4,5,6,7)
          SELECT *  FROM TB_Agency_Customer_TravelPlan_Accom where TPintSysId in (1,3,4,5,6,7)
          SELECT *  FROM TB_Agency_Customer_TravelPlan_Air where TPintSysId in (1,3,4,5,6,7)
          SELECT *  FROM TB_Agency_Customer_TravelPlan_SightSeeing where InvnItemSysId in (1,3,4,5,6,7)
          select * from TB_Master_Geo_City where cityId in (2,4,6)
         */
        $arrTravelPlan = array('Title as PkgTitle', 'CurrencyType', 'StartDate', 'ValidTill', 'AgentSysId', 'AgencySysId', 'RoomInfoJson', 'CustomerSysId', 'StatusType', 'DiscountType', 'DiscountVal', 'AdvBookingPercent', 'AdvBookingDays', 'NetPrice', 'SupplierSysId', 'PlanType', 'IsCardChrgToCustomer', 'CustomDiscount', 'MarkUp', 'MasterTPSysId', 'AgentServiceTaxAmount', 'XServiceTaxAmount', 'AgentsCustomServiceTaxAmount', 'ServiceTaxOnCustomDiscount', 'TotalCostGen', 'EconomyMask', 'ExtraMarkUp', 'B2BAgencySysId', 'IsB2BProposal','IsFixedDeparturePackage');
        $arrTravelPlanItenary = array('TPintSysId', 'Cities', 'Sequence');
        //        $arrTravelPlanRate = array('CostPPDO');
        $select = $this->db->select();
        $select->from(array('t1' => 'TB_Agency_Customer_TravelPlan'), $arrTravelPlan);
        $select->joinLeft(array('t2' => 'TB_Agency_Customer_TravelPlan_Itenary'), 't2.TPSysId = t1.TPSysId', $arrTravelPlanItenary);
        $select->joinLeft(array('t3' => 'TB_Agency_Customer_TravelPlan_Mapping'), 't3.TPSysId = t1.TPSysId', array('CouponCode', 'IsCoupon'));
        if (!empty($this->TPSysId)) {
            $select->where('t1.TPSysId = ?', $this->TPSysId);
        }

        //        echo $select; exit;

        $result = $this->db->fetchAll($select);



        return $result;
    }

    public function getPackageTravelPlanDetailForPaymentReminder($TPSysId) {
        $arrTravelPlan = array(
            'Title as PkgTitle', 'StartDate', 'ApproveDate', 'AgentSysId', 'AgencySysId', 'CustomerSysId','CurrencyType',
            'StatusType', 'DiscountType', 'DiscountVal', 'AdvBookingPercent', 'AdvBookingDays', 'NetPrice',
            'SupplierSysId', 'PlanType', 'IsCardChrgToCustomer', 'CustomDiscount', 'MarkUp', 'PackageCostCalType', 'RoomInfoJson', 'Readymade', 'TotalCostGen', 'ExtraMarkUp',
            "(select sum(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = '0' AND t1.TPSysId = adT1.TPSysId AND (adT1.PlanType = 21 OR adT1.PlanType = 19)) as TotalTcs"
        );
        $select = $this->db->select();
        $select->from(array('t1' => 'TB_Agency_Customer_TravelPlan'), $arrTravelPlan);
        if (!empty($TPSysId)) {
            $select->where('t1.TPSysId = ?', $TPSysId);
        }
//        echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getPackageTravelPlanAccomodation() {

        $sql = " SELECT DISTINCT AccoSysId, TotalPax,AdultPax,ChildPax,InfantPax FROM TB_Agency_Customer_TravelPlan_Accom ";
        $sql .= " WHERE 1=1 ";
        if (!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }
        //echo $sql;

        $result = $this->db->query($sql)->fetchAll();

        return $result;
    }

    public function getPackageTermsAndConditions($intTPSysId) {

        $sql = " SELECT Inclusions, Exclusions,CancellationPolicy FROM TB_Agency_Customer_TravelPlan_Terms_Conditions ";
        $sql .= " WHERE 1=1 AND TPIntSysId = '" . $intTPSysId . "'";
        $result = $this->db->query($sql)->fetchAll();
        return $result;
    }

    public function getPackageTravelPlanAir($strTPintSysIds) {

        $sql = " SELECT * FROM TB_Agency_Customer_TravelPlan_Air ";
        $sql .= " WHERE 1=1 ";
        if (!empty($strTPintSysIds)) {
            $sql .= " AND TPintSysId IN (" . $strTPintSysIds . ")";
        }
        //echo $sql; exit;
        $result = $this->db->query($sql)->fetchAll();

        return $result;
    }

    public function getPackageTravelPlanAirFlightDetails($strTPintSysIds) {
        $arrTravelPlan_Air = array('InvnItemSysId');
        $arrTrans_API_Air = array('TBQAirInvenSysId as AirInvenSysId', 'SourceAirportCode', 'DestAirportCode', 'CurrencyType', 'ApiResultIndex', 'apiTraceId', 'FlightNumber');
        $arrTrans_API_Air_Stop = array('ArrivalTime', 'DepartureTime', 'FlightDuration', 'AirlineCode', 'AirlineName');
        $arrTrans_API_Air_Fare = array('PublishedFare', 'CommissionEarned', 'PLBEarned', 'IncentiveEarned', 'OfferedFare');


        $select = $this->db->select();
        $select->from(array('t1' => 'TB_Agency_Customer_TravelPlan'), array(''));
        $select->joinLeft(array('t2' => 'TB_Agency_Customer_TravelPlan_Itenary'), 't2.TPSysId = t1.TPSysId', array(''));
        $select->joinLeft(array('t3' => 'TB_Agency_Customer_TravelPlan_Air'), 't3.TPIntSysId = t2.TPIntSysId', $arrTravelPlan_Air);
        $select->joinLeft(array('t4' => 'TB_IC_Trans_API_Air'), 't4.TBQAirInvenSysId = t3.InvnItemSysId', $arrTrans_API_Air);
        $select->joinLeft(array('t5' => 'TB_IC_Trans_API_Air_Stop'), 't5.TBQAirInvenSysId = t4.TBQAirInvenSysId', $arrTrans_API_Air_Stop);
        $select->joinLeft(array('t6' => 'TB_IC_Trans_API_Air_Fare'), 't6.TBOAirInvenAPISysId = t4.TBQAirInvenSysId', $arrTrans_API_Air_Fare);


        if (!empty($strTPintSysIds)) {
            $select->where('t1.TPSysId = ?', $strTPintSysIds);
        }
        $result = $this->db->fetchAll($select);

        return $result;
    }

    public function getPackageTravelPlanSightSeeing($strTPintSysIds) {

        $sql = " SELECT * FROM TB_Agency_Customer_TravelPlan_SightSeeing ";
        $sql .= " WHERE 1=1 AND IsActive = '1' AND IsMarkForDelete = '0'";
        if (!empty($strTPintSysIds)) {
            $sql .= " AND InvnItemSysId IN (" . $strTPintSysIds . ")";
        }

        $result = $this->db->query($sql)->fetchAll();

        return $result;
    }

    public function updateProposalStatus($StatusType, $paymentStatus = null, $bookingStatus = null, $intTPSysId, $MasterTPSysId = NULL) {
        if ((int) $MasterTPSysId > 0) {
            try {
                $data = array('StatusType' => $StatusType, 'PaymentStatus' => $paymentStatus);
                $this->db->update("TB_Agency_Customer_TravelPlan_Query", $data, array('TPSysId = ?' => $MasterTPSysId));
                if ((int) $bookingStatus > 0) {
                    $updatesqlquery = "UPDATE TB_Agency_Customer_TravelPlan_Query SET BookingStatus = $bookingStatus WHERE TPSysId = $MasterTPSysId AND BookingStatus is null";
                    $this->db->query($updatesqlquery);
                }
                $querystatus = 4;
                if ($StatusType == 143 && $paymentStatus == 22) {
                    $querystatus = 17;
                } else if ($StatusType == 143 && $paymentStatus == 23) {
                    $querystatus = 13;
                } else if ($StatusType == 3 && $paymentStatus == 20) {
                    $querystatus = 5;
                }
                if (isset($querystatus) && !empty($querystatus) && !empty($intTPSysId)) {
                    $whereMaster = array('TPSysId = ? ' => $intTPSysId);
                    // echo "<pre>";print_r($whereMaster);exit;
                    $updatetravelplanquery = array(
                        'StatusType' => $querystatus,
                        'UpdateDate' => date('Y-m-d H:i:s')
                    );
                    $this->db->update('TB_Agency_Customer_TravelPlan', $updatetravelplanquery, $whereMaster);
                }
            } catch (Zend_Exception $e) {
                echo $e->getMessage();
                exit;
            }
        }
    }

    public function GetCustomerMembersById($agencySysID, $CRMCustSysId, $TPSysId = null, $memberId = null, $IsLeadPax = null) { // get  CustomerMembers According to travel Plan
        $select = $this->db->select();

        $select->from(array('t1' => 'TB_Agency_Customer_Members'));
        $select->joinInner(array('t2' => 'TB_Agency_Customer_TravelPlan_Pax'), 't2.MemberSysId = t1.MemberSysId');
        $select->where("t1.CustomerSysId = ?", $CRMCustSysId);
        $select->where('t1.AgencySysId = ?', $agencySysID);
        if (!empty($TPSysId)) {
            $select->where('t2.TPSysId = ?', $TPSysId);
        }
        if (!empty($IsLeadPax)) {
            $select->where('t2.IsLeadPax = ?', $IsLeadPax);
        }
        if (!empty($memberId)) {
            $select->where('MemberSysId.MemberSysId = ?', $memberId);
        }
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAgencyMmbrById($id) {
        $agenctcustomer = array('CountrySysId', 'StateOrZoneSysId', 'Address', 'DOB', 'CitySysId', 'AgencySysId', 'CustomerSysId', 'EmailId', 'Title', 'Contacts', 'FirstName', 'LastName', 'IsActive', 'IsMarkForDelete');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), $agenctcustomer);
        //$select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tbl.CRMCustSysId = tb2.CRMCustSysId" , '');
        $select->where("tbl.CustomerSysId = ?", $id);
        //$select->where("tbl.CRMCustSysId = ?", $id);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    /* Added By Pardeep Panchal */

    public function getAutoSuggestList() {

        // $sql = " Select * From TB_IC_Accomdation where 1 = 1 " ;

        $sql = " SELECT  TOP 10 AccomSysId, IsAgentSpec, ICSourceSysId, TB_IC_Accomdation.Title, AliasAndMeta, ShortName, PlaceSysId_XRef
                ,TB_IC_Accomdation.Icon, ThumnailImg, DetailImg, Stars, Rating, Popularity, AwardRecognizations, PropertyType, 
                TB_IC_Accomdation.EconomyType AS EconomyTypeId,
                TB_Master_EconomyType.Title as EconomyType
                ,AccoAminitiesMask, MaxPaxCount, RoomCounts, Brief, CheckinTime, CheckOutTime, IsDayLightApp
                ,GeoLat, GeoLong, Address, PinCode, CitySysId, TB_Master_Geo_City.Title as CityName,
                TB_Master_Geo_Country.Title as CountryName, TB_IC_Accomdation.ZoneSysId, 
                TB_IC_Accomdation.ZoneType, TB_IC_Accomdation.StateSysId, TB_IC_Accomdation.ContSysId
                ,TB_IC_Accomdation.UpdateDate, TB_IC_Accomdation.ApproveDate, TB_IC_Accomdation.ApproveBy, 
                TB_IC_Accomdation.CreateDate, TB_IC_Accomdation.IsApproved, TB_IC_Accomdation.IsActive, TB_IC_Accomdation.IsMarkForDel
                FROM TB_IC_Accomdation LEFT JOIN TB_Master_Geo_City ON TB_IC_Accomdation.CitySysId = TB_Master_Geo_City.CityId
                     LEFT JOIN TB_Master_Geo_Country ON TB_IC_Accomdation.ContSysId = TB_Master_Geo_Country.ContId
                        LEFT JOIN TB_Master_EconomyType ON TB_IC_Accomdation.EconomyType = TB_Master_EconomyType.EconomyType 
                        WHERE 1 = 1 ";


        if (!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }


        $sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;

        //echo $sql; die;
        return $this->db->query($sql)->fetchAll();
    }

    public function getCustomerPackagePlacesList($packageId) {
        $sql = "SELECT TB_Agency_Customer_TravelPlan_Itenary_Places.PlaceSysId,TB_Master_Geo_City.Title ,TB_Master_Geo_City.Country 
							FROM TB_Agency_Customer_TravelPlan_Itenary_Places 
							LEFT JOIN TB_Master_Geo_City
							ON TB_Agency_Customer_TravelPlan_Itenary_Places.PlaceSysId = TB_Master_Geo_City.CityId
							WHERE TB_Agency_Customer_TravelPlan_Itenary_Places.TPIntSysId IN (SELECT TPIntSysId FROM TB_Agency_Customer_TravelPlan_Itenary WHERE TPSysId = '" . @$packageId . "')
							GROUP BY TB_Agency_Customer_TravelPlan_Itenary_Places.PlaceSysId,TB_Master_Geo_City.Title,TB_Master_Geo_City.Country";
        $result = $this->db->query($sql)->fetchAll();
        return $result;
    }

    public function getCustPackageActivityList($packageId) {
        $sqlPackageActivity = "SELECT TB_Agency_Customer_TravelPlan_Itenary_Events.*,TB_IC_Activity.DurationStr as duration, TB_IC_Activity.*	,TB_Master_Geo_City.Title as City				 
                        FROM TB_Agency_Customer_TravelPlan_Itenary_Events 
                        LEFT JOIN TB_Master_Geo_City ON TB_Agency_Customer_TravelPlan_Itenary_Events.CityId = TB_Master_Geo_City.CityId
						LEFT JOIN TB_IC_Activity ON TB_Agency_Customer_TravelPlan_Itenary_Events.TPActivitySysId=TB_IC_Activity.ActivitySysId
						WHERE TB_Agency_Customer_TravelPlan_Itenary_Events.IsActive = '1' AND TB_Agency_Customer_TravelPlan_Itenary_Events.IsMarkForDel = '0' AND TB_Agency_Customer_TravelPlan_Itenary_Events.TPIntSysId IN (SELECT TPIntSysId FROM TB_Agency_Customer_TravelPlan_Itenary WHERE TPSysId = '" . @$packageId . "')";
        //AND TB_TravelPlan_Itenary_Events.IsActive = '1' AND TB_TravelPlan_Itenary_Events.IsMarkForDel = '0'
        $resultPackageActivity = $this->db->query($sqlPackageActivity)->fetchAll();
        return $resultPackageActivity;
    }

    public function getCustPackageSightseenList($packageId) {
        $sqlPackageActivity = "SELECT TB_Agency_Customer_TravelPlan_SightSeeing.*,TB_IC_SightSeeing.DurationStr as duration, TB_IC_SightSeeing.*
                        FROM TB_Agency_Customer_TravelPlan_SightSeeing 
						LEFT JOIN TB_IC_SightSeeing ON TB_Agency_Customer_TravelPlan_SightSeeing.SSSysId=TB_IC_SightSeeing.SSSysId
						WHERE TB_Agency_Customer_TravelPlan_SightSeeing.IsActive = '1' AND TB_Agency_Customer_TravelPlan_SightSeeing.IsMarkForDelete = '0' AND TB_Agency_Customer_TravelPlan_SightSeeing.InvnItemSysId IN (SELECT TPIntSysId FROM TB_Agency_Customer_TravelPlan_Itenary WHERE TPSysId = '" . @$packageId . "')";

        //AND TB_TravelPlan_SightSeeing.IsActive = '1' AND TB_TravelPlan_SightSeeing.IsMarkForDelete = '0'
        $resultPackageActivity = $this->db->query($sqlPackageActivity)->fetchAll();
        return $resultPackageActivity;
    }

    public function getCustPackageHotelsList($packageId) {
        $sqlPackageActivity = "SELECT T1.Title,T1.CheckInTime,T1.CheckOutTime,T1.RoomNumber,T1.TotalNights,T1.XRefBookingId,T1.AccoBookingId, T3.Title as RoomType,T2.GeoLat,T2.GeoLong,T2.Address,T2.CitySysId,T4.Title as CityName
                FROM TB_Agency_Customer_TravelPlan_Accom T1
                INNER JOIN  TB_IC_Accomdation T2 ON T1.AccoSysId = T2.AccomSysId
                LEFT JOIN   TB_Master_RoomType T3 ON T1.RoomType = T3.RoomType
                LEFT JOIN TB_Master_Geo_City T4 ON T2.CitySysId = T4.CityId
                WHERE T1.IsActive = '1' AND T1.IsMarkForDel = '0' AND T1.TPIntSysId IN (SELECT TPIntSysId FROM TB_Agency_Customer_TravelPlan_Itenary WHERE TPSysId = '" . @$packageId . "')
                ";
        //                GROUP By T1.Title,T1.CheckInTime,T1.CheckOutTime,T1.RoomNumber, T3.Title,T2.GeoLat,T2.GeoLong,T2.Address,T2.CitySysId,T4.Title

        $resultPackageActivity = $this->db->query($sqlPackageActivity)->fetchAll();
        return $resultPackageActivity;
    }

    public function getCustPackageAir($packageId) {
        $sqlPackageActivity = "SELECT TPIntSysId,LocalFromTime,LocalToTime,SourceAirportCode,SourcePlaceSysId,DestAirportCode,
            DestPlaceSysId,FlightNumber,FlightCabinClass,AirlineSysId FROM TB_Agency_Customer_TravelPlan_Air 
                WHERE IsActive = '1' AND IsMarkForDel = '0' AND TPIntSysId IN (SELECT TPIntSysId FROM TB_Agency_Customer_TravelPlan_Itenary WHERE TPSysId = '" . @$packageId . "')
                ";
        //                GROUP By T1.Title,T1.CheckInTime,T1.CheckOutTime,T1.RoomNumber, T3.Title,T2.GeoLat,T2.GeoLong,T2.Address,T2.CitySysId,T4.Title

        $resultPackageActivity = $this->db->query($sqlPackageActivity)->fetchAll();
        return $resultPackageActivity;
    }

    public function getPackageBookingId($packageId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Customer_Trx", array("XRefTrxNo", "TrxDate"));
        if (!empty($packageId)) {
            $select->where("TPSysId =?", $packageId);
        }

        $select->limit(1);
        // echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCustPackageList($id) {
        $select = $this->db->select()
                ->from("TB_Agency_Customer_TravelPlan", "RoomInfoJson")
                ->where("TPSysId =?", $id);
        $result = $this->db->fetchRow($select);
        return $result;
    }
    
    public function getAmountPaidForProposalByTPSysIdNew($TpSysId) {
        $select = $this->db->select()
                ->from(array("tb1" => "TB_Agency_Customer_Trx"), array('SUM(NetAmount) as TotalAmountPaid', 'SUM(ISNULL(TcsFivePercentage,0)) as TcsFivePercentage','SUM(ISNULL(TcsTwentyPercentage,0)) as TcsTwentyPercentage','CurrencyType'))
                ->joinLeft(array('tb2' => "TB_Master_Currency"), "tb2.CurrencyType = tb1.CurrencyType", array('Symbol'))
                ->where("tb1.TPSysId =?", $TpSysId)
                ->where("tb1.IsActive = 1")
                ->where("tb1.TrxStatus = 1")
                ->group(array('tb1.CurrencyType','tb2.Symbol' )); 
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAmountPaidForProposalByTPSysId($TpSysId) {
        $select = $this->db->select()
                ->from(array("tb1" => "TB_Agency_Customer_Trx"), array(new Zend_Db_Expr('SUM(NetAmount) - SUM(ISNULL(TcsFivePercentage,0)) - SUM(ISNULL(TcsTwentyPercentage,0)) as TotalAmountPaid'),'CurrencyType', 'SUM(ISNULL(TcsFivePercentage,0)) as TcsFivePercentage','SUM(ISNULL(TcsTwentyPercentage,0)) as TcsTwentyPercentage', 'ConversionRate'))
                ->joinLeft(array('tb2' => "TB_Master_Currency"), "tb2.CurrencyType = tb1.CurrencyType", array('Symbol'))
                ->where("tb1.TPSysId =?", $TpSysId)
                ->where("tb1.IsActive = 1")
                ->where("tb1.TrxStatus = 1")
                ->group(array('tb1.CurrencyType','tb2.Symbol','ConversionRate')); 
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAmountBasedTrxId($TrxId) {
        $select = $this->db->select()
                ->from("TB_Agency_Customer_Trx", array('NetAmount'))
               // ->where("IsActive = 1")
               // ->where("TrxStatus = 1")
                ->where("TrxSysId =?", $TrxId);
        $result = $this->db->fetchRow($select);
        return isset($result['NetAmount']) ? (float) $result['NetAmount'] : 0;
    }
    public function getCustomerTrxByTrxId($TrxId) {
        $select = $this->db->select()
                ->from("TB_Agency_Customer_Trx", array('NetAmount','IsTCS','TcsFivePercentage','TcsTwentyPercentage','ConversionRate'))
               // ->where("IsActive = 1")
               // ->where("TrxStatus = 1")
                ->where("TrxSysId =?", $TrxId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAmountPaidForProposalOfflinePaymentByTPSysId($TpSysId, $TrxOrdDocId = 0) {
        $select = $this->db->select()
                ->from(array("tb1" => "TB_Agency_Customer_Trx"), array(new Zend_Db_Expr('SUM(NetAmount) as TotalAmountPaid'),'CurrencyType', 'SUM(ISNULL(TcsFivePercentage,0)) as TcsFivePercentage','SUM(ISNULL(TcsTwentyPercentage,0)) as TcsTwentyPercentage'))
                ->joinLeft(array('tb2' => "TB_Master_Currency"), "tb2.CurrencyType = tb1.CurrencyType", array('Symbol'))
                ->where("tb1.IsActive = 1")
                ->where("tb1.TrxStatus = 1")
                ->where("tb1.TPSysId =?", $TpSysId);
        if ($TrxOrdDocId == 19) {
            $select->where("tb1.TrxOrdDocId <> 19");
        }
        $select->group(array('tb1.CurrencyType','tb2.Symbol' )); 
        //echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAmountPaidForProposalOfflinePaymentByTrxId($TpSysId, $TrxId, $TrxOrdDocId = 0) {
        $select = $this->db->select()
                ->from("TB_Agency_Customer_Trx", array(new Zend_Db_Expr('SUM(NetAmount) as TotalAmountPaid')))
                ->where("IsActive = 1")
                ->where("TrxStatus = 1")
                ->where("TPSysId =?", $TpSysId)
                ->where("TrxSysId =?", $TrxId);
        if ($TrxOrdDocId == 19) {
            $select->where("TrxOrdDocId <> 19");
        }
        //echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAmountPaidForProposalOfflinePaymentByTPSysIdAll($TpSysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Customer_Trx", array(new Zend_Db_Expr('SUM(NetAmount) as TotalAmountPaid')))
                ->where("TPSysId =?", $TpSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getOfflinePaymentByTPSysIdList($TpSysId) {
        $select = $this->db->select()
                ->from(array("tb1" => "TB_Agency_Wallet_Trx"), array('TrxDate', 'NetAmount', 'TrxOrdDocId', 'GUID', 'TrxId', 'CurrencyType'))
                ->joinLeft(array('tb2' => "TB_Master_Currency"), "tb2.CurrencyType = tb1.CurrencyType", array('Symbol'))
                ->where("tb1.IsActive = 1")
                ->where("tb1.IsApproved = 1")
                ->where("tb1.XRefTPSysId =?", $TpSysId)
                ->order("tb1.CreateDate DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAgencyCustomerDetailsForBooking($TPSysId) {

        $agenyCustomerTravelPlan_Air = array(new Zend_Db_Expr('DISTINCT(CustomerSysId) as CustomerSysId'), 'MemberSysId', 'IsLeadPax', 'TPSysId','TPPaxSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Pax"), $agenyCustomerTravelPlan_Air);
        //$select->distinct();
        $select->where("tbl.TPSysId = ?", $TPSysId);
        $select->order("tbl.TPPaxSysId ASC");
        $arrResultPax = $this->db->fetchAll($select);
        //echo  "<pre>";print_r($arrResultPax);
        $arrTrevllerDetails = array();
        if (count($arrResultPax) > 0) {

            foreach ($arrResultPax as $resultPax) {
                $intCustomerSysId = trim($resultPax['CustomerSysId']);
                $intMemberSysId = trim($resultPax['MemberSysId']);
                if (!empty($intMemberSysId)) {
                    $agenyCustomerMember = array('MemberSysId', 'Title', 'Relation', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId','CountrySysId', 'PassportIssue', 'PassportExpiry', 'PassportNo', 'PinCode', 'StateOrZoneSysId', 'EmailId', 'Contacts as ContactsMember', 'countrycode as countrycodeMember', 'PanCard as PanCardMember', 'GstNumber as GstNumberMember','paxType');
                    $arrGeoCity = array('Title as CityTitle', 'ContSysId');
                    $arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
                    $agenyCustomer = array('Contacts', 'EmailId as customerEmailId', 'SecondaryEmail', 'SecondaryPhone');
                    $arrState = array("Title as stateTitle");
                    $select = $this->db->select();
                    $select->from(array('tbl' => "TB_Agency_Customer_Members"), $agenyCustomerMember);
                    $select->joinInner(array('tb2' => "TB_Agency_Customer"), "tb2.CustomerSysId = tbl.CustomerSysId", $agenyCustomer);
                    $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tbl.CitySysId", $arrGeoCity);

                    $select->joinLeft(array('tb4' => "TB_Master_Geo_Country"), "tb4.ContId = tb3.ContSysId", $arrGeoCountry);
                    $select->joinLeft(array('tb5' => "TB_Master_Geo_State"), "tb5.StateId = tbl.StateOrZoneSysId", $arrState);
                    $select->where("tbl.MemberSysId = ?", $intMemberSysId);
                    $select->order("tbl.MemberSysId ASC");
                    $result = $this->db->fetchAll($select);
                    $result[0]['IsLeadPax'] = trim($resultPax['IsLeadPax']);
                } else {
                    $agenyCustomer = array('Title', 'Relation', 'Contacts', 'countrycode as countrycodeCustomer', 'EmailId', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId', 'CountrySysId','PassportNo','PassportIssue','PassportExpiry', 'PinCode', 'StateOrZoneSysId', 'SecondaryEmail', 'SecondaryPhone','paxType');
                    $arrGeoCity = array('Title as CityTitle', 'ContSysId');
                    $arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
                    $arrState = array("Title as stateTitle");
                    $select = $this->db->select();
                    $select->from(array('tbl' => "TB_Agency_Customer"), $agenyCustomer);
                    $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tbl.CitySysId", $arrGeoCity);
                    $select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb3.ContId = tb2.ContSysId", $arrGeoCountry);
                    $select->joinLeft(array('tb5' => "TB_Master_Geo_State"), "tb5.StateId = tbl.StateOrZoneSysId", $arrState);
                    $select->where("tbl.CustomerSysId = ?", $intCustomerSysId);
                    $select->limit(1);
                    //echo $select;
                    $result = $this->db->fetchAll($select);
                    $result[0]['MemberSysId'] = 0;
                    $result[0]['IsLeadPax'] = trim($resultPax['IsLeadPax']);
                }

                $arrTrevllerDetails[] = $result;
            }
        }
        // echo "<pre>";print_r($arrTrevllerDetails);exit;
        return $arrTrevllerDetails;
    }

    public function GetTravelPlanCustomerById($id) {
        $agenctcustomer = array('CountrySysId', 'StateOrZoneSysId', 'Address', 'DOB', 'CitySysId', 'AgencySysId', 'CustomerSysId', 'EmailId', 'Title', 'Contacts','countrycode', 'FirstName', 'LastName', 'IsActive', 'IsMarkForDelete', 'PassportNo','PassportIssue','PassportExpiry', 'PinCode', 'Relation', 'PanCard', 'GstNumber', 'SecondaryEmail', 'SecondaryPhone', 'IsB2bAgent',);
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), $agenctcustomer);
        $select->where("tbl.CustomerSysId = ?", $id);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function GetTravelPlanTNC($id) {
        $agenctcustomer = array('TermsAndConditions');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Terms_Conditions"), $agenctcustomer);
        $select->where("tbl.TPIntSysId = ?", $id);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function GetTravelPlanPACKINCLUSION() {
        $agenctcustomer = array('INCLID', 'TITLE');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_MASTER_PACKINCLUSION"), $agenctcustomer);

        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        $select->order("tbl.INCLID");
        //echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAgencyInsuranceData($AgencySysId) {

        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Insurance_Setting"));
        $select->where("tbl.AgencySysId = ?", $AgencySysId);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        //echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }
    public function getProposalAgencyAgentSecurityKey($TPSysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan"),array('TPSysId','AgencySysId','IsB2BProposal'));
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb2.AgencySysId = tbl.AgencySysId", array('SecurityKey','DisplayName','Logo','PrimaryEmail'));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId = tbl.CustomerSysId", array('CustomerSysId', 'EmailId', 'Contacts'));
        $select->joinLeft(array('tb4' => "TB_Agency"), "tb4.AgencySysId = tb3.AgencySysId", array('SecurityKey as AgentSecurityKey','PrimaryUserSysId as AgentUserSysId','PrimaryEmail as AgentPrimaryEmail','ContactInfo as AgentContactInfo'));
        $select->where("tbl.TPSysId = ?", $TPSysId);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

}