/// <summary>
/// Evento click del boton aceptar
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnAceptar_Click(object sender, EventArgs e)
{
usu = new Usuario();
//pr = new Principal(usu);
pr = (Principal) this.Owner;
if (txtPassword.Text.Trim() == "" || txtUser.Text.Trim() == "")
{
MessageBox.Show(this, "Campos en blanco detectados, verifique!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
usu.Name = txtUser.Text.Trim();
usu.Password = txtPassword.Text.Trim();
con = new Connection(usu);
if (con.Conexion())
{
this.Hide();
pr.Pri_Load(usu);
pr.Show();
}
else
{
MessageBox.Show(this, "Usuario o contraseña incorrectos, Verifique!", "Advertencia!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
public List<Sector> LoadSector()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
listSec = new List<Sector>();
cn.Open();
sql = "select * from asada.view_sectores";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
sec = new Sector();
sec.Code = reader.GetString(0);
sec.Consecutive = reader.GetInt32(1);
sec.Description = reader.GetString(2);
listSec.Add(sec);
}
reader.Close();
return listSec;
}
}
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 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)
{
throw;
}
}
public List<Sector> LoadSector()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon=new Connection(this.user)).Parameters()))
{
listSec = new List<Sector>();
cn.Open();
sql = "select * from asada.view_sectores";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
sec = new Sector();
sec.Code = reader.GetString(0);
sec.Consecutive = reader.GetInt32(1);
sec.Description = reader.GetString(2);
listSec.Add(sec);
}
reader.Close();
return listSec;
}
}
catch (Exception)
{
throw;
}
}
public MySqlDataAdapter LoadAbonado()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
abo = new Abonado();
cn.Open();
sql = "select * from asada.view_abonados";
cmd = new MySqlCommand(sql, cn);
MySqlDataAdapter dt = new MySqlDataAdapter(sql, cn);
return dt;
}
}
catch (Exception)
{
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 MySqlDataAdapter LoadPrevista()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
pre = new Prevista();
cn.Open();
// guardar esta vista en la BD
sql = "select pr.ID_PREVISTA as 'Código de Prevista', pr.ID_ABONADO as 'Cédula de Abonado', concat(ab.APELLIDO1,' ', ab.APELLIDO2,' ',ab.NOMBRE) as 'Nombre', pr.ID_TARIFAS as 'Código de Tarifa', pr.UBICACION as 'Ubicación', pr.ESTADO as 'Estado'"+
"from asada.prevista as pr left join asada.abonados as ab on pr.ID_ABONADO = ab.ID_ABONADO ";
cmd = new MySqlCommand(sql, cn);
MySqlDataAdapter dt = new MySqlDataAdapter(sql, cn);
return dt;
}
}
catch (Exception)
{
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 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 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)
{
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;
}
}
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 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;
}
}
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 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 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();
return result > 0;
}
}
catch (Exception)
{
return false;
throw;
}
}
public bool Delete(Abonado abo)
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
cn.Open();
sql = "delete from asada.abonados where ID_ABONADO=@Identification";
cmd = new MySqlCommand(sql, cn);
cmd.Parameters.Add(new MySqlParameter("@Identifcation", abo.Identification));
result = cmd.ExecuteNonQuery();
return result > 0;
}
}
catch (Exception)
{
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 * from asada.consecutivos";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
n = reader.GetInt32(1)+1;
}
reader.Close();
}
return n;
}
catch (Exception)
{
return 0;
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;
}
}