public List <AccademyOrder> GetAllOrdersByIDEmployee(int EmployeeID) { List <AccademyOrder> resultList = new List <AccademyOrder>(); var result = from e in ctx.Employees join o in ctx.Orders on e.EmployeeID equals o.EmployeeID where e.EmployeeID == EmployeeID select o.Order_Details; foreach (var details in result.ToList()) { double amount = 0; int orderID = 0; DateTime orderDate = DateTime.Now; foreach (var detail in details) { orderID = detail.OrderID; orderDate = detail.Order.OrderDate.HasValue? detail.Order.OrderDate.Value : DateTime.Now; short qta = detail.Quantity; decimal up = detail.UnitPrice; double tot = (double)(qta * up); double discount = tot * detail.Discount; amount += tot - discount; } AccademyOrder tmp_order = new AccademyOrder() { OrderID = orderID, OrderDate = orderDate, OrderAmount = Math.Round(amount, 2) }; resultList.Add(tmp_order); } return(resultList); }
public List <AccademyOrder> GetAllOrdersByIDEmployee(int EmployeeID) { List <AccademyOrder> resultList = new List <AccademyOrder>(); using (conn = new SqlConnection(connString)) { cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT Orders.OrderID, Orders.OrderDate, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Employees.EmployeeID " + "FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN " + "[Order Details] ON Orders.OrderID = [Order Details].OrderID " + "WHERE(Employees.EmployeeID = " + EmployeeID + ")"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); int tmpID = -1; bool fine = !dr.Read(); if (!fine) { tmpID = (int)dr["OrderID"]; } while (!fine) { double amount = 0; DateTime orderDate = (DateTime)dr["OrderDate"]; while (!fine && tmpID == (int)dr["OrderID"]) { Int16 qta = (Int16)dr["Quantity"]; decimal up = (decimal)dr["UnitPrice"]; double tot = (double)(qta * up); Double discount = (double)(tot * (Single)dr["Discount"]); amount += (tot - discount); fine = !dr.Read(); } AccademyOrder tmp_order = new AccademyOrder() { OrderID = tmpID, OrderDate = orderDate, OrderAmount = Math.Round(amount, 2) }; resultList.Add(tmp_order); if (!fine) { tmpID = (int)dr["OrderID"]; } } conn.Close(); } return(resultList); }