protected void TxtSearch_TextChanged(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("select firstName from del_user where firstname like'" + (TxtSearch.Text).Trim() + "%'", con);
SqlDataReader rdr = cmd.ExecuteReader();
LstItems.DataSource = rdr;
LstItems.DataTextField = "FirstName";
LstItems.DataBind();
}
void carga_productos(int d_familia, int id_categoria, int id_subcategoria)
{
string sql = "";
sql = "SELECT dbo.tbl_Items.Id_Linea_Venta, dbo.tbl_Familias_Productos.ID_Familia, dbo.tbl_Items.Id_Categoria, dbo.tbl_Items.Id_SubCategoria, Publicar_Web, ";
sql = sql + "Ultimo_Factor, Ultimo_Precio , Fecha_precio, dbo.tbl_Items.Sigla, dbo.tbl_Items.Precio, dbo.tbl_Items.ID_Item, dbo.tbl_Items.Codigo, dbo.tbl_Items.Descripcion, ";
sql = sql + "dbo.tbl_Categorias.Nombre AS Categoria, ";
sql = sql + "dbo.tbl_Subcategorias.Nombre AS SubCategoria, dbo.tbl_Proveedores.Nombre_Fantasia AS Proveedor, dbo.tbl_Items.Unidad, dbo.tbl_Items.Activo, ";
sql = sql + "dbo.tbl_Items.Cod_Barra ";
sql = sql + "FROM dbo.tbl_Items with(nolock) LEFT OUTER JOIN ";
sql = sql + "dbo.tbl_Categorias ON dbo.tbl_Items.Id_Categoria = dbo.tbl_Categorias.ID_Categoria LEFT OUTER JOIN ";
sql = sql + "dbo.tbl_Subcategorias ON dbo.tbl_Items.Id_SubCategoria = dbo.tbl_Subcategorias.ID_SubCategoria LEFT OUTER JOIN ";
sql = sql + "dbo.tbl_Proveedores ON dbo.tbl_Items.Id_proveedor = dbo.tbl_Proveedores.ID_Proveedor LEFT OUTER JOIN ";
sql = sql + "dbo.tbl_Familias_Productos ON dbo.tbl_Familias_Productos.ID_Familia = dbo.tbl_categorias.Id_Familia ";
sql = sql + "WHERE dbo.tbl_Familias_Productos.ID_Familia = " + id_familia;
sql = sql + "AND dbo.tbl_Items.Id_Categoria = " + id_categoria;
sql = sql + "AND dbo.tbl_Items.Id_SubCategoria = " + id_subcategoria;
using (SqlConnection connection = new SqlConnection(Sserver))
{
try
{
connection.Open();
SqlDataAdapter reader = new SqlDataAdapter(sql, connection);
DataSet dr = new DataSet();
reader.Fill(dr, "tbl_Items");
LstItems.DataSource = dr;
LstItems.DataBind();
connection.Close();
connection.Dispose();
}
catch (Exception ex)
{
lbl_error.Text = ex.Message;
connection.Close();
connection.Dispose();
}
}
}
protected void Buscar_Click(object sender, EventArgs e)
{
string sql = "";
sql = "SELECT tbl_Items.id_item ID, tbl_Items.codigo Codigo, dbo.PadreHijo(id_item) ph, isnull(tbl_Items.sigla, '') Letra, tbl_items.descripcion, tbl_items.Unidad, ";
sql = sql + "isnull(tbl_Familias_Productos.ID_Familia,0) ID_Familia, isnull(tbl_Categorias.ID_Categoria,0) ID_Categoria, isnull(tbl_Subcategorias.ID_SubCategoria,0) ID_SubCategoria, ";
sql = sql + " isnull((select 1 from tbl_items_web where Id_Item = dbo.tbl_Items.ID_Item),0) item_web ";
/* sql = "SELECT tbl_Items.id_item, tbl_Items.codigo,dbo.PadreHijo(id_item) ph, dbo.tbl_Items.Id_Linea_Venta, dbo.tbl_Items.Id_Categoria, dbo.tbl_Items.Id_SubCategoria, Publicar_Web, Ultimo_Factor, Ultimo_Precio , Fecha_precio, dbo.tbl_Items.Sigla, dbo.tbl_Items.Precio, dbo.tbl_Items.ID_Item, dbo.tbl_Items.Codigo, dbo.tbl_Items.Descripcion, dbo.tbl_Categorias.Nombre AS Categoria, ";
* sql = sql + " dbo.tbl_Subcategorias.Nombre AS SubCategoria, dbo.tbl_Proveedores.Nombre_Fantasia AS Proveedor, dbo.tbl_Items.Unidad, dbo.tbl_Items.Activo, ";
* sql = sql + " dbo.tbl_Items.Cod_Barra";*/
sql = sql + " FROM dbo.tbl_Items LEFT OUTER JOIN";
sql = sql + " dbo.tbl_Categorias ON dbo.tbl_Items.Id_Categoria = dbo.tbl_Categorias.ID_Categoria LEFT OUTER JOIN";
sql = sql + " dbo.tbl_Familias_Productos ON tbl_Familias_Productos.ID_Familia = dbo.tbl_Categorias.Id_Familia LEFT OUTER JOIN ";
sql = sql + " dbo.tbl_Subcategorias ON dbo.tbl_Items.Id_SubCategoria = dbo.tbl_Subcategorias.ID_SubCategoria LEFT OUTER JOIN";
sql = sql + " dbo.tbl_Proveedores ON dbo.tbl_Items.Id_proveedor = dbo.tbl_Proveedores.ID_Proveedor";
sql = sql + " where tbl_Items.Activo = 1 ";
if (Txt_Codigo.Text != "")
{
sql = sql + " and tbl_items_web.codigo like '" + Txt_Codigo.Text + "%'";
}
if (Txt_CodigoProv.Text != "")
{
sql = sql + "and tbl_items_web.Codigo_prov like '" + Txt_CodigoProv.Text + "%'";
}
if (Lst_Cat.SelectedItem.Value.ToString() != "0")
{
sql = sql + "and tbl_items.Id_Categoria = " + Lst_Cat.SelectedItem.Value.ToString();
}
if (Lst_SubCat.SelectedItem.Value.ToString() != "0")
{
sql = sql + "and tbl_items.Id_Subcategoria = " + Lst_SubCat.SelectedItem.Value.ToString();
}
if (Lst_Letra.SelectedItem.Value.ToString() != "0")
{
sql = sql + "and tbl_items.Sigla = '" + Lst_Letra.SelectedItem.Value.ToString() + "'";
}
if (Lst_LV.SelectedItem.Value.ToString() != "0")
{
sql = sql + "and tbl_items.Id_Linea_Venta = '" + Lst_LV.SelectedItem.Value.ToString() + "'";
}
if (Lst_division.SelectedItem.Value.ToString() != "0")
{
sql = sql + "and tbl_Familias_Productos.Id_Familia = '" + Lst_division.SelectedItem.Value.ToString() + "'";
}
if (Lst_Prov.SelectedItem.Value.ToString() != "0")
{
sql = sql + "and tbl_items.Id_proveedor = " + Lst_Prov.SelectedItem.Value.ToString();
}
if (Chk_sin_cat.Checked)
{
sql = sql + "and isnull(tbl_items.Id_Categoria,0) = 0 ";
}
if (Chk_publicados.Checked)
{
sql = sql + " and tbl_Items.Publicar_Web = 1";
}
if (Chk_sin_cat.Checked)
{
sql = sql + " (tbl_Items.ID_Categoria = 0 or tbl_Items.ID_Categoria IS Null)";
}
using (SqlConnection connection = new SqlConnection(Sserver))
{
try
{
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
SqlDataAdapter reader = new SqlDataAdapter(sql, connection);
DataSet dr = new DataSet();
reader.Fill(dr, "tbl_clientes");
LstItems.DataSource = dr;
LstItems.DataBind();
//SqlCommand command = new SqlCommand(sql, connection);
//SqlDataReader reader = command.ExecuteReader();
//DataSet ds = new DataSet();
//DataTable table = new DataTable("items");
//table.Columns.Add(new DataColumn("Id", typeof(int)));
//table.Columns.Add(new DataColumn("Código", typeof(string)));
//table.Columns.Add(new DataColumn("PH", typeof(string)));
//table.Columns.Add(new DataColumn("Sigla", typeof(string)));
//table.Columns.Add(new DataColumn("Descripción", typeof(string)));
//table.Columns.Add(new DataColumn("Unidad", typeof(string)));
//table.Columns.Add(new DataColumn("ID_Familia", typeof(int)));
//table.Columns.Add(new DataColumn("ID_Categoria", typeof(int)));
//table.Columns.Add(new DataColumn("ID_SubCategoria", typeof(int)));
//table.Columns.Add(new DataColumn("item_web", typeof(int)));
//int v_id = 0;
//string v_codigo = "";
//string v_ph = "";
//string v_sigla = "";
//string v_descripcion = "";
//string v_unidad = "";
//int v_id_fam = 0;
//int v_id_cat = 0;
//int v_id_subcat = 0;
//int v_web = 0;
//while (reader.Read())
//{
// v_id = reader.GetInt32(0);
// v_codigo = reader.GetString(1);
// v_ph = reader.GetString(2);
// v_sigla = reader.GetString(3);
// v_descripcion = reader.GetString(4);
// v_unidad = reader.GetString(5);
// v_id_fam = reader.GetInt32(6);
// v_id_cat = reader.GetInt32(7);
// v_id_subcat = reader.GetInt32(8);
// v_web = reader.GetInt32(9);
// table.Rows.Add(v_id,
// v_codigo,
// v_ph,
// v_sigla,
// v_descripcion,
// v_unidad,
// v_id_fam,
// v_id_cat,
// v_id_subcat,
// v_web);
//}
// LstItems.DataSource = table;
//LstItems.DataBind();
connection.Close();
connection.Dispose();
}
catch (Exception ex)
{
lbl_error.Text = ex.Message;
connection.Close();
connection.Dispose();
}
}
}