public static List <AppOrder> getListaUser(int userId)
{
string sql = String.Format(
$"select ao.\"idOrder\", ao.\"createDate\", pr.\"name\", au.\"fullname\", ad.\"address\"" +
$" from \"AppOrder\" ao, \"Product\" pr, \"AppUser\" au, \"Address\" ad" +
$" where ao.\"idProduct\"= pr.\"idProduct\" and ao.\"idAddress\" = ad.\"idAddress\"" +
" and ad.\"idUser\"= au.\"idUser\"and au.\"idUser\"={0};",
userId);
DataTable dt = ConnectionBD.ExecuteQuery(sql);
List <AppOrder> lista = new List <AppOrder>();
foreach (DataRow row in dt.Rows)
{
AppOrder o = new AppOrder();
o.idOrder = Convert.ToInt32(row[0].ToString());
o.createDate = DateTime.Parse(row[1].ToString());
o.idAddress = Convert.ToInt32(row[2].ToString());
o.idProduct = Convert.ToInt32(row[3].ToString());
lista.Add(o);
}
return(lista);
}
public static void removeProduct(int idProduct)
{
string act = String.Format(
"delete from \"Product\" where \"idProduct\"={0};",
idProduct);
ConnectionBD.ExecuteNonQuery(act);
}
public static void removeOrder(int idOrder)
{
string act = String.Format(
"delete from \"AppOrder\" where \"idOrder\"={0};",
idOrder);
ConnectionBD.ExecuteNonQuery(act);
}
public static void actualizarContra(int idUser, string newPassword)
{
string act = String.Format(
"update \"AppUser\" set password='******' where \"idUser\"={1};",
newPassword, idUser);
ConnectionBD.ExecuteNonQuery(act);
}
public static void removeBusiness(int idBusiness)
{
string act = String.Format(
"delete from \"Business\" where \"idBusiness\"={0};",
idBusiness);
ConnectionBD.ExecuteNonQuery(act);
}
public static void addProduct(Product p)
{
string act = String.Format(
"insert into \"Product\"" +
"(\"idBusiness\", \"name\")" +
"values ({0}, '{1}');",
p.idBusiness, p.name);
ConnectionBD.ExecuteNonQuery(act);
}
public static void addOrder(AppOrder o)
{
string act = String.Format(
"insert into \"AppOrder\"" +
"(\"createDate\", \"idProduct\", \"idAddress\")" +
"values ('{0}', {1}, {2});",
o.createDate, o.idProduct, o.idAddress);
ConnectionBD.ExecuteNonQuery(act);
}
public static void addBusiness(Business b)
{
string act = String.Format(
"insert into \"Business\"" +
"(\"name\", \"description\")" +
"values ('{0}', '{1}');",
b.name, b.description);
ConnectionBD.ExecuteNonQuery(act);
}
public static void addAddress(Address a)
{
string act = String.Format(
"insert into \"Address\"" +
"(\"idUser\", \"address\")" +
"values ({0}, '{1}');",
a.idUser, a.address);
ConnectionBD.ExecuteNonQuery(act);
}
public static void addUser(AppUser u)
{
u.password = Encriptador.CrearMD5(u.username);
string act = String.Format(
"insert into \"AppUser\"" +
"(\"admin\", \"fullname\", \"username\", \"password\")" +
"values ('{0}', '{1}', '{2}', '{3}');",
u.admin, u.fullname, u.username, u.password);
ConnectionBD.ExecuteNonQuery(act);
}
public static List <AppOrder> getLista()
{
string query = $"select ao.\"idOrder\", ao.\"createDate\", pr.\"name\", au.\"fullname\", ad.\"address\"" +
$" from \"AppOrder\" ao, \"Product\" pr, \"AppUser\" au, \"Address\" ad" +
$" where ao.\"idProduct\"= pr.\"idProduct\" and ao.\"idAddress\" = ad.\"idAddress\"" +
$" and ad.\"idUser\"= au.\"idUser\"";
DataTable dt = ConnectionBD.ExecuteQuery(query);
List <AppOrder> lista = new List <AppOrder>();
foreach (DataRow row in dt.Rows)
{
AppOrder o = new AppOrder();
lista.Add(o);
}
return(lista);
}
public static List <Product> getLista()
{
string query = "select * from \"Product\" order by \"idBusiness\", \"idProduct\"";
DataTable dt = ConnectionBD.ExecuteQuery(query);
List <Product> lista = new List <Product>();
foreach (DataRow row in dt.Rows)
{
Product p = new Product();
p.idProduct = Convert.ToInt32(row[0].ToString());
p.idBusiness = Convert.ToInt32(row[1].ToString());
p.name = row[2].ToString();
lista.Add(p);
}
return(lista);
}
public static List <Business> getLista()
{
string query = "Select * from \"Business\"";
DataTable dt = ConnectionBD.ExecuteQuery(query);
List <Business> lista = new List <Business>();
foreach (DataRow row in dt.Rows)
{
Business b = new Business();
b.idBusiness = Convert.ToInt32(row[0].ToString());
b.name = row[1].ToString();
b.description = row[2].ToString();
lista.Add(b);
}
return(lista);
}
public static List <AppUser> getLista()
{
string query = "select * from \"AppUser\"";
DataTable dt = ConnectionBD.ExecuteQuery(query);
List <AppUser> lista = new List <AppUser>();
foreach (DataRow row in dt.Rows)
{
AppUser u = new AppUser();
u.id_User = Convert.ToInt32(row[0].ToString());
u.fullname = row[1].ToString();
u.username = row[2].ToString();
u.password = row[3].ToString();
u.admin = Convert.ToBoolean(row[4].ToString());
lista.Add(u);
}
return(lista);
}
public static List <Address> getLista(int idUser)
{
string query = String.Format("SELECT * FROM \"Address\" INNER JOIN \"AppUser\" ON \"AppUser\".\"idUser\" = \"Address\".\"idUser\" where \"AppUser\".\"idUser\" = '{0}';",
idUser);
DataTable dt = ConnectionBD.ExecuteQuery(query);
List <Address> lista = new List <Address>();
foreach (DataRow row in dt.Rows)
{
Address a = new Address();
a.idAddress = Convert.ToInt32(row[0].ToString());
a.idUser = Convert.ToInt32(row[1].ToString());
a.address = row[2].ToString();
lista.Add(a);
}
return(lista);
}