115 lines
6.5 KiB
SQL
115 lines
6.5 KiB
SQL
-- =============================================================================
|
||
-- IT-5 叫乘执行与状态机（M3）增量 DDL（可重放）
|
||
-- 依据：公寓端/03 表 3-10（状态迁移矩阵）、公寓端/04 表 4-9（叫乘接口）、
|
||
-- 公寓端/05 表 5-7（干预动作）/ 表 5-20（终态矩阵）、13 表 13-15
|
||
-- 口径：
|
||
-- 1) 不新增业务表（59 张口径不变）；只补 gy_jcxx 的时间戳/标志位存量列
|
||
-- 与 duty_manual_action 的 reason 列。
|
||
-- 2) ryzt / finish_jc / finish_ly / stoped 仍只有 apt.duty.state 一个写入口，
|
||
-- 本脚本不写任何业务数据。
|
||
-- 3) 幂等：补列走 apt_add_column_if_missing，索引先查 information_schema。
|
||
-- =============================================================================
|
||
SET NAMES utf8mb4;
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 1. gy_jcxx 存量列补齐（表 3-10 的「同表写入」列）
|
||
-- 注意：apt_add_column_if_missing(p_table, p_column, p_ddl) 的 p_ddl 必须
|
||
-- **自带列名**（过程只拼 "ALTER TABLE `t` ADD COLUMN "），
|
||
-- 与 07_patch_columns.sql 的写法一致。
|
||
-- -----------------------------------------------------------------------------
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'jcsj_s',
|
||
"jcsj_s DATETIME(3) NULL COMMENT '实际开始叫乘时刻（CALL_START 条件更新写入）'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'jch',
|
||
"jch VARCHAR(32) NULL COMMENT '机次；表 4-9 correct 白名单字段（存量表缺列，IT-5 补齐）'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'hjxysj',
|
||
"hjxysj DATETIME(3) NULL COMMENT '应答时刻：SIP ConfbridgeJoin / 平板 VERIFIED'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'jcbjsj_s',
|
||
"jcbjsj_s DATETIME(3) NULL COMMENT '叫乘报警时刻（无应答 / NO_ANSWER）'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'fjsj_s',
|
||
"fjsj_s DATETIME(3) NULL COMMENT '复叫时刻（RE_CALL 或自动复叫）'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'lybjsj',
|
||
"lybjsj DATETIME(3) NULL COMMENT '离寓报警时刻'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'is_jcbj',
|
||
"is_jcbj VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '叫乘报警标志 0/1'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'is_lybj',
|
||
"is_lybj VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '离寓报警标志 0/1'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'is_temp_call',
|
||
"is_temp_call VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '临时叫乘标志（人工临时叫乘仅置 1）'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'leave_type',
|
||
"leave_type VARCHAR(20) NULL COMMENT '离寓类型：MANUAL / DEVICE / FACE 等'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'leave_event_id',
|
||
"leave_event_id VARCHAR(64) NULL COMMENT '离寓事件 ID（gygl_ly_event.event_id）'");
|
||
CALL apt_add_column_if_missing('gy_jcxx', 'leave_remark',
|
||
"leave_remark VARCHAR(256) NULL COMMENT '离寓/修正备注（correct 白名单字段）'");
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 2. duty_manual_action：reason 列 + 动作枚举注释扩展
|
||
-- 表 4-9 第 5 个接口的 5 个命令全部先落本表，再由 apt.duty.state 执行
|
||
-- -----------------------------------------------------------------------------
|
||
CALL apt_add_column_if_missing('duty_manual_action', 'reason',
|
||
"reason VARCHAR(256) NULL COMMENT '命令原因（审计与追责用）'");
|
||
|
||
ALTER TABLE duty_manual_action
|
||
MODIFY COLUMN action_type VARCHAR(32) NOT NULL
|
||
COMMENT 'STOP / RE_CALL / FORCE_FINISH / MARK_LEAVE / CANCEL / CORRECT';
|
||
|
||
ALTER TABLE duty_manual_action
|
||
MODIFY COLUMN state VARCHAR(20) NOT NULL DEFAULT 'PENDING'
|
||
COMMENT 'PENDING / DONE / FAILED / DUPLICATED';
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 3. gy_jcxx 派发扫描索引（平板补任务 + SIP 超时扫描）
|
||
-- -----------------------------------------------------------------------------
|
||
SET @apt_idx_mode = (SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'gy_jcxx'
|
||
AND INDEX_NAME = 'idx_jcxx_gyid_mode_state');
|
||
SET @apt_sql_mode = IF(@apt_idx_mode = 0,
|
||
'ALTER TABLE gy_jcxx ADD KEY idx_jcxx_gyid_mode_state (gyid, call_mode, ryzt, finish_ly)',
|
||
'SELECT 1');
|
||
PREPARE apt_stmt_mode FROM @apt_sql_mode;
|
||
EXECUTE apt_stmt_mode;
|
||
DEALLOCATE PREPARE apt_stmt_mode;
|
||
|
||
-- 平板结果按 (gyid, occurred_at) 归日统计的覆盖索引
|
||
SET @apt_idx_result = (SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_tablet_call_result'
|
||
AND INDEX_NAME = 'idx_tabletresult_gyid_time');
|
||
SET @apt_sql_result = IF(@apt_idx_result = 0,
|
||
'ALTER TABLE apt_tablet_call_result ADD KEY idx_tabletresult_gyid_time (gyid, occurred_at)',
|
||
'SELECT 1');
|
||
PREPARE apt_stmt_result FROM @apt_sql_result;
|
||
EXECUTE apt_stmt_result;
|
||
DEALLOCATE PREPARE apt_stmt_result;
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 4. 校验
|
||
-- -----------------------------------------------------------------------------
|
||
SELECT '=== IT-5 gy_jcxx 增量列（应 12）===' AS section;
|
||
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'gy_jcxx'
|
||
AND COLUMN_NAME IN ('jcsj_s','hjxysj','jcbjsj_s','fjsj_s','lybjsj','is_jcbj',
|
||
'is_lybj','is_temp_call','leave_type','leave_event_id','leave_remark','jch')
|
||
ORDER BY COLUMN_NAME;
|
||
|
||
SELECT '=== IT-5 duty_manual_action 动作枚举（应含 5 命令 + CORRECT / reason 列）===' AS section;
|
||
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'duty_manual_action'
|
||
AND COLUMN_NAME IN ('action_type','state','reason');
|
||
|
||
SELECT '=== IT-5 索引（应 2：gy_jcxx 模式扫描 + 平板结果归日）===' AS section;
|
||
SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND INDEX_NAME IN ('idx_jcxx_gyid_mode_state','idx_tabletresult_gyid_time')
|
||
GROUP BY TABLE_NAME, INDEX_NAME;
|
||
|
||
SELECT '=== IT-5 状态字段初值兜底（应 0：不得出现 NULL/stoped 非法值）===' AS section;
|
||
SELECT COUNT(*) AS illegal_state_rows
|
||
FROM gy_jcxx
|
||
WHERE deleted = 0
|
||
AND (stoped IS NULL OR stoped NOT IN ('0','1')
|
||
OR finish_jc IS NULL OR finish_jc NOT IN ('0','1')
|
||
OR finish_ly IS NULL OR finish_ly NOT IN ('0','1'));
|