zkxw/docs/sql/qc_material_usage_out_fields_alter.sql

-- 质检物料变动记录：出库领用人/用途独立字段，来源类型补充 manual_out
-- 用法：mysql -u root -p zkxw < docs/sql/qc_material_usage_out_fields_alter.sql

SET NAMES utf8mb4;

ALTER TABLE `biz_qc_material_usage`
  ADD COLUMN `receiver` varchar(64) DEFAULT NULL COMMENT '领用人(手动出库)' AFTER `qc_task_no`,
  ADD COLUMN `purpose` varchar(512) DEFAULT NULL COMMENT '用途/事由(手动出库)' AFTER `receiver`;

ALTER TABLE `biz_qc_material_usage`
  MODIFY COLUMN `source_type` varchar(16) NOT NULL COMMENT '来源：task_complete任务完成 / manual_in手动入库 / manual_out手动出库';

-- 历史数据：从备注中回填领用人、用途（格式：领用人：xxx；用途：yyy）
UPDATE `biz_qc_material_usage`
SET
  `receiver` = TRIM(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(`remark`, '领用人：', -1),
      '；',
      1
    )
  ),
  `purpose` = TRIM(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(`remark`, '用途：', -1),
      '；',
      1
    )
  )
WHERE `source_type` = 'manual_out'
  AND `del_flag` = 0
  AND (`receiver` IS NULL OR `receiver` = '')
  AND `remark` LIKE '%领用人：%';