<?php

class Travel_Model_TblTransport {

    private $db = NULL;
    public $intId = NULL;
    public $searchArr = NULL;
    public $baseUrl = Null;

    /*     * ************************************ */

    public function __construct() {
        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }

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

    public function getAllTransportList($intLoggedinUserAgencySysId) {
        $sql = " SELECT InvnItemSysId, AgencySysId, VehType, VehicleName, IsAC, CostCurrency, TB_Master_Currency.Symbol AS Currency, TB_IC_Supplier.SupplierName AS SupplierType, "
                . "TB_Master_BookingType.Title AS BookingName, TB_Master_VehicleType.Title as carType, NightCharges, MinKmPerDay, PerKMRate, Rate40Km, Rate80Km, PerKmsRate, ExtraHrsRate, tmt.IsActive, tmt.IsSaleStart "
                . "FROM TB_MP_Transport [tmt] "
                . "JOIN TB_Master_VehicleType ON tmt.VehType=TB_Master_VehicleType.VehicleTypeSysId "
                . "JOIN TB_Master_Currency ON tmt.CostCurrency=TB_Master_Currency.CurrencyType "
                . "JOIN TB_IC_Supplier ON tmt.SupplierSysId=TB_IC_Supplier.SupplierSysId "
                . "JOIN TB_Master_BookingType ON tmt.BookingType=TB_Master_BookingType.BookingType "
                . " WHERE 1=1 AND tmt.IsMarkForDel='0' ";

        if (!empty($intLoggedinUserAgencySysId)) {
            $sql .= " AND AgencySysId = '" . $intLoggedinUserAgencySysId . "' ";
        }

        if (!empty($this->searchArr)) {
            //echo "<pre>"; print_r($searchArr); die;

            if ($this->searchArr['VehicleType'] != 0) {
                $sql .= " AND tmt.VehType = '" . $this->searchArr['VehicleType'] . "' ";
            }

            if ($this->searchArr['supplierId'] != 0) {
                $sql .= " AND tmt.SupplierSysId = '" . $this->searchArr['supplierId'] . "'";
            }
        }

        $sql .= " ORDER BY tmt.UpdateDate DESC ";

        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }

    public function getAllManageTransportList($intLoggedinUserAgencySysId = null) {
        $sql = " SELECT tmv.VehSysId, tmv.VehTypeSysId, tmv.VehName, tmv.IsAC, tmv.IsApproved, tmv.IsActive, "
                . " TB_Master_VehicleType.Title as carType, TB_Agency.Title as AgencyName "
                . "FROM TB_Master_Vehicle tmv "
                . "JOIN TB_Master_VehicleType ON tmv.VehTypeSysId=TB_Master_VehicleType.VehicleTypeSysId "
                . "LEFT JOIN TB_Agency ON tmv.AgencySysId=TB_Agency.AgencySysId "
                . " WHERE 1=1 AND tmv.IsMarkForDel='0' ";
        if ($intLoggedinUserAgencySysId != null) {
            if($intLoggedinUserAgencySysId == 139360){
                $sql .= " AND tmv.AgencySysId = " . $intLoggedinUserAgencySysId;
            }else{
                $sql .= " AND tmv.AgencySysId IN(0," . $intLoggedinUserAgencySysId . ")";
            }
            
        }
        if (!empty($this->searchArr)) {

            if ($this->searchArr['VehicleType'] != 0) {
                $sql .= " AND tmv.VehTypeSysId = '" . $this->searchArr['VehicleType'] . "' ";
            }

            if ($this->searchArr['VehicleName'] != "") {
                $VehicleName = $this->searchArr['VehicleName'];
                $sql .= " AND tmv.VehName LIKE '%$VehicleName%'";
            }
        }

        $sql .= " ORDER BY tmv.VehSysId DESC ";

        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }

    public function getAllVehicleTypeList() {
        $sql = "Select VehicleTypeSysId, Title From TB_Master_VehicleType where 1 = 1 AND IsActive='1'";
        $sql .= " ORDER BY Title ";

        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }

