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 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_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_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_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(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_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_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 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 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)
def get_users_by_role(self, role_code):
sql = '''
select * from user_role where role_code = '%s'
''' % role_code
return pg_update.selectBySql(sql)
