private static void ExportReportFees(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
if (report.FeeReport.Count > 0)
{
var row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Event Fees", allStyles.Header2Style);
foreach (var feeType in report.FeeReport.GroupBy(x => x.FeeType).Select(x => x.Key))
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, feeType.ToString(), allStyles.Header3Style);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Cost", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 1, "Use Count", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 2, "Cost Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 3, "Discount Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 4, "Local Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 5, "State Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 6, "Actual Total", allStyles.TitleStyle);
foreach (var fee in report.FeeReport.Where(x => x.FeeType == feeType).OrderBy(x => x.Cost))
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, fee.Cost, allStyles.Currency);
ReportUtilities.CreateCell(row, 1, fee.UseCount, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 2, fee.CostTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 3, fee.DiscountTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 4, fee.LocalTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 5, fee.StateTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 6, fee.ActualTotal, allStyles.Currency);
}
row = sheet.CreateRow(rowNumber++);
}
}
}
private static void ExportReportCharges(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
if (report.ChargeReport.Count > 0)
{
var row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Event Charges", allStyles.Header2Style);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Name", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 1, "Cost Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 2, "Discount Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 3, "Local Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 4, "State Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 5, "Actual Total", allStyles.TitleStyle);
foreach (var charge in report.ChargeReport.OrderBy(x => x.Name))
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, charge.Name, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, charge.CostTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 2, charge.DiscountTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 3, charge.LocalTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 4, charge.StateTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 5, charge.ActualTotal, allStyles.Currency);
}
}
}
private FileResult ExportSummaryReport(vmEventManager_SummaryReport report, vmEventManager_EventFilter filter)
{
int rowNumber = 0;
NPOI.SS.UserModel.IRow row;
//Create new Excel workbook
var workbook = new HSSFWorkbook();
//Create new Excel sheet
var sheet = workbook.CreateSheet();
sheet.SetColumnWidth(0, 11 * 256);
sheet.SetColumnWidth(1, 44 * 256);
sheet.SetColumnWidth(2, 33 * 256);
sheet.SetColumnWidth(3, 11 * 256);
sheet.SetColumnWidth(4, 11 * 256);
var allStyles = ReportUtilities.CreateStyles(workbook);
ReportUtilities.ExportReportHeader("Summary Report", sheet, allStyles, ref rowNumber);
if (filter.EventId != null)
{
var thisEvent = _service.GetEventById(filter.EventId.Value);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, string.Format("{0} - {1}", thisEvent.GeneralLocality, thisEvent.EventDates.Min().DateOfEvent.ToShortDateString()), allStyles.Header2Style);
}
sheet.CreateRow(rowNumber++);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Location", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 1, "Wave Number", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 2, "Start Time", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 3, "Num Participants", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 4, "Active", allStyles.TitleStyle);
foreach (var wave in report.WaveData)
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, report.EventName, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, wave.WaveNumber, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 2, wave.StartTime.ToString("MM/dd/yyyy HH:mm"), allStyles.RightAligned);
ReportUtilities.CreateCell(row, 3, wave.NumParticipants, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 4, wave.Active, allStyles.LeftAligned);
}
//Write the workbook to a memory stream
var output = new MemoryStream();
workbook.Write(output);
//Return the result to the end user
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"SummaryExport.xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
private static void ExportReportValues(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
var row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Event Revenue", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, report.TotalRevenue, allStyles.Currency);
ReportUtilities.CreateCell(row, 2, "Available Spots", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 3, report.TotalSpots, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 4, "Fee Total", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 5, report.FeeValue, allStyles.Currency);
ReportUtilities.CreateCell(row, 6, "Charge Total", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 7, report.ChargeValue, allStyles.Currency);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Days count", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, report.DayCount, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 2, "Active Registrations", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 3, report.SpotsTaken, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 4, "Discount value", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 5, report.DiscountValue, allStyles.Currency);
ReportUtilities.CreateCell(row, 6, "Local Tax", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 7, report.ChargeLocalTaxValue, allStyles.Currency);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Event count", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, report.EventCount, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 2, "Spots Available", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 3, report.SpotsLeft, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 4, "Local Tax", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 5, report.LocalTaxValue, allStyles.Currency);
ReportUtilities.CreateCell(row, 6, "State Tax", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 7, report.ChargeStateTaxValue, allStyles.Currency);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Revenue Per Day", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, report.RevenuePerDay, allStyles.Currency);
ReportUtilities.CreateCell(row, 2, "Registrations Per Day", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 3, report.RegPerDay, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 4, "State Tax", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 5, report.StateTaxValue, allStyles.Currency);
ReportUtilities.CreateCell(row, 6, "Total Revenue", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 7, report.ChargeActualRevenue, allStyles.Currency);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Revenue Per Event", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, report.RevenuePerEvent, allStyles.Currency);
row.CreateCell(2);
row.CreateCell(3);
ReportUtilities.CreateCell(row, 4, "Total Revenue", allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 5, report.FeeActualRevenue, allStyles.Currency);
row.CreateCell(6);
row.CreateCell(7);
row = sheet.CreateRow(rowNumber++);
}
private static void ExportReportTshirts(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
if (report.TShirtSizes != null && report.TShirtSizes.Count > 0)
{
var row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "T-Shirts", allStyles.Header3Style);
foreach (var size in report.TShirtSizes)
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, size.Keys.ElementAt(0).ToString(), allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, size.Values.ElementAt(0), allStyles.RightAligned);
}
row = sheet.CreateRow(rowNumber++);
}
}
private void ExportReportSubheader(NPOI.SS.UserModel.ISheet sheet, vmAdmin_PerformanceFilter filter, StyleContainer allStyles, ref int rowNumber)
{
var row = sheet.CreateRow(rowNumber++);
if (filter.EventId.HasValue && filter.EventId.Value > 0)
{
var thisEvent = _service.GetEventById(filter.EventId.Value);
ReportUtilities.CreateCell(row, 0, string.Format("{0} - {1}", thisEvent.GeneralLocality, thisEvent.EventDates.Min().DateOfEvent.ToShortDateString()), allStyles.Header2Style);
row = sheet.CreateRow(rowNumber++);
}
else
{
if (filter.startDate.HasValue)
{
var value = string.Format("Events from {0} to {1}", filter.startDate.Value.ToShortDateString(), filter.endDate.Value.ToShortDateString());
ReportUtilities.CreateCell(row, 0, value, allStyles.Header2Style);
row = sheet.CreateRow(rowNumber++);
}
}
}
private FileResult ExportDetailedReport(vmEventManager_DetailedReport report, vmEventManager_EventFilter filter)
{
int rowNumber = 0;
NPOI.SS.UserModel.IRow row;
//Create new Excel workbook
var workbook = new HSSFWorkbook();
//Create new Excel sheet
int col = 0;
var sheet = workbook.CreateSheet();
sheet.SetColumnWidth(col++, 22 * 256);
sheet.SetColumnWidth(col++, 22 * 256);
sheet.SetColumnWidth(col++, 33 * 256);
sheet.SetColumnWidth(col++, 33 * 256);
sheet.SetColumnWidth(col++, 33 * 256);
sheet.SetColumnWidth(col++, 22 * 256);
sheet.SetColumnWidth(col++, 44 * 256);
sheet.SetColumnWidth(col++, 44 * 256);
sheet.SetColumnWidth(col++, 11 * 256);
sheet.SetColumnWidth(col++, 15 * 256);
sheet.SetColumnWidth(col++, 15 * 256);
sheet.SetColumnWidth(col++, 18 * 256);
var allStyles = ReportUtilities.CreateStyles(workbook);
ReportUtilities.ExportReportHeader("Detailed Report", sheet, allStyles, ref rowNumber);
if (filter.EventId != null)
{
var thisEvent = _service.GetEventById(filter.EventId.Value);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, string.Format("{0} - {1}", thisEvent.GeneralLocality, thisEvent.EventDates.Min().DateOfEvent.ToShortDateString()), allStyles.Header2Style);
}
sheet.CreateRow(rowNumber++);
row = sheet.CreateRow(rowNumber++);
col = 0;
ReportUtilities.CreateCell(row, col++, "Location", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Start Time", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "First Name", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Last Name", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Emergency Contact", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Emergency Phone", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Medical Information", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Special Needs", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Shirt Size", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Agreed Legal", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "Agreed Trademark", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, col++, "ConfirmationCode", allStyles.TitleStyle);
foreach (var wave in report.WaveData)
{
col = 0;
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, col++, report.EventName, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.StartTime.ToString("MM/dd/yyyy HH:mm"), allStyles.RightAligned);
ReportUtilities.CreateCell(row, col++, wave.Firstname, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.Lastname, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.EmergencyContact, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.EmergencyPhone, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.MedicalInformation, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.SpecialNeeds, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.ShirtSize, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.AgreedLegal, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.AgreedTrademark, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, col++, wave.ConfirmationCode, allStyles.LeftAligned);
}
//Write the workbook to a memory stream
var output = new MemoryStream();
workbook.Write(output);
//Return the result to the end user
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"DetailedExport.xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}