//read Borrow Document with Employee
public IEnumerable <EmpDocBorrowViewModel> ReadDocBorrow(byte Range, DateTime?Start, DateTime?End, int CompanyId, string culture)
{
//10- All, 0-Custom
if (Range != 10 && Range != 0)
{
RequestRangeFilter(Range, CompanyId, out Start, out End);
}
var query = (from d in context.EmpDocBorrow
where (d.CompanyId == CompanyId && d.delvryDate == null)
join p in context.People on d.EmpId equals p.Id
select new EmpDocBorrowViewModel
{
Id = d.Id,
CompanyId = d.CompanyId,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
EmpId = d.EmpId,
Purpose = d.Purpose,
Notes = d.Notes,
RecvDate = d.RecvDate,
Site = d.Site,
ExpdelvryDate = d.ExpdelvryDate,
AttUrl = HrContext.GetDoc("BorrowPapers", d.Id),
Document = context.DocBorrowList.Where(a => a.DocBorrowId == d.Id).Select(s => s.Doc.Name).ToList()
}).ToList();
if (Range != 10)
{
query = query.Where(c => Start <= c.RecvDate && End >= c.RecvDate).ToList();
}
return(query);
}
public IQueryable <EmpChkListViewModel> GetEmpCheckLists(string culture, int companyId)
{
var EmpList = from el in context.EmpChkLists
where el.CompanyId == companyId
join p in context.People on el.EmpId equals p.Id into g
from p in g.DefaultIfEmpty()
select new EmpChkListViewModel
{
Id = el.Id,
EmpId = el.EmpId,
ListEndDate = el.ListEndDate,
ListStartDate = el.ListStartDate,
PicUrl = HrContext.GetDoc("EmployeePic", p.Id),
Gender = p.Gender,
ListType = el.ListType,
Name = el.Name,
Employee = p != null?HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture) : "",
Status = el.Status,
CreatedUser = el.CreatedUser,
ManagerId = el.ManagerId,
Count = context.EmpTasks.Where(a => a.EmpListId == el.Id).Count(),
PrograssBar = context.EmpTasks.Where(a => a.EmpListId == el.Id && a.Status == 2).Count()
};
return(EmpList);
}
public IQueryable <CustodyViewModel> ReadEmpCustody(int CompanyId, string culture)
{
var result = (from c in context.Custody
where (c.CompanyId == CompanyId && c.InUse == true)
join Emp in context.EmpCustodies on c.Id equals Emp.CustodyId
where Emp.delvryDate == null
join p in context.People on Emp.EmpId equals p.Id
join l in context.Sites on Emp.BranchId equals l.Id into j
from l in j.DefaultIfEmpty()
select new CustodyViewModel
{
Id = c.Id,
EmpCustodyId = Emp.Id,
Code = c.Code,
Name = c.Name,
SerialNo = c.SerialNo,
CompanyId = c.CompanyId,
CustodyCatId = HrContext.TrlsName(c.CustodyCat.Name, culture),
PurchaseAmount = c.PurchaseAmount,
Status = c.Status,
InUse = c.InUse,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
Branch = HrContext.TrlsName(l.Name, culture),
EmpId = Emp.EmpId,
Description = c.Description,
Qty = Emp.Qty,
RecvDate = Emp.RecvDate,
PurchaseDate = c.PurchaseDate,
AttUrl = HrContext.GetDoc("RecieveCustody", Emp.Id),
Filename = context.CompanyDocsView.Where(a => a.Source == "RecieveCustody" && a.SourceId == Emp.Id).Select(c => c.name).FirstOrDefault()
});
return(result);
}
// 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 <MedicalIndexViewModel> GetApprovedMedicalReq(int companyId, string culture)
{
return(from l in context.BenefitRequests
where l.CompanyId == companyId && l.ApprovalStatus == 6
join n in context.BenefitServs on l.ServiceId equals n.Id
join prov in context.Providers on l.ProviderId equals prov.Id
join p in context.People on l.EmpId equals p.Id
join wft in context.WF_TRANS on new { p1 = "Medical", p2 = l.CompanyId, p3 = l.Id } equals new { p1 = wft.Source, p2 = wft.SourceId, p3 = wft.DocumentId } into g
from wft in g.DefaultIfEmpty()
select new MedicalIndexViewModel
{
Id = l.Id,
RequestDate = l.RequestDate,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
ApprovalStatus = l.ApprovalStatus,
CompanyId = l.CompanyId,
EmpId = l.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,
Attachment = HrContext.GetDoc("EmployeePic", p.Id),
Gender = p.Gender,
Service = n.Name,
Provider = prov.Name
});
}
public IQueryable <RenewRequestViewModel> ReadRenewRequestTabs(int companyId, byte Tab, byte Range, DateTime?Start, DateTime?End, string culture, byte Version, int EmpId)
{
if (Range != 10 && Range != 0)
{
RequestRangeFilter(Range, companyId, out Start, out End);
}
DateTime Today = DateTime.Today.Date;
var query1 = from RQ in context.RenewRequests
where RQ.EmpId == EmpId && RQ.CompanyId == companyId
select RQ;
if (Range != 10) // Allow date range
{
query1 = query1.Where(l => Start <= l.RequestDate && l.RequestDate <= End);
}
if (Tab == 1) //Pending
{
query1 = query1.Where(l => l.ApprovalStatus == 2);
}
else if (Tab == 2) //Approved
{
query1 = query1.Where(l => l.ApprovalStatus == 6);
}
else if (Tab == 3) //Rejected
{
query1 = query1.Where(l => l.ApprovalStatus == 9);
}
IQueryable <RenewRequestViewModel> query = from q in query1
select new RenewRequestViewModel
{
Id = q.Id,
ApprovalStatus = q.ApprovalStatus,
ColumnName = HrContext.GetColumnTitle(companyId, culture, "People", Version, q.ColumnName),
EmpId = q.EmpId,
CompanyId = q.CompanyId,
NewValue = q.NewValue,
NewValueId = q.NewValueId,
OldValue = q.OldValue,
OldValueId = q.OldValueId,
RequestDate = q.RequestDate,
CreatedUser = q.CreatedUser,
CreatedTime = q.CreatedTime,
RejectionRes = q.RejectionRes,
AttUrl = HrContext.GetDoc("RenewRequest", q.Id)
};
return(query);
}
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 IEnumerable ReadEmployeeTasks(int CompanyId, int empId, string culture)
{
var tasks = context.EmpTasks.Where(t => t.EmpId == empId && t.CompanyId == CompanyId && t.Status == 1).OrderBy(t => t.Priority).Select(t => new NavBarItemVM
{
Id = t.Id,
From = HrContext.GetLookUpCode("EmpTaskCat", t.TaskCat.Value, culture),
Message = t.Description,
MoreInfo = HrContext.TrlsName(t.EmpChklist.Employee.Title + " " + t.EmpChklist.Employee.FirstName + " " + t.EmpChklist.Employee.Familyname, culture),
PicUrl = HrContext.GetDoc("EmployeePic", t.Manager.Id),
Gender = t.Manager.Gender
}).Take(5).ToList();
return(tasks);
}
public IQueryable <NavBarItemVM> GetAllNotifications(string UserName, string Lan, int CompanyId)
{
//context.CompanyDocsView.Where(c=>c.SourceId==)
//_hrUnitOfWork.Repository<CompanyDocsViews>().Where(a => a.Source == source && a.SourceId == id).Select(a => a.file_stream).FirstOrDefault();
return(context.WebMobLog.Include(ww => ww.Notificat).Where(a => a.SentToUser == UserName && a.CompanyId == CompanyId).Select(c => new NavBarItemVM
{
Id = c.Id,
From = c.Subject == null ? " " : c.Subject,
Message = c.Message,
PicUrl = HrContext.GetDoc("EmployeePic", c.Notificat.EmpId.Value), // please check null in view not here
Read = c.MarkAsRead,
SentDate = c.SentTime
}).OrderByDescending(a => a.SentDate));
}
//for navbar
public IEnumerable ReadEmpMessages(int CompanyId, int empId, string culture)
{
var query = (from m in context.MsgEmployees
where m.ToEmpId == empId && !m.Read && m.Message.CompanyId == CompanyId
join p in context.People on m.FromEmpId equals p.Id
select new NavBarItemVM
{
Id = m.Id,
PicUrl = HrContext.GetDoc("EmployeePic", p.Id),
Gender = p.Gender,
From = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
Message = m.Message.Title
}).Take(5).ToList();
return(query);
}
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> 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 <TerminationGridViewModel> ReadTermRequests(int companyId, string culture)
{
var requests = from tr in context.Terminations
where tr.CompanyId == companyId
join wft in context.WF_TRANS on new { p1 = "Termination", p2 = tr.CompanyId, p3 = tr.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
join role in context.Roles on wft.RoleId equals role.Id into g4
from role in g4.DefaultIfEmpty()
from dep in g3.DefaultIfEmpty()
select new TerminationGridViewModel
{
Id = tr.Id,
EmpId = tr.EmpId,
CompanyId = tr.CompanyId,
AssignStatus = HrContext.GetLookUpUserCode("Assignment", tr.AssignStatus, culture),
RequestDate = tr.RequestDate,
Employee = HrContext.TrlsName(tr.Employee.Title + " " + tr.Employee.FirstName + " " + tr.Employee.Familyname, culture),
JoinedDate = tr.ServStartDate,
PersonType = HrContext.GetLookUpUserCode("PersonType", tr.PersonType, culture),
BonusInMonths = tr.BonusInMonths,
ServYear = tr.ServYear,
ApprovalStatus = tr.ApprovalStatus,
TermReason = HrContext.GetLookUpUserCode("Termination", tr.TermReason, culture),
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,
Image = HrContext.GetDoc("EmployeePic", tr.Employee.Id),
Gender = tr.Employee.Gender
};
return(requests);
}
public IQueryable <MedicalIndexViewModel> GetMedicalReqFollowUp(int companyId, string culture)
{
var query = from c in context.BenefitRequests
where c.CompanyId == companyId && c.ApprovalStatus < 6 && c.ApprovalStatus > 1
join n in context.BenefitServs on c.ServiceId equals n.Id
join prov in context.Providers on c.ProviderId equals prov.Id
join p in context.People on c.EmpId equals p.Id
join wft in context.WF_TRANS on new { p1 = "Medical", 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 MedicalIndexViewModel
{
Id = c.Id,
EmpId = c.EmpId,
RequestDate = c.RequestDate,
Employee = HrContext.TrlsName(p.Title + " " + p.FirstName + " " + p.Familyname, culture),
CompanyId = c.CompanyId,
ApprovalStatus = c.ApprovalStatus,
Attachment = HrContext.GetDoc("EmployeePic", p.Id),
Gender = p.Gender,
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,
Service = n.Name,
Provider = prov.Name
};
return(query);
}
public IQueryable <CompanyViewModel> GetAllCompanies(string culture)
{
var lang = culture.Split('-')[0];
var result = context.Companies.Select(company => new CompanyViewModel
{
Id = company.Id,
SearchName = company.SearchName != null ? company.SearchName : "",
Name = company.Name,
LocalName = HrContext.TrlsName(company.Name, culture),
LogoUrl = HrContext.GetCompanyDoc("Company", company.Id, 1),
Purpose = HrContext.GetLookUpCode("Purpose", company.Purpose.Value, culture),
Country = lang == "ar" ? company.Country.NameAr : company.Country.Name,
Email = company.Email,
WebSite = company.WebSite,
Attachement = HrContext.GetDoc("CompanyLogo", company.Id)
});
return(result);
}
//
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 <CompanyDiagramViewModel> GetDiagram(int companyId, string Culture)
{
var compDiag = from s in context.CompanyStructures
where ((s.CompanyId == companyId) && (s.StartDate <= DateTime.Today && (s.EndDate == null || s.EndDate >= DateTime.Today)))
join a in context.Assignments on s.Id equals a.DepartmentId into g
from f in g.Where(c => c.CompanyId == companyId && c.AssignDate <= DateTime.Today.Date && c.EndDate >= DateTime.Today.Date && c.IsDepManager).DefaultIfEmpty()
orderby s.Sort
select new CompanyDiagramViewModel
{
Id = s.Id,
Image = f != null?HrContext.GetDoc("EmployeePic", f.EmpId) : null,
Employee = f != null?HrContext.TrlsName(f.Employee.Title + " " + f.Employee.FirstName + " " + f.Employee.Familyname, Culture) : null,
Name = HrContext.TrlsName(f == null ? s.Name : f.Department.Name, Culture),
ParentId = s.ParentId,
colorSchema = s.ColorName == null ? "#55dd28" : s.ColorName,
HasImage = f != null ? f.Employee.HasImage : false,
Gender = f != null ? f.Employee.Gender : (short)1
};
return(compDiag);
}
public IQueryable <TerminationGridViewModel> ReadTermsApproved(int companyId, string culture)
{
var approved = from tr in context.Terminations
where tr.CompanyId == companyId && tr.ApprovalStatus == 6 && tr.Terminated == false
select new TerminationGridViewModel
{
Id = tr.Id,
EmpId = tr.EmpId,
AssignStatus = HrContext.GetLookUpUserCode("Assignment", tr.AssignStatus, culture),
RequestDate = tr.RequestDate,
Employee = HrContext.TrlsName(tr.Employee.Title + " " + tr.Employee.FirstName + " " + tr.Employee.Familyname, culture),
JoinedDate = tr.ServStartDate,
PersonType = HrContext.GetLookUpUserCode("PersonType", tr.PersonType, culture),
BonusInMonths = tr.BonusInMonths,
ServYear = tr.ServYear,
TermReason = HrContext.GetLookUpUserCode("Termination", tr.TermReason, culture),
Image = HrContext.GetDoc("EmployeePic", tr.Employee.Id),
Gender = tr.Employee.Gender
};
return(approved);
}
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 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);
}
}
public NavBarItemVM GetNotify(int Id, string culture, int CompanyId, string UserName)
{
var log = context.WebMobLog.Include(w => w.Notificat).Where(a => a.Id == Id && a.CompanyId == CompanyId && a.SentToUser == UserName).Select(a => new { weblog = a, PicUrl = HrContext.GetDoc("EmployeePic", a.Notificat.EmpId.Value) }).FirstOrDefault();
var weblog = log.weblog;
bool Read = true;
if (weblog != null)
{
if (!weblog.MarkAsRead)
{
Read = false;
weblog.MarkAsRead = true;
Attach(weblog);
context.Entry(weblog).State = EntityState.Modified;
context.SaveChanges();
}
return(new NavBarItemVM()
{
From = weblog.Subject,
Read = Read,
Id = weblog.Id,
Message = weblog.Message,
PicUrl = log.PicUrl ?? "noimage.jpg", // please adjust ur view
SentDate = weblog.SentTime
});
}
else
{
return new NavBarItemVM()
{
From = "",
Read = Read,
Id = 0,
Message = "",
PicUrl = "noimage.jpg",
}
};
}
public IQueryable <MeetingViewModel> GetMeetings(int EmpId, byte Range, DateTime?Start, DateTime?End, string culture, int CompanyId, DateTime currentTime)
{
//10- All, 0-Custom
if (Range != 10 && Range != 0)
{
RequestRangeFilter(Range, CompanyId, out Start, out End);
}
// Organizer
var query = (from m in context.Meetings
where m.CompanyId == CompanyId && m.EmpId == EmpId && m.Status != 3
join P in context.People on m.EmpId equals P.Id
select new MeetingViewModel
{
Id = m.Id,
MeetDate = m.MeetDate,
Status = m.Status,
StartTime = m.StartTime.ToString(),
EndTime = m.EndTime.ToString(),
MeetSubject = HrContext.GetLookUpCode("MeetSubject", m.MeetSubject, culture),
EmpId = m.EmpId,
organiser = HrContext.TrlsName(P.Title + " " + P.FirstName + " " + P.Familyname, culture),
IsActivate = m.IsActivate == true ? "active" : "inactive",
SubjectDesc = m.SubjectDesc,
AttUrl = HrContext.GetDoc("Meeting", m.Id),
IsOrganiser = true,
}).Union(from m in context.Meetings
where m.CompanyId == CompanyId && m.Status != 3
join ma in context.MeetAttendees on m.Id equals ma.MeetingId
where ma.EmpId == EmpId && m.EmpId != EmpId
join P in context.People on m.EmpId equals P.Id
select new MeetingViewModel
{
Id = m.Id,
MeetDate = m.MeetDate,
Status = m.Status,
StartTime = m.StartTime.ToString(),
EndTime = m.EndTime.ToString(),
MeetSubject = HrContext.GetLookUpCode("MeetSubject", m.MeetSubject, culture),
EmpId = m.EmpId,
organiser = HrContext.TrlsName(P.Title + " " + P.FirstName + " " + P.Familyname, culture),
IsActivate = m.IsActivate == true ? "active" : "inactive",
SubjectDesc = m.SubjectDesc,
AttUrl = null,
IsOrganiser = false,
}).Union(from m in context.Meetings
where m.CompanyId == CompanyId && m.Status != 3
join ma in context.MeetViewers on m.Id equals ma.MeetingId
where ma.EmpId == EmpId && m.EmpId != EmpId
join P in context.People on m.EmpId equals P.Id
select new MeetingViewModel
{
Id = m.Id,
MeetDate = m.MeetDate,
Status = m.Status,
StartTime = m.StartTime.ToString(),
EndTime = m.EndTime.ToString(),
MeetSubject = HrContext.GetLookUpCode("MeetSubject", m.MeetSubject, culture),
EmpId = m.EmpId,
organiser = HrContext.TrlsName(P.Title + " " + P.FirstName + " " + P.Familyname, culture),
IsActivate = m.IsActivate == true ? "active" : "inactive",
SubjectDesc = m.SubjectDesc,
AttUrl = null,
IsOrganiser = false,
}).ToList().Select(l => new MeetingViewModel
{
Id = l.Id,
StartTime = Convert.ToDateTime(l.StartTime).ToString("hh:mm tt"),
EndTime = Convert.ToDateTime(l.EndTime).ToString("hh:mm tt"),
Status = l.Status,
MeetDate = l.MeetDate,
SubjectDesc = l.SubjectDesc,
MeetSubject = l.MeetSubject,
Time = (Convert.ToDateTime(l.EndTime) - Convert.ToDateTime(l.StartTime)).ToString(@"hh\:mm"),
EmpId = l.EmpId,
organiser = l.organiser,
IsActivate = l.IsActivate,
AttUrl = l.AttUrl,
Attendes = l.Attendes,
IsOrganiser = l.IsOrganiser,
CanUpload = (currentTime.Date > l.MeetDate.Date || (currentTime.Date == l.MeetDate.Date && Convert.ToDateTime(l.EndTime).TimeOfDay < currentTime.TimeOfDay)) ? true : false,
CanCancel = (currentTime.Date > l.MeetDate.Date || (currentTime.Date == l.MeetDate.Date && Convert.ToDateTime(l.StartTime).TimeOfDay <= currentTime.TimeOfDay)) ? true : false
});
if (Range != 10)
{
query = query.Where(c => Start <= c.MeetDate && End >= c.MeetDate);
}
return(query.OrderByDescending(a => a.MeetDate).AsQueryable());
}