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 CheckOnLine(self):
"""
检查用户在线状态(服务器专用)
Args:
Returns:
returnValue(int): 影响行数
"""
returnValue = 0
# 是否更新访问日期信息
if not SystemInfo.UpdateVisit:
return returnValue
sqlQuery = " UPDATE piuserlogon " \
+ " SET useronline = 0 " \
+ " WHERE (lastvisit IS NULL) " \
+ " OR ((useronline > 0) AND (lastvisit IS NOT NULL) AND ( DATE_ADD ( LastVisit, Interval " + str(SystemInfo.OnLineTime0ut) + " SECOND) < now()))"
returnValue = DbCommonLibaray.executeQuery(self, sqlQuery)
return returnValue
def GetConstraintDT(resourceCategory,
resourceId,
permissionCode="Resource.AccessPermission"):
"""
获取约束条件（所有的约束）
Args:
resourceCategory (string): 资源类别
resourceId (string): 资源主键
Returns:
returnValue (): 数据表
"""
permissionId = ''
permissionId = PermissionItemService.GetIdByAdd(permissionCode)
sqlQuery = " SELECT PIPERMISSIONSCOPE.ID , PITABLEPERMISSIONSCOPE.ITEMVALUE TABLECODE, PITABLEPERMISSIONSCOPE.ITEMNAME TABLENAME, PIPERMISSIONSCOPE.PERMISSIONCONSTRAINT, PITABLEPERMISSIONSCOPE.SORTCODE FROM(" + \
"SELECT ITEMVALUE, ITEMNAME, SORTCODE FROM PITABLEPERMISSIONSCOPE WHERE(DELETEMARK=0) AND(ENABLED=1) ) PITABLEPERMISSIONSCOPE LEFT OUTER JOIN (SELECT ID, TARGETID, PERMISSIONCONSTRAINT FROM PIPERMISSIONSCOPE WHERE (RESOURCECATEGORY = '" + resourceCategory + "') AND(RESOURCEID='" + resourceId + "') AND(TARGETCATEGORY='Table') AND(PERMISSIONID='" + permissionId + "') AND(DELETEMARK=0) AND(ENABLED=1) ) PIPERMISSIONSCOPE" + \
"ON PITABLEPERMISSIONSCOPE.ITEMVALUE = PIPERMISSIONSCOPE.TARGETID ORDER BY PITABLEPERMISSIONSCOPE.SORTCODE"
dataTable = DbCommonLibaray.executeQuery(None, sqlQuery)
return dataTable
def GetDTByPage(pageIndex=1, pageSize=20, whereConditional="", order=""):
"""
获取系统操作日志分页列表
Args:
pageIndex (int): 当前页
pageSize (int): 记录总数
whereConditional (string): 条件表达式
order (stirng): 排序字段
Returns:
"""
if whereConditional:
whereConditional = "WHERE " + whereConditional
if not order:
order = " createon "
sqlQuery = "select * from cilog " + whereConditional + " order by " + order
dtLog = DbCommonLibaray.executeQuery(None, sqlQuery)
pages = Paginator(dtLog, pageSize)
recordCount = pages.count
return recordCount, pages.page(pageIndex)
def ExportExcel(request):
fields = request.GET['fields']
filters = request.GET['filters']
tableName = request.GET['tableName']
sortField = request.GET['sortField']
if not sortField:
sortField = "SORTCODE"
dt = DbCommonLibaray.GetDTByPage(tableName, filters, sortField, fields, 1,
99999)
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment;filename=' + tableName + str(
datetime.datetime.now()) + '.xls'
wb = xlwt.Workbook(encoding='utf8')
sheet = wb.add_sheet(tableName)
if len(dt) > 0:
i = 0
headerDic = []
for headerKey in dt[0].keys():
sheet.write(0, i, headerKey)
headerDic.append(headerKey)
i = i + 1
data_row = 1
for excelValue in dt:
data_col = 0
for header in headerDic:
sheet.write(data_row, data_col, excelValue.get(header))
data_col = data_col + 1
data_row = data_row + 1
output = BytesIO()
wb.save(output)
output.seek(0)
response.write(output.getvalue())
return response
def GetFileDTByPage(self,
pageIndex=1,
pageSize=20,
whereConditional="",
order=""):
"""
分页获取文件
Args:
pageIndex (string): 页数
pageSize (string): 每页数据大小
whereConditional (string): 查询条件
order (string): 排序方式
Returns:
returnValue (List[Cifile] or None): 文件列表
"""
SelectField = 'ID'
+ " ," + 'FOLDERID'
+ " ," + 'FILENAME'
+ " ," + 'FILEPATH'
+ " ," + 'FILESIZE'
+ " ," + 'READCOUNT'
+ " ," + 'CATEGORY'
+ " ," + 'DESCRIPTION'
+ " ," + 'ENABLED'
+ " ," + 'SORTCODE'
+ " ," + 'CREATEUSERID'
+ " ," + 'CREATEBY'
+ " ," + 'CREATEON'
+ " ," + 'MODIFIEDUSERID'
+ " ," + 'MODIFIEDBY'
+ " ," + 'MODIFIEDON'
+ " , (SELECT " + 'FOLDERNAME'
+ " FROM " + 'cifolder'
+ " WHERE " + 'ID' + " = '" + 'FOLDERID' + "') AS FolderFullName "
returnValue = DbCommonLibaray.GetDTByPage('cifile', whereConditional,
order, SelectField,
pageIndex, pageSize)
return len(returnValue), returnValue
def GetDTByPage(pageIndex=1, pageSize=20, whereConditional="", order=""):
"""
获取系统异常分页列表
Args:
searchValue (string): 查询字段
departmentId (string): 部门主键
roleId (string): 角色主键
pageSize (int): 每页显示
order (string): 排序
Returns:
returnValue (Paginator): 异常分页列表
"""
if whereConditional:
whereConditional = "WHERE " + whereConditional
if not order:
order = " createon "
sqlQuery = "select * from ciexception " + whereConditional + " order by " + order
dtException = DbCommonLibaray.executeQuery(None, sqlQuery)
pages = Paginator(dtException, pageSize)
recordCount = pages.count
return recordCount, pages.page(pageIndex)
def GetMessagesByConditional(userInfo,
whereConditional,
pageIndex=0,
pageSize=20,
order=None):
"""
通过指定条件得到消息
Args:
userInfo (string): 用户
whereConditional (string): 条件表达式
pageIndex (string): 当前页
pageSize (string): 每页显示
order (string): 排序
Returns:
returnValue (Cimessage[]): 数据表
"""
if not order:
order = "createon DESC "
if not userInfo.IsAdministrator:
if whereConditional:
whereConditional = whereConditional + " AND (" + 'receiverid' + "='" + userInfo.Id + "' OR " \
+ 'createuserid' + "='" + userInfo.Id + "')"
else:
whereConditional = whereConditional + 'receiverid' + "='" + userInfo.Id + "' OR " \
+ 'createuserid' + "='" + userInfo.Id + "'"
if whereConditional:
whereConditional = whereConditional + " AND " + 'deletemark' + " = 0"
else:
whereConditional = whereConditional + ' deletemark = 0 '
if whereConditional:
sqlQuery = "select * from cimessage where " + whereConditional + " order by " + order
else:
sqlQuery = "select * from cimessage" + " order by " + order
dataTable = DbCommonLibaray.executeQuery(None, sqlQuery)
pages = Paginator(dataTable, pageSize)
recordCount = pages.count
return recordCount, pages.page(pageIndex)
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 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 GetUserOrganizeDT(self, userId):
"""
获得用户的组织机构兼职情况
Args:
userId (string): 用户主键
Returns:
returnValue (Piorganize[]): 组织结构实体
"""
sqlQuery = " SELECT PIUSERORGANIZE.* " \
+ " , PiOrganize1.FULLNAME AS CompanyName " \
+ " , PiOrganize2.FULLNAME AS SubCompanyName " \
+ " , PiOrganize3.FULLNAME AS DepartmentName " \
+ " , PiOrganize4.FULLNAME AS SubDepartmentName " \
+ " , PiOrganize5.FULLNAME AS WorkGroupName " \
+ " FROM PIUSERORGANIZE LEFT OUTER JOIN " \
+ " PIORGANIZE PiOrganize1 ON PIUSERORGANIZE.CompanyId = PiOrganize1.Id LEFT OUTER JOIN " \
+ " PIORGANIZE PiOrganize2 ON PIUSERORGANIZE.SubCompanyId = PiOrganize2.Id LEFT OUTER JOIN " \
+ " PIORGANIZE PiOrganize3 ON PIUSERORGANIZE.DepartmentId = PiOrganize3.Id LEFT OUTER JOIN " \
+ " PIORGANIZE PiOrganize4 ON PIUSERORGANIZE.SubDepartmentId = PiOrganize4.Id LEFT OUTER JOIN " \
+ " PIORGANIZE PiOrganize5 ON PIUSERORGANIZE.WorkgroupId = PiOrganize5.Id " \
+ " WHERE USERID = '" + userId + "'AND PIUSERORGANIZE.DELETEMARK = 0 "
returnValue = DbCommonLibaray.executeQuery(self, sqlQuery)
return returnValue
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 Searchs(userInfo, permissionScopeCode, search, roleIds, enabled,
audiStates, departmentId):
"""
查询用户
Args:
permissionScopeCode (string): 权限码
search (string): 查询字段
roleIds (string[]): 用户角色
enabled (string): 启用标志
auditStates (string): 审核状态
departmentId (string): 组织机构ID
Returns:
returnValue (List): 用户列表
"""
userList = []
sqlQuery = 'select piuser.*,piuserlogon.FIRSTVISIT,piuserlogon.PREVIOUSVISIT,piuserlogon.LASTVISIT,piuserlogon.IPADDRESS,piuserlogon.MACADDRESS,piuserlogon.LOGONCOUNT,piuserlogon.USERONLINE,piuserlogon.CHECKIPADDRESS,piuserlogon.MULTIUSERLOGIN FROM PIUSER LEFT OUTER JOIN PIUSERLOGON ON PIUSER.ID = PIUSERLOGON.ID '
whereConditional = UserSerivce.GetSearchConditional(
None, userInfo, permissionScopeCode, search, roleIds, enabled,
audiStates, departmentId)
sqlQuery = sqlQuery + " WHERE " + whereConditional
sqlQuery = sqlQuery + " ORDER BY piuser.SORTCODE"
userList = DbCommonLibaray.executeQuery(None, sqlQuery)
return userList