<?php
/***************************************************************************
 *																			*
 * Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
 *																			*
 * This  is  commercial  software,  only  users  who have purchased a valid *
 * license  and  accept  to the terms of the  License Agreement can install *
 * and use this program.													*
 *																			*
 ****************************************************************************
 * PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
 * "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE.			*
 ****************************************************************************/ //
//$Id: CurrencyModel.php, v 1.0 2013/03/18 11:18:11
//
class CurrencyModel extends Model {
    //FETCHING THE DETAILS OF ALL CURRENCIES
    //$productCount => Yes - for getting active product count assigned to each currency @uage MIC
    public function listCurrencies($order = NULL, $start = NULL, $limit = NULL, $productCount = 'No', $status = 'Active') {
        if ($order)
            $append = " ORDER BY
						" . $order;
        else
            $append = " 
				ORDER BY
					currencyID ASC";
        if ($start && $limit)
            $append .= " LIMIT
						" . $start . "," . $limit . "";
        $sql = " 
				SELECT 
					cur.*";
        if ($productCount == 'Yes') {
            $sql .= " ,(
						SELECT 
						COUNT(product.productDefaultCurrency) 
						FROM 
						" . DB_PREFIX . "products product 
						LEFT JOIN " . DB_PREFIX . "timezones AS timz ON (
						  product.productTimeZone = timz.timeZoneID
						) 
						WHERE 
						product.productEndTime >= IF (
						  (
							CONVERT_TZ(
							  NOW(), 
							  @@session.time_zone, 
							  timz.timeZoneDiff
							)> 0
						  ), 
						  CONVERT_TZ(
							NOW(), 
							@@session.time_zone, 
							timz.timeZoneDiff
						  ), 
						  NOW()
						) 
						AND (
						  product.productStatus = 'Active' 
						  OR product.productStatus = 'Inactive'
						) 
						AND product.productDefaultCurrency = cur.currencyID
						) AS productCount ";
        }
        $sql .= " 
				FROM
					" . DB_PREFIX . "currencies cur ";
        if ($status == 'Active') {
            $sql .= " 
				WHERE 
					currencyStatus != 'Delete'";
        }
        if ($productCount == 'Yes') {
            $sql .= " 
				GROUP BY 
					cur.currencyID";
        }
        $sql .= $append;
        return $this->ObjDatabase->readValuesSlave($sql);
    }
    //FETCHING THE DETAILS OF ALL STANDARD CURRENCIES WHICH IS NOT ADDED TO THE SYSYTEM
    public function listStandardCurrencies($order = NULL) {
        $append = '';
        if ($order)
            $append = "ORDER BY
						" . $order;
        $sql                    = "SELECT 
				  standardCurrencyCode, 
				  CONCAT(
					standardCurrencyName, 
					'   -  ', 
					standardCurrencyCode
				  ) AS standardCurrencyName 
				FROM 
				  " . DB_PREFIX . "standard_currencies 
				WHERE 
				  standardCurrencyCode NOT IN (
					SELECT 
					  currencyCode 
					FROM 
					  " . DB_PREFIX . "currencies 
					WHERE 
					  currencyStatus != 'Delete'
				  ) " . $append . "";
        $resSelectStdCurrencies = $this->ObjDatabase->readValues($sql);
        return $resSelectStdCurrencies;
    }
    //GET STATNDARD CURRENCY DETAILS BY CURRENCY CODE
    public function getStandardCurrencyByCurrencyCode($standardCurrencyCode) {
        $sql                  = "SELECT 
				  * 
				FROM 
				  " . DB_PREFIX . "standard_currencies  
				WHERE 
				  standardCurrencyCode = '" . $standardCurrencyCode . "'";
        $resStdSelectCurrency = $this->ObjDatabase->readValue($sql);
        return $resStdSelectCurrency;
    }
    //FETCHING CURRENCY DETAILS BY ID
    public function getCurrencyByCurrencyID($currencyID, $status = '') {
        $sql = "SELECT 
				  *, 
				  IF(
					currencySymbolLeft = '', 
					currencySymbolRight, 
					currencySymbolLeft
				  ) currencySymbol, 
				  IF(
					currencySymbolLeft = '', 
					'Right', 
					'Left'
				  ) currencySymbolPosition  
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyID = '" . $currencyID . "'";
        if ($status != '') {
            $sql .= " AND currencyStatus='" . $status . "'";
        }
        $resSelectCurrency = $this->ObjDatabase->readValue($sql);
        return $resSelectCurrency;
    }
    //DELETING A PARTICULAR CURRENCY DETAILS (CHANGING THE STATUS TO DELETE)
    public function changeCurrencyStatus($currencyID, $status) {
        $sql                 = "UPDATE 
				  " . DB_PREFIX . "currencies 
				SET 
				  currencyStatus = '" . $status . "' 
				WHERE 
				  currencyID = '" . $currencyID . "'";
        $resUpdateCurrencies = $this->ObjDatabase->setQuery($sql);
        $this->addCurrenciestoSettings();
        if ($this->isDefaultCurrency($currencyID)) {
            $this->changeDefaultCurrency(1);
        }
        return true;
    }
    //UPDATING CURRENCY USING WEBSERVICEX API/GoogleAPI
    public function updateCurrencyExchRateUsingAPI($currencyID = 1) {
        try {
            $this->updateCurrencyExchangeRtateByAPI($currencyID);
        }
        catch (Exception $exc) {
            $this->updateCurrencyExchRateUsingGoogleAPI($currencyID);
        }
    }
    //SETTING A CURRENCY AS DEFAULT
    public function changeDefaultCurrency($currencyID) {
        $sql               = "UPDATE 
				  " . DB_PREFIX . "currencies 
				SET 
				  currencyDefault = 'No'";
        $resUpdateCurrency = $this->ObjDatabase->setQuery($sql);
        $sql               = "UPDATE 
				  " . DB_PREFIX . "currencies 
				SET 
				  currencyDefault = 'Yes', 
				  currencyValue = '1' 
				WHERE 
				  currencyID = '" . $currencyID . "'";
        $resUpdateCurrency = $this->ObjDatabase->setQuery($sql);
        $currencyDetails   = $this->getCurrencyByCurrencyID($currencyID);
        $sql               = "UPDATE 
				  " . DB_PREFIX . "settings 
				SET 
				  settingsValue = '" . $currencyDetails['currencyID'] . "' 
				WHERE 
				  settingsKey = 'default_currency'";
        $this->ObjDatabase->setQuery($sql);
        $ObjSettings = new SettingsModel();
        $ObjSettings->writeSettingsToXMLFile();
    }
    //INSERTING/UPDATING CURRENCY DETAILS
    public function saveCurrencyDetails($POST, $currencyID = NULL,$languages=array()) {
        $currencySymbolRight   = $currencySymbolLeft = '';
        $currencyName          = $this->escape($POST['currencyName']);
        $currencyCode          = $this->escape($POST['currencyCode']);
        $currencyDecimalPlaces = $this->escape($POST['currencyDecimalPlaces']);
        $currencyValue         = $this->escape($POST['currencyValue']);
        if ($this->isStandardCurrency($currencyCode))
            $currencyType = "Standard";
        else
            $currencyType = "Custom";
        $currencySymbolLeftOthers = array();
        $currencySymbolRightOthers= array();
        if ($currencyID) {
            $currencySymbolPosition = $this->escape($POST['currencySymbolPosition']);
            if(!empty($languages)) {
                
                foreach($languages as $lang) {
                    if($lang['languageCode']=='en') {
                        if ($currencySymbolPosition == "Left")
                           $currencySymbolLeft = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                       else
                           $currencySymbolRight = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                    } else {
                        if ($currencySymbolPosition == "Left")
                           $currencySymbolLeftOthers['currencySymbol_'.$lang['languageID']] = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                       else
                           $currencySymbolRightOthers['currencySymbol_'.$lang['languageID']] = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                    }
                }
                
            }
            if(!empty($currencySymbolLeftOthers)) {
                $currencySymbolLeftOthersValue = serialize($currencySymbolLeftOthers);                
            } else {
                $currencySymbolLeftOthersValue  = '';                
            }
            if(!empty($currencySymbolRightOthersValue)) {                
                $currencySymbolRightOthersValue= serialize($currencySymbolRightOthers);
            } else {                
                $currencySymbolRightOthersValue = '';
            }
            $sql               = "UPDATE 
					  " . DB_PREFIX . "currencies 
					SET 
					  currencyName = '" . $currencyName . "', 
					  currencyCode = '" . $currencyCode . "', 
					  currencySymbolLeft = '" . $currencySymbolLeft . "', 
					  currencySymbolRight = '" . $currencySymbolRight . "', 
					  currencyDecimalPlaces = '" . $currencyDecimalPlaces . "', 
					  currencyValue = '" . $currencyValue . "', 
					  currencyType = '" . $currencyType . "' ,
                                          currencySymbolLeftOthers= '" . $currencySymbolLeftOthersValue . "' ,
                                          currencySymbolRightOthers= '" . $currencySymbolRightOthersValue . "'    
					WHERE 
					  currencyID = '" . $currencyID . "'";
            $resUpdateCurrency = $this->ObjDatabase->setQuery($sql);
        } else {
            $currencySymbolPosition = $this->escape($POST['currencySymbolPosition' . $currencyID]);
            if(!empty($languages)) {
                
                foreach($languages as $lang) {
                    if($lang['languageCode']=='en') {
                        if ($currencySymbolPosition == "Left")
                           $currencySymbolLeft = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                       else
                           $currencySymbolRight = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                    } else {
                        if ($currencySymbolPosition == "Left")
                           $currencySymbolLeftOthers['currencySymbol_'.$lang['languageID']] = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                       else
                           $currencySymbolRightOthers['currencySymbol_'.$lang['languageID']] = $this->escape($POST['currencySymbol_'.$lang['languageID']]);
                    }
                }
                
            }
            if(!empty($currencySymbolLeftOthers)) {
                $currencySymbolLeftOthersValue = serialize($currencySymbolLeftOthers);                
            } else {
                $currencySymbolLeftOthersValue  = '';                
            }
            if(!empty($currencySymbolRightOthersValue)) {                
                $currencySymbolRightOthersValue= serialize($currencySymbolRightOthers);
            } else {                
                $currencySymbolRightOthersValue = '';
            }        
            $sql               = "INSERT INTO " . DB_PREFIX . "currencies (
					  currencyName, 
					  currencyCode, 
					  currencySymbolLeft, 
					  currencySymbolRight, 
					  currencyDecimalPlaces, 
					  currencyValue, 
					  currencyDefault, 
					  currencyType,
                                          currencySymbolLeftOthers,
                                          currencySymbolRightOthers
					) 
					VALUES 
					  (
						'" . $currencyName . "', 
						'" . $currencyCode . "', 
						'" . $currencySymbolLeft . "', 
						'" . $currencySymbolRight . "', 
						'" . $currencyDecimalPlaces . "', 
						'" . $currencyValue . "', 
						'No', 
						'" . $currencyType . "',
                                                '" . $currencySymbolLeftOthersValue . "',
                                                '" . $currencySymbolRightOthersValue . "'
					  )";
            $resInsertCurrency = $this->ObjDatabase->setQuery($sql);
        }
        $this->addCurrenciestoSettings();
    }
    //ADD STATNDARD CURRENCIES TO SYSTEM
    public function addStandardCurrencyToSystem($POST) {
        $currencyCode        = $this->escape($POST['currencyCodeStandard']);
        $currencyDetails     = $this->getStandardCurrencyByCurrencyCode($currencyCode);
        $currencyName        = $currencyDetails['standardCurrencyName'];
        $currencySymbolLeft  = $currencyDetails['standardCurrencySymbolLeft'];
        $currencySymbolRight = $currencyDetails['standardCurrencySymbolRight'];
        $sql                 = "INSERT INTO " . DB_PREFIX . "currencies (
						  currencyName, 
						  currencyCode, 
						  currencyValue, 
						  currencyType, 
						  currencyDefault, 
						  currencySymbolLeft, 
						  currencySymbolRight
						) 
						VALUES 
						  (
							'" . $currencyName . "', 
							'" . $currencyCode . "', 
							'" . $this->escape($POST['currencyValueStandard']) . "', 
							'Standard', 
							'No', 
							'" . $currencySymbolLeft . "', 
							'" . $currencySymbolRight . "'
						  )";
        $resInsertCurrency   = $this->ObjDatabase->setQuery($sql);
        $this->addCurrenciestoSettings();
    }
    //ADDING CURRENCY TO SETTINGS TABLE IN DATABASE
    public function addCurrenciestoSettings() {
        $defaultCurrency = '';
        $allCurrencies   = '';
        $rs              = $this->listCurrencies("currencyName ASC");
        for ($i = 0; $i < count($rs); $i++) {
            if ($rs[$i]["currencyType"] == "Standard") {
                if ($rs[$i]["currencyDefault"] != "Yes")
                    $allCurrencies .= $rs[$i]["currencyID"] . '->' . $rs[$i]["currencyName"] . '~*';
                else
                    $defaultCurrency .= $rs[$i]["currencyID"] . '->' . $rs[$i]["currencyName"] . '~*';
            }
        }
        $allCurrencies = substr($defaultCurrency . $allCurrencies, 0, -2);
        $sql           = "UPDATE 
				  " . DB_PREFIX . "settings 
				SET 
				  settingsOptions = '" . $allCurrencies . "' 
				WHERE 
				  settingsKey = 'default_currency'";
        $this->ObjDatabase->setQuery($sql);
    }
    //CHECKING CURRENCY CODE ALREADY EXISTS
    public function isCurrencyCodeAlreadyExists($currencyCode, $currencyID = NULL) {
        $append = '';
        if ($currencyID)
            $append = "AND currencyID != '" . $currencyID . "'";
        $sql               = "SELECT 
					currencyID
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyCode = '" . $currencyCode . "' 
				  AND currencyStatus != 'Delete' " . $append . "";
        $resSelectCurrency = $this->ObjDatabase->numberOfRecords($sql);
        if ($resSelectCurrency)
            return true;
        else
            return false;
    }
    //CHECKING WEATHER CURRENCY IS A STATNDARD CURRENCY AND RETURNS VALID RATE
    public function isStandardCurrency($currencyCode) {
        $sql                    = "SELECT 
					standardCurrencyID
				FROM 
				  " . DB_PREFIX . "standard_currencies 
				WHERE 
				  standardCurrencyCode = '" . $currencyCode . "'";
        $resSelectStdCurrencies = $this->ObjDatabase->numberOfRecords($sql);
        if ($resSelectStdCurrencies) {
            try {
                $wsdlURL                    = "http://www.webservicex.net/CurrencyConvertor.asmx?WSDL";
                $soap                       = new soapclient($wsdlURL);
                $parameters['FromCurrency'] = $currencyCode;
                $parameters['ToCurrency']   = "USD";
                $result                     = $soap->ConversionRate($parameters);
                $currencyValue              = $result->ConversionRateResult;
            }
            catch (Exception $exc) {
                return false;
            }
            if ($currencyValue != 0 || $currencyCode == 'USD') {
                return true;
            } else {
                return false;
            }
        } else
            return false;
    }
    //CHECKING IS DEFAULT CURRENCY
    public function isDefaultCurrency($currencyID) {
        $sql               = "SELECT 
					currencyID
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
					currencyID = '" . $currencyID . "' 
					AND currencyStatus != 'Delete' 
					AND currencyDefault = 'Yes'";
        $resSelectCurrency = $this->ObjDatabase->numberOfRecords($sql);
        if ($resSelectCurrency)
            return true;
        else
            return false;
    }
    //GET DEFAULT CURRENCY DETAILS
    public function getDefaultCurrencyDetails($currencyID = '', $type = '', $code = '') {
        if ($type == 'C') {
            $selectField = 'currencyCode,
							currencySymbolLeft,
							currencySymbolRight';
        } elseif ($type == 'ID') {
            $selectField = 'currencyID';
        } else {
            $selectField = '*';
        }
        $sql = "
				SELECT 
					" . $selectField . " 
				FROM 
					" . DB_PREFIX . "currencies 
				WHERE 
					currencyStatus != 'Delete'";
        if ($currencyID > 0) {
            $sql .= "
					AND currencyID='" . $currencyID . "' ";
        } else {
            $sql .= "
					AND currencyDefault='Yes'";
        }
        $sql .= "
				LIMIT 1";
        $resSelectCurrency = $this->ObjDatabase->readValueSlave($sql);
        if ($type == 'C') {
            if ($code != '') {
                $return_arr[0] = 'Code';
                $return_arr[1] = $resSelectCurrency['currencyCode'];
            } else {
                if ($resSelectCurrency['currencySymbolLeft'] != '') {
                    $return_arr[0] = 'Left';
                    $return_arr[1] = $resSelectCurrency['currencySymbolLeft'];
                } elseif ($resSelectCurrency['currencySymbolRight'] != '') {
                    $return_arr[0] = 'Right';
                    $return_arr[1] = $resSelectCurrency['currencySymbolRight'];
                }
            }
            return $return_arr;
        }
        return $resSelectCurrency;
    }
    public function getFormattedCurrency($OriginalPrice, $decimalType = "Rounded", $from = 0, $to = 0) // Full,Rounded
        {
        $decimalType = ($decimalType == '') ? 'Rounded' : $decimalType;
        if ($from > 0 && $to > 0) {
            $OriginalPrice = $this->convertValueCustom($OriginalPrice, $from, $to);
        }
        $OriginalPrice      = $this->getCurrencyValue($OriginalPrice, $decimalType, $to);
        $resDefaultCurrency = $this->getDefaultCurrencyDetails($to);
        if (count($resDefaultCurrency) < 1) {
            $resDefaultCurrency = $this->getDefaultCurrencyDetails();
        }
        if ($resDefaultCurrency['currencySymbolLeft'] != '') {
            $OriginalPrice = $resDefaultCurrency['currencySymbolLeft'] . $OriginalPrice;
        } elseif ($resDefaultCurrency['currencySymbolRight'] != '') {
            $OriginalPrice = $OriginalPrice . $resDefaultCurrency['currencySymbolRight'];
        } else {
            $OriginalPrice = $OriginalPrice . $resDefaultCurrency['currencyCode'];
        }
        return $OriginalPrice;
    }
    public function getFormattedAmount($OriginalPrice, $decimalType = "Rounded", $from = 0, $to = 0) // Full,Rounded
        {
        $decimalType = ($decimalType == '') ? 'Rounded' : $decimalType;
        if ($from > 0 && $to > 0) {
            $OriginalPrice = $this->convertValueCustom($OriginalPrice, $from, $to);
        }
        $OriginalPrice      = $this->getCurrencyValue($OriginalPrice, $decimalType, $to);
        $resDefaultCurrency = $this->getDefaultCurrencyDetails($to);
        if (count($resDefaultCurrency) < 1) {
            $resDefaultCurrency = $this->getDefaultCurrencyDetails();
        }
        return $OriginalPrice;
    }
    public function getCurrencyValue($OriginalPrice, $decimalType = "Rounded", $to = 0) {
        $resDefaultCurrency = $this->getDefaultCurrencyDetails($to);
        if (($decimalType == "Rounded")) {
            $currencyDecimalPlaces      = (($resDefaultCurrency['currencyDecimalPlaces']) > 0) ? ($resDefaultCurrency['currencyDecimalPlaces']) : 0;
            $OriginalPrice              = round($OriginalPrice, $currencyDecimalPlaces);
            $OriginalPriceArray         = explode(".", $OriginalPrice);
            $OriginalPriceDecimal       = isset($OriginalPriceArray[1]) ? $OriginalPriceArray[1] : '';
            $OriginalPriceDecimalLength = strlen($OriginalPriceDecimal);
            if ($OriginalPriceDecimalLength < $currencyDecimalPlaces && $OriginalPriceDecimal != "") {
                $OriginalPriceDecimalBalanceLength = $currencyDecimalPlaces - $OriginalPriceDecimalLength;
                $OriginalPrice                     = $OriginalPrice . str_repeat("0", $OriginalPriceDecimalBalanceLength);
            }
        } elseif (($decimalType == "Full")) {
            $currencyDecimalPlaces = (($resDefaultCurrency['currencyDecimalPlaces']) > 0) ? ($resDefaultCurrency['currencyDecimalPlaces']) : 0;
            $originalPrice         = number_format($originalPrice, $currencyDecimalPlaces, '.', '');
        }
        return $OriginalPrice;
    }
    //GET STATNDARD CURRENCY DETAILS BY CURRENCY CODE
    public function getCurrencyByCurrencyCode($currencyCode) {
        $sql               = "SELECT 
				  * 
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyCode = '" . $currencyCode . "'";
        $resSelectCurrency = $this->ObjDatabase->readValue($sql);
        return $resSelectCurrency;
    }
    public function checkCurrencyAmountPurchased($supportedCurrenciesArray, $purchasedCurrency, $default_currency, $getOrderDetails) {
        // IF NO SUPPORTED CURRENCIES THEN USD
        $resDefaultCurrency = $this->getDefaultCurrencyDetails($default_currency);
        $resSuppCurrency    = $this->getCurrencyByCurrencyID(1);
        $suppConversionRate = $resSuppCurrency['currencyValue'];
        if ($purchasedCurrency != $resDefaultCurrency['currencyCode']) {
            $purchaseCurrencyDetails = $this->getCurrencyByCurrencyCode($purchasedCurrency);
        }
        if (!in_array($purchasedCurrency, $supportedCurrenciesArray) && $purchasedCurrency == $resDefaultCurrency['currencyCode']) {
            //IF DEAFULT CURRENCY EQUAL TO PURCHASED CURRENCY AND NOT IN SUPPORTED CURRENCIES
            //Covenrt to USD
            $capturedTotalAmount   = $getOrderDetails['orderTotal'] * $suppConversionRate;
            $taxAmount             = $getOrderDetails['orderTaxAmount'] * $suppConversionRate;
            $paymentGatewayCurreny = "USD";
            //IF DEAFULT CURRENCY NOT EQUAL TO PURCHASED CURRENCY AND NOT IN SUPPORTED CURRENCIES
        } else if (!in_array($purchasedCurrency, $supportedCurrenciesArray) && $purchasedCurrency != $resDefaultCurrency['currencyCode']) {
            $convertedToDefaultAmount    = $getOrderDetails['orderTotal'] / $purchaseCurrencyDetails['currencyValue'];
            $convertedToDefaultTaxAmount = $getOrderDetails['orderTaxAmount'] / $purchaseCurrencyDetails['currencyValue'];
            if (!in_array($resDefaultCurrency['currencyCode'], $supportedCurrenciesArray)) {
                //Covenrt to USD
                $capturedTotalAmount   = $convertedToDefaultAmount * $suppConversionRate;
                $taxAmount             = $convertedToDefaultTaxAmount * $suppConversionRate;
                $paymentGatewayCurreny = "USD";
            } else {
                $capturedTotalAmount   = $convertedToDefaultAmount;
                $taxAmount             = $convertedToDefaultTaxAmount;
                $paymentGatewayCurreny = $resDefaultCurrency['currencyCode'];
            }
        } else {
            $capturedTotalAmount   = $getOrderDetails['orderTotal'];
            $taxAmount             = $getOrderDetails['orderTaxAmount'];
            $paymentGatewayCurreny = $purchasedCurrency;
        }
        $resParamsarray['convertedTotalAmount']           = $capturedTotalAmount;
        $resParamsarray['convertedTaxAmount']             = $taxAmount;
        $resParamsarray['convertedPaymentGatewayCurreny'] = $paymentGatewayCurreny;
        return $resParamsarray;
    }
    //List currency codes
    public function listCurrencyCodes($status = 'Active') {
        $sql = "SELECT 
				  currencyID,
				  currencyCode,
				  currencySymbolLeft,
				  currencySymbolRight 
				FROM 
				  " . DB_PREFIX . "currencies";
        if ($status == 'Active') {
            $sql .= " WHERE 
						currencyStatus = 'Active' ";
        }
        $sql .= " ORDER BY 
					currencyDefault ASC ";
        $resSelectCurrencies = $this->ObjDatabase->readValues($sql);
        return $resSelectCurrencies;
    }
    public function listProductCityCurrencies($productID, $from = 'product') {
        $sql                 = "SELECT 
					  cur.* 
					FROM 
					  " . DB_PREFIX . "currencies AS cur 
					WHERE 
					  currencyStatus != 'Delete' 
					GROUP BY 
					  cur.currencyID ";
        $resSelectCurrencies = $this->ObjDatabase->readValues($sql);
        return $resSelectCurrencies;
    }
    public function convertValueCustom($price = 0, $from = 0, $to = 0) {
        if ($price > 0 && $from > 0 && $to > 0) {
            if ($from == $to) {
                return $price;
            } else {
                $resDefaultCurrency = $this->getDefaultCurrencyDetails();                
                if (isset($resDefaultCurrency['currencyID']) && $resDefaultCurrency['currencyID'] == $to) {
                    $sql               = "SELECT 
							  currencyValue,currencyDecimalPlaces 
							FROM 
							  " . DB_PREFIX . "currencies 
							WHERE 
							  currencyID = '" . $from . "'";
                    $resSelectCurrency = $this->ObjDatabase->readValue($sql);
                    if ($resDefaultCurrency['currencyValue'] <= 0) {
                        return 0;
                    } else if ($resSelectCurrency['currencyValue'] <= 0) {
                        return 0;
                    } else {
                        $decimalPlace = $resDefaultCurrency['currencyDecimalPlaces'];
                        $returnPrice  = $price / $resSelectCurrency['currencyValue'];
                        return number_format($returnPrice, $decimalPlace, '.', '');
                    }
                } else if (isset($resDefaultCurrency['currencyID']) && $resDefaultCurrency['currencyID'] == $from) {
                    $sql               = "SELECT 
							  currencyValue,currencyDecimalPlaces 
							FROM 
							  " . DB_PREFIX . "currencies 
							WHERE 
							  currencyID = '" . $to . "'";
                    $resSelectCurrency = $this->ObjDatabase->readValue($sql);
                    
                    $decimalPlace      = $resSelectCurrency['currencyDecimalPlaces'];                    
                    $returnPrice       = $price * $resSelectCurrency['currencyValue'];
                    
                    return number_format($returnPrice, $decimalPlace, '.', '');
                } else {
                    $sql               = "SELECT 
							  currencyValue,currencyDecimalPlaces 
							FROM 
							  " . DB_PREFIX . "currencies 
							WHERE 
							  currencyID = '" . $from . "'";
                    $resSelectCurrency = $this->ObjDatabase->readValue($sql);
                    $defaultCurValue   = $price / $resSelectCurrency['currencyValue'];
                    $sql               = "SELECT 
							  currencyValue,currencyDecimalPlaces 
							FROM 
							  " . DB_PREFIX . "currencies 
							WHERE 
							  currencyID = '" . $to . "'";
                    $resSelectCurrency = $this->ObjDatabase->readValue($sql);
                    $decimalPlace      = $resSelectCurrency['currencyDecimalPlaces'];
                    $returnPrice       = $defaultCurValue * $resSelectCurrency['currencyValue'];
                    return number_format($returnPrice, $decimalPlace, '.', '');
                }
            }
        } else {
            return $price;
        }
    }
    public function getUpcomingProductCount($currencyID) {
        $sql                 = "SELECT 
				  COUNT(product.productDefaultCurrency) AS productCount 
				FROM 
				  " . DB_PREFIX . "products product 
				  LEFT JOIN " . DB_PREFIX . "timezones AS timz ON (
					product.productTimeZone = timz.timeZoneID
				  ) 
				WHERE 
				  product.productEndTime >= IF (
					(
					  CONVERT_TZ(
						NOW(), 
						@@session.time_zone, 
						timz.timeZoneDiff
					  )> 0
					), 
					CONVERT_TZ(
					  NOW(), 
					  @@session.time_zone, 
					  timz.timeZoneDiff
					), 
					NOW()
				  ) 
				  AND (
					product.productStatus = 'Active' 
					OR product.productStatus = 'Inactive'
				  ) 
				  AND product.productDefaultCurrency = '" . $currencyID . "'";
        $resSelectCurrencies = $this->ObjDatabase->readValue($sql);
        return $resSelectCurrencies['productCount'];
    }
    //Webservice X
    public function updateCurrencyExchRateUsingWebservicexAPI($currencyID = 1) {
        $sql                        = "SELECT 
				  currencyCode 
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyID = '" . $currencyID . "'";
        $resSelectCurrency          = $this->ObjDatabase->readValue($sql);
        $currencyCode               = $resSelectCurrency['currencyCode'];
        $wsdlURL                    = "http://www.webservicex.net/CurrencyConvertor.asmx?WSDL";
        $soap                       = new soapclient($wsdlURL);
        $parameters['FromCurrency'] = $currencyCode;
        $sql                        = "SELECT 
				  * 
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyStatus != 'Delete' 
				  AND currencyID != '" . $currencyID . "' 
				  AND currencyType = 'Standard'";
        $resSelectCurrencies        = $this->ObjDatabase->readValues($sql);
        for ($k = 0; $k < count($resSelectCurrencies); $k++) {
            $parameters['ToCurrency'] = $resSelectCurrencies[$k]['currencyCode'];
            $result                   = $soap->ConversionRate($parameters);
            $currencyValue            = $result->ConversionRateResult;
            if ($currencyValue != 0) {
                $sql               = "UPDATE 
						  " . DB_PREFIX . "currencies 
						SET 
						  currencyValue = '" . $currencyValue . "' 
						WHERE 
						  currencyID = '" . $resSelectCurrencies[$k]['currencyID'] . "'";
                $resUpdateCurrency = $this->ObjDatabase->setQuery($sql);
            }
        }
    }
    //Google Calculate
    public function updateCurrencyExchRateUsingGoogleAPI($currencyID = 1) {
        $sql                 = "SELECT 
				  currencyCode 
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyID = '" . $currencyID . "'";
        $resSelectCurrency   = $this->ObjDatabase->readValue($sql);
        $fromCurrency        = $resSelectCurrency['currencyCode'];
        $sql                 = "SELECT 
				  * 
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyStatus != 'Delete' 
				  AND currencyID != '" . $currencyID . "' 
				  AND currencyType = 'Standard'";
        $resSelectCurrencies = $this->ObjDatabase->readValues($sql);
        for ($k = 0; $k < count($resSelectCurrencies); $k++) {
            $toCurrency    = $resSelectCurrencies[$k]['currencyCode'];
            $currencyValue = $this->googleConvert($fromCurrency, $toCurrency, 1);
            if ($currencyValue != 0) {
                $sql               = "UPDATE 
						  " . DB_PREFIX . "currencies 
						SET 
						  currencyValue = '" . $currencyValue . "' 
						WHERE 
						  currencyID = '" . $resSelectCurrencies[$k]['currencyID'] . "'";
                $resUpdateCurrency = $this->ObjDatabase->setQuery($sql);
            }
        }
    }
    private function googleConvert($fromCurrency, $toCurrency, $amount) {
        $amount          = urlencode($amount);
        $fromCurrency    = urlencode($fromCurrency);
        $toCurrency      = urlencode($toCurrency);
        /* $url = "http://www.google.com/ig/calculator?hl=en&q=$amount$fromCurrency=?$toCurrency";
        $ch = curl_init();
        $timeout = 0;
        curl_setopt ($ch, CURLOPT_URL, $url);
        curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch,  CURLOPT_USERAGENT , "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1)");
        curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
        $rawdata = curl_exec($ch);
        curl_close($ch);
        $data = explode('"', $rawdata);
        $data = explode(' ', $data['3']);
        $var = $data['0'];
        return round($var,5); */
        $get             = file_get_contents("https://www.google.com/finance/converter?a=$amount&from=$fromCurrency&to=$toCurrency");
        $get             = explode("<span class=bld>", $get);
        $get             = explode("</span>", $get[1]);
        $convertedAmount = preg_replace("/[^0-9\.]/", null, $get[0]);
        return round($convertedAmount, 5);
    }
     public function updateCurrencyExchangeRtateByAPI($currencyID)
    {   
         $sql                        = "SELECT 
				  currencyCode 
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyID = '" . $currencyID . "'";
        $baseCurrency          = $this->ObjDatabase->getField($sql);
        $sql                        = "SELECT 
				  * 
				FROM 
				  " . DB_PREFIX . "currencies 
				WHERE 
				  currencyStatus != 'Delete' 
				  
				  ";
        $resSelectCurrencies        = $this->ObjDatabase->readValues($sql);
        $symbols = '';
           // Replace with your API key
	$api_key = "83efbc82ea2f9c2faf84b10195718db9";
        $currency1 = $baseCurrency;
        foreach($resSelectCurrencies as $key=>$currencies){
            $symbols .= ($key>0)?',':'';
            $symbols .= $currencies['currencyCode'];
        }
            $url = "http://apilayer.net/api/live?access_key=$api_key&currencies=$symbols&format=1";
            $ch = curl_init();  
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 15); 
            curl_setopt($ch, CURLOPT_TIMEOUT, 30);
            $json_string = curl_exec($ch);
            curl_close($ch);
            $parsed_json = json_decode($json_string,true);
           // echo "<pre>";print_r($parsed_json);exit;
            $success = $parsed_json->success;
            $source = $parsed_json->source;
            foreach($resSelectCurrencies as $key=>$currencies){
                if($source==$baseCurrency){
                    $currencyValue = $parsed_json['quotes']['USD'.$currencies['currencyCode']];
                } else {
                    $baseCurrencyValue = $parsed_json['quotes']['USD'.$baseCurrency];
                    $customCurrencyValue = $parsed_json['quotes']['USD'.$currencies['currencyCode']];
                    $currencyValue = $customCurrencyValue/$baseCurrencyValue;
                }
                if($currencyValue && $currencies['currencyID']!=$currencyID){
                    echo "<br />". $sql               = "UPDATE 
                                                      " . DB_PREFIX . "currencies 
                                                    SET 
                                                      currencyValue = '" . $currencyValue . "' 
                                                    WHERE 
                                                      currencyID = '" . $currencies['currencyID'] . "'";
                     $this->ObjDatabase->setQuery($sql);
                }
            }
        return true;                
    }
}