public byte[] Generate(KrogerUnloadingInvoiceModel invoice)
{
var template = new FileInfo(TemplatePath);
using (var package = new ExcelPackage(template, true))
{
var sheets = package.Workbook.Worksheets;
var blankUnloadActivitySheet = sheets.First(w => w.Name == "Blank Unload Activity");
createUnloadActivityWorksheets(invoice, sheets, blankUnloadActivitySheet);
createSummaryWorksheet(invoice, sheets);
createMainInvoiceWorksheet(invoice, sheets);
return(package.GetAsByteArray());
}
}
public FileContentResult KrogerInvoiceExcelReport(int locationId, DateTime periodFrom, DateTime periodTo, Boolean breakdownByDepartment)
{
var location = locationRepository.FindById(locationId);
if (location == null)
{
throw new Exception("Location not found");
}
var workOrders = workOrderRepository.GetLoadsForKrogerInvoice(location, periodFrom, periodTo);
var invoiceReportModel = new KrogerUnloadingInvoiceModel(location, workOrders, periodFrom, periodTo, breakdownByDepartment);
var fileBytes = krogerExcelGenerator.Generate(invoiceReportModel);
var invoiceFileName = $"Invoice{invoiceReportModel.HeaderInfo.InvoiceNumber}.xlsx";
return(File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", invoiceFileName));
}
private void createUnloadActivityWorksheets(KrogerUnloadingInvoiceModel invoice, ExcelWorksheets sheets, ExcelWorksheet blankUnloadActivitySheet)
{
// Create and populate "All Loads" worksheet:
var allLoadsSheet = sheets.Add("All Loads", blankUnloadActivitySheet);
populateUnloadActivitySheet(allLoadsSheet, invoice.AllWorkOrders);
// Create and populate "Street" worksheet:
var streetSheet = sheets.Add("Street", blankUnloadActivitySheet);
populateUnloadActivitySheet(streetSheet, invoice.PrepaidWorkOrders);
// Create and populate "Controlled" worksheet:
var controlledSheet = sheets.Add("Controlled", blankUnloadActivitySheet);
populateUnloadActivitySheet(controlledSheet, invoice.ControlledWorkOrders);
// Get the names of all unique department #'s in the collection of work orders:
var controlledWorkOrdersByRefNo = invoice.ControlledWorkOrdersByReferenceCode;
var allRefNames = controlledWorkOrdersByRefNo
.Select(g => g[0].ReferenceNumber)
.Where(s => !String.IsNullOrEmpty(s))
.ToList();
// Create workSheets for each unique Ref in the collection of invoices:
allRefNames.ForEach(n =>
{
var sheet = sheets.Add(String.Format("Controlled ({0})", n), blankUnloadActivitySheet);
var workOrdersFromThisRef = controlledWorkOrdersByRefNo.Find(l => l[0].ReferenceNumber == n);
populateUnloadActivitySheet(sheet, workOrdersFromThisRef);
});
if (invoice.BreakdownByDepartment)
{
// Create workSheets for each department
foreach (var gr in invoice.AllWorkOrders.GroupBy(x => new { x.LocationDepartmentId, x.DepartmentName }))
{
var departmentSheet = sheets.Add(gr.Key.DepartmentName, blankUnloadActivitySheet);
populateUnloadActivitySheet(departmentSheet, gr.ToList());
}
}
sheets.Delete(blankUnloadActivitySheet);
}
private void createMainInvoiceWorksheet(KrogerUnloadingInvoiceModel invoice, ExcelWorksheets sheets)
{
var sheet = sheets.First(w => w.Name == "Invoice");
var allOrders = invoice.AllWorkOrders;
var curRow = 21;
var subtotalRows = new List <int>();
if (invoice.BreakdownByDepartment)
{
// Create sub tables for each department
foreach (var group in invoice.AllWorkOrders.GroupBy(i => new { i.LocationDepartmentId, i.DepartmentName }))
{
curRow = buildMainInvoiceSubTable(invoice, sheet, curRow, group, group.Key.DepartmentName);
subtotalRows.Add(curRow);
curRow += 2;
}
}
else
{
curRow = buildMainInvoiceSubTable(invoice, sheet, curRow, invoice.AllWorkOrders);
subtotalRows.Add(curRow);
}
sheet.SetValue(13, 1, String.Format("{0} - {1}", invoice.HeaderInfo.Client, invoice.HeaderInfo.ClientLocation));
sheet.SetValue(14, 1, "Attn: " + invoice.HeaderInfo.NameForReceipts);
sheet.SetValue(15, 1, invoice.HeaderInfo.AddressLine1);
sheet.SetValue(16, 1, invoice.HeaderInfo.AddressLine2);
sheet.SetValue(17, 1, invoice.HeaderInfo.AddressLine3);
sheet.SetValue(12, 6, invoice.HeaderInfo.InvoiceNumber);
sheet.SetValue(12, 7, invoice.HeaderInfo.InvoiceDate.Value.ToString("MM/dd/yyyy"));
// Amount Due is calculated as sum of all subtotals
sheet.Cells[14, 6].Formula = String.Join("+", subtotalRows.Select(row => String.Format("G{0}", row)));
sheet.SetValue(14, 7, invoice.HeaderInfo.InvoiceTerms);
sheet.Cells[17, 6].Value = invoice.HeaderInfo.InvoiceAddress1;
sheet.Cells[18, 6].Value = invoice.HeaderInfo.InvoiceAddress2;
sheet.Calculate();
sheet.Select();
}
private void createSummaryWorksheet(KrogerUnloadingInvoiceModel invoice, ExcelWorksheets sheets)
{
var summarySheet = sheets.First(w => w.Name == "Summary");
summarySheet.Select();
if (invoice.BreakdownByDepartment)
{
var curRow = 2;
foreach (var group in invoice.AllWorkOrders.GroupBy(i => new { i.LocationDepartmentId, i.DepartmentName }))
{
curRow = buildSummarySubTable(invoice, summarySheet, curRow, group, group.Key.DepartmentName) + 3;
}
}
else
{
buildSummarySubTable(invoice, summarySheet, 2, invoice.AllWorkOrders);
}
}
private int buildMainInvoiceSubTable(KrogerUnloadingInvoiceModel invoice, ExcelWorksheet sheet, int startRow, IEnumerable <UnloadingWorkOrderModel> workOrders, string name = null)
{
// header
if (startRow > 23)
{
sheet.Cells[21, 1, 23, 7].Copy(sheet.Cells[startRow, 1, startRow + 2, 7]);
}
if (name != null)
{
sheet.Cells[startRow, 1].Value = name;
}
startRow += 3;
var weekBeginDate = (invoice.HeaderInfo.ServiceDateStart.HasValue)
? invoice.HeaderInfo.ServiceDateStart.Value.ToShortDateString()
: "";
var weekEndDate = (invoice.HeaderInfo.ServiceDateEnd.HasValue)
? invoice.HeaderInfo.ServiceDateEnd.Value.ToShortDateString()
: "";
var clientOrders = workOrdersChargedToClient(workOrders, invoice);
var carrierOrders = workOrdersChargedToCarrier(workOrders, invoice);
var loadLines = workOrdersByReferenceCode(clientOrders)
.Select(l => new MainInvoiceLoadItem
{
Location = String.Format("{0}, {1}", invoice.HeaderInfo.Client, invoice.HeaderInfo.LocationShortAddress),
BillTo = l.Key,
WeekStart = weekBeginDate,
WeekEnd = weekEndDate,
Amount = (Double)l.Sum(w => w.TotalAmount),
})
.OrderBy(i => i.BillTo)
.ToList();
var rebateLoadLines = workOrdersByReferenceCode(carrierOrders)
.Select(l => new MainInvoiceLoadItem
{
Location = String.Format("{0}, {1}", invoice.HeaderInfo.Client, invoice.HeaderInfo.LocationShortAddress),
BillTo = String.Format("{0} Rebate", l.Key),
WeekStart = weekBeginDate,
WeekEnd = weekEndDate,
Amount = -(Double)l.Sum(w => w.Rebate),
})
.OrderBy(i => i.BillTo)
.ToList();
var allLoadLines = loadLines.Concat(rebateLoadLines).ToList();
var currentRow = startRow;
foreach (var item in allLoadLines)
{
addMainInvoiceLine(sheet, currentRow, item);
++currentRow;
}
// Create Invoice Total Formula:
var invoiceLinesTotalRow = startRow + allLoadLines.Count + 1;
sheet.Cells[invoiceLinesTotalRow, 6].Value = "Subtotal:";
sheet.Cells[invoiceLinesTotalRow, 7].Formula = String.Format("SUM(G{0}:G{1})", startRow, invoiceLinesTotalRow - 2);
sheet.Cells[invoiceLinesTotalRow, 7].Style.Numberformat.Format = CurrencyFormatCode;
return(invoiceLinesTotalRow);
}
private int buildSummarySubTable(KrogerUnloadingInvoiceModel invoice, ExcelWorksheet sheet, int startRow, IEnumerable <UnloadingWorkOrderModel> workOrders, string name = null)
{
// header
if (startRow > 3)
{
sheet.Cells[2, 2, 2, 5].Copy(sheet.Cells[startRow, 2, startRow, 5]);
addSummaryLine(sheet);
}
if (name != null)
{
sheet.Cells[startRow, 2].Value = String.Format("Kroger {0} Activity", name);
}
sheet.SetValue(startRow, 5, (invoice.HeaderInfo.ServiceDateEnd.HasValue)
? invoice.HeaderInfo.ServiceDateEnd.Value.ToString("MM/dd/yyyy")
: string.Empty);
var loadsChargedToClient = workOrdersChargedToClient(workOrders, invoice);
var loadsChargedToCarrier = workOrdersChargedToCarrier(workOrders, invoice);
var totalLoadsLines = new List <SummaryLineItem>
{
new SummaryLineItem
{
Name = "TOTAL LOADS",
},
new SummaryLineItem
{
Name = "# OF LOADS CHARGED TO CLIENT",
Value = loadsChargedToClient.Count(),
EmphasizeValue = true
},
new SummaryLineItem
{
Name = "% OF LOADS CHARGED TO CLIENT",
IsFormula = true,
NumberFormat = "0.0%",
Value = string.Format("C{0}/C{1}", startRow + 3, startRow + 2)
},
new SummaryLineItem
{
Name = "# OF LOADS CHARGED TO CARRIER",
Value = loadsChargedToCarrier.Count(),
EmphasizeValue = true
},
new SummaryLineItem
{
Name = "% OF LOADS CHARGED TO CARRIER",
IsFormula = true,
NumberFormat = "0.0%",
Value = string.Format("C{0}/C{1}", startRow + 5, startRow + 2)
},
};
totalLoadsLines.ForEach(l => addSummaryLine(sheet, l));
addSummaryLine(sheet);
sheet.Cells[startRow + 2, 3].Formula = string.Format("C{0}+C{1}", startRow + 3, startRow + 5);
var unloadChargesLines = workOrdersByReferenceCode(loadsChargedToClient)
.Select(gr => new SummaryLineItem
{
Name = String.Format("Unload Charges - {0}", gr.Key).ToUpper(),
Value = gr.Sum(w => w.TotalAmount),
NumberFormat = CurrencyFormatCode,
EmphasizeValue = true
})
.ToList();
var rebateAmountLines = workOrdersByReferenceCode(loadsChargedToCarrier)
.Select(gr => new SummaryLineItem
{
Name = String.Format("Rebate Amt - {0}", gr.Key).ToUpper(),
Value = -gr.Sum(w => w.Rebate),
NumberFormat = CurrencyFormatCode,
EmphasizeValue = true
})
.ToList();
var unloadChargesRowNums = new List <int>();
var rebateAmountRowNums = new List <int>();
unloadChargesLines.ForEach(l =>
{
unloadChargesRowNums.Add(addSummaryLine(sheet, l));
addSummaryLine(sheet);
});
rebateAmountLines.ForEach(l =>
{
rebateAmountRowNums.Add(addSummaryLine(sheet, l));
addSummaryLine(sheet);
});
addSummaryLine(sheet);
int totalRowFirst = addSummaryLine(sheet, new SummaryLineItem
{
Name = "TOTAL REBATE AMT",
IsFormula = true,
Value = rebateAmountRowNums.Count > 0
? String.Join("+", rebateAmountRowNums.Select(s => String.Format("C{0}", s)))
: "0",
NumberFormat = CurrencyFormatCode,
});
addSummaryLine(sheet, new SummaryLineItem
{
Name = "TOTAL UNLOAD CHARGES",
IsFormula = true,
Value = unloadChargesRowNums.Count > 0
? String.Join("+", unloadChargesRowNums.Select(s => String.Format("C{0}", s)))
: "0",
NumberFormat = CurrencyFormatCode,
});
addSummaryLine(sheet, new SummaryLineItem
{
Name = "NET REBATE AMT",
Value = 0.00, // manually entered by user in Excel
NumberFormat = CurrencyFormatCode,
});
int lastRowNum = addSummaryLine(sheet, new SummaryLineItem
{
Name = "NET AMOUNT DUE",
IsFormula = true,
Value = String.Format("SUM(C{0}:C{1})", totalRowFirst, totalRowFirst + 2),
NumberFormat = CurrencyFormatCode,
EmphasizeName = true,
EmphasizeValue = true,
});
sheet.Cells[startRow + 2, 2, lastRowNum, 3].Style.Border.BorderAround(ExcelBorderStyle.Medium);
sheet.Cells[startRow + 2, 5, lastRowNum, 5].Style.Border.BorderAround(ExcelBorderStyle.Medium);
return(lastRowNum);
}
private IEnumerable <UnloadingWorkOrderModel> workOrdersChargedToCarrier(IEnumerable <UnloadingWorkOrderModel> workOrders, KrogerUnloadingInvoiceModel invoice)
{
return(workOrders.Where(i => i.BillTo != invoice.HeaderInfo.Client));
}
