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