<?php

/* Zend Framework
 * @category   Zend
 * @package    Zend_Controller_Action
 * @copyright  Copyright (c) 2008-2014 Zend Technologies USA Inc. (http://www.zend.com)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 * @version    1.0
 * @author     Ranvir singh <ranvir@catpl.co.in>
 * Create Date 06-09-2016
 * Update Date 06-09-2016
 * ************************************************************* */

class Gtxwebservices_Model_GetPackageDetailForCustomer extends Zend_Db_Table_Abstract {

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

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

    public function checkPackageType($packageId) {
//        $select = $this->db->select()->from(array("t1" => "TB_Agency_Customer_TravelPlan"), array("PackSpecType"));
//        $select->where("t1.TPSysId=?", $packageId);
//        $select->where("t1.IsActive=?", 1);
//        $select->where("t1.IsMarkForDel=?", 0);

        $select = $this->db->select()->from(array("t1" => "TB_Agency_Customer_TravelPlan"), array("AgentSysId", "PackSpecType", "TPSysId", "AgencySysId", "CustomerSysId"))
                ->joinLeft(array('t2' => "TB_Agency_Customer"), "t1.CustomerSysId = t2.CustomerSysId", array("EmailId", "Contacts", "FirstName", "Title as Salutation", "LastName", "IsB2BAgent"))
                ->joinLeft(array('t3' => "TB_Agency_User"), "t1.AgentSysId = t3.UserSysId", array("FirstName as AgentFirstName", "LastName as AgentLastName", "ContactNo1 as AgentContactNo1Name",))
                ->joinLeft(array('t4' => "TB_Agency"), "t1.AgencySysId = t4.AgencySysId", array("ServiceTaxNo", "TrxCurrency", "AgencyPhoto", "Logo", "Title", "DisplayName", "Url", "EmailIdForCustomer", "IsEmailIdForCustVarified", "PrimaryContactNo",'IsDisplayHelloGTX'));
        //->joinLeft(array('t5' => "$table"), "t1.TrxSysId = t2.TrxId",array("ClosingBalance","TrxPayMediaRefNo","PaymentModeType","SupplierSourceStr","PaxCount"))
        // ->joinLeft(array('t6' => "$table"), "t1.TrxSysId = t2.TrxId",array("ClosingBalance","TrxPayMediaRefNo","PaymentModeType","SupplierSourceStr","PaxCount"));
        $select->where("t1.TPSysId=?", $packageId);
        $select->where("t1.IsActive=?", 1);
        $select->where("t1.IsMarkForDel=?", 0);

        try {
            $resultSet1 = $this->db->fetchAll($select);
        } catch (Zend_Exception $e) {
            $resultSet1 = $e->getMessage();
        }
        return $resultSet1;
    }