    public function getVehicleList($vehicleTypeSysId) {
        $sql = "Select VehSysId, VehName From TB_Master_Vehicle where 1 = 1 AND IsActive='1'";
        if (!empty($vehicleTypeSysId)) {
            $sql .= " AND VehTypeSysId = '" . $vehicleTypeSysId . "'";
        }
        $sql .= " ORDER BY VehName ";
        //echo $sql; die;
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }

    public function getAllBookingTypeList() {
        $sql = "Select BookingType, Title From TB_Master_BookingType where 1 = 1 AND IsActive='1'";
        $sql .= " ORDER BY Title ";

        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }

    public function getVehicleNameList($vehicle_id) {
        $sql = "select dbo.GetVehicles('" . $vehicle_id . "') as VehicleName ";
        $rowset = $this->db->query($sql)->fetch();
        return $rowset;
    }

    public function addVehicleDetails($data) {
        try {
            $this->db->insert('TB_Master_Vehicle', $data);
            return $this->db->lastInsertId('TB_Master_Vehicle');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function checkDuplicate($data, $VehSysId = null) {
        $VehName = $data['VehName'];
        $IsAC = $data['IsAC'];
        $VehTypeSysId = $data['VehTypeSysId'];
        $AgencySysId = (int) $data['AgencySysId'];

        $select = $this->db->select();
        $select->from("TB_Master_Vehicle", array(new Zend_Db_Expr('count(*) as n')));
        $select->where("VehName =?", $VehName);
        $select->where("VehTypeSysId =?", $VehTypeSysId);
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsAC =?", $IsAC);
        if (!empty($VehSysId)) {
            $select->where("VehSysId !=?", $VehSysId);
        }
        //echo $select;exit;
        $result = $this->db->fetchRow($select);
        if ($result['n'] > 0) {
            return 1;
        }
        return 0;
    }

    public function updateVehicleDetails($update, $VehSysId) {
        $where = array('VehSysId = ? ' => $VehSysId);
        $this->db->update('TB_Master_Vehicle', $update, $where);
    }

    public function getSightseeingList($strSightseeingName, $strcountryId, $strCitySysId) {
        $sql = "Select SSSysId, Title From TB_IC_SightSeeing where 1 = 1";

        if (!empty($strSightseeingName)) {
            $sql .= " AND Title LIKE '$strSightseeingName%' ";
        }
        if (!empty($strcountryId)) {
            $sql .= " AND ContSysId ='" . $strcountryId . "' ";
        }
        if (!empty($strCitySysId)) {
            $sql .= " AND CitySysId ='" . $strCitySysId . "' ";
        }
        $sql .= " ORDER BY Title";
        //echo $sql; die;

        $rowset = $this->db->query($sql)->fetchAll();

        if (count($rowset) > 0) {
            foreach ($rowset as $row) {

                $response[] = array('SSSysId' => $row['SSSysId'], 'value' => $row['Title'], 'label' => $row['Title']);
            }
        } else {
            $response[] = array('SSSysId' => 0, 'value' => 'Add New Activity', 'label' => 'Add New Sightseeing');
        }
        return $response;
    }

    public function addTransportInventory($data) {
        try {
            $this->db->insert('TB_MP_Transport', $data);
            return $this->db->lastInsertId('TB_MP_Transport');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function getAllTransportInventoryList($intLoggedinUserAgencySysId) {
        $sql = " SELECT TOP 10 InvnItemSysId, AgencySysId, VehType, VehicleName, IsAC, CostCurrency, TB_Master_Currency.Symbol AS Currency, TB_IC_Supplier.SupplierName AS SupplierType, "
                . "TB_Master_BookingType.Title AS BookingName, TB_Master_VehicleType.Title as carType, NightCharges, MinKmPerDay, PerKMRate, Rate40Km, Rate80Km, PerKmsRate, ExtraHrsRate, tmt.IsActive, tmt.IsSaleStart "
                . "FROM TB_MP_Transport [tmt] "
                . "JOIN TB_Master_VehicleType ON tmt.VehType=TB_Master_VehicleType.VehicleTypeSysId "
                . "JOIN TB_Master_Currency ON tmt.CostCurrency=TB_Master_Currency.CurrencyType "
                . "JOIN TB_IC_Supplier ON tmt.SupplierSysId=TB_IC_Supplier.SupplierSysId "
                . "JOIN TB_Master_BookingType ON tmt.BookingType=TB_Master_BookingType.BookingType "
                . " WHERE tmt.IsMarkForDel='0' ";

        if (!empty($intLoggedinUserAgencySysId)) {
            $sql .= " AND AgencySysId = '" . $intLoggedinUserAgencySysId . "' ";
        }
        $sql .= " ORDER BY tmt.InvnItemSysId DESC ";

        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }

    public function getTransportInventoryList($InvnItemSysId, $intLoggedinUserAgencySysId) {

        $sql = " SELECT TOP 10 InvnItemSysId, AgencySysId, VehType, VehicleName, IsAC, CostCurrency, TB_Master_Currency.Symbol AS Currency, TB_IC_Supplier.SupplierName AS SupplierType, "
                . "TB_Master_BookingType.Title AS BookingName, TB_Master_VehicleType.Title as carType, NightCharges, MinKmPerDay, PerKMRate, Rate40Km, Rate80Km, PerKmsRate, ExtraHrsRate, tmt.IsActive "
                . "FROM TB_MP_Transport [tmt] "
                . "JOIN TB_Master_VehicleType ON tmt.VehType=TB_Master_VehicleType.VehicleTypeSysId "
                . "JOIN TB_Master_Currency ON tmt.CostCurrency=TB_Master_Currency.CurrencyType "
                . "JOIN TB_IC_Supplier ON tmt.SupplierSysId=TB_IC_Supplier.SupplierSysId "
                . "JOIN TB_Master_BookingType ON tmt.BookingType=TB_Master_BookingType.BookingType "
                . " WHERE tmt.IsMarkForDel='0' ";

        if (!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = '" . $InvnItemSysId . "' ";
        }
        if (!empty($intLoggedinUserAgencySysId)) {
            $sql .= " AND AgencySysId = '" . $intLoggedinUserAgencySysId . "' ";
        }

        $sql .= " ORDER BY tmt.InvnItemSysId DESC ";
        //echo $sql;exit;
        return $this->db->query($sql)->fetchAll();
    }

    public function getStandardMarkups($intAgencySysId) {

        $sql = " SELECT TB_MP_StdMarkup_Trans.MPType AS MPType_id, TB_Master_MPType.Title as MPType, 
                CustGrSysId, Currency, Tax, MarkUpType, IsSameForAll, MarkUpPer, PerPaxMarkUp, ValidTill,
                 CreateDate, UpdateDate, IsMarkForDel, IsActive
                 FROM TB_MP_StdMarkup_Trans INNER JOIN TB_Master_MPType 
                 ON TB_MP_StdMarkup_Trans.MPType = TB_Master_MPType.MPType
                 WHERE 1 = 1  ";

        if (!empty($intAgencySysId)) {
            $sql .= " AND AgencySysId = " . $intAgencySysId;
        }

        $sql .= " ORDER BY UpdateDate DESC ";

        //echo $sql; die;
        return $this->db->query($sql)->fetchAll();
    }

    public function getCustomizedMarkups($InvnItemSysId, $MPType = '', $intLoggedinUserAgencySysId = '') {

        $sql = " SELECT InvnItemSysId, TB_MP_Trans_CustomMarkup.MPType AS MPType_id, TB_Master_MPType.Title as MPType, 
                CustGrSysId, AgencySysId, Currency, MarkUpType, IsSameForAll, MarkUpPer, Tax, PerPaxMarkUp, ValidTill,
                 CreateDate, UpdateDate, IsMarkForDel, IsActive
                 FROM TB_MP_Trans_CustomMarkup INNER JOIN TB_Master_MPType 
                 ON TB_MP_Trans_CustomMarkup.MPType = TB_Master_MPType.MPType
                 WHERE 1 = 1  ";

        if (!empty($MPType)) {
            $sql .= " AND TB_MP_Trans_CustomMarkup.MPType = " . $MPType;
        }

        if (!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }

        if (!empty($intLoggedinUserAgencySysId)) {
            $sql .= " AND AgencySysId = " . $intLoggedinUserAgencySysId;
        }

        $sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";

        //echo $sql; die;

        return $this->db->query($sql)->fetchAll();
    }

    public function getTransportMarketPlacesByAgencySysId($InvnItemSysId, $intAgencySysId) {
        $sql = "Select MPType From TB_MP_Trans_CustomMarkup where 1 = 1";

        if (!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }

        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 getAllTransportInventoryMarkupList($InvnItemSysId, $intLoggedinUserAgencySysId) {

        $sql = " SELECT * FROM TB_MP_Trans_CustomMarkup 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 getStandardMarkupsPolicies($InvnItemSysId, $intAgencySysId) {

        $sql = " SELECT InvnItemSysId, TCInfo, TCDocPath FROM TB_MP_Transport WHERE 1 = 1  ";
        if (!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }
        if (!empty($intAgencySysId)) {
            $sql .= " AND AgencySysId = " . $intAgencySysId;
        }
        $sql .= " ORDER BY InvnItemSysId";
        //echo $sql; die;
        return $this->db->query($sql)->fetch();
    }

    public function addMarkupSettings($data) {
        $this->db->insert('TB_MP_Trans_CustomMarkup', $data);
        return $this->db->lastInsertId();
    }

    public function deleteCustomizedMarkupSetting($InvnItemSysId, $MPType, $intLoggedinUserAgencySysId) {
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'MPType = ? ' => $MPType, 'AgencySysId = ? ' => $intLoggedinUserAgencySysId);
        $this->db->delete('TB_MP_Trans_CustomMarkup', $where);
    }

    public function getCustomizedMarkupsPolicies($InvnItemSysId, $intAgencySysId) {

        $sql = " SELECT InvnItemSysId, TCInfo, TCDocPath FROM TB_MP_Transport WHERE 1 = 1 ";

        if (!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }
        if (!empty($intAgencySysId)) {
            $sql .= " AND AgencySysId = " . $intAgencySysId;
        }
        //echo $sql; die;
        return $this->db->query($sql)->fetch();
    }

    public function editMarkupPoliciesSettings($update, $InvnItemSysId) {
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId);
        $this->db->update('TB_MP_Transport', $update, $where);
    }

    public function getTransportInventoryDetailsList($InvnItemSysId, $intLoggedinUserAgencySysId) {

        $sql = " SELECT TOP 10 InvnItemSysId, AgencySysId, VehType, VehicleName, IsAC, CostCurrency, TB_Master_Currency.Symbol AS Currency, TB_IC_Supplier.SupplierName AS SupplierType, "
                . "TB_Master_BookingType.Title AS BookingName, TB_Master_VehicleType.Title as carType, NightCharges, MinKmPerDay, PerKMRate, Rate40Km, Rate80Km, PerKmsRate, ExtraHrsRate, tmt.IsActive "
                . "FROM TB_MP_Transport [tmt] "
                . "JOIN TB_Master_VehicleType ON tmt.VehType=TB_Master_VehicleType.VehicleTypeSysId "
                . "JOIN TB_Master_Currency ON tmt.CostCurrency=TB_Master_Currency.CurrencyType "
                . "JOIN TB_IC_Supplier ON tmt.SupplierSysId=TB_IC_Supplier.SupplierSysId "
                . "JOIN TB_Master_BookingType ON tmt.BookingType=TB_Master_BookingType.BookingType "
                . " WHERE 1=1 ";

        if (!empty($InvnItemSysId)) {
            $sql .= " AND InvnItemSysId = " . $InvnItemSysId;
        }
        if (!empty($intLoggedinUserAgencySysId)) {
            $sql .= " AND AgencySysId = '" . $intLoggedinUserAgencySysId . "' ";
        }

        $sql .= " ORDER BY tmt.InvnItemSysId DESC ";
        //echo $sql;exit;
        return $this->db->query($sql)->fetch();
    }

    public function updateTransportInventoryDetails($update, $InvnItemSysId, $intLoggedinUserId) {
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'AgencySysId = ? ' => $intLoggedinUserId);
        $this->db->update('TB_MP_Transport', $update, $where);
    }

    public function updateTransportSaleInventory($InvnItemSysId, $actionValue, $intLoggedinUserId) {
        $update = array('IsSaleStart' => $actionValue);
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'AgencySysId = ? ' => $intLoggedinUserId);
        $this->db->update('TB_MP_Transport', $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 cloneMPInventoryTransport($InvnItemSysId) {

        $currentDate = date('Y-m-d H:i:s');

        $sql = " INSERT INTO TB_MP_Transport ( CityType
                ,AgencySysId, SupplierSysId, ICSourceSysId, IsFromAgency, VehType, VehicleName, IsAC
                ,NightCharges, MinKmPerDay, PerKMRate, Rate40Km, Rate80Km
                ,PerKmsRate, ExtraHrsRate, BookingType, CostCurrency, TrxCurrency,ImageTN
                ,ImgForList,ImgeDetails,TCInfo,TCDocPath
                ,UpdateDate
                ,CreateDate
                ,ApproveBy
                ,ApprovalDate
                ,IsApproved
                ,IsActive
                ,IsMarkForDel) 
                
                SELECT  CityType
                ,AgencySysId, SupplierSysId, ICSourceSysId, IsFromAgency, VehType, VehicleName, IsAC
                ,NightCharges, MinKmPerDay, PerKMRate, Rate40Km, Rate80Km
                ,PerKmsRate, ExtraHrsRate, BookingType, CostCurrency, TrxCurrency,ImageTN
                ,ImgForList,ImgeDetails,TCInfo,TCDocPath
                ,'" . $currentDate . "'
                ,'" . $currentDate . "'
                ,ApproveBy
                ,'" . $currentDate . "'
                ,'0'
                ,'0'
                ,'0' FROM TB_MP_Transport WHERE InvnItemSysId = " . $InvnItemSysId;

        //echo $sql; die;

        $this->db->query($sql);
        return $this->db->lastInsertId('TB_MP_Transport');
    }

    public function deleteTransportInventory($InvnItemSysId, $intLoggedinUserAgencySysId) {
        $where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'AgencySysId = ? ' => $intLoggedinUserAgencySysId);
        $updateData = array('IsMarkForDel' => '1');
        $this->db->update('TB_MP_Transport', $updateData, $where);
    }

    public function getSuppliersList($cityId, $intLoggedinUserId) {

        $sql = " SELECT distinct(TB_IC_Supplier.SupplierSysId), TB_IC_Supplier.SupplierName FROM TB_IC_Supplier "
                . " RIGHT JOIN TB_MP_Transport ON TB_MP_Transport.SupplierSysId=TB_IC_Supplier.SupplierSysId WHERE 1 =1 AND TB_IC_Supplier.IsDelete='0' ";

        if (!empty($cityId)) {
            $sql .= " AND TB_IC_Supplier.CitySysId = " . $cityId;
        }

        if (!empty($intLoggedinUserId)) {
            $sql .= " AND TB_IC_Supplier.AgentSysId = " . $intLoggedinUserId;
        }

        $sql .= " AND TB_IC_Supplier.Services LIKE '______1%' ";
        $sql .= " ORDER BY TB_IC_Supplier.SupplierName ";

        //echo $sql; exit;
        return $this->db->query($sql)->fetchAll();
    }

    public function setActiveDeactiveInventory($InvnItemSysId, $intLoggedinUserAgencySysId, $statusValue) {
        $where = array('InvnItemSysId = ? ' => base64_decode($InvnItemSysId), 'AgencySysId = ? ' => $intLoggedinUserAgencySysId);
        $updateData = array('IsActive' => $statusValue);
        return $this->db->update('TB_MP_Transport', $updateData, $where);
    }

    public function setActiveDeactive($VehSysId, $statusValue) {
        $where = array('VehSysId = ? ' => base64_decode($VehSysId));
        $updateData = array('IsActive' => $statusValue);
        return $this->db->update('TB_Master_Vehicle', $updateData, $where);
    }

    public function getManageTransportDetails($VehSysId) {
        $sql = "Select VehSysId, VehTypeSysId, VehName, Icon, Capacity, IsAC, NoofVehicle, IsActive, IsMarkForDel, CreateDate, UpdateDate  From TB_Master_Vehicle where 1 = 1 ";

        if (!empty($VehSysId)) {
            $sql .= " AND VehSysId = '" . $VehSysId . "'";
        }

        //echo $sql; die;
        $rowset = $this->db->query($sql)->fetch();
        return $rowset;
    }

    /**
     * setSaleStartStop. To set IsSaleStart record.
     * 
     * @param  string $whereCondition
     * @param  string $tableName
     * @param  int    $IsSaleStart
     * @return int
     */
    public function setSaleStartStop($whereCondition, $tableName, $IsSaleStart = 0) {
        try {
            $isChanged = $this->db->update($tableName, array('IsSaleStart' => $IsSaleStart), $whereCondition);
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return @$isChanged ? $isChanged : 0;
    }

    public function addStdMarkupTransport($data = array()) {
        try {
            $select = $this->db->select()
                    ->from("TB_MP_StdMarkup_Trans", array('count(*) AS totalRecords'))
                    ->where("AgencySysId =?", $data['AgencySysId'])
                    ->where("MpType =?", $data['MPType']);
            $resultData = $this->db->fetchOne($select);
//            echo $resultData; die;
            if ($resultData == 1) {
                $where['AgencySysId = ?'] = $data['AgencySysId'];
                $where['MpType = ?'] = $data['MPType'];
                return $this->db->update('TB_MP_StdMarkup_Trans', $data, $where);
            } else {
                $this->db->insert('TB_MP_StdMarkup_Trans', $data);
                return $this->db->lastInsertId('TB_MP_StdMarkup_Trans');
            }
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
    }

    public function getAgencyMarkupSettingDetails($intLoggedinUserAgencySysId, $maketPlaceId) {
        $agencyMarkup = array('AgencySysId', 'MPType', 'Currency', 'MarkUpType', 'IsSameForAll', 'MarkUpPer', 'Tax', 'IsActive');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_MP_StdMarkup_Trans"), $agencyMarkup);
        $select->where("tbl.AgencySysId = ?", $intLoggedinUserAgencySysId);
        $select->where("tbl.MPType = ?", $maketPlaceId);
        //echo $select; die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function addDriverDetails($data) {
        try {
            $this->db->insert('TB_Agency_Master_Driver', $data);
            return $this->db->lastInsertId('TB_Agency_Master_Driver');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function updateDriverDetails($update, $where) {
        $this->db->update('TB_Agency_Master_Driver', $update, $where);
    }

    public function checkDuplicateDriver($data, $DriverSysId = null) {
        $DriverName = $data['DriverName'];
        $DriverEmailId = $data['DriverEmailId'];
        $DriverMobile = $data['DriverMobile'];
        $AgencySysId = (int) $data['AgencySysId'];
        $DriverCityId = (int) $data['DriverCityId'];
        $DriverCountryId = (int) $data['DriverCountryId'];
        $DrivingLicense = $data['DrivingLicense'];

        $select = $this->db->select();
        $select->from("TB_Agency_Master_Driver", array(new Zend_Db_Expr('count(*) as n')));
        $select->where("DriverName =?", $DriverName);
        $select->where("DriverEmailId =?", $DriverEmailId);
        $select->where("DriverMobile =?", $DriverMobile);
        $select->where("DriverCityId =?", $DriverCityId);
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("DriverCountryId =?", $DriverCountryId);
        $select->where("DrivingLicense =?", $DrivingLicense);
        if (!empty($DriverSysId)) {
            $select->where("DriverSysId !=?", $DriverSysId);
        }
//        echo $select;exit;
        $result = $this->db->fetchRow($select);
        if ($result['n'] > 0) {
            return 1;
        }
        return 0;
    }

    public function getAllManageDriverList($intLoggedinUserAgencySysId = null) {
        $where = "TBL1.AgencySysId = '" . $intLoggedinUserAgencySysId . "'";
        $where .= "AND TBL1.IsMarkForDel = '0'";
        if (!empty($this->searchArr)) {
            if ($this->searchArr['DriverMobile'] != "") {
                $where .= " AND TBL1.DriverMobile = '" . $this->searchArr['DriverMobile'] . "' ";
            }

            if ($this->searchArr['DriverName'] != "") {
                $where .= " AND TBL1.DriverName = '" . $this->searchArr['DriverName'] . "'";
            }
            if ($this->searchArr['Country'] != "") {
                $where .= " AND TBL1.DriverCountryId = '" . $this->searchArr['Country'] . "'";
            }
            if ($this->searchArr['cityId'] != "") {
                $where .= " AND TBL1.DriverCityId = '" . $this->searchArr['cityId'] . "'";
            }
            if ($this->searchArr['DriverEmail'] != "") {
                $where .= " AND TBL1.DriverEmailId = '" . $this->searchArr['DriverEmail'] . "'";
            }
        }

        $selectCompBAnkDet = $this->db->select()
                ->from(array('TBL1' => 'TB_Agency_Master_Driver'), array('TBL1.DriverSysId', 'TBL1.AgencySysId', 'TBL1.DriverName', 'TBL1.DriverEmailId', 'TBL1.DriverMobile', 'TBL1.DriverLanguages', 'TBL1.Location', 'TBL1.DriverCityId', 'TBL1.DriverCountryId', 'TBL1.DrivingLicense', 'TBL1.PrimaryVehicle', 'TBL1.IsActive', 'TBL1.DriverCountryCode'))
                ->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBL1.DriverCountryId=TBMGCO.ContId', array('TBMGCO.Title as CountryName'))
                ->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBL1.DriverCityId=TBMGC.CityId', array('TBMGC.Title as CityName'))
//                ->joinLeft(array('TBL2' => 'TB_Master_Lang'), 'TBL1.DriverLanguages=TBL2.LangSysId', array('TBL2.TitleEng as LanguageName'))
                ->where($where)
                ->order("TBL1.DriverSysId DESC");

        $resultBankDetails = $this->db->fetchAll($selectCompBAnkDet);
        return $resultBankDetails;
    }

    public function getManageDriverDetails($DriverSysId) {
        if ($DriverSysId != "") {
            $where .= "Tbl.DriverSysId = '" . $DriverSysId . "'";
        }
        $select = $this->db->select()
                ->from((array('Tbl' => 'TB_Agency_Master_Driver')), 'Tbl.*')
                ->where($where)
                ->order("Tbl.DriverSysId DESC");
        $result = $this->db->fetchRow($select);
        $languageIds = (!empty($result['DriverLanguages'])) ? trim($result['DriverLanguages']) : '';
        if(!empty($languageIds)){
            $select1 = $this->db->select()
                ->from((array('TBL2' => 'TB_Master_Lang')), 'TBL2.*')
                ->where("TBL2.LangSysId IN(" . $languageIds . ")")  ;
            $result1 = $this->db->fetchAll($select1);
            $result['LanguageArray'] = $result1;
        }
        return $result;
    }

    public function setDriverActiveDeactive($DriverSysId, $statusValue) {
        $where = array('DriverSysId = ? ' => base64_decode($DriverSysId));
        $updateData = array('IsActive' => $statusValue);
        return $this->db->update('TB_Agency_Master_Driver', $updateData, $where);
    }

    public function setDriverDelete($where, $statusValue) {
        $updateData = array('IsMarkForDel' => $statusValue);
        return $this->db->update('TB_Agency_Master_Driver', $updateData, $where);
    }

    public function getDriverCityList($intLoggedinUserId) {
        $sql = " SELECT distinct(tb3.CityId) AS CityId, tb3.Title FROM TB_Agency_Master_Driver AS tb2 "
                . "LEFT JOIN TB_Master_Geo_City AS tb3 ON tb2.DriverCityId = tb3.CityId  WHERE 1 = 1 ";
        if (!empty($intLoggedinUserId)) {
            $sql .= " AND tb2.AgencySysId = " . $intLoggedinUserId . "AND tb2.IsMarkForDel = '0'";
        }
        $sql .= " AND tb3.IsMarkForDel = '0' AND tb3.Title!='0' ";

        $sql .= " ORDER BY tb3.Title";
        //echo $sql; die;
        $result = $this->db->query($sql)->fetchAll();
        return $result;
    }

    public function getDriverCountryList($intLoggedinUserId) {
        $sql = " SELECT distinct(tb3.ContId) AS ContId, tb3.Title FROM TB_Agency_Master_Driver AS tb2 "
                . "LEFT JOIN TB_Master_Geo_Country AS tb3 ON tb2.DriverCountryId = tb3.ContId  WHERE 1 = 1 ";
        if (!empty($intLoggedinUserId)) {
            $sql .= " AND tb2.AgencySysId = " . $intLoggedinUserId . "AND tb2.IsMarkForDel = '0'";
        }
        $sql .= " AND tb3.IsMarkForDel = '0' AND tb3.Title!='0' ";

        $sql .= " ORDER BY tb3.Title";
        //echo $sql; die;

        $result = $this->db->query($sql)->fetchAll();
        return $result;
    }

    public function addGuideDetails($data) {
        try {
            $this->db->insert('TB_Agency_Master_Guide', $data);
            return $this->db->lastInsertId('TB_Agency_Master_Guide');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function updateGuideDetails($update, $where) {
        $this->db->update('TB_Agency_Master_Guide', $update, $where);
    }

    public function getAllGuideList($intLoggedinUserAgencySysId = null) {
        $where = "TBL1.AgencySysId = '" . $intLoggedinUserAgencySysId . "'";
        $where .= "AND TBL1.IsMarkForDel = '0'";
        if (!empty($this->searchArr)) {
            if ($this->searchArr['GuideMobile'] != "") {
                $where .= " AND TBL1.GuideMobile = '" . $this->searchArr['GuideMobile'] . "' ";
            }

            if ($this->searchArr['GuideName'] != "") {
                $where .= " AND TBL1.GuideName = '" . $this->searchArr['GuideName'] . "'";
            }
            if ($this->searchArr['GuideEmail'] != "") {
                $where .= " AND TBL1.GuideEmailId = '" . $this->searchArr['GuideEmail'] . "'";
            }
        }

        $selectCompBAnkDet = $this->db->select()
                ->from(array('TBL1' => 'TB_Agency_Master_Guide'), array('TBL1.GuideSysId', 'TBL1.AgencySysId', 'TBL1.GuideName', 'TBL1.GuideEmailId', 'TBL1.GuideMobile', 'TBL1.GuideLanguages', 'TBL1.Description', 'TBL1.Destinations', 'TBL1.PerDayRate', 'TBL1.GuideCountryCode', 'TBL1.CurrencySysId', 'TBL1.IsActive'))
                ->where($where)
                ->order("TBL1.GuideSysId DESC");
        $resultBankDetails = $this->db->fetchAll($selectCompBAnkDet);
        return $resultBankDetails;
    }

    public function getGuideDetailsById($GuideSysId) {
        if ($GuideSysId != "") {
            $where .= "GuideSysId = '" . $GuideSysId . "'";
        }
        $select = $this->db->select()
                ->from(('TB_Agency_Master_Guide'), '*')
                ->where($where)
                ->order("GuideSysId DESC");
        $result = $this->db->fetchRow($select);
        $languageIds = (!empty($result['GuideLanguages'])) ? trim($result['GuideLanguages']) : '';
        if(!empty($languageIds)){
            $select1 = $this->db->select()
                ->from((array('TBL2' => 'TB_Master_Lang')), 'TBL2.*')
                ->where("TBL2.LangSysId IN(" . $languageIds . ")")  ;
            $result1 = $this->db->fetchAll($select1);
            $result['LanguageArray'] = $result1;
        }
        return $result;
    }

    public function setGuideDelete($where, $statusValue) {
        $updateData = array('IsMarkForDel' => $statusValue);
        return $this->db->update('TB_Agency_Master_Guide', $updateData, $where);
    }

    public function setGuideActiveDeactive($GuideSysId, $statusValue) {
        $where = array('GuideSysId = ? ' => base64_decode($GuideSysId));
        $updateData = array('IsActive' => $statusValue);
        return $this->db->update('TB_Agency_Master_Guide', $updateData, $where);
    }
        public function getAutosuggestLanguage($keyword)
    {
        $keyword = trim($keyword);
        $response = array();
        $sql = "SELECT * FROM TB_Master_Lang WHERE TitleEng like '%" . $keyword . "%'";
        $result = $this->db->query($sql)->fetchAll();
        if (count($result) > 0) {
            foreach ($result as $row) {
                    $response[] = array('LangSysId' => trim($row['LangSysId']), 'TitleEng' => stripslashes(trim($row['TitleEng'])));
            }
        }
        return $response;
    }
}