public List <Pelicula> GetPeliculas()
{
List <Pelicula> Peliculas = new List <Pelicula>();
using (SQLiteRecordSet rs2 = ExecuteQuery($"SELECT p.codigo AS codigo, p.titulo AS titulo, p.sinopsis AS sinopsis, " +
$"p.genero AS genero, p.clasificacion AS clasificacion, " +
$"p.duracion AS duracion, p.imagen AS imagen, p.year AS year, " +
$"m.descripcion AS membresia " +
$"FROM membresias m " +
$"INNER JOIN( " +
$" SELECT pg.codigo AS codigo, pg.titulo AS titulo, pg.sinopsis AS sinopsis, " +
$" g.descripcion AS genero, pg.clasificacion AS clasificacion, " +
$" pg.duracion AS duracion, pg.imagen AS imagen, pg.year AS year, " +
$" pg.membresia_id AS membresia " +
$" FROM peliculas pg " +
$" INNER JOIN generos g ON (g.id = pg.genero_id) " +
$" ) p ON(p.membresia = m.id) "))
{
while (rs2.NextRecord())
{
Peliculas.Add(new Pelicula(rs2.GetInt32("codigo"),
rs2.GetString("titulo"),
rs2.GetString("genero"),
rs2.GetInt32("year"),
rs2.GetString("sinopsis"),
rs2.GetString("clasificacion"),
rs2.GetInt32("duracion"),
rs2.GetString("imagen"),
rs2.GetString("membresia")));
}
}
return(Peliculas);
}
public List <Usuario> GetUsers()
{
List <Usuario> usuarios = new List <Usuario>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT " +
"u.id, u.fname, u.lname, u.correo, u.contraseña, " +
"d.direccion, d.codigopostal, u.estado, u.active " +
"FROM Usuario u " +
"INNER JOIN Direcciones d ON (u.id = d.id);"))
{
while (rs.NextRecord())
{
usuarios.Add(new Usuario(rs.GetString("id"),
rs.GetString("fname"),
rs.GetString("lname"),
rs.GetString("correo"),
rs.GetString("contraseña"),
rs.GetString("direccion"),
rs.GetInt32("codigopostal"),
Convert.ToBoolean(rs.GetInt32("estado")),
Convert.ToBoolean(rs.GetInt32("active"))));
}
}
return(usuarios);
}
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 List <Pelicula> calificacionfiltro(int puntaje)
{
List <Pelicula> Peliculas = new List <Pelicula>();
using (SQLiteRecordSet rs2 = ExecuteQuery($"SELECT *, AVG(puntaje) " +
$"FROM calificaciones c " +
$"INNER JOIN peliculas p ON(c.pelicula_codigo = p.codigo) " +
$"GROUP BY p.codigo, c.pelicula_codigo" +
$"HAVING puntaje >= {puntaje}"))
{
while (rs2.NextRecord())
{
Peliculas.Add(new Pelicula(rs2.GetInt32("codigo"),
rs2.GetString("titulo"),
rs2.GetString("genero"),
rs2.GetInt32("year"),
rs2.GetString("sinopsis"),
rs2.GetString("clasificacion"),
rs2.GetInt32("duracion"),
rs2.GetString("imagen"),
rs2.GetString("membresia")));
}
}
return(Peliculas);
}
public List <int> Getyears()
{
List <int> years = new List <int>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT year FROM peliculas"))
{
while (rs.NextRecord())
{
years.Add(rs.GetInt32("year"));
}
}
return(years);
}
public List <string> GetGeneros()
{
List <String> generos = new List <string>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT descripcion FROM generos"))
{
while (rs.NextRecord())
{
generos.Add(rs.GetString("descripcion"));
}
}
return(generos);
}
public List <string> GetCategories()
{
List <string> categories = new List <string>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT category FROM products GROUP BY category"))
{
while (rs.NextRecord())
{
categories.Add(rs.GetString("category"));
}
}
return(categories);
}
public int GetMaxId()
{
int id = 0;
using (SQLiteRecordSet rs = ExecuteQuery("SELECT id FROM users ORDER BY id DESC LIMIT 1"))
{
while (rs.NextRecord())
{
id = rs.GetInt32("id");
}
}
return(id);
}
public List <string> GetClasificaciones()
{
List <String> clasificaciones = new List <string>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT clasificacion FROM peliculas"))
{
while (rs.NextRecord())
{
clasificaciones.Add(rs.GetString("clasificacion"));
}
}
return(clasificaciones);
}
public int GetQuantityOfProduct(User user, Product product)
{
int quantity = 0;
using (SQLiteRecordSet rs = ExecuteQuery($"SELECT quantity FROM shoppingcarts WHERE " +
$"user_id = {user.id} AND product_id= '{product.id}'"))
{
while (rs.NextRecord())
{
quantity = rs.GetInt32("quantity");
}
}
return(quantity);
}
public int GetSalesByRecord()
{
string query = $"SELECT record_id FROM sales_record WHERE record_id =(SELECT MAX(record_id) From sales_record)";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
return(rs.GetInt32("record_id"));
}
return(0);
}
}
public int GetPrice()
{
string query = $"SELECT price FROM sales_product ";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
return(rs.GetInt32("price"));
}
return(0);
}
}
public List <Carrito> GetComprasById(string id)
{
List <Carrito> carritos = new List <Carrito>();
using (SQLiteRecordSet rs = ExecuteQuery($"SELECT * FROM Carrito WHERE id_user = {id};"))
{
while (rs.NextRecord())
{
carritos.Add(new Carrito(rs.GetString("id_user"),
rs.GetString("id_producto"),
rs.GetInt32("cantidad")));
}
}
return(carritos);
}
public int GetSalesTotal(int sale_id)
{
string query = $"SELECT SUM(sp.qty_sold* (p.price/ 100))AS ganancia FROM sales_record sr INNER JOIN sales_product sp ON(sr.record_id = sp.sale_id) " +
$"INNER JOIN products p ON(sp.product = p.product_id) WHERE sale_id = {sale_id}";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
return(rs.GetInt32("sale_id"));
}
return(0);
}
}
public List <Payment> GetPaymentType()
{
List <Payment> payment = new List <Payment>();
string query = $"SELECT * FROM payment ";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
int transactionID = rs.GetInt32("transaction_id");
string transactionType = rs.GetString("type_transaction");
payment.Add(new Payment(transactionID, transactionType));
}
}
return(payment);
}
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);
}
//Data base requests
public List <User> GetAllUsers()
{
List <User> users = new List <User>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT * FROM users"))
{
while (rs.NextRecord())
{
users.Add(new User(rs.GetInt32("id"),
rs.GetString("firstname"),
rs.GetString("lastname"),
rs.GetString("username"),
rs.GetString("password"),
rs.GetString("image")));
}
}
return(users);
}
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 <Usuario> GetUsuarios()
{
List <Usuario> usuarios = new List <Usuario>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT * FROM usuarios"))
{
while (rs.NextRecord())
{
usuarios.Add(new Usuario(rs.GetInt32("id"),
rs.GetString("username"),
rs.GetString("password"),
rs.GetString("name"),
rs.GetString("lastname"),
rs.GetInt32("membresia_id")));
}
}
return(usuarios);
}
public List <Usuario> GetUsername()
{
List <Usuario> aux = new List <Usuario>();
using (SQLiteRecordSet rs = ExecuteQuery($"SELECT user_id, role_id, login_name, password FROM users; "))
{
while (rs.NextRecord())
{
int userid = rs.GetInt32("user_id");
int roleid = rs.GetInt32("role_id");
string username = rs.GetString("login_name");
int password = rs.GetInt32("password");
aux.Add(new Usuario(userid, roleid, username, password));
}
}
return(aux);
}
public List <Usuario> GetUsuarios()
{
List <Usuario> usuarios = new List <Usuario>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT * FROM usuarios"))
{
while (rs.NextRecord())
{
usuarios.Add(new Usuario(rs.GetInt32("id"),
rs.GetString("nombre"),
rs.GetString("apellido"),
rs.GetInt32("edad"),
rs.GetInt32("administracion"),
rs.GetInt32("estado")));
}
}
return(usuarios);
}
public List <CreditCard> GetCreditCards()
{
List <CreditCard> tarjetas = new List <CreditCard>();
using (SQLiteRecordSet rs = ExecuteQuery("SELECT * FROM Tarjetas"))
{
while (rs.NextRecord())
{
tarjetas.Add(new CreditCard(rs.GetString("id"),
rs.GetString("usuario_id"),
rs.GetString("numero_tarjeta"),
rs.GetInt32("mes_exp"),
rs.GetInt32("año_exp"),
rs.GetInt32("cvv")));
}
}
return(tarjetas);
}
public User GetUserLogged()
{
User user = null;
using (SQLiteRecordSet rs = ExecuteQuery("SELECT id, firstname, lastname, username, " +
"password, image FROM user_logged ul INNER JOIN users u ON ul.user_id = u.id"))
{
while (rs.NextRecord())
{
user = new User(rs.GetInt32("id"),
rs.GetString("firstname"),
rs.GetString("lastname"),
rs.GetString("username"),
rs.GetString("password"),
rs.GetString("image"));
}
}
return(user);
}
public List <SalesFecha> GetProductLow()
{
List <SalesFecha> aux = new List <SalesFecha>();
using (SQLiteRecordSet rs = ExecuteQuery($"SELECT p.description, SUM(sp.qty_sold ) AS vendido, SUM(sp.qty_sold* (p.price/100))AS ganancia FROM sales_record sr INNER JOIN sales_product sp ON (sr.record_id = sp.sale_id )" +
"INNER JOIN products p ON(sp.product = p.product_id) GROUP BY p.description ORDER BY MAX(sp.qty_sold)ASC LIMIT 1; "))
{
while (rs.NextRecord())
{
string producto = rs.GetString("description");
int quantity = rs.GetInt32("vendido");
decimal price = rs.GetInt32("ganancia");
aux.Add(new SalesFecha(producto, quantity, price));
}
}
return(aux);
}
public List <Categories> GetCategories()
{
List <Categories> categories = new List <Categories>();
string query = $"SELECT * FROM categories ORDER BY id";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
int categoryID = rs.GetInt32("id");
string category = rs.GetString("category");
categories.Add(new Categories(categoryID, category));
}
}
return(categories);
}
public int GetRole(int pass)
{
int roleid = 3;
string query = $"SELECT role_id FROM users WHERE password = '******'; ";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
int role = rs.GetInt32("role_id");
return(role);
}
}
return(roleid);
}
public int GetPassword(int Usuario)
{
int password = 0;
string query = $"SELECT password FROM users WHERE user_id = '{Usuario}'; ";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
int passw = rs.GetInt32("password");
return(passw);
}
}
return(password);
}
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 Products GetProductsByName(string name)
{
string query = $"SELECT * FROM products WHERE description = '{name}'";
using (SQLiteRecordSet rs = ExecuteQuery(query))
{
while (rs.NextRecord())
{
int productID = rs.GetInt32("product_id");
int categoryID = rs.GetInt32("category_id");
string descripcion = rs.GetString("description");
int price = rs.GetInt32("price");
int stock = rs.GetInt32("qty");
return(new Products(productID, categoryID, descripcion, price, stock));
}
}
return(null);
}
