select
distinct
res_users.login 帐号,
case when res_users.active=True then '有效' else '无效' end 帐号状态,
uar.employee_code 工号,
emp.name 姓名,
bu_trans.value 所属业务单元,
dep_trans.value 部门,
position_trans.value 岗位,
uar.authorization_group_code 权限组编码,
uag.name 权限组名称,
role.code 角色组编码,
role.name 角色组名称,
case goal_crowd.goal_crowd_type when 1 then '所有人'
when 2 then goal_auth_group.authorization_group_name
else goal_crowd.rule_define_name end 目标人群,
award.status 状态
from uc_authorization_group_user_relation uar
left join res_users
on uar.customer_user_id=res_users.id
left join hr_employee emp
on emp.id=uar.employee_id
left join uc_authorization_group uag
on uar.authorization_group_id=uag.id
left join uc_role_award_authorization_group uraag
on uraag.authorization_group_code=uar.authorization_group_code
left join uc_role_award award
on award.id=uraag.role_award_id
LEFT JOIN uc_role AS role ON award.role_code=role.code
LEFT JOIN uc_role_award_goal_crowd AS goal_crowd ON award.id=goal_crowd.role_award_id
LEFT JOIN uc_role_award_goal_crowd_authorization_group AS goal_auth_group on award.id=goal_auth_group.role_award_id
left join emp_job on emp.job_info_id=emp_job.id
LEFT JOIN hr_business_unit AS bu ON
emp_job.business_unit_id = bu.eroad_index_id AND
bu.eroad_index_id IS NOT NULL AND
bu.eroad_start_date <= current_date AND
(bu.eroad_end_date >= current_date OR bu.eroad_end_date IS NULL) AND
bu.active = TRUE
left join ir_translation bu_trans on bu_trans.res_id=bu.id
and bu_trans.name ='hr.business.unit,name'
and bu_trans.lang='zh_CN'
LEFT JOIN hr_department AS dep ON
emp_job.department_id = dep.eroad_index_id AND
dep.eroad_index_id IS NOT NULL AND
dep.eroad_start_date <= current_date AND
(dep.eroad_end_date >= current_date OR dep.eroad_end_date IS NULL) AND
dep.active = TRUE
left join ir_translation dep_trans on dep_trans.res_id=dep.id
and dep_trans.name ='hr.department,name'
and dep_trans.lang='zh_CN'
LEFT JOIN hr_position AS position ON
emp_job.position_id = position.eroad_index_id AND
position.eroad_index_id IS NOT NULL AND
position.eroad_start_date <= current_date AND
(position.eroad_end_date >= current_date OR position.eroad_end_date IS NULL) AND
position.active = TRUE
left join ir_translation position_trans on position_trans.res_id=position.id
and position_trans.name ='hr.position,name'
and position_trans.lang='zh_CN'
where award.is_delete=0
Change
Medium
Page 正在加载
杨光磊
黄诗怡
