public bool InsertEmployeeLeaveDetails(int leaveType, string fromDate, string toDate, string comments, int workingDays)
{
var result = false;
try
{
using (var ctx = new LeaveManagementSystemEntities1())
{
var employeeLeaveDetails = new EmployeeLeaveTransaction
{
EmployeeComment = comments,
FromDate = Convert.ToDateTime(fromDate),
ToDate = Convert.ToDateTime(toDate),
CreatedDate = DateTime.Now,
NumberOfWorkingDays = workingDays,
RefLeaveType = leaveType,
RefStatus = (int)LeaveStatus.Planned,
RefEmployeeId = 1,
CreatedBy = "Alekya"
};
ctx.EmployeeLeaveTransactions.Add(employeeLeaveDetails);
ctx.SaveChanges();
}
result = true;
}
catch (Exception ex)
{
throw;
}
return(result);
}
public IQueryable <EmployeeLeaveTransaction> GetAllEmployeeLeaveApply(int schoolId)
{
try
{
List <EmployeeLeaveTransaction> entites = new List <EmployeeLeaveTransaction>();
String SqlQuery = "select * from viewGetEmployeeListForAddLeave";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
EmployeeLeaveTransaction entity = new EmployeeLeaveTransaction();
entity.EmployeeId = Convert.ToInt32(dr["EmployeeId"]);
entity.EmployeeFullName = Convert.ToString(dr["FirstName"]).Trim() + " " + Convert.ToString(dr["MiddleName"]).Trim() + " " + Convert.ToString(dr["LastName"]).Trim();
entity.BalanceLeaves = 0;
if (dr["BalanceLeaves"] != DBNull.Value)
{
entity.BalanceLeaves = Convert.ToDecimal(dr["BalanceLeaves"]);
}
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public void leaveApply(EmployeeLeaveTransaction entity)
{
try
{
string queryString;
if (entity.LeavesInDays != 0)
{
queryString = string.Format("INSERT INTO [EmployeeLeaveTransaction]([EmployeeId],[TransactionType],[LeaveType],[LeaveFromDate],[LeaveToDate],[LeavesInDays],[BalanceLeaves],[Remark],[TransactionDate])" +
"VALUES('{0}', N'{1}',N'{2}',convert(date,'{3}',105), convert(date,'{4}',105), '{5}', '{6}', N'{7}',GETDATE());", entity.EmployeeId, "Dr", entity.LeaveType, entity.LeaveFromDate, entity.LeaveToDate, entity.LeavesInDays, entity.BalanceLeaves, entity.Remark);
int effetedRows = CommanMethodsForSQL.ExecuteNonQuery(queryString);
}
}
catch (Exception ex)
{
throw;
}
}
public void leaveApply(EmployeeLeaveTransaction entity)
{
EmployeeDAL dalObject = new EmployeeDAL();
dalObject.leaveApply(entity);
}
public IQueryable <EmployeeLeaveTransaction> GetAllEmployeeLeaves(int schoolId)
{
try
{
List <EmployeeLeaveTransaction> entites = new List <EmployeeLeaveTransaction>();
String SqlQuery = "SELECT EmployeeCode, FirstName, MiddleName, LastName, TransactionType,EmployeeLeaveTransaction.EmployeeId as EmpId, LeaveType, LeaveFromDate,LeaveToDate, LeavesInDays, BalanceLeaves,Remark, TransactionDate" +
" FROM Employee INNER JOIN EmployeeLeaveTransaction ON Employee.EmployeeId = EmployeeLeaveTransaction.EmployeeId";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
EmployeeLeaveTransaction entity = new EmployeeLeaveTransaction();
entity.EmployeeId = Convert.ToInt32(dr["EmpId"]);
entity.EmployeeFullName = Convert.ToString(dr["FirstName"]).Trim() + " " + Convert.ToString(dr["MiddleName"]).Trim() + " " + Convert.ToString(dr["LastName"]).Trim();
entity.LeaveType = Convert.ToInt32(dr["LeaveType"]);
entity.LeaveFromDate = Convert.ToDateTime(dr["LeaveFromDate"]);
entity.LeaveToDate = Convert.ToDateTime(dr["LeaveToDate"]);
if (dr["LeavesInDays"] != DBNull.Value)
{
entity.LeavesInDays = Convert.ToDecimal(dr["LeavesInDays"]);
}
if (dr["BalanceLeaves"] != DBNull.Value)
{
entity.BalanceLeaves = Convert.ToDecimal(dr["BalanceLeaves"]);
}
entity.Remark = Convert.ToString(dr["Remark"]);
entity.TransactionDate = Convert.ToDateTime(dr["TransactionDate"]);
entity.TransactionType = 0;
if (Convert.ToString(dr["TransactionType"]).Trim() == "Cr")
{
entity.TransactionType = 1;
}
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
