public async Task <IActionResult> GeneratePayratesFile(UnitTutorsViewModel model)
{
// set file name
var fileName = "StaffPayrates_" + model.UnitCode + ".xlsx";
var filePath = Path.Combine(_hostingEnvironment.WebRootPath, fileName);
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
// create woorkbookpart
WorkbookPart workbookPart = spreadSheetDocument.AddWorkbookPart();
spreadSheetDocument.WorkbookPart.Workbook = new Workbook();
spreadSheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
// styles part
WorkbookStylesPart stylesPart = spreadSheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
//create worksheetPart Teaching events
WorksheetPart worksheetPartTeachingEvents = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetDataTeachingEvents = new SheetData();
worksheetPartTeachingEvents.Worksheet = new Worksheet();//sheetDataTeachingEvents);
// set column widths
Columns colsTeachingEvents = TeachingEventsColumns();
worksheetPartTeachingEvents.Worksheet.Append(colsTeachingEvents);
worksheetPartTeachingEvents.Worksheet.Append(sheetDataTeachingEvents);
// create merge cells for O1-T1
MergeCells mergeCells = new MergeCells();
mergeCells.Append(new MergeCell()
{
Reference = new StringValue("O1:T1")
});
worksheetPartTeachingEvents.Worksheet.Append(mergeCells);
// create sheet
Sheets sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheetTeachingEvents = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPartTeachingEvents),
SheetId = 1,
Name = "1. Teaching Events"
};
sheets.Append(sheetTeachingEvents);
// create worksheetPart New or Edited Staff Details
WorksheetPart worksheetPartNewStaff = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetDataNewStaff = new SheetData();
worksheetPartNewStaff.Worksheet = new Worksheet();
/**Columns colsNewStaff = NewStaffColumns();
* worksheetPartNewStaff.Worksheet.Append(colsNewStaff);
* worksheetPartNewStaff.Worksheet.Append(sheetDataNewStaff);**/
// create merge cells for O1-T1
MergeCells mergeCellsNewStaff = new MergeCells();
mergeCellsNewStaff.Append(new MergeCell()
{
Reference = new StringValue("A1:G1")
});
worksheetPartNewStaff.Worksheet.Append(mergeCellsNewStaff);
// create sheet
Sheet sheetNewStaff = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPartNewStaff),
SheetId = 2,
Name = "2. New or Edited Staff Details"
};
sheets.Append(sheetNewStaff);
// header row Teaching Events
Row headers = new Row();
// column A
headers.Append(StringCell("Subject Code", 3));
// column B
headers.Append(StringCell("Class Type", 3));
// column C (blank column), need to change format
headers.Append(EmptyCell(6));
// column D
headers.Append(StringCell("Day of week", 3));
// column E
headers.Append(StringCell("Start Time", 3));
// column F
headers.Append(StringCell("Duration", 3));
// column G (blank column)
headers.Append(EmptyCell(6));
// column H
headers.Append(StringCell("Staff Name", 3));
// column I (blank column)
headers.Append(EmptyCell(6));
// column J
headers.Append(StringCell("Weeks", 3));
// column K
headers.Append(StringCell("Pay rate", 3));// payrate code
// column L (blank column)
headers.Append(EmptyCell(6));
// column M
headers.Append(StringCell("Staff Status", 3));
// column N (blank column)
headers.Append(EmptyCell(6));
// column O
headers.Append(StringCell("FACULTY SUPPORT STAFF USE ONLY - PLEASE REFRAIN FROM AMENDING COLUMNS O-T", 7));
// add the headers to the sheet
sheetDataTeachingEvents.AppendChild(headers);
List <int> blackCol = new List <int> {
3, 7, 9, 12, 14
};
// Row 2 displays headers for faculty staff use
Row row2 = new Row();
for (int i = 0; i < 14; i++) // column A-N
{
if (blackCol.Contains(i + 1))
{
row2.Append(EmptyCell(6));
}
else
{
row2.Append(EmptyCell(3));
}
}
// column O
row2.Append(StringCell("Pay Rate", 1));
// column P
row2.Append(StringCell("No. of sessions", 1));
// column Q
row2.Append(StringCell("Hours", 1));
// column R
row2.Append(StringCell("Cost", 1));
// column S
row2.Append(StringCell("Cost inc. on-costs", 1));
// column T
row2.Append(StringCell("Notes/Comments", 1));
// add the row to the sheet
sheetDataTeachingEvents.AppendChild(row2);
// warning row for NewStaff sheet
Row warningRow = new Row();
warningRow.Append(StringCell("**ONLY COMPLETE THIS SECTION FOR NEW SESSIONAL STAFF WHO ARE NOT ON SWINBURNE'S PAYROLL", 13));
sheetDataNewStaff.AppendChild(warningRow);
// Header data for NewStaff sheet
Row headersNewStaff = new Row();
// Column A
headersNewStaff.Append(StringCell("Surname", 4));
// Column B
headersNewStaff.Append(StringCell("FirstName", 4));
// Column C
headersNewStaff.Append(StringCell("Email", 4));
// Column D
headersNewStaff.Append(StringCell("Address", 4));
// Column E
headersNewStaff.Append(StringCell("Suburb", 4));
// Column F
headersNewStaff.Append(StringCell("Post Code", 4));
// Column G
headersNewStaff.Append(StringCell("Home Phone", 4));
// Column H
headersNewStaff.Append(StringCell("Work Phone", 4));
// Column I
headersNewStaff.Append(StringCell("Mobile Phone", 4));
sheetDataNewStaff.AppendChild(headersNewStaff);
foreach (TutorPayrateViewModel tutor in model.Tutors.Values)
{
// get weeks that tutor is teaching
List <int> teachingWeeks = new List <int>();
foreach (var kv in tutor.Weeks)
{
if (kv.Value)
{
teachingWeeks.Add(kv.Key);
}
}
Row row = new Row();
// column A
row.Append(StringCell(model.UnitCode, 2));
// column B
row.Append(StringCell(tutor.ClassType, 2));
// column C (blank column)
row.Append(EmptyCell(6));
// column D
row.Append(StringCell(tutor.ClassDayOfWeek, 2));
// column E
row.Append(StringCell(tutor.ClassStartTime.ToString(@"hh\:mm"), 2));
// column F
row.Append(NumberCell(tutor.ClassDuration.TotalMinutes.ToString(), 2));
// column G (blank column)
row.Append(EmptyCell(6));
// column H
row.Append(StringCell(tutor.TutorFullName, 2));
// column I (blank column)
row.Append(EmptyCell(6));
// column J
row.Append(StringCell(string.Join(',', teachingWeeks.ToArray()), 2));
// column K
row.Append(StringCell(tutor.PayrateCode, 2));
// column L (blank column)
row.Append(EmptyCell(6));
// column M
row.Append(StringCell(tutor.StaffStatus, 2));
// column N (blank column)
row.Append(EmptyCell(6));
// column O
Payrate payrate = _context.Payrate.Where(c => c.Code == tutor.PayrateCode).FirstOrDefault();
row.Append(NumberCell(payrate.Rate.ToString(), 11));
// add formating to next 5 rows
for (int i = 0; i < 5; i++)
{
row.Append(EmptyCell(8));
}
sheetDataTeachingEvents.AppendChild(row);
// new staff
if (tutor.NewStaff)
{
Row rowNewStaff = new Row();
// Column A
rowNewStaff.Append(StringCell(tutor.TutorLastName));
// Column B
rowNewStaff.Append(StringCell(tutor.TutorFirstName));
// Column C
rowNewStaff.Append(StringCell(tutor.TutorEmail));
// Column D
rowNewStaff.Append(StringCell(tutor.TutorAddress));
// Column E
rowNewStaff.Append(StringCell(tutor.TutorSuburb));
// Column F
rowNewStaff.Append(StringCell(tutor.TutorPostCode));
// Column G
rowNewStaff.Append(EmptyCell());
// Column H
rowNewStaff.Append(EmptyCell());
// Column I
rowNewStaff.Append(StringCell(tutor.TutorMobileNumber));
sheetDataNewStaff.AppendChild(rowNewStaff);
}
}
// empty row between staff details and total stuff
Row emptyRow = new Row();
sheetDataTeachingEvents.AppendChild(emptyRow);
Row TotalRow = new Row();
for (int i = 0; i < 14; i++) // column A-N
{
TotalRow.Append(EmptyCell());
}
// column O
TotalRow.Append(StringCell("TOTAL", 9));
// column P
Cell noSessions = NumberCell("0.00", 10);
int totalStaff = model.Tutors.Count;
CellFormula noSessionsFormula = new CellFormula();
noSessionsFormula.Text = "SUM(P3:P" + (3 + totalStaff - 1) + ")";
noSessions.CellFormula = noSessionsFormula;
TotalRow.Append(noSessions);
// column Q
Cell hours = NumberCell("0.00", 10);
CellFormula hoursFormula = new CellFormula();
hoursFormula.Text = "SUM(Q3:Q" + (3 + totalStaff - 1) + ")";
hours.CellFormula = hoursFormula;
TotalRow.Append(hours);
// column R
Cell cost = NumberCell("0.00", 12);
CellFormula costFormula = new CellFormula();
costFormula.Text = "SUM(R3:R" + (3 + totalStaff - 1) + ")";
cost.CellFormula = costFormula;
TotalRow.Append(cost);
// column S
Cell costInc = NumberCell("0.00", 12);
CellFormula costIncFormula = new CellFormula();
costIncFormula.Text = "SUM(S3:S" + (3 + totalStaff - 1) + ")";
costInc.CellFormula = costIncFormula;
TotalRow.Append(costInc);
sheetDataTeachingEvents.AppendChild(TotalRow);
}
// Download the created file
if (fileName != null)
{
var memory = new MemoryStream();
using (var stream = new FileStream(filePath, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
System.IO.File.Delete(filePath);
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
return(RedirectToAction(nameof(Index)));
}
public IActionResult TutorPayrates(UnitTutorsViewModel model)
{
ViewData["Payrates"] = new SelectList(_context.Payrate, "Code", "Code");
return(View(model));
}
public IActionResult TutorPayratesConfirm(UnitTutorsViewModel model)
{
return(View(model));
}
public async Task <IActionResult> TutorPayrates(int?id)
{
if (id == null)
{
return(NotFound());
}
// get selected unit details
var unit = await _context.Unit.FindAsync(id);
// get all classes with an approved tutor
var classes = await _context.Class.Include(c => c.TutorAllocatedNavigation).Where(Class => Class.UnitId == id && Class.Allocated && Class.Approved).ToListAsync();
var payrates = await _context.Payrate.ToListAsync();
// payrates dictionary
//Dictionary<string, Payrate> payrateValues = new Dictionary<string, Payrate>();
//foreach (Payrate p in payrates)
//{
// payrateValues.Add(p.Code, p);
//}
if (unit == null)
{
return(NotFound());
}
var model = new UnitTutorsViewModel
{
UnitCode = unit.UnitCode,
Tutors = new Dictionary <int, TutorPayrateViewModel>()
};
foreach (Class c in classes)
{
// get calendar. Used to get week # of year
//https://docs.microsoft.com/en-us/dotnet/api/system.globalization.calendar.getweekofyear?redirectedfrom=MSDN&view=netframework-4.8#System_Globalization_Calendar_GetWeekOfYear_System_DateTime_System_Globalization_CalendarWeekRule_System_DayOfWeek_
CultureInfo myCI = new CultureInfo("en-AU");
Calendar myCal = myCI.Calendar;
CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule;
DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek;
int startWeek = myCal.GetWeekOfYear(c.StartDate, myCWR, myFirstDOW);
int endWeek = startWeek + 12; // data does not have end week, just assuming 12 weeks of classes + mid semester break
TutorPayrateViewModel temp = new TutorPayrateViewModel
{
Weeks = new Dictionary <int, bool>(),
ClassStartDate = c.DateOnlyString,
ClassStartTime = c.StartTimeScheduled,
ClassDuration = c.EndTimeScheduled - c.StartTimeScheduled,
ClassType = c.ClassType,
ClassDayOfWeek = c.DayOfWeek,
TutorFullName = c.TutorAllocatedNavigation.LastName + ", " + c.TutorAllocatedNavigation.FirstName,
NewStaff = false,
TutorId = c.TutorAllocated,
TutorFirstName = c.TutorAllocatedNavigation.FirstName,
TutorLastName = c.TutorAllocatedNavigation.LastName,
TutorEmail = c.TutorAllocatedNavigation.Email,
TutorAddress = c.TutorAllocatedNavigation.Street,
TutorSuburb = c.TutorAllocatedNavigation.City,
TutorPostCode = c.TutorAllocatedNavigation.PostalCode,
TutorMobileNumber = c.TutorAllocatedNavigation.PhoneNumber,
};
for (int i = startWeek; i <= endWeek; i++)
{
temp.Weeks.Add(i, true); // assume all weeks are teaching weeks for now, allow user to modify with checkboxes
}
if (c.TutorAllocatedNavigation.Qualification.ToString() == "PhD")
{
temp.StaffStatus = "Sessional with PhD";
}
else
{
temp.StaffStatus = "Sessional without PhD";
}
if (c.ClassType.Contains("Lecture"))
{
// not quite sure what requirements are for other lecture payrates, setting to lecturing repeat for now
temp.PayrateCode = "LD";
}
else if (c.ClassType.Contains("Tutorial") || c.ClassType.Contains("Workshop") || c.ClassType.Contains("Practical") || c.ClassType.Contains("Demonstration") || c.ClassType.Contains("Lab"))
{ // may have missed some class types
if (temp.StaffStatus.Equals("Sessional with PhD"))
{
temp.PayrateCode = "TH";
}
else
{
temp.PayrateCode = "TF";
}
}
model.Tutors.Add(c.Id, temp);
}
ViewData["Payrates"] = new SelectList(_context.Payrate, "Code", "Code");
return(View(model));
}
