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