public List <EmployeeAddress> GetEmployee(int id)
{
DBParameters.Clear();
AddParameter("@empId", id);
DataSet table = ExecuteDataSet("sp_getEmployee");
List <EmployeeAddress> employee = table.Tables[0].AsEnumerable().Select(emp => new EmployeeAddress()
{
EmpId = emp.Field <int>("emp_id"),
Gender = emp.Field <string>("gender"),
EmpName = emp.Field <string>("emp_name"),
Manager = emp.Field <string>("Manager"),
Department = emp.Field <int>("depId"),
Email = emp.Field <string>("Emailid"),
DateOfBirth = emp.Field <DateTime>("DateOfBirth"),
JoinDate = emp.Field <DateTime>("dateofjoin"),
PhoneNumber = emp.Field <Int64>("PhoneNumber").ToString(),
Address = emp.Field <string>("address"),
PinCode = emp.Field <int>("pincode").ToString(),
State = emp.Field <string>("state"),
Country = emp.Field <string>("country")
}).ToList();
return(employee);
}
public void CancelLeave(Leave leave)
{
DBParameters.Clear();
AddParameter("@emailId", leave.Email);
AddParameter("@startdate ", leave.FromDate);
AddParameter("@enddate", leave.ToDate);
ExecuteDataSet("sp_CancelLeave");
// ExecuteScalar("sp_CancelLeave");
}
public List <Department> GetDepartments()
{
DBParameters.Clear();
DataSet deplist = ExecuteDataSet("sp_GetAllDepartments");
List <Department> departments = deplist.Tables[0].AsEnumerable().Select(dep => new Department()
{
DepId = dep.Field <int>("depId"),
DepartmentName = dep.Field <string>("department")
}).ToList();
return(departments);
}
public void ApplyLeave(Leave leave)
{
DBParameters.Clear();
AddParameter("@emailId", leave.Email);
AddParameter("@fromDate", leave.FromDate);
AddParameter("@toDate", leave.ToDate);
AddParameter("@dayCount", DayCount(leave.FromDate, leave.ToDate));
AddParameter("@comment", leave.Comment);
AddParameter("@leaveType", leave.LeaveType);
AddParameter("@leaveStatus", "pending");
AddParameter("@StartTime", DateTime.Today.TimeOfDay);
AddParameter("@EndTime", DateTime.Today.TimeOfDay);
ExecuteNonQuery("sp_ApplyLeave");
}
public void AddEmployee(EmployeeAddress emp)
{
DBParameters.Clear();
AddParameter("@emp_Name", emp.EmpName);
AddParameter("@gender", emp.Gender);
AddParameter("@EmailId", emp.Email);
AddParameter("@DateOfBirth", emp.DateOfBirth);
AddParameter("@DepId", 1);
AddParameter("@Manager", emp.Manager);
AddParameter("@PhoneNumber", Int64.Parse(emp.PhoneNumber));
AddParameter("@pincode", Int32.Parse(emp.PinCode));
AddParameter("@Address", emp.Address);
AddParameter("@state", emp.State);
AddParameter("@country", emp.Country);
ExecuteNonQuery("sp_AddEmployee");
}
public List <Employee> GetEmployeeData()
{
DBParameters.Clear();
DataSet empList = ExecuteDataSet("sp_GetAllEmlpoyees");
List <Employee> allemp = empList.Tables[0].AsEnumerable().Select(emp => new Employee()
{
EmpId = emp.Field <int>("emp_id"),
EmpName = emp.Field <string>("emp_name"),
Manager = emp.Field <string>("Manager"),
Department = emp.Field <string>("department"),
Email = emp.Field <string>("Emailid"),
DateOfBirth = emp.Field <DateTime>("DateOfBirth"),
JoinDate = emp.Field <DateTime>("dateofjoin"),
PhoneNumber = emp.Field <Int64>("PhoneNumber")
}).ToList();
return(allemp);
}
public List <PublicHolidays> GetPublicHolidays()
{
DBParameters.Clear();
DateTime yearStart = new DateTime(DateTime.Now.Year, 1, 1);
DateTime yearEnd = new DateTime(DateTime.Now.Year, 12, 31);
AddParameter("@yearStart", yearStart);
AddParameter("@yearEnd", yearEnd);
DataSet deplist = ExecuteDataSet("sp_GetPublicHolidays");
List <PublicHolidays> holidayses = deplist.Tables[0].AsEnumerable().Select(h => new PublicHolidays()
{
Date = h.Field <DateTime>("date"),
Name = h.Field <string>("name")
}).OrderBy(h => h.Date).ToList();
return(holidayses);
}
public List <Leave> GetLeaveStatus(string emailId)
{
DBParameters.Clear();
AddParameter("@emailId", emailId);
DataSet deplist = ExecuteDataSet("sp_GetPendingLeaves");
List <Leave> leaves = deplist.Tables[0].AsEnumerable().Select(l => new Leave()
{
Email = l.Field <string>("emailId"),
FromDate = l.Field <DateTime>("fromDate"),
ToDate = l.Field <DateTime>("toDate"),
DayCount = l.Field <int>("dayCount"),
Comment = l.Field <string>("comment"),
LeaveType = l.Field <string>("leaveType"),
LeaveStatus = l.Field <string>("leaveStatus"),
}).ToList();
return(leaves);
}
public void EditEmployee(EmployeeAddress emp)
{
DBParameters.Clear();
AddParameter("@emp_id", emp.EmpId);
AddParameter("@gender", emp.Gender);
AddParameter("@emp_Name ", emp.EmpName);
AddParameter("@EmailId ", emp.Email);
AddParameter("@DateOfBirth ", emp.DateOfBirth);
AddParameter("@DepId ", emp.Department);
AddParameter("@Manager ", emp.Manager);
AddParameter("@PhoneNumber ", Int64.Parse(emp.PhoneNumber));
AddParameter("@pincode ", int.Parse(emp.PinCode));
AddParameter("@Address ", emp.Address);
AddParameter("@state ", emp.State);
AddParameter("@country ", emp.Country);
AddParameter("@joindate ", emp.JoinDate);
ExecuteDataSet("sp_EditEmployee");
}
public List <Leave> GetApplyedLeaves()
{
DBParameters.Clear();
AddParameter("@startDate", DateTime.Today.AddDays(-15));
AddParameter("@endDate", DateTime.Today.AddDays(15));
DataSet dataSet = ExecuteDataSet("sp_GetLeaves");
List <Leave> leaves = dataSet.Tables[0].AsEnumerable().Select(l => new Leave()
{
EmpName = l.Field <string>("emp_Name"),
Department = l.Field <string>("Department"),
FromDate = l.Field <DateTime>("fromDate"),
ToDate = l.Field <DateTime>("toDate"),
DayCount = l.Field <int>("dayCount"),
Comment = l.Field <string>("comment"),
LeaveType = l.Field <string>("leaveType"),
LeaveStatus = l.Field <string>("leaveStatus"),
}).ToList();
return(leaves);
}