public IQueryable <Chronic_disease_Comm_Testing_Faeces> LoadSearchEntities(Model.SearchParam.CommonParam parms) { var temp = CurrentDal.LoadEntityAsNoTracking(t => true); if (!string.IsNullOrEmpty(parms.idCard)) { //身份证号不为空的情况下:只要输入身份证号,其他条件一律过滤掉,只以身份证号为准 temp = temp.Where(t => t.id_card_number == parms.idCard).OrderBy(t => t.create_time); } else { temp = temp.Where(t => t.community_code.StartsWith(parms.region_code) && t.type.Contains("Therioma")); if (!string.IsNullOrEmpty(parms.name)) { temp = temp.Where(t => t.names.Contains(parms.name)); } if (!string.IsNullOrEmpty(parms.sex)) { temp = temp.Where(t => t.sex == parms.sex); } if (!string.IsNullOrEmpty(parms.txtBirthDateBegin) && !string.IsNullOrEmpty(parms.txtBirthDateEnd)) { DateTime birthDateBegin = Convert.ToDateTime(parms.txtBirthDateBegin); DateTime birthDateEnd = Convert.ToDateTime(parms.txtBirthDateEnd); temp = temp.Where(t => (birthDateBegin <= t.birth_date) && (birthDateEnd >= t.birth_date)); } if (!string.IsNullOrEmpty(parms.address)) { temp = temp.Where(t => t.address.Contains(parms.address)); } temp = temp.OrderByDescending(t => t.create_time); } parms.TotalCount = temp.Count(); return(temp.Skip((parms.PageIndex - 1) * parms.PageSize).Take(parms.PageSize)); }
//分页 多条件搜索 public IQueryable <Chronic_disease_SmokeAndDrink> LoadSearchEntities(Model.SearchParam.CommonParam commonParam) { var temp = CurrentDal.LoadEntityAsNoTracking(t => true); if (!string.IsNullOrEmpty(commonParam.idCard)) { //身份证号不为空的情况下:只要输入身份证号,其他条件一律过滤掉,只以身份证号为准 temp = temp.Where(t => t.id_card_number == commonParam.idCard).OrderByDescending(t => t.create_time); } else { temp = temp.Where(t => t.community_code.StartsWith(commonParam.region_code)); if (!string.IsNullOrEmpty(commonParam.name)) { temp = temp.Where(t => t.name.Contains(commonParam.name)); } if (!string.IsNullOrEmpty(commonParam.sex)) { temp = temp.Where(t => t.sex == commonParam.sex); } if (!string.IsNullOrEmpty(commonParam.txtBirthDateBegin) && !string.IsNullOrEmpty(commonParam.txtBirthDateEnd)) { DateTime birthDateBegin = Convert.ToDateTime(commonParam.txtBirthDateBegin); DateTime birthDateEnd = Convert.ToDateTime(commonParam.txtBirthDateEnd); temp = temp.Where(t => birthDateBegin <= t.birth_date && birthDateEnd >= t.birth_date); } if (!string.IsNullOrEmpty(commonParam.address)) { temp = temp.Where(t => t.permanent_address.Contains(commonParam.address)); } temp = temp.OrderByDescending(t => t.create_time); } commonParam.TotalCount = temp.Count(); return(temp.Skip((commonParam.PageIndex - 1) * commonParam.PageSize).Take(commonParam.PageSize)); }
public IQueryable <Model.ViewModel.BasicAndConstitutionViewModel> LoadSearchEntities(Model.SearchParam.CommonParam pam) { int start = (pam.PageIndex - 1) * pam.PageSize + 1; int end = pam.PageSize * pam.PageIndex; string sql = "select * from (select a.id aid, b.id bid,a.id_card_number,a.community_code,a.resident_name,a.sex,a.birth_date,a.permanent_home_address,b.resident_id,b.result,b.create_time,ROW_NUMBER() over (order by b.create_time) as num from Comm_ResidentFile a left join Chronic_disease_Constitution b on a.resident_id=b.resident_id where b.create_time in (select MAX(create_time) from Chronic_disease_Constitution group by resident_id)"; if (!string.IsNullOrEmpty(pam.idCard)) { //身份证号不为空的情况下:只要输入身份证号,其他条件一律过滤掉,只以身份证号为准 sql += "and a.id_card_number like '%" + pam.idCard + "%'"; sql += ")as t where t.num>=" + start + " and t.num<=" + end + ""; } else { sql += "and a.community_code like '%" + pam.region_code + "%'"; if (!string.IsNullOrEmpty(pam.name)) { sql += "and a.resident_name like '%" + pam.name + "%'"; } if (!string.IsNullOrEmpty(pam.sex)) { sql += "and a.sex like '%" + pam.sex + "%'"; } if (!string.IsNullOrEmpty(pam.txtBirthDateBegin) && !string.IsNullOrEmpty(pam.txtBirthDateEnd)) { DateTime birthDateBegin = Convert.ToDateTime(pam.txtBirthDateBegin); DateTime birthDateEnd = Convert.ToDateTime(pam.txtBirthDateEnd); sql += "and a.birth_date between '" + birthDateBegin + "' and '" + birthDateEnd + "'"; } if (!string.IsNullOrEmpty(pam.address)) { sql += "and a.permanent_home_address like '%" + pam.address + "%'"; } sql += ")as t where t.num>=" + start + " and t.num<=" + end + ""; } string sql2 = "select count(*) from Chronic_disease_Constitution where create_time in (select MAX(create_time) from Chronic_disease_Constitution group by resident_id)"; var totalCount = Db.Database.SqlQuery <int>(sql2).FirstOrDefault(); pam.TotalCount = totalCount; var temp = Db.Database.SqlQuery <Model.ViewModel.BasicAndConstitutionViewModel>(sql).AsQueryable(); return(temp); }