async def edit_group(group_id: int,
item_in: ItemInEditGroup,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改用户组\n
:param group_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_EDIT)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查找用户组
group_sql = t_group.select().where(
t_group.c.id == group_id).limit(1).with_for_update()
group_obj = conn.execute(group_sql).fetchone()
if not group_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND,
detail={
'code': HTTP_404_NOT_FOUND,
'msg': 'group not exists'
})
# 修改用户组
group_val = {'editor': userinfo['name']}
if item_in.pid:
group_val['pid'] = item_in.pid
if item_in.name:
group_val['name'] = item_in.name
if item_in.intro:
group_val['intro'] = item_in.intro
update_group_sql = t_group.update().where(
t_group.c.id == group_id).values(group_val)
conn.execute(update_group_sql)
if item_in.role_id:
# 指定了角色，绑定用户组 - 角色关系
tool.bind_group_role(group_id, item_in.role_id, userinfo, conn)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def add_permission(item_in: ItemInAddPermission, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加权限\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_PERMISSION_ADD)
conn = db_engine.connect()
try:
# 查看是否已经有该code的权限
if not tool.is_code_unique(t_permission, item_in.code, conn):
raise MyException(status_code=HTTP_400_BAD_REQUEST, detail={'code': MULTI_DATA, 'msg': 'code repeat'})
# 新增权限
permission_sql = t_permission.insert().values({
'pid': item_in.pid,
'name': item_in.name,
'code': item_in.code,
'intro': item_in.intro,
'category': item_in.category,
'creator': userinfo['name']
})
conn.execute(permission_sql)
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except Exception as ex:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg=str(ex)))
finally:
conn.close()
async def del_user(permission_id: int, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
删除权限\n
:param permission_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_PERMISSION_DEL)
conn = db_engine.connect()
try:
# 查找权限
permission_sql = t_permission.select().where(t_permission.c.id == permission_id).limit(1).with_for_update()
conn.execute(permission_sql).fetchone()
# 修改权限状态为无效（软删除）
update_permission_sql = t_permission.update().where(and_(
t_permission.c.id == permission_id,
t_permission.c.sub_status != TABLE_SUB_STATUS_INVALID_DEL,
)).values({
'status': TABLE_STATUS_INVALID,
'sub_status': TABLE_SUB_STATUS_INVALID_DEL
})
conn.execute(update_permission_sql)
return ItemOutOperateSuccess()
except:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
async def edit_menu(menu_id: int, item_in: ItemInEditMenu, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改菜单\n
:param menu_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_MENU_EDIT)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查找菜单
menu_sql = t_menu.select().where(t_menu.c.id == menu_id).limit(1).with_for_update()
menu_obj = conn.execute(menu_sql).fetchone()
if not menu_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND, detail={'code': HTTP_404_NOT_FOUND, 'msg': 'menu not exists'})
# 修改菜单
# 过滤掉空字段
item_dict = dict((k, v) for k, v in item_in.dict().items() if v is not None)
item_dict['editor'] = userinfo['name']
update_menu_sql = t_menu.update().where(t_menu.c.id == menu_id).values(item_dict)
conn.execute(update_menu_sql)
permission_dict = {}
if item_in.name and menu_obj.name != item_in.name:
permission_dict['name'] = '{}菜单访问'.format(item_in.name)
permission_dict['intro'] = '[{}菜单访问]权限'.format(item_in.name)
if item_in.code and menu_obj.code != item_in.code:
permission_dict['code'] = 'PERMISSION_{}_QUERY'.format(item_in.code)
if permission_dict:
# 因修改菜单而导致权限名称无法人眼识别，故同步修改权限
# 从菜单-权限绑定关系中，获取权限信息
menu_permission_sql = t_menu_permission.select().where(t_menu_permission.c.menu_id == menu_id).limit(1)
menu_permission_obj = conn.execute(menu_permission_sql).fetchone()
permission_dict['editor'] = userinfo['name']
permission_sql = t_permission.update().where(t_permission.c.id == menu_permission_obj.permission_id).values(permission_dict)
conn.execute(permission_sql)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
async def add_menu(item_in: ItemInAddMenu, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加菜单\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_MENU_ADD)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查看是否已经有该code的用户组
if not tool.is_code_unique(t_menu, item_in.code, conn):
raise MyException(status_code=HTTP_400_BAD_REQUEST, detail={'code': MULTI_DATA, 'msg': 'code repeat'})
# 新增菜单
menu_val = {
'creator': userinfo['name']
}
menu_val.update(item_in.dict())
menu_sql = t_menu.insert().values(menu_val)
menu_res = conn.execute(menu_sql)
# 新增该菜单的可见权限
permission_sql = t_permission.insert().values({
'pid': 0,
'code': 'PERMISSION_{}_QUERY'.format(item_in.code),
'name': '{}菜单访问'.format(item_in.name),
'intro': '[{}菜单访问]权限'.format(item_in.name),
'category': 1,
'creator': userinfo['name']
})
permission_res = conn.execute(permission_sql)
# 绑定菜单与可见权限关系
menu_permission_sql = t_menu_permission.insert().values({
'menu_id': menu_res.lastrowid,
'permission_id': permission_res.lastrowid,
'creator': userinfo['name']
})
conn.execute(menu_permission_sql)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
async def add_group(item_in: ItemInAddGroup,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加用户组\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_ADD)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查看是否已经有该code的用户组
if not tool.is_code_unique(t_group, item_in.code, conn):
raise MyException(status_code=HTTP_400_BAD_REQUEST,
detail={
'code': MULTI_DATA,
'msg': 'code repeat'
})
# 新增用户组
print('insert group start')
group_sql = t_group.insert().values({
'pid': item_in.pid,
'name': item_in.name,
'code': item_in.code,
'intro': item_in.intro,
'creator': userinfo['name']
})
group_res = conn.execute(group_sql)
if item_in.role_id:
# 指定了角色，绑定用户组 - 角色关系
tool.bind_group_role(group_res.lastrowid, item_in.role_id,
userinfo, conn)
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
trans.rollback()
raise mex
except Exception as ex:
trans.rollback()
raise MyException(
status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR,
msg=str(ex)))
finally:
conn.close()
async def edit_role(role_id: int,
item_in: ItemInEditRole,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改角色\n
:param role_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_ROLE_EDIT)
conn = db_engine.connect()
try:
# 查找角色
role_sql = t_role.select().where(
t_role.c.id == role_id).limit(1).with_for_update()
role_obj = conn.execute(role_sql).fetchone()
if not role_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND,
detail={
'code': HTTP_404_NOT_FOUND,
'msg': 'role not exists'
})
# 修改角色
data = {'editor': userinfo['name']}
if item_in.pid:
data['pid'] = item_in.pid
if item_in.name:
data['name'] = item_in.name
if item_in.intro:
data['intro'] = item_in.intro
update_role_sql = t_role.update().where(
t_role.c.id == role_id).values(data)
conn.execute(update_role_sql)
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def disable_group(group_id: int,
userinfo: dict = Depends(
tool.get_userinfo_from_token)):
"""
禁用用户组\n
:param group_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_DISABLE)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查找用户组
group_sql = t_group.select().where(
t_group.c.id == group_id).limit(1).with_for_update()
conn.execute(group_sql).fetchone()
# 修改用户组状态为禁用
update_group_sql = t_group.update().where(
and_(
t_group.c.id == group_id,
t_group.c.status == TABLE_STATUS_VALID,
t_group.c.sub_status == TABLE_SUB_STATUS_VALID,
)).values({
'status': TABLE_STATUS_INVALID,
'sub_status': TABLE_SUB_STATUS_INVALID_DISABLE
})
conn.execute(update_group_sql)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def get_groups(userinfo: dict = Depends(tool.get_userinfo_from_token),
p: Optional[int] = Query(settings.web.page,
description='第几页'),
ps: Optional[int] = Query(settings.web.page_size,
description='每页条数')):
item_out = ItemOutGroupList()
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_VIEW)
with db_engine.connect() as conn:
# 获取当前有多少数据
count_sql = select([func.count(t_group.c.id)])
total = conn.execute(count_sql).scalar()
# 获取分页后的用户组列表
group_sql = select([
t_group.c.id,
t_group.c.pid,
t_group.c.name,
t_group.c.code,
t_group.c.intro,
t_group.c.status,
t_group.c.sub_status,
]).order_by('sort', 'id').limit(ps).offset((p - 1) * ps)
group_obj_list = conn.execute(group_sql).fetchall()
item_out.data = ListDataGroup(
result=[
ItemOutGroup(
id=group_obj.id,
name=group_obj.name,
code=group_obj.code,
intro=group_obj.intro,
status=group_obj.status,
sub_status=group_obj.sub_status,
) for group_obj in group_obj_list
],
total=total,
p=p,
ps=ps,
)
return item_out
async def enable_menu(menu_id: int, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
启用菜单\n
:param menu_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_MENU_ENABLE)
conn = db_engine.connect()
trans = conn.begin()
try:
# 1.查找菜单
menu_sql = t_menu.select().where(t_menu.c.id == menu_id).limit(1).with_for_update()
menu_obj = conn.execute(menu_sql).fetchone()
if not menu_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND, detail={'code': HTTP_404_NOT_FOUND, 'msg': 'menu not exists'})
# 2.修改菜单状态为启用
update_menu_sql = t_menu.update().where(and_(
t_menu.c.id == menu_id,
t_menu.c.status == TABLE_STATUS_INVALID,
t_menu.c.sub_status == TABLE_SUB_STATUS_INVALID_DISABLE,
)).values({
'status': TABLE_STATUS_VALID,
'sub_status': TABLE_SUB_STATUS_VALID
})
conn.execute(update_menu_sql)
# 3.提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
async def bind_role_permission(role_id: int,
item_in: List[int],
userinfo: dict = Depends(
tool.get_userinfo_from_token)):
"""
绑定角色-权限\n
:param role_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
conn = db_engine.connect()
trans = conn.begin()
try:
# 鉴权
tool.check_operation_permission(userinfo['id'],
PERMISSION_ROLE_PERMISSION_BIND)
# 查询角色是否存在
role = tool.get_role(role_id, conn)
if not role.is_super:
# 不是超级管理员，绑定权限
for permission_id in item_in:
# 绑定角色权限
tool.bind_role_permission(role_id, permission_id, userinfo,
conn)
trans.commit()
else:
trans.rollback()
return ItemOutOperateSuccess()
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def enable_role(role_id: int,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
启用角色\n
:param role_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_ROLE_ENABLE)
conn = db_engine.connect()
try:
# 查找角色
role_sql = t_role.select().where(
t_role.c.id == role_id).limit(1).with_for_update()
conn.execute(role_sql).fetchone()
# 修改角色状态为启用
update_role_sql = t_role.update().where(
and_(
t_role.c.id == role_id,
t_role.c.status == TABLE_STATUS_INVALID,
t_role.c.sub_status == TABLE_SUB_STATUS_INVALID_DISABLE,
)).values({
'status': TABLE_STATUS_VALID,
'sub_status': TABLE_SUB_STATUS_VALID
})
conn.execute(update_role_sql)
return ItemOutOperateSuccess()
except:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def get_users(userinfo: dict = Depends(tool.get_userinfo_from_token),
p: Optional[int] = Query(settings.web.page,
description='第几页'),
ps: Optional[int] = Query(settings.web.page_size,
description='每页条数'),
name: Optional[str] = Query(None, description='用户名'),
mobile: Optional[str] = Query(None,
description='用户手机号',
regex=REGEX_MOBILE)):
item_out = ItemOutUserList()
# 检查权限
tool.check_operation_permission(userinfo['id'], PERMISSION_USER_VIEW)
with db_engine.connect() as conn:
# 获取当前有多少数据
count_sql = select([func.count(t_user.c.id)])
# 获取分页后的用户列表
user_sql = select([
t_user.c.id,
t_user.c.name,
t_user.c.head_img_url,
t_user.c.mobile,
t_user.c.status,
t_user.c.sub_status,
])
if name is not None:
# 用户名过滤
name = name.strip()
count_sql = count_sql.where(t_user.c.name.like(
'%{}%'.format(name)))
user_sql = user_sql.where(t_user.c.name.like('%{}%'.format(name)))
if mobile is not None:
# 用户手机号过滤
mobile = mobile.strip()
count_sql = count_sql.where(
t_user.c.mobile.like('%{}%'.format(mobile)))
user_sql = user_sql.where(
t_user.c.mobile.like('%{}%'.format(mobile)))
total = conn.execute(count_sql).scalar()
user_sql = user_sql.order_by('sort', 'id').limit(ps).offset(
(p - 1) * ps)
user_obj_list = conn.execute(user_sql).fetchall()
item_out.data = ListDataUser(
result=[
ItemOutUser(
id=user_obj.id,
name=user_obj.name,
head_img_url=user_obj.head_img_url,
mobile=user_obj.mobile,
status=user_obj.status,
sub_status=user_obj.sub_status,
) for user_obj in user_obj_list
],
total=total,
p=p,
ps=ps,
)
return item_out
async def edit_user(user_id: int,
item_in: ItemInEditUser,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改用户\n
:param user_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_USER_EDIT)
conn = db_engine.connect()
trans = conn.begin()
try:
# 1.查找用户
user_sql = t_user.select().where(
t_user.c.id == user_id).limit(1).with_for_update()
user_obj = conn.execute(user_sql).fetchone()
if not user_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND,
detail={
'code': HTTP_404_NOT_FOUND,
'msg': 'user not exists'
})
# 2.修改用户
user_val = {'editor': userinfo['name']}
if item_in.name:
user_val['name'] = item_in.name
if item_in.head_img_url:
user_val['head_img_url'] = item_in.head_img_url
if item_in.mobile:
user_val['mobile'] = item_in.mobile
if item_in.email:
user_val['email'] = item_in.email
if item_in.password:
user_val['password'] = item_in.password
update_user_sql = t_user.update().where(
t_user.c.id == user_id).values(user_val)
conn.execute(update_user_sql)
# 3.修改账号
# 3.1 获取账号
account_sql = t_account.select().where(
t_account.c.user_id == user_obj.id).order_by(
'sort', 'id').with_for_update()
account_res = conn.execute(account_sql).fetchall()
# 3.2 遍历账号，并修改
for account in account_res:
if account.category == TABLE_ACCOUNT_CATEGORY_CUSTOM and item_in.name and account.open_code != item_in.name:
# 账号类型为自定义，并且修改了用户名
tmp_account_update_sql = t_account.update().where(
t_account.c.id == account.id).values({
'open_code':
item_in.name,
'editor':
userinfo['name']
})
conn.execute(tmp_account_update_sql)
elif account.category == TABLE_ACCOUNT_CATEGORY_PHONE and item_in.mobile and account.open_code != item_in.mobile:
# 账号类型为手机号，并且用户修改了手机号
tmp_account_update_sql = t_account.update().where(
t_account.c.id == account.id).values({
'open_code':
item_in.mobile,
'editor':
userinfo['name']
})
conn.execute(tmp_account_update_sql)
elif account.category == TABLE_ACCOUNT_CATEGORY_EMAIL and item_in.email and account.open_code != item_in.email:
# 账号类型为手机号，并且用户修改了手机号
tmp_account_update_sql = t_account.update().where(
t_account.c.id == account.id).values({
'open_code':
item_in.mobile,
'editor':
userinfo['name']
})
conn.execute(tmp_account_update_sql)
if item_in.role_id:
# 4.指定了角色，绑定用户角色关系
tool.bind_user_role(user_id, item_in.role_id, userinfo, conn)
if item_in.group_id:
# 5.指定了组，绑定用户与组关系
tool.bind_user_group(user_id, item_in.group_id, userinfo, conn)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def add_user(item_in: ItemInAddUser,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加用户\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_USER_ADD)
conn = db_engine.connect()
trans = conn.begin()
try:
# 1.新增用户
# 1.1 设置用户盐值
user_salt = tool.get_rand_str(6)
# 1.2 执行新增
user_val = {
'name': item_in.name,
'head_img_url': item_in.head_img_url,
'mobile': item_in.mobile,
'email': item_in.email,
'salt': user_salt,
'password': md5(item_in.password, user_salt),
'creator': userinfo['name']
}
user_sql = t_user.insert().values(user_val)
user_res = conn.execute(user_sql)
# 2.新增账号
# 添加一个用户名的登录账号
account_sql = t_account.insert().values({
'user_id': user_res.lastrowid,
'open_code': item_in.name,
'category': TABLE_ACCOUNT_CATEGORY_CUSTOM,
'creator': userinfo['name']
})
conn.execute(account_sql)
if item_in.email:
# 填写了邮箱，添加一个邮箱的登录账号
account_sql = t_account.insert().values({
'user_id': user_res.lastrowid,
'open_code': item_in.email,
'category': TABLE_ACCOUNT_CATEGORY_EMAIL,
'creator': userinfo['name']
})
conn.execute(account_sql)
if item_in.mobile:
# 填写了手机号，添加一个手机号的登录账号
account_sql = t_account.insert().values({
'user_id': user_res.lastrowid,
'open_code': item_in.mobile,
'category': TABLE_ACCOUNT_CATEGORY_PHONE,
'creator': userinfo['name']
})
conn.execute(account_sql)
if item_in.role_id:
# 3.指定了角色，绑定用户角色关系
tool.bind_user_role(user_res.lastrowid, item_in.role_id, userinfo,
conn)
if item_in.group_id:
# 4.指定了组，绑定用户与组关系
tool.bind_user_group(user_res.lastrowid, item_in.group_id,
userinfo, conn)
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
trans.rollback()
raise mex
except Exception as ex:
trans.rollback()
raise MyException(
status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR,
msg=str(ex)))
finally:
conn.close()
