public IActionResult GetBatchReport(
[FromQuery][QueryObject] BatchReportOptions options)
{
var validationData = _service.ValidateGetBatchReport(User, options);
if (!validationData.IsValid)
{
return(BadRequest(AppResult.FailValidation(data: validationData)));
}
using var workbook = _service.GenerateBatchEventReport(options);
var data = _service.SaveAsBytes(workbook);
return(File(data, Business.Constants.ContentType.SPREADSHEET, $"batch-{options.batch_id}-report.xlsx"));
}
public XLWorkbook GenerateBatchEventReport(BatchReportOptions options)
{
var proBatchService = provider.GetRequiredService <IProductionBatchService>();
var qcEventService = provider.GetRequiredService <IQCEventService>();
var defectTypeService = provider.GetRequiredService <IDefectTypeService>();
var proBatch = proBatchService.ProductionBatchs.Id(options.batch_id)
.Select(o => new ProductionBatch
{
Id = o.Id,
Code = o.Code,
TotalAmount = o.TotalAmount
}).First();
var workbook = new XLWorkbook();
// sheet 1
var sheet1 = workbook.Worksheets.Add("Batch report");
var currentRow = 1;
var headerTitle = new[] { "No", "Id", "Time", "Defect code", "Defect name", "Batch code" };
var header = sheet1.SetRowData(currentRow++, headerTitle);
header.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
header.Style.Font.SetBold();
var defectTypesMap = defectTypeService.DefectTypes.Select(o => new DefectType
{
Id = o.Id,
QCMappingCode = o.QCMappingCode,
Code = o.Code,
Name = o.Name,
}).ToDictionary(o => o.Id);
var defRecords = qcEventService.QCEvents.OfBatch(options.batch_id)
.SortByTime(false)
.Join(qcEventService.QCEventDetails, o => o.Id, o => o.EventId, (ev, dt) => new
{
ev.Id,
ev.CreatedTime,
dt.DefectTypeId,
BatchCode = ev.Batch.Code
}).ToList();
var defRecordsFinal = defRecords.Select(o => new
{
o.Id,
o.CreatedTime,
o.BatchCode,
DefectTypeName = defectTypesMap[o.DefectTypeId].Name,
DefectTypeCode = defectTypesMap[o.DefectTypeId].Code,
});
var passRecords = qcEventService.QCEvents.OfBatch(options.batch_id)
.Passed()
.SortByTime(false)
.Select(o => new
{
o.Id,
o.CreatedTime,
BatchCode = o.Batch.Code,
DefectTypeName = "",
DefectTypeCode = "",
}).ToList();
var no = 1;
IEnumerable <dynamic> allRecords = new List <dynamic>();
allRecords = allRecords.Concat(defRecordsFinal).Concat(passRecords).ToList();
foreach (var o in allRecords)
{
Business.Helpers.XLHelper.SetRowData(sheet1, currentRow++,
no++, o.Id, o.CreatedTime, o.DefectTypeCode, o.DefectTypeName, o.BatchCode);
}
for (var i = 1; i <= headerTitle.Length; i++)
{
sheet1.Column(i).AdjustToContents();
}
// sheet 2
var sheet2 = workbook.AddWorksheet("Defect report");
currentRow = 1;
headerTitle = new[] { "No", "Defect code", "Defect name", "Amount", "Average(%)",
"", "Batch code", proBatch.Code };
var row = sheet2.SetRowData(currentRow++, headerTitle);
var totalCell = sheet2.Cell(2, 7);
totalCell.Value = "Total amount"; totalCell.Style.Font.SetBold();
sheet2.Cell(2, 8).Value = proBatch.TotalAmount;
row.Style.Font.SetBold();
row.Cell(headerTitle.Length).Style.Font.SetBold(false);
var groups = allRecords.GroupBy(o => new
{
o.DefectTypeCode,
o.DefectTypeName
}).ToList();
no = 1;
foreach (var g in groups)
{
var count = g.Count();
var avg = Math.Round((double)count / proBatch.TotalAmount * 100, 2);
Business.Helpers.XLHelper.SetRowData(sheet2, currentRow++, no++,
g.Key.DefectTypeCode ?? "", g.Key.DefectTypeName ?? "", count, avg);
}
for (var i = 1; i <= headerTitle.Length; i++)
{
sheet2.Column(i).AdjustToContents();
}
return(workbook);
}
public ValidationData ValidateGetBatchReport(
ClaimsPrincipal principal, BatchReportOptions options)
{
return(new ValidationData());
}
