<?php

class Travel_Model_TblFlight {
    
    protected $db = NULL;
    
    public $intId = NULL;
    
    public $strName = NULL;
    
    public $intCountry = NULL;
    
    public $intState = NULL;
    
    public $intCity = NULL;
    
    public $strAddress1 = NULL;
    
    public $strAddress2 = NULL;
    
    public $intRating  = NULL;
    
    public $location = NULL;
    
    public $description = NULL;
    
    public $postalCode = NULL;
    
    public $services = NULL;
    
    public $strHotelImage = NULL;
            
    
    /* Standard, Deluxe and Luxury */
    public $strCategory = NULL;
    
    /* From database standard, deluxe and luxury */
    public $intCategoryId = NULL;
    
    public $isBackend = 'Y';
    
     /*  Common to all models */
    public $strCondition  = NULL;
    
    public $deleted = 'N';
    
    public $status = '1';
        
    public $strSelectedView = '';
    
    public $intQueryOffset = 0;
    
    public $intListPerPage = 10;
    
    public $strOrderBy = 'hotel_name' ;
    
    public $isVerified = 'Y';
    
    public $imageServerUrl = '';
    /***************************************/
    
    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 getAllFlightList($intLoggedinAgencyId) {
        $sql = " SELECT  InvnItemSysId, XRefFlightSysId, IsItTwoWay, IsItParent, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty,CostCurrency, TB_Master_Currency.Symbol AS Currency, TotalCost, TB_IC_Supplier.SupplierName AS SupplierType, TB_Master_Geo_City.Title AS SupplierCityName, TB_MP_Inventory_Air.StartPlaceSysId, TB_MP_Inventory_Air.DestPlaceSysId, "
                . "TB_IC_Trans_Air.FlightNumber, TB_Master_Airline.Title As Airline, TB_Master_Airline.AirlineLogo, TMSP.Title AS FromPlace, TMP.Title AS ToPlace, "
                . "TB_IC_Trans_Air.StopCount, CAST(  CASE WHEN TB_IC_Trans_Air.IsDirect = 0 THEN 'Direct' ELSE 'Connecting' END AS char(15)) AS flightType, TB_MP_Inventory_Air.IsActive "
                . "FROM TB_MP_Inventory_Air "
                . "JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId=TB_IC_Trans_Air.AirInvenSysId "
                . "JOIN TB_Master_Currency ON TB_MP_Inventory_Air.CostCurrency=TB_Master_Currency.CurrencyType "
                . "JOIN TB_IC_Supplier ON TB_MP_Inventory_Air.SupplierSysId=TB_IC_Supplier.SupplierSysId "
                . "JOIN TB_Master_Geo_City ON TB_Master_Geo_City.CityId=TB_IC_Supplier.CitySysId "
                . "JOIN TB_Master_Airline ON TB_IC_Trans_Air.AirlineSysId=TB_Master_Airline.AirlineSysId "
                . "JOIN TB_Master_Geo_City AS TMSP ON TB_MP_Inventory_Air.StartPlaceSysId=TMSP.CityId "
                . "JOIN TB_Master_Geo_City AS TMP ON TB_MP_Inventory_Air.DestPlaceSysId=TMP.CityId "
                . "WHERE 1=1 ";
        
        if(!empty($intLoggedinAgencyId)) {
            $sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
        } 
        $sql .= " ORDER BY TB_MP_Inventory_Air.IsItTwoWay DESC, TB_MP_Inventory_Air.UpdateDate DESC, IsItParent";
        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }
    
