// Workshop details: name, project, employee and list of attendees with // professional development hours. public ActionResult Details(int id = 0, int p = 1, int ps = 50) { var pageNumber = PagingHelpers.ValidatePageNumber(p); var pageSize = PagingHelpers.ValidatePageSize(ps); if (id == 0) { return(RedirectToAction(nameof(Index), new { p = pageNumber, ps = pageSize })); } // Store page number and size for return to list of workshops ViewData["PageNumber"] = pageNumber; ViewData["PageSize"] = pageSize; // Get the workshop and the employee/contact and project var workshopViewModel = _workshopService.Get(id); workshopViewModel.Employee = _employeeService.Get(workshopViewModel.EmployeeId); workshopViewModel.Project = _projectService.Get(workshopViewModel.ProjectId); workshopViewModel.ProviderCode = _providerCodeService.Get(workshopViewModel.ProviderCodeId); // Get sign-out sheet Id's workshopViewModel.SignOutSheets = _signOutSheetUploadService.GetIdsByWorkshop(id); // Get all attendees with professional development hours workshopViewModel.WorkshopAttendeeHours = _workshopService.GetWorkshopAttendeeHours(id); return(View(workshopViewModel)); }
// Default action : Get // Shows workshop details and list of attendees with hours // with form to add more attendees and hours // id is workshop id public ActionResult Index(int id = 0, int p = 1, int ps = 50) { StorePaging(p, ps); // Id is for workshop and must be > 0 if (id <= 0) { return(RedirectToAction("Index", "Workshops", new { p = PageNumber, ps = PageSize })); } // Get workshop with project, employee and provider code var model = _workshopService.GetDetails(id); // Get attendee hours for workshop model.WorkshopAttendeeHours = _workshopService.GetWorkshopAttendeeHours(id); return(View(model)); }
/// <summary> /// Creates an Excel spreadsheet with the name of the workshop /// and all attendees with PD hours listed. /// </summary> /// <param name="workshopId"></param> /// <returns></returns> public byte[] WorkshopAttendeeHours(int workshopId) { // Get workshop data and attendee hours from db var workshop = _workshopService.GetDetails(workshopId); var workshopAttendeeHours = _workshopService.GetWorkshopAttendeeHours(workshopId); using (var ms = new MemoryStream()) { using (var package = new ExcelPackage(ms)) { var worksheet = package.Workbook.Worksheets.Add("Sheet 1"); // Print workshop name, date and session id at top // Merge cells A-D on first row so workshop name is centered across data var workshopNameRange = "A1:E1"; var workshopDateRange = "A2:E2"; var workshopIdRange = "A3:E3"; worksheet.Cells[workshopNameRange].Merge = true; worksheet.Cells[workshopNameRange].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; worksheet.Cells[workshopNameRange].Style.Font.Bold = true; worksheet.Cells[workshopNameRange].Style.Font.Size = 18; worksheet.Cells[workshopNameRange].Value = workshop.WorkshopName; // Workshop Date worksheet.Cells[workshopDateRange].Merge = true; worksheet.Cells[workshopDateRange].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; worksheet.Cells[workshopDateRange].Style.Font.Bold = true; worksheet.Cells[workshopDateRange].Style.Font.Size = 16; worksheet.Cells[workshopDateRange].Value = workshop.TrainingDate.ToShortDateString(); // Workshop Session Id worksheet.Cells[workshopIdRange].Merge = true; worksheet.Cells[workshopIdRange].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; worksheet.Cells[workshopIdRange].Style.Font.Italic = true; worksheet.Cells[workshopIdRange].Value = $"{workshop.SessionIdentifier}-{workshop.ProviderCode.Code}"; // Column Headings worksheet.Cells["A4"].Value = "Name"; worksheet.Cells["A4"].Style.Font.Bold = true; worksheet.Cells["B4"].Value = "ID"; worksheet.Cells["B4"].Style.Font.Bold = true; worksheet.Cells["C4"].Value = "Hours"; worksheet.Cells["C4"].Style.Font.Bold = true; worksheet.Cells["D4"].Value = "Title"; worksheet.Cells["D4"].Style.Font.Bold = true; worksheet.Cells["E4"].Value = "Agency"; worksheet.Cells["E4"].Style.Font.Bold = true; // Print attendee name, ID, PD hours, title and agency // in columns A, B, C, D and E var row = 5; var col = 1; foreach (var attendee in workshopAttendeeHours) { // Reset column back to 1 (A) col = 1; // Attendee name worksheet.Cells[row, col].Value = attendee.FullName; // ID col++; worksheet.Cells[row, col].Value = attendee.CertId; // PD Hours col++; worksheet.Cells[row, col].Value = attendee.PDHours; // Job title col++; worksheet.Cells[row, col].Value = attendee.JobTitle; // Agency col++; worksheet.Cells[row, col].Value = attendee.AgencyName; // Update counter which is row number row++; } // Style PD Hours column for two decimal places worksheet.Column(3).Style.Numberformat.Format = "0.00"; worksheet.Column(3).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; // Set column widths // name worksheet.Column(1).Width = 20; // id worksheet.Column(2).Width = 10; // pd hours worksheet.Column(3).Width = 7; // job title worksheet.Column(4).Width = 20; // agency worksheet.Column(5).Width = 30; return(package.GetAsByteArray()); } } }