<?php

class Travel_Model_TblAddForex {

    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 updateData($table, $update, $id) {
        $where = array('id =?' => $id);
        $this->db->update($table, $update, $where);
    }

    public function DeleteData($update, $AgencySysId, $forexType) {
        $where = array('AgencySysId = ? ' => $AgencySysId,'ForexType =?' => $forexType);
        $this->db->update('Tb_Agency_Forex_Currency', $update, $where);
    }

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

    public function getcurrecy($AgencySysId,$forexType) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Forex_Currency"));
        if($forexType==1){
            $select->joinLeft(array('tbl2' => "TB_Master_Currency"), "tbl2.CurrencyType = tbl.ToCurrency", array('Symbol as TSymbol'));
        } else{
            $select->joinLeft(array('tbl2' => "TB_Master_Currency"), "tbl2.CurrencyType = tbl.FromCurrency", array('Symbol as FSymbol'));
        }
        $select->where("tbl.ForexType = ?", $forexType);
        $select->where("tbl.AgencySysId IN (?)", $AgencySysId);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        $select->where("tbl2.IsActive = ?", 1);
        $select->where("tbl2.IsDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkExistCurrency($id, $forexType) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Forex_Currency"));
        $select->where("tbl.ForexType = ?", $forexType);
        $select->where("tbl.id = ?", $id);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkcityDublicate($AgencySysId, $CityId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Service_Location"));
        $select->where("tbl.CityId = ?", $CityId);
        $select->where("tbl.AgencySysId =?", $AgencySysId);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getlocationbyId($id) {
        $state = array('Title as StateName');
        $city = array('Title as CityName');
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Service_Location"));
        $select->joinLeft(array('tbl2' => "TB_Master_Geo_City"), "tbl2.CityId = tbl.CityId", $city);
        $select->joinLeft(array('tbl3' => "TB_Master_Geo_State"), "tbl3.StateId = tbl.StateId", $state);
        $select->where("tbl.id = ?", $id);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getlocationlist($AgencySysId, array $searchArr = null) {
        $state = array('Title as StateName');
        $city = array('Title as CityName');
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Service_Location"));
        $select->joinLeft(array('tbl2' => "TB_Master_Geo_City"), "tbl2.CityId = tbl.CityId", $city);
        $select->joinLeft(array('tbl3' => "TB_Master_Geo_State"), "tbl3.StateId = tbl.StateId", $state);
        if (!empty($searchArr['CityId'])) {
            $select->where("tbl.CityId IN (?)", $searchArr['CityId']);
        }
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        $select->where("tbl.AgencySysId IN (?)", $AgencySysId);
        //$result = $this->db->fetchAll($select);
        return $select;
    }

    public function getlocation($AgencySysId) {
        $state = array('Title as StateName');
        $city = array('Title as CityName');
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Service_Location"));
        $select->joinLeft(array('tbl2' => "TB_Master_Geo_City"), "tbl2.CityId = tbl.CityId", $city);
        $select->joinLeft(array('tbl3' => "TB_Master_Geo_State"), "tbl3.StateId = tbl.StateId", $state);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDelete = ?", 0);
        $select->where("tbl.AgencySysId IN (?)", $AgencySysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getforexdetails($AgencySysId,$forexType = 0, $fromCurrency = 0, $toCurrency = 0) {
        if ($fromCurrency > 0 && $toCurrency > 0 && $forexType > 0) {
            $select = $this->db->select();
            $select->from(array('tbl' => "Tb_Agency_Forex_Currency"));
            $select->where("tbl.ForexType = ?", $forexType);
            $select->where("tbl.FromCurrency = ?", $fromCurrency);
            $select->where("tbl.ToCurrency = ?", $toCurrency);
            $select->where("tbl.AgencySysId IN (?)", $AgencySysId);
            $select->where("tbl.IsActive = ?", 1);
            $select->where("tbl.IsMarkForDelete = ?", 0);
        }
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCityList($AgencySysId) {
        $selectedarray = array('CityName' => 'tbl2.Title','CityId' => 'tbl2.CityId','StateName' => 'tbl3.Title');
        $select = $this->db->select()
            ->from(array('tbl' => 'Tb_Agency_Service_Location'),array())
            ->joinLeft(array('tbl2' => 'TB_Master_Geo_City'), 'tbl2.CityId = tbl.CityId', $selectedarray)
            ->joinLeft(array('tbl3' => 'TB_Master_Geo_State'), 'tbl3.StateId = tbl.StateId',array())
            ->where('tbl.IsActive = ?', 1)
            ->where('tbl.IsMarkForDelete = ?', 0)
            ->where('tbl.AgencySysId IN (?)', $AgencySysId);
        return $this->db->fetchAll($select);
    }
    
    public function getForexCurrency($AgencySysId, $ForexType) {
        $selectedarray = array('ForexType' => 'tbl.ForexType','ForexCardCost' => 'tbl.ForexCardCost','CurrencyNotesCost' => 'tbl.CurrencyNotesCost','FromCurrency' => 'tbl.FromCurrency','ToCurrency' => 'tbl.ToCurrency','FromCurrencySymbol' => 'from_currency.Symbol','ToCurrencySymbol' => 'to_currency.Symbol');
        $select = $this->db->select()
            ->from(array('tbl' => 'Tb_Agency_Forex_Currency'), $selectedarray)
            ->joinLeft(array('from_currency' => 'TB_Master_Currency'), 'tbl.FromCurrency = from_currency.CurrencyType',array('Title as f_title'))
            ->joinLeft(array('to_currency' => 'TB_Master_Currency'), 'tbl.ToCurrency = to_currency.CurrencyType',array('Title as t_title'))
            ->where('tbl.IsActive = ?', 1)
            ->where('tbl.IsMarkForDelete = ?', 0)
            ->where('tbl.AgencySysId IN (?)', $AgencySysId)
            ->where('tbl.ForexType IN (?)', $ForexType)
            ->where('from_currency.IsActive = ?', 1)
            ->where('to_currency.IsActive = ?', 1);
        return $this->db->fetchAll($select);
    }
    
    public function getForexProductRate($AgencySysId, $ForexType, $from_curr, $to_curr) {
        $selectedarray = array('ForexType', 'MarkupType', 'CurrencyNotesCost', 'CurrencyNotesB2cMarkup', 'CurrencyNotesB2bMarkup', 
        'ForexCardCost', 'ForexCardB2cMarkup', 'ForexCardB2bMarkup', 'FromCurrency', 'ToCurrency');
        $select = $this->db->select()
            ->from('Tb_Agency_Forex_Currency', $selectedarray)
            ->where('IsActive = ?', 1)
            ->where('IsMarkForDelete = ?', 0)
            ->where('AgencySysId = ?', $AgencySysId)
            ->where('ForexType = ?', $ForexType)
            ->where('FromCurrency = ?', $from_curr)
            ->where('ToCurrency = ?', $to_curr);
        return $this->db->fetchRow($select);
    }     
}