public async Task <IActionResult> PostReport([FromBody] CreateReportDto createReportDto)
{
MonthReportModel model = new MonthReportModel
{
Month = createReportDto.DateReport.ToString("MMMM", CultureInfo.CreateSpecificCulture("en-US")),
Year = createReportDto.DateReport.Year.ToString(),
Procedures = await _reportService.GetPerformedProcedures(createReportDto.DateReport),
Doctors = await _reportService.GetDoctors(),
RentExpense = createReportDto.RentExpense,
AdvertisingExpense = createReportDto.AdvertisingExpense,
UtilitiesExpense = createReportDto.UtilitiesExpense
};
byte[] bin = await _reportService.SaveExcelReportFile(model);
//clear the buffer stream
Response.Headers.Clear();
Response.Clear();
//set the correct contenttype
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//set the correct length of the data being send
Response.Headers.Add("content-length", bin.Length.ToString());
//set the filename for the excel package
Response.Headers.Add("content-disposition", "attachment; filename=\"MonthReport.xlsx\"");
//send the byte array to the browser
await Response.Body.WriteAsync(bin, 0, bin.Length);
//cleanup
await Response.CompleteAsync();
return(Ok());
}
public async Task <byte[]> SaveExcelReportFile(MonthReportModel model)
{
using (var package = new ExcelPackage())
{
var workSheet = package.Workbook.Worksheets.Add($"{model.Month} , {model.Year}");
workSheet.Cells.Style.Font.Size = 14;
workSheet.Cells["A1"].Value = "VETERINARY CLINIC";
workSheet.Cells["A1:D1"].Merge = true;
workSheet.Row(1).Style.Font.Bold = true;
workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells["A2"].Value = "Income statement";
workSheet.Cells["A2:D2"].Merge = true;
workSheet.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells["A3"].Value = $"For the month ended {model.Month} , {model.Year}";
workSheet.Cells["A3:D3"].Merge = true;
workSheet.Row(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells["A3:D3"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells["A4"].Value = "Revenues";
workSheet.Cells["A4:D4"].Merge = true;
workSheet.Row(4).Style.Font.Bold = true;
int proceduresCount = 0;
if (model.Procedures != null && model.Procedures.Count > 0)
{
for (int i = 1; i <= model.Procedures.Count; i++)
{
workSheet.Cells["B" + (4 + i).ToString()].Value = $"{model.Procedures[i - 1].ProcedureName}: ({model.Procedures[i - 1].Count})";
workSheet.Cells["C" + (4 + i).ToString()].Value = model.Procedures[i - 1].Price * model.Procedures[i - 1].Count;
proceduresCount++;
}
}
workSheet.Cells["C" + (5 + proceduresCount).ToString()].Value = "Service revenue";
if (proceduresCount > 0)
{
workSheet.Cells["D" + (5 + proceduresCount).ToString()].Formula = "=SUM(C5:C" + (5 + proceduresCount - 1).ToString() + ")";
}
else
{
workSheet.Cells["D" + (5 + proceduresCount).ToString()].Value = 0;
}
workSheet.Cells["D" + (5 + proceduresCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["A" + (6 + proceduresCount).ToString()].Value = "Expenses";
workSheet.Cells["A" + (6 + proceduresCount).ToString() + ":D" + (6 + proceduresCount).ToString()].Merge = true;
workSheet.Row(6 + proceduresCount).Style.Font.Bold = true;
int employeeCount = 0;
if (model.Doctors != null && model.Doctors.Count > 0)
{
for (int i = 1; i <= model.Doctors.Count; i++)
{
workSheet.Cells["B" + (6 + (proceduresCount + i)).ToString()].Value = $"{model.Doctors[i - 1].FirstName} {model.Doctors[i - 1].LastName}";
workSheet.Cells["C" + (6 + (proceduresCount + i)).ToString()].Value = model.Doctors[i - 1].Salary;
employeeCount++;
}
}
workSheet.Cells["B" + (7 + proceduresCount + employeeCount).ToString()].Value = "Salaries expense";
if (employeeCount > 0)
{
workSheet.Cells["C" + (7 + proceduresCount + employeeCount).ToString()].Formula = "=SUM(C" + (7 + proceduresCount).ToString() + ":C" + (6 + proceduresCount + employeeCount).ToString() + ")";
}
else
{
workSheet.Cells["C" + (7 + proceduresCount + employeeCount).ToString()].Value = 0;
}
workSheet.Cells["C" + (7 + proceduresCount + employeeCount).ToString()].Style.Font.Bold = true;
int totalCount = proceduresCount + employeeCount;
workSheet.Cells["B" + (8 + totalCount).ToString()].Value = "Rent expense";
workSheet.Cells["C" + (8 + totalCount).ToString()].Value = model.RentExpense != null ? model.RentExpense : 0;
workSheet.Cells["C" + (8 + totalCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["B" + (9 + totalCount).ToString()].Value = "Advertising expense";
workSheet.Cells["C" + (9 + totalCount).ToString()].Value = model.AdvertisingExpense != null ? model.AdvertisingExpense : 0;
workSheet.Cells["C" + (9 + totalCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["B" + (10 + totalCount).ToString()].Value = "Utilities expense";
workSheet.Cells["C" + (10 + totalCount).ToString()].Value = model.UtilitiesExpense != null ? model.UtilitiesExpense : 0;
workSheet.Cells["C" + (10 + totalCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["C" + (11 + totalCount).ToString()].Value = "Total expense";
workSheet.Cells["D" + (11 + totalCount).ToString()].Formula = "=SUM(C" + (7 + totalCount).ToString() + ":C" + (10 + totalCount).ToString() + ")";
workSheet.Cells["D" + (11 + totalCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["A" + (12 + totalCount).ToString()].Value = "Net income";
workSheet.Cells["A" + (12 + totalCount).ToString() + ":" + "C" + (12 + totalCount).ToString()].Merge = true;
workSheet.Cells["A" + (12 + totalCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["D" + (12 + totalCount).ToString()].Formula = "=D" + (5 + proceduresCount).ToString() + "-D" + (11 + totalCount).ToString();
workSheet.Cells["D" + (12 + totalCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["D" + (12 + totalCount).ToString()].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ExcelRange rg = workSheet.Cells[1, 1, workSheet.Dimension.End.Row, workSheet.Dimension.End.Column];
rg.Style.Border.BorderAround(ExcelBorderStyle.Thick);
double minimumSize = 12;
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns(minimumSize);
return(await package.GetAsByteArrayAsync());
}
}
