public List <Holiday> RetrieveHolidays(long ptoCycleID)
{
var returnValue = new List <Holiday>();
try
{
using (var context = new PTOMEntities())
{
var query = from h in context.HOLIDAY
where h.PTO_CYCLE == ptoCycleID
select new Holiday
{
HolidayID = h.HOLIDAY_ID,
HolidayDate = h.HOLIDAY_DATE,
Name = h.NAME,
PTOCycleID = h.PTO_CYCLE
};
if (query.Any())
{
returnValue = query.ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public void UpdateEmployee(long employeeId, bool isAdmin, string displayName, string email, DateTime hireDate, DateTime anniversaryDate, long approverId)
{
try
{
using (var context = new PTOMEntities())
{
var item = context.EMPLOYEE.FirstOrDefault(emp => emp.EMPLOYEE_ID == employeeId);
if (item != null)
{
item.DISPLAY_NAME = displayName;
item.IS_ADMIN = isAdmin ? "Y" : "N";
item.EMAIL = email;
item.HIRE_DATE = hireDate;
item.ANNIVERSARY_DATE = anniversaryDate;
item.APPROVER_EMPLOYEE_ID = approverId;
context.SaveChanges();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public List <Employee> SearchEmployeesByName(string name)
{
var query = new List <Employee>();
try
{
using (var context = new PTOMEntities())
{
query = context.EMPLOYEE.Where(emp => emp.DISPLAY_NAME.ToLower().Contains(name.ToLower())).Select(
e => new Employee
{
EmployeeID = e.EMPLOYEE_ID,
DisplayName = e.DISPLAY_NAME,
Username = e.USERNAME,
IsAdmin = e.IS_ADMIN.Equals("Y", StringComparison.OrdinalIgnoreCase),
IsApprover = e.IS_ADMIN.Equals("Y", StringComparison.OrdinalIgnoreCase),
Email = e.EMAIL,
HireDate = e.HIRE_DATE,
AnniversaryDate = e.ANNIVERSARY_DATE,
ApproverEmployeeId = e.APPROVER_EMPLOYEE_ID
}).ToList();
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(query);
}
public long CreateRequest(long userId, long approverEmployeeId, string comment, long ptoCycleID)
{
long returnValue;
try
{
using (var context = new PTOMEntities())
{
returnValue = GetSequenceNextValue(context, "PTOMS.PTO_REQUEST_ID_SQ");
var r = new PTO_REQUEST
{
APPROVER_EMPLOYEE_ID = approverEmployeeId,
EMPLOYEE_ID = userId,
PTO_COMMENT = comment,
PTO_REQUEST_ID = returnValue,
REQUEST_DATE = DateTime.Now,
PTO_CYCLE_ID = ptoCycleID,
STATUS = RequestStatus.Pending.ToString()
};
context.PTO_REQUEST.AddObject(r);
context.SaveChanges();
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public List <PTORequest> RetrieveAllRequestsByTeam(long teamId)
{
var returnvalue = new List <PTORequest>();
try
{
using (var context = new PTOMEntities())
{
var query = from t in context.EMPLOYEE_TEAM
join emp in context.EMPLOYEE on t.EMPLOYEE_ID equals emp.EMPLOYEE_ID
join req in context.PTO_REQUEST on emp.EMPLOYEE_ID equals req.EMPLOYEE_ID
where t.TEAM_ID == teamId && req.STATUS != "Declined"
select new PTORequest
{
EmployeeID = emp.EMPLOYEE_ID,
PTORequestId = req.PTO_REQUEST_ID,
TeamID = t.TEAM_ID,
};
if (query.Any())
{
returnvalue = query.ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnvalue);
}
public long CreateRollover(long employeeId, decimal?hours)
{
long returnvalue;
try
{
using (var context = new PTOMEntities())
{
returnvalue = GetSequenceNextValue(context, "PTOMS.ROLLOVER_ID_SQ");
var r = new ROLLOVER
{
ROLLOVER_ID = returnvalue,
EMPLOYEE_ID = employeeId,
HOURS = hours,
PTO_CYCLE_ID = CurrentCycle.PTOCycleID.Value
};
context.ROLLOVER.AddObject(r);
context.SaveChanges();
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnvalue);
}
public long CreateRequestDay(long requestId, DateTime ptoDayDate, long requestTypeID)
{
long returnValue;
try
{
using (var context = new PTOMEntities())
{
returnValue = GetSequenceNextValue(context, "PTOMS.PTO_DAY_ID_SQ");
var d = new PTO_DAY
{
PTO_DAY_DATE = ptoDayDate,
PTO_DAY_ID = returnValue,
PTO_REQUEST_ID = requestId,
REQUEST_TYPE_ID = requestTypeID
};
context.PTO_DAY.AddObject(d);
context.SaveChanges();
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public List <Team> RetrieveTeamByEmployeeId(long employeeId)
{
var returnValue = new List <Team>();
try
{
using (var context = new PTOMEntities())
{
var query = from et in context.EMPLOYEE_TEAM
join t in context.TEAM on et.TEAM_ID equals t.TEAM_ID
where et.EMPLOYEE_ID == employeeId
select new Team
{
TeamID = t.TEAM_ID,
TeamName = t.TEAM_NAME
};
if (query.Any())
{
returnValue = query.ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public Team RetrieveTeam(long teamID)
{
var returnValue = new Team();
try
{
using (var context = new PTOMEntities())
{
var query = context.TEAM.FirstOrDefault(x => x.TEAM_ID == teamID);
{
returnValue = new Team()
{
TeamName = query.TEAM_NAME,
TeamID = query.TEAM_ID
};
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public Employee GetEmployeeByUsername(string username)
{
var returnValue = new Employee();
using (var context = new PTOMEntities())
{
var employee = context.EMPLOYEE.FirstOrDefault(emp => emp.USERNAME.ToLower() == username.ToLower());
if (employee != null)
{
returnValue = new Employee
{
EmployeeID = employee.EMPLOYEE_ID,
DisplayName = employee.DISPLAY_NAME,
Username = employee.USERNAME,
IsAdmin = employee.IS_ADMIN == "Y",
HireDate = employee.HIRE_DATE,
AnniversaryDate = employee.ANNIVERSARY_DATE,
Email = employee.EMAIL,
ApproverEmployeeId = employee.APPROVER_EMPLOYEE_ID
};
returnValue.IsApprover = IsEmployeeApprover(employee.EMPLOYEE_ID);
}
}
return(returnValue);
}
public List <Team> RetrieveTeams()
{
var returnValue = new List <Team>();
try
{
using (var context = new PTOMEntities())
{
var query = from t in context.TEAM
select new Team
{
TeamID = t.TEAM_ID,
TeamName = t.TEAM_NAME,
};
if (query.Any())
{
returnValue = query.ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public long?CreateHoliday(DateTime date, string name, long ptoCycle)
{
long returnValue;
try
{
using (var context = new PTOMEntities())
{
returnValue = GetSequenceNextValue(context, "PTOMS.HOLIDAY_ID_SQ");
var holiday = new HOLIDAY
{
HOLIDAY_ID = returnValue,
NAME = name,
HOLIDAY_DATE = date,
PTO_CYCLE = ptoCycle
};
context.HOLIDAY.AddObject(holiday);
context.SaveChanges();
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public PTORequest RetrieveRequestListById(long requestId)
{
var returnValue = new PTORequest();
try
{
using (var context = new PTOMEntities())
{
var query = from r in context.PTO_REQUEST
where r.PTO_REQUEST_ID == requestId
select r;
if (query.Any())
{
returnValue = ConvertToPTORequest(query.FirstOrDefault());
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public List <Approver> RetrieveApprovers()
{
var returnValue = new List <Approver>();
try
{
using (var context = new PTOMEntities())
{
var query = from a in context.EMPLOYEE
where a.IS_APPROVER == "Y"
select new Approver
{
ApproverID = a.APPROVER_EMPLOYEE_ID,
EmployeeID = a.EMPLOYEE_ID
};
if (query.Any())
{
returnValue = query.ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public List <RequestType> RetrieveRequestTypes()
{
var returnValue = new List <RequestType>();
try
{
using (var context = new PTOMEntities())
{
var query = from rt in context.REQUEST_TYPE
select new RequestType
{
RequestTypeID = rt.REQUEST_TYPE_ID,
Description = rt.DESCRIPTION,
Enabled = rt.ENABLED == "Y" ? true : false,
Code = rt.CODE,
ApproverID = rt.APPROVER_EMPLOYEE_ID
};
if (query.Any())
{
returnValue = query.OrderBy(e => e.RequestTypeID).ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public long CreateEmployee(string userName, string displayName, bool isAdmin, string email, DateTime hireDate, DateTime anniversaryDate, long approverId)
{
long returnValue;
try
{
using (var context = new PTOMEntities())
{
returnValue = GetSequenceNextValue(context, "PTOMS.EMPLOYEE_ID_SQ");
var employee = new EMPLOYEE
{
EMPLOYEE_ID = returnValue,
USERNAME = userName.ToLower(),
DISPLAY_NAME = displayName,
IS_ADMIN = isAdmin ? "Y" : "N",
EMAIL = email,
HIRE_DATE = hireDate,
ANNIVERSARY_DATE = anniversaryDate,
APPROVER_EMPLOYEE_ID = approverId
};
context.EMPLOYEE.AddObject(employee);
context.SaveChanges();
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public Rollover RetrieveRolloverForEmployee(long employeeId)
{
var returnValue = new Rollover();
try
{
using (var context = new PTOMEntities())
{
var query = context.ROLLOVER.FirstOrDefault(EMP => EMP.EMPLOYEE_ID == employeeId);
if (query != null)
{
returnValue.EmployeeID = employeeId;
returnValue.Hours = query.HOURS;
returnValue.PTOCycleID = query.PTO_CYCLE_ID;
returnValue.RolloverID = query.ROLLOVER_ID;
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public Employee RetrieveEmployeeById(long employeeID)
{
try
{
using (var context = new PTOMEntities())
{
var query = context.EMPLOYEE.FirstOrDefault(emp => emp.EMPLOYEE_ID == employeeID);
if (query != null)
{
var returnValue = new Employee
{
EmployeeID = query.EMPLOYEE_ID,
DisplayName = query.DISPLAY_NAME,
Username = query.USERNAME.ToLower(),
IsAdmin = query.IS_ADMIN.Equals("Y", StringComparison.OrdinalIgnoreCase) ? true : false,
Email = query.EMAIL,
HireDate = query.HIRE_DATE,
AnniversaryDate = query.ANNIVERSARY_DATE,
ApproverEmployeeId = query.APPROVER_EMPLOYEE_ID
};
returnValue.IsApprover = IsEmployeeApprover(query.EMPLOYEE_ID);
return(returnValue);
}
else
{
return(null);
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public List <Rollover> RetrieveRolloverList()
{
var returnvalue = new List <Rollover>();
try
{
using (var context = new PTOMEntities())
{
var query = from r in context.ROLLOVER
select new Rollover
{
EmployeeID = r.EMPLOYEE_ID,
Hours = r.HOURS,
PTOCycleID = r.PTO_CYCLE_ID,
RolloverID = r.ROLLOVER_ID
};
if (query.Any())
{
returnvalue = query.OrderBy(x => x.RolloverID).ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnvalue);
}
public Employee RetrieveApproverbyId(long approverEmployeeId)
{
try
{
using (var context = new PTOMEntities())
{
var query = context.EMPLOYEE.FirstOrDefault(emp => emp.EMPLOYEE_ID == approverEmployeeId);
if (query != null)
{
return(new Employee
{
Email = query.EMAIL,
ApproverEmployeeId = query.APPROVER_EMPLOYEE_ID,
EmployeeID = query.EMPLOYEE_ID,
DisplayName = query.DISPLAY_NAME
});
}
else
{
return(null);
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public PTORequest RetrievePTORequest(long requestId)
{
var returnValue = new PTORequest();
try
{
using (var context = new PTOMEntities())
{
var query = context.PTO_REQUEST.FirstOrDefault(REQ => REQ.PTO_REQUEST_ID == requestId);
if (query != null)
{
returnValue = ConvertToPTORequest(query);
}
else
{
return(null);
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
public List <PTORequest> RetrieveRequestListByApprover(long approverId)
{
var returnValue = new List <PTORequest>();
try
{
using (var context = new PTOMEntities())
{
var query = from r in context.PTO_REQUEST
where r.APPROVER_EMPLOYEE_ID == approverId
select r;
if (query.Any())
{
returnValue = query.Select(ConvertToPTORequest).ToList();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
return(returnValue);
}
protected long GetSequenceNextValue(PTOMEntities context, string sequenceName)
{
long returnValue = 0;
decimal? nextValue = null;
context.GET_SEQUENCE_NEXT_VALUE(sequenceName, ref nextValue);
if (nextValue.HasValue)
{
returnValue = (long)nextValue;
}
return returnValue;
}
public bool IsEmployeeApprover(long employeeID)
{
try
{
using (var context = new PTOMEntities())
{
var query = (from a in context.EMPLOYEE
where a.EMPLOYEE_ID == employeeID && a.IS_APPROVER == "Y"
select a).Count();
return(query > 0);
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public void DeleteRequest(long requestId)
{
try
{
using (var context = new PTOMEntities())
{
var REQ = context.PTO_REQUEST.FirstOrDefault(x => x.PTO_REQUEST_ID == requestId);
if (REQ != null)
{
context.DeleteObject(REQ);
context.SaveChanges();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public void DeleteEmployee(long employeeId)
{
try
{
using (var context = new PTOMEntities())
{
var EMPLOYEE = context.EMPLOYEE.FirstOrDefault(emp => emp.EMPLOYEE_ID == employeeId);
if (EMPLOYEE != null)
{
context.DeleteObject(EMPLOYEE);
context.SaveChanges();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
private void UpdateApprover(long employeeId, bool Approver)
{
try
{
using (var context = new PTOMEntities())
{
var employee = context.EMPLOYEE.FirstOrDefault(h => h.EMPLOYEE_ID == employeeId);
if (employee != null)
{
employee.IS_APPROVER = Approver ? "Y":"N";
context.SaveChanges();
}
;
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public void UpdateRollover(long employeeId, decimal?hours)
{
try
{
using (var context = new PTOMEntities())
{
var r = context.ROLLOVER.FirstOrDefault(x => x.EMPLOYEE_ID == employeeId);
if (r != null)
{
r.HOURS = hours;
r.PTO_CYCLE_ID = CurrentCycle.PTOCycleID.Value;
context.SaveChanges();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public void UpdateEmployeeTeams(long employeeId, long teamId)
{
try
{
using (var context = new PTOMEntities())
{
var item = context.EMPLOYEE_TEAM.FirstOrDefault(x => x.EMPLOYEE_ID == employeeId);
if (item != null)
{
item.EMPLOYEE_ID = employeeId;
item.TEAM_ID = teamId;
context.SaveChanges();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
public void EditRequestDay(long requestId, DateTime ptoDayDate, long requestTypeID, long ptoDayID)
{
try
{
using (var context = new PTOMEntities())
{
var d = context.PTO_DAY.FirstOrDefault(day => day.PTO_DAY_ID == ptoDayID);
if (d != null)
{
d.PTO_DAY_DATE = ptoDayDate;
d.PTO_REQUEST_ID = requestId;
d.REQUEST_TYPE_ID = requestTypeID;
context.SaveChanges();
}
}
}
catch (Exception ex)
{
LoggingHelper.LogServiceMessage(LoggingHelper.MessageType.Error, string.Empty, ex);
throw;
}
}
