<?php

/**
 * Digitalus CMS
 *
 * LICENSE
 *
 */
class Travel_Model_AgencyCustomerAuth {

    protected $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 init() {
        parent::init();
        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $auth = Zend_Auth::getInstance();
        $authStorage = $auth->getStorage();
        $authStorage->read();
    }

    public function getUserList() {
        $select = $this->db->select()
                ->from("TB_Agency_Customer");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function authenticateSecurityKey($username = null, $password = null, $AgencySysId = null) {
        $fields = array("IsActive", "EmailId", "FirstName", "LastName", "CustomerSysId", "AgencySysId", "UserName", "Password", "Contacts");
        $select = $this->db->select()
                ->from(array('tbl' => "TB_Agency_Customer"), $fields)
                ->joinLeft(array('tb2' => "TB_Agency"), "tb2.AgencySysId=tbl.AgencySysId", array(""))
                ->where("tbl.EmailId =?", $username)
                ->where("tbl.Password =?", $password)
                ->where("tb2.SecurityKey =?", $AgencySysId)
                ->where("tbl.IsB2BAgent =?", 0);
        //echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function authenticate($username = null, $password = null, $AgencySysId = null) {
        $select = $this->db->select()
                ->from(array('tbl' => "TB_Agency_Customer"),array('EmailId', 'FirstName', 'LastName', 'CustomerSysId','AgencySysId','UserName', 'Password', 'Contacts', 'IsEnabledWallet', 'countrycode', 'Title', 'IsActive'))
                ->joinLeft(array('tb2' => "TB_Agency"), "tb2.AgencySysId=tbl.AgencySysId", array("SecurityKey"))
                ->joinLeft(array('tb3' => "TB_Agency_Mapping"), "tb2.AgencySysId=tb3.AgencySysId", array("IsEnabledB2CWallet"))
                ->where("tbl.EmailId =?", $username)
                ->where("tbl.Password =?", $password)
                ->where("tbl.AgencySysId =?", $AgencySysId)
                ->where("tbl.IsActive =?", 1)
                ->where("tb2.IsActive =?", 1)
                ->where("tb2.IsMarkForDelete =?", 0)
                ->where("tbl.IsB2BAgent =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getcustomerprofile($CustomerSysId, $AgencySysId) {
        $fields = array('AgencySysId', 'CustomerSysId', 'EmailId', 'CONVERT(VARCHAR(24),DOB,103) as DOB', 'Contacts', 'UserName', 'FirstName', 'LastName', 'Password', 'CONVERT(VARCHAR(24),PasswordExpiryDate,103) as PasswordExpiryDate', 'CONVERT(VARCHAR(24),MarriageAnniversary,103) as MarriageAnniversary', 'PassportNo', 'CONVERT(VARCHAR(24),PassportExpiry,103) as PassportExpiry', 'CitySysId', 'CountrySysId', 'PinCode', 'Address', 'countrycode', 'IsMarried', 'Title', 'GstCompany', 'GstNumber','IsEnabledWallet');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), $fields);
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb2.AgencySysId=tbl.AgencySysId", array("SecurityKey"));
        $select->joinLeft(array('tb3' => "TB_Agency_Mapping"), "tb2.AgencySysId=tb3.AgencySysId", array("IsEnabledB2CWallet"));
        $select->where('tbl.CustomerSysId = ?', $CustomerSysId);
        $select->where('tbl.AgencySysId = ?', $AgencySysId); 
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getcustomerinfobyemailmobile($CustomerEmail, $CustomerMobile, $AgencySysId) {
        $fields = array('AgencySysId', 'CustomerSysId', 'EmailId', 'CONVERT(VARCHAR(24),DOB,103) as DOB', 'Contacts', 'UserName', 'FirstName', 'LastName', 'Password', 'CONVERT(VARCHAR(24),PasswordExpiryDate,103) as PasswordExpiryDate', 'CONVERT(VARCHAR(24),MarriageAnniversary,103) as MarriageAnniversary', 'PassportNo', 'CONVERT(VARCHAR(24),PassportExpiry,103) as PassportExpiry', 'CitySysId', 'CountrySysId', 'PinCode', 'Address', 'countrycode', 'IsMarried');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), $fields);
        $select->where('tbl.EmailId = ?', $CustomerEmail);
        $select->where('tbl.Contacts = ?', $CustomerMobile);
        $select->where('tbl.AgencySysId = ?', $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getcustomerinfobyemail($email, $AgencySysId) {
        $fields = array('AgencySysId', 'CustomerSysId', 'EmailId', 'CONVERT(VARCHAR(24),DOB,103) as DOB', 'Contacts', 'UserName', 'FirstName', 'LastName', 'Password', 'CONVERT(VARCHAR(24),PasswordExpiryDate,103) as PasswordExpiryDate', 'CONVERT(VARCHAR(24),MarriageAnniversary,103) as MarriageAnniversary', 'PassportNo', 'CONVERT(VARCHAR(24),PassportExpiry,103) as PassportExpiry', 'CitySysId', 'CountrySysId', 'PinCode', 'Address', 'countrycode', 'IsMarried');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), $fields);
        $select->where('tbl.EmailId = ?', $email);
        $select->where('tbl.AgencySysId = ?', $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getcustomerinfobymobile($CustomerMobile, $AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"));
        $select->where('tbl.Contacts = ?', $CustomerMobile);
        $select->where('tbl.AgencySysId = ?', $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function agentLogin($userName, $password) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_User"));
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb2.AgencySysId=tbl.AgencySysId", array("DisplayName"));
        $select->where('tbl.EmailId = ?', $userName);
        $select->where('tbl.Password = ?', md5($password));
        $select->where('tbl.IsB2BAgent = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tb2.MasterAgencySysId = ?', 0);
        $select->where('tb2.IsApproved = ?', 1);
        $select->where('tb2.IsActive = ?', 1);
        $select->where('tb2.IsMarkForDelete = ?', 0);
        /*  echo $select;
          exit; */
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkandgetcustomerdetail($mobileNo, $sequiryKey) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), array('EmailId', 'FirstName', 'LastName', 'CustomerSysId', 'AgencySysId', 'UserName', 'Contacts', 'IsActive'));
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb2.AgencySysId=tbl.AgencySysId", array(''));
        $select->where('tbl.Contacts = ?', $mobileNo);
        $select->where('tb2.SecurityKey = ?', $sequiryKey);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencySysId($sequiryKey) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency"), array('AgencySysId', 'IsActive'));
        $select->where('tbl.SecurityKey = ?', $sequiryKey);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkMobileInAgency($CustomerMobile, $AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer"));
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb2.AgencySysId=tb1.AgencySysId", array("SecurityKey"));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.Contacts = ?', substr($CustomerMobile, -10));
        $select->where('tb2.IsActive = ?', 1);
        $select->where('tb2.IsMarkForDelete = ?', 0);
        $result = $this->db->query($select)->fetch();
        return $result;
    }

    public function getCustomerDetail($customerName, $AgencySysId, $customerType = 0) {
        $CustomerMobile = trim($CustomerMobile);
        $select = "SELECT CustomerSysId, FirstName, LastName, EmailId, Contacts, countrycode FROM TB_Agency_Customer AS tb2 WHERE (tb2.AgencySysId = $AgencySysId) AND (tb2.IsB2BAgent = $customerType) AND (tb2.IsMarkForDelete = 0)  AND (lower(tb2.FirstName) LIKE '%$customerName%' OR lower(tb2.LastName) LIKE '%$customerName%')";
        $result = $this->db->query($select)->fetchAll();
        return $result;
    }

    public function changepasswordcustomer($password, $CustomerSysId, $AgencySysId) {
        $updatewhere = array('AgencySysId = ? ' => $AgencySysId, 'CustomerSysId = ? ' => $CustomerSysId);
        $updateData = array('Password' => md5($password));
        $updatequery = $this->db->update('TB_Agency_Customer', $updateData, $updatewhere);
        return $updatequery;
    }

    public function getcitybycountryid($ContSysId) {
        $fields = array('CityId', 'Title', 'ContSysId', 'Country');
        $select = $this->db->select()
                ->from(array('tbl' => "TB_Master_Geo_City"), $fields);
        $select->where('tbl.ContSysId = ?', $ContSysId);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.IsApproved = ?', 1);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function updatecustomerprofile($data) {
        $updatewhere = array('AgencySysId = ? ' => $data['AgencySysId'], 'CustomerSysId = ? ' => $data['CustomerSysId']);
        $updateData = array(
            'Title' => $data['Title'],
            'FirstName' => $data['FirstName'],
            'LastName' => $data['LastName'],
            'Contacts' => $data['contacts'],
            'PassportNo' => $data['PassportNo'],
            'PassportExpiry' => $data['PassportExpiry'],
            'DOB' => $data['DOB'],
            'MarriageAnniversary' => $data['MarriageAnniversary'],
            'UserName' => $data['FirstName'] . ' ' . $data['LastName'],
            'CountrySysId' => $data['country'],
            'GstCompany' => $data['CompanyName'],
            'GstNumber' => $data['GSTNo'],
            'CitySysId' => $data['city'],
        );
        try {
            $updatequery = $this->db->update('TB_Agency_Customer', $updateData, $updatewhere);
        } catch (Zend_Exception $e) {
            echo $e->getMessage();
            exit;
        }

        return $updatequery;
    }

    public function getcustomerbooking($agencySysID, $customerId) {

        $agency_customer_trx = array('TPSysId', 'CustomerSysId', 'PaidAmount', 'CurrencyType', 'TrxDate', 'TrxUpdatedOn', 'Amount', 'NetAmount', 'PaidAmount', 'CurrencyType', 'TrxFee');
        $agency_customer_travel_plan = array('PlanBookingId', 'Title', 'StartDate', 'RoomInfoJson', 'DestinationPlaces', 'MasterTPSysId', 'StatusType', 'PlanType', 'Price', 'NetPrice', 'CustomerSysId', 'SupplierSysId', 'Readymade', 'PackSpecType', 'VersionId', 'XServiceTaxAmount', 'AgencyMarkUp', 'MarkUp', 'AgentServiceTaxAmount', 'AgentsCustomServiceTaxAmount', 'ServiceTaxOnCustomDiscount', 'CustomDiscount');
        $agency_customer = array('AgencySysId', 'EmailId', 'Contacts', 'FirstName', 'LastName');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Trx"), $agency_customer_trx);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan"), "tb2.TPSysId=tb1.TPSysId", $agency_customer_travel_plan);
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb1.CustomerSysId", $agency_customer);
        $select->joinLeft(array('tb5' => "TB_Agency"), "tb5.AgencySysId=tb2.AgencySysId", array('TrxCurrency', 'PrimaryContactNo', 'PrimaryMobileNo'));
        $select->joinLeft(array('tb6' => "TB_IC_CurrencyConversion"), "tb6.TCurrencyType=tb5.TrxCurrency AND tb6.FCurrencyType = tb2.CurrencyType", array('Rate as ToBaseCurrencyExchangeRate'));
        //added filter condition
        //end of filter condition		
        $select->where('tb1.AgencySysId = ?', $agencySysID);
        $select->where('tb2.AgencySysId = ?', $agencySysID);
        $select->where('tb3.AgencySysId = ?', $agencySysID);
        $select->where('tb3.CustomerSysId =?', $customerId);
        $select->where('tb1.CustomerSysId =?', $customerId);
        $select->order("tb1.TrxSysId DESC");
        //$select->getPart(Zend_Db_Select::WHERE);
        //echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getcustomerbookingFlight($agencySysID, $customerId) {

