AppUserLvlInfo.sql 4.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
  1. #1.用户层级关系表
  2. #创建表
  3. drop table if exists `application`.app_user_lvl_info;
  4. create table `application`.app_user_lvl_info
  5. (
  6. lvl varchar(100) NOT NULL COMMENT '运营id',
  7. lvl_name varchar(100) comment '运营名称',
  8. role_id varchar(100) comment '运营角色id',
  9. role_name varchar(100) comment '运营角色名称',
  10. dep_id varchar(100) comment '运营部门id',
  11. org_code varchar(100) comment '运营组织id',
  12. depart_name varchar(100) comment '运营组织名称',
  13. lvl2 varchar(100) comment '一级经理id',
  14. lvl2_name varchar(100) comment '一级经理名称',
  15. role_id2 varchar(100) comment '一级经理角色id',
  16. role_name2 varchar(100) comment '一级经理角色名称',
  17. lvl3 varchar(100) comment '二级经理id',
  18. lvl3_name varchar(100) comment '二级经理名称',
  19. role_id3 varchar(100) comment '二级经理角色id',
  20. role_name3 varchar(100) comment '二级经理角色名称',
  21. lvl4 varchar(100) comment '三级经理id',
  22. lvl4_name varchar(100) comment '三级经理名称',
  23. type bigint comment '类型',
  24. `data_create_time` timestamp DEFAULT CURRENT_TIMESTAMP,
  25. `data_update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  26. PRIMARY KEY (`lvl`)
  27. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户层级关系表';
  28. #插入数据
  29. replace into `application`.app_user_lvl_info (lvl,lvl_name,role_id,role_name,dep_id,org_code,depart_name,lvl2,lvl2_name,role_id2,role_name2,lvl3,lvl3_name,role_id3,role_name3,lvl4,lvl4_name,type)
  30. select * from
  31. (select a.id lvl,a.realname lvl_name,c.role_id,c.role_name,g.dep_id,g.org_code,g.depart_name,a.leader_id lvl2,b.realname lvl2_name,d.role_id role_id2,d.role_name role_name2,
  32. b.leader_id lvl3, e.realname lvl3_name,f.role_id role_id3,f.role_name role_name3,e.leader_id lvl4 ,v.realname lvl4_name, case when d.role_id in ('12b1f86a4d4adda3e5ad1739685c74ff','6a0429d0a879c8e884bcb1a169d66678','8e3b10a1e82ef0f742fa219839a8abdc') then 1
  33. when d.role_id='8d3e2ed58bdedf17716f698ea674da27' then 2 else 0 end type
  34. from `jeecg-boot`.sys_user a
  35. left join `jeecg-boot`.sys_user b
  36. on a.leader_id=b.id
  37. left join
  38. (select a.*,b.role_name from `jeecg-boot`.sys_user_role a
  39. left join (select * from `jeecg-boot`.sys_role) b
  40. on a.role_id=b.id) c
  41. on a.id=c.user_id
  42. left join
  43. (select a.*,b.role_name from `jeecg-boot`.sys_user_role a
  44. left join (select * from `jeecg-boot`.sys_role) b
  45. on a.role_id=b.id) d
  46. on a.leader_id=d.user_id
  47. left join `jeecg-boot`.sys_user e
  48. on b.leader_id=e.id
  49. left join `jeecg-boot`.sys_user v
  50. on e.leader_id=v.id
  51. left join
  52. (select a.*,b.role_name from `jeecg-boot`.sys_user_role a
  53. left join (select * from `jeecg-boot`.sys_role) b
  54. on a.role_id=b.id) f
  55. on b.leader_id=f.user_id
  56. left join
  57. (select m.user_id,m.dep_id,n.org_code,n.depart_name from `jeecg-boot`.sys_user_depart m
  58. left join `jeecg-boot`.sys_depart n
  59. on m.dep_id=n.id)g
  60. on a.id=g.user_id
  61. where a.status=1 ) v
  62. #2.用户与经理关系表
  63. #创建表
  64. drop table if exists `application`.app_user_manage;
  65. create table `application`.app_user_manage
  66. (
  67. user_id varchar(100) NOT NULL COMMENT '用户id',
  68. user_name varchar(200) comment '用户名称',
  69. manage_id varchar(100) NOT NULL comment '经理id',
  70. manage_name varchar(100) NOT NULL comment '经理名称',
  71. type bigint comment '类型,1:运营;2:设计;0:其它',
  72. flag bigint comment '级别',
  73. `data_create_time` timestamp DEFAULT CURRENT_TIMESTAMP,
  74. `data_update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  75. PRIMARY KEY (`user_id`,`manage_id`)
  76. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户与经理关系表';
  77. #插入数据
  78. replace into `application`.app_user_manage (user_id,user_name,manage_id,manage_name,type,flag)
  79. select * from
  80. (select distinct lvl user_id ,lvl_name user_name,ifnull(lvl4,'-') manage_id,ifnull(lvl4_name,'-') manage_name,type,3 flag from `application`.app_user_lvl_info
  81. union all
  82. select distinct lvl user_id ,lvl_name user_name,ifnull(lvl3,'-') manage_id,ifnull(lvl3_name,'-') manage_name,type,2 flag from `application`.app_user_lvl_info
  83. union all
  84. select distinct lvl user_id ,lvl_name user_name,ifnull(lvl2,'-') manage_id,ifnull(lvl2_name,'-') manage_name,type,1 flag from `application`.app_user_lvl_info )m