select * from fwa_user_info; --用户
select * from fwa_role_info a; --角色
select * from fwa_menu_info a; --菜单
select * from fwa_role_user; --用户和角色关联
select * from fwa_role_menu; --角色和菜单关联
/**
系统用户表和角色表根据用户和角色关联表id_role关联，一个用户对应多个角色。
角色表和菜单表根据角色菜单表id_role字段管理，多对多的关系。
*/
-----添加菜单：
--user 1 : n role 1 : n menu
select * from fwa_user_info; --用户
select * from fwa_role_info a; --角色
select * from fwa_menu_info a; --菜单
select * from fwa_role_user; --用户和角色关联
select * from fwa_role_menu; --角色和菜单关联
--角色
WARN_APPROVER_ROLE 预警审批岗
WARN_AGENT_ROLE 预警分析岗
WARN_AUDIT_ROLE 预警复核岗
WARN_MANAGE_ROLE 预警管理岗
SETTLEMENT_MANAGE_ROLE 结算管理岗
SETTLEMENT_ANALYSIS_ROLE 结算分析岗
--监控分析 1
--监控分析报表 2
--病程情况汇总 3 路由、css名字
--菜单的层级关系
select * from fwa_menu_info a where a.parent_menu = '0' for update;--id xxx 1a2b3c
select * from fwa_menu_info a where a.parent_menu = '1a2b3c' for update;--菜单图表需要顾彦提供 id yyy 1a2b3c4d
select * from fwa_menu_info a where a.parent_menu = '1a2b3c4d' for update;-- 1a2b3c4d5e
select * from fwa_user_info f where f.id_user = 'FE1EFDD57BA71190E043171D190A965E'
--用户和角色关联
select distinct *
from fwa_user_info f, fwa_role_info a, fwa_role_user u
where f.id_user = u.id_user
and a.id_role = u.id_role
and f.id_user = 'FE1EFDD57BA71190E043171D190A965E'
--角色和菜单关联
select * from fwa_role_info r where r.id_role='FDE749B8C4867F54E043171D190A91A8';
select * from fwa_menu_info m;
select * from fwa_role_menu rm where rm.id_role='FDE749B8C4867F54E043171D190A91A8';
select * from fwa_menu_info m where m.parent_menu='1F47F2360464B146E0531580140A2F39';
select * from fwa_menu_info m where m.parent_menu='0';
----插入监控分析 1
insert into fwa_menu_info
(ID_MENU,
MENU_NAME,
MENU_HREF,
MENU_CLASS,
MENU_DESC,
PARENT_MENU,
MENU_ORDER,
MENU_STATUS,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE,
MENU_STATENAME,
USE_BASIC_DATA)
values
(sys_guid(),
'监控分析1',
'',
'jkfx1',
'监控分析1',
'0',
'1037',
'Y',
'FWADATA',
to_date('01-11-2015 15:56:18', 'dd-mm-yyyy hh24:mi:ss'),
'FWADATA',
to_date('02-11-2015 15:56:18', 'dd-mm-yyyy hh24:mi:ss'),
'',
null);
-----插入监控分析报表 2
insert into fwa_menu_info
(ID_MENU,
MENU_NAME,
MENU_HREF,
MENU_CLASS,
MENU_DESC,
PARENT_MENU,
MENU_ORDER,
MENU_STATUS,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE,
MENU_STATENAME,
USE_BASIC_DATA)
values
(sys_guid(),
'监控分析报表2',
'',
'jkfxbb',
'监控分析报表2',
'238630FAEF6B9483E0531480140A2F4E',
'1037',
'Y',
'HCPMSDATA',
to_date('01-11-2015 15:56:18', 'dd-mm-yyyy hh24:mi:ss'),
'FWADATA',
to_date('02-11-2015 15:56:18', 'dd-mm-yyyy hh24:mi:ss'),
'',
null);
-----插入病程情况汇总 3
insert into fwa_menu_info
(ID_MENU,
MENU_NAME,
MENU_HREF,
MENU_CLASS,
MENU_DESC,
PARENT_MENU,
MENU_ORDER,
MENU_STATUS,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE,
MENU_STATENAME,
USE_BASIC_DATA)
values
(sys_guid(),
'病程情况汇总3',
'',
'bcqkhz',
'病程情况汇总3',
'238630FAEF6C9483E0531480140A2F4E',
'1037',
'Y',
'HCPMSDATA',
to_date('01-11-2015 15:56:18', 'dd-mm-yyyy hh24:mi:ss'),
'FWADATA',
to_date('02-11-2015 15:56:18', 'dd-mm-yyyy hh24:mi:ss'),
'',
null);
--------插入管理关系
insert into fwa_role_menu
(ID_RELATIONSHIP,
ID_ROLE,
ID_MENU,
RELATIONSHIP_STATUS,
CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE)
values
(sys_guid(),
'FDE749B8C48D7F54E043171D130A91A8',
'238630FAEF6C9483E0531480140A2F4E',
'Y',
'FWADATA',
to_date('01-11-2015 11:15:30', 'dd-mm-yyyy hh24:mi:ss'),
'FWADATA',
to_date('02-11-2015 11:15:30', 'dd-mm-yyyy hh24:mi:ss'));
--------创建关联关系
WARN_APPROVER_ROLE 预警审批岗 FDE749B8C48D7F54E043171D130A91A8
WARN_AGENT_ROLE 预警分析岗 FDE749B8C48D7F54E043171D110A91A8
WARN_AUDIT_ROLE 预警复核岗 FDE749B8C48D7F54E043171D120A91A8
WARN_MANAGE_ROLE 预警管理岗 2001BE9552EE611AE0531480140A73EE
select * from fwa_role_info a where a.role_name ='WARN_MANAGE_ROLE'; --角色
SETTLEMENT_MANAGE_ROLE 结算管理岗 080B04B06C3408C8E0531580140AE008
SETTLEMENT_ANALYSIS_ROLE 结算分析岗 080B04B06C3308C8E0531580140AE008
-------一级菜单监控分析
select * from fwa_menu_info m where m.parent_menu='0'
select * from fwa_menu_info m where m.parent_menu='238630FAEF6B9483E0531480140A2F4E'
select * from fwa_menu_info m where m.parent_menu='238630FAEF6C9483E0531480140A2F4E'
select * from fwa_menu_info m where m.id_menu='238630FAEF6D9483E0531480140A2F4E'
select * from fwa_role_menu rm where rm.id_menu='238630FAEF6B9483E0531480140A2F4E'
--------------
----一级菜单诚信管理
select * from fwa_menu_info m where m.parent_menu='1F47F2360464B146E0531580140A2F39'
----二级菜单医务人员管理
select * from fwa_menu_info m where m.parent_menu='1F5CAE6290109293E0531580140A02E0'
----三级菜单医生诚信登记
select * from fwa_menu_info m where m.id_menu='0EB4463D709AB733E0531580140A9FEE'
select * from fwa_role_menu rm where rm.id_menu='1F47F2360464B146E0531580140A2F39'
----------------
select * from fwa_role_info a where a.id_role='FDE749B8C4867F54E043171D190A91A8'; --角色
select to_date(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss') from dual;
select * from fwa_role_menu
select * from fwa_menu_info m where m.parent_menu='0'
delete from fwa_role_menu rm where rm.id_menu='238630FAEF6D9483E0531480140A2F4E'