public ActionResult <QuickSheet[]> Manager([FromBody] User client) { try { SqlConnection connection = new SqlConnection(SQLConnection); SqlCommand manager = new SqlCommand("TimesheetManager", connection); manager.Parameters.AddWithValue("@ManagerID", client.EmployeeID); manager.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader managerreader = manager.ExecuteReader(); QuickSheet[] package = new QuickSheet[5]; int i = 0; bool t2 = true; while (managerreader.Read() && (i < 5)) { if (t2) { package[i] = new QuickSheet { Firstname = managerreader.GetString(0), Lastname = managerreader.GetString(1), T2Status = managerreader.GetByte(5), Sheet2ID = managerreader.GetInt32(2), EndDate = managerreader.GetDateTime(4), }; t2 = false; } else { package[i].StartDate = managerreader.GetDateTime(3); package[i].Sheet1ID = managerreader.GetInt32(2); package[i].T1Status = managerreader.GetByte(5); t2 = true; i++; } } connection.Close(); Array.Resize(ref package, i); return(Ok(package)); } catch { return(BadRequest()); } }
public ActionResult <List <PayClass> > Payroll([FromBody] QuickSheet client) { try { if (!(Validatejwt(client.jwt, client.EmployeeID.ToString(), client.EmployeeType.ToString()) && client.EmployeeType == 8)) { return(Unauthorized()); } List <PayClass> paylist = new List <PayClass>(); SqlConnection connection = new SqlConnection(SQLConnection); SqlCommand getpaylst = new SqlCommand("TimesheetFilter", connection); getpaylst.Parameters.AddWithValue("@StartDate", client.StartDate); getpaylst.Parameters.AddWithValue("@EndDate", client.EndDate); getpaylst.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader payreader = getpaylst.ExecuteReader(); while (payreader.Read()) { paylist.Add(new PayClass() { EmployeeID = payreader.GetInt32(0), StudentF = payreader.GetString(2), StudentL = payreader.GetString(1), Manager = payreader.GetString(4) + ' ' + payreader.GetString(5), Regular = payreader.GetSqlDecimal(6).Value - payreader.GetSqlDecimal(7).Value, Overtime = payreader.GetSqlDecimal(7).Value, Total = payreader.GetSqlDecimal(6).Value, }); } connection.Close(); return(Ok(paylist)); } catch { return(BadRequest()); } }
public ActionResult <QuickSheet[]> Reject([FromBody] QuickSheet client) { try { if (!(Validatejwt(client.jwt, client.EmployeeID.ToString(), client.EmployeeType.ToString()) && client.EmployeeType == 2)) { return(Unauthorized()); } bool change1 = false; // assuming sheet1 is earlier then sheet2 bool change2 = false; //intialize return package SqlConnection connection = new SqlConnection(SQLConnection); SqlCommand manager = new SqlCommand("TimesheetManager", connection); manager.Parameters.AddWithValue("@ManagerID", client.EmployeeID); manager.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader managerreader = manager.ExecuteReader(); QuickSheet[] package = new QuickSheet[5]; int i = 0; bool t2 = true; while (managerreader.Read() && (i < 5)) { if (t2) { package[i] = new QuickSheet { Firstname = managerreader.GetString(0), Lastname = managerreader.GetString(1), T2Status = managerreader.GetByte(5), Sheet2ID = managerreader.GetInt32(2), EndDate = managerreader.GetDateTime(4), }; if (package[i].Sheet2ID == client.Sheet2ID && package[i].T2Status == 10 && client.EmployeeID == managerreader.GetInt32(7)) { change2 = true; package[i].T2Status = 5; } t2 = false; } else { package[i].StartDate = managerreader.GetDateTime(3); package[i].Sheet1ID = managerreader.GetInt32(2); package[i].T1Status = managerreader.GetByte(5); if (package[i].Sheet1ID == client.Sheet1ID && package[i].T1Status == 10 && client.EmployeeID == managerreader.GetInt32(7)) { change1 = true; package[i].T1Status = 5; } t2 = true; i++; } } connection.Close(); Array.Resize(ref package, i); if (!(change1 && change2)) { return(Forbid()); } if (change1) { SqlCommand reject = new SqlCommand("TimesheetReject", connection); reject.Parameters.AddWithValue("@TimesheetID", client.Sheet1ID); reject.Parameters.AddWithValue("@Comments", client.Comments); reject.Parameters.AddWithValue("@TStatus", 5); reject.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader execute = reject.ExecuteReader(); connection.Close(); } if (change2) { SqlCommand reject = new SqlCommand("TimesheetReject", connection); reject.Parameters.AddWithValue("@TimesheetID", client.Sheet2ID); reject.Parameters.AddWithValue("@Comments", client.Comments); reject.Parameters.AddWithValue("@TStatus", 5); reject.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader execute = reject.ExecuteReader(); connection.Close(); } return(Ok(package)); } catch { return(BadRequest()); } }
//[Route("User")] user initalization only, manager should use another get // no jwt public ActionResult <QuickSheet[]> Get([FromBody] User client) { try { //get calender weeks CultureInfo myCI = new CultureInfo("en-US"); Calendar myCal = myCI.Calendar; CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule; DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek; int week = myCal.GetWeekOfYear(DateTime.Now, myCWR, myFirstDOW); // # of weeks since year start // first sunday of the biweekly, occurs when week number is even DateTime biweekstart = (week % 2 == 0) ? DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek) : DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek - 7); DateTime packagestart = biweekstart.AddDays(-56); //first sunday of package DateTime packageend = biweekstart.AddDays(7); //Last Sunday of package //get a list of datetime already in database SqlConnection connection = new SqlConnection(SQLConnection); List <DateTime> sqlDatelst = new List <DateTime>(); SqlCommand getweeks = new SqlCommand("TimesheetInitQuery", connection); getweeks.Parameters.AddWithValue("@WeeksStart", packagestart); getweeks.Parameters.AddWithValue("@WeeksEnd", packageend); getweeks.Parameters.AddWithValue("@EmployeeID", client.EmployeeID); getweeks.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader readweeks = getweeks.ExecuteReader(); while (readweeks.Read()) { sqlDatelst.Add(readweeks.GetDateTime(0)); } connection.Close(); //filter those date that alrady exist List <DateTime> tracking = new List <DateTime>(); if (sqlDatelst != null) { while (packageend.Date >= packagestart.Date) { if (sqlDatelst.Any() && packageend.Date == sqlDatelst.First().Date) { sqlDatelst.Remove(sqlDatelst.First()); } else { tracking.Add(packageend); } packageend = packageend.AddDays(-7); } } //create all the nessary timesheets SqlCommand initnewtimesheet = new SqlCommand("TimesheetInitCreate", connection) { CommandType = CommandType.StoredProcedure }; initnewtimesheet.Parameters.AddWithValue("@EmployeeID", client.EmployeeID); initnewtimesheet.Parameters.AddWithValue("@ManagerID", client.ManagerID); initnewtimesheet.Parameters.AddWithValue("@day1", 0); initnewtimesheet.Parameters.AddWithValue("@day2", 0); initnewtimesheet.Parameters.AddWithValue("@day3", 0); initnewtimesheet.Parameters.AddWithValue("@day4", 0); initnewtimesheet.Parameters.AddWithValue("@day5", 0); initnewtimesheet.Parameters.AddWithValue("@day6", 0); initnewtimesheet.Parameters.AddWithValue("@day7", 0); initnewtimesheet.Parameters.AddWithValue("@Overtime", 0); initnewtimesheet.Parameters.AddWithValue("Tstatus", 0); initnewtimesheet.Parameters.Add("@StartDate", SqlDbType.Date); initnewtimesheet.Parameters.Add("@EndDate", SqlDbType.Date); foreach (var date in tracking) { connection.Open(); initnewtimesheet.Parameters["@StartDate"].Value = date; initnewtimesheet.Parameters["@EndDate"].Value = date.AddDays(6); SqlDataReader createtimesheet = initnewtimesheet.ExecuteReader(); connection.Close(); } //intalize the return pacakge QuickSheet[] package = new QuickSheet[5]; //now return with full package with timesheet id, dates, and status SqlCommand gettimesheet = new SqlCommand("TimesheetsGetQuick", connection); gettimesheet.Parameters.AddWithValue("@EmployeeID", client.EmployeeID); gettimesheet.Parameters.AddWithValue("@WeeksStart", packagestart); gettimesheet.Parameters.AddWithValue("@WeeksEnd", biweekstart.AddDays(7)); gettimesheet.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader sheetsreader = gettimesheet.ExecuteReader(); int i = 0; //index to iterate through package array Boolean t2 = true; // boolearn for timesheet 1 or 2, since sql is from latest to earliest, week2 will appear first while (sheetsreader.Read()) { if (t2) { package[i] = new QuickSheet { Sheet2ID = sheetsreader.GetInt32(0), EndDate = sheetsreader.GetDateTime(2), T2Status = sheetsreader.GetByte(3) }; t2 = false; } else { package[i].Sheet1ID = sheetsreader.GetInt32(0); package[i].StartDate = sheetsreader.GetDateTime(1); package[i].T1Status = sheetsreader.GetByte(3); t2 = true; i++; } } connection.Close(); return(Ok(package)); } catch { return(BadRequest()); } }
public ActionResult <QuickSheet[]> Submit([FromBody] QuickSheet client) { try { if (!(Validatejwt(client.jwt, client.EmployeeID.ToString(), client.EmployeeType.ToString()) && client.EmployeeType == 1)) { return(Unauthorized()); } //get calender weeks CultureInfo myCI = new CultureInfo("en-US"); Calendar myCal = myCI.Calendar; CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule; DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek; int week = myCal.GetWeekOfYear(DateTime.Now, myCWR, myFirstDOW); DateTime biweekstart = (week % 2 == 0) ? DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek) : DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek - 7); DateTime packagestart = biweekstart.AddDays(-56); //intalize the return pacakge QuickSheet[] package = new QuickSheet[5]; //now return with full package with timesheet id, dates, and status SqlConnection connection = new SqlConnection(SQLConnection); SqlCommand gettimesheet = new SqlCommand("TimesheetsGetQuick", connection); gettimesheet.Parameters.AddWithValue("@EmployeeID", client.EmployeeID); gettimesheet.Parameters.AddWithValue("@WeeksStart", packagestart); gettimesheet.Parameters.AddWithValue("@WeeksEnd", biweekstart.AddDays(7)); gettimesheet.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader sheetsreader = gettimesheet.ExecuteReader(); int i = 0; //index to iterate through package array Boolean t2 = true; // boolearn for timesheet 1 or 2, since sql is from latest to earliest, week2 will appear first bool change1 = false; // switch to update timesheet 1 bool change2 = false; // switch to update timesheet 2 while (sheetsreader.Read()) { if (t2) { package[i] = new QuickSheet { Sheet2ID = sheetsreader.GetInt32(0), EndDate = sheetsreader.GetDateTime(2), T2Status = sheetsreader.GetByte(3) }; if (package[i].T2Status < 10 && package[i].Sheet2ID == client.Timesheet2.TimesheetID && sheetsreader.GetInt32(4) == client.EmployeeID) { change2 = true; package[i].T2Status = 10;//preset the status } t2 = false; } else { package[i].Sheet1ID = sheetsreader.GetInt32(0); package[i].StartDate = sheetsreader.GetDateTime(1); package[i].T1Status = sheetsreader.GetByte(3); if (package[i].T1Status < 10 && package[i].Sheet1ID == client.Timesheet1.TimesheetID && sheetsreader.GetInt32(4) == client.EmployeeID) { change1 = true; package[i].T1Status = 10;//preset the status } t2 = true; i++; } } connection.Close(); Array.Resize(ref package, i); if (!(change1 && change2)) { return(Forbid()); } if (change1) { SqlCommand submit1 = new SqlCommand("TimesheetSubmit", connection); submit1.Parameters.AddWithValue("@TimesheetID", client.Timesheet1.TimesheetID); submit1.Parameters.AddWithValue("@Day1", client.Timesheet1.Day1); submit1.Parameters.AddWithValue("@Day2", client.Timesheet1.Day2); submit1.Parameters.AddWithValue("@Day3", client.Timesheet1.Day3); submit1.Parameters.AddWithValue("@Day4", client.Timesheet1.Day4); submit1.Parameters.AddWithValue("@Day5", client.Timesheet1.Day5); submit1.Parameters.AddWithValue("@Day6", client.Timesheet1.Day6); submit1.Parameters.AddWithValue("@Day7", client.Timesheet1.Day7); submit1.Parameters.AddWithValue("@Overtime", client.Timesheet1.Overtime); submit1.Parameters.AddWithValue("@Tstatus", 10); submit1.Parameters.AddWithValue("@Comments", client.Comments); submit1.Parameters.AddWithValue("@ManagerID", client.ManagerID); submit1.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader execute = submit1.ExecuteReader(); connection.Close(); } if (change2) { SqlCommand submit2 = new SqlCommand("TimesheetSubmit", connection); submit2.Parameters.AddWithValue("@TimesheetID", client.Timesheet2.TimesheetID); submit2.Parameters.AddWithValue("@Day1", client.Timesheet2.Day1); submit2.Parameters.AddWithValue("@Day2", client.Timesheet2.Day2); submit2.Parameters.AddWithValue("@Day3", client.Timesheet2.Day3); submit2.Parameters.AddWithValue("@Day4", client.Timesheet2.Day4); submit2.Parameters.AddWithValue("@Day5", client.Timesheet2.Day5); submit2.Parameters.AddWithValue("@Day6", client.Timesheet2.Day6); submit2.Parameters.AddWithValue("@Day7", client.Timesheet2.Day7); submit2.Parameters.AddWithValue("@Overtime", client.Timesheet2.Overtime); submit2.Parameters.AddWithValue("@Tstatus", 10); submit2.Parameters.AddWithValue("@Comments", client.Comments); submit2.Parameters.AddWithValue("@ManagerID", client.ManagerID); submit2.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader execute = submit2.ExecuteReader(); connection.Close(); } return(Ok(package)); } catch { return(BadRequest()); } }