//Obtener todos los productos
public static List <Product> GetListComplete()
{
try
{
string sql = $"SELECT * FROM PRODUCT";
DataTable dt = ConnectionDB.ExecuteQuery(sql);
List <Product> lista = new List <Product>();
foreach (DataRow fila in dt.Rows)
{
Product u = new Product();
u.id = Convert.ToInt32(fila[0]);
u.name = fila[2].ToString();
u.idBusiness = Convert.ToInt32(fila[1]);
lista.Add(u);
}
return(lista);
}
catch (Exception)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todos los negocios
public static List <Business> GetList()
{
try
{
string sql = "SELECT * FROM BUSINESS";
DataTable dt = ConnectionDB.ExecuteQuery(sql);
List <Business> lista = new List <Business>();
foreach (DataRow fila in dt.Rows)
{
Business u = new Business();
u.id = Convert.ToInt32(fila[0]);
u.name = fila[1].ToString();
u.description = fila[2].ToString();
lista.Add(u);
}
return(lista);
}
catch (Exception)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todas las ordenes de un cliente (segun id)
public static List <Order> GetList()
{
try
{
string sql = "SELECT ao.idOrder, ao.createDate, pr.name, au.fullname, " +
"ad.address FROM APPORDER ao, ADDRESS ad, PRODUCT pr, APPUSER au " +
"WHERE ao.idProduct = pr.idProduct AND ao.idAddress = ad.idAddress " +
$"AND ad.idUser = au.idUser AND au.idUser = {CurrentUser.User.id};";
DataTable dt = ConnectionDB.ExecuteQuery(sql);
List <Order> lista = new List <Order>();
foreach (DataRow fila in dt.Rows)
{
Order u = new Order();
u.id = Convert.ToInt32(fila[0]);
u.date = fila[1].ToString();
u.product = fila[2].ToString();
u.address = fila[4].ToString();
lista.Add(u);
}
return(lista);
}
catch (Exception)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todas las direcciones de un usuario (segun id)
public static List <Addres> GetAddressList()
{
try
{
string sql = $"SELECT ad.idAddress, ad.address FROM ADDRESS ad WHERE idUser = {CurrentUser.User.id}";
DataTable dt = ConnectionDB.ExecuteQuery(sql);
List <Addres> lista = new List <Addres>();
foreach (DataRow fila in dt.Rows)
{
Addres u = new Addres();
u.id = Convert.ToInt32(fila[0]);
u.address = fila[1].ToString();
u.idUser = CurrentUser.User.id;
lista.Add(u);
}
return(lista);
}
catch (Exception)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todos los usuarios
public static List <User> GetList()
{
try
{
string sql = "select * from APPUSER";
DataTable dt = ConnectionDB.ExecuteQuery(sql);
List <User> lista = new List <User>();
foreach (DataRow fila in dt.Rows)
{
User u = new User();
u.id = Convert.ToInt32(fila[0]);
u.fullname = fila[1].ToString();
u.username = fila[2].ToString();
u.password = fila[3].ToString();
u.admin = Convert.ToBoolean(fila[4].ToString());
lista.Add(u);
}
return(lista);
}
catch (Exception)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todos los negocios
public static DataTable SelectFromBusiness()
{
try
{
var dt = ConnectionDB.ExecuteQuery("SELECT * FROM BUSINESS;");
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todas las direcciones de un usuario (segun id)
public static DataTable SelectAddressFromUsuario()
{
try
{
var dt = ConnectionDB.ExecuteQuery($"SELECT ad.idAddress, ad.address FROM ADDRESS ad WHERE idUser = {CurrentUser.User.id}");
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todos los productos de todos los negocio (unico query que cambie o es "propio")
public static DataTable SelectFromProduct()
{
try
{
var dt = ConnectionDB.ExecuteQuery($"SELECT p.idProduct, p.name, b.name AS \"negocio\" FROM PRODUCT p, " +
$"BUSINESS b WHERE p.idBusiness = b.idBusiness");
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
//Obtener todas las ordenes
public static DataTable SelectFromOrder()
{
try
{
var dt = ConnectionDB.ExecuteQuery("SELECT ao.idOrder, ao.createDate, pr.name, au.fullname, " +
"ad.address FROM APPORDER ao, ADDRESS ad, PRODUCT pr, APPUSER au WHERE " +
"ao.idProduct = pr.idProduct AND ao.idAddress = ad.idAddress AND ad.idUser = au.idUser;");
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("Error al cargar datos");
return(null);
}
}
public static AppUser GetUser(String nombre, String contra)
{
string sql = String.Format("SELECT * FROM appuser WHERE username = '******' AND password = '******';", nombre, contra);
DataTable dt = ConnectionDB.ExecuteQuery(sql);
AppUser u = new AppUser();
foreach (DataRow fila in dt.Rows)
{
u.idUser = Convert.ToInt32(fila[0].ToString());
u.fullName = fila[1].ToString();
u.userName = fila[2].ToString();
u.password = fila[3].ToString();
u.userType = Convert.ToBoolean(fila[4].ToString());
}
return(u);
}
public static List <Pedidos> getEstadisticas()
{
string sql = "SELECT b.name AS \"Negocio\", sum(cp.cant) AS \"Total pedidos\" FROM BUSINESS b, " +
"(SELECT p.idBusiness, p.name, count(ap.idProduct) AS \"cant\" " +
"FROM PRODUCT p, APPORDER ap WHERE p.idProduct = ap.idProduct " +
"GROUP BY p.idProduct ORDER BY p.name ASC) AS cp " +
"WHERE b.idBusiness = cp.idBusiness GROUP BY b.idBusiness;";
DataTable dt = ConnectionDB.ExecuteQuery(sql);
List <Pedidos> lista = new List <Pedidos>();
foreach (DataRow fila in dt.Rows)
{
Pedidos f = new Pedidos();
f.cantidad = Convert.ToInt32(fila[1].ToString());
f.negocio = fila[0].ToString();
lista.Add(f);
}
return(lista);
}
