bot_dev2
fddf330ab2
- GisService: 点位CRUD/空间查询(矩形+圆形)/管网数据/热力图/统计 - GisController: 11个API端点 (/api/production/gis/*) - 支持流量/压力/液位/水质/阀门5类监测点位 - Haversine距离计算 + 网格聚合热力图 - DDL: prod_gis_point/pipeline/area + 6个索引
90 lines
4.6 KiB
SQL
90 lines
4.6 KiB
SQL
-- 药剂投加监控 DDL
|
|
-- 全工艺药剂投加监控（混凝→沉淀→过滤→消毒）
|
|
|
|
-- 1. 药剂投加记录表
|
|
CREATE TABLE IF NOT EXISTS prod_chemical_dosing (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
process_stage VARCHAR(32) NOT NULL, -- 工艺段: coagulation/sedimentation/filtration/disinfection
|
|
chemical_name VARCHAR(64) NOT NULL, -- 药剂名称
|
|
chemical_code VARCHAR(32), -- 药剂编码
|
|
dosing_amount DECIMAL(12,4), -- 投加量(kg)
|
|
dosing_rate DECIMAL(10,4), -- 投加速率(kg/h)
|
|
concentration DECIMAL(10,4), -- 投加浓度(mg/L)
|
|
flow_rate DECIMAL(12,4), -- 当时流量(m³/h)
|
|
station VARCHAR(64), -- 站点/水厂
|
|
operator VARCHAR(32), -- 操作员
|
|
status VARCHAR(16) DEFAULT 'active', -- active/paused/stopped
|
|
remark VARCHAR(255),
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
COMMENT ON TABLE prod_chemical_dosing IS '药剂投加监控记录';
|
|
COMMENT ON COLUMN prod_chemical_dosing.process_stage IS '工艺段: coagulation(混凝)/sedimentation(沉淀)/filtration(过滤)/disinfection(消毒)';
|
|
|
|
-- 2. 投加历史记录表
|
|
CREATE TABLE IF NOT EXISTS prod_dosing_record (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
dosing_id BIGINT, -- 关联投加记录
|
|
process_stage VARCHAR(32) NOT NULL,
|
|
chemical_name VARCHAR(64) NOT NULL,
|
|
dosing_amount DECIMAL(12,4),
|
|
dosing_rate DECIMAL(10,4),
|
|
concentration DECIMAL(10,4),
|
|
flow_rate DECIMAL(12,4),
|
|
station VARCHAR(64),
|
|
record_time TIMESTAMP NOT NULL, -- 记录时间
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
COMMENT ON TABLE prod_dosing_record IS '投加历史记录（用于趋势分析）';
|
|
|
|
-- 3. 药剂库存表
|
|
CREATE TABLE IF NOT EXISTS prod_chemical_stock (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
chemical_name VARCHAR(64) NOT NULL,
|
|
chemical_code VARCHAR(32),
|
|
current_stock DECIMAL(12,4) NOT NULL, -- 当前库存(kg)
|
|
max_stock DECIMAL(12,4), -- 最大库存
|
|
min_stock DECIMAL(12,4), -- 安全库存(低于此值预警)
|
|
unit VARCHAR(16) DEFAULT 'kg',
|
|
warehouse VARCHAR(64), -- 仓库位置
|
|
supplier VARCHAR(128), -- 供应商
|
|
station VARCHAR(64),
|
|
status VARCHAR(16) DEFAULT 'normal', -- normal/low/out
|
|
last_inbound TIMESTAMP, -- 最近入库时间
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
COMMENT ON TABLE prod_chemical_stock IS '药剂库存管理';
|
|
|
|
-- 4. 投加策略表
|
|
CREATE TABLE IF NOT EXISTS prod_dosing_strategy (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
strategy_name VARCHAR(64) NOT NULL,
|
|
process_stage VARCHAR(32) NOT NULL,
|
|
chemical_name VARCHAR(64) NOT NULL,
|
|
strategy_type VARCHAR(32), -- auto/manual/semi-auto
|
|
base_dosing_rate DECIMAL(10,4), -- 基础投加速率
|
|
min_dosing_rate DECIMAL(10,4), -- 最小投加速率
|
|
max_dosing_rate DECIMAL(10,4), -- 最大投加速率
|
|
turbidity_threshold DECIMAL(10,4), -- 浊度阈值联动
|
|
flow_threshold DECIMAL(12,4), -- 流量阈值联动
|
|
ph_threshold_min DECIMAL(6,2), -- pH下限
|
|
ph_threshold_max DECIMAL(6,2), -- pH上限
|
|
formula VARCHAR(255), -- 投加公式
|
|
enabled BOOLEAN DEFAULT true,
|
|
station VARCHAR(64),
|
|
remark VARCHAR(255),
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
COMMENT ON TABLE prod_dosing_strategy IS '自动投加策略配置（基于原水水质/流量联动）';
|
|
|
|
-- 索引
|
|
CREATE INDEX IF NOT EXISTS idx_dosing_stage ON prod_chemical_dosing(process_stage);
|
|
CREATE INDEX IF NOT EXISTS idx_dosing_station ON prod_chemical_dosing(station);
|
|
CREATE INDEX IF NOT EXISTS idx_dosing_created ON prod_chemical_dosing(created_time);
|
|
CREATE INDEX IF NOT EXISTS idx_record_stage ON prod_dosing_record(process_stage);
|
|
CREATE INDEX IF NOT EXISTS idx_record_time ON prod_dosing_record(record_time);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_station ON prod_chemical_stock(station);
|
|
CREATE INDEX IF NOT EXISTS idx_strategy_stage ON prod_dosing_strategy(process_stage);
|