public bool IsLeaveTaken(EmployeeLeaveTaken leaveTaken)
{
try
{
string Query = "SELECT * FROM tb_EmployeeLeave WHERE (StartDate <= @EndDate AND EndDate >= @StartDate AND EmployeeId = @EmployeeId)";
SqlCommand Command = new SqlCommand(Query, con);
con.Open();
Command.Parameters.Clear();
Command.Parameters.Add("StartDate", SqlDbType.Date);
Command.Parameters["StartDate"].Value = leaveTaken.StartDate;
Command.Parameters.Add("EndDate", SqlDbType.Date);
Command.Parameters["EndDate"].Value = leaveTaken.EndDate;
Command.Parameters.Add("EmployeeId", SqlDbType.Int);
Command.Parameters["EmployeeId"].Value = leaveTaken.EmployeeId;
SqlDataReader Reader = Command.ExecuteReader();
Reader.Read();
bool isExist = Reader.HasRows;
Reader.Close();
return(isExist);
}
catch (Exception exception)
{
throw new Exception("Unable to connect Server", exception);
}
finally
{
con.Close();
}
}
public int SendLeaveApplication(EmployeeLeaveTaken leaveTaken)
{
string query = @"INSERT INTO [dbo].[tb_EmployeeLeave]
([EmployeeId]
,[LeaveTypeId]
,[StartDate]
,[EndDate]
,[TotalDay]
,[Status]
,[EntryDate])
VALUES
('" + leaveTaken.EmployeeId + "', '" + leaveTaken.LeaveTypeId + "', '" + leaveTaken.StartDate +
"', '" + leaveTaken.EndDate + "', '" + leaveTaken.TotalDay + "','" +
leaveTaken.Status + "', '" + leaveTaken.EntryDate + "')";
try
{
SqlCommand command = new SqlCommand(query, con);
con.Open();
int rowAffected = command.ExecuteNonQuery();
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Unable to connect Server", exception);
}
finally
{
con.Close();
}
}
public ActionResult LeaveTaken(EmployeeLeaveTaken leaveTaken)
{
leaveTaken.EmployeeId = (int)Session["user"];
leaveTaken.Status = "Submit";
leaveTaken.EntryDate = DateTime.Now;
if (ModelState.IsValid)
{
try
{
if (adminManager.IsLeaveTaken(leaveTaken))
{
ViewBag.ShowMsg = "Date Overlapping Problem.";
}
else
{
int message = userManager.LeaveApplication(leaveTaken);
if (message > 0)
{
ViewBag.ShowMsg = "Leave Application Submit Successfully!";
List <SubmitedApplicationInfo> userEmail = adminManager.GetUserEmailAndName(leaveTaken.EmployeeId);
bool result = adminManager.SendEmail(userEmail[0].Email, "About your leave application",
"<p>Hello '" + userEmail[0].EmployeeName + "' <br/>Your Leave Application start date '" +
leaveTaken.StartDate.ToString("dd/MM/yyyy") + "' and end date '" + leaveTaken.EndDate.ToString("dd/MM/yyyy") + "', total day " +
leaveTaken.TotalDay + " are received by HR Admin<br/>Thank You<br/>PBL-001</p>");
}
else
{
ViewBag.ShowMsg = "Opps! Application Not Saved! Try Again Please";
}
}
}
catch (Exception exception)
{
ViewBag.ShowMsg = exception.Message;
}
}
ViewBag.casualLeaveLeft = adminManager.CasualLeaveLeft(leaveTaken.EmployeeId);
ViewBag.totalCasualkLeave = adminManager.TotalCasualLeave(leaveTaken.EmployeeId);
ViewBag.sickLeaveLeft = adminManager.SickLeaveLeft(leaveTaken.EmployeeId);
ViewBag.totalSickLeave = adminManager.TotalSickLeave(leaveTaken.EmployeeId);
ViewBag.ListOfLeaveType = adminManager.GetLeaveTypes();
ViewBag.ListOfEmployees = adminManager.ListOfEmployee();
return(View());
}
public List <EmployeeLeaveTaken> GetTotalCasualLeaveByEmployeeId(int employeeId)
{
string query = @"SELECT SUM(TotalDay) as number
FROM tb_EmployeeLeave
WHERE EmployeeId ='" + employeeId + "' and LeaveTypeId = '" + 2 + "' and Status = '" + "Approve" + "'";
try
{
SqlCommand com = new SqlCommand(query, con);
con.Open();
SqlDataReader dr = com.ExecuteReader();
List <EmployeeLeaveTaken> totalSickLeaves = new List <EmployeeLeaveTaken>();
while (dr.Read())
{
EmployeeLeaveTaken leave = new EmployeeLeaveTaken();
if (!object.ReferenceEquals(dr["number"], DBNull.Value))
{
leave.TotalDay = Convert.ToInt32(dr["number"]);
}
else
{
leave.TotalDay = 0;
}
totalSickLeaves.Add(leave);
}
dr.Close();
return(totalSickLeaves.ToList());
}
catch (Exception exception)
{
throw new Exception("Unable to connect Server", exception);
}
finally
{
con.Close();
}
}
public int LeaveApplication(EmployeeLeaveTaken leaveTaken)
{
int rowAffected = userGateway.SendLeaveApplication(leaveTaken);
return(rowAffected);
}
public bool IsLeaveTaken(EmployeeLeaveTaken leaveTaken)
{
return(adminGateway.IsLeaveTaken(leaveTaken));
}