public async Task <IActionResult> ExportExcelMainDetail(KanbanByPoParam kanbanByPoParam)
{
var data = await _kanbanByPoService.GetKanbanByPoDetailMainExcel(kanbanByPoParam);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\KanbanByPoMainDetail.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
// custom style
for (int i = 2; i < data.Count + 2; i++)
{
//Custom cell Kind nếu kind bằng 6 hoặc 7 thì cho background màu vàng
Cell cellKind = ws.Cells["Q" + i];
CustomStyle(ref cellKind);
}
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "Excel" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public async Task <PagedList <KanbanByPo_Dto> > GetKanbanByPo(KanbanByPoParam kanbanByPoParam, PaginationParams paginationParams, bool isPaging = true)
{
// gọi store procedure
var data = await(_context.KanbanByPo_Dto.FromSqlRaw("EXEC [dbo].[NSP_REPORT_MAIN] @DateType, @Date_S, @Date_E, @Line, @MO_No, @MO_Seq, @Material_No, @Supplier, @Model_Name, @Article ",
new SqlParameter("DateType", kanbanByPoParam.DateType != 0 ? kanbanByPoParam.DateType : (object)DBNull.Value),
new SqlParameter("Date_S", kanbanByPoParam.DateStart != "" ? kanbanByPoParam.DateStart : (object)DBNull.Value),
new SqlParameter("Date_E", kanbanByPoParam.DateEnd != "" ? kanbanByPoParam.DateEnd : (object)DBNull.Value),
new SqlParameter("Line", kanbanByPoParam.Line != "" ? kanbanByPoParam.Line : (object)DBNull.Value),
new SqlParameter("MO_No", kanbanByPoParam.MoNo != "" ? kanbanByPoParam.MoNo : (object)DBNull.Value),
new SqlParameter("MO_Seq", kanbanByPoParam.MoSeq != "" ? kanbanByPoParam.MoSeq : (object)DBNull.Value),
new SqlParameter("Material_No", (object)DBNull.Value),
new SqlParameter("Supplier", (kanbanByPoParam.Supplier != "" && kanbanByPoParam.Supplier != "All") ? kanbanByPoParam.Supplier : (object)DBNull.Value),
new SqlParameter("Model_Name", kanbanByPoParam.ModelName != "" ? kanbanByPoParam.ModelName : (object)DBNull.Value),
new SqlParameter("Article", kanbanByPoParam.Article != "" ? kanbanByPoParam.Article : (object)DBNull.Value)
)).ToListAsync();
data = data.OrderBy(x => x.Date).ToList();
return(PagedList <KanbanByPo_Dto> .Create(data, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
public async Task <IActionResult> ExportExcelMainSummary([FromQuery] PaginationParams paginationParams, KanbanByPoParam kanbanByPoParam)
{
var data = await _kanbanByPoService.GetKanbanByPo(kanbanByPoParam, paginationParams, false);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\KanbanByPoMainSummary.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "Excel" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public async Task <IActionResult> GetKanbanByPo([FromQuery] PaginationParams paginationParams, KanbanByPoParam kanbanByPoParam)
{
var result = await _kanbanByPoService.GetKanbanByPo(kanbanByPoParam, paginationParams);
Response.AddPagination(result.CurrentPage, result.PageSize, result.TotalCount, result.TotalPages);
return(Ok(result));
}
public async Task <List <KanbanByPoDetailExcel_Dto> > GetKanbanByPoDetailMainExcel(KanbanByPoParam kanbanByPoParam)
{
var data = await(_context.KanbanByPoDetailExcel_Dto.FromSqlRaw("EXEC [dbo].[NSP_REPORT_DETAIL] @DateType, @Date_S, @Date_E, @Line, @MO_No, @MO_Seq, @Material_No, @Supplier, @Model_Name, @Article ",
new SqlParameter("DateType", kanbanByPoParam.DateType != 0 ? kanbanByPoParam.DateType : (object)DBNull.Value),
new SqlParameter("Date_S", kanbanByPoParam.DateStart != "" ? kanbanByPoParam.DateStart : (object)DBNull.Value),
new SqlParameter("Date_E", kanbanByPoParam.DateEnd != "" ? kanbanByPoParam.DateEnd : (object)DBNull.Value),
new SqlParameter("Line", kanbanByPoParam.Line != "" ? kanbanByPoParam.Line : (object)DBNull.Value),
new SqlParameter("MO_No", kanbanByPoParam.MoNo != "" ? kanbanByPoParam.MoNo : (object)DBNull.Value),
new SqlParameter("MO_Seq", kanbanByPoParam.MoSeq != "" ? kanbanByPoParam.MoSeq : (object)DBNull.Value),
new SqlParameter("Material_No", (object)DBNull.Value),
new SqlParameter("Supplier", kanbanByPoParam.Supplier != "" ? kanbanByPoParam.Supplier : (object)DBNull.Value),
new SqlParameter("Model_Name", kanbanByPoParam.ModelName != "" ? kanbanByPoParam.ModelName : (object)DBNull.Value),
new SqlParameter("Article", kanbanByPoParam.Article != "" ? kanbanByPoParam.Article : (object)DBNull.Value)
)).ToListAsync();
var result = data.Select(x =>
{
x.Material_No = x.Material_No.Trim() == "ZZZZZZZZZZ" ? "" : x.Material_No;
return(x);
}).OrderBy(x => x.Plan_Start_STF).ToList();
return(result);
}
