public List <Product> GetProductsWithFilter(string description, string category, string min, string max, bool active)
{
List <Product> products = new List <Product>();
string query = $"SELECT * FROM products WHERE description LIKE '{description}%' AND price >= {min} AND price <= {max}";
if (category != "Todas")
{
query += $" AND category = '{category}';";
}
if (active)
{
query += $" AND active = 1";
}
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
products.Add(new Product(rs.GetString("id"),
rs.GetString("description"),
rs.GetString("category"),
Convert.ToDecimal(rs.GetDouble("price")),
rs.GetString("image"),
Convert.ToBoolean(rs.GetInt32("active"))));
}
}
return(products);
}
public double avgcalificacion(int id)
{
double calificacion = -1;
using (SQLiteRecordSet rs = ExecuteQuery($"SELECT AVG(puntaje) FROM calificaciones WHERE pelicula_codigo" +
$" = {id}"))
{
while (rs.NextRecord())
{
if (!rs.IsNull("AVG(puntaje)"))
{
calificacion = rs.GetDouble("AVG(puntaje)");
}
}
}
return(calificacion);
}
public List <Product> GetAllProducts()
{
List <Product> products = new List <Product>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT * FROM products"))
{
while (rs.NextRecord())
{
products.Add(new Product(rs.GetString("id"),
rs.GetString("description"),
rs.GetString("category"),
Convert.ToDecimal(rs.GetDouble("price")),
rs.GetString("image"),
Convert.ToBoolean(rs.GetInt32("active"))));
}
}
return(products);
}
public List <Product> GetAllCart(int user_id)
{
List <Product> products = new List <Product>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT products.* FROM shoppingcarts sc INNER JOIN products " +
$"ON (products.id = sc.product_id) WHERE user_id = {user_id}"))
{
while (rs.NextRecord())
{
products.Add(new Product(rs.GetString("id"),
rs.GetString("description"),
rs.GetString("category"),
Convert.ToDecimal(rs.GetDouble("price")),
rs.GetString("image"),
Convert.ToBoolean(rs.GetInt32("active"))));
}
}
return(products);
}
public List <Producto> GetProductos()
{
List <Producto> productos = new List <Producto>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT * FROM productos"))
{
while (rs.NextRecord())
{
productos.Add(new Producto(rs.GetInt32("id"),
rs.GetString("nombre"),
rs.GetString("descripcion"),
rs.GetDouble("precio"),
rs.GetInt32("cantidad"),
rs.GetInt32("categoria_id")));
}
}
return(productos);
}
public List <Product> GetProductsMaxFilter(string text)
{
List <Product> products = new List <Product>();
string query = $"SELECT * FROM products WHERE id LIKE '{text}%' OR description LIKE '{text}%' " +
$"OR price = '{text}' OR category = '{text}';";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
products.Add(new Product(rs.GetString("id"),
rs.GetString("description"),
rs.GetString("category"),
Convert.ToDecimal(rs.GetDouble("price")),
rs.GetString("image"),
Convert.ToBoolean(rs.GetInt32("active"))));
}
}
return(products);
}
public List <Tarjeta> GetTarjetas()
{
List <Tarjeta> tarjetas = new List <Tarjeta>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT " +
"p.codigo, p.descripcion, p.precio, p.cantidad, t.Tipo, m.marca " +
"FROM Productos p " +
"INNER JOIN Tipo_Producto t ON (p.tipo_id = t.id) " +
"INNER JOIN Marca m ON (p.marca_id = m.id);"))
{
while (rs.NextRecord())
{
tarjetas.Add(new Tarjeta(rs.GetString("codigo"),
rs.GetString("descripcion"),
Convert.ToDecimal(rs.GetDouble("precio")),
rs.GetInt32("cantidad"),
rs.GetString("Tipo"),
rs.GetString("marca")));
}
}
return(tarjetas);
}
public Tarjeta GetTarjeta(string id)
{
Tarjeta tarjeta = null;
using (SQLiteRecordSet rs = ExecuteQuery("SELECT " +
"p.codigo, p.descripcion, p.precio, p.cantidad, t.Tipo, m.marca " +
"FROM Productos p " +
"INNER JOIN Tipo_Producto t ON (p.tipo_id = t.id) " +
"INNER JOIN Marca m ON (p.marca_id = m.id) " +
$"WHERE p.codigo = {id};"))
{
while (rs.NextRecord())
{
tarjeta = new Tarjeta(Convert.ToString(rs.GetString("codigo")),
rs.GetString("descripcion"),
Convert.ToDecimal(rs.GetDouble("precio")),
rs.GetInt32("cantidad"),
rs.GetString("tipo"),
rs.GetString("marca"));
}
}
return(tarjeta);
}
