bot_dev1
6dcb535874
- 新增巡检问题上报表 (patrol_problem) - 新增工单表 (work_order) - 新增工单处理记录表 (work_order_process) - 新增巡检问题与工单关联触发表 (patrol_work_order_trigger) - 实现PatrolProblemService和WorkOrderService业务逻辑 - 实现RESTful API接口 - 开发前端问题上报和工单管理界面 - 添加JSON列表TypeHandler处理图片URL数组 - 更新数据库schema支持新功能 完成了issue要求的： ✅ 巡检中问题上报(类型/描述/拍照) ✅ 自动创建工单 ✅ 处理跟踪
301 lines
12 KiB
SQL
301 lines
12 KiB
SQL
-- =============================================
|
|
-- 智慧水务管理系统 - 供水生产 + 巡检 + 报警 DDL
|
|
-- 版本: V1
|
|
-- =============================================
|
|
|
|
-- ==================== 报警规则 ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS alert_rule (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
rule_name VARCHAR(100) NOT NULL,
|
|
device_type VARCHAR(30),
|
|
metric_key VARCHAR(50) NOT NULL,
|
|
alert_level VARCHAR(10) NOT NULL, -- info/warning/critical/emergency
|
|
condition_expr VARCHAR(200) NOT NULL, -- "> 0.5" 或 "ABS_DELTA > 10"
|
|
threshold_value DECIMAL(12,4),
|
|
debounce_sec INT DEFAULT 300, -- 去重窗口(秒)
|
|
notify_scheme_id BIGINT REFERENCES sys_notify_scheme(id),
|
|
enabled SMALLINT DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE alert_rule IS '报警规则表';
|
|
COMMENT ON COLUMN alert_rule.alert_level IS '报警等级: info/warning/critical/emergency';
|
|
|
|
CREATE TABLE IF NOT EXISTS alert_event (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
rule_id BIGINT REFERENCES alert_rule(id),
|
|
device_id BIGINT,
|
|
device_sn VARCHAR(100) NOT NULL,
|
|
device_name VARCHAR(200),
|
|
area VARCHAR(50),
|
|
metric_key VARCHAR(50) NOT NULL,
|
|
metric_value DECIMAL(12,4),
|
|
threshold_value VARCHAR(50),
|
|
alert_level VARCHAR(10) NOT NULL,
|
|
title VARCHAR(200),
|
|
message TEXT,
|
|
confirmed_by BIGINT,
|
|
confirmed_at TIMESTAMP,
|
|
dispatched SMALLINT DEFAULT 0, -- 是否已派单
|
|
resolved_by BIGINT,
|
|
resolved_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE alert_event IS '报警事件表';
|
|
CREATE INDEX IF NOT EXISTS idx_alert_event_time ON alert_event(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_alert_event_device ON alert_event(device_sn, created_at DESC);
|
|
|
|
-- ==================== 调度指令 ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS dispatch_command (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
command_no VARCHAR(30) UNIQUE NOT NULL,
|
|
command_type VARCHAR(30) NOT NULL, -- routine/emergency
|
|
command_title VARCHAR(200) NOT NULL,
|
|
command_content TEXT NOT NULL,
|
|
source VARCHAR(50), -- 来源: 平台/SCADA/上级
|
|
issuer_id BIGINT,
|
|
issuer_name VARCHAR(50),
|
|
target_type VARCHAR(30), -- station/device_area/person
|
|
target_ids JSONB, -- 目标站点/设备ID数组
|
|
priority VARCHAR(10) DEFAULT 'normal', -- low/normal/high/urgent
|
|
status VARCHAR(20) DEFAULT 'draft', -- draft/issued/received/executing/completed/rejected
|
|
issued_at TIMESTAMP,
|
|
received_at TIMESTAMP,
|
|
completed_at TIMESTAMP,
|
|
result_remark TEXT,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE dispatch_command IS '调度指令表';
|
|
|
|
CREATE TABLE IF NOT EXISTS dispatch_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
command_id BIGINT REFERENCES dispatch_command(id),
|
|
action VARCHAR(20) NOT NULL, -- issue/receive/execute/complete/reject
|
|
operator_id BIGINT,
|
|
operator_name VARCHAR(50),
|
|
remark TEXT,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE dispatch_log IS '调度指令操作日志';
|
|
|
|
-- ==================== 值班管理 ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS duty_schedule (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
schedule_name VARCHAR(100),
|
|
dept_id BIGINT REFERENCES sys_dept(id),
|
|
station_id BIGINT,
|
|
shift_type VARCHAR(20), -- day/night/full
|
|
start_time TIME NOT NULL,
|
|
end_time TIME NOT NULL,
|
|
status SMALLINT DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE duty_schedule IS '值班班次表';
|
|
|
|
CREATE TABLE IF NOT EXISTS duty_record (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
schedule_id BIGINT REFERENCES duty_schedule(id),
|
|
duty_date DATE NOT NULL,
|
|
user_id BIGINT REFERENCES sys_user(id),
|
|
shift_type VARCHAR(20),
|
|
on_duty_at TIMESTAMP,
|
|
off_duty_at TIMESTAMP,
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending/on_duty/off_duty
|
|
handover_remark TEXT,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE duty_record IS '值班记录表';
|
|
|
|
-- ==================== 巡检管理 ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS patrol_route (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
route_name VARCHAR(100) NOT NULL,
|
|
area VARCHAR(50) NOT NULL,
|
|
route_points JSONB, -- [{seq, lng, lat, name, device_ids}]
|
|
estim_duration INT, -- 预计时长(分钟)
|
|
status SMALLINT DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE patrol_route IS '巡检路线表';
|
|
|
|
CREATE TABLE IF NOT EXISTS patrol_task (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
route_id BIGINT REFERENCES patrol_route(id),
|
|
assignee_id BIGINT REFERENCES sys_user(id),
|
|
task_name VARCHAR(200),
|
|
task_date DATE NOT NULL,
|
|
plan_start TIMESTAMP,
|
|
plan_end TIMESTAMP,
|
|
actual_start TIMESTAMP,
|
|
actual_end TIMESTAMP,
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending/in_progress/completed/expired/cancelled
|
|
distance DECIMAL(8,2), -- 实际巡检距离(km)
|
|
score INT, -- 评分
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE patrol_task IS '巡检任务表';
|
|
CREATE INDEX IF NOT EXISTS idx_patrol_task_date ON patrol_task(task_date);
|
|
|
|
CREATE TABLE IF NOT EXISTS patrol_record (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
task_id BIGINT REFERENCES patrol_task(id),
|
|
point_seq INT,
|
|
device_id BIGINT,
|
|
device_name VARCHAR(200),
|
|
check_items JSONB, -- [{item, result, value, photo_urls, remark}]
|
|
gps_lng DOUBLE PRECISION,
|
|
gps_lat DOUBLE PRECISION,
|
|
geom GEOMETRY(Point, 4326),
|
|
record_time TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE patrol_record IS '巡检记录表';
|
|
CREATE INDEX IF NOT EXISTS idx_patrol_record_geom ON patrol_record USING GIST(geom);
|
|
|
|
-- ==================== 水质检测 ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS water_quality_record (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
test_type VARCHAR(20) NOT NULL, -- manual/auto
|
|
test_point VARCHAR(100) NOT NULL, -- 检测点位名称
|
|
point_type VARCHAR(20), -- source_water/plant_out/pipe_network/terminal
|
|
area VARCHAR(50),
|
|
station_id BIGINT,
|
|
lng DOUBLE PRECISION,
|
|
lat DOUBLE PRECISION,
|
|
test_date DATE NOT NULL,
|
|
test_time TIME,
|
|
tester VARCHAR(50),
|
|
turbidity DECIMAL(8,2), -- 浊度(NTU)
|
|
ph DECIMAL(5,2),
|
|
residual_chlorine DECIMAL(8,2), -- 余氯(mg/L)
|
|
total_chlorine DECIMAL(8,2),
|
|
cod DECIMAL(8,2), -- 化学需氧量(mg/L)
|
|
ammonia DECIMAL(8,2), -- 氨氮(mg/L)
|
|
total_bacteria INT, -- 总大肠菌群(CFU/100mL)
|
|
extra_data JSONB, -- 扩展指标
|
|
is_qualified SMALLINT,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE water_quality_record IS '水质检测记录表';
|
|
CREATE INDEX IF NOT EXISTS idx_wq_record_date ON water_quality_record(test_date);
|
|
CREATE INDEX IF NOT EXISTS idx_wq_record_area ON water_quality_record(area);
|
|
-- =============================================
|
|
-- 智慧水务管理系统 - 巡检问题上报 + 工单管理 DDL
|
|
-- 版本: V1
|
|
-- =============================================
|
|
|
|
-- 巡检问题上报表
|
|
CREATE TABLE IF NOT EXISTS patrol_problem (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
problem_no VARCHAR(30) UNIQUE NOT NULL, -- 问题编号：WQ-2026-001
|
|
task_id BIGINT REFERENCES patrol_task(id),
|
|
point_seq INT,
|
|
device_id BIGINT,
|
|
device_name VARCHAR(200),
|
|
problem_type VARCHAR(50) NOT NULL, -- 设备故障/水质异常/安全隐患/环境卫生/其他
|
|
problem_level VARCHAR(20) DEFAULT 'normal', -- low/normal/high/critical
|
|
problem_title VARCHAR(200) NOT NULL,
|
|
problem_description TEXT,
|
|
location VARCHAR(300),
|
|
lng DOUBLE PRECISION,
|
|
lat DOUBLE PRECISION,
|
|
photo_urls JSONB, -- 现场照片URL数组
|
|
reporter_id BIGINT REFERENCES sys_user(id),
|
|
reporter_name VARCHAR(50),
|
|
report_time TIMESTAMP DEFAULT NOW(),
|
|
status VARCHAR(20) DEFAULT 'reported', -- reported/processing/completed/closed
|
|
work_order_id BIGINT, -- 关联工单ID
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE patrol_problem IS '巡检问题上报表';
|
|
CREATE INDEX IF NOT EXISTS idx_problem_task ON patrol_problem(task_id);
|
|
CREATE INDEX IF NOT EXISTS idx_problem_status ON patrol_problem(status);
|
|
CREATE INDEX IF NOT EXISTS idx_problem_device ON patrol_problem(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_problem_type ON patrol_problem(problem_type);
|
|
|
|
-- 工单表
|
|
CREATE TABLE IF NOT EXISTS work_order (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
order_no VARCHAR(30) UNIQUE NOT NULL, -- 工单编号：WO-2026-001
|
|
problem_id BIGINT REFERENCES patrol_problem(id),
|
|
order_type VARCHAR(50) NOT NULL, -- 设备维修/水质处理/安全隐患处理/清洁/其他
|
|
priority VARCHAR(20) DEFAULT 'normal', -- low/normal/high/critical
|
|
title VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
location VARCHAR(300),
|
|
contact_person VARCHAR(50),
|
|
contact_phone VARCHAR(20),
|
|
reporter_id BIGINT REFERENCES sys_user(id),
|
|
reporter_name VARCHAR(50),
|
|
assignee_id BIGINT REFERENCES sys_user(id),
|
|
assignee_name VARCHAR(50),
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending/assigned/processing/completed/cancelled
|
|
process_status VARCHAR(20) DEFAULT 'created', -- created/accepted/in_progress/completed
|
|
estimated_duration INT, -- 预计工时(分钟)
|
|
actual_start_time TIMESTAMP,
|
|
actual_end_time TIMESTAMP,
|
|
completion_time TIMESTAMP,
|
|
photos_before JSONB, -- 处理前照片
|
|
photos_after JSONB, -- 处理后照片
|
|
solution_description TEXT, -- 处理方案描述
|
|
solution_result TEXT, -- 处理结果
|
|
customer_feedback TEXT, -- 客户反馈
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE work_order IS '工单表';
|
|
CREATE INDEX IF NOT EXISTS idx_order_problem ON work_order(problem_id);
|
|
CREATE INDEX IF NOT EXISTS idx_order_status ON work_order(status, process_status);
|
|
CREATE INDEX IF NOT EXISTS idx_order_assignee ON work_order(assignee_id);
|
|
|
|
-- 工单处理记录表
|
|
CREATE TABLE IF NOT EXISTS work_order_process (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
work_order_id BIGINT REFERENCES work_order(id),
|
|
process_step VARCHAR(50) NOT NULL, -- created/accepted/in_progress/completed
|
|
processor_id BIGINT REFERENCES sys_user(id),
|
|
processor_name VARCHAR(50),
|
|
action VARCHAR(50) NOT NULL, -- create/assign/start/complete/cancel
|
|
comment TEXT,
|
|
photos JSONB, -- 处理过程照片
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE work_order_process IS '工单处理记录表';
|
|
CREATE INDEX IF NOT EXISTS idx_process_order ON work_order_process(work_order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_process_step ON work_order_process(process_step);
|
|
|
|
-- 工单附件表
|
|
CREATE TABLE IF NOT EXISTS work_order_attachment (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
work_order_id BIGINT REFERENCES work_order(id),
|
|
file_name VARCHAR(200) NOT NULL,
|
|
file_path VARCHAR(500) NOT NULL,
|
|
file_type VARCHAR(50), -- image/pdf/doc/other
|
|
file_size BIGINT,
|
|
uploaded_by BIGINT REFERENCES sys_user(id),
|
|
uploaded_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE work_order_attachment IS '工单附件表';
|
|
CREATE INDEX IF NOT EXISTS idx_attachment_order ON work_order_attachment(work_order_id);
|
|
|
|
-- 巡检问题与工单关联触发记录
|
|
CREATE TABLE IF NOT EXISTS patrol_work_order_trigger (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
patrol_problem_id BIGINT REFERENCES patrol_problem(id),
|
|
work_order_id BIGINT REFERENCES work_order(id),
|
|
trigger_type VARCHAR(20) NOT NULL, -- auto/manual
|
|
trigger_condition JSONB, -- 触发条件
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
COMMENT ON TABLE patrol_work_order_trigger IS '巡检问题与工单关联触发记录';
|
|
CREATE INDEX IF NOT EXISTS idx_trigger_problem ON patrol_work_order_trigger(patrol_problem_id);
|
|
CREATE INDEX IF NOT EXISTS idx_trigger_order ON patrol_work_order_trigger(work_order_id); |