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); }