/// <summary> /// 社保专员提取报增数据 /// </summary> /// <param name="SysEntities">数据访问的上下文</param> /// <param name="page">页码</param> /// <param name="rows">行数</param> /// <returns></returns> public List <EmployeeGoonPaymentView> GetEmployeeGoonPaymentExcelList(SysEntities db, int page, int rows, string search, ref int count) { using (var ent = new SysEntities()) { try { #region 查询条件 var emp = ent.Employee.Where(a => true); var empGoon = ent.EmployeeGoonPayment.Where(a => true); var com = ent.CRM_Company.Where(a => true); var city = ent.City.Where(a => true); Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString()); if (queryDic != null && queryDic.Count > 0) { if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"])) { string str = queryDic["Name"]; emp = emp.Where(a => a.Name.Contains(str)); } if (queryDic.ContainsKey("CertificateNumber") && !string.IsNullOrWhiteSpace(queryDic["CertificateNumber"])) { string[] cardIdList = queryDic["CertificateNumber"].Split(Convert.ToChar(10)); List <string> cardList = new List <string>(); for (int i = 0; i < cardIdList.Length; i++) { cardList.Add(cardIdList[i]); cardList.Add(CardCommon.CardIDTo15(cardIdList[i])); cardList.Add(CardCommon.CardIDTo18(cardIdList[i])); } cardList = cardList.Distinct().ToList(); emp = emp.Where(o => cardList.Contains(o.CertificateNumber)); } if (queryDic.ContainsKey("State") && !string.IsNullOrWhiteSpace(queryDic["State"])) { string str = queryDic["State"]; string[] states = str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); empGoon = empGoon.Where(a => states.Contains(a.State)); } if (queryDic.ContainsKey("InsuranceKinds") && !string.IsNullOrWhiteSpace(queryDic["InsuranceKinds"])) { string str = queryDic["InsuranceKinds"]; int?[] Ids = Array.ConvertAll <string, int?>(str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), delegate(string s) { return(int.Parse(s)); }); empGoon = empGoon.Where(a => Ids.Contains(a.EmployeeAdd.InsuranceKindId)); } if (queryDic.ContainsKey("YearMonth") && !string.IsNullOrWhiteSpace(queryDic["YearMonth"])) { int str = Convert.ToInt32(queryDic["YearMonth"]); empGoon = empGoon.Where(a => a.YearMonth == str); } if (queryDic.ContainsKey("CompanyName") && !string.IsNullOrWhiteSpace(queryDic["CompanyName"])) { string str = queryDic["CompanyName"]; com = com.Where(a => a.CompanyName.Contains(str)); } if (queryDic.ContainsKey("CityId") && !string.IsNullOrWhiteSpace(queryDic["CityId"])) { string str = queryDic["CityId"]; city = city.Where(a => a.Id == str); } } var list = (from a in emp join r in ent.CompanyEmployeeRelation on a.Id equals r.EmployeeId join b in empGoon on r.Id equals b.EmployeeAdd.CompanyEmployeeRelationId join c in com on r.CompanyId equals c.ID //join d in ent.InsuranceKind on b.InsuranceKindId equals d.Id join e in city on r.CityId equals e.Id join f in ent.PoliceAccountNature on b.EmployeeAdd.PoliceAccountNatureId equals f.Id select new EmployeeGoonPaymentView() { CompanyEmployeeRelationId = b.EmployeeAdd.CompanyEmployeeRelationId, CompanyId = r.CompanyId, CompanyName = c.CompanyName, Name = a.Name, CertificateNumber = a.CertificateNumber, City = e.Name, // CityID = r.CityId, PoliceAccountNatureName = f.Name, YearMonth = (int)b.YearMonth, CompanyCode = c.CompanyCode, Station = r.Station }).Distinct(); count = 0; if (list != null && list.Count() >= 1) { count = list.Count(); if (page > -1) { list = list.OrderBy(a => a.CompanyId).ThenBy(a => a.CompanyEmployeeRelationId).Skip((page - 1) * rows).Take(rows); } } List <EmployeeGoonPaymentView> appList = list.ToList(); //List<EmployeeAddView> appList1 = uuuu.ToList(); //将险种进行查询补充,将页面需要展示人员的新增险种信息全部查询并放入系统内存中; var temp = (from b in empGoon // join f in appList1 on b.Id equals f.Id // join c in list on b.CompanyEmployeeRelationId equals c.CompanyEmployeeRelationId join d in ent.PoliceInsurance on b.EmployeeAdd.PoliceInsuranceId equals d.Id join e in ent.PoliceOperation on b.EmployeeAdd.PoliceOperationId equals e.Id select new { b.Id, b.EmployeeAdd.CompanyEmployeeRelationId, b.EmployeeAdd.InsuranceKindId, b.EmployeeAdd.PoliceInsuranceId, b.YearMonth, b.EmployeeAdd.InsuranceCode, b.EmployeeAdd.Wage, d.Name, b.State, b.StartTime, b.EndTime, PoliceOperationName = e.Name, b.InsuranceMonth, b.EmployeeAdd.IsIndependentAccount, b.CreateTime, PoliceAccountNaturename = b.EmployeeAdd.PoliceAccountNature.Name }).ToList(); int comMark = 1; int empMark = 2; appList = (from a in appList join bb1 in temp.Where(a => a.InsuranceKindId == 1) on a.CompanyEmployeeRelationId equals bb1.CompanyEmployeeRelationId into bt1 from b1 in bt1.DefaultIfEmpty() join bb2 in temp.Where(a => a.InsuranceKindId == 2) on a.CompanyEmployeeRelationId equals bb2.CompanyEmployeeRelationId into bt2 from b2 in bt2.DefaultIfEmpty() join bb3 in temp.Where(a => a.InsuranceKindId == 3) on a.CompanyEmployeeRelationId equals bb3.CompanyEmployeeRelationId into bt3 from b3 in bt3.DefaultIfEmpty() join bb4 in temp.Where(a => a.InsuranceKindId == 4) on a.CompanyEmployeeRelationId equals bb4.CompanyEmployeeRelationId into bt4 from b4 in bt4.DefaultIfEmpty() join bb5 in temp.Where(a => a.InsuranceKindId == 5) on a.CompanyEmployeeRelationId equals bb5.CompanyEmployeeRelationId into bt5 from b5 in bt5.DefaultIfEmpty() join bb6 in temp.Where(a => a.InsuranceKindId == 6) on a.CompanyEmployeeRelationId equals bb6.CompanyEmployeeRelationId into bt6 from b6 in bt6.DefaultIfEmpty() join bb7 in temp.Where(a => a.InsuranceKindId == 7) on a.CompanyEmployeeRelationId equals bb7.CompanyEmployeeRelationId into bt7 from b7 in bt7.DefaultIfEmpty() join bb8 in temp.Where(a => a.InsuranceKindId == 8) on a.CompanyEmployeeRelationId equals bb8.CompanyEmployeeRelationId into bt8 from b8 in bt8.DefaultIfEmpty() select new EmployeeGoonPaymentView() { CompanyEmployeeRelationId = a.CompanyEmployeeRelationId, CompanyCode = a.CompanyCode, CompanyName = a.CompanyName, Name = a.Name, CertificateNumber = a.CertificateNumber, City = a.City, PoliceAccountNatureName = a.PoliceAccountNatureName, Station = a.Station, AddIds = (b1 == null ? "" : b1.Id.ToString() + ",") + (b2 == null ? "" : b2.Id.ToString() + ",") + (b3 == null ? "" : b3.Id.ToString() + ",") + (b4 == null ? "" : b4.Id.ToString() + ",") + (b5 == null ? "" : b5.Id.ToString() + ",") + (b6 == null ? "" : b6.Id.ToString() + ",") + (b7 == null ? "" : b7.Id.ToString() + ",") + (b8 == null ? "" : b8.Id.ToString()), InsuranceCode_1 = b1 == null ? null : b1.InsuranceCode, CompanyNumber_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, comMark), CompanyPercent_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, comMark), EmployeeNumber_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, empMark), EmployeePercent_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, empMark), State_1 = b1 == null ? null : b1.State, PoliceInsuranceName_1 = b1 == null ? null : b1.Name, YearMonth_1 = b1 == null ? null : b1.YearMonth, StartTime_1 = b1 == null ? null : b1.StartTime, EndTime_1 = b1 == null ? null : b1.EndTime, Wage_1 = b1 == null ? null : b1.Wage, PoliceOperationName_1 = b1 == null ? null : b1.PoliceOperationName, InsuranceMonth_1 = b1 == null ? null : b1.InsuranceMonth, IsIndependentAccount_1 = b1 == null ? null : b1.IsIndependentAccount, CreateTime_1 = b1 == null ? null : b1.CreateTime, PoliceAccountNatureName_1 = b1 == null ? null : b1.PoliceAccountNaturename, InsuranceCode_2 = b2 == null ? null : b2.InsuranceCode, CompanyNumber_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, comMark), CompanyPercent_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, comMark), EmployeeNumber_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, empMark), EmployeePercent_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, empMark), State_2 = b2 == null ? null : b2.State, PoliceInsuranceName_2 = b2 == null ? null : b2.Name, YearMonth_2 = b2 == null ? null : b2.YearMonth, StartTime_2 = b2 == null ? null : b2.StartTime, EndTime_2 = b2 == null ? null : b2.EndTime, Wage_2 = b2 == null ? null : b2.Wage, PoliceOperationName_2 = b2 == null ? null : b2.PoliceOperationName, InsuranceMonth_2 = b2 == null ? null : b2.InsuranceMonth, IsIndependentAccount_2 = b2 == null ? null : b2.IsIndependentAccount, CreateTime_2 = b2 == null ? null : b2.CreateTime, PoliceAccountNatureName_2 = b2 == null ? null : b2.PoliceAccountNaturename, InsuranceCode_3 = b3 == null ? null : b3.InsuranceCode, CompanyNumber_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, comMark), CompanyPercent_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, comMark), EmployeeNumber_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, empMark), EmployeePercent_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, empMark), State_3 = b3 == null ? null : b3.State, PoliceInsuranceName_3 = b3 == null ? null : b3.Name, YearMonth_3 = b3 == null ? null : b3.YearMonth, StartTime_3 = b3 == null ? null : b3.StartTime, EndTime_3 = b3 == null ? null : b3.EndTime, Wage_3 = b3 == null ? null : b3.Wage, PoliceOperationName_3 = b3 == null ? null : b3.PoliceOperationName, InsuranceMonth_3 = b3 == null ? null : b3.InsuranceMonth, IsIndependentAccount_3 = b3 == null ? null : b3.IsIndependentAccount, CreateTime_3 = b3 == null ? null : b3.CreateTime, PoliceAccountNatureName_3 = b3 == null ? null : b3.PoliceAccountNaturename, InsuranceCode_4 = b4 == null ? null : b4.InsuranceCode, CompanyNumber_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, comMark), CompanyPercent_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, comMark), EmployeeNumber_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, empMark), EmployeePercent_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, empMark), State_4 = b4 == null ? null : b4.State, PoliceInsuranceName_4 = b4 == null ? null : b4.Name, YearMonth_4 = b4 == null ? null : b4.YearMonth, StartTime_4 = b4 == null ? null : b4.StartTime, EndTime_4 = b4 == null ? null : b4.EndTime, Wage_4 = b4 == null ? null : b4.Wage, PoliceOperationName_4 = b4 == null ? null : b4.PoliceOperationName, InsuranceMonth_4 = b4 == null ? null : b4.InsuranceMonth, IsIndependentAccount_4 = b4 == null ? null : b4.IsIndependentAccount, CreateTime_4 = b4 == null ? null : b4.CreateTime, PoliceAccountNatureName_4 = b4 == null ? null : b4.PoliceAccountNaturename, InsuranceCode_5 = b5 == null ? null : b5.InsuranceCode, CompanyNumber_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, comMark), CompanyPercent_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, comMark), EmployeeNumber_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, empMark), EmployeePercent_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, empMark), State_5 = b5 == null ? null : b5.State, PoliceInsuranceName_5 = b5 == null ? null : b5.Name, YearMonth_5 = b5 == null ? null : b5.YearMonth, StartTime_5 = b5 == null ? null : b5.StartTime, EndTime_5 = b5 == null ? null : b5.EndTime, Wage_5 = b5 == null ? null : b5.Wage, PoliceOperationName_5 = b5 == null ? null : b5.PoliceOperationName, InsuranceMonth_5 = b5 == null ? null : b5.InsuranceMonth, IsIndependentAccount_5 = b5 == null ? null : b5.IsIndependentAccount, CreateTime_5 = b5 == null ? null : b5.CreateTime, PoliceAccountNatureName_5 = b5 == null ? null : b5.PoliceAccountNaturename, InsuranceCode_6 = b6 == null ? null : b6.InsuranceCode, CompanyNumber_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, comMark), CompanyPercent_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, comMark), EmployeeNumber_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, empMark), EmployeePercent_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, empMark), State_6 = b6 == null ? null : b6.State, PoliceInsuranceName_6 = b6 == null ? null : b6.Name, YearMonth_6 = b6 == null ? null : b6.YearMonth, StartTime_6 = b6 == null ? null : b6.StartTime, EndTime_6 = b6 == null ? null : b6.EndTime, Wage_6 = b6 == null ? null : b6.Wage, PoliceOperationName_6 = b6 == null ? null : b6.PoliceOperationName, InsuranceMonth_6 = b6 == null ? null : b6.InsuranceMonth, IsIndependentAccount_6 = b6 == null ? null : b6.IsIndependentAccount, CreateTime_6 = b6 == null ? null : b6.CreateTime, PoliceAccountNatureName_6 = b6 == null ? null : b6.PoliceAccountNaturename, InsuranceCode_7 = b7 == null ? null : b7.InsuranceCode, CompanyNumber_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, comMark), CompanyPercent_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, comMark), EmployeeNumber_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, empMark), EmployeePercent_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, empMark), State_7 = b7 == null ? null : b7.State, PoliceInsuranceName_7 = b7 == null ? null : b7.Name, YearMonth_7 = b7 == null ? null : b7.YearMonth, StartTime_7 = b7 == null ? null : b7.StartTime, EndTime_7 = b7 == null ? null : b7.EndTime, Wage_7 = b7 == null ? null : b7.Wage, PoliceOperationName_7 = b7 == null ? null : b7.PoliceOperationName, InsuranceMonth_7 = b7 == null ? null : b7.InsuranceMonth, IsIndependentAccount_7 = b7 == null ? null : b7.IsIndependentAccount, CreateTime_7 = b7 == null ? null : b7.CreateTime, PoliceAccountNatureName_7 = b7 == null ? null : b7.PoliceAccountNaturename, InsuranceCode_8 = b8 == null ? null : b8.InsuranceCode, CompanyNumber_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, comMark), CompanyPercent_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, comMark), EmployeeNumber_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, empMark), EmployeePercent_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, empMark), State_8 = b8 == null ? null : b8.State, PoliceInsuranceName_8 = b8 == null ? null : b8.Name, YearMonth_8 = b8 == null ? null : b8.YearMonth, StartTime_8 = b8 == null ? null : b8.StartTime, EndTime_8 = b8 == null ? null : b8.EndTime, Wage_8 = b8 == null ? null : b8.Wage, PoliceOperationName_8 = b8 == null ? null : b8.PoliceOperationName, InsuranceMonth_8 = b8 == null ? null : b8.InsuranceMonth, IsIndependentAccount_8 = b8 == null ? null : b8.IsIndependentAccount, CreateTime_8 = b8 == null ? null : b8.CreateTime, PoliceAccountNatureName_8 = b8 == null ? null : b8.PoliceAccountNaturename, }).ToList(); #endregion return(appList); } catch (Exception e) { throw e; } } }
/// <summary> /// 获取费用中间表中数据 /// </summary> /// <param name="db"></param> /// <param name="yearMonth">时间</param> /// <param name="companyId">公司编号</param> /// <param name="insuranceId">种类</param> /// <param name="certificate">身份证号</param> /// <param name="name">姓名</param> /// <returns></returns> public IQueryable <EmployeeMiddleShow> GetData(SysEntities db, int yearMonth, List <int> companyId, List <string> cityId, int insuranceId, string certificate, string name) { var query = from em in db.EmployeeMiddle join cer in db.CompanyEmployeeRelation on em.CompanyEmployeeRelationId equals cer.Id join e in db.Employee on cer.EmployeeId equals e.Id join c in db.CRM_Company on cer.CompanyId equals c.ID select new EmployeeMiddleShow { EmployeeId = e.Id, EmployeeName = e.Name, CardId = e.CertificateNumber, CompanyId = c.ID, CompanyName = c.CompanyName, CompanyEmployeeRelationId = em.CompanyEmployeeRelationId, CityId = em.CityId, InsuranceKindId = em.InsuranceKindId, Id = em.Id, PaymentBetween = em.PaymentBetween, PaymentStyle = em.PaymentStyle, CompanyBasePayment = em.CompanyBasePayment, CompanyPayment = em.CompanyPayment, EmployeeBasePayment = em.EmployeeBasePayment, EmployeePayment = em.EmployeePayment, PaymentMonth = em.PaymentMonth, StartDate = em.StartDate, EndedDate = em.EndedDate, UseBetween = em.UseBetween, Remark = em.Remark, State = em.State, CreateTime = em.CreateTime, CreatePerson = em.CreatePerson, UpdateTime = em.UpdateTime, UpdatePerson = em.UpdatePerson }; if (yearMonth != 0) { query = query.Where(u => u.EndedDate >= yearMonth && u.StartDate <= yearMonth); } if (companyId != null) { query = query.Where(u => companyId.Contains(u.CompanyId)); } if (cityId != null) { query = query.Where(u => cityId.Contains(u.CityId)); } if (insuranceId != 0) { query = query.Where(u => u.InsuranceKindId == insuranceId); } if (!string.IsNullOrEmpty(certificate)) { // 身份证号验证 List <string> cardList = new List <string>(); string[] certificateList = certificate.Split(Convert.ToChar(10)); for (int i = 0; i < certificateList.Length; i++) { cardList.Add(certificateList[i]); cardList.Add(CardCommon.CardIDTo15(certificateList[i])); cardList.Add(CardCommon.CardIDTo18(certificateList[i])); } cardList = cardList.Distinct().ToList(); query = query.Where(u => cardList.Contains(u.CardId)); } if (!string.IsNullOrEmpty(name)) { // 名称验证 string[] nameList = name.Split(Convert.ToChar(10)); query = query.Where(u => nameList.Contains(u.EmployeeName)); } return(query.OrderBy(o => o.EmployeeId)); }
/// <summary> /// 查询待责任客服审核列表 /// </summary> /// <param name="SysEntities">数据访问的上下文</param> /// <param name="page">页码</param> /// <param name="rows">行数</param> /// <returns></returns> public List <EmployeeApprove> GetApproveList(SysEntities db, string CertificateNumber, int page, int rows, string search, out int count) { using (var ent = new SysEntities()) { try { var emp = ent.Employee.Where(a => true); var empGoon = ent.EmployeeGoonPayment.Where(a => true); var com = ent.CRM_Company.Where(a => true); var city = ent.City.Where(a => true); Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString()); if (queryDic != null && queryDic.Count > 0) { if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"])) { string str = queryDic["Name"]; emp = emp.Where(a => a.Name.Contains(str)); } if (!string.IsNullOrEmpty(CertificateNumber)) { string[] cardIdList = CertificateNumber.Split(Convert.ToChar(10)); List <string> cardList = new List <string>(); for (int i = 0; i < cardIdList.Length; i++) { cardList.Add(cardIdList[i]); cardList.Add(CardCommon.CardIDTo15(cardIdList[i])); cardList.Add(CardCommon.CardIDTo18(cardIdList[i])); } cardList = cardList.Distinct().ToList(); emp = emp.Where(o => cardList.Contains(o.CertificateNumber)); } if (queryDic.ContainsKey("State") && !string.IsNullOrWhiteSpace(queryDic["State"])) { string str = queryDic["State"]; string[] states = str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); empGoon = empGoon.Where(a => states.Contains(a.State)); } if (queryDic.ContainsKey("InsuranceKinds") && !string.IsNullOrWhiteSpace(queryDic["InsuranceKinds"])) { string str = queryDic["InsuranceKinds"]; int?[] Ids = Array.ConvertAll <string, int?>(str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), delegate(string s) { return(int.Parse(s)); }); empGoon = empGoon.Where(a => Ids.Contains(a.EmployeeAdd.InsuranceKindId)); } if (queryDic.ContainsKey("YearMonth") && !string.IsNullOrWhiteSpace(queryDic["YearMonth"])) { int str = Convert.ToInt32(queryDic["YearMonth"]); empGoon = empGoon.Where(a => a.YearMonth == str); } if (queryDic.ContainsKey("CompanyName") && !string.IsNullOrWhiteSpace(queryDic["CompanyName"])) { string str = queryDic["CompanyName"]; com = com.Where(a => a.CompanyName.Contains(str)); } if (queryDic.ContainsKey("CityId") && !string.IsNullOrWhiteSpace(queryDic["CityId"])) { string str = queryDic["CityId"]; city = city.Where(a => a.Id == str); } } var list = (from a in emp join r in ent.CompanyEmployeeRelation on a.Id equals r.EmployeeId join b in empGoon on r.Id equals b.EmployeeAdd.CompanyEmployeeRelationId join c in com on r.CompanyId equals c.ID //join d in ent.InsuranceKind on b.InsuranceKindId equals d.Id join e in city on r.CityId equals e.Id join f in ent.PoliceAccountNature on b.EmployeeAdd.PoliceAccountNatureId equals f.Id select new EmployeeApprove() { CompanyEmployeeRelationId = b.EmployeeAdd.CompanyEmployeeRelationId, CompanyId = r.CompanyId, CompanyName = c.CompanyName, Name = a.Name, CertificateNumber = a.CertificateNumber, City = e.Name, CityID = r.CityId, PoliceAccountNature = f.Name, YearMonth = b.YearMonth }).Distinct(); count = 0; if (list != null && list.Count() >= 1) { count = list.Count(); if (page > -1) { list = list.OrderBy(a => a.CompanyId).ThenBy(a => a.CompanyEmployeeRelationId).Skip((page - 1) * rows).Take(rows); } } List <EmployeeApprove> appList = list.ToList(); //将险种进行查询补充,将页面需要展示人员的补缴险种信息全部查询并放入系统内存中; var temp = (from a in empGoon join b in list on a.EmployeeAdd.CompanyEmployeeRelationId equals b.CompanyEmployeeRelationId //join d in ent.InsuranceKind on a.InsuranceKindId equals d.Id where a.YearMonth == b.YearMonth select new { a.EmployeeAdd.CompanyEmployeeRelationId, a.EmployeeAdd.InsuranceKindId, a.YearMonth, a.Id }).ToList(); for (int i = 0; i < appList.Count(); i++) { int?Id = appList[i].CompanyEmployeeRelationId; int?YearMonth = appList[i].YearMonth; if (Id != null) { var kinds = temp.Where(a => a.CompanyEmployeeRelationId == Id && a.YearMonth == YearMonth).ToList(); //var kinds = temp.Where(a => a.CompanyEmployeeRelationId == Id).ToList(); string kindsName = ""; string addIds = ""; foreach (var a in kinds) { kindsName += ((Common.EmployeeAdd_InsuranceKindId)a.InsuranceKindId + ","); addIds += (a.Id + ","); } kindsName = kindsName.Substring(0, kindsName.Length - 1); appList[i].InsuranceKinds = kindsName; appList[i].AddIds = addIds; } } return(appList); } catch (Exception e) { throw e; } } }
/// <summary> /// 查询员工列表 /// </summary> /// <param name="db"></param> /// <param name="page"></param> /// <param name="rows"></param> /// <param name="sort"></param> /// <param name="search"></param> /// <param name="count"></param> /// <returns></returns> public IQueryable <EmployeeInfo> GetEmployeeList(SysEntities db, string search) { var query = from employee in db.Employee join relation in db.CompanyEmployeeRelation on employee.Id equals relation.EmployeeId into lg1 from relation in lg1.DefaultIfEmpty() join branch in db.CRM_CompanyToBranch on relation.CompanyId equals branch.CRM_Company_ID into lg2 from branch in lg2.DefaultIfEmpty() select new EmployeeInfo() { empId = employee.Id, Empname = employee.Name, CertificateType = employee.CertificateType, CertificateNumber = employee.CertificateNumber, Sex = employee.Sex, AccountType = employee.AccountType, CreateTime = employee.CreateTime, zrkf = branch.UserID_ZR, ygkf = branch.UserID_YG }; Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString()); if (queryDic != null && queryDic.Count > 0) { if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"])) { string str = queryDic["Name"]; query = query.Where(a => a.Empname.Contains(str)); } if (queryDic.ContainsKey("CertificateNumber") && !string.IsNullOrWhiteSpace(queryDic["CertificateNumber"])) { string str = queryDic["CertificateNumber"]; string[] numList = str.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries); List <string> CARDLIST = new List <string>(); for (int i = 0; i < numList.Length; i++) { numList[i] = numList[i].TrimEnd(','); CARDLIST.Add(numList[i]); CARDLIST.Add(CardCommon.CardIDTo15(numList[i])); CARDLIST.Add(CardCommon.CardIDTo18(numList[i])); } CARDLIST = CARDLIST.Distinct().ToList(); query = query.Where(o => CARDLIST.Contains(o.CertificateNumber)); } } return(query.OrderByDescending(o => o.CreateTime)); //获取员工姓名查询员工信息 //IQueryable<Employee> employee = db.Employee.Where(o => true); //Dictionary<string, string> queryDic = ValueConvert.StringToDictionary(search.GetString()); //if (queryDic != null && queryDic.Count > 0) //{ // if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"])) // { // string str = queryDic["Name"]; // employee = employee.Where(a => a.Name.Contains(str)); // } // if (queryDic.ContainsKey("CertificateNumber") && !string.IsNullOrWhiteSpace(queryDic["CertificateNumber"])) // { // string str = queryDic["CertificateNumber"]; // string[] numList = str.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries); // List<string> CARDLIST = new List<string>(); // for (int i = 0; i < numList.Length; i++) // { // numList[i] = numList[i].TrimEnd(','); // CARDLIST.Add(numList[i]); // CARDLIST.Add(CardCommon.CardIDTo15(numList[i])); // CARDLIST.Add(CardCommon.CardIDTo18(numList[i])); // } // CARDLIST = CARDLIST.Distinct().ToList(); // employee = employee.Where(o => CARDLIST.Contains(o.CertificateNumber)); // } //} //return employee; }