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