//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); }