public string UpdateCurrencyExchangeRate(ACRF_CurrencyExchangeRateModel objModel)
{
string result = "Error on Updating Currency Exchange Rate!";
try
{
result = CheckIfCurrencyExchangeRateExists(objModel);
if (result == "")
{
var connection = gConnection.Connection();
connection.Open();
SqlCommand cmd = connection.CreateCommand();
SqlTransaction transaction;
transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
cmd.Connection = connection;
try
{
string sqlstr = "";
sqlstr = "update ACRF_CurrencyExchangeRate set FCountryId=@FCountryId,FCurrency=@FCurrency"
+ ", FCurrencyCode=@FCurrencyCode, Unit=@Unit, ImportRate=@ImportRate, ExportRate=@ExportRate,"
+ " UpdatedBy=@UpdatedBy,UpdatedOn=@UpdatedOn where Id=@Id";
cmd.CommandText = sqlstr;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@FCountryId", objModel.FCountryId);
cmd.Parameters.AddWithValue("@FCurrency", objModel.FCurrency);
cmd.Parameters.AddWithValue("@FCurrencyCode", objModel.FCurrencyCode);
cmd.Parameters.AddWithValue("@Unit", objModel.Unit);
cmd.Parameters.AddWithValue("@ImportRate", objModel.ImportRate);
cmd.Parameters.AddWithValue("@ExportRate", objModel.ExportRate);
cmd.Parameters.AddWithValue("@Id", objModel.Id);
cmd.Parameters.AddWithValue("@UpdatedBy", objModel.UpdatedBy);
cmd.Parameters.AddWithValue("@UpdatedOn", StandardDateTime.GetDateTime());
cmd.ExecuteNonQuery();
transaction.Commit();
connection.Close();
result = "Currency Exchange Rate Updated Successfully!";
}
catch (Exception ex)
{
transaction.Rollback();
connection.Close();
Global.ErrorHandlerClass.LogError(ex);
result = ex.Message;
}
}
else
{
return(result);
}
}
catch (Exception ex)
{
ErrorHandlerClass.LogError(ex);
}
return(result);
}
public string CheckIfCurrencyExchangeRateExists(ACRF_CurrencyExchangeRateModel objModel)
{
string result = "";
try
{
string sqlstr = "Select * from ACRF_CurrencyExchangeRate Where ISNULL(FCountryId,0)=@FCountryId and "
+ " ISNULL(FCurrency,'')=@FCurrency and Isnull(FCurrencyCode,'') as FCurrencyCode and Isnull(Id,0)!=@Id ";
var connection = gConnection.Connection();
connection.Open();
SqlCommand cmd = new SqlCommand(sqlstr, connection);
cmd.Parameters.AddWithValue("@FCountryId", objModel.FCountryId);
cmd.Parameters.AddWithValue("@FCurrency", objModel.FCurrency);
cmd.Parameters.AddWithValue("@FCurrencyCode", objModel.FCurrencyCode);
cmd.Parameters.AddWithValue("@Id", objModel.Id);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
result = "Currency Exchange Rate already exists!";
}
sdr.Close();
connection.Close();
}
catch (Exception ex)
{
ErrorHandlerClass.LogError(ex);
}
return(result);
}
public IHttpActionResult ViewOneCurrencyExchangeRate(int CurrId)
{
ACRF_CurrencyExchangeRateModel objList = new ACRF_CurrencyExchangeRateModel();
try
{
objList = objCERVM.OneCurrencyExchangeRate(CurrId);
}
catch (Exception ex)
{
ErrorHandlerClass.LogError(ex);
}
return(Ok(new { results = objList }));
}
public List <ACRF_CurrencyExchangeRateModel> ListCurrencyExchangeRate()
{
List <ACRF_CurrencyExchangeRateModel> objList = new List <ACRF_CurrencyExchangeRateModel>();
try
{
string sqlstr = "Select R.Id, R.FCountryId, R.FCurrency, R.FCurrencyCode, R.Unit, R.ImportRate, R.ExportRate, R.CreatedBy, R.CreatedOn, "
+ " C.Country From ACRF_CurrencyExchangeRate R, ACRF_Country C where R.FCountryId=C.Id";
var connection = gConnection.Connection();
connection.Open();
SqlCommand cmd = new SqlCommand(sqlstr, connection);
cmd.CommandType = System.Data.CommandType.Text;
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
ACRF_CurrencyExchangeRateModel tempobj = new ACRF_CurrencyExchangeRateModel();
tempobj.Id = Convert.ToInt32(sdr["Id"].ToString());
tempobj.FCountryId = Convert.ToInt32(sdr["FCountryId"].ToString());
tempobj.FCurrency = sdr["FCurrency"].ToString();
tempobj.FCurrencyCode = sdr["FCurrencyCode"].ToString();
tempobj.Unit = Convert.ToInt32(sdr["Unit"].ToString());
tempobj.ImportRate = Convert.ToDecimal(sdr["ImportRate"].ToString());
tempobj.ExportRate = Convert.ToDecimal(sdr["ExportRate"].ToString());
tempobj.Country = sdr["Country"].ToString();
tempobj.CreatedBy = sdr["CreatedBy"].ToString();
tempobj.CreatedOn = Convert.ToDateTime(sdr["CreatedOn"].ToString());
objList.Add(tempobj);
}
sdr.Close();
connection.Close();
}
catch (Exception ex)
{
ErrorHandlerClass.LogError(ex);
}
return(objList);
}
public IHttpActionResult UpdateCurrencyExchangeRate(ACRF_CurrencyExchangeRateModel objModel)
{
string result = "";
if (ModelState.IsValid)
{
try
{
objModel.UpdatedBy = GlobalFunction.getLoggedInUser(Request.Headers.GetValues("Token").First());
result = objCERVM.UpdateCurrencyExchangeRate(objModel);
}
catch (Exception ex)
{
ErrorHandlerClass.LogError(ex);
result = ex.Message;
}
}
else
{
result = "Enter Valid Mandatory Fields";
}
return(Ok(new { results = result }));
}
public Paged_ACRF_CurrencyExchangeRateModel ListCurrencyExchangeRateWithPagination(int max, int page, string search, string sort_col, string sort_dir)
{
Paged_ACRF_CurrencyExchangeRateModel objPaged = new Paged_ACRF_CurrencyExchangeRateModel();
List <ACRF_CurrencyExchangeRateModel> objList = new List <ACRF_CurrencyExchangeRateModel>();
try
{
if (search == null)
{
search = "";
}
int startIndex = max * (page - 1);
string sqlstr = "ACRF_GetCurrencyExchangeRateByPage";
var connection = gConnection.Connection();
connection.Open();
SqlCommand cmd = new SqlCommand(sqlstr, connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startRowIndex", startIndex);
cmd.Parameters.AddWithValue("@pageSize", max);
cmd.Parameters.AddWithValue("@search", search);
cmd.Parameters.AddWithValue("@sort_col", sort_col);
cmd.Parameters.AddWithValue("@sort_dir", sort_dir);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
ACRF_CurrencyExchangeRateModel tempobj = new ACRF_CurrencyExchangeRateModel();
tempobj.Id = Convert.ToInt32(sdr["Id"].ToString());
tempobj.FCountryId = Convert.ToInt32(sdr["FCountryId"].ToString());
tempobj.FCurrency = sdr["FCurrency"].ToString();
tempobj.FCurrencyCode = sdr["FCurrencyCode"].ToString();
tempobj.Unit = Convert.ToInt32(sdr["Unit"].ToString());
tempobj.ImportRate = Convert.ToDecimal(sdr["ImportRate"].ToString());
tempobj.ExportRate = Convert.ToDecimal(sdr["ExportRate"].ToString());
tempobj.CreatedBy = sdr["CreatedBy"].ToString();
tempobj.CreatedOn = Convert.ToDateTime(sdr["CreatedOn"].ToString());
objList.Add(tempobj);
}
sdr.Close();
objPaged.ACRF_CurrencyExchangeRateModelList = objList;
sqlstr = "select count(*) as cnt from ACRF_CurrencyExchangeRate where FCurrency like @search ";
cmd.Parameters.Clear();
cmd.CommandText = sqlstr;
cmd.Connection = connection;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@search", '%' + @search + '%');
sdr = cmd.ExecuteReader();
while (sdr.Read())
{
objPaged.PageCount = Convert.ToInt32(sdr["cnt"].ToString());
}
connection.Close();
}
catch (Exception ex)
{
ErrorHandlerClass.LogError(ex);
}
return(objPaged);
}