    public function getPackageDetailForCustomer($TPSysId) {
        $statement = $this->db->query("GET_BYO_READYMADEPACKAGE_FULLINFO_CUSTOMERTRAVELPLAN $TPSysId");
        $finalResultSet = array();
        // Result set 1
        $resultSet1 = $statement->fetchAll();
        foreach ($resultSet1 as $row) {
            $finalResultSet['Result1'][] = $row;
        }
        // Result set 2
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet2 = $statement->fetchAll();
        foreach ($resultSet2 as $row) {
            $finalResultSet['Result2'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet3 = $statement->fetchAll();
        foreach ($resultSet3 as $row) {
            $finalResultSet['Result3'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet4 = $statement->fetchAll();
        foreach ($resultSet4 as $row) {
            $finalResultSet['Result4'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet5 = $statement->fetchAll();
        foreach ($resultSet5 as $row) {
            $finalResultSet['Result5'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet6 = $statement->fetchAll();
        foreach ($resultSet6 as $row) {
            $finalResultSet['Result6'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet7 = $statement->fetchAll();
        foreach ($resultSet7 as $row) {
            $finalResultSet['Result7'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet8 = $statement->fetchAll();
        foreach ($resultSet8 as $row) {
            $finalResultSet['Result8'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet9 = $statement->fetchAll();
        foreach ($resultSet9 as $row) {
            $finalResultSet['Result9'][] = $row;
        }
        $statement->nextRowSet(); // Advance to the second result set
        $resultSet10 = $statement->fetchAll();
        foreach ($resultSet10 as $row) {
            $finalResultSet['Result10'][] = $row;
        }

        $statement->nextRowSet(); // Advance to the second result set
        $resultSet11 = $statement->fetchAll();
        foreach ($resultSet11 as $row) {
            $finalResultSet['Result11'][] = $row;
        }

        $statement->nextRowSet(); // Advance to the second result set
        $resultSet12 = $statement->fetchAll();
        foreach ($resultSet12 as $row) {
            $finalResultSet['Result12'][] = $row;
        }

        $statement->nextRowSet(); // Advance to the second result set
        $resultSet13 = $statement->fetchAll();
        foreach ($resultSet13 as $row) {
            $finalResultSet['Result13'][] = $row;
        }

//        $statement->nextRowSet(); // Advance to the second result set
//        $resultSet14 = $statement->fetchAll();
//        foreach ($resultSet14 as $row) {
//            $finalResultSet['Result14'][] = $row;
//        }
        return $finalResultSet;
    }

    public function getActivityRate($packageId, $Adult, $child,$AgencySysId) {
        $select = $this->db->select()->from(array("t1" => "TB_MP_Inventory_Activity"), array("TotalCostNativeAdult", "TotalCostNativeChild", "InvnItemSysId"));
        $select->where("t1.XRef=?", $packageId);
        $select->where("t1.AgencySysId=?", $AgencySysId);
        $select->where("t1.IsActive=?", 1);
        $select->where("t1.IsSaleStart=?", 1);
        $select->where("t1.IsMarkForDelete=?", 0);
        $totelAmount = 0;
        try {
            $resultSet1 = $this->db->fetchRow($select);
            $selectAlo = $this->db->select()->from(array("t1" => "TB_MP_Inventory_Activity_Alloc"));
            $selectAlo->where("t1.InvnItemSysId=?", $resultSet1['InvnItemSysId']);
            $selectAlo->where("t1.IsActive=?", 1);
            $selectAlo->where("t1.IsMarkForDel=?", 0);
            $resultSet2 = $this->db->fetchRow($selectAlo);
            $totalAdultMark = 0;
            $totalChildMark = 0;        
            if(!empty($resultSet2) && $resultSet2['MarkUpNativeAdult']) {
                $totalAdultMark = $resultSet2['MarkUpNativeAdult']+$resultSet2['TaxPer']-$resultSet2['DiscountNativeAdult'];
                $totalChildMark = $resultSet2['MarkUpNativeChild']+$resultSet2['TaxPer']-$resultSet2['DiscountNativeChild'];
            } else {
               $totalAdultMark = 100; 
               $totalChildMark = 50;
            }
            if ($Adult > 0) {
                $totelAmount += ((float)$resultSet1['TotalCostNativeAdult']+(float)$totalAdultMark) * (int)$Adult;
            }
            if ($child > 0) {
                $totelAmount += ((float)$resultSet1['TotalCostNativeChild']+(float)$totalChildMark) * (int)$child;
            }
        } catch (Zend_Exception $e) {
            $resultSet1 = $e->getMessage();
        }
       // echo $totelAmount;exit;
        return $totelAmount;
    }

    public function getslowquery() {
        $queryRow = "SELECT TOP 1 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) as query,
qs.execution_count,OBJECT_NAME(qt.objectid) Object_Name,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- LOGICAL READS
-- ORDER BY qs.total_logical_writes DESC -- LOGICAL WRITES
ORDER BY qs.total_worker_time DESC";
        $statement = $this->db->query("$queryRow");
        $resultSet1 = $statement->fetchAll();
        return $resultSet1;
    }

}