public List<Tarifa> LoadTarifa()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
lisTrf = new List<Tarifa>();
cn.Open();
sql = "select * from asada.view_tarifas";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
trf = new Tarifa();
trf.Code = reader.GetString(0);
trf.FixAmount = float.Parse(reader.GetString(1));
trf.MetAmount = float.Parse(reader.GetString(2));
trf.RateHidrant = float.Parse(reader.GetString(3));
trf.TypeAppString = reader.GetString(4);
lisTrf.Add(trf);
}
reader.Close();
return lisTrf;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
throw;
}
}
public bool InsertNew(Prevista prv)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "insert into asada.prevista (ID_PREVISTA, ID_TARIFAS, ID_ABONADO, ID_SECTOR, UBICACION, SALDO, ESTADO, TIPO_CEDULA, UBICACION)" +
"values(@Code, @CodeRate, @Identification, @ID_Sector, @Saldo, @IsActive, @Nacionality, @Ubication)";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.Add(new MySqlParameter("@Code", prv.Code));
cmd.Parameters.Add(new MySqlParameter("@CodeRate", prv.CodeRate));
cmd.Parameters.Add(new MySqlParameter("@Identification", prv.Identification));
cmd.Parameters.Add(new MySqlParameter("@ID_Sector", prv.Sector));
cmd.Parameters.Add(new MySqlParameter("@Saldo", prv.Balance));
cmd.Parameters.Add(new MySqlParameter("IsActive", prv.IsActive));
cmd.Parameters.Add(new MySqlParameter("@Nacionality", prv.Nacionality));
cmd.Parameters.Add(new MySqlParameter("Ubication", prv.Ubication));
result = cmd.ExecuteNonQuery();
return result > 0;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return false;
throw;
}
}
public Abonado SerchOneAbonado(string id)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "select * from asada.abonados where ID_ABONADO=@Identification";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.Add(new MySqlParameter("@Identification", id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
abo = new Abonado();
abo.Identification = reader.GetString(0);
abo.Name = reader.GetString(1);
abo.Firstname = reader.GetString(2);
abo.Lastname = reader.GetString(3);
abo.Movil = reader.GetString(4);
abo.Phonenum = reader.GetString(5);
abo.Email = reader.GetString(6);
abo.Dateregister = reader.GetString(7);
abo.Address = reader.GetString(8);
}
reader.Close();
return(abo);
}
}
catch (Exception e)
{
Asada.DAO.Logs lg = new Asada.DAO.Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
throw;
}
}
public bool Conexion()
{
try
{
using (MySqlConnection cn = new MySqlConnection(this.Parameters()))
{
cn.Open();
return true;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return false;
throw;
}
}
public MySqlDataAdapter LoadPrevista()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
pre = new Prevista();
cn.Open();
sql = "select * from asada.view_previstas";
cmd = new MySqlCommand(sql, cn);
MySqlDataAdapter dt = new MySqlDataAdapter(sql, cn);
return dt;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
throw;
}
}
public bool InsertNew(Abonado abo)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
if (!Exist(abo))
{
sql = "insert into asada.abonados (ID_ABONADO, NOMBRE, APELLIDO1, APELLIDO2, MOVIL, TELEFONO, EMAIL, FECHA_REGISTRO, DIRECCION)" +
"values (@Identification, @Name, @Firstname, @Lastname, @Movil, @Phonenum, @Email, @Dateregister, @Address)";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.Add(new MySqlParameter("@Identification", abo.Identification));
cmd.Parameters.Add(new MySqlParameter("@Name", abo.Name));
cmd.Parameters.Add(new MySqlParameter("@Firstname", abo.Firstname));
cmd.Parameters.Add(new MySqlParameter("@Lastname", abo.Lastname));
cmd.Parameters.Add(new MySqlParameter("@Movil", abo.Movil));
cmd.Parameters.Add(new MySqlParameter("@Phonenum", abo.Phonenum));
cmd.Parameters.Add(new MySqlParameter("@Email", abo.Email));
cmd.Parameters.Add(new MySqlParameter("@Dateregister", abo.Dateregister));
cmd.Parameters.Add(new MySqlParameter("@Addres", abo.Address));
result = cmd.ExecuteNonQuery();
return result > 0;
}
else
{
return false;
}
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return false;
throw;
}
}
public Abonado SerchOneAbonado(string id)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "select * from asada.abonados where ID_ABONADO=@Identification";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.Add(new MySqlParameter("@Identification", id));
reader = cmd.ExecuteReader();
while (reader.Read())
{
abo = new Abonado();
abo.Identification = reader.GetString(0);
abo.Name = reader.GetString(1);
abo.Firstname = reader.GetString(2);
abo.Lastname = reader.GetString(3);
abo.Movil = reader.GetString(4);
abo.Phonenum = reader.GetString(5);
abo.Email = reader.GetString(6);
abo.Dateregister = reader.GetString(7);
abo.Address = reader.GetString(8);
}
reader.Close();
return abo;
}
}
catch (Exception e)
{
Asada.DAO.Logs lg = new Asada.DAO.Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
throw;
}
}
public List<Abonado> LoadAbonado1()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
listAbo = new List<Abonado>();
cn.Open();
sql = "select * from asada.vw_abonados";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
abo = new Abonado();
abo.Identification = reader.GetString(0);
abo.Name = reader.GetString(1);
listAbo.Add(abo);
}
reader.Close();
return listAbo;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
throw;
}
}
private bool Exist(Abonado abo)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "select count(ID_ABONADO) from asada.abonados where ID_ABONADO=@Identification";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.Add(new MySqlParameter("@Identification", abo.Identification));
result = Int32.Parse(cmd.ExecuteScalar().ToString());
return result > 0;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return false;
throw;
}
}
public bool Update(Abonado abo)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "update asada.abonados set NOMBRE=@Name, APELLIDO1=@Firstname, APELLIDO2=@Lastname, MOVIL=@Movil, TELEFONO=@Phonenum, EMAIL=@Email, FECHA_REGISTRO=@Dateregister, DIRECCION=@Address where ID_ABONADO=@Identification";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.Add(new MySqlParameter("@Name", abo.Name));
cmd.Parameters.Add(new MySqlParameter("@Firstname", abo.Firstname));
cmd.Parameters.Add(new MySqlParameter("@Lastname", abo.Lastname));
cmd.Parameters.Add(new MySqlParameter("@Movil", abo.Movil));
cmd.Parameters.Add(new MySqlParameter("@Phonenum", abo.Phonenum));
cmd.Parameters.Add(new MySqlParameter("@Email", abo.Email));
cmd.Parameters.Add(new MySqlParameter("@Dateregister", abo.Dateregister));
cmd.Parameters.Add(new MySqlParameter("@Address", abo.Address));
cmd.Parameters.Add(new MySqlParameter("@Identification", abo.Identification));
result = cmd.ExecuteNonQuery();
if (result > 0) // ¿ verificar estas líneas?
{
return true;
}
else
{
return false;
}
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return false;
throw;
}
}
public bool UpdatePrv(Prevista prv)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "update asada.prevista set ID_TARIFAS=@CodeRate, ID_ABONADO=@Identification, ID_SECTOR=@Sector, UBICACION=@Ubication, ESTADO=@IsActive where ID_PREVISTA=@Code";
cmd.Parameters.Add(new MySqlParameter("@CodeRate", prv.CodeRate));
cmd.Parameters.Add(new MySqlParameter("@Identification", prv.Identification));
cmd.Parameters.Add(new MySqlParameter("@Sector", prv.Sector));
cmd.Parameters.Add(new MySqlParameter("@Ubication", prv.Ubication));
cmd.Parameters.Add(new MySqlParameter("@IsActive", prv.IsActive));
cmd.Parameters.Add(new MySqlParameter("@Code", prv.Code));
result = cmd.ExecuteNonQuery();
return result > 0;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return false;
throw;
}
}
public Prevista SerchOnePrevista(string id)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "select * from asada.prevista where ID_PREVISTA=@id";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.AddWithValue("id", id);
reader = cmd.ExecuteReader();
while (reader.Read())
{
pre = new Prevista();
pre.Code = reader.GetString(0);
pre.CodeRate = reader.GetString(1);
pre.Identification = reader.GetString(2);
pre.Sector = reader.GetString(3);
pre.Balance = float.Parse(reader.GetString(4));
pre.IsActive = bool.Parse(reader.GetString(5));
pre.Nacionality = reader.GetString(6);
pre.Ubication = reader.GetString(7);
}
reader.Close();
return pre;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
throw;
}
}
public bool Delete(string id)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "delete prevista from asada.prevista where ID_PREVISTA=@id";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.AddWithValue("id", id);
result = cmd.ExecuteNonQuery();
return result > 0;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return false;
throw;
}
}
public int ConsecutivePrv()
{
try
{
int n = 0;
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "select PREVISTA from asada.consecutivos";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
n = reader.GetInt32(0) + 1;
}
reader.Close();
}
return n;
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
return 0;
throw;
}
}