public static List<PersonObj> getAllByKindId(int kind)
{
List<PersonObj> list = new List<PersonObj>();
try
{
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from person where kind=" + kind;
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
PersonObj product = new PersonObj();
product.id = r.GetInt32("id");
product.name = r.GetString("name");
product.lastname = r.GetString("lastname");
product.address = r.GetString("address1");
product.phone = r.GetString("phone1");
product.email = r.GetString("email1");
list.Add(product);
}
}
catch (MySqlException me)
{
MessageBox.Show(me.Message);
}
return list;
}
public static List<ProductObj> getAll()
{
List<ProductObj> list = new List<ProductObj>();
try
{
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from product";
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
ProductObj product = new ProductObj();
product.id = r.GetInt32("id");
product.name = r.GetString("name");
product.price_in = r.GetFloat("price_in");
product.price_out = r.GetFloat("price_out");
product.unit = r.GetString("unit");
list.Add(product);
}
}
catch (MySqlException me)
{
MessageBox.Show(me.Message);
}
return list;
}
private void button1_Click(object sender, EventArgs e)
{
if (name.Text != "" && lastname.Text != "")
{
Connection c = new Connection();
c.execute("insert into person (name,lastname,address1,email1,phone1,kind,created_at) value(\"" + name.Text + "\",\"" + lastname.Text + "\",\"" + address.Text + "\",\"" + email.Text + "\",\"" + phone.Text + "\","+kind+",NOW() )");
MessageBox.Show("Contacto agregado exitosamente!");
name.Text = lastname.Text = address.Text = email.Text = phone.Text = "";
}
}
private void button1_Click(object sender, EventArgs e)
{
if (product.Text != "" && q.Text != "") {
//MessageBox.Show(""+ids[product.SelectedIndex]);
Connection c = new Connection();
c.execute("insert into operation(product_id,q,operation_type_id,created_at) value (" + ids[product.SelectedIndex] + ","+q.Text+",1,NOW())");
q.Text = "";
MessageBox.Show("Alta en inventario exitosa!");
}
else {
MessageBox.Show("Campos Requeridos: Producto, Cantidad");
}
}
private void button1_Click(object sender, EventArgs e)
{
if (name.Text != "" && price_in.Text != "" && price_out.Text != "")
{
Connection c = new Connection();
c.execute("insert into product (name,price_in,price_out,unit) value(\""+name.Text+"\",\""+price_in.Text+"\",\""+price_out.Text+"\",\""+unit.Text+"\")");
MessageBox.Show("Producto agregado exitosamente!");
name.Text = price_in.Text = price_out.Text = unit.Text = "";
}
else {
MessageBox.Show("Campos requeridos: Nombre, precio entrada, precio salida");
}
}
public static int getSumByProduct(int product_id, int op_type_id)
{
int total = 0;
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
String sql = "select q from operation where product_id=" + product_id + " and operation_type_id=" + op_type_id;
Console.WriteLine(sql);
cmd.CommandText = sql;
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
total += r.GetInt32("q");
}
return total;
}
public static List<SellObj> getAllByOperationTypeId(int kind)
{
List<SellObj> list = new List<SellObj>();
try
{
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from sell where operation_type_id=" + kind+" order by created_at desc";
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
SellObj product = new SellObj();
product.id = r.GetInt32("id");
if (!r.IsDBNull(1))
{
product.person_id = r.GetInt32("person_id");
}
else {
product.person_id = 0;
}
if (!r.IsDBNull(4))
{
product.box_id = r.GetInt32("box_id");
}
else
{
product.box_id = 0;
}
product.operation_type_id = r.GetInt32("operation_type_id");
product.created_at = r.GetDateTime("created_at");
list.Add(product);
}
}
catch (MySqlException me)
{
MessageBox.Show(me.Message);
}
return list;
}
public static List<OperationObj> getAllBySellId(int sell_id)
{
List<OperationObj> list = new List<OperationObj>();
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from operation where sell_id=" + sell_id + " order by created_at desc";
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
OperationObj product = new OperationObj();
product.product_id = r.GetInt32("product_id");
//product.sell_id= r.GetInt32("sell_id");
product.q = r.GetInt32("q");
product.operation_type_id = r.GetInt32("operation_type_id");
product.created_at = r.GetDateTime("created_at");
list.Add(product);
}
return list;
}
public static ProductObj getById(int product_id)
{
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from product where id=" + product_id;
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
ProductObj product = new ProductObj();
while (r.Read())
{
product.id= r.GetInt32("id");
product.name = r.GetString("name");
product.price_in = r.GetFloat("price_in");
product.price_out = r.GetFloat("price_out");
product.unit = r.GetString("unit");
break;
}
return product;
}
private void procesarToolStripMenuItem_Click(object sender, EventArgs e)
{
Connection c = new Connection();
if (client_.SelectedIndex != -1)
{
c.execute("insert into sell (person_id,created_at) value (" + lp[client_.SelectedIndex].id + ",NOW())");
}
else
{
c.execute("insert into sell (created_at) value (NOW())");
}
long sell_id = c.insert_id;
List<SellObj> sell = SellObj.sell;
for (int i = 0; i < sell.Count; i++)
{
c.execute("insert into operation (product_id,q,sell_id,operation_type_id,created_at) value (" + sell[i].product_id + "," + sell[i].q + "," + sell_id + ",2,NOW())");
}
data.Rows.Clear();
SellObj.sell.Clear();
MessageBox.Show("Venta Aplicada Exitosamente");
Dispose();
}
private void button2_Click(object sender, EventArgs e)
{
Connection c = new Connection();
if (provider.SelectedIndex != -1)
{
c.execute("insert into sell (person_id,operation_type_id,created_at) value (" + provs[provider.SelectedIndex].id + ",1,NOW())");
}
else
{
c.execute("insert into sell (operation_type_id,created_at) value (1,NOW())");
}
long sell_id = c.insert_id;
List<SellObj> sell = SellObj.re;
for (int i = 0; i < sell.Count; i++)
{
c.execute("insert into operation (product_id,q,sell_id,operation_type_id,created_at) value (" + sell[i].product_id + "," + sell[i].q + "," + sell_id + ",1,NOW())");
}
data.Rows.Clear();
SellObj.sell.Clear();
MessageBox.Show("Abastecimiento Aplicado Exitosamente");
// Dispose();
}
public static PersonObj getById(int product_id)
{
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from product where id=" + product_id;
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
PersonObj product = new PersonObj();
while (r.Read())
{
product.id= r.GetInt32("id");
product.name = r.GetString("name");
product.lastname = r.GetString("lastname");
product.address = r.GetString("address1");
product.phone = r.GetString("phone1");
product.email = r.GetString("email1");
break;
}
return product;
}
public Input()
{
InitializeComponent();
try
{
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from product";
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
product.Items.Add(r.GetString("Name"));
ids.Add(r.GetInt32("id"));
}
}
catch (MySqlException me)
{
MessageBox.Show(me.Message);
}
}
private void button3_Click(object sender, EventArgs e)
{
if (product.Text != "")
{
data.Rows.Clear();
try
{
Connection c = new Connection();
MySqlCommand cmd = c.con.CreateCommand();
cmd.CommandText = "select * from product where name like \"%" + product.Text + "%\"";
c.con.Open();
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
data.Rows.Add(r.GetString("id"), r.GetString("name"), r.GetString("unit"), "", r.GetInt32("price_out"));
}
}
catch (MySqlException me)
{
MessageBox.Show(me.Message);
}
}
else {
data.Rows.Clear();
}
}