private void PropagandaCliente_Load(object sender, EventArgs e)
{
textBox1.Text = nombre;
modifica = 0;
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera Datos del cliente
try
{
conexion.Open();
sentencia = "select count(*) from tb_propaganda_cliente where id_cli = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (row[0] != null && Convert.ToDecimal(row[0]) > 0)
{
modifica = 1;
traer_datos();
}
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void traer_mon_tot_cadet()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera clientes
try
{
sentencia = "select monto_tot,marca_deuda from tb_cadete_pago where id_cadet = '" + id + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
conexion.Close();
foreach (DataRow row in dt.Rows)
{
importe_normal = Convert.ToDecimal(row[0]);
marca_deuda = Convert.ToInt16(row[1]);
}
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
}
private void traer_datos()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera Datos del cliente
try
{
conexion.Open();
sentencia = "select * from tb_clientes where id_cli = '" + id_mod + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
textBox1.Text = Convert.ToString(row[1]);
textBox2.Text = Convert.ToString(row[2]);
textBox3.Text = Convert.ToString(row[3]);
textBox4.Text = Convert.ToString(row[4]);
textBox5.Text = Convert.ToString(row[5]);
dateTimePicker1.Value = Convert.ToDateTime(row[6]);
comboBox1.Text = Convert.ToString(row[7]);
textBox6.Text = Convert.ToString(row[8]);
}
dt.Rows.Clear();
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void iserta_propaganda()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
byte[] blobData = ImageToByteArray(pictureBox1.Image);
if (blobData == null)
{
MessageBox.Show("Debe cargar una publicidad para el cliente antes de Grabar", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (textBox2.Text == "")
{
MessageBox.Show("Debe ingresar un precio de publicidad antes de Grabar", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
try
{
conexion.Open();
sentencia = "insert into tb_propaganda_cliente(id_cli,publicidad,fecvigencia,precio) values ('" + id + "', @imagen ,'" + String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) + "','" + textBox2.Text + "')";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.Parameters.Add("@imagen", FbDbType.Binary, blobData.Length).Value = blobData;
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("La Propaganda para el cliente a sido Actualizada", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private static decimal Busca_resto(decimal id_reg2, FbConnection conexion, string sentencia, decimal importe_cadete, decimal resto)
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "select sdo_restante from TB_CADET_REG_PAGO where id_reg_pg = '" + id_reg2 + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (row[0] == null)
{
resto = Convert.ToDecimal(importe_cadete);
}
else
{
resto = Convert.ToDecimal(row[0]);
}
}
dt.Rows.Clear();
return(resto);
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return(-1);
}
}
private void verifica_inserta()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera clientes
try
{
sentencia = "select count(*) from tb_bloqcadet where id_cadet = '" + id + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (Convert.ToInt16(row[0]) == 0)
{
inserta_fila();
bandera = 1;
label3.Text = "Estado: Desbloqueado";
label3.ForeColor = Color.Green;
}
else
{
setea_valor();
bandera = 0;
}
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void traer_datos()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera Datos del cliente
try
{
conexion.Open();
sentencia = "select * from tb_propaganda_cliente where id_cli = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if ((row[1]) != null)
{
pictureBox1.Image = ByteArrayToImage((byte[])row[1]);
}
dateTimePicker1.Value = Convert.ToDateTime(row[2]);
textBox2.Text = Convert.ToString(row[3]);
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void button5_Click(object sender, EventArgs e)
{
Decimal id = 0;
DataGridViewRow row = new DataGridViewRow();
row = dataGridView1.CurrentRow;
if (row == null)
{
return;
}
id = Convert.ToDecimal(row.Cells["Id"].Value);
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
sentencia = "update tb_cadetes set nom_cadet = '" + textBox2.Text + "',ape_cadet = '" + textBox6.Text + "' ,dni_cadet = '" + textBox3.Text + "'" +
",nummov_cadet = '" + textBox5.Text + "',cel_cadet = '" + textBox4.Text + "' where id_cadet = '" + id + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("Datos de Cadete modificados correctamente", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
Retrive();
}
private void Retrive()
{
dataGridView1.Rows.Clear();
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera Max ID cliente
try
{
conexion.Open();
sentencia = "select cod_localidad,localidad from tb_localidad";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
dataGridView1.Rows.Add(row[0], row[1]);
}
dt.Rows.Clear();
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void Config_PCadetes_Load(object sender, EventArgs e)
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
sentencia = "select count(*) from tb_confi_pc";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (Convert.ToInt16(row[0]) == 0)
{
inserta_fila();
}
else
{
trae_info();
}
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void Retrive()
{
dataGridView1.Rows.Clear();
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera clientes
try
{
//id_cadet,nom_cadet,ape_cadet,dni_cadet,fecnac_cadet,fecalt_cadet,nummov_cadet,vehi_cadet,marc_cadet,patent_cadet,domi_cadet,localidad_cadet,foto_cadet,cel_cadet
if (bandera == "nombre")
{
sentencia = "select id_cadet,nom_cadet,ape_cadet,dni_cadet,vehi_cadet,cel_cadet,domi_cadet from tb_cadetes where (UPPER(nom_cadet) like UPPER('%" + textBox1.Text + "%')) OR (UPPER(ape_cadet) like UPPER('%" + textBox1.Text + "%'))";
}
if (bandera == "dni")
{
sentencia = "select id_cadet,nom_cadet,ape_cadet,dni_cadet,vehi_cadet,cel_cadet,domi_cadet from tb_cadetes where CAST(dni_cadet as varchar(50)) like '%" + textBox1.Text + "%'";
}
if (bandera == "domicilio")
{
sentencia = "select id_cadet,nom_cadet,ape_cadet,dni_cadet,vehi_cadet,cel_cadet,domi_cadet from tb_cadetes where (UPPER(domi_cadet) like UPPER('%" + textBox1.Text + "%'))";
}
if (bandera == "movil")
{
sentencia = "select select id_cadet,nom_cadet,ape_cadet,dni_cadet,vehi_cadet,cel_cadet,domi_cadet from tb_cadetes where nummov_cadet = '" + textBox1.Text + "'";
}
if (bandera == "id")
{
sentencia = "select id_cadet,nom_cadet,ape_cadet,dni_cadet,vehi_cadet,cel_cadet,domi_cadet from tb_cadetes where id_cadet = '" + textBox1.Text + "'";
}
if ((bandera == "general") || (textBox1.Text == ""))
{
sentencia = "select id_cadet,nom_cadet,ape_cadet,dni_cadet,vehi_cadet,cel_cadet,domi_cadet from tb_cadetes";
}
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
string nombre;
nombre = Convert.ToString(row[1]) + " " + Convert.ToString(row[2]);
dataGridView1.Rows.Add(row[0], nombre, row[3], row[4], row[5], row[6]);
}
dt.Rows.Clear();
conexion.Close();
datos_rapidos();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void button4_Click(object sender, EventArgs e)
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
string nombre, apellido, vehiculo, marca, patente, domicilio, localidad, celular, comentario;
int dni, nom_movil, activo;
string fecnac, fecalt;
byte[] blobData = ImageToByteArray(pictureBox1.Image);
if (checkBox1.Checked == true)
{
activo = 0;
}
else
{
activo = 1;
}
nombre = textBox1.Text;
apellido = textBox2.Text;
dni = Convert.ToInt32(textBox3.Text);
fecnac = String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value);
fecalt = String.Format("{0:yyyy-MM-dd}", dateTimePicker2.Value);;
nom_movil = Convert.ToInt32(textBox4.Text);
vehiculo = textBox5.Text;
marca = textBox6.Text;
patente = textBox7.Text;
domicilio = textBox8.Text;
localidad = comboBox1.Text;
celular = textBox9.Text;
comentario = textBox10.Text;
try
{
sentencia = "update tb_cadetes set nom_cadet = '" + nombre + "',ape_cadet = '" + apellido + "' ,dni_cadet = '" + dni + "'" +
",fecnac_cadet = '" + fecnac + "',fecalt_cadet = '" + fecalt + "',nummov_cadet = '" + nom_movil + "',vehi_cadet = '" + vehiculo + "'" +
",marc_cadet = '" + marca + "',patent_cadet = '" + patente + "',domi_cadet = '" + domicilio + "',localidad_cadet = '" + localidad + "'," +
"foto_cadet = @imagen,cel_cadet = '" + celular + "',comen_cadet = '" + comentario + "',activo = '" + activo + "' where id_cadet = '" + id_mod + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.Parameters.Add("@imagen", FbDbType.Binary, blobData.Length).Value = blobData;
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("Datos de Cadete modificados correctamente", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
this.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void traer_datos()
{
sentencia = "select nom_cadet,ape_cadet,dni_cadet,fecnac_cadet,fecalt_cadet,nummov_cadet,vehi_cadet,marc_cadet,patent_cadet,domi_cadet,localidad_cadet,foto_cadet,cel_cadet,comen_cadet,activo from tb_cadetes where id_cadet = '" + id_mod + "'";
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row1 in dt.Rows)
{
textBox1.Text = Convert.ToString(row1[0]);
textBox2.Text = Convert.ToString(row1[1]);
textBox3.Text = Convert.ToString(row1[2]);
dateTimePicker1.Value = Convert.ToDateTime(row1[3]);
dateTimePicker2.Value = Convert.ToDateTime(row1[4]);
textBox4.Text = Convert.ToString(row1[5]);
textBox5.Text = Convert.ToString(row1[6]);
textBox6.Text = Convert.ToString(row1[7]);
textBox7.Text = Convert.ToString(row1[8]);
textBox8.Text = Convert.ToString(row1[9]);
comboBox1.Text = Convert.ToString(row1[10]);
if ((row1[11]) != null)
{
pictureBox1.Image = ByteArrayToImage((byte[])row1[11]);
}
textBox9.Text = Convert.ToString(row1[12]);
textBox10.Text = Convert.ToString(row1[13]);
Decimal valor;
valor = Convert.ToDecimal(row1[14]);
if (valor == 0)
{
checkBox1.Checked = true;
}
else
{
checkBox1.Checked = false;
}
}
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void Retrive()
{
dataGridView1.Rows.Clear();
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera clientes
try
{
conexion.Open();
if (bandera == "general")
{
sentencia = "select id_cli,nombre_cli,domicilio_cli,telefonoprin_cli,telefonoaux_cli,telefonocel_cli from tb_clientes";
}
if (bandera == "nombre")
{
sentencia = "select id_cli,nombre_cli,domicilio_cli,telefonoprin_cli,telefonoaux_cli,telefonocel_cli from tb_clientes where UPPER(nombre_cli) like UPPER('%" + textBox1.Text + "%')";
}
if (bandera == "domicilio")
{
sentencia = "select id_cli,nombre_cli,domicilio_cli,telefonoprin_cli,telefonoaux_cli,telefonocel_cli from tb_clientes where UPPER(domicilio_cli) like UPPER('%" + textBox1.Text + "%')";
}
if (bandera == "telefono")
{
sentencia = "select id_cli,nombre_cli,domicilio_cli,telefonoprin_cli,telefonoaux_cli,telefonocel_cli from tb_clientes where (UPPER(CAST(telefonoprin_cli as varchar(50))) like UPPER('%" + textBox1.Text + "%')) OR (UPPER(CAST(telefonoaux_cli as varchar(50))) like UPPER('%" + textBox1.Text + "%')) OR (UPPER(CAST(telefonocel_cli as varchar(50))) like UPPER('%" + textBox1.Text + "%')) ";
}
if (bandera == "id")
{
sentencia = "select id_cli,nombre_cli,domicilio_cli,telefonoprin_cli,telefonoaux_cli,telefonocel_cli from tb_clientes where UPPER(CAST(id_cli as varchar(50))) like UPPER('%" + textBox1.Text + "%')";
}
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
dataGridView1.Rows.Add(row[0], row[1], row[2], row[3], row[4], row[5]);
}
dt.Rows.Clear();
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
public static DataTable LoadDataTable()
{
DataTable dt = new DataTable();
FbConnection conexion = new FbConnection();
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
FbCommand command = new FbCommand();
command.Connection = conexion;
command.CommandText = "SELECT * FROM tb_localidad";
FbDataAdapter da = new FbDataAdapter(command);
da.Fill(dt);
return(dt);
}
private void button2_Click(object sender, EventArgs e)
{
if (button2.Text == "Bloquear")
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "update tb_bloqcadet set marc_bloqueo = 1,fecha_bloqueo = '" + String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) + "' , comentario = '" + textBox1.Text + "' where id_cadet = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("El Cadete fue Bloqueado con Exito", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
else
{
if (MessageBox.Show("¿Esta seguro que desea Desbloquear al Cadete?", "Atención", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.No)
{
return;
}
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "update tb_bloqcadet set marc_bloqueo = 0,fecha_bloqueo = null, comentario = null where id_cadet = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("El Cadete fue desbloqueado con Exito", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
}
private void inserta_fila()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "insert into tb_bloqcadet (id_cadet,marc_bloqueo) " +
"values ('" + id + "',0)";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void inserta_fila()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "insert into tb_confi_pc (monto_cadete,monto_interes) " +
"values (0,0)";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void button2_Click(object sender, EventArgs e)
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "update tb_confi_pc set monto_cadete = '" + textBox1.Text + "' ,monto_interes = '" + textBox2.Text + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("Datos Guardados con exitos", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void setea_valor()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera clientes
try
{
sentencia = "select marc_bloqueo,fecha_bloqueo,comentario from tb_bloqcadet where id_cadet = '" + id + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
int marca;
string comentario;
marca = Convert.ToInt16(row[0]);
comentario = Convert.ToString(row[2]);
textBox1.Text = comentario;
if (marca == 0)
{
label3.Text = "Estado: Desbloqueado";
label3.ForeColor = Color.Green;
button2.Text = "Bloquear";
}
else
{
label3.Text = "Estado: Bloqueado";
label3.ForeColor = Color.Red;
button2.Text = "Desbloquear";
dateTimePicker1.Value = Convert.ToDateTime(row[1]);
}
}
dt.Rows.Clear();
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void inserta_fila()
{
decimal importe;
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
importe = 0;
if ((Convert.ToDecimal(textBox2.Text)) != 0)
{
importe = Convert.ToDecimal(textBox2.Text);
}
if ((Convert.ToDecimal(textBox3.Text)) != 0)
{
importe = Convert.ToDecimal(textBox3.Text);
}
if ((Convert.ToDecimal(textBox4.Text)) != 0)
{
importe = Convert.ToDecimal(textBox4.Text);
}
fecha = dateTimePicker1.Value;
dateTimePicker2.Value = fecha.AddDays(7);
conexion.Open();
sentencia = "insert into tb_cadete_pago (id_cadet,ultimo_pago,pago_actual,fecha_pago,fecha_venc,monto_tot,marca_deuda) " +
"values ('" + id + "',0,'" + importe + "','" + String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) + "','" + String.Format("{0:yyyy-MM-dd}", dateTimePicker2.Value) + "','" + importe_cadete + "','" + marca_deuda + "')";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
}
private void actualiza_cadet_pag()
{
decimal importe;
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
importe = 0;
if ((Convert.ToDecimal(textBox2.Text)) != 0)
{
importe = Convert.ToDecimal(textBox2.Text);
}
if ((Convert.ToDecimal(textBox3.Text)) != 0)
{
importe = Convert.ToDecimal(textBox3.Text);
}
if ((Convert.ToDecimal(textBox4.Text)) != 0)
{
importe = Convert.ToDecimal(textBox4.Text);
}
conexion.Open();
sentencia = "update tb_cadete_pago " +
"set ultimo_pago = pago_actual ," +
"pago_actual = '" + importe + "' ," +
"fecha_pago = '" + String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) + "', " +
" marca_deuda = '" + marca_deuda + "' where id_cadet = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
}
private void setea_numMovil()
{
decimal nummovil = 0;
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera Max ID cliente
try
{
conexion.Open();
sentencia = "select max(nummov_cadet) from tb_cadetes";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
if (row[0] == DBNull.Value)
{
nummovil = 0;
}
else
{
nummovil = Convert.ToDecimal(row[0]);
}
}
nummovil = nummovil + 1;
dt.Rows.Clear();
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
textBox4.Text = Convert.ToString(nummovil);
}
private void button4_Click(object sender, EventArgs e)
{
//Elimina Cliente
Decimal id = 0;
DataGridViewRow row = new DataGridViewRow();
row = dataGridView1.CurrentRow;
if (row == null)
{
return;
}
id = Convert.ToDecimal(row.Cells["Id"].Value);
if (MessageBox.Show("¿Esta seguro que desea eliminar el Cliente?", "Atención", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.No)
{
return;
}
else
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "delete from tb_clientes where id_cli = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("Cliente Eliminado con Exito", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
Retrive();
}
}
private void trae_info()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
sentencia = "select * from tb_confi_pc";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
conexion.Close();
foreach (DataRow row in dt.Rows)
{
textBox1.Text = Convert.ToString(row[0]);
textBox2.Text = Convert.ToString(row[1]);
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void button3_Click(object sender, EventArgs e)
{
decimal id = 0;
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera Max ID cliente
try
{
conexion.Open();
sentencia = "select max(id_cadet) from tb_cadetes";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
if (row[0] == DBNull.Value)
{
id = 0;
}
else
{
id = Convert.ToDecimal(row[0]);
}
}
id = id + 1;
dt.Rows.Clear();
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
string nombre, apellido, vehiculo, marca, patente, domicilio, localidad, celular, comentario;
int dni, nom_movil, activo;
string fecnac, fecalt;
byte[] blobData = ImageToByteArray(pictureBox1.Image);
if (checkBox1.Checked == true)
{
activo = 0;
}
else
{
activo = 1;
}
nombre = textBox1.Text;
apellido = textBox2.Text;
dni = Convert.ToInt32(textBox3.Text);
fecnac = String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value);
fecalt = String.Format("{0:yyyy-MM-dd}", dateTimePicker2.Value);;
nom_movil = Convert.ToInt32(textBox4.Text);
vehiculo = textBox5.Text;
marca = textBox6.Text;
patente = textBox7.Text;
domicilio = textBox8.Text;
localidad = comboBox1.Text;
celular = textBox9.Text;
comentario = textBox10.Text;
try
{
conexion.Open();
sentencia = "insert into tb_cadetes (id_cadet,nom_cadet,ape_cadet,dni_cadet,fecnac_cadet,fecalt_cadet,nummov_cadet,vehi_cadet,marc_cadet,patent_cadet,domi_cadet,localidad_cadet,foto_cadet,cel_cadet,comen_cadet,activo) " +
"values ('" + id + "','" + nombre + "','" + apellido + "','" + dni + "','" + fecnac + "','" + fecalt + "','" + nom_movil + "','" + vehiculo + "','" + marca + "','" + patente + "','" + domicilio + "','" + localidad + "', @imagen,'" + celular + "','" + comentario + "','" + activo + "')";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.Parameters.Add("@imagen", FbDbType.Binary, blobData.Length).Value = blobData;
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
MessageBox.Show("Alta de Cadete Exitosa", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
this.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}
private void PagoCadetes_Load(object sender, EventArgs e)
{
// Fecha seleccionada en el control DataTimePicker
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
recupera_config();
//Recupera clientes
try
{
sentencia = "select count(*) from tb_cadete_pago where id_cadet = '" + id + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (Convert.ToInt16(row[0]) == 0)
{
bandera = "PP";
}
else
{
bandera = "PN";
sentencia = "select fecha_venc from tb_cadete_pago where id_cadet = '" + id + "'";
conexion.Open();
FbCommand cmd1 = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader1 = cmd1.ExecuteReader();
DataTable dt1 = new DataTable();
dt1.Load(fb_datareader1);
conexion.Close();
foreach (DataRow row1 in dt1.Rows)
{
fecha = Convert.ToDateTime(row1[0]);
if (fecha <= DateTime.Today)
{
while (fecha < DateTime.Today)
{
// fecha = DateTime.Today;
fecha = fecha.AddDays(7);
//dateTimePicker2.Value = fecha.AddDays(7);
actualizar_fecysdo();
}
dateTimePicker2.Value = fecha;
}
else
{
dateTimePicker2.Value = fecha;
}
traer_mon_tot_cadet();
importe_cadete = importe_normal;
}
}
}
dt.Rows.Clear();
label2.Text = nombre;
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
Retrive();
}
private void inserta_regpag()
{
decimal id_reg = 0;
decimal id_reg2 = 0;
id_reg = 0;
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
try
{
conexion.Open();
sentencia = "select max(id_reg_pg) from TB_CADET_REG_PAGO";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (row[0] == DBNull.Value)
{
id_reg = 0;
}
else
{
id_reg = Convert.ToDecimal(row[0]);
}
}
id_reg = id_reg + 1;
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
/*Logica de Inserción */
decimal importe = 0;
decimal resto = 0;
int compensa = 0;
int tip_pago = 0;
try
{
conexion.Open();
sentencia = "select max(id_reg_pg) from TB_CADET_REG_PAGO where id_cadet = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (row[0] == DBNull.Value)
{
id_reg2 = 0;
}
else
{
id_reg2 = Convert.ToDecimal(row[0]);
}
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
try
{
sentencia = "select count(*) from TB_CADET_REG_PAGO where id_reg_pg = '" + id_reg2 + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (bandera_denewsemana == 1)
{
resto = importe_cadete;
}
else
{
if (Convert.ToInt16(row[0]) == 0)
{
resto = importe_cadete;
}
else
{
resto = Busca_resto(id_reg2, conexion, sentencia, importe_cadete, resto);
}
}
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
decimal origen = 0;
if ((Convert.ToDecimal(textBox2.Text)) != 0)
{
importe = importe + Convert.ToDecimal(textBox2.Text);
origen = 1; //Pago efectivo
}
if ((Convert.ToDecimal(textBox3.Text)) != 0)
{
importe = importe + Convert.ToDecimal(textBox3.Text);
origen = 2; //Pago Cupon
}
if ((Convert.ToDecimal(textBox4.Text)) != 0)
{
importe = importe + Convert.ToDecimal(textBox4.Text);
origen = 3; //Pago CC
}
if (checkBox1.Checked == true)
{
compensa = 1;
tip_pago = 0; //Pago compensado por Admin/Usuario
resto = resto - importe;
importe = resto;
resto = 0;
origen = 4;
}
else
{
resto = resto - importe;
compensa = 0;
if (resto <= 0)
{
marca_deuda = 0;
//Pago Completado
tip_pago = 1;
if (resto < 0)
{
if (MessageBox.Show("El Cadete realiza un pago que dejara saldo a su favor. ¿Desea continuar con la Operación? ", "Atención", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
else
{
MessageBox.Show("El Cadete completo el Pago semanal", "Información", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
tip_pago = 2; //Pago Pago Parcial
resto = resto + importe_interes;
}
}
string fecha = DateTime.Now.ToString("yyyy-MM-dd");
string tiempo = DateTime.Now.ToString("HH:mm:ss.ffff");
fecha = fecha + " " + tiempo;
try
{
conexion.Open();
sentencia = "insert into TB_CADET_REG_PAGO (id_reg_pg,id_cadet,fecpago,fecven,fecreal,fecpro,importe,sdo_total,sdo_restante,tip_pago,compensado,comentario,origen_pago) " +
"values ('" + id_reg + "','" + id + "','" + String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) + "','" + String.Format("{0:yyyy-MM-dd}", dateTimePicker2.Value) + "'," +
"'" + fecha + "','" + String.Format("{0:yyyy-MM-dd}", DateTime.Today) + "','" + importe + "','" + importe_cadete + "','" + resto + "','" + tip_pago + "','" + compensa + "','" + textBox1.Text + "','" + origen + "')";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
Retrive();
}
private void Retrive()
{
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera clientes
decimal marca_deuda = 0;
try
{
sentencia = "select pago_actual,marca_deuda from tb_cadete_pago where id_cadet = '" + id + "'";
conexion.Open();
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
conexion.Close();
foreach (DataRow row in dt.Rows)
{
label18.Text = Convert.ToString(row[0]);
marca_deuda = Convert.ToDecimal(row[1]);
}
if (dt.Rows.Count == 0)
{
label18.Text = "Sin datos";
label15.Text = "Sin datos";
marca_deuda = 9;
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
decimal id_reg2 = 0;
decimal sdo_total = 0;
decimal origen_pago = 0;
decimal tipo_pago = 0;
try
{
conexion.Open();
sentencia = "select max(id_reg_pg) from TB_CADET_REG_PAGO where id_cadet = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (row[0] == DBNull.Value)
{
id_reg2 = 0;
}
else
{
id_reg2 = Convert.ToDecimal(row[0]);
}
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
try
{
conexion.Open();
sentencia = "select origen_pago,sdo_restante,tip_pago,fecreal from TB_CADET_REG_PAGO where id_reg_pg = '" + id_reg2 + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
label12.Text = Convert.ToString(row[1]);
origen_pago = Convert.ToDecimal(row[0]);
tipo_pago = Convert.ToDecimal(row[2]);
string fecha = Convert.ToDateTime(row[3]).ToString("yyyy-MM-dd");
string tiempo = Convert.ToDateTime(row[3]).ToString("HH:mm:ss");
label15.Text = fecha + " " + tiempo;
}
if (dt.Rows.Count == 0)
{
label12.Text = "Sin Datos";
origen_pago = 9;
tipo_pago = 9;
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
try
{
conexion.Open();
sentencia = "select sum(importe) from TB_CADET_REG_PAGO where id_cadet = '" + id + "'";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
conexion.Close();
foreach (DataRow row in dt.Rows)
{
if (row[0] == DBNull.Value)
{
sdo_total = 0;
}
else
{
sdo_total = Convert.ToDecimal(row[0]);
}
}
dt.Rows.Clear();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
return;
}
if (marca_deuda == 0 && tipo_pago == 1)
{
textBox5.Text = "Al día";
textBox5.ForeColor = Color.Green;
}
else
{
if (marca_deuda == 0 && tipo_pago == 2)
{
textBox5.Text = "Al día,con Pago Parcial";
textBox5.ForeColor = Color.Green;
}
else
{
if (marca_deuda == 0 && tipo_pago == 0)
{
textBox5.Text = "Al día,Conpensado por Usuario";
textBox5.ForeColor = Color.Green;
}
}
}
if (marca_deuda == 1)
{
textBox5.Text = "Con Atraso Moderado";
textBox5.ForeColor = Color.Orange;
}
if (marca_deuda > 1)
{
textBox5.Text = "Con Atraso Riesgoso";
textBox5.ForeColor = Color.Red;
}
if (origen_pago == 1)
{
label20.Text = "Efectivo";
}
if (origen_pago == 2)
{
label20.Text = "Cupones";
}
if (origen_pago == 3)
{
label20.Text = "Cuenta Corriente";
}
if (marca_deuda == 9)
{
textBox5.Text = "Sin Datos, Cadete Nuevo";
textBox5.ForeColor = Color.Black;
}
if (origen_pago == 9)
{
label20.Text = "Sin Datos";
}
label29.Text = Convert.ToString(sdo_total);
}
private void button3_Click(object sender, EventArgs e)
{
decimal id = 0;
conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena());
//Recupera Max ID cliente
try
{
conexion.Open();
sentencia = "select max(id_cli) from tb_clientes";
FbCommand cmd = new FbCommand(sentencia, conexion);
FbDataReader fb_datareader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(fb_datareader);
cmd = null;
foreach (DataRow row in dt.Rows)
{
if (row[0] == DBNull.Value)
{
id = 0;
}
else
{
id = Convert.ToDecimal(row[0]);
}
}
id = id + 1;
dt.Rows.Clear();
conexion.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
decimal tel_aux;
decimal cel;
//Inserta Cliente
if (textBox4.Text == "")
{
tel_aux = 0;
}
else
{
tel_aux = Convert.ToDecimal(textBox4.Text);
}
if (textBox5.Text == "")
{
cel = 0;
}
else
{
cel = Convert.ToDecimal(textBox5.Text);
}
try
{
conexion.Open();
sentencia = "insert into tb_clientes(id_cli,nombre_cli,domicilio_cli,telefonoprin_cli,telefonoaux_cli,telefonocel_cli,fecalta_cli,localidad_cli,comentarios_cli) values ('" + id + "','" + this.textBox1.Text + "','" + this.textBox2.Text + "','" + this.textBox3.Text + "','" + tel_aux + "','" + cel + "','" + String.Format("{0:yyyy-MM-dd}", dateTimePicker1.Value) + "','" + comboBox1.Text + "','" + textBox6.Text + "')";
FbCommand cmd = new FbCommand(sentencia, conexion);
cmd.ExecuteNonQuery();
cmd = null;
conexion.Close();
if (MessageBox.Show("Alta de cliente exitosa. ¿Desea continuar con la configuración del cliente?", "Atención", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
button4.Enabled = false;
button3.Enabled = false;
textBox1.Enabled = false;
textBox2.Enabled = false;
textBox3.Enabled = false;
textBox4.Enabled = false;
textBox5.Enabled = false;
comboBox1.Enabled = false;
button1.Enabled = false;
textBox6.Enabled = false;
id_mod = id;
}
else
{
this.Close();
}
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString(), "Error");
}
}