/// <summary> /// 根据人员查询项目 /// </summary> public List <XMNick> GetXMProjectListSS(int customerId, int ProjectName) { IQueryable <XMNick> query = from b in this._context.XMNicks join a in this._context.XMProjects on b.ProjectId equals a.Id into JoinedEmpDept from a in JoinedEmpDept.DefaultIfEmpty() join c in this._context.XMNickCustomerMappings on b.nick_id equals c.NickId into JoinedEmpDept2 from c in JoinedEmpDept2.DefaultIfEmpty() where (a.customerId == customerId || c.CustomerID == customerId) && a.IsEnable == true && b.isEnable == true && (a.ProjectTypeId == ProjectName || ProjectName == 0) //group b by new { nick = b.nick, nick_id = b.nick_id } into p select b; return(new List <XMNick>(query.ToList().Distinct())); }
/// <summary> /// 显示查询 /// </summary> /// <param name="hierarchyid"></param> /// <param name="name"></param> /// <param name="englishname"></param> /// <returns></returns> public List <Ooperationuserview> ShowOoperationUser(int hierarchyid = 0, string name = "", string englishname = "") { if (hierarchyid == 0 && (name == "" || name == null) && (englishname == "" || englishname == null)) { var operation = from o1 in db.Ooperationuser join r in db.Role on o1.Roleid equals r.Id into JoinedEmpDept1 from r in JoinedEmpDept1.DefaultIfEmpty() join p in db.Position on o1.PositionId equals p.Id into JoinedEmpDept2 from p in JoinedEmpDept2.DefaultIfEmpty() join pro in db.Professional on o1.ProfessionalId equals pro.Id into JoinedEmpDept3 from pro in JoinedEmpDept3.DefaultIfEmpty() join h in db.Hierarchy on o1.HierarchyId equals h.Id into JoinedEmpDept4 from h in JoinedEmpDept4.DefaultIfEmpty() join o2 in db.Ooperationuser on o1.Userid equals o2.Id into JoinedEmpDept5 from o2 in JoinedEmpDept5.DefaultIfEmpty() select new Ooperationuserview { Id = o1.Id, Jobnumber = o1.Jobnumber, OoperationUserName = o1.OoperationUserName, Phone = o1.Phone, Sex = o1.Sex, PositionName = p.PositionName, ProfessionalName = pro.ProfessionalName, HierarchyName = h.HierarchyName, UserName = o2.OoperationUserName, Enrollmentdate = DateTime.Now, HierarchyId = o1.HierarchyId, Simplename = o1.Simplename }; return(operation.ToList()); } string chinese = ""; string english = ""; //判断是汉字还是拼音 if (name != null && name != "") { bool res = System.Text.RegularExpressions.Regex.IsMatch(name, @"[\u4e00-\u9fbb]"); if (res) { chinese = name; } else { english = name; } } var operations = from o1 in db.Ooperationuser join r in db.Role on o1.Roleid equals r.Id into JoinedEmpDept1 from r in JoinedEmpDept1.DefaultIfEmpty() join p in db.Position on o1.PositionId equals p.Id into JoinedEmpDept2 from p in JoinedEmpDept2.DefaultIfEmpty() join pro in db.Professional on o1.ProfessionalId equals pro.Id into JoinedEmpDept3 from pro in JoinedEmpDept3.DefaultIfEmpty() join h in db.Hierarchy on o1.HierarchyId equals h.Id into JoinedEmpDept4 from h in JoinedEmpDept4.DefaultIfEmpty() join o2 in db.Ooperationuser on o1.Userid equals o2.Id into JoinedEmpDept5 from o2 in JoinedEmpDept5.DefaultIfEmpty() where (o1.HierarchyId == hierarchyid && o1.OoperationUserName == name) || (o1.Simplename == englishname && o1.HierarchyId == hierarchyid) select new Ooperationuserview() { Id = o1.Id, Jobnumber = o1.Jobnumber, OoperationUserName = o1.OoperationUserName, Phone = o1.Phone, Sex = o1.Sex, PositionName = p.PositionName, ProfessionalName = pro.ProfessionalName, HierarchyName = h.HierarchyName, UserName = o2.OoperationUserName, HierarchyId = o1.HierarchyId, Simplename = o1.Simplename }; //中文查询 if (!string.IsNullOrEmpty(chinese)) { operations = operations.Where(p => p.OoperationUserName.Contains(chinese)); } //拼音查询 if (!string.IsNullOrEmpty(english)) { operations = operations.Where(p => p.Simplename.Contains(english)); } //能级下拉查询 if (hierarchyid != 0) { operations = operations.Where(p => p.HierarchyId == hierarchyid); } return(operations.ToList()); }