public Hashtable SearchEmployeesByMultiSort(string key, int index, int size, ArrayList sortFields)
{
//System.Threading.Thread.Sleep(300);
string sql = @"
select a.*, b.name dept_name, c.name position_name, d.name educational_name
from t_employee a
left join t_department b
on a.dept_id = b.id
left join t_position c
on a.position = c.id
left join t_educational d
on a.educational = d.id";
if (sortFields.Count > 0)
{
for (int i = 0, l = sortFields.Count; i < l; i++)
{
Hashtable record = (Hashtable)sortFields[i];
String sortField = (String)record["field"];
String sortOrder = (String)record["dir"];
if (i == 0)
{
sql += " order by " + sortField + " " + sortOrder;
}
else
{
sql += "," + sortField + " " + sortOrder;
}
}
}
else
{
sql += " order by createtime desc";
}
ArrayList dataAll = new DBUtil().Select(sql);
//System.Threading.Thread.Sleep(10000); //延时10秒，模拟读取数据耗费时间
//实现一个内存分页(实际应该使用SQL分页)
ArrayList data = new ArrayList();
int start = index * size, end = start + size;
for (int i = 0, l = dataAll.Count; i < l; i++)
{
Hashtable record = (Hashtable)dataAll[i];
if (record == null)
{
continue;
}
if (start <= i && i < end)
{
data.Add(record);
}
}
Hashtable result = new Hashtable();
result["data"] = data;
result["total"] = dataAll.Count;
//2)总员工数 total
return(result);
}
public Hashtable SearchEmployees2(string key, int index, int size, string sortField, string sortOrder)
{
//System.Threading.Thread.Sleep(300);
string sql = @"
select a.*, b.name dept_name, c.name position_name, d.name educational_name
from t_employee a
left join t_department b
on a.dept_id = b.id
left join t_position c
on a.position = c.id
left join t_educational d
on a.educational = d.id
where a.name like '%" + key + "%'";
if (String.IsNullOrEmpty(sortField) == false)
{
if (sortOrder != "desc")
{
sortOrder = "asc";
}
sql += " order by " + sortField + " " + sortOrder;
}
else
{
sql += " order by createtime desc";
}
ArrayList dataAll = new DBUtil().Select(sql);
//System.Threading.Thread.Sleep(1000); //延时1秒，模拟读取数据耗费时间
//实现一个内存分页(实际应该使用SQL分页)
ArrayList data = new ArrayList();
int start = index * size, end = start + size;
for (int i = 0, l = dataAll.Count; i < l; i++)
{
Hashtable record = (Hashtable)dataAll[i];
if (record == null)
{
continue;
}
if (start <= i && i < end)
{
data.Add(record);
}
}
Hashtable result = new Hashtable();
result["data"] = data;
result["total"] = dataAll.Count;
//生成一些汇总信息
//1)年龄：minAge, maxAge, avgAge
ArrayList ages = new DBUtil().Select("select min(age) as minAge, max(age) as maxAge, avg(age) as avgAge from t_employee");
Hashtable ageInfo = ages[0] as Hashtable;
result["minAge"] = ageInfo["minAge"];
result["maxAge"] = ageInfo["maxAge"];
result["avgAge"] = ageInfo["avgAge"];
//2)总员工数 total
return(result);
}