public IEnumerable <ReportViewModel> initreportdata()
{
string sql = @"select year(o.OrderDate) as year, MONTH(o.OrderDate) as mouth from OrderDetails od
left join Products p on od.ProductID = p.ProductID
left join Orders o on od.OrderID=o.OrderID
where o.DeliverName is not null and o.DeliverPhone is not null and o.DeliverAddress is not null
GROUP BY year(o.OrderDate), MONTH(o.OrderDate)
";
ConnRespository <ReportViewModel> Respository = new ConnRespository <ReportViewModel>(context);
var data = Respository.GetAll(sql);
return(data);
}
public IEnumerable <ReportViewModel> initreportdata2()
{
string sql = @"select c.CategoryName,Month(od.OrderDate)as mouth,year(od.OrderDate) as year,SUM(o.Quantity*p.ProductPrice) as total from OrderDetails o
left join Products p on o.ProductID=p.ProductID
left join Orders od on o.OrderID=od.OrderID
left join Categories c on p.CategoryID=c.CategoryID
where od.DeliverName is not null and od.DeliverPhone is not null and od.DeliverAddress is not null
GRoup by c.CategoryName,Month(od.OrderDate),year(od.OrderDate)
";
ConnRespository <ReportViewModel> Respository = new ConnRespository <ReportViewModel>(context);
var data = Respository.GetAll(sql);
return(data);
}
// GET: Backstage
public List <ProducetDetail> initdetil()
{
List <ProducetDetail> data = new List <ProducetDetail>();
try
{
string sql = @"select *from Products p
left join Categories c on p.CategoryID= c.CategoryID
left join PicDetails pic on pic.ProductID=p.ProductID";
ConnRespository <ProducetDetail> Conn = new ConnRespository <ProducetDetail>(item);
data = Conn.GetAll(sql).ToList();
//有join有viewmodel才要用隱含轉換
//data = (from d in item.Products
// join c in item.Categories on d.CategoryID equals c.CategoryID
// join pic in item.PicDetails on d.ProductID equals pic.ProductID
// select new
// {
// ProductID = d.ProductID,
// CategoryDescription = c.CategoryDescription,
// CategoryName = c.CategoryName,
// CategoryPic = c.CategoryPic,
// PicUrl = pic.PicUrl,
// ProductDescription = d.ProductDescription,
// ProductName = d.ProductName,
// UnitsInStock = d.UnitsInStock,
// ProductPrice=d.ProductPrice
// }).ToList().Select(x=>new ProducetDetail
// {
// ProductID = x.ProductID,
// CategoryDescription = x.CategoryDescription,
// CategoryName = x.CategoryName,
// CategoryPic = x.CategoryPic,
// PicUrl = x.PicUrl,
// ProductDescription = x.ProductDescription,
// ProductName = x.ProductName,
// UnitsInStock = x.UnitsInStock,
// ProductPrice=x.ProductPrice
// }).ToList();
}
catch (Exception ex) {
return(null);
}
return(data);
}
// 用daper抓出初始資料
public List <NewProductDetail> initdetail()
{
List <NewProductDetail> data = new List <NewProductDetail>();
try
{
string sql = @"select *from Products p
left join Categories c on p.CategoryID= c.CategoryID
left join PicDetails pic on pic.ProductID=p.ProductID";
ConnRespository <NewProductDetail> Conn = new ConnRespository <NewProductDetail>(item);
data = Conn.GetAll(sql).ToList();
}
catch (Exception ex)
{
return(null);
}
return(data);
}
