public FileResult GetReport(GetReportVM filters)
{
var templateDirectory = new DirectoryInfo(Server.MapPath("~/Content/uploaded/templates"));
Response.AddHeader("Content-Disposition", String.Format("attachment; filename={0}.xlsx", filters.Title));
return(new FileContentResult(ExcelWriter.GenerateReport(templateDirectory, filters, db),
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
public VisualizatorHomeVM()
{
GetReport = new GetReportVM();
}
public AdministratorHomeVM()
{
GetReport = new GetReportVM();
}
public static byte[] GenerateReport(DirectoryInfo templateDir, GetReportVM filters, PortalContext db)
{
var title = filters.Title;
var startDate = filters.StartDate;
var endDate = filters.EndDate;
var manager = filters.Manager;
var templatePath = templateDir.FullName + @"\BasicReportTemplate.xlsx";
var template = new FileInfo(templatePath);
if (!template.Exists)
{
throw new Exception("Template file does not exist! Should exist at: " + templatePath);
}
using (var xlPackage = new ExcelPackage(new FileInfo(title), template))
{
// get handle to the existing worksheet
var worksheet = xlPackage.Workbook.Worksheets["Relatório"];
if (worksheet != null)
{
const int startRow = 6;
var row = startRow;
var freelancers = db.Freelancers.Where(f => f.RefundProfile.User.Active).ToList();
if (manager != null)
{
freelancers = freelancers.Where(f => manager.Freelancers.Any(mf => mf.UserID == f.UserID)).ToList();
}
if (freelancers.Count > 2)
{
worksheet.InsertRow(row + 1, freelancers.Count - 2);
}
worksheet.Cells[2, 1].Value = title;
foreach (var freelancer in freelancers)
{
var visits = freelancer.Visits.Where(v => v.Date >= startDate && v.Date <= endDate).ToList();
var events = freelancer.Events.Where(e => e.StartDate >= startDate && e.StartDate <= endDate).ToList();
var monthlies = freelancer.Monthlies.Where(m => (m.MonthStart >= startDate && m.MonthStart <= endDate) || (m.MonthEnd >= startDate && m.MonthEnd <= endDate)).ToList();
var visitsRefundItems = visits.SelectMany(v => v.Refund.RefundItems.Where(ri => ri.Status == RefundItemStatus.PAID || ri.Status == RefundItemStatus.ACCEPTED)).ToList();
var eventsRefundItems = events.SelectMany(v => v.Refund.RefundItems.Where(ri => ri.Status == RefundItemStatus.PAID || ri.Status == RefundItemStatus.ACCEPTED)).ToList();
var monthliesRefundItems = monthlies.SelectMany(v => v.Refund.RefundItems.Where(ri => ri.Status == RefundItemStatus.PAID || ri.Status == RefundItemStatus.ACCEPTED)).ToList();
var refundItems = visitsRefundItems.Concat(eventsRefundItems).ToList();
var allItems = refundItems.Concat(monthliesRefundItems).ToList();
worksheet.Cells[row, 1].Value = freelancer.RefundProfile.User.PersonalInfo.Name;
worksheet.Cells[row, 2].Value = visits.Count;
worksheet.Cells[row, 3].Value = events.Count;
worksheet.Cells[row, 4].Value = monthlies.Count;
worksheet.Cells[row, 5].Value = allItems.Sum(ri => ri.Value);
worksheet.Cells[row, 6].Value = monthliesRefundItems.Sum(ri => ri.Value);
worksheet.Cells[row, 7].Value = refundItems.Sum(ri => ri.Value);
worksheet.Cells[row, 8].Value = refundItems.Where(ri => ri.SubCategory == RefundItemSubCategory.TRANSPORTATION_KM).Sum(ri => ri.Value);
worksheet.Cells[row, 9].Value = refundItems.Where(ri => ri.SubCategory == RefundItemSubCategory.TRANSPORTATION_BUS_TICKET).Sum(ri => ri.Value);
worksheet.Cells[row, 10].Value = refundItems.Where(ri => ri.SubCategory == RefundItemSubCategory.TRANSPORTATION_TAXI).Sum(ri => ri.Value);
worksheet.Cells[row, 11].Value = refundItems.Where(ri => ri.SubCategory == RefundItemSubCategory.TRANSPORTATION_TOOL).Sum(ri => ri.Value);
worksheet.Cells[row, 12].Value = refundItems.Where(ri => ri.Category == RefundItemCategory.TRANSPORTATION).Sum(ri => ri.Value);
worksheet.Cells[row, 13].Value = refundItems.Where(ri => ri.SubCategory == RefundItemSubCategory.MEAL_LUNCH).Sum(ri => ri.Value);
worksheet.Cells[row, 14].Value = refundItems.Where(ri => ri.SubCategory == RefundItemSubCategory.MEAL_DINNER).Sum(ri => ri.Value);
worksheet.Cells[row, 15].Value = refundItems.Where(ri => ri.Category == RefundItemCategory.MEAL).Sum(ri => ri.Value);
worksheet.Cells[row, 16].Value = refundItems.Where(ri => ri.Category == RefundItemCategory.XEROX_COPY).Sum(ri => ri.Value);
worksheet.Cells[row, 17].Value = refundItems.Where(ri => ri.Category == RefundItemCategory.MAIL_SEDEX).Sum(ri => ri.Value);
worksheet.Cells[row, 18].Value = refundItems.Where(ri => ri.Category == RefundItemCategory.LUGGAGE).Sum(ri => ri.Value);
worksheet.Cells[row, 19].Value = refundItems.Where(ri => ri.Category == RefundItemCategory.OTHER).Sum(ri => ri.Value);
row++;
}
//Sets the styles for the new rows
for (var iCol = 1; iCol <= 19; iCol++)
{
var originalCell = worksheet.Cells[startRow, iCol];
for (var iRow = startRow; iRow < row; iRow++)
{
var cell = worksheet.Cells[iRow, iCol];
cell.StyleID = originalCell.StyleID;
}
}
worksheet.HeaderFooter.OddHeader.CenteredText = "Relatório Mensal Wella Educação";
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Página {0} de {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
}
// set some core property values
xlPackage.Workbook.Properties.Title = title;
xlPackage.Workbook.Properties.Author = "Sistema de Reembolso Wella Mates";
return(xlPackage.GetAsByteArray());
}
}
public ManagerHomeVM()
{
GetReport = new GetReportVM();
}
