CRM 系统的数据库设计、功能需求、前台界面

最新推荐文章于 2024-01-04 12:03:07 发布

纯洁的明依

最新推荐文章于 2024-01-04 12:03:07 发布

阅读量3.7k

点赞数

本文链接：https://blog.csdn.net/qq_32423845/article/details/88395947

版权

欢迎关注博主公众号：【纯洁的明依】文章由陈晓阳原创。
本人微信：chenxiaoyangzxy. 免费提供本人大量学习资料。

-- 删除数据库
DROP DATABASE IF EXISTS crmdb ;
-- 创建数据库
CREATE DATABASE crmdb CHARACTER SET UTF8 ;
-- 使用数据库
USE crmdb ;
-- 创建数据表
-- 1、创建权限组表
CREATE TABLE groups (
gid INT AUTO_INCREMENT,
title VARCHAR(50),
img VARCHAR(50) ,
type VARCHAR(50) ,
CONSTRAINT pk_gid PRIMARY KEY (gid)
);
-- 2、创建权限表
CREATE TABLE action(
actid INT AUTO_INCREMENT,
title VARCHAR(50),
menu INT,
url VARCHAR(200),
CONSTRAINT pk_actid PRIMARY KEY (actid)
);

-- 3、权限组-权限
CREATE TABLE groups_action (
gid INT ,
actid INT ,
CONSTRAINT fk_gid11 FOREIGN KEY(gid) REFERENCES groups(gid) ON DELETE CASCADE ,
CONSTRAINT fk_actid11 FOREIGN KEY(actid) REFERENCES action(actid) ON DELETE CASCADE
) ;
-- 4、创建角色表
CREATE TABLE role (
rid INT AUTO_INCREMENT ,
title VARCHAR(50),
CONSTRAINT pk_rid PRIMARY KEY (rid)
);

-- 5、创建角色-权限组
CREATE TABLE role_groups (
rid INT,
gid INT ,
CONSTRAINT fk_rid4 FOREIGN KEY(rid) REFERENCES role(rid) ON DELETE CASCADE ,
CONSTRAINT fk_gid4 FOREIGN KEY(gid) REFERENCES groups(gid) ON DELETE CASCADE
);

-- 6、创建用户表
CREATE TABLE member (
mid VARCHAR(50) NOT NULL,
rid INT,
password VARCHAR(32),
tel VARCHAR(50),
lastdate DATETIME,
photo VARCHAR(200),
flag INT,
locked INT,
CONSTRAINT pk_mid PRIMARY KEY (MID) ,
CONSTRAINT fk_rid FOREIGN KEY(rid) REFERENCES role(rid) ON DELETE CASCADE
);
-- 7、创建登录日志表
CREATE TABLE logs (
logid INT AUTO_INCREMENT ,
mid VARCHAR(50),
indate DATETIME,
CONSTRAINT pk_logid PRIMARY KEY (logid) ,
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE
);

-- 8、公告信息表
CREATE TABLE news (
nid INT AUTO_INCREMENT ,
mid VARCHAR(50),
title VARCHAR(50),
type INT,
pubdate DATETIME,
note TEXT,
CONSTRAINT pk_nid PRIMARY KEY (nid) ,
CONSTRAINT fk_mid7 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL
);

-- 9、用户公告阅读
CREATE TABLE member_news (
mid VARCHAR(50),
nid INT,
rdate DATETIME ,
CONSTRAINT fk_mid8 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE ,
CONSTRAINT fk_nid8 FOREIGN KEY(nid) REFERENCES news(nid) ON DELETE CASCADE
);

-- 10、客户信息登记表
CREATE TABLE client (
cid INT AUTO_INCREMENT ,
mid VARCHAR(50),
name VARCHAR(50),
sex VARCHAR(10),
email VARCHAR(200),
tel VARCHAR(200),
qq VARCHAR(200),
type INT,
reg DATETIME,
note TEXT,
CONSTRAINT pk_cid PRIMARY KEY (cid) ,
CONSTRAINT fk_mid9 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL
);

-- 11、客户回访记录表
CREATE TABLE task (
tid INT AUTO_INCREMENT ,
mid VARCHAR(50),
cid INT,
title VARCHAR(50),
tdate DATE,
visit INT,
type INT,
note TEXT,
status INT,
level INT,
CONSTRAINT pk_tid PRIMARY KEY (tid) ,
CONSTRAINT fk_mid10 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL ,
CONSTRAINT fk_cid10 FOREIGN KEY(cid) REFERENCES client(cid) ON DELETE CASCADE
);

