public static void loadSettings() { settings.Clear(); string strSetting = "Select * from \"@B1_SETTING\" "; System.Data.DataTable dtSettings = ds.getDataTable(strSetting); foreach (System.Data.DataRow dr in dtSettings.Rows) { try { settings.Add(dr["Name"].ToString(), dr["U_Value"].ToString()); } catch { } } }
public static void UpdateForDate(DateTime pDATE) { SAPbobsCOM.SBObob bobs = sboDI.oDiCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge); string _mainCurrency = ((string)bobs.GetLocalCurrency().Fields.Item(0).Value).ToUpperInvariant(); SAPbobsCOM.Recordset recordset = sboDI.oDiCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset); System.Data.DataTable currencies = ds.getDataTable( sboDI.oDiCompany.DbServerType.Equals(SAPbobsCOM.BoDataServerTypes.dst_HANADB) ? @"SELECT OCRN.* FROM OCRN LEFT JOIN ORTT ON OCRN.""CurrCode"" = ORTT.""Currency"" AND ""RateDate"" = '" + pDATE.ToString("yyyyMMdd") + @"' WHERE COALESCE(""Rate"", 0) = 0" : @"SELECT OCRN.* FROM OCRN LEFT JOIN ORTT ON OCRN.""CurrCode"" = ORTT.""Currency"" AND ""RateDate"" = '" + pDATE.ToString("yyyyMMdd") + @"' WHERE COALESCE(""Rate"", 0) = 0"); var currencyCodes = new Dictionary <string, string>(); foreach (System.Data.DataRow currency in currencies.Rows) { string code = currency["CurrCode"] as string; string intcode = currency["DocCurrCod"] as string; string isocode = currency["ISOCurrCod"] as string; string currencyCode = string.IsNullOrWhiteSpace(isocode) ? intcode?.ToUpperInvariant() : isocode.ToUpperInvariant(); if (code != null && !string.IsNullOrWhiteSpace(currencyCode) && currencyCode.Length == 3) { currencyCodes.Add(code, currencyCode); } } string mainCurrency = currencyCodes[_mainCurrency]; string strDate = pDATE.ToString("yyyy-MM-dd"); currencyCodes.Remove(_mainCurrency); if (!currencyCodes.Any()) { return; } JObject document; CURRENCIES result; using (WebClient wc = new WebClient()) { //string callString = "https://openexchangerates.org/api/historical/" + strDate + ".json?app_id=64f0b566047049ec93b765fce90530e4&base=" + mainCurrency; //// MessageBox.Show(_mainCurrency + strDate + callString); //document = // JObject.Parse( // wc.DownloadString( // $"https://openexchangerates.org/api/historical/" + strDate + ".json?app_id=64f0b566047049ec93b765fce90530e4&base=" + mainCurrency + "")); string strXml = wc.DownloadString($"http://www.boi.org.il/currency.xml"); XmlDocument doc = new XmlDocument(); doc.LoadXml(strXml); XmlSerializer serializer = new XmlSerializer(typeof(CURRENCIES)); using (TextReader reader = new StringReader(doc.InnerXml)) { result = (CURRENCIES)serializer.Deserialize(reader); } } //var test = from rate in document.SelectToken("rates").Children() // where rate is JProperty && currencyCodes.ContainsValue(((JProperty)rate).Name) // select // new // { // CurrencyCode = ((JProperty)rate).Name, // Date = pDATE.Date, // Rate = Convert.ToDouble(1M / ((JProperty)rate).Value.Value<decimal>()) // }; var test = from rate in result.CURRENCY where currencyCodes.ContainsValue(rate.CURRENCYCODE) select new { CurrencyCode = rate.CURRENCYCODE, Date = pDATE.Date, Rate = Convert.ToDouble(rate.RATE) }; int codesUpdated = 0; foreach (var rate in test) { // MessageBox.Show("Updating " + rate.CurrencyCode); var code = currencyCodes.FirstOrDefault(kp => kp.Value.Equals(rate.CurrencyCode)).Key; var rateExists = false; try { if (bobs.GetCurrencyRate(code, rate.Date).RecordCount > 0) { rateExists = true; } } catch (Exception ex) { string err = ex.Message; } finally { if (!rateExists) { try { bobs.SetCurrencyRate(code, rate.Date, rate.Rate, false); } catch (Exception ex) { // MessageBox.Show("Updating " + code); } codesUpdated++; } } } }