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