def GetDTByDate(userInfo, beginDate, endDate, userId, moduleId):
"""
按时间获取列表
Args:
userInfo (UserInfo): 当前用户
beginDate (string): 开始时间
endDate (string): 结束时间
userId (string): 用户主键
moduleId (string): 模块主键
Returns:
"""
if userId:
dataTable = DbCommonLibaray.executeQuery(
None,
LogService.GetDTSql([userId], 'processid', moduleId, beginDate,
endDate))
else:
if userInfo.IsAdministrator:
dataTable = DbCommonLibaray.executeQuery(
None,
LogService.GetDTSql(None, 'processid', moduleId, beginDate,
endDate))
else:
userIds = PermissionScopeService.GetUserIds(
None, userInfo.Id, "Resource.ManagePermission")
dataTable = DbCommonLibaray.executeQuery(
None,
LogService.GetDTSql(userIds, 'processid', moduleId,
beginDate, endDate))
return dataTable
def GetUserReceivedMessagesByPage(userInfo, userId, whereConditional, pageIndex=0, pageSize=20, order=None):
"""
得到指定用户收到的消息
Args:
userInfo (string): 用户
userId (string): 指定用户主键
whereConditional (string): 条件表达式
pageIndex (string): 当前页
pageSize (string): 每页显示
order (string): 排序
Returns:
returnValue (Cimessage[]): 数据表
"""
if not userId:
userId = userInfo.Id
if whereConditional:
whereConditional = whereConditional + " AND "
whereConditional = whereConditional + "deletemark = 0 and enabled = 1 and createuserid = '" + userId + "'"
if not order:
order = ' desc '
sqlQuery = "select id from cimessage " + whereConditional + " order by " + order
messageDT = DbCommonLibaray.executeQuery(None, sqlQuery)
pages = Paginator(messageDT, pageSize)
recordCount = pages.count
return recordCount, pages.page(pageIndex)
def GetDTByFolder(self, folderId):
"""
根据目录获取文件
Args:
folderId (string): 目录id
Returns:
returnValue (Cifile or None): 是否存在
"""
sqlQuery = " SELECT " + '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 "
+ " FROM " + 'cifile'
+ " WHERE " + 'FOLDERID' + " = " + folderId
returnValue = DbCommonLibaray.executeQuery(self, sqlQuery)
return returnValue
def GetDTByPage(self, searchValue, pageSize=50, order=None):
"""
分页查询
Args:
searchValue (string): 查询字段
pageSize (int): 每页显示
order (string): 排序
Returns:
staffCount (int): 所有员工数
returnValue (Paginator): 员工分页列表
"""
if not searchValue:
if not order:
whereConditional = 'SELECT * FROM ' + Cisequence._meta.db_table + ' WHERE deletemark = 0'
else:
whereConditional = 'SELECT * FROM ' + Cisequence._meta.db_table + ' WHERE deletemark = 0 ORDER BY ' + order
else:
if not order:
'SELECT * FROM ' + Cisequence._meta.db_table + ' WHERE ' + searchValue + ' AND deletemark = 0'
else:
whereConditional = 'SELECT * FROM ' + Cisequence._meta.db_table + ' WHERE ' + searchValue + ' AND deletemark = 0 ORDER BY ' + order
staffList = DbCommonLibaray.executeQuery(self, whereConditional)
returnValue = Paginator(staffList, pageSize)
staffCount = returnValue.count
return staffCount, returnValue
def GetDTByPage(userInfo, searchValue, pageSize=50, order=None):
"""
分页查询
Args:
searchValue (string): 查询字段
pageSize (int): 每页显示
order (string): 排序
Returns:
staffCount (int): 所有员工数
returnValue (Paginator): 员工分页列表
"""
LogService.WriteLog(userInfo, __class__.__name__, FrameworkMessage.StaffService,
sys._getframe().f_code.co_name, FrameworkMessage.StaffService_GetDTByPage, '')
if not searchValue:
if not order:
whereConditional = 'SELECT * FROM ' + Pistaff._meta.db_table + ' WHERE deletemark = 0'
else:
whereConditional = 'SELECT * FROM ' + Pistaff._meta.db_table + ' WHERE deletemark = 0 ORDER BY ' + order
else:
if not order:
'SELECT * FROM ' + Pistaff._meta.db_table + ' WHERE ' + searchValue + ' AND deletemark = 0'
else:
whereConditional = 'SELECT * FROM ' + Pistaff._meta.db_table + ' WHERE ' + searchValue + ' AND deletemark = 0 ORDER BY ' + order
staffList = DbCommonLibaray.executeQuery(None, whereConditional)
returnValue = Paginator(staffList, pageSize)
staffCount = returnValue.count
return staffCount,returnValue
def GetDtByPage(userInfo, pageSize=20, whereConditional="", order=""):
"""
取得角色列表
Args:
whereConditional (string): 查询字段
pageSize (int): 每页显示
order (string): 排序
Returns:
recordCount (int): 角色数
returnValue (List): 角色分页列表
"""
LogService.WriteLog(userInfo, __class__.__name__,
FrameworkMessage.RoleService,
sys._getframe().f_code.co_name,
FrameworkMessage.RoleService_GetDTByPage, '')
if not whereConditional:
if not order:
whereConditional = 'SELECT * FROM ' + Pirole._meta.db_table + ' WHERE deletemark = 0'
else:
whereConditional = 'SELECT * FROM ' + Pirole._meta.db_table + ' WHERE deletemark = 0 ORDER BY ' + order
else:
if not order:
'SELECT * FROM ' + Pirole._meta.db_table + ' WHERE ' + whereConditional + ' AND deletemark = 0'
else:
whereConditional = 'SELECT * FROM ' + Pirole._meta.db_table + ' WHERE ' + whereConditional + ' AND deletemark = 0 ORDER BY ' + order
staffList = DbCommonLibaray.executeQuery(None, whereConditional)
returnValue = Paginator(staffList, 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 GetUserDTByRole(userInfo, roleId):
"""
按角色获取用户列表
Args:
roleId (string): 角色主键
Returns:
returnValue (List): 用户实体列表
"""
LogService.WriteLog(userInfo, __class__.__name__,
FrameworkMessage.UserService,
sys._getframe().f_code.co_name,
FrameworkMessage.UserService_GetUserDTByRole,
roleId)
sqlQuery = " SELECT " + 'piuser' + ".* " \
+ "," + 'piuserlogon' + "." + 'useronline' \
+ " FROM PIUSER LEFT OUTER JOIN PIUSERLOGON ON PIUSER.ID = PIUSERLOGON.ID "
sqlQuery = sqlQuery + " WHERE (" + 'piuser' + "." + 'deletemark' + " = 0 "
+ " AND " + 'piuser' + "." + 'enabled' + " = 1 "
+ " AND " + 'piuser' + "." + 'isvisible' + " = 1 ) "
if roleId:
sqlQuery = sqlQuery + " AND (" + 'piuser' + "." + 'roleid' + " = '" + roleId + "') "
sqlQuery += " OR " + 'piuser' + "." + 'id' + " IN (" \
+ " SELECT " + 'userid' \
+ " FROM " + 'piuserrole' \
+ " WHERE " + 'piuserrole' + "." + 'deletemark' + " = 0 " \
+ " AND " + 'piuserrole' + "." + 'enabled' + " = 1 " \
+ " AND " + 'piuserrole' + "." + 'roleid' + " = '" + roleId + "') "
sqlQuery += " ORDER BY " + 'piuser' + "." + 'sortcode'
dataTable = DbCommonLibaray.executeQuery(None, sqlQuery)
return dataTable
def GetListByDepartment(userInfo, departmentId, containChildren):
"""
得到指定组织包含的用户列表
Args:
departmentId (string): 组织机构主键
containChildren (string): 是否包含子部门
Returns:
returnValue (List[Dic[Piuser]]): 用户列表
"""
LogService.WriteLog(userInfo, __class__.__name__,
FrameworkMessage.UserService,
sys._getframe().f_code.co_name,
FrameworkMessage.UserService_GetListByDepartment,
departmentId + '/' + containChildren)
sqlQuery = " SELECT " + Piuser._meta.db_table + ".* FROM " + Piuser._meta.db_table \
+ " WHERE (" + Piuser._meta.db_table + ".deletemark = 0 " \
+ " AND " + Piuser._meta.db_table + ".enabled = 1 ) "
if departmentId:
sqlQuery = sqlQuery + " AND ((" + Piuser._meta.db_table + ".departmentid = '" + departmentId + "') "
sqlQuery = sqlQuery + " OR id IN (" \
+ " SELECT userid" \
+ " FROM " + Piuserorganize._meta.db_table \
+ " WHERE (" + Piuserorganize._meta.db_table + ".deletemark = 0 ) " \
+ " AND (" + Piuserorganize._meta.db_table + ".departmentid = '" + departmentId + "'))) " \
returnValue = DbCommonLibaray.executeQuery(None, sqlQuery)
return returnValue
def SearchByPage(self,
userInfo,
permissionScopeCode,
search,
roleIds,
enabled,
auditStates,
departmentId,
pageIndex=0,
pageSize=20,
order=None):
LogService.WriteLog(userInfo, __class__.__name__,
FrameworkMessage.UserService,
sys._getframe().f_code.co_name,
FrameworkMessage.UserService_SearchByPage, '')
whereConditional = UserSerivce.GetSearchConditional(
None, userInfo, permissionScopeCode, search, roleIds, enabled,
auditStates, departmentId)
#countSqlQuery = ' SELECT * FROM ' + Piuser._meta.db_table + ' WHERE '
countSqlQuery = 'SELECT * FROM (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 WHERE PIUSER.DELETEMARK = 0 AND PIUSER.ISVISIBLE = 1 AND (PIUSER.ENABLED = 1) ORDER BY PIUSER.SORTCODE) PIUSER WHERE '
countSqlQuery = countSqlQuery + ' ' + whereConditional
userList = DbCommonLibaray.executeQuery(self, countSqlQuery)
pageValue = Paginator(userList, pageSize)
page = pageValue.page(pageIndex)
return pageValue.count, page
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 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 GetOnLineStateDT(self):
sqlQuery = " SELECT " + 'piuserlogon' + "." + 'id' \
+ ", " + 'piuserlogon' + "." + 'useronline' \
+ " FROM " + 'piuserlogon' \
+ " WHERE " + 'piuserlogon' + "." + 'lastvisit' + " IS NOT NULL " \
+ " AND (DATEADD (s, " + str(SystemInfo.OnLineTime0ut + 5) + ", " + 'lastvisit' + ") > " + datetime.datetime.now() + ")"
returnValue = DbCommonLibaray.executeQuery(None, sqlQuery)
return returnValue
def GetTableNameAndCode(self):
"""
得到所有数据表（表的中文名称与英文名称）
Args:
Returns:
returnValue (Citablecolumns[]): 实体列表
"""
sqlQuery = "SELECT DISTINCT TABLECODE,TABLECODE AS TABLENAME FROM CITABLECOLUMNS ORDER BY TABLECODE"
returnValue = DbCommonLibaray.executeQuery(None, sqlQuery)
return returnValue
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 GetDTApplicationByDate(self, beginDate, endDate):
"""
按日期获取日志（业务）
Args:
beginDate (datetime): 开始时间
endDate (datetime): 结束时间
Returns:
returnValue (Cilog): 数据表
"""
returnValue = DbCommonLibaray.executeQuery(
None, LogService.GetDTSql(None, '', '', beginDate, endDate))
return returnValue
def GetDTByModule(userInfo, processId, beginDate, endDate):
"""
按模块获取日志
Args:
processId (string): 服务名称
beginDate (datetime): 开始时间
endDate (datetime): 结束时间
Returns:
"""
if userInfo.IsAdministrator:
dataTable = DbCommonLibaray.executeQuery(
None,
LogService.GetDTSql(None, 'processid', processId, beginDate,
endDate))
else:
userIds = PermissionScopeService.GetUserIds(
None, userInfo.Id, "Resource.ManagePermission")
dataTable = DbCommonLibaray.executeQuery(
None,
LogService.GetDTSql(userIds, 'processid', processId, beginDate,
endDate))
return dataTable
def GetDTByUser(userId, beginDate, endDate):
"""
按用户获取日志
Args:
userId (string): 用户主键
beginDate (datetime): 开始时间
endDate (datetime): 结束时间
Returns:
"""
returnValue = DbCommonLibaray.executeQuery(
None,
LogService.GetDTSql(None, 'userid', userId, beginDate, endDate))
return returnValue
def GetChildrensIdByCode(self, code):
"""
根据编号获取子节点列表
Args:
code (string): 编号
Returns:
returnValue (List): 子节点列表
"""
returnValue = []
sqlQuery = "select id from piorganize where (LEFT(CODE,LENGTH('" + code + "'))='" + code + "')"
childrens = DbCommonLibaray.executeQuery(self, sqlQuery)
for c in childrens:
returnValue.append(c.get('id'))
return returnValue
def GetDTByCondition(self, condition):
"""
通过条件得到模块
Args:
condition (string): 条件表达式
Returns:
returnValue (Pimodule[]): 模块实体
"""
if not condition:
condition = ' deletemark=0 '
else:
condition = condition + ' AND deletemark = 0'
returnValue = DbCommonLibaray.executeQuery(self, condition)
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 GetSearchFields(tableCode):
"""
得到查询项
Args:
tableCode (string): 表名称
Returns:
returnValue (): 数据表
"""
sqlQuery = "SELECT " + 'columncode' + "," \
+ 'columnname' + "," \
+ 'datatype' \
+ " FROM " + 'citablecolumns' \
+ " WHERE " + 'tablecode' + "='" + tableCode + "' AND " \
+ 'issearchcolumn' + "= 1 AND " \
+ 'deletemark' + "= 0" \
+ " ORDER BY " + 'sortcode'
returnValue = DbCommonLibaray.executeQuery(None, sqlQuery)
return returnValue
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 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 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 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 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
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)