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);
}
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);
}
