//@include lib_Class.js //@include lib_sol.common.DateUtils.js //@include lib_sol.common.Config.js //@include lib_sol.common.HttpUtils.js /** * Contains functions for loading exchange rates and converting amounts. * * Exchange rates refer to EUR as base currency. * * @author PZ, ELO Digital Office GmbH * @version 1.0 * */ sol.define("sol.common_fx.ix.FxUtils", { singleton: true, /** * @private * @property logger */ initialize: function () { this.logger = sol.create("sol.Logger", { scope: this.$className }); }, /** * @private */ configuration: { baseCurrency: "EUR", serviceUrl: "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml", serviceUrlHistory: "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml", regex: /<Cube currency='(.*?)' rate='(.*?)'\/>/g, timeRegex: /<Cube time='(.*?)'>/, // optional, fall back is the date of execution timeFormat: "yyyy-MM-dd", // required only if 'timeRegex' is defined, format see java.text.SimpleDateFormat, isoDateTimeFormat: "YYYYMMDDHHmmss", updateStatement: "UPDATE sol_common_fx SET currency_exchange_rate=?,time=? WHERE currency_code=?", selectStatement: "SELECT currency_code, currency_exchange_rate FROM sol_common_fx WHERE currency_code=?", selectHistoryCountStatement: "SELECT COUNT(*) FROM sol_common_fx_history", selectCurrencyCodesStatement: "SELECT currency_code FROM sol_common_fx", truncateHistoryTable: "TRUNCATE TABLE sol_common_fx_history", selectHistoryStatement: "SELECT currency_code, currency_exchange_rate, time FROM sol_common_fx_history WHERE currency_code=? AND time=?", updateHistoryStatement: "UPDATE sol_common_fx_history SET currency_exchange_rate=? WHERE currency_code=? AND time=?", insertHistoryStatement: "INSERT INTO sol_common_fx_history (currency_code, currency_exchange_rate, time, base_currency_code) VALUES ( ?, ?, ?, ?)", selectByDateAndCodeStatement: "SELECT currency_exchange_rate, time FROM sol_common_fx_history WHERE currency_code=? AND time >=? AND time <=? ORDER BY time", selectOldestEntryByCodeStatement: "SELECT currency_exchange_rate, time FROM sol_common_fx_history WHERE currency_code=? AND time=(SELECT MIN(time) FROM sol_common_fx_history WHERE currency_code=?)", selectYoungestBaseCurrencyEntryStatement: "SELECT time FROM sol_common_fx_history WHERE currency_code=? AND time=(SELECT MAX(time) FROM sol_common_fx_history WHERE currency_code =?)" }, /** * Loads the exchange rates from an external service. * * Result object: * * { * "USD": "1.1389", * "JPY": "136.33", * "BGN": "1.9558", * ... * } * * @param {Boolean} persist (optional) If `true`, the database will be updated * @return {Object} The exchange rates */ loadExchangeRates: function (persist) { var me = this, currencies, serviceUrl, currencyRegex, response, time, matches, code, rate; currencies = { EUR: "1" }; me.fxConfig = sol.create("sol.common.Config", { compose: "/common_fx/Configuration/fx.config" }).config; serviceUrl = me.configuration.serviceUrl; currencyRegex = me.configuration.regex; response = me.getExchangeRates(serviceUrl); time = me.parseDate(response); if (response && (response.length > 0)) { while (matches = currencyRegex.exec(response)) { code = matches[1]; rate = matches[2]; currencies[code] = rate; } } else { throw "empty response from '" + serviceUrl + "'"; } currencies = me.convertLocalCurrency(currencies); if ((typeof persist !== "undefined") && (persist === true)) { if (!me.historicDataFilled(time)) { try { me.fillHistoricData(me.configuration.serviceUrlHistory); } catch (e) { me.logger.warn("Error writing historical exchange rates", e); } } sol.common_fx.ix.FxUtils.updateExchangeRates(currencies, time); try { sol.common_fx.ix.FxUtils.recordExchangeRateHistory(currencies, time, true); } catch (error) { me.logger.warn("Error writing historical exchange rates", error); } } return currencies; }, /** * @private * checks data count and latest entry in history table * @param {String} isoDateStr * @returns {Boolean} */ historicDataFilled: function (isoDateStr) { var me = this, result = true; try { result = me.historyTableHasEntries(); if (result === false) { return result; } result = me.latestHistoryDateIsCurrent(isoDateStr); if (result === false) { return result; } } catch (error) { me.logger.warn("Error reading historical exchange rate", error); } return result; }, /** * checks if the history table has entries * @returns {Boolean} */ historyTableHasEntries: function () { var me = this, dbConnection, count; dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); count = dbConnection.query(me.configuration.selectHistoryCountStatement); return !(!count || count.length < 1 || count[0][0] < 1); }, /** * @private * checks if latest date for the base currency * in the history table is older than the * two days before the given isodate string * @param {String} isoDateStr * @returns {Boolean} */ latestHistoryDateIsCurrent: function (isoDateStr) { var me = this, isoDateTimeFormat, fxConfig, localCurrency, dbConnection, ret, youngestISOString, dailyIso, twoDaysAgo; isoDateTimeFormat = me.configuration.isoDateTimeFormat || "YYYYMMDDHHmmss"; if (!isoDateStr || !sol.common.DateUtils.parse(isoDateStr, isoDateTimeFormat)) { throw "ISO String is missing or has an unexpected format"; } fxConfig = me.fxConfig || sol.create("sol.common.Config", { compose: "/common_fx/Configuration/fx.config" }).config; localCurrency = fxConfig.localCurrency || "EUR"; dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); ret = dbConnection.query(me.configuration.selectYoungestBaseCurrencyEntryStatement, [localCurrency, localCurrency]); if (!ret || ret.length < 1) { throw "Could not determine latest timestamp for base currency"; } youngestISOString = String(ret[0][0]); if (!sol.common.DateUtils.parse(youngestISOString, isoDateTimeFormat)) { throw "ISO string is not correctly formatted"; } dailyIso = sol.common.DateUtils.isoToMoment(isoDateStr, { startOfDay: true }); twoDaysAgo = dailyIso.clone().subtract("2", "days").format(isoDateTimeFormat); return !(sol.common.DateUtils.parse(twoDaysAgo, isoDateTimeFormat) && (youngestISOString < twoDaysAgo)); }, /** * @private * requests and parses an xml file containing historical * foreign exchange rates * and writes these entries to the history table * @param {String} serviceUrl */ fillHistoricData: function (serviceUrl) { var me = this, response, xmlDoc, documentBuilder, xmlRates, sdf = new Packages.java.text.SimpleDateFormat(me.configuration.timeFormat), historicData = {}, i, time, xmlRate, code, rate, dbConnection; dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); dbConnection.update(me.configuration.truncateHistoryTable); response = me.getExchangeRates(serviceUrl); documentBuilder = Packages.javax.xml.parsers.DocumentBuilderFactory.newInstance().newDocumentBuilder(); xmlDoc = documentBuilder.parse(new Packages.org.xml.sax.InputSource(new java.io.StringReader(response))); xmlRates = xmlDoc.getElementsByTagName("Cube"); for (i = 0; i < xmlRates.length; i++) { xmlRate = xmlRates.item(i); time = xmlRate.parentNode.getAttribute("time") + ""; if (time) { time = sdf.parse(time); time = ixConnect.dateToIso(time) + ""; if (!historicData[time]) { historicData[time] = { EUR: 1 }; } code = xmlRate.getAttribute("currency"); rate = xmlRate.getAttribute("rate"); historicData[time][code] = rate; } } Object.keys(historicData).map(function (key, index) { var currencies = me.convertLocalCurrency(historicData[key]); sol.common_fx.ix.FxUtils.recordExchangeRateHistory(currencies, key, false); }); }, /** * Converts the currencies to another local currency * @param {Object} currencies Currencies * @return {Object} Converted currencies */ convertLocalCurrency: function (currencies) { var me = this, fxConfig, currency, localCurrencyRate, localCurrency, localCurrencyDecimalRate, rate, rateDecimal, newRateDecimal, newRate, precision; fxConfig = me.fxConfig || sol.create("sol.common.Config", { compose: "/common_fx/Configuration/fx.config" }).config; localCurrency = fxConfig.localCurrency || "EUR"; precision = fxConfig.precision || 6; if (localCurrency == "EUR") { return currencies; } localCurrencyRate = currencies[localCurrency]; localCurrencyDecimalRate = new Decimal(localCurrencyRate); for (currency in currencies) { rate = currencies[currency]; rateDecimal = new Decimal(rate); newRateDecimal = rateDecimal.dividedBy(localCurrencyDecimalRate); newRate = newRateDecimal.toPrecision(precision).toString(); currencies[currency] = newRate; } return currencies; }, /** * Converts an amount from one currency to another. * * Because all exchange rates refer to EUR the conversion has up to two steps: FROM_CURRENCY -> EUR ( -> TO_CURRENCY) * * Result object: * * { * amount: "47.11", * from: "USD", * to: "EUR", * result: "42.06" * } * * @param {String} amount * @param {String} from The currency of the amount * @param {String} to The currency in which the amount should be converted. If undfined 'baseCurrency' will be used (see {@link #configuration baseCurrency}) * @return {Object} */ convertAmount: function (amount, from, to) { var me = this, result; amount = amount || 0; if (!from) { throw "invalid arguments: from can not be undefined"; } result = { amount: amount, from: from }; result.to = to || me.configuration.baseCurrency; me.logger.info(["convert amount : {0} from '{1}' to '{2}'", result.amount, result.from, result.to]); result.result = parseFloat(result.amount) / me.getExchangeRateForCurrency(result.from); if (result.to !== "EUR") { result.result = parseFloat(result.result) * me.getExchangeRateForCurrency(result.to); } me.logger.info(["conversion result : {0} {1}", result.result, result.to]); return result; }, /** * @private * Retrieves the exchange rates from an external service. * @param {String} url The service URL * @return {String} The service response */ getExchangeRates: function (url) { var me = this, responseObj, fxConfig, proxyHost, proxyPort; fxConfig = me.fxConfig || sol.create("sol.common.Config", { compose: "/common_fx/Configuration/fx.config" }).config; proxyHost = fxConfig.proxyHost; proxyPort = fxConfig.proxyPort || "8080"; responseObj = sol.common.HttpUtils.sendRequest({ url: url, proxyHost: proxyHost, proxyPort: proxyPort }); return responseObj.content; }, /** * @private * Parses the request date from a text using a regular expression (see {@link #configuration timeRegex}). * * @param {String} text * @return {String} ISO date */ parseDate: function (text) { var me = this, sdf = new Packages.java.text.SimpleDateFormat(me.configuration.timeFormat), date = new Date(), timeMatch; timeMatch = me.configuration.timeRegex.exec(text); if (timeMatch && timeMatch.length == 2) { date = sdf.parse(timeMatch[1]); } return ixConnect.dateToIso(date) + ""; }, /** * @private * Writes updated exchange rates to the database. * @param {Object} currencies Currencies * @param {String} time Time */ updateExchangeRates: function (currencies, time) { var me = this, rows = [], currency, dbConnection, rowCount; for (currency in currencies) { rows.push([currencies[currency], time, currency]); } dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); rowCount = dbConnection.update(me.configuration.updateStatement, rows); me.logger.info(["updated exchange rates; updated rows: {0}", rowCount]); }, /** * @private * Retrives the exchange rate for one currency from the database. * @param {String} currencyCode * @return {Number} The exchange rate */ getExchangeRateForCurrency: function (currencyCode) { var me = this, dbConnection, ret; if (!currencyCode) { throw "no source currency defined"; } dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); ret = dbConnection.query(me.configuration.selectStatement, [currencyCode]); if (!ret || ret.length < 1) { throw "no exchange rate found for '" + currencyCode + "'"; } return parseFloat(ret[0][1]); }, /** * @private * Records exchange rates to the history table in the database. * @param {Object} currencies Currencies * @param {String} time Time * @param {Boolean} checkExisting checkExisting */ recordExchangeRateHistory: function (currencies, time, checkExisting) { var me = this, sortResults; sortResults = me.sortExchangeRateEntries(currencies, time, checkExisting); me.addExchangeRateHistory(sortResults.newEntries); me.updateExchangeRateHistory(sortResults.existingEntries); }, /** * @private * Sorts exchange rates into new entries and * existing entries with rates that need updating. * @param {Object} currencies Currencies * @param {String} time Time * @param {Boolean} checkExisting checkExisting * @returns {Object} result */ sortExchangeRateEntries: function (currencies, time, checkExisting) { var me = this, rows = [], result, currency, dbConnection, args, ret, startOfDay, fxConfig, localCurrency, currencyCodes; fxConfig = me.fxConfig || sol.create("sol.common.Config", { compose: "/common_fx/Configuration/fx.config" }).config; localCurrency = fxConfig.localCurrency || "EUR"; startOfDay = sol.common.DateUtils.isoToMoment(time, { startOfDay: true }); time = sol.common.DateUtils.dateToIso(startOfDay); currencyCodes = me.collectCurrentCurrencyCodes(); for (currency in currencies) { if (currencyCodes.indexOf(currency) !== -1) { rows.push([currency, currencies[currency], time, localCurrency]); } } if ((typeof checkExisting !== "undefined") && (checkExisting === false)) { result = {}; result.newEntries = rows; result.existingEntries = []; } else { dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); result = rows.reduce(function (collector, entry) { args = [entry[0], time]; ret = dbConnection.query(me.configuration.selectHistoryStatement, args); if (!ret || ret.length < 1) { collector.newEntries.push(entry); } else { if (ret[0][1] !== entry[1]) { args.unshift(entry[1]); collector.existingEntries.push(args); } } return collector; }, { newEntries: [], existingEntries: [] }); } return result; }, /** * gets currency codes form sol_common_fx table * @returns {Array} result */ collectCurrentCurrencyCodes: function () { var me = this, dbConnection, ret, result = [], i; dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); ret = dbConnection.query(me.configuration.selectCurrencyCodesStatement); if (!ret || ret.length < 1) { throw "No currency codes could be read from the database"; } for (i = 0; i < ret.length; i++) { result.push(String(ret[i][0])); } return result; }, /** * Inserts new entries into the history table * @param {Array} results results */ addExchangeRateHistory: function (results) { var me = this, dbConnection, rowCount; dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); if (!results || !results.length) { return; } rowCount = dbConnection.update(me.configuration.insertHistoryStatement, results); me.logger.debug(["inserted rows in history table: {0}", rowCount]); }, /** * Updates existing entries into the history table * @param {Array} results results */ updateExchangeRateHistory: function (results) { var me = this, dbConnection, rowCount; dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); if (!results || !results.length) { return; } rowCount = dbConnection.update(me.configuration.updateHistoryStatement, results); me.logger.debug(["updated rows in history table: {0}", rowCount]); }, /** * @private * Reads exchange rate by date and code from history table * @param {String} currencyCode currencyCode * @param {String} isoDate isoDate, ELO iso date string * @returns {Object} object with or without exchange rate */ getHistoricalExchangeRate: function (currencyCode, isoDate) { var me = this, result = {}, dates, firstEntryForCurrency, isoDateIsPriorToFirstEntry, isoDateIsInFuture; if (!currencyCode) { throw "no currency code defined"; } dates = me.calcIsoDates(isoDate); if (!dates.isoDate) { throw "Error processing iso date"; } isoDateIsInFuture = (dates.isoNow && (dates.isoDate > dates.isoNow)); if (isoDateIsInFuture) { return result; } firstEntryForCurrency = me.getOldestEntryForCurrency(currencyCode); isoDateIsPriorToFirstEntry = (firstEntryForCurrency && firstEntryForCurrency.isoDate && dates.isoDate <= firstEntryForCurrency.isoDate); if (isoDateIsPriorToFirstEntry && firstEntryForCurrency.exchangeRate) { result.exchangeRate = firstEntryForCurrency.exchangeRate; return result; } result = me.queryHistoricalExchangeRateByPeriod(currencyCode, dates); return result; }, /** * @private * Calculates ISO Date Strings * @param {String} isoDate isoDate, ELO iso date string * @returns {Object} dates */ calcIsoDates: function (isoDate) { if (!isoDate) { throw "isoDate is missing"; } var me = this, dates = {}, isoDateTimeFormat = me.configuration.isoDateTimeFormat || "YYYYMMDDHHmmss", fxConfig = sol.create("sol.common.Config", { compose: "/common_fx/Configuration/fx.config" }).config, daysInPast = fxConfig.daysInPast || 7, daysInFuture = fxConfig.daysInFuture || 7, lowerTimeLimit, upperTimeLimit, startOfDay; if (!sol.common.DateUtils.parse(isoDate, isoDateTimeFormat)) { throw "isoDate is not correctly formatted"; } startOfDay = sol.common.DateUtils.isoToMoment(isoDate, { startOfDay: true }); dates.isoDate = sol.common.DateUtils.dateToIso(startOfDay); lowerTimeLimit = startOfDay.clone().subtract(daysInPast, "days"); dates.isoLowerTimeLimit = sol.common.DateUtils.dateToIso(lowerTimeLimit); upperTimeLimit = startOfDay.clone().add(daysInFuture, "days"); dates.isoUpperTimeLimit = sol.common.DateUtils.dateToIso(upperTimeLimit); dates.isoNow = sol.common.DateUtils.nowIso({ startOfDay: true }); return dates; }, /** * selects row in historical database by currency code * and minimum isodate * @param {String} currencyCode currencyCode * @returns {Object} result */ getOldestEntryForCurrency: function (currencyCode) { var me = this, dbConnection, ret, result = {}; if (!currencyCode) { throw "no currency code defined"; } dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); ret = dbConnection.query(me.configuration.selectOldestEntryByCodeStatement, [currencyCode, currencyCode]); if (!ret || ret.length < 1) { return result; } result.exchangeRate = parseFloat(ret[0][0]); result.isoDate = String(ret[0][1]); return result; }, /** * Checks history table for currency by code and date * within a configurable time frame * @param {String} currencyCode * @param {Object} dates * @params {String} dates.isoDate, ELO iso date string * @params {String} dates.isoLowerTimeLimit, ELO iso date string * @params {String} dates.isoUpperTimeLimit, ELO iso date string * * @returns {Object} result */ queryHistoricalExchangeRateByPeriod: function (currencyCode, dates) { var me = this, dbConnection, ret, result = {}; dates = dates || {}; if (!currencyCode) { throw "No currency code defined"; } if (!dates.isoDate || !dates.isoLowerTimeLimit || !dates.isoUpperTimeLimit) { throw "Missing time configuration"; } dbConnection = new Packages.de.elo.ix.jscript.DBConnection(); ret = dbConnection.query(me.configuration.selectByDateAndCodeStatement + " DESC", [currencyCode, dates.isoLowerTimeLimit, dates.isoDate]); if (!ret || ret.length < 1) { ret = dbConnection.query(me.configuration.selectByDateAndCodeStatement, [currencyCode, dates.isoDate, dates.isoUpperTimeLimit]); if (!ret || ret.length < 1) { return result; } } result.exchangeRate = parseFloat(ret[0][0]); return result; }, /** * gets exchange rate from history table if available * Otherwise the current rate is returned * @param {String} currencyCode currency code * @param {String} isoDate ELO iso date string * @returns {Object} result object with or without exchange rate */ getExchangeRateByCodeAndDate: function (currencyCode, isoDate) { var me = this, result, errorMsg; try { result = me.getHistoricalExchangeRate(currencyCode, isoDate); } catch (e) { errorMsg = e; me.logger.warn("Error reading historical exchange rate", errorMsg); } result = result || {}; if (!result.hasOwnProperty("exchangeRate")) { try { result.exchangeRate = me.getExchangeRateForCurrency(currencyCode); } catch (e) { errorMsg = e; me.logger.warn("Error reading daily exchange rate", errorMsg); } } return result; } });