def GetSearchConditional(userInfo, permissionScopeCode, search, roleIds, enabled, auditStates, departmentId): """ 获取SQL查询串 Args: permissionScopeCode (string): 权限码 search (string): 查询字段 roleIds (string[]): 用户角色ID字典 enabled (string): 启用标志 auditStates (string): 审核状态 departmentId (string): 组织机构ID Returns: returnValue (int): SQL组合查询串 """ LogService.WriteLog(userInfo, __class__.__name__, FrameworkMessage.UserService, sys._getframe().f_code.co_name, FrameworkMessage.UserService_UpdateUser, '') #easyui search whereConditional = 'piuser.DELETEMARK = 0 AND piuser.ISVISIBLE = 1 ' if enabled: whereConditional = whereConditional + ' AND ( piuser.ENABLED = 1 ) ' if search: whereConditional = whereConditional + ' AND ( piuser.USERNAME LIKE \'' + search + '\'' \ + ' OR piuser.CODE LIKE \'' + search + '\'' \ + ' OR piuser.REALNAME LIKE \'' + search + '\'' \ + ' OR piuser.QUICKQUERY LIKE \'' + search + '\'' \ + ' OR piuser.DEPARTMENTNAME LIKE \'' + search + '\'' \ + ' OR piuser.DESCRIPTION LIKE \'' + search + '\')' if departmentId: organizeIds = OrganizeService.GetChildrensById(None, departmentId) if len(organizeIds) > 0: whereConditional = whereConditional + ' AND (piuser.COMPANYID IN (' + StringHelper.ArrayToList(None, organizeIds, '\'') + ')' \ + ' OR piuser.COMPANYID IN (' + StringHelper.ArrayToList(None, organizeIds, '\'') + ')' \ + ' OR piuser.DEPARTMENTID IN (' + StringHelper.ArrayToList(None, organizeIds, '\'') + ')' \ + ' OR piuser.SUBDEPARTMENTID IN (' + StringHelper.ArrayToList(None, organizeIds, '\'') + ')' \ + ' OR piuser.WORKGROUPID IN (' + StringHelper.ArrayToList(None, organizeIds, '\'') + '))' whereConditional = whereConditional + ' OR piuser.ID IN (' \ + ' SELECT ID' \ + ' FROM piuser' \ + ' WHERE (piuserorganize.DELETEMARK = 0)' \ + ' AND (' \ + ' piuserorganize.COMPANYID=' + departmentId + '\' OR ' \ + ' piuserorganize.SUBCOMPANYID=' + departmentId + '\' OR ' \ + ' piuserorganize.DEPARTMENTID=' + departmentId + '\' OR ' \ + ' piuserorganize.SUBDEPARTMENTID=' + departmentId + '\' OR ' \ + ' piuserorganize.WORKGROUPID=' + departmentId + '\'))' if auditStates: whereConditional = whereConditional + ' AND (piuser.AUDITSTATUS=\'' + auditStates + '\')' if roleIds: roles = StringHelper.ArrayToList(None, roleIds, '\'') whereConditional = whereConditional + ' AND (piuser.ID IN ( SELECT USERID FROM piuserrole WHERE ROLEID IN (' + roles + ')))' return whereConditional
def GetDTByPage(userInfo, searchValue, departmentId, roleId, pageSize=50, order=None): """ 分页查询 Args: searchValue (string): 查询字段 departmentId (string): 部门主键 roleId (string): 角色主键 pageSize (int): 每页显示 order (string): 排序 Returns: returnValue (Paginator): 用户分页列表 """ LogService.WriteLog(userInfo, __class__.__name__, FrameworkMessage.UserService, sys._getframe().f_code.co_name, FrameworkMessage.UserService_GetDTByPage, '') #countSqlQuery =' SELECT * FROM ' + Piuser._meta.db_table + ' WHERE ' countSqlQuery = 'SELECT PIUSER.* ,PIUSERLOGON.FIRSTVISIT,PIUSERLOGON.PREVIOUSVISIT,PIUSERLOGON.LASTVISIT,PIUSERLOGON.IPADDRESS,PIUSERLOGON.MACADDRESS,PIUSERLOGON.LOGONCOUNT,PIUSERLOGON.USERONLINE FROM PIUSER LEFT OUTER JOIN PIUSERLOGON ON PIUSER.ID = PIUSERLOGON.ID WHERE ' whereConditional = Piuser._meta.db_table + '.DELETEMARK' + ' = 0 ' \ + " AND " + Piuser._meta.db_table + '.ENABLED' + ' = 1 ' \ + " AND " + Piuser._meta.db_table + '.ISVISIBLE' + ' = 1 ' if departmentId: organizeIds = OrganizeService.GetChildrensById(None, departmentId) if len(organizeIds) != 0: whereConditional = whereConditional + " AND (" + Piuser._meta.db_table + '.COMPANYID IN (' + StringHelper.ArrayToList(None,organizeIds,"\'") + ')' \ + " OR " + Piuser._meta.db_table + '.SUBCOMPANYID IN (' + StringHelper.ArrayToList(None,organizeIds,"\'") + ')' \ + " OR " + Piuser._meta.db_table + '.DEPARTMENTID IN (' + StringHelper.ArrayToList(None,organizeIds,"\'") + ')' \ + " OR " + Piuser._meta.db_table + '.SUBDEPARTMENTID IN (' + StringHelper.ArrayToList(None,organizeIds,"\'") + ')' \ + " OR " + Piuser._meta.db_table + '.WORKGROUPID IN (' + StringHelper.ArrayToList(None,organizeIds,"\'") + '))' if roleId: whereConditional = whereConditional + ' AND ( ' + Piuser._meta.db_table + '.ID IN' \ + ' (SELECT USERID FROM ' + Piuserrole._meta.db_table \ + ' WHERE ROLEID = \'' + roleId + '\'' \ + ' AND ENABLED = 1' \ + ' AND DELETEMARK = 0 ))' if searchValue: whereConditional = whereConditional + " AND (" + searchValue + ')' if order: whereConditional = whereConditional + " ORDER BY " + order countSqlQuery = countSqlQuery + ' ' + whereConditional userList = DbCommonLibaray.executeQuery(None, countSqlQuery) returnValue = Paginator(userList, pageSize) return returnValue
def GetTreeResourceScopeIds(self, userId, targetCategory, permissionItemCode, childrens): """ 树型资源的权限范围 Args: userId (string): 用户主键 targetCategory (string): 资源分类 permissionItemCode (权限编号): childrens (是否含子节点): Returns: returnValue(string[]): 主键数组 """ resourceScopeIds = ResourcePermission.GetResourceScopeIds( self, userId, targetCategory, permissionItemCode) isList = StringHelper.ArrayToList(self, resourceScopeIds, ',') if not childrens: return resourceScopeIds if resourceScopeIds: sqlQuery = "SELECT ID FROM (SELECT ID FROM " + targetCategory + " WHERE (Id IN (" + isList + ") ) UNION ALL SELECT ResourceTree.Id AS ID FROM " + targetCategory + " AS ResourceTree INNER JOIN PiPermissionScope AS A ON A.Id = ResourceTree.ParentId) AS PermissionScopeTree" resourceIds = DbCommonLibaray.executeQuery(self, sqlQuery) #TODO:这里有一个BUG return resourceScopeIds.Concat(resourceIds) return resourceScopeIds
def GetPermissionScopeTargetIds(request): try: resourceCategory = request.POST['resourceCategory'] resourceId = request.POST['resourceId'] targetCategory = request.POST['targetCategory'] except: resourceCategory = None resourceId = None targetCategory = None ids = ResourcePermission.GetPermissionScopeTargetIds( None, resourceCategory, resourceId, targetCategory, "Resource.ManagePermission") returnValue = StringHelper.ArrayToList(None, ids, ',') response = HttpResponse() response.content = str(returnValue).strip(',') return response
def GetTreeResourceScopeIds(self, userId, tableName, permissionItemCode, childrens): """ 用户名是否重复 Args: fieldNames (string): 字段名 fieldValue (string): 字段值 Returns: returnValue(bool): 已存在 """ resourceScopeIds = None resourceScopeIds = PermissionScopeService.GetResourceScopeIds(self, userId, tableName, permissionItemCode) idList = StringHelper.ArrayToList(self, resourceScopeIds, '\'') if idList: sqlQuery = 'select id from ( select id from ' + tableName + ' where (id in (' + idList + ')) UNION ALL select ResourceTree.Id AS ID FROM ' + tableName + ' AS ResourceTree INNER JOIN pipermissionscope AS A ON A.Id = ResourceTree.ParentId) AS PermissionScopeTree' dataTable = DbCommonLibaray.executeQuery(self, sqlQuery) return resourceScopeIds
def GetTreeResourceScopeIds(self, userId, tableName, permissionItemCode, childrens): """ 树型资源的权限范围 Args: userId (string): 用户主键 tableName (string): 资源分类 permissionItemCode (string): 权限编号 childrens (string): 是否含子节点 Returns: returnValue(string[]): 主键列表 """ resourceScopeIds = ScopPermission.GetResourceScopeIds(self, userId, tableName, permissionItemCode) if not childrens: return resourceScopeIds idList = StringHelper.ArrayToList(self, resourceScopeIds, ',') if idList: sqlQuery = 'select id from ( select id from ' + tableName + ' where (id in (' + idList + ')) UNION ALL select ResourceTree.Id AS ID FROM ' + tableName + ' AS ResourceTree INNER JOIN pipermissionscope AS A ON A.Id = ResourceTree.ParentId) AS PermissionScopeTree' dataTable = DbCommonLibaray.executeQuery(self, sqlQuery) #TODO:这个地方需要把两个列表合并 #resourceScopeIds + dataTable return resourceScopeIds
def GetDTByOrganizes(userInfo, organizeIds): LogService.WriteLog(userInfo, __class__.__name__, FrameworkMessage.UserService, sys._getframe().f_code.co_name, FrameworkMessage.UserService_GetDTByOrganizes, organizeIds) organizeList = StringHelper.ArrayToList(None, organizeIds, '\'') sqlQuery = " SELECT * " \ + " FROM " + Piuser._meta.db_table \ + " WHERE (" + Piuser._meta.db_table + ".deletemark = 0 ) " \ + " AND (" + Piuser._meta.db_table + ".workgroupid IN ( " + organizeList + ") " \ + " OR " + Piuser._meta.db_table + ".departmentid IN (" + organizeList + ") " \ + " OR " + Piuser._meta.db_table + ".companyid IN (" + organizeList + ")) " \ + " OR id IN (" \ + " SELECT userid" \ + " FROM " + Piuserorganize._meta.db_table \ + " WHERE (" + Piuserorganize._meta.db_table + ".deletemark = 0 ) " \ + " AND (" + Piuserorganize._meta.db_table + ".workgroupid IN ( " + organizeList + ") " \ + " OR " + Piuserorganize._meta.db_table + ".departmentid IN (" + organizeList + ") " \ + " OR " + Piuserorganize._meta.db_table + ".companyid IN (" + organizeList + "))) " \ + " ORDER BY " + Piuser._meta.db_table + ".sortcode" return DbCommonLibaray.executeQuery(None, sqlQuery)
def GetSearchConditional(self, userInfo, permissionScopeCode, search, roleIds, enabled, auditStates, departmentId): LogService.WriteLog(userInfo, __class__.__name__, FrameworkMessage.UserService, sys._getframe().f_code.co_name, FrameworkMessage.UserService_GetSearchConditional, '') search = StringHelper.GetSearchString(self, search) whereConditional = 'piuser.deletemark=0 and piuser.isvisible=1 ' if not enabled == None: if enabled == True: whereConditional = whereConditional + " and ( piuser.enabled = 1 )" else: whereConditional = whereConditional + " and ( piuser.enabled = 0 )" if search: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'username' + " LIKE '" + search + "'" \ + " OR " + 'piuser' + "." + 'code' + " LIKE '" + search + "'" \ + " OR " + 'piuser' + "." + 'realname' + " LIKE '" + search + "'" \ + " OR " + 'piuser' + "." + 'quickquery' + " LIKE '" + search + "'" \ + " OR " + 'piuser' + "." + 'departmentname' + " LIKE '" + search + "'" \ + " OR " + 'piuser' + "." + 'description' + " LIKE '" + search + "')" if departmentId: organizeIds = OrganizeService.GetChildrensById(self, departmentId) if organizeIds and len(organizeIds) > 0: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'companyid' + " IN (" + StringHelper.ArrayToList(self, organizeIds,"'") + ")" \ + " OR " + 'piuser' + "." + 'companyid' + " IN (" + StringHelper.ArrayToList(self, organizeIds, "'") + ")" \ + " OR " + 'piuser' + "." + 'departmentid' + " IN (" + StringHelper.ArrayToList(self, organizeIds, "'") + ")" \ + " OR " + 'piuser' + "." + 'subdepartmentid' + " IN (" + StringHelper.ArrayToList(self, organizeIds, "'") + ")" \ + " OR " + 'piuser' + "." + 'workgroupid' + " IN (" + StringHelper.ArrayToList(self, organizeIds, "'") + "))" whereConditional = whereConditional + " OR " + 'piuser' + "." + 'id' + " IN (" \ + " SELECT " + 'userid' \ + " FROM " + 'piuserorganize' \ + " WHERE (" + 'piuserorganize' + "." + 'deletemark' + " = 0 ) " \ + " AND (" \ + 'piuserorganize' + "." + 'companyid' + " = '" + departmentId + "' OR " \ + 'piuserorganize' + "." + 'subcompanyid' + " = '" + departmentId + "' OR " \ + 'piuserorganize' + "." + 'departmentid' + " = '" + departmentId + "' OR " \ + 'piuserorganize' + "." + 'subdepartmentid' + " = '" + departmentId + "' OR " \ + 'piuserorganize' + "." + 'workgroupid' + " = '" + departmentId + "')) " if auditStates: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'auditstatus' + " = '" + auditStates + "')" if roleIds and len(roleIds) > 0: roles = StringHelper.ArrayToList(self, roleIds, "'") whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'id' + " IN (" + "SELECT " + 'userid' + " FROM " + 'piuserrole' + " WHERE " + 'roleid' + " IN (" + roles + ")" + "))" if (not userInfo.IsAdministrator ) and SystemInfo.EnableUserAuthorizationScope: permissionScopeItemId = PermissionItemService.GetId( self, permissionScopeCode) if permissionScopeItemId: #从小到大的顺序进行显示,防止错误发生 organizeIds = PermissionScopeService.GetOrganizeIds( self, userInfo.Id, permissionScopeCode) #没有任何数据权限 if PermissionScope.PermissionScopeDic.get('No') in organizeIds: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'id' + " = NULL ) " #按详细设定的数据 if PermissionScope.PermissionScopeDic.get( 'Detail') in organizeIds: userIds = PermissionScopeService.GetUserIds( self, userInfo.Id, permissionScopeCode) whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'id' + " IN (" + StringHelper.ObjectsToList( userIds) + ")) " #自己的数据,仅本人 if PermissionScope.PermissionScopeDic.get( 'User') in organizeIds: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'id' + " = '" + userInfo.Id + "') " #用户所在工作组数据 if PermissionScope.PermissionScopeDic.get( 'UserWorkgroup') in organizeIds: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'workgroupid' + " = '" + userInfo.WorkgroupId + "') " #用户所在部门数据 if PermissionScope.PermissionScopeDic.get( 'UserDepartment') in organizeIds: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'departmentid' + " = '" + userInfo.DepartmentId + "') " #用户所在公司数据 if PermissionScope.PermissionScopeDic.get( 'UserCompany') in organizeIds: whereConditional = whereConditional + " AND (" + 'piuser' + "." + 'companyid' + " = '" + userInfo.CompanyId + "') " #全部数据,这里就不用设置过滤条件了 if PermissionScope.PermissionScopeDic.get( 'All') in organizeIds: pass return whereConditional