/// <summary>
///Get Currency Conversion Rate
/// </summary>
/// <param name="CurFrom_ID">The C_Currency_ID FROM</param>
/// <param name="CurTo_ID">The C_Currency_ID TO</param>
/// <param name="ConvDate">The Conversion date - if null - use current date</param>
/// <param name="ConversionType_ID">Conversion rate type - if 0 - use Default</param>
/// <param name="AD_Client_ID">client</param>
/// <param name="AD_Org_ID">organization</param>
/// <returns>currency Rate or null</returns>
public static Decimal GetRate(int CurFrom_ID, int CurTo_ID,
DateTime?convDate, int ConversionType_ID, int AD_Client_ID, int AD_Org_ID)
{
string isFetchAllDateOrNot = "N";
if (CurFrom_ID == CurTo_ID)
{
return(Env.ONE);
}
// Conversion Type
int C_ConversionType_ID = ConversionType_ID;
if (C_ConversionType_ID == 0)
{
C_ConversionType_ID = MConversionType.GetDefault(AD_Client_ID);
}
// Conversion Date
if (convDate == null)
{
// convDate = new DateTime(CommonFunctions.CurrentTimeMillis());
convDate = System.DateTime.Now.Date;
}
// Get Rate
String sql = "SELECT MultiplyRate "
+ "FROM C_Conversion_Rate "
+ "WHERE C_Currency_ID=" + CurFrom_ID // #1
+ " AND C_Currency_To_ID=" + CurTo_ID // #2
+ " AND C_ConversionType_ID="+ C_ConversionType_ID // #3
+ " AND " + DataBase.DB.TO_DATE(convDate, true) + " BETWEEN ValidFrom AND ValidTo" // #4 TRUNC (?) ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
+ " AND AD_Client_ID IN (0," + AD_Client_ID + ")" // #5
+ " AND AD_Org_ID IN (0," + AD_Org_ID + ") " // #6
+ " AND IsActive = 'Y' " // #7
+ "ORDER BY AD_Client_ID DESC, AD_Org_ID DESC, ValidFrom DESC";
//decimal retValue = null;
decimal?retValue = null;
DataSet ds = null;
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, null);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
retValue = Utility.Util.GetValueOfDecimal(dr[0].ToString());
//when record found then not to continue with another record
break;
}
ds = null;
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
// added By Amit
// if fetch all date or not is true on Conversion type then ppick conversion rate of max previous date
if (retValue == null)
{
try
{
isFetchAllDateOrNot = Util.GetValueOfString(DB.ExecuteScalar(@"SELECT IsFetchAllDateOrnot FROM C_ConversionType
WHERE C_ConversionType_ID = " + C_ConversionType_ID, null, null));
if (isFetchAllDateOrNot == "Y")
{
//System took currencies rate from last updated rate,
//when current date rate not defined and consider previous record is true on Conversion type
sql = @"SELECT MultiplyRate FROM C_Conversion_Rate WHERE C_Conversion_Rate_id = (
SELECT MAX(C_Conversion_Rate_id) keep (dense_rank last ORDER BY ValidFrom, C_Conversion_Rate_id)
FROM C_Conversion_Rate WHERE IsActive = 'Y' AND C_Currency_ID = " + CurFrom_ID + @"
AND C_Currency_To_ID = " + CurTo_ID + @"
AND C_ConversionType_ID= " + C_ConversionType_ID + @"
AND ValidFrom < " + DataBase.DB.TO_DATE(convDate, true) + @"
AND AD_Client_ID IN (0," + AD_Client_ID + ")" + @"
AND AD_Org_ID IN (0," + AD_Org_ID + "))";
retValue = null;
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, null);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
retValue = Utility.Util.GetValueOfDecimal(dr[0].ToString());
//when record found then not to continue with another record
break;
}
ds = null;
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
}
//end
if (retValue == null)
{
_log.Info("Not found - CurFrom=" + CurFrom_ID
+ ", CurTo=" + CurTo_ID
+ ", " + convDate
+ ", Type=" + ConversionType_ID + (ConversionType_ID == C_ConversionType_ID ? "" : "->" + C_ConversionType_ID)
+ ", Client=" + AD_Client_ID
+ ", Org=" + AD_Org_ID);
retValue = 0;
}
return(retValue.Value);
}
/// <summary>
///Get Currency Conversion Rate
/// </summary>
/// <param name="CurFrom_ID">The C_Currency_ID FROM</param>
/// <param name="CurTo_ID">The C_Currency_ID TO</param>
/// <param name="ConvDate">The Conversion date - if null - use current date</param>
/// <param name="ConversionType_ID">Conversion rate type - if 0 - use Default</param>
/// <param name="AD_Client_ID">client</param>
/// <param name="AD_Org_ID">organization</param>
/// <returns>currency Rate or null</returns>
public static Decimal GetRate(int CurFrom_ID, int CurTo_ID,
DateTime?convDate, int ConversionType_ID, int AD_Client_ID, int AD_Org_ID)
{
if (CurFrom_ID == CurTo_ID)
{
return(Env.ONE);
}
// Conversion Type
int C_ConversionType_ID = ConversionType_ID;
if (C_ConversionType_ID == 0)
{
C_ConversionType_ID = MConversionType.GetDefault(AD_Client_ID);
}
// Conversion Date
if (convDate == null)
{
// convDate = new DateTime(CommonFunctions.CurrentTimeMillis());
convDate = System.DateTime.Now.Date;
}
// Get Rate
String sql = "SELECT MultiplyRate "
+ "FROM C_Conversion_Rate "
+ "WHERE C_Currency_ID=" + CurFrom_ID // #1
+ " AND C_Currency_To_ID=" + CurTo_ID // #2
+ " AND C_ConversionType_ID="+ C_ConversionType_ID // #3
+ " AND " + DataBase.DB.TO_DATE(convDate, true) + " BETWEEN ValidFrom AND ValidTo" // #4 TRUNC (?) ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
+ " AND AD_Client_ID IN (0," + AD_Client_ID + ")" // #5
+ " AND AD_Org_ID IN (0," + AD_Org_ID + ") " // #6
+ "ORDER BY AD_Client_ID DESC, AD_Org_ID DESC, ValidFrom DESC";
//decimal retValue = null;
decimal?retValue = null;
DataSet ds = null;
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, null);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
retValue = Utility.Util.GetValueOfDecimal(dr[0].ToString());
}
ds = null;
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
if (retValue == null)
{
_log.Info("Not found - CurFrom=" + CurFrom_ID
+ ", CurTo=" + CurTo_ID
+ ", " + convDate
+ ", Type=" + ConversionType_ID + (ConversionType_ID == C_ConversionType_ID ? "" : "->" + C_ConversionType_ID)
+ ", Client=" + AD_Client_ID
+ ", Org=" + AD_Org_ID);
retValue = 0;
}
return(retValue.Value);
}
