【EasyUI】根据用户权限显示左侧菜单（一）数据库表设计

CREATE DATABASE EasyUi_RoleManage
GO
USE EasyUi_RoleManage
GO

--权限表
CREATE TABLE tb_role
(
    roleId INT IDENTITY(1,1) PRIMARY KEY , 
    roleName VARCHAR(50) NOT NULL 
)
INSERT INTO tb_role VALUES('超级管理员'), ('中级操作员'), ('普通操作员'), ('游客')

--用户表
CREATE TABLE tb_users
(
    userId INT IDENTITY(1,1) PRIMARY KEY ,
    userName VARCHAR(50) NOT NULL ,
    userPassWord VARCHAR(50) NOT NULL ,
    diasble BIT
)
INSERT INTO tb_users VALUES('admin', '123123', 0), ('张三', '321321', 0), ('李四', '7878234', 0), ('test', 'test', 0)

--用户权限表
CREATE TABLE tb_users_role
(
    Id INT PRIMARY KEY IDENTITY(1,1) ,
    userId INT ,
    roleId INT  
)
INSERT INTO tb_users_role VALUES(1,1), (2,3), (3,2), (4,4)

--菜单表
CREATE TABLE tb_menu
( 
    menuId VARCHAR(100) PRIMARY KEY ,
    menuParId VARCHAR(100) NOT NULL ,
    menuName VARCHAR(50) NOT NULL ,
    menuCode VARCHAR(50) ,
    menuUrl VARCHAR(200) ,
    menuIcon VARCHAR(100) ,
    createTime DATE DEFAULT GETDATE() ,
    createBy VARCHAR(100) ,
    updateTime DATE DEFAULT GETDATE() ,
    updateBy VARCHAR(100)
)
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1', 'root', '全部菜单', NULL, '', 'icon-application_home', 'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1001', '1', '采购管理', NULL, '', 'icon-accept',  'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1002', '1', '销售管理', NULL, '', 'icon-money-yen',  'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1003', '1', '基础资料', NULL, '', 'icon-rainbow', 'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1004', '1', '系统管理', NULL, '', 'icon-cog', 'admin')
 
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1001001', '1001', '采购入库单', 'buy', '/Pages/BuyBill.aspx', 'icon-television', 'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1001002', '1001', '采购退货单', 'buyBack', '','icon-television', 'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1001003', '1001', '采购换货单', 'buyEx', '','icon-television',  'admin')

INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1002001', '1002', '销售出库单', 'sale', '','icon-feed', 'admin') 
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1002002', '1002', '销售退货单', 'saleBack', '/Pages/SaleBackBill.aspx', 'icon-feed',  'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1002003', '1002', '销售换货单', 'saleEx', '','icon-feed', 'admin')

INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1003001', '1003', '商品档案', 'ptype', '/Pages/Ptype.aspx', 'icon-comment',  'admin') 
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1003002', '1003', '存货仓库', 'stock', '','icon-comment', 'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1003003', '1003', '商品单位', 'ptypeUnit', '','icon-comment',  'admin')

INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1004001', '1004', '用户权限', 'users', '','icon-user-home', 'admin') 
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1004002', '1004', '安全设置', 'safe', '','icon-user-home',  'admin')
INSERT INTO tb_menu(menuId, menuParId, menuName, menuCode, menuUrl, menuIcon, createBy) VALUES('1004003', '1004', '单据设置', 'bill', '','icon-user-home',  'admin')


--按钮表
CREATE TABLE tb_button
(
    btnId INT IDENTITY(1,1) PRIMARY KEY , 
    btnName VARCHAR(50) NOT NULL ,
    btnCode VARCHAR(50) ,
    btnIcon VARCHAR(100) ,
    createTime DATE DEFAULT GETDATE() ,
    createBy VARCHAR(100) ,
    updateTime DATE DEFAULT GETDATE() ,
    updateBy VARCHAR(100) 
)
INSERT INTO tb_button(btnName, btnCode, btnIcon, createBy) VALUES('新增', 'add', 'icon-add', 'admin'), ('删除', 'delete', 'icon-delete', 'admin'), ('修改', 'edit', 'icon-edit', 'admin'), ('查询', 'search', 'icon-search', 'admin')



--菜单按钮表
CREATE TABLE tb_menu_button
(
    Id INT PRIMARY KEY IDENTITY(1,1) , 
    menuId VARCHAR(100) ,
    btnId INT 
)
INSERT INTO tb_menu_button VALUES('1001001', 1), ('1001001', 2), ('1001001', 3), ('1001001', 4), ('1001002', 1), ('1001002', 2), ('1001002', 3), ('1001002', 4)
INSERT INTO tb_menu_button VALUES('1003001', 1), ('1003001', 2), ('1003001', 3), ('1003001', 4), ('1003002', 1), ('1003002', 2), ('1003002', 3), ('1003002', 4)
INSERT INTO tb_menu_button VALUES('1003003', 1), ('1003003', 2), ('1003003', 3), ('1003003', 4), ('1002003', 1), ('1002003', 2), ('1002003', 3), ('1002003', 4)
INSERT INTO tb_menu_button VALUES('1002001', 1), ('1002001', 2), ('1002001', 3), ('1002001', 4), ('1002002', 1), ('1002002', 2), ('1002002', 3), ('1002002', 4)

--菜单按钮权限表
CREATE TABLE tb_role_menu_button
(
    Id INT PRIMARY KEY IDENTITY(1,1) ,
    roleId INT ,
    menuId VARCHAR(100) ,
    btnId INT  
)
INSERT INTO tb_role_menu_button VALUES(1, '1',1), (1, '1',2), (1, '1',3), (1, '1',4)
INSERT INTO tb_role_menu_button VALUES(2, '1',4), (2, '1001', 4), (2, '1002', 4), (2, '1003', 4), (2, '1004', 4)
INSERT INTO tb_role_menu_button VALUES(3, '1',4), (3, '1002', 4), (3, '1002001',1), (3, '1002001',2), (3, '1002001',3), (3, '1002001',4)
INSERT INTO tb_role_menu_button VALUES(4, '1001', 4), (4, '1001001',4)
 

INSERT INTO dbo.tb_role_menu_button
        ( roleId, menuId, btnId )
SELECT 1 AS roleId, menuId, 4 AS btnId FROM dbo.tb_menu