50 lines
2.7 KiB
SQL
50 lines
2.7 KiB
SQL
-- 今日联系计划:仅记录真实来源的当日责任,不重建启用前历史,不改变提醒发送状态。
|
|
-- MySQL 5.7 兼容;先执行 1.9.20261109 医助服务模块表迁移。核实实际表前缀后执行。
|
|
CREATE TABLE IF NOT EXISTS `zyt_assistant_service_daily_contact` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`business_date` date NOT NULL COMMENT 'Asia/Shanghai 自然日',
|
|
`assistant_id` int unsigned NOT NULL,
|
|
`diagnosis_id` int unsigned NOT NULL,
|
|
`patient_id` int unsigned NOT NULL DEFAULT 0,
|
|
`source_type` varchar(16) NOT NULL COMMENT 'revisit/action',
|
|
`source_id` bigint unsigned NOT NULL,
|
|
`source_order_id` int unsigned NOT NULL DEFAULT 0 COMMENT '复诊原订单周期,动作固定0',
|
|
`reason` varchar(200) NOT NULL DEFAULT '',
|
|
`due_at` int unsigned NOT NULL DEFAULT 0,
|
|
`state` varchar(16) NOT NULL DEFAULT 'active' COMMENT 'active/exempt,豁免不算完成',
|
|
`exempt_reason` varchar(200) NOT NULL DEFAULT '',
|
|
`created_at` int unsigned NOT NULL,
|
|
`updated_at` int unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_day_source` (`business_date`,`assistant_id`,`source_type`,`source_id`,`source_order_id`),
|
|
KEY `idx_day_staff_patient` (`business_date`,`assistant_id`,`patient_id`),
|
|
KEY `idx_diagnosis_day` (`diagnosis_id`,`business_date`),
|
|
KEY `idx_day_cursor` (`business_date`,`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='医助今日联系固化计划与来源豁免';
|
|
|
|
-- 旧泛化动作不能自动成为联系任务。已存在列时不重写既有取值。
|
|
SET @daily_contact_column = (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_assistant_service_action' AND COLUMN_NAME = 'contact_required'
|
|
);
|
|
SET @daily_contact_sql = IF(@daily_contact_column = 0,
|
|
'ALTER TABLE `zyt_assistant_service_action` ADD COLUMN `contact_required` tinyint unsigned NOT NULL DEFAULT 0 COMMENT ''人工采纳时明确要求联系患者''',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE daily_contact_stmt FROM @daily_contact_sql;
|
|
EXECUTE daily_contact_stmt;
|
|
DEALLOCATE PREPARE daily_contact_stmt;
|
|
|
|
-- 实际完成/取消操作者只记录今后的真实操作,0表示旧数据未知,禁止按负责人回填。
|
|
SET @daily_contact_column = (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_assistant_service_action' AND COLUMN_NAME = 'completed_by'
|
|
);
|
|
SET @daily_contact_sql = IF(@daily_contact_column = 0,
|
|
'ALTER TABLE `zyt_assistant_service_action` ADD COLUMN `completed_by` int unsigned NOT NULL DEFAULT 0 COMMENT ''实际完成或取消操作者,旧数据0未知''',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE daily_contact_stmt FROM @daily_contact_sql;
|
|
EXECUTE daily_contact_stmt;
|
|
DEALLOCATE PREPARE daily_contact_stmt;
|