-- 测试数据
INSERT INTO role(title) VALUES ('系统管理员') ;
INSERT INTO role(title) VALUES ('信息管理员') ;
INSERT INTO role(title) VALUES ('客服人员') ;
-- 权限组信息
INSERT INTO groups(title,img,type) VALUES ('业务信息','leftico01.png' ,'客服人员') ;
INSERT INTO groups(title,img,type) VALUES ('任务管理','leftico02.png' ,'客服人员') ;
INSERT INTO groups(title,img,type) VALUES ('公告管理','leftico03.png' ,'客服人员') ;

INSERT INTO groups(title,img,type) VALUES ('公告管理','leftico03.png' ,'信息管理员') ;
INSERT INTO groups(title,img,type) VALUES ('客户管理','leftico05.png' ,'信息管理员') ;

INSERT INTO groups(title,img,type) VALUES ('公告管理','leftico03.png' ,'系统管理员') ;
INSERT INTO groups(title,img,type) VALUES ('权限管理','leftico04.png' ,'系统管理员') ;
INSERT INTO groups(title,img,type) VALUES ('客户管理','leftico05.png' ,'系统管理员') ;

-- 权限信息
INSERT INTO action(title,menu,url) VALUES ('添加客户',1,'/pages/back/client/ClientServletBack/addPre') ;
INSERT INTO action(title,menu,url) VALUES ('客户列表',1,'/pages/back/client/ClientServletBack/listSplit') ;
INSERT INTO action(title,menu,url) VALUES ('客户修改',0,'/pages/back/client/ClientServletBack/editPre') ;
INSERT INTO action(title,menu,url) VALUES ('客户删除',0,'/pages/back/client/ClientServletBack/rm') ;

INSERT INTO action(title,menu,url) VALUES ('添加任务',1,'/pages/back/task/TaskServletBack/addPre') ;
INSERT INTO action(title,menu,url) VALUES ('任务列表',1,'/pages/back/task/TaskServletBack/listSplit') ;
INSERT INTO action(title,menu,url) VALUES ('任务修改',0,'/pages/back/task/TaskServletBack/editPre') ;
INSERT INTO action(title,menu,url) VALUES ('任务完成',0,'/pages/back/task/TaskServletBack/finish') ;
INSERT INTO action(title,menu,url) VALUES ('任务关闭',0,'/pages/back/task/TaskServletBack/over') ;
INSERT INTO action(title,menu,url) VALUES ('任务删除',0,'/pages/back/task/TaskServletBack/rm') ;

INSERT INTO action(title,menu,url) VALUES ('公告列表',1,'/pages/back/news/NewsServletBack/show') ;
INSERT INTO action(title,menu,url) VALUES ('发布公告',1,'/pages/back/news/NewsServletBack/addPre') ;
INSERT INTO action(title,menu,url) VALUES ('公告修改',0,'/pages/back/news/NewsServletBack/editPre') ;
INSERT INTO action(title,menu,url) VALUES ('公告删除',0,'/pages/back/news/NewsServletBack/rm') ;

INSERT INTO action(title,menu,url) VALUES ('创建用户',1,'/pages/back/member/MemberServletBack/addPre') ;
INSERT INTO action(title,menu,url) VALUES ('用户列表',1,'/pages/back/member/MemberServletBack/listSplit') ;
INSERT INTO action(title,menu,url) VALUES ('用户修改',0,'/pages/back/member/MemberServletBack/editPre') ;
INSERT INTO action(title,menu,url) VALUES ('用户删除',0,'/pages/back/member/MemberServletBack/rm') ;
INSERT INTO action(title,menu,url) VALUES ('角色增加',1,'/pages/back/role/RoleServletBack/addPre') ;
INSERT INTO action(title,menu,url) VALUES ('角色列表',1,'/pages/back/role/RoleServletBack/list') ;
INSERT INTO action(title,menu,url) VALUES ('角色修改',0,'/pages/back/role/RoleServletBack/editPre') ;
INSERT INTO action(title,menu,url) VALUES ('角色删除',0,'/pages/back/role/RoleServletBack/rm') ;
INSERT INTO action(title,menu,url) VALUES ('权限组列表',1,'/pages/back/groups/GroupsServletBack/list') ;
INSERT INTO action(title,menu,url) VALUES ('权限组详情',0,'/pages/back/groups/GroupsServletBack/show') ;
INSERT INTO action(title,menu,url) VALUES ('权限列表',1,'/pages/back/action/ActionServletBack/list') ;

INSERT INTO action(title,menu,url) VALUES ('客户列表',1,'/pages/back/mclient/ManagerClientServletBack/listSplit') ;
INSERT INTO action(title,menu,url) VALUES ('客户详情',0,'/pages/back/mclient/ManagerClientServletBack/show') ;
INSERT INTO action(title,menu,url) VALUES ('用户任务列表',1,'/pages/back/mtask/ManagerTaskServletBack/listSplit') ;
INSERT INTO action(title,menu,url) VALUES ('查看客户任务',0,'/pages/back/mtask/ManagerTaskServletBack/listByClient') ;

