public List <AllocatedLeave> RemaingCasualLeave(int employeeId)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand(@"SELECT SUM(NoOfLeaveTaken) as number
FROM LeaveTaken
WHERE EmployeeId ='" + employeeId + "' and LeaveTypeId = '" + 2 + "'", con);
con.Open();
SqlDataReader dr = com.ExecuteReader();
List <AllocatedLeave> totalLeave = new List <AllocatedLeave>();
while (dr.Read())
{
AllocatedLeave leave = new AllocatedLeave();
if (!object.ReferenceEquals(dr["number"], DBNull.Value))
{
leave.NoOfAllocatedLeave = Convert.ToInt32(dr["number"]);
//exception
}
else
{
leave.NoOfAllocatedLeave = 0;
}
totalLeave.Add(leave);
}
con.Close();
return(totalLeave.ToList());
}
public ActionResult AllocatedLeave(AllocatedLeave allocateClassroom)
{
if (eManager.IsLeaveAllocated(allocateClassroom))
{
ViewBag.Msg = "Leave allocated alrady.";
ViewBag.Employees = eManager.EmployeeList();
ViewBag.LeaveTypes = eManager.LeaveTypes();
}
else
{
ViewBag.Msg = eManager.AllocatedLeave(allocateClassroom);
ViewBag.Employees = eManager.EmployeeList();
ViewBag.LeaveTypes = eManager.LeaveTypes();
}
return(View());
}
public List <AllocatedLeave> AllCasualLeaveInfo(int employeeId)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand(@"SELECT * FROM AllocatedLeave Where EmployeeId ='" + employeeId + "' and LeaveTypeId = '" + 2 + "' ", con);
con.Open();
SqlDataReader dr = com.ExecuteReader();
List <AllocatedLeave> totalLeave = new List <AllocatedLeave>();
while (dr.Read())
{
AllocatedLeave leave = new AllocatedLeave();
leave.Id = (int)dr["Id"];
leave.NoOfAllocatedLeave = (int)dr["NoOfLeaveAllocated"];
totalLeave.Add(leave);
}
con.Close();
return(totalLeave.ToList());
}
public string AllocatedLeave(AllocatedLeave allocated)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand(@"INSERT INTO [dbo].[AllocatedLeave]
([EmployeeId]
,[LeaveTypeId]
,[NoOfLeaveAllocated])
VALUES
( '" + allocated.EmployeeId + "' , '" + allocated.LeaveTypeId + "' , '" + allocated.NoOfAllocatedLeave + "')", con);
con.Open();
int number = com.ExecuteNonQuery();
con.Close();
if (number > 0)
{
return("Employee Leave Allocation Save Successfully!");
}
return("Employee Leave Allocation Save Failed, Please Try Again!!!");
}
public bool IsLeaveAllocated(AllocatedLeave leaveTaken)
{
string Query = "SELECT * FROM AllocatedLeave WHERE (EmployeeId = @EmployeeId and LeaveTypeId = @LeaveTypeId)";
SqlConnection Connection = new SqlConnection(connectionString);
SqlCommand Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("EmployeeId", SqlDbType.Int);
Command.Parameters["EmployeeId"].Value = leaveTaken.EmployeeId;
Command.Parameters.Add("LeaveTypeId", SqlDbType.Int);
Command.Parameters["LeaveTypeId"].Value = leaveTaken.LeaveTypeId;
SqlDataReader Reader = Command.ExecuteReader();
Reader.Read();
bool isExist = Reader.HasRows;
Reader.Close();
Connection.Close();
return(isExist);
}
public bool IsLeaveAllocated(AllocatedLeave leaveTaken)
{
return(eGateway.IsLeaveAllocated(leaveTaken));
}
public string AllocatedLeave(AllocatedLeave allocated)
{
return(eGateway.AllocatedLeave(allocated));
}