/// <summary> /// 按部门获取部门用户,包括子部门的用户 /// </summary> /// <param name="departmentId">部门主键</param> /// <param name="searchValue"></param> /// <returns>数据表</returns> public DataTable SearchByDepartment(string departmentId, string searchValue) { var sqlQuery = " SELECT " + PiUserTable.TableName + ".* " + " FROM " + PiUserTable.TableName; sqlQuery += " WHERE (" + PiUserTable.TableName + "." + PiUserTable.FieldDeleteMark + " = 0 "; sqlQuery += " AND " + PiUserTable.TableName + "." + PiUserTable.FieldEnabled + " = 1 ) "; if (!String.IsNullOrEmpty(departmentId)) { /* * 用非递归调用的建议方法 * sqlQuery += " AND " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN ( SELECT " + PiOrganizeTable.FieldId + " FROM " + PiOrganizeTable.TableName + " WHERE " + PiOrganizeTable.FieldId + " = " + departmentId + " OR " + PiOrganizeTable.FieldParentId + " = " + departmentId + ")"; */ var organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo); var organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, departmentId, PiOrganizeTable.FieldParentId); if (organizeIds != null && organizeIds.Length > 0) { sqlQuery += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldCompanyId + " IN (" + BusinessLogic.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN (" + BusinessLogic.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldWorkgroupId + " IN (" + BusinessLogic.ArrayToList(organizeIds, "'") + "))"; } } var dbParameters = new List <IDbDataParameter>(); searchValue = searchValue.Trim(); if (!String.IsNullOrEmpty(searchValue)) { sqlQuery += " AND (" + PiUserTable.FieldUserName + " LIKE " + DBProvider.GetParameter(PiUserTable.FieldUserName); sqlQuery += " OR " + PiUserTable.FieldCode + " LIKE " + DBProvider.GetParameter(PiUserTable.FieldCode); sqlQuery += " OR " + PiUserTable.FieldRealName + " LIKE " + DBProvider.GetParameter(PiUserTable.FieldRealName); sqlQuery += " OR " + PiUserTable.FieldDepartmentName + " LIKE " + DBProvider.GetParameter(PiUserTable.FieldDepartmentName) + ")"; if (searchValue.IndexOf("%") < 0) { searchValue = "%" + searchValue + "%"; } dbParameters.Add(DBProvider.MakeParameter(PiUserTable.FieldUserName, searchValue)); dbParameters.Add(DBProvider.MakeParameter(PiUserTable.FieldCode, searchValue)); dbParameters.Add(DBProvider.MakeParameter(PiUserTable.FieldRealName, searchValue)); dbParameters.Add(DBProvider.MakeParameter(PiUserTable.FieldDepartmentName, searchValue)); } sqlQuery += " ORDER BY " + PiUserTable.TableName + "." + PiUserTable.FieldSortCode; return(DBProvider.Fill(sqlQuery, dbParameters.ToArray())); }
public DataTable GetChildrenUsers(string organizeId) { string[] organizeIds = null; var organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo); switch (DBProvider.CurrentDbType) { case CurrentDbType.Access: case CurrentDbType.SqlServer: var organizeCode = this.GetCodeById(organizeId); organizeIds = organizeManager.GetChildrensIdByCode(PiOrganizeTable.FieldCode, organizeCode); break; case CurrentDbType.Oracle: organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldParentId); break; } return(this.GetDTByOrganizes(organizeIds)); }
public List <PiUserEntity> GetChildrenUserList(string organizeId) { string[] organizeIds = null; var manager = new PiOrganizeManager(base.DBProvider, base.UserInfo); switch (base.DBProvider.CurrentDbType) { case CurrentDbType.Oracle: organizeIds = manager.GetChildrensId(PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldParentId); break; case CurrentDbType.SqlServer: case CurrentDbType.Access: { string codeById = manager.GetCodeById(organizeId); organizeIds = manager.GetChildrensIdByCode(PiOrganizeTable.FieldCode, codeById); break; } } return(this.GetListByOrganizes(organizeIds)); }
public DataTable GetChildrenStaffs(string organizeId) { PiOrganizeManager organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo); string[] organizeIds = null; switch (DBProvider.CurrentDbType) { case CurrentDbType.Access: case CurrentDbType.SqlServer: string organizeCode = DbCommonLibary.GetProperty(dbProvider, PiOrganizeTable.TableName, PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldCode); //this.GetCodeById(organizeId); organizeIds = organizeManager.GetChildrensIdByCode(PiOrganizeTable.FieldCode, organizeCode); break; case CurrentDbType.Oracle: organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldParentId); break; case CurrentDbType.MySql: organizeIds = organizeManager.GetMySqlChildrensId(PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldParentId); break; } return(this.GetDTByOrganizes(organizeIds)); }
/// <summary> /// 分页查询 /// </summary> /// <param name="searchValue">查询字段</param> /// <param name="departmentId">部门主键</param> /// <param name="roleId">角色主键</param> /// <param name="recordCount">记录数</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示</param> /// <param name="order">排序</param> /// <returns>数据表</returns> public DataTable GetDTByPage(string searchValue, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null) { string whereConditional = PiUserTable.TableName + "." + PiUserTable.FieldDeleteMark + " = 0 " + " AND " + PiUserTable.TableName + "." + PiUserTable.FieldEnabled + " = 1 " + " AND " + PiUserTable.TableName + "." + PiUserTable.FieldIsVisible + " = 1 "; if (!String.IsNullOrEmpty(departmentId)) { /* * 用非递归调用的建议方法 * sqlQuery += " AND " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN ( SELECT " + PiOrganizeTable.FieldId + " FROM " + BaseOrganizeEntity.TableName + " WHERE " + PiOrganizeTable.FieldId + " = " + departmentId + " OR " + PiOrganizeTable.FieldParentId + " = " + departmentId + ")"; */ PiOrganizeManager organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo); string[] organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, departmentId, PiOrganizeTable.FieldParentId); if (organizeIds != null && organizeIds.Length > 0) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldWorkgroupId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + "))"; } } if (!string.IsNullOrEmpty(roleId)) { string tableNameUserRole = PiUserRoleTable.TableName; whereConditional += " AND ( " + PiUserTable.TableName + "." + PiUserTable.FieldId + " IN " + " (SELECT + " + PiUserRoleTable.FieldUserId + " FROM " + tableNameUserRole + " WHERE " + PiUserRoleTable.FieldRoleId + " = '" + roleId + "'" + " AND " + PiUserRoleTable.FieldEnabled + " = 1" + " AND " + PiUserRoleTable.FieldDeleteMark + " = 0)) "; } if (!string.IsNullOrEmpty(searchValue)) { searchValue = "'" + StringHelper.GetSearchString(searchValue) + "'"; whereConditional += " AND (" + PiUserTable.FieldRealName + " LIKE " + searchValue; whereConditional += " OR " + PiUserTable.FieldUserName + " LIKE " + searchValue + ")"; //whereConditional += " AND (" + searchValue + ")"; ; } recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional); this.CurrentTableName = "PIUSER LEFT OUTER JOIN PIUSERLOGON ON PIUSER.ID = PIUSERLOGON.ID "; switch (DBProvider.CurrentDbType) { case CurrentDbType.SqlServer: case CurrentDbType.Access: case CurrentDbType.Oracle: case CurrentDbType.MySql: this.SelectField = PiUserTable.TableName + ".* " + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldFirstVisit + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldPreviousVisit + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLastVisit + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldIPAddress + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldMACAddress + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLogOnCount + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldUserOnLine; break; case CurrentDbType.DB2: break; } return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order)); }
private string GetSearchConditional(string permissionScopeCode, string search, string[] roleIds, bool?enabled, string auditStates, string departmentId) { search = StringHelper.GetSearchString(search); string whereConditional = PiUserTable.TableName + "." + PiUserTable.FieldDeleteMark + " = 0 " + " AND " + PiUserTable.TableName + "." + PiUserTable.FieldIsVisible + " = 1 "; if (enabled != null) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldEnabled + " = " + ((bool)enabled ? 1 : 0) + ")"; } if (!String.IsNullOrEmpty(search)) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldUserName + " LIKE '" + search + "'" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldCode + " LIKE '" + search + "'" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldRealName + " LIKE '" + search + "'" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldQuickQuery + " LIKE '" + search + "'" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentName + " LIKE '" + search + "'" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDescription + " LIKE '" + search + "')"; } if (!string.IsNullOrEmpty(departmentId)) { var organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo); string[] organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, departmentId, PiOrganizeTable.FieldParentId); if (organizeIds != null && organizeIds.Length > 0) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldWorkgroupId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + "))"; // 从兼职表读取用户 whereConditional += " OR " + PiUserTable.TableName + "." + PiUserTable.FieldId + " IN (" + " SELECT " + PiUserOrganizeTable.FieldUserId + " FROM " + PiUserOrganizeTable.TableName + " WHERE (" + PiUserOrganizeTable.TableName + "." + PiUserOrganizeTable.FieldDeleteMark + " = 0 ) " + " AND (" + PiUserOrganizeTable.TableName + "." + PiUserOrganizeTable.FieldCompanyId + " = '" + departmentId + "' OR " + PiUserOrganizeTable.TableName + "." + PiUserOrganizeTable.FieldSubCompanyId + " = '" + departmentId + "' OR " + PiUserOrganizeTable.TableName + "." + PiUserOrganizeTable.FieldDepartmentId + " = '" + departmentId + "' OR " + PiUserOrganizeTable.TableName + "." + PiUserOrganizeTable.FieldSubDepartmentId + " = '" + departmentId + "' OR " + PiUserOrganizeTable.TableName + "." + PiUserOrganizeTable.FieldWorkgroupId + " = '" + departmentId + "')) "; } } if (!String.IsNullOrEmpty(auditStates)) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldAuditStatus + " = '" + auditStates + "')"; } if ((roleIds != null) && (roleIds.Length > 0)) { string roles = StringHelper.ArrayToList(roleIds, "'"); whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldId + " IN (" + "SELECT " + PiUserRoleTable.FieldUserId + " FROM " + PiUserRoleTable.TableName + " WHERE " + PiUserRoleTable.FieldRoleId + " IN (" + roles + ")" + "))"; } // 是否过滤用户, 获得组织机构列表 if ((!UserInfo.IsAdministrator) && (SystemInfo.EnableUserAuthorizationScope)) { // string permissionScopeCode = "Resource.ManagePermission"; var permissionItemManager = new PiPermissionItemManager(this.DBProvider, this.UserInfo); string permissionScopeItemId = permissionItemManager.GetId(new KeyValuePair <string, object>(PiPermissionItemTable.FieldCode, permissionScopeCode)); if (!string.IsNullOrEmpty(permissionScopeItemId)) { // 从小到大的顺序进行显示,防止错误发生 var userPermissionScopeManager = new UserScopeManager(this.DBProvider, this.UserInfo); string[] organizeIds = userPermissionScopeManager.GetOrganizeIds(this.UserInfo.Id, permissionScopeCode); // 没有任何数据权限 if (StringHelper.Exists(organizeIds, ((int)PermissionScope.None).ToString())) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldId + " = NULL ) "; } // 按详细设定的数据 if (StringHelper.Exists(organizeIds, ((int)PermissionScope.Detail).ToString())) { var permissionScopeManager = new PiPermissionScopeManager(DBProvider, UserInfo); string[] userIds = permissionScopeManager.GetUserIds(UserInfo.Id, permissionScopeCode); whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldId + " IN (" + BusinessLogic.ObjectsToList(userIds) + ")) "; } // 自己的数据,仅本人 if (StringHelper.Exists(organizeIds, ((int)PermissionScope.User).ToString())) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldId + " = '" + this.UserInfo.Id + "') "; } // 用户所在工作组数据 if (StringHelper.Exists(organizeIds, ((int)PermissionScope.UserWorkgroup).ToString())) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldWorkgroupId + " = '" + this.UserInfo.WorkgroupId + "') "; } // 用户所在部门数据 if (StringHelper.Exists(organizeIds, ((int)PermissionScope.UserDepartment).ToString())) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " = '" + this.UserInfo.DepartmentId + "') "; } // 用户所在公司数据 if (StringHelper.Exists(organizeIds, ((int)PermissionScope.UserCompany).ToString())) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldCompanyId + " = '" + this.UserInfo.CompanyId + "') "; } // 全部数据,这里就不用设置过滤条件了 if (StringHelper.Exists(organizeIds, ((int)PermissionScope.All).ToString())) { } } } return(whereConditional); }