/// <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;"));
}
}
//public static int NumOfOrdersFromShop(int ShopID)
//{
// return int.Parse( DalHelper.Select($"SELECT Count(Orders.ID) AS NumOfOrders FROM Orders WHERE(([Orders].[ShopID] = {ShopID})); ").Rows[0]["NumOfOrders"].ToString());
//}
public static int NumOfOrders(string condition)
{
return(int.Parse(DalHelper.Select($"SELECT Count(Orders.ID) AS NumOfOrders FROM Orders {condition}").Rows[0]["NumOfOrders"].ToString()));
}
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 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 string GetProductNameById(int productId)
{
return(DalHelper.Select("SELECT Description FROM Products WHERE ID=" + productId).Rows[0]["Description"].ToString());
}
public static DataTable GetAllProductsOrderByName(string condition)
{
return(DalHelper.Select("SELECT * FROM Products GROUP BY Description " + condition));
}
public static DataTable SearchProductsByPrice(double price)
{
return(DalHelper.Select($"SELECT * FROM Products WHERE Price={price}"));
}
public static DataTable GetAllProducts(string condition)
{
return(DalHelper.Select("SELECT * FROM Products " + condition));
}
public static DataTable DeliveriesList()
{
return(DalHelper.Select($"SELECT Users.ID, Users.FirstName,Users.LastName,Users.Email,Users.PhoneNumber FROM Users WHERE((Users.UserType = 3) AND (Users.ID<>'111111111')) "));
}
public static string GetName(string customerId)
{
return(DalHelper.Select($"SELECT Users.FirstName FROM Users WHERE Users.ID='{customerId}'").Rows[0]["FirstName"].ToString());
}
public static DataTable DeliversSearch(string condition)
{
return(DalHelper.Select($"SELECT Users.FirstName,Users.Email,Users.PhoneNumber FROM Users WHERE((Users.UserType = 3) AND{condition}) Group By Users.FirstName,Users.Email,Users.PhoneNumber "));
}
/// <summary>
/// get a data table order by order time
/// </summary>
/// <param name="deliveryId"></param>
/// <returns></returns>
public static DataTable GetOrdersListByTime(string deliveryId)
{
return(DalHelper.Select($"SELECT * FROM Orders WHERE DeliverID='{deliveryId}' AND OrderStutus=4 ORDER BY Time"));
}
public static int GetOrderStatus(int orderId)
{
return(int.Parse(DalHelper.Select($"SELECT OrderStutus FROM Orders WHERE ID={orderId}").Rows[0]["OrderStutus"].ToString()));
}
//public static string NumOfActiveCustomers(string condition)
//{
// return DalHelper.Select($"SELECT Count(Orders.CustomerID) AS NumOfActiveCustomers FROM Orders {condition}").Rows[0]["NumOfActiveCustomers"].ToString();
//}
public static string NumOfCustomers(string condition)
{
return(DalHelper.Select($"SELECT Count(Users.ID) AS numOfUsers FROM Users {condition};").Rows[0]["numOfUsers"].ToString());
}
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 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 DataTable GetDeliverersLocations()
{
return(DalHelper.Select("SELECT Users.Lat ,Users.Lng FROM Users WHERE Users.UserType=3 AND Users.ID<>'111111111'"));
}
public static DataTable GetAllProductsOfShop(int shopId, string condition)
{
return(DalHelper.Select($"SELECT * FROM Products WHERE ShopID = {shopId} " + condition));
}
public static string GetDeliveryIdByPoint(double lat, double lng)
{
return(DalHelper.Select($"SELECT Users.ID FROM Users WHERE Users.Lat={lat} AND Users.Lng={lng} AND Users.UserType=3").Rows[0]["ID"].ToString());
}
public static DataTable SearchProductsByName(string name)
{
return(DalHelper.Select($"SELECT * FROM Products WHERE Description='{name}'"));
}
//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()));
}
public static DataTable GetShopIdByProductId(int productId)
{
return(DalHelper.Select("SELECT ShopID FROM Products WHERE ID=" + productId));
}
/// <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]);
}
//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 "));
}
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 "));
}
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}"));
}
/// <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 DataRow GetOrderById(int orderId)
{
return(DalHelper.Select($"SELECT * FROM Orders WHERE ID={orderId}").Rows[0]);
}
