/// <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));
        }
Exemple #5
0
        /// <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));
        }
Exemple #6
0
        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);
        }