public List <CurrencyBE> ListaTodosActivo(int IdCompany)
{
Database db = DatabaseFactory.CreateDatabase("cnERPBD");
DbCommand dbCommand = db.GetStoredProcCommand("usp_Currency_ListaTodosActivo");
db.AddInParameter(dbCommand, "pIdCompany", DbType.Int32, IdCompany);
IDataReader reader = db.ExecuteReader(dbCommand);
List <CurrencyBE> Currencylist = new List <CurrencyBE>();
CurrencyBE Currency;
while (reader.Read())
{
Currency = new CurrencyBE();
Currency.IdCurrency = Int32.Parse(reader["idCurrency"].ToString());
Currency.IdCompany = Int32.Parse(reader["IdCompany"].ToString());
Currency.Abbreviate = reader["Abbreviate"].ToString();
Currency.NameCurrency = reader["NameCurrency"].ToString();
Currency.FlagState = Boolean.Parse(reader["FlagState"].ToString());
Currencylist.Add(Currency);
}
reader.Close();
reader.Dispose();
return(Currencylist);
}
private void tlbMenu_DeleteClick()
{
try
{
Cursor = Cursors.WaitCursor;
if (XtraMessageBox.Show("Be sure to delete the record?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (!ValidarIngreso())
{
CurrencyBE objE_Currency = new CurrencyBE();
objE_Currency.IdCurrency = int.Parse(gvCurrency.GetFocusedRowCellValue("IdCurrency").ToString());
objE_Currency.Login = Parametros.strUsuarioLogin;
objE_Currency.Machine = WindowsIdentity.GetCurrent().Name.ToString();
objE_Currency.IdCompany = Parametros.intEmpresaId;
CurrencyBL objBL_Currency = new CurrencyBL();
objBL_Currency.Elimina(objE_Currency);
XtraMessageBox.Show("The record was successfully deleted.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
Cargar();
}
}
Cursor = Cursors.Default;
}
catch (Exception ex)
{
Cursor = Cursors.Default;
XtraMessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <CurrencyBE> GetAllCurrency()
{
List <CurrencyBE> currencyList = new List <CurrencyBE>();
using (SqlConnection conn = new SqlConnection(ConnectionData.dbCn))
{
conn.Open();
SqlCommand cmd = new SqlCommand("usp_currencyList", conn);
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader dr = cmd.ExecuteReader())
{
CurrencyBE c;
while (dr.Read())
{
c = new CurrencyBE();
c.Id = Convert.ToInt32(dr["id"].ToString());
c.Name = dr["name"].ToString();
c.Symbol = dr["symbol"].ToString();
c.Iso = dr["iso"].ToString();
currencyList.Add(c);
}
}
}
return(currencyList);
}
public void Elimina(CurrencyBE pItem)
{
try
{
CurrencyDL Currency = new CurrencyDL();
Currency.Elimina(pItem);
}
catch (Exception ex)
{ throw ex; }
}
public void Actualiza(CurrencyBE pItem)
{
try
{
CurrencyDL Currency = new CurrencyDL();
Currency.Actualiza(pItem);
}
catch (Exception ex)
{ throw ex; }
}
public void Inserta(CurrencyBE pItem)
{
try
{
CurrencyDL Currency = new CurrencyDL();
Currency.Inserta(pItem);
}
catch (Exception ex)
{ throw ex; }
}
public CurrencyBE SeleccionaDescripcion(int IdCompany, string NameCurrency)
{
try
{
CurrencyDL Currency = new CurrencyDL();
CurrencyBE objEmp = Currency.SeleccionaDescripcion(IdCompany, NameCurrency);
return(objEmp);
}
catch (Exception ex)
{ throw ex; }
}
public CurrencyBE Selecciona(int IdCurrency)
{
try
{
CurrencyDL Currency = new CurrencyDL();
CurrencyBE objEmp = Currency.Selecciona(IdCurrency);
return(objEmp);
}
catch (Exception ex)
{ throw ex; }
}
public void Elimina(CurrencyBE pItem)
{
Database db = DatabaseFactory.CreateDatabase("cnERPBD");
DbCommand dbCommand = db.GetStoredProcCommand("usp_Currency_Elimina");
db.AddInParameter(dbCommand, "pIdCurrency", DbType.Int32, pItem.IdCurrency);
db.AddInParameter(dbCommand, "pIdCompany", DbType.Int32, pItem.IdCompany);
db.AddInParameter(dbCommand, "pLogin", DbType.String, pItem.Login);
db.AddInParameter(dbCommand, "pMachine", DbType.String, pItem.Machine);
db.ExecuteNonQuery(dbCommand);
}
public void Actualiza(CurrencyBE pItem)
{
Database db = DatabaseFactory.CreateDatabase("cnERPBD");
DbCommand dbCommand = db.GetStoredProcCommand("usp_Currency_Actualiza");
db.AddInParameter(dbCommand, "pIdCurrency", DbType.Int32, pItem.IdCurrency);
db.AddInParameter(dbCommand, "pIdCompany", DbType.Int32, pItem.IdCompany);
db.AddInParameter(dbCommand, "pAbbreviate", DbType.String, pItem.Abbreviate);
db.AddInParameter(dbCommand, "pNameCurrency", DbType.String, pItem.NameCurrency);
db.AddInParameter(dbCommand, "pFlagState", DbType.Boolean, pItem.FlagState);
db.AddInParameter(dbCommand, "pLogin", DbType.String, pItem.Login);
db.AddInParameter(dbCommand, "pMachine", DbType.String, pItem.Machine);
db.ExecuteNonQuery(dbCommand);
}
private void frmManCurrencyEdit_Load(object sender, EventArgs e)
{
if (pOperacion == Operacion.Nuevo)
{
this.Text = "Currency - New";
}
else if (pOperacion == Operacion.Modificar)
{
this.Text = "Currency - Update";
CurrencyBE objE_Currency = null;
objE_Currency = new CurrencyBL().Selecciona(IdCurrency);
if (objE_Currency != null)
{
txtAbbreviate.Text = objE_Currency.Abbreviate;
txtDescripcion.Text = objE_Currency.NameCurrency.Trim();
}
}
txtAbbreviate.Select();
}
public void InicializarModificar()
{
if (gvCurrency.RowCount > 0)
{
CurrencyBE objCurrency = new CurrencyBE();
objCurrency.IdCurrency = int.Parse(gvCurrency.GetFocusedRowCellValue("IdCurrency").ToString());
frmManCurrencyEdit objManCurrencyEdit = new frmManCurrencyEdit();
objManCurrencyEdit.pOperacion = frmManCurrencyEdit.Operacion.Modificar;
objManCurrencyEdit.IdCurrency = objCurrency.IdCurrency;
objManCurrencyEdit.pCurrencyBE = objCurrency;
objManCurrencyEdit.StartPosition = FormStartPosition.CenterParent;
objManCurrencyEdit.ShowDialog();
Cargar();
}
else
{
MessageBox.Show("No se pudo editar");
}
}
public CurrencyBE Selecciona(int IdCurrency)
{
Database db = DatabaseFactory.CreateDatabase("cnERPBD");
DbCommand dbCommand = db.GetStoredProcCommand("usp_Currency_Selecciona");
db.AddInParameter(dbCommand, "pidCurrency", DbType.Int32, IdCurrency);
IDataReader reader = db.ExecuteReader(dbCommand);
CurrencyBE Currency = null;
while (reader.Read())
{
Currency = new CurrencyBE();
Currency.IdCurrency = Int32.Parse(reader["idCurrency"].ToString());
Currency.IdCompany = Int32.Parse(reader["IdCompany"].ToString());
Currency.Abbreviate = reader["Abbreviate"].ToString();
Currency.NameCurrency = reader["NameCurrency"].ToString();
Currency.FlagState = Boolean.Parse(reader["FlagState"].ToString());
}
reader.Close();
reader.Dispose();
return(Currency);
}
private void btnGrabar_Click(object sender, EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor;
if (!ValidarIngreso())
{
CurrencyBL objBL_Currency = new CurrencyBL();
CurrencyBE objCurrency = new CurrencyBE();
objCurrency.IdCurrency = IdCurrency;
objCurrency.Abbreviate = txtAbbreviate.Text;
objCurrency.NameCurrency = txtDescripcion.Text;
objCurrency.FlagState = true;
objCurrency.Login = Parametros.strUsuarioLogin;
objCurrency.Machine = WindowsIdentity.GetCurrent().Name.ToString();
objCurrency.IdCompany = Parametros.intEmpresaId;
if (pOperacion == Operacion.Nuevo)
{
objBL_Currency.Inserta(objCurrency);
}
else
{
objBL_Currency.Actualiza(objCurrency);
}
this.Close();
}
}
catch (Exception ex)
{
Cursor = Cursors.Default;
XtraMessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void Execute()
{
using (var sqlConnection = new SqlConnection(Properties.Settings.Default.CurrencyApp_DB))
{
sqlConnection.Open();
IEnumerable <CurrencyBE> currencies = sqlConnection.Query <CurrencyBE>("SELECT * FROM Currency");
foreach (CurrencyBE currency in currencies)
{
// 2. Retrieve currency rate one by one (https://api.fixer.io/latest?base={currencyRate})
HttpClient httpClient = new HttpClient();
httpClient.BaseAddress = new Uri(Properties.Settings.Default.CurrencyRate_BaseURL);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/x-www-form-urlencoded"));
//DateTime dateTime = DateTime.Now.Date.AddDays(-1);
DateTime dateTime = new DateTime(2017, 5, 4);
while (dateTime.Date < DateTime.Now.Date)
{
try
{
dateTime = dateTime.AddDays(1);
string dateTimeString = dateTime.ToString("yyyy-MM-dd");
var response = httpClient.GetAsync($"latest/{dateTimeString}?base={currency.Code}").Result;
//var response = httpClient.GetAsync($"latest?base={currency.Code}").Result;
string responseText = response.Content.ReadAsStringAsync().Result;
RateResultBE rateResult = JsonConvert.DeserializeObject <RateResultBE>(responseText);
CurrencyBE fromCurrency = currencies.FirstOrDefault(x => x.Code == rateResult.Base);
if (fromCurrency == null)
{
var fromCodeParam = new DynamicParameters();
fromCodeParam.Add("@code", rateResult.Base);
fromCurrency = sqlConnection.Query <CurrencyBE>("InsertCurrency", fromCodeParam, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
foreach (var rate in rateResult.Rates)
{
CurrencyBE toCurrency = currencies.FirstOrDefault(x => x.Code == rate.Key);
if (toCurrency == null)
{
var toCodeParam = new DynamicParameters();
toCodeParam.Add("@code", rate.Key);
toCurrency = sqlConnection.Query <CurrencyBE>("InsertCurrency", toCodeParam, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
DateTime rateDate = DateTime.ParseExact(rateResult.Date, "yyyy-MM-dd", null);
decimal actualRate = 0;
if (!Decimal.TryParse(rate.Value, NumberStyles.Any, CultureInfo.InvariantCulture, out actualRate))
{
log.Error($"Unable to convert value {rate.Value} to decimal.");
#if DEBUG
Console.WriteLine($"Unable to convert value {rate.Value} to decimal.");
#endif
}
var rateParam = new DynamicParameters();
rateParam.Add("@fromCurrency", fromCurrency.Id);
rateParam.Add("@toCurrency", toCurrency.Id);
rateParam.Add("@rate", actualRate);
rateParam.Add("@year", rateDate.Year);
rateParam.Add("@month", rateDate.Month);
rateParam.Add("@day", rateDate.Day);
sqlConnection.Execute("UpsertCurrencyRateHistory", rateParam, commandType: CommandType.StoredProcedure);
}
#if DEBUG
Console.WriteLine(responseText);
#endif
}
catch
{
dateTime = dateTime.AddDays(-1);
continue;
}
}
}
}
}