public IActionResult GetBestSeller([FromBody] OrderFullReq req)
{
var res = new SingleRsp();
res.Data = _svc.GetBestSeller(req);
return(Ok(res));
}
//2a
public List <object> GetOrderInSpaceTime(OrderFullReq req)
{
List <object> res = new List <object>();
res = _rep.GetOrderInSpaceTime(req);
return(res);
}
public IActionResult GetOrderOfEmployee_Linq([FromBody] OrderFullReq req)
{
var res = new SingleRsp();
res.Data = _svc.GetOrderOfEmployee_Linq(req);
return(Ok(res));
}
public IActionResult GetOrderInSpaceTime([FromBody] OrderFullReq req)
{
var res = new SingleRsp();
res.Data = _svc.GetOrderInSpaceTime(req);
return(Ok(res));
}
public IActionResult DoanhThuTheoQG_Linq1([FromBody] OrderFullReq req)
{
var res = new SingleRsp();
res.Data = _svc.DoanhThuTheoQG_Linq1(req);
return(Ok(res));
}
/// <summary>
/// 3b: danh sách sản phẩm bán chạy
/// </summary>
/// <param name="req"></param>
/// <returns>danh sách sản phẩm bán chạy trong tháng năm nhập vô, theo số lượng hoặc doanh thu</returns>
public object GetBestSeller(OrderFullReq 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 = "dh_DSSPBanChay";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@page", req.Page);
cmd.Parameters.AddWithValue("@size", req.Size);
cmd.Parameters.AddWithValue("@month", req.Month);
cmd.Parameters.AddWithValue("@year", req.Year);
cmd.Parameters.AddWithValue("@isQuantity", req.IsQuantity);
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (req.IsQuantity == 1)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
var x = new
{
ProductId = row["ProductId"],
ProductName = row["ProductName"],
DoanhThu = row["DoanhThu"]
};
res.Add(x);
}
}
else if (req.IsQuantity == 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
var x = new
{
ProductId = row["ProductId"],
ProductName = row["ProductName"],
SoLuong = row["SoLuong"]
};
res.Add(x);
}
}
}
}
catch (Exception ex)
{
res = null;
}
return(res);
}
//phân trang linq method
public object GetOrderOfEmployee_Linq(OrderFullReq req)
{
var res = Context.Orders.Join(Context.Employees, o => o.EmployeeId, e => e.EmployeeId, (o, e) => new
{
o.OrderId,
o.CustomerId,
o.EmployeeId,
o.OrderDate,
o.RequiredDate,
o.ShippedDate,
o.ShipVia,
o.Freight,
o.ShipName,
o.ShipAddress,
o.ShipCity,
o.ShipRegion,
o.ShipPostalCode,
o.ShipCountry,
e.FirstName,
e.LastName,
}).Where(x => x.LastName.Equals(req.Keyword) && x.OrderDate >= req.DateFrom && x.OrderDate <= req.DateTo).ToList();
var offSet = (req.Page - 1) * req.Size;
var total = res.Count();
int totalPage = (total % req.Size) == 0 ? (int)(total / req.Size) : ((int)(total / req.Size) + 1);
var data = res.OrderBy(x => x.OrderDate).Skip(offSet).Take(req.Size).ToList();
List <object> lst = new List <object>();
for (int i = 0; i < data.Count(); i++)
{
var item = data[i];
var tam = new
{
STT = i + 1 + offSet,
item.OrderId,
item.CustomerId,
item.EmployeeId,
item.OrderDate,
item.RequiredDate,
item.ShippedDate,
item.ShipVia,
item.Freight,
item.ShipName,
item.ShipAddress,
item.ShipCity,
item.ShipRegion,
item.ShipPostalCode,
item.ShipCountry
};
lst.Add(tam);
}
return(new
{
Data = lst,
TotalRecords = total,
Page = req.Page,
Size = req.Size,
TotalPages = totalPage
});
}
/// <summary>
/// 3a: danh sách đơn hàng của nhân viên
/// </summary>
/// <param name="req"></param>
/// <returns>danh sách đơn hàng của nhân viên trong khoảng thời gian</returns>
public List <object> GetOrderOfEmployee(OrderFullReq 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 = "dh_DSDHNV";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@page", req.Page);
cmd.Parameters.AddWithValue("@size", req.Size);
cmd.Parameters.AddWithValue("@keyword", req.Keyword);
cmd.Parameters.AddWithValue("@dateFrom", req.DateFrom);
cmd.Parameters.AddWithValue("@dateTo", 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
{
STT = row["STT"],
OrderId = row["OrderId"],
CustomerId = row["CustomerId"],
EmployeeId = row["EmployeeId"],
OrderDate = row["OrderDate"],
RequiredDate = row["RequiredDate"],
ShippedDate = row["ShippedDate"],
ShipVia = row["ShipVia"],
Freight = row["Freight"],
ShipName = row["ShipName"],
ShipAddress = row["ShipAddress"],
ShipCity = row["ShipCity"],
ShipRegion = row["ShipRegion"],
ShipPostalCode = row["ShipPostalCode"],
ShipCountry = row["ShipCountry"]
};
res.Add(x);
}
}
}
catch (Exception ex)
{
res = null;
}
return(res);
}
// suntax
public object DoanhThuTheoQG_Linq(OrderFullReq req)
{
var res = from o in Context.Orders
where o.OrderDate.Value.Month == req.Month && o.OrderDate.Value.Year == req.Year
join od in Context.OrderDetails on o.OrderId equals od.OrderId
group od by o.ShipCountry into g
select new
{
g.Key,
DoanhThu = g.Sum(x => x.Quantity * x.UnitPrice * (1 - (decimal)x.Discount))
};
return(res);
}
// linq method
public object DoanhThuTheoQG_Linq1(OrderFullReq req)
{
var res = Context.Orders
.Where(x => x.OrderDate.Value.Month == req.Month && x.OrderDate.Value.Year == req.Year)
.Join(Context.OrderDetails, o => o.OrderId, od => od.OrderId, (o, od) => new
{
o.ShipCountry,
DoanhThu = od.UnitPrice * od.Quantity * (1 - (decimal)od.Discount)
})
.GroupBy(x => new { x.ShipCountry })
.Select(x => new
{
x.Key.ShipCountry,
DoanhThu = x.Sum(x => x.DoanhThu)
});
return(res);
}
// linq
public object GetOrderInSpaceTime_Linq(OrderFullReq req)
{
var res = All.Where(x => x.OrderDate >= req.DateFrom && x.OrderDate <= req.DateTo);
var offSet = (req.Page - 1) * req.Size;
var total = res.Count();
int totalPage = (total % req.Size) == 0 ? (int)(total / req.Size) : ((int)(total / req.Size) + 1);
var data = res.OrderBy(x => x.OrderDate).Skip(offSet).Take(req.Size).ToList();
List <object> lst = new List <object>();
for (int i = 0; i < data.Count(); i++)
{
var item = data[i];
var tam = new
{
STT = i + 1 + offSet,
item.OrderId,
item.CustomerId,
item.EmployeeId,
item.OrderDate,
item.RequiredDate,
item.ShippedDate,
item.ShipVia,
item.Freight,
item.ShipName,
item.ShipAddress,
item.ShipCity,
item.ShipRegion,
item.ShipPostalCode,
item.ShipCountry
};
lst.Add(tam);
}
return(new
{
Data = lst,
TotalRecords = total,
Page = req.Page,
Size = req.Size,
TotalPages = totalPage
});
}
public object GetBestSeller_Linq(OrderFullReq req)
{
var res = from p in Context.Products
join od in Context.OrderDetails on p.ProductId equals od.ProductId
join o in Context.Orders on od.OrderId equals o.OrderId
group od by new { p.ProductId, p.ProductName } into g
select new
{
g.Key.ProductId,
g.Key.ProductName,
DoanhThu = g.Sum(x => x.Quantity * x.UnitPrice * (1 - (decimal)x.Discount))
};
var offSet = (req.Page - 1) * req.Size;
var total = res.Count();
int totalPage = (total % req.Size) == 0 ? (int)(total / req.Size) : ((int)(total / req.Size) + 1);
var data = res.OrderByDescending(x => x.DoanhThu).Skip(offSet).Take(req.Size).ToList();
List <object> lst = new List <object>();
for (int i = 0; i < data.Count(); i++)
{
var item = data[i];
var tam = new
{
STT = i + 1 + offSet,
item.ProductId,
item.ProductName,
item.DoanhThu
};
lst.Add(tam);
}
return(new
{
Data = lst,
TotalRecords = total,
Page = req.Page,
Size = req.Size,
TotalPages = totalPage
});
}
/// <summary>
/// 3b: doanh thu quốc gia trong tháng năm nhập vô
/// </summary>
/// <param name="req"></param>
/// <returns>doanh thu quốc gia trong tháng năm nhập vô</returns>
public List <object> DoanhThuTheoQG(OrderFullReq 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 = "dh_DoanhThuTheoQG";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@month", req.Month);
cmd.Parameters.AddWithValue("@year", req.Year);
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
{
Country = row["Country"],
DoanhThu = Math.Round(float.Parse(row["DoanhThu"].ToString()), 2)
};
res.Add(x);
}
}
}
catch (Exception ex)
{
res = null;
}
return(res);
}
public object GetOrderInSpaceTime_Linq(OrderFullReq req)
{
return(_rep.GetOrderInSpaceTime_Linq(req));
}
public object DoanhThuTheoQG_Linq1(OrderFullReq req)
{
return(_rep.DoanhThuTheoQG_Linq1(req));
}
public object GetBestSeller_Linq(OrderFullReq req)
{
return(_rep.GetBestSeller_Linq(req));
}
public object GetOrderOfEmployee_Linq(OrderFullReq req)
{
return(_rep.GetOrderOfEmployee_Linq(req));
}