bot_dev2
b445b089c8
- Entity: KbArticle(知识库文章), Announcement(公告), KpiDashboard(KPI看板VO)
- Mapper: KbArticleMapper, AnnouncementMapper (MyBatis-Plus)
- Service: KnowledgeBaseService(知识库CRUD+搜索+分类+点赞+热门),
AnnouncementService(公告发布/编辑/按类型筛选/按范围推送/撤回),
KpiService(KPI聚合计算:待处理量/时效/满意率/趋势/排行)
- Controller: CsSupportController (/api/revenue/cs/*)
- SQL DDL: V_cs_support.sql (cs_kb_article + cs_announcement 表+示例数据)
- Frontend: KnowledgeBaseView.vue(列表/卡片/Markdown编辑器),
AnnouncementView.vue(类型标签/状态切换/时间范围),
KpiDashboardView.vue(ECharts趋势图+饼图+排行)
- Unit Test: CsSupportServiceTest (知识库/公告/KPI三组测试)
- Router: 新增 /cs/knowledge, /cs/announcement, /cs/kpi 路由
110 lines
7.3 KiB
SQL
110 lines
7.3 KiB
SQL
-- ============================================================
|
|
-- V4__quality_ledger.sql
|
|
-- 水质检测台账模块 DDL
|
|
-- 包含: 检测记录、水质标准、检测计划
|
|
-- ============================================================
|
|
|
|
-- 1. 水质检测记录表
|
|
CREATE TABLE IF NOT EXISTS prod_quality_test_record (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
test_type VARCHAR(20) NOT NULL DEFAULT 'routine', -- routine/special/complaint
|
|
water_type VARCHAR(20) NOT NULL DEFAULT 'treated', -- raw/treated/network
|
|
sampling_point VARCHAR(100), -- 采样点
|
|
area VARCHAR(50), -- 所属区域
|
|
test_date DATE NOT NULL, -- 检测日期
|
|
test_time TIME, -- 检测时间
|
|
tester VARCHAR(50), -- 检测人
|
|
turbidity NUMERIC(10,2), -- 浊度 (NTU)
|
|
ph NUMERIC(5,2), -- pH值
|
|
residual_chlorine NUMERIC(6,3), -- 余氯 (mg/L)
|
|
color NUMERIC(8,2), -- 色度 (度)
|
|
odor NUMERIC(4,1), -- 嗅味 (级)
|
|
ecoli NUMERIC(10,2), -- 大肠杆菌 (CFU/100mL)
|
|
colony_count NUMERIC(10,2), -- 菌落总数 (CFU/mL)
|
|
compliance_status VARCHAR(20) NOT NULL DEFAULT 'pending', -- qualified/unqualified/pending
|
|
unqualified_items TEXT, -- 不合格项 (JSON)
|
|
remark VARCHAR(500), -- 备注
|
|
deleted INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_quality_record_type ON prod_quality_test_record(test_type);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_record_water_type ON prod_quality_test_record(water_type);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_record_area ON prod_quality_test_record(area);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_record_date ON prod_quality_test_record(test_date);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_record_compliance ON prod_quality_test_record(compliance_status);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_record_deleted ON prod_quality_test_record(deleted);
|
|
|
|
COMMENT ON TABLE prod_quality_test_record IS '水质检测记录表';
|
|
COMMENT ON COLUMN prod_quality_test_record.test_type IS '检测类型: routine-常规/special-专项/complaint-投诉';
|
|
COMMENT ON COLUMN prod_quality_test_record.water_type IS '水样类型: raw-原水/treated-出厂水/network-管网末梢水';
|
|
COMMENT ON COLUMN prod_quality_test_record.compliance_status IS '合格状态: qualified-合格/unqualified-不合格/pending-待判定';
|
|
|
|
-- 2. 水质标准表 (GB5749-2022)
|
|
CREATE TABLE IF NOT EXISTS prod_quality_standard (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
standard_name VARCHAR(100) NOT NULL,
|
|
standard_code VARCHAR(50) NOT NULL DEFAULT 'GB5749-2022',
|
|
param_name VARCHAR(50) NOT NULL, -- 参数编码
|
|
param_label VARCHAR(50), -- 参数显示名
|
|
param_unit VARCHAR(20), -- 单位
|
|
min_value NUMERIC(12,4), -- 最小值 (NULL=无下限)
|
|
max_value NUMERIC(12,4), -- 最大值 (NULL=无上限)
|
|
water_type VARCHAR(20) NOT NULL DEFAULT 'all', -- 适用水样类型
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
deleted INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_quality_standard_code ON prod_quality_standard(standard_code);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_standard_param ON prod_quality_standard(param_name);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_standard_deleted ON prod_quality_standard(deleted);
|
|
|
|
COMMENT ON TABLE prod_quality_standard IS '水质标准表 (基于GB5749-2022)';
|
|
|
|
-- 初始化 GB5749-2022 默认标准
|
|
INSERT INTO prod_quality_standard (standard_name, standard_code, param_name, param_label, param_unit, min_value, max_value, water_type)
|
|
VALUES
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'turbidity', '浊度', 'NTU', NULL, 1.0, 'treated'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'turbidity', '浊度', 'NTU', NULL, 3.0, 'network'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'ph', 'pH', '', 6.5, 8.5, 'all'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'residual_chlorine', '余氯', 'mg/L', 0.3, 2.0, 'treated'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'residual_chlorine', '余氯', 'mg/L', 0.05, 2.0, 'network'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'color', '色度', '度', NULL, 15.0, 'all'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'odor', '嗅味', '级', NULL, 2.0, 'all'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'ecoli', '大肠杆菌', 'CFU/100mL', NULL, 0.0, 'all'),
|
|
('生活饮用水卫生标准', 'GB5749-2022', 'colony_count', '菌落总数', 'CFU/mL', NULL, 100.0, 'all')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- 3. 水质检测计划表
|
|
CREATE TABLE IF NOT EXISTS prod_quality_test_plan (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
plan_name VARCHAR(100) NOT NULL,
|
|
test_type VARCHAR(20) NOT NULL DEFAULT 'routine', -- routine/special
|
|
water_type VARCHAR(20) NOT NULL DEFAULT 'treated',
|
|
sampling_point VARCHAR(100),
|
|
area VARCHAR(50),
|
|
frequency VARCHAR(20) NOT NULL DEFAULT 'daily', -- daily/weekly/monthly
|
|
test_params VARCHAR(200), -- 检测参数 (逗号分隔)
|
|
start_date DATE NOT NULL,
|
|
end_date DATE, -- NULL=长期
|
|
next_test_date DATE,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active', -- active/paused/completed
|
|
execution_count INTEGER NOT NULL DEFAULT 0,
|
|
remark VARCHAR(500),
|
|
deleted INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_quality_plan_status ON prod_quality_test_plan(status);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_plan_frequency ON prod_quality_test_plan(frequency);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_plan_next_date ON prod_quality_test_plan(next_test_date);
|
|
CREATE INDEX IF NOT EXISTS idx_quality_plan_deleted ON prod_quality_test_plan(deleted);
|
|
|
|
COMMENT ON TABLE prod_quality_test_plan IS '水质检测计划表';
|
|
COMMENT ON COLUMN prod_quality_test_plan.frequency IS '检测频率: daily-日检/weekly-周检/monthly-月检';
|
|
COMMENT ON COLUMN prod_quality_test_plan.status IS '计划状态: active-启用/paused-暂停/completed-已完成';
|