public static Employee FindControlRecursively(List <Employee> root, Int64 id)
{
try
{
foreach (Employee emp in root)
{
if (emp.UserId == id)
{
return(emp);
}
using (var context = new NLTDDbContext())
{
var child = (from e in context.Employee
where e.ReportingToId == emp.UserId
select e).ToList();
Employee found = FindControlRecursively(child, id);
if (found != null)
{
return(found);
}
}
}
}
catch (Exception)
{
throw;
}
return(null);
}
public List <ShiftQueryModel> GetShiftDetails(Int64 UserID, DateTime FromDate, DateTime ToDate)
{
List <ShiftQueryModel> shiftQueryModelList = new List <ShiftQueryModel>();
try
{
using (var entity = new NLTDDbContext())
{
shiftQueryModelList = (from sm in entity.ShiftMaster
join smp in entity.ShiftMapping on sm.ShiftID equals smp.ShiftID
join e in entity.Employee on smp.UserID equals e.UserId
where smp.UserID == UserID && smp.ShiftDate >= FromDate && smp.ShiftDate <= ToDate
select new ShiftQueryModel
{
UserID = smp.UserID,
Employeename = e.FirstName + " " + e.LastName,
ShiftFromtime = sm.FromTime,
ShiftTotime = sm.ToTime,
ShiftDate = smp.ShiftDate,
}).ToList();
}
}
catch (Exception)
{
throw;
}
return(shiftQueryModelList.OrderBy(m => m.ShiftDate).ToList());
}
public IList <UserEmailListModel> GetUserEmailData()
{
IList <UserEmailListModel> lstEmail = new List <UserEmailListModel>();
try
{
using (var context = new NLTDDbContext())
{
lstEmail = (from e in context.Employee
join em in context.Employee on e.ReportingToId equals em.UserId
where e.IsActive == true
select new UserEmailListModel
{
UserId = e.UserId,
EmployeeEmailAddress = e.EmailAddress,
ReportingToEmailAddress = em.EmailAddress,
FirstName = e.FirstName,
LastName = e.LastName,
SkipTimesheetCompliance = e.SkipTimesheetCompliance == true ? true : false
}).ToList();
}
}
catch
{
throw;
}
return(lstEmail);
}
public bool IsUserHR(long userId)
{
bool result = false;
try
{
using (var context = new NLTDDbContext())
{
var hrUsr = (from e in context.Employee
join r in context.EmployeeRole on e.EmployeeRoleId equals r.RoleId
where e.UserId == userId
select new { r.Role }
).FirstOrDefault();
if (hrUsr != null)
{
if (hrUsr.Role.ToUpper() == "HR" || hrUsr.Role.ToUpper() == "ADMIN")
{
result = true;
}
}
}
}
catch
{
throw;
}
return(result);
}
public List <LeaveTransactionHistoryModel> GetTransactionDetails(NLTDDbContext context, long userId)
{
var transactionDetails = (from lth in context.LeaveTransactionHistory
join lt in context.LeaveType on lth.LeaveTypeId equals lt.LeaveTypeId
join l in context.Leave on lth.LeaveId equals l.LeaveId into ps
from p in ps.DefaultIfEmpty()
join e in context.Employee on lth.UserId equals e.UserId
where lth.UserId == userId && lt.IsTimeBased == false
select new LeaveTransactionHistoryModel
{
LeaveId = lth.LeaveId,
LeaveTypeId = lth.LeaveTypeId,
TransactionType = lth.TransactionType == "C" ? "Credit" : "Debit",
NumberOfDays = lth.NumberOfDays,
UserId = lth.UserId,
Remarks = lth.Remarks,
TransactionDate = lth.TransactionDate,
Type = lt.Type,
FirstName = e.FirstName,
LastName = e.LastName,
EmployeeId = e.EmployeeId,
StartDate = p.StartDate,
EndDate = p.EndDate
}).ToList().OrderByDescending(x => x.TransactionDate).ToList();
return(transactionDetails);
}
public List <EmployeeAttendanceModel> GetAccessCardAttendanceForRange(Int64 UserID, DateTime FromDateTime, DateTime ToDateTime, string requestLevelPerson)
{
List <EmployeeAttendanceModel> employeeAttendanceModelList = new List <EmployeeAttendanceModel>();
try
{
using (var context = new NLTDDbContext())
{
employeeAttendanceModelList = (from ea in context.EmployeeAttendance
join e in context.Employee on ea.UserID equals e.UserId
into ej
from d in ej.DefaultIfEmpty()
where ea.InOutDate >= FromDateTime && ea.InOutDate <= ToDateTime
select new EmployeeAttendanceModel
{
UserID = ea.UserID,
InOutDate = ea.InOutDate,
CardID = ea.CardID,
InOut = (ea.InOut ? "Out" : "In"),
Name = (d.UserId == 0 ? "Visitor" : (d.FirstName + " " + d.LastName))
}).OrderBy(e => e.CardID).ToList();
}
}
catch (Exception)
{
throw;
}
return(employeeAttendanceModelList);
}
public List <EmployeeLeave> GetLeaveForEmployee(Int64 UserID, DateTime FromDate, DateTime ToDate)
{
List <EmployeeLeave> leaveList = new List <EmployeeLeave>();
LeaveDac lv = new LeaveDac();
try
{
using (var context = new NLTDDbContext())
{
leaveList = (from l in context.Leave
join lt in context.LeaveType on l.LeaveTypeId equals lt.LeaveTypeId
join ld in context.LeaveDetail on l.LeaveId equals ld.LeaveId
where l.UserId == UserID && l.Status == "A" && ld.IsDayOff == false && ld.LeaveDate >= FromDate && ld.LeaveDate <= ToDate
select new EmployeeLeave
{
UserId = l.UserId,
StartDate = ld.LeaveDate,
EndDate = ld.LeaveDate,
LeaveType = lt.Type,
LeaveDayQty = ld.LeaveDayQty,
StartDateType = ld.PartOfDay,
EndDateType = ld.PartOfDay,
LeaveTypeId = l.LeaveTypeId,
IsLeave = lt.IsLeave,
WorkFromHomeDayQty = ld.LeaveDayQty
}
).ToList();
List <EmployeeLeave> PermissionList = (from l in context.Leave
join lt in context.LeaveType on l.LeaveTypeId equals lt.LeaveTypeId
join ld in context.PermissionDetail on l.LeaveId equals ld.LeaveId
where l.UserId == UserID && l.Status == "A" && ld.PermissionDate >= FromDate && ld.PermissionDate <= ToDate
select new EmployeeLeave
{
UserId = l.UserId,
StartDate = ld.PermissionDate,
EndDate = ld.PermissionDate,
LeaveType = lt.Type,
TimeFrom = ld.TimeFrom,
TimeTo = ld.TimeTo,
LeaveTypeId = l.LeaveTypeId,
IsLeave = lt.IsLeave
}
).ToList();
PermissionList.ForEach(pl => pl.PermissionCount = Math.Round((decimal)(lv.CalculateDuration(pl.TimeFrom, pl.TimeTo).TotalMinutes) / 60, 2));
if (PermissionList.Count > 0)
{
leaveList.AddRange(PermissionList);
}
}
}
catch (Exception)
{
throw;
}
return(leaveList);
}
public IList <LeaveBalanceEmpProfile> GetLeaveBalanceEmpProfile(Int64 userId)
{
IList <LeaveBalanceEmpProfile> retModel = new List <LeaveBalanceEmpProfile>();
using (var context = new NLTDDbContext())
{
if (userId > 0)
{
var employeeLeaveBalanceProfile = (from employee in context.Employee
where employee.UserId == userId
select new EmployeeLeaveBalanceProfile
{
LogonId = employee.LoginId,
EmployeeId = employee.EmployeeId,
UserId = employee.UserId,
FirstName = employee.FirstName,
LastName = employee.LastName,
ReportedToId = employee.ReportingToId
}).FirstOrDefault();
var repName = context.Employee.Where(x => x.UserId == employeeLeaveBalanceProfile.ReportedToId).FirstOrDefault();
if (repName != null)
{
employeeLeaveBalanceProfile.ReportedToName = repName.FirstName + " " + repName.LastName;
}
var employeeLeaveBalance = (from l in context.LeaveType
from elb in context.EmployeeLeaveBalance.Where(x => x.LeaveTypeId == l.LeaveTypeId && x.UserId == userId && x.Year == DateTime.Now.Year).DefaultIfEmpty()
where l.AdjustLeaveBalance == true
orderby l.LeaveTypeId
select new EmployeeLeaveBalanceDetails
{
LeaveTypeId = l.LeaveTypeId,
OfficeId = l.OfficeId,
Type = l.Type,
AdjustLeaveBalance = l.AdjustLeaveBalance,
LeaveBalanceId = elb.LeaveBalanceId,
ExistingTotalDays = elb.TotalDays ?? 0,
BalanceDays = elb.BalanceDays,
UserId = elb.UserId,
Year = elb.Year
}).ToList();
retModel.Add(new LeaveBalanceEmpProfile
{
employeeLeaveBalanceProfile = employeeLeaveBalanceProfile,
lstEmployeeLeaveBalance = employeeLeaveBalance,
Name = employeeLeaveBalanceProfile.FirstName + " " + employeeLeaveBalanceProfile.LastName
});
}
}
return(retModel);
}
public List <DropDownItem> GetAllRoles()
{
using (var context = new NLTDDbContext())
{
List <DropDownItem> RoleList = (from role in context.EmployeeRole
select new DropDownItem
{
Key = role.RoleId.ToString(),
Value = role.Role
}).ToList();
return(RoleList);
}
}
public List <DropDownItem> GetAllOfficeLocations()
{
using (var context = new NLTDDbContext())
{
List <DropDownItem> OfficeLocations = (from office in context.OfficeLocation
select new DropDownItem
{
Key = office.OfficeId.ToString(),
Value = office.OfficeName
}).ToList();
return(OfficeLocations);
}
}
public List <OfficeHoliday> GetOfficeHoliday(Int64 userID = 0)
{
List <OfficeHoliday> officeHolidayList = new List <OfficeHoliday>();
using (var context = new NLTDDbContext())
{
officeHolidayList = (from oh in context.OfficeHoliday
join e in context.Employee on oh.OfficeId equals e.OfficeHolidayId
where e.UserId == userID || userID == 0
select oh).ToList();
}
return(officeHolidayList);
}
public Int64 GetRoleOfEmployee(Int64 EmployeeId)
{
using (var context = new NLTDDbContext())
{
var employee = context.Employee.Where(e => e.UserId == EmployeeId).FirstOrDefault();
if (employee != null)
{
return(employee.EmployeeRoleId.Value);
}
else
{
return(0);
}
}
}
public static DateTime GetlastCreditRunforEL()
{
DateTime lastCreditRun;
using (var context = new NLTDDbContext())
{
List <LeaveTypesModel> LeaveTypes = (from l in context.LeaveType
where l.Type == "Earned Leave"
select new LeaveTypesModel
{
lastCreditRun = l.lastCreditRun
}).ToList();
lastCreditRun = LeaveTypes.Select(s => (DateTime)s.lastCreditRun).FirstOrDefault();
}
return(lastCreditRun);
}
public List <string> GetEmployeeWeekOffDay(Int64 UserID)
{
List <string> weekOffDaysList = new List <string>();
try
{
using (var context = new NLTDDbContext())
{
weekOffDaysList = (from ewf in context.EmployeeWeekOff
join dw in context.DayOfWeek
on ewf.DaysOfWeekId equals dw.DaysOfWeekId
where ewf.UserId == UserID
select dw.Day).ToList();
}
}
catch (Exception)
{
throw;
}
return(weekOffDaysList);
}
public long GetHrUserId()
{
long userId = 0;
try
{
using (var context = new NLTDDbContext())
{
var hrUsr = (from e in context.Employee
join r in context.EmployeeRole on e.EmployeeRoleId equals r.RoleId
where e.IsActive == true && r.Role.ToUpper() == "HR"
select new { e.UserId }
).FirstOrDefault();
userId = hrUsr.UserId;
}
}
catch
{
throw;
}
return(userId);
}
public List <EmployeeAttendanceModel> GetAttendance(Int64 UserID)
{
List <EmployeeAttendanceModel> employeeAttendanceModelList = new List <EmployeeAttendanceModel>();
try
{
using (var context = new NLTDDbContext())
{
employeeAttendanceModelList = (from ea in context.EmployeeAttendance
where ea.UserID == UserID
select new EmployeeAttendanceModel
{
UserID = ea.UserID,
InOutDate = ea.InOutDate,
InOut = (ea.InOut ? "OUT" : "IN")
}).OrderByDescending(e => e.InOutDate).ToList();
}
}
catch (Exception)
{
throw;
}
return(employeeAttendanceModelList);
}
public string UpdateLeaveBalance(List <EmployeeLeaveBalanceDetails> empLeaveBalanceDetails, Int64 LoginUserId, bool isElCredit = false)
{
try
{
int isSaved = 0;
using (var context = new NLTDDbContext())
{
EmployeeDac employeeDac = new EmployeeDac();
string userRole = string.Empty;
if (LoginUserId != 0)
{
userRole = employeeDac.GetEmployeeRole(LoginUserId);
}
else
{
userRole = "System";
}
if (userRole == "HR" || userRole == "System")
{
using (var transaction = context.Database.BeginTransaction())
{
int creditYear = Convert.ToDateTime(context.LeaveType.Where(x => x.Type == "Earned Leave").FirstOrDefault().lastCreditRun).Year;
foreach (var item in empLeaveBalanceDetails)
{
if (item.NoOfDays > 0)
{
EmployeeLeaveBalance leaveBalance;
if (isElCredit == false)
{
leaveBalance = context.EmployeeLeaveBalance.Where(x => x.UserId == item.UserId && x.LeaveTypeId == item.LeaveTypeId &&
x.LeaveBalanceId == item.LeaveBalanceId && x.Year == DateTime.Now.Year).FirstOrDefault();
}
else
{
leaveBalance = context.EmployeeLeaveBalance.Where(x => x.UserId == item.UserId && x.LeaveTypeId == item.LeaveTypeId &&
x.LeaveBalanceId == item.LeaveBalanceId && x.Year == creditYear).FirstOrDefault();
}
if (leaveBalance != null)
{
leaveBalance.TotalDays = item.CreditOrDebit == "C" ? (leaveBalance.TotalDays + item.NoOfDays) : (leaveBalance.TotalDays - item.NoOfDays);
leaveBalance.ModifiedBy = LoginUserId;
leaveBalance.ModifiedOn = DateTime.Now;
leaveBalance.BalanceDays = item.TotalDays;
isSaved = context.SaveChanges();
}
else
{
leaveBalance = new EmployeeLeaveBalance
{
UserId = Convert.ToInt64(item.UserId)
};
if (isElCredit == false)
{
leaveBalance.Year = DateTime.Now.Year;
}
else
{
leaveBalance.Year = creditYear;
}
leaveBalance.LeaveTypeId = Convert.ToInt64(item.LeaveTypeId);
leaveBalance.TotalDays = item.TotalDays;
leaveBalance.LeaveTakenDays = 0;
leaveBalance.PendingApprovalDays = 0;
leaveBalance.BalanceDays = item.TotalDays;
leaveBalance.CreatedBy = LoginUserId;
leaveBalance.CreatedOn = DateTime.Now;
leaveBalance.ModifiedBy = LoginUserId;
leaveBalance.ModifiedOn = DateTime.Now;
context.EmployeeLeaveBalance.Add(leaveBalance);
isSaved = context.SaveChanges();
}
LeaveTransactionHistory leaveTransactionHistory = new LeaveTransactionHistory
{
UserId = Convert.ToInt64(item.UserId),
LeaveTypeId = Convert.ToInt64(item.LeaveTypeId),
LeaveId = -1,
TransactionDate = DateTime.Now,
TransactionType = item.CreditOrDebit,
NumberOfDays = item.NoOfDays,
TransactionBy = LoginUserId,
Remarks = item.Remarks
};
context.LeaveTransactionHistory.Add(leaveTransactionHistory);
isSaved = context.SaveChanges();
if (isSaved > 0)
{
continue;
}
else
{
break;
}
}
}
if (isSaved > 0 && isElCredit == true)
{
var leaveType = context.LeaveType.Where(x => x.Type.ToUpper() == "EARNED LEAVE").FirstOrDefault();
if (leaveType != null)
{
leaveType.lastCreditRun = System.DateTime.Now;
leaveType.ModifiedBy = LoginUserId;
leaveType.Modifiedon = System.DateTime.Now;
isSaved = context.SaveChanges();
}
}
if (isSaved > 0)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
}
}
else
{
return("Need Role");
}
}
if (isSaved > 0)
{
return("Saved");
}
else
{
return("Failed");
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public IList <LeaveTransactionDetail> GetTransactionLog(string Name, string RequestMenuUser, long leadUserId)
{
IList <LeaveTransactionDetail> retModel = new List <LeaveTransactionDetail>();
LeaveDac lv = new LeaveDac();
IList <Int64> empList = lv.GetEmployeesReporting(leadUserId);
try
{
using (var context = new NLTDDbContext())
{
EmployeeDac employeeDac = new EmployeeDac();
long userId = 0;
if (RequestMenuUser != "My")
{
userId = employeeDac.GetUserId(Name);
}
if (userId > 0 || (RequestMenuUser == "My" && leadUserId > 0))
{
string ReportingTo = (RequestMenuUser == "My" && leadUserId > 0) ? employeeDac.ReportingToName(leadUserId) : employeeDac.ReportingToName(userId);
List <LeaveTransactionHistoryModel> transactionDetails = new List <LeaveTransactionHistoryModel>();
if (RequestMenuUser == "My")
{
transactionDetails = GetTransactionDetails(context, leadUserId);
}
if (RequestMenuUser == "Team")
{
string leadRole = employeeDac.GetEmployeeRole(leadUserId);
if (leadRole == "ADMIN" || leadRole == "HR")
{
transactionDetails = GetTransactionDetails(context, userId);
}
else
{
var user = empList.Where(x => x == userId).FirstOrDefault();
if (user > 0)
{
transactionDetails = GetTransactionDetails(context, userId);
}
}
}
var groupedLeaveList = transactionDetails.GroupBy(u => u.LeaveTypeId)
.Select(grp => new { LeaveTypeId = grp.Key, leaveTransactionHistoryModel = grp.ToList() })
.ToList();
retModel = (from gv in groupedLeaveList
select new LeaveTransactionDetail
{
ReportingTo = ReportingTo,
LeaveTypeId = gv.LeaveTypeId,
LeaveType = gv.leaveTransactionHistoryModel[0].Type,
leaveTransactionHistoryModel = gv.leaveTransactionHistoryModel
}).ToList();
}
}
}
catch (Exception)
{
throw;
}
return(retModel);
}
public List <EmployeeAttendanceModel> GetAttendanceForRange(Int64 UserID, DateTime FromDateTime, DateTime ToDateTime, string requestLevelPerson, bool isDirectEmployees, bool getBreakStatus)
{
List <EmployeeAttendanceModel> employeeAttendanceModelList = new List <EmployeeAttendanceModel>();
EmployeeDac employeeDac = new EmployeeDac();
IList <Int64> employeeIDs = new List <Int64>();
if (requestLevelPerson.ToUpper() == "MY")
{
employeeIDs.Add(UserID);
}
else
{
if (isDirectEmployees)
{
employeeIDs = employeeDac.GetDirectEmployees(UserID);
}
else
{
employeeIDs = employeeDac.GetEmployeesReporting(UserID);
}
}
using (var context = new NLTDDbContext())
{
employeeAttendanceModelList = (from ea in context.EmployeeAttendance
join e in context.Employee on ea.UserID equals e.UserId
join s in context.ShiftMapping on e.UserId equals s.UserID
join sm in context.ShiftMaster on s.ShiftID equals sm.ShiftID
where employeeIDs.Contains(ea.UserID ?? 0) && ea.InOutDate >= FromDateTime && ea.InOutDate <= ToDateTime &&
ea.InOutDate >= DbFunctions.AddHours(s.ShiftDate, sm.FromTime.Hours - BeforeShiftBuffer) &&
ea.InOutDate <= (sm.ToTime.Hours > 9 ? DbFunctions.AddHours(s.ShiftDate, sm.ToTime.Hours + AfterShiftBuffer) : DbFunctions.AddHours(DbFunctions.AddDays(s.ShiftDate, 1), sm.ToTime.Hours + AfterShiftBuffer))
select new EmployeeAttendanceModel
{
UserID = ea.UserID,
InOutDate = ea.InOutDate,
InOut = (ea.InOut ? "Out" : "In"),
Name = (e.FirstName + " " + e.LastName),
ShiftDate = s.ShiftDate
}).OrderBy(e => e.UserID).ThenBy(e => e.InOutDate).ToList();
}
if (getBreakStatus)
{
TimeSpan punchTime = new TimeSpan();
TimeSpan breakTime = new TimeSpan();
TimeSpan workTime = new TimeSpan();
for (int i = 0; i <= employeeAttendanceModelList.Count - 1; i++)
{
if (i == employeeAttendanceModelList.Count - 1)
{
employeeAttendanceModelList[i].BreakDuration = "Total Work : " + workTime.ToString() + ", Total Break : " + breakTime.ToString();
}
else if (employeeAttendanceModelList[i].UserID == employeeAttendanceModelList[i + 1].UserID)
{
punchTime = (employeeAttendanceModelList[i + 1].InOutDate - employeeAttendanceModelList[i].InOutDate);
if (employeeAttendanceModelList[i].ShiftDate == employeeAttendanceModelList[i + 1].ShiftDate)
{
if (employeeAttendanceModelList[i].InOut == "In")
{
if (employeeAttendanceModelList[i + 1].InOut == "Out")
{
employeeAttendanceModelList[i].BreakDuration = "Work : " + punchTime.ToString();
workTime += punchTime;
}
else
{
employeeAttendanceModelList[i].BreakDuration = "Missing Out Punch";
}
}
else
{
if (employeeAttendanceModelList[i + 1].InOut == "In")
{
employeeAttendanceModelList[i].BreakDuration = "Break : " + punchTime.ToString();
breakTime += punchTime;
}
else
{
employeeAttendanceModelList[i].BreakDuration = "Missing In Punch";
}
}
}
else
{
employeeAttendanceModelList[i].BreakDuration = "Total Work : " + workTime.ToString() + ", Total Break : " + breakTime.ToString();
workTime = TimeSpan.Zero;
breakTime = TimeSpan.Zero;
}
}
else
{
employeeAttendanceModelList[i].BreakDuration = "Total Work : " + workTime.ToString() + ", Total Break : " + breakTime.ToString();
workTime = TimeSpan.Zero;
breakTime = TimeSpan.Zero;
}
}
}
return(employeeAttendanceModelList.OrderBy(e => e.Name).ThenByDescending(e => e.InOutDate).ToList());
}