-- 客服人员权限组
INSERT INTO groups_action(gid,actid) VALUES(1,1) ;
INSERT INTO groups_action(gid,actid) VALUES(1,2) ;
INSERT INTO groups_action(gid,actid) VALUES(1,3) ;
INSERT INTO groups_action(gid,actid) VALUES(2,5) ;
INSERT INTO groups_action(gid,actid) VALUES(2,6) ;
INSERT INTO groups_action(gid,actid) VALUES(2,7) ;
INSERT INTO groups_action(gid,actid) VALUES(2,8) ;
INSERT INTO groups_action(gid,actid) VALUES(2,9) ;
INSERT INTO groups_action(gid,actid) VALUES(2,10) ;
INSERT INTO groups_action(gid,actid) VALUES(3,11) ;

-- 公告管理员
INSERT INTO groups_action(gid,actid) VALUES(4,11) ;
INSERT INTO groups_action(gid,actid) VALUES(4,12) ;
INSERT INTO groups_action(gid,actid) VALUES(4,13) ;
INSERT INTO groups_action(gid,actid) VALUES(4,14) ;
INSERT INTO groups_action(gid,actid) VALUES(5,26) ;
INSERT INTO groups_action(gid,actid) VALUES(5,27) ;
INSERT INTO groups_action(gid,actid) VALUES(5,28) ;
INSERT INTO groups_action(gid,actid) VALUES(5,29) ;

-- 系统管理员
INSERT INTO groups_action(gid,actid) VALUES(6,11) ;
INSERT INTO groups_action(gid,actid) VALUES(6,12) ;
INSERT INTO groups_action(gid,actid) VALUES(6,13) ;
INSERT INTO groups_action(gid,actid) VALUES(6,14) ;
INSERT INTO groups_action(gid,actid) VALUES(7,15) ;
INSERT INTO groups_action(gid,actid) VALUES(7,16) ;
INSERT INTO groups_action(gid,actid) VALUES(7,17) ;
INSERT INTO groups_action(gid,actid) VALUES(7,18) ;
INSERT INTO groups_action(gid,actid) VALUES(7,19) ;
INSERT INTO groups_action(gid,actid) VALUES(7,20) ;
INSERT INTO groups_action(gid,actid) VALUES(7,21) ;
INSERT INTO groups_action(gid,actid) VALUES(7,22) ;
INSERT INTO groups_action(gid,actid) VALUES(7,23) ;
INSERT INTO groups_action(gid,actid) VALUES(7,24) ;
INSERT INTO groups_action(gid,actid) VALUES(7,25) ;
INSERT INTO groups_action(gid,actid) VALUES(8,26) ;
INSERT INTO groups_action(gid,actid) VALUES(8,27) ;
INSERT INTO groups_action(gid,actid) VALUES(8,28) ;
INSERT INTO groups_action(gid,actid) VALUES(8,29) ;

-- 管理员角色
INSERT INTO role_groups(rid,gid) VALUES (1,6) ;
INSERT INTO role_groups(rid,gid) VALUES (1,7) ;
INSERT INTO role_groups(rid,gid) VALUES (1,8) ;
-- 信息管理员角色
INSERT INTO role_groups(rid,gid) VALUES (2,4) ;
INSERT INTO role_groups(rid,gid) VALUES (2,5) ;
-- 客户人员角色
INSERT INTO role_groups(rid,gid) VALUES (3,1) ;
INSERT INTO role_groups(rid,gid) VALUES (3,2) ;
INSERT INTO role_groups(rid,gid) VALUES (3,3) ;

-- 管理员数据
-- 超级管理员：admin / hello
INSERT INTO member(mid,rid,password,tel,lastdate,photo,flag,locked) VALUES ('admin',1,'5D41402ABC4B2A76B9719D911017C592','110','2016-10-19','nophoto.jpg',1,0) ;
-- 信息管理员：mldn / java
INSERT INTO member(mid,rid,password,tel,lastdate,photo,flag,locked) VALUES ('mldn',2,'93F725A07423FE1C889F448B33D21F46','110','2016-10-19','nophoto.jpg',0,0) ;
-- 客户人员：mermaid / hello
INSERT INTO member(mid,rid,password,tel,lastdate,photo,flag,locked) VALUES ('mermaid',3,'5D41402ABC4B2A76B9719D911017C592','110','2016-10-19','nophoto.jpg',0,0) ;