/// <summary>
/// 分页
/// </summary>
/// <param name="entity"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <Entity.UserInfo> GetPage(Entity.UserInfo entity, int PageIndex, int PageSize, out int count)
{
string sqlwhere = "";
if (entity.DepartmentID != 0)
{
sqlwhere += " and UserInfo.DepartmentID=" + entity.DepartmentID;
}
if (entity.UserName != null && !entity.UserName.Equals(""))
{
sqlwhere += " and UserInfo.UserName like '%" + entity.UserName + "%'";
}
string sql = "select count(*) from UserInfo where 1=1 " + sqlwhere;
count = (int)db.ExecScalar();
sql = @"select * from (
select UserInfo.*,ROW_NUMBER() over(order by UserInfo.UserID) rowid,Department.DepartmentName
from UserInfo inner join Department on UserInfo.DepartmentID=Department.DepartmentID
where 1=1 " + sqlwhere + ") tamp where rowid between @PageIndex and @PageSize";
db.PrepareSql(sql);
db.SetParameter("PageIndex", (PageIndex - 1) * PageSize + 1);
db.SetParameter("PageSize", PageIndex * PageSize);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Entity.UserInfo> GetList = new List <UserInfo>();
foreach (DataRow item in dt.Rows)
{
entity.UserID = int.Parse(item["UserID"].ToString());
entity.DepartmentID = int.Parse(item["DepartmentID"].ToString());
entity.RoleID = int.Parse(item["RoleID"].ToString());
entity.UserNumber = item["UserNumber"].ToString();
entity.LoginName = item["LoginName"].ToString();
entity.LoginPwd = item["LoginPwd"].ToString();
entity.UserName = item["UserName"].ToString();
entity.UserAge = int.Parse(item["UserAge"].ToString());
entity.UserSex = int.Parse(item["UserSex"].ToString());
entity.UserTel = item["UserTel"].ToString();
entity.UserAddress = item["UserAddress"].ToString();
entity.UserIphone = item["UserIphone"].ToString();
entity.UserRemarks = item["UserRemarks"].ToString();
entity.UserStatr = int.Parse(item["UserStatr"].ToString());
entity.EntryTime = DateTime.Parse(item["EntryTime"].ToString());
entity.DimissionTime = DateTime.Parse(item["DimissionTime"].ToString());
entity.BasePay = decimal.Parse(item["BasePay"].ToString());
entity.department = new Department();
entity.department.DepartmentName = item["DepartmentName"].ToString();
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 获取管理员数量
/// </summary>
/// <returns></returns>
public int GetAdministrator()
{
return((int)DBHelper.ExecScalar("select count(*) from [Administrator]"));
}
//获取学员的数量
public int GetStudentDAL()
{
return((int)DBHelper.ExecScalar("select count(*) from [Student]"));
}
//获取宿舍的人数
public int GetDormitoryDAL()
{
return((int)DBHelper.ExecScalar("select count(*) from [Dormitory]"));
}
