private void SerchPrevista_FormClosed(object sender, FormClosedEventArgs e)
{
prv = new Prevista();
this.Hide();
fprv.Prev_Load(user, prv);
fprv.Show();
}
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;
}
}
private Prevista GetInfo()
{
prv = new Prevista();
trf = new Tarifa();
prv.Code = txtCode.Text.Trim();
prv.CodeRate = cbxRate.SelectedIndex.ToString();
prv.Sector = cbxSector.SelectedValue.ToString();
prv.Identification = cbxAbonado.SelectedValue.ToString();
int n = cbxRate.SelectedIndex;
if (n == 0)
{
trf.TypeApplication = true;
prv.IsActive = true;
}
else
{
trf.TypeApplication = false;
prv.IsActive = false;
}
prv.Ubication = txtAddress.Text;
return prv;
}
public frmPrevistas(Usuario usu, Prevista pv)
{
InitializeComponent();
this.user = usu;
this.prv = pv;
this.ValidUtils(prv);
this.Prev_Load(usu,prv);
}
private void SerchPrevista_Load(object sender, EventArgs e)
{
prv = new Prevista();
dt = new DataTable();
dts = new DataSet();
prvDao = new PrevistaDAO(this.user);
// Con la información del adaptador se rellena el DataTable
prvDao.LoadPrevista().Fill(dt);
dts.Tables.Add(dt);
// Se asigna el DataTable como origen de datos del DataGridView
dataGridView1.DataSource = dts.Tables[0];
}
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 void Prev_Load(Usuario usu, Prevista prv)
{
// Llena el combobox tarifas
//trfDao = new TarifaDAO(this.user);
//cbxRate.DisplayMember = "TypeAppString";
//cbxRate.ValueMember = "Code";
//cbxRate.DataSource = trfDao.LoadTarifa();
cbxRate.Items.Add("Mensual Fija");
cbxRate.Items.Add("Metros Cúbicos");
cbxRate.SelectedItem = 0;
// LLena el combo de Abonados
abnDao = new AbonadoDAO(this.user);
cbxAbonado.DisplayMember = "Name";
cbxAbonado.ValueMember = "Identification";
if (abnDao.LoadAbonado1().Count > 0)
{
cbxAbonado.DataSource = abnDao.LoadAbonado1();
}
else
{
MessageBox.Show("No hay abonados disponibles para asignar a las previstas!", "Atención");
//mostrar popup, notificacion o mensaje en un label o formulario, por al menos 3 segundos
}
// obtiene el consecutivo de previstas
prvDao = new PrevistaDAO(this.user);
txtCode.Text = prvDao.ConsecutivePrv().ToString();
txtCode.ReadOnly = true;
txtCode.Enabled = false;
// se obtienen y establecen los sectores
secDao = new SectorDAO(this.user);
cbxSector.DisplayMember = "Description";
cbxSector.ValueMember = "Code";
cbxSector.DataSource = secDao.LoadSector();
}
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)
{
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;
}
}
private void previstasToolStripMenuItem_Click(object sender, EventArgs e)
{
Prevista pv = new Prevista();
frmPrevistas fprv = new frmPrevistas(usu,pv);
fprv.ShowDialog(this);
}
private void btnNewUpdate_Click(object sender, EventArgs e)
{
prvDao = new PrevistaDAO(this.user);
btnSaveOption = btnNewUpdate.Text;
if (txtAddress.Text.Trim() == "")
{
MessageBox.Show(this, "Se detecto campos vacíos! Verifique...", "Información", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
switch (btnSaveOption)
{
case "Guardar":
if (prvDao.InsertNew((prv = this.GetInfo())))
{
MessageBox.Show(this, "Datos almacenados correctamente!", "Información", MessageBoxButtons.OK, MessageBoxIcon.Information);
//refrescar el combo de abonados sin prevista
}
else
{
MessageBox.Show(this, "Error al guardar los datos! Verifique...", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
break;
case "Actualizar":
prvDao.UpdatePrv((prv = this.GetInfo()));
this.ValidUtils(prv);
break;
}
}
}
private bool ValidUtils(Prevista pv)
{
prv = new Prevista();
prv = pv;
if (pv.Code == null)
{
btnNewUpdate.Text = "Guardar";
return false;
}
else
{
btnNewUpdate.Text = "Actualizar";
checkBoxState.Checked = true;
return true;
}
}