public List <OrderHistory> GetUserOrders(int userID)
{
ElectronicsShopDBEntities1 dBEntities = new ElectronicsShopDBEntities1();
List <OrderHistory> orders = dBEntities.OrderHistory.AsNoTracking().Where(o => o.CustomerID == userID).ToList();
return(orders);
}
public List <OrderDetails> ViewDetails(int OrderID)
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
List <OrderDetails> details = dbEntities.OrderDetails.AsNoTracking().Where(d => d.OrderID == OrderID).ToList();
return(details);
}
public void DeleteOrder(int OrderID)
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
OrderHistory orderToRemove = dbEntities.OrderHistory.Where(x => x.OrderID == OrderID).FirstOrDefault();
dbEntities.OrderDetails.RemoveRange(orderToRemove.OrderDetails.ToList());
dbEntities.OrderHistory.Remove(orderToRemove);
dbEntities.SaveChanges();
}
public async Task <int> Login(string username, string password) //Login operation (stored procedure with ADO NET)
{
Users currentUser = new Users();
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
using (SqlCommand sqlComm = new SqlCommand("uspLogin"))
{
try
{
SqlParameter usernameParam = new SqlParameter("@pLoginName", System.Data.SqlDbType.NVarChar);
SqlParameter passwordParam = new SqlParameter("@pPassword", System.Data.SqlDbType.NVarChar);
SqlParameter responseParam = new SqlParameter("@responseMessage", System.Data.SqlDbType.Int);
usernameParam.Value = username;
passwordParam.Value = password;
responseParam.Value = null;
sqlComm.Parameters.Add(usernameParam);
sqlComm.Parameters.Add(passwordParam);
sqlComm.Parameters.Add(responseParam);
responseParam.Direction = System.Data.ParameterDirection.ReturnValue; //parameter that is supposed to be returned as a result of a sql procedure
sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
sqlComm.Connection = sqlConn;
if (sqlComm.Connection.State == System.Data.ConnectionState.Closed)
{
sqlComm.Connection.Open();
}
await sqlComm.ExecuteNonQueryAsync();
int loginResult = (Int32)responseParam.Value; // if 0 success , if 1 - invalid password , if 2 - Invalid Username
ElectronicsShopDBEntities1 dbEntites = new ElectronicsShopDBEntities1();
if (loginResult == 0)
{
currentUser = dbEntites.Users.Where(u => u.Username == username).FirstOrDefault();
Application.Current.Properties["CurrentUser"] = currentUser;
return(0); // Successful login
}
return(loginResult); //Failed Login
}
catch
{
sqlConn.Close();
return(2);
}
}
}
}
public int GetProductStock(int productID)
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
if (productID != -1)
{
return(dbEntities.Products.AsNoTracking().Where(x => x.ProductID == productID).FirstOrDefault().StockQuantity);
}
return(0);
}
public void DeleteOrderDetail(int detailID)
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
//var sql = "DELETE FROM OrderDetails Where OrderDetailID = {0}";
OrderDetails detailsToRemove = dbEntities.OrderDetails.Where(o => o.OrderDetailID == detailID).FirstOrDefault();
dbEntities.OrderDetails.Remove(detailsToRemove);
dbEntities.SaveChanges(); //this has to be saved because the price of the details is computed in the database
decimal newPrice = GetNewPrice(detailsToRemove.OrderID);
dbEntities.OrderHistory.Where(o => o.OrderID == detailsToRemove.OrderID).FirstOrDefault().TotalPrice = newPrice;
dbEntities.SaveChanges();
}
public void ModifyOrder(OrderHistory modifiedOrder)
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
OrderHistory orderToModify = dbEntities.OrderHistory.Where(o => o.OrderID == modifiedOrder.OrderID).FirstOrDefault();
orderToModify.EmployeeID = modifiedOrder.EmployeeID;
orderToModify.DeliveryAddress = modifiedOrder.DeliveryAddress;
orderToModify.ReceiptNumber = modifiedOrder.ReceiptNumber;
orderToModify.Status = modifiedOrder.Status;
orderToModify.OrderDate = modifiedOrder.OrderDate;
dbEntities.SaveChanges();
}
public Dictionary <int, string> GetCustomers()
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
var getUsersQuery = from Users in dbEntities.Users
orderby Users.FirstName
select new { Users.UserID, Users.FirstName, Users.LastName };
Dictionary <int, string> wholeNames = new Dictionary <int, string>();
foreach (var user in getUsersQuery.ToList())
{
string wholeName = $"{user.FirstName} {user.LastName}";
wholeNames[user.UserID] = wholeName;
}
return(wholeNames);
}
public Dictionary <int, string> GetProducts()
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
var getProductsQuery = from Products in dbEntities.Products
orderby Products.ProductName
select new { Products.ProductID, Products.ProductName };
Dictionary <int, string> productNames = new Dictionary <int, string>();
foreach (var product in getProductsQuery.ToList())
{
productNames[product.ProductID] = product.ProductName;
}
return(productNames);
}
public Dictionary <int, string> GetEmployees()
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
var getEmployeesQuery = from Employees in dbEntities.Employees
orderby Employees.FirstName
select new { Employees.EmployeeID, Employees.FirstName, Employees.LastName }; //won't be tracked as it doesn't contain an instance of an Employee object
Dictionary <int, string> wholeNames = new Dictionary <int, string>();
foreach (var employee in getEmployeesQuery.ToList())
{
string wholeName = $"{employee.FirstName} {employee.LastName}";
wholeNames[employee.EmployeeID] = wholeName;
}
return(wholeNames);
}
public OrderDetails AddOrderDetail(OrderDetails details)
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
details.UnitPrice = GetProductPrice(details.ProductID);
dbEntities.OrderDetails.Add(details);
dbEntities.SaveChanges(); //this has to be saved because the price of the details is computed in the database
decimal newPrice = GetNewPrice(details.OrderID);
dbEntities.OrderHistory.Where(x => x.OrderID == details.OrderID).FirstOrDefault().TotalPrice = newPrice;
dbEntities.SaveChanges();
return(details);
}
public void AddOrder(Users currentUser, OrderHistory order, OrderDetails details)
{
ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1();
order.OrderDate = DateTime.Now;
order.ReceiptNumber = GetRandomString(13);
order.CustomerID = currentUser.UserID;
order.CurrencyCode = "USD";
details.OrderID = order.OrderID;
details.UnitPrice = GetProductPrice(details.ProductID);
order.OrderDetails.Add(details);
dbEntities.OrderHistory.Add(order);
dbEntities.OrderDetails.Add(details);
dbEntities.SaveChanges();
// Add total price after automatically calculating it in DB
decimal newPrice = GetNewPrice(order.OrderID);
order.TotalPrice = newPrice; //still tracked
dbEntities.SaveChanges();
}
