// GET: sLeaveHistory public ActionResult Index() { var model = new List <Models.sLeaveModel>(); var claimsIdentity = User.Identity as System.Security.Claims.ClaimsIdentity; var c = claimsIdentity.FindFirst(System.Security.Claims.ClaimTypes.NameIdentifier); ViewBag.claim = c; string a = c.ToString(); a = a.Substring(a.Length - 5); //System.Diagnostics.Debug.WriteLine("id is:"+a + "."); var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string query = "Select * FROM dbo.leave,dbo.Leave_Type where Employee_ID = '" + a + "' AND Start_Date < GETDATE() AND leave.Leave_ID = Leave_Type.Leave_ID and leave.Status IN (2,3,4,5,7)"; using (var connection = new SqlConnection(connectionString)){ var command = new SqlCommand(query, connection); connection.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var leave = new Models.sLeaveModel(); string leave1 = (string)reader["Leave_Name"]; if (leave1.Equals("Annual")) { leave.leaveType = "Annual"; } if (leave1.Equals("Sick")) { leave.leaveType = "Sick"; } if (leave1.Equals("Compassionate")) { leave.leaveType = "Compassionate"; } if (leave1.Equals("Maternity")) { leave.leaveType = "Maternity"; } if (leave1.Equals("Short_Hours")) { leave.leaveType = "Short"; } if (leave1.Equals("Unpaid")) { leave.leaveType = "Unpaid"; } leave.startDate = (DateTime)reader["Start_Date"]; leave.endDate = (DateTime)reader["End_Date"]; leave.leaveDuration = (int)reader["Total_Leave_Days"]; if (!reader.IsDBNull(11)) { leave.shortStartTime = (TimeSpan)reader["Start_Hrs"]; } else { leave.shortStartTime = new TimeSpan(0, 0, 0, 0, 0); } if (!reader.IsDBNull(12)) { leave.shortEndTime = (TimeSpan)reader["End_Hrs"]; } else { leave.shortEndTime = new TimeSpan(0, 0, 0, 0, 0); } leave.leaveStatus = (int)reader["Status"]; model.Add(leave); } } connection.Close(); } return(View(model)); }
// GET: lmPendingApplications public ActionResult Index() { string userID = ""; List <sLeaveModel> RetrievedApplications = new List <sLeaveModel>(); //to get the id of the person logged in var claimsIdentity = User.Identity as System.Security.Claims.ClaimsIdentity; if (claimsIdentity != null) { var c = claimsIdentity.FindFirst(System.Security.Claims.ClaimTypes.NameIdentifier); if (c != null) { userID = c.Value; } } var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"]. ConnectionString; string queryString = "SELECT dbo.Leave.Leave_Application_ID, dbo.Leave.Employee_ID, " + "dbo.Leave.Start_Date, dbo.Leave.End_Date, dbo.Leave.Reporting_Back_Date, " + "dbo.Leave.Leave_ID, dbo.Leave.Contact_Outside_UAE, dbo.Leave.Comment, " + "dbo.Leave.Document, dbo.Leave.Flight_Ticket, dbo.Leave.Total_Leave_Days, " + "dbo.Leave.Start_Hrs, dbo.Leave.End_Hrs, dbo.Leave.Status, " + "dbo.Leave.LM_Comment, dbo.Leave.HR_Comment, dbo.Employee.First_Name, dbo.Employee.Last_Name " + "FROM dbo.Leave " + "FULL JOIN dbo.Employee " + "ON dbo.Leave.Employee_ID = dbo.Employee.Employee_ID " + "FULL JOIN dbo.Department " + "ON dbo.Employee.Department_ID = dbo.Department.Department_ID " + "WHERE dbo.Department.Line_Manager_ID = '" + userID + "' " + "AND dbo.Leave.Status = '0'" + "AND dbo.Leave.Leave_ID IS NOT NULL "; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(queryString, connection); connection.Open(); using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var leave = new Models.sLeaveModel(); if (reader["Leave_ID"] != DBNull.Value) { leave.leaveType = GetLeaveType((int)reader["Leave_ID"]); // Leave Type ID } var lidint = (int)reader["Leave_Application_ID"]; //Leave Application ID leave.leaveID = lidint.ToString(); leave.startDate = (DateTime)reader["Start_Date"]; string date1 = leave.startDate.ToString("yyyy-MM-dd"); //ViewBag.stDt = date1; leave.endDate = (DateTime)reader["End_Date"]; string date2 = leave.endDate.ToString("yyyy-MM-dd"); //ViewBag.enDt = date2; leave.leaveDuration = (int)reader["Total_Leave_Days"]; if (!reader.IsDBNull(11)) { leave.shortStartTime = (TimeSpan)reader["Start_Hrs"]; } else { leave.shortStartTime = new TimeSpan(0, 0, 0, 0, 0); } if (!reader.IsDBNull(12)) { leave.shortEndTime = (TimeSpan)reader["End_Hrs"]; } else { leave.shortEndTime = new TimeSpan(0, 0, 0, 0, 0); } leave.leaveStatus = (int)reader["Status"]; if (!reader.IsDBNull(15)) { leave.hrComment = (string)reader["HR_Comment"]; } else { leave.hrComment = ""; } if (!reader.IsDBNull(14)) { leave.lmComment = (string)reader["LM_Comment"]; } else { leave.hrComment = ""; } string empFirstName = (string)reader["First_Name"]; string empLastName = (string)reader["Last_Name"]; leave.staffName = empFirstName + " " + empLastName; int empID = (int)reader["Employee_ID"]; leave.employeeID = empID.ToString(); RetrievedApplications.Add(leave); } } } } /*Get the list of applications due for the line manager to approve*/ TempData["RetrievedApplications"] = RetrievedApplications; return(View(RetrievedApplications)); }
public ActionResult Create(Models.sLeaveModel model, HttpPostedFileBase file) { var claimsIdentity = User.Identity as System.Security.Claims.ClaimsIdentity; var c = claimsIdentity.FindFirst(System.Security.Claims.ClaimTypes.NameIdentifier); ViewBag.claim = c; string a = c.ToString(); a = a.Substring(a.Length - 5); string fileN = ""; System.Diagnostics.Debug.WriteLine("Entered post"); // Verify that the user selected a file if (file != null && file.ContentLength > 0) { // extract only the filename var fileName = Path.GetFileName(file.FileName); fileName = a + fileName; System.Diagnostics.Debug.WriteLine("filename is:" + fileName); System.Diagnostics.Debug.WriteLine("file Uploaded"); fileN = fileName; // store the file inside ~/App_Data/uploads folder var path = Path.Combine(Server.MapPath("~/App_Data"), fileName); file.SaveAs(path); } DateTime d1 = model.startDate; string stdate = d1.ToString("yyyy-MM-dd"); DateTime d2 = model.endDate; string endate = d2.ToString("yyyy-MM-dd"); System.Diagnostics.Debug.WriteLine("Endate is: " + endate); DateTime today = DateTime.Today; int result = DateTime.Compare(d2, d1); if (d1 < today) { ModelState.AddModelError("startDate", "The Start Date cannot be in the Past"); } if (result < 0) { ModelState.AddModelError("endDate", "The End Date cannot be earlier than the start date"); //Response.Write("<script> alert('End Date Cannot be earlier than the Start Date');location.href='Create'</script>"); System.Diagnostics.Debug.WriteLine("is earlier than"); //Redirect(Create.UrlReferrer.ToString()); } System.Diagnostics.Debug.WriteLine("Reached is valid"); System.Diagnostics.Debug.WriteLine("entered is valid"); int days = 0; if (result > 0 && !(model.leaveType.Equals("Maternity"))) { TimeSpan diff = d2 - d1; days = diff.Days; System.Diagnostics.Debug.WriteLine("Difference is" + days); for (var i = 0; i <= days; i++) { var testDate = d1.AddDays(i); switch (testDate.DayOfWeek) { case DayOfWeek.Saturday: case DayOfWeek.Friday: days--; System.Diagnostics.Debug.WriteLine("weekend reached"); //Console.WriteLine(testDate.ToShortDateString()); break; } } var connectionString1 = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string query1 = "Select * from dbo.Public_Holiday where Date between'" + stdate + "' AND '" + endate + "'"; using (var connection1 = new SqlConnection(connectionString1)) { var command1 = new SqlCommand(query1, connection1); connection1.Open(); using (var reader1 = command1.ExecuteReader()) { while (reader1.Read()) { DateTime day = (DateTime)reader1["Date"]; System.Diagnostics.Debug.WriteLine("Day of holiday is: " + day.DayOfWeek); if (day.DayOfWeek.Equals(DayOfWeek.Saturday) || day.DayOfWeek.Equals(DayOfWeek.Friday)) { System.Diagnostics.Debug.WriteLine("Holiday on weekend"); } else { days--; System.Diagnostics.Debug.WriteLine("Holiday Deducted"); } } } connection1.Close(); } System.Diagnostics.Debug.WriteLine("Not Maternity Days: " + days); } int leaveId = 0; if (model.leaveType.Equals("Annual")) { leaveId = 1; double difference = (d1 - today).TotalDays; if (difference < 30) { ModelState.AddModelError("startDate", "Leave must be applied 30 days in advance"); } } if (model.leaveType.Equals("Sick")) { leaveId = 3; } if (model.leaveType.Equals("Compassionate")) { leaveId = 4; } if (model.leaveType.Equals("Maternity")) { leaveId = 2; // ModelState.AddModelError("startDate", "Maternity is disabled;"); TimeSpan diff = d2 - d1; days = diff.Days; System.Diagnostics.Debug.WriteLine("Maternity Days: " + days); var connectionString1 = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string query1 = "Select * from dbo.Public_Holiday where Date between'" + stdate + "' AND '" + endate + "'"; using (var connection1 = new SqlConnection(connectionString1)) { var command1 = new SqlCommand(query1, connection1); connection1.Open(); using (var reader1 = command1.ExecuteReader()) { while (reader1.Read()) { days--; } } connection1.Close(); System.Diagnostics.Debug.WriteLine("Maternity Days: After holiday Deduction" + days); } } if (model.leaveType.Equals("Short")) { leaveId = 6; if (model.startDate != model.endDate) { ModelState.AddModelError("endDate", "For short leave, Start date and Return Date should be same!"); } } else { } if (model.leaveType.Equals("Unpaid")) { leaveId = 6; } string leaveid = leaveId.ToString(); if (ModelState.IsValid) { bool ticket = model.bookAirTicket; string abc = model.comments; var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string queryString = "Insert INTO Leave (Employee_ID,Document,Start_Date,End_Date,Reporting_Back_Date,Leave_ID,Contact_Outside_UAE,Comment,Flight_Ticket,Total_Leave_Days,Start_Hrs,End_Hrs,Status) VALUES ('" + a + "','" + fileN + "','" + stdate + "','" + endate + "','" + endate + "','" + leaveid + "','" + model.contactDetails + "','" + model.comments + "','" + ticket + "','" + days + "','" + model.shortStartTime + "','" + model.shortEndTime + "','0');"; var connection = new SqlConnection(connectionString); connection.Open(); // System.Diagnostics.Debug.WriteLine("connection opened"); var command = new SqlCommand(queryString, connection); command.ExecuteNonQuery(); // System.Diagnostics.Debug.WriteLine("connection executed"); Response.Write("<script> alert('Leave Application Submitted');location.href='Create'</script>"); connection.Close(); } return(Create()); }