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