public IActionResult DoanhThuNVTheoThoiGian_Linq([FromBody] TimeReq req)
{
var res = new SingleRsp();
res.Data = _svc.DoanhThuNVTheoThoiGian_Linq(req);
return(Ok(res));
}
public IActionResult DoanhThuNVTheoNgay_Linq1([FromBody] TimeReq req)
{
var res = new SingleRsp();
res.Data = _svc.DoanhThuNVTheoNgay_Linq1(req.DateFrom);
return(Ok(res));
}
/// <summary>
/// câu 5: nhập vào ngày bắt đầu ngày kết thúc
/// </summary>
/// <param name="req"></param>
/// <returns>số lượng hàng hóa cần giao trong từng ngày</returns>
public object QuantityProducts(TimeReq req)
{
var res = Context.Orders.Join(Context.OrderDetails, a => a.OrderId, b => b.OrderId, (a, b) => new
{
a.OrderId,
a.OrderDate,
a.ShippedDate,
b.Quantity
}).Where(x => x.OrderDate >= req.DateFrom && x.OrderDate <= req.DateTo && x.ShippedDate == null).ToList();
var data = res.GroupBy(x => x.OrderDate)
.Select(x => new
{
OrderDate = x.First().OrderDate,
SoLuong = x.Sum(x => x.Quantity)
}).ToList();
return(data);
}
/// <summary>
/// 1b: doanh thu nhân viên theo khoảng thời gian
/// </summary>
/// <param name="date"></param>
/// <returns>danh sách nhân viên và doanh thu tương ứng trong khoảng thời gian đó</returns>
public object DoanhThuNVTheoThoiGian(TimeReq req)
{
List <object> res = new List <object>();
var cnn = (SqlConnection)Context.Database.GetDbConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
try
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
var cmd = cnn.CreateCommand();
cmd.CommandText = "nv_DoanhThuNVTheoThoiGian";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@begintime", req.DateFrom);
cmd.Parameters.AddWithValue("@endTime", req.DateTo);
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
var x = new
{
EmployeeID = row["EmployeeID"],
Name = row["FirstName"].ToString() + " " + row["LastName"].ToString(),
DoanhThu = Math.Round(float.Parse(row["DoanhThu"].ToString()), 2)
};
res.Add(x);
}
}
}
catch (Exception ex)
{
res = null;
}
return(res);
}
// method
public object DoanhThuNVTheoThoiGian_Linq(TimeReq req)
{
var data = from e in Context.Employees
join o in Context.Orders on e.EmployeeId equals o.EmployeeId
join od in Context.OrderDetails on o.OrderId equals od.OrderId
where o.OrderDate >= req.DateFrom && o.OrderDate <= req.DateTo
select new
{
e.EmployeeId,
Name = e.FirstName + " " + e.LastName,
DoanhThu = od.UnitPrice * od.Quantity * (1 - (decimal)od.Discount)
};
var res = from d in data
group d by new { d.EmployeeId, d.Name } into g
select new
{
g.Key.EmployeeId,
g.Key.Name,
DoanhThu = Math.Round(float.Parse(g.Sum(x => x.DoanhThu).ToString()))
};
return(res);
}
public object QuantityProducts(TimeReq req)
{
return(_rep.QuantityProducts(req));
}
public IActionResult QuantityProducts([FromBody] TimeReq req)
{
var res = _svc.QuantityProducts(req);
return(Ok(res));
}
public object DoanhThuNVTheoThoiGian_Linq(TimeReq req)
{
return(_rep.DoanhThuNVTheoThoiGian_Linq(req));
}
