// 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);
}
}
