78d45eea74
- 新增epay v2、v3、v3new版本的支付类库及证书文件 - 新增cos云存储类库文件 - 调整文件上传保存格式为按分类存储 - 修复会员、规则、类目等语言翻译键名 - 新增小程序图标等静态资源文件
86 行
4.0 KiB
SQL
86 行
4.0 KiB
SQL
-- ============================================================================
|
|
-- 优匠优服 · Phase 12 对标奇兵到家业务体系高级演进
|
|
-- 1. 多商品组合明细表 fa_order_goods
|
|
-- 2. 主订单表 fa_order 扩展超时转单、空跑费与施工增项补差价
|
|
-- ============================================================================
|
|
SET NAMES utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `fa_order_goods` (
|
|
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`order_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属主订单ID',
|
|
`service_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务类目ID',
|
|
`goods_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称',
|
|
`goods_model` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '规格型号',
|
|
`goods_num` INT(11) NOT NULL DEFAULT 1 COMMENT '商品数量',
|
|
`goods_image` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '商品实物照片',
|
|
`addon_services` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '增项服务(拆旧/打孔/切割)',
|
|
`base_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '单项服务基础价格',
|
|
`createtime` INT(11) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_order` (`order_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单多商品组合明细表';
|
|
|
|
-- 检查并添加字段
|
|
SET @dbname = DATABASE();
|
|
SET @tablename = "fa_order";
|
|
|
|
-- auto_quote_timeout
|
|
SET @preparedStatement = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'auto_quote_timeout') > 0,
|
|
'SELECT 1',
|
|
'ALTER TABLE `fa_order` ADD COLUMN `auto_quote_timeout` INT(11) NOT NULL DEFAULT 0 COMMENT "超时转报价单时长(秒)"'
|
|
));
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|
EXECUTE alterIfNotExists;
|
|
DEALLOCATE PREPARE alterIfNotExists;
|
|
|
|
-- empty_run_fee
|
|
SET @preparedStatement = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'empty_run_fee') > 0,
|
|
'SELECT 1',
|
|
'ALTER TABLE `fa_order` ADD COLUMN `empty_run_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT "空跑补偿费"'
|
|
));
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|
EXECUTE alterIfNotExists;
|
|
DEALLOCATE PREPARE alterIfNotExists;
|
|
|
|
-- diff_amount
|
|
SET @preparedStatement = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_amount') > 0,
|
|
'SELECT 1',
|
|
'ALTER TABLE `fa_order` ADD COLUMN `diff_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT "增项补差价金额"'
|
|
));
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|
EXECUTE alterIfNotExists;
|
|
DEALLOCATE PREPARE alterIfNotExists;
|
|
|
|
-- diff_status
|
|
SET @preparedStatement = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_status') > 0,
|
|
'SELECT 1',
|
|
'ALTER TABLE `fa_order` ADD COLUMN `diff_status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT "补差价状态:0无,1待支付,2已支付"'
|
|
));
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|
EXECUTE alterIfNotExists;
|
|
DEALLOCATE PREPARE alterIfNotExists;
|
|
|
|
-- diff_remark
|
|
SET @preparedStatement = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_remark') > 0,
|
|
'SELECT 1',
|
|
'ALTER TABLE `fa_order` ADD COLUMN `diff_remark` VARCHAR(255) NOT NULL DEFAULT "" COMMENT "补差价原因"'
|
|
));
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|
EXECUTE alterIfNotExists;
|
|
DEALLOCATE PREPARE alterIfNotExists;
|
|
|
|
-- diff_image
|
|
SET @preparedStatement = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_image') > 0,
|
|
'SELECT 1',
|
|
'ALTER TABLE `fa_order` ADD COLUMN `diff_image` VARCHAR(255) NOT NULL DEFAULT "" COMMENT "补差价凭据图"'
|
|
));
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|
EXECUTE alterIfNotExists;
|
|
DEALLOCATE PREPARE alterIfNotExists;
|