public static DataTable totalPrice(int orderid)
{
SqlCommand cmd = new SqlCommand("SELECT sum(prod_price*quantity) from Product p inner join [OrderItem] ot on p.Prod_id=ot.Prod_id where Order_id=@orderid");
cmd.Parameters.AddWithValue("@orderid", orderid);
return(DBlayer.Sel(cmd));
}
public static DataTable Blocked()
{
SqlCommand cmd = new SqlCommand("select r.order_id,r.Packing_date,u.Cust_username from [Order] r,[Customer] u where u.Cust_id=r.cust_Id and r.state='Blocked'");
DataTable dt = DBlayer.Sel(cmd);
return(dt);
}
public static DataTable selectAll()
{
SqlCommand command = new SqlCommand("select Cust_fName,Cust_lName,Cust_mail,Cust_username,Cust_ssnimg,Cust_address,status,Cust_phone,Cust_zipCode from [Customer]");
DataTable dt = DBlayer.Sel(command);
return(dt);
}
public static DataTable getByCateg(string category)
{
SqlCommand cmd = new SqlCommand("select p.prod_id,p.Prod_img , p.Prod_name ,p.Prod_Desc,p.Prod_price,p.promotion ,c.Category_name from Product p , Category c where c.Category_name=@name and p.Category_id=c.Category_id and Prod_quantity!=0");
cmd.Parameters.AddWithValue("@name", category);
return(DBlayer.Sel(cmd));
}
public static DataTable getbyPrice(decimal price)
{
SqlCommand cmd = new SqlCommand("select p.prod_id, p.Prod_img , p.Prod_name ,p.Prod_Desc,p.Prod_price,p.promotion ,c.Category_name from Product p , Category c where p.Prod_price=@price and p.Category_id=c.Category_id and prod_quantity!=0");
cmd.Parameters.AddWithValue("@price", price);
return(DBlayer.Sel(cmd));
}
//rasha
public static DataTable getbyid(int id)
{
SqlCommand cmd = new SqlCommand("select Cust_fname,Cust_lname,Cust_username,Cust_mail,Cust_address,Cust_phone from Customer where Cust_id=@id");
cmd.Parameters.AddWithValue("@id", id);
return(DBlayer.Sel(cmd));
}
public static DataTable getbyimg(string path)
{
SqlCommand cmd = new SqlCommand("select * from product where prod_img=@img");
cmd.Parameters.AddWithValue("@img", path);
return(DBlayer.Sel(cmd));
}
public static DataTable getByid(int id)
{
SqlCommand cmd = new SqlCommand("select * from Product where prod_id=@id");
cmd.Parameters.AddWithValue("@id", id);
return(DBlayer.Sel(cmd));
}
public static DataTable selByUserID(int UserID)
{
SqlCommand comnd = new SqlCommand("select top(1)Order_id from[order] where Cust_id =@custid order by order_id desc");
comnd.Parameters.AddWithValue("@custid", UserID);
return(DBlayer.Sel(comnd));
}
public static DataTable getcart(int cus_id)
{
SqlCommand cmd = new SqlCommand("select p.prod_id as id ,prod_img as image, prod_name as productName, prod_price as price ,quantity as quantity from cart c, product p where customer_id=@c and c.prod_id=p.prod_id");
cmd.Parameters.AddWithValue("@c", cus_id);
return(DBlayer.Sel(cmd));
}
public static DataTable getProdpriceByname(int id)
{
SqlCommand cmd = new SqlCommand("select Prod_price,Prod_id from Product p where p.Prod_id=@id and prod_quantity!=0");
cmd.Parameters.AddWithValue("@id", id);
return(DBlayer.Sel(cmd));
}
public static DataTable pendingOrdecustomer()
{
SqlCommand cmd = new SqlCommand("SELECT Customer.Cust_fname + ' ' + Customer.Cust_lname as CustomerName, Customer.Cust_id, Customer.Cust_phone as phone, Customer.Cust_address as address, [Order].Order_id " +
"FROM Customer INNER JOIN" +
" [Order] ON Customer.Cust_id = [Order].Cust_id and[Order].state = 'pending'");
return(DBlayer.Sel(cmd));
}
//select by name
public static DataTable getByName(string name)
{
SqlCommand cmd = new SqlCommand("select * from Category where Category_name=@name");
cmd.Parameters.AddWithValue("@name", name);
return(DBlayer.Sel(cmd));
}
//select by id
public static DataTable getById(int id)
{
SqlCommand cmd = new SqlCommand("select Category_name from Category where Category_id=@id");
cmd.Parameters.AddWithValue("@id", id);
return(DBlayer.Sel(cmd));
}
//Rasha
public static DataTable pendingOrders_detailsbyid(int id)
{
SqlCommand cmd = new SqlCommand("SELECT Product.Prod_img, Product.Prod_name, Product.Prod_price, OrderItem.Quantity" +
" FROM Product INNER JOIN OrderItem ON Product.Prod_id = OrderItem.prod_id and OrderItem.Order_id =@id");
cmd.Parameters.AddWithValue("@id", id);
return(DBlayer.Sel(cmd));
}
public static DataTable emailsearch(string mail, int id)
{
SqlCommand cmd = new SqlCommand("select Cust_fname from Customer where Cust_mail=@mail and Cust_id!=@id");
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@mail", mail);
return(DBlayer.Sel(cmd));
}
public static DataTable checkId(string national_id)
{
SqlCommand command = new SqlCommand("select Cust_id from [Customer] where Cust_ssn=@nid");
command.Parameters.AddWithValue("nid", national_id);
DataTable dt = DBlayer.Sel(command);
return(dt);
}
public static DataTable search(int cus_id, int prod_id)
{
SqlCommand cmd = new SqlCommand("select quantity from cart where customer_id=@c and prod_id=@p");
cmd.Parameters.AddWithValue("@c", cus_id);
cmd.Parameters.AddWithValue("@p", prod_id);
return(DBlayer.Sel(cmd));
}
//nada
public static DataTable selectByCartID(int cartid, int order)
{
SqlCommand cmd = new SqlCommand("select prod_id,quantity,Order_id from cart inner join [Order] on customer_id=Cust_id and cust_id=@custid and order_id=@orderid");
cmd.Parameters.AddWithValue("@custid", cartid);
cmd.Parameters.AddWithValue("@orderid", order);
return(DBlayer.Sel(cmd));
}
public static DataTable usersearch(string user, int id)
{
SqlCommand cmd = new SqlCommand("select Cust_fname from Customer where cust_username=@user and Cust_id!=@id");
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@user", user);
return(DBlayer.Sel(cmd));
}
public static DataTable getAll()
{
SqlCommand cmd = new SqlCommand("select * from Product where prod_quantity!=0");
return(DBlayer.Sel(cmd));
}
//select all
public static DataTable getAll()
{
SqlCommand cmd = new SqlCommand("select * from Category");
return(DBlayer.Sel(cmd));
}
public static DataTable getPromotions()
{
SqlCommand cmd = new SqlCommand("select * from Product where promotion is not null ");
return(DBlayer.Sel(cmd));
}
public static DataTable getProdPrice()
{
SqlCommand cmd = new SqlCommand("select Prod_price,Prod_id from Product where prod_quantity!=0 ");
return(DBlayer.Sel(cmd));
}
public static DataTable getPendingUsers()
{
SqlCommand cmd = new SqlCommand("select * from Customer where State='pending' ");
return(DBlayer.Sel(cmd));
}
//select
public static DataTable getBlockedUsers()
{
SqlCommand cmd = new SqlCommand("select * from Customer where State='blocked' ");
return(DBlayer.Sel(cmd));
}
public static DataTable getUsers()
{
SqlCommand cmd = new SqlCommand("select * from Customer where IsAdmin=0 ");
return(DBlayer.Sel(cmd));
}
public static DataTable getAll2()
{
SqlCommand cmd = new SqlCommand("select * from Product");
return(DBlayer.Sel(cmd));
}
//samar
public static DataTable numofpending()
{
SqlCommand cmd = new SqlCommand("Select count(order_id) from [order] where state='Pending'");
return(DBlayer.Sel(cmd));
}