    public function getFlightList($strFlightNo) {
        $sql = "Select top 1 RTRIM(LTRIM(FlightNumber)) FlightNumber, AirInvenSysId, LocalFromTime From TB_IC_Trans_Air where 1 = 1" ;
        
        if(!empty($strFlightNo)) {
            $sql .= " AND FlightNumber LIKE '$strFlightNo%' ";
        }
        $sql .= " AND LocalFromTime >= '".@date('Y-m-d')."' ";
        $sql .= " order by LocalFromTime ";
        
        //echo $sql; die;
        
        $rowset = $this->db->query($sql)->fetchAll();
        
        if(count($rowset) >  0 ){
            foreach($rowset as $row) {
                
                $FromDateTime = (array) $row['LocalFromTime'];
                $date_time = date("d/m/Y",strtotime($FromDateTime['date']));
                $response[] = array('value' => $row['FlightNumber'], 'label' => $row['FlightNumber'], 'flightId' => $row['AirInvenSysId'], 'flightDate' => $date_time);
            }
        } else {
            $response[] = array('flightId'=>0,'value' => 'Add New Flight' , 'label' => 'Add New Flight');   
        }
        return $response;
    }
    
    
    public function getAllPlacelList() {
        $sql = "Select CityId as PlaceSysId, Title From TB_Master_Geo_City where 1 = 1 AND IsActive='1' AND Code IS NOT NULL";
        $sql .= " ORDER BY Title ";
        
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }
    
    public function getPlacelDetailsByPlaceId($placeId) {
        $sql = "Select CityId as PlaceSysId, Code, Title From TB_Master_Geo_City where 1 = 1 AND IsActive='1'";
        if(!empty($placeId)) {
            $sql .= " AND CityId = '".$placeId."' ";
        }
        //echo $sql; die;
        $rowset = $this->db->query($sql)->fetch();
        return $rowset;
    }
    
    
    public function addFlightDetails($data) {
        $this->db->insert('TB_IC_Trans_Air', $data);
        return $this->db->lastInsertId();
    }
    
    
    public function getFlightSourcePlaceList($flightId) {
        
        $sql1 = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air.LocalFromTime as DepartureTime, IsDirect From TB_IC_Trans_Air JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air.SourcePlaceSysId=cty.CityId where 1 = 1  ";
        
        if(!empty($flightId)) {
            $sql1 .= " AND TB_IC_Trans_Air.AirInvenSysId = '".$flightId."' ";
        }
        //echo $sql1; die;
        
        $fetchData = $this->db->query($sql1)->fetchAll();
        
        if($fetchData[0]['IsDirect']==0)
        {
            $sql = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air_Stop.DepartureTime From TB_IC_Trans_Air_Stop JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air_Stop.SourcePlaceSysId=cty.CityId where 1 = 1  "; 
            if(!empty($flightId)) {
                $sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '".$flightId."' ";
            }
            $fetchData = $this->db->query($sql)->fetchAll();
        } 
        
        //$sql .= " ORDER BY TB_IC_Trans_Air.SourcePlaceSysId "; 
//       echo $sql; 
//        die;
        return $fetchData;
    }
    
