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);
}
