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(); 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); }
} // prepare /// <summary> /// Perrform Process. /// </summary> /// <returns>message</returns> protected override String DoIt() { log.Info("doIt - AD_Client_ID=" + _AD_Client_ID + ",AD_Org_ID=" + _AD_Org_ID + ",C_ConversionType_ID=" + _C_ConversionType_ID + ",ValidFrom=" + _ValidFrom + ",CreateReciprocalRate=" + _CreateReciprocalRate); // StringBuilder sql = null; int no = 0; String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID; // **** Prepare **** // Delete Old Imported if (_DeleteOldImported) { sql = new StringBuilder("DELETE FROM I_Conversion_Rate " + "WHERE I_IsImported='Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Delete Old Impored =" + no); } // Set Client, Org, Location, IsActive, Created/Updated sql = new StringBuilder("UPDATE I_Conversion_Rate " + "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),"); if (_C_ConversionType_ID != 0) { sql.Append(" C_ConversionType_ID = COALESCE (C_ConversionType_ID,").Append(_C_ConversionType_ID).Append("),"); } if (_ValidFrom != null) { sql.Append(" ValidFrom = COALESCE (ValidFrom,").Append(DataBase.DB.TO_DATE(_ValidFrom)).Append("),"); } else { sql.Append(" ValidFrom = COALESCE (ValidFrom,SysDate),"); } sql.Append(" CreateReciprocalRate = COALESCE (CreateReciprocalRate,'").Append(_CreateReciprocalRate ? "Y" : "N").Append("')," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = ").Append(GetAD_User_ID()).Append("," + " I_ErrorMsg = NULL," + " Processed = 'N'," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL"); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Info("Reset =" + no); // Org String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly sql = new StringBuilder("UPDATE I_Conversion_Rate o " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '" + "WHERE (AD_Org_ID IS NULL" + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Org =" + no); } // Conversion Type sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET C_ConversionType_ID = (SELECT C_ConversionType_ID FROM C_ConversionType c" + " WHERE c.Value=i.ConversionTypeValue AND c.AD_Client_ID IN (0,i.AD_Client_ID) AND c.IsActive='Y') " + "WHERE C_ConversionType_ID IS NULL AND ConversionTypeValue IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no > 0) { log.Fine("Set ConversionType =" + no); } sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid ConversionType, ' " + "WHERE (C_ConversionType_ID IS NULL" + " OR NOT EXISTS (SELECT * FROM C_ConversionType c " + "WHERE i.C_ConversionType_ID=c.C_ConversionType_ID AND c.IsActive='Y'" + " AND c.AD_Client_ID IN (0,i.AD_Client_ID)))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid ConversionType =" + no); } // Currency sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET C_Currency_ID = (SELECT C_Currency_ID FROM C_Currency c" + " WHERE c.ISO_Code=i.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID) AND c.IsActive='Y') " + "WHERE C_Currency_ID IS NULL AND ISO_Code IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no > 0) { log.Fine("Set Currency =" + no); } sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Currency, ' " + "WHERE (C_Currency_ID IS NULL" + " OR NOT EXISTS (SELECT * FROM C_Currency c " + "WHERE i.C_Currency_ID=c.C_Currency_ID AND c.IsActive='Y'" + " AND c.AD_Client_ID IN (0,i.AD_Client_ID)))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Currency =" + no); } // Currency To sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET C_Currency_To_ID = (SELECT C_Currency_ID FROM C_Currency c" + " WHERE c.ISO_Code=i.ISO_Code_To AND c.AD_Client_ID IN (0,i.AD_Client_ID) AND c.IsActive='Y') " + "WHERE C_Currency_To_ID IS NULL AND ISO_Code_To IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no > 0) { log.Fine("Set Currency To =" + no); } sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Currency To, ' " + "WHERE (C_Currency_To_ID IS NULL" + " OR NOT EXISTS (SELECT * FROM C_Currency c " + "WHERE i.C_Currency_To_ID=c.C_Currency_ID AND c.IsActive='Y'" + " AND c.AD_Client_ID IN (0,i.AD_Client_ID)))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Currency To =" + no); } // Rates sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET MultiplyRate = 1 / DivideRate " + "WHERE (MultiplyRate IS NULL OR MultiplyRate = 0) AND DivideRate IS NOT NULL AND DivideRate<>0" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no > 0) { log.Fine("Set MultiplyRate =" + no); } sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET DivideRate = 1 / MultiplyRate " + "WHERE (DivideRate IS NULL OR DivideRate = 0) AND MultiplyRate IS NOT NULL AND MultiplyRate<>0" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no > 0) { log.Fine("Set DivideRate =" + no); } sql = new StringBuilder("UPDATE I_Conversion_Rate i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Rates, ' " + "WHERE (MultiplyRate IS NULL OR MultiplyRate = 0 OR DivideRate IS NULL OR DivideRate = 0)" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Rates =" + no); } // sql = new StringBuilder ("UPDATE I_Conversion_Rate i " // Rate diff > 10% // + "SET I_IsImported='E', I_ErrorMsg="+ts +"||'ERR=Inconsistent Rates='||(MultiplyRate - (1/DivideRate)) " // + "WHERE ((MultiplyRate - (1/DivideRate)) > (MultiplyRate * .1))" // + " AND I_IsImported<>'Y'").Append (clientCheck); // no = DataBase.DB.ExecuteQuery(sql.ToString(),null, Get_TrxName()); // if (no != 0) // log.warn ("Inconsistent Rates =" + no); Commit(); /*********************************************************************/ int noInsert = 0; sql = new StringBuilder("SELECT * FROM I_Conversion_Rate " + "WHERE I_IsImported='N'").Append(clientCheck) .Append(" ORDER BY C_Currency_ID, C_Currency_To_ID, ValidFrom"); //PreparedStatement pstmt = null; IDataReader idr = null; try { //pstmt = DataBase.prepareStatement(sql.ToString(), Get_TrxName()); //ResultSet rs = pstmt.executeQuery(); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); while (idr.Read()) { X_I_Conversion_Rate imp = new X_I_Conversion_Rate(GetCtx(), idr, Get_TrxName()); MConversionRate rate = new MConversionRate(imp, imp.GetC_ConversionType_ID(), imp.GetC_Currency_ID(), imp.GetC_Currency_To_ID(), imp.GetMultiplyRate(), imp.GetValidFrom()); if (imp.GetValidTo() != null) { rate.SetValidTo(imp.GetValidTo()); } if (rate.Save()) { imp.SetC_Conversion_Rate_ID(rate.GetC_Conversion_Rate_ID()); imp.SetI_IsImported(X_I_Conversion_Rate.I_ISIMPORTED_Yes); imp.SetProcessed(true); imp.Save(); noInsert++; // if (imp.IsCreateReciprocalRate()) { rate = new MConversionRate(imp, imp.GetC_ConversionType_ID(), imp.GetC_Currency_To_ID(), imp.GetC_Currency_ID(), imp.GetDivideRate(), imp.GetValidFrom()); if (imp.GetValidTo() != null) { rate.SetValidTo(imp.GetValidTo()); } if (rate.Save()) { noInsert++; } } } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql.ToString(), e); } // Set Error to indicator to not imported sql = new StringBuilder("UPDATE I_Conversion_Rate " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@"); // AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_Conversion_Rate_ID@: @Inserted@"); return(""); } // doIt
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); }
/// <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()); }