2021-09-29

1.**表pamirs_major.data_audit_operation_log 用户操作记录的全量导出

select
  *
from
  pamirs_major.data_audit_operation_log where is_deleted = 0

2.2.DMS用户&角色：DMS角色名称，用户姓名，手机号

SELECT
  r.name as 角 色 名 称,
  u.name as 用 户 名 称,
  u.phone 手 机 号
from
  pamirs_major.user_pamirs_user u
  left join pamirs_major.auth_user_role_rel rel ON u.id = rel.user_id
  left join pamirs_major.auth_auth_role r on r.id = rel.role_id
where
  u.is_deleted = 0
  and rel.is_deleted = 0

3.DMS角色导出：DMS角色名称，已配置的权限（需要具体到操作层级）

select
  user_rel_permission.r_name as 角色名称,
  user_rel_permission.p_name as 权限名称,
  module.display_name as 按钮所属模块,
  menu.display_name as 按钮归属菜单,
  action_permission.permission_mate_data_type as 按钮类型,
  action_permission.action as 按钮技术名称,
  action_permission.action_display_name as 按钮展示名称,
  action_permission.description as 按钮备注,
  action_permission.model as 按钮归属模型编码,
  action_permission.model_display_name as 按钮归属模型名称
from
  (
    select
      r.name as r_name,
      p.id as pid,
      p.name as p_name
    from
      pamirs_major.auth_auth_role r
      left join pamirs_major.auth_auth_group_role_rel glr on r.id = glr.auth_role_id
      left join pamirs_major.auth_auth_group g on g.id = glr.auth_group_id
      left join pamirs_major.auth_auth_group_permission_rel glp on g.id = glp.auth_group_id
      left join pamirs_major.auth_resource_permission p on p.id = glp.resource_permission_id
    where
      r.is_deleted = 0
      and glr.is_deleted = 0
      and g.is_deleted = 0
      and glp.is_deleted = 0
      and p.is_deleted = 0
  ) as user_rel_permission
  left join (
    select
      r.id,
      r.model,
      r.permission_mate_data_type,
      m.display_name as model_display_name,
      m.module,
      r.action,
      a.display_name as action_display_name,
      a.description
    from
      pamirs_major.auth_resource_permission r
      left join pamirs_base.base_view_action a on r.model = a.model
      and r.action = a.name
      left join pamirs_base.base_model m on r.model = m.model
    where
      r.permission_mate_data_type = 'VIEW_ACTION'
      and r.is_deleted = 0
      and a.is_deleted = 0
      and m.is_deleted = 0
    union all
    select
      r.id,
      r.model,
      r.permission_mate_data_type,
      m.display_name as model_display_name,
      m.module,
      r.action,
      a.display_name as action_display_name,
      a.description
    from
      pamirs_major.auth_resource_permission r
      left join pamirs_base.base_server_action a on r.model = a.model
      and r.action = a.name
      left join pamirs_base.base_model m on r.model = m.model
    where
      r.permission_mate_data_type = 'SERVER_ACTION'
      and r.is_deleted = 0
      and a.is_deleted = 0
      and m.is_deleted = 0
  )  action_permission on user_rel_permission.pid = action_permission.id
  left join pamirs_base.base_module module on module.module = action_permission.module
  left join (
    select
      *
    from
      pamirs_base.base_menu
    where
      is_deleted = 0
    group by
      model
  )  menu on menu.model = action_permission.model
where
  action_permission.id is not null;

4.DMS数据权限：员工名称，手机号，可查看的客户编码

select
  user_rel_permission.p_name as 权限名称,
  user_rel_permission.e_name as 员工名称,
  user_rel_permission.company_code as 客户编码,
  user_rel_permission.phone as 手机号,
  module.display_name as 按钮所属模块,
  menu.display_name as 按钮归属菜单,
  action_permission.permission_mate_data_type as 按钮类型,
  action_permission.action as 按钮技术名称,
  action_permission.action_display_name as 按钮展示名称,
  action_permission.description as 按钮备注,
  action_permission.model as 按钮归属模型编码,
  action_permission.model_display_name as 按钮归属模型名称
from
  (
    select
      r.name as r_name,
      p.id as pid,
      p.name as p_name,
      e.name as e_name,
      e.company_code ,
      u.phone 
    from
    pamirs_major.auth_auth_role r
  left join auth_employee_rel_role elr on r.id = elr.auth_role_id
  left join business_pamirs_employee e on e.id= elr.employee_id 
  left join pamirs_major.auth_auth_group_role_rel glr on r.id = glr.auth_role_id
  left join pamirs_major.auth_auth_group g on g.id = glr.auth_group_id
  left join pamirs_major.auth_auth_group_permission_rel glp on g.id = glp.auth_group_id
  left join pamirs_major.auth_resource_permission p on p.id = glp.resource_permission_id
  left join pamirs_major.user_pamirs_user u on u.id = e.binding_user_id
    where
      r.is_deleted = 0
      and glr.is_deleted = 0
      and g.is_deleted = 0
      and glp.is_deleted = 0
      and p.is_deleted = 0
  ) as user_rel_permission
  left join (
    select
      r.id,
      r.model,
      r.permission_mate_data_type,
      m.display_name as model_display_name,
      m.module,
      r.action,
      a.display_name as action_display_name,
      a.description
    from
      pamirs_major.auth_resource_permission r
      left join pamirs_base.base_view_action a on r.model = a.model
      and r.action = a.name
      left join pamirs_base.base_model m on r.model = m.model
    where
      r.permission_mate_data_type = 'VIEW_ACTION'
      and r.is_deleted = 0
      and a.is_deleted = 0
      and m.is_deleted = 0
    union all
    select
      r.id,
      r.model,
      r.permission_mate_data_type,
      m.display_name as model_display_name,
      m.module,
      r.action,
      a.display_name as action_display_name,
      a.description
    from
      pamirs_major.auth_resource_permission r
      left join pamirs_base.base_server_action a on r.model = a.model
      and r.action = a.name
      left join pamirs_base.base_model m on r.model = m.model
    where
      r.permission_mate_data_type = 'SERVER_ACTION'
      and r.is_deleted = 0
      and a.is_deleted = 0
      and m.is_deleted = 0
  )  action_permission on user_rel_permission.pid = action_permission.id
  left join pamirs_base.base_module module on module.module = action_permission.module
  left join (
    select
      *
    from
      pamirs_base.base_menu
    where
      is_deleted = 0
    group by
      model
  )  menu on menu.model = action_permission.model
where
  action_permission.id is not null;