/// <summary>
/// 分页查询
/// </summary>
/// <param name="userInfo"></param>
/// <param name="departmentId">部门编号</param>
/// <param name="searchValue">查询字段</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="sortExpression">排序字段</param>
/// <param name="sortDire">排序方向</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(BaseUserInfo userInfo, string departmentId, string searchValue, out int recordCount, int pageIndex = 1, int pageSize = 20, string sortExpression = null, string sortDire = null)
{
string whereConditional = BaseRoleEntity.FieldDeletionStateCode + " = 0 ";
if (!String.IsNullOrEmpty(departmentId))
{
/*
* 用非递归调用的建议方法
* sqlQuery += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId
+ " IN ( SELECT " + BaseOrganizeEntity.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + BaseOrganizeEntity.FieldId + " = " + departmentId + " OR " + BaseOrganizeEntity.FieldParentId + " = " + departmentId + ")";
*/
BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.DbHelper, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
whereConditional += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
}
if (!string.IsNullOrEmpty(searchValue))
{
searchValue = "'" + StringUtil.GetSearchString(searchValue) + "'";
whereConditional += " AND (" + BaseUserEntity.FieldRealName + " LIKE " + searchValue;
whereConditional += " OR " + BaseUserEntity.FieldUserName + " LIKE " + searchValue + ")";
}
return(GetDataTableByPage(out recordCount, pageIndex, pageSize, sortExpression, sortDire, this.CurrentTableName, whereConditional, "*"));
}
/// <summary>
/// 按部门获取部门用户,包括子部门的用户
/// </summary>
/// <param name="departmentId">部门主键</param>
/// <returns>数据表</returns>
public DataTable SearchByDepartment(string departmentId, string searchValue)
{
string sqlQuery = " SELECT " + BaseUserEntity.TableName + ".* "
+ " FROM " + BaseUserEntity.TableName;
sqlQuery += " WHERE (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDeletionStateCode + " = 0 ";
sqlQuery += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = 1 ) ";
if (!String.IsNullOrEmpty(departmentId))
{
/*
* 用非递归调用的建议方法
* sqlQuery += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId
+ " IN ( SELECT " + BaseOrganizeEntity.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + BaseOrganizeEntity.FieldId + " = " + departmentId + " OR " + BaseOrganizeEntity.FieldParentId + " = " + departmentId + ")";
*/
BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.DbHelper, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
}
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
searchValue = searchValue.Trim();
if (!String.IsNullOrEmpty(searchValue))
{
sqlQuery += " AND (" + BaseUserEntity.FieldUserName + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldUserName);
sqlQuery += " OR " + BaseUserEntity.FieldCode + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldCode);
sqlQuery += " OR " + BaseUserEntity.FieldRealName + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldRealName);
sqlQuery += " OR " + BaseUserEntity.FieldDepartmentName + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldDepartmentName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldUserName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldCode, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldDepartmentName, searchValue));
}
sqlQuery += " ORDER BY " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSortCode;
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
public DataTable GetChildrenUsers(string organizeId)
{
string[] organizeIds = null;
BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.DbHelper, this.UserInfo);
switch (DbHelper.CurrentDbType)
{
case CurrentDbType.Access:
case CurrentDbType.SqlServer:
string organizeCode = organizeManager.GetCodeById(organizeId);
organizeIds = organizeManager.GetChildrensIdByCode(BaseOrganizeEntity.FieldCode, organizeCode);
break;
case CurrentDbType.Oracle:
organizeIds = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, organizeId, BaseOrganizeEntity.FieldParentId);
break;
}
return(this.GetDataTableByOrganizes(organizeIds));
}
public DataTable Search(string permissionScopeItemCode, string search, string[] roleIds, bool?enabled, string auditStates, string departmentId)
{
search = StringUtil.GetSearchString(search);
string sqlQuery = " SELECT " + BaseUserEntity.TableName + ".* "
+ "," + BaseRoleEntity.TableName + "." + BaseRoleEntity.FieldRealName + " AS RoleName "
+ " FROM " + BaseUserEntity.TableName
+ " LEFT OUTER JOIN " + BaseRoleEntity.TableName
+ " ON " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldRoleId + " = " + BaseRoleEntity.TableName + "." + BaseRoleEntity.FieldId
// 被删除的排出在外比较好一些
+ " WHERE " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldIsVisible + " = 1 ";
if (!String.IsNullOrEmpty(search))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldUserName + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCode + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldRealName + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldQuickQuery + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentName + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDescription + " LIKE '" + search + "')";
}
if (!string.IsNullOrEmpty(departmentId))
{
BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.DbHelper, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
}
if (!String.IsNullOrEmpty(auditStates))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldAuditStatus + " = '" + auditStates + "')";
}
if (enabled != null)
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = " + ((bool)enabled ? 1:0) + ")";
}
if ((roleIds != null) && (roleIds.Length > 0))
{
string roles = StringUtil.ArrayToList(roleIds, "'");
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldRoleId + " IN (" + roles + ") ";
sqlQuery += " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN (" + "SELECT " + BaseUserRoleEntity.FieldUserId + " FROM " + BaseUserRoleEntity.TableName + " WHERE " + BaseUserRoleEntity.FieldRoleId + " IN (" + roles + ")" + "))";
}
// 是否过滤用户， 获得组织机构列表， 这里需要一个按用户过滤得功能
if ((!UserInfo.IsAdministrator) && (BaseSystemInfo.UsePermissionScope))
{
// string permissionScopeItemCode = "Resource.ManagePermission";
BasePermissionItemManager permissionItemManager = new BasePermissionItemManager(this.DbHelper, this.UserInfo);
string permissionScopeItemId = permissionItemManager.GetId(new KeyValuePair <string, object>(BasePermissionItemEntity.FieldCode, permissionScopeItemCode));
if (!string.IsNullOrEmpty(permissionScopeItemId))
{
// 从小到大的顺序进行显示，防止错误发生
BaseUserScopeManager userPermissionScopeManager = new BaseUserScopeManager(this.DbHelper, this.UserInfo);
string[] organizeIds = userPermissionScopeManager.GetOrganizeIds(this.UserInfo.Id, permissionScopeItemId);
// 没有任何数据权限
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.None).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " = NULL ) ";
}
// 按详细设定的数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.Detail).ToString()))
{
BasePermissionScopeManager permissionScopeManager = new BasePermissionScopeManager(DbHelper, UserInfo);
string[] userIds = permissionScopeManager.GetUserIds(UserInfo.Id, permissionScopeItemCode);
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN (" + BaseBusinessLogic.ObjectsToList(userIds) + ")) ";
}
// 自己的数据，仅本人
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.User).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " = " + this.UserInfo.Id + ") ";
}
// 用户所在工作组数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserWorkgroup).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " = " + this.UserInfo.WorkgroupId + ") ";
}
// 用户所在部门数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserDepartment).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " = " + this.UserInfo.DepartmentId + ") ";
}
// 用户所在分支机构数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserSubCompany).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSubCompanyId + " = " + this.UserInfo.SubCompanyId + ") ";
}
// 用户所在公司数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserCompany).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " = " + this.UserInfo.CompanyId + ") ";
}
// 全部数据，这里就不用设置过滤条件了
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.All).ToString()))
{
}
}
}
sqlQuery += " ORDER BY " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSortCode;
return(DbHelper.Fill(sqlQuery));
}