Repository navigation
Expand file tree
/
Copy pathmanual-database-setup.sql
More file actions
136 lines (124 loc) · 6.15 KB
/
Copy pathmanual-database-setup.sql
File metadata and controls
136 lines (124 loc) · 6.15 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
-- ========================================
-- Easy CRM System - 手动数据库初始化脚本
-- ========================================
-- 使用方法：
-- 1. 打开命令行，登录 MySQL: mysql -u root -p
-- 2. 依次执行以下 SQL 命令
-- ========================================
-- 1. 创建数据库
CREATE DATABASE easy_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE easy_crm;
-- 2. 创建用户表
CREATE TABLE crm_user (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
password VARCHAR(100) NOT NULL COMMENT '密码',
real_name VARCHAR(50) COMMENT '真实姓名',
phone VARCHAR(20) COMMENT '手机号',
email VARCHAR(100) COMMENT '邮箱',
role VARCHAR(20) DEFAULT 'USER' COMMENT '角色: ADMIN/USER',
status TINYINT DEFAULT 1 COMMENT '状态: 1启用 0禁用',
deleted TINYINT DEFAULT 0 COMMENT '删除标记: 0未删 1已删',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 3. 创建客户等级表
CREATE TABLE crm_customer_grade (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '等级ID',
grade_name VARCHAR(50) NOT NULL COMMENT '等级名称',
grade_level INT NOT NULL COMMENT '等级级别',
discount DECIMAL(5,2) DEFAULT 1.00 COMMENT '折扣率',
deleted TINYINT DEFAULT 0 COMMENT '删除标记',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户等级表';
-- 4. 创建客户标签表
CREATE TABLE crm_customer_tag (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
tag_name VARCHAR(50) NOT NULL COMMENT '标签名称',
tag_color VARCHAR(20) COMMENT '标签颜色',
deleted TINYINT DEFAULT 0 COMMENT '删除标记',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户标签表';
-- 5. 创建客户表
CREATE TABLE crm_customer (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
customer_name VARCHAR(100) NOT NULL COMMENT '客户姓名',
gender VARCHAR(10) COMMENT '性别',
phone VARCHAR(20) COMMENT '手机号',
email VARCHAR(100) COMMENT '邮箱',
wechat VARCHAR(50) COMMENT '微信号',
company VARCHAR(200) COMMENT '公司名称',
position VARCHAR(100) COMMENT '职位',
industry VARCHAR(100) COMMENT '行业',
source_channel VARCHAR(50) COMMENT '来源渠道',
customer_type VARCHAR(20) DEFAULT 'POTENTIAL' COMMENT '客户类型',
grade_id BIGINT COMMENT '客户等级ID',
status VARCHAR(20) DEFAULT 'NEW' COMMENT '客户状态',
address VARCHAR(500) COMMENT '地址',
remark VARCHAR(1000) COMMENT '备注',
owner_id BIGINT COMMENT '负责人ID',
follow_count INT DEFAULT 0 COMMENT '跟进次数',
last_follow_time DATETIME COMMENT '最后跟进时间',
consume_score INT DEFAULT 50 COMMENT '消费力评分',
active_score INT DEFAULT 50 COMMENT '活跃度评分',
loyalty_score INT DEFAULT 50 COMMENT '忠诚度评分',
potential_score INT DEFAULT 50 COMMENT '潜力评分',
satisfaction_score INT DEFAULT 50 COMMENT '满意度评分',
deleted TINYINT DEFAULT 0 COMMENT '删除标记',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
FOREIGN KEY (grade_id) REFERENCES crm_customer_grade(id),
FOREIGN KEY (owner_id) REFERENCES crm_user(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';
-- 6. 创建跟进记录表
CREATE TABLE crm_follow_record (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
customer_id BIGINT NOT NULL COMMENT '客户ID',
follow_user_id BIGINT NOT NULL COMMENT '跟进人ID',
follow_method VARCHAR(20) COMMENT '跟进方式: PHONE/MEETING/WECHAT/EMAIL',
follow_content TEXT COMMENT '跟进内容',
next_plan VARCHAR(500) COMMENT '下一步计划',
next_follow_time DATETIME COMMENT '下次跟进时间',
customer_demand VARCHAR(500) COMMENT '客户需求',
follow_status VARCHAR(20) DEFAULT 'COMPLETED' COMMENT '跟进状态',
deleted TINYINT DEFAULT 0 COMMENT '删除标记',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
FOREIGN KEY (customer_id) REFERENCES crm_customer(id),
FOREIGN KEY (follow_user_id) REFERENCES crm_user(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='跟进记录表';
-- 7. 创建销售漏斗表
CREATE TABLE crm_sales_funnel (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '漏斗ID',
customer_id BIGINT NOT NULL COMMENT '客户ID',
stage VARCHAR(50) COMMENT '阶段',
amount DECIMAL(15,2) COMMENT '金额',
probability INT COMMENT '成交概率',
expected_deal_date DATE COMMENT '预计成交日期',
remark VARCHAR(500) COMMENT '备注',
deleted TINYINT DEFAULT 0 COMMENT '删除标记',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
FOREIGN KEY (customer_id) REFERENCES crm_customer(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='销售漏斗表';
-- 8. 插入初始数据
-- 插入管理员用户（密码: admin123）
INSERT INTO crm_user (username, password, real_name, role) VALUES
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iAt6Z5EH', '系统管理员', 'ADMIN');
-- 插入默认客户等级
INSERT INTO crm_customer_grade (grade_name, grade_level, discount) VALUES
('普通客户', 1, 1.00),
('重点客户', 2, 0.95),
('VIP客户', 3, 0.90);
-- 插入默认客户标签
INSERT INTO crm_customer_tag (tag_name, tag_color) VALUES
('潜在客户', '#409EFF'),
('高意向', '#67C23A'),
('待跟进', '#E6A23C'),
('已成交', '#F56C6C');
-- ========================================
-- 初始化完成！
-- ========================================
-- 登录信息:
-- 用户名: admin
-- 密码: admin123
-- ========================================