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