/**
* Getting value to display
* @return array
*/
public function typeValues()
{
return Vtiger_Functions::getAllCurrency(true);
}
public function getCRMConversionRate($from, $to, $date = '')
{
$db = PearDatabase::getInstance();
$mainCurrencyCode = Vtiger_Functions::getDefaultCurrencyInfo()['currency_code'];
$activeBankId = self::getActiveBankId();
$exchange = false;
if (is_numeric($from)) {
$from = Vtiger_Functions::getAllCurrency(true)[$from]['currency_code'];
}
if (is_numeric($to)) {
$to = Vtiger_Functions::getAllCurrency(true)[$to]['currency_code'];
}
// get present conversion rate from crm
if (empty($date)) {
$query = 'SELECT `conversion_rate` FROM `vtiger_currency_info` WHERE `currency_code` = ? LIMIT 1;';
$result = $db->pquery($query, [$to]);
$exchange = floatval($db->getSingleValue($result));
if ($from != $mainCurrencyCode) {
$convertToMainCurrency = 1 / $exchange;
$query = 'SELECT `conversion_rate` FROM `vtiger_currency_info` WHERE `currency_code` = ? LIMIT 1;';
$result = $db->pquery($query, [$from]);
$fromExchange = floatval($db->getSingleValue($result));
$exchange = 1 / ($fromExchange * $convertToMainCurrency);
}
} else {
$query = 'SELECT
COUNT(1) as num
FROM
`yetiforce_currencyupdate` yfc
INNER JOIN `vtiger_currency_info` cur
ON yfc.`currency_id` = cur.`id` AND cur.`deleted` = 0
WHERE
yfc.`exchange_date` = ? AND
yfc.`bank_id` = ? AND
cur.`currency_code` = ?
LIMIT 1;';
$result = $db->pquery($query, [$date, $activeBankId, $to]);
$num = floatval($db->getSingleValue($result));
// no exchange rate in archive, fetch new rates
if ($num == 0) {
self::fetchCurrencyRates($date);
}
$query = 'SELECT
yfc.`exchange`
FROM
`yetiforce_currencyupdate` yfc
INNER JOIN `vtiger_currency_info` cur
ON yfc.`currency_id` = cur.`id` AND cur.`deleted` = 0
WHERE
yfc.`exchange_date` = ? AND
yfc.`bank_id` = ? AND
cur.`currency_code` = ?
LIMIT 1;';
$result = $db->pquery($query, [$date, $activeBankId, $to]);
$exchange = floatval($db->getSingleValue($result));
if ($exchange > 0) {
$exchange = 1 / $exchange;
}
if ($from != $mainCurrencyCode) {
$convertToMainCurrency = $exchange == 0 ? 1 : 1 / $exchange;
$query = 'SELECT
yfc.`exchange`
FROM
`yetiforce_currencyupdate` yfc
INNER JOIN `vtiger_currency_info` cur
ON yfc.`currency_id` = cur.`id` AND cur.`deleted` = 0
WHERE
yfc.`exchange_date` = ? AND
yfc.`bank_id` = ? AND
cur.`currency_code` = ?
LIMIT 1;';
$result = $db->pquery($query, [$date, $activeBankId, $from]);
$fromExchange = floatval($db->getSingleValue($result));
if ($from != $mainCurrencyCode && $to != $mainCurrencyCode) {
$exchange = $fromExchange / $convertToMainCurrency;
} else {
$exchange = $fromExchange * $convertToMainCurrency;
}
}
}
return $exchange = round($exchange, 5);
}