protected override async Task <IProcessResult <AllExpendituresModel> > Handle(GetAllExpendituresRequest msg)
{
var employeeDetails = (await empRepo.GetAllEmployees())
.Where(x => x.EmployeeId != 1) //exclude Admin here
.ToDictionary(x => x.EmployeeId);
var jobs = (await jobsRepository.GetAsync()).ToDictionary(x => x.JobId);
var result = new AllExpendituresModel();
if (msg.IncludeArcFlashlabels)
{
await LoadSection(await arcFlashLabelsRepo.SearchForEntity(x => msg.LimitToSingleExpense.HasValue ?
x.ExternalId == msg.LimitToSingleExpense.Value :
true),
(ArcFlashLabelExpenditure x) => new Expense <ArcFlashLabelExpenditure>()
{
Detail = x,
Amount = x.TotalLabelsCost,
EmployeeName = $"{employeeDetails[x.EmployeeId].Last}, {employeeDetails[x.EmployeeId].First}",
FullJobNameWithCode = jobs[x.JobId].FullJobCodeWithName,
Id = x.Id,
ShortExpenseName = $"Arc Flash Label Expense ({DateTimeWithZone.ConvertToEST(x.DateOfInvoice.UtcDateTime).ToShortDateString()})",
JobId = x.JobId,
LastModifiedDateEst = DateTimeWithZone.ConvertToEST(x.LastModified.UtcDateTime)
},
mapped => result.ArcFlashLabelExpenditures = mapped);
}
if (msg.IncludeCompanyVehicles)
{
await LoadSection(await companyVehicleRepo.SearchForEntity(x => msg.LimitToSingleExpense.HasValue ?
x.ExternalId == msg.LimitToSingleExpense.Value :
true),
(CompanyVehicleExpenditure x) => new Expense <CompanyVehicleExpenditure>()
{
Detail = x,
Amount = x.GetTotalCost(),
EmployeeName = $"{employeeDetails[x.EmployeeId].Last}, {employeeDetails[x.EmployeeId].First}",
FullJobNameWithCode = jobs[x.JobId].FullJobCodeWithName,
Id = x.ExternalId,
ShortExpenseName = $"Company Vehicle: {x.Vehicle} ({x.TotalMiles:N} miles)",
JobId = x.JobId,
LastModifiedDateEst = DateTimeWithZone.ConvertToEST(x.LastModified.UtcDateTime)
},
mapped => result.CompanyVehicleExpenditures = mapped);
}
if (msg.IncludeContractorExpenditures)
{
await LoadSection(await contractorExpRepo.SearchForEntity(x => msg.LimitToSingleExpense.HasValue ?
x.ExternalId == msg.LimitToSingleExpense.Value :
true),
(ContractorExpenditure x) => new Expense <ContractorExpenditure>()
{
Detail = x,
Amount = x.TotalPOContractAmount,
EmployeeName = $"{employeeDetails[x.EmployeeId].Last}, {employeeDetails[x.EmployeeId].First}",
FullJobNameWithCode = jobs[x.JobId].FullJobCodeWithName,
Id = x.ExternalId,
ShortExpenseName = $"Contrator Expense (PO: {x.OrionPONumber})",
JobId = x.JobId,
LastModifiedDateEst = DateTimeWithZone.ConvertToEST(x.LastModified.UtcDateTime)
},
mapped => result.ContractorExpenditures = mapped);
}
if (msg.IncludeMiscExpenditure)
{
await LoadSection(await miscRepo.SearchForEntity(x => msg.LimitToSingleExpense.HasValue ?
x.ExternalId == msg.LimitToSingleExpense.Value :
true),
(MiscExpenditure x) => new Expense <MiscExpenditure>()
{
Detail = x,
Amount = x.Amount,
EmployeeName = $"{employeeDetails[x.EmployeeId].Last}, {employeeDetails[x.EmployeeId].First}",
FullJobNameWithCode = jobs[x.JobId].FullJobCodeWithName,
Id = x.Id,
ShortExpenseName = $"Misc - {x.Description}",
JobId = x.JobId,
LastModifiedDateEst = DateTimeWithZone.ConvertToEST(x.LastModified.UtcDateTime)
},
mapped => result.MiscExpenditures = mapped);
}
if (msg.IncludeTimeAndExpenseExpenditures)
{
await LoadSection(await timeAndExpenseExpRepo.SearchForEntity(x => msg.LimitToSingleExpense.HasValue ?
x.ExternalId == msg.LimitToSingleExpense.Value :
true),
(TimeAndExpenceExpenditure x) => new Expense <TimeAndExpenceExpenditure>()
{
Detail = x,
Amount = x.Amount,
EmployeeName = $"{employeeDetails[x.EmployeeId].Last}, {employeeDetails[x.EmployeeId].First}",
FullJobNameWithCode = jobs[x.JobId].FullJobCodeWithName,
Id = x.Id,
ShortExpenseName = $"T&E - ({DateTimeWithZone.ConvertToEST(x.ExpenseOnDate.UtcDateTime).ToShortDateString()})",
JobId = x.JobId,
LastModifiedDateEst = DateTimeWithZone.ConvertToEST(x.LastModified.UtcDateTime)
},
mapped => result.TimeAndExpenceExpenditures = mapped);
}
result.AvailableEmployees = employeeDetails.Select(e => new CoreEmployeeDto()
{
EmployeeId = e.Key,
First = e.Value.First,
Last = e.Value.Last
}).ToList();
var inUseJobs = result.AsFullList().Select(x => x.JobId).ToHashSet();
result.AvailableJobs = jobs.Values.Where(x => x.JobStatusId == JobStatus.Enabled || inUseJobs.Contains(x.JobId)).ToList();
return(Success(result));
}
private static void WriteTimeAndExpensesSection(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 7;
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.TimeAndExpense.Count());
excelSheet.Range($"{columns[1]}{rowStart - 1}:{columns[2]}{rowStart - 1}")
.AddBottomBorder(XLBorderStyleValues.Medium);
foreach (var sectionRow in report.Data.TimeAndExpense)
{
excelSheet.Cell(rowStart, 1)
.MergeWith(FluentCellStyle.CellToThe.right)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.SetDataType(XLDataType.DateTime)
.AssignValue(DateTimeWithZone.ConvertToEST(sectionRow.Date.UtcDateTime))
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == 7 ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 3)
.AssignValue(sectionRow.EmployeeFirstLast)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == 7 ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 4)
.AssignValue(sectionRow.Cost, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(rowStart == 7 ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 5)
.AddLeftBorder(XLBorderStyleValues.Thin);
rowStart++;
}
excelSheet.Cell(rowStart, 1)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 2)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 3).AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.SetFontStyle(x => x.Bold = true);
var totalCell = excelSheet.Cell(rowStart, 4)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
totalCell.SetFontStyle(x => x.Bold = true);
totalCell.FormulaA1 = $"SUM({columns[4]}{originalRowStart}:{columns[4]}{rowStart - 1})";
totalCell.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}
private static void WriteCompanyVehicleSection(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 11 + report.Data.TimeAndExpense.Count();
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.CompanyVehicle.Count());
excelSheet.Range($"{columns[1]}{rowStart - 1}:{columns[2]}{rowStart - 1}")
.AddBottomBorder(XLBorderStyleValues.Medium);
foreach (var sectionRow in report.Data.CompanyVehicle)
{
excelSheet.Cell(rowStart, 1)
.MergeWith(FluentCellStyle.CellToThe.right)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.SetDataType(XLDataType.DateTime)
.AssignValue(DateTimeWithZone.ConvertToEST(sectionRow.Date.UtcDateTime))
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 3)
.AssignValue(sectionRow.Vehicle)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 4)
.AssignValue(sectionRow.EmployeeFirstLast)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Left)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 5)
.AssignValue(sectionRow.NumberOfDays)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 6)
.AssignValue(sectionRow.TotalMiles)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false);
excelSheet.Cell(rowStart, 7)
.AssignValue(sectionRow.Cost, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false)
.AddBottomBorder(XLBorderStyleValues.Thin);
//HACK: set cell in column 7's right border to be thin, not sure why to do so we must touch cell 7 + 8, but meh
excelSheet.Cell(rowStart, 8).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
rowStart++;
}
excelSheet.Cell(rowStart, 1)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 2)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 3)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 4)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 5)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 6).AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.SetFontStyle(x => x.Bold = true);
var totalCell = excelSheet.Cell(rowStart, 7)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
totalCell.SetFontStyle(x => x.Bold = true);
totalCell.FormulaA1 = $"SUM({columns[7]}{originalRowStart}:{columns[7]}{rowStart - 1})";
totalCell.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}
private void WriteArcFlashLabelSection(ReportDTO <DetailedExpenseForJobReportDTO> report, IXLWorksheet excelSheet)
{
var rowStart = 19 + report.Data.TimeAndExpense.Count() + report.Data.CompanyVehicle.Count() + report.Data.SubContractor.Count();
var originalRowStart = rowStart;
excelSheet.Row(rowStart).InsertRowsBelow(report.Data.ArcFlashLabel.Count());
foreach (var sectionRow in report.Data.ArcFlashLabel)
{
excelSheet.Cell(rowStart, 1)
.MergeWith(FluentCellStyle.CellToThe.right)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.SetDataType(XLDataType.DateTime)
.AssignValue(DateTimeWithZone.ConvertToEST(sectionRow.DateOfInvoice.UtcDateTime))
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 3)
.AssignValue(sectionRow.Quantity)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.SetFontStyle(f => f.Bold = false)
.AddRightBorder(XLBorderStyleValues.Dotted)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 4)
.AssignValue(sectionRow.LabelCost, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.SetFontStyle(f => f.Bold = false)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 5)
.AssignValue(sectionRow.PostageCost, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Dotted)
.SetFontStyle(f => f.Bold = false)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 6)
.AssignValue(sectionRow.TotalCost, dataFormatOverride: (XLDataType.Number, "$#,###,##0.00"))
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddLeftBorder(XLBorderStyleValues.Dotted)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetFontStyle(f => f.Bold = false)
.AddTopBorder(rowStart == originalRowStart ? XLBorderStyleValues.Medium : XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin);
excelSheet.Cell(rowStart, 7).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
rowStart++;
}
excelSheet.Cell(rowStart, 1)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 2)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray);
excelSheet.Cell(rowStart, 3).AssignValue("TOTAL")
.SetAlignHorizontal(XLAlignmentHorizontalValues.Right)
.AddRightBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddBottomBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.AddLeftBorder(XLBorderStyleValues.Thin, XLColor.Gray)
.SetFontStyle(x => x.Bold = true);
var labelTotal = excelSheet.Cell(rowStart, 4)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
labelTotal.SetFontStyle(x => x.Bold = true);
labelTotal.FormulaA1 = $"SUM({columns[4]}{originalRowStart}:{columns[4]}{rowStart - 1})";
labelTotal.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
var postageTotal = excelSheet.Cell(rowStart, 5)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
postageTotal.SetFontStyle(x => x.Bold = true);
postageTotal.FormulaA1 = $"SUM({columns[5]}{originalRowStart}:{columns[5]}{rowStart - 1})";
postageTotal.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
var totalCell = excelSheet.Cell(rowStart, 6)
.AddRightBorder(XLBorderStyleValues.Thin)
.SetAlignHorizontal(XLAlignmentHorizontalValues.Center)
.AddBottomBorder(XLBorderStyleValues.Thin)
.AddLeftBorder(XLBorderStyleValues.Thin)
.AddTopBorder(XLBorderStyleValues.Thin)
.AddRightBorder(XLBorderStyleValues.Thin);
totalCell.SetFontStyle(x => x.Bold = true);
totalCell.FormulaA1 = $"SUM({columns[6]}{originalRowStart}:{columns[6]}{rowStart - 1})";
totalCell.CellRight().AddLeftBorder(XLBorderStyleValues.Thin);
}