public static Payrate GetObject(Payrate o, FormCollection fc)
{
string staff_id = fc.Get("staff_id");
string paramMonth = fc.Get("month");
int month = CommonHelper.GetValue <int>(paramMonth);
string paramYear = fc.Get("year");
int year = CommonHelper.GetValue <int>(paramYear);
string paramPayRate = fc.Get("pay_rate");
double hourlyPayRate = CommonHelper.GetValue <double>(paramPayRate);
if (o == null)
{
o = new Payrate();
}
o.Staffid = staff_id;
o.Month = month;
o.Year = year;
o.Hourlypayrate = hourlyPayRate;
return(o);
}
public async Task <JsonResult> Create(FormCollection fc)
{
Payrate o = PayrateHelper.GetObject(null, fc);
Dictionary <string, object> err = null;
ISession se = NHibernateHelper.CurrentSession;
err = o.IsValid();
if (err == null)
{
await Task.Run(() =>
{
using (ITransaction tx = se.BeginTransaction())
{
se.SaveOrUpdate(o);
tx.Commit();
}
});
return(Json(new Dictionary <string, object>
{
{ "success", 1 },
{ "message", "Pay Rate was successfully added." }
},
JsonRequestBehavior.AllowGet));
}
else
{
return(Json(err, JsonRequestBehavior.AllowGet));
}
}
public async Task <IActionResult> Edit(Payrate payrate)
{
_context.Update(payrate);
await _context.SaveChangesAsync();
return(RedirectToAction("Index"));
}
public async Task <ActionResult> Edit(Guid id)
{
ViewBag.form_id = "edit-form";
ISession se = NHibernateHelper.CurrentSession;
Payrate o = await Task.Run(() => { return(se.Get <Payrate>(id)); });
return(View("_form", o));
}
private static async Task CreatePayRate(ISession se, string staffId)
{
Random r = new Random();
await Task.Run(() =>
{
for (int y = 2000; y <= DateTime.Now.Year; y++)
{
for (int m = 1; m <= 12; m++)
{
int x = r.Next(12, 18);
Payrate p = new Payrate {
Staffid = staffId, Month = m, Year = y, Hourlypayrate = x
};
se.SaveOrUpdate(p);
}
}
});
}
public static async Task <double> GetPayRate(Dictionary <string, object> filters)
{
ISession se = NHibernateHelper.CurrentSession;
ICriteria cr = se.CreateCriteria <Payrate>();
cr.Add(Restrictions.Eq("Staffid", filters["staff_id"]));
cr.Add(Restrictions.Eq("Year", filters["year"]));
cr.Add(Restrictions.Eq("Month", filters["month"]));
Payrate o = await Task.Run(() => { return(cr.List <Payrate>().SingleOrDefault()); });
double rate = 0;
if (o != null)
{
rate = o.Hourlypayrate;
}
return(rate);
}
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)));
}
