228 lines
8.8 KiB
SQL
228 lines
8.8 KiB
SQL
-- ============================================
|
|
-- V3.0__ops_maintenance_and_energy.sql
|
|
-- 运营管理模块 - 维保计划、维保任务、能耗监控
|
|
-- ============================================
|
|
|
|
-- ============================================
|
|
-- 维保计划表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_maintenance_plan (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL,
|
|
plan_code VARCHAR(50) UNIQUE NOT NULL,
|
|
plan_name VARCHAR(100) NOT NULL,
|
|
equipment_type VARCHAR(50),
|
|
trigger_type VARCHAR(30) NOT NULL,
|
|
trigger_value INTEGER,
|
|
trigger_unit VARCHAR(20),
|
|
maintenance_items TEXT,
|
|
estimated_duration INTEGER,
|
|
assigned_to UUID,
|
|
sla_response_hours INTEGER,
|
|
sla_complete_hours INTEGER,
|
|
status VARCHAR(20) DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_plan_project ON ops_maintenance_plan(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_plan_status ON ops_maintenance_plan(status);
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_plan_trigger ON ops_maintenance_plan(trigger_type);
|
|
|
|
-- ============================================
|
|
-- 维保任务表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_maintenance_task (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL,
|
|
task_code VARCHAR(50) UNIQUE NOT NULL,
|
|
plan_id UUID,
|
|
equipment_id UUID,
|
|
task_type VARCHAR(20) DEFAULT 'PREVENTIVE',
|
|
trigger_type VARCHAR(30),
|
|
maintenance_items TEXT,
|
|
status VARCHAR(20) DEFAULT 'PENDING',
|
|
assigned_to UUID,
|
|
scheduled_date TIMESTAMP,
|
|
actual_start_date TIMESTAMP,
|
|
actual_end_date TIMESTAMP,
|
|
labor_hours DECIMAL(10,2),
|
|
materials_cost DECIMAL(12,2),
|
|
remarks TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_task_project ON ops_maintenance_task(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_task_status ON ops_maintenance_task(status);
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_task_equipment ON ops_maintenance_task(equipment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_task_assignee ON ops_maintenance_task(assigned_to);
|
|
CREATE INDEX IF NOT EXISTS idx_maintenance_task_scheduled ON ops_maintenance_task(scheduled_date);
|
|
|
|
-- ============================================
|
|
-- 能耗计量点表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_energy_meter (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL,
|
|
meter_code VARCHAR(50) UNIQUE NOT NULL,
|
|
meter_name VARCHAR(100) NOT NULL,
|
|
energy_type VARCHAR(20) NOT NULL,
|
|
space_node_id UUID,
|
|
installation_location VARCHAR(200),
|
|
rated_capacity DECIMAL(10,2),
|
|
unit_price DECIMAL(10,4),
|
|
status VARCHAR(20) DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_energy_meter_project ON ops_energy_meter(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_energy_meter_type ON ops_energy_meter(energy_type);
|
|
CREATE INDEX IF NOT EXISTS idx_energy_meter_space ON ops_energy_meter(space_node_id);
|
|
|
|
-- ============================================
|
|
-- 能耗记录表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_energy_consumption (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL,
|
|
meter_id UUID NOT NULL,
|
|
consumption_date DATE NOT NULL,
|
|
previous_reading DECIMAL(12,2),
|
|
current_reading DECIMAL(12,2) NOT NULL,
|
|
consumption DECIMAL(12,2) NOT NULL,
|
|
amount DECIMAL(10,2),
|
|
recorded_by UUID,
|
|
record_method VARCHAR(20) DEFAULT 'MANUAL',
|
|
remarks TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_energy_consumption_meter ON ops_energy_consumption(meter_id);
|
|
CREATE INDEX IF NOT EXISTS idx_energy_consumption_date ON ops_energy_consumption(consumption_date);
|
|
CREATE INDEX IF NOT EXISTS idx_energy_consumption_project ON ops_energy_consumption(project_id);
|
|
|
|
-- ============================================
|
|
-- 备件分类表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_spare_part_category (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
parent_id UUID,
|
|
category_code VARCHAR(50) UNIQUE NOT NULL,
|
|
category_name VARCHAR(100) NOT NULL,
|
|
description VARCHAR(500),
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_spare_part_category_parent ON ops_spare_part_category(parent_id);
|
|
|
|
-- ============================================
|
|
-- 备件表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_spare_part (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL,
|
|
spare_part_code VARCHAR(50) UNIQUE NOT NULL,
|
|
spare_part_name VARCHAR(100) NOT NULL,
|
|
category_id UUID,
|
|
specification VARCHAR(200),
|
|
unit VARCHAR(20) NOT NULL,
|
|
safe_stock INTEGER DEFAULT 0,
|
|
current_stock INTEGER DEFAULT 0,
|
|
unit_price DECIMAL(10,2),
|
|
supplier VARCHAR(200),
|
|
supplier_contact VARCHAR(100),
|
|
location VARCHAR(200),
|
|
remarks TEXT,
|
|
status VARCHAR(20) DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_spare_part_project ON ops_spare_part(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_spare_part_category ON ops_spare_part(category_id);
|
|
CREATE INDEX IF NOT EXISTS idx_spare_part_stock ON ops_spare_part(current_stock);
|
|
|
|
-- ============================================
|
|
-- 备件记录表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_spare_part_record (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
record_code VARCHAR(50) UNIQUE NOT NULL,
|
|
record_type VARCHAR(20) NOT NULL,
|
|
spare_part_id UUID NOT NULL,
|
|
quantity INTEGER NOT NULL,
|
|
balance INTEGER NOT NULL,
|
|
related_order_id UUID,
|
|
recorded_by UUID,
|
|
record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
remarks TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_spare_part_record_part ON ops_spare_part_record(spare_part_id);
|
|
CREATE INDEX IF NOT EXISTS idx_spare_part_record_order ON ops_spare_part_record(related_order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_spare_part_record_date ON ops_spare_part_record(record_date);
|
|
|
|
-- ============================================
|
|
-- 设备故障历史表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_equipment_failure_history (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
equipment_id UUID NOT NULL,
|
|
failure_date TIMESTAMP NOT NULL,
|
|
failure_type VARCHAR(50),
|
|
failure_cause VARCHAR(200),
|
|
failure_description TEXT,
|
|
repair_start_time TIMESTAMP,
|
|
repair_end_time TIMESTAMP,
|
|
repair_duration DECIMAL(10,2),
|
|
repair_cost DECIMAL(12,2),
|
|
spare_parts_used TEXT,
|
|
work_order_id UUID,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_failure_equipment ON ops_equipment_failure_history(equipment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_failure_date ON ops_equipment_failure_history(failure_date);
|
|
|
|
-- ============================================
|
|
-- 设备健康度表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_equipment_health_score (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
equipment_id UUID NOT NULL,
|
|
score_date DATE NOT NULL,
|
|
health_score DECIMAL(5,2),
|
|
mtbf DECIMAL(10,2),
|
|
mttr DECIMAL(10,2),
|
|
failure_count INTEGER,
|
|
maintenance_completion_rate DECIMAL(5,2),
|
|
alert_level VARCHAR(20) DEFAULT 'NORMAL',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_health_equipment ON ops_equipment_health_score(equipment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_health_date ON ops_equipment_health_score(score_date);
|
|
|
|
-- ============================================
|
|
-- 点检模板表
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS ops_inspection_template (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL,
|
|
template_code VARCHAR(50) UNIQUE NOT NULL,
|
|
template_name VARCHAR(100) NOT NULL,
|
|
equipment_type VARCHAR(50),
|
|
inspection_items TEXT,
|
|
estimated_duration INTEGER,
|
|
status VARCHAR(20) DEFAULT 'ACTIVE',
|
|
version INTEGER DEFAULT 1,
|
|
created_by UUID,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_inspection_template_project ON ops_inspection_template(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_inspection_template_type ON ops_inspection_template(equipment_type);
|