def roles_business_test(roles, api): ''' 角色和业务匹配 ''' sql = ''' select restrict from api where api = '%s' ''' % api apis = pg_update.selectBySql(sql) if len(api) == 0: return False elif apis[0]['restrict'] == 0: return True if len(roles) == 0: return False roles_str = '' for role in roles: roles_str += "'" + role['role_code'] + "'," sql = ''' select business_code from business where business_code in (select business_code from role_business where role_code in (%s)) ''' % roles_str[:-1] business_codes = pg_update.selectBySql(sql) sql = ''' select seq_code from business where business_code in (select business_code from business_api where api = '%s') ''' % api business_seq_code = pg_update.selectBySql(sql)[0]['seq_code'] for business_code in business_codes: if business_seq_code.find(business_code['business_code']) > 0: return True return False
def get_users(parm): ''' 获取用户信息类别,加所属角色信息 ''' result = {} limit = 10 page = parm['page'] search_key = parm['search_key'] offset = (int(page) - 1) * limit where = ("u.status = 0 and u.name like '%%%s%%' " % search_key if search_key and search_key != '' else " u.status = 0 ") sql = ''' select u.name,u.account from user_info u where %s order by u.id limit %s offset %s ''' % (where, limit, offset) users = pg_update.selectBySql(sql) accounts_str = '' for user in users: accounts_str += "'" + user['account'] + "'," sql = ''' select u_r.account,r.role_code,r.role_name from user_role u_r,role r where u_r.account in (%s) and u_r.role_code = r.role_code and u_r.status = 0 ''' % accounts_str[:-1] user_roles = pg_update.selectBySql(sql) for user in users: user['roles'] = [] user['role'] = '' for user_role in user_roles: if user_role['account'] == user['account']: user['role'] += (user_role['role_name'] + ' ') user['roles'].append(user_role) sql_count = ''' select count(*) from user_info u where %s ''' % where count = pg_update.selectBySql(sql_count)[0]['count'] page_count = math.ceil(float(count) / limit) result['datas'] = users result['page_count'] = page_count return result
def users_count(self, where): sql_count = ''' select count(*) from user_info where %s ''' % where return pg_update.selectBySql(sql_count)[0]['count']
def get_apis(parm): ''' 获取api ''' result = {} limit = 10 page = parm['page'] search_key = parm['search_key'] offset = (int(page) - 1) * limit where = ("a.status = 0 and a.api like '%%%s%%' " % search_key if search_key and search_key != '' else " a.status = 0 ") sql = ''' select a.* from api a where %s order by id limit %s offset %s ''' % (where, limit, offset) apis = pg_update.selectBySql(sql) api_str = '' for api in apis: api_str += "'" + api['api'] + "'," sql = ''' select b_a.api,b.business_code,b.business_name from business_api b_a,business b where b_a.api in (%s) and b_a.business_code = b.business_code and b_a.status = 0 ''' % api_str[:-1] business_apis = pg_update.selectBySql(sql) for api in apis: api['business'] = [] api['business_text'] = '' for business_api in business_apis: if business_api['api'] == api['api']: api['business_text'] += (business_api['business_name'] + ' ') api['business'].append(business_api) sql_count = ''' select count(*) from api a where %s ''' % where count = pg_update.selectBySql(sql_count)[0]['count'] page_count = math.ceil(float(count) / limit) result['datas'] = apis result['page_count'] = page_count return result
def get_apis(self, parm): ''' 获取api ''' result = {} limit = 10 page = parm['page'] search_key = parm['search_key'] offset = (int(page) - 1) * limit where = ("a.status = 0 and a.api like '%%%s%%' " % search_key if search_key and search_key != '' else " a.status = 0 ") sql = ''' select a.* from api a where %s order by id limit %s offset %s ''' % (where, limit, offset) apis = pg_update.selectBySql(sql) api_str = ','.join(["'" + api['api'] + "'" for api in apis]) sql = ''' select b_a.api,b.business_code,b.business_name from business_api b_a,business b where b_a.api in (%s) and b_a.business_code = b.business_code and b_a.status = 0 ''' % api_str business_apis = pg_update.selectBySql(sql) if apis else [] for api in apis: api['business'] = [] api['business_text'] = '' for business_api in business_apis: if business_api['api'] == api['api']: api['business_text'] += (business_api['business_name'] + ' ') api['business'].append(business_api) sql_count = ''' select count(*) from api a where %s ''' % where count = pg_update.selectBySql(sql_count)[0]['count'] page_count = math.ceil(float(count) / limit) result['datas'] = apis result['page_count'] = page_count return result
def get_roles_by_account(self, account): sql = ''' select * from role where role_code in ( select r.role_code from user_info u,user_role r where u.account = '%s' and u.account = r.account and r.status=0) ''' % account return pg_update.selectBySql(sql)
def get_users(self, offset, limit, where): sql = ''' select name,account from user_info where %s order by id limit %s offset %s ''' % (where, limit, offset) return pg_update.selectBySql(sql)
def get_message_by_account(self, account): sql = ''' select m.message,m.message_type,m.send_type,m.send_value,msr.* from message m, message_send_record msr where msr.message_id = m.id and msr.send_status = 0 and m.status = 0 and msr.status = 0 and msr.receive_account = '%s' ''' % account return pg_update.selectBySql(sql)
def get_user_by_id(user_id): sql = ''' select * from role where role_code in ( select r.role_code from user_info u,user_role r where u.id = %s and u.account = r.account and r.status=0) ''' % user_id roles = pg_update.selectBySql(sql) user = pg_update.select("user_info", where=' id = %s ' % user_id, columns='id,account')[0] user['roles'] = roles return user
def get_businesses_by_role_code(self, role_code): ''' 获取某角色拥有的所有业务 ''' business_codes = pg_update.select("role_business", where="role_code = '%s'" % role_code) where = '' for business_code in business_codes: where += "or seq_code like '%%%s%%'" % business_code['business_code'] sql = ''' select * from ( select * from business where status = 0 ) b where %s ''' % where[2:] return pg_update.selectBySql(sql)
def __addBusinessApi(api, business): ''' 传入api及business 添加到business_code表中 ''' sql = ''' select * from business_api where api = '%s' and business_code = '%s' ''' % (api, business) business_apis = pg_update.selectBySql(sql) if len(business_apis) == 0: data_map['api'] = api data_map['business_code'] = business pg_update.insertOne("business_api", data_map)
def __addRoleBusiness(role, business): ''' 传入role及business 添加到role_business表中 ''' sql = ''' select * from role_business where role = '%s' and business_code = '%s' ''' % (role, business) role_business = pg_update.selectBySql(sql) if len(business_apis) == 0: data_map['role_code'] = api data_map['business_code'] = business pg_update.insertOne("role_business", data_map)
def get_bar_data_by_roles(self, roles): ''' 更加角色获取菜单数据 ''' roles = [role['role_code'] for role in roles] ls = "','" roles_str = "'" + (ls.join(roles)) + "'" sql = ''' select b.* from role_business rb, business b where rb.business_code = b.business_code and rb.role_code in (%s) and rb.status = 0 and b.status = 0 ''' % roles_str return pg_update.selectBySql(sql)
def get_bar_data_by_roles(roles): ''' 更加角色获取菜单数据 ''' roles_str = '' for role in roles: roles_str += "'" + role['role_code'] + "'," sql = ''' select b.* from role_business rb, business b where rb.business_code = b.business_code and rb.role_code in (%s) and rb.status = 0 and b.status = 0 ''' % roles_str[:-1] return pg_update.selectBySql(sql)
def get_businesses_by_roles(self, roles): ''' 获取所有业务 ''' roles = [role['role_code'] for role in roles] ls = "','" roles_str = "'" + (ls.join(roles)) + "'" sql = ''' select business_code from business where business_code in (select business_code from role_business where role_code in (%s) and status = 0 ) ''' % roles_str return pg_update.selectBySql(sql)
def get_businesses_by_role_code(self, role_code): ''' 获取某角色拥有的所有业务 ''' business_codes = pg_update.select("role_business", where="role_code = '%s'" % role_code) where = '' for business_code in business_codes: where += "or seq_code like '%%%s%%'" % business_code[ 'business_code'] sql = ''' select * from ( select * from business where status = 0 ) b where %s ''' % where[2:] return pg_update.selectBySql(sql)
def get_roles_by_accounts(self, accounts): ''' 通过accounts获取roles ''' accounts_str = '' for account in accounts: accounts_str += "'" + account + "'," if accounts_str == '': return [] sql = ''' select u_r.account,r.role_code,r.role_name from user_role u_r,role r where u_r.account in (%s) and u_r.role_code = r.role_code and u_r.status = 0 ''' % accounts_str[:-1] return pg_update.selectBySql(sql)
def get_user_detail_by_account(self, account): ''' 通过帐号获取用户详细信息 ''' sql = ''' select ui.*,ud.head_file,ud.motto from user_info ui left join ( select * from user_detail where status=0 and account='%s' )ud on ud.account=ui.account where ui.status=0 and ui.account='%s' ''' % (account, account) users = pg_update.selectBySql(sql) if len(users) != 1: raise CooError(text='系统数据异常') roles = UserRole.get_roles_by_account(account) users[0]['roles'] = roles return users[0]
def roles_api_test(self, roles, api): ''' 角色和业务匹配 ''' apis = Api.get_api_by_api(api) if len(api) == 0: return False elif apis[0]['restrict'] == 0: return True if len(roles) == 0: return False business_codes = RoleBusiness.get_businesses_by_roles(roles) sql = ''' select seq_code from business where business_code in (select business_code from business_api where api = '%s') ''' % api business_seq_code = pg_update.selectBySql(sql)[0]['seq_code'] for business_code in business_codes: if business_seq_code.find(business_code['business_code']) > 0: return True return False
def get_api_by_api(self, api): sql = ''' select * from api where api = '%s' ''' % api return pg_update.selectBySql(sql)
def get_users_by_role(self, role_code): sql = ''' select * from user_role where role_code = '%s' ''' % role_code return pg_update.selectBySql(sql)
def get_api_by_api(api): sql = ''' select * from api where api = '%s' ''' % api return pg_update.selectBySql(sql)