    public function getFlightDestinationPlaceList($flightNumber) {
        
        $sql2 = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air.LocalToTime as ArrivalTime, IsDirect From TB_IC_Trans_Air JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air.DestPlaceSysId=cty.CityId where 1 = 1  ";
        
        if(!empty($flightNumber)) {
            $sql2 .= " AND TB_IC_Trans_Air.AirInvenSysId = '".$flightNumber."' ";
        }
        $fetchData = $this->db->query($sql2)->fetchAll();
        
        if($fetchData[0]['IsDirect']==0)
        {
            $sql = "Select cty.CityId as PlaceSysId , cty.Title, TB_IC_Trans_Air_Stop.ArrivalTime "
                    . "From TB_IC_Trans_Air_Stop JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air_Stop.DestPlaceSysId=cty.CityId where 1 = 1" ;

            if(!empty($flightNumber)) {
                $sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '".$flightNumber."' ";
            }
            $fetchData = $this->db->query($sql)->fetchAll();
        }
        //$sql .= " ORDER BY TB_IC_Trans_Air.DestPlaceSysId "; 
        
        return $fetchData;
    }
    
    
    public function getFlightChangeDetailsList($FlightNumber,$OnwardSourcePlaceId,$OnwardDestinationPlaceId) {
        $sql = "Select TB_IC_Trans_Air.StopCount, CAST(  CASE WHEN TB_IC_Trans_Air.IsDirect = 1 THEN 'Direct' ELSE 'Connecting' END AS char(15)) AS flightType "
                . "From TB_IC_Trans_Air WHERE 1 = 1" ;
        
        if(!empty($FlightNumber)) {
            $sql .= " AND TB_IC_Trans_Air.AirInvenSysId = '".$FlightNumber."' ";
        }
        
        if(!empty($OnwardSourcePlaceId)) {
            $sql .= " AND TB_IC_Trans_Air.SourcePlaceSysId = '".$OnwardSourcePlaceId."' ";
        }
        
        if(!empty($OnwardDestinationPlaceId)) {
            $sql .= " AND TB_IC_Trans_Air.DestPlaceSysId = '".$OnwardDestinationPlaceId."' ";
        }
        $resData = $this->db->query($sql)->fetch();
        //echo $sql; die;
        if($resData['StopCount']=="")
        {
            $sql = "Select count(AirInvenSysId) as total_count From TB_IC_Trans_Air_Stop WHERE 1 = 1 " ;
        
            if(!empty($FlightNumber)) {
                $sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '".$FlightNumber."' ";
            }

            if(!empty($OnwardSourcePlaceId)) {
                $sql .= " AND TB_IC_Trans_Air_Stop.SourcePlaceSysId = '".$OnwardSourcePlaceId."' ";
            }

            if(!empty($OnwardDestinationPlaceId)) {
                $sql .= " AND TB_IC_Trans_Air_Stop.DestPlaceSysId = '".$OnwardDestinationPlaceId."' ";
            }
            $resDataStop = $this->db->query($sql)->fetch();
            if($resDataStop['total_count']>0)
            {
               $response = array('StopCount' => 0, 'flightType' => 'Direct'); 
            } 
            //echo "<pre>"; print_r($resData);die;
            $resData = $response;
        }
        
        //$sql .= " ORDER BY TB_IC_Trans_Air.DestPlaceSysId "; 
        
        return $resData;
    }
    
