protected override string DoIt() { string status = "OK"; string baseCurrency = DB.ExecuteScalar("Select ISO_Code from C_Currency Where C_Currency_ID=" + baseCurrencyID).ToString(); string currencySourceName = DB.ExecuteScalar("Select url from C_CurrencySource Where C_CurrencySource_ID=" + C_CurrencySource_ID).ToString(); string myCurrency = ""; int myCurrencyID = 0; string sql = @"SELECT ISO_Code,C_Currency_ID FROM C_Currency WHERE IsActive='Y' AND ISMYCURRENCY='Y'"; DataSet ds = DB.ExecuteDataset(sql); Trx trx = Trx.Get("CreateConVersionEnties"); try { if (ds != null) { String URL = "http://localhost/CloudService55/AccountService.asmx"; //String CloudURL = "http://cloudservice.viennaadvantage.com/AccountService.asmx"; BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.None) { CloseTimeout = new TimeSpan(00, 20, 00), SendTimeout = new TimeSpan(00, 20, 00), OpenTimeout = new TimeSpan(00, 20, 00), ReceiveTimeout = new TimeSpan(00, 20, 00), MaxReceivedMessageSize = int.MaxValue, MaxBufferSize = int.MaxValue }; int defaultconversionType = 0; try { defaultconversionType = Convert.ToInt32(DB.ExecuteScalar("select c_conversiontype_id from c_conversiontype where isdefault='Y' and isactive='Y'")); } catch { } MConversionRate conversion = null; Decimal rate1 = 0; Decimal rate2 = 0; Decimal one = new Decimal(1.0); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { myCurrency = ds.Tables[0].Rows[i]["ISO_Code"].ToString(); myCurrencyID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_Currency_ID"]); var client = new ModelLibrary.AcctService.AccountServiceSoapClient(binding, new EndpointAddress(URL)); if (!String.IsNullOrEmpty(myCurrency) && !String.IsNullOrEmpty(baseCurrency) && !String.IsNullOrEmpty(currencySourceName)) { string result = client.GetConvertedCurrencyValue(baseCurrency, myCurrency, currencySourceName, KEY); if (!String.IsNullOrEmpty(result)) { conversion = new MConversionRate(GetCtx(), 0, trx); conversion.SetAD_Org_ID(0); conversion.SetAD_Client_ID(GetCtx().GetAD_Client_ID()); conversion.SetValidFrom(DateTime.Now); conversion.SetValidTo(DateTime.Now); conversion.SetC_ConversionType_ID(defaultconversionType); conversion.SetC_Currency_ID(myCurrencyID); conversion.SetC_Currency_To_ID(baseCurrencyID); //conversion.SetC_Currency_To_ID(); rate1 = Convert.ToDecimal(result); rate2 = Utility.Env.ZERO; one = new Decimal(1.0); if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero { rate2 = Decimal.Round(Decimal.Divide(one, rate1), 12); // MidpointRounding.AwayFromZero); } conversion.SetMultiplyRate(rate1); conversion.SetDivideRate(rate2); if (!conversion.Save(trx)) { status = "ConversionRateNotsaved"; } } result = client.GetConvertedCurrencyValue(myCurrency, baseCurrency, currencySourceName, KEY); if (!String.IsNullOrEmpty(result)) { conversion = new MConversionRate(GetCtx(), 0, trx); conversion.SetAD_Org_ID(0); conversion.SetAD_Client_ID(GetCtx().GetAD_Client_ID()); conversion.SetValidFrom(DateTime.Now); conversion.SetValidTo(DateTime.Now); conversion.SetC_ConversionType_ID(defaultconversionType); conversion.SetC_Currency_ID(baseCurrencyID); conversion.SetC_Currency_To_ID(myCurrencyID); //conversion.SetC_Currency_To_ID(); rate1 = Convert.ToDecimal(result); rate2 = Utility.Env.ZERO; one = new Decimal(1.0); if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero { rate2 = Decimal.Round(Decimal.Divide(one, rate1), 12); // MidpointRounding.AwayFromZero); } conversion.SetMultiplyRate(rate1); conversion.SetDivideRate(rate2); if (!conversion.Save(trx)) { status = "ConversionRateNotsaved"; } } } } } } catch (Exception ex) { status = ex.Message; } if (status.Equals("OK")) { trx.Commit(); } else { trx.Rollback(); } trx.Close(); return(status); }
protected override string DoIt() { string status = "OK"; // Trx trx = Get_Trx(); ds.Clear(); try { ds = DB.ExecuteDataset(@"SELECT distinct cl.AD_Client_ID, cl.AD_Org_ID, cl.CurrencyRateUpdateFrequency, acct.C_Currency_ID ,cr.ISO_Code,cl.C_CurrencySource_ID FROM ad_client cl INNER JOIN AD_CLientinfo ci ON ci.ad_client_ID=cl.ad_client_ID INNER JOIN C_AcctSchema acct ON acct.C_AcctSchema_ID =ci.C_AcctSchema1_ID Left Join C_Currency cr on cr.C_Currency_ID=acct.C_Currency_ID WHERE cl.ad_client_Id! =0 AND cl.UpdateCurrencyRate='A' AND cl.IsMultiCurrency='Y' AND cl.currencyrateupdatefrequency IS NOT NULL"); // Where ci.AD_CLient_ID= " + GetAD_Client_ID()); //in this DataSet we'll get CLient's Base Currency & the Currency ID // int clientCount = ds.Tables[0].Rows.Count; if (ds.Tables[0].Rows.Count > 0) { _lstCurr = new List <CurrencyProp>(); } else { return(VAdvantage.Utility.Msg.GetMsg(GetCtx(), "NoMultiCurrencySettingsFound")); } for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { CurrencyProp _curr = new CurrencyProp(); _curr.baseCurrency = Convert.ToString(ds.Tables[0].Rows[j]["ISO_Code"]); _curr.baseCurrencyID = Convert.ToInt32(ds.Tables[0].Rows[j]["C_Currency_ID"]); _curr.frequency = Convert.ToString(ds.Tables[0].Rows[j]["CurrencyRateUpdateFrequency"]); _curr.AD_Client_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Client_ID"]); _curr.AD_Org_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Org_ID"]); _curr.CurrencySource = Convert.ToInt32(ds.Tables[0].Rows[j]["C_CurrencySource_ID"]); _lstCurr.Add(_curr); } ds.Clear(); sql = @"Select Cur.ISO_Code, Cur.C_Currency_ID From C_Currency Cur Where Cur.IsMyCurrency='Y' And Cur.IsActive='Y' "; ds = DB.ExecuteDataset(sql); // Here we get all currencies in which our Client is in dealing with //String frequency;// = DB.ExecuteScalar("Select currencyrateupdatefrequency from AD_Client where IsActive='Y' AND currencyrateupdatefrequency is not null").ToString(); // getting the frequency(TimePeriod) for the converted rate if (ds != null) { for (Int32 k = 0; k < _lstCurr.Count; k++) { string currencySourceName = DB.ExecuteScalar("Select url from C_CurrencySource Where C_CurrencySource_ID=" + _lstCurr[k].CurrencySource).ToString(); //int defaultconversionType = 0; //try //{ // defaultconversionType = Convert.ToInt32(DB.ExecuteScalar(@"SELECT C_ConversionType_id, Surchargepercentage,Surchargevalue FROM c_conversiontype WHERE autocalculate='Y' AND isactive ='Y'")); //} //catch //{ //} DataSet dsConversion = DB.ExecuteDataset(@"SELECT C_ConversionType_id, Surchargepercentage,Surchargevalue,CurrencyRateUpdateFrequency FROM c_conversiontype WHERE isautocalculate='Y' AND isactive ='Y'"); if (dsConversion != null && dsConversion.Tables[0].Rows.Count > 0) { for (int x = 0; x < dsConversion.Tables[0].Rows.Count; x++) { int defaultconversionType = 0; defaultconversionType = Convert.ToInt32(dsConversion.Tables[0].Rows[x]["C_ConversionType_id"]); MConversionRate conversion = null; Decimal rate1 = 0; Decimal rate2 = 0; Decimal one = new Decimal(1.0); string updateFrequency = _lstCurr[k].frequency; if (dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != null && dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != DBNull.Value && Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]) != "") { updateFrequency = Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]); } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { myCurrency = ds.Tables[0].Rows[i]["ISO_Code"].ToString(); myCurrencyID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Currency_ID"]); sql = String.Empty; sql = @" Select ValidTo from C_Conversion_Rate where IsActive='Y' AND C_ConversionType_id=" + defaultconversionType + " AND C_Currency_ID=" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID + " AND Created=(SELECT Max(Created) FROM C_Conversion_Rate WHERE isactive ='Y' AND C_ConversionType_id=" + defaultconversionType + " AND " + " C_Currency_ID =" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID + ") AND AD_Client_ID = " + _lstCurr[k].AD_Client_ID + "AND AD_Org_ID= " + _lstCurr[k].AD_Org_ID; //the Maximum date from Converted rate of every currency if (DateTime.Now.Date > Convert.ToDateTime(DB.ExecuteScalar(sql.Trim(), null, null)).Date) { if (!String.IsNullOrEmpty(myCurrency) && !String.IsNullOrEmpty(_lstCurr[k].baseCurrency) && !String.IsNullOrEmpty(currencySourceName) && (myCurrencyID != _lstCurr[k].baseCurrencyID)) { String result = GetConvertedCurrencyValue(_lstCurr[k].baseCurrency, myCurrency, currencySourceName); if (!String.IsNullOrEmpty(result)) { conversion = new MConversionRate(GetCtx(), 0, null); conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID)); conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID); //conversion.SetValidFrom(DateTime.Now.AddDays(-1)); conversion.SetValidFrom(DateTime.Now); if (updateFrequency.Equals("D")) { conversion.SetValidTo(DateTime.Now); } else if (updateFrequency.Equals("W")) { conversion.SetValidTo(DateTime.Now.AddDays(7)); } else if (updateFrequency.Equals("M")) { conversion.SetValidTo(DateTime.Now.AddMonths(1)); } conversion.SetC_ConversionType_ID(defaultconversionType); conversion.SetC_Currency_ID(_lstCurr[k].baseCurrencyID); conversion.SetC_Currency_To_ID(myCurrencyID); rate2 = VAdvantage.Utility.Env.ZERO; one = new Decimal(1.0); //if (dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != null && dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != DBNull.Value // && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) != 0) //{ // rate1 = (Convert.ToDecimal(result) + (Convert.ToDecimal(result) * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100))); // if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero // { // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); // } // rate2 = (rate2 + rate2 * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100)); //} //else if (dsConversion.Tables[0].Rows[x]["Surchargevalue"] != null && dsConversion.Tables[0].Rows[x]["Surchargevalue"] != DBNull.Value // && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]) != 0) //{ // rate1 = (Convert.ToDecimal(result) + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"])); // if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero // { // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); // } // rate2 = (rate2 + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"])); //} //else //{ // rate1 = Convert.ToDecimal(result); // if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero // { // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); // } //} rate1 = Convert.ToDecimal(result); //if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero //{ // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); //} conversion.SetMultiplyRate(rate1); //conversion.SetDivideRate(rate2); if (!conversion.Save()) { //status = "ConversionRateNotsaved"; } } } } } } } } } } catch (Exception ex) { //trx.Rollback(); //status = ex.Message; //trx.Close(); return(status); } //trx.Commit(); //trx.Close(); ds.Dispose(); _lstCurr = null; return(status); }
protected override string DoIt() { string status = "OK"; // Trx trx = Get_Trx(); XDocument doc = null; decimal exchangeRate = 0; string currencyQty = ""; string content = ""; ds.Clear(); try { // Get Currency from all Accounting Schemas available in the Tenant. ds = DB.ExecuteDataset(@"SELECT DISTINCT cl.AD_Client_ID, cl.AD_Org_ID, cl.CurrencyRateUpdateFrequency, acct.C_Currency_ID ,cr.ISO_Code,cl.C_CurrencySource_ID FROM AD_Client cl INNER JOIN C_AcctSchema acct ON acct.AD_Client_ID =cl.AD_Client_ID INNER JOIN C_Currency cr on cr.C_Currency_ID=acct.C_Currency_ID WHERE cl.IsActive='Y' AND cl.AD_Client_ID!=0 AND cl.UpdateCurrencyRate='A' AND cl.IsMultiCurrency='Y' AND acct.IsActive='Y' AND cl.currencyrateupdatefrequency IS NOT NULL", null, Get_Trx()); // Where ci.AD_CLient_ID= " + GetAD_Client_ID()); //in this DataSet we'll get CLient's Base Currency & the Currency ID // int clientCount = ds.Tables[0].Rows.Count; if (ds.Tables[0].Rows.Count > 0) { _lstCurr = new List <CurrencyProp>(); } else { return(VAdvantage.Utility.Msg.GetMsg(GetCtx(), "NoMultiCurrencySettingsFound")); } for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { CurrencyProp _curr = new CurrencyProp(); _curr.baseCurrency = Convert.ToString(ds.Tables[0].Rows[j]["ISO_Code"]); _curr.baseCurrencyID = Convert.ToInt32(ds.Tables[0].Rows[j]["C_Currency_ID"]); _curr.frequency = Convert.ToString(ds.Tables[0].Rows[j]["CurrencyRateUpdateFrequency"]); _curr.AD_Client_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Client_ID"]); _curr.AD_Org_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Org_ID"]); _curr.CurrencySource = Convert.ToInt32(ds.Tables[0].Rows[j]["C_CurrencySource_ID"]); _lstCurr.Add(_curr); } ds.Clear(); sql.Append(@"Select Cur.ISO_Code, Cur.C_Currency_ID From C_Currency Cur Where Cur.IsMyCurrency='Y' And Cur.IsActive='Y' "); ds = DB.ExecuteDataset(sql.ToString(), null, Get_Trx()); // Here we get all currencies in which our Client is in dealing with if (ds != null) { for (Int32 k = 0; k < _lstCurr.Count; k++) { sql.Clear(); // Get UTRL and Api Key From Currency Source sql.Append("SELECT URL, ApiKey FROM C_CurrencySource WHERE C_CurrencySource_ID=" + _lstCurr[k].CurrencySource); DataSet dsSource = DB.ExecuteDataset(sql.ToString(), null, Get_Trx()); if (dsSource != null && dsSource.Tables.Count > 0 && dsSource.Tables[0].Rows.Count > 0) { string currencySourceName = Util.GetValueOfString(dsSource.Tables[0].Rows[0]["URL"]); string apiKey = Util.GetValueOfString(dsSource.Tables[0].Rows[0]["ApiKey"]); if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("pwebapps.ezv.admin.ch")) { doc = XDocument.Load("http://www.pwebapps.ezv.admin.ch/apps/rates/rate/getxml?activeSearchType=userDefinedDay"); } else if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("api.bnm.gov.my")) { content = GetConvertedCurrencyValue("", "", currencySourceName, apiKey); } DataSet dsConversion = DB.ExecuteDataset(@"SELECT C_ConversionType_id, Surchargepercentage,Surchargevalue,CurrencyRateUpdateFrequency FROM c_conversiontype WHERE isautocalculate='Y' AND isactive ='Y'", null, Get_Trx()); if (dsConversion != null && dsConversion.Tables[0].Rows.Count > 0) { for (int x = 0; x < dsConversion.Tables[0].Rows.Count; x++) { int defaultconversionType = 0; defaultconversionType = Convert.ToInt32(dsConversion.Tables[0].Rows[x]["C_ConversionType_id"]); MConversionRate conversion = null; Decimal rate1 = 0; Decimal rate2 = 0; Decimal one = new Decimal(1.0); string updateFrequency = _lstCurr[k].frequency; if (dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != null && dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != DBNull.Value && Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]) != "") { updateFrequency = Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]); } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { myCurrency = ds.Tables[0].Rows[i]["ISO_Code"].ToString(); myCurrencyID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Currency_ID"]); if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("pwebapps.ezv.admin.ch")) { currencyQty = ""; exchangeRate = 0; if (doc != null) { var devise = from p in doc.Descendants() where p.Name.LocalName == "devise" select p; if (devise != null) { foreach (XElement d in devise.Where(d => d.Attributes("code").FirstOrDefault().Value == myCurrency.ToLower())) { var data = from p in d.Descendants() where p.Name.LocalName == "waehrung" || p.Name.LocalName == "kurs" select p; if (data != null) { currencyQty = data.Where(n => n.Name.LocalName == "waehrung").FirstOrDefault().Value.ToString(); exchangeRate = Util.GetValueOfDecimal(data.Where(n => n.Name.LocalName == "kurs").FirstOrDefault().Value); } } } if (!String.IsNullOrEmpty(currencyQty) && exchangeRate != 0) { decimal qty = Util.GetValueOfDecimal(currencyQty.Substring(0, currencyQty.Length - 4)); exchangeRate /= qty; } else { continue; } conversion = new MConversionRate(GetCtx(), 0, Get_Trx()); conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID)); conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID); //conversion.SetValidFrom(DateTime.Now.AddDays(-1)); conversion.SetValidFrom(DateTime.Now); if (updateFrequency.Equals("D")) { conversion.SetValidTo(DateTime.Now); } else if (updateFrequency.Equals("W")) { conversion.SetValidTo(DateTime.Now.AddDays(7)); } else if (updateFrequency.Equals("M")) { conversion.SetValidTo(DateTime.Now.AddMonths(1)); } conversion.SetC_ConversionType_ID(defaultconversionType); conversion.SetC_Currency_ID(myCurrencyID); conversion.SetC_Currency_To_ID(318); rate1 = Convert.ToDecimal(exchangeRate); conversion.SetMultiplyRate(rate1); if (!conversion.Save()) { } } } else if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("api.bnm.gov.my")) { exchangeRate = 0; if (!String.IsNullOrEmpty(content)) { dynamic rates = JsonConvert.DeserializeObject <dynamic>(content); if (rates != null && rates.data != null) { for (int j = 0; j < rates.data.Count; j++) { if (rates.data[j].currency_code == myCurrency.ToUpper()) { int unit = Util.GetValueOfInt(rates.data[j].unit); exchangeRate = Util.GetValueOfDecimal(rates.data[j].rate["middle_rate"]); if (exchangeRate > 0 && unit > 1) { exchangeRate /= unit; break; } } } } if (exchangeRate != 0) { conversion = new MConversionRate(GetCtx(), 0, Get_Trx()); conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID)); conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID); //conversion.SetValidFrom(DateTime.Now.AddDays(-1)); conversion.SetValidFrom(DateTime.Now); if (updateFrequency.Equals("D")) { conversion.SetValidTo(DateTime.Now); } else if (updateFrequency.Equals("W")) { conversion.SetValidTo(DateTime.Now.AddDays(7)); } else if (updateFrequency.Equals("M")) { conversion.SetValidTo(DateTime.Now.AddMonths(1)); } conversion.SetC_ConversionType_ID(defaultconversionType); conversion.SetC_Currency_ID(myCurrencyID); conversion.SetC_Currency_To_ID(301); rate1 = Convert.ToDecimal(exchangeRate); conversion.SetMultiplyRate(rate1); if (!conversion.Save()) { } } } } else { sql.Clear(); sql.Append(@"Select ValidTo from C_Conversion_Rate where IsActive='Y' AND C_ConversionType_id=" + defaultconversionType + " AND C_Currency_ID=" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID + " AND Created=(SELECT Max(Created) FROM C_Conversion_Rate WHERE isactive ='Y' AND C_ConversionType_id=" + defaultconversionType + " AND " + " C_Currency_ID =" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID + ") AND AD_Client_ID = " + _lstCurr[k].AD_Client_ID + "AND AD_Org_ID= " + _lstCurr[k].AD_Org_ID); //the Maximum date from Converted rate of every currency object validDate = DB.ExecuteScalar(sql.ToString(), null, Get_Trx()); //Check if valid date available.. and less than current date.. //By Karan 22 June if (validDate != null && validDate != DBNull.Value && DateTime.Now.Date > Convert.ToDateTime(validDate).Date) { if (!String.IsNullOrEmpty(myCurrency) && !String.IsNullOrEmpty(_lstCurr[k].baseCurrency) && !String.IsNullOrEmpty(currencySourceName) && (myCurrencyID != _lstCurr[k].baseCurrencyID)) { String result = GetConvertedCurrencyValue(_lstCurr[k].baseCurrency, myCurrency, currencySourceName, apiKey); if (!String.IsNullOrEmpty(result)) { conversion = new MConversionRate(GetCtx(), 0, Get_Trx()); conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID)); conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID); //conversion.SetValidFrom(DateTime.Now.AddDays(-1)); conversion.SetValidFrom(DateTime.Now); if (updateFrequency.Equals("D")) { conversion.SetValidTo(DateTime.Now); } else if (updateFrequency.Equals("W")) { conversion.SetValidTo(DateTime.Now.AddDays(7)); } else if (updateFrequency.Equals("M")) { conversion.SetValidTo(DateTime.Now.AddMonths(1)); } conversion.SetC_ConversionType_ID(defaultconversionType); conversion.SetC_Currency_ID(_lstCurr[k].baseCurrencyID); conversion.SetC_Currency_To_ID(myCurrencyID); rate2 = VAdvantage.Utility.Env.ZERO; one = new Decimal(1.0); //if (dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != null && dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != DBNull.Value // && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) != 0) //{ // rate1 = (Convert.ToDecimal(result) + (Convert.ToDecimal(result) * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100))); // if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero // { // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); // } // rate2 = (rate2 + rate2 * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100)); //} //else if (dsConversion.Tables[0].Rows[x]["Surchargevalue"] != null && dsConversion.Tables[0].Rows[x]["Surchargevalue"] != DBNull.Value // && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]) != 0) //{ // rate1 = (Convert.ToDecimal(result) + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"])); // if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero // { // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); // } // rate2 = (rate2 + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"])); //} //else //{ // rate1 = Convert.ToDecimal(result); // if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero // { // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); // } //} rate1 = Convert.ToDecimal(result); //if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero //{ // rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero); //} conversion.SetMultiplyRate(rate1); //conversion.SetDivideRate(rate2); if (!conversion.Save()) { //status = "ConversionRateNotsaved"; } } } } } } } } } } } } catch (Exception ex) { //trx.Rollback(); //status = ex.Message; //trx.Close(); return(status); } //trx.Commit(); //trx.Close(); ds.Dispose(); _lstCurr = null; return(status); }
/// <summary> /// Process to get Cross Currency Rate /// </summary> /// <returns>message</returns> protected override string DoIt() { // Getting records from cross rate setting query.Append("SELECT * FROM C_CurrCrossRate WHERE IsActive='Y'"); dsobj = DB.ExecuteDataset(query.ToString()); query.Clear(); if (dsobj != null && dsobj.Tables.Count > 0 && dsobj.Tables[0].Rows.Count > 0) { //for (int i = 0; i < dsobj.Tables[0].Rows.Count; i++) foreach (DataRow dr in dsobj.Tables[0].Rows) { Currobj = new MCurrCrossRate(GetCtx(), dr, Get_Trx()); // Getting records from currency rate based on conditions query.Append("SELECT AD_Org_ID,C_Currency_ID,ValidFrom,ValidTo,MultiplyRate FROM C_Conversion_Rate WHERE " + GlobalVariable.TO_DATE(DateTime.Now.Date, true) + " BETWEEN ValidFrom AND ValidTo AND IsActive='Y' AND AD_Org_ID=" + Currobj.GetAD_Org_ID() + " AND C_ConversionType_ID=" + Currobj.GetC_ConversionType_ID() + " AND C_Currency_To_ID=" + Currobj.GetC_Currency_ID() + " AND C_Currency_ID IN ('" + Currobj.GetC_Currency_From_ID() + "','" + Currobj.GetC_Currency_To_ID() + "')"); dsobj = DB.ExecuteDataset(query.ToString()); query.Clear(); if (dsobj != null && dsobj.Tables.Count > 0 && dsobj.Tables[0].Rows.Count == 2) { for (int j = 0; j < dsobj.Tables[0].Rows.Count; j++) { //Org_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j]["AD_Org_ID"]); FromCurr1 = Util.GetValueOfInt(dsobj.Tables[0].Rows[j]["C_Currency_ID"]); // Getting multiply rate from both records if (Currobj.GetC_Currency_From_ID() == FromCurr1) { ValidFrom1 = Convert.ToDateTime(dsobj.Tables[0].Rows[j]["ValidFrom"]); ValidTo1 = Convert.ToDateTime(dsobj.Tables[0].Rows[j]["ValidTo"]); MulRate1 = Util.GetValueOfDecimal(dsobj.Tables[0].Rows[j]["MultiplyRate"]); } else if (Currobj.GetC_Currency_To_ID() == FromCurr1) { ValidFrom2 = Convert.ToDateTime(dsobj.Tables[0].Rows[j]["ValidFrom"]); ValidTo2 = Convert.ToDateTime(dsobj.Tables[0].Rows[j]["ValidTo"]); MulRate2 = Util.GetValueOfDecimal(dsobj.Tables[0].Rows[j]["MultiplyRate"]); } else { retmsg.Append("Error: " + Msg.GetMsg(GetCtx(), "VIS_CurrNotMatch")); } } // Calculating new multiply rate & divide rate if (MulRate2 > 0) { newMulRate = MulRate1 / MulRate2; newDivideRate = 1 / newMulRate; } else { retmsg.Append("Error: " + Msg.GetMsg(GetCtx(), "ProductUOMConversionRateError")); } // Geting Valid from and valid to dates if (ValidFrom1 <= ValidFrom2 && ValidTo1 <= ValidTo2) { newValidFrom = ValidFrom2; newValidTo = ValidTo1; } else if (ValidFrom1 >= ValidFrom2 && ValidTo1 >= ValidTo2) { newValidFrom = ValidFrom1; newValidTo = ValidTo2; } else if (ValidFrom1 <= ValidFrom2 && ValidTo1 >= ValidTo2) { newValidFrom = ValidFrom2; newValidTo = ValidTo2; } else if (ValidFrom1 >= ValidFrom2 && ValidTo1 <= ValidTo2) { newValidFrom = ValidFrom1; newValidTo = ValidTo1; } else { retmsg.Append(Msg.GetMsg(GetCtx(), "VIS_InvalidDate")); } query.Clear(); //int ID = DB.GetNextID(0, "C_Conversion_Rate", null); //query.Append("INSERT INTO C_Conversion_Rate (AD_Client_ID,AD_Org_ID,C_Conversion_Rate_ID,C_Currency_ID,C_Currency_To_ID,C_ConversionType_ID,ValidFrom,ValidTo,MultiplyRate,DivideRate,CreatedBy,UpdatedBy) " // + "Values (" + Client_ID + ", " + Org_ID + ","+ ID +", " + FromCurr + ", " + ToCurr + ", " + CurrType + ", TO_DATE('" + newValidFrom.ToShortDateString() + "','MM-DD-YYYY'), TO_DATE('" + newValidTo.ToShortDateString() + "','MM-DD-YYYY') , " + newMulRate + ", " + newDivideRate + ", " + Client_ID + ", "+ Client_ID + ") "); //int _updated = DB.ExecuteQuery(query.ToString(), null); //if (_updated > 0) //{ // Msg.GetMsg("Record Inserted",""); //} // Inserting new conversion in the currency rate conobj = new MConversionRate(GetCtx(), 0, Get_Trx()); conobj.SetAD_Client_ID(Currobj.GetAD_Client_ID()); conobj.SetAD_Org_ID(Currobj.GetAD_Org_ID()); conobj.SetC_Currency_ID(Currobj.GetC_Currency_From_ID()); conobj.SetC_Currency_To_ID(Currobj.GetC_Currency_To_ID()); conobj.SetC_ConversionType_ID(Currobj.GetC_ConversionType_ID()); conobj.SetValidFrom(newValidFrom); conobj.SetValidTo(newValidTo); conobj.SetMultiplyRate(newMulRate); conobj.SetDivideRate(newDivideRate); if (!conobj.Save()) { ValueNamePair pp = VLogger.RetrieveError(); if (pp != null) { retmsg.Append(Msg.GetMsg(GetCtx(), "VIS_CrossRateNotCal") + ":- " + pp.GetName() + Currobj.GetName() + ", "); } else { retmsg.Append(Msg.GetMsg(GetCtx(), "VIS_CrossRateNotCal")); } } else { retmsg.Append(Msg.GetMsg(GetCtx(), "VIS_CrossRateCal") + ":- " + Currobj.GetName() + ", "); } } else { retmsg.Append(Msg.GetMsg(GetCtx(), "VIS_CrossRateNotCal") + ":- " + Currobj.GetName() + ", "); } } } else { retmsg.Append(Msg.GetMsg(GetCtx(), "NoRecords")); } return(retmsg.ToString()); }