private void SearchInfo()
{
try
{
if (StartDate.SelectedDate == null && FinishDate.SelectedDate == null && (TxtCat.SelectedIndex == -1 || TxtCat.SelectedItem.ToString() == "Sin Seleccion"))
{
Update_DataGrid();
return;
}
if (StartDate.SelectedDate != null && FinishDate.SelectedDate == null && (TxtCat.SelectedIndex == -1 || TxtCat.SelectedItem.ToString() == "Sin Seleccion"))
{
TablaProductos.ItemsSource = SqlQueries.FilterReport("SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Records].[time] >= @Parameter", StartDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd")).DefaultView;
/*TablaProductos.AutoGenerateColumns = false;
* TablaProductos.CanUserAddRows = false;*/
return;
}
if (StartDate.SelectedDate == null && FinishDate.SelectedDate != null && (TxtCat.SelectedIndex == -1 || TxtCat.SelectedItem.ToString() == "Sin Seleccion"))
{
TablaProductos.ItemsSource = SqlQueries.FilterReport("SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Records].[time] <= @Parameter", FinishDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd")).DefaultView;
/*TablaProductos.AutoGenerateColumns = false;
* TablaProductos.CanUserAddRows = false;*/
return;
}
if (StartDate.SelectedDate == null && FinishDate.SelectedDate == null && (TxtCat.SelectedIndex != -1 || TxtCat.SelectedItem.ToString() != "Sin Seleccion"))
{
TablaProductos.ItemsSource = SqlQueries.FilterReport("SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Categories].[name] = @Parameter", TxtCat.SelectedValue.ToString().Trim()).DefaultView;
/*TablaProductos.AutoGenerateColumns = false;
* TablaProductos.CanUserAddRows = false;*/
return;
}
if (StartDate.SelectedDate != null && FinishDate.SelectedDate != null && (TxtCat.SelectedIndex == -1 || TxtCat.SelectedItem.ToString() == "Sin Seleccion"))
{
TablaProductos.ItemsSource = SqlQueries.FilterReport("SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Records].[time] >= @ParameterA AND [FlemidraDB].[dbo].[Records].[time] <= @ParameterB", StartDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd"), FinishDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd")).DefaultView;
/*TablaProductos.AutoGenerateColumns = false;
* TablaProductos.CanUserAddRows = false;*/
return;
}
if (StartDate.SelectedDate != null && FinishDate.SelectedDate == null && (TxtCat.SelectedIndex != -1 || TxtCat.SelectedItem.ToString() != "Sin Seleccion"))
{
TablaProductos.ItemsSource = SqlQueries.FilterReport("SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Records].[time] >= @ParameterA AND [FlemidraDB].[dbo].[Categories].[name] = @ParameterB", StartDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd"), TxtCat.SelectedValue.ToString().Trim()).DefaultView;
/*TablaProductos.AutoGenerateColumns = false;
* TablaProductos.CanUserAddRows = false;*/
return;
}
if (StartDate.SelectedDate == null && FinishDate.SelectedDate != null && (TxtCat.SelectedIndex != -1 || TxtCat.SelectedItem.ToString() != "Sin Seleccion"))
{
TablaProductos.ItemsSource = SqlQueries.FilterReport("SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Records].[time] <= @ParameterA AND [FlemidraDB].[dbo].[Categories].[name] = @ParameterB", FinishDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd"), TxtCat.SelectedValue.ToString().Trim()).DefaultView;
/*TablaProductos.AutoGenerateColumns = false;
* TablaProductos.CanUserAddRows = false;*/
return;
}
if (StartDate.SelectedDate != null && FinishDate.SelectedDate != null && (TxtCat.SelectedIndex != -1 || TxtCat.SelectedItem.ToString() != "Sin Seleccion"))
{
TablaProductos.ItemsSource = SqlQueries.FilterReport("SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Records].[time] >= @ParameterA AND [FlemidraDB].[dbo].[Records].[time] <= @ParameterB AND [FlemidraDB].[dbo].[Categories].[name] = @ParameterC", StartDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd"), FinishDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd"), TxtCat.SelectedValue.ToString().Trim()).DefaultView;
const string select = "SELECT [FlemidraDB].[dbo].[Records].[type] As 'Evento', [FlemidraDB].[dbo].[Products].[name] As 'Producto', [FlemidraDB].[dbo].[Categories].[name] As 'Categoria', [FlemidraDB].[dbo].[Records].[quantity] As 'Cantidad', CONVERT(date, [FlemidraDB].[dbo].[Records].[time]) As 'Fecha', [FlemidraDB].[dbo].[Users].[username] As 'Usuario' FROM [FlemidraDB].[dbo].[Records] INNER JOIN [FlemidraDB].[dbo].[Products] on [FlemidraDB].[dbo].[Records].[id_Product] = [FlemidraDB].[dbo].[Products].[id] INNER JOIN [FlemidraDB].[dbo].[Users] ON [FlemidraDB].[dbo].[Records].[id_Users] = [FlemidraDB].[dbo].[Users].[id] INNER JOIN [FlemidraDB].[dbo].[Categories] on [FlemidraDB].[dbo].[Products].[id_cat] = [FlemidraDB].[dbo].[Categories].[id] WHERE [FlemidraDB].[dbo].[Records].[time] >= @StartDate AND [FlemidraDB].[dbo].[Records].[time] <= @FinishDate AND [FlemidraDB].[dbo].[Categories].[name] = @catName";
var con = new SqlConnection(GlobalVariables.GetConString());
con.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.AddWithValue("@StartDate",
StartDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd"));
sqlCommand.Parameters.AddWithValue("@FinishDate",
FinishDate.SelectedDate.Value.Date.ToString("yyyy-MM-dd"));
sqlCommand.Parameters.AddWithValue("@catName",
TxtCat.SelectedValue.ToString().Trim());
sqlCommand.CommandText = select;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Connection = con;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
_dataTable = new DataTable();
sqlDataAdapter.Fill(_dataTable);
TablaProductos.ItemsSource = _dataTable.DefaultView;
TablaProductos.AutoGenerateColumns = false;
TablaProductos.CanUserAddRows = false;
con.Close();
}
}
catch (Exception en)
{
MessageBox.Show("Error al realizar operacion de filtrado. " + en);
}
}