Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
163 lines (146 loc) · 8.27 KB
/
Copy pathschema.sql
File metadata and controls
163 lines (146 loc) · 8.27 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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
-- ============================================================
-- 设备报修工单系统 —— 数据结构(MySQL 8.0+)
-- 设计原则:设备台账是唯一锚点;状态只由动作推进,不允许手填时间
-- ============================================================
SET NAMES utf8mb4;
-- ------------------------------------------------------------
-- 1. 位置(车间 / 楼层 / 区域)
-- 单独建表而不是写字符串:避免"一号车间"和"1号车间"变成两个地方
-- ------------------------------------------------------------
CREATE TABLE location (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL COMMENT '位置名称',
parent_id INT UNSIGNED NULL COMMENT '上级位置,支持车间→产线两级',
sort_no INT NOT NULL DEFAULT 0,
UNIQUE KEY uk_location_name (name, parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='位置';
-- ------------------------------------------------------------
-- 2. 人员(报修人 / 维修工 / 验收人共用一个池子,用岗位区分)
-- ------------------------------------------------------------
CREATE TABLE staff (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
phone VARCHAR(20) NULL,
role ENUM('reporter','receiver','worker','inspector','manager') NOT NULL DEFAULT 'worker',
dept VARCHAR(50) NULL,
active TINYINT(1) NOT NULL DEFAULT 1,
UNIQUE KEY uk_staff_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员';
-- ------------------------------------------------------------
-- 3. 设备台账(关联锚点)
-- 设备编号必须唯一且不可改;它是所有关联的锚点,改一次全断
-- ------------------------------------------------------------
CREATE TABLE device (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(20) NOT NULL COMMENT '设备编号,如 SB-001,二维码内容即 EQP:SB-001',
name VARCHAR(50) NOT NULL,
model VARCHAR(50) NULL COMMENT '型号',
location_id INT UNSIGNED NOT NULL,
owner_id INT UNSIGNED NULL COMMENT '责任人',
enabled_on DATE NULL COMMENT '启用日期',
warranty_until DATE NULL COMMENT '保修到期',
status ENUM('running','idle','repairing','scrapped') NOT NULL DEFAULT 'running',
UNIQUE KEY uk_device_code (code),
KEY idx_device_location (location_id),
CONSTRAINT fk_device_location FOREIGN KEY (location_id) REFERENCES location(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备台账';
-- ------------------------------------------------------------
-- 4. 报修工单(主表)
-- 金额统一用 DECIMAL,不用 FLOAT/DOUBLE:对账差几分钱就是事故
-- ------------------------------------------------------------
CREATE TABLE ticket (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ticket_no VARCHAR(24) NOT NULL COMMENT '报修单号 BX-yyyyNNN,由序列生成',
device_id INT UNSIGNED NOT NULL,
source ENUM('scan','phone','wechat','patrol') NOT NULL COMMENT '报修来源',
fault_category VARCHAR(30) NOT NULL COMMENT '故障分类,下拉枚举',
urgency ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
description VARCHAR(500) NOT NULL,
reporter_id INT UNSIGNED NOT NULL COMMENT '报修人',
reported_at DATETIME NOT NULL COMMENT '报修时间(提交时写入,不可手填)',
accepted_at DATETIME NULL COMMENT '受理时间(点"受理"时由自动化写入)',
receiver_id INT UNSIGNED NULL,
dispatched_at DATETIME NULL COMMENT '派工时间',
worker_id INT UNSIGNED NULL,
expect_finish_at DATETIME NULL COMMENT '预计完工(按 SLA 自动算)',
finished_at DATETIME NULL COMMENT '实际完工',
result VARCHAR(500) NULL COMMENT '处理结果',
status ENUM('pending_accept','pending_dispatch','processing','pending_check','done','false_alarm','rejected')
NOT NULL DEFAULT 'pending_accept',
rework_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否返修',
inspector_id INT UNSIGNED NULL,
score TINYINT NULL COMMENT '满意度 1-5,仅 done 状态可填',
parts_cost DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '备件金额合计(由明细汇总)',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_ticket_no (ticket_no),
KEY idx_ticket_device_time (device_id, reported_at),
KEY idx_ticket_status (status, urgency, expect_finish_at),
CONSTRAINT fk_ticket_device FOREIGN KEY (device_id) REFERENCES device(id),
CONSTRAINT fk_ticket_worker FOREIGN KEY (worker_id) REFERENCES staff(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报修工单';
-- ------------------------------------------------------------
-- 5. 工单流转日志(审计)
-- 状态变化必须留痕:出了争议时,唯一能还原真相的就是这张表
-- ------------------------------------------------------------
CREATE TABLE ticket_log (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ticket_id BIGINT UNSIGNED NOT NULL,
from_status VARCHAR(20) NULL,
to_status VARCHAR(20) NOT NULL,
action VARCHAR(30) NOT NULL COMMENT 'accept/dispatch/finish/check/reject/reopen',
operator_id INT UNSIGNED NULL,
note VARCHAR(200) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_log_ticket (ticket_id, created_at),
CONSTRAINT fk_log_ticket FOREIGN KEY (ticket_id) REFERENCES ticket(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单流转日志';
-- ------------------------------------------------------------
-- 6. 备件
-- ------------------------------------------------------------
CREATE TABLE part (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(30) NOT NULL,
name VARCHAR(60) NOT NULL,
unit VARCHAR(10) NOT NULL DEFAULT '个',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
stock INT NOT NULL DEFAULT 0,
UNIQUE KEY uk_part_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='备件';
-- ------------------------------------------------------------
-- 7. 工单备件明细
-- 一行一个备件:不要把"换了三个轴承两个垫片"写进一个文本字段,
-- 否则永远做不出备件消耗排行
-- ------------------------------------------------------------
CREATE TABLE ticket_part (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ticket_id BIGINT UNSIGNED NOT NULL,
part_id INT UNSIGNED NOT NULL,
qty DECIMAL(10,2) NOT NULL DEFAULT 1,
unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '下单时单价快照,不随 part.price 变动',
KEY idx_tp_ticket (ticket_id),
CONSTRAINT fk_tp_ticket FOREIGN KEY (ticket_id) REFERENCES ticket(id),
CONSTRAINT fk_tp_part FOREIGN KEY (part_id) REFERENCES part(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单备件明细';
-- ------------------------------------------------------------
-- 常用视图:设备故障排行(演示和报表都用它)
-- ------------------------------------------------------------
CREATE OR REPLACE VIEW v_device_fault_rank AS
SELECT d.code, d.name, l.name AS location, COUNT(t.id) AS fault_count,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, t.reported_at, t.finished_at)) / 60, 1) AS avg_hours
FROM device d
LEFT JOIN location l ON l.id = d.location_id
LEFT JOIN ticket t ON t.device_id = d.id AND t.status IN ('done','pending_check')
GROUP BY d.id, d.code, d.name, l.name
ORDER BY fault_count DESC;
-- ------------------------------------------------------------
-- 常用视图:超时未处理(SLA 预警)
-- ------------------------------------------------------------
CREATE OR REPLACE VIEW v_overdue_ticket AS
SELECT t.ticket_no, d.name AS device, t.urgency, t.status, t.reported_at,
TIMESTAMPDIFF(MINUTE, t.reported_at, NOW()) AS elapsed_min,
CASE t.urgency WHEN 'high' THEN 60 WHEN 'normal' THEN 240 ELSE 1440 END AS sla_min
FROM ticket t
JOIN device d ON d.id = t.device_id
WHERE t.status IN ('pending_accept','pending_dispatch','processing')
HAVING elapsed_min > sla_min;