public PagedList <EmployeeDTO> Query(QueryCondition <EmployeeQuery> query)
{
lock (m_sync_Object)
{
PagedList <EmployeeDTO> pagedList = new PagedList <EmployeeDTO>();
#region sql
var sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS * from employees ");
#endregion
sql.AppendLine(" Where 1=1");
if (!string.IsNullOrEmpty(query.Param.Name))
{
if (query.Param.Name.IndexOf(" ") > 0)
{
var names = query.Param.Name.Split(" ");
sql.AppendLine(string.Format(" and (First_Name like '{0}' or last_Name like '{1}')", query.GetLikeValue(names[0]), query.GetLikeValue(names[1])));
}
else
{
sql.AppendLine(string.Format(" and (First_Name like '{0}' or last_Name like '{0}')", query.GetLikeValue(query.Param.Name)));
}
}
if (!string.IsNullOrEmpty(query.Param.Emp_No))
{
sql.AppendLine(string.Format(" and emp_no = @Emp_No"));
}
if (!string.IsNullOrEmpty(query.Param.Gender))
{
sql.AppendLine(string.Format(" and gender = @Gender"));
}
DateTime?hire_date_start = null;
DateTime?hire_date_end = null;
if (query.Param.Hire_Date_Range != null)
{
if (query.Param.Hire_Date_Range[0].HasValue)
{
hire_date_start = query.Param.Hire_Date_Range[0];
sql.AppendLine(string.Format(" and hire_date >= @Hire_Date_Range_Start"));
}
if (query.Param.Hire_Date_Range[1].HasValue)
{
hire_date_end = query.Param.Hire_Date_Range[1];
sql.AppendLine(string.Format(" and hire_date <= @Hire_Date_Range_End"));
}
}
DateTime?birth_date_start = null;
DateTime?birth_date_end = null;
if (query.Param.Birth_Date_Range != null)
{
if (query.Param.Birth_Date_Range[0].HasValue)
{
birth_date_start = query.Param.Birth_Date_Range[0];
sql.AppendLine(string.Format(" and birth_date >= @Birth_Date_Range_Start"));
}
if (query.Param.Birth_Date_Range[1].HasValue)
{
birth_date_end = query.Param.Birth_Date_Range[1];
sql.AppendLine(string.Format(" and birth_date <= @Birth_Date_Range_End"));
}
}
sql.AppendLine(" order by emp_no desc");
sql.AppendLine($" LIMIT {(query.pageInfo.PageIndex-1) * query.pageInfo.PageSize},{query.pageInfo.PageSize}");
sql.Append(";");
var connection = this.GetReadingConnection();
var result = connection.Query <EmployeeDTO>(sql.ToString(),
new
{
Emp_No = query.Param.Emp_No,
Hire_Date_Range_Start = hire_date_start,
Hire_Date_Range_End = hire_date_end,
Birth_Date_Range_Start = birth_date_start,
Birth_Date_Range_End = birth_date_end,
Gender = query.Param.Gender
}).AsList();
pagedList.Items = result;
pagedList.TotalCount = connection.ExecuteScalar <int>("SELECT FOUND_ROWS();");
return(pagedList);
}
}
