public static List <BaseEmployee> QueryEmployeePage(EmployeeCondition condition, int pagesize, int pageindex, out int total)
{
if (condition == null)
{
throw new ArgumentNullException("condition");
}
if (string.IsNullOrWhiteSpace(condition.VillageId))
{
throw new ArgumentNullException("VillageId");
}
IBaseEmployee factory = BaseEmployeeFactory.GetFactory();
return(factory.QueryEmployeePage(condition, pagesize, pageindex, out total));
}
public List <BaseEmployee> QueryEmployeePage(EmployeeCondition condition, int pagesize, int pageindex, out int total)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * FROM BaseEmployee WHERE VID=@VID");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.AddParameter("VID", condition.VillageId);
if (!string.IsNullOrEmpty(condition.DeptId))
{
sql.Append(" AND DeptID=@DeptID");
dbOperator.AddParameter("DeptID", condition.DeptId);
}
if (!string.IsNullOrEmpty(condition.Name))
{
sql.Append(" AND EmployeeName like @EmployeeName");
dbOperator.AddParameter("UserAccount", "%" + condition.Name + "%");
}
if (!string.IsNullOrEmpty(condition.Phone))
{
sql.Append(" AND (MobilePhone=@MobilePhone or HomePhone=@MobilePhone)");
dbOperator.AddParameter("MobilePhone", condition.Phone);
}
if (condition.EmployeeType.HasValue)
{
sql.Append(" AND (EmployeeType=@EmployeeType or EmployeeType=@EmployeeType1)");
dbOperator.AddParameter("EmployeeType", (int)condition.EmployeeType);
dbOperator.AddParameter("EmployeeType1", (int)EmployeeType.OwnerAndStaff);
}
using (DbDataReader reader = dbOperator.Paging(sql.ToString(), "LastUpdateTime DESC", pageindex, pagesize, out total))
{
List <BaseEmployee> models = new List <BaseEmployee>();
while (reader.Read())
{
models.Add(DataReaderToModel <BaseEmployee> .ToModel(reader));
}
return(models);
}
}
}
public JsonResult Get(int draw, int start)
{
var employeeNo = "";
var countEmpNo = "";
int count = 0;
int index = 0;
int empCount = 0;
string empNo = "";
List <Employee> rows = new List <Employee>();
List <string> rowsCount = new List <string>();
List <Employee> tbrows = new List <Employee>();
string parameter = Request.Query["search[value]"].FirstOrDefault();
EmployeeCondition condition = null;
var counttmp = _db.tbEmployee.Select(m => m.EmployeeNo).Distinct().AsQueryable();
var tmp = (from a in _db.tbEmployee
from b in _db.tbEmpGroup
.Where(f => f.EmployeeNo == a.EmployeeNo)
select new
{
a.EmployeeNo,
a.EmployeeName,
b.GroupID
} into x
join c in _db.tbGroup on x.GroupID equals c.GroupID into gj
from y in gj.DefaultIfEmpty()
select new Employee
{
EmployeeNo = x.EmployeeNo,
EmployeeName = x.EmployeeName,
GroupName = (y == null ? String.Empty : y.GroupName)
}).AsQueryable();
if (parameter != "")
{
condition = (EmployeeCondition)JsonConvert.DeserializeObject(parameter, typeof(EmployeeCondition));
}
if (condition != null)
{
if (condition.EmployeeNo != "")
{
tmp = tmp.Where(e => e.EmployeeNo.Contains(condition.EmployeeNo));
}
if (condition.EmployeeName != "")
{
tmp = tmp.Where(e => e.EmployeeName.Contains(condition.EmployeeName));
}
}
rows = tmp.OrderBy(e => e.EmployeeNo).ToList();
//計算搜尋員工總數
foreach (Employee emp in rows)
{
if (!empNo.Equals(emp.EmployeeNo))
{
empNo = emp.EmployeeNo;
empCount++;
}
}
//for (int i = rows.Count - 1; i >= 0; i--)
//{
// if (!rows[i].EmployeeNo.Equals(employeeNo))
// {
// index = i;
// employeeNo = rows[i].EmployeeNo;
// }
// else
// {
// rows[index].GroupName = rows[index].GroupName + "," + rows[i].GroupName;
// rows.RemoveAt(i);
// }
//}
Employee Employee = new Employee();
for (int i = 0; i < rows.Count; i++)
{
if (!rows[i].EmployeeNo.Equals(countEmpNo))
{
countEmpNo = rows[i].EmployeeNo;
count++;
}
if (count > (start + 10))
{
break;
}
if (count > start)
{
if (!rows[i].EmployeeNo.Equals(employeeNo))
{
if (!employeeNo.Equals(""))
{
tbrows.Add(Employee);
}
Employee = new Employee();
employeeNo = rows[i].EmployeeNo;
Employee.EmployeeNo = employeeNo;
Employee.EmployeeName = rows[i].EmployeeName;
Employee.GroupName = rows[i].GroupName;
}
else
{
Employee.GroupName = Employee.GroupName + "," + rows[i].GroupName;
}
}
}
if (!countEmpNo.Equals(""))
{
tbrows.Add(Employee);
}
int recordsTotal = counttmp.Count();
//rows = filteredData.OrderBy(e => e.GroupID).Skip(start).Take(10).ToList();
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = tbrows,
length = tbrows.Count(),
recordsFiltered = empCount,
recordsTotal = empCount
};
return(Json(renderModel));
}