    public function addFlightInventory($data) {
        try {
            $this->db->insert('TB_MP_Inventory_Air', $data);
            return $this->db->lastInsertId('TB_MP_Inventory_Air');
        } catch(Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }
    
    
    public function getAllFlightInventoryList($intLoggedinAgencyId) {
        $sql = " SELECT  TOP 10 InvnItemSysId, XRefFlightSysId, AgencySysId, IsItTwoWay, IsItParent, PairItemSysId, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty, CostCurrency, TB_Master_Currency.Symbol AS Currency, TotalCost, TB_IC_Supplier.SupplierName AS SupplierType, TB_MP_Inventory_Air.StartPlaceSysId, TB_MP_Inventory_Air.DestPlaceSysId, "
                . "TB_IC_Trans_Air.FlightNumber, TB_Master_Airline.Title As Airline, TB_Master_Geo_City.Title AS FromPlace, TMP.Title AS ToPlace, "
                . "TB_IC_Trans_Air.StopCount, CAST(  CASE WHEN TB_IC_Trans_Air.IsDirect = 1 THEN 'Direct' ELSE 'Connecting' END AS char(15)) AS flightType, TB_MP_Inventory_Air.IsActive "
                . "FROM TB_MP_Inventory_Air "
                . "JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId=TB_IC_Trans_Air.AirInvenSysId "
                . "JOIN TB_Master_Currency ON TB_MP_Inventory_Air.CostCurrency=TB_Master_Currency.CurrencyType "
                . "JOIN TB_IC_Supplier ON TB_MP_Inventory_Air.SupplierSysId=TB_IC_Supplier.SupplierSysId "
                . "JOIN TB_Master_Airline ON TB_IC_Trans_Air.AirlineSysId=TB_Master_Airline.AirlineSysId "
                . "JOIN TB_Master_Geo_City ON TB_MP_Inventory_Air.StartPlaceSysId=TB_Master_Geo_City.CityId "
                . "JOIN TB_Master_Geo_City AS TMP ON TB_MP_Inventory_Air.DestPlaceSysId=TMP.CityId "
                . "WHERE 1=1 ";
        
        if(!empty($intLoggedinAgencyId)) {
            $sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
        } 
        $curr_date = date('Y-m-d');
        //$sql .= " AND FromDate >='".$curr_date."' ";
        $sql .= " ORDER BY TB_MP_Inventory_Air.UpdateDate DESC ";
        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }
    
    public function getFlightTimeInventoryDetailsByFlightId($XRefFlightSysId,$StartPlaceSysId,$DestPlaceSysId) {
        $sql = " SELECT AirInvenSysId, LocalFromTime as DepartureTime, LocalToTime as ArrivalTime FROM TB_IC_Trans_Air WHERE 1=1 ";
        if(!empty($XRefFlightSysId)) {
            $sql .= " AND AirInvenSysId = " . $XRefFlightSysId;
        }   
        if(!empty($StartPlaceSysId)) {
            $sql .= " AND SourcePlaceSysId = " . $StartPlaceSysId;
        }  
        if(!empty($DestPlaceSysId)) {
            $sql .= " AND DestPlaceSysId = " . $DestPlaceSysId;
        }  
        //echo $sql;exit;
        return $this->db->query($sql)->fetch();
    }
    
    
    public function getFlightInventoryList($InvnItemSysId, $intLoggedinAgencyId)
    {
        $sql = " SELECT * FROM TB_MP_Inventory_Air WHERE 1 = 1  ";
        if(!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        } 
        if(!empty($intLoggedinAgencyId)) {
            $sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
        } 
        $sql .= " ORDER BY InvnItemSysId DESC ";
        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }
    
    public function getStandardMarkupsAir($intAgencySysId) {
        
        $sql = " SELECT AgencySysId, TB_MP_StdMarkup_Air.MPType AS MPType_id,  TB_Master_MPType.Title AS MPType, CustGrSysId, 
                Currency, StdMarkUpPer, TaxPer, DiscountPer, DiscountMsg,IsActive 
                FROM TB_MP_StdMarkup_Air INNER JOIN TB_Master_MPType  
                    ON TB_MP_StdMarkup_Air.MPType = TB_Master_MPType.MPType 
                WHERE 1 = 1 ";
        if(!empty($intAgencySysId)) {
            $sql .= " AND AgencySysId = " . $intAgencySysId;
        }
        //echo $sql; die;
        return $this->db->query($sql)->fetchAll();
    }
    
    
    public function getAirMarketPlacesByAgencySysId($intAgencySysId) {
        $sql = "Select MPType From TB_MP_Inventory_Air_Alloc where 1 = 1";
        
        if(!empty($intAgencySysId)) {
            $sql .= " AND AgencySysId = '".$intAgencySysId."' ";
        }
        $sql .= " ORDER BY MPType";
        
        $rowset = $this->db->query($sql)->fetchAll();
        if(count($rowset) >  0 ){
            foreach($rowset as $row) {
                $response[] = $row['MPType'];
            }
        } else {
           $response = array(); 
        } 
        return array_values($response);
    }
    
    
    public function getCustomizedMarkups($InvnItemSysId, $MPType = '', $intLoggedinAgencyId = '') {
        
        $sql = " SELECT InvnItemSysId, TB_MP_Inventory_Air_Alloc.MPType AS MPType_id, TB_Master_MPType.Title as MPType, 
                CustGrSysId, ItemType, AgencySysId, AllocateQnt, IsVirtualInven
                ,SoldQnt, RetrunQty, NetQnt, BlockQnt, MarkUpType, MarkUp, Currency, Price, Tax, Discount, DiscountType, DiscountAmount,
                 DiscountMsg, NetPrice, IsPublish, PublishDate, TB_MP_Inventory_Air_Alloc.Details, RateRuleSysId, TCDocSysId, ValidTill,
                 ApproveDate, CreateDate, UpdateDate, IsApproved, IsMarkForDel, IsActive
                 FROM TB_MP_Inventory_Air_Alloc INNER JOIN TB_Master_MPType 
                 ON TB_MP_Inventory_Air_Alloc.MPType = TB_Master_MPType.MPType
                 WHERE 1 = 1  " ;
        
        if(!empty($MPType)) {
            $sql .= " AND TB_MP_Inventory_Air_Alloc.MPType = " . $MPType;
        }
        
        if(!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }
        
        if(!empty($intLoggedinAgencyId)) {
            $sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
        }
        
        $sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";
        
        return $this->db->query($sql)->fetchAll();
    }
    
    public function getStandardMarkupsPolicies($intAgencySysId) {
        
        $sql = " SELECT AgencySysId, ArtifectSysId, Title, Writeup FROM TB_MP_Inventory_Air_Artifect_Std WHERE 1 = 1  ";
        if(!empty($intAgencySysId)) {
            $sql .= " AND AgencySysId = " . $intAgencySysId;
        }
        $sql .= " ORDER BY AgencySysId";
        return $this->db->query($sql)->fetch();
    }
    
    public function addMarkupSettings($data) {
        $this->db->insert('TB_MP_Inventory_Air_Alloc', $data);
        return $this->db->lastInsertId();
    }
    
    public function updateIsItParentFlight($intLastInsertInwardId,$lastInvnItemSysId)
    {
        $where = array('InvnItemSysId = ? ' => $lastInvnItemSysId);
        $update = array('PairItemSysId'=>$intLastInsertInwardId);
        $this->db->update('TB_MP_Inventory_Air', $update, $where);
    }
    
    public function deleteCustomizedMarkupSetting($InvnItemSysId, $MPType, $intLoggedinAgencyId)
    {
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'MPType = ? ' => $MPType, 'AgencySysId = ? ' => $intLoggedinAgencyId);
        $this->db->delete('TB_MP_Inventory_Air_Alloc', $where);
    }
    
    
    public function getCustomizedMarkupsPolicies($InvnItemSysId) {
        
        $sql = " SELECT * FROM TB_MP_Inventory_Air_Artifect WHERE 1 = 1 ";
        
        if(!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }
        
        $sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";
        //echo $sql; die;
        return $this->db->query($sql)->fetch();
    }
    
