def GetDTByOrganize(userInfo, organizeId, containChildren):
"""
按组织结构获取员工列表
Args:
organizeId (string): 组织结构id
containChildren (bool): 组织结构是否包含子机构
Returns:
returnValue (List): 员工列表
"""
LogService.WriteLog(userInfo, __class__.__name__, FrameworkMessage.StaffService,
sys._getframe().f_code.co_name, FrameworkMessage.StaffService_GetDTByOrganize, organizeId)
if containChildren:
organizeIds = OrganizeService.GetChildrensById(None, organizeId)
staffIds = []
for staff in Pistafforganize.objects.filter(Q(organizeid__in=organizeIds) & Q(deletemark=0)):
staffIds.append(staff.staffid)
returnValue = Pistaff.objects.filter(Q(id__in=staffIds) & Q(deletemark=0)).order_by('sortcode')
return returnValue
else:
starffIds = []
for staff in Pistafforganize.objects.filter(Q(organizeid=organizeId) & Q(deletemark=0)):
starffIds.append(staff.staffid)
returnValue = Pistaff.objects.filter(Q(id__in=starffIds) & Q(deletemark=0)).order_by('sortcode')
return returnValue
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 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