<?php

class GTX_Model_Webservice extends Zend_Db_Table_Abstract {

    private $db = NULL;

    function init() {
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }

    public function GUID() {
        if (function_exists('com_create_guid') === true) {
            return trim(com_create_guid(), '{}');
        }
        return sprintf('%04X%04X%04X%04X%04X%04X%04X%04X', mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(16384, 20479), mt_rand(32768, 49151), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535));
    }

    public function updateTable($table, $editData, $where) {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->update($editData, $where);
    }

    public function insertTable($table, $data) {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->insert($data);
    }

    public function getAirlineData($securityKey, $flightType) {
        $dbObj = $this->db;
        $currentDate = date("Y-m-d");
        $select = $dbObj->select()->from(array("T1" => "flightinventory_flightinventory"), array("OnwardFromAirportCode", "OnwardToAirportCode", "InwardToAirportCode", "FlightType"))
                ->joinLeft(array('T3' => "flightinventory_airlineprice"), "T1.id = T3.FlightInventory_id", array())
                ->where("T1.SecurityKey = (?)", $securityKey)
                // ->where("T1.FlightType = (?)", $flightType)
                ->where("T3.CutOffDate >= (?)", $currentDate)
                ->where("T1.IsMarkForDelete != (?)", 1);
        //->where("T3.IsActive = (?)", 1);
        //echo $select; exit;
        $result = $dbObj->fetchAll($select);
        $returnArray = [];
        if (!empty($result)) {
            $k = 0;
            $checkarray = [];
            foreach ($result as $flightInv) {
                $checkKey = trim($flightInv["OnwardFromAirportCode"]) . '' . trim($flightInv["OnwardToAirportCode"]) . '' . trim($flightInv["InwardToAirportCode"]);
                if (!empty($checkKey)) {
                    if (!in_array($checkKey, $checkarray)) {
                        $checkarray[] = $checkKey;
                        $returnArray[$k]["FlightType"] = trim($flightInv["FlightType"]);
                        $returnArray[$k]["FromAirPortCode"] = trim($flightInv["OnwardFromAirportCode"]);
                        $returnArray[$k]["ToAirPortCode"] = trim($flightInv["OnwardToAirportCode"]);
                        if (trim($flightInv["InwardToAirportCode"]) != "") {
                            $returnArray[$k]["InwardToAirPortCode"] = trim($flightInv["InwardToAirportCode"]);
                        }
                        $k++;
                    }
                }
            }
        }
        return $returnArray;
    }

    public function updateInventoryDetail($RefNo, $type) {
        $dbObj = $this->db;
        $currentDateAndTime = date("Y-m-d H:i:s");
        $select = $dbObj->select()->from(array("T1" => "flightinventory_airlinepricehistory"), array("Inventory", "AirLinePrice_id"))
                ->where("InventoryStatus = (?)", 2)
                ->where("HistoryId = (?)", $RefNo);
        $resultArr = $dbObj->fetchRow($select);
        if (is_array($resultArr) && (isset($resultArr['Inventory']) && (int) $resultArr['Inventory'] > 0) && (isset($resultArr['AirLinePrice_id']) && (int) $resultArr['AirLinePrice_id'] > 0)) {
            try {
                $TotalInventory = (int) $resultArr['Inventory'];
                $AirLinePriceId = (int) $resultArr['AirLinePrice_id'];
                if ($type == 1) {
                    $historyIdArray = array(
                        'InventoryStatus' => 1, // 0 No action //1 Sold //2 hold 
                        'UpdatedDate' => $currentDateAndTime,
                        'ReleasedDate' => $currentDateAndTime
                    );
                    $updatedArray = array('Hold' => new Zend_Db_Expr("Hold - $TotalInventory"), 'Sold' => new Zend_Db_Expr("Sold + $TotalInventory"));
                    $WhereHIstoryArray = array('HistoryId =?' => $RefNo);
                    $this->updateTable("flightinventory_airlinepricehistory", $historyIdArray, $WhereHIstoryArray);
                    $WhereArray = array('id =?' => $AirLinePriceId);
                    $this->updateTable("flightinventory_airlineprice", $updatedArray, $WhereArray);
                } else if ($type == 0) {
                    $historyIdArray = array(
                        'InventoryStatus' => 0, // 0 No action //1 Sold //2 hold 
                        'UpdatedDate' => $currentDateAndTime,
                        'ReleasedDate' => $currentDateAndTime
                    );
                    $updatedArray = array('Hold' => new Zend_Db_Expr("Hold - $TotalInventory"), 'CurrentSeat' => new Zend_Db_Expr("CurrentSeat + $TotalInventory"));
                    $WhereHIstoryArray = array('HistoryId =?' => $RefNo);
                    $this->updateTable("flightinventory_airlinepricehistory", $historyIdArray, $WhereHIstoryArray);
                    $WhereArray = array('id =?' => $AirLinePriceId);
                    $this->updateTable("flightinventory_airlineprice", $updatedArray, $WhereArray);
                }
                http_response_code(200);
                $status = True;
                $data = array();
                $message = "";
            } catch (Zend_Exception $e) {
                http_response_code(200);
                $status = False;
                $data = array();
                $message = $e->getMessage();
            }
        } else {
            http_response_code(200);
            $status = False;
            $data = array();
            $message = "";
        }
        return ["Status" => $status, "message" => $message, "data" => $data];
    }

    Public function getBookingIds($AirlinePriceID, $SecurityKey) {
        $dbObj = $this->db;
        $select = $dbObj->select()->from(array("T1" => "flightinventory_flightinventory"), array())
                ->joinLeft(array('T2' => "flightinventory_airlineprice"), "T1.id = T2.FlightInventory_id", array())
                ->joinLeft(array('T3' => "flightinventory_airlinepricehistory"), "T2.id = T3.AirLinePrice_id", array("HistoryId"))
                ->where("T1.SecurityKey = (?)", $SecurityKey)
                ->where("T2.id = (?)", $AirlinePriceID)
                ->where("T3.AirLinePrice_id = (?)", $AirlinePriceID);
        $result = $dbObj->fetchAll($select);
        return $result;
    }

    public function releasedHoldInventory() {
        $returnArray = array();
        $currentDateAndTime = date("Y-m-d H:i:s");
        $dbObj = $this->db;
        try {
            $select = $dbObj->select()->from(array("T1" => "flightinventory_airlinepricehistory"), array('HistoryId'))
                    ->where("T1.InventoryStatus = (?)", 2)
                    ->where("T1.ReleasedDate <= (?)", $currentDateAndTime);
            $resultArr = $dbObj->fetchAll($select);
           // echo "<pre>";print_r($resultArr);exit;
            foreach ($resultArr as $valData) {
                $this->updateInventoryDetail($valData['HistoryId'], 0);
            }
            $returnArray = array(
                "Status" => True,
                "Data" => ""
            );
        } catch (Zend_Exception $e) {
            $returnArray = array(
                "Status" => False,
                "Data" => $e->getMessage()
            );
        }

        return json_encode($returnArray);
    }

    public function checkRetriveUpdate($SecurityKey, $flightType, $FromDate, $id, $adult = 0, $child = 0, $infant = 0) {
        $currentDate = date("Y-m-d");
        $dbObj = $this->db;
        $Pax = $adult + $child + $infant;
        $GUID = $this->GUID();
        $message = "";
        $select = $dbObj->select()->from(array("T1" => "flightinventory_flightinventory"), array())
                ->joinLeft(array('T3' => "flightinventory_airlineprice"), "T1.id = T3.FlightInventory_id", array("IsRefundable", "FromDate", "OnwardGroupPNR", "OnwardAutoTicket", "CutOffDate", "CurrentSeat"))
                ->where("T1.SecurityKey = (?)", $SecurityKey)
                ->where("T1.FlightType = (?)", $flightType)
                ->where("T3.CurrentSeat >= (?)", $Pax)
                ->where("T3.CutOffDate >= (?)", $currentDate);
        $select->where("T3.id >= (?)", $id);
        $select->where("T3.FromDate = (?)", $FromDate);
        $currentDateAndTime = date("Y-m-d H:i:s");
        $resultArr = $dbObj->fetchRow($select);
        if (is_array($resultArr) && (isset($resultArr['CurrentSeat']) && (int) $resultArr['CurrentSeat'] >= (int) $Pax)) {
            try {
                $status = True;
                http_response_code(200);
                $WhereArray = array('id =?' => $id);
                $updatedArray = array('Hold' => new Zend_Db_Expr("Hold + $Pax"), 'CurrentSeat' => new Zend_Db_Expr("CurrentSeat - $Pax"));
                $this->updateTable("flightinventory_airlineprice", $updatedArray, $WhereArray);
                $historyIdArray = array(
                    'ReferenceNo' => 0,
                    'Adult' => $adult,
                    'Child' => $child,
                    'Infant' => $infant,
                    'Inventory' => $Pax,
                    'InventoryStatus' => 2, // 0 No action //1 Sold //2 hold 
                    'CreatedDate' => $currentDateAndTime,
                    'UpdatedDate' => $currentDateAndTime,
                    'ReleasedDate' => date('Y-m-d H:i:s', (strtotime($currentDateAndTime) + 900)),
                    'AirLinePrice_id' => $id,
                    'IsMarkForDelete' => '0',
                    'HistoryId' => $GUID
                );
                $this->insertTable("flightinventory_airlinepricehistory", $historyIdArray);
                $data = array("RefNo" => $GUID);
            } catch (Zend_Exception $e) {
                http_response_code(500);
                $status = False;
                $data = array();
                $message = $e->getMessage();
            }
        } else {
            http_response_code(200);
            $status = False;
            $data = array();
        }
        return ["Status" => $status, "message" => $message, "data" => $data];
    }

    public function getFlightInventorry($securityKey, $flightType, $ofac, $otac, $fromData = null, $SearchDate = null, $CurrentSeat = null, $ifac = null, $itac = null, $InwardDate = null) {
        $currentDate = date("Y-m-d");
        $dbObj = $this->db;
        $select = $dbObj->select()->from(array("T1" => "flightinventory_flightinventory"), array('Currency', 'CheckInLuggage', 'InCabinLuggage', 'Fare as FareData', 'CancellationPolicy', 'InternalRemarks', 'id', 'SecurityKey', 'FlightType', 'Supplier'))
                ->joinLeft(array('T2' => "flightinventory_airlineinventory"), "T1.id = T2.FlightInventory_id", array("id as aid", "AirLines", "FlightNumber", "FromCityCode", "ToCityCode", "Stops", "TIME_FORMAT(DepartureTime,'%H:%i:%s') as DepartureTime", "EstimateHours", "TIME_FORMAT(ArrivalTime,'%H:%i:%s') as ArrivalTime", "FlightInventory_id"))
                ->joinLeft(array('T3' => "flightinventory_airlineprice"), "T1.id = T3.FlightInventory_id", array("id as pid", 'CurrentSeat', "IsRefundable", "FromDate", "OnwardGroupPNR", "OnwardAutoTicket", "Seats", "Hold", "MinimumBooking", "CutOffDate", "Fare", "Taxes", "MarkupType", "Markup", "FlightInventory_id"))
                ->where("T1.SecurityKey = (?)", $securityKey)
                ->where("T1.FlightType = (?)", $flightType)
                ->where("T1.OnwardFromAirportCode = (?)", $ofac)
                ->where("T1.OnwardToAirportCode = (?)", $otac)
                ->where("T1.IsMarkForDelete != (?)", 1)
                ->where("T3.IsActive = (?)", 1)
                ->where("T3.CutOffDate >= (?)", $currentDate);
        if ($ifac != null && $itac != null && $InwardDate != null) {
            $select->where("T1.InwardFromAirportCode = (?)", $ofac)
                    ->where("T1.InwardToAirportCode = (?)", $otac);
        }
        if ($InwardDate) {
            $select->where("T3.InDate >= (?)", $InwardDate);
        }
        if ($fromData) {
            $select->where("T3.FromDate >= (?)", $fromData);
        }
		if ($SearchDate) {
            $select->where("T3.FromDate = (?)", $SearchDate);
        }
		if ($CurrentSeat) {
            $select->where("T3.CurrentSeat >= (?)", $CurrentSeat);
        }
		//echo $select;die; 
        $result = $dbObj->fetchAll($select);
        //echo "<pre>";print_r($result);exit;
        $returnArray = array();
        if (!empty($result)) {
            $k = 0;
            $checkarray = [];
            foreach ($result as $flightInv) {
                if (!in_array($flightInv["id"], $checkarray)) {
                    $checkarray[] = $flightInv["id"];
                    $returnArray[$k]["id"] = $flightInv["id"];
                    $returnArray[$k]["SecurityKey"] = $flightInv["SecurityKey"];
                    $returnArray[$k]["FlightType"] = $flightInv["FlightType"];
                    $returnArray[$k]["Supplier"] = $flightInv["Supplier"];
                    $returnArray[$k]["Currency"] = $flightInv["Currency"];
                    $returnArray[$k]["CheckInLuggage"] = $flightInv["CheckInLuggage"];
                    $returnArray[$k]["InCabinLuggage"] = $flightInv["InCabinLuggage"];
                    $returnArray[$k]["Fare"] = $flightInv["FareData"];
                    $returnArray[$k]["CancellationPolicy"] = $flightInv["CancellationPolicy"];
                    $returnArray[$k]["InternalRemarks"] = $flightInv["InternalRemarks"];
                    $returnArray[$k]["airlineinventories"] = [];
                    $h = 0;
                    $checkInv = array();
                    foreach ($result as $flightInvArr) {
                        if ($flightInv["id"] == $flightInvArr["FlightInventory_id"]) {
                            if (!in_array($flightInvArr["aid"], $checkInv)) {
                                $checkInv[] = $flightInvArr["aid"];
                                $returnArray[$k]["airlineinventories"][$h]["id"] = $flightInvArr["aid"];
                                $returnArray[$k]["airlineinventories"][$h]["AirLines"] = $flightInvArr["AirLines"];
                                $returnArray[$k]["airlineinventories"][$h]["FlightNumber"] = $flightInvArr["FlightNumber"];
                                $returnArray[$k]["airlineinventories"][$h]["FromCityCode"] = $flightInvArr["FromCityCode"];
                                $returnArray[$k]["airlineinventories"][$h]["ToCityCode"] = $flightInvArr["ToCityCode"];
                                $returnArray[$k]["airlineinventories"][$h]["Stops"] = $flightInvArr["Stops"];
                                $returnArray[$k]["airlineinventories"][$h]["DepartureTime"] = date('H:i',strtotime($flightInvArr["DepartureTime"]));
                                $returnArray[$k]["airlineinventories"][$h]["EstimateHours"] = $flightInvArr["EstimateHours"];
                                $returnArray[$k]["airlineinventories"][$h]["ArrivalTime"] = date('H:i',strtotime($flightInvArr["ArrivalTime"]));
                                $h++;
                            }
                        }
                    }
                    $returnArray[$k]["airlineprices"] = [];
                    $g = 0;
                    $checkPrice = array();
                    foreach ($result as $flightInvPriceArr) {
                        if ($flightInv["id"] == $flightInvPriceArr["FlightInventory_id"]) {
                            if (!in_array($flightInvPriceArr["pid"], $checkPrice)) {
                                $checkPrice[] = $flightInvPriceArr["pid"];
                                $returnArray[$k]["airlineprices"][$g]["id"] = $flightInvPriceArr["pid"];
                                $returnArray[$k]["airlineprices"][$g]["IsRefundable"] = $flightInvPriceArr["IsRefundable"];
                                $returnArray[$k]["airlineprices"][$g]["FromDate"] = $flightInvPriceArr["FromDate"];
                                $returnArray[$k]["airlineprices"][$g]["OnwardGroupPNR"] = $flightInvPriceArr["OnwardGroupPNR"];
                                $returnArray[$k]["airlineprices"][$g]["OnwardAutoTicket"] = $flightInvPriceArr["OnwardAutoTicket"];
                                $returnArray[$k]["airlineprices"][$g]["Seats"] = $flightInvPriceArr["Seats"];
                                $returnArray[$k]["airlineprices"][$g]["Hold"] = $flightInvPriceArr["Hold"];
                                $returnArray[$k]["airlineprices"][$g]["CurrentSeat"] = $flightInvPriceArr["CurrentSeat"];
                                //$returnArray[$k]["airlineprices"][$g]["Hold"] = $flightInvPriceArr["Hold"];
                                $returnArray[$k]["airlineprices"][$g]["CutOffDate"] = $flightInvPriceArr["CutOffDate"];
                                $returnArray[$k]["airlineprices"][$g]["Fare"] = $flightInvPriceArr["Fare"];
                                $returnArray[$k]["airlineprices"][$g]["Taxes"] = $flightInvPriceArr["Taxes"];
                                $returnArray[$k]["airlineprices"][$g]["MarkupType"] = $flightInvPriceArr["MarkupType"];
                                $returnArray[$k]["airlineprices"][$g]["Markup"] = $flightInvPriceArr["Markup"];
                                $g++;
                            }
                        }
                    }
                    $k++;
                }
            }
        }
        return $returnArray;
    }

}