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