        $arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId');
        $arrCustomerTravelPlanItenary = array('TPIntSysId');
        $arrCustomerTravelPlanAir = array(
            'FlightNumber', 'TotalCost', 'Cost', 'GTXMarkup', 'AgencyCommission', 'AgencyMarkUp', 'AgentServiceTaxAmount', 'SourceAirportCode', 'DestAirportCode',
            'FromUTCTime', 'ToUTCTime', 'XRefBookingId', 'LocalToTime as returnDate', 'AgentsCustomServiceTaxAmount', 'MarkUp'
        );
        $arrCustomerTrx = array('TrxDate AS bookingDate');
        $arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
        $arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName');
        $arrStatusType = array('TPStatus as BookingStatusName', 'Details as BookingStatusDetails');
        $arrCurrencyType = array('Symbol as Symbol', 'Title as CurrencyTitle', 'Icon as CurrencyIcon');

        $select = $this->db->select();
        $select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
        $select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
        $select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
        $select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational'));
        $select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
        $select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
        $select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
        $select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
        $select->joinLeft(array('STS' => "TB_Master_TravelPlan_Status"), "STS.TPStatusSysId = TP.StatusType", $arrStatusType);
        $select->joinLeft(array('CURR' => "TB_Master_Currency"), "CURR.CurrencyType = TP.CurrencyType", $arrCurrencyType);
        if (!empty($agencySysID)) {
            $select->where("TP.AgencySysId = ?", $agencySysID);
        }
        $select->where("AC.CustomerSysId = ?", $customerId);
        $select->where("TP.PlanType = ?", 1);
        $select->where('TP.StatusType IN(?)', array(17, 10, 9, 18, 19));
        $select->order('TP.TPSysId DESC');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function customerInvoiceData($intTPSysId, $AgencySysId = NULL) {

        $arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId', 'RoomInfoJson');
        $arrCustomerTravelPlanItenary = array('TPIntSysId');
        $arrCustomerTravelPlanAir = array(
            'VersionId', 'FlightNumber', 'Cost', 'TotalCost', 'GTXMarkup', 'AgencyCommission', 'AgencyMarkUp',
            'AgentServiceTaxAmount', 'MarkUp', 'AgentsCustomServiceTaxAmount', 'XServiceTaxAmount', 'GTXServiceTaxAmount', 'Taxes',
            'SourceAirportCode', 'DestAirportCode', 'XRefBookingId', 'APIBookingRes'
        );
        $arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
        $arrCustomerTrx = array('TrxDate AS bookingDate');
        $arrAgency = array('AgencySysId', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo');
        $arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
        $arrGC = array('Title AS CityTitle', 'Country');
        $arrStatusType = array('TPStatus as BookingStatusName', 'Details as BookingStatusDetails');
        $select = $this->db->select();
        $select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
        $select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
        $select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
        $select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'FromUTCTime', 'ToUTCTime',));
        $select->joinLeft(array('TMA' => "TB_Master_Airline"), "TMA.AirlineSysId = TPA.AirlineSysId", $arrAirLine);
        $select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
        $select->joinLeft(array('WTRX' => "TB_Agency_Wallet_Trx"), "WTRX.XRefTPSysId = TP.TPSysId", array("InvoiceId"));
        $select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
        $select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
        $select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
        $select->joinLeft(array('STS' => "TB_Master_TravelPlan_Status"), "STS.TPStatusSysId = TP.StatusType", $arrStatusType);
        if (!empty($AgencySysId)) {
            $select->where("TP.AgencySysId = ?", $AgencySysId);
        } else if (!empty($this->intLoggedinUserAgencySysId)) {
            $select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
        }

