31 lines
4.8 KiB
SQL
31 lines
4.8 KiB
SQL
-- 换标PDA完工、客户确认和出库分流字段（MySQL 8，可重复执行）
|
|
USE baiyue_lastleg;
|
|
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='delivery_method'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN delivery_method VARCHAR(32) NULL COMMENT ''派送方式：TRUCK卡车、PICKUP自提、EXPRESS快递；仅涉及箱唛换标时必填'' AFTER contact_phone'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='print_shipping_label'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN print_shipping_label TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否需要打印面单'' AFTER delivery_method'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='shipping_label_file_id'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN shipping_label_file_id VARCHAR(128) NULL COMMENT ''客户上传面单文件ID'' AFTER print_shipping_label'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='shipping_label_file_name'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN shipping_label_file_name VARCHAR(255) NULL COMMENT ''客户上传面单文件名'' AFTER shipping_label_file_id'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='shipping_label_file_url'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN shipping_label_file_url VARCHAR(1000) NULL COMMENT ''客户上传面单下载地址'' AFTER shipping_label_file_name'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='fulfillment_status'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN fulfillment_status VARCHAR(32) NOT NULL DEFAULT ''WAIT_RELABEL'' COMMENT ''换标后履约状态'' AFTER shipping_label_file_url'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='outbound_order_no'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN outbound_order_no VARCHAR(64) NULL COMMENT ''自提或快递生成的换标出库单号'' AFTER fulfillment_status'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='customer_confirmation_key'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN customer_confirmation_key VARCHAR(255) NULL COMMENT ''客户确认完成幂等键'' AFTER fulfillment_status'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_relabel_application' AND column_name='customer_confirmed_at'), 'SELECT 1', 'ALTER TABLE ll_relabel_application ADD COLUMN customer_confirmed_at DATETIME(3) NULL COMMENT ''客户确认换标完成时间'' AFTER customer_confirmation_key'));
|
|
PREPARE relabel_fulfillment_stmt FROM @ddl; EXECUTE relabel_fulfillment_stmt; DEALLOCATE PREPARE relabel_fulfillment_stmt;
|
|
|
|
UPDATE ll_relabel_application
|
|
SET fulfillment_status = CASE
|
|
WHEN status = 'CUSTOMER_CONFIRM_PENDING' THEN 'WAIT_CUSTOMER_CONFIRMATION'
|
|
WHEN status = 'READY_FOR_OUTBOUND' AND delivery_method = 'TRUCK' THEN 'WAIT_PRE_DISPATCH'
|
|
WHEN status = 'READY_FOR_OUTBOUND' THEN 'WAIT_OUTBOUND_CREATION'
|
|
WHEN status = 'COMPLETED' THEN 'COMPLETED'
|
|
ELSE COALESCE(NULLIF(fulfillment_status, ''), 'WAIT_RELABEL')
|
|
END;
|