<?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("Tbl" => "flightinventory_agencytoagencymapping"), array())
                ->joinLeft(array('T1' => "flightinventory_flightinventory"), "Tbl.SecurityKey = T1.SecurityKey", array("OnwardFromAirportCode", "OnwardToAirportCode", "InwardToAirportCode", "FlightType"))
                ->joinLeft(array('T3' => "flightinventory_airlineprice"), "T1.id = T3.FlightInventory_id", array())
                ->where("Tbl.MasterSecurityKey = (?)", $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);
        $status = False;
        $data = array();
        $message = "No Action";
        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) {
                    $select1 = $dbObj->select()->from(array("T1" => "flightinventory_airlineprice"), array("CurrentSeat", 'Hold', 'Sold', 'Seats'));
                    $select1->where("T1.id >= (?)", $AirLinePriceId);
                    $resultArr11 = $dbObj->fetchRow($select1);
                    $CurrentSeat = $resultArr11['CurrentSeat'] ? (int) $resultArr11['CurrentSeat'] : 0;
                    if ($CurrentSeat >= 0) {
                        $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);
                        http_response_code(200);
                        $status = True;
                        $data = array();
                        $message = "Inventory Sold";
                    } else {
                        http_response_code(200);
                        $status = False;
                        $data = array();
                        $message = "Check Inventory";
                    }
                } 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 = "Inventory Released";
                }
            } 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, $Type) {
        //echo $Type;
        $dbObj = $this->db;
        $select = $dbObj->select()->from(array("Tbl" => "flightinventory_agencytoagencymapping"), array())
                ->joinLeft(array('T1' => "flightinventory_flightinventory"), "Tbl.SecurityKey = T1.SecurityKey", array())

                // $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", "Adult", "Child", "CAdult", "CChild", "Infant", "CInfant", "IsCancel"))
                ->where("Tbl.MasterSecurityKey = (?)", $SecurityKey)
                ->where("T2.id = (?)", $AirlinePriceID)
                ->where("T3.AirLinePrice_id = (?)", $AirlinePriceID);
        if ($Type == 'cancel') {
            $select->where("T3.IsCancel = 1");
        } else {
            $select->where("T3.InventoryStatus = 1");
            $select->where("T3.IsCancel = 0 or T3.IsCancel = 2");
        }
        //echo $select; exit;
        $result = $dbObj->fetchAll($select);
        return $result;
    }

    public function releasedHoldInventory() {
        date_default_timezone_set('Asia/Kolkata');
        $returnArray = array();
        $currentDateAndTime = date("Y-m-d H:i:s");
        //echo "<br>";
        $minutes_to_add = 10;
        $time = new DateTime($currentDateAndTime);
        $time->sub(new DateInterval('PT' . $minutes_to_add . 'M'));
        $stamp = $time->format('Y-m-d H:i');
        //echo "<br>";
        $dbObj = $this->db;
        try {
            $select = $dbObj->select()->from(array("T1" => "flightinventory_airlinepricehistory"), array('HistoryId'))
                    ->where("T1.InventoryStatus = (?)", 2)
                    ->where("T1.ReleasedDate <= (?)", $stamp);
            // echo $select; exit;
            $resultArr = $dbObj->fetchAll($select);

            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;
        $GUID = $this->GUID();
        $message = "";
        $select = $dbObj->select()->from(array("Tbl" => "flightinventory_agencytoagencymapping"), array())
                ->joinLeft(array('T1' => "flightinventory_flightinventory"), "Tbl.SecurityKey = T1.SecurityKey", array())
                //$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", "InfantFare", "InfantMarkup", "MarkupType", "Fare", "Taxes", "Markup"))
                ->where("Tbl.MasterSecurityKey = (?)", $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);
                $TotalMarkup = $TotalCost = $TotalInfantMarkup = $TotalInfantCost = 0;
                if ($infant > 0) {
                    $TotalInfantCost = (float) $resultArr['InfantFare'];
                    $TotalInfantMarkup = (float) $resultArr['InfantMarkup'];
                }
                if ($Pax > 0) {
                    if ($resultArr['MarkupType'] == 2) {
                        $TotalCost = (float) $resultArr['Fare'] + (float) $resultArr['Taxes'];
                        $TotalMarkup = (($TotalCost * (float) $resultArr['Markup']) / 100);
                    } else {
                        $TotalCost = $resultArr['Fare'] + $resultArr['Taxes'];
                        $TotalMarkup = $resultArr['Markup'];
                    }
                }
                $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,
                    'TotalCost' => $TotalCost,
                    'TotalMarkup' => $TotalMarkup,
                    'TotalInfantCost' => $TotalInfantCost,
                    'TotalInfantMarkup' => $TotalInfantMarkup
                );
                $historyAirlinePrice = $this->insertTable("flightinventory_airlinepricehistory", $historyIdArray);
                $data = array("RefNo" => $GUID);

                $select1 = $dbObj->select()->from(array("T1" => "flightinventory_flightinventory"), array())
                        ->joinLeft(array('T3' => "flightinventory_airlineprice"), "T1.id = T3.FlightInventory_id", array("CurrentSeat", 'Hold', 'Sold', 'Seats'))
                        ->where("T1.SecurityKey = (?)", $SecurityKey)
                        ->where("T1.FlightType = (?)", $flightType)
                        ->where("T3.CutOffDate >= (?)", $currentDate);
                $select1->where("T3.id >= (?)", $id);
                $select1->where("T3.FromDate = (?)", $FromDate);
                $currentDateAndTime = date("Y-m-d H:i:s");
                $resultArr11 = $dbObj->fetchRow($select1);
                $CurrentSeat = $resultArr11['CurrentSeat'] ? (int) $resultArr11['CurrentSeat'] : 0;
                $Hold = $resultArr11['Hold'] ? (int) $resultArr11['Hold'] : 0;
                $Sold = $resultArr11['Sold'] ? (int) $resultArr11['Sold'] : 0;
                $Seats = $resultArr11['Seats'] ? (int) $resultArr11['Seats'] : 0;
                $tempSeat = $Hold + $Sold + $CurrentSeat;
                if ($tempSeat != $Seats || $CurrentSeat < 0) {
                    $updatedArray = array('Hold' => new Zend_Db_Expr("Hold - $Pax"), 'CurrentSeat' => new Zend_Db_Expr("CurrentSeat + $Pax"));
                    $this->updateTable("flightinventory_airlineprice", $updatedArray, $WhereArray);
                    $WhereHistoryArray = array('id =?' => $historyAirlinePrice);
                    $updatedHistoryArray = array('InventoryStatus' => 0);
                    $this->updateTable("flightinventory_airlinepricehistory", $updatedHistoryArray, $WhereHistoryArray);
                    http_response_code(500);
                    $status = False;
                    $data = array();
                    $message = "Seats Data mismatch";
                }
            } 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 H:i");
        $dbObj = $this->db;
        $select = $dbObj->select()->from(array("Tbl" => "flightinventory_agencytoagencymapping"), array())
                ->joinLeft(array('T1' => "flightinventory_flightinventory"), "Tbl.SecurityKey = T1.SecurityKey", 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", "FromTerminal", "ToTerminal", "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", "B2CMarkup", "FlightInventory_id", "InfantFare", "InfantMarkup"))
                ->where("Tbl.MasterSecurityKey = (?)", $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);
        }
        file_put_contents("log/tttt.txt", json_encode($select));
        $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]["FromTerminal"] = $flightInvArr["FromTerminal"];
                                $returnArray[$k]["airlineinventories"][$h]["ToTerminal"] = $flightInvArr["ToTerminal"];
                                $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"];
                                $returnArray[$k]["airlineprices"][$g]["B2CMarkup"] = $flightInvPriceArr["B2CMarkup"];
                                $returnArray[$k]["airlineprices"][$g]["InfantFare"] = $flightInvPriceArr["InfantFare"];
                                $returnArray[$k]["airlineprices"][$g]["InfantMarkup"] = $flightInvPriceArr["InfantMarkup"];
                                $g++;
                            }
                        }
                    }
                    $k++;
                }
            }
        }
        return $returnArray;
    }

    public function getBookingHistory($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("*"))
                ->where("T1.SecurityKey = (?)", $SecurityKey)
                ->where("T2.id = (?)", $AirlinePriceID)
                ->where("T3.InventoryStatus IN (?) ", array(1, 2))
                ->where("T3.AirLinePrice_id = (?)", $AirlinePriceID);
        $result = $dbObj->fetchAll($select);
        return $result;
    }

    public function getCancellationRules($SecurityKey, $id, $days = 0) {
        $dbObj = $this->db;
        $select1 = $dbObj->select()->from(array("T1" => "flightinventory_cancellationrules"), array('*'))
                ->where("T1.SecurityKey = (?)", $SecurityKey)
                ->where("T1.AirLinePrice_id = (?)", $id);
        $result1 = $dbObj->fetchAll($select1);
        if (!empty($result1)) {
            $select = $dbObj->select()->from(array("T1" => "flightinventory_cancellationrules"), array('*'))
                    ->where("T1.SecurityKey = (?)", $SecurityKey)
                    ->where("T1.AirLinePrice_id = (?)", $id)
                    ->where("T1.Days <= (?)", $days)
                    ->order("T1.Days DESC")
                    ->limit(1);
            $result = $dbObj->fetchAll($select);
        } else {
            $select11 = $dbObj->select()->from(array("T1" => "flightinventory_cancellationrules"), array('*'))
                    ->where("T1.SecurityKey = (?)", $SecurityKey)
                    ->where("T1.AirLinePrice_id is NULL")
                    ->where("T1.Days <= (?)", $days)
                    ->order("T1.Days DESC")
                    ->limit(1);
            $result = $dbObj->fetchAll($select11);
        }
        return $result;
    }

    public function getCancellationAmount($SecurityKey, $BookingId, $Adult = 0, $Child = 0, $Infant = 0, $type = 'check', $CancellationType = 1) {
        $dbObj = $this->db;
        $select = $dbObj->select()->from(array("T1" => "flightinventory_airlinepricehistory"), array('TotalCost', 'TotalMarkup', 'TotalInfantCost', 'TotalInfantMarkup', 'Adult', 'Child', 'Infant', 'IsCancel', 'CAdult', 'CChild', 'CInfant', 'TotalAdult' => new Zend_Db_Expr("Adult - CAdult"), 'TotalChild' => new Zend_Db_Expr("Child - CChild"), 'TotalInfant' => new Zend_Db_Expr("Infant - CInfant")))
                ->joinLeft(array('T2' => "flightinventory_airlineprice"), "T1.AirLinePrice_id = T2.id", array('id', 'FromDate', 'IsRefundable', 'Fare', 'Taxes', 'MarkupType', 'Markup', 'B2CMarkup', 'InfantFare', 'InfantMarkup'))
                ->joinLeft(array('T3' => "flightinventory_flightinventory"), "T3.id = T2.FlightInventory_id", array())
                ->where("T3.SecurityKey = (?)", $SecurityKey)
                ->where("T1.HistoryId = (?) ", $BookingId)
                ->where("T1.InventoryStatus = (?)", 1)
                ->having("TotalAdult >= (?)", $Adult)
                ->having("TotalChild >= (?)", $Child)
                ->having("TotalInfant >= (?)", $Infant);
        //echo $select; exit;
        $result = $dbObj->fetchAll($select);
        if (!empty($result) && isset($result[0]['IsRefundable']) && $result[0]['IsRefundable'] == 1) {
            $AirlineId = $result[0]['id'];
            date_default_timezone_set('Asia/Kolkata');
            $FromDate = $result[0]['FromDate'];
            $currentDate = date("Y-m-d");
            $earlier = new DateTime($FromDate);
            $later = new DateTime($currentDate);
            $calculateDays = $later->diff($earlier)->format("%a");
            $totalInventory = (int) $Adult + (int) $Child;
            $InfantPrice = 0;
            if ($Infant > 0) {
                $InfantPrice = ((float) $result[0]['TotalInfantCost'] + (float) $result[0]['TotalInfantMarkup']) * (int) $Infant;
            }
            if ($totalInventory > 0) {
                $totalAdultPrice = (int) $totalInventory * ($result[0]['TotalCost'] + $result[0]['TotalMarkup']);
            }
            $getCancellationRule = $this->getCancellationRules($SecurityKey, $AirlineId, $calculateDays);
            if (!empty($getCancellationRule)) {
                $adultCanCarge = ((($totalAdultPrice * (float) $getCancellationRule[0]['PercentageValue']) / 100) + ($totalInventory * (float) $getCancellationRule[0]['FixedVal']));
                $infantCanCarge = ((($InfantPrice * (float) $getCancellationRule[0]['PercentageValue']) / 100) + ($Infant * (float) $getCancellationRule[0]['FixedVal']));
            } else {
                $adultCanCarge = $totalAdultPrice;
                $infantCanCarge = $InfantPrice;
            }
            $dataSet = array("AC" => array(
                    "TotalInventory" => (int) $totalInventory,
                    "TotalInventoryCost" => $totalAdultPrice,
                    "TotalInventoryCC" => $adultCanCarge,
                    "RefundAmount" => (float) $totalAdultPrice - (float) $adultCanCarge
                ),
                "Infant" => array(
                    "TotalInventory" => (int) $Infant,
                    "TotalInventoryCost" => $InfantPrice,
                    "TotalInventoryCC" => $infantCanCarge,
                    "RefundAmount" => (float) $InfantPrice - (float) $infantCanCarge
                )
            );

            if ($type == 'update') {
                http_response_code(200);
                $WhereArray = array('id =?' => $AirlineId);
                $updatedArray = array('Sold' => new Zend_Db_Expr("Sold - $totalInventory"), 'CurrentSeat' => new Zend_Db_Expr("CurrentSeat + $totalInventory"));
                $this->updateTable("flightinventory_airlineprice", $updatedArray, $WhereArray);
                $WhereHistoryArray = array('HistoryId =?' => $BookingId);
                $IsCancel = 1;
                if ((int) $CancellationType == 2) {
                    $IsCancel = 2;
                }
                $updatedHistoryArray = array('IsCancel' => $IsCancel, 'CAdult' => new Zend_Db_Expr("CAdult + $Adult"), 'CChild' => new Zend_Db_Expr("CChild + $Child"), 'CInfant' => new Zend_Db_Expr("CInfant + $Infant"), 'TotalCancelCostAdult' => new Zend_Db_Expr("TotalCancelCostAdult + $adultCanCarge"), 'TotalCancelCostInfant' => new Zend_Db_Expr("TotalCancelCostInfant + $infantCanCarge"));
                $this->updateTable("flightinventory_airlinepricehistory", $updatedHistoryArray, $WhereHistoryArray);
                $ChistoryId = $this->GUID();
                $currentDate = date("Y-m-d");
                $arrayInsert = array(
                    "HistoryId" => $ChistoryId,
                    "Adult" => $Adult,
                    "Child" => $Child,
                    "Infant" => $Infant,
                    "IsCancel" => $IsCancel,
                    "TotalCancelCostAdult" => $adultCanCarge,
                    "TotalCancelCostInfant" => $infantCanCarge,
                    "CreatedDate" => $currentDate,
                    "UpdatedDate" => $currentDate,
                    "ReleasedDate" => $currentDate,
                    "IsMarkForDelete" => 0,
                    "AirLinePriceHistory_id" => $AirlineId
                );
                $this->insertTable("flightinventory_cancellationhistory", $arrayInsert);
            }
            $resultSet = ["Status" => True, "refId" => $ChistoryId, "resultSet" => $dataSet];
        } else {
            $resultSet = ["Status" => False, "Message" => "This booking is not refundable. Please contact to administrator"];
        }

        return $resultSet;
    }

    public function getCancellationAmountAutoTiket($SecurityKey, $BookingId, $Adult = 0, $Child = 0, $Infant = 0, $type = 'check', $CancellationType = 1) {
        $adultCanCarge = $infantCanCarge = 0;
        $dbObj = $this->db;
        $select = $dbObj->select()->from(array("T1" => "flightinventory_airlinepricehistory"), array('id As hid','TotalCost', 'TotalMarkup', 'TotalInfantCost', 'TotalInfantMarkup', 'Adult', 'Child', 'Infant', 'IsCancel', 'CAdult', 'CChild', 'CInfant', 'TotalAdult' => new Zend_Db_Expr("Adult - CAdult"), 'TotalChild' => new Zend_Db_Expr("Child - CChild"), 'TotalInfant' => new Zend_Db_Expr("Infant - CInfant")))
                ->joinLeft(array('T2' => "flightinventory_airlineprice"), "T1.AirLinePrice_id = T2.id", array('id', 'FromDate', 'IsRefundable', 'OnwardAutoTicket', 'Fare', 'Taxes', 'MarkupType', 'Markup', 'B2CMarkup', 'InfantFare', 'InfantMarkup'))
                ->joinLeft(array('T3' => "flightinventory_flightinventory"), "T3.id = T2.FlightInventory_id", array())
                ->where("T3.SecurityKey = (?)", $SecurityKey)
                ->where("T1.HistoryId = (?) ", $BookingId)
                ->where("T1.InventoryStatus = (?)", 1)
                ->having("TotalAdult >= (?)", $Adult)
                ->having("TotalChild >= (?)", $Child)
                ->having("TotalInfant >= (?)", $Infant);
        //echo $select; exit;
        $result = $dbObj->fetchAll($select);
        //if (!empty($result) && isset($result[0]['OnwardAutoTicket']) && $result[0]['OnwardAutoTicket'] == 0) {
        if (!empty($result)) {
            $AirlineId = $result[0]['id'];
            $AirlineHId = $result[0]['hid']; 
            date_default_timezone_set('Asia/Kolkata');
            $totalInventory = (int) $Adult + (int) $Child;
            $InfantPrice = 0;
            if ($Infant > 0) {
                $InfantPrice = ((float) $result[0]['TotalInfantCost'] + (float) $result[0]['TotalInfantMarkup']) * (int) $Infant;
            }
            if ($totalInventory > 0) {
                $totalAdultPrice = (int) $totalInventory * ($result[0]['TotalCost'] + $result[0]['TotalMarkup']);
            }
            $dataSet = array("AC" => array(
                    "TotalInventory" => (int) $totalInventory,
                    "TotalInventoryCost" => $totalAdultPrice
                ),
                "Infant" => array(
                    "TotalInventory" => (int) $Infant,
                    "TotalInventoryCost" => $InfantPrice
                )
            );
            if ($type == 'update') {
                http_response_code(200);
                $WhereArray = array('id =?' => $AirlineId);
                $updatedArray = array('Sold' => new Zend_Db_Expr("Sold - $totalInventory"), 'CurrentSeat' => new Zend_Db_Expr("CurrentSeat + $totalInventory"));
                $this->updateTable("flightinventory_airlineprice", $updatedArray, $WhereArray);
                $WhereHistoryArray = array('HistoryId =?' => $BookingId);
                $IsCancel = 1;
                if ((int) $CancellationType == 2) {
                    $IsCancel = 2;
                }
                $updatedHistoryArray = array('IsCancel' => $IsCancel, 'CAdult' => new Zend_Db_Expr("CAdult + $Adult"), 'CChild' => new Zend_Db_Expr("CChild + $Child"), 'CInfant' => new Zend_Db_Expr("CInfant + $Infant"), 'TotalCancelCostAdult' => new Zend_Db_Expr("TotalCancelCostAdult + $adultCanCarge"), 'TotalCancelCostInfant' => new Zend_Db_Expr("TotalCancelCostInfant + $infantCanCarge"));
                $this->updateTable("flightinventory_airlinepricehistory", $updatedHistoryArray, $WhereHistoryArray);
                $ChistoryId = $this->GUID();
                $currentDate = date("Y-m-d");
                $arrayInsert = array(
                    "HistoryId" => $ChistoryId,
                    "Adult" => $Adult,
                    "Child" => $Child,
                    "Infant" => $Infant,
                    "IsCancel" => $IsCancel,
                    "TotalCancelCostAdult" => $adultCanCarge,
                    "TotalCancelCostInfant" => $infantCanCarge,
                    "CreatedDate" => $currentDate,
                    "UpdatedDate" => $currentDate,
                    "ReleasedDate" => $currentDate,
                    "IsMarkForDelete" => 0,
                    "AirLinePriceHistory_id" => $AirlineHId
                );
                try {
                    $this->insertTable("flightinventory_cancellationhistory", $arrayInsert);
                    $resultSet = ["Status" => True, "refId" => $ChistoryId, "resultSet" => $dataSet];
                } catch (Zend_Exception $e) {
                    $resultSet = ["Status" => False, "Message" => $e->getMessage()];
                }
            }
            
        } else {
            $resultSet = ["Status" => False, "Message" => "This booking is not refundable. Please contact to administrator"];
        }

        return $resultSet;
    }

}