        $select->where("TP.PlanType = ?", 1);
        $select->where('TP.TPSysId = ?', $intTPSysId);
        //echo $select->where('TPA.InvnItemSysId IN (28533,28573)');
        //echo $select->limit(1); exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAgencyCustomerDetailsForBooking($id, $TPSysId) {

        $agenyCustomerTravelPlan_Air = array(new Zend_Db_Expr('DISTINCT(CustomerSysId) as CustomerSysId'), 'MemberSysId', 'IsLeadPax', 'TPSysId');
        $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.TPSysId DESC");
        $arrResultPax = $this->db->fetchAll($select);
        //ECHO "<PRE>";print_r($arrResultPax);
        $arrTrevllerDetails = array();
        if (count($arrResultPax) > 0) {

            foreach ($arrResultPax as $result) {
                $intCustomerSysId = trim($result['CustomerSysId']);
                $intMemberSysId = trim($result['MemberSysId']);
                if (!empty($intMemberSysId)) {
                    $agenyCustomerMember = array('MemberSysId', 'Title', 'Relation', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId');
                    $arrGeoCity = array('Title as CityTitle');
                    $arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
                    $agenyCustomer = array('Contacts', 'EmailId');
                    $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->joinInner(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->where("tbl.MemberSysId = ?", $intMemberSysId);
                    $select->order("tbl.MemberSysId ASC");
                    $result = $this->db->fetchAll($select);
                } else {
                    $agenyCustomer = array('Title', 'Relation', 'Contacts', 'EmailId', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId');
                    $arrGeoCity = array('Title as CityTitle');
                    $arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
                    $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->where("tbl.CustomerSysId = ?", $intCustomerSysId);
                    $select->limit(1);
                    //echo $select;
                    $result = $this->db->fetchAll($select);
                    $result[0]['MemberSysId'] = 0;
                }

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

    public function getInterNationalFlightByMasterId($strIds) {

        $arrTransAPIAir = array(
            'TBQAirInvenSysId', 'AirInvenSysId', 'SourceAirportCode', 'DestAirportCode', 'FlightNumber',
            'AirlineSysId', 'CurrencyType', 'FlyingMinutes as FlightDuration', 'FromUTCTime as OriginDepartureTime', 'ToUTCTime as DestArrivalTime',
            'StopCount', 'IsRefundable', 'NoOfSeatAvailable', 'ApiResultIndex', 'apiTraceId', 'FareClass'
        );
        $arrTAAF = array('PublishedFare', 'CommissionEarned', 'PLBEarned', 'IncentiveEarned', 'OfferedFare');
        $arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');

        // For Api Flights DB Results...
        $select = $this->db->select();
        $select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAPIAir);
        $select->joinLeft(array('t2' => 'TB_IC_Trans_API_Air_Fare'), 't2.TBOAirInvenAPISysId = t1.AirInvenSysId ', $arrTAAF);
        $select->joinLeft(array('t3' => 'TB_Master_Airline'), 't3.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
        $select->where("t1.IsInternational = ?", 1);
        $select->where("t1.AirInvenSysId IN( $strIds )");
        $select->where("t1.IsActive = ?", 1);
        //echo $select->where("t1.IsExpired = ?",0);
        //$select->order("t1.TBQAirInvenSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetTravelPlanStatusName($type = NULL, $TPStatusSysId = NULL) {
        $objTravPlanStatus = new Travel_Model_CRM_TravelPlanStatus();
        $result = $objTravPlanStatus->GetTravelPlanStatusByTypeName($type, $TPStatusSysId);
        return $result;
    }

    public function GetCustomerMembersById($agencySysID, $CRMCustSysId) {
        $select = $this->db->select();
        $select->from('TB_Agency_Customer_Members');
        $select->where("TB_Agency_Customer_Members.CustomerSysId = ?", $CRMCustSysId);
        $select->where('TB_Agency_Customer_Members.AgencySysId = ?', $agencySysID);
        //$memberId?$select->where('TB_Agency_Customer_Members.MemberSysId = ?', $memberId):'';
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function viewFlightBookingDetails($intTPSysId, $AgencySysId = NULL) {

        $arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId');
        $arrCustomerTravelPlanItenary = array('TPIntSysId');
        $arrCustomerTravelPlanAir = array(
            'VersionId', 'FlightNumber', 'Cost', 'TotalCost', 'GTXMarkup', 'AgencyCommission', 'AgencyMarkUp', 'AgentServiceTaxAmount', 'MarkUp', 'AgentsCustomServiceTaxAmount', 'SourceAirportCode', 'DestAirportCode',
            'XRefBookingId', 'APIBookingRes'
        );
        $arrCustomerTrx = array('TrxDate AS bookingDate');
        $arrAgency = array('Title AS agentName', 'PrimaryContactName');
        $arrStatusType = array('TPStatus as BookingStatusName', 'Details as BookingStatusDetails');

        $select = $this->db->select();
        $select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
        $select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
        $select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
        $select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'FromUTCTime', 'ToUTCTime'));
        $select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
        $select->join(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
        $select->joinLeft(array('STS' => "TB_Master_TravelPlan_Status"), "STS.TPStatusSysId = TP.StatusType", $arrStatusType);
        if (!empty($AgencySysId)) {
            $select->where("TP.AgencySysId = ?", $AgencySysId);
        }

        $select->where("TP.PlanType = ?", 1);
        $select->where('TP.TPSysId = ?', $intTPSysId);
        //$select->limit(1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function deactivateCustomerProfile($CustomerSysId, $AgencySysId) {
        $updatewhere = array('AgencySysId = ? ' => $AgencySysId, 'CustomerSysId = ? ' => $CustomerSysId);
        $updateData = array('IsActive' => 0);
        $updatequery = $this->db->update('TB_Agency_Customer', $updateData, $updatewhere);
        return $updatequery;
    }

}