public function getRates($otherCurrencyCode, $dateParam, $cron = false) { $db = PearDatabase::getInstance(); $moduleModel = Settings_CurrencyUpdate_Module_Model::getCleanInstance(); $selectedBank = $moduleModel->getActiveBankId(); $yesterday = date('Y-m-d', strtotime('-1 day')); // check if data is correct, currency rates can be retrieved only for working days $lastWorkingDay = Vtiger_Functions::getLastWorkingDay($yesterday); $today = date('Y-m-d'); $mainCurrency = Vtiger_Functions::getDefaultCurrencyInfo()['currency_code']; $dateCur = $dateParam; $chosenYear = date('Y', strtotime($dateCur)); $date = str_replace('-', '', $dateCur); $date = substr($date, 2); if (date('Y') == $chosenYear) { $txtSrc = 'http://www.nbp.pl/kursy/xml/dir.txt'; } else { $txtSrc = 'http://www.nbp.pl/kursy/xml/dir' . $chosenYear . '.txt'; } $xmlSrc = 'http://nbp.pl/kursy/xml/'; $newXmlSrc = ''; $file = file($txtSrc); $fileNum = count($file); $numberOfDays = 1; $stateA = false; while (!$stateA && $file) { for ($i = 0; $i < $fileNum; $i++) { $lineStart = strstr($file[$i], $date, true); if ($lineStart && $lineStart[0] == 'a') { $stateA = true; $newXmlSrc = $xmlSrc . $lineStart . $date . '.xml'; } } if ($stateA == false) { $newDate = strtotime("-{$numberOfDays} day", strtotime($dateCur)); $newDate = date('Y-m-d', $newDate); $date = str_replace('-', '', $newDate); $date = substr($date, 2); $numberOfDays++; } } $xml = simplexml_load_file($newXmlSrc); $xmlObj = $xml->children(); $num = count($xmlObj->pozycja); $datePublicationOfFile = $xmlObj->data_publikacji->__toString(); $exchangeRate = 1.0; // if currency is diffrent than PLN we need to calculate rate for converting other currencies to this one from PLN if ($mainCurrency != $this->getMainCurrencyCode()) { for ($i = 0; $i <= $num; $i++) { if ($xmlObj->pozycja[$i]->kod_waluty == $mainCurrency) { $exchangeRate = str_replace(',', '.', $xmlObj->pozycja[$i]->kurs_sredni); } } } for ($i = 0; $i <= $num; $i++) { if (!$xmlObj->pozycja[$i]->nazwa_waluty) { continue; } $currency = $xmlObj->pozycja[$i]->kod_waluty->__toString(); foreach ($otherCurrencyCode as $key => $currId) { if ($key == $currency && $currency != $mainCurrency) { $exchange = str_replace(',', '.', $xmlObj->pozycja[$i]->kurs_sredni); $exchange = $exchange / $xmlObj->pozycja[$i]->przelicznik; $exchangeVtiger = $exchangeRate / $exchange; $exchange = $exchange / $exchangeRate; if ($cron == true || (strtotime($dateParam) == strtotime($today) || strtotime($dateParam) == strtotime($lastWorkingDay))) { $moduleModel->setCRMConversionRate($currency, $exchangeVtiger); } $existingId = $moduleModel->getCurrencyRateId($currId, $datePublicationOfFile, $selectedBank); if ($existingId > 0) { $moduleModel->updateCurrencyRate($existingId, $exchange); } else { $moduleModel->addCurrencyRate($currId, $datePublicationOfFile, $exchange, $selectedBank); } } } } // currency diffrent than PLN, we need to add manually PLN rates if ($mainCurrency != $this->getMainCurrencyCode()) { $exchange = 1.0 / $exchangeRate; $mainCurrencyId = false; foreach ($otherCurrencyCode as $code => $id) { if ($code == $this->getMainCurrencyCode()) { $mainCurrencyId = $id; } } if ($mainCurrencyId) { if ($cron == true || (strtotime($dateParam) == strtotime($today) || strtotime($dateParam) == strtotime($lastWorkingDay))) { $moduleModel->setCRMConversionRate($this->getMainCurrencyCode(), $exchangeRate); } $existingId = $moduleModel->getCurrencyRateId($mainCurrencyId, $datePublicationOfFile, $selectedBank); if ($existingId > 0) { $moduleModel->updateCurrencyRate($existingId, $exchange); } else { $moduleModel->addCurrencyRate($mainCurrencyId, $datePublicationOfFile, $exchange, $selectedBank); } } } }
public function process(Vtiger_Request $request) { $log = vglobal('log'); $log->debug('Start ' . __CLASS__ . ':' . __FUNCTION__); $db = PearDatabase::getInstance(); $qualifiedModule = $request->getModule(false); $moduleModel = Settings_CurrencyUpdate_Module_Model::getCleanInstance(); $currentUser = Users_Record_Model::getCurrentUserModel(); // synchronise bank list $moduleModel->refreshBanks(); $downloadBtn = !$request->isEmpty('download') ? $request->get('download') : false; $date = !$request->isEmpty('duedate') ? Vtiger_Datetime_UIType::getDBInsertedValue($request->get('duedate')) : false; $dateCur = ''; if ($date) { // if its future date change it to present one if (strtotime($date) > strtotime(date('Y-m-d'))) { $date = date('Y-m-d'); } $dateCur = $date; } else { $dateCur = date('Y-m-d'); } // take currency rates for yesterday if (strcmp(date('Y-m-d'), $dateCur) == 0) { $dateCur = strtotime("-1 day", strtotime($dateCur)); $dateCur = date('Y-m-d', $dateCur); } $dateCur = Vtiger_Functions::getLastWorkingDay($dateCur); // get currency if not already archived if ($downloadBtn) { $moduleModel->fetchCurrencyRates($dateCur); } $selectBankId = $moduleModel->getActiveBankId(); $history = $moduleModel->getRatesHistory($selectBankId, $dateCur, $request); $bankTab = array(); $bankSQL = "SELECT * FROM yetiforce_currencyupdate_banks"; $bankResult = $db->query($bankSQL, true); $i = 0; while ($row = $db->fetchByAssoc($bankResult)) { $bankTab[$i]['id'] = $row['id']; $bankName = $row['bank_name']; $bankTab[$i]['bank_name'] = $bankName; $bankTab[$i]['active'] = $row['active']; $i++; } // number of currencies $curr_num = $moduleModel->getCurrencyNum(); // get info about main currency $mainCurrencyInfo = Vtiger_Functions::getDefaultCurrencyInfo(); $viewer = $this->getViewer($request); $viewer->assign('QUALIFIED_MODULE', $qualifiedModule); $viewer->assign('USER_MODEL', $currentUser); $viewer->assign('MODULE_MODEL', $moduleModel); $viewer->assign('MODULENAME', 'CurrencyUpdate'); $viewer->assign('DATE', $request->has('duedate') ? Vtiger_Date_UIType::getDisplayValue($dateCur) : ''); $viewer->assign('CURRNUM', $curr_num); $viewer->assign('BANK', $bankTab); $viewer->assign('HISTORIA', $history); $viewer->assign('MAINCURR', $mainCurrencyInfo); $viewer->assign('SUPPORTED_CURRENCIES', $moduleModel->getSupportedCurrencies()); $viewer->assign('UNSUPPORTED_CURRENCIES', $moduleModel->getUnSupportedCurrencies()); $viewer->view('Index.tpl', $qualifiedModule); $log->debug('End ' . __CLASS__ . ':' . __FUNCTION__); }
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); }
public function getRates($otherCurrencyCode, $dateParam, $cron = false) { $db = PearDatabase::getInstance(); $moduleModel = Settings_CurrencyUpdate_Module_Model::getCleanInstance(); $selectedBank = $moduleModel->getActiveBankId(); $yesterday = date('Y-m-d', strtotime('-1 day')); // check if data is correct, currency rates can be retrieved only for working days $lastWorkingDay = Vtiger_Functions::getLastWorkingDay($yesterday); $today = date('Y-m-d'); $mainCurrency = Vtiger_Functions::getDefaultCurrencyInfo()['currency_code']; // source, ECB has 2 sources for older rates // 0 - last 90 days // 1 - historical data from year 1999 // we speed script choosing the smaller file for our needs $source = $this->getSource(); //explode( '*|*', $this->getBankSource() ); // how old is the currency rate $now = time(); // or your date as well $rateDay = strtotime($dateParam); $datediff = $now - $rateDay; if (floor($datediff / (60 * 60 * 24)) >= 90) { $sourceURL = $source[1]; } else { $sourceURL = $source[0]; } $XML = simplexml_load_file($sourceURL); // European Central Bank xml only contains business days! oh well.... if ($XML === false) { return false; } $xml_obj = $XML->children(); $num = count($xml_obj->pozycja); $datePublicationOfFile = $dateParam; $exchangeRate = 1.0; // if currency is diffrent than EUR we need to calculate rate for converting other currencies to this one from EUR if ($mainCurrency != $this->getMainCurrencyCode()) { $foundRate = false; foreach ($XML->Cube->Cube as $time) { if ($time["time"] == $dateParam) { foreach ($time->Cube as $rate) { if ($rate['currency'] == $mainCurrency) { $exchangeRate = $rate['rate']; $foundRate = true; } if ($foundRate) { break; } } } if ($foundRate) { break; } } } $foundRate = false; foreach ($XML->Cube->Cube as $time) { if ($time["time"] == $dateParam) { $num = count($time->Cube); for ($i = 0; $i < $num; $i++) { $currency = $time->Cube[$i]['currency']->__toString(); // currency code foreach ($otherCurrencyCode as $key => $currId) { if ($key == $currency && $currency != $mainCurrency) { $exchange = $time->Cube[$i]['rate']; $exchangeVtiger = (double) $exchange / (double) $exchangeRate; $exchange = (double) $exchangeRate / (double) $exchange; if ($cron == true || (strtotime($dateParam) == strtotime($today) || strtotime($dateParam) == strtotime($lastWorkingDay))) { $moduleModel->setCRMConversionRate($currency, $exchangeVtiger); } $existingId = $moduleModel->getCurrencyRateId($currId, $datePublicationOfFile, $selectedBank); if ($existingId > 0) { $moduleModel->updateCurrencyRate($existingId, $exchange); } else { $moduleModel->addCurrencyRate($currId, $datePublicationOfFile, $exchange, $selectedBank); } } } } $foundRate = true; } if ($foundRate) { break; } } // currency diffrent than EUR, we need to add manually EUR rates if ($mainCurrency != $this->getMainCurrencyCode()) { $yfRate = 1.0 / (double) $exchangeRate; $exchange = (double) $exchangeRate; $mainCurrencyId = false; foreach ($otherCurrencyCode as $code => $id) { if ($code == $this->getMainCurrencyCode()) { $mainCurrencyId = $id; } } if ($mainCurrencyId) { if ($cron == true || (strtotime($dateParam) == strtotime($today) || strtotime($dateParam) == strtotime($lastWorkingDay))) { $moduleModel->setCRMConversionRate($this->getMainCurrencyCode(), $yfRate); } $existingId = $moduleModel->getCurrencyRateId($mainCurrencyId, $datePublicationOfFile, $selectedBank); if ($existingId > 0) { $moduleModel->updateCurrencyRate($existingId, $exchange); } else { $moduleModel->addCurrencyRate($mainCurrencyId, $datePublicationOfFile, $exchange, $selectedBank); } } } }
public function getRates($otherCurrencyCode, $dateParam, $cron = false) { $db = PearDatabase::getInstance(); $moduleModel = Settings_CurrencyUpdate_Module_Model::getCleanInstance(); $selectedBank = $moduleModel->getActiveBankId(); $yesterday = date('Y-m-d', strtotime('-1 day')); // check if data is correct, currency rates can be retrieved only for working days $lastWorkingDay = Vtiger_Functions::getLastWorkingDay($yesterday); $today = date('Y-m-d'); $mainCurrency = Vtiger_Functions::getDefaultCurrencyInfo()['currency_code']; $dateCur = $dateParam; $source = $this->getSource(); $client = new \SoapClient($source[0]); $curs = $client->GetCursOnDate(array('On_date' => $dateCur)); $ratesXml = new \SimpleXMLElement($curs->GetCursOnDateResult->any); $datePublicationOfFile = $dateCur; $exchangeRate = 1.0; // if currency is diffrent than RUB we need to calculate rate for converting other currencies to this one from RUB if ($mainCurrency != $this->getMainCurrencyCode()) { foreach ($ratesXml->ValuteData[0] as $currencyRate) { if ($currencyRate->VchCode == $mainCurrency) { echo $currencyRate->VchCode . ' == ' . $mainCurrency . ' = ' . $currencyRate->Vcurs; $exchangeRate = $currencyRate->Vcurs; } } } foreach ($ratesXml->ValuteData[0] as $currencyRate) { $currency = (string) $currencyRate->VchCode; foreach ($otherCurrencyCode as $key => $currId) { if ($key == $currency && $currency != $mainCurrency) { $curs = (string) $currencyRate->Vcurs; $nom = (string) $currencyRate->Vnom; $exchange = $curs / $nom; $exchangeVtiger = $exchangeRate / $exchange; $exchange = $exchange / $exchangeRate; if ($cron == true || (strtotime($dateParam) == strtotime($today) || strtotime($dateParam) == strtotime($lastWorkingDay))) { $moduleModel->setCRMConversionRate($currency, $exchangeVtiger); } $existingId = $moduleModel->getCurrencyRateId($currId, $datePublicationOfFile, $selectedBank); if ($existingId > 0) { $moduleModel->updateCurrencyRate($existingId, $exchange); } else { $moduleModel->addCurrencyRate($currId, $datePublicationOfFile, $exchange, $selectedBank); } } } } // currency diffrent than RUB, we need to add manually RUB rates if ($mainCurrency != $this->getMainCurrencyCode()) { $exchange = 1.0 / $exchangeRate; $mainCurrencyId = false; foreach ($otherCurrencyCode as $code => $id) { if ($code == $this->getMainCurrencyCode()) { $mainCurrencyId = $id; } } if ($mainCurrencyId) { if ($cron == true || (strtotime($dateParam) == strtotime($today) || strtotime($dateParam) == strtotime($lastWorkingDay))) { $moduleModel->setCRMConversionRate($this->getMainCurrencyCode(), $exchangeRate); } $existingId = $moduleModel->getCurrencyRateId($mainCurrencyId, $datePublicationOfFile, $selectedBank); if ($existingId > 0) { $moduleModel->updateCurrencyRate($existingId, $exchange); } else { $moduleModel->addCurrencyRate($mainCurrencyId, $datePublicationOfFile, $exchange, $selectedBank); } } } }