<?php

class Travel_Model_TblCurrency
{

    private $db = NULL;

    public $intId = NULL;

    public $strCurrencySymbol = 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 getCurrencyTypes()
    {
        $sql = " SELECT * from TB_Master_Currency WHERE 1=1 AND IsDelete = '0' AND IsActive = 1 ";
        if (!empty($this->strCurrencySymbol)) {
            $sql .= " AND Symbol = '" . $this->strCurrencySymbol . "' ";
        }
        $sql .= " ORDER BY Title ";
        return $this->db->query($sql)->fetchAll();
    }

    public function getCurrencySymbol($currencyType = NULL)
    {
        if ($currencyType == NULL) {
            $currencyType =  1;
        }
        $sql = " SELECT Symbol FROM TB_Master_Currency WHERE CurrencyType = " . $currencyType;
        $result  = $this->db->query($sql)->fetch();
        if (!empty($result)) {
            return trim($result['Symbol']);
        }
        return '';
    }

    public function getConvertionRate($fromCurrencyType, $toCurrencyType)
    {
        $sql = " SELECT Rate FROM TB_IC_CurrencyConversion WHERE FCurrencyType = " . $fromCurrencyType . " AND TCurrencyType = " . $toCurrencyType .
            " AND IsDelete = '0' AND IsActive = 1 ";

        $result  = $this->db->query($sql)->fetchAll();
        if (count($result) > 0) {
            return $result[0]['Rate'];
        }
        return 1;
    }
    public function getMultipleConvertionRate($fromCurrencyType, $toCurrencyType,$ExchangeRateMaarkup = 0)
    {
        
        if(!empty($fromCurrencyType) && !empty($toCurrencyType)){
            $Condition = ' 1 = 1 ';
            if(is_array($fromCurrencyType)){
                $Condition .= ' AND  FCurrencyType IN ('. implode(',',$fromCurrencyType) . ')';
            }else{
                $Condition .= ' AND FCurrencyType = '. $fromCurrencyType;
            }
            if(is_array($toCurrencyType)){
                $Condition .= ' AND TCurrencyType IN ('. implode(',',$toCurrencyType) . ')';
            }else{
                $Condition .= ' AND TCurrencyType = '. $toCurrencyType;
            }
            $sql = " SELECT FCurrencyType,TCurrencyType,Rate FROM TB_IC_CurrencyConversion WHERE ".$Condition . " AND IsDelete = '0' AND IsActive = 1 ";

            $result  = $this->db->query($sql)->fetchAll();
            $resultArray = array();
            foreach($result as $key => $value){
                if($ExchangeRateMaarkup > 0){
                    $Rate = $value['Rate'] + (($value['Rate'] * $ExchangeRateMaarkup) / 100);
                    $value['Rate'] = $Rate;
                }
                $resultArray[$value['FCurrencyType'].'-'.$value['TCurrencyType']] = $value;
            } 
            return $resultArray; 
        }else{
            array();
        }
        
    }
    public function getToCurrencyConvertionRate($toCurrencyType)
    {
        $sql = " SELECT FCurrencyType,TCurrencyType,Rate FROM TB_IC_CurrencyConversion WHERE TCurrencyType = " . $toCurrencyType .
            " AND IsDelete = '0' AND IsActive = 1 ";

        $result  = $this->db->query($sql)->fetchAll();
        return $result;
         
    }
    public function getAllConvertionRate()
    {
        $sql = " SELECT FCurrencyType,TCurrencyType,Rate FROM TB_IC_CurrencyConversion WHERE IsDelete = '0' AND IsActive = 1 ";

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

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


    public function getAgencyMarkupSettingDetails($intLoggedinUserAgencySysId, $CurrencyType)
    {
        $agencyMarkup = array('AgencySysId', 'CurrencyType', 'IsInPercentage', 'Value', 'IsActive');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_CurrencyExcPadding"), $agencyMarkup);
        $select->where("tbl.AgencySysId = ?", $intLoggedinUserAgencySysId);
        $select->where("tbl.CurrencyType = ?", $CurrencyType);
        //echo $select; die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCurrencyListingRecord()
    {
        $sql = " SELECT TB_Master_Currency.CurrencyType, TB_Master_Currency.Title, TB_Master_Currency.Symbol, TB_Master_Currency.Icon, 
                    TB_Master_Currency.RelatedCountries, TB_Master_Currency.IsActive
				FROM TB_Master_Currency WHERE TB_Master_Currency.IsDelete =0 ";

        //select RelatedCountries from TB_Master_Currency where ',' + RelatedCountries + ',' like '%,6,%';

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

            if ($this->searchArr['Title'] != '') {
                $sql .= " AND TB_Master_Currency.Title LIKE '%" . $this->searchArr['Title'] . "%' ";
            }
            if ($this->searchArr['Symbol'] != '') {
                $sql .= " AND TB_Master_Currency.Symbol LIKE '%" . $this->searchArr['Symbol'] . "%' ";
            }
            if ($this->searchArr['countryList'] != '') {
                $country = $this->searchArr['countryList'];
                $sql .= " AND ( ',' + TB_Master_Currency.RelatedCountries + ',' like '%,' + '$country' + ',%' )";
                //$sql .= " AND TB_Master_Currency.RelatedCountries ',' + RelatedCountries + ',' like '%,$country,%' ";
            }
            if ($this->searchArr['filter1'] != 'All' && $this->searchArr['filter1'] != '') {
                $sql .= " AND TB_Master_Currency.IsActive = '" . $this->searchArr['filter1'] . "' ";
            }
        }

        $sql .= " ORDER BY TB_Master_Currency.Title ASC ";
        //echo $sql; //die();

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





    //Added By Pardeep Panchal... 
    public function getCurrencyIdsAndSymbolList()
    {

        $arrCurrencyType = array('CurrencyType as CurrencySysId', 'Symbol');

        $select = $this->db->select();
        $select->from(array("t1" => "TB_Master_Currency"), $arrCurrencyType);
        $select->where("IsActive = ?", 1);
        $select->where("IsDelete = ?", 0);
        $select->order("Title");
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        $arrCurrency = array();
        if (count($result) > 0) {
            foreach ($result as $res) {
                $arrCurrency[trim($res['Symbol'])] = trim($res['CurrencySysId']);
            }
        }
        return $arrCurrency;
    }
    public function getCurrencySymbolByIdsList()
    {

        $arrCurrencyType = array('CurrencyType as CurrencySysId', 'Symbol');

        $select = $this->db->select();
        $select->from(array("t1" => "TB_Master_Currency"), $arrCurrencyType);
        $select->where("IsActive = ?", 1);
        $select->where("IsDelete = ?", 0);
        $select->order("Title");
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        $arrCurrency = array();
        if (count($result) > 0) {
            foreach ($result as $res) {
                $arrCurrency[trim($res['CurrencySysId'])] = trim($res['Symbol']);
            }
        }
        return $arrCurrency;
    }

    //Added By Pardeep Panchal ends...

    public function getCurrencyAutosuggestList($term){
            $select = $this->db->select()
            ->from(['tbl' => 'TB_Master_Currency'], array('CurrencyType as CurrencySysId', 'Symbol', 'Title'))
            ->where("tbl.Title LIKE ? OR tbl.Symbol LIKE ?", $term . '%')
            ->where('tbl.IsActive = ?', 1)
            ->where('tbl.IsDelete = ?', 0)
            ->order('tbl.CurrencyType ASC');

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






}