/// <summary>
/// delete order by id
/// </summary>
/// <param name="id"></param>
/// <returns>true if delete success</returns>
public static bool DeleteOrder(int id)
{
bool successes = true;
try
{
successes = DalHelper.DeleteRowById(id, "Orders", "ID");
}
catch
{
successes = false;
}
return(successes);
}
/// <summary>
/// get order data table by user type and if they new or old
/// </summary>
/// <param name="type"></param>
/// <param name="userid"></param>
/// <param name="newOrOld"></param>
/// <returns>order data table by user type</returns>
public static DataTable GetOrders(int type, string userid, string newOrOld)
{
switch (type)
{
//Dictionary<int, string> types =new Dictionary<int, string>() { { 4,"CustomerID"},{3,"DeliverID"},{1,"ShopID"}};
case 2:
return(DalHelper.Select($"SELECT Orders.ID,Orders.ArrivalTime, Orders.OrderStutus, Users.FirstName, Shops.ShopName, Users_1.FirstName AS DeliveryName FROM Users AS Users_1 INNER JOIN(Shops INNER JOIN(Users INNER JOIN Orders ON Users.ID = Orders.[CustomerID]) ON Shops.ID = Orders.ShopID) ON Users_1.ID = Orders.DeliverID WHERE(((Users.ID) =[Orders].[DeliverID] Or(Users.ID) =[Orders].[CustomerID]){newOrOld}) ORDER BY Orders.ArrivalTime;"));
case 4:
return(DalHelper.Select($"SELECT Orders.ID,Orders.ArrivalTime, Orders.OrderStutus, Shops.ShopName, Users_1.FirstName AS DeliveryName FROM Users AS Users_1 INNER JOIN(Shops INNER JOIN(Users INNER JOIN Orders ON Users.ID = Orders.[CustomerID]) ON Shops.ID = Orders.ShopID) ON Users_1.ID = Orders.DeliverID WHERE(((Users.ID) =[Orders].[CustomerID]) AND Users.ID ='{userid}' {newOrOld}) ORDER BY Orders.ArrivalTime;"));
case 1:
return(DalHelper.Select($"SELECT Orders.ID,Orders.ArrivalTime, Orders.OrderStutus, Users_1.FirstName AS DeliveryName, Users.FirstName,Orders.ReadyTime FROM Users AS Users_1 INNER JOIN(Shops INNER JOIN (Users INNER JOIN Orders ON Users.ID = Orders.[CustomerID]) ON Shops.ID = Orders.ShopID) ON Users_1.ID = Orders.DeliverID WHERE(([Shops].[ID] =[Orders].[ShopID]) {newOrOld}) ORDER BY Orders.ArrivalTime;"));
default:
return(DalHelper.Select($"SELECT Orders.ID,Orders.ArrivalTime, Orders.OrderStutus, Shops.ShopName, Users.FirstName FROM Users AS Users_1 INNER JOIN(Shops INNER JOIN (Users INNER JOIN Orders ON Users.ID = Orders.[CustomerID]) ON Shops.ID = Orders.ShopID) ON Users_1.ID = Orders.DeliverID WHERE(([Users_1].[ID] =[Orders].[DeliverID]) AND [Users_1].[ID] ='{userid}' {newOrOld}) ORDER BY Orders.ArrivalTime;"));
}
}
//shop Manager
/// <summary>
/// get shop id by shop manager id
/// </summary>
/// <param name="managerId"></param>
/// <returns></returns>
public static int GetShopId(string managerId)
{
return(int.Parse(DalHelper.Select($"SELECT Shops.ID FROM Shops WHERE(((Shops.ShopManagerID) = '{managerId}'));").Rows[0]["ID"].ToString()));
}
/// <summary>
/// get shop data row by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DataRow GetShop(int id)
{
return(DalHelper.AllWhere("Shops", "ID", id).Rows[0]);
}
public static DataTable GetShopTable()
{
return(DalHelper.AllFromTable("Shops"));
}
public static DataTable GetProductListByOrderId(int orderId)
{
return(DalHelper.Select("SELECT Products.*, OrderDetails.Amount, OrderDetails.Price AS DetailPrice FROM Products INNER JOIN OrderDetails ON Products.ID = OrderDetails.ProductID WHERE [OrderDetails].[OrderID]=" + orderId));
}
public static DataTable GetDetailsOfOrder(int orderId)
{
return(DalHelper.AllWhere("OrderDetails", "OrderID", orderId));
}
public static bool RemoveProduct(int Id)
{
return(DalHelper.DeleteRowById(Id, "Products", "ID"));
}
public static DataTable ShopAndManagerTable()
{
return(DalHelper.Select($"SELECT Shops.ShopName, Users.FirstName AS ManagerName FROM (Users INNER JOIN Shops ON Users.ID = Shops.ShopManagerID) GROUP BY Shops.ShopName, Users.FirstName ;"));
}
public static int GetAmountByProductId(int productId, string TotalAmountTableName, string TotalAmountColumn, string idColumnName)
{
return(int.Parse(DalHelper.Select($"SELECT {TotalAmountColumn} FROM {TotalAmountTableName} WHERE {idColumnName}={productId}").Rows[0][TotalAmountColumn].ToString()));
}
public static bool UpdateProductAmountAtShop(int Id, int ReplaceAmount, string TotalAmountTableName, string TotalAmountColumn, string idColumnName)
{
return(DalHelper.UpdateColumnById(Id, TotalAmountTableName, idColumnName, ReplaceAmount.ToString(),
TotalAmountColumn));
}
public static DataTable GetAllProductFromShopDB(string productsTableName, string ShopIDColumn, string OrderIDColumn, string DescriptionColumn, string ShopProductCodeColumn, string PriceColumn, string ImageColumn)
{
return(DalHelper.Select($"SELECT {ShopIDColumn},{OrderIDColumn},{PriceColumn},{DescriptionColumn},{ImageColumn},{ShopProductCodeColumn} FROM {productsTableName}"));
}
public static DataTable CustomersTableByShop(int shopId)
{
return(DalHelper.Select($"SELECT Users.FirstName, Users.Email, Users.PhoneNumber FROM Users INNER JOIN Orders ON(Users.ID = Orders.CustomerID) WHERE(((Users.UserType)= 4) AND([Orders].[ShopID]= {shopId})) Group By Users.FirstName,Users.Email,Users.PhoneNumber "));
}
//Customer
public static DataTable CustomersTable()
{
return(DalHelper.Select($"SELECT Users.FirstName,Users.Email,Users.PhoneNumber FROM Users WHERE(Users.UserType = 4) Group By Users.FirstName,Users.Email,Users.PhoneNumber "));
}
/// <summary>
/// get data table of shop manager with no shop
/// </summary>
/// <returns>data table of shop manager with no shop</returns>
public static DataTable ShopManagersWithNoShop()
{
return(DalHelper.Select("SELECT Users.FirstName, Users.ID FROM Users LEFT JOIN Shops ON Users.ID=[Shops].[ShopManagerID] WHERE [Shops].[ShopManagerID] IS NULL AND (Users.UserType)=1"));
}
public static DataRow GetProductById(int productId)
{
return(DalHelper.Select("SELECT * FROM Products WHERE ID=" + productId).Rows[0]);
}
public static int AddProduct(double price, string description, int shopId, int shopProductCode, string imageUrl)
{
return(DalHelper.Insert($"INSERT INTO [Products]([ShopID],[Description],[ShopProductCode],[Price],[Image]) VALUES({shopId},'{description}',{shopProductCode},{price},'{imageUrl}')"));
}
public static DataTable GetAllProducts(string condition)
{
return(DalHelper.Select("SELECT * FROM Products " + condition));
}
//public static bool IsExsist(string pass, string user)
//{
// DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH);
// if (!helper.OpenConnection()) throw new ConnectionException();
// string sql = $"SELECT * FROM Customers WHERE FirstName = '" + user + "'" + "AND Password = '******'";
// DataTable tb = helper.GetDataTable(sql);
// helper.CloseConnection();
// if (tb.Rows.Count == 0)
// {
// return false;
// }
// return true;
//}
//public static int AddCustomer(string FirstName,string LastName, string pass, string phoneNum, string email ,int NUmOfFloors,string Adress)
//{
// DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH);
// if (!helper.OpenConnection()) throw new ConnectionException();
// string sql = $"INSERT INTO Customers (FirstName,LastName,[Password],PhoneNamber,Gmail,NUmOfFloors,Adress) VALUES('{FirstName}','{LastName}','{pass}','{phoneNum}','{email}','{NUmOfFloors}','{Adress}')";
// int a = helper.InsertWithAutoNumKey(sql);
// helper.CloseConnection();
// if (a == -1) throw new Exception("sql ex");
// return a;
//}
/// <summary>
/// get data table of customer orders
/// </summary>
/// <returns></returns>
public static DataTable GetOrders()
{
return(DalHelper.Select("SELECT Orders.ArrivalTime, Orders.OrderStutus, Users.FirstName, Shops.ShopName FROM Users INNER JOIN(Shops INNER JOIN Orders ON Shops.ID = Orders.ShopID) ON(Users.ID = Shops.ShopManagerID) AND(Users.ID = Orders.DeliverID) AND(Users.ID = Orders.[CustomerID]) WHERE[CustomerID] = '1' ORDER BY Orders.ArrivalTime DESC;"));
}
public static DataTable GetAllProductsOfShop(int shopId, string condition)
{
return(DalHelper.Select($"SELECT * FROM Products WHERE ShopID = {shopId} " + condition));
}
public static int AddDetail(int orderId, int productId, int amount, double price)
{
return(DalHelper.Insert($"INSERT INTO OrderDetails(OrderID,ProductID,Amount,Price) VALUES({orderId},{productId},{amount},{price}) "));
}
public static DataTable SearchProductsByPrice(double price)
{
return(DalHelper.Select($"SELECT * FROM Products WHERE Price={price}"));
}
public static bool UpdateAmount(int id, int amount)
{
return(DalHelper.UpdateColumnById(id, "OrderDetails", "ID", "" + amount, "Amount"));
}
public static DataTable SearchProductsByName(string name)
{
return(DalHelper.Select($"SELECT * FROM Products WHERE Description='{name}'"));
}
public static bool DeleteOrderDetails(int orderId)
{
return(DalHelper.DeleteRowById(orderId, "OrderDetails", "OrderID"));
}
public static DataTable GetAllProductsOrderByName(string condition)
{
return(DalHelper.Select("SELECT * FROM Products GROUP BY Description " + condition));
}
/// <summary>
/// add a new shop to DB
/// </summary>
/// <param name="shopManegerId"></param>
/// <param name="shopName"></param>
/// <returns></returns>
public static int AddShop(string shopManegerId, string shopName)
{
return(DalHelper.Insert($"INSERT INTO Shops(ShopName,ShopManagerID) VALUES('{shopName}','{shopManegerId}')"));
}
public static DataTable GetShopIdByProductId(int productId)
{
return(DalHelper.Select("SELECT ShopID FROM Products WHERE ID=" + productId));
}
/// <summary>
/// get location data row by id
/// </summary>
/// <param name="shopId"></param>
/// <returns></returns>
public static DataRow GetLocation(int shopId)
{
return(DalHelper.Select($"SELECT Users.Lat, Users.Lng FROM Users INNER JOIN Shops ON Users.ID = Shops.ShopManagerID WHERE(([Shops].[ID] = {shopId}));").Rows[0]);
}
public static string GetProductNameById(int productId)
{
return(DalHelper.Select("SELECT Description FROM Products WHERE ID=" + productId).Rows[0]["Description"].ToString());
}