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 GetNewSequence(self, fullName, defaultSequence, sequenceLength,
fillZeroPrefix):
"""
获取新序列号
Args:
fullName (string): 序列名称
defaultSequence (int): 默认序列
sequenceLength (int): 序列长度
fillZeroPrefix (bool): 是否填充补零
Returns:
returnValue (string): 序列号
"""
sequenceEntity = Cisequence.objects.get_or_create(
defaults={'fullname': fullName},
fullname=fullName,
sequence=defaultSequence,
reduction=SequenceService.DefaultReduction,
step=SequenceService.DefaultStep,
prefix=fillZeroPrefix,
separate=SequenceService.DefaultSeparator)
sequence = sequenceEntity.sequence
if SequenceService.FillZeroPrefix:
sequence = StringHelper.RepeatString(
self, '0', len(
sequenceEntity.sequence)) + sequenceEntity.sequence
if SequenceService.UsePrefix:
sequence = sequenceEntity.prefix + sequenceEntity.separate + sequence
return sequence
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 GetTreeResourceScopeIds(self, userId, tableName, permissionItemCode,
childrens):
"""
树型资源的权限范围
Args:
userId (string): 用户主键
tableName (string): 资源分类
permissionItemCode (string): 权限编号
childrens (string): 是否含子节点
Returns:
returnValue(string[]): 主键列表
"""
resourceScopeIds = PermissionScopeService.GetResourceScopeIds(
self, userId, tableName, permissionItemCode)
if not childrens:
return resourceScopeIds
idList = StringHelper.ObjectsToList(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 GetRoleUserIds(request):
try:
roleId = request.POST['roleId']
except:
roleId = None
dtScope = RoleService.GetRoleUserIds(None, roleId)
returnValue = StringHelper.GetSpitString(dtScope, ',')
response = HttpResponse()
response.content = returnValue
return response
def GetPermissionItemsByUserId(request):
try:
userId = request.POST['userId']
except:
userId = None
if userId:
ids = UserPermission.GetUserPermissionItemIds(None, userId)
returnValue = StringHelper.GetSpitString(ids, ',')
response = HttpResponse()
response.content = returnValue
return response
else:
response = HttpResponse()
response.content = ''
return response
def GetUserRoleIds(request):
try:
userId = request.POST['userId']
except:
userId = None
if userId:
ids = UserRoleService.GetUserRoleIds(None, userId)
returnValue = StringHelper.GetSpitString(ids, ',')
response = HttpResponse()
response.content = returnValue
return response
else:
response = HttpResponse()
response.content = ''
return response
def GetPermissionItemsByRoleId(request):
try:
roleId = request.POST['roleId']
except:
roleId = None
if roleId:
ids = RolePermission.GetRolePermissionItemIds(None, roleId)
returnValue = StringHelper.GetSpitString(ids, ',')
response = HttpResponse()
response.content = returnValue
return response
else:
response = HttpResponse()
response.content = ''
return response
def GetModuleByUserId(request):
try:
userId = request.POST['userId']
except:
userId = None
if userId:
moduleIds = UserPermission.GetScopeModuleIdsByUserId(
None, userId, "Resource.AccessPermission")
returnValue = StringHelper.GetSpitString(moduleIds, ',')
response = HttpResponse()
response.content = returnValue
return response
else:
response = HttpResponse()
response.content = ''
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 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 GetSequence(self, fullName):
"""
获取序列号
Args:
fullName (string): 序列名称
Returns:
returnValue (string): 序列号
"""
sequenceEntity = SequenceService.GetEntityByAdd(self, fullName)
SequenceService.UpdateSequence(self, fullName, 1)
sequence = sequenceEntity.sequence
if SequenceService.FillZeroPrefix:
sequence = StringHelper.RepeatString(
self, '0', len(str(sequenceEntity.sequence))) + str(
sequenceEntity.sequence)
if SequenceService.UsePrefix:
sequence = sequenceEntity.prefix + sequenceEntity.separate + sequence
return sequence
def GetUserids(self, fullName, count):
"""
获取序列号
Args:
fullName (string): 序列名称
count (int): 个数
Returns:
returnValue (string): 序列号
"""
returnValue = [count]
sequenceEntity = SequenceService.GetEntityByAdd(self, fullName)
SequenceService.UpdateSequence(self, fullName, count)
sequence = sequenceEntity.sequence
if SequenceService.FillZeroPrefix:
sequence = StringHelper.RepeatString(
self, '0', len(
sequenceEntity.sequence)) + sequenceEntity.sequence
if SequenceService.UsePrefix:
sequence = sequenceEntity.prefix + sequenceEntity.separate + sequence
return sequence
def GetDTSql(userIds, name, value, beginDate, endDate):
sqlQuery = " SELECT * FROM " + 'cilog' + " WHERE 1=1 "
if value:
sqlQuery = sqlQuery + " AND " + name + " = '" + value + "' "
if beginDate and endDate:
beginDate = str(time.strftime("%Y-%m-%d %H:%M:%S", beginDate))
endDate = str(time.strftime("%Y-%m-%d %H:%M:%S", endDate))
if userIds:
sqlQuery = sqlQuery + " AND " + 'createuserid' + " IN (" + StringHelper.ObjectsToList(
userIds) + ") "
if len(beginDate.strip()) > 0:
sqlQuery = sqlQuery + " AND CREATEON >= '" + beginDate + "'"
if len(endDate.strip()) > 0:
sqlQuery = sqlQuery + " AND CREATEON <= '" + endDate + "'"
sqlQuery = sqlQuery + " ORDER BY CREATEON DESC "
return sqlQuery
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