public List <WarehouseMonthReportVM> GetMonthReport(WarehouseMonthFilterVM filter)
{
DateTime filterDate;
if (filter.Date.Length == 11)
{
filterDate = DateTime.ParseExact(filter.Date, "dd.MM.yyyy.", null);
}
else
{
filterDate = DateTime.ParseExact(filter.Date, "MM.yyyy.", null);
}
var list = _context.Inventory
.Include(x => x.Branch)
.Include(x => x.ItemSize)
.ThenInclude(x => x.Item)
.Include(x => x.ItemSize)
.ThenInclude(x => x.Size)
.Where(x => x.BranchID == filter.BranchId)
.Select
(
x => new WarehouseMonthReportVM
{
Branch = x.Branch.Name,
ItemSizeName = x.ItemSize.Item.Name + " - " + x.ItemSize.Size.Name,
Sold = _context.Warehouse.Where(y => y.Date.Month == filterDate.Month &&
y.ItemSizeID == x.ItemSizeID && y.Date.Year == filterDate.Year)
.Select(y => y.Sold).Sum()
}
).ToList();
return(list);
}
public IActionResult ExcelMonth(string Date, int BranchId)
{
using (var workbook = new XLWorkbook())
{
WarehouseMonthFilterVM filterVM = new WarehouseMonthFilterVM
{
Date = Date,
BranchId = BranchId
};
if (Date.Length == 11)
{
Date = Date.Substring(3);
}
var worksheet = workbook.Worksheets.Add($"Report {Date}");
worksheet.Cell(1, 1).Value = $"Report for {Date}";
var list = _warehouseService.GetMonthReport(filterVM);
worksheet.Column("A").Width = 30;
worksheet.Column("B").Width = 30;
worksheet.Column("C").Width = 10;
worksheet.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, 1).Style.Font.Bold = true;
worksheet.Range("A1:C1").Merge();
worksheet.Cell(2, 1).InsertTable(list);
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
$"Report {Date}.xlsx"));
}
}
}
