public ActionResult ChartData(int ID = 0)
 {
     using (Models.VTEntities db = new VTEntities())
     {
         string SQL  = @"select t2.ProductName,ISNULL(SUM(Total),0) as Total from SalesOrderLine
                         AS T1 INNER JOIN Product AS T2 ON T1.ProductId = T2.ProductId
                          where SalesOrderId = " + ID + "group by t1.ProductId, t2.ProductName";
         var    data = db.Database.SqlQuery <PIE_CHART>(SQL).ToArray();
         return(Json(data, JsonRequestBehavior.AllowGet));
     }
 }
        public ActionResult BaoGiaThongThuong(int ID = 0)
        {
            string path = System.Web.HttpContext.Current.Server.MapPath("~") + "BAOGIATHONGTHUONG.xlsx";

            using (System.IO.FileStream stream = new System.IO.FileStream(path, System.IO.FileMode.Open))
            {
                using (ExcelPackage excel = new ExcelPackage(stream))
                {
                    // Get Sheet Name
                    var workbook  = excel.Workbook;
                    var ws        = workbook.Worksheets.FirstOrDefault();
                    int recordRow = 12;
                    using (Models.VTEntities db = new VTEntities())
                    {
                        var data = db.Database.SqlQuery <C_BaoGiaThongThuong>(@"
                                SELECT T2.ProductName,T3.UnitOfMeasureName,T1.Quantity,T2.DefaultSellingPrice 
                                FROM SalesOrderLine as t1
                                inner join Product as t2 on t1.ProductId = t2.ProductId

                                left join UnitOfMeasure as t3 on t2.UnitOfMeasureId = t3.UnitOfMeasureId

                                where t1.SalesOrderId = " + ID + "").ToList();

                        foreach (var item in data)
                        {
                            ws = CoppyRow(ws, 12, recordRow);
                            ws.Cells[recordRow, 2].Value = item.ProductName;
                            ws.Cells[recordRow, 3].Value = item.UnitOfMeasureName;
                            ws.Cells[recordRow, 4].Value = item.Quantity;
                            ws.Cells[recordRow, 5].Value = item.DefaultSellingPrice;
                            ws.InsertRow(recordRow, 1);

                            recordRow++;
                        }
                        var byteexcel             = excel.GetAsByteArray();
                        System.IO.MemoryStream ms = new System.IO.MemoryStream();
                        var TENKH = db.Database.SqlQuery <String>("SELECT t2.CustomerName FROM SalesOrder as t1 inner join Customer as t2 on t1.CustomerId = t2.CustomerId WHERE SalesOrderId = " + ID).FirstOrDefault();
                        return(File(byteexcel, "application/vnd.ms-excel", "Báo giá thông thường " + TENKH + ".xlsx"));
                    }
                }
            }
        }