// Get the employee with Dept manger role only // Done By fatma public IQueryable <FormList> GetActiveMangers(int companyId, string culture) { var today = DateTime.Today.Date; //Get Departments managers var query1 = (from a in context.Assignments where a.CompanyId == companyId && a.AssignDate <= today && a.EndDate >= today && a.IsDepManager == true select new FormList { id = a.EmpId, name = HrContext.TrlsName(a.Employee.Title + " " + a.Employee.FirstName + " " + a.Employee.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", a.EmpId), Gender = a.Employee.Gender, Icon = HrContext.GetEmpStatus(a.Employee.Id) }); var query2 = (from a in context.Assignments where a.CompanyId == companyId && a.AssignDate <= today && a.EndDate >= today join p in context.People on a.ManagerId equals p.Id select new FormList { id = p.Id, name = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", p.Id), Gender = p.Gender, Icon = HrContext.GetEmpStatus(p.Id) }).Distinct(); var query3 = query1.Union(query2); return(query3); }
public IQueryable <DropDownList> GetAllPeoples(string culture) { return(from p in context.People select new DropDownList { Id = p.Id, Name = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", p.Id), Icon = HrContext.GetEmpStatus(p.Id), Gender = p.Gender }); }
public IQueryable <TrainIndexFollowUpViewModel> GetPeopleTrain(string culture, int CompanyId) { var query = from c in context.PeopleTraining where c.CompanyId == CompanyId && c.ApprovalStatus == 6 join p in context.People on c.EmpId equals p.Id join Cu in context.TrainCourses on c.CourseId equals Cu.Id join Ev in context.TrainEvents on c.EventId equals Ev.Id into Ev1 from Ev in Ev1.DefaultIfEmpty() join wft in context.WF_TRANS on new { p1 = "Training", p2 = c.CompanyId, p3 = c.Id } equals new { p1 = wft.Source, p2 = wft.SourceId, p3 = wft.DocumentId } into g from wft in g.DefaultIfEmpty() join ap in context.People on wft.AuthEmp equals ap.Id into g1 from ap in g1.DefaultIfEmpty() join apos in context.Positions on wft.AuthPosition equals apos.Id into g2 from apos in g2.DefaultIfEmpty() join dep in context.CompanyStructures on wft.AuthDept equals dep.Id into g3 from dep in g3.DefaultIfEmpty() join role in context.Roles on wft.RoleId equals role.Id into g4 from role in g4.DefaultIfEmpty() select new TrainIndexFollowUpViewModel { Id = c.Id, EmpId = c.EmpId, Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture), ActualHours = c.ActualHours, Event = Ev.Name, Course = HrContext.TrlsName(Cu.Name, culture), CourseEDate = c.CourseEDate, EventId = c.EventId, RequestDate = c.RequestDate, Status = c.Status, CompanyId = c.CompanyId, ApprovalStatus = c.ApprovalStatus, CourseSDate = c.CourseSDate, HasImage = p.HasImage, RoleId = wft.RoleId.ToString(), DeptId = wft.DeptId, PositionId = wft.PositionId, AuthBranch = wft.AuthBranch, AuthDept = wft.AuthDept, AuthDeptName = HrContext.TrlsName(dep.Name, culture), AuthEmp = wft.AuthEmp, AuthEmpName = HrContext.TrlsName(ap.Title + " " + ap.FirstName + " " + ap.Familyname, culture), AuthPosition = wft.AuthPosition, AuthPosName = role == null?HrContext.TrlsName(apos.Name, culture) : role.Name, BranchId = wft.BranchId, CourseTitle = c.CourseTitle, Attachement = HrContext.GetDoc("EmployeePic", p.Id), Gender = p.Gender, EmpStatus = HrContext.GetEmpStatus(p.Id) }; return(query); }
public IQueryable <WorkFlowViewModel> GetAllRequests(int companyId, string culture) { var result = from wft in context.WF_TRANS where wft.CompanyId == companyId join p in context.People on wft.EmpId equals p.Id join d in context.CompanyStructures on wft.DeptId equals d.Id join b in context.CompanyStructures on wft.BranchId equals b.Id into g from b in g.DefaultIfEmpty() join ap in context.People on wft.AuthEmp equals ap.Id into g1 from ap in g1.DefaultIfEmpty() join apos in context.Positions on wft.AuthPosition equals apos.Id into g2 from apos in g2.DefaultIfEmpty() join dep in context.CompanyStructures on wft.AuthDept equals dep.Id into g3 from dep in g3.DefaultIfEmpty() join role in context.Roles on wft.RoleId equals role.Id into g4 from role in g4.DefaultIfEmpty() join postion in context.Positions on wft.PositionId equals postion.Id into g5 from postion in g5.DefaultIfEmpty() select new WorkFlowViewModel { Id = wft.DocumentId, Source = wft.Source, ApprovalStatus = wft.ApprovalStatus, Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture), Department = HrContext.TrlsName(d.Name, culture), Branch = HrContext.TrlsName(b.Name, culture), AuthEmpName = HrContext.TrlsName(ap.Title + " " + ap.FirstName + " " + ap.Familyname, culture), AuthDeptName = HrContext.TrlsName(dep.Name, culture), AuthPosName = role == null?HrContext.TrlsName(apos.Name, culture) : role.Name, CreatedTime = wft.CreatedTime, CreatedUser = wft.CreatedUser, HasImage = p.HasImage, CompanyId = companyId, EmpId = wft.EmpId, RoleId = wft.RoleId.ToString(), DeptId = wft.DeptId, PositionId = wft.PositionId, AuthBranch = wft.AuthBranch, AuthDept = wft.AuthDept, AuthEmp = wft.AuthEmp, AuthPosition = wft.AuthPosition, BranchId = wft.BranchId, EmpStatus = HrContext.GetEmpStatus(p.Id), PositionName = HrContext.TrlsName(postion.Name, culture), LocalRoleName = HrContext.TrlsName(role.Name, culture), }; return(result); }
public IQueryable <FormList> GetActiveMangersByMangerId(int companyId, string culture, int MangerId) { var today = DateTime.Today.Date; var query1 = (from a in context.Assignments where a.CompanyId == companyId && a.AssignDate <= today && a.EndDate >= today && a.EmpId == MangerId select new FormList { id = a.EmpId, name = HrContext.TrlsName(a.Employee.Title + " " + a.Employee.FirstName + " " + a.Employee.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", a.EmpId), Gender = a.Employee.Gender, Icon = HrContext.GetEmpStatus(a.Employee.Id) }); return(query1); }
public IEnumerable <FormList> GetEmployeeById(int companyId, string culture, int EmpId) { var today = DateTime.Today.Date; return((from a in context.Assignments where a.EmpId == EmpId && a.AssignDate <= today && a.EndDate >= today join p in context.People on a.EmpId equals p.Id select new FormList { id = p.Id, name = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", p.Id), Gender = p.Gender, Icon = HrContext.GetEmpStatus(p.Id) }).ToList()); }
public IQueryable <PeoplesViewModel> GetPeople(string culture) { var q = from p in context.People join e in context.Employements on p.Id equals e.EmpId where e.Status == 1 select new PeoplesViewModel { Id = e.EmpId, Title = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", p.Id), Gender = p.Gender, EmpStatus = HrContext.GetEmpStatus(p.Id) }; return(q); }
// public IQueryable <FormList> GetEmployeeManagedByManagerId(int companyId, string culture, int MangId) { var today = DateTime.Today.Date; // Get employees direct managed by manager Id // or get employees in the manager tree var query1 = from a in context.Assignments where a.CompanyId == companyId && a.AssignDate <= today && a.EndDate >= today && a.SysAssignStatus == 1 && a.EmpId != MangId && (a.ManagerId == MangId || (a.Department.Sort.StartsWith(context.Assignments.Where(b => b.EmpId == MangId && b.AssignDate <= today && b.EndDate >= today && b.SysAssignStatus == 1 && b.IsDepManager).Select(b => b.Department.Sort).FirstOrDefault()))) select new FormList { id = a.EmpId, name = HrContext.TrlsName(a.Employee.Title + " " + a.Employee.FirstName + " " + a.Employee.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", a.EmpId), Gender = a.Employee.Gender, Icon = HrContext.GetEmpStatus(a.Employee.Id) }; return(query1); }
public IQueryable <EmpDisciplineViewModel> ReadEmpDiscipline(string culture, int CompanuId) { var EmpDisplin = from c in context.EmpDisciplines join e in context.People on c.EmpId equals e.Id join a in context.Assignments on e.Id equals a.EmpId where (a.CompanyId == CompanuId && a.AssignDate <= DateTime.Today.Date && a.EndDate >= DateTime.Today.Date) select new EmpDisciplineViewModel { Id = c.Id, ActDispline = c.ActDispline.Value, ActualNofDays = c.ActualNofDays, ActualPeriod = c.ActualPeriod, DeductPoint = c.DeductPoint, DescionDate = c.DescionDate, DescionNo = c.DescionNo, SuggPeriod = c.SuggPeriod, Defense = c.Defense, Description = c.Description, EmpId = HrContext.TrlsName(e.Title + " " + e.FirstName + " " + e.Familyname, culture), DiscplinId = c.DiscplinId, ViolDate = c.ViolDate, Summary = c.Summary, SuggDispline = c.SuggDispline, EffectEDate = c.EffectEDate, Manager = c.Manager, SuggNofDays = c.SuggNofDays, DeptId = a.DepartmentId, BranchId = a.BranchId, PositionId = a.PositionId, Image = HrContext.GetDoc("EmployeePic", c.EmpId), Gender = e.Gender, EmpStatus = HrContext.GetEmpStatus(e.Id), empId = c.EmpId }; return(EmpDisplin); }
public PeoplesViewModel ReadPerson(int id, string culture) { var person = (from p in context.People where p.Id == id select new PeoplesViewModel { Id = p.Id, BloodClass = p.BloodClass, BirthCity = p.BirthCity, BirthCountry = p.BirthCountry, WorkTel = p.WorkTel, ProviderId = p.ProviderId, EmergencyTel = p.EmergencyTel, BirthDate = p.BirthDate, ExpiryDate = p.ExpiryDate, Familyname = p.Familyname, Gender = p.Gender, localName = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture), Attachments = HrContext.GetAttachments("People", p.Id), BirthDstrct = p.BirthDstrct, Fathername = p.Fathername, FirstName = p.FirstName, HomeTel = p.HomeTel, GFathername = p.GFathername, IssueDate = p.IssueDate, JoinDate = p.JoinDate, IssuePlace = p.IssuePlace, KafeelId = p.KafeelId, InspectDate = p.InspectDate, MaritalStat = p.MaritalStat, MedStatDate = p.MedStatDate, Mobile = p.Mobile, MedicalStat = p.MedicalStat, Rank = p.Rank, MilResDate = p.MilResDate, MilCertGrade = p.MilCertGrade, MilitaryNo = p.MilitaryNo, MilStatDate = p.MilStatDate, NationalId = p.NationalId, MilitaryStat = p.MilitaryStat, Nationality = p.Nationality, PassportNo = p.PassportNo, Profession = p.Profession, RecommenReson = p.RecommenReson, Recommend = p.Recommend, Religion = p.Religion, OtherEmail = p.OtherEmail, Ssn = p.Ssn, QualificationId = p.QualificationId, Title = p.Title, WorkEmail = p.WorkEmail, TaxFamlyCnt = p.TaxFamlyCnt, BnftFamlyCnt = p.BnftFamlyCnt, StartExpDate = p.StartExpDate, EmpStatus = HrContext.GetEmpStatus(p.Id), CreatedTime = p.CreatedTime, ModifiedTime = p.ModifiedTime, ModifiedUser = p.ModifiedUser, CreatedUser = p.CreatedUser, PicUrl = HrContext.GetDocument("EmployeePic", p.Id), HasImage = p.HasImage, IdIssueDate = p.IdIssueDate, VisaNo = p.VisaNo, VarSubAmt = p.VarSubAmt, BasicSubAmt = p.BasicSubAmt, SubscripDate = p.SubscripDate, Address1 = p.Address1, CityId = p.CityId, CountryId = p.CountryId, DistrictId = p.DistrictId, HoAddress = p.HoAddress, Latitude = p.Latitude, Longitude = p.Longitude, TreatCardNo = p.TreatCardNo, Status = p.Status, PaperStatus = p.PaperStatus }).FirstOrDefault(); return(person); }
public IEnumerable GetManagerEmpList(int managerId, int?positionId, int companyId, string culture) { var EmpTasks = context.Assignments.Where(a => a.CompanyId == companyId && (a.AssignDate <= DateTime.Today && a.EndDate >= DateTime.Today) && a.EmpId == managerId).Select(a => a.EmpTasks).FirstOrDefault(); if (EmpTasks == 2) //2-Use eligibility criteria { string sql = "SELECT P.Id, dbo.fn_TrlsName(ISNULL(P.Title, '') + ' ' + P.FirstName +' ' + P.Familyname , '" + culture + "') Name , [dbo].[fn_GetDoc]('EmployeePic', P.Id) PicUrl, P.Gender, dbo.fn_GetEmpStatus(P.Id) Icon FROM Assignments M, Assignments A, Employements E, People P WHERE M.EmpId = " + managerId + " AND M.CompanyId = " + companyId + " And (GETDATE() Between M.AssignDate And M.EndDate) And A.EmpId = E.EmpId And A.EmpId = P.Id AND A.CompanyId = " + companyId + " AND (GETDATE() Between A.AssignDate And A.EndDate) AND E.Status = 1 AND (CASE WHEN LEN(M.Employments) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.Employments, ',') WHERE VALUE = E.PersonType), 0) ELSE E.PersonType END) = E.PersonType AND (CASE WHEN LEN(M.Jobs) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.Jobs, ',') WHERE VALUE = A.JobId), 0) ELSE A.JobId END) = A.JobId AND (CASE WHEN LEN(M.CompanyStuctures) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.CompanyStuctures, ',') WHERE VALUE = A.DepartmentId), 0) ELSE A.DepartmentId END) = A.DepartmentId AND (CASE WHEN LEN(M.Branches) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.Branches, ',') WHERE VALUE = ISNULL(A.BranchId, 0)), -1) ELSE ISNULL(A.BranchId, 0) END) = ISNULL(A.BranchId, 0) AND (CASE WHEN LEN(M.Positions) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.Positions, ',') WHERE VALUE = ISNULL(A.PositionId, 0)), -1) ELSE ISNULL(A.PositionId, 0) END) = ISNULL(A.PositionId, 0) AND (CASE WHEN LEN(M.PeopleGroups) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.PeopleGroups, ',') WHERE VALUE = ISNULL(A.GroupId, 0)), -1) ELSE ISNULL(A.GroupId, 0) END) = ISNULL(A.GroupId, 0) AND (CASE WHEN LEN(M.Payrolls) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.Payrolls, ',') WHERE VALUE = ISNULL(A.PayrollId, 0)), -1) ELSE ISNULL(A.PayrollId, 0) END) = ISNULL(A.PayrollId, 0) AND (CASE WHEN LEN(M.PayrollGrades) > 0 THEN ISNULL((SELECT VALUE FROM STRING_SPLIT(M.PayrollGrades, ',') WHERE VALUE = ISNULL(A.PayGradeId, 0)), -1) ELSE ISNULL(A.PayGradeId, 0) END) = ISNULL(A.PayGradeId, 0)"; return(context.Database.SqlQuery <DropDownList>(sql).Select(a => new { id = a.Id, name = a.Name, a.PicUrl, a.Icon }).ToList()); } else //1-Employee whose direct managed { var emps = context.Assignments .Where(a => a.CompanyId == companyId && (a.AssignDate <= DateTime.Today && a.EndDate >= DateTime.Today) && a.ManagerId == managerId) .Union(context.Assignments .Where(a => a.CompanyId == companyId && (a.AssignDate <= DateTime.Today && a.EndDate >= DateTime.Today) && positionId != null && a.Position.Supervisor == positionId)) .Select(a => new { id = a.EmpId, name = HrContext.TrlsName(a.Employee.Title + " " + a.Employee.FirstName + " " + a.Employee.Familyname, culture), PicUrl = HrContext.GetDoc("EmployeePic", a.EmpId), Gender = a.Employee.Gender, Icon = HrContext.GetEmpStatus(a.EmpId) }); return(emps); } }