zkxw/docs/sql/migrations/2026-10-07-sample-freight-batch.sql

-- 样本运费批次 + 关联 + 运输方式字典预置（租户 zkxw；其他租户首次用时由后端按需补齐预置也可只插 zkxw）

CREATE TABLE IF NOT EXISTS biz_sample_freight (
  id VARCHAR(64) NOT NULL COMMENT '主键ID',
  transport_mode VARCHAR(100) NOT NULL DEFAULT '' COMMENT '运输方式字典值 sample_transport_mode',
  freight_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '运费（元）',
  tenant_no VARCHAR(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  order_num INT DEFAULT NULL COMMENT '排序号',
  remark VARCHAR(512) DEFAULT NULL COMMENT '备注',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人id',
  create_dept_id VARCHAR(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  create_time DATETIME NOT NULL COMMENT '创建时间',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人id',
  update_time DATETIME NOT NULL COMMENT '更新时间',
  del_flag SMALLINT NOT NULL DEFAULT 0 COMMENT '删除标记',
  PRIMARY KEY (id),
  KEY idx_sample_freight_tenant_ctime (tenant_no, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='样本运费批次';

CREATE TABLE IF NOT EXISTS biz_sample_freight_item (
  id VARCHAR(64) NOT NULL COMMENT '主键ID',
  freight_id VARCHAR(64) NOT NULL COMMENT '运费批次ID',
  sample_source VARCHAR(32) NOT NULL COMMENT 'cell_storage|research_donation',
  sample_id VARCHAR(64) NOT NULL COMMENT '样本ID',
  sample_code VARCHAR(32) DEFAULT NULL COMMENT '样本编码快照',
  protocol_id VARCHAR(64) DEFAULT NULL COMMENT '协议单ID',
  tenant_no VARCHAR(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  order_num INT DEFAULT NULL COMMENT '排序号',
  remark VARCHAR(512) DEFAULT NULL COMMENT '备注',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人id',
  create_dept_id VARCHAR(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  create_time DATETIME NOT NULL COMMENT '创建时间',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人id',
  update_time DATETIME NOT NULL COMMENT '更新时间',
  del_flag SMALLINT NOT NULL DEFAULT 0 COMMENT '删除标记',
  PRIMARY KEY (id),
  KEY idx_freight_item_freight (freight_id, del_flag),
  KEY idx_freight_item_sample (tenant_no, sample_source, sample_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='样本运费关联';

-- 字典类型（若不存在）
INSERT INTO sys_dict_type (
  id, dict_name, dict_type, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
  REPLACE(UUID(),'-',''), '样本运输方式', 'sample_transport_mode', 'zkxw', 0, '协议单运费批次运输方式',
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM sys_dict_type
  WHERE dict_type = 'sample_transport_mode' AND tenant_no = 'zkxw' AND del_flag = 0
);

INSERT INTO sys_dict_data (
  id, dict_label, dict_value, dict_type, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(),'-',''), '顺丰快递', 'sfkd', 'sample_transport_mode', 'zkxw', 1, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM sys_dict_data
  WHERE dict_type = 'sample_transport_mode' AND tenant_no = 'zkxw' AND dict_value = 'sfkd' AND del_flag = 0
);

INSERT INTO sys_dict_data (
  id, dict_label, dict_value, dict_type, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(),'-',''), '专线车', 'zxc', 'sample_transport_mode', 'zkxw', 2, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM sys_dict_data
  WHERE dict_type = 'sample_transport_mode' AND tenant_no = 'zkxw' AND dict_value = 'zxc' AND del_flag = 0
);