59 lines
3.5 KiB
MySQL
59 lines
3.5 KiB
MySQL
|
-- ==========================================================
|
|||
|
|
-- 库房重构 Phase 0：建审计日志表 + 修正历史状态
|
|||
|
|
-- 全部为新增/数据修正，零影响现有功能
|
|||
|
|
-- ==========================================================
|
|||
|
|
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
-- 1. 旧 log 表留底（结构残缺，只有 20 行）
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
RENAME TABLE `sys_oa_warehouse_log` TO `_deprecated_sys_oa_warehouse_log`;
|
|||
|
|
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
-- 2. 统一审计日志表
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
DROP TABLE IF EXISTS `sys_oa_warehouse_audit_log`;
|
|||
|
|
CREATE TABLE `sys_oa_warehouse_audit_log` (
|
|||
|
|
`log_id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志ID',
|
|||
|
|
`op_type` varchar(32) NOT NULL COMMENT '操作类型：REQ_CREATE/REQ_DONE/REQ_CANCEL/TASK_CREATE/TASK_DONE/IN/OUT/RETURN/STOCK_ADJUST 等',
|
|||
|
|
`ref_type` varchar(32) DEFAULT NULL COMMENT '关联实体类型：requirement/task/master/warehouse',
|
|||
|
|
`ref_id` bigint DEFAULT NULL COMMENT '关联实体ID',
|
|||
|
|
`project_id` bigint DEFAULT NULL COMMENT '所属项目ID（可选，便于按项目筛日志）',
|
|||
|
|
`warehouse_id` bigint DEFAULT NULL COMMENT '关联物料ID（可选，便于按物料筛日志）',
|
|||
|
|
`summary` varchar(500) NOT NULL COMMENT '一句话摘要（前端列表直接展示）',
|
|||
|
|
`before_json` text COMMENT '变更前快照JSON（可选）',
|
|||
|
|
`after_json` text COMMENT '变更后快照JSON（可选）',
|
|||
|
|
`op_user_id` bigint DEFAULT NULL COMMENT '操作人ID',
|
|||
|
|
`op_user_name` varchar(64) DEFAULT NULL COMMENT '操作人昵称（冗余便于展示）',
|
|||
|
|
`op_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
|||
|
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
|||
|
|
PRIMARY KEY (`log_id`),
|
|||
|
|
KEY `idx_op_type` (`op_type`),
|
|||
|
|
KEY `idx_ref` (`ref_type`, `ref_id`),
|
|||
|
|
KEY `idx_project_id` (`project_id`),
|
|||
|
|
KEY `idx_warehouse_id` (`warehouse_id`),
|
|||
|
|
KEY `idx_op_time` (`op_time`),
|
|||
|
|
KEY `idx_op_user_id` (`op_user_id`)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库房统一操作日志';
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
-- 3. 修正历史退库单状态（去审批化前的尾巴）
|
|||
|
|
-- type=2 且 status=0 的 8 条历史单子，本意是"已批准的退库"，统一改成 status=1
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
UPDATE `sys_oa_warehouse_master`
|
|||
|
|
SET `status` = 1,
|
|||
|
|
`update_time`= NOW(),
|
|||
|
|
`update_by` = 'system_migration'
|
|||
|
|
WHERE `type` = 2
|
|||
|
|
AND `status` = 0
|
|||
|
|
AND `del_flag` = 0;
|
|||
|
|
SELECT ROW_COUNT() AS type2_status0_fixed;
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
-- 4. 给 oa_requirements 补可读注释（之前没注释也没乱码，但 status 含义不明）
|
|||
|
|
-- ----------------------------------------------------------
|
|||
|
|
ALTER TABLE `oa_requirements`
|
|||
|
|
MODIFY COLUMN `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '完成情况（0=未采购 1=采购中 2=完成 3=取消）',
|
|||
|
|
COMMENT = '采购需求（项目大件/特殊件，对应"采购需求"菜单）';
|