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))); }