    public function addMarkupPoliciesSettings($data) {
        $this->db->insert('TB_MP_Inventory_Air_Artifect', $data);
        return $this->db->lastInsertId();
    }
    
    public function editMarkupPoliciesSettings($update, $InvnItemSysId)
    {
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId);
        $this->db->update('TB_MP_Inventory_Air_Artifect', $update, $where);
    }
    
    public function getAllFlightInventoryDetailsList($InvnItemSysId, $intLoggedinAgencyId) {
        $sql = " SELECT InvnItemSysId, XRefFlightSysId, IsItTwoWay, IsItParent, PairItemSysId, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty,CostCurrency as Currency, TB_MP_Inventory_Air.TrxCurrency, TB_Master_Currency.Symbol AS CurrencySymbol, TB_MP_Inventory_Air.SupplierSysId, TB_IC_Supplier.SupplierName AS SupplierType, TotalCost, TB_MP_Inventory_Air.StartPlaceSysId, TB_MP_Inventory_Air.DestPlaceSysId, "
                . "ltrim(rtrim(TB_IC_Trans_Air.FlightNumber)) FlightNumber, TB_IC_Trans_Air.StopCount, CAST(  CASE WHEN TB_IC_Trans_Air.IsDirect = 1 THEN 'Direct' ELSE 'Connectiing' END AS char(15)) AS flightType, TB_Master_Geo_City.Title AS FromPlace, TMP.Title AS ToPlace "
                . "FROM TB_MP_Inventory_Air "
                . "JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId=TB_IC_Trans_Air.AirInvenSysId "
                . "JOIN TB_Master_Currency ON TB_MP_Inventory_Air.CostCurrency=TB_Master_Currency.CurrencyType " 
                . "JOIN TB_IC_Supplier ON TB_MP_Inventory_Air.SupplierSysId=TB_IC_Supplier.SupplierSysId "
                . "JOIN TB_Master_Geo_City ON TB_MP_Inventory_Air.StartPlaceSysId=TB_Master_Geo_City.CityId "
                . "JOIN TB_Master_Geo_City AS TMP ON TB_MP_Inventory_Air.DestPlaceSysId=TMP.CityId "
                . "WHERE 1=1 ";
        if(!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }
        if(!empty($intLoggedinAgencyId)) {
            $sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
        }
        //echo $sql;exit;
        
        return $this->db->query($sql)->fetch();
    }
    
    public function updateFlightInventory($InvnItemSysId, $updateOnwardData)
    {
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId);
        $this->db->update('TB_MP_Inventory_Air', $updateOnwardData, $where);
    }
    
    public function updateFlightSaleInventory($InvnItemSysId, $actionValue, $intLoggedinAgencyId)
    {
        $update = array('IsActive' => $actionValue);
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId,'AgencySysId = ? ' => $intLoggedinAgencyId);
        $this->db->update('TB_MP_Inventory_Air', $update, $where);
    }
    
    public function updateIsPairFlightSaleInventory($InvnItemSysId, $actionValue, $intLoggedinUserId)
    {
        $update = array('IsActive' => $actionValue);
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId,'AgencySysId = ? ' => $intLoggedinUserId);
        $this->db->update('TB_MP_Inventory_Air', $update, $where);
    }
    
    
    public function getCurrencyConversionPrice($agentBaseCurrency,$inventoryCurrency) {
        $sql = " SELECT * FROM TB_IC_CurrencyConversion WHERE 1=1 AND IsActive='1' ";
        
        if(!empty($agentBaseCurrency)) {
            $sql .= " AND FCurrencyType = " . $agentBaseCurrency;
        }
        if(!empty($inventoryCurrency)) {
            $sql .= " AND TCurrencyType = " . $inventoryCurrency;
        }
        
        return $this->db->query($sql)->fetch();
    }
    
    
    public function getCurrencyConversionPaddingPrice($agentBaseCurrency) {
        $sql = " SELECT * FROM TB_System_CurrencyExcPadding WHERE 1=1 AND IsActive='1' ";
        
        if(!empty($agentBaseCurrency)) {
            $sql .= " AND CurrencyType = " . $agentBaseCurrency;
        }
        
        return $this->db->query($sql)->fetch();
    }
    
    public function getSearchResult($data) {
        
        $sql = "   SELECT XRefFlightSysId, FlightNumber FROM TB_MP_Inventory_Air
                        INNER JOIN TB_IC_Trans_Air ON  TB_MP_Inventory_Air.XRefFlightSysId = TB_IC_Trans_Air.AirInvenSysId ";
        
        $result = $this->db->query($sql)->fetchAll();
        return $result;        
    }
    
    
    public function getFilteredList() {
        
        $sql = "   SELECT XRefFlightSysId, FlightNumber FROM TB_MP_Inventory_Air
                        INNER JOIN TB_IC_Trans_Air ON  TB_MP_Inventory_Air.XRefFlightSysId = TB_IC_Trans_Air.AirInvenSysId ";
        
        if(!empty($this->strCondition)) {
            $sql .= $this->strCondition;
        }
        
        $result = $this->db->query($sql)->fetchAll();
        return $result;        
    }
    
    
    public function getFlightSourceCities() {
        $sql = "  SELECT DISTINCT TB_IC_Trans_Air.sourcePlaceSysId, sourceCity.Title as SourceCityName, SourceAirportCode as AirportCode, sourceCountry.Title as Country
                    from TB_IC_Trans_Air INNER JOIN TB_MP_Inventory_Air
                    on TB_IC_Trans_Air.AirInvenSysId = TB_MP_Inventory_Air.InvnItemSysId
                    LEFT JOIN TB_Master_Geo_City as sourceCity on  TB_IC_Trans_Air.sourcePlaceSysId = sourceCity.CityId 
                    LEFT JOIN TB_Master_Geo_Country as sourceCountry on sourceCity.ContSysId = sourceCountry.ContId
                    " ;
        $result = $this->db->query($sql)->fetchAll();
        return $result;       
    }
    
    public function getFlightDestinationCities() {
        $sql =  "   SELECT DISTINCT TB_IC_Trans_Air.DestPlaceSysId, destinationCity.Title as DestinationCityName, DestAirportCode as AirportCode, 
                    destinationCountry.Title as Country
                    from TB_IC_Trans_Air INNER JOIN TB_MP_Inventory_Air
                    on TB_IC_Trans_Air.AirInvenSysId = TB_MP_Inventory_Air.InvnItemSysId
                    LEFT JOIN TB_Master_Geo_City as destinationCity on  TB_IC_Trans_Air.DestPlaceSysId = destinationCity.CityId
                    LEFT JOIN TB_Master_Geo_Country as destinationCountry on  destinationCity.ContSysId = destinationCountry.ContId
                " ;
        $result = $this->db->query($sql)->fetchAll();
        return $result;       
    }
    
    public function createAgencyCustomer($data) {
        
        $ContNo  = $data['Contacts'];
        $EmailId = $data['EmailId'];
        
        $intAgencySysId = $data['AgencySysId'];
        
        /* Check if already added or not */
        
        $sql = " SELECT CustomerSysId FROM TB_Agency_Customer WHERE EmailId = '" .$EmailId ."' AND AgencySysId = " . $intAgencySysId;
        $result = $this->db->query($sql)->fetch();
        
        if( count($result) == 0 ) {
            
            $sql = " SELECT CustomerSysId FROM TB_Agency_Customer WHERE Contacts = '" . $ContNo ."' AND AgencySysId = " . $intAgencySysId;
            
            $result = $this->db->query($sql)->fetch();
        
            if( count($result) == 0 ) {
                $intCustomerId = $this->getNextAgencyCustomerId($intAgencySysId);
                $data['CustomerSysId'] = $intCustomerId;
                $data['JoinDate']      = $currentDate   = date('Y-m-d H:i:s');
                $this->db->insert('TB_Agency_Customer', $data);
                return $this->db->lastInsertId('TB_Agency_Customer');
            }else {
                return $result['CustomerSysId'];
            }
            
        } else {
            return $result['CustomerSysId'];
        }
        
        return 0;
    }
    
    public function createAgencyCustomerMembers($arrCustomerMembers, $AgencySysId, $intCustomerSysId) {
        
      
        if( count($arrCustomerMembers) == 0  || empty($AgencySysId) ||  empty($intCustomerSysId)) {
            throw new Exception('Information missing');
        }
        
        foreach($arrCustomerMembers as $member) {
            
            $intMemberSysId          = $this->getNextAgencyCustomerMemberId($AgencySysId,$intCustomerSysId);
            
            $member['AgencySysId']   = $AgencySysId;
            $member['CustomerSysId'] = $intCustomerSysId;
            $member['MemberSysId']   = $intMemberSysId;
            $member['JoinDate']        = $currentDate   = date('Y-m-d H:i:s');
            $this->db->insert('TB_Agency_Customer_Members', $member);
            
        }
        
        return true;
    }
    
    
    
    public function getNextAgencyCustomerId($intAgencySysId) {
        $sql = "   select (isnull(MAX(CustomerSysId),0) + 1 ) as CustomerSysId from TB_Agency_Customer where AgencySysId = " . $intAgencySysId;
        $result = $this->db->query($sql)->fetch();
        return $result['CustomerSysId'];
    }
    
    public function getNextAgencyCustomerMemberId($intAgencySysId, $intCustomerSysId) {
        $sql = "   select (isnull(MAX(MemberSysId),0) + 1 ) as MemberSysId from TB_Agency_Customer_Members "
                . " where ( AgencySysId = " . $intAgencySysId . " AND CustomerSysId = " . $intCustomerSysId ." ) ";
        $result = $this->db->query($sql)->fetch();
        return $result['MemberSysId'];
    }
    
    public function booking($data) {
        $this->db->insert('TB_Agency_Customer_Trx', $data);
    }
    
    public function getTransactionDetail($AgencySysId, $CustomerSysId, $TrxSysId) {
        $sql= " SELECT * FROM TB_Agency_Customer_Trx "
                . " WHERE AgencySysId = " . $AgencySysId ." AND CustomerSysId = " . $CustomerSysId ." AND TrxSysId = '" .$TrxSysId ."'"; 
        return $this->db->query($sql)->fetchAll();
    }
    
    
    public function getAllFlightInventoryMarkupList($InvnItemSysId, $intLoggedinUserAgencySysId) {
        
        $sql = " SELECT * FROM TB_MP_Inventory_Air_Alloc WHERE 1 = 1 ";
        
        if(!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }
        if(!empty($intLoggedinUserAgencySysId)) {
            $sql .= " AND AgencySysId = '".$intLoggedinUserAgencySysId."' ";
        }
        
        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }
    
    
    public function cloneMPInventoryFlight($InvnItemSysId,$updateCopyData, $intNewInvnItemSysId) {
        
        $currentDate = date('Y-m-d H:i:s');
        if($intNewInvnItemSysId!=0){
           $PairItemSysId = $intNewInvnItemSysId; 
        } else {
           $PairItemSysId = 0;  
        }
        
        $sql = " INSERT INTO TB_MP_Inventory_Air (InventoryType, XRefFlightSysId, IsItTwoWay, IsItParent, PairItemSysId
                ,AgencySysId, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty
                ,BlockQty, SoldQty, ReturnQty, NetInHandQty, CostCurrency
                ,TrxCurrency, SupplierSysId, TotalCost, Tax, SaleAmount,DefaultPrice
                ,Brief,IfAnyOffer,OfferMsg,OfferImage
                ,StartPlaceSysId, DestPlaceSysId, SearchTags, IxFixedDateInven
                ,CreatorSysId
                ,CreateDate
                ,UpdateDate
                ,IsActive
                ,IsMarkForDelete
                ,IsApproved) 
                
                SELECT InventoryType, XRefFlightSysId, IsItTwoWay, IsItParent, '".$PairItemSysId."'
                ,AgencySysId, EconomyType, '".$updateCopyData['strDateOnward']."', '".$updateCopyData['strDateOnward']."', '".$updateCopyData['PurchasedQty']."', '".$updateCopyData['PublishQty']."'
                ,BlockQty, SoldQty, ReturnQty, '".$updateCopyData['NetInHandQty']."', '".$updateCopyData['CostCurrency']."'
                ,TrxCurrency, '".$updateCopyData['SupplierSysId']."', '".$updateCopyData['TotalCost']."', Tax, SaleAmount,DefaultPrice
                ,Brief,IfAnyOffer,OfferMsg,OfferImage
                ,'".$updateCopyData['StartPlaceSysId']."', '".$updateCopyData['DestPlaceSysId']."', SearchTags, IxFixedDateInven
                ,CreatorSysId
                ,'".$currentDate."'
                ,'".$currentDate."'
                ,'0'
                ,'0'
                ,'0' FROM TB_MP_Inventory_Air WHERE InvnItemSysId = " . $InvnItemSysId;
        
        //echo $sql; die;
        
        $this->db->query($sql);
        return $this->db->lastInsertId('TB_MP_Inventory_Air');